MYSQL

MySQL Practical Questions

🔹 DDL (Data Definition Language) – 30 Questions

DDL commands involve creating, modifying, and deleting database structures.

1. Create a database named school_db.
2. Delete a database named test_db.
3. Create a table students with id, name, age, and class.
4. Add a gender column to the students table.
5. Modify the age column to TINYINT(3).
6. Rename the students table to pupils.
7. Drop the gender column from the students table.
8. Set the id column as the primary key in students.
9. Remove the primary key from the students table.
10. Create a unique constraint on the email column.
11. Drop the unique constraint on email.
12. Create a foreign key linking class_id in students to id in classes.
13. Drop the foreign key constraint from students.
14. Create an index on the name column in students.
15. Drop the index from name.
16. Create a composite primary key using id and class_id.
17. Create a table teachers with a teacher_id column as the primary key.
18. Set a default value of 5 for the age column.
19. Alter the default value of age to 6.
20. Remove the default value from age.
21. Create a table users with id as AUTO_INCREMENT.
22. Drop the users table.
23. Create a table with a CHECK constraint to ensure age > 18.
24. Drop the CHECK constraint from the age column.
25. Create a table orders with created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP.
26. Change the column student_name to full_name.
27. Create a view top_students showing students with grades above 90.
28. Drop the view top_students.
29. Clone the structure of the students table into backup_students.
30. Create a temporary table named temp_students.

🔹 DML (Data Manipulation Language) – 30 Questions

DML commands involve inserting, updating, and deleting data.

1. Insert a student into students.
2. Insert multiple students into students.
3. Insert a student without specifying class.
4. Insert a record with a NULL value in class_id.
5. Update the age of a student with id = 1.
6. Increase the age of all students by 1 year.
7. Update students in class_id = 3 to class_id = 4.
8. Delete a student with id = 5.
9. Delete all students where age > 18.
10. Delete all records from students but keep the structure.
11. Restore the deleted records using ROLLBACK.
12. Insert a student with an id that already exists and observe the error.
13. Insert a record violating a NOT NULL constraint and check the error.
14. Use INSERT IGNORE to prevent errors for duplicate keys.
15. Use REPLACE INTO to insert or update a record.
16. Insert a record using DEFAULT values.
17. Use a subquery in an INSERT INTO statement.
18. Use ON DUPLICATE KEY UPDATE to update an existing record.
19. Swap the age of two students.
20. Insert a record into a table with a foreign key constraint.
21. Delete a student and check if foreign key constraints prevent it.
22. Use SET NULL on a foreign key constraint when deleting a parent record.
23. Use CASCADE on a foreign key constraint when deleting a parent record.
24. Restore data using ROLLBACK.
25. Use SAVEPOINT and ROLLBACK TO SAVEPOINT in a transaction.
26. Commit a transaction after inserting multiple records.
27. Demonstrate an AUTO_INCREMENT reset.
28. Insert a record with CURRENT_TIMESTAMP in a created_at column.
29. Update the updated_at column automatically using ON UPDATE CURRENT_TIMESTAMP.
30. Clone a table’s structure and data into a new table.

🔹 LIKE Operator – 10 Questions

1. Retrieve students whose name starts with "A".
2. Retrieve students whose name ends with "n".
3. Retrieve students whose name contains "John".
4. Find students whose name contains "o" in the second position.
5. Retrieve students whose name starts with "J" and is followed by any two characters.
6. Retrieve students whose name has exactly five letters.
7. Retrieve students whose name starts with "S" and ends with "h".
8. Retrieve students whose name does not start with "A".
9. Retrieve students whose name contains "a" or "e".
10. Retrieve students whose name starts with "A" and contains "o" later.

🔹 BETWEEN Operator – 10 Questions

1. Retrieve students aged between 10 and 15.
2. Retrieve students whose class_id is between 3 and 7.
3. Retrieve students enrolled between '2022-01-01' and '2023-01-01'.
4. Retrieve students with a score between 70 and 90.
5. Retrieve students with an ID between 10 and 20.
6. Retrieve students who joined between two specific dates.
7. Retrieve students whose ages are NOT between 12 and 16.
8. Retrieve students whose fees_paid is between 5000 and 10000.
9. Retrieve students whose roll numbers are between 100 and 200.
10. Retrieve students who joined in a particular month and year.

🔹 <, >, OR, AND Operators – 20 Questions

1. Retrieve students whose age is greater than 15.
2. Retrieve students whose age is less than 12.
3. Retrieve students whose fees_paid is greater than 10000.
4. Retrieve students whose marks are less than 35 (failed students).
5. Retrieve students whose marks are greater than 80 and age is less than 18.
6. Retrieve students whose marks are greater than 90 or age is less than 14.
7. Retrieve students whose marks are between 60 and 80 and age is more than 15.
8. Retrieve students whose age is less than 10 or greater than 18.
9. Retrieve students who are in class_id = 5 and have marks greater than 85.
10. Retrieve students in class_id = 7 or class_id = 9.
11. Retrieve students whose fees are more than 5000 but less than 10000.
12. Retrieve students whose marks are greater than 75 and fees are more than 8000.
13. Retrieve students who are either in Class 1 or Class 2.
14. Retrieve students who are NOT in Class 4.
15. Retrieve students whose name starts with "A" and marks are greater than 90.
16. Retrieve students whose marks are less than 50 and fees are more than 7000.
17. Retrieve students who are younger than 12 or older than 17.
18. Retrieve students who belong to Class 3 but do not have any pending fees.
19. Retrieve students who either scored full marks or failed completely.
20. Retrieve students whose roll numbers are even and greater than 50.