Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cantstopoulos
Starting Member
1 Post |
 Posted - 2011-03-29 : 01:09:05
|
| Hey I have some practice problems for an intro to SQL Server that I need help with, if anyone can with any of the below that'd be great let me know, thanks.SQL Practice Problems1. Create synonyms for the admin01 tables (CUSTOMER, SALES_REP, ORDERS, ORDER_LINE, PART, EMP DEPT, SALGRADE)2. Describe the structure of the ORDERS table.3. List the contents of the PART table.4. Create a table named ORDERS2 with the same structure as ORDERS.5. Copy into ORDERS2 all rows from ORDERS for which ORDER_NUMBER < 12500.6. Change the data type of ORDER_NUMBER in ORDERS2 to NUMBER(6).7. Add a column named ORDER_DESCRIPTION, with data type VARCHAR2(10), to the ORDERS2 table.8. For ORDER_NUMBER 12489, change the C_NUMBER to 315 (in the ORDERS2 table).9. Delete the row for ORDER_NUMBER 12491 (from the ORDERS2 table).10. Declare ORDER_NUMBER as the primary key of ORDERS2 and call it PK_ORDERS2.11. Show the last and first names of the customers whose credit limit is more than $1,500.12. Show the part # and part description of all parts whose item class is HW or AP.13. List all order #s and part #s for which the quoted price falls between $20 and $25.14. List the last and first names of all customers who have a negative available credit currently.15. Use the IN operator to find the last and first names of all customers who are serviced by sales rep 3 or 12.16. Find all part #s and part descriptions for which the part description begins with the letter B.17. For each part in item class HW, show the part # and the dollar amount in inventory (which is units on hand * unit price). Call the latter INV_$VALUE.18. List the contents of the part table sorted in ascending order of item class and, within each item class, sorted in descending order of unit price.19. Find the total # of parts (in the part table).20. Find the $ amount in inventory (considering all parts). Call this amount TOT_INV_$AMT.21. Find how many cities appear in the addresses of the customer table. Don’t count the same city more than once.22. Use a subquery to find the part # and part description of all parts whose unit price is more than the average unit price of parts in item class HW.23. Show the part # and part description of any part which has not been ordered yet.24. For every part, show the number of times it has been ordered. Include parts which may not have been ordered yet.25. Show the order # and customer # of every pair of orders which are made on the same date.26. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * ** * * * * * * * * * * *27. Show the hire date of each employee in the format MM-DD-YY.28. Show the name and hire date of each employee who was hired before 12-01-2002.29. For every department which has employees, list the department # and the minimum of the salaries of its employees. (You should not include any employee whose department is null.)30. Repeat the previous query, with the condition that the minimum salary for the department is more than $1200.31. Find all employees who are located in New York.32. Show the name of each employee who has a manager, along with the name of the manager. Call these names EMP_NAME and MGR_NAME, respectively.33. Use a set operator to find out every employee who works in the accounting department and whose salary is between $1,000 and $1,500. (First, note that you are not supposed to know the department # of the accounting department.)34. Solve the above query without using a set operator.35. List the names and salaries of all employees who make more than the average salary of his/her department. 36. Create a view named sales_emp (with four columns named as emp#, empname, salary, and hire_date) which includes only those employees of the emp table who belong to the sales department. (As before, you are not supposed to know the department # of the sales department.) Then, show the contents of this view. |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2011-03-29 : 06:11:00
|
If you're taking a class... and this is your practice, you should reference your course materials and attempt to do this on your own. It completely defeats the purpose if we do these for you.Without a reference to the database, many of these questions we couldn't help you with anyway.If youare making an honest attempt, and are having a syntax issue or something, post some sample data and expectation and people will probably be able to help.Corey snSQL on previous signature "...Oh and by the way Seventhnight, your signature is so wrong! On so many levels, morally, gramatically and there is a typo!"  |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-03-29 : 06:27:24
|
| These are Oracle questions. This is a Microsoft SQL Server forum. Most people here do not have extensive experience with Oracle, they are SQL Server people. You'll probably get better answers if you ask on a forum that's for the database engine that you are using.In addition, we don't do homework. I don't need the practice, I don't need the qualification. Show what you've done, explain where you're stuck, someone will offer advice. Not do it for you, you learn nothing in that case.--Gail ShawSQL Server MVP |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-29 : 10:50:50
|
quote:
26. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * ** * * * * * * * * * * *
Ohhh! Ohhh! I can answer that one for you, it's:"% % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %% % % % % % % % % % % %"Without the quotes, of course. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2011-03-29 : 14:08:04
|
quote:
Originally posted by robvolk
quote:
26. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * ** * * * * * * * * * * *
Ohhh! Ohhh! I can answer that one for you, it's:"% % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %% % % % % % % % % % % %"Without the quotes, of course.
Just in case you need an example:select [% % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % % %% % % % % % % % % % % %]from [* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * ** * * * * * * * * * * *] as [# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # ## # # # # # # # # # # #]where [^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^] in ( '$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $$ $ $ $ $ $ $ $ $ $ $ $', '@ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @ @@ @ @ @ @ @ @ @ @ @ @ @', '| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | || | | | | | | | | | | |' )order by [& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & & && & & & & & & & & & & &] CODO ERGO SUM |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-03-29 : 14:27:01
|
| Aw man, how are they supposed to learn if you just throw out the full answer like that? |
|
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2011-03-29 : 14:42:46
|
Watch your language!  http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspxHow to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxFor ultra basic questions, follow these links.http://www.sql-tutorial.net/ http://www.firstsql.com/tutor.htm http://www.w3schools.com/sql/default.asp |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
Pactice problems I need help with to understand