DBS3110 DBS311 - MIDTERM TEST Solution

STEP 1: Create a .SQL script and call it M01-<lastname>-<firstname>.sql For example: M01-king-les.sql

STEP 2: Fill in your name at the beginning of the .sql file --NAME:

STEP 3: Execute the two CREATE TABLE commands in the dbs311-midterm-
DDL.sql file provided

STEP 4: Ingest (IMPORT, LOAD, INSERT, etc) data into your two tables
The data is in two delimited text files provided: dbs311-midterm-employee and dbs311-midterm-staff
Use whatever method of data ingestion you are most comfortable with

STEP 5:
Run a SELECT * and a SELECT COUNT(*) against both tables to:
(a) Familiarize yourself with the data and
(b) Ensure you have the correct number of rows (42 and 35).
Make sure you have the correct data in your tables.
If not, you'll end up with incorrect result sets and lose marks.

STEP 6:
You will now write the following queries
Include the queries in your M01-<lastname>-<firstname>.sql file
Make sure you include the question as comments in your .sql file

QUESTION 1:
Write a query which shows the common last names of any individuals in both tables. Make sure you ignore case (Smith=SMITH=smith). Make sure duplicates are removed. Alphabetically order the results.

QUESTION 2:
Write a query which shows the employee IDs that are unique to the employee table. Order the employee IDs in descending order. An employee ID Is the same in both tables if the integer value of the ID matches.

QUESTION 3:
We want to add a new column to the employee table. We want to provide a new column with a more complete phone number. Right now the PHONENO column only shows the last 4 digits.
We want a new column which is called PHONE and consists of ###-###-####. The last 4-digits are already in the PHONENO column. The first three digits should be 416 and the next three should be 123.
To improve clarity in the table, we also want to rename the PHONENO column to PHONEEXT.
Show all the commands used to accomplish this, then, select all data for employees who have the last name of 'smith' (case insensitive).

QUESTION 4:
Show a list of employee id, names, department, years and job of any employee in the staff table who makes a total amount more than their manager or has more years of service than their manager.
Make sure to include both salary and commission when calculating the total amount someone makes.
Exclude staff in department 10 from the query.
Order the results by department then name

QUESTION 5:
Show a list of all employees, their department and their jobs, from the staff table, that are in the same department as 'Graham'
Order by name alphabetically. Exclude 'Graham' from the result set.

QUESTION 6:
Show the list of employee names, job and variable pay, from the employee table, who have the lowest and highest variable pay (includes commission and bonus) by job category.
The name should be formatted: lastname, firstname with the first character capitalized and all other characters in lower case. (ie: King, Les). The title of this column should be “Name”.
The variable pay column should be called “Variable Pay”.
Order the results by highest variable pay to lowest variable pay.

QUESTION 7:
Using the staff table, show all employees who have an 'il' in their name - or - their name ends with an 's'. Make sure your query is case insensitive.
You just need to display the name of the employee in your output. Order them alphabetically.

QUESTION 8:
Using the staff table, display the employee name, job, salary and commission for all employees with a salary less than the salary of all people with a manager job or full compensation less than the full compensation of all the people with a sales job.
Full compensation is the sum of both salary and commission.
Exclude people with a sales job from the output.

QUESTION 9:
From the employee table, calculate the average compensation for each job category where the employee has 16 or more years of education.
Display the job and average compensation in the result set.
Exclude people who are clerks
Make sure to include salary, commission and bonus when looking at employee compensation
Order the output by the average salary in ascending order

QUESTION 10:
An individual is the same individual if a case insensitive comparison of last name matches.

STEP 7: What to hand in
Hand in your completed .sql script.
Hand in an output file called M01-<lastname>-<firstname>-output which contains all the output from executing your .sql script
I should receive two files from you in an email, for example: M01-king-les.sql and M01-king-les-output