Week 4 Lab - SQL DML (Select)
Week 4 Lab - SQL DML (Select)
References:
Rob, P. & Coronel, C. Database Systems: Design, Implementation & Management, 9th & 8th Edition, Chapter 7 Sections 7.1 – 7.2.4,
7.4, 7.6 – 7.6.2 and 7.7
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 two
things must occur:
you must preface the table with the other users name
the other user must grant you access to the table (using the SQL DCL)
For example, to see the employee table in the payroll schema (i.e., the employee table owned by payroll), you would use:
select * from [Link];
For this week’s 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.
1. Using SQL Developer examine the structure of the tables. 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 logical data model:
1 of 3
Week 4 Lab - SQL DML (Select)
The Oracle files to create/load/drop these tables are available in the archive [Link]. For this week, please do not use these files to create
the tables in your account on the Monash Oracle database, use the tables available under the PAYROLL account. If you are working from a
personal copy of Oracle XE, please use these files to create the tables this week’s lab questions are based on - first create a PAYROLL
account and then run the schema under this account so that you duplicate the on-campus experience. Creating a user under XE is detailed in
the XE PDF documentation or online.
Write and test the SQL statements to satisfy the following queries - save all your answers in a single text file. For some queries several
approaches are possible. As you code your answers keep this in mind and include some of these alternatives into your text file.
1. Display the employees who earn less than $1000.
2. List the department number of departments that have employees.
3. Display the trainers who earn less than 2500 each month and are working in department 20.
4. 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.
5. Display the employees whose job ends with the letter R.
6. Display the employees that have a name starting with “J”, “K” or “M”.
7. Display the employees who were born before 1960 and earn more than 1500 each month.
8. Display the employees that don’t have a commission.
9. Display the employee name, job, department name, location and monthly salary of employees that work in New York.
10. Display the name and job of employees who do not work in New York or Chicago.
11. Display the employees who were born in the first half of the 60s. Display the output in birth date order.
12. Display the employees who earn less than 1500 or greater than 3000 per month.
13. Display the employees who have a manager.
14. Display the employees who either work in Dallas or as a manager and earn more than 2500.
15. Display the name, job, monthly salary and salary grade of all employees. Display the list in monthly salary order within salary grade
order.
16. 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.
17. 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.
18. 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.
19. Display the employee name, empjob, monthly salary and annual salary of all employees.
20. Display the employee name, empjob, monthly salary, empcommission and annual income (salary and empcommission) of all
employees.
2 of 3
Week 4 Lab - SQL DML (Select)
m Week 4 lab as a PDF file
3 of 3
