MySQL QUESTIONS

🔹 SECTION A: Table Creation (DDL)

1. Write a command to create a table STUDENT with fields: RollNo (int), Name
(varchar), Class (int), Marks (float).

2. Write SQL to create a table EMPLOYEES with EmpID (int), Name (varchar), Salary
(float), DeptID (int).

3. Create a table PRODUCTS with the following fields: PID, PName, Price, Quantity.

4. Write SQL to create LIBRARY table with BookID, Title, Author, Price, Quantity.

5. How will you create a table TEACHERS with ID, Name, Subject, and PhoneNumber?

6. Create a table HOTELS with HotelID, HotelName, Location, Rating.

7. Create table CUSTOMERS with CustomerID (primary key), Name, Age, Email.

8. Create a table ORDERS with OrderID, CustomerID, ProductID, Quantity.

9. Write SQL to create a table MOVIES with fields: MovieID, Title, Genre, ReleaseYear.

10. Create CARRENTAL table with CarID, Brand, Model, RentPerDay.

11. Write SQL command to add a new column “City” to the table EMPLOYEES.

12. Write SQL to drop the column “Age” from CUSTOMERS table.

13. How do you change the data type of “Salary” to double in EMPLOYEES?

14. Write SQL to rename the table EMPLOYEES to STAFF.

15. How do you set a column “Phone” as NOT NULL in TEACHERS?

16. Create table RESULTS with RollNo, Name, Subject1, Subject2, Subject3, Total, Grade.

17. How do you define PRIMARY KEY while creating a table DEPARTMENT with DeptID?

18. Create a table ACCOUNT with AccountNo, Name, Balance with default balance 0.

19. Write SQL to delete the table TEMP_DATA.

20. Create table SHOPPING with fields: ItemCode, ItemName, Price, Quantity, Discount.

🔹 SECTION B: Data Insertion and Modification (DML)

 21. Write SQL to insert a record into STUDENT table.

22. Insert 5 records into PRODUCTS table.

23. Write SQL to update the price of ‘Pencil’ to 10 in PRODUCTS.

24. Write SQL to update the salary of employee with EmpID 105 to 50000.

25. Write SQL to delete record of employee with EmpID = 104.

26. Delete all records from CUSTOMERS table.

27. Insert a record in HOTELS where HotelID = 101, Name = "Taj", Location = "Delhi",
Rating = 5.

28. Update MOVIES table to change Genre of movie with MovieID 205 to "Drama".

29. Write SQL to increase all salaries by 10% in EMPLOYEES.

30. Insert data into ORDERS with NULL values for Quantity.

31. Update Product Price where ProductID = 105 by 20%.

32. Delete records from PRODUCTS where Price < 50.

33. Change name of customer with ID = 102 to "Rahul Sharma".

34. Write SQL to set all discount values to 5 in SHOPPING.

35. Insert a row in RESULTS with all subjects marks and calculate total manually.

36. Write command to add a constraint CHECK (Marks >= 0) on RESULTS.

37. Insert a row into ACCOUNT where balance is not specified.

38. Write SQL to delete all rows from ORDERS table without deleting the table.

39. Insert 3 movie records in MOVIES table.

40. Update LIBRARY to change Author name to "Ruskin Bond" where Title = "The Blue
Umbrella".

🔹 SECTION C: SELECT Queries – Basic Retrieval

41. Display all records from STUDENT table.

42. Show name and salary from EMPLOYEES table.

43. Select only Name and Class from STUDENT.

44. Show all books with Price > 300 in LIBRARY.

 45. Display all products whose quantity is less than 10.

46. Display movies released after 2010.

47. Select records from CUSTOMERS where Age is more than 30.

48. Show students who scored more than 75 in all subjects.

49. Display all hotels with rating >= 4.

50. Retrieve all orders of customer with ID = 101.

51. Show all teachers who teach "Maths".

52. Display books by author "Chetan Bhagat".

53. Retrieve customer details whose name starts with "A".

54. Display products with price between 50 and 100.

55. Select employees whose name ends with "a".

56. Show students in Class 12.

57. Display distinct subjects from TEACHERS.

58. Show names in ascending order from CUSTOMERS.

59. Display top 3 most expensive products.

60. Show movies of genre "Comedy" released before 2010.

🔹 SECTION D: Aggregate Functions and GROUP BY / HAVING

61. Find the total number of students.

62. Display average salary from EMPLOYEES.

63. Show maximum price from PRODUCTS.

64. Show minimum marks from RESULTS.

65. Count number of books by each author.

66. Find total quantity of each product.

67. Count number of movies in each genre.

68. Show average rating of hotels city-wise.

69. Display total orders by each customer.

70. Show maximum and minimum salary from EMPLOYEES.

 71. Count number of customers older than 40.

72. Display products having quantity more than average quantity.

73. Group students by class and show average marks.

74. Find highest salary in each department.

75. Count teachers in each subject with more than 2 teachers.

🔹 SECTION E: SQL Clauses – WHERE, ORDER BY, LIKE, IN, BETWEEN, etc.

76. Find all employees with salary between 30000 and 50000.

77. Display students in class 11 or 12.

78. Show all books with title starting with 'T'.

79. Select movies not in genre 'Horror'.

80. List customers from cities ‘Delhi’, ‘Mumbai’.

81. Show hotels located in 'Agra' or 'Jaipur'.

82. Display products not having price between 50 and 150.

83. Order students by marks descending.

84. Show books where author name contains ‘Singh’.

85. Find employees whose names start with 'S' and end with 'a'.

86. Show students with total marks > 200 and class = 12.

87. Select teachers with phone number not null.

88. Retrieve top 5 products sorted by quantity descending.

89. Show all accounts with balance = 0.

90. Display movies where release year IN (2012, 2013, 2014).

🔹 SECTION F: Joins, Subqueries, and Advanced

91. Write a query to join ORDERS and CUSTOMERS based on CustomerID.

92. Retrieve all customer names and their orders.

93. Join EMPLOYEES and DEPARTMENT on DeptID.

94. Show products with price more than average price (using subquery).
 95. Find students who scored above class average (using subquery).

96. Display all movies along with their genre name from MOVIES and GENRES tables.

97. Find hotels whose rating is more than average rating in their location.

98. Display product names from PRODUCTS table that were never ordered.

99. Use nested query to find the name of employee with highest salary.

100. Retrieve customers who have placed more than 3 orders

ANSWER KEY
Section A: Table Creation (DDL - CREATE, ALTER, DROP) [20 Questions]
1. Write a SQL statement to create a table STUDENT with fields RollNo,
Name, Class, DOB, Marks.
2. Create a table TEACHER with EmpID, EmpName, Subject, Salary.
3. Create a table LIBRARY with BookID, Title, Author, Price, and Quantity.
4. Create a table CUSTOMER with CustomerID, Name, City, Phone, Balance.

5. Write SQL to create a table EMPLOYEE with constraints: EmpID (Primary

Key), Name (Not Null), Salary (default 5000).
6. Alter the STUDENT table to add a column Gender.
7. Modify the data type of the column Marks from INT to FLOAT in
STUDENT table.
8. Drop the column Phone from the CUSTOMER table.
9. Delete the table TEACHER permanently.

10.Create a table SALES with columns SaleID, Product, Date, Quantity,

Amount.
11.Add a CHECK constraint on Marks to ensure it is between 0 and 100.
12.Add a UNIQUE constraint to the Phone column in CUSTOMER.
13.Alter table EMPLOYEE to change column name from EmpName to
FullName.
14.Add a new column Email to the STUDENT table.
 15.Remove the Email column from the STUDENT table.
16.Create a table VENDOR with VendorID, Name, City, and make VendorID
the primary key.
17.Alter the LIBRARY table to increase the size of Title to VARCHAR(100).

18.Drop the entire SALES table.

19.Add a foreign key ClassID in STUDENT referencing CLASS(ClassID).
20.Create a table EXAM_RESULTS with StudentID, Subject, Marks, Grade.

Section B: Data Insertion (DML - INSERT) [10 Questions]

21.Insert a record into STUDENT table: (1, 'Rahul', '12A', '2006-10-15', 89).
22.Insert multiple rows into LIBRARY in a single SQL statement.
23.Insert data into only Name, City of CUSTOMER table.
24.Insert a new EMPLOYEE without providing salary (check default
constraint).
25.Insert a record with null values in optional columns of TEACHER.

26.Add 5 records to the EXAM_RESULTS table with different subjects.

27.Insert a duplicate phone number in CUSTOMER and observe constraint
violation.
28.Insert a record into SALES with today’s system date.
29.Use INSERT INTO ... SELECT to copy data from one table to another.

30.Insert data with wrong data type and observe what error occurs.

Section C: Data Retrieval (SELECT Queries) [25 Questions]

31.Retrieve all records from STUDENT.
32.Display only Name and Marks from STUDENT.

33.Display all customers from the city 'Delhi'.

 34.Display employees with salary more than 10000.
35.Select books from LIBRARY where quantity < 5.
36.Retrieve sales made after '2023-01-01'.

37.Display names starting with ‘A’ from STUDENT.

38.Display students with Marks between 80 and 100.
39.Sort students by Name in ascending order.
40.Display the top 5 highest marks.
41.Count total number of books in LIBRARY.

42.Find average salary of all employees.

43.Show minimum and maximum marks from EXAM_RESULTS.
44.Group employees by Subject and display average salary.
45.Count how many students are there in each class.

46.Display total amount of sales done for each product.

47.Show students born in the year 2006.
48.Display distinct city names from CUSTOMER.
49.Select records from TEACHER where name ends with ‘n’.
50.Show students whose names contain ‘an’.

51.Select employees not from the city ‘Mumbai’.

52.Show all books priced between 200 and 500.
53.Select students whose marks are NULL.
54.Use aliasing to rename columns in the output of SELECT.
55.Display today’s date using SQL.

Section D: Data Update & Deletion [10 Questions]

56.Update marks of student with RollNo=1 to 95.
 57.Increase salary by 10% for all employees.
58.Update city of CUSTOMER with CustomerID=101 to 'Noida'.
59.Set Marks to NULL for all students of class ‘12A’.

60.Delete a record from TEACHER with EmpID=5.

61.Delete all books where quantity is 0.
62.Delete customers from city 'Agra'.
63.Increase price of all books by 50.
64.Update Grade in EXAM_RESULTS based on Marks using CASE.

65.Delete all records from SALES.

Section E: Conditional and Aggregate Functions [20 Questions]

66.Use SUM() to calculate total marks of all students.

67.Use AVG() to find average price of books.

68.Find number of students who scored above 90.
69.Use COUNT(*) to get total number of sales.
70.Group students by class and show average marks.
71.Use HAVING to filter groups with avg marks > 80.

72.Count how many books each author has in the library.

73.Show maximum salary for each subject from TEACHER.
74.Display number of employees in each city.
75.Find the highest quantity sold in any sale.
76.Display students who scored above average marks.

77.Use ROUND() to round off average marks to 2 decimals.

78.Display class-wise count of students having marks < 40.
79.Count how many customers have phone numbers starting with 9.
 80.Use IN to filter students from class ‘11A’, ‘12B’.
81.Use NOT IN to exclude students from class ‘10C’.
82.Use BETWEEN to find customers with balance between 5000 and 10000.

83.Find number of teachers whose name starts with ‘S’ and salary > 40000.
84.Display count of books grouped by price range (<200, 200-500, >500).
85.Use nested query to find student(s) with highest marks.

Section F: Joins & Subqueries [15 Questions]

86.Write an INNER JOIN between STUDENT and EXAM_RESULTS on RollNo.

87.Join CUSTOMER and SALES on CustomerID.
88.Use LEFT JOIN between TEACHER and EMPLOYEE.
89.Use RIGHT JOIN between LIBRARY and a table ISSUE.

90.Write a FULL JOIN between STUDENT and EXAM_RESULTS.

91.Use a subquery to find the names of students who scored highest in
each subject.
92.Write a subquery to find books priced above average.
93.Use a subquery to find employees working in the same subject as 'Amit'.
94.Show customer details whose balance is equal to max balance using
subquery.

95.Join EXAM_RESULTS and STUDENT and show Name, Marks, and Grade.
96.Show sales records with product name and customer name using join.
97.Use EXISTS to find students who have given at least one exam.
98.Write a correlated subquery to fetch students with marks more than
class average.
99.Write a query to join three tables: STUDENT, EXAM_RESULTS, and CLASS.
100. Use UNION to combine results of students from two different
tables.