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
Copyright By PowCoder代写 加微信 powcoder
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
¡ô 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
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com