Compiled Contents of:Week 7 SQL DML Part 2

 

Content File: Topic 8 Objectives


Topic 8 Objectives

Topic 8 Objectives

After completing this weeks study students should be able to:

  1. Use aggregate and SQL functions
  2. Manipulate sets of data
  3. Write subqueries
  4. Manipulate data in the database

 

 

Content File: FIT1004SG8.pdf


FIT1004SG8.pdf
 

Content File: fit1004t8.pdf


fit1004t8.pdf
 

Content File: Week 7 Tutorial - SQL Part 2


Week 7 Tutorial - SQL Part 2

Tutorial 7 – SQL Part 2

References:

  • Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 6th Edition, 2004. Chapter 6 & 7.
  • Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 7th Edition, 2006. Chapter 7 & 8 .

For this weeks tutorial we will be using the same scenario as last weeks tutorial. Download the archive topic7-8.zip and create and load the tables in your Oracle account.

  1. Display all employees, their current annual salary (not including commission) and what their annual salary would be if they were given a 10% pay rise.
  2. Display the name of all employees, their birthdate and their age in years.
  3. Display all employees, their job and their current annual salary (including commission).
  4. Display all employees’ details in the following format: EMPLOYEE N. Smith IS A Trainer AND WORKS IN THE Training DEPARTMENT.
  5. Display the name of all employees, their birthdate and their age in months.
  6. Display the employees who were born in February.
  7. Display the employees (using the GREATEST function) who earn more commission than their monthly salary.
  8. Display the name of all employees and their birthdate in the following format: EMPLOYEE N. Smith was born on FRIDAY the 17 of DECEMBER , 1965
  9. Display the name of the employees who have registered for a course and the number of times they have registered.
  10. Who is the oldest employee?
  11. For each department list the department number and name, the number of employees, the minimum and maximum monthly salary, the total monthly salary and the average salary paid to their employees. Name the columns: NbrOfEmployees, MinSalary, MaxSalary, TotalSalary, AvgSalary
  12. Display the jobs in each department and the total monthly salary paid for each job.
  13. Which employee earns more than the average salary?
  14. Which department has the greatest average monthly salary?
  15. Which course has the most offerings?
  16. Display the name of employees who perform the same job as SCOTT and were born in the same year. Do not include SCOTT in the output.
  17. Using the MINUS statement, which employees have never registered in a course.
  18. Using the INTERSECT statement, which employees have both registered for and conducted courses.
  19. Add the following details to the employee table. Employee number: 9999, name: H.B. Bear, birthdate: 21st July 1965, monthly salary: $3500, department same as MILLER.
  20. Update employee H.B. Bear’s monthly salary to the same amount as KING’s, his job to the same as CLARK’s and his department to SALES.
  21. Delete employee H.B. Bear.

Week 7 tutorial as a PDF file

 

Content File: wk7-tut.pdf


wk7-tut.pdf