DBS3110 Finals Solution

- Create the EMPLOYEE table and STAFF table based on DDL in the dbs311-final-DDL file
- Take file dbs311-final-employee and load this data into an employee table
- Take file dbs311-final-staff and load this data into a staff table
- Perform a SELECT COUNT(*) from both tables to ensure there is an exact match with rows
- You should have 35 records in STAFF and 42 records in EMPLOYEE
You can use INSERT, IMPORT, LOAD – whatever you prefer – to ingest the data
Question 1: 5 marks
- Write a query which lists the employee (from EMPLOYEE table) with the highest total compensation (includes SALARY, BONUS and COMMISSION) by department and job type.
- The result set should have department, job type, employee number, total compensation.
- The result set should be ordered by department, then, job type within the department.
WHAT TO HAND IN: Provide your query. Provide the result set.
Question 2: 5 marks
- Write a query which shows the complete list of last names from both the EMPLOYEE table and STAFF table. Make sure your query is case insensitive (ie SMITH = Smith = smith).
- Make sure names are not duplicated. We only want to see each name once in the result set.
- Display the names with the initial character as a capital (ie: Smith, Jones, etc).
- Output should be in ascending order (alphabetical order) WHAT TO HAND IN: Provide your query. Provide the result set.
Question 3: 5 marks
- The output should be ordered first by employee number, then by last name WHAT TO HAND IN: Provide your query. Provide the result set.
Question 4: 5 marks
- Write a query which lists all employees across both the STAFF and EMPLOYEE table, which have an ‘oo’ OR a ‘z’ in their last name.
- This query should be case insensitive, meaning both a ‘Z’ and a ‘z’ count for the condition, as an example.

- The output should be ordered by lastname.
WHAT TO HAND IN: Provide your query. Provide the result set.
Question 5: 5 marks
- Write a query which looks at the EMPLOYEE table and, for each department, compares the manager’s total compensation (SALARY, BONUS and COMMISSION) to the top paid employee’s total compensation and displays output if the top paid employee in that department makes within $10,000 in total compensation as compared to their manager
- The output should include department, manager’s total compensation and top paid employee’s total compensation
- If a department has no non-managers – OR – has no manager, assume total compensation is 0 WHAT TO HAND IN: Provide your query. Provide the result set.
Question 6: 5 marks
- Write a query which looks across both the EMPLOYEE and STAFF table and returns the total “variable pay” (COMMISSION + BONUS) for each employee.
- If an employee does not make either, the output should be 0
- The output should include the employee’s last name and total variable pay
- The output should be ordered by a case insensitive view of their last name in alphabetical order
WHAT TO HAND IN: Provide your query. Provide the result set.
Question 7: 10 marks
- Write a stored procedure for the EMPLOYEE table which takes, as input, an employee number and a rating of either 1, 2 or 3.
- The stored procedure should perform the following changes:
- If the employee was rated a 1 – they receive a $10,000 salary increase, additional $300 in bonus and an additional 5% of salary as commission
- If the employee was rated a 2 – they receive a $5,000 salary increase, additional $200 in bonus and an additional 2% of salary as commission
- If the employee was rated a 3 – they receive a $2,000 salary increase with no change to their variable pay
- Make sure you handle two types of errors: (1) A non-existent employee – and – (2) A nonvalid rating. Both should have an appropriate message.
- The stored procedure should return the employee number, previous compensation and new compensation (all three compensation components showed separately)
- EMP OLD SALARY OLD BONUS OLD COMM NEW SALARY NEW BONUS NEW COMM
- Demonstrate that your stored procedure works correctly by running it 5 times: Three times with a valid employee number and a 1 rating, 2 rating and 3 rating. Once with an invalid employee number. Once with an invalid rating level.
WHAT TO HAND IN: A copy of your stored procedure and the output of the 5 calls described above.
Question 8: 10 marks
- Write a stored procedure for the EMPLOYEE table which takes employee number and education level upgrade as input - and - increases the education level of the employee based on the input. Valid input is:
- “M” (for masters) – and – this will update the edlevel to 23
- “P” (for PhD) – and – this will update the edlevel to 25
- Make sure you handle the error condition of incorrect education level input – and – nonexistent employee number
- Also make sure you never reduce the existing education level of the employee. They can only stay the same or go up.
- A message should be provided for all three error cases.
- When no errors occur, the output should look like:
- EMP OLD EDUCATION NEW EDUCATION
WHAT TO HAND IN: A copy of your stored procedure and the output of the a set of calls which test all input conditions and error handling. Total of 8 calls and 8 output.
Question 9: 5 marks
- Write a function called PHONE which takes an employee number as input and displays a full phone number for that employee, using the PHONENO value as part of the function.
- PHONE(100) should run for employee 100
- This function should convert the existing PHONENO value into a full phone number which looks like “(416) 123-xxxx” where xxxx is the existing PHONENO value.
- This function will return the full phone number.
WHAT TO HAND IN: A copy of the code for your function – no execution required yet
Question 10: 10 marks
- Execute an UPDATE command which adds a new column to your EMPLOYEE table called PHONENUM as CHAR(14)
- Write a stored procedure which calls your PHONE function.
- This stored procedure should go through a loop for all records where the department number begins with a E and updates the value of PHONENUM with the output of the PHONE function
- For each row, as it is updated the following should be printed
- DEPT EMP PHONENO PHONENUM
WHAT TO HAND IN: A copy of the code for your stored procedure – and – the output, as described above, from executing your stored procedure – and – the output of a SELECT WORKDEPT, PHONENO, PHONENUM FROM EMPLOYEE


Section/Part B
Time Allowed As needed – 35 marks
Instructions: This section is worth 35 marks
You will need the dbs311-final-music file for this section.
Question 1: 5 marks
- Load in the documents into a collection called “music” from the dbs311.final.json file WHAT TO HAND IN: The output from a db.music.find()
Question 2: 5 marks
- Insert a new document into the collection, following the same structure as in the music collection, as follows: - Artist is “Scorpions”
- Albums are: “Lovedrive” in 1979; “Blackout” in 1982, “Love at first sting” in 1984, “Eye to Eye” in 1999
- Singer is: Klaus Meine
WHAT TO HAND IN: The insert command – and – the return code – and – the output from a db.music.find()
Question 3: 5 marks
- Run a query which will output all of the bands and albums which hit the market in either the 1960s OR the 1990s.
- The result set should include band, album title and year – nothing else (ie: no “_Id”) WHAT TO HAND IN: The query – and – the output as described above
Question 4: 5 marks
- Run a query which reads a list of years and displays the bands and their albums which delivered in any year in the list
- The result set should include band, album title and year – nothing else (ie: no “_id”)
- Use a list of years which includes: 1971, 1973, 1975, 1977 and 1979
WHAT TO HAND IN: A copy of your query – and – the output as described above
Question 5: 5 marks
- Run a set of commands which will add in a new attribute to each document called “Guitarist” - The values should be as follows:
- For Rush – the guitarist is “Alex Lifeson”
- For Scorpions – the guitarist is “Mathias Jabs”
- For AC/DC – the guitarist is “Malcolm Young”
- For Pink Floyd – the guitarist is “David Gilmore” and “Roger Waters” (should be a list) - For Led Zeppelin – the guitarist is “Jimmy Page”
WHAT TO HAND IN: A copy of your update commands – the return codes – and – the output from a db.music.find() once all updates are complete (only do this at the end)
Question 6: 5 marks
- Run a query which shows each album and the year from the Scorpions
- Run a command which increments each of the years by 5 for every album by Scorprions - Run a query which shows each album and the year from the Scorpions
WHAT TO HAND IN: A copy of all commands – the return codes – and – the output of the query commands
Question 7: 5 marks
- Run a command which removes the document for the Scorpions
- Run a command which removes the album “The Wall” from Pink Floyd
- Run a command which removes all albums that hit the market in the 1970s from all bands WHAT TO HAND IN: A copy of the commands above – the return codes – and – the output from a db.music.find()

Instructions:
The exam is broken into 2 sections. You will submit one final submission, including all answered questions, as described in each question above.
The structure of the final exam/project is:
Part A: This is worth 65% of the exam/project. This section focuses on SQL.
Part B: This is worth 35% of the exam/project. This section focuses on Mongo.

I will be available through email and through teams to answer any questions. If you have technical issues please contact the ITS service desk at servicedesk@senecacollege.ca and I, or login to our Technician’s Microsoft Teams site (available 08:00 AM EST – 05:00 PM EST).
If you are a student with an accommodation and I have received a current accommodation letter from Counselling and Accessibility Service then I will follow the accommodations listed where able. If you have any questions or concerns please reach out to me.