INFO20003 Week 6 Lab Solutions Section 1: Continuing SQL
¡ô 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.
SELECT CONCAT(emp.FirstName, ‘ ‘, emp.LastName) AS employee_name, emp.departmentID,
CONCAT(boss.FirstName, ‘ ‘, boss.LastName) AS boss_name
FROM employee AS emp INNER JOIN employee AS boss ON emp.BossID = boss.employeeID
WHERE emp.departmentID = boss.departmentID;
¡ôTask1.3 Typeaquerytofindthename,salary,andboss¡¯snameoftheemployees
of department ID 11 who have a salary over $55,000.
SELECT CONCAT(emp.FirstName, ‘ ‘, emp.LastName) AS EmployeeName, emp.salary, CONCAT(boss.FirstName, ‘ ‘, boss.LastName) AS Manager
FROM employee AS emp INNER JOIN employee AS boss ON emp.BossID = boss.employeeID
WHERE emp.salary > 55000 AND emp.departmentid = 11;
¡ô Task 1.5 Type a query to return the items that have been sold by at least two departments.
SELECT item.Name
FROM item NATURAL JOIN saleitem NATURAL JOIN sale GROUP BY item.Name
HAVING COUNT(DISTINCT DepartmentID) >= 2;
Section 2: SQL self-test: single-table queries
¡ô 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.
SELECT COUNT(deliverydate)
FROM delivery
WHERE MONTHNAME(deliverydate) = ‘July’;
¡ô Task 2.2 List the names of the tents available for sale.
SELECT name
FROM item
WHERE name LIKE ‘%tent%’;
INFO20003 Week 6 Lab Solutions 1
¡ô Task 2.3 What month has had the highest number of sales?
SELECT MONTHNAME(saledate) AS month, COUNT(*) AS num_sales FROM sale
GROUP BY MONTHNAME(saledate)
ORDER BY COUNT(*) DESC
LIMIT 1;
¡ô Task 2.4 List the salary total and employee count for each departmentID. Order
the results from the smallest salary total to the largest.
SELECT departmentid, SUM(salary), COUNT(*) FROM employee
GROUP BY departmentid
ORDER BY SUM(salary);
In a ¡°single-table queries¡± self-test, the above answer is acceptable.
¡ô Task 2.5 How many sales have been on a Sunday?
SELECT COUNT(saleid)
FROM sale
WHERE DAYNAME(saledate) = ‘Sunday’;
¡ô Task 2.6 How many days have elapsed between the first delivery date and most recent delivery date for each supplier?
SELECT supplierid,
DATEDIFF(MAX(deliverydate), MIN(deliverydate)) AS datedif
FROM delivery
GROUP BY supplierid;
¡ô Task 2.7 Produce the following output by writing a SQL statement.
SELECT CONCAT(‘The ‘, name, ‘ department is on floor number ‘, floor) AS ‘Where is each department?’
FROM department ORDER BY name;
¡ôTask2.8 Findtheminimum,maximum,averageandstandarddeviationforsalaries in each department.
SELECT departmentid, MIN(salary) AS MIN, MAX(salary) AS MAX, STDDEV(salary) AS STDDEV
FROM employee
GROUP BY departmentid;
¡ô Task 2.9 List the green items of type C.
SELECT ItemID, Name FROM item
WHERE Type = ‘C’
AND Colour = ‘Green’;
INFO20003 Week 6 Lab Solutions 2