CBSE Tution
PERIODIC TEST - SQL
Class 12 - Computer Science
Time Allowed: 1 hour and 30 minutes Maximum Marks: 40
1. Consider the following table namely Employee: [1]
Employee_id Name Salary
1001 Misha 6000
1009 Khushi 4500
1018 Japneet 7000
Which of the names will not be displayed by the below-given query?
SELECT name FROM employee WHERE employee_id>1009;
a) Misha, Japneet b) Misha, Khushi
c) Khushi, Japneet d) Japneet
2. Consider the following SQL statement. What type of statement is this? [1]
INSERT INTO instructor VALUES (10211, 'Shreya', 'Biology', 66000);
a) DDL b) DML
c) Procedure d) DCL
3. In SQL, which command is used to SELECT only one copy of each set of duplicable rows? [1]
a) SELECT DIFFERENT b) All of these
c) SELECT UNIQUE d) SELECT DISTINCT
4. Which clause is used with "aggregate functions"? [1]
a) GROUP BY b) WHERE
c) SELECT d) Both GROUP BY and WHERE
5. What is the full form of DDL? [1]
a) Data Definition Language b) Dynamic Data Language
c) Detailed Data Language d) Data Derivation Language
6. How will you select the content of the columns named LastName and FirstName from the Employee table. [1]
7. What do you understand by the terms Candidate Key and Cardinality of relation in the relational database [1]
8. What is an Alternate Key? [1]
9. What is the difference between WHERE and HAVING clause? [1]
10. What is wrong with the following statement? [1]
SELECT * FROM Employee
WHERE grade = NULL;
Write the corrected form of the above SQL statement.
11. Charu has to create a database named MYEARTH in MySQL. [2]
1/5
Pavithra Karthik : 9840240410
She now needs to create a table named CITY in the database to store the records of various cities across the
globe. The table CITY has the following structure.
Table: CITY
Field Name Data Type Remarks
CITYCODE CHAR(5) Primary Key
CITYNAME CHAR (30)
SIZE INTEGER
AVGTEMP INTEGER
POPULATIONRATE INTEGER
POPULATION INTEGER
Help her to complete the task by suggesting appropriate SQL commands.
12. Gopi Krishna is using a table Employee. It has the following columns: [2]
Code, Name, Salary, Deptcode
He wants to display maximum salary departmentwise. He wrote the following command :
SELECT Deptcode, Max(Salary) FROM Employee ;
But he did not get the desired result.
Rewrite the above query with necessary changes to help him get the desired output.
13. Write the output for SQL queries (i) to (iii), which are based on the table CARDEN. [2]
TABLE: CARDEN
Ccode CarName Make Color Capacity Charges
501 A-star Suzuki RED 3 14
503 Indigo Tata SILVER 3 12
502 Innova Toyota WHITE 7 15
509 SX4 Suzuki SILVER 4 14
510 C-Class Mercedes RED 4 35
i. SELECT COUNT( DISTINCT Make) FROM CARDEN;
ii. SELECT CarName FROM CARDEN WHERE Capacity = 4;
14. Sonal needs to display name of teachers, who have "0" as the third character in their name. She wrote the [2]
following query.
SELECT NAME FROM TEACHER WHERE NAME = "$$0?";
But the query isn't producing the result. Identify the problem.
15. Differentiate between char(n) and varchar(n) data types with respect to databases. [2]
16. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), Which are based on the table. [6]
Table: CUSTOMER
CNO CNAME ADDRESS
101 Richa Jain Delhi
2/5
Pavithra Karthik : 9840240410
102 Surbhi Sinha Chennai
103 Lisa Thomas Bangalore
104 Imran Ali Delhi
105 Roshan Singh Chennai
Table: TRANSACTION
TRNO CNO AMOUNT TYPE DOT
T001 101 1500 Credit 2017-11-23
T002 103 2000 Debit 2017-05-12
T003 102 3000 Credit 2017-06-10
T004 103 12000 Credit 2017-09-12
T005 101 1000 Debit 2017-09-05
i. To display details of all transactions of TYPE Credit from Table TRANSACTION.
ii. To display the CNO and AMOUNT of all Transactions done in the month of September 2017 from table
TRANSACTION.
iii. To display the last dale of transaction (DOT) front the table TRANSACTION for the customer having CNO
as 103.
iv. To display all CNO CNAME and DOT (date of transaction) of those CUSTOMERS fron, tables
CUSTOMER and TRANSACTION who have done transactions more than or equal to 2000.
v. SELECT COUNT(*), AVG (AMOUNT) FROM TRANSACTION WHERE DOT > = '2017-06-01'
vi. SELECT CNO, COUNT(*), MAX (AMOUNT) FROM TRANSACTION GROUP BY CNO HAVING
COUNT (*)> 1
vii. SELECT CNO, CNAME FROM CUSTOMER WHERE ADDRESS NOT IN ('DELHI', BANGALORE )
viii. SELECT DISTINCT CNO FROM TRANSACTION
17. Write SQL queries for (i) to (vii) on the basis of table ITEMS and TRADERS: [6]
Table: ITEMS
ICODE INAME QTY PRICE COMPANY TCODE
1001 DIGITAL PAD 12i 120 11000 XENITA T01
1006 LED SCREEN 40 70 38000 SANTORA T02
1004 CAR GPS SYSTEM 50 21500 GEOKNOW T01
1003 DIGITAL CAMERA 12X 160 8000 DIGICLICK T02
1005 PEN DRIVE 32 GB 600 1200 STOREHOME T03
Table: TRADERS
TCode TName City
101 ELECTRONIC SALES MUMBAI
103 BUSY STORE CORP DELHI
102 DISP HOUSE INC CHENNAI
3/5
Pavithra Karthik : 9840240410
i. To display the details of all the items in ascending order of item names (i.e., INAME).
ii. To display item name and price of all those items, whose price is in the range of 10000 and 22000 (both
values inclusive).
iii. To display the number of items, which are traded by each trader. The expected output of this query should be:
T01 2 T02 2 T03 1
iv. To display the price, item name and quantity (i.e., qty) of those items which have quantity more than 150.
v. To display the names of those traders, who are either from DELHI or from MUMBAI.
vi. To display the names of the companies and the names of the items in descending order of company names.
vii. Obtain the outputs of the following SQL queries based on the data given in tables ITEMS and TRADERS
above.
a. SELECT MAX (PRICE), MIN (PRICE) FROM ITEMS;
b. SELECT PRICE*QTY FROM ITEMS WHERE CODE=1004;
c. SELECT DISTINCT TCODE FROM ITEMS;
d. SELECT INAME, TNAME FROM ITEMS I, TRADERS T WHERE [Link]=[Link] AND
QTY<100;
Question No. 18 to 21 are based on the given text. Read the text carefully and answer the questions: [4]
Consider the following tables GAMES and PLAYER:
Table: GAMES
GCode Game Name Type Number Prize Money Schedule Date
101 Carom Board Indoor 2 5000 23-Jan-2004
102 Badminton Outdoor 2 12000 12-Dec-2003
103 Table Tennis Indoor 4 8000 14-Feb-2004
105 Chess Indoor 2 9000 01-Jan-2004
108 Lawn Tennis Outdoor 4 25000 19-Mar-2004
Table: PLAYER
PCode Name GCode
1 Nabi Ahmad 101
2 Ravi Sahai 108
3 Jatin 101
4 Nazneen 103
18. Identify the primary key and foreign key in these tables.
19. Write SQL command to display details of those GAMES which are having PrizeMoney more than 7000.
20. Write SQL command to display sum of PrizeMoney for each type of GAMES.
21. Give the output of the following SQL queries:
i. SELECT MAX(ScheduleDate), MIN (ScheduleDate) FROM GAMES;
ii. SELECT Name, GameName FROM GAMES G, PLAYER P WHERE ([Link]= [Link] AND
[Link]>10000);
4/5
Pavithra Karthik : 9840240410
Question No. 22 to 25 are based on the given text. Read the text carefully and answer the questions: [4]
Consider the following table Persons and answer the questions that follows
PID LastName FirstName Address City
101 Hansen Ola Timoteivn 10 Sandnes
102 Svendson Tove Borgvn 23 Sandnes
103 Petterson Kari Storgt 2 Stavanger
104 Nilsen Johan Bakken 2 Stavanger
105 Tjessem Jakob NULL NULL
22. Write the degree and cardinality of table Persons.
23. Which command is used to show the content of table Persons?
24. To display the detail of persons whose city is Sandnes.
25. Insert the row with value (106, John, Miller, NULL, Sandnes)
5/5
Pavithra Karthik : 9840240410