数据库系统实验二

数据库系统实验二

EXERCISES 2 : JOINS

1. Find the name and salary of employees in Luton.

   SELECT ENAME, SAL
   FROM EMP
   INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
   WHERE DEPT.LOC = 'LUTON';
   

2. Join the DEPT table to the EMP table and show in department number order.

   SELECT d.DNAME, e.ENAME, e.JOB, e.SAL
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   ORDER BY d.DEPTNO;
   

3. List the names of all salesmen who work in SALES.

   SELECT ENAME
   FROM EMP
   WHERE DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME = 'SALES');
   

4. List all departments that do not have any employees.

   SELECT DEPTNO, DNAME
   FROM DEPT
   WHERE DEPTNO NOT IN (SELECT DISTINCT DEPTNO FROM EMP);
   

5. 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 e.ENAME AS Employee_Name, e.SAL AS Employee_Salary, m.ENAME AS Manager_Name, m.SAL AS Manager_Salary
   FROM EMP e
   INNER JOIN EMP m ON e.MGR = m.EMPNO
   WHERE e.SAL > m.SAL;
   

6. List the employees who have BLAKE as their manager.

   SELECT ENAME
   FROM EMP
   WHERE MGR = (SELECT EMPNO FROM EMP WHERE ENAME = 'BLAKE');
   

7. List all the employee Name and his Manager’s name, even if that employee doesn’t have a manager.

   SELECT e.ENAME AS Employee_Name, IFNULL(m.ENAME, 'No Manager') AS Manager_Name
   FROM EMP e
   LEFT JOIN EMP m ON e.MGR = m.EMPNO;
   

EXERCISES 3 : FUNCTIONS

1. Find how many employees have a title of manager without listing them.

   SELECT COUNT(*)
   FROM EMP
   WHERE MGR IS NULL;
   

2. Compute the average annual salary plus commission for all salesmen.

   SELECT AVG(SAL + IFNULL(COMM, 0)) AS Avg_Annual_Salary_Commission
   FROM EMP
   WHERE JOB = 'SALESMAN';
   

3. Find the highest and lowest salaries and the difference between them. (single SELECT statement)

   SELECT MAX(SAL) AS highest_salary, MIN(SAL) AS lowest_salary, MAX(SAL) - MIN(SAL) AS salary_difference
   FROM EMP;
   

4. Find the number of characters in the longest department name.

   SELECT MAX(LENGTH(DNAME)) AS longest_name_length
   FROM DEPT;
   

5. Count the number of people in department 30 who receive a salary and the number of people who receive a commission.(single statement)

   SELECT
     COUNT(CASE WHEN SAL IS NOT NULL THEN EMPNO END) AS with_salary,
     COUNT(CASE WHEN COMM IS NOT NULL THEN EMPNO END) AS with_commission
   FROM EMP
   WHERE DEPTNO = 30;
   

6. 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
     AVG(COMM) AS avg_comm_with_comm,
     AVG(COALESCE(COMM, 0)) AS avg_comm_all
   FROM EMP;
   

7. 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
     AVG(SAL) AS avg_salary,
     AVG(COMM) AS avg_comm,
     AVG(IF(COMM IS NOT NULL, SAL + COMM, 0)) AS avg_salary_plus_comm_with_comm,
     AVG(SAL + COALESCE(COMM, 0)) AS avg_salary_plus_comm_all
   FROM EMP;
   

8. 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 ENAME,
   	ROUND(SAL / 22, 2) AS daily_salary,
     ROUND((SAL / 22) / 8, 2) AS hourly_salary
   FROM EMP
   WHERE DEPTNO = 30;
   

9. Issue the same query as the previous one except that this time truncate (TRUNC) to the nearest penny rather than round.

   SELECT ENAME,
          TRUNCATE(SAL / 22, 2) AS daily_salary,
          TRUNCATE((SAL / 22) / 8, 2) AS hourly_salary
   FROM EMP
   WHERE DEPTNO = 30;
   

EXERCISES 4 : DATES

1. Select the name, job, and date of hire of the employees in department 20. (Format the hiredate column using a picture MM/DD/YY)

   SELECT ENAME, JOB, DATE_FORMAT(HIREDATE, '%m/%d/%y') AS hire_date
   FROM EMP
   WHERE DEPTNO = 20;
   

2. Use a picture to format hiredate as DAY(day of the week), MONTH (name of the month), DD (day of the month) and YYYY(year).

   SELECT ENAME,
   	CONCAT(DAYNAME(HIREDATE), ', ', MONTHNAME(HIREDATE), ' ', DAY(HIREDATE), ', ', YEAR(HIREDATE)) AS formatted_hire_date
   FROM EMP;
   

3. Which employees were hired in March?

   SELECT ENAME, HIREDATE
   FROM EMP
   WHERE MONTH(HIREDATE) = 3;
   

4. Which employees were hired on a Tuesday?

   SELECT ENAME, HIREDATE
   FROM EMP
   WHERE DAYNAME(HIREDATE) = 'TUESDAY';
   

5. Are there any employees who have worked more than 16 years for the company?

   SELECT EXISTS(
     SELECT 1
     FROM EMP
     WHERE YEAR(CURDATE()) - YEAR(HIREDATE) > 16
   );
   

6. Show the weekday of the first day of the month in which each employee was hired. (plus their names)

   SELECT ENAME, DAYNAME(CONCAT(YEAR(HIREDATE), '-', MONTH(HIREDATE), '-01')) AS weekday_of_hire
   FROM EMP;
   

7. Show details of employee hiredates and the date of their first payday. (Paydays occur on the last Friday of each month) (plus their names) (need to create User Defined Function)

   DELIMITER //
   
   CREATE FUNCTION FirstPayday(hire_date DATE)
   RETURNS DATE
   DETERMINISTIC
   NO SQL
   BEGIN
     DECLARE first_payday DATE;
     DECLARE last_day_of_month DATE;
     DECLARE last_friday DATE;
   
     SET last_day_of_month = LAST_DAY(hire_date);
     SET last_friday = last_day_of_month - INTERVAL((WEEKDAY(last_day_of_month) + 3) % 7) DAY;
     SET first_payday = last_friday;
   
     RETURN first_payday;
   END//
   
   DELIMITER ;
   
   SELECT ENAME, HIREDATE, FirstPayday(HIREDATE) AS First_Payday
   FROM EMP;
   

8. Refine your answer to 7 such that it works even if an employee is hired after the last Friday of the month.(cf Martin)

   DELIMITER //
   
   CREATE FUNCTION FirstPayday(hire_date DATE)
   RETURNS DATE
   DETERMINISTIC
   NO SQL
   BEGIN
     DECLARE first_payday DATE;
     DECLARE last_day_of_month DATE;
     DECLARE last_friday DATE;
   
     SET last_day_of_month = LAST_DAY(hire_date);
     SET last_friday = last_day_of_month - INTERVAL((WEEKDAY(last_day_of_month) + 3) % 7) DAY;
   
     IF DAY(hire_date) < DAY(last_friday) THEN
       SET first_payday = last_friday;
     ELSE
       SET last_day_of_month = LAST_DAY(last_day_of_month + INTERVAL 1 MONTH);
       SET last_friday = last_day_of_month - INTERVAL((WEEKDAY(last_day_of_month) + 3) % 7) DAY;
       SET first_payday = last_friday;
     END IF;
   
     RETURN first_payday;
   END//
   
   DELIMITER ;
   
   SELECT ENAME, HIREDATE, FirstPayday(HIREDATE) AS First_Payday
   FROM EMP;
   

EXERCISES 5 : GROUP BY & HAVING

1. List the department number and average salary of each department.

   SELECT d.DEPTNO, AVG(e.SAL) AS avg_salary
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   GROUP BY d.DEPTNO;
   

2. Divide all employees into groups by department and by job within department. Count the employees in each group and compute each group’s average annual salary.

   SELECT d.DEPTNO, e.JOB, AVG(e.SAL) AS avg_salary
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   GROUP BY d.DEPTNO, e.JOB;
   

3. Issue the same query as above except list the department name rather than the department number.

   SELECT d.DNAME AS department,e.JOB,AVG(e.SAL) AS avg_salary
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   GROUP BY d.DNAME, e.JOB;
   

4. List the average annual salary for all job groups having more than 2 employees in the group.

   SELECT e.JOB, AVG(e.SAL) AS avg_salary
   FROM EMP e
   GROUP BY e.JOB
   HAVING COUNT(*) > 2;
   

5. Find all departments with an average commission greater than 25% of average salary.

   SELECT d.DEPTNO
   FROM DEPT d
   INNER JOIN EMP e ON d.DEPTNO = e.DEPTNO
   GROUP BY d.DEPTNO
   HAVING AVG(e.COMM) > (AVG(e.SAL) * 0.25);
   

6. Find each department’s average annual salary for all its employees except the managers and the president.

   SELECT d.DEPTNO, AVG(e.SAL) AS avg_salary
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   WHERE e.JOB NOT IN ('MANAGER', 'PRESIDENT')
   GROUP BY d.DEPTNO;
   

7. List the Department ID and Name where there are at least one Manager and two clerk and whose average salary is greater that the company’s average salary.

   SELECT d.DEPTNO, d.DNAME
   FROM DEPT d
   INNER JOIN EMP e ON d.DEPTNO = e.DEPTNO
   WHERE e.JOB = 'MANAGER'
   GROUP BY d.DEPTNO
   HAVING COUNT(CASE WHEN e.JOB = 'CLERK' THEN e.EMPNO END) >= 2 AND AVG(e.SAL) > (SELECT AVG(SAL) AS company_avg_salary FROM EMP);
   

8. List the name of the Manager who manages most employee.

   SELECT e.ENAME AS manager_name
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   WHERE e.JOB = 'MANAGER'
   GROUP BY e.ENAME
   ORDER BY COUNT(*) DESC
   LIMIT 1;
   

9. List the name of all the Manager who manages at least 2 employees

   SELECT e.ENAME AS manager_name
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   WHERE e.JOB = 'MANAGER'
   GROUP BY e.ENAME
   HAVING COUNT(DISTINCT e.EMPNO) >= 2;
   

EXERCISES 6 : SUB QUERIES

1. List the name and job of employees who have the same job as Jones.

   SELECT e.ENAME AS name, e.JOB AS job
   FROM EMP e
   WHERE e.JOB = (
     SELECT JOB
     FROM EMP
     WHERE ENAME = 'Jones'
   );
   

2. Find all the employees in Department 10 that have a job that is the same as anyone in department 30.

   SELECT e1.ENAME AS name, e1.JOB AS job
   FROM EMP e1
   WHERE e1.DEPTNO = 10
     AND EXISTS (
       SELECT 1
       FROM EMP e2
       WHERE e2.DEPTNO = 30
         AND e1.JOB = e2.JOB
     );
   

3. List the name, job, and department of employees who have the same job as Jones or a salary greater than or equal to Ford.

   SELECT e.ENAME AS name, e.JOB AS job, d.DNAME AS department
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   WHERE (e.JOB = (SELECT JOB FROM EMP WHERE ENAME = 'Jones') OR e.SAL >= (SELECT SAL FROM EMP WHERE ENAME = 'Ford'));
   

4. Find all employees in department 10 that have a job that is the same as anyone in the Sales department.

   SELECT e1.ENAME AS name, e1.JOB AS job
   FROM EMP e1
   INNER JOIN DEPT d1 ON e1.DEPTNO = d1.DEPTNO
   WHERE e1.DEPTNO = 10
     AND EXISTS (
       SELECT 1
       FROM EMP e2
       INNER JOIN DEPT d2 ON e2.DEPTNO = d2.DEPTNO
       WHERE d2.DNAME = 'Sales'
         AND e1.JOB = e2.JOB
     );
   

5. Find the employees located in Liverpool who have the same job as Allen. Return the results in alphabetical order by employee name.

   SELECT e.ENAME AS name, e.JOB AS job
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   WHERE e.JOB = (
     SELECT JOB
     FROM EMP
     WHERE ENAME = 'Allen'
   )
   AND d.LOC = 'Liverpool'
   ORDER BY e.ENAME;
   

6. Find all the employees that earn more than the average salary of employees in their department.

   SELECT e.ENAME AS name, e.JOB AS job, e.SAL AS salary
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   WHERE e.SAL > (
     SELECT AVG(SAL) AS avg_dept_salary
     FROM EMP
     WHERE e.DEPTNO = d.DEPTNO
     GROUP BY d.DEPTNO
   );
   

7. Find all the employees that earn more than JONES, using temporary labels to abbreviate table names.

   SELECT e.ENAME AS name, e.JOB AS job, e.SAL AS salary
   FROM EMP AS e
   INNER JOIN DEPT AS d ON e.DEPTNO = d.DEPTNO
   WHERE e.SAL > (
     SELECT SAL
     FROM EMP AS J
     WHERE J.ENAME = 'JONES'
   );
   

8. List the Name of all employees who earn Highest salary and Second Highest salary.

   SELECT e.ENAME AS name
   FROM EMP e
   ORDER BY e.SAL DESC
   LIMIT 2;
   

EXERCISES 7 : Data Manipulation

1. Create a new table called loans with columns named LNO NUMBER (3), EMPNO NUMBER (4), TYPE CHAR(1), AMNT NUMBER (8,2), Create all constraints, such as Primary Key, Foreign Key, Check

   CREATE TABLE loans (
     LNO MEDIUMINT PRIMARY KEY,
     EMPNO INT NOT NULL,
     TYPE CHAR(1) CHECK (TYPE IN ('C', 'M', 'H')),
     AMNT DECIMAL(8,2) CHECK (AMNT > 0),
     FOREIGN KEY (EMPNO) REFERENCES emp(EMPNO)
   );
   

2. Insert the following data

   LNO	EMPNO	TYPE	AMNT
   23	7499	M	20000.00
   42	7499	C	2000.00
   65	7844	M	3564.00

   INSERT INTO loans (LNO, EMPNO, TYPE, AMNT)
   VALUES (23, 7499, 'M', 20000.00),
          (42, 7499, 'C', 2000.00),
          (65, 7844, 'M', 3564.00);
   

3. Check that you have created 3 new records in Loans.

   SELECT * FROM loans;
   

4. The Loans table must be altered to include another column OUTST NUMBER(8,2).

   ALTER TABLE loans ADD (OUTST DECIMAL(8,2));
   

5. Add 10% interest to all M type loans.

   UPDATE loans
   SET OUTST = AMNT * 1.1
   WHERE TYPE = 'M';
   
   UPDATE loans
   SET OUTST = AMNT
   WHERE TYPE != 'M';
   

6. Remove all loans less than £3000.00.

   DELETE FROM loans
   WHERE OUTST < 3000;
   

7. Change the name of loans table to accounts.

   RENAME TABLE loans TO accounts;
   

8. Change the name of column LNO to LOANNO.

   ALTER TABLE accounts RENAME COLUMN LNO TO LOANNO;
   

9. Create a view for use by personnel in department 30 showing employee name, number, job and hiredate.

   CREATE VIEW dept30_emp_details AS
   SELECT e.ENAME AS name, e.EMPNO AS emp_no, e.JOB, e.HIREDATE
   FROM EMP e
   INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
   WHERE d.DEPTNO = 30;
   

10. Use the view to show employees in department 30 having jobs which are not salesman.

    SELECT *
    FROM dept30_emp_details
    WHERE JOB <> 'Salesman';
    

11. Create a view which shows summary information for each department.

    CREATE VIEW dept_summary AS
    SELECT d.DNAME AS department,
           COUNT(*) AS total_employees,
           AVG(e.SAL) AS avg_salary
    FROM EMP e
    INNER JOIN DEPT d ON e.DEPTNO = d.DEPTNO
    GROUP BY d.DNAME;