Complex Queries using MYSQL00123211.pptx

SQL Lecture1

1. SQL Exercises – Complex Queries Sample Tables and Insert Demo Data

2. Learn Structured Query Language

3. It is better to create the test data in a dedicated and separate database. Hence, let’s first create a database for our testing purposes. CREATE database SQLTest ; USE SQLTest ;

4. It is better to create the test data in a dedicated and separate database. Hence, let’s first create a database for our testing purposes. CREATE TABLE DEPARTMENT ( DEPTCODE INT(10), DeptName CHAR(30), LOCATION VARCHAR(33) ); CREATE TABLE EMPLOYEE ( EmpCode INT(4), EmpFName VARCHAR(15), EmpLName VARCHAR(15), Job VARCHAR(45), Manager CHAR(4), HireDate DATE, Salary INT(6), Commission INT(6), DEPTCODE INT(2) );

5. We have now created the desired SQL tables. Next, you should run the below commands to change the table structure. It is sometimes quite useful that you know how to alter existing table properties. ALTER TABLE DEPARTMENT ADD PRIMARY KEY (DEPTCODE); ALTER TABLE DEPARTMENT CHANGE COLUMN DEPTCODE DEPTCODE INT(10) NOT NULL; ALTER TABLE DEPARTMENT CHANGE COLUMN DeptName DeptName CHAR(30) UNIQUE; ALTER TABLE DEPARTMENT CHANGE COLUMN LOCATION LOCATION VARCHAR(33) NOT NULL; ALTER TABLE DEPARTMENT CHANGE COLUMN DeptName DeptName VARCHAR(15) UNIQUE;

6. We have now created the desired SQL tables. Next, you should run the below commands to change the table structure. It is sometimes quite useful that you know how to alter existing table properties. ALTER TABLE EMPLOYEE ADD PRIMARY KEY ( EmpCode ); ALTER TABLE EMPLOYEE CHANGE COLUMN EmpCode EmpCode INT(4) NOT NULL; ALTER TABLE EMPLOYEE ADD FOREIGN KEY (DEPTCODE) REFERENCES DEPARTMENT(DEPTCODE); ALTER TABLE EMPLOYEE CHANGE COLUMN Salary Salary DECIMAL(6,2); ALTER TABLE EMPLOYEE ADD COLUMN DOB DATE AFTER EmpLName ; ALTER TABLE EMPLOYEE DROP COLUMN DOB;

7. INSERT statement below will fill the above tables with demo data you can use to run queries. INSERT INTO DEPARTMENT VALUES (10, 'FINANCE', 'EDINBURGH'), (20,'SOFTWARE','PADDINGTON'), (30, 'SALES', 'MAIDSTONE'), (40,'MARKETING', 'DARLINGTON'), (50,'ADMIN', 'BIRMINGHAM’); INSERT INTO EMPLOYEE VALUES (9369, 'TONY', 'STARK', 'SOFTWARE ENGINEER', 7902, '1980-12-17', 2800,0,20), (9499, 'TIM', 'ADOLF', 'SALESMAN', 7698, '1981-02-20', 1600, 300,30), (9566, 'KIM', 'JARVIS', 'MANAGER', 7839, '1981-04-02', 3570,0,20), (9654, 'SAM', 'MILES', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30), (9782, 'KEVIN', 'HILL', 'MANAGER', 7839, '1981-06-09', 2940,0,10), (9788, 'CONNIE', 'SMITH', 'ANALYST', 7566, '1982-12-09', 3000,0,20), (9839, 'ALFRED', 'KINSLEY', 'PRESIDENT', 7566, '1981-11-17', 5000,0, 10), (9844, 'PAUL', 'TIMOTHY', 'SALESMAN', 7698, '1981-09-08', 1500,0,30), (9876, 'JOHN', 'ASGHAR', 'SOFTWARE ENGINEER', 7788, '1983-01-12',3100,0,20), (9900, 'ROSE', 'SUMMERS', 'TECHNICAL LEAD', 7698, '1981-12-03', 2950,0, 20), (9902, 'ANDREW', 'FAULKNER', 'ANAYLYST', 7566, '1981-12-03', 3000,0, 10), (9934, 'KAREN', 'MATTHEWS', 'SOFTWARE ENGINEER', 7782, '1982-01-23', 3300,0,20), (9591, 'WENDY', 'SHAWN', 'SALESMAN', 7698, '1981-02-22', 500,0,30), (9698, 'BELLA', 'SWAN', 'MANAGER', 7839, '1981-05-01', 3420, 0,30), (9777, 'MADII', 'HIMBURY', 'ANALYST', 7839, '1981-05-01', 2000, 200, NULL), (9860, 'ATHENA', 'WILSON', 'ANALYST', 7839, '1992-06-21', 7000, 100, 50), (9861, 'JENNIFER', 'HUETTE', 'ANALYST', 7839, '1996-07-01', 5000, 100, 50);

8. 1. Create a query that displays EMPFNAME, EMPLNAME, DEPTCODE, DEPTNAME, LOCATION from EMPLOYEE, and DEPARTMENT tables. Make sure the results are in ascending order based on the EMPFNAME and LOCATION of the department.

9. 1. Create a query that displays EMPFNAME, EMPLNAME, DEPTCODE, DEPTNAME, LOCATION from EMPLOYEE, and DEPARTMENT tables. Make sure the results are in ascending order based on the EMPFNAME and LOCATION of the department. SELECT E.EMPFNAME, E.EMPLNAME, E.DEPTCODE, D.DEPTNAME, D.LOCATION FROM EMPLOYEE E, DEPARTMENT D WHERE E.DEPTCODE = D.DEPTCODE ORDER BY E.EMPFNAME, D.LOCATION;

10. 2. Display EMPFNAME and “TOTAL SALARY” for each employee

11. 2. Display EMPFNAME and “TOTAL SALARY” for each employee SELECT EMPFNAME, SUM(COMMISSION+SALARY) AS "TOTAL SALARY" FROM EMPLOYEE GROUP BY EMPCODE;

12. 3  Display MAX and 2nd MAX SALARY from the EMPLOYEE table.

13. 3  Display MAX and 2nd MAX SALARY from the EMPLOYEE table. SELECT (SELECT MAX(SALARY) FROM EMPLOYEE) MAXSALARY, (SELECT MAX(SALARY) FROM EMPLOYEE WHERE SALARY NOT IN (SELECT MAX(SALARY) FROM EMPLOYEE )) as 2ND_MAX_SALARY;

14. 4. Display the TOTAL SALARY drawn by an analyst working in dept no 20

15. 4. Display the TOTAL SALARY drawn by an analyst working in dept no 20 SELECT SUM(SALARY+COMMISSION) AS TOTALSALARY FROM EMPLOYEE WHERE JOB = 'ANALYST' AND DEPTCODE = 20;

16. 5. Compute the average, minimum, and maximum salaries of the group of employees having the job of ANALYST.

17. 5. Compute the average, minimum, and maximum salaries of the group of employees having the job of ANALYST. SELECT AVG(Salary) AS AVG_SALARY, MIN(Salary) AS MINSALARY, MAX(Salary) AS MAXSALARY FROM EMPLOYEE WHERE Job = 'ANALYST';

18. Queries useful for a database engineer Query to find all departments that are located in Edinburgh: Here are 5 complex SQL exercises that are important for the database engineer: SELECT * FROM DEPARTMENT WHERE LOCATION = 'EDINBURGH'; Query to find all employees who work in the FINANCE department: SELECT * FROM EMPLOYEE JOIN DEPARTMENT ON EMPLOYEE.DEPTCODE = DEPARTMENT.DEPTCODE WHERE DEPARTMENT.DeptName = 'FINANCE'; This query uses a JOIN clause to combine data from two tables, the EMPLOYEE table and the DEPARTMENT table. The WHERE clause is for filtering the results to only include employees who work in the FINANCE department.

19. Query to find the average salary of employees in each department: SELECT DEPARTMENT.DeptName , AVG( EMPLOYEE.Salary ) AS AVERAGE_SALARY FROM EMPLOYEE JOIN DEPARTMENT ON EMPLOYEE.DEPTCODE = DEPARTMENT.DEPTCODE GROUP BY DEPARTMENT.DeptName ORDER BY AVERAGE_SALARY DESC; This query uses a GROUP BY clause to group the results by department and an AVG() function to calculate the average salary for each department. The ORDER BY clause sorts the results in descending order by average salary. SELECT * FROM EMPLOYEE ORDER BY Salary DESC LIMIT 10; This query uses the ORDER BY clause to sort the results in descending order by salary and the LIMIT clause to limit the results to the top 10 highest-paid employees. Query to find the top 10 highest-paid employees: