Computer Sci. & Informatics Practices: My SQL Assignment-01

CLASS XII – INFORMATICS PRACTICES

ASSIGNMENT – MY SQL

Q1. Consider the table Projects given below. Write commands in SQL for i to iv and output for v to viii

PROJECTS

ID	ProjName	Projsize	StartDate	EndDate	Cost
1	Payroll-MMS	Medium	2006-03-17	2006-09-16	60000
2	Payroll-ITC	Large	2008-02-12	2008-01-11	500000
3	IDMgmt-LITL	Large	2008-06-13	2009-05-21	300000
4	Recruit-LITL	Medium	2008-03-18	2008-06-01	50000
5	IDMgmt-MTC	Small	2007-01-15	2007-01-29	20000
6	Recruit-ITC	Medium	2007-03-01	2007-06-28	50000

i) To display all information about projects of Medium ProjSize.

ii) To list the ProjSize of projects whose ProjName ends with LITL.

iii) To list ID, name, size and cost of all the projects in descending order of StartDate.

iv) To count the number of projects of cost less than 100000.

v) SELECTsum(Cost) FROM projects.

vi) SELECT DISTINCT(Projsize) FROM projects.

vii) SELECT MAX(Cost) FROM projects.

viii) SELECT AVG(Salary) FROM projects group by ProjSize.

Q2 Consider the table DOCTORS and PATIENTS given below.

DOCTORS

DocID	DocName	Department	OPD_Days
101	M. Pandey	ENT	TTS
102	G.P. Gupta	Paed	MWF
201	C.K. Sharma	Ortho	MWF

PATIENTS

PatNo	PatName	Department	DocID
1	Neeraj	ENT	101
2	Mohit	Ortho	201
3	Ragini	ENT	101
4	Mohit	Paed	102
5	Nandini	Ortho	201

With reference to these tables, write commands in SQL for (i) and (ii) and output for (iii) below.

i) Display the PatNo, PatName, and corresponding DocName for each patient.

ii) Display the list of all patients whoseOPD_days are MWF.

iii) Select OPD_Days, Count(*)

From Doctors, Patients

Where Patients.Department = Doctors.Department

Group by OPD_Days

(a)

A database db1contains three tables namely tb1,tb2,tb3.To delete all the tables and database following set of commands is given ,but it is not working. Why?

Drop database db1;

Drop table tb1;

Drop table tb2;

Drop table tb3;

(b)

Write a SQL statement to display

Today, the date is <current date>

(c)

Consider the table employee(empid,sal,job) job is a not null column. The following statement is correct or not? Why?

Delete empid,sal,job

From employee

Where sal>=10000;

(d)

Name a SQL command which point to a transaction uptil which all changes have been saved permanently.

(e)

A numeric data field CNT contains 35675.8765.Write a SQL command to round off CNT to

(i)Whole number (ii) Upto 3 decimal places

(f)

Name the SQL commands for the following situations:

(i)To add tuples in a relation.

(ii)To view the structure of the table.

(g)

Name the keyword used to:

(i)To combine all those records that have identical values in a particular field or group of fields.

(ii)To enforce referential integrity .

(h)

List any three clauses ,which can be used with select statements.

(i)

A table student in a database has 10 columns and 21 rows. There are 5 new admissions and e_mailid of those students also inserted in the above table. Initially e_mailid column was not there in the table. Now what is the degree and cardinality of the relation.

(j)

Consider the table student given below. Find out the output of the following queries:

Student

(i) (ii) (iii) Select count(*) from student; Select count(Fees) from student;	Rno	Fees
	1	4000
	2	4300
	3	5000
	4	Null
	5	4000
	6	Null

(iv)

(a)

Differentiate between SQL commands DROP TABLE and DELETE.

(b)

How will you drop a constraint from a table.

(c)

Write the command to Display the first three and last four characters of “Information”?

(d)

Find out the output.

(i)SELECT 1000+SQRT(25);

(ii)SELECT FLOOR(144.34)+1000+CELL932.23);

(iii)SELECT SUBSTR(TRIM(‘ I LOVE PROGRAMMING ‘)5,7);

(iv)SELECT MOD(ROUND(125.60,1),5);

(e)

Write SQL commands for statements (i) to (vi) and give output for SQL queries (vii) to (x) on the basis of the table FAMILY:

No	Name	Female Members	Male Members	Income	Occupation
1	Mishra	3	2	67000	Service
2	Gupta	4	5	150000	Business
3	Khan	6	4	48000	Mixed
4	Chaddha	2	2	125000	Business
5	Yadav	7	4	120000	Mixed
6	Joshi	1	4	114000	Service
7	Maurya	6	3	45000	Farming
8	Rao	5	2	110000	Service

(i) To display the name of all those where Male members are more than 3.

(ii) To display the total female members of occupation business.

(iii) To show the occupation of all those members whose income is between 75000 to 110000.

(iv) To list the total no of female members which have an ‘o’ in their name.

(v) To arrange the table in the descending order of income.

(vi)To insert a new record in the Family table with the following data:

9,’D’souza’,2,1,15000,’Service’.

(vii)SELECT MIN(DISTINCT Income) FROM Family;

(viii)SELECT MIN(Female Members) FROM Family WHERE Occupation = ‘Mixed’;

(ix)SELECT Occupation ,SUM( Income) FROM Familygroup by Occupation ;

(x) SELECT AVG( Income) FROM Family;

Q5. Write SQL commands to create the table HOSPITAL with following specification:

Field Name	Data Type	Constraints
DId	Int(4)	Part of primary key
PNo	Int(4)	Part of primary key
Age	Int(2)	<=16
Department	Varchar(20)	Not Null
DateOfAdm	Date
Charges	Double(7,2)
Address	Varchar(15)	Default Hyderabad

Q6. Evaluate this CREATE TABLE statement.Which line will cause an error?Why?

Create table part(

Part_id number,

Part_namevarchar(250),

Cost number(7,2),

Constraint pk1 primary key (Part_id),

Constraint cost_nn not null(cost));

Q7. Given following CREATE TABLE statement ,identify constraints and their complete details:

CREATE TABLE Orders(

O_Idint not null,

Ordernoint not null,

P_Idint,

primary key(O_Id),

constraint fk_PerOrders foreign key (P_Id)

references Persons(P_Id));

Q8. Consider the following tables Stationery and Consumer.

Stationery

S_ID	StationeryName	Company	Price
DP01	Dot Pen	ABC	10
PL02	Pencil	XYZ	6
ER05	Eraser	XYZ	7
PL01	Pencil	CAM	5
GP02	Gel Pen	ABC	15

Consumer

C_ID	ConsumerName	Address	S_ID
01	Good Learner	Delhi	PL01
06	Write Well	Mumbai	GP02
12	Topper	Delhi	DP01
15	Write & Draw	Delhi	PL02
16	Motivation	Banglore	PL01

(i)Write a query to display ConsumerName, Address, Company,Price for those whose price is not more than 10.

(ii)Display the company whose consumers are located in Delhi.

(iii)Select company,ConsumerName,address

From Stationery RIGHT JOIN Consumer

ON Stationery.S-ID= Consumer.S-ID;

(iv)Select company,count(*)

From Stationery, Consumer

Where Stationery.S-ID= Consumer.S-ID

Group by company;

Q9. Answer the following questions-

(a)How can we see the list of existing tables?

(b) Room_No, Cust_Name, Room_Type and Room_Rent of table “HOTEL” are given below:

Table :HOTEL

Room_No	Cust_Name	Room_Type	Room_Rent
204	Rajesh	Single	700
308	Anuja	Double AC	1600
105	Vinamra	Single NAC	500
202	Soma

Based on this information, find output of the following queries: (2)

(I) SELECT Room_Type, SUM(Room_Rent) FROM hotel GROUP BY Room_Type;

(II) SELECT Room_no FROM hotel WHERE Cust_Name LIKE ‘%n%;

(d) Why do we use ROLLBACK statement ? (1)

(e) What is foreign key?How do we define a foreign key in our table ? (2)

(f) Differentiate between CHAR and VARCHAR datatypes? (2)

Q10. (a) What do you understand by primary key and candidate key. (2)

(b) Write the output of following SQL queries. (2)

i. SELECT ROUND(6.88,2) ;

ii. SELECT MID(‘Discovery Channel’,4,6) ;

iii. SELECT DAYOFMONTH (‘2011–03–30‘);

iv. SELECT TRUNCATE (7.727,1);

RollNo	Name	Class	DOB	Sex	City	Marks
1	Nanda	X	6/6/95	M	Agra	551
2	Saurabh	XII	7/5/93	M	Mumbai	462
3	Sanal	XI	6/5/94	F	Delhi	400
4	Trisla	XII	8/8/95	F	Mumbai	450
5	Store	XII	8/10/95	M	Delhi	369
6	Marisla	XI	12/12/94	F	Dubai	250
7	Neha	X	8/12/95	F	Moscow	377
8	Nishant	X	12/6/95	M	Moscow	489

(i) To Display all information about class XII students. (1)

(ii) List names all class of all students in descending order of DOB. (1)

(iii) To count the number of student in XII Class of Mumbai city. (1)

(iv) SELECT DISTINCT(Sex) FROM Student. (1)

(v) SELECT AVERAGE(Marks) FROM Student GROUP BY Sex. (1)

(vi) SELECT COUNT(*)FROM Student where Class = ‘XI’ (1)

Q11.(A)Study the following table TEACHER and SALARY and write the SQL commands for the questions (i) and (ii) and give outputs for SQL queries(iii) and (iv)

Table : TEACHER

SID	NAME	DEPT	SEX	EXPERIENCE
101	Siddharth	Computer	M	12
104	Raghav	Physics	M	5
107	Naman	Chemistry	M	10
114	Nupur	Computer	F	3
109	Janvi	Physics	F	9
105	Rama	Accounts	M	10
117	James	Computer	F	3
111	Binoy	Accounts	F	12
130	Samuel	Computer	M	15

Table : SALARY

SID	BASIC	ALLOWANCE	DA
101	12000	1000	300
104	23000	2300	500
107	32000	4000	500
114	12000	5200	1000
109	42000	1700	200
105	18900	1690	300
130	21700	2600	300

i. Display NAME and DA of all staff who are in Accounts department and having more than 10 years of experience and DA is more than 300. (2)

ii. Display the NAME and salary of all staff working in physics department and salary more than average salary. (SALARY=BASIC +ALLOWANCE+ DA) (2)

iii. Select NAME, DA, BASIC from TEACHER, SALARY

Where BASIC>20000 and TEACHER.SID=SALARY.SID; (1)

iv. Select NAME, DEPT, SEX, BASIC, DA from TEACHER, SALARY

Where DEPT LIKE ‘%o%’ and DA >=500 and TEACHER.SID=SALARY.SID;(1)

Q12.Given the following tables for a database Library: 6

Table BOOKS

Book_id	Book_name	Author_name	Publishers	Price	Type	Qty
F001	The tears	William Hopkins	First Publ	750	Fiction	10
F002	Thunderbolts	Anna Roberts	First Publ	700	Fiction	5
T001	My First c++	Brain & Brooke	EPB	250	Text	10
T002	C++ Brain works	A.W.Rossaine	TDH	325	Text	5
C001	Fast Cook	LataKapoor	EPB	350	Cookery	8

Write SQL queries for (a) to (f):

(a) To show book name, author name and price of books of epb publishers and priceLess than 700.

(b) To list the sum of price and average of quantity type wise.

(d) To increase the price of all books of first publ by 50.

(e) To insert a new row in the books table.

(f) Add a column in the above table serial no which is a primary key and not null.

Q13. A Which Keyword is used to eliminate redundant data from a query result?

B. Write a query to display the number of employees with same job.

C. What is an use of COMMIT, ROLLBACK and SAVEPOINT in SQL?

Q 14

i. What will be an output of the following SQL queries:

1. SELECT DAYOFMONTH(CURDATE());

2. SELECT ROUND(453.345,2);

3. SELECT LENGTH(INSTR(SUBSTRING(‘CORPORATE WORLD’, 11,5),LD));

4. SELECT MOD(657,9);

5. SELECT CHAR(66,67.3,’69.3);

6. SELECT SUBSTR(“INTERNATIONAL”,LENGTH(CONCAT(“MARKET”,”ING”)),3);

ii. What do you mean by Begin Commit and Rollback?

iii. What is the difference between Primary key and unique Key?

iv. What do you mean by derived table and base table?

v. What is the difference between Natural Join and Self Join?

vi. What is the difference between table level constraint and column level Constraints?Give Example

vii. Why we use truncate command?

Q15. (a) which command is used to see the names of the tables present in a database.

(b)While creating a table ‘Customer’ Varidhi forgot to set the primary key for the table. Give the statement

which she should write now to set the column ‘CustID’ as the primary key of the table?

(c)Ananya wants to add some records ( with balance >10000) from an opld table chequeOld to another

table namely chequeNew What command she should use for this.

(d)What is the following command doing ?

Alter Table Persons

Add Constraint chk_persons Check ( P_ID > 0 and City =’Shimla’);

(e)What is the following query doing ?

Alter Table Persons

Drop Primary Key;

(f) Anurag has created a table namely Trial that has 7 columns and 3 records .after testing , Anurag added

three more columns, fourteen more records but deleted the 3 records. What is the degree and cardinality

of the table now ?

(g) The Table Qsales has following information in it. i.e.

Quarter	Sales
1	15000
2	NULL
3	5000
4	4000

Based on this, find the output of the following :

(i)Select AVG(Sales ) from QSales;

(ii) Select Concat(‘Quarter’,Quarter), Sales from QSales;

Q16.(a) What is the purpose of DROP TABLE command in MySQL. How is it different from DELETE

command.

(b) What is the difference between Natural join and cross join?

No	Name	Age	Department	Dateofadm	Charges	Gender
1	Pratik	62	Surgery	2011-10-12	300	M
2	Shubham	22	ENT	2011-10-15	250	M
3	Krinjal	32	Orthopedic	2011-10-16	200	F
4	Vipul	12	Surgery	2011-10-18	300	M
5	Nitesh	30	ENT	2011-10-22	250	M
6	Lakshit	16	ENT	2011-10-25	250	M
7	Alpesh	29	Cardiology	2011-10-28	800	M
8	Shruti	45	Nuclear Medicine	2011-10-29	300	F
9	Aishwarya	19	Cardiology	2011-11-1	800	F
10	Neha	23	Surgery	2011-11-2	400	F

(i)To list the names of female patients who are in ENT department.

(ii) To list the names of patients with their date of admission in the reverse order of their admission.

(iii) To display patient’s name, Charges, Age for only female patients.

(iv) To count the number of patients with age < 30.

(v) (a) Select COUNT( Distinct Charges) from Hospital.

(b) Select MIN(age) from Hospital where gender =’M’;

(d) Select SUM ( charges) from Hospital where dateofadm<= ’2011-11-01’;

Q17. (a) Create table as per following table Instance Chart.

Column Name	EmpID	EmpName	EmpAddress	EmpPhone	EmpSal	DeptID
Key Type	Primary					Foreign
Null / Unique		Not Null
Foreign Key Table						Department
Foreign Key Column						Dept_ID
Data Type	NUMBER	VARCHAR	VARCHAR	VARCHAR	NUMBER	VARCHAR
Size	6	20	30	10	9,2	2

(b) Given two tables as shown below :

Employee2

Empno(PK)	Salary
100	200.85
200	129.54
300	98.17

Employer

Id	Employee_no(FK)
51	100
52	100
53	200
54	300

Write commands to create them with constraints . Delete on a primary key Should be cascaded to its foreign key.

Q18. Consider the following tables Product and Client. Write SQL commands for the statements (c) to (d) and give outputs for SQL queries (e) to (f).

Table : Product

P_ID	ProductName	Manufacturer	Price
TP01	Talcom Powder	LAK	40
FW05	FaceWash	ABC	45
BS01	Bath Soap	ABC	55
SH06	Shampoo	XYZ	120
FW12	FaceWash	XYZ	95

Table : Client

C_ID	ClientName	City	P_ID
01	Cosmetic Shop	Delhi	FW05
06	Total Health	Mumbai	BS01
12	Live Life	Delhi	SH06
15	Pretty Woman	Delhi	FW12
16	Dreams	Banglore	TP01

(1) To display the details of those clients whose city is not Delhi.

(2) To display the Client name, City From table Client and Product Name and Price from table Product, with

their corresponding matching P_ID.

(3) Select Manufacturer, Max(price), Min(Price), Count(*)

From Product

Group By Manufacturer;

(4) Select ClientName, Manufacturer

From Product , Client

Where Client.P_ID = Product.P_ID;

Q19. (a) If a database “Employee” exists, which MySql command helps you to start working in that database?

(b) Sahil created a table in MySql. Later on he found that there should have been another column in the table. Which command should he use to add another column to the table?

(c) Rama is not able to change a value in a column to NULL. What constraint did she specify when she created the table?

(d) The LastName column of a table “Directory” is given below:

LastName

Batra

Sehgal

Bhatia

Sharma

Mehta

Based on this information, find the output of the following queries:

(a) SELECT LastName FROM Directory WHERE LastName like “_a%”;

(e) Write SQL statement to set Autocommit to off.

(f) Distinguish between a Primary key and Candidate key with the help of suitable example of each.

(g) Distinguish between Commit and Rollback.

(h)

Q20. (a) What is the purpose of ALTER TABLE command in MySql? How is it different from UPDATE command?

(b) Table Employee has 4 records and table Dept has 3 records in it. Mr. Jain wants to display all information stored in both of these related tables. He forgot to specify equi–join condition in the query. How many rows will get displayed on execution of this query?

(c) Consider the table EXAM given below. Write commands in MySql for (i) to (iv) and output for (v) to (viii).

Table: EXAM

No.	Name	Stipend	Subject	Average	Division
1	Karan	400	English	15	FIRST
2	Aman	680	Maths	24	FIRST
3	Javed	500	Accounts	NULL	FIRST
4	Bishakh	200	IP	20	SECOND
5	Sugandha	400	History	10	THIRD
6	Suparna	550	Geo	5	THIRD
7	Ankit	400	NULL	10	THIRD

(i) To list the names of those students, who have obtained Division as FIRST in the ascending order of NAME.

(ii) To display a report listing NAME, SUBJECT and Annual stipend received assuming that the stipend column has monthly stipend.

(iii) To count the number of students, who have either Accounts or Informatics as Subject.

(iv) To insert a new row in the table EXAM:

6, “Mohan”, 500, “English”, 73, “SECOND”;

(v) SELECT AVG(Stipend) FROM EXAM WHERE DIVISION=“THIRD”;

(vi) SELECT COUNT (DISTINCT Subject) FROM EXAM;

(vii) SELECT MIN (Average) FROM EXAM WHERE Subject=“English”;

(viii) To count the number of students according to their Division wise.

Q21. (a) Write a MySql command for creating a table “PAYMENT” whose structure is given below:

Table : PAYMENT

Field Name	DateType	Size	Constraint
Loan_number	Integer	4	Primary key
Payment_number	Varchar	3
Payment_date	Date
Payment_amount	Integer	8	Not NULL

(b) Write a MySQL command to change the constraint of field name Payment_amount to accept NULL values.

(c) Write a MySQL command to set the combination of Payment_number&Payment_date as the Primary key instead of Loan_number.

Q22. (a) In a database there are two tables “Product” and “Client” as shown below:

Table : PRODUCT

P_ID	ProductName	Manufacture	Price
P001	Moisturiser	XYZ	40
P002	Sanitizer	LAC	35
P003	Bath Soap	COP	25
P004	Shampoo	TAP	95
P005	Lens Solution	COP	350

Table : Client

C_ID	ClientName	City	P_ID
01	Dreamz Disney	New Delhi	P002
05	Life Line Inc	Mumbai	P005
12	Harry Ltd.	New Delhi	P001
15	Appolo	Banglore	P003

Write the command in MySql queries for the following:

(i) Write MySql command to show the Cartesian product of above mentioned tables.

(ii) To display the ClientName, City from table Client and ProductName and Price from table Product, with their corresponding matching P_ID.

(iii) To increase the Price of all the Products by 20.

(b) In a Database school there are two tables Member and Division as show below.

Table : Member

Empld	Name	Pay	Divno
1001	Shankhya	34000	10
1003	Ridhima	32000	50
1002	Sunish	45000	20

Table : Division

Divno	Divname	Location
10	Media	TF02
20	Dance	FF02
30	Production	SF01

(i) Identify the foreign key in the table Member. Justify your choices.

(ii) Write a MySql Query to display the NAME from Member Table corresponding their Divname from Division table?

Q23 (a) Write MySql command that will be used to show all the databases which is already

created in MySql.

(b) The Department column and date of joining of a table Employee is given below:

Department	Date_of_Joining
Biology	2009-07-19
Zoology	2007-02-13
Bio_Tech	2010-05-15
Psycology	2011-09-06

(i)Based on the above table write SQL Query to display the name of those department

Whose name ends with logy.

(ii)Based on the above table write SQL Query to display the name of those department

Whose name starts with Bi.

(d) Differentiate between Primary key and Unique Key?

(e) Define Class and Object with reference to Object Oriented Programming. Also write

syntax to declare and initialize an object. Why they are used?

(f) In the above given table Employee Mr Roy wants to display the date of joining of

those employees whose date of joining is less than 15-05-2010. He has written the

following Select statement which is not giving correct result.

Select * from Employee where Doj<15-05-2010;

Help Mr Roy to run the query by removing the errors. Write the correct query.

Q24 (a) What is an use of Modify and Truncate command?

(b) Write the name of different TCL commands of MySql?

Table : Library

Sr_No	Book_Name	Publishers	Author	Year_of_Publication	Price
1	Basic for beginners	BPB	Raman Prakash	1995-05-19	150
2	Mastering Informatics Practices	McGraw	Mrs Lilli	2010-07-11	270
3	Advance Pascal	Galgotia	SandipVerma	2005-02-21	380
4	Learn Ms-Office	Student Publication	ReetaAahuja	2009-09-23	470
5	Network Guide	BPB	VinaySainy	2008-08-25	225
6	Mastering C++	BhartiBhawan	Mrs Lilli	2011-11-18	210
7	Genes and Evolution	McGraw	Dr A P Jha	1995-12-30	450
8	IT Laws	Galgotia	SurendraSexsena	2008-10-05	600
9	Auditing	Mcgraw	S K Paul	2012-01-07	105
10	Play with IP	Student Publication	DhruvAdhikari	1999-08-03	110

(i) To list the Authors whose publishers is Student Publication and price is greater

than 350.

(ii) To list the Sr_No, Price, Author in descending order of Price.

(iii) To list the Book_Name which are published before year 2000.

(iv) Display the name of authors whose name ends with “lli”.

(v) Select Distinct Count(Publishers) from library;

(vi) Select Sum(Price) from Library Group by Publishers;

(vii) Select * from Library where Publishers in(“Student Publication”,”Galgotia”);

Q25 Write Sql Commands for the following:

(a) Display the Student Name, Age from table Student and Member_Id from Library table

with their matching Roll_No. Both the table here contains Roll_No.

(b) To add a column Parents in Student table to store Alpha Numeric Characters.

Field Name	Data Type	Size	Constraints
Member_Id	Varchar	5	Primary Key
Membership_fees	Integer		Less than 500
Expiry_Date	Date

(ii) Write a command to insert any one record in the table Members created above.

Q 26.

a) Which MySQL command helps you to see existing databases?

b) Rakesh created a table in Mysql. Later he found that table is wrongly created and he wants to remove it. Name the command by which Rakesh can do it.

c)Reena created a table named student, she wants to see those students whose name ending with p. She wrote a query-

SELECT name.* FROM student WHERE name=”%p”;

Help Reena to run the query by removing the errors from the query and rewriting it.

d)Sanjeev is not able to reduce the salary of employee. Which constraint has he used while creating table?

e) What is primary key? How is it different from candidate key? Explain with the help of suitable example.

f) Emp_no, Emp_name and Basic of table “EMP” are given below

Emp_No	Emp_Name	Basic
6985	Anuj	6700
5874	Kirti	7500
6587	Kiran
5478	Krihsna	5500

Based on this information, find output of the following queries.

a) SELECT MAX(Basic) FORM EMP;

b) SELECT Name FROM emp WHERE Basic IS Null;

g) Define Degree and Cardinality.

Q27.

a) Categorize the DDL and DML commands :

CREATE, TRUNCATE, DELETE, GRANT, ROLLBACK, SELECT

b) Write output of the following SQL queries :

i) SELECT TRUNCATE(22.78,1);

ii) SELECT DAYOFMONTH(‘2010-10-17);

iii) SELECT SUBSTRING(‘Common Wealth Games 2010’,8,6);

iv) SELECT CONCAT(LOWER(‘Class’),UPPER(‘xii’));

c) Consider the table Flight given below, write command in SQL for (1) to (4) and output for (5) to (8).

Table : FLIGHT

Flight_No	Origin	Destination	Seats	FlightDate	Rate
1005	Varanasi	Nepal	275	12-Dec-07	3000
2785	Delhi	Kerala	290	17-Jan-08	5500
6587	Mumbai	Varanasi	435	19-Feb-08	5000
1265	Varanasi	Nepal	200	02-Jan-08	5400
4457	Delhi	Lucknow	150	22-Feb-08	4500
6856	Varanasi	Mumbai	180	03-Mar-08	6000

(1) To display Flight flying between Varanasi and Nepal.

(2) To display the different Origin of Flights.

(3) To display list of flights in descending order of Rate.

(4) To display flight details of the flight whose flightdate is after Jan 2008.

(5) SELECT Flight_No, Destination FROM Flight WHERE Destination LIKE ‘_u%’;

(6) SELECT Origin, COUNT(*) FROM Flight GROUP BY Origin;

(7) SELECT Origin, Destination FROM Flight WHERE seats>400;

(8) SELECT SUM(Rate),MAX( Seats) FROM Flight;

Q 28.

Answer the following question.

a) Write an SQL query to create the table “Books“ with the following structure-

Field	Type	Constraint
Book_Id	Varchar(6)	Primary Key
Book_Name	Varchar(25)
Author_Name	Varchar(30)
Publisher	Varchar(20)
Price	Integer
Type	Varchar(15)
Quantity	Integer	Not Null

b) In a database there are two tables ‘Patient’ and ‘Doctors’ are shown below-

Table: Patient

Name	Patient_No	Date_Adm	Doctor_No
Kishor	P104	2009-05-15	502
Ragini	P202	2010-01-11	165
Reshu	P754	2007-12-31	325
Kanti	P612	2010-04-22	165

Table: Doctor

Doctor_No	Doctor_Name	Speciality
122	M. K Singh	Dentist
165	R. K. Tiwari	Neurology
325	V. K. Chauhan	Surgery
502	N. Singh	Nephrology
530	K. P. Sinha	Urology

i) Name the columns which can be made ‘Primary Key’ in both the tables.

ii) What will be the cardinality of Cartesian product of both the tables?

c) Consider the tables given below.

Table : STOCK

Itcode	Itname	Dcode	Qty	unitpr	Stkdate
444	Drawing Copy	101	10	21	31-June-2009
445	Sharpener Camlin	102	25	13	21-Apr-2010
450	Eraser Natraj	101	40	6	11-Dec-2010
452	Gel Pen Montex	103	80	10	03-Jan-2010
457	Geometry Box	101	65	65	15-Nov-2009
467	Parker Premium	102	40	109	27-Oct-2009
469	Office File	103	27	34	13-Sep-2010

Table : DEALERS

Dcode	Dname	Location
101	Vikash Stationers	Lanka Varanasi
102	Bharat Drawing Emporium	Luxa Varanasi
103	Banaras Books Corporation	Bansphatak Varanasi

With reference to these tables, write commands in SQL for (i) and (ii) and output for (iii) below-

(i) To display the amount that has been spent to purchase Office file & Sharpener camlin.

(ii) To display all the items of Vikash Stationers.

(iii) SELECT DCODE,COUNT(QTY),AVG(UNITPR) FROM STOCK GROUP BY DCODE;

Q29. Answer the following questions.

(a) Define the ACID Property in MYSQL?

(b) Write brief notes on any four table Constraints in MySQL?

(d) How you can add /remove constraints from table with an example?

(e)Differentiate Primary key and foreign key

Q30. (a) What is a datatype? Name some datatypes used in MYSQL?

(b) What are three transaction control statements n MySQL? Give example

(i) To show all information about the patients of cardiology department.

(ii) To list the names of female patients who are in orthopedic department.

(i) To display Patient’s name, charges ,age for only male patients

(ii) To count the number of patients with age>30.

NO.	NAME	Age	Department	Dateofadm	Charges	Sex
1	Tarun	12	Surgery	01/01/98	300	M
2	Zubin	36	Orthopedic	12/01/98	250	F
3	Ankita	16	Orthopedic	24/02/98	300	F

d) What is a join? Explain with a suitable query?

Q31.(a) Write an SQL command for creating a table student who structure given below

Field Name	Data type	Size	Constraint
Rno	Number	3	Primary key
Class	Varchar	5	Not Null
Percentage	Number	5,2	Not Null

(b) Given the following teacher relation: Write SQL commands for question.

Teacher Table

No.	Name	Department	DateofJoining	Salary	Sex
1	Raja	Computer	21/5/98	8000	M
2	Sangeeta	History	21/5/97	9000	F
3	Ritu	Sociology	29/8/98	8000	F

Question c.d,e based on teacher table

(b) Show the list of Computer and history department.

(d) To list all names of teacher with date of admission in ascending order.

(e) To display Teachers name, department and salary of female teachers.

Q32.

a. You need to remove all the rows from the Sales_HIST table. You want to release the storage space but do not want to remove the table structure. What is the solution to the problem?

b.Differentiate between CHAR and VARCHAR datatypes?

c. Predict the output:

i. Select round(29.21,1), round(32.76,1);

ii. Select power(2,5);

iii. Select 9 mod 2;

iv. Select concat(‘catch a ’ ,concat( ‘falling’, ‘star’));

v. Select length(trim( ‘……..to be continued…’));

Q 33.

a. What is significance of Group By Clause in used in SELECT query ?

b. Define Equi join and non Equi join

c. What do you understand by Transaction COMMIT & ROLLBACK?

d. What is constraint.? Explain the difference between default and check constraints.

Q 34.

a. Create table Depart including constraints:

b.Add one more column Email of datatypeVarchar and size 30 to the table Depart

c. Select all the department name in descending order.

d.Select the department name whose distance is maximum.

e. Insert values in the table as S101, ‘Sales’, “delhi’, 120.

Column Name	Dept ID	DeptName	DepartLOC	Distance
Key Type	Primary
Constraint		NOTNULL
Datatype	Number	Varchar	Varchar	Number
Length	2	20	20	4

Q35. a. Which command is used in MySql to change the column name?

b While creating a table ‘Customer’ Simrita forgot to set the primary key for the table.

Give the statement which she should write now to set the column ‘CustID’ as the primary

key of the table?

c. Define Transaction? Give the name of TCL commands

d. Write command to add a foreign key constraint ( namelyFK_address_person_num ) to table address so that person_num field of address becomes foreign key of num field of person table.

Note : Both tables already created.

e. Difference between Equi and Non EquiJoin with the help of suitable example.

f. Differentiate between COUNT(*) and COUNT(DISTINCT).

g. What do you understand by Partial Roll backing?

Q36.(a) Table employee has 4 records and Table Dept has 3records in it. Mr. Garvit wants to display all information stored in both of these related tables. He forgot to specify equi-join condition in the query. How many rows will get displayed on execution of this query.

(b) What is the purpose of DDL command in MySql ? How is it different from DML command.

(c) Write SQL commands for the statement (i) to (iv) and give outputs for SQL queries (v) to (viii) on basis of the table LAB.

No	ItemName	CostPerItem	Quantity	DateOFPurchase	Warranty	Operational
1	Computer	60000	9	21/05/2006	2	7
2	Printer	15000	3	21/02/2007	4	2
3	Scanner	18000	1	29/08/2008	3	1
4	Camera	21000	2	13/06/2006	1	2
5	Hub	8000	1	31/10/2009	2	1
6	UPS	4000	5	21/05/2006	1	4
7	Plotter	25000	2	11/01/2010	2	2

UM ( DISTINCTCostPerItem ) from paint. (1/2)

(viii) Select AVG ( CostPerItem ) from LAB where DateOfPurchase<’01-01-2009’ .

Q37. (a) Create two tables:

Customer ( customer_id , name )

Customer_Sales( transaction_id , amount , customer_id)

Underline columns indicate primary keys and customer_id indicates foreign key in customer_sales Table .Make sure that no action should take place in case of a

DELETE or UPDATE in the parent table. Name the foreign key constraint as

fk_cust.

(b) In a database there are two tables ‘LOAN’ and ‘BORROWER’ as shown below:

LOAN

Loan_number	Branch_name	Amount
L-170	Downtown	3000
L-230	RedWood	4000
L-260	Perryridge	1700

BORROWER

Customer_Name	Loan_number
Jones	L-170
Smith	L-230
Hayes	L-155

(i) Identify the foreign key column in the BORROWER table.

(ii) How many rows and columns will be there in the natural join of these two tables.

TABLE : PRODUCCT

P_ID	ProductName	Manufacture	Price
TP01	Talcom Powder	LAK	40
FW05	FaceWash	ABC	45
BS01	Bath Soap	ABC	55
SH06	Shampoo	XYZ	120
FW12	FaceWash	XYZ	95

TABLE : CLIENT

C_ID	ClientName	City	P_ID
01	Cosmetic Soap	Delhi	FW05
06	Total Health	Mumbai	BS01
12	Live Life	Delhi	SH06
15	Pretty	Delhi	FW12
16	Dreams	Banglore	TP01

Answer the following questions based on the above tables:

(i) To display the details of Product whose price is in the range of 50 to 100

(ii) To display the Client Name,city from table client and Product Name and price from table product, with their corresponding matching P_ID.

(iii) To increase the price of all products by 10.

Q38. a. Which command is used in MySql to close an existing database “Member”?

b The EName column of a table Customer is given below:

CName

Kamal

Kunal

Garvit

Rajiv

Rohit

Khushi

Sonu

Based on the information, find out the output the following queries :

(i) SELECT Ename from employee where Ename like ‘%o%;

(ii) SELCT Ename from employee where Ename like ‘%i_’;

c. A table “Transport” in a database has column 3 and row 8.What is the cardinality and

Degree in it.

d. Write command to add a foreign key constraint ( namelyFK_address_person_num ) to table address so that person_num field of address becomes foreign key of num field of person table.

Note : Both tables already created.

e. Difference between Equi and and natural join

f. Which two statements complete a transaction? (Choose Two )

a. delete employees;

b. describe employees;

c. rollback to savepoint c;

d. alter table employees

modify column sal;

e. select max(sal)

from employees

wheredepartment_id=20;

g. What do you understand by ACID properties of database transaction?

Q39. (a) Table employee has 4 records and Table Dept has 3records in it. Mr. Garvit wants to display all information stored in both of these related tables. He forgotto

specifyequi-join condition in the query. How many rows will get displayed on execution of this query.

(b) What is the purpose of GROUP BY command in MySql ? How is it different from JOIN .Explain with suitable example.

(c) Write SQL commands for the statement (i) to (iv) and give outputs for SQL queries (v) to (vii) on basis of the table Market

Table : MARKET

No	Shop_Name	Sale	Area	Cust_Percent	Rating	City
1	Radha Gifts	250000	West	68.6	C	Delhi
2	PeterEngland	0.00	South	81.8	A	Chennai
3	Rock On	300000	North	79.8	B	Amiritsar
4	Sports King	380000	North	null	B	Baroda
5	Biswas Stores	Null	East	92.0	A	Dehli
6	Big Bazar	290000	South	66.7	A	Kolkota

i) To display number of shops in each Area in ascending order.

ii) To display name and customer percentage of all the shops having average cust_percent more than 90.

To display Min,Max,Avg sale in each city and arrange these record according to their rating.

iii) To display a report with shop_Name,Area and Rating for each shop in the table, for only those shops whose sale is not between 250000 and 350000.

iv) Select count(cust_percent) from market;

v) Select avg(distinct sale) fro market where Rating=’A’;

vi) Select Max(DistinctSale) from market where city=’Dehli’;

vii) Select count( Distinct city) from market;

Q40. (a) Create two tables:

Customer ( customer_id , name )

Customer_Sales( transaction_id , amount , customer_id)

Underline columns indicate primary keys and customer_id indicates foreign key in customer_sales Table .Make sure that no action should take place in case of a

DELETE or UPDATE in the parent table. Name the foreign key constraint as

fk_cust.

(b) In the database there are two tables Product_Sale_Detail and Item_Detail as shown below:

TABLE Product_Sale_Detail

No	PName	SName	Qty	Price	City
P1	Bread	Britenia	150	8.00	Dehli
P2	Cake	Britenia	250	20.00	Mumbai
P3	Coffee	Nescafe	170	45.00	Mumbai
P4	Chocolate	Amul	380	10.00	Delhi
P5	Souce	Kissan	470	36.00	Jaipur
P6	Maggi	Nestle	340	10.00	Kolkata
P7	Biscuit	Marie	560	21.00	Chennai
P8	Jam	Kissan	220	40.00	Dehli
P9	Piknik		345	5.00	Kolkata

TABLE :Item_Detail

No	IName	City
I1	Bread	Dehli
I2	Cake	Dehli
I3	Coffee	Kolkate
I4	Sauce	JaiIur

i) How many rows and how many columns will be there in the Unrestricted Join or Cross product of these two tables.

ii) Identify the foreign key column in both tables.

(b) Consider the following tables , item and customer . write SQL commands for the statements (i) to (iii) .

TABLE : SENDER

SenderID	SenderName	SenderAddress	SenderCity
ND01	R Jain	2, AbcAIIts	New Delhi
MU02	H Sinha	12, Newtown	Mumbai
MU15	S. Jha	22/A, Iark Street	Mumbai
Nd50	T Irased	122-K SDA	New Delhi

TABLE : RECIIIENT

RecID	SenderID	RecName	RecAddress	RecCity
K005	ND01	R. BajIayee	5,central Avenue	Kolkata
ND08	MU02	S. mahajan	116, A vihar	New Delhi
MU19	ND01	H Singh	2A, Andheri East	Mumbai
MU32	MU15	I K Swamy	B5, C S Vihar	Mumbai
ND48	ND50	H triIathi	13,b MayurVihar	New Delhi

i) To disIlay the sender Name,city from SENDER table and ReciIient Name and address from table RECIIIENT

ii) To disIlay the number of sender in each reciIient city.

iii) Write a query to disIlay number of reciIient of each sender. Arrange the list in ascending order.

Q41 (a) Differentiate between COMMIT and ROLLBACK.

(b) Given the following lab relations: Write SQL command for questions (i) to (vi) and the

output for (vii). Table:Lab

No	Item Name	Cost Per Item	Quantity	Date of Purchase	Warranty	Operational
1	Computer	60000	9	2006-05-21	2	7
2	Printer	15000	3	2007-05-21	4	2
3	Scanner	18000	1	2008-08-29	3	1
4	Camera	21000	2	2006-06-13	1	2
5	Hub	8000	1	2009-06-13	2	1
6	UPS	5000	5	2006-05-21	1	4
7	Plotter	25000	2	2010-01-11	2	2

(i) To select the Item Name purchased after 2007-10-31.

(ii) To list the ItemName, Which are within the Warranty period till current date.

(iii) To list the ItemName in ascending order of the date of purchase where

quantity is more than 3.

(iv) To display ItemName, CostPerItem, and Quantity whose warranty is over.

(v) To count the number of Items whose cost is more than 10000.

(vi) To insert a new record in the lab table with the following data:

8,’VCR’,10000,2,’2010-02-02’,1,2.

(vii) Give the output of following SQL command:[Include the last inserted

values in part(vi)]

a) SELECT MIN(DISTINCT QUANTITY) FROM LAB;

b) SELECT MIN(WARRANTY) FROM LAB WHERE QUANTITY=2;

c) SELECT SUM(COSTPERITEM) FROM LAB WHERE QUANTITY>2;

d) SELECT AVG(COSTPERITEM)FROM LAB WHERE DATEOFPURCHASE<’2009-01-01’;

Q42 (i) Write the resulting output of the following:

a) SELECT 1000+SQRT(100);

b) SELECT TRIM(‘ ABSPUBLIC SCHOOL ‘);

c) SELECT ROUND(ROUND(407.39,1),-1);

d) SELECT LOWER(‘ABSPUBLIC SCHOOL’);

(ii) What is Column Alias?

(iii) What is the difference between Alter and Update Command ?

(iv) What is Primary Key,Foreign Key and Candidate Key?

(v) How could we get different entries from a table?

(vi) What is the difference between Drop and Delete Command?

Q43 (a) If R1 is a relation with 5 rows and R2 is a relation with 6 rows and 7 columns,

then how many rows will be their in Cartesian product of R1 and R2? What is the

degree of R2 table.

(b) Write an SQL query to create the table ‘Menu’ with the following structure.

Field	Type	Constraint
ItemCode	Varchar(5)	Primary Key
Item Name	Varchar(20)	Default ‘CPU’
Category	Varchar(20)	Check either Software or Hardware
Price	Decimal(5,2)	Greater than 1000

(d) Write SQL command to list names of pets who have names ending with y.

Q44. In a database there are two tables Books and Issued as shown below.

Books Issued

Book_id	Book_name	Quantity	Book_Id	Quantity-issued
C001	Fast Cook	5	T001	4
F001	The Tears	20	C001	5
T001	C++	10	F001	2
T002	VB	15
F002	NeatBeans	50

(i)Identify the foreign key column in the table Issued.

(ii)How many rows and columns will be there in the Natural Join of these two tables?

Q45. Answer the following:

1. What is the importance of primary key in a table? Explain with a suitable example?

2. Which MySQL command helps you to create database if it does not exist?

3. Write brief notes on any four table Constraints in MySQL?

4. Difference between ROWSPAN and COLSPAN attribute.

5. Difference between TABLE and FORM tags.

7 comments:

Unknown20 March 2016 at 05:38
This comment has been removed by the author.
Ram Kishore20 March 2016 at 08:22
Sir Please solve and send solution of q15 a
Nalin Sood20 May 2020 at 22:59
pls send all the answers
Unknown15 April 2021 at 22:29
Write output for SQL queries (i) to (iii) ,which are based on the table given below :
TABLE : DOCTOR
DID NAME DEPTNO CHARGES SEX DOJ
1 ARPIT 201 300 M 21-11-2011
2 ZARINA 202 250 F 16-11-2011
3 ANKITA 203 300 F 14-11-2007
4 ARUN 201 300 M 5-09-2007
5 SHILPA 202 200 F 12-10-2012
6 JASPAL 202 250 M 3-05-2011
7 SANJAY 203 200 M 1-06-2012
(i) Select AVG(CHARGES) from DOCTOR;
(ii) Select distinct DEPTNO from DOCTOR;
(iii) Select DID, NAME from DOCTOR where DOJ > “01-01-2012 ”;
Unknown15 April 2021 at 22:30
Sir solve this
Anurag7 June 2022 at 04:31
Sir pls provide solutions for Questions 1 to 20, i have to check my answers and clear some doubts, u can email me: anuragjha7516@gmail.com
Thanku sir
Loved ur content