BBPS
Class 12 (Joins)
1. In a database there are two tables 'Customer' and 'Bill' as shown below:
(i) How many rows and how many columns will be there in the Cartesian product of these two tables?
(ii) Which column in the 'Bill' table is the foreign key?
2. Consider the tables HANDSETS and CUSTOMER given below:
With reference to these tables, Write commands in SQL for (i) and (ii) and output for (iii) below:
(i) Display the CustNo, CustAddress and corresponding SetName for each customer.
(ii) Display the Customer Details for each customer who uses a Nokia handset.
(iii) select SetNo, SetName from Handsets, customer where SetNo = SetCode and CustAddress = 'Delhi';
3. In a database there are two tables "Company" and "Model" as shown below:
(i) Identify the foreign key column in the table Model.
(ii) Check every value in CompiD column of both the tables. Do you find any discrepancy?
4. Consider the tables DOCTORS and PATIENTS given below:
W1th reference to these tables, wnte commands m SQL for (1) 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 whose OPD_Days are MWF.
(iii) select OPD_Days, Count(*) from Doctors, Patients where [Link] = [Link] Group by
OPD_Days;
BBPS
Class 12 (Joins)
5. In a database there are two tables "Product" and "Client" as shown below :
Write the commands in SQL queries for the following :
(i) To display the details of Product whose Price is in the range of 40 and 120 (Both values included)
(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.
6. In a. Database School there are two tables Member and Division as show below.
(i) Identify the foreign key in the table Member.
(ii) What output, you will get, when an equi-join query is executed to get the NAME from Member Table and
corresponding DivName from Division table ?
i)
7. In a Database there are two tables :
Table ITEM:
Write MySql queries for the following :
(i) To display ICode, IName and corresponding Brand of those Items, whose Price is between 20000 and 45000 (both
values inclusive).
(ii) To display ICode, Price and BName, of the item which has IName as "Television".
(iii) To increase the price of all the Items by 15%.
8. In a Database there are two tables :
BBPS
Class 12 (Joins)
Table MAGAZINE:
(i) Which column can be set as the PRIMARY KEY in the MAGAZINE table?
(ii) Which column in the ‘MAGAZINE’ table is the foreign key?
(iii) How many rows and columns will be there in the Cartesian product of the above 2 tables.
(iv) Write command in SQL to display the mag_code, Mag_Title and corresponding types for all the Magazines.
(v) Write the output :
(i) Select Mag_Code, Mag_Title, Number_of_Pages, Type From MAGAZINE,MAGTYPE Where
Magazine.Mag_Category=Magtype.Mag_Category and Type=’Spiritual’;
9. In a Database Kamataka_Sangam there are two tables with the instances given below :
Write SQL queries for the following :
(i) To count how many addresses are not having NULL values in the address column of students table.
(ii) To display Name, Class from STUDENT table and the corresponding Grade from SPORTS table.
(iii) To display Name of the student and their corresponding Coachnames from STUDENTS and SPORTS tables.
10. In a Database Multiplexes, there are two tables with the following data. Write MySQL queries for (i) to (iii), which
are based on TicketDetails and AgentDetails :
(i) To display Tcode, Name and Aname of all the records where the number of tickets sold is more than 5.
(ii) To display total number of tickets booked by agent “Mr. Ayush”
(iii) To display Acode, Aname and corresponding Tcode where Aname ends with “k”.
BBPS
Class 12 (Joins)
(iv) With reference to “TicketDetails” table, which column is the primary key ? Which column is the foreign key?
Give reason(s)
i)
11. In a database there are two tables ‘CD’ and ‘TYPE’ as shown below :
(i) Name the Primary key in ‘‘CD’’ table.
(ii) Name the foreign key in ‘‘CD’’ table.
(iii) Write the Cardinality and Degree of ‘‘TYPE’’ table.
(iv) Check every value in CATEGORY column of both the tables. Do you find any discrepancy ? State the discrepancy.
13. Consider the tables ‘Flights’ & ‘Fares’ given below:
Flights
FNO SOURCE DEST NO_OF_FL NO_OF_STOP
IC301 MUMBAI BANGALORE 3 2
IC799 BANGALORE KOLKATA 8 3
MC101 DELHI VARANASI 6 0
IC302 MUMBAI KOCHI 1 4
AM812 LUCKNOW DELHI 4 0
MU499 DELHI CHENNAI 3 3
Fares
FNO AIRLINES FARE TAX
IC301 Indian Airlines 9425 5
IC799 Spice Jet 8846 10
MC101 Deccan Airlines 4210 7
IC302 Jet Airways 13894 5
AM812 Indian Airlines 4500 6
MU499 Sahara 12000 4
With reference to these tables, write commands in SQL for (i) and (ii) and output for (iii) below:
i. To display flight number, source, airlines of those flights where fare is less than Rs. 10000.
ii. To count total no of Indian Airlines flights starting from various cities.
iii. SELECT [Link], NO_OF_FL, AIRLINES FROM FLIGHTS,FARES WHERE [Link] = [Link] AND
SOURCE=’DELHI’;
i)
14. A table STUDENT has 5 rows and 3 columns. Table ACTIVITY has 4 rows and 2 columns. What will be the
cardinality and degree of the Cartesian product of them ?
15. Consider the following table named “GARMENT”.
BBPS
Class 12 (Joins)
What is the degree and cardinality of ‘Garment’ table ?
16. In a Database, there are two tables given below :
Write SQL Queries for the following :
(i) To display employee ids, names of employees, job ids with corresponding job titles.
(ii) To display names of employees, sales and corresponding job titles who have achieved sales more than 1300000.
(iii) To display names and corresponding job titles of those employee who have ‘SINGH’ (anywhere) in their names.
(iv) Identify foreign key in the table EMPLOYEE.
i)
ii)
17. Consider the tables given below.
Salesperson Orders
i. The SalespersonId column in the "Salesperson" table is the Primary [Link]
SalespersonId column in the "Orders" table is a Foreign KEY.
ii. Can the ‘SalespersonId’ be set as the primary key in table ‘Orders’. Give reason.
18. With reference to the above given tables, Write commands in SQL for (i) and
(ii) and output for (iii) below:
i. To display SalespersonID, names, orderids and order amount of all salespersons.
ii. To display names ,salespersons ids and order ids of those sales persons whose names start with ‘A’ and
sales amount is between 15000 and 20000.
iii. SELECT [Link], name, age, amount FROM Salesperson, orders WHERE
[Link]= [Link] AND AGE BETWEEN 30 AND 45;
BBPS
Class 12 (Joins)
i.
19. Consider the tables given below :
Table : Faculty
TeacherId Name Address State PhoneNumber
T101 Savita Sharma A-151, Adarsh Delhi 991019564
Nagar
T102 Deepak Ghai K-5/52, Vikas Mumbai 893466448
Vihar
T103 MahaLakshmi D-6 Delhi 981166568
T104 Simi Arora Mumbai 658777564
Table : Course
CourseId Subject TeacherId Fee
C101 Introductory Mathematics T101 4500
C103 Physics T101 5000
C104 Introductory Computer Science T102 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.
i) TeacherID
ii) Yes, it is possible to have both Primary and foreign key in one table. For Example in the above course table
CourseID is a primary key and teacherid is the foreign key.
20. With reference to the above given tables, write commands in SQL for (i) and (ii)
and output for (iii) :
(i) To display CourseId, TeacherId, Name of Teacher, Phone Number of Teachers living in Delhi.
(ii) To display TeacherID, Names of Teachers, Subjects of all teachers with names of Teachers starting with ‘S’.
(iii) SELECT CourseId, Subject,[Link],Name,PhoneNumber FROM
Faculty,Course WHERE [Link] = [Link] AND Fee>=5000;
i)
21. Consider the tables given below which are linked with each other and maintains referential integrity:
Table: SAP
Table : Store
With reference to the above given tables, write commands in SQL for (i) and (ii) and output for (iii) below:
i. To display the ItemCode,ItemName and ReceivedDate of all the items .
ii. To display SAPID,ItemName,ItemStorageLocation of all the items whose Received date is after 2nd May
2016.
BBPS
Class 12 (Joins)
iii. SELECT SAPID,ItemName,STOREID FROM SAP,Store WHERE [Link]=[Link] AND
StoreLocation = “Hauz Khas”
iv. What will be the degree and cardinality of the cartesian product formed while combining both the above
given tables ‘SAP’ and ‘Store’ ?
v. Sangeeta is not able to add a new record in the table ‘Store’ through the following query:
Insert into store values (1206,1006,’Karol Bagh’, ‘2016/07/25’);
Identify the error if there is any
i.