Dear friends, we have again brought you a new set of SQL exercises for practice. This post first provides SQL commands to create the required tables and populate demo data.
After running the below commands, you will get ready to execute SQL queries from the below exercises. You may try and run any other complex query as well. After that, please do share it with us, and we’ll get it listed in this post.
The section below outlines the SQL queries to create test tables and demo data. Execute these commands to proceed.
Sample Tables and Insert Demo Data
Step-1 (Create Table)
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;
Here, you’ll be creating two tables, namely EMPLOYEE and DEPARTMENT. The exercises will revolve around them.
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) );
Step-2 (Alter Table)
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; 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;
Step-3 (Populate Table)
Below 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);
SQL Exercises for Basic to Advanced Queries
#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;
#2 Display EMPFNAME and “TOTAL SALARY” for each employee
SELECT EMPFNAME, SUM(COMMISSION+SALARY) AS "TOTAL SALARY" FROM EMPLOYEE GROUP BY EMPCODE;
#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;
#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;
#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';
5 Queries useful for a database engineer
Here are 5 complex SQL exercises that are important for the database engineer:
a) Query to find all departments that are located in Edinburgh:
SELECT * FROM DEPARTMENT WHERE LOCATION = 'EDINBURGH';
This query uses the WHERE clause to filter the results to only include departments where the CITY column is equal to EDINBURGH.
b) 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.
c) 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.
d) Query to find the top 10 highest-paid employees:
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.
e) Query to find all employees who did not get a promotion in the last year:
SELECT * FROM EMPLOYEE WHERE HireDate < CURRENT_DATE - INTERVAL 1 YEAR AND Commission IS NULL;
This query selects all employees who have been with the company for at least one year and have not received a commission.
4 Queries useful for a DevOps engineer
Here are 4 complex SQL exercises that are important for the DevOps engineer:
a) Query to find all database tables which was not part of the backup during last week:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND LAST_BACKUP_DATE IS NULL OR LAST_BACKUP_DATE < CURRENT_DATE - INTERVAL 1 WEEK;
This query uses the INFORMATION_SCHEMA.TABLES view to get a list of all database tables. The WHERE clause is filtering the results to only include tables that were not in the backup plan for the last week.
b) Query to find all database indexes that have some level of fragmentation:
SELECT INDEX_NAME, FRAGMENTATION_PERCENT FROM sys.dm_db_index_physical_stats WHERE FRAGMENTATION_PERCENT > 5;
This query uses the sys.dm_db_index_physical_stats dynamic management view to get a list of all database indexes and their fragmentation percentage. The WHERE clause filters the results to only include indexes that are more than 5% fragmented.
c) Query to find all database queries that are running for longer than 10 seconds:
SELECT QUERY_TEXT, ELAPSED_TIME FROM sys.dm_exec_query_stats WHERE ELAPSED_TIME > 10000;
This query uses the sys.dm_exec_query_stats dynamic management view to get a list of all database queries that are currently running and their elapsed time. The WHERE clause filters the results to only include queries that are running for longer than 10 seconds.
d) Query to find all database locks with retention time for longer than 1 minute:
SELECT RESOURCE_TYPE, RESOURCE_DESCRIPTION, WAIT_TIME FROM sys.dm_exec_locks WHERE WAIT_TIME > 60000;
This query uses the sys.dm_exec_locks dynamic management view to get a list of all database locks that are currently holding for longer than 1 minute.
5 SQL exercises to make or break the selection of a candidate
Here are 5 SQL exercises that can make or break the selection of a candidate in an interview:
a) Return a list of all employees who are paid above the average salary.
SELECT EmpFName, EmpLName, Salary FROM EMPLOYEE WHERE Salary > (SELECT AVG(Salary) FROM EMPLOYEE);
b) Return a list of all employees who have been with the company for more than 5 years.
SELECT EmpFName, EmpLName, DateDiff(Now(), HireDate) AS YearsOfService FROM EMPLOYEE WHERE YearsOfService > 5;
c) Return a list of all departments, ordered by the number of employees in each department.
SELECT DeptName, COUNT(*) AS NumEmployees FROM EMPLOYEE GROUP BY DeptName ORDER BY NumEmployees DESC;
d) Return a list of all job titles, ordered by the number of employees in each job title.
SELECT Job, COUNT(*) AS NumEmployees FROM EMPLOYEE GROUP BY Job ORDER BY NumEmployees DESC;
e) Return a list of all managers, ordered by the number of employees managed by each manager.
SELECT Manager, COUNT(*) AS NumEmployees FROM EMPLOYEE GROUP BY Manager ORDER BY NumEmployees DESC;
In the world of SQL, mastering complex queries is key. Database experts, DevOps specialists, and QA pros use them to keep data solid, deployments smooth and tests thorough. These SQL skills power top-notch software development.
Please note that we’ll be adding more and more SQL queries for practice to this post based on your feedback. So, please do share your questions with us.