Chapter 5: MySQL revision Tour

Assignment : Short Answer Questions / Conceptual Question

1. How are SQL commands classified?

2. Differentiate between DDL and DML

3. (a)  What is the use of UPDATE statement in SQL? How is it different from ALTER statement?

3. (b)  Mr. Shankar created a table VEHICLE with 3 rows and 4 columns. He added 1 more row to it and deleted one column. What is the Cardinality and Degree of the Table VEHICLE?
Ans: Degree – Total No of Columns = 4
Cardinality – Total No of Rows = 4

3. (c) Consider the following table named “GYM” with details about fitness items being sold in the store. Write commands of SQL for (i) to (iv)

ICODE	INAME	PRICE	BRANDNAME
G101	Power Fit Exerciser	20000	Power Gymea
G102	Aquafit Hand Grip	1800	Reliable
G103	Cycle Bike	14000	Ecobike
G104	Protoner Extreame Gym	30000	Coscore
G105	Message Belt	5000	Message Expert
G106	Cross Trainer	13000	GTC Fitness

(i) To display the names of all the items whose name starts with “A”
(ii) To display ICODEs and INAMEs of all items, whose Brandname is Reliable or Coscore
(iii) To change the Brandname to “Fit Trend India” of the item, whose ICODE as “G101”.
(iv) Add a new row for new item in GYM with the details:
“G107”, “Vibro Exerciser”, 21000, “GTCFitness”

4. (a) Mr. James created a table CLIENT with 2 rows and 4 columns. He added 2 more rows to it and deleted one column. What is the Cardinality and Degree of the Table CLIENT?
Ans: Degree – Total No of Columns = 4 – 1 = 3
Cardinality – Total No of Rows = 2 + 2 = 4

4. (b) Consider the following table FITNESS with details about fitness products being sold in the store. Write command of SQL for (i) to (iv)

(i) To display the names of all the products with price more than 20000
(ii) To display the names of all products by the manufacturer “Aone”
(iii) To change the price data of all the products by applying 25% discount reduction.
(iv) To add a new row for product with the details:
“P7”, “Vibro Excersiser”, 28000, “Aone”

5. Write SQL commands for the following on the basis of given table Club.

(a) To show all information about the swimming coaches in the club
(b) To list names of all coaches with their date of appointment (DATEOFAPP0 in descending order.
(c) To display a report, showing coachname, pay, age and bonus (15% of pay) of all the coaches.

6. Write SQL commands for the following in the basis of given table STUDENT.

(a) Select all the Nonmedical stream students from STUDENT1.
(b) List the name of those students who are in class 12 sorted by Stipend.
(c) List all student sorted by AvgMark in descending order.

7. What is Foreign key? How do you define a Foreign key in your table?

8. How is FOREIGN KEY commands different from PRIMARY KEY command?

9. How is FOREIGN KEY related to PRIMARY KEY command?

10. What are table constraints? What are column constraints? How are these two different?

11. Insert all those records of table Account into table Pending where amt_outstanding is more than 10000.

12. Increase salary of employee records by 10% (table employee)
Ans: UPDATE employee SET salary = salary + salary * 0.1

13. Add a constraint (NN-Grade) in table Empl (given before assignment) that declares column Grade not null.
Ans: ALTER TABLE Empl MODIFY Grade char(1) NOT NULL;
* Here we are taking data type char(1) keeping in mind that value being passed is like A, B etc. you can take is as per your concern.

14. Drop the table Empl.
Ans: DROP TABLE Empl;

15. Differentiate between

(i) DROP TABLE, DROP DATABASE

(ii) DROP TABLE, DROP clause of ALTER TABLE

16. Mr. Mittal is using a table with following columns:
Name, class, Stream_ID, Stream_Name
He needs to display names of students who have not been assigned any stream or have been assigned stream_name that ends with “computers”.
He wrote the following command, which did not give the desired result
SELECT Name, Class FROM Students
WHERE Stream_name = NULL OR Stream_name = “%computers”;
Help Mr. Mittal to run the query by removing error and write correct query.
Ans:
SELECT Name, Class FROM Students
WHERE Stream_name IS NULL OR Stream_name LIKE “%computers”;

17. The Doc_name Column of a table Hospital is given below:

Doc_name
Avinash
Hariharan
Vinayak
Deepak
Sanjeev

Based on the information, find the output of the following queries:
(i) SELECT Doc_name FROM HOSPITAL WHERE Doc_name like “%v”;
Ans:
Sanjeev
(ii) SELECT Doc_name FROM HOSPITAL WHERE Doc_name like “%e%”;
Ans:
Deepak
Sanjeev

18. Sarthak a student of class XII, created a table “Class”. Grade is one of the columns of this table. To find the details of students whose Grades have not been entered, he wrote the following MySQL query, which did not give the desired result :
SELECT * FROM Class WHERE Grade = “Null”
Help sarthak to run the query by removing the errors from the query and write the correct query.
Ans:
SELECT * FROM Class WHERE Grade IS NULL;

19. What is the purpose of DROP TABLE command in MySQL? How is it different from DELETE command?