Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

1 Which SQL command can change the cardinality of an existing relation?

a) Insert b) Delete c) Both a) &b) d) Drop
2 In SQL, a relation consists of 5 columns and 6 rows. If 2 columns and 3 rows
are added to the existing relation, what will be the updated degree of a relation?
a) Degree: 7 b) Degree: 8
c) Degree: 9 d) Degree: 6
3 Which SQL command is used to remove a column from a table in MySQL?
a) UPDATE b) ALTER c) DROP d) DELETE
4 Which SQL command can change the degree of an existing relation?
5 What will be the output of the query? SELECT * FROM products WHERE
product_name LIKE 'App%';
(A) Details of all products whose names start with 'App'
(B) Details of all products whose names end with 'App'
(C) Names of all products whose names start with 'App'
(D) Names of all products whose names end with 'App'
6 In which datatype the value stored is padded with spaces to fit the specified
length.
(A) DATE (B) VARCHAR (C) FLOAT (D) CHAR
7 Which aggregate function can be used to find the cardinality of a table?
(A) sum() (B) count() (C) avg() (D) max()
8 While creating a table, which constraint does not allow insertion of duplicate
values in the table?
(A) UNIQUE (B) DISTINCT
(C) NOT NULL (D) HAVING

9 Which of the following is a DML command in SQL?

(A) UPDATE (C) ALTER (B) CREATE (D) DROP

10 Which aggregate function in SQL displays the number of values

in the specified column ignoring the NULL values?
(A) Len () (B) count () (C) number () (D) num ( )

11 In MYSQL, which type of value should not be enclosed within

quotation marks?
(A) DATE (B) VARCHAR (C) FLOAT (D) CHAR

12 State True or False:

If table A has 6 rows and 3 columns, and table B has 5 rows
and 2 columns, the Cartesian product of A and B will have 30 rows and
5 columns.

13 In a table in MYSQL database, an attribute A of datatype varchar(20)

has the value “Keshav”. The attribute B of datatype char(20) has
value “Meenakshi”. How many characters are occupied by attribute
A and attribute B?
a. 20,6 b. 6,20 c. 9,6 d. 6,9
14 In MYSQL database, if a table, Alpha has degree 5 and cardinality 3,
and another table, Beta has degree 3 and cardinality 5, what will be
the degree and cardinality of the Cartesian product of Alpha and
Beta?
a. 5,3 b. 8,15 c. 3,5 d. 15,8

1
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

15 Which of the following statements is FALSE about keys in a

relational database?
a. Any candidate key is eligible to become a primary key.
b. A primary key uniquely identifies the tuples in a relation.
c. A candidate key that is not a primary key is a foreign key.
d. A foreign key is an attribute whose value is derived from the
primary key of another relation.
16 The SELECT s ta te m e nt when combined with --------------
clause, returns records without repetition.
(a) DISTINCT (b) DESCRIBE
(c) UNIQUE (d) NULL
17 In SQL, the aggregate function which will display the cardinality of
the-------------------
(a) sum()
(b) count (*)
(c) avq ()
(d) sum(*)

18 Which of the following is not a DDL command in SQL?

(a) DROP (b) CREATE (c) TJPDATE (d) ALTER

19 The primary key is selected from the set of ___________ .

(A) composite keys (B) alternate keys
(C) candidate keys (D) foreign keys
20 In SQL, which command will be used to add a new record in a table?
(A) UPDATE (B) ADD (C) INSERT (D) ALTER TABLE
21 Mr. Ravi is creating a field that contains alphanumeric values and fixed
lengths. Which MySQL data type should he choose for the same?
(A) VARCHAR (B) CHAR (C) LONG (D) NUMBER
22 Consider the given SQL Query:
SELECT department, COUNT(*) FROM employees HAVING COUNT(*) > 5
GROUP BY department;
Saanvi is executing the query but not getting the correct output. Write the
correction.
23 Assertion (A): A SELECT command in SQL can have both WHERE and HAVING
clauses.
Reasoning (R): WHERE and HAVING clauses are used to check conditions,
therefore, these can be used interchangeably.
24 Assertion (A): A primary key must be unique and cannot have NULL values.
Reasoning (R): The primary key uniquely identifies each row in the table.
25 Assertion (A) We can retrieve records from more than one table in
MYSQL.

Reason (R) Foreign key is used to establish a relationship between two

tables.

2
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

Short answer Questions

1 Write suitable commands to do the following in MySQL. 2
I. View the table structure.
II. Create a database named SQP
2 Differentiate between drop and delete query in SQL with a suitable example. 2
3 What constraint should be applied on a table column so that duplicate values are
not allowed in that column, but NULL is allowed.
4 What constraint should be applied on a table column so that NULL is not allowed
in that column, but duplicate values are allowed.
5 Write an SQL command to remove the Primary Key constraint from a table,
named MOBILE. M_ID is the primary key of the table.
6 Write an SQL command to make the column M_ID the Primary Key of an already
existing table, named MOBILE.
7 Ms. Shalini has just created a table named “Employee” containing columns 2
Ename, Department and Salary.
After creating the table, she realized that she has forgotten to add a primary key
column in the table. Help her in writing an SQL command to add a primary key
column EmpId of integer type to the table Employee.
Thereafter, write the command to insert the following record in the table:
EmpId- 999
Ename- Shweta
Department: Production
Salary: 26900
8 Zack is working in a database named SPORT, in which he has created a table 2
named “Sports” containing columns SportId, SportName, no_of_players, and
category.
After creating the table, he realized that the attribute, category has to be deleted
from the table and a new attribute TypeSport of data type string has to be added.
This attribute TypeSport cannot be left blank. Help Zack write the commands to
complete both the tasks.
9 Ms. Veda created a table named Sports in a MySQL database, 2
containing columns Game_id, P_Age and G_name.
After creating the table, she realized that the attribute, Category has to
be added. Help her to write a command to add the Category column.
Thereafter, write the command to insert the following record in the table :
Game_id :G42
P_Aqe :Above 18
G_name : Chess
Category : Senior

10 Write the SQL commands to perform the following tasks: 2

I. View the list of tables in the database, Exam.
II. View the structure of the table, Term1

11 Mr. Atharva is given a task to create a database, Admin. He has to create a table, 2
users in the database with the following columns:
User_id – int
User_name – varchar(20)
Password – varchar(10)
Help him by writing SQL queries for both tasks.

3
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

12 Ms. Rita is a database administrator at a school. She is working on the table, 2

student containing the columns like Stud_id, Name, Class and Stream. She has
been asked by the Principal to strike off the record of a student named Rahul with
student_id as 100 from the school records and add another student who has been
admitted with the following details:
Stud_id – 123
Name – Rajeev
Class – 12
Stream – Science
Help her by writing SQL queries for both tasks.

Answers

commands to do the following in MySQL.

I. View the table structure.
DESC table_name;

II. Create a database named SQP.

CREATE DATABASE SQP;

2. Differentiate between DROP and DELETE query in SQL with a suitable example.

• DROP: Deletes the entire table structure and data.

DROP TABLE student;

• DELETE: Deletes records (rows) from a table but keeps the structure.
DELETE FROM student WHERE id=101;

3. What constraint should be applied on a table column so that duplicate values are not
allowed in that column, but NULL is allowed.
Answer: UNIQUE

4. What constraint should be applied on a table column so that NULL is not allowed in
that column, but duplicate values are allowed.
Answer: NOT NULL

5. Write an SQL command to remove the Primary Key constraint from a table, named
MOBILE. M_ID is the primary key of the table.
ALTER TABLE MOBILE DROP PRIMARY KEY;

6. Write an SQL command to make the column M_ID the Primary Key of an already
existing table, named MOBILE.
ALTER TABLE MOBILE ADD PRIMARY KEY (M_ID);

7. Ms. Shalini has just created a table “Employee”...

Add a primary key column:
ALTER TABLE Employee ADD EmpId INT PRIMARY KEY;

Insert record:
INSERT INTO Employee (EmpId, Ename, Department, Salary)
VALUES (999, 'Shweta', 'Production', 26900);

4
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

8. Zack is working in a database named SPORT...

Delete category column:
ALTER TABLE Sports DROP COLUMN category;

Add TypeSport column (not NULL):

ALTER TABLE Sports ADD TypeSport VARCHAR(30) NOT NULL;

9. Ms. Veda created a table “Sports”...

Add Category column:
ALTER TABLE Sports ADD Category VARCHAR(20);

Insert record:
INSERT INTO Sports (Game_id, P_Age, G_name, Category)
VALUES ('G42', 'Above 18', 'Chess', 'Senior');

10. Write the SQL commands to perform the following tasks:

I. View the list of tables in the database, Exam.
SHOW TABLES FROM Exam;

II. View the structure of the table, Term1.

DESC Term1;

11. Mr. Atharva is given a task to create a database, Admin...

Create database:
CREATE DATABASE Admin;

Create table:
CREATE TABLE users (
User_id INT,
User_name VARCHAR(20),
Password VARCHAR(10)
);

12. Ms. Rita is a database administrator...

Delete Rahul’s record:
DELETE FROM student WHERE Stud_id=100;

Insert new student record:

INSERT INTO student (Stud_id, Name, Class, Stream)
VALUES (123, 'Rajeev', 12, 'Science');

5
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

Long Answer Questions

1 Consider the table SALES as given below: 4

sales_id customer_name product quantity_sold price

S001 John Doe Laptop 5 50000
S002 Jane Smith Smartphone 10 30000
S003 Michael Lee Tablet 3 15000
S004 Sarah Brown Headphones 7 2000
S005 Emily Davis Smartwatch 8 8000
S006 David Smartwatch 3 16000
S007 Mark Tablet 5 34000

Write the following queries:

I. To display the total quantity sold for each product whose total quantity sold
exceeds 12.
II. To display the records of SALES table sorted by Product name in
descending order.
III. To display the distinct Product names from the SALES table.
IV. To display the records of customers whose names end with the letter 'e'.

I. To display the total quantity sold for each product whose total quantity sold
exceeds 12.

SELECT product, SUM(quantity_sold) AS total_quantity

FROM SALES
GROUP BY product
HAVING SUM(quantity_sold) > 12;

II. To display the records of SALES table sorted by Product name in

descending order.

SELECT *
FROM SALES
ORDER BY product DESC;

III. To display the distinct Product names from the SALES table.

SELECT DISTINCT product

FROM SALES;

IV. To display the records of customers whose names end with the letter 'e'.

SELECT *
FROM SALES
WHERE customer_name LIKE '%e';

6
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

2 Consider the table SALES as given below:

sales_id customer_name product quantity_sold price

S001 John Doe Laptop 5 50000
S002 Jane Smith Smartphone 10 30000
S003 Michael Lee Tablet 3 15000
S004 Sarah Brown Headphones 7 2000
S005 Emily Davis Smartwatch 8 8000
S006 David Smartwatch 3 16000
S007 Mark Tablet 5 34000

Predict the output of the following:

I. SELECT * FROM Sales where product='Tablet';
II. SELECT sales_id, customer_name FROM Sales WHERE product LIKE 'S%';
III. SELECT COUNT(*) FROM Sales WHERE product in ('Laptop', 'Tablet');
IV. SELECT AVG(price) FROM Sales where product='Tablet';

I. SELECT * FROM Sales where product='Tablet';

Output:
sales_id customer_name product quantity_sold price
S003 Michael Lee Tablet 3 15000
S007 Mark Tablet 5 34000
II. SELECT sales_id, customer_name FROM Sales WHERE product LIKE
'S%';
Output:
sales_id customer_name
S002 Jane Smith
S005 Emily Davis
S006 David
III. SELECT COUNT(*) FROM Sales WHERE product in ('Laptop',
'Tablet');
Output:
3
IV. SELECT AVG(price) FROM Sales where product='Tablet';
Output:
24500

3 Pranav is managing a Travel Database and needs to access certain information 4

from the Hotels and Bookings tables for an upcoming tourism survey. Help him
extract the required information by writing the appropriate SQL queries as per the
tasks mentioned below:

Table: Hotels

7
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

H_ID Hotel_Name City Star_Rating

1 Hotel1 Delhi 5
2 Hotel2 Mumbai 5
3 Hotel3 Hyderabad 4
4 Hotel4 Bengaluru 5
5 Hotel5 Chennai 4
6 Hotel6 Kolkata 4

Table: Bookings
B_ID H_ID Customer_Name Check_In Check_Out
1 1 Jiya 2024-12-01 2024-12-05
2 2 Priya 2024-12-03 2024-12-07
3 3 Alicia 2024-12-01 2024-12-06
4 4 Bhavik 2024-12-02 2024-12-03
5 5 Charu 2024-12-01 2024-12-02
6 6 Esha 2024-12-04 2024-12-08
7 6 Dia 2024-12-02 2024-12-06
8 4 Sonia 2024-12-04 2024-12-08

I. To display a list of customer names who have bookings in any hotel of

'Delhi' city.
II. To display the booking details for customers who have booked hotels in
'Mumbai', 'Chennai', or 'Kolkata'.
III. To delete all bookings where the check-in date is before 2024-12-03.
IV. A. To display the Cartesian Product of the two tables. OR

B. To display the customer’s name along with their booked hotel’s name.

I. To display a list of customer names who have bookings in any hotel of

'Delhi' city.
SELECT Customer_Name
FROM Bookings B
JOIN Hotels H ON B.H_ID = H.H_ID
WHERE [Link] = 'Delhi';
Expected Output:
Customer_Name
Jiya

II. To display the booking details for customers who have booked hotels in
'Mumbai', 'Chennai', or 'Kolkata'.
SELECT B.*
FROM Bookings B
JOIN Hotels H ON B.H_ID = H.H_ID
WHERE [Link] IN ('Mumbai', 'Chennai', 'Kolkata');
Expected Output:
B_ID H_ID Customer_Name Check_In Check_Out
2 2 Priya 2024-12-03 2024-12-07
5 5 Charu 2024-12-01 2024-12-02
6 6 Esha 2024-12-04 2024-12-08
7 6 Dia 2024-12-02 2024-12-06

8
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

III. To delete all bookings where the check-in date is before 2024-12-03.
DELETE FROM Bookings
WHERE Check_In < '2024-12-03';
(This will delete bookings of Jiya, Alicia, Bhavik, Charu, and Dia.)

IV. A. To display the Cartesian Product of the two tables.

SELECT *
FROM Hotels, Bookings;
(This will display all combinations of rows from both tables.)
OR
IV. B. To display the customer’s name along with their booked hotel’s name.
SELECT B.Customer_Name, H.Hotel_Name
FROM Bookings B
JOIN Hotels H ON B.H_ID = H.H_ID;
Expected Output:
Customer_Name Hotel_Name
Jiya Hotel1
Priya Hotel2
Alicia Hotel3
Bhavik Hotel4
Charu Hotel5
Esha Hotel6
Dia Hotel6
Sonia Hotel4

4 Consider the table ORDERS as given below 4

O_Id C_Name Product Quantity Price

1001 Jitendra Laptop 1 12000
1002 Mustafa Smartphone 2 10000
1003 Dhwani Headphone 1 1500
Note: The table contains many more records than shown here.
A) Write the following queries:
I. To display the total Quantity for each Product, excluding Products with
total Quantity less than 5.
II. To display the orders table sorted by total price in descending order.
III. To display the distinct customer names from the Orders table.
IV. Display the sum of Price of all the orders for which the quantity is null.

A) Queries

I.

SELECT Product, SUM(Quantity) AS Total_Quantity

FROM Orders
GROUP BY Product
HAVING SUM(Quantity) >= 5;

II.

SELECT O_Id, C_Name, Product, Quantity, Price, (Quantity * Price)

AS Total_Price

9
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

FROM Orders
ORDER BY Total_Price DESC;

III.

SELECT DISTINCT C_Name

FROM Orders;

IV.

SELECT SUM(Price) AS Total_Price

FROM Orders
WHERE Quantity IS NULL;

OR
B) Write the output
I. Select c_name, sum(quantity) as total_quantity from orders group by
c_name;
II. Select * from orders where product like '%phone%';
III. Select o_id, c_name, product, quantity, price from orders where price
between 1500 and 12000;
IV. Select max(price) from orders;

B) Outputs

I.
C_Name total_quantity
Jitendra 1
Mustafa 2
Dhwani 1

II.
O_Id C_Name Product Quantity Price
1002 Mustafa Smartphone 2 10000
1003 Dhwani Headphone 1 1500

III.
O_Id C_Name Product Quantity Price
1001 Jitendra Laptop 1 12000
1002 Mustafa Smartphone 2 10000
1003 Dhwani Headphone 1 1500

IV.
12000

5 Saman has been entrusted with the management of Law University Database. He
needs to access some information from FACULTY and COURSES tables for a
survey analysis. Help him extract the following information by writing the desired
SQL queries as mentioned below.

10
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

Table: FACULTY
F_ID FName LName Hire_Date Salary
102 Amit Mishra 12-10-1998 12000
103 Nitin Vyas 24-12-1994 8000
104 Rakshit Soni 18-5-2001 14000
105 Rashmi Malhotra 11-9-2004 11000
106 Sulekha Srivastava 5-6-2006 10000
Table: COURSES

C_ID F_ID CName Fees

C21 102 Grid Computing 40000
C22 106 System Design 16000
C23 104 Computer Security 8000
C24 106 Human Biology 15000
C25 102 Computer Network 20000
C26 105 Visual Basic 6000

I. To display complete details (from both the tables) of those Faculties whose
salary is less than 12000.
II. To display the details of courses whose fees is in the range of 20000 to
50000 (both values included).
III. To increase the fees of all courses by 500 which have "Computer" in their
Course names.
VI. (A)To display names (FName and LName) of faculty taking System
Design.
OR
(B) To display the Cartesian Product of these two tables.

I. To display complete details (from both the tables) of those Faculties whose
salary is less than 12000.

SELECT F.*, C.*

FROM FACULTY F
JOIN COURSES C ON F.F_ID = C.F_ID
WHERE [Link] < 12000;

II. To display the details of courses whose fees is in the range of 20000 to
50000 (both values included).

SELECT *
FROM COURSES
WHERE Fees BETWEEN 20000 AND 50000;

III. To increase the fees of all courses by 500 which have "Computer" in their
Course names.

UPDATE COURSES
SET Fees = Fees + 500

11
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

WHERE CName LIKE '%Computer%';

IV (A). To display names (FName and LName) of faculty taking System

Design.

SELECT [Link], [Link]

FROM FACULTY F
JOIN COURSES C ON F.F_ID = C.F_ID
WHERE [Link] = 'System Design';

OR

IV (B). To display the Cartesian Product of these two tables.

SELECT *
FROM FACULTY, COURSES;

6 Suman has created a table named WORKER with a set of records to

maintain the data of the construction sites, which consists of WID,
WNAME, WAGE, HOURS, TYPE, and [Link] creating the table,
she entered data in it, which is as follows:
WID WNAME WAGE HOURS TYPE SITEID
WOl Ahmed J 1500 200 Unskilled 103
Wll Naveen S 520 100 Skilled 101
W02 Jacob B 780 95 Unskilled 101
Wl5 Nihal K 560 110 Semiskilled NULL
WlO Anju S 1200 130 Skilled 103

Based on the data given above, answer the following questions :

I. Write the SQL statement to display the names and wages of
those workers whose wages are between 800 and 1500.
II. Write the SQL statement to display the record of workers whose
SITEID is not known.
III. Write the SQL statement to display WNAME, WAGE and HOURS of
all those workers whose TYPE is ' Skilled ' .
IV. Write the SQL statement to change the WAGE to 1200 of the
workers where the TYPE is "Semiskilled".

I. To display the names and wages of those workers whose wages are between
800 and 1500.

SELECT WNAME, WAGE

FROM WORKER
WHERE WAGE BETWEEN 800 AND 1500;

II. To display the record of workers whose SITEID is not known.

SELECT *
FROM WORKER
WHERE SITEID IS NULL;

12
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

III. To display WNAME, WAGE and HOURS of all those workers whose
TYPE is 'Skilled'.

SELECT WNAME, WAGE, HOURS

FROM WORKER
WHERE TYPE = 'Skilled';

IV. To change the WAGE to 1200 of the workers where the TYPE is
"Semiskilled".

UPDATE WORKER
SET WAGE = 1200
WHERE TYPE = 'Semiskilled';

7 Suman has created a table named WORKER with a set of records to 4

maintain the data of the construction sites, which consists of WID,
WNAME, WAGE, HOURS, TYPE, and [Link] creating the table,
she entered data in it, which is as follows:
WID WNAME WAGE HOURS TYPE SITEID
WOl Ahmed J 1500 200 Unskilled 103
Wll Naveen S 520 100 Skilled 101
W02 Jacob B 780 95 Unskilled 101
Wl5 Nihal K 560 110 Semiskilled NULL
WlO Anju S 1200 130 Skilled 103

Considering the above given table WORKER , write the output on

execution of the following SQL commands :
I. SELECT WNAME , WAGE*HOURS FROM WORKER WHERE
SITEID = 103;
II. SELECT COUNT (DISTINCT TYPE ) FROM WORKER ;
III. SELECT MAX (WAGE ) , MIN (WAGE ) , TYPE FROM
WORKER GROUP BY TYPE ;
IV. SELECT WNAME , SITEID FROM WORKER WHERE
TYPE="Unskilled" ORDER BY HOURS ;

I.
WNAME WAGE*HOURS
Ahmed J 300000
Anju S 156000

II.
COUNT(DISTINCT TYPE)
3

III.
MAX(WAGE) MIN(WAGE) TYPE
1500 780 Unskilled

13
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

1200 520 Skilled

560 560 Semiskilled

IV.
WNAME SITEID
Jacob B 101
Ahmed J 103

8 Assume that you are working in the IT Department of a Creative Art

Gallery (CAG), which sells different forms of art creations like Paintings,
Sculptures etc. The data of Art Creations and Artists are kept in tables
Articles and Artists respectively. Following are few records from
these two tables :

Table : Articles
Code A_Code Article DOC Price
PL001 A0001 Painting 2018-10-19 20000
SC028 A0004 Sculpture 2021-01-15 16000
QL005 A0003 Quilling 2024-04-24 3000

Table : Artists
A_Code Name Phone Email DOB
A0001 Roy 595923 r@[Link] 1986-10-12
A0002 Ghosh 1122334 ghosh@[Link] 1972-02-05
A0003 Gargi 121212 Gargi@[Link] 1996-03-22
A0004 Mustafa 3333333 M@[Link] 2000-01-01

Note: The tables contain many more records than shown here.
DOC is Date of Creation of an Article.
As an employee of CAG, you are required to write the SQL queries for the
following:
I. To display all the records from the Articles table in
descending order of price.
II. To display the details of Articles which were created in the year
2020.
III. To display the structure of Artists table.
IV. (a)To display the name of all artists whose Article is Painting
through qui Join.
OR
(b) To display the name of all Artists whose Article is 'Painting'
through Natural Join.

14
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

I. To display all the records from the Articles table in descending order of
price.

SELECT * FROM Articles ORDER BY Price DESC;

II. To display the details of Articles which were created in the year 2020.

SELECT * FROM Articles WHERE YEAR(DOC) = 2020;

III. To display the structure of Artists table.

DESC Artists;

IV. (a) To display the name of all artists whose Article is Painting through
equi join.

SELECT [Link] FROM Artists

JOIN Articles ON Artists.A_Code = Articles.A_Code
WHERE [Link] = 'Painting';

OR

IV. (b) To display the name of all Artists whose Article is 'Painting' through
Natural Join.

SELECT Name FROM Artists NATURAL JOIN Articles

WHERE Article = 'Painting';

9 Consider the table CLUB given below and write the output of the SQL queries that 3
follow.
Table : CLUB
CID CNAME AGE GENDER SPORTS PAY DOAPP
5246 AMRITA 35 FEMALE CHESS 900 2006-03-27
4687 SHYAM 37 MALE CRICKET 1300 2004-04-15
1245 MEENA 23 FEMALE VOLLEYBALL 1000 2007-06-18
1622 AMRIT 28 MALE KARATE 1000 2007-09-05
1256 AMINA 36 FEMALE CHESS 1100 2003-08-15
1720 MANJU 33 FEMALE KARATE 1250 2004-04-10
2321 VIRAT 35 MALE CRICKET 1050 2005-04-30

I. SELECT COUNT(DISTINCT SPORTS) FROM CLUB;

II. SELECT CNAME, SPORTS FROM CLUB WHERE DOAPP<"2006-04-
30" AND CNAME LIKE "%NA";
III. SELECT CNAME, AGE, PAY FROM CLUB WHERE GENDER =
"MALE" AND PAY BETWEEN 1000 AND 1200;

15
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

Answer:
I. Output:
4

II. Output:
CNAME SPORTS
AMINA CHESS

III. Output:
CNAME AGE PAY
AMRIT 28 1000
VIRAT 35 1050

10 Consider the table Personal given below: 3

P_ID Name Desig Salary Allowance
P01 Rohit Manager 89000 4800
P02 Kashish Clerk NULL 1600
P03 Mahesh Superviser 48000 NULL
P04 Salil Clerk 31000 1900
P05 Ravina Superviser NULL 2100

Based on the given table, write SQL queries for the following:
(i) Increase the salary by 5% of personals whose allowance is known.
(ii) Display Name and Total Salary (sum of Salary and Allowance) of all
personals. The column heading ‘Total Salary’ should also be displayed.
(iii) Delete the record of personals who have salary greater than 25000
Answer:
(i) UPDATE Personal SET Salary = Salary + (Salary * 0.05)
WHERE Allowance IS NOT NULL;
(ii) SELECT Name, (Salary + Allowance) AS "Total Salary" FROM Personal;
(iii) DELETE FROM Personal WHERE Salary > 25000;

11 Consider the tables PRODUCT and BRAND given below: 4

Table: PRODUCT
PCode PName UPrice Rating BID

P01 Shampoo 120 6 M03

P02 Toothpaste 54 8 M02

P03 Soap 25 7 M03

P04 Toothpaste 65 4 M04

P05 Soap 38 5 M05

P06 Shampoo 245 6 M05

Table: BRAND

16
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

BID BName
M02 Dant Kanti
M03 Medimix
M04 Pepsodent
M05 Dove

Write SQL queries for the following:

(i) Display product name and brand name from the tables PRODUCT and
BRAND.
(ii) Display the structure of the table PRODUCT.
(iii) Display the average rating of Medimix and Dove brands
(iv) Display the name, price, and rating of products in descending order of rating.

Answer:

(i) Display product name and brand name from the tables PRODUCT and BRAND
SELECT PName, BName FROM PRODUCT
INNER JOIN BRAND ON [Link] = [Link];
(ii) Display the structure of the table PRODUCT
DESCRIBE PRODUCT;
(iii) Display the average rating of Medimix and Dove brands
SELECT BName, AVG(Rating) AS Avg_Rating
FROM PRODUCT
INNER JOIN BRAND
ON [Link] = [Link]
WHERE BName IN ('Medimix', 'Dove')
GROUP BY BName;
(iv) Display the name, price, and rating of products in descending order of rating
SELECT PName, UPrice, Rating
FROM PRODUCT
ORDER BY Rating DESC;

17
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

12 Consider the table ORDERS given below and write the output of the SQL 3
queries that follow :

ORDN :ITBM QT RATB ORDATB

O Y
1001 RICE 23 120 2023-09-10
1002 PULSES 13 120 2023-10-18
1003 RICE 25 110 2023-11-17
1004 WHEA 28 65 2023-12-25
T
1005 PULSES 16 110 2024-01-15
1006 WHEA 27 55 2024-04-15
T
1007 WHEA 25 60 2024-04-30
T
I.
SELECT ITEM, SUM(QTY) FROM ORDERS GROUP BY ITEM;
II.
SELECT ITEM, QTY FROM ORDERS WHERE ORDATE BETWEEN
'2023-11-01' AND '2023-12-31';
III. SELECT ORDNO, ORDATE FROM ORDERS WHERE ITEM =
'WHEAT' AND RATE>=60;.
Answer:
I. Output:
ITEM SUM(QTY)
RICE 48
PULSES 29
WHEAT 80

II. Output:
ITEM QTY
RICE 25
WHEAT 28

III. Output:
ORDNO ORDATE
1004 2023-12-25
1007 2024-04-30

18
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

13 3

Answer:

(i) Add the constraint, primary key to column P_id in the existing table Projects.
ALTER TABLE Projects ADD PRIMARY KEY (P_id);
(ii) To change the language to Python of the project whose id is P002.
UPDATE Projects SET Language = 'Python' WHERE P_id = 'P002';
(iii) To delete the table Projects from MySQL database along with its data.
DROP TABLE Projects;

14 4

19
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

Answer:
(i) Display the student name and their stop name from the tables Admin and
Transport.
SELECT Admin.S_name, Transport.Stop_name
FROM Admin
INNER JOIN Transport
ON Admin.S_id = Transport.S_id;

(ii) Display the number of students whose S_type is not known.

SELECT COUNT(*) AS No_of_Students
FROM Admin
WHERE S_type IS NULL;

(iii) Display all details of the students whose name starts with 'V'.
SELECT *
FROM Admin
WHERE S_name LIKE 'V%';

(iv) Display student id and address in alphabetical order of student name, from the
table Admin.
SELECT S_id, Address
FROM Admin
ORDER BY S_name ASC;

20
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

15 5

Answer:

(i) Cartesian Product with respect to RDBMS

In RDBMS, the Cartesian Product is a binary operation that returns all possible
combinations of tuples from two relations (tables).
If relation R has m tuples and relation S has n tuples, then R × S will have m × n
tuples.
It is also called the Cross Join.

Example:
If table A has 2 rows and table B has 3 rows, then A × B will have 6 rows.

(ii) Python program to update records in MySQL

import [Link]

# Establish connection
mydb = [Link](
host="localhost",
user="admin",
password="Shopping",
database="Keeper"
)

# Create cursor object

cursor = [Link]()

# Update query
update_query = "UPDATE shop SET Qty = 20 WHERE Item_code = 111"

try:
[Link](update_query)
[Link]() # Save changes

21
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

print("Record updated successfully")

except:
[Link]()
print("Error in updating record")

# Close connection
[Link]()
[Link]()

Explanation:

1. Establish connection to MySQL using [Link]().

2. Create a cursor object.
3. Execute the UPDATE SQL command.
4. Commit the transaction using [Link]().
5. Handle exceptions with try-except.
6. Close cursor and connection.

16
5

Answer:

(i) Give any two features of SQL.

1. SQL is non-procedural: It allows users to specify what data they want

without specifying how to retrieve it.
2. SQL is portable: It can be used across different databases such as MySQL,
Oracle, SQL Server etc. with little or no changes.

(ii) Python code to display all the details of passengers from the table flight:

import [Link]

22
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

# Establish connection
con = [Link](
host="localhost",
user="root",
password="airplane",
database="Travel"
)

cur = [Link]()

# Execute query
[Link]("SELECT * FROM flight")

# Fetch and display all rows

rows = [Link]()
for row in rows:
print(row)

# Close connection
[Link]()

This code connects Python with the MySQL database Travel, retrieves all records
from the table flight, and prints them.

17 Consider the table Stationery given below and write the output of the 3
SQL queries that follow.
Table : Stationery

ITEMNO ITEM DISTRIBUTOR QTY PRICE

401 Ball Pen 0.5 Reliable Stationers 100 16
402 Gel Pen Premium Classic Plastics 150 20
403 Eraser Big Clear Deals 210 10
404 Eraser Small Clear Deals 200 5
405 Sharpener Classic Classic Plastics 150 8
406 Gel Pen Classic Classic Plastics 100 15

(a) SELECT DISTRIBUTOR, SUM(QTY) FROM STATIONERY

GROUP BY DISTRIBUTOR;

(b) SELECT ITEMNO, ITEM FROM STATIONERY WHERE

DISTRIBUTOR = "Classic Plastics" AND PRICE > 10;

(c) SELCET ITEM, QTY * PRICE AS "AMOUNT" FROM

STATIONERY WHERE ITEMNO = 402;
Answer:
(a)Output:
DISTRIBUTOR SUM(QTY)
Reliable Stationers 100
Classic Plastics 400

23
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

Clear Deals 410

(b) Output:
ITEMNO ITEM
402 Gel Pen Premium
406 Gel Pen Classic

(c) Output:
ITEM AMOUNT
Gel Pen Premium 3000

18 Consider the table Rent_cab, given below : 3

Table : Rent_cab

Vcode VName Make Color Charges

101 Big car Carus White 15
102 Small car Polestar Silver 10
103 Family car Windspeed Black 20
104 Classic Studio White 30
105 Luxury Trona Red 9

Based on the given table, write SQL queries for the following:

I. Add a primary key to a column name Vcode.

II. Increase the charges of all the cabs by 10%.
III. Delete all the cabs whose maker name is "Carus".

Answer:

I. Add a primary key to a column name Vcode.

ALTER TABLE Rent_cab ADD PRIMARY KEY (Vcode);
II. Increase the charges of all the cabs by 10%.
UPDATE Rent_cab SET Charges = Charges * 1.10;
III. Delete all the cabs whose maker name is "Carus".
DELETE FROM Rent_cab WHERE Make = 'Carus';

17 4
Consider the tables GAMES and PLAYERS given below
Table: GAMES

GCode GameName Type Number PrizeMoney

101 Carrom Board Indoor 2 5000
102 Badminton Outdoor 2 12000
103 Table Tennis Indoor 4 NULL

24
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

104 Chess Indoor 2 9000

105 Lawn Tennis Outdoor 4 25000

Table: PLAYERS
PCode Name GCode
1 Nabi Ahmad 101
2 Ravi Sahai 108
3 Jatin 101
4 Nazneen 103
Write SQL queries for the following:
(i) Display the game type and average number of games played in each
type.
(ii) Display prize money, name of the game, and name of the players from
the tables Games and Players.
(iii) Display the types of games without repetition.
(iv) Display the name of the game and prize money of those games whose
prize money is known.
Answer:
(i) Display the game type and average number of games played in each type.
SELECT Type, AVG(Number) AS AvgPlayers
FROM Games GROUP BY Type;

(ii) Display prize money, name of the game, and name of the players from the
tables Games and Players.
(We need to join both tables using GCode)
SELECT [Link], [Link], [Link]
FROM Games G
INNER JOIN Players P
ON [Link] = [Link];

(iii) Display the types of games without repetition.

SELECT DISTINCT Type
FROM Games;

(iv) Display the name of the game and prize money of those games whose prize
money is known.
SELECT GameName, PrizeMoney
FROM Games
WHERE PrizeMoney IS NOT NULL;

25
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

MySQL LINK WITH PYTHON

1 MySQL database named WarehouseDB has a product_inventory table in MySQL which 4
contains the following attributes:
• Item_code: Item code (Integer)
• Product_name: Name of product (String)
• Quantity: Quantity of product (Integer)
• Cost: Cost of product (Integer)

Consider the following details to establish Python-MySQL connectivity:

• Username: admin_user
• Password: warehouse2024
• Host: localhost

Write a Python program to change the Quantity of the product to 91 whose Item_code is
208 in the product_inventory table.

Here’s a Python program using [Link] to update the Quantity of the

product to 91 where Item_code is 208 in the product_inventory table of
WarehouseDB:
import [Link]

def update_quantity():
try:
# Establish connection
mydb = [Link](
host="localhost",
user="admin_user",
password="warehouse2024",
database="WarehouseDB"
)

# Create a cursor object

mycursor = [Link]()

# SQL query to update Quantity

update_query = "UPDATE product_inventory SET Quantity = %s
WHERE Item_code = %s"
values = (91, 208)

# Execute query
[Link](update_query, values)

# Commit changes
[Link]()

print("Quantity updated successfully.")

except [Link] as err:

print(f"Error: {err}")

finally:
if mydb.is_connected():
[Link]()
[Link]()

# Call the function

update_quantity()

26
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

Explanation:
• [Link]() establishes the connection using given
credentials.
• UPDATE query changes the Quantity to 91 where Item_code = 208.
• commit() saves the changes to the database.
• try-except-finally ensures error handling and proper closing of the
connection.

2 A table, named STATIONERY, in ITEMDB database, has the following

structure:

Field Type
itemNo int(11)
itemName varchar(15)
price float
qty int(11)

Write the following Python function to perform the specified operation:

AddAndDisplay(): To input details of an item and store it in the table STATIONERY.
The function should then retrieve and display all records from the STATIONERY table
where the Price is greater than 120.

Assume the following for Python-Database connectivity: Host: localhost, User: root,
Password: Pencil

Here’s the Python function AddAndDisplay() as per your requirement:

import [Link]

def AddAndDisplay():
try:
# Establish connection
mydb = [Link](
host="localhost",
user="root",
password="Pencil",
database="ITEMDB"
)
mycursor = [Link]()

# Input item details

itemNo = int(input("Enter Item Number: "))
itemName = input("Enter Item Name: ")
price = float(input("Enter Price: "))
qty = int(input("Enter Quantity: "))

# Insert record into STATIONERY table

insert_query = "INSERT INTO STATIONERY (itemNo, itemName,
price, qty) VALUES (%s, %s, %s, %s)"
values = (itemNo, itemName, price, qty)
[Link](insert_query, values)
[Link]()

27
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

print("Record inserted successfully!\n")

# Retrieve and display records where Price > 120

select_query = "SELECT * FROM STATIONERY WHERE price > 120"
[Link](select_query)
records = [Link]()

print("Items with Price greater than 120:")

for row in records:
print(row)

except [Link] as err:

print(f"Error: {err}")

finally:
if mydb.is_connected():
[Link]()
[Link]()

# Call the function

AddAndDisplay()

Explanation:

• Connects to MySQL database ITEMDB.

• Accepts user input for itemNo, itemName, price, and qty.
• Inserts the new record into the STATIONERY table.
• Fetches and displays all records where price > 120.
• Uses try-except-finally for safe execution and closing connection.

3 A table, named THEATRE, in CINEMA database, has the following structure:

Field Type
Th_ID char(5)
Name varchar(15)
City varchar(15)
Location varchar(15)
Seats int
Write a function Delete_Theatre(), to input the value of Th_ID from the user and
permanently delete the corresponding record from the table.
Assume the following for Python-Database connectivity:
• Host : localhost
• User : root
• Password : Ex2025

Here’s the Python function Delete_Theatre() that will take Th_ID from the user and
delete the record from the THEATRE table in CINEMA database:

import [Link]

def Delete_Theatre():
try:
# Establish connection

28
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

mydb = [Link](
host="localhost",
user="root",
password="Ex2025",
database="CINEMA"
)
mycursor = [Link]()

# Input Theatre ID
th_id = input("Enter Theatre ID (Th_ID) to delete: ")

# Delete query
delete_query = "DELETE FROM THEATRE WHERE Th_ID = %s"
[Link](delete_query, (th_id,))
[Link]()

# Check if deletion happened

if [Link] > 0:
print(f"Record with Th_ID {th_id} deleted successfully!")
else:
print(f"No record found with Th_ID {th_id}.")

except [Link] as err:

print(f"Error: {err}")

finally:
if mydb.is_connected():
[Link]()
[Link]()

# Call the function

Delete_Theatre()

Explanation

• Connects to database CINEMA using given credentials.

• Takes Th_ID input from user.
• Executes DELETE query to remove matching record.
• [Link] checks whether a row was deleted or not.
• Closes connection safely in finally.

(i) Define the term foreign key with respect to RDBMS. 5

(ii) Sangeeta wants to write a program in Python to delete the
record of a candidate “Raman” from the table named
Placement in MySQL database, Agency:
The table Placement in MySQL contains the following
attributes :
CName – String
Dept – String
Place – String
Salary – integer
Note the following to establish connectivity between Python
and MySQL :
· Username – root
· Password – job

29
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

· Host – localhost
Help Sangeeta to write the program in Python for the above
mentioned task.

(i) Definition of Foreign Key:

In RDBMS (Relational Database Management System), a foreign key is a field (or a
set of fields) in one table that refers to the primary key in another table.
It is used to establish and enforce a link between the data in two tables ensuring
referential integrity.

Example:
If we have a table STUDENTS(StudentID, Name, DeptID) and another table
DEPARTMENTS(DeptID, DeptName), then DeptID in STUDENTS can be declared as a
foreign key referencing DeptID in DEPARTMENTS.

(ii) Python Program to Delete Candidate "Raman":

Here’s the Python code that Sangeeta can use:

import [Link]

def delete_candidate():
try:
# Establish connection
mydb = [Link](
host="localhost",
user="root",
password="job",
database="Agency"
)
mycursor = [Link]()

# Candidate to delete
cname = "Raman"

# Delete query
query = "DELETE FROM Placement WHERE CName = %s"
[Link](query, (cname,))
[Link]()

# Check if record deleted

if [Link] > 0:
print(f"Record of candidate '{cname}' deleted
successfully.")
else:
print(f"No record found for candidate '{cname}'.")

except [Link] as err:

print(f"Error: {err}")

finally:
if mydb.is_connected():
[Link]()
[Link]()

# Call the function

30
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

delete_candidate()

Explanation of Code:

• Connects to database Agency with given credentials.

• Deletes record from table Placement where CName = 'Raman'.
• [Link] checks whether any record was actually deleted.
• Connection is closed safely in finally.

(i) Give one difference between CHAR and VARCHAR datatype in 5

MySQL.
(ii) Rahim wants to write a program in Python to insert the
following record in the table named Bank_Account in MySQL
database, Bank :
· Accno – integer
· Cname – string
· Atype – string
· Amount – float
Note the following to establish connectivity between Python
and MySQL :
· Username – admin
· Password – root
· Host – localhost
The values of fields Accno, Cname, Atype and Amount have
to be accepted from the user. Help Rahim to write the program
in Python.

Here’s the solution broken into two parts:

(i) Difference between CHAR and VARCHAR in MySQL:

Feature CHAR VARCHAR

Fixed-length; pads with spaces if Variable-length; stores only actual
Storage
value is shorter than defined length characters, plus 1–2 bytes for length
VARCHAR(10) uses 1–10 bytes
Example CHAR(10) always uses 10 bytes
depending on actual data

One-line answer:
CHAR is fixed-length, while VARCHAR is variable-length.

(ii) Python program to insert record into MySQL

import [Link]

# Establish connection
conn = [Link](
host="localhost",
user="admin",

31
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

password="root",
database="Bank"
)

# Create a cursor object

cursor = [Link]()

# Accept input from user

accno = int(input("Enter Account Number: "))
cname = input("Enter Customer Name: ")
atype = input("Enter Account Type: ")
amount = float(input("Enter Amount: "))

# Prepare SQL query to insert data

sql = "INSERT INTO Bank_Account (Accno, Cname, Atype, Amount) VALUES
(%s, %s, %s, %s)"
values = (accno, cname, atype, amount)

# Execute the query

[Link](sql, values)

# Commit the transaction

[Link]()

print("Record inserted successfully!")

# Close the connection

[Link]()
[Link]()

Explanation:

1. [Link]() establishes a connection to the database.

2. [Link]() is used to run the INSERT query.
3. [Link]() saves changes to the database.
4. Inputs are accepted from the user using input().

1 5
(i) Define the term Domain with respect to RDBMS. Give one example to support
your answer.

(ii) Kabir wants to write a program in Python to insert the following record in the
table named Student in MYSQL database, SCHOOL:
• rno(Roll number )- integer
• name(Name) - string
• DOB (Date of birth) – Date
• Fee – float

Note the following to establish connectivity between Python and MySQL:

• Username - root
• Password - tiger

32
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

• Host - localhost

The values of fields rno, name, DOB and fee has to be accepted from the user. Help
Kabir to write the program in Python.

Here’s the solution in two parts:

(i) Definition of Domain in RDBMS:

Definition:
A domain is the set of permissible values that a column (attribute) in a table can
hold in a relational database. It defines the type, range, and format of data that can
be stored in a column.

Example:

• Column Fee in table Student may have a domain of FLOAT with values
greater than 0.
• Column DOB may have a domain of DATE.

One-line answer:
A domain restricts the type of values that can be stored in a column. For example,
rno can only accept integer values.

(ii) Python program to insert a record into MySQL table

import [Link]

# Establish connection
conn = [Link](
host="localhost",
user="root",
password="tiger",
database="SCHOOL"
)

# Create a cursor object

cursor = [Link]()

# Accept input from user

rno = int(input("Enter Roll Number: "))
name = input("Enter Name: ")
dob = input("Enter Date of Birth (YYYY-MM-DD): ")
fee = float(input("Enter Fee: "))

# Prepare SQL query to insert data

sql = "INSERT INTO Student (rno, name, DOB, Fee) VALUES (%s, %s,
%s, %s)"
values = (rno, name, dob, fee)

# Execute the query

[Link](sql, values)

# Commit the transaction

[Link]()

33
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

print("Record inserted successfully!")

# Close the connection

[Link]()
[Link]()

Explanation:

1. [Link]() connects to the MySQL database.

2. [Link]() executes the INSERT query.
3. [Link]() saves the changes to the database.
4. User inputs are accepted using input() and converted to the correct type.

5
(i) Give one difference between alternate key and candidate key.

(ii) Sartaj has created a table named Student in MYSQL database, SCHOOL:
• rno(Roll number )- integer
• name(Name) - string
• DOB (Date of birth) – Date
• Fee – float

Note the following to establish connectivity between Python and MySQL:

• Username - root
• Password - tiger
• Host - localhost
Sartaj, now wants to display the records of students whose fee is more than 5000.
Help Sartaj to write the program in Python.

Here’s the solution broken into two parts:

(i) Difference between Alternate Key and Candidate Key:

Feature Candidate Key Alternate Key

A column or set of columns that can A candidate key that is not
Definition
uniquely identify a row in a table. chosen as the primary key.
rno and email in a Student table If rno is chosen as primary key,
Example
can both be candidate keys. email becomes an alternate key.

One-line answer:
An alternate key is a candidate key that is not selected as the primary key.

(ii) Python program to display students with fee > 5000

import [Link]

# Establish connection
conn = [Link](
host="localhost",
user="root",

34
Class XII Subject: Computer Science Topic: Relational Database and SQL Marks:20

password="tiger",
database="SCHOOL"
)

# Create a cursor object

cursor = [Link]()

# Prepare SQL query

sql = "SELECT * FROM Student WHERE Fee > 5000"

# Execute the query

[Link](sql)

# Fetch all matching records

records = [Link]()

# Display the records

if records:
print("Students with Fee > 5000:")
for row in records:
print(f"Roll No: {row[0]}, Name: {row[1]}, DOB: {row[2]},
Fee: {row[3]}")
else:
print("No records found with Fee > 5000.")

# Close the connection

[Link]()
[Link]()

Explanation:

1. [Link]() establishes a connection to the database.

2. [Link]() runs the SELECT query.
3. [Link]() fetches all rows matching the condition Fee > 5000.
4. Records are displayed using a loop.

35