Scribd
Upload
27 views14 pages

Class12 MySQL Answers

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
27 views14 pages

Class12 MySQL Answers

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');

You might also like