Comprehensive Guide to SQL: 200 Essential Questions and Answers for Mastering SQL Queries and Database Management, Including Advanced Techniques and Best Practices

SQL Explanation and 50 SQL Questions

SQL Detailed Explanation

1. SQL Overview

SQL (Structured Query Language) is a standard language used to communicate with relational databases. It allows users to perform various operations such as querying, updating, inserting, and deleting data.

2. Data Definition Language (DDL)

• CREATE TABLE: Defines a new table along with its columns and their data types.

  CREATE TABLE Employees (
      ID INT PRIMARY KEY,
      Name VARCHAR(100),
      Position VARCHAR(50),
      Salary DECIMAL(10, 2)
  );

• ALTER TABLE: Modifies an existing table structure, such as adding or deleting columns.

  ALTER TABLE Employees ADD COLUMN HireDate DATE;

• DROP TABLE: Deletes an entire table and its data.

  DROP TABLE Employees;

3. Data Manipulation Language (DML)

• SELECT: Retrieves data from one or more tables.

  SELECT Name, Position FROM Employees;

• INSERT INTO: Adds new rows to a table.

  INSERT INTO Employees (ID, Name, Position, Salary)
  VALUES (1, 'John Doe', 'Manager', 75000.00);

• UPDATE: Modifies existing data in a table.

  UPDATE Employees SET Salary = 80000.00 WHERE ID = 1;

• DELETE: Removes rows from a table.

  DELETE FROM Employees WHERE ID = 1;

4. Data Control Language (DCL)

• GRANT: Provides specific permissions to users.

  GRANT SELECT, INSERT ON Employees TO User1;

• REVOKE: Removes specific permissions from users.

  REVOKE INSERT ON Employees FROM User1;

5. Transaction Control Language (TCL)

• COMMIT: Saves all changes made during the current transaction.

  COMMIT;

• ROLLBACK: Undoes changes made during the current transaction.

  ROLLBACK;

• SAVEPOINT: Sets a point within a transaction to which you can later roll back.

  SAVEPOINT Savepoint1;

6. SQL Queries and Clauses

• WHERE Clause: Filters records based on a condition.

  SELECT * FROM Employees WHERE Salary > 50000;

• JOIN Operations:
  • INNER JOIN: Retrieves records with matching values in both tables.

    SELECT Employees.Name, Departments.DepartmentName
    FROM Employees
    INNER JOIN Departments ON Employees.DepartmentID = Departments.ID;

  • LEFT JOIN: Retrieves all records from the left table and matched records from the right table.

    SELECT Employees.Name, Departments.DepartmentName
    FROM Employees
    LEFT JOIN Departments ON Employees.DepartmentID = Departments.ID;

  • RIGHT JOIN: Retrieves all records from the right table and matched records from the left table.

    SELECT Employees.Name, Departments.DepartmentName
    FROM Employees
    RIGHT JOIN Departments ON Employees.DepartmentID = Departments.ID;

  • FULL JOIN: Retrieves records when there is a match in either table.

    SELECT Employees.Name, Departments.DepartmentName
    FROM Employees
    FULL JOIN Departments ON Employees.DepartmentID = Departments.ID;

SQL Questions

1. What does SQL stand for?

Answer: Structured Query Language

2. How do you select all columns from a table named 'Employees'?

Answer: SELECT * FROM Employees;

3. What SQL clause is used to filter records?

Answer: WHERE

4. How do you add a new column 'Email' to an existing table 'Customers'?

Answer: ALTER TABLE Customers ADD COLUMN Email VARCHAR(255);

5. How do you remove a table named 'Orders'?

Answer: DROP TABLE Orders;

6. What SQL command is used to update existing records in a table?

Answer: UPDATE

7. How do you delete all records from a table 'Sales' without removing the table itself?

Answer: DELETE FROM Sales;

8. How do you retrieve distinct values from a column 'Category' in the 'Products' table?

Answer: SELECT DISTINCT Category FROM Products;

9. What SQL statement is used to create a new table?

Answer: CREATE TABLE

10. How do you find the total number of rows in the 'Employees' table?

Answer: SELECT COUNT(*) FROM Employees;

11. How do you sort results by the 'Name' column in ascending order?

Answer: SELECT * FROM Employees ORDER BY Name ASC;

12. How do you join two tables 'Customers' and 'Orders' on a common column 'CustomerID'?

Answer: SELECT * FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

13. What is the purpose of the SQL JOIN clause?

Answer: To combine rows from two or more tables based on a related column between them.

14. How do you modify the 'Salary' column in the 'Employees' table to increase all salaries by 10%?

Answer: UPDATE Employees SET Salary = Salary * 1.10;

15. How do you select records where the 'Age' column is greater than 30?

Answer: SELECT * FROM Employees WHERE Age > 30;

16. How do you select records where 'Name' starts with 'A'?

Answer: SELECT * FROM Employees WHERE Name LIKE 'A%';

17. What SQL keyword is used to prevent duplicate records?

Answer: DISTINCT

18. How do you retrieve the maximum value from the 'Price' column in the 'Products' table?

Answer: SELECT MAX(Price) FROM Products;

19. How do you add a new row to the 'Orders' table?

Answer: INSERT INTO Orders (OrderID, ProductID, Quantity) VALUES (1, 101, 5);

20. How do you count the number of unique 'ProductID' values in the 'Orders' table?

Answer: SELECT COUNT(DISTINCT ProductID) FROM Orders;

21. How do you find records where 'Name' is either 'Alice' or 'Bob'?

Answer: SELECT * FROM Employees WHERE Name IN ('Alice', 'Bob');

22. What is the default sorting order in SQL?

Answer: Ascending (ASC)

23. How do you change the 'Price' column type to DECIMAL(10, 2) in the 'Products' table?

Answer: ALTER TABLE Products MODIFY Price DECIMAL(10, 2);

24. What SQL function is used to find the average value of a column?

Answer: AVG()

25. How do you create a view named 'EmployeeView' that shows all columns from the 'Employees' table?

Answer: CREATE VIEW EmployeeView AS SELECT * FROM Employees;

26. How do you drop a view named 'EmployeeView'?

Answer: DROP VIEW EmployeeView;

27. How do you select the first 10 rows from the 'Products' table?

Answer: SELECT * FROM Products LIMIT 10;

28. How do you find the sum of the 'Total' column in the 'Sales' table?

Answer: SELECT SUM(Total) FROM Sales;

29. How do you find the number of rows in the 'Customers' table?

Answer: SELECT COUNT(*) FROM Customers;

30. What is the purpose of the SQL GROUP BY clause?

Answer: To group rows that have the same values into summary rows, like finding the average salary of employees by department.

31. How do you retrieve the records with the highest 'Salary' in the 'Employees' table?

Answer: SELECT * FROM Employees WHERE Salary = (SELECT MAX(Salary) FROM Employees);

32. How do you use the SQL CASE statement?

Answer: The CASE statement is used for conditional logic. Example: SELECT Name, CASE WHEN Salary > 50000 THEN 'High' ELSE 'Low' END AS SalaryLevel FROM Employees;

33. How do you rename a table 'OldTable' to 'NewTable'?

Answer: RENAME TABLE OldTable TO NewTable;

34. How do you find the records where 'Age' is not equal to 25?

Answer: SELECT * FROM Employees WHERE Age != 25;

35. How do you select all columns from 'Orders' table where 'OrderDate' is between '2024-01-01' and '2024-12-31'?

Answer: SELECT * FROM Orders WHERE OrderDate BETWEEN '2024-01-01' AND '2024-12-31';

36. How do you insert multiple rows into the 'Products' table?

Answer: INSERT INTO Products (ProductID, ProductName, Price) VALUES (1, 'Product1', 10.00), (2, 'Product2', 15.00);

37. What is the purpose of the SQL HAVING clause?

Answer: The HAVING clause is used to filter groups based on a specified condition, similar to the WHERE clause but for aggregated data.

38. How do you select the average 'Salary' of employees by 'DepartmentID'?

Answer: SELECT DepartmentID, AVG(Salary) FROM Employees GROUP BY DepartmentID;

39. How do you update a specific row's 'Address' in the 'Customers' table?

Answer: UPDATE Customers SET Address = 'New Address' WHERE CustomerID = 1;

40. How do you select rows where 'Status' is either 'Active' or 'Pending'?

Answer: SELECT * FROM Orders WHERE Status IN ('Active', 'Pending');

41. How do you remove duplicate rows from a table?

Answer: DELETE FROM table_name WHERE rowid NOT IN (SELECT MIN(rowid) FROM table_name GROUP BY column1, column2, ...);

42. How do you find the minimum 'Price' in the 'Products' table?

Answer: SELECT MIN(Price) FROM Products;

43. How do you select the last 5 rows from the 'Sales' table?

Answer: SELECT * FROM Sales ORDER BY SaleDate DESC LIMIT 5;

44. How do you use SQL subqueries?

Answer: Subqueries are used to perform a query within another query. Example: SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

45. How do you combine results from two queries using UNION?

Answer: SELECT Name FROM Employees UNION SELECT Name FROM Customers;

46. How do you select the highest 'OrderAmount' in the 'Orders' table?

Answer: SELECT OrderAmount FROM Orders ORDER BY OrderAmount DESC LIMIT 1;

47. How do you use the SQL BETWEEN operator?

Answer: The BETWEEN operator is used to select values within a given range. Example: SELECT * FROM Products WHERE Price BETWEEN 10 AND 50;

48. How do you get the current date in SQL?

Answer: SELECT CURRENT_DATE;

49. How do you find all columns where 'Category' is not 'Electronics'?

Answer: SELECT * FROM Products WHERE Category != 'Electronics';

50. How do you add a primary key constraint to an existing 'Orders' table?

Answer: ALTER TABLE Orders ADD CONSTRAINT PK_OrderID PRIMARY KEY (OrderID);

51. How do you find the second highest salary from the 'Employees' table?

Answer: SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);

52. How do you list all tables in the current database?

Answer: SHOW TABLES;

53. How do you select rows where 'Department' is not null?

Answer: SELECT * FROM Employees WHERE Department IS NOT NULL;

54. How do you find the number of rows with 'Status' equal to 'Completed'?

Answer: SELECT COUNT(*) FROM Orders WHERE Status = 'Completed';

55. How do you rename a column 'OldName' to 'NewName' in a table 'Products'?

Answer: ALTER TABLE Products RENAME COLUMN OldName TO NewName;

56. How do you calculate the total 'Revenue' from the 'Sales' table?

Answer: SELECT SUM(Revenue) FROM Sales;

57. How do you find the average 'OrderAmount' from the 'Orders' table?

Answer: SELECT AVG(OrderAmount) FROM Orders;

58. How do you create an index on the 'Name' column of the 'Customers' table?

Answer: CREATE INDEX idx_name ON Customers (Name);

59. How do you select rows with 'OrderDate' in the current year?

Answer: SELECT * FROM Orders WHERE YEAR(OrderDate) = YEAR(CURDATE());

60. How do you select the top 5 highest salaries from the 'Employees' table?

Answer: SELECT Salary FROM Employees ORDER BY Salary DESC LIMIT 5;

61. How do you find the total number of distinct 'ProductID's in the 'OrderDetails' table?

Answer: SELECT COUNT(DISTINCT ProductID) FROM OrderDetails;

62. How do you insert data into multiple tables at once?

Answer: SQL does not support multi-table inserts in a single command. You must use separate INSERT statements for each table.

63. How do you create a temporary table named 'TempTable'?

Answer: CREATE TEMPORARY TABLE TempTable (ID INT, Name VARCHAR(50));

64. How do you drop all records from a table 'TempTable' without deleting the table itself?

Answer: DELETE FROM TempTable;

65. How do you select all records from 'Employees' where 'DepartmentID' is not equal to 3?

Answer: SELECT * FROM Employees WHERE DepartmentID != 3;

66. How do you create a unique constraint on the 'Email' column in the 'Users' table?

Answer: ALTER TABLE Users ADD CONSTRAINT unique_email UNIQUE (Email);

67. How do you find the highest 'Price' of products that belong to 'CategoryID' 10?

Answer: SELECT MAX(Price) FROM Products WHERE CategoryID = 10;

68. How do you update the 'Stock' for products in the 'Products' table based on a condition?

Answer: UPDATE Products SET Stock = Stock - 1 WHERE ProductID = 1001;

69. How do you select all columns from 'Orders' where 'OrderAmount' is between 100 and 500?

Answer: SELECT * FROM Orders WHERE OrderAmount BETWEEN 100 AND 500;

70. How do you find the most recent 'OrderDate' from the 'Orders' table?

Answer: SELECT MAX(OrderDate) FROM Orders;

71. How do you count the number of orders made by each customer?

Answer: SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID;

72. How do you list all columns from the 'Employees' table but exclude the 'Salary' column?

Answer: SQL does not support excluding columns directly. You need to specify the columns you want to select, excluding 'Salary'. Example: SELECT ID, Name, Position FROM Employees;

73. How do you find the total 'SalesAmount' for each 'SalesPerson'?

Answer: SELECT SalesPerson, SUM(SalesAmount) FROM Sales GROUP BY SalesPerson;

74. How do you get a list of all databases on the server?

Answer: SHOW DATABASES;

75. How do you drop a column 'Discount' from the 'Orders' table?

Answer: ALTER TABLE Orders DROP COLUMN Discount;

76. How do you select 'EmployeeID' and 'Name' from 'Employees' where 'HireDate' is before '2020-01-01'?

Answer: SELECT EmployeeID, Name FROM Employees WHERE HireDate < '2020-01-01';

77. How do you find the number of records in 'Orders' that have 'Status' as 'Shipped'?

Answer: SELECT COUNT(*) FROM Orders WHERE Status = 'Shipped';

78. How do you create a table 'Students' with columns 'StudentID' and 'StudentName'?

Answer: CREATE TABLE Students (StudentID INT PRIMARY KEY, StudentName VARCHAR(50));

79. How do you find records with 'LastName' starting with 'S'?

Answer: SELECT * FROM Employees WHERE LastName LIKE 'S%';

80. How do you add a new column 'PhoneNumber' to the 'Customers' table?

Answer: ALTER TABLE Customers ADD PhoneNumber VARCHAR(15);

81. How do you delete all records from the 'Logs' table?

Answer: DELETE FROM Logs;

82. How do you find all records where 'City' is not 'New York'?

Answer: SELECT * FROM Customers WHERE City != 'New York';

83. How do you update 'Quantity' in the 'OrderDetails' table for a specific 'OrderID'?

Answer: UPDATE OrderDetails SET Quantity = 10 WHERE OrderID = 1001;

84. How do you select all employees from the 'Employees' table who have not been assigned a 'DepartmentID'?

Answer: SELECT * FROM Employees WHERE DepartmentID IS NULL;

85. How do you find the total number of distinct 'CategoryID' values in the 'Products' table?

Answer: SELECT COUNT(DISTINCT CategoryID) FROM Products;

86. How do you create an 'Employees' table with a 'BirthDate' column of type DATE?

Answer: CREATE TABLE Employees (ID INT PRIMARY KEY, Name VARCHAR(50), BirthDate DATE);

87. How do you select all columns from 'Sales' where 'SaleAmount' is greater than 500?

Answer: SELECT * FROM Sales WHERE SaleAmount > 500;

88. How do you find records where 'DateOfBirth' is in the month of July?

Answer: SELECT * FROM Customers WHERE MONTH(DateOfBirth) = 7;

89. How do you list all unique 'ProductName' values from the 'Products' table?

Answer: SELECT DISTINCT ProductName FROM Products;

90. How do you find the average 'OrderAmount' by 'CustomerID'?

Answer: SELECT CustomerID, AVG(OrderAmount) FROM Orders GROUP BY CustomerID;

91. How do you retrieve the latest 'OrderDate' for each 'CustomerID'?

Answer: SELECT CustomerID, MAX(OrderDate) FROM Orders GROUP BY CustomerID;

92. How do you find all records from 'Products' where 'Price' is greater than 100 but less than 200?

Answer: SELECT * FROM Products WHERE Price > 100 AND Price < 200;

93. How do you select the first 10 records from the 'Employees' table ordered by 'JoinDate'?

Answer: SELECT * FROM Employees ORDER BY JoinDate LIMIT 10;

94. How do you create a new user with read-only access to a database?

Answer: CREATE USER 'username'@'host' IDENTIFIED BY 'password'; GRANT SELECT ON database.* TO 'username'@'host';

95. How do you find the number of orders placed by each customer?

Answer: SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID;

96. How do you retrieve the last 10 records from the 'Sales' table?

Answer: SELECT * FROM Sales ORDER BY SaleDate DESC LIMIT 10;

97. How do you find the total 'Discount' given in the 'Orders' table?

Answer: SELECT SUM(Discount) FROM Orders;

98. How do you get the first 5 records from 'Products' ordered by 'ProductID' in ascending order?

Answer: SELECT * FROM Products ORDER BY ProductID ASC LIMIT 5;

99. How do you find records from 'Employees' where 'Salary' is between 40000 and 80000?

Answer: SELECT * FROM Employees WHERE Salary BETWEEN 40000 AND 80000;

100. How do you select records from 'Orders' where 'OrderDate' is after the last Monday?

Answer: SELECT * FROM Orders WHERE OrderDate > (CURDATE() - INTERVAL (WEEKDAY(CURDATE()) + 1) DAY);

101. How do you select records with a NULL value in the 'Email' column?

Answer: SELECT * FROM Users WHERE Email IS NULL;

102. How do you find all records where 'Price' is not between 20 and 100?

Answer: SELECT * FROM Products WHERE Price NOT BETWEEN 20 AND 100;

103. How do you get the second lowest 'Salary' from the 'Employees' table?

Answer: SELECT MIN(Salary) FROM Employees WHERE Salary > (SELECT MIN(Salary) FROM Employees);

104. How do you list all unique 'DepartmentID' values from the 'Employees' table?

Answer: SELECT DISTINCT DepartmentID FROM Employees;

105. How do you update the 'Status' column to 'Inactive' for all records in 'Orders' where 'OrderDate' is before '2023-01-01'?

Answer: UPDATE Orders SET Status = 'Inactive' WHERE OrderDate < '2023-01-01';

106. How do you delete records from 'Products' where 'Stock' is less than 10?

Answer: DELETE FROM Products WHERE Stock < 10;

107. How do you retrieve the current date and time in SQL?

Answer: SELECT NOW();

108. How do you count the number of employees in each department?

Answer: SELECT DepartmentID, COUNT(*) FROM Employees GROUP BY DepartmentID;

109. How do you select records where 'Name' ends with 'son'?

Answer: SELECT * FROM Employees WHERE Name LIKE '%son';

110. How do you add a foreign key constraint to the 'Orders' table referencing 'Customers' table?

Answer: ALTER TABLE Orders ADD CONSTRAINT fk_customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

111. How do you find the highest 'SaleAmount' for each 'SalesPerson'?

Answer: SELECT SalesPerson, MAX(SaleAmount) FROM Sales GROUP BY SalesPerson;

112. How do you retrieve all 'EmployeeID' and 'Name' from 'Employees' where 'Position' is either 'Manager' or 'Assistant'?

Answer: SELECT EmployeeID, Name FROM Employees WHERE Position IN ('Manager', 'Assistant');

113. How do you select the middle record from the 'Products' table?

Answer: SELECT * FROM Products ORDER BY ProductID LIMIT 1 OFFSET (SELECT FLOOR(COUNT(*) / 2) FROM Products);

114. How do you find the average 'Discount' in the 'Orders' table?

Answer: SELECT AVG(Discount) FROM Orders;

115. How do you add a unique constraint to the 'Username' column in the 'Users' table?

Answer: ALTER TABLE Users ADD CONSTRAINT unique_username UNIQUE (Username);

116. How do you select all columns from 'Orders' where 'OrderAmount' is more than the average 'OrderAmount'?

Answer: SELECT * FROM Orders WHERE OrderAmount > (SELECT AVG(OrderAmount) FROM Orders);

117. How do you find the total 'Revenue' from 'Sales' grouped by 'SalesRegion'?

Answer: SELECT SalesRegion, SUM(Revenue) FROM Sales GROUP BY SalesRegion;

118. How do you find records where 'LastLogin' is in the last 30 days?

Answer: SELECT * FROM Users WHERE LastLogin >= CURDATE() - INTERVAL 30 DAY;

119. How do you find the number of orders placed by each customer, including customers with no orders?

Answer: SELECT Customers.CustomerID, COUNT(Orders.OrderID) FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Customers.CustomerID;

120. How do you select the first 10 records from 'Sales' and order by 'SaleDate' in descending order?

Answer: SELECT * FROM Sales ORDER BY SaleDate DESC LIMIT 10;

121. How do you retrieve the maximum 'Stock' value from the 'Products' table?

Answer: SELECT MAX(Stock) FROM Products;

122. How do you count the number of unique 'CustomerID' values in the 'Orders' table?

Answer: SELECT COUNT(DISTINCT CustomerID) FROM Orders;

123. How do you find all records where 'EmployeeName' starts with 'A' and ends with 'n'?

Answer: SELECT * FROM Employees WHERE EmployeeName LIKE 'A%n';

124. How do you create an 'Orders' table with columns 'OrderID', 'OrderDate', and 'Amount'?

Answer: CREATE TABLE Orders (OrderID INT PRIMARY KEY, OrderDate DATE, Amount DECIMAL(10, 2));

125. How do you list all products where 'Price' is between 50 and 150 and 'Stock' is greater than 20?

Answer: SELECT * FROM Products WHERE Price BETWEEN 50 AND 150 AND Stock > 20;

126. How do you update the 'Email' column for a specific 'UserID'?

Answer: UPDATE Users SET Email = 'newemail@example.com' WHERE UserID = 1;

127. How do you retrieve records from 'Employees' where 'HireDate' is between '2019-01-01' and '2020-12-31'?

Answer: SELECT * FROM Employees WHERE HireDate BETWEEN '2019-01-01' AND '2020-12-31';

128. How do you find the total number of products in the 'Products' table?

Answer: SELECT COUNT(*) FROM Products;

129. How do you create a view named 'EmployeeView' that shows 'EmployeeID' and 'Name' from 'Employees'?

Answer: CREATE VIEW EmployeeView AS SELECT EmployeeID, Name FROM Employees;

130. How do you find the total 'OrderAmount' from 'Orders' where 'OrderDate' is this year?

Answer: SELECT SUM(OrderAmount) FROM Orders WHERE YEAR(OrderDate) = YEAR(CURDATE());

131. How do you find records from 'Customers' where 'LastName' is either 'Smith' or 'Johnson'?

Answer: SELECT * FROM Customers WHERE LastName IN ('Smith', 'Johnson');

132. How do you create a table 'Sales' with 'SaleID', 'SaleDate', and 'SaleAmount' columns, and set 'SaleID' as primary key?

Answer: CREATE TABLE Sales (SaleID INT PRIMARY KEY, SaleDate DATE, SaleAmount DECIMAL(10, 2));

133. How do you find all records where 'Description' contains the word 'urgent'?

Answer: SELECT * FROM Orders WHERE Description LIKE '%urgent%';

134. How do you delete a table named 'TempData'?

Answer: DROP TABLE TempData;

135. How do you find records from 'Orders' where 'Quantity' is greater than the average 'Quantity'?

Answer: SELECT * FROM Orders WHERE Quantity > (SELECT AVG(Quantity) FROM Orders);

136. How do you find the total 'Quantity' for each 'ProductID' in 'OrderDetails'?

Answer: SELECT ProductID, SUM(Quantity) FROM OrderDetails GROUP BY ProductID;

137. How do you list all records from 'Employees' where 'Salary' is higher than 50000, sorted by 'Salary' descending?

Answer: SELECT * FROM Employees WHERE Salary > 50000 ORDER BY Salary DESC;

138. How do you find the count of orders for each product in 'OrderDetails'?

Answer: SELECT ProductID, COUNT(*) FROM OrderDetails GROUP BY ProductID;

139. How do you select all customers who have placed orders but are not in the 'VIP' list?

Answer: SELECT DISTINCT Customers.CustomerID, Customers.CustomerName FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID LEFT JOIN VIPList ON Customers.CustomerID = VIPList.CustomerID WHERE VIPList.CustomerID IS NULL;

140. How do you create a stored procedure that returns the top 5 most expensive products?

Answer: CREATE PROCEDURE GetTop5ExpensiveProducts AS BEGIN SELECT TOP 5 * FROM Products ORDER BY Price DESC; END;

141. How do you find records from 'Orders' where 'OrderAmount' is not null and greater than 100?

Answer: SELECT * FROM Orders WHERE OrderAmount IS NOT NULL AND OrderAmount > 100;

142. How do you list all employees with a salary greater than the average salary?

Answer: SELECT * FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

143. How do you create an index on the 'LastName' column in the 'Employees' table?

Answer: CREATE INDEX idx_lastname ON Employees (LastName);

144. How do you find the maximum 'Price' for each 'CategoryID' in the 'Products' table?

Answer: SELECT CategoryID, MAX(Price) FROM Products GROUP BY CategoryID;

145. How do you retrieve all records from 'Employees' where 'HireDate' is within the last 6 months?

Answer: SELECT * FROM Employees WHERE HireDate >= CURDATE() - INTERVAL 6 MONTH;

146. How do you list all products with 'Price' greater than the average price but less than the highest price?

Answer: SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products) AND Price < (SELECT MAX(Price) FROM Products);

147. How do you count the number of records in the 'Orders' table where 'OrderAmount' is less than 50?

Answer: SELECT COUNT(*) FROM Orders WHERE OrderAmount < 50;

148. How do you retrieve all orders with a 'Discount' greater than 20%?

Answer: SELECT * FROM Orders WHERE Discount > 20;

149. How do you find the number of products in each category, including categories with zero products?

Answer: SELECT Categories.CategoryID, COUNT(Products.ProductID) FROM Categories LEFT JOIN Products ON Categories.CategoryID = Products.CategoryID GROUP BY Categories.CategoryID;

150. How do you find all records in 'Employees' where 'DepartmentID' is not assigned?

Answer: SELECT * FROM Employees WHERE DepartmentID IS NULL;

151. How do you update 'Status' to 'Completed' for all orders with 'OrderDate' older than 1 year?

Answer: UPDATE Orders SET Status = 'Completed' WHERE OrderDate < CURDATE() - INTERVAL 1 YEAR;

152. How do you find the total revenue generated by each salesperson?

Answer: SELECT SalesPersonID, SUM(Revenue) FROM Sales GROUP BY SalesPersonID;

153. How do you retrieve the top 10 highest 'Salary' values from 'Employees'?

Answer: SELECT * FROM Employees ORDER BY Salary DESC LIMIT 10;

154. How do you find all 'OrderID's where the 'OrderAmount' was not greater than 100 and 'Discount' was applied?

Answer: SELECT OrderID FROM Orders WHERE OrderAmount <= 100 AND Discount > 0;

155. How do you create a table with 'ID', 'Name', and 'Age' columns, and set 'ID' as the primary key?

Answer: CREATE TABLE People (ID INT PRIMARY KEY, Name VARCHAR(100), Age INT);

156. How do you find records where 'Email' contains the domain 'example.com'?

Answer: SELECT * FROM Users WHERE Email LIKE '%example.com';

157. How do you list all 'ProductName' values and their corresponding 'Price' from the 'Products' table?

Answer: SELECT ProductName, Price FROM Products;

158. How do you add a new column 'PhoneNumber' to the 'Employees' table?

Answer: ALTER TABLE Employees ADD PhoneNumber VARCHAR(15);

159. How do you list all records from 'Orders' where 'OrderAmount' is equal to 0?

Answer: SELECT * FROM Orders WHERE OrderAmount = 0;

160. How do you find the number of employees in each department?

Answer: SELECT DepartmentID, COUNT(*) FROM Employees GROUP BY DepartmentID;

161. How do you select the records with 'Salary' between 50000 and 100000, ordered by 'Salary' descending?

Answer: SELECT * FROM Employees WHERE Salary BETWEEN 50000 AND 100000 ORDER BY Salary DESC;

162. How do you find the total number of products in the 'Products' table that have 'Stock' greater than 0?

Answer: SELECT COUNT(*) FROM Products WHERE Stock > 0;

163. How do you retrieve the 'OrderID' and 'CustomerID' from 'Orders' where 'OrderAmount' is greater than 100?

Answer: SELECT OrderID, CustomerID FROM Orders WHERE OrderAmount > 100;

164. How do you find the average 'Salary' of employees who work in 'DepartmentID' 10?

Answer: SELECT AVG(Salary) FROM Employees WHERE DepartmentID = 10;

165. How do you update 'Price' by increasing it by 10% for all products in 'CategoryID' 5?

Answer: UPDATE Products SET Price = Price * 1.10 WHERE CategoryID = 5;

166. How do you delete records from 'Employees' where 'HireDate' is more than 10 years ago?

Answer: DELETE FROM Employees WHERE HireDate < CURDATE() - INTERVAL 10 YEAR;

167. How do you find all records from 'Sales' where 'SaleAmount' is less than the average 'SaleAmount'?

Answer: SELECT * FROM Sales WHERE SaleAmount < (SELECT AVG(SaleAmount) FROM Sales);

168. How do you create an index on the 'OrderDate' column in the 'Orders' table?

Answer: CREATE INDEX idx_orderdate ON Orders (OrderDate);

169. How do you find records from 'Customers' where 'RegistrationDate' is within the last 60 days?

Answer: SELECT * FROM Customers WHERE RegistrationDate >= CURDATE() - INTERVAL 60 DAY;

170. How do you retrieve the number of products for each 'CategoryID' where 'Stock' is greater than 10?

Answer: SELECT CategoryID, COUNT(*) FROM Products WHERE Stock > 10 GROUP BY CategoryID;

171. How do you list all employees who have not been assigned to any department?

Answer: SELECT * FROM Employees WHERE DepartmentID IS NULL;

172. How do you select the top 5 most expensive products in each category?

Answer: SELECT ProductName, Price, CategoryID FROM (SELECT ProductName, Price, CategoryID, ROW_NUMBER() OVER (PARTITION BY CategoryID ORDER BY Price DESC) as rn FROM Products) AS ranked WHERE rn <= 5;

173. How do you find all orders that have not been shipped yet?

Answer: SELECT * FROM Orders WHERE ShippedDate IS NULL;

174. How do you count the number of distinct 'ProductID's in 'OrderDetails'?

Answer: SELECT COUNT(DISTINCT ProductID) FROM OrderDetails;

175. How do you retrieve all customers who have placed at least 5 orders?

Answer: SELECT CustomerID FROM Orders GROUP BY CustomerID HAVING COUNT(*) >= 5;

176. How do you find the minimum 'Price' of products in each 'CategoryID'?

Answer: SELECT CategoryID, MIN(Price) FROM Products GROUP BY CategoryID;

177. How do you update all 'Status' to 'Active' where 'LastLogin' is not null?

Answer: UPDATE Users SET Status = 'Active' WHERE LastLogin IS NOT NULL;

178. How do you retrieve the average 'Discount' applied in 'Orders'?

Answer: SELECT AVG(Discount) FROM Orders;

179. How do you create a table 'Suppliers' with columns 'SupplierID', 'SupplierName', and 'ContactNumber'?

Answer: CREATE TABLE Suppliers (SupplierID INT PRIMARY KEY, SupplierName VARCHAR(100), ContactNumber VARCHAR(15));

180. How do you list all records from 'Products' where 'CategoryID' is 1 or 2?

Answer: SELECT * FROM Products WHERE CategoryID IN (1, 2);

181. How do you find all customers who have a balance greater than the average balance?

Answer: SELECT * FROM Customers WHERE Balance > (SELECT AVG(Balance) FROM Customers);

182. How do you retrieve all records from 'Orders' where 'ShippingAddress' is not null?

Answer: SELECT * FROM Orders WHERE ShippingAddress IS NOT NULL;

183. How do you create a stored procedure to delete all records from 'Logs' older than 1 year?

Answer: CREATE PROCEDURE DeleteOldLogs AS BEGIN DELETE FROM Logs WHERE LogDate < CURDATE() - INTERVAL 1 YEAR; END;

184. How do you find the highest 'OrderAmount' for each 'CustomerID' in 'Orders'?

Answer: SELECT CustomerID, MAX(OrderAmount) FROM Orders GROUP BY CustomerID;

185. How do you create a table 'Courses' with 'CourseID', 'CourseName', and 'Credits'?

Answer: CREATE TABLE Courses (CourseID INT PRIMARY KEY, CourseName VARCHAR(100), Credits INT);

186. How do you find all records from 'Products' where 'Price' is below the median price?

Answer: SELECT * FROM Products WHERE Price < (SELECT MEDIAN(Price) FROM Products);

187. How do you find the total 'Revenue' generated by each 'ProductID' in 'Sales'?

Answer: SELECT ProductID, SUM(Revenue) FROM Sales GROUP BY ProductID;

188. How do you create a view 'CustomerView' that shows 'CustomerID', 'CustomerName', and 'Email'?

Answer: CREATE VIEW CustomerView AS SELECT CustomerID, CustomerName, Email FROM Customers;

189. How do you find the total 'OrderAmount' of all orders placed by a specific customer?

Answer: SELECT SUM(OrderAmount) FROM Orders WHERE CustomerID = 1;

190. How do you find all products that have not been sold yet?

Answer: SELECT * FROM Products WHERE ProductID NOT IN (SELECT DISTINCT ProductID FROM OrderDetails);

191. How do you retrieve the number of orders placed each month?

Answer: SELECT MONTH(OrderDate) AS Month, COUNT(*) FROM Orders GROUP BY MONTH(OrderDate);

192. How do you find the top 10 highest 'SaleAmount' values from 'Sales'?

Answer: SELECT * FROM Sales ORDER BY SaleAmount DESC LIMIT 10;

193. How do you update 'Price' by decreasing it by 15% for all products in 'CategoryID' 3?

Answer: UPDATE Products SET Price = Price * 0.85 WHERE CategoryID = 3;

194. How do you find all employees who joined after the company was founded?

Answer: SELECT * FROM Employees WHERE HireDate > (SELECT FoundationDate FROM Company);

195. How do you list all 'CustomerName' values and their corresponding 'TotalOrders' from 'Customers' who have placed more than 10 orders?

Answer: SELECT c.CustomerName, COUNT(o.OrderID) AS TotalOrders FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID GROUP BY c.CustomerName HAVING COUNT(o.OrderID) > 10;

196. How do you find the second highest 'Salary' from 'Employees'?

Answer: SELECT MAX(Salary) FROM Employees WHERE Salary < (SELECT MAX(Salary) FROM Employees);

197. How do you create a table 'Departments' with 'DepartmentID', 'DepartmentName', and 'ManagerID'?

Answer: CREATE TABLE Departments (DepartmentID INT PRIMARY KEY, DepartmentName VARCHAR(100), ManagerID INT);

198. How do you find the total 'Salary' of employees who work in 'DepartmentID' 20?

Answer: SELECT SUM(Salary) FROM Employees WHERE DepartmentID = 20;

199. How do you create an index on the 'Email' column in the 'Users' table?

Answer: CREATE INDEX idx_email ON Users (Email);

200. How do you find the total 'SalesAmount' for each 'ProductID' in 'OrderDetails'?

Answer: SELECT ProductID, SUM(SalesAmount) FROM OrderDetails GROUP BY ProductID;