INDIAN SCHOOL DARSAIT

DEPARTMENT OF ICT
Subject: Informatics Practices (065) Topic: SQL (Theory) Worksheet No.: 1
Resource Person: Fathima P. Aliyar Date: 12-05-2019
Name of the Student : Class: XI C Roll Number :

1. What is RDBMS? Name any two RDBMS software.

2. Define the following with suitable examples:
i) Primary Key ii) Alternate Key iii) Candidate Key iv) Foreign Key
3. What is a relation? What is the difference between a tuple and an attribute?
4. Explain relational database model.
5. What are the advantages of DBMS?
6. Define database.Give any two features of DBMS.
7. Differentiate between the following:
i) Primary Key and Candidate Key
ii) CHAR and VARCHAR datatypes
iii) Degree and Cardinality of a relation
8. What do you meant by datatype? Name the various datatypes used in SQL.
9. ‗‗XYZ‘‘ Company conducts workshops for employees of organizations. The company requires data of
workshops that are organized. Write SQL query to create a table ‗Workshop‘ with the following
structure: Field Type Constraint
WorkshopId Integer Primary key
Title Varchar(50)
DateWorkshop Date
NumSpeakers Integer
Insert two rows into the above table.

10. Which keyword is used to eliminate redundant data?

11. Write down SQL statement for creating table LIBRARY having following attributes:
i) BOOK_ID – an integer, primary key of the table
ii) BOOK_TITLE –a string
iii) AUTHOR—a string
iv)QTY – an integer
v) PRICE – a real number
12. Write the SQL query to create the following table:

13. Write the SQL queries based on the following on the following tables:
ISD/XI/IP Worksheet/2019-20 Page 1 of 7
 i) Write SQL Query to display ProductName and Price for all products whose Price is in the
range 50 to 150.
ii) Write SQL Query to display details of product whose manufacturer is either XYZ or ABC.
iii) Write SQL query to display ProductName, Manufacturer and Price for all products that are
not given any discount.
iv) Write SQL query to display ClientName, City, P_ID and ProductName for all clients whose
city is Delhi.
v) Which column is used as Foreign Key and name the table where it has been used as Foreign key.

14. Create table student with appropriate data types:

Based on above table student answer the following queries:

i) Display names and Roll_No of students whose name starts with ―S‖.
ii) Display records of students having FEE are less than 3000.
iii) Display records whose Fee is from 2500 to 4000.
15. Write command to view the structure of table.
16. Differentiate between DDL and DML statements with suitable examples.
17. Name two open source database software.
18. Find the output of the queries based on the following relation STUDENT:

i) SELECT NAME, GRADE FROM STUDENT WHERE MARKS BETWEEN 40 AND 75;

ii) SELECT * FROM STUDENT WHERE GRADE = ‗A+‘;

19. Differentiate between primary key and alternate key with the help of an example.
ISD/XI/IP Worksheet/2019-20 Page 2 of 7
20. Consider the following relation MobileMaster& MobileStock:

Write the SQL query for questions from (i) to (iv) & write the output of SQL command for questions
from (v) to (vii) given below:
i) Display the Mobile company, name & price in descending order of their Manufacturing date.
ii) List the details of mobile whose name starts with "S" or ends with "a".
iii) Display the Mobile supplier & quantity of all mobiles except "MB003".
iv) Display a list showing the name of mobile company having price between 3000 & 5000.
v) SELECT M_Id, SUM(M_Qty) FROM MobileStock;
vi) SELECT MAX(M_Date), MIN(M_Date) FROM MobileMaster;
vii) SELECT AVG(M_Price) FROM MobileMaster;

21. Consider the following tables FACULTY and COURSES. Write SQL commands for the statements
(i) to (iv) and give outputs for SQL queries (v) to (viii):

ISD/XI/IP Worksheet/2019-20 Page 3 of 7

 i) To display details of those faculties whose date of joining is before 31-12-2001.
ii) To display the details of courses whose fees is in the range of 15000 to 50000(both values
included).
iii) To increase the fees of Dream Weaver course by 500.
iv) To display F_ID,FNAME,CNAME of those faculties who charged more than 15000 as fees.
v) SELECT COUNT(DISTINCT F_ID) FROM COURSES ;
vi) SELECT MIN(SALARY) FROM FACULTY;
vii) SELECT SUM(FEES) FROM COURSES ;
viii) SELECT FNAME, LNAME FROM FACULTY WHERE LNAME LIKE ―M%‖ ;
22. From the below table identify the most suitable field for Primary key and candidate key. Also
mention its cardinality and degree:

S.NO NAME STIPEND SUBJECT AVERAGE DIV.

1 KARAN 400 PHYSICS 68 I
2 DIWAKAR 450 COMP. Sc. 68 I
3 DIVYA 300 CHEMISTRY 62 I
23. Write SQL commands for (i) to (vi) and write the output for (vii) on the basis of teacher relation
given below:

(i) To show all information about the teacher of History department.

(ii) To list the name of female teachers who are in Hindi department.
(iii) To list the names of all the teachers with their date of joining in ascendingorder.
(iv) To display Teachers Name, Salary, Age for male teachers only.
(v) To count the number of teachers with age > 23.
(vi) To insert a new row in the Teacher table with the following
data. 9, ―Raja‖, 26, ―Computer‖, {13/05/95}, 2300, "M"
(vii) Give the output of the following SQL statements :
 SELECT COUNT (DISTINCT department) FROM teacher;
 SELECT MAX(age) FROM teacher WHERE sex = "F";
 SELECT AVG(salary) FROM teacher WHERE sex = "M";
 SELECT SUM(salary) FROM teacher WHERE dateofjoin<{12,07/96};

ISD/XI/IP Worksheet/2019-20 Page 4 of 7

24. Write SQL commands for (i) to (iv) and write the outputs for (v) on the basis of table CLUB.

(i) To show all information about the swimming coaches in the club.
(ii) To list name of all coaches with their date of appointment (DATEOFAPP) in
descending order.
(iii) To display a report, showing coachname, pay, age and bonus (15% of pay) for all
the coaches.
(iv) To insert a new row in the CLUB table with the following data :
11, "PRAKASH", 37, "SQUASH", {25/02/98}, 2500, "M"
(v) Give the output of following SQL statements :
 SELECT COUNT (DISTINCT sports) FROM club;
 SELECT MIN (age) FROM club WHERE sex = "F";
 SELECT AVG(pay) FROM club WHERE sports ="KARATE";
 SELECT SUM(pay) FROM club WHERE dateofapp> {31/01/98};
25. Write SQL commands for (i) to (vi) and write the output for (vii) for Lab relations :

(i) To select the Itemname purchased after 31/10/97.

(ii) To list the ItemName, which are within the Warranty period till present date.
(iii) To list the ItemName in ascending order of the date of purchase
(iv) To display ItemName, CostPerltem 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.
(vii) Give the output of the following SQL command :
 SELECT MIN (DISTINCT quantity) FROM lab;
 SELECT MIN(warranty) FROM lab WHERE quantity = 2;
 SELECT SUM(costperitem) FROM lab WHERE quantity >2;
 SELECT AVG(csostperitem) FROM lab WHERE dateofpurchase<{1/l/1999};

26. Given the following tables for a database LIBRARY,write SQL commands for (i) to (v) and write the
output for (vi)

ISD/XI/IP Worksheet/2019-20 Page 5 of 7

 (i) To show Book name, Author name and Price of books of EPB publishers
(ii) To list the names of books of Fiction Type
(iii) To display the names and price of the books in descending order of their price
(iv) To increase the price of all books of First Publ. by 50
(v) To insert a new row in the table Issued having the following data :―F0002‖, 4
(vi) Give the output of the following queries based on the above tables :
 SELECT COUNT(DISTINCT Publishers) FROM Books;
 SELECT SUM(Price) FROM Books WHERE Quantity >5;
 SELECT Book_Name, Author_Name FROM Books WHERE Price<500;
 SELECT COUNT (*) FROM Books;
27. Consider the following tables. Write SQL commands for the statements (i) to (iv)and give outputs for
SQL queries (v).

ISD/XI/IP Worksheet/2019-20 Page 6 of 7

 Write SQL commands for the following statements :
(i) To display details of all the items in the Store table in ascending order of LastBuy.
(ii) To display ItemNo and Item name of those items from Store table Whose Rate is
more than 15 Rupees.
(iii) To display the details of those items whose Suppliers code (Scode) is 22 or Quantity
in Store (Qty) is more than 110 from the table Store.
(iv) To display Minimum Rate of items for each Supplier individually as per Scode from
the table Store.
(v) Give the output of the following SQL queries:
 SELECT COUNT (DISTINCT Scode) FROM Store;
 SELECT Rate*Qty FROM Store WHERE ItemNO = 2004;
 SELECT Item, Sname FROM Store Item No = 2006 ;
 SELECT MAX (LastBuy) FROM Store ;
28. Consider the following tables SCHOOL and ADMIN. Write SQL commands for the statements (i) to(iv)
and give outputs for SQL queries (v) to (vii).

(i) To display TEACHERNAME, PERIODS of all teachers whose periods less than 25.
(ii) To display TEACHERNAME, CODE and DESIGNATION from tables SCHOOL
and ADMIN whose gender is male.
(iii) To display number of teachers in each subject wise.
(iv) To display CODE, TEACHERNAME and SUBJECT of all teachers who have joined the
school after 01/01/1999.
(v) SELECT MAX (EXPERIENCE) FROM SCHOOL;
(vi) SELECT TEACHERNAME, SUBJECT FROM SCHOOL WHERE DESIGNATION=
‗COORDINATOR‘;
(vii) SELECT COUNT (DISTINCT SUBJECT) FROM SCHOOL;

ISD/XI/IP Worksheet/2019-20 Page 7 of 7