Oracle数据库习题Practice311Word下载.docx

1、5. Write a query that displays the last name （with the first letter uppercase and all other letters lowercase） and the length of the last name for all employees whose name starts with the letters J, A, or M. Give each column an appropriate label. Sort the results by the employees last names.Rewrite

2、the query so that the user is prompted to enter a letter that starts the last name. For example, if the user enters H when prompted for a letter, then the output should show all employees whose last name starts with the letter H. Practice 4 Determine the validity of the following three statements. C

3、ircle either True or False.1.Group functions work across many rows to produce one result per group.True/False2.Group functions include nulls in calculations. True/False3.The WHERE clause restricts rows before inclusion in a group calculation.The HR department needs the following reports:4. Find the

4、highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Place your SQL statement in a text file named lab_04_04.sql.5. Modify the query in lab_04_04.sql to display the minimum, maxi

5、mum, sum, and average salary for each job type. Resave lab_04_04.sql as lab_04_05.sql. Run the statement in lab_04_05.sql. 6、Write a query to display the number of people with the same job.Save the script to a file named lab_04_06.sql.7. Determine the number of managers without listing them. Label t

6、he column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers.8. Find the difference between the highest and lowest salaries. Label the column DIFFERENCE.If you have time, complete the following exercises:9. Create a report to display the manager number and the sa

7、lary of the lowest-paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is $6,000 or less. Sort the output in descending order of salary. If you want an extra challenge, complete the following exercises:10. Create a query to display t

8、he total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.11. Create a matrix query to display the job, the salary for that job based on department number, and the total salary for that job, for departments 20, 50

9、, 80, and 90, giving each column an appropriate heading. Practice 5 1、Write a query for the HR department to produce the addresses of all the departments. Use the LOCATIONS and COUNTRIES tables. Show the location ID, street address, city, state or province, and country in the output. Use a NATURAL J

10、OIN to produce the results.2、The HR department needs a report of all employees. Write a query to display the last name, department number, and department name for all employees.3、The HR department needs a report of employees in Toronto. Display the last name, job, department number, and department n

11、ame for all employees who work in Toronto.4、Create a report to display employees last name and employee number along with their managers last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Place your SQL statement in a text file named lab_05_04.sql.5、Modi

12、fy lab_05_04.sql to display all employees including King, who has no manager. Order the results by the employee number. Place your SQL statement in a text file named lab_05_05.sql. Run the query in lab_05_05.sql.6、numbers, and all the employees who work in the same department as a given employee. Gi

13、ve each column an appropriate label. Save the script to a file named lab_05_06.sql.7、The HR department needs a report on job grades and salaries. To familiarize yourself with the JOB_GRADES table, first show the structure of the JOB_GRADES table. Then create a query that displays the name, job, depa

14、rtment name, salary, and grade for all employees.8. The HR department wants to determine the names of all employees who were hired after Davies. Create a query to display the name and hire date of any employee hired after employee Davies. 9、The HR department needs to find the names and hire dates fo

15、r all employees who were hired before their managers, along with their managers names and hire dates. Save the script to a file named lab5_09.sql.Practice 61. The HR department needs a query that prompts the user for an employee last name. The query then displays the last name and hire date of any e

16、mployee in the same department as the employee whose name they supply （excluding that employee）. For example, if the user enters Zlotkey, find all employees who work with Zlotkey （excluding Zlotkey）.2. Create a report that displays the employee number, last name, and salary of all employees who earn

17、 more than the average salary. Sort the results in order of ascending salary.3. Write a query that displays the employee number and last name of all employees who work in a department with any employee whose last name contains a u. Place your SQL statement in a text file named lab_06_03.sql. Run you

18、r query.4. The HR department needs a report that displays the last name, department number, and job ID of all employees whose department location ID is 1700.Modify the query so that the user is prompted for a location ID. Save this to a file named lab_06_04.sql.5. Create a report for HR that display

19、s the last name and salary of every employee who reports to King.6、 Create a report for HR that displays the department number, last name, and job ID for every employee in the Executive department.If you have time, complete the following exercise:7. Modify the query in lab_06_03.sql to display the e

20、mployee number, last name, and salary of all employees who earn more than the average salary and who work in a department with any employee whose last name contains a u. Resave lab_06_03.sql as lab_06_07.sql. Run the statement in lab_06_07.sql. Practice 71.The HR department needs a list of departmen

21、t IDs for departments that do not contain the job ID ST_CLERK. Use set operators to create this report.2. The HR department needs a list of countries that have no departments located in them. Display the country ID and the name of the countries. Use set operators to create this report.3. Produce a l

22、ist of jobs for departments 10, 50, and 20, in that order. Display job ID and department ID using set operators.4. Create a report that lists the employee IDs and job IDs of those employees who currently have a job title that is the same as their job title when they were initially hired by the compa

23、ny （that is, they changed jobs but have now gone back to doing their original job）.5. The HR department needs a report with the following specifications:-Last name and department ID of all the employees from the EMPLOYEES table, regardless of whether or not they belong to a department-Department ID

24、and department name of all the departments from the DEPARTMENTS table, regardless of whether or not they have employees working in themWrite a compound query to accomplish this.Practice 8The HR department wants you to create SQL statements to insert, update, and delete employee data. As a prototype,

25、 you use the MY_EMPLOYEE table, before giving the statements to the HR department.Insert data into the MY_EMPLOYEE table.1. Run the statement in the lab_08_01.sql script to build the MY_EMPLOYEE table to be used for the lab.2. Describe the structure of the MY_EMPLOYEE table to identify the column na

26、mes.2.Create an INSERT statement to add the first row of data to the MY_EMPLOYEE table from the following sample data. Do not list the columns in the INSERT clause. Do not enter all rows yet.4. Populate the MY_EMPLOYEE table with the second row of sample data from the preceding list. This time, list

27、 the columns explicitly in the INSERT clause.5. Confirm your addition to the table.6. Write an insert statement in a dynamic reusable script file named loademp.sql to load rows into the MY_EMPLOYEE table. Concatenate the first letter of the first name and the first seven characters of the last name

28、to produce the user ID. Save this script to a file named lab_08_06.sql.7. Populate the table with the next two rows of sample data listed in step 3 by running the insert statement in the script that you created.8. Confirm your additions to the table.9. Make the data additions permanent.Update and de

29、lete data in the MY_EMPLOYEE table.10. Change the last name of employee 3 to Drexler.11. Change the salary to $1,000 for all employees who have a salary less than $900.12. Verify your changes to the table.13. Delete Betty Dancs from the MY_EMPLOYEE table.14. Confirm your changes to the table. Practi

30、ce 9 1.Create the DEPT table based on the following table instance chart. Place thesyntax in a script called lab_09_01.sql, and then execute the statement in the script to create the table. Confirm that the table is created.2. Populate the DEPT table with data from the DEPARTMENTS table. Include only columns that you need.3. Create the EMP table