Comprehensive MySQL Practice Worksheet for CBSE Class 11th & 12th: Mastering DDL, DML, TCL, and SQL Functions

MySQL is an essential part of the Class 11th and 12th CBSE Computer Science curriculum. Mastering MySQL enables students to manage and interact with databases effectively, a skill highly valued in programming and data management. This worksheet is designed to give CBSE students hands-on practice with MySQL operations, including Data Definition Language (DDL), Data Manipulation Language (DML), Transaction Control Language (TCL), and commonly used SQL Functions. Let’s dive into some key exercises that will help you get familiar with these concepts.

---

Part 1: Data Definition Language (DDL)

DDL commands are used to define or modify the structure of the database and database objects like tables. The most common DDL commands are CREATE, ALTER, DROP, and TRUNCATE.

Exercise 1: Create and Modify Tables

1. Create a table Students with the following structure:

• StudentID (INT, PRIMARY KEY, AUTO_INCREMENT)
• FirstName (VARCHAR(30))
• LastName (VARCHAR(30))
• DOB (DATE)
• Marks (INT) Solution:

   CREATE TABLE Students (
     StudentID INT PRIMARY KEY AUTO_INCREMENT,
     FirstName VARCHAR(30),
     LastName VARCHAR(30),
     DOB DATE,
     Marks INT
   );

2. Alter the table to add a new column Gender (CHAR(1)) to the Students table. Solution:

   ALTER TABLE Students ADD Gender CHAR(1);

3. Drop a column from the table.
   Drop the Marks column from the Students table. Solution:

   ALTER TABLE Students DROP COLUMN Marks;

---

Part 2: Data Manipulation Language (DML)

DML commands deal with manipulating data in the tables. Common commands include INSERT, UPDATE, DELETE, and SELECT.

Exercise 2: Insert, Update, and Delete Data

1. Insert records into the Students table. Insert the following data:

• FirstName: Ravi, LastName: Kumar, DOB: 2004-05-15, Gender: M
• FirstName: Priya, LastName: Sharma, DOB: 2003-08-10, Gender: F Solution:

   INSERT INTO Students (FirstName, LastName, DOB, Gender) 
   VALUES ('Ravi', 'Kumar', '2004-05-15', 'M'), 
          ('Priya', 'Sharma', '2003-08-10', 'F');

2. Update a record: Change Priya’s last name from Sharma to Gupta. Solution:

   UPDATE Students 
   SET LastName = 'Gupta' 
   WHERE FirstName = 'Priya' AND LastName = 'Sharma';

3. Delete a record: Remove Ravi’s record from the Students table. Solution:

   DELETE FROM Students 
   WHERE FirstName = 'Ravi';

---

Part 3: Transaction Control Language (TCL)

TCL commands control the transactions in a database. Common TCL commands include COMMIT, ROLLBACK, and SAVEPOINT.

Exercise 3: Manage Transactions

1. Insert data and use transactions to manage it.
   Insert a new student record (Anil, Verma, 2005-07-20, M), and use TCL commands to handle the transaction. Solution:

   START TRANSACTION;

   INSERT INTO Students (FirstName, LastName, DOB, Gender) 
   VALUES ('Anil', 'Verma', '2005-07-20', 'M');

   -- If satisfied with the result, commit the changes
   COMMIT;

   -- Otherwise, rollback the transaction
   -- ROLLBACK;

2. Set a savepoint during the transaction.
   Insert another record (Nisha, Singh, 2004-09-25, F), but create a savepoint before committing the changes. Solution:

   START TRANSACTION;

   INSERT INTO Students (FirstName, LastName, DOB, Gender) 
   VALUES ('Anil', 'Verma', '2005-07-20', 'M');

   SAVEPOINT savepoint1;

   INSERT INTO Students (FirstName, LastName, DOB, Gender) 
   VALUES ('Nisha', 'Singh', '2004-09-25', 'F');

   -- Rollback to the savepoint if necessary
   -- ROLLBACK TO savepoint1;

   COMMIT;

---

Part 4: SQL Functions

SQL Functions are used to perform operations on the data stored in a database. They can be aggregate functions like SUM(), AVG(), or string functions like UPPER(), LOWER().

Exercise 4: Use SQL Functions

1. Find the average age of all students in the table (assuming the current year is 2024). Solution:

   SELECT AVG(YEAR(CURDATE()) - YEAR(DOB)) AS AverageAge 
   FROM Students;

2. Count the number of students in the table. Solution:

   SELECT COUNT(*) AS TotalStudents 
   FROM Students;

3. Concatenate the first and last names of the students to display their full names. Solution:

   SELECT CONCAT(FirstName, ' ', LastName) AS FullName 
   FROM Students;

---

Additional Practice Questions

1. Create a table Books with columns BookID, Title, Author, Price, and PublishedYear.
2. Insert at least 3 records into the Books table.
3. Update the price of a book using the UPDATE statement.
4. Use SELECT statements with WHERE clauses to filter records (e.g., books priced above 500).
5. Try out TCL commands with a series of inserts and deletions and see how COMMIT and ROLLBACK work in practice.

---

Conclusion

This worksheet provides a comprehensive foundation for CBSE Class 11th and 12th students to understand and practice essential MySQL commands, including DDL, DML, TCL, and SQL functions. Regular practice of these exercises will enhance your ability to manage databases effectively and prepare you for exams and future coursework. Happy learning!