| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/JimMartinson/Classes/CST1600/Labs/ |
Upload File : |
Lab 5.5 - Another subquery
Grading Criteria
Demonstrated assignment effectively: 7 points.
There are some things I expect. If they are incorrect you will lose the following:
Work submitted incorrectly: -1 pt. This covers errors such as incorrect files names, incorrect file formats, poor formatting, etc.
Second and subsequent submission of work for grading: -1 pt.
Work submitted late: -1 pt per week.
I reserve the right not to apply the deduction points at my absolute discretion.
-1 Work submitted incorrectly.
Your folder was misnamed `LAB05.5` instead of `Lab05.5`.
Points will be deducted in the future for this type of error.
-7 You zip file was empty.
-0.7 Work submitted late.
# RepresentedByFoonYue.sql
SELECT customerNumber
, customerName
, contactFirstName
, contactLastName
, phone
FROM `customers`
WHERE salesRepEmployeeNumber = (
SELECT employeeNumber
FROM `employees`
WHERE lastName = 'Tseng'
AND firstName ='Foon Yue'
)
ORDER BY customerName
;
# RepresentedByFoonYue.sql:
-2 While your join query works, the assignment requires you to use a subquery.
-1 Step 2.1; The SELECT field list is not in the correct order.
-3 Step 2.1; You do not want the customerNumber field in your primary query WHERE clause.
You want who the sales rep is for the company (salesRepEmployeeNumber).
-2 Step 2.1; Your subquery is not specific enough.
You were just lucky that there is only one employee with a last name of Tseng.
-1 Step 2.1; The SELECT field list is incorrect.
You added a field to the select list required.
-3 Step 2.1; Your WHERE phrase does not have a condition.
You are getting results for more than Foon Yue Tseng.
You need to test salesRepEmployeeNumber against your subquery.
-2 Step 2.2; You did not order the result by customerName.
You ordered it in reverse order.
-1 Step 2.2; You did not order the result by customerName.
The query is correct, but your screenshot shows the result ordered by customerNumber.
-3 Step 2.3; You may NOT use Foon Yue Tseng's employeeNumber (1286) directly in the subquery.
Try selecting his employeeNumber using his name in a subquery.
-1 Step 2.6; The screenshot is not of the MySQL Workbench window, just part of it.
-1 Step 2.7; The screenshot you turned in is not the result of this query.
+1 You turned in the correct screenshot with lab 5 so I will take it.
# Working query without the use of a subquery.
SELECT customerNumber
, customerName
, contactFirstName
, contactLastName
, phone
FROM customers c
INNER JOIN employees e ON c.salesRepEmployeeNumber= e.employeeNumber
WHERE lastName = 'Tseng' AND firstName = 'Foon Yue'
ORDER BY customerName
;