MySQL Relational Query

Topic :Relational Query (Join Query)

Q1. Consider the given below and answer the questions that follow:

Table : Event

EventId	Event	NumPerforms	CelebrityId
101	Birthday	10	C102
102	Promotion Party	20	C103
103	Engagement	12	C102
104	Wedding	15	C104

Table : Celebrity

CelebrityId	Name	Phone	FeeChanged
C101	Faiz Khan	99101956	200000
C102	Sanjay Kumar	893466448	250000
C103	Neera Khan Kapoor	981166568	300000
C104	Reen Bhatia	65877756	100000

(i)                Name the Primary keys in both the tables and foreign key in ‘Event table’. Can NumPerformers (Numbers for performers) be set as the Primary Key? Give Reason.  

Q2. How many rows will be present in the Cartesian join of the above mentioned two tables? In the table ‘Event’, the CelebrityID 102 is present twice in the column ‘CelebrityId’. Is there any discrepancy? Give reason.                           

In a Database there are two tables :

Table : ITEM

ICode	ItemName	Price
101	Television	75000
202	Computer	40000
303	Refrigrator	90000
404	Washing Machine	27000

Table : BRAND

ICode	Brand
101	Sony
202	HP
303	LG
404	IFB

Write MySql queries for the following :

(i)                To display ICode, ItemName and corresponding Brand of those Item, whose price is between 20000 and 45000 (both values inclusive).                                                                                    

(ii)              To display Item Code, Price and Brand name of the Items, which has IName as ‘Television.

(iii) To increase the price of all the  Items by 15%.                                                                        

Q3. Consider the tables given below and answer the questions that follow :

TABLE :WORKSHOP

WorkshopId	Title	NumSpeakers	MeantFor	Fee
551	Time Management	3	Senior Manager	7000
553	App Development	1	Computer Management	9000
554	Planing	2	Senior Manager	8000
556	Marketing Strategirs	2	Junior Manger	9000

Table : Participant

ParticipantId	Name	WorkshopId
100	Prabhu Shankar	551
101	Dev Sen	554
102	Pauzia Khan	551
103	Tom Winters	553

 

(i) WorkshioId ‘552’ is missing in the table workshop. Is there any discrepancy (something not correct)? Give reason for your answer.                                                                                                            

(ii) WorkshopId ‘551’ is present twice in the table Praticipant. Is there any discrepancy? Give reason for your answer.                                                                                                                                                       

(c) With reference the above tables(in Q6(b)), write commands in SQL for (i) to (iii) given below:                                                                                                                                                                            

(i) To display names of Participants along with workshop titles for only those wokshops that have more than 2 Speakers.

(ii) To display PraticipantID, Participant’s name, WorkshopID for workshops meant for Senior Managers and Junior Managers.

(iii)To display WorkshopId, title, ParticipantId for only those workshops that have fees in the range of 5000.00 to 8000.00.

In a Database Company, there are two tables given below:

Table: SALES

SALESMANID	NAME	SALES	LOCATIONID
S1	ANITA SINGH ARIRA	250000	102
S2	Y.P. SINGH	1300000	101
S3	TINA JAISWAL	1400000	103
S4	GURDEEO SINGH	1250000	102
S5	SIMI FAIZAL	1450000	103

Table: LOCATION

LOCATIONID	LOCATIONNAME
101	Delhi
102	Mumbai
103	Kolkata
104	Chennai

Write SQL queries for the following :

(i)                To display SalesmanID,Name of salesman, LocationID with corresponding location names.   

(ii)              To display name of salesmen, sales and corresponding location names who have achieved Sales more than 1300000.                                                                                                           

(iii)            To display names of those salesmen who have ‘SINGH’ in their names.                                   

(iv)            Indentify Primary key in the table SALES. Give reason for your choice.                                  

Write SQL command to change the LocationID to 104 of the Salesman with ID as S3 in the table ‘SALES’.                                                                                                                            

Q4.  Consider the tables ITEMS & COMPANY. Write SQL commands for the statements (i) to (iv) and give the outputs for SQL queries (v) to (viii)               Table : ITEMS

ID	PNAME	PRICE	MDATE	QTY
T001	Soap	12.00	2007-03-11	200
T002	Paste	39.50	2006-12-23	55
T003	Deodorant	125.00	2007-6-12	46
T004	HairOil	28.75	2007-9-25	325
T005	ColdCream	66.00	2007-10-9	144
T006	ToothBrush	25.00	2006-2-17	455

Table : COMPANY

ID	COMP	City
T001	HLL	Mumbai
T008	Colgate	Delhi
T003	HLL	Mumbai
T004	Paras	Haryana
T009	Ponds	Noida
T006	Wipro	Ahmedabad

a)      To display PNAME, PRICE * QTY only for the city Mumbai.

b)     To display product name, company name & price for those items which IDs are equal to

c)      the IDs of company.

d)     To delete the items produced before 2007.

e)      To increase the quantity by 20 for soap and paste.

f)       SELECT COUNT(*) FROM ITEMS WHERE ITEMS.ID=COMPANY.ID;

g)      SELECT PNAME FROM ITEMS WHERE PRICE=SELECT MIN(PRICE) FROM ITEMS;

h)     SELECT COUNT(*) FROM COMPANY WHERE COMP LIKE “P_ _ _ _”;

i)       SELECT PNAME FROM ITEMS WHERE QTY<100;

Q5. 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-170

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

(ii)              How many Tuples and Attributes will be there in the unrestricted join of these two tables?

Consider the following tables Stationery and Consumer. write command in MySQL for (i) to (iv) and output for (v) to (vi).

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	Bangalore	PL01

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

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

(iii)               Display Stationery ID, Stationery name and Address of consumer order of Stationery Name.

(iv)              Write a query to display Stationery ID, Stationery name Company name descending order of price.

(v)                SELECT Company, ConsumerName, Address  FROM Stationery S,  Consumer C  WHERE        S.S_ID= C.S_ID;

(vi)              SELECT Company, COUNT(*)  FROM Stationery   GROUP BY company;

 

 

Q6.  Consider the tables given below :

Table : PARTY

PartyId	Description	CostPerPerson
P101	Birthday	400
P102	Wedding	700
P103	Farewell	350
P104	Engagement	450

Table : CLIENT

ClientId	ClientName	Address	Phone	NoofGuests	PartyId
C101	A.K.Antony	A-151, Adarsh Nagar	99101956	80	P101
C102	Fauzia Aria	K-5/52, Vikas Vihar	893466448	500	P102
C103	Rashi Khanna	D-6, Hakikat Nagar	981166568	50	P101
C104	S.K. Chandra	76-A/2, MG Colony, Adarsh Avenue	65877756	100	P104

(i)                Name the Primary Keys in both the tables.

(ii)              ‘P101’ data is present twice in column ‘PartyId’ in ‘Client’ table-Is there any discrepancy? Give reason for your answer.

(iii)            To display Client names of clients, their phone numbers, PartyId and party description who will have number of guests more than 50 for their parties.

(iv)            To display Client Ids, their addresses, numbers of guests of those clients who have ‘Adarsh’ anywhere in their addresses.

ClientId

ClientName

Address

Phone

NoofGuests

PartyId

(v)              SELECT Clientid, Cleintname, Noofguests,Description,Costperperson, FORM Client, Party WHERE Client.Partyid=Party.Partyid AND Noofguests BETWEEN 50 AND 100;

Q6.  Consider the tables given below :

Table:FACULTY

TeacherId	Name	Address	State	PhoneNumber
T101	Savita Sharma	A-151, Adarsh Nagar	Delhi	991019564
T102	Deepak Ghai	K-5/52, Vikas Vihar	Mumbai	893466448
T103	MahaLakshmi	D-6	Delhi	981166568
Simi Arora			Mumbai	65877766

Table : COURSE

CourseId	Subject	Teacherid	Fees
C101	Introductory Mathematics	T101	4500
C103	Physics	T102	5000
C104	Introductory Computer Science	T103	4000
C105	Advance Computer Science	T104	6500

(i)                Which column is used to relate the two tables?

(ii)              Is it possible to have a primary key and a foreign key both in one table> justify your answer with the help of table given above.

(iii)            To display coursed, TeacherId, Name of Teacher, Phone number of Teachers living in Delhi.

(iv)            To display TeacherId, Names of Teachers, Subjects of all Teachers with names of Teachers starting with ‘S’

(v)              SELECT CourseID,Subject,TeacherID,Name, PhoneNumber FROM Faculty, Course Where Faculty.TeacherID=Course.TeacherID AND Fee>=500;

a) Based on these tables write SQL statements for the following queries:

i. Display the lowest and the highest classes from the table STUDENTS.

ii. Display the number of students in each class from the table STUDENTS.

iii. Display the number of students in class 10.

iv. Display details of the students of Cricket team.

v. Display the Admission number, name, class, section, and roll number of the

students whose grade in Sports table is 'A'.

vi. Display the name and phone number of the students of class 12 who are play

some game.

vii. Display the Number of students with each coach.

viii. Display the names and phone numbers of the students whose grade is 'A' and

whose coach is Narendra.

b) Identify the Foreign Keys (if any) of these tables. Justify your choices.

c) Predict the the output of each of the following SQL statements, and then verify the

output by actually entering these statements:

i. SELECT class, sec, count(*) FROM students GROUP BY class, sec;

ii. SELECT Game, COUNT(*) FROM Sports GROUP BY Game;

iii. SELECT game, name, address FROM students, Sports

WHERE students.admno = sports.admno AND grade = 'A';

iv. SELECT Game FROM students, Sports

WHERE students.admno = sports.admno AND Students.AdmNo = 1434;

2. In a database create the following tables with suitable constraints :

a) Based on these tables write SQL statements for the following queries:

i. Display the average rate of a South Indian item.

ii. Display the number of items in each category.

iii. Display the total quantity sold for each item.

iv. Display total quanity of each item sold but don't display this data for the

items whose total quantity sold is less than 3.

v. Display the details of bill records along with Name of each corresponding

item.

vi. Display the details of the bill records for which the item is 'Dosa'.

vii. Display the bill records for each Italian item sold.

viii. Display the total value of items sold for each bill.

b) Identify the Foreign Keys (if any) of these tables. Justify your answer.

c) Answer with justification (Think independently. More than one answers may be

correct. It all depends on your logical thinking):

i. Is it easy to remember the Category of item with a given item code? Do you

find any kind of pattern in the items code? What could be the item code of

another South Indian item?

ii. What can be the possible uses of Bills table? Can it be used for some

analysis purpose?

iii. Do you find any columns in these tables which can be NULL? Is there any

column which must not be NULL?

3. In a database create the following tables with suitable constraints :

a) Based on these tables write SQL statements for the following queries:

i. Display the dates of first registration and last registration from the table Vehicle.

ii. Display the number of challans issued on each date.

iii. Display the total number of challans issued for each offence.

iv. Display the total number of vehicles for which the 3rd and 4th characters of RegNo are '6C'.

v. Display the total value of challans issued for which the Off_Desc is 'Driving without License'.

vi. Display details of the challans issued on '2010-04-03' along with Off_Desc for each challan.

vii. Display the RegNo of all vehicles which have been challaned more than once.

viii. Display details of each challan alongwith vehicle details, Off_desc, and Challan_Amt.

b) Identify the Foreign Keys (if any) of these tables. Justify your choices.

c) Should any of these tables have some more column(s)? Think, discuss in peer groups, and discuss with your teacher.

4. In a database create the following tables with suitable constraints:

a) Based on these tables write SQL statements for the following queries:

i. Display the details of all the employees who work in Sales department.

ii. Display the Salary, Zone, and Grade of all the employees whose HOD is Nupur.

iii. Display the Name and Department Name of all the employees.

iv. Display the names of all the employees whose salary is not within the specified range for the corresponding department.

v. Display the name of the department and the name of the corresponding HOD for all the departments.

b) Identify the Foreign Keys (if any) of these tables. Justify your choices.