Description

Objectives
The purpose of this lab is to start learning SQL by writing basic SELECT and DML statements involving a single table.
Submission
Your submission will be a single SQL file with the solutions provided (with a .sql file extension). Submission in any other file format will get a 0.
▪ Save your SQL statements as a script named: DBS211_L02_FirstName_LastName.sql
▪ Locate, select, and submit the file to the Lab 02 link.
Using comments to number the question answers, write the SQL code to complete the following tasks.
Example Submission
— ***********************
— Name: Your Name
— ID: #########
— Purpose: Lab 02 DBS211
— ***********************

— Q1 SOLUTION —
SELECT * FROM TABLE;

— Q2 Solution – SELECT * FROM TABLE;

Setup

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

SELECT column1, column2, column3
FROM table_name
WHERE conditions
ORDER BY column1, column2;

To save time, you can write all SQL statements in your SQL developer.

Marking Scheme
Total Questions 8
Mark on each Question 0.5
Total Marks 4

Grade Policy
▪ Questions with errors do not get any marks (they get 0). Execute your .sql file using the “Run Script” button to make sure there are no errors in your file. ▪ You do not receive marks for the missing solutions.

Tasks:
SQL SELECT
Remember to comment the question number for each solution.
1. Display the data for all offices. Display office code, city, state, country and phone for all offices.

2. Display employee number, first name, last name, and extension for all employees whose office code is 1. Sort the result based on the employee number.

3. Display customer number, customer name, contact first name, contact last name and phone for all customers taking into account the below points:
a. The contact’s first and last name should be in a single column in the below format.
lastname, firstname
b. Show customers who are in Canada
c. The column names should look exactly like in the sample shown below
d. Sort the result based on the customer name.

4. Display customer number for customers who have payments. Do not included any repeated values. Sort the result based on the customer number. (Hints: How do you know a customer has made a payment? You will need to access only one table for this query)
Only the first 10 rows of the output result is shown below but the query must return 98 rows.

5. List customer numbers, check number, and amount for customers whose payment amount is not in the range of $1,500 to $120,000. Sort the output by top payment amounts first.

6. The company needs to know the percentage markup for each product sold. Produce a query that outputs the product code, product name, buy price, MSRP in addition to
a. The difference between MSRP and BuyPrice (i.e. MSRP – BuyPrice) called markup
b. The percentage markup (100 * (markup / BuyPrice)) called percmarkup rounded to 1 decimal place.
c. Sort the result according to percmarkup.
d. Show products with percmarkup greater than 140.

7. Display product code (in the format shown in the sample below), product name, and quantity in stock information of all products with the string ‘co’ in their product name. (c and o can be lower or upper case). Sort the result according to quantity in stock.

8. Display customer number, contact first name, contact last name for all customers whose contact first name starts with letter s (both lowercase and uppercase) and includes letter e (both lowercase and uppercase). Sort the result according to customer number.