Week 6 Tutorial - SQL Part 1
Tutorial 6 – SQL Part 1
- 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 .
In a SELECT statement a table name is always prefaced by the schema (owners/users) name, where you omit the owners name (as we have so far) Oracle automatically appends the name of the current logged in user, so you see your own table. To view someone else's table contents two things must occur:
- you must preface the table in the SQL statement with the other users name
- the other user must grant you access to the table
For example to see the employee table in the payroll schema (ie. the employee table owned by payroll), you would use:
select * from payroll.employee;
For this weeks tutorial the tables which you will be viewing have all been created by the user PAYROLL. The user payroll has GRANTed you select privilege on these tables.
- Using SQL Developer examine the structure of the tables and prepare a logical ERD (DSD) for the set of tables in the PAYROLL schema. To view these table you will need to select Other Users (if PAYROLL is not shown select 'Show More' and then All) in SQL Developer in the left panel.
The tables represent the following data model:
The Oracle files to create/load/drop these tables and the Microsoft VISIO ERD are available in the archive topic7-8.zip. For this week, please do not use these files to create the tables in your account of the Monash database, use the tables available under the PAYROLL account.
Write and test the SQL statements to satisfy the following queries - save all your answers in a single text or MS Word file
- Display the employees who earn less than $1000.
- List the department number of departments that have employees.
- Display the trainers who earn less than 2500 each month and are working in department 20.
- Display the name, job, monthly salary and commission of employees whose monthly salary is higher than their commission. Rename the columns: Name, Job, Monthly Salary and Commission.
- Display the employees whose job ends with the letter R.
- Display the employees that have a name starting with “J”, “K” or “M”.
- Display the employees who were born before 1960 and earn more than 1500 each month.
- Display the employees that don’t have a commission.
- Display the employee name, job, department name, location and monthly salary of employees that that work in New York.
- Display the name and job of employees who do not work in New York or Chicago.
- Display the employees who were born in the first half of the 60s. Display the output in birth date order.
- Display the employees who earn less than 1500 or greater than 3000 per month.
- Display the employees who have a manager.
- Display the employees who either work in Dallas or as a manager and earn more than 2500.
- Display the name, job, monthly salary and salary grade of all employees. Display the list in monthly salary order within salary grade order.
- Display the name and location of all departments, and the name of their employees. Display the output in employee name order within department name order.
- Display the name of all employees, their job and the name of their manager. List the output in employee name order within manager name order.
- For each employee display their employment history. In the listing include the employees’ name, the name of the department they worked for, the begin and end date and their monthly salary. Display the output in begin date order (most recent at the top of the list) within employee name order.
Week 6 tutorial as a PDF file