QUESTIONS: SQL Practice Questions based on based on the provided SQL queries and tables
4 min readAug 13, 2023
20 SQL questions from beginner to advanced levels.
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
--
-- Table structure for table `employeeinfo`
--
DROP TABLE IF EXISTS `employeeinfo`;
CREATE TABLE IF NOT EXISTS `employeeinfo` (
`empID` int(11) NOT NULL AUTO_INCREMENT,
`empFname` varchar(20) NOT NULL,
`empLname` varchar(20) NOT NULL,
`department` varchar(5) NOT NULL,
`project` char(2) NOT NULL,
`address` varchar(30) NOT NULL,
`dob` date NOT NULL,
`gender` char(1) NOT NULL,
PRIMARY KEY (`empID`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employeeinfo`
--
INSERT INTO `employeeinfo` (`empID`, `empFname`, `empLname`, `department`, `project`, `address`, `dob`, `gender`) VALUES
(1, 'Sanjeewa', 'Mahendara', 'HR', 'P1', 'Colombo 4', '1976-12-01', 'M'),
(2, 'Anjula', 'Maheema', 'Admin', 'P2', 'Dehiwala North', '1968-05-02', 'F'),
(3, 'Rohana', 'Deshapriya', 'Acc', 'P3', 'Makola North', '1980-01-01', 'M'),
(4, 'Sajeevika', 'Kavindi', 'HR', 'P1', 'Colombo 4', '1992-05-02', 'F'),
(5, 'Asanga', 'Kosta', 'Admin', 'P2', 'Dehiwala', '1994-07-03', 'M');
-- --------------------------------------------------------
--
-- Table structure for table `employeeposition`
--
DROP TABLE IF EXISTS `employeeposition`;
CREATE TABLE IF NOT EXISTS `employeeposition` (
`empID` int(11) NOT NULL,
`empPosition` varchar(15) NOT NULL,
`dateOfJoining` date NOT NULL,
`salary` int(11) NOT NULL,
PRIMARY KEY (`empID`,`empPosition`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employeeposition`
--
INSERT INTO `employeeposition` (`empID`, `empPosition`, `dateOfJoining`, `salary`) VALUES
(1, 'Manager', '2022-05-01', 500000),
(2, 'Executive', '2022-05-02', 75000),
(3, 'Manager', '2022-05-01', 90000),
(2, 'Lead', '2022-05-02', 85000),
(1, 'Executive', '2022-05-01', 300000);
-- --------------------------------------------------------
--
-- Table structure for table `employment`
--
DROP TABLE IF EXISTS `employment`;
CREATE TABLE IF NOT EXISTS `employment` (
`empID` varchar(2) NOT NULL,
`startDate` date NOT NULL,
`endDate` date NOT NULL,
PRIMARY KEY (`empID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
--
-- Dumping data for table `employment`
--
INSERT INTO `employment` (`empID`, `startDate`, `endDate`) VALUES
('E1', '2020-01-01', '2020-01-31'),
('E2', '2020-01-16', '2020-01-26'),
('E3', '2020-01-28', '2020-02-06'),
('E4', '2020-02-16', '2020-02-26');
-- --------------------------------------------------------
--
-- Table structure for table `purchases`
--
DROP TABLE IF EXISTS `purchases`;
CREATE TABLE IF NOT EXISTS `purchases` (
`orderID` int(2) NOT NULL AUTO_INCREMENT,
`userID` varchar(3) NOT NULL,
`createdDate` date NOT NULL,
`itemID` varchar(3) NOT NULL,
`quantity` int(3) NOT NULL,
PRIMARY KEY (`orderID`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;
--
-- Dumping data for table `purchases`
--
INSERT INTO `purchases` (`orderID`, `userID`, `createdDate`, `itemID`, `quantity`) VALUES
(1, 'U1', '2020-12-16', 'P1', 2),
(2, 'U2', '2020-12-16', 'P2', 1),
(3, 'U1', '2020-12-16', 'P3', 1),
(4, 'U4', '2020-12-16', 'P4', 4),
(5, 'U2', '2020-12-17', 'P5', 3),
(6, 'U2', '2020-12-17', 'P6', 2),
(7, 'U4', '2020-12-18', 'P7', 1),
(8, 'U3', '2020-12-19', 'P8', 2),
(9, 'U3', '2020-12-19', 'P9', 8);
COMMIT;
Beginner
- What is the purpose of the
SET SQL_MODE
statement at the beginning of the script? - How would you create a new employee record in the
employeeinfo
table? - What is the significance of the
AUTO_INCREMENT
attribute for theempID
column in theemployeeinfo
table? - How would you retrieve all columns of an employee with the
empID
of 3 from theemployeeinfo
table? - Explain the purpose of the
PRIMARY KEY
constraint in theemployeeposition
table. - Write an SQL query to retrieve the names of employees whose
gender
is 'M' anddepartment
is 'HR'. - How can you find the total quantity of items purchased from the
purchases
table? - What does the
COMMIT
statement do at the end of the script?
Intermediate
- Write a query to list the names of employees who hold the position of ‘Manager’.
- How would you modify the
employeeinfo
table to add a new column calledemail
with a maximum length of 50 characters? - Write a query to retrieve the highest salary from the
employeeposition
table. - How can you join the
employeeinfo
andemployeeposition
tables to get a list of employees along with their positions and salaries? - Create a query to calculate the total quantity of items purchased by each user.
- Explain the difference between the
INNER JOIN
andLEFT JOIN
clauses using theemployment
andemployeeinfo
tables.
Advanced
- Write a query to find the average salary of employees who joined the company in January 2022.
- How would you find the user who made the most purchases based on the
purchases
table? - Create a query to retrieve the names of employees who have changed positions within the company.
- Write a query to calculate the total quantity of items purchased for each month in 2020.
- How can you update the
endDate
of theemployment
table to be one month later for each employee? - Write a query to identify employees who have not made any purchases based on the data provided.