INFO20003 Week 5 Lab Solutions Section 2: Beginning SQL
¡ô Task 2.3 Type the SQL to select all data from the employee table. SELECT *
FROM employee;
¡ô Task 2.4 Type an SQL query to select the first name, last name and department
ID for every employee in the employee table. SELECT FirstName, LastName, departmentID
FROM employee;
¡ô Task 2.5 Type a query to return the first and last name, salary and department ID
of all employees who earn exactly $45,000.
SELECT FirstName, LastName, Salary, departmentID FROM employee
WHERE Salary = 45000;
¡ô Task 2.7 Type a query that returns the first and last name and salary of all employees who earn more than $45,000. Order the result from the highest earner to the lowest.
SELECT FirstName, LastName, Salary FROM employee
WHERE Salary > 45000
ORDER BY Salary DESC;
¡ô Task 2.9 Write a query to list the first and last names of the five highest-paid employees.
SELECT FirstName, LastName FROM employee
ORDER BY Salary DESC LIMIT 5;
¡ô Task 2.10 Type a query to find how many employees work in each department. Alias the two columns of the result set to dept and staff_count respectively.
SELECT departmentID AS dept, COUNT(*) AS staff_count FROM employee
GROUP BY departmentID;
INFO20003 Week 5 Lab Solutions 1
¡ô Task 2.11 What is the maximum salary for each department? Use the MAX() aggregate function.
SELECT departmentID, MAX(Salary) AS MAXSAL FROM employee
GROUP BY departmentID;
¡ô Task 2.12 Find the department which has the highest average salary.
SELECT departmentID, AVG(Salary) AS AvgSalary FROM employee
GROUP BY departmentID
ORDER BY AvgSalary DESC
LIMIT 1;
¡ô Task 2.13 Find the department IDs of departments with only one employee.
SELECT departmentID FROM employee
GROUP BY departmentID HAVING COUNT(*) = 1;
¡ô Task 2.14 Find the names of employees who work in the ¡®Marketing¡¯ department.
SELECT FirstName, LastName
FROM employee
WHERE departmentID = (SELECT departmentID
FROM department
WHERE Name = ‘Marketing’);
¡ô Task 2.15 Type the names and salaries of the employees who earn more than any employee in the marketing department.
SELECT FirstName, LastName, Salary FROM employee
WHERE Salary > (SELECT MAX(Salary)
FROM employee NATURAL JOIN department WHERE Name = ‘Marketing’);
Note: Solutions to Tasks 2.16 and 2.17 are provided in the lab sheet.
INFO20003 Week 5 Lab Solutions 2