MySQL Worksheet-2

1. Write SQL commands for the following queries (a) to (e) based on the relation Passengers.

Table: Passengers

Pnr	T_no	P_name	Sex	Age	TravelDate
P011	11001	Puneet Kumar	M	42	2019-07-12
P022	11010	Kush arora	M	39	2014-12-30
P033	12201	Shweta Nagpal	F	25	2014-12-30
P044	11011	Sumit Mishra	M	32	2018-07-15
P055	12021	Ashmita Sharma	F	33	2019-11-30
P066	12012	Vinny Sood	F	35	2017-12-17
P077	13013	Akshat Gupta	M	30	2018-07-22

Note: Dates are given in YYYY-MM-DD format.

(a) To display pnr, passenger names with their age in descending order by traveldate.

(b) To display the details of passengers who travelled before year 2015.

(c) To display no. of male passengers of below 40.

(d) To display ticket no, passenger names with their travel date whose last digitof ticket no. is 1.

(e) To remove the record of all passengers who travelled on 30th December irrespective of the year.

2. Write SQL commands for the following queries (a) to (e) based on the relations Teacher and write output (f) to (h) to given below:

Table: Teacher

SNo	TName	Sal	Subject	Qualification	Sex
1	Parmeet Kaur	70000	Chemistry	Msc	F
2	Rohit Sharma	72000	Social sci	MA	M
3	Gurveen Kaur	65000	Physics	Msc.	F
4	Sunita Kumar	85000	Chemistry	Msc	F
5	KJ Singh	90000	Comp sci	M Tech	M
6	Amrita	64000	Comp sci	M Tech	F
7	Pankaj	82000	Maths	Msc.	M

(a) Write a command to print the name of "M Tech" teachers with their subjects.

(b) Write a command to print the names along with annual salary in ascending order of salary.

Note: Given salary is monthwise in table Teacher

(C) Write a command to print the details of "M Tech" and "Msc. Female Teachers. Note use IN operator

(d) Write the total no of female and male teachers present in table Teacher.

(e) Write a command to display the data in reverse order (bottom to top) as givenin table.

(f) SELECT MAX(Sal), MIN(Sal) FROM Teacher WHERE subject = 'chemistry';

(g) SELECT subject, COUNT(*) FROM Teacher GROUP BY subject;

(h) SELECT Tname, Subject, Salary FROM TeacherWHERE Qualification= 'M Tech' ORDER BY Sal desc;

3. Write the answers based on the following table Patients of database Hospital.

Table: Patients

Apt_no	FName	SName	Disease	Address
P56	Rekha	Verma	Diphtheria	P. Vihar
P57	Shalini	Kapoor	Cholera	B Bagh
P58	Jasbeer	Chopra	Typhoid	S Road
P64	Neeraj	Bansal	Tuberculosis	S Road
P66	Amboo	Nath	Cholera	P Vihar
P70	Angad	Singh	Typhoid	S Nagar

(a) Suggest a suitable data type with its size for theApt no field.

(b) Add a new record with the following details:      (p65 Preet", "R garden").

(c) Write a command to change the column "FName" to "FirstName"

(d) To remove of all records of Typhoid patients, select the correct command from the following:

(i) Drop from patients where disease = "Typhoid";

(ii) Delete from patients having disease ="Typhoid";

(iii) Remove from patient where disease = "Typhoid";

(iv) Deletefrumpatients where disease = “Typhoid";

 

4. Write a command to rename the table to "New Patients" in SQL.

Table: STOCK

Ino	I_name	Dcode	U_Price	Dateofstock
5005	Gel Pen	102	16	31-Mar-10
5003	Pencil	102	20	01-Jan-10
5002	Eraser	101	14	14-Feb-10
5006	Geometry Box	101	22	01-Jan-10
5001	Scale	102	5	14-Mar-10
5004	Pencil	102	10	12-Dec-09
5007	Pen	103	8	25-Jan-10

(a) Suggest the serving key to be declared as a primary key.

(b) Write a sql command to create a table STOCK with specified column in table

(Give data type for each column as per your choice)

(c) Add a new column "Discount" in table STOCK of type integer.

(d) Write a command to change a column name "I_name" to "ItemName".

(e) Write a command to view the structure of table STOCK.

5. Write SQL commands for the following queries (a) to (e) based on the relation below: Worker given below.

Table: Worker

Empno	ENAME	Design	Salary	DeptNo	Sex
6678	Nameeta Paul	President	80000	111	F
6787	Shyam Singh	Clerk	25000	444	M
6788	Veer Singh	Sales	20000	333	M
6867	Rajshree	Sales	25000	333	F
6687	AngadWalia	Clerk	25000	444	M
6777	Simran Kaur	Sales	20000	333	F
6876	Abhay Kumar	Manager	50000	222	M

(a) To display the Empno, name and annual Salary of all clerks

(b) To increment the salary by 1500/- for all sales girls present in table Worker.

(c) To insert a new value for columns emp name, designation and salary with empcode 6777).

(d) To display total no. of workers in each department with department numberpresent in a table worker.

(e) To delete all records of employees whoseEmpno begins with 66.

Write output:

(f) SELECT MIN(Salary), Max(Salary) FROM Worker WHERE Sex= ‘F’;

(g) SELECT Desig, COUNT(*) FROM Worker Group By Desig;

(h) SELECT Empno, E_NAME, DeptNo, FROM Worker WHERE Salary between 25000 and 50000;

 

6. Ram Prasad has his own manufacturing unit of garments where he has to maintain the details of the products and his salesman. He decides to use MySQL to store and maintain the data. The details of the database are as follows.

Name of the database - "Garments"

Name of the table- Salesman .

The attributes of Salesman are as follows:

E code- Integer

Name-char of size 20

Item_Name-char of size 10

Sales - Float

Commission-Float

Table :Saleman

E_code	Name	Item_Name	Sales	Commission
1101	Shyam Sunder	Upper	50000.00	5000.00
1102	Om Prakash	Lower	80000.00	8000.00
1103	Hari Om	Inner wear	10000.00	1000.00
1105	Ghanshyam	Upper	55000.00	5500.00
1104	AmbuNath	Shorts	30000.00	3000.00

(a) Identify the attribute best suitable to be declared as a Primary Key in tableSalesman.

(b) Insert the following data into the attributes E_code, Name and commission with the following values: 1106, 'Raj Kumar', 85000.

(c) Write the degree and cardinality of the table given above.

(d) Suggest the correct statement to increase the commission of each salesman by5 percent.

(i) Alter salesman set commission = commission + commission 0.05;

(ii) Update salesman change commission= commission + commission 0.05;

(iii) Update salesman set commission =commission + commission 0.05 ;

(iv) Update salesman set commission =commission * 0.05;

(e) Display the highest and the lowest commissions from table Salesman.

 

7. A manufacturing company "XYZ Ltd. is considering to maintain its workersdatabase using SQL for storing their details. As a database administrator, Mr. Khanna has decided for this Job.

 

·         Name of the database -WORKER

·         Name of the table -MANAGER

·         The attributes of table MANAGER are as follows:

·         Deptno:_____________

·         Deptname: character of size 20

·         Name: character of size 20

·         City: character of size 10

 

(a) Suggest a suitable data type for the Deptno field with required size.

(b) Identify suitable field for unique values from the above table.

(c) Suggest the SQL command to insert new record in the table MANAGER

(d) To get into a database, suggest the correct command from the following:

(1) Start Worker,         (ii) Begin Worker,                               (iii) Use Worker,         (iv) Move Worker;

(e) Which command can be used to make some changes in structure of existing table present in a database Worker?

8. Write SQL commands for the following queries (a) to (e) based on the AIRLINES given below:

(a) Display flight no., source and destination in decreasing order of distance.

(b) Display the details of flights which are having seating capacity more than 200.

(c) Display the destination of flights from the table AIRLINES.

(d) Display the details of all flights whose flight number ends with digit 3.

(e) Display the source and destination of the flights whose distance is more than 10000 Km.

Write the output of the SQL quires from (f) to (h) based on relation.

(f) Select Start, To, Distance from Airlines where F_No Like “T%”

(g) Select Min(distance), Max(distance) from Airlines;

(h) Select Start, count(*) from Airlines group by Start;

 

9.  A Department store “DAILY NEEDS” is considering to maintain its inventory using SQL to store the data. As a database administrator. Vijay has decided that:

·         Name of the database – “MYSTORE”

·         Name of the table – NEEDS

·         The attributes of table – NEEDS are as follows:

·         R_NO-__________

·         R_Name – character of size 20

·         Price – numeric

·         Quantity -  numeric

(a) Identify the type of attribute R_NO.

(b) Increase the price of “Bread” by ` 2.

(c) Write the degree and cardinality of the table NEEDS.

(d) Vijay wants to see the structure of table NEEDS. What should he write to see the structure of the table?

(e) Vijay Wants to remove the details the of item “Butter Milk” from the table, Suggest him the command from the following :

(i) DELDE FROM NEEDS;

(ii) DROP FROM NEEDS WHERE R_NO = ‘R65’;

(iii) DELETE FROM NEEDS WHERE R_NO= ‘R65’;

(iv) DELETE “BUTTER MILK” FROM NEEDS;

 

10. A Senior Secondary School “ANS Public School” is considering to maintain their date sheet using SQL for preboard exam. As a database administrator, Ms. Anita has decided that

·         Name of the database – ANSPublic

·         Name of the table – DATESHEET

·         The attributes of DATASHEET are as follows :

Sub_code – numeric

Subject – Character of size 20

Date- date

Day -  character of size 10

Table : Datesheet

Sub_code	Subject	Date	Day
083	English	1	Mon
145	Physics	3	Wed
405	Maths	8	Mon
220	Cheistry	12	Fri
223	P.Ed	20	Sat
R65	CA	25	Thur

(a) Identify the candidate key and primary key from the above table Datesheet.

(b) Ms. Anita wants to chance the date of P.Ed exam from 20 to 22 bbut she is not able to wriote correct command. Help her to run a correct command so that proposed date can be changed to 22.

(i)     Alter datasheet set date=22 where sub_code=223;

(ii)   Update datesheet set date=22 where sub_code=223;

(iii) Update date=22 from datresheet where sub_code=223;

(iv) Alter date=22 from datasheet where sub_code=223;

(c) Ms Anita wants to add a new column “Strength” to store the number of students appearing in each exam. Suggest her the command from the following, which can be use to do so.

(i)     Alter table datasheet insert Strength ineger;

(ii)   Update table datesheet add Strength integer;

(iii)  Alter table datesheet add column Strength integer;

(iv)  Update datesheet add column Strength integer;

(d) After adding one more column, what will be the degree and cardinality of the table datesheet?

(e) Which command she should write to see the structure of the Datesheet?

 

11. A fitness Gym “FITNESS PALACE” is considering to maintain their database using SQL. As a database administrator, Vicky has decided the following :

·         Name of the database – “FITNESS PALACE”

·         Name of the table – GYM

·         Attributes of the table  -  GYM are as follows

Mem_no – character of size 4

Mem_Name – character of size 15

Address – character of size 20

Age – numeric

Fees – numeric

Consider the table GYM given below:

Table : GYM

Mem_No	Mem_Name	Address	Age	Fees
M001	Manisha	New Delhi	29	3500
M002	Isha	New Delhi	25	3200
M003	Nikhil	Noida	20	3500
M004	Sunil	Noida	22	3200

(a) Write the name of the most appropriate column that can ne considered as primary key. Also, write the statement to declare a column as a primary key.

(b) Insert the following data into the attributes: Mem_No, Mem_Name and frres respectively in the given table GYM.

(c) What will be the degree and cardinality of the above table, after the insertion of  new record of member no. M005?

(d) Which command will be use to remove the record of Mem_Ni=M002?

(e) Write the statement to display the structure of the table GYM?

12. A Super Speciality Hospital  “New Life” setting up their database using SQL to store the data. As a database administer, Mr. Raghav has deicide that :

·         Name of the database – “NEWLIFE”

·         Name of the table – PATIENTS

·         The Attributes of table – PATIENTS are as follows :

P_NO – numeric

P_Name – character of size 20

App_no – numeric

Spl_Dr – Character of size 15

Table : Patients :

P_no	P_Name	App_no	Spl_Dr
11	Swati	25	Ortho
12	Atul	29	Ortho
13	Binod	42	Dental
14	Liza	26	ENT
15	Puneet	30	Neuro
16	Shweta	41	ENT
17	Dheeraj	33	Dental

(a) Does P_no Column of tale Patients, can be declared as a primary key? If yes give the reason.

(b) Write the SQL statement to get the first and last appointment from the table.

(c) By assuming an empy table Patients, give the degree and cardinality of it.

(d) Mr. Raghav wants to add one more columns ‘ADDRESS’ in the table Patients. Suggest him the correct command.

(e) Remove record of ENT Doctors from the PATIENTS?

13. Sneha Manage the allowance and commissions for her employee’s in MySQL table called ‘Salary’. She made some changes in the existing table but she has also made some changes in the existing table but she has also made some mistake in SQL commands. Helo her to seen SQL commands properly in database.

·         Name of the database – STORE

·         Name of the table – SALARY

·         Attributes of the table Salary are :

ID – numeric

Basic- numeric

Allowance - numeric

Commission - numeric

Table : Salary

ID	Basic	Allowance	Commission
101	18000	1800	800
103	15000	1500	500
105	16500	1650	650
107	20500	2500	500
109	19000	1900	900
301	15200	1520	520
303	16800	1680	680

(a) Which SQL command she must write to change the data type of the column Commission form integer to float?

Mysql>____________ Slary ________________ commission float;

(b) Suggest the attribute to be declared as a primary key.

(c) Sneha herself tried to add one more column ‘Name’ in table Salary but she is not able towrite the correct commend. Suggest her the correct statement from the given statements:

(i)     ADD Name varchar(15) INTO TABLE Salary;

(ii)   UPDATE Table Slary add Name varchar(15);

(iii)  Alter table Salary add Name varcahr(15);

(iv)  INSERT Name varchar(15) INTO TABLE Salary;

(d) After making all the required changes, write a command to show the structure of the table Salary.

(e) Tell her about the cardinality and degree of the table Salary.

 

14. A Grocery store “SuperMart”  use to tell sell daily essentials to its customers. For the inventory management and bills generations, they are planning to maintain a database in SQL. As a database administrator, Mr. Kaual has decided that:

·         Name of the Database – SUPERMARKET

·         Name of the Table – ITEM

·         Attributes of the table ITEM are as follows :

Item_no

Item_Name

Item_qty

Item_Price

Item_no	Item_Name	Item_qty	Item_Price
S101	Maggie	2	10
S103	Ketchup	3	5
S103	Eggs	8	4
S104	Bread	6	25
S105	Butter	3	55
S106	Cheese	2	50
S107	Sweet buns	4	12

 

(a) Suggest a constraint for column Item_no so that the user can’t input any duplicate value.

(b) Give the name of the middle attribute of the table Item with its respective data type.

(c) What data type would you suggest for the column Item_No?

(d) Mr. Kaul wants to add one more column in table Item. Suggest him the correct command from the given options to add a new column TotalAmt of type integer.

(i)     ADD COLUMN  TotalAmt INTEGER IN Item;

(ii)   ALTER TABLE Item add TotalAmt INTEGER;

(iii)  INSERT INTO TABLE Item add TotalAmt INTEGER;

(iv)  ALTER TABLE Item INSERT TotalAmt INTEGER;

(e) Mr. Kaul wants to calculate total amount of each item stored in a table. Help  him calculate the total amount.

Note: The total amount must be calculated as the item price multiplied by item quantity for each item.

 

15. Write SQL commands for the following queries (a) to (e)  and output (f) to (h) based in the relation INSTITUTE given below:

Table : INSTITUTE

S_ID	NAME	FEE	CITY	PHONE
S1	Aryan	55000	Jaipur	8820726523
S2	Jastej	45000	Jaipur	9812351900
S4	Dhruv	50000	Noida	9811591567
S6	Isha	55000	Delhi	7812330978
S7	Sakshi	44000	Bhopal	7921239465
S8	Vinay	44000	Bhatinda	9822330044

(a) To show all information about the students residing in Jaipur alphabetically arranged by students’ name.

(b) To add one more column “Subject” of type character in table INSTITUTE.

(c) To list the name and phone number of all students whose phone number begins with 98.

(d) write a SQL command to reduce the fees of all students by 12 percent.

(e) Write a SQL command to count the number of students from each city.

(f) SELECT NAME, CITY FROM INSTITUTE WHERE CITY LIKE “B%”;

(g) SELECT MIN(FEE), MAX(FEE) FROM INSTITUTE;

(h) SELECT NAME, PHONE FROM INSTITUTE WHERE FEE>50000 ORDER BY PHONE;