oracle实验内容2(共40题).doc

Part 11. Write a query to display the current date. Label the column Date.select to_char(sysdate,year-mon-day) “Date”from dual 2.The HR department needs a report to display the employee number, last name, salary, and salary increased by 15.5% (expressed as a whole number) for each employee. Label the column New Salary. Place your SQL statement in a text file named lab_03_02.sql.select employee_id,last_name,salary,salary*1.155 new_salaryfrom employees3.Run your query in the file lab_03_02.sql. 4.Modify your query lab_03_02.sql to add a column that subtracts the old salary fromthe new salary. Label the column Increase. Save the contents of the file as lab_03_04.sql. Run the revised query. select employee_id,last_name,salary,salary*1.155 new_salary,salary*0.155 increasefrom employees5. 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.select last_name,length(last_name) lengthfrom employeeswhere last_name like A% or last_name like J%or last_name like M%order by last_nameRewrite 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.6.The HR department wants to find the length of employment for each employee. For each employee, display the last name and calculate the number of months between today and the date on which the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.Note: Your results will differ.select last_name,round(months_between(sysdate,hire_date) MONTHS_WORKEDfrom employeesorder by hire_date desc7.Create a report that produces the following for each employee: earns monthly but wants . Label the column Dream Salaries.select last_name| earns|to_char(salary,$999,999.99)| monthly but wants|to_char(3*salary,$999,999.99)from employees8. Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with the $ symbol. Label the column SALARY. select last_name,lpad(salary,15,$) SALARYfrom employees9. Display each employees last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”Selectlast_name,to_char(hire_date,dd-mm-yy) hire_date,to_char(next_day(add_months(hire_date,星期一),day,the ddspth of month,yyyy) REVIEWfrom employees10. Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week, starting with Monday.select last_name,hire_date,to_char(hire_date,day) DAYfrom employeesorder by to_char(hire_date-1,d)11. Create a query that displays the employees last names and commission amounts. If an employee does not earn commission, show “No Commission.” Label the column COMM.Select last_name,nvl(to_char(commission_pct), No Commission) COMMFrom employees12. Create a query that displays the first eight characters of the employees last names and indicates the amounts of their salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES.Select rpad(substr(last_name,1,8),8)| lpad( ,salary/1000,*)from employeesorder by salary desc13. Using the DECODE function, write a query that displays the grade of all employees based on the value of the column JOB_ID, using the following data:JobGrade AD_PRESA ST_MANB IT_PROGC SA_REPD ST_CLERKE None of the above0select job_id,decode(job_id,AD_PRES,A, ST_MAN,B, IT_PROG,C, SA_REP,D, ST_CLERK,E,0) GRAfrom employees14. Rewrite the statement in the preceding exercise using the CASE syntax. select job_id,case job_id when AD_PRESthen A when ST_MANthenB whenIT_PROGthen C whenSA_REPthenD whenST_CLERKthenEelse 0 end GRAfrom employees15.Group functions work across many rows to produce one result per group.True/FalseT16Group functions includee nulls in calculations.True/FalseF17.The WHERE clause restricts rows before inclusion in a group calculation.True/FalseTThe HR department needs the following reports:18Find the highest, lowest, sum, and average salary of all employees. Label the columnsMaximum, 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. select max(salary) maximum,min(salary) minimum,sum(salary) Sum, round(avg(salary) Averagefrom employees 19,Modify the query in lab_04_04.sql to display the minimum, maximum, 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. select job_id,max(salary) maximum,min(salary) minimum,sum(salary) Sum, round(avg(salary) Averagefrom employeesgroup by job_id 20.Write a query to display the number of people with the same job.select job_id,count(*)from employeesgroup by job_id21.Determine the number of managers without listing them. Label the column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers. select count(distinct manager_id) Number of Managersfrom employees 22. Find the difference between the highest and lowest salaries. Label the column DIFFERENCE.select max(salary)-min(salary) Differencefrom employees23. Create a report to display the manager number and the salary 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. select manager_id,min(salary)from employeeswhere manager_id is not nullgroup by manager_idhaving min(salary)600024Create a query to display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings. select count(*) total,sum(decode(to_char(hire_date,yyyy),1995,1,0) 1995,sum(decode(to_char(hire_date,yyyy),1996,1,0) 1996,sum(decode(to_char(hire_date,yyyy),1997,1,0) 1997,sum(decode(to_char(hire_date,yyyy),1998,1,0) 1998from employees 25.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, 80, and 90, giving each column an appropriate heading. select job_id JOB,sum(decode(department_id,20,salary,0) Dept 20,sum(decode(department_id,50,salary,0) Dept 50,sum(decode(department_id,80,salary,0) Dept 80,sum(decode(department_id,90,salary,0) Dept 90,sum(salary) Totalfrom employeesgroup by job_id26.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 JOIN to produce the results.select LOCATION_ID,STREET_ADDRESS,CITY,STATE_PROVINCE,COUNTRY_NAMEfrom LOCATIONS natural join COUNTRIES 27.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.select last_name,department_id,department_namefrom employees natural join departments28.The HR department needs a report of employees in Toronto. Display the last name, job, department number, and department name for all employees who work in Toronto.select last_name,job_id,department_id,department_name,cityfrom employees join departments using(department_id)join locations using(location_id) where city=Toronto29. 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.Select e1.last_name,e1.employee_id,e2.last_name,e1.manager_idFrom employees e1 join employees e2 on e1.manager_id=e2.employee_id30. Modify 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. Select e1.last_name,e1.employee_id,e2.last_name,e1.manager_idFrom employees e1 left join employees e2 on e1.manager_id=e2.employee_id Order by e1.employee_id 31. Create a report for the HR department that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label. Save the script to a file named lab_05_06.sqlselect e1.department_id,e1.last_name EMPLOYEE,e2.last_name COLLEAGUEfrom employees e1 join employees e2 on e1.DEPARTMENT_ID = e2. DEPARTMENT_ID and e1.LAST_NAMEe2.LAST_NAMEorder by e1.department_id32. 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 table. Then create a query that displays the name, job, department name, salary, and grade for all employees.select last_name,job_id,department_name,salary,GRADE_LEVEL from employees join departments using(department_id) join JOB_GRADES on salaryLOWEST_SAL and salary(select hire_date from employees where last_name = Davies)34. The HR department needs to find the names and hire dates for 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.select e1.LAST_NAME,e1.HIRE_DATE,e2.last_name,e2.hire_date from employees e1 join employees e2 on e1.MANAGER_ID = e2.employee_idwhere e1.hire_date(select avg(salary) from employees)order by salary37.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 your query.select employee_id,last_namefrom employeeswhere department_id in (select department_idfrom employeeswhere last_name like%u%)38.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. select last_name,department_id,job_idfrom employees join departments using(department_id) join locatio