SQL Exercises with Sample Tables and Demo Data

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.

SQL Exercises

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 purpose.

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 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 the 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 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';

Please note that we’ll be adding more and more SQL queries to this post based on your feedback. So, please do share your questions with us.