Purpose of Experiment purpose and Requirements
Answers to the following questions must include:
SQL Query command ( 60 Points)
Screenshot of your SQL command result (30 Points)
Note: Oral Question in LAB ( 10 points)
EXERCISES 2 JOINS
Find the name and salary of employees in Luton.
SELECT ENAME, SAL FROM emp2019284073 e JOIN dept2019284073 d ON e.DEPTNO = d.DEPTNO WHERE LOC = 'LUTON'
Join the DEPT table to the EMP table and show in department number order.
SELECT * FROM emp2019284073 e JOIN dept2019284073 d ON e.DEPTNO = d.DEPTNO ORDER BY e.DEPTNO
- List the names of all salesmen who work in SALES
SELECT ENAME FROM emp2019284073 e JOIN dept2019284073 d ON e.DEPTNO = d.DEPTNO WHERE JOB = 'SALESMAN' AND DNAME = 'SALES'
- List all departments that do not have any employees.
SELECT dept2019284073.DEPTNO FROM dept2019284073 WHERE dept2019284073.DEPTNO NOT IN (SELECT dept2019284073.DEPTNO FROM dept2019284073 JOIN emp2019284073 ON dept2019284073.DEPTNO = emp2019284073.DEPTNO)
For each employee whose salary exceeds his manager’s salary, list the employee’s name and salary and the manager’s name and salary.
SELECT worker.ENAME, worker.SAL, manager.ENAME, manager.SAL FROM emp2019284073 worker JOIN emp2019284073 manager ON worker.MGR = manager.EMPNO WHERE worker.SAL > manager.SAL;
List the employees who have BLAKE as their manager.
SELECT worker.ENAME, manager.ENAME FROM emp2019284073 worker, emp2019284073 manager WHERE worker.MGR = manager.EMPNO AND manager.ENAME = 'BLAKE';
- List all the employee Name and his Manager’s name, even if that employee doesn’t have a manager.
SELECT worker.ENAME AS 'workername', manager.ENAME AS 'manager name' FROM emp2019284073 worker LEFT JOIN emp2019284073 manager ON worker.MGR = manager.EMPNO
EXERCISES 3 FUNCTIONS
- Find how many employees have a title of manager without listing the
SELECT COUNT(temp.JOB) FROM (SELECT e.JOB FROM emp2019284073 e WHERE e.JOB = 'MANAGER') temp;
- Compute the average annual salary plus commission for all salesmen.
SELECT AVG(SAL + COMM) FROM emp2019284073
- Find the highest and lowest salaries and the difference between them (single SELECT statement)
SELECT MAX(SAL) AS MAX, MIN(SAL) AS MIN, MAX(SAL) - MIN(SAL) AS DIFFERENCE FROM emp2019284073
- Find the number of characters in the longest department name.
SELECT MAX(length(LOC)) AS MAXLENGTH FROM dept2019284073
- Count the number of people in department 30 who receive a salary and the number of people who receive a commission (single statement).
SELECT * FROM (SELECT COUNT(*) FROM (SELECT e.EMPNO FROM emp2019284073 e WHERE e.DEPTNO = 30 AND e.SAL IS NOT NULL) temp1) tempa, (SELECT COUNT(*) FROM (SELECT e.EMPNO FROM emp2019284073 e WHERE e.SAL IS NOT NULL) temp2) tempb;
List the average commission of employees who receive a commission, and the average commission of all employees (assume employees who do not receive a commission attract zero commission)
SELECT SUM(COMM) / COUNT(*) AS AVERAGE FROM emp2019284073
List the average salary of employees that receive a salary, the average commission of employees that receive a commission, the average salary plus commission of only those employees that receive a commission and the average salary plus commission of all employees including those that do not receive a commission. (single statement)
SELECT * FROM (SELECT AVG(SAL) FROM emp2019284073) temp1, (SELECT AVG(COMM) FROM emp2019284073) temp2, (SELECT AVG(COMM + SAL) FROM emp2019284073 WHERE COMM IS NOT NULL) temp3, (SELECT SUM(COMM + SAL)/COUNT(*) FROM emp2019284073 WHERE COMM IS NOT NULL) temp4;
Compute the daily and hourly salary for employees in department 30, round to the nearest penny. Assume there are 22 working days in a month and 8 working hours in a day.
SELECT * FROM (SELECT ROUND(SUM(SAL) / COUNT(*) / 22) AS 'daily salary' FROM emp2019284073 WHERE DEPTNO = 30) temp1, (SELECT ROUND(SUM(SAL) / COUNT(*) / 22 / 8) AS 'hourly salary' FROM emp2019284073 WHERE DEPTNO = 30) temp2;
