Scribd
Upload
25 views14 pages

Class 12 MySQL Worksheets Answer Key

The document contains answer keys for Class 12 MySQL worksheets covering various SQL commands including database, table, insert, update, delete, and select commands. Each worksheet provides specific SQL queries and explanations related to database management and manipulation. The answers address common SQL operations and concepts, serving as a study guide for students learning MySQL.

Uploaded by

kz7k59gt5p
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
25 views14 pages

Class 12 MySQL Worksheets Answer Key

The document contains answer keys for Class 12 MySQL worksheets covering various SQL commands including database, table, insert, update, delete, and select commands. Each worksheet provides specific SQL queries and explanations related to database management and manipulation. The answers address common SQL operations and concepts, serving as a study guide for students learning MySQL.

Uploaded by

kz7k59gt5p
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Class 12 MySQL Worksheets - Answer Key

Worksheet-1: Database Related Commands

Worksheet-1 (Database Commands)

1. USE Employee;

2. USE LIBRARY;

3. USE <database_name>;

4. SQL: Structured Query Language. MySQL: A database software.

5. Oracle, MySQL

6. USE COMPANY;

7. MySQL is an open-source RDBMS.

8. SQL is a language; MySQL is a software using SQL.

9. MySQL, PostgreSQL

10.

i. SHOW DATABASES;

ii. USE City;

iii. DROP DATABASE Clients;

11. SELECT DATABASE();

12. CREATE DATABASE School;

Worksheet-2: Table Related Commands

Worksheet-2 (Table Related Commands)

1. CREATE TABLE Menu (ItemID INT PRIMARY KEY, ItemName VARCHAR(30), Price DECIMAL(6,2));

2. No, a table can have only one primary key but multiple foreign keys.

3. Roll Number - uniquely identifies each student.

4. DROP TABLE BACKUP;


Class 12 MySQL Worksheets - Answer Key

5. CREATE TABLE STOCK (ItemID INT PRIMARY KEY, ItemName VARCHAR(50) NOT NULL, Quantity INT

CHECK (Quantity >= 0), Price DECIMAL(8,2));

6. CHAR reserves fixed space; VARCHAR uses variable space.

7. DESCRIBE Product;

8. SHOW TABLES;

9. DROP TABLE <table_name>; and DESCRIBE <table_name>;

10. PRIMARY KEY is unique and not null; UNIQUE can be null.

11. A: 4 characters; B: 20 characters

12. i) RollNo, Name; ii) RollNo is primary key - uniquely identifies a student

13. CREATE TABLE Player (PlayerID INT PRIMARY KEY, PlayerName VARCHAR(30), Score INT);

14. i) OrderId: INT, OrderDate: DATE; ii) Depends on constraints

15. CREATE TABLE Event (EventId VARCHAR(5) PRIMARY KEY, EventName VARCHAR(30) NOT NULL,

Location VARCHAR(50), ClientID INT, EventDate DATE);

16. i) EventId - unique id; ii) ClientID - unique constraint candidate

17. Same as Q15

18. iv. SHOW DATABASES; v. USE City; vi. DROP DATABASE Clients; vii. DROP TABLE Club;

19. VARCHAR for EName - it saves space and is more efficient.

Worksheet-3: Alter Table Commands

Worksheet-3 (Alter Table Commands)

1. ALTER TABLE table_name ADD column_name datatype;

2. ALTER TABLE Customer ADD PRIMARY KEY (CustID);

3. ALTER TABLE Hospital ADD Address VARCHAR(100);

4. ALTER TABLE Student DROP COLUMN Hobbies;


Class 12 MySQL Worksheets - Answer Key

5. ALTER TABLE Student ADD Game_Played VARCHAR(30);

6. ALTER TABLE Friends DROP COLUMN Hobbies;

7. ALTER TABLE Student ADD Hobbies VARCHAR(50);

8. ALTER TABLE Employee ADD empid INT PRIMARY KEY;

9. ALTER TABLE Customer DROP PRIMARY KEY;

10. ALTER TABLE employees MODIFY name VARCHAR(50);

Worksheet-4: INSERT INTO Commands

Worksheet-4 (INSERT INTO Commands)

1. NOT NULL constraint

2. INSERT INTO RESULT VALUES (6, 'Mohan', 500, 'English', 73, 'Second');

3. INSERT INTO Shoppe VALUES ('110', 'Pizza', 'Papa Jones', 120, 'Kolkata', 50.0);

4. NULL = unknown; 0 = defined numeric value

5. INSERT INTO GYM VALUES ('G107', 'Vibro exerciser', 21000, 'GTCFitness');

6. NULL means missing/unknown value

7. INSERT INTO STUDENT(RNO, MARKS) VALUES (5, 78.5);

8. INSERT INTO EMP(EMPNO, SALES) VALUES (100, 20078.50);

9. INSERT INTO Emp(LastName) VALUES ('Sharma');

10. Already answered in Worksheet-2 Q14

11. i. DML ii. DDL

12. NULL != 0; NULL means no value

13. i. DDL: Data Definition Language ii. DML: Data Manipulation Language

Worksheet-5: UPDATE and DELETE Commands


Class 12 MySQL Worksheets - Answer Key

Worksheet-5 (UPDATE and DELETE Commands)

1. DROP TABLE deletes structure + data; DELETE removes data only

2. UPDATE Product SET Price = Price + 20;

3. UPDATE Employee SET LastName = 'Singh' WHERE LastName = 'Sharma';

4. UPDATE changes data; ALTER changes structure

5. UPDATE GYM SET Brandname = 'Fit Trend India' WHERE ICODE = 'G101';

6. UPDATE Emp SET Commission = Commission + 100.00;

7. INSERT, UPDATE

8. UPDATE CD SET Singer = 'Sonvi Mehra' WHERE Singer = 'Sonvi Kumar';

9. 1) UPDATE GARMENT SET Colour = 'Orange' WHERE GCode = 116;

2) UPDATE GARMENT SET Price = Price * 1.10 WHERE Size = 'XL';

3) DELETE FROM GARMENT WHERE GCode = 116;

10. UPDATE EMPLOYEE SET JOBID = 104 WHERE ID = 'E4';

11. UPDATE Student SET Marks = 50 WHERE RollNumber = 2;

12. Wrong query. Correct: UPDATE Orders SET salesamount = salesamount + 20;
Class 12 MySQL Worksheets - Answer Key

Worksheet-1: Database Related Commands

Worksheet-1 (Database Commands)

1. USE Employee;

2. USE LIBRARY;

3. USE <database_name>;

4. SQL: Structured Query Language. MySQL: A database software.

5. Oracle, MySQL

6. USE COMPANY;

7. MySQL is an open-source RDBMS.

8. SQL is a language; MySQL is a software using SQL.

9. MySQL, PostgreSQL

10.

i. SHOW DATABASES;

ii. USE City;

iii. DROP DATABASE Clients;

11. SELECT DATABASE();

12. CREATE DATABASE School;

Worksheet-2: Table Related Commands

Worksheet-2 (Table Related Commands)

1. CREATE TABLE Menu (ItemID INT PRIMARY KEY, ItemName VARCHAR(30), Price DECIMAL(6,2));

2. No, a table can have only one primary key but multiple foreign keys.

3. Roll Number - uniquely identifies each student.

4. DROP TABLE BACKUP;


Class 12 MySQL Worksheets - Answer Key

5. CREATE TABLE STOCK (ItemID INT PRIMARY KEY, ItemName VARCHAR(50) NOT NULL, Quantity INT

CHECK (Quantity >= 0), Price DECIMAL(8,2));

6. CHAR reserves fixed space; VARCHAR uses variable space.

7. DESCRIBE Product;

8. SHOW TABLES;

9. DROP TABLE <table_name>; and DESCRIBE <table_name>;

10. PRIMARY KEY is unique and not null; UNIQUE can be null.

11. A: 4 characters; B: 20 characters

12. i) RollNo, Name; ii) RollNo is primary key - uniquely identifies a student

13. CREATE TABLE Player (PlayerID INT PRIMARY KEY, PlayerName VARCHAR(30), Score INT);

14. i) OrderId: INT, OrderDate: DATE; ii) Depends on constraints

15. CREATE TABLE Event (EventId VARCHAR(5) PRIMARY KEY, EventName VARCHAR(30) NOT NULL,

Location VARCHAR(50), ClientID INT, EventDate DATE);

16. i) EventId - unique id; ii) ClientID - unique constraint candidate

17. Same as Q15

18. iv. SHOW DATABASES; v. USE City; vi. DROP DATABASE Clients; vii. DROP TABLE Club;

19. VARCHAR for EName - it saves space and is more efficient.

Worksheet-3: Alter Table Commands

Worksheet-3 (Alter Table Commands)

1. ALTER TABLE table_name ADD column_name datatype;

2. ALTER TABLE Customer ADD PRIMARY KEY (CustID);

3. ALTER TABLE Hospital ADD Address VARCHAR(100);

4. ALTER TABLE Student DROP COLUMN Hobbies;


Class 12 MySQL Worksheets - Answer Key

5. ALTER TABLE Student ADD Game_Played VARCHAR(30);

6. ALTER TABLE Friends DROP COLUMN Hobbies;

7. ALTER TABLE Student ADD Hobbies VARCHAR(50);

8. ALTER TABLE Employee ADD empid INT PRIMARY KEY;

9. ALTER TABLE Customer DROP PRIMARY KEY;

10. ALTER TABLE employees MODIFY name VARCHAR(50);

Worksheet-4: INSERT INTO Commands

Worksheet-4 (INSERT INTO Commands)

1. NOT NULL constraint

2. INSERT INTO RESULT VALUES (6, 'Mohan', 500, 'English', 73, 'Second');

3. INSERT INTO Shoppe VALUES ('110', 'Pizza', 'Papa Jones', 120, 'Kolkata', 50.0);

4. NULL = unknown; 0 = defined numeric value

5. INSERT INTO GYM VALUES ('G107', 'Vibro exerciser', 21000, 'GTCFitness');

6. NULL means missing/unknown value

7. INSERT INTO STUDENT(RNO, MARKS) VALUES (5, 78.5);

8. INSERT INTO EMP(EMPNO, SALES) VALUES (100, 20078.50);

9. INSERT INTO Emp(LastName) VALUES ('Sharma');

10. Already answered in Worksheet-2 Q14

11. i. DML ii. DDL

12. NULL != 0; NULL means no value

13. i. DDL: Data Definition Language ii. DML: Data Manipulation Language

Worksheet-5: UPDATE and DELETE Commands


Class 12 MySQL Worksheets - Answer Key

Worksheet-5 (UPDATE and DELETE Commands)

1. DROP TABLE deletes structure + data; DELETE removes data only

2. UPDATE Product SET Price = Price + 20;

3. UPDATE Employee SET LastName = 'Singh' WHERE LastName = 'Sharma';

4. UPDATE changes data; ALTER changes structure

5. UPDATE GYM SET Brandname = 'Fit Trend India' WHERE ICODE = 'G101';

6. UPDATE Emp SET Commission = Commission + 100.00;

7. INSERT, UPDATE

8. UPDATE CD SET Singer = 'Sonvi Mehra' WHERE Singer = 'Sonvi Kumar';

9. 1) UPDATE GARMENT SET Colour = 'Orange' WHERE GCode = 116;

2) UPDATE GARMENT SET Price = Price * 1.10 WHERE Size = 'XL';

3) DELETE FROM GARMENT WHERE GCode = 116;

10. UPDATE EMPLOYEE SET JOBID = 104 WHERE ID = 'E4';

11. UPDATE Student SET Marks = 50 WHERE RollNumber = 2;

12. Wrong query. Correct: UPDATE Orders SET salesamount = salesamount + 20;
Class 12 MySQL Worksheets - Answer Key

Worksheet-6: SELECT Command

Worksheet-6 (SELECT Command)

1. Correct: SELECT * FROM Book WHERE Price IS NULL;

2. a) Matches any 2nd letter 'a': results like "Ravi", "Rani", etc.

b) Last names not ending with 'a'

3. i) SELECT * FROM TEACHER WHERE Category='PGT';

ii) SELECT Name FROM TEACHER WHERE Gender='F' AND Department='Hindi';

iii) SELECT Name, Department, HireDate FROM TEACHER ORDER BY HireDate ASC;

iv) SELECT DISTINCT(Category) FROM TEACHER;

4. a) SELECT COST+100 FROM ITEMS WHERE ITEM_NO > 103;

5. i) SELECT * FROM Projects WHERE ProjSize='Medium';

ii) SELECT ProjSize FROM Projects WHERE ProjName LIKE '%LITL';

iii) SELECT ID, ProjName, ProjSize, Cost FROM Projects ORDER BY StartDate DESC;

iv) SELECT DISTINCT ProjSize FROM Projects;

6. i) SELECT Mname FROM Members WHERE Mname LIKE '%v';

ii) SELECT Mname FROM Members WHERE Mname LIKE '%e%';

7. Correct: SELECT * FROM Result WHERE Grade IS NULL;

8. i) SELECT Name FROM RESULT WHERE Division='FIRST' ORDER BY Name ASC;

ii) SELECT Name, Subject, Stipend*12 AS AnnualStipend FROM RESULT;

9. Correct: SELECT Name, Class FROM Students WHERE Course_name IS NULL OR Course_name LIKE

'%economics';

10. i) SELECT ItemName FROM SHOPPE WHERE ItemName LIKE 'C%' ORDER BY Price ASC;

ii) SELECT Code, ItemName, City FROM SHOPPE WHERE Quantity < 100;

11. SELECT * FROM TableName;


Class 12 MySQL Worksheets - Answer Key

12. Keyword: DISTINCT

13. i) SELECT Surname, FirstName, City FROM PERSONS WHERE City='Udhamwara';

ii) SELECT PID, City, Pincode FROM PERSONS ORDER BY Pincode DESC;

iii) SELECT FirstName, City FROM PERSONS WHERE Gender='F' AND BasicSalary > 40000;

iv) SELECT FirstName, BasicSalary FROM PERSONS WHERE FirstName LIKE 'G%';

v) SELECT Surname FROM PERSONS WHERE BasicSalary >= 50000;

14. Corrected: SELECT * FROM EMP ORDER BY ENAME ASC, DEPT ASC;

15. i) SELECT INAME FROM GYM WHERE INAME LIKE 'A%';

ii) SELECT ICODE, INAME FROM GYM WHERE Brandname IN ('Reliable', 'Coscore');

16. i) SELECT Accountno, Name, DateOfOpen FROM SBOP WHERE Transactions > 8;

ii) SELECT * FROM SBOP WHERE TransactionValue IS NULL;

iii) SELECT NAME, BALANCE FROM SBOP WHERE NAME LIKE '%i';

17. Wildcard: %

18. i) SELECT * FROM FLIGHT WHERE SOURCE='Delhi';

ii) SELECT * FROM FLIGHT WHERE NO_OF_FL > 4;

iii) SELECT FNO, SOURCE, DEST, NO_OF_FL FROM FLIGHT ORDER BY NO_OF_FL DESC;

iv) SELECT DEST, FNO FROM FLIGHT WHERE DEST LIKE 'A%';

v) SELECT DISTINCT(NO_STOPS) FROM FLIGHT;

19. i) SELECT Salary+100 FROM Employee WHERE EmpId='A002';

20. i) SELECT * FROM Friends WHERE Nationality != 'Indian';

ii) SELECT Name, City, Country FROM Friends ORDER BY Age DESC;

iii) SELECT Name, City FROM Friends WHERE Email IS NULL;

iv) SELECT Name, Country FROM Friends WHERE Age > 12 AND Name LIKE 'A%';

21. i) SELECT GNAME FROM GARMENT WHERE SIZE='XL';

ii) SELECT GCode, GNAME FROM GARMENT WHERE GNAME LIKE 'Ladies%';
Class 12 MySQL Worksheets - Answer Key

iii) SELECT GNAME, GCode, PRICE FROM GARMENT WHERE PRICE BETWEEN 1000 AND 1500;

iv) SELECT GNAME FROM GARMENT WHERE SIZE IN ('M', 'L') AND PRICE > 1500;

22. Correct: SELECT ID, Salary FROM empsalary WHERE Salary IS NOT NULL;

23. Write query to match required output

24. i) SELECT * FROM Emp WHERE Age < 25;

ii) SELECT Name, Salary FROM Emp ORDER BY Salary DESC;

iii) SELECT Name, Address FROM Emp WHERE Address LIKE '%Delhi%';

iv) SELECT Name, Salary FROM Emp WHERE Salary BETWEEN 50000 AND 70000;

v) SELECT Name, Phone FROM Emp WHERE Phone LIKE '99%';

25. Corrected: SELECT * FROM Sales WHERE City='Chennai' OR City='Mumbai';

26. i) SELECT Name, Location, City, SalesAmount FROM Store ORDER BY SalesAmount DESC;

ii) SELECT Name, SalesAmount FROM Store WHERE Name LIKE '%fashion%';

iii) SELECT Name, Location, DateOpened FROM Store WHERE DateOpened < '2015-03-01';

iv) SELECT DISTINCT City FROM Store;

27. i) IN ii) DISTINCT

28. i) UPDATE PharmaDB SET Price = Price + 50 WHERE DrugName = 'Amlodipine';

ii) SELECT * FROM PharmaDB WHERE Price BETWEEN 100 AND 150;

iii) SELECT DrugID, DrugName, PharmacyName FROM PharmaDB ORDER BY Price DESC;

iv) SELECT RxID, DrugName, Price FROM PharmaDB WHERE PharmacyName IN ('Rx Parmacy', 'Raj

Medicos');

29. SELECT NAME FROM STUDENT WHERE STATE IN ('VA');

30. iii) SELECT * FROM Employees WHERE UPPER(EmpName) LIKE '%AMIT%';

31. i) SID - VARCHAR, DOB - DATE

ii) SELECT Name, Salary FROM Salesperson WHERE Salary BETWEEN 30000 AND 40000;

iii) SELECT Name, Phone, DOB FROM Salesperson WHERE DOB < '1992-11-01';
Class 12 MySQL Worksheets - Answer Key

iv) SELECT Name, Salary FROM Salesperson ORDER BY Salary DESC;

v) SELECT DISTINCT Area FROM Salesperson;

vi) SELECT SID, Name, Salary+500 FROM Salesperson;

vii) SELECT Name FROM Salesperson WHERE Name LIKE '%Kumar%';

32. SELECT first_name, last_name, subject FROM studentdetails WHERE subject='Maths' OR

subject='Science';

33. i) SELECT Mname, Age, FeeGiven FROM Gym WHERE FeeGiven > 12000;

ii) SELECT Mcode, Mname, Age FROM Gym WHERE Gender='F' ORDER BY Age DESC;

iii) SELECT Mname, DateOfAdmission FROM Gym WHERE DateOfAdmission > '2015-12-31';

iv) SELECT Mname, FeeGiven FROM Gym WHERE Age < 40 AND MembershipType='Monthly';

v) SELECT Mname FROM Gym WHERE Mname LIKE '%mit%';

vi) SELECT DISTINCT MembershipType FROM Gym;

34. i) SELECT * FROM Students WHERE Optional='IP';

ii) SELECT Name, Stream, Optional FROM Students WHERE Name LIKE 'A%';

iii) UPDATE Students SET Average = Average + 3 WHERE Stream='Humanities' AND Optional='Maths';

iv) SELECT Name FROM Students WHERE Average > 75;

v) SELECT Name FROM Students WHERE Optional IN ('CS', 'IP');

Common questions

Powered by AI

NULL represents a missing or unknown value, indicating an absence of data, while 0 is a specific numeric value. This distinction is crucial in SQL queries, where NULL must be explicitly checked using IS NULL or IS NOT NULL conditions, whereas 0 can be part of standard numeric comparisons. The implications are significant: assuming one as the other might lead to incorrect data interpretations in queries, such as arithmetic aggregations, averages, and conditional filtering. Proper handling ensures accurate data analysis and database integrity.

UPDATE and DELETE cater to different data management needs. The UPDATE command modifies existing records, allowing for changes or corrections in data without altering the table structure, ideal for data integrity maintenance and minor adjustments. DELETE removes rows based on conditions, suitable for data cleansing or removal of obsolete entries. Choosing between them depends on whether data should be modified in place (UPDATE) or eliminated if unnecessary (DELETE), with considerations for transaction logs and rollback capabilities affecting decision-making.

DML (Data Manipulation Language) commands are used for managing data within schema objects, allowing operations such as SELECT, INSERT, UPDATE, and DELETE, which affect the data itself within the database tables. DDL (Data Definition Language) commands, on the other hand, deal with schema and structure, including commands like CREATE, ALTER, and DROP, which do not affect data directly but define and modify its structure. Together, DML and DDL commands enable comprehensive database management by allowing both structural adjustments and data modifications within the constraints of the relational database schema. Their interaction is essential for database evolution, where DDL prepares the environment that DML then populates or alters.

Normalization uses SQL commands like CREATE TABLE, ALTER TABLE, and constraints (PRIMARY KEY, FOREIGN KEY) to ensure that databases reduce redundancy, achieve data integrity, and optimize queries. By enforcing structured rules, normalization helps in organizing data in relational tables where dependency and redundancy are minimized, thus enhancing data integrity. Operations become more efficient due to reduced data duplication and improved indexing, leading to optimized query performance.

Wildcard characters, like '%' and '_', enhance SQL queries by allowing pattern matching within strings, facilitating flexible searches for similar or partial matches. This increases the power and versatility of queries, but it may reduce efficiency because pattern matching can be resource-intensive, especially when applied to large datasets. Effective indexing and cautious usage are required to mitigate performance impacts while maximizing flexibility in query results.

Using ALTER TABLE to modify or add columns can significantly impact data integrity and performance. Adding new columns or modifying existing ones can lead to rearranging or recalibrating underlying data storage, affecting performance during the operation. It can also affect data integrity, requiring caution to ensure new constraints or data types align with existing data without causing errors or losses. Proper planning is required to minimize downtime and ensure that changes preserve or enhance data integrity.

The DISTINCT keyword in a SELECT statement is used to eliminate duplicate rows from the result set, ensuring that only unique values are returned for the specified columns. This can be crucial for data analysis tasks where redundancy skews results or when unique entries are required for further operations. Using DISTINCT can impact performance, as it requires additional processing to filter duplicates, but it enhances result accuracy by providing a clean, non-redundant dataset.

DROP TABLE command removes the entire table, including its structure and all its data, effectively erasing any existence of the table from the database schema permanently. DELETE FROM, however, is used to remove rows from an existing table without affecting the table's structure; it merely clears data. Choosing DROP TABLE would be ideal for removing obsolete tables entirely, while DELETE FROM is preferred when preserving the table to store new data or maintain its schema for future use.

CHAR reserves a fixed amount of space for the specified length, which means it always uses that amount of storage regardless of actual data size, leading to potential wasted space if data is shorter. VARCHAR, on the other hand, is variable and uses only the necessary space to store data plus a small byte for keeping track of the data's length, optimizing space usage but with a slight overhead for this manageability. This difference affects how efficiently data storage is handled in a database, particularly in terms of disk space utilization and retrieval performance.

A primary key is crucial because it uniquely identifies each record in a table, ensuring that no duplicate rows exist and maintaining data integrity. It cannot contain NULL values, which enforces that every record must have a unique identifier. In contrast, a unique key also enforces uniqueness in a column but allows NULLs, meaning that while duplicates are not permitted for actual data, entries could exist where no data (NULL) is recorded, potentially weakening data integrity in certain contexts.

You might also like