DBS211 Assignment – SQL Solution

Objectives
The purpose of this assignment is to assess the students’ knowledge about storing and querying data from a database considering the relationships between tables in that database. Students will query data from a database (single/multiple tables) and use the relationships to reconnect some tables when extracting data and obtaining information. Student will:
• Produce query results containing data from multiple tables using joins and demonstrate their knowledge of inner, outer and full joins.
• Troubleshoot queries to handle potentially ambiguous fields across multiple tables through the use of aliases.
Submission
Your submission will be a single SQL file with the solutions provided. (with a .sql file extension and PDF include all the codes/outputs)
DBS211_Assignment01.sql
Your submission needs to include a comment header block and be commented top clearly indicate the answers to each question. Make sure every SQL statement terminates with a semicolon.
Example Submission
-- *********************** -- Student Names:
-- Student (ID)
-- Purpose: Assignment 01 DBS211
-- ***********************

-- Q1 SOLUTION --
SELECT * FROM TABLE;

-- Q2 Solution –
SELECT * FROM TABLE;
Locate, select, and submit the file to the assignment link.
Setup


Style Guide
Your SQL should be written using the standard coding style:
• all keywords are to be upper case,
• all user-defined names are to be lower case, (example: table and field names)
• there should be a carriage return before each major part of the SQL statements (i.e. before SELECT, FROM, WHERE and ORDER BY)
See the following sample:

SELECT columns
FROM tables
WHERE conditions
ORDER BY column1, column2;

To save time, you can write the SQL statements in your SQL developer. To make sure that your SQL statements style follows the standard SQL style guideline, copy and paste your SQL statements onto the following website and click on “FORMAT SQL” or “FORMAT SQL IN NEW WINDOW”.
https://www.freeformatter.com/sql-formatter.html#ad-output
You can also upload your SQL file. See the setting in the following image. Have SQL keywords (SELECT, INSERT, UPDATE, etc.) uppercase and user defined objects and identifiers (tables, columns, etc.) lowercase.




Marking Scheme
Question Points Question Points
1 2 6 1.5
2 1.5
3 2
4 1.5
5 1.5
Total: 10
Grade Policy
▪ Questions with errors do not get any marks. (They get zero.) o Execute your .sql file by selecting all the statements and using the “Run statement” button to make sure there is no errors in your file and check the output result returned by each query.
▪ Make sure your result in a question matches the sample output result.
▪ You do not receive marks for the missing or incomplete solutions.
Tasks:
Important: For each question, the title of columns and the output result must match the provided output result in that question to receive a full mark. Remember to comment the question number for each solution.
IF you do not have a solution for any question, write the following statement for that question.
See the example:
SELECT 'No Solution'
FROM dual;
1. Display employee number, employee full name, phone, extension, and city for employees who work in NYC, London, and Paris. Sort the result based on the city and the employee number. (2 Marks)

2. Display employee number, employee full name, phone, extension, city and manager ID for employees who do not report to a manager. (See the following output) Sort the result based on the city and the employee number. (1.5 Marks)

3. Modify the query in Question 2 to display the manager ID and the manager name for the employees returned by the previous query. Sort the result based on the city and the employee number. (2 Marks)

4. Select employee name, email, country, and postal code for the employees whose job title is Sales Rep. Sort the result based on the postal code. (1.5 Marks)


5. Select customer name, customer number, phone, city, state, and postal code for all the customers who have not made the payment. Sort the result based on the customer number. The output should be as follows: (1.5 Marks)

6. Find the maximum value of priceeach for the product with productcode = 'S10_1678'. (1.5 Marks). Your query returns the following output:



Good Luck