QUESTIONS: SQL Practice Questions based on based on the provided SQL queries and tables

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

1. What is the purpose of the SET SQL_MODE statement at the beginning of the script?
2. How would you create a new employee record in the employeeinfo table?
3. What is the significance of the AUTO_INCREMENT attribute for the empID column in the employeeinfo table?
4. How would you retrieve all columns of an employee with the empID of 3 from the employeeinfo table?
5. Explain the purpose of the PRIMARY KEY constraint in the employeeposition table.
6. Write an SQL query to retrieve the names of employees whose gender is 'M' and department is 'HR'.
7. How can you find the total quantity of items purchased from the purchases table?
8. What does the COMMIT statement do at the end of the script?

Intermediate

1. Write a query to list the names of employees who hold the position of ‘Manager’.
2. How would you modify the employeeinfo table to add a new column called email with a maximum length of 50 characters?
3. Write a query to retrieve the highest salary from the employeeposition table.
4. How can you join the employeeinfo and employeeposition tables to get a list of employees along with their positions and salaries?
5. Create a query to calculate the total quantity of items purchased by each user.
6. Explain the difference between the INNER JOIN and LEFT JOIN clauses using the employment and employeeinfo tables.

Advanced

1. Write a query to find the average salary of employees who joined the company in January 2022.
2. How would you find the user who made the most purchases based on the purchases table?
3. Create a query to retrieve the names of employees who have changed positions within the company.
4. Write a query to calculate the total quantity of items purchased for each month in 2020.
5. How can you update the endDate of the employment table to be one month later for each employee?
6. Write a query to identify employees who have not made any purchases based on the data provided.