CS代考 INFO20003 Week 6 Lab

INFO20003 Week 6 Lab
Objectives:
• Learn unary and outer joins in SQL
• Self-test your SQL skills

Copyright By PowCoder代写 加微信 powcoder

Section 1: Continuing SQL
Unary joins
Unary joins are a type of join where the table is joined to itself. Within our Department store schema, employees can be bosses of other employees. Sometimes we will need to find information about each employee’s boss:
emp.FirstName AS employee_first, — employee first name emp.LastName AS employee_last, — employee last name emp.departmentID, — employee department id boss.FirstName AS boss_first, — boss first name boss.LastName AS boss_last — boss last name
FROM employee AS emp INNER JOIN employee AS boss ON emp.BossID = boss.employeeID
— join the boss ID in emp to employee ID in boss
ORDER BY departmentID, employee_last;
Inspect the FROM clause. The employee table is first aliased as emp, then listed again aliased as boss. To the database server this is now effectively two tables: emp and boss. One table list the employees (subordinates), the other table lists the bosses of those employees.
This query only returns sixteen rows. However, we have seventeen employees. does not have a boss, so her record is missing as an employee. We can fix this by using a LEFT JOIN, which is a type of outer join.
INFO20003 Week 6 Lab 1

Outer joins
Outer joins are used when we wish to display all records in one table even if there is no matching row in the other table.
In the above example, the emp copy of the employee table has no boss for – the value for BossID for is NULL. Therefore the join condition ON emp.bossID = boss.employeeID does not have a match. There is no boss with an employeeID of NULL. (Nor can there be, as employeeID is a primary key – primary key columns can never be NULL.) Because there is no match, ’s row is not part of the result set.
An outer join resolves this by returning rows that do not have a match in the Boss table. MySQL Server supports the use of LEFT JOIN and RIGHT JOIN. This query uses LEFT JOIN:
emp.FirstName AS employee_first, emp.LastName AS employee_last, emp.departmentID,
boss.FirstName AS boss_first, boss.LastName AS boss_last
FROM employee AS emp LEFT JOIN employee AS boss ON emp.BossID = boss.employeeID
ORDER BY departmentID, employee_last;
is now listed as an employee with no boss, and NULL is returned as the boss first and last name.
In the above example using a LEFT JOIN, all records on the left table are kept, irrespective of whether a match exists in the right table. Whether to use a LEFT JOIN or a RIGHT JOIN is dependent on which table’s records must be completely preserved. Here is the same query written as a RIGHT JOIN:
emp.FirstName AS employee_first, emp.LastName AS employee_last, emp.departmentID,
boss.FirstName AS boss_first,
INFO20003 Week 6 Lab 2

boss.LastName AS boss_last
FROM employee AS boss RIGHT JOIN employee AS emp
ON emp.BossID = boss.employeeID ORDER BY departmentID, employee_last;
The result set is the same as before.
Practicing unary joins
◆ Task 1.1 Find the names of employees who work in the same department as their boss. Report the full name of the employee, the department, and the boss’s name. Hint: Fill in the gaps in the following query:
SELECT …
FROM employee AS emp INNER JOIN employee AS boss
ON emp.BossID = boss.employeeID WHERE emp.departmentID = …
Your result set should look like this:
◆ Task 1.2 List the IDs of the departments where all the employees earn less than their boss.
SELECT DISTINCT departmentID FROM employee
WHERE departmentID NOT IN
(SELECT emp.departmentID
FROM employee AS emp INNER JOIN employee AS boss
ON emp.BossID = boss.employeeID WHERE emp.Salary >= boss.Salary);
Notice that the inner query uses a unary join to create a result set that lists all departmentIDs where at least one employee earns more than their boss. That is why the condition is NOT IN.
INFO20003 Week 6 Lab 3

◆Task1.3 Typeaquerytofindthename,salary,andboss’snameoftheemployees of department ID 11 who have a salary over $55,000.
Hint: This will require a unary join to find the bosses and employees. You will need to provide a WHERE clause to fulfil the two conditions listed.
Your result set should look like this:
Bringing together the concepts so far
SQL queries are provided for some of the following tasks – try to write the query on your own before trying the suggested query for yourself in Workbench.
◆ Task 1.4 Find the items delivered by at least two suppliers.
SELECT item.Name
FROM item NATURAL JOIN deliveryitem NATURAL JOIN delivery GROUP BY item.Name
HAVING COUNT(DISTINCT SupplierID) >= 2;
COUNT(SupplierID) will count the rows per item for which SupplierID is not NULL. If we want to count the number of different suppliers per item, we need to count the number of distinct SupplierID values.
◆ Task 1.5 Type a query to return the items that have been sold by at least two departments.
INFO20003 Week 6 Lab 4

◆Task1.6 Findthenameofthehighest-paidemployeeintheMarketingdepartment.
SELECT employee.Firstname, employee.Lastname, employee.Salary FROM employee NATURAL JOIN department
WHERE department.Name = ‘Marketing’
AND employee.Salary = (SELECT MAX(salary)
FROM employee NATURAL JOIN department WHERE department.Name = ‘Marketing’);
◆ Task 1.7 Find the names of employees who earn at least 30 per cent less than the average salary.
SELECT firstname, lastname, salary FROM employee
WHERE salary < (SELECT AVG(salary) * 0.7 FROM employee); ◆ Task 1.8 Find the number of employees with a salary under $45,000. SELECT COUNT(*) FROM employee WHERE salary < 45000; ◆ Task 1.9 Find the number of units sold of each item. Hint: The “number of units sold” is the total quantity of that item that has been sold. SELECT item.Name, SUM(saleitem.quantity) AS UnitsSold FROM saleitem NATURAL JOIN item GROUP BY ItemID ORDER BY Name; This query does not return the fact that the Horse Saddle (ItemID 1) has never been sold! As such, to retrieve all of the information, you need to use an outer join: SELECT item.Name, SUM(saleitem.quantity) AS UnitsSold FROM saleitem RIGHT JOIN item ON saleitem.ItemID = item.ItemID GROUP BY item.ItemID ORDER BY item.Name; When solving queries of this type, always consider the possibility that some items may have never been sold, delivered, etc. ◆ Task 1.10 Find any suppliers that have delivered no more than two unique items. List the suppliers in alphabetical order. SELECT supplier.Name FROM delivery INNER JOIN supplier INNER JOIN deliveryItem ON supplier.SupplierID = delivery.SupplierID AND delivery.DeliveryID = deliveryitem.DeliveryID GROUP BY supplier.SupplierID HAVING COUNT(DISTINCT deliveryitem.ItemID) <= 2 ORDER BY supplier.Name; INFO20003 Week 6 Lab 5 ◆ Task 1.11 Find the names of suppliers that have never delivered a compass. SELECT DISTINCT supplier.Name FROM supplier WHERE supplier.SupplierID NOT IN (SELECT SupplierID FROM delivery NATURAL JOIN deliveryitem NATURAL JOIN item WHERE item.Name LIKE 'Compass%'); This question was challenging in that you were not given the complete data. The Item name for the Compass in the database is ‘Compass - Silva’ but you only had part of the information. In this case you cannot get an exact match, so a condition such as = 'Compass' would return no rows. To find rows that match, you must use the LIKE condition and the wildcard %. This is why it is always good to query reference tables like Item to know how the item name is actually stored in the database. Section 2: SQL self-test: single-table queries Note: You will need to read the functions section of the MySQL reference manual to answer some of these questions: https://dev.mysql.com/doc/refman/8.0/en/functions.html ◆ Task 2.1 How many deliveries have there been in the month of July? Hint: The only information you have been given is the month name. ◆ Task 2.2 List the names of the tents available for sale. ◆ Task 2.3 What month has had the highest number of sales? INFO20003 Week 6 Lab 6 ◆ Task 2.4 List the salary total and employee count for each departmentID. Order the results from the smallest salary total to the largest. ◆ Task 2.5 How many sales have been on a Sunday? ◆ Task 2.6 How many days have elapsed between the first delivery date and most recent delivery date for each supplier? ◆ Task 2.7 Produce the following output by writing a SQL statement. INFO20003 Week 6 Lab 7 ◆Task2.8 Findtheminimum,maximum,averageandstandarddeviationforsalaries in each department. ◆ Task 2.9 List the green items of type C. End of Week 6 Lab INFO20003 Week 6 Lab 8 程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com