Description
MIS562 Week 2 Homework Oracle Queries
Create the tables from demo.sql script as follows:
Download and copy the demo.sql file from the Doc Sharing to the C:\temp directory. At the sql prompt enter the command @V:\temp\demo.sql. This will create some tables and insert data into them. View the script in notepad to determine the table names that were created. Use the describe command to view the structure of the tables. Please use the instructions in the Submission Requirements to create and submit a document with your solutions. Submissions that do not meet the Requirements will be returned with a grade of zero and will be accepted and graded when corrected and re-submitted. Anyone having difficulties can obtain assistance in the TDAs.
Write SQL statements to create result tables showing the solutions to the following requests.
1. (4 pts) List all employee information in department 30.
2. (4 pts) List employees name, job, and salary that is a manager and has a salary > $1,000
3. (4 pts) Repeat exercise 2 for any employee that is not a manager or earns a salary > $1,000
4. (4 pts) Show all employee names and salary that earn between $1,000 and $2,000. Use the between operator.
5. (4 pts) Select all employees that are in department 10 and 30. Use the IN operator.
6. (4 pts) Select all employee names with an “A” in the first position of the employee name. Use the substring function or a wild card.
7. (4 pts) Select all employees with an “A” as the second character of their name. Use a wildcard.
8. (4 pts) List the employee names in alphabetical sequence.
9. (4 pts) List the job, salary, and employee name in job order and then salary in descending order.
10. (4 pts) Show a list of different jobs. Eliminate repeating values.
11. (4 pts) Show employee names, salary that has not earned a commission yet.
12. (4 pts) Show the employee name, salary, commission, and the total of salary and commission. Be sure you evaluate null values correctly.
13. (4 pts) Write an SQL query that retrieves data from the COURSE table for courses that cost 1195, and whose descriptions do not start with ‘Intro’, sorted by their prerequisites in descending order.
14. (4 pts) Write an SQL query that retrieves data from the STUDENT table for students whose last names begin with “A” though “T” and who work for ‘Competrol Real Estate’, sorted by the last names.
15. (4 pts) Determine which student does not have the first letter the last name capitalized. Show the STUDENT_ID and LAST_NAME columns.
16. (4 pts) Check if any of the phone numbers in the INSTRUCTOR table have not been entered in the (###)###-#### format. Show the instructor last name and the phone number that is in the incorrect format.
17. (4 pts) Write an SQL statement that uses the CAST function that converts a number datatype to a varchar datatype.
18. (4 pts) Write a SQL statement that converts a date datatype to a char datatype.
19. (4 pts) Write a SQL statement that convert a number value to a character.
Before doing problems 20 – 25 Download and Execute the script demoaddrows.sql from the Doc Sharing to add rows into the two tables above. Write SQL statements and produce result tables to solve the following requests.
20. (2 pts) Show a list of different jobs. Eliminate repeating values.
21. (2 pts) How many employees are working at each job in each department and what the sums and averages are for the salary of those employees?
22. (2 pts) Show the employee name with the maximum salary
23. (2 pts) Show the average salary for all employees that are not managers.
24. (2 pts) What is the difference between the highest and lowest salary?
25. (2 pts) Select employee number, name for all employees with salaries above the average salary. Use a subquery.
Before doing problems 26 through 31 Create the text Student tables with the createStudent.sql script. The script and instructions are in the schemaset.zip file available in the Doc Sharing. Download and unzip the files from the Doc Sharing to a C: drive directory such as the \temp directory. At the sql prompt enter the command @V:\temp\createStudent.sql. This will create tables exactely as they are portrayed in the text and insert data into them.
26. (2 pts) Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence. Use the ZIPCODES table.
27. (2 pts) Which city has the most zip codes assigned to it? Use a TOP-N query.
28. (2 pts) Show the state and the number of zip codes by state. Order the result by number of zip codes in descending sequence and exclude all states having less than 5 zip codes.
29. (2 pts) Show the Student_ID, last name, and the number of enrollments for the student. Show only students with more than 2 enrollments. Use an Inline view.
30. (2 pts) Show the number of students enrolled and zip code for New York and where the city begins with ‘W’. Use a Scalar subquery.
31. (2 pts) Display the course number and description of courses with no enrollment. Also include courses which have no section assigned.
Reviews
There are no reviews yet.