SRI VIJAY VIDYASHRAM SR. SEC.
SCHOOL-DPI
FIRST 25%
Class 12 - Computer Science
Time Allowed: 3 hours Maximum Marks: 60
Section A
1. Which statement of SQL provides statements for manipulating the database objects? [1]
a) DCL b) TCL
c) DML d) DDL
2. Which of the following is not a valid DML command in SQL? [1]
a) INSERT b) DELETE
c) ALTER d) UPDATE
3. Which of the following is not a valid datatype in SQL? [1]
a) DECIMAL b) DATE
c) STRING d) CHAR
4. Which of the following sublanguages of SQL is used to define the structure of the relation, deleting relations and [1]
relating schemas?
a) Relational Schema b) DDL (Data Definition Language)
c) DML (Data Manipulation Language) d) Query
5. Fill in the blank: [1]
________ clause is used with SELECT statement to display data in a sorted form with respect to a specified
column.
a) HAVING b) WHERE
c) ORDER BY d) DISTINCT
6. Consider the following SQL statement. What type of statement is this? [1]
SELECT * FROM employee
a) DDL b) DCL
c) Integrity constraint d) DML
7. Aggregate functions are also known as [1]
a) group functions b) Add function
c) group method d) sum function
8. Which of the following operator displays a record if either the first condition or the second condition is true? [1]
a) BETWEEN b) OR
c) AMONG d) AND
9. Which of the following command is used to remove the table definition and all data? [1]
1/7
a) Choose b) Drop
c) Create d) Select
10. The operation whose result contains all pairs of tuples from the two relations, regardless of whether their [1]
attribute values match.
a) Intersection b) Set difference
c) Join d) Cartesian product
11. Which of the following is a SQL aggregate function? [1]
a) JOIN b) LEN
c) AVG d) LEFT
12. Which is the subset of SQL commands used to manipulate database structures, including tables? [1]
a) Data Differential Language b) Both Data Definition Language (DDL)
and Data Manipulation Language (DML)
c) Data Definition Language (DDL) d) Data Manipulation Language (DML)
13. Fill in the blank: [1]
________ statement of SQL is used to insert new records in a table.
a) INSERT b) CREATE
c) ALTER d) UPDATE
14. Which of the following function returns the total number of values? [1]
a) MIN b) MAX
c) COUNT d) SUM
15. Which of the following join gives the intersection of two tables? [1]
a) Inner join b) Outer join
c) Equi join d) Fuller join
16. Which of the following sublanguages of SQL is used to query information from the database and to insert tuples [1]
into, delete tuples from, and modify tuples in the database?
a) DDL (Data Definition Language) b) Query
c) Relational Schema d) DML (Data Manipulation Language)
17. Consider the following query : [1]
SELECT name, instructor name, course_id FROM instructor;
To display the field heading course with a different heading as id, which keyword must be used here to rename
the field name?
a) From b) Join
c) As d) Rename
18. Which operator tests the column for the absence of data (i.e., NULL value)? [1]
a) IS NULL operator b) NOT operator
c) IS EMPTY operator d) EXISTS operator
2/7
19. Which keyword can be used to return only different values in a particular column or a whole table? [1]
a) BETWEEN b) WHERE
c) ALL d) DISTINCT
20. Fill in the blank. [1]
________ is a number of tuples in a relation.
a) Domain b) Cardinality
c) Attribute d) Degree
21. 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
22. With SQL, how do you select all the records from a table named "Students" where the value of the column [1]
"FirstName" ends with an "a"?
a) SELECT * FROM Students WHERE b) SELECT * FROM Students WHERE
FirstName LIKE '%a' FirstName = '%a%'
c) SELECT * FROM Students WHERE d) SELECT * FROM Students WHERE
FirstName LIKE 'a%' FirstName = 'a'
23. Which operator performs pattern matching? [1]
a) LIKE operator b) BETWEEN operator
c) INTO operator d) EXISTS operator
24. Consider following SQL statement. What type of statement is this? [1]
CREATE TABLE employee (name VARCHAR, id INTEGER)
a) Integrity constraint b) DML
c) DDL d) DCL
25. Which of the following commands in SQL is used to add a new record into a table? [1]
a) ADD b) NEW
c) UPDATE d) INSERT
26. Name the various data models available for database system. [1]
27. Name three types of users who play an important role in terms of designing, maintaining and utilising a [1]
database.
28. Name the various types of data that a database can contain. [1]
29. List the major components of a database system. [1]
30. What is a database? [1]
Section B
31. Consider the following table PLAYER: [2]
Table : PLAYER
PNO NAME SCORE
P1 RISHABH 52
3/7
P2 HUSSAIN 45
P3 ARNOLD 23
P4 ARNAV 18
P5 GURSHARAN 42
a. Identify and write the name of the most appropriate column from the given table PLAYER that can be used
as a Primary key.
b. Define the term Degree in relational data model. What is the Degree of the given table PLAYER?
32. 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 COUNT(*) Make FROM CARDEN;
iii. SELECT CarName FROM CARDEN WHERE Capacity = 4;
33. i. A table, ITEM has been created in a database with the following fields [2]
ITEMCODE, ITEMNAME, QTY, PRICE
Give the SQL command to add a new field, DISCOUNT (of type Integer) to the ITEM table.
ii. Categorize following commands into DDL and DML commands?
INSERT INTO, DROP TABLE, ALTER TABLE, UPDATE...SET
34. Why does the need of the database system arise? Give the reason. [2]
35. Mention atleast three limitations of DBMS. [2]
36. Mention the various advantages of DBMS. [2]
Section C
37. i. Write two examples of DBMS software. [3]
ii. What is meant by NULL value in MySQL?
iii. Table 'Club' has 4 rows and 3 columns. Table 'Member' has 2 rows and 5 columns. What will be the
cardinality of the Cartesian product of them?
38. Write the outputs of the SQL queries (i) to (iii) based on the relations CUSTOMER and TRANSACTION given [3]
below:
Table : CUSTOMER
ACNO NAME GENDER BALANCE
C1 RISHABH M 15000
C2 AAKASH M 12500
4/7
C3 INDIRA F 9750
C4 TUSHAR M 14600
C5 ANKITA F 22000
Table : TRANSACTION
ACNO TDATE AMOUNT TYPE
C1 2020-07-21 1000 DEBIT
C2 2019-12-31 1500 CREDIT
C3 2020-01-01 2000 CREDIT
i. SELECT MAX(BALANCE), MIN(BALANCE)FROM CUSTOMER
WHERE GENDER = 'M';
ii. SELECT SUM(AMOUNT), TYPE FROM TRANSACTION
GROUP BY TYPE;
iii. SELECT NAME, TDATE, AMOUNT
FROM CUSTOMER C, TRANSACTION T
WHERE C.ACNO = T.ACNO AND TYPE = 'CREDIT';
Section D
39. Given the following family relation. Write SQL commands for questions (i) to (v) based on the table FAMILY [4]
TABLE: FAMILY
No. Name Female Members Male Members Income Occupation
1 Mishra 3 2 7000 Service
2 Gupta 4 1 50000 Business
3 Khan 6 3 8000 Mixed
4 Chaddha 2 2 25000 Business
5 Yadav 7 2 20000 Mixed
6 Joshi 3 2 14000 Service
7 Maurya 6 3 5000 Farming
8 Rao 5 2 10000 Service
i. To select all the information of family, whose Occupation is Service.
ii. To list the name of family, where female members are more than 3.
iii. To list all names of family with income in ascending order.
iv. To count the number of family, whose income is less than 10000.
40. Consider the following tables CARDEN and CUSTOMER and answer the following parts of this question : [4]
Table: CARDEN
Ccode CarName Make Color Capacity Charges
501 A-Star Suzuki RED 3 14
503 Indigo Tata SILVER 3 12
5/7
502 Innova Toyota WHITE 7 15
509 SX4 Suzuki SILVER 4 14
510 C Class Mercedes RED 4 35
Table: CUSTOMER
CCode Cname Ccode
1001 Hemant Sahu 501
1002 Raj Lai 509
1003 Feroza Shah 503
1004 Ketan Dhal 502
Give the output of the following SQL queries :
i. SELECT COUNT (DISTINCT Make) FROM CARDEN;
ii. SELECT MAX(Charges), MIN(Charges) FROM CARDEN;
iii. SELECT COUNT(*), Make FROM CARDEN;
iv. SELECT CarName FROM CARDEN WHERE Capadty = 4;
41. Consider the following tables ACTIVITY and COACH and answer (a) and (b) parts of this question: [4]
Table: ACTIVITY
ACode ActivityName Stadium ParticipantsNum PrizeMoney ScheduleDate
1001 Relay 100 × 4 Star Annex 16 10000 23-Jan-2004
1002 High Jump Star Annex 10 12000 12-Dec-2003
1003 Shot Put Super Power 12 8000 14-Feb-2004
1005 Long Jump Star Annex 12 9000 01-Jan-2004
1008 Discuss Throw Super Power 10 15000 19-Mar-2004
Table: COACH
PCode Name ACode
1 Ahmad Hussain 1001
2 Ravinder 1008
3 Janila 1001
4 Naaz 1003
a. Write SQL commands for the following statements:
i. To display the names of all activities with their Acodes in descending order.
ii. To display sum of PrizeMoney for the Activities played in each of the Stadium separately.
iii. To display the coach's name and Acodes in ascending order of Acode from the table Coach.
iv. To display the content of the Activity table whose schedule date earlier than 01-01-2004 in ascending
order of Participants Num.
b. Give the output of the following SQL queries:
6/7
i. SELECT COUNT (DISTINCT Participants Num) FROM ACTIVITY;
ii. SELECT MAX (Schedule Date), Min (Schedule Date) FROM ACTIVITY;
iii. SELECT Name, Activity Name FROM ACTIVITY A, COACH C WHERE A. Acode=C. Acode AND
A.Participants Num=10;
iv. SELECT DISTINCT Acode FROM COACH;
7/7
