DATABASE MANAGEMENT SYSTEM

Computer Science class 12 previous year questions

Most expected 1 mark questions Unit III Database Management

1] Vishal is giving an online test on the computer. The question he got on screen is which
of the following is a valid SQL statement?

a) DELETE ALL ROWS FROM TABLE STUDENT;

b) DELETE * from student;
c) DROP all rows from student;
d) DELETE FROM student;

Select an appropriate option to help him.

[2] Yashvi wants to change the structure of table in MySQL. Select an appropriate SQL
command and help her to accomplish her task.

a) update b) alter c) modify d) change

[3] Alpesh wants to remove a column from a table in SQL. Which command is used to
remove a column from a table in SQL?

a) update b) remove c) alter d) drop

[4] Observe the given keywords used in mysql queries and identify which keyword is not
used with DDL comamnd?

a) DROP b) MODIFY c) DISTINCT d) ADD

[5] Bhavi wants to assign NULL as the value for all the tuples of a new Attribute in a
relation. Select an appropriate command to fulfill her need.

a) MODIFY b) DROP c) ADD d) ALTER

[6] Kiran wants to remove rows from a table. She has given the following commands but
she is confused about which one is the correct command. Help her to identify the correct
command to accomplish her task.

a) DELETE command b) DROP Command

c) REMOVE Command d) ALTER Command

[7] State true or false – “Drop command will remove the entire database from MySQL.”

[8] There are various keys associated with database relations. Select an appropriate
invalid key out of the following:

a) Primary Key b) Master key c) Foreign Key d) Unique Key

[9 ]Which of the following statements is True?

a) There can be only one Foreign Key in a table

b) There can be only one Unique key is a table

c) There can be only one Primary Key in a Table

d) A table must have a Primary Key

[10] Raj is preparing for DBA. He read something as a non-key attribute of a relation
whose values are derived from the primary key of another table. Help him to identify the
term he read about.

a) Primary key b) Foreign key c) candidate key d) Alternate key

[12] Anirudh wants to use the SELECT statement combined with the clause which returns
records without repetition. Help him by selecting appropriate keyword to fulfill his task.

a) distinct b) describe c) unique d) null

[13] Vinay wants to identify the keyword used to obtain unique values in a SELECT query
from the following

a) UNIQUE b) DISTINCT c) SET d) HAVING

[14] Priyank wants to display total number of records from MySQL database table. Which
command help him to the task out of the following?

a) total(*) b) sum(*) c) count(*) d) all(*)

[15] Udit is learning the concept of operators in SQL. Help him to identify the Logical
Operators used in SQL by selecting the appropriate option.

a) AND,OR,NOT b) &&,||,! c) $,|,! d) None of these

[16] Hriday wants to fetch records from a table that contains some null values. Which of
the following ignores the NULL values in SQL?

a) Count(*) b) count(column_name)
c) total(*) d) None of these
Most expected 2/3 marks questions Unit III Database Management

[1] What is MySQL? List some popular versions of MySQL.

[2] Hetal is inserting “Rathod” in the “LastName” column of the “Emp” table but an error
is being displayed. Write the correct SQL statement.

INSERT INTO Emp(‘Rathod’)VALUES(LastName) ;

[3] Darsh created the following table with the name ‘Friends’ :

Table : Friends

Freind_Code Name Hobbies

F001 Anurag Traveling
F002 Manish Watching Movies

Now, Darsh wants to delete the ‘Hobbies’ column. Write the MySQL statement.

[4] Mr. Nikunj entered the following SQL statement to display all Salespersons of the cities
“Chennai” and ‘Mumbai’ from the table ‘Sales’.

Table: Sales

Scode Name City

S0001 Krishanan Iyer Chennai
S0002 Atmaram Bhide Mumbai
S0003 Jethalal Gada Bhachau
S0004 Venugopal Srinavasana Chennai
S0005 Mandar Mumbaikar Mumbai

SELECT * FROM Sales WHERE City=‘Chennai’ AND City=‘Mumbai’;

He is getting the Empty Set as output. Explain the problem with the statement and rewrite
the correct statement.

[5] Is NULL value the same as 0 (zero) ? Write the reason for your answer.

[6] Write the UPDATE command to increase the commission (Column name : COMM) by
500 of all the Salesmen who have achieved Sales (Column name : SALES) more than
200000. The table’s name is COMPANY.

[7] While using SQL pattern matching, what is the difference between ‘_’ (underscore) and
‘%’ wildcard symbols?

[8] What is the meaning of open source and open source database management system?

[9] In a table employee, a column occupation contains many duplicate values. Which
keyword would you use if wish to list of only different values? Support your answer with
example.
[10] How is alter table statement different from UPDATE statement?

[11] Charvi wants to delete the records where the first name is Rama in the emp table.
She has entered the following SQL statement. An error is being displayed. Rewrite the
correct statement.

Delete from firstname from emp;

[12] What is the relationship between SQL and MySQL?

[13] Rani wants to add another column ‘Hobbies’ with datatype and size as VARCHAR(50)
in the already existing table ‘Student’. She has written the following statement. However,
it has errors. Rewrite the correct statement.

MODIFY TABLE Student Hobbies VARCHAR;

[14] Write SQL query to display employee details from table named ‘Employee’ whose
‘firstname’ ends with ‘n’ and firstname contains a total of 4 characters (including n).

[15] Identify aggregat functions of MySQL amongst the following :

TRIM(), MAX(), COUNT(*), ROUND()

[16] Write the following statement using ‘OR’ logical operator :

SELECT first_name, last_name, subject FROM studentdetails WHERE subject IN (‘Maths’,

‘Science’);

[17] What is MySQL used for? Ajay wants to start learning MySQL. From where can he
obtain the MySQL software?

[18] In the table ‘‘Student’’, Priya wanted to increase the Marks (Column Name:Marks) of
those students by 5 who have got Marks below 33. She has entered the following
statement :

SELECT Marks+5 FROM Student WHERE Marks <33;

Rewrite the correct statement.

[19] Consider a table Accounts and answer the following:

(i) Name the Data type that should be used to store AccountCodes like ‘‘A1001’’ of
Customers.

(ii) Name two Data types that require data to be enclosed in quotes.
[20] Given the table ‘Player’ with the following columns :

Table – Player

Pcode Points
P001 95
P002 82
P003 74
P004 93
P005 77

Write the output of the following queries:

SELECT AVG(POINTS) FROM Player;

Select COUNT(POINTS) FROM Player;

[21] Differentiate between char and varchar. Priya has created a table and used char(10)
and varchar(10) as a datatype for two of the columns of her table. What (10) indicate
here?

[22] ‘Employee’ table has a column named ‘CITY’ that stores city in which each employee
resides. Write SQL query to display details of all rows except those rows that have CITY as
‘DELHI’ or ‘MUMBAI’ or ‘CHANDIGARH’.

[23] Ajay has applied a Constraint on a column (field) such that

(i) Ajay will certainly have to insert a value in this field when he inserts a new row in the
table.

(ii) If a column is left blank then it will accept 0 by itself.

Which constraints has Ajay used?

[24] ‘STUDENT’ table has a column named ‘REMARK’ that stores Remarks. The values
stored in REMARK column in different rows are “PASS” or “NOT PASS” or “COMPTT” etc.
Write SQL query to display details of all rows except those that have REMARK as “PASS”.

[25] Consider the table : Company

SID Sales
S001 250
S002 100
S002 200
What output will be displayed by the following SQL statements?

(i) select avg(sales) from company where sales>200;

(ii) select sum(sales)/2 from company where SID=’S002′;

[26] Consider the table ‘Hotel’ given below:

Table : Hotel

EMPID Category Salary

1001 Permanent 25000
1002 Contract 18000
1003 Adhoc 10000
1004 Contract 12000
1005 Permanent 20000

Mr. Vinay wanted to display the average salary of each Category. He entered the following
SQL statement. Identify error(s) and Rewrite the correct SQL statement.

SELECT Category, Salary FROM Hotel GROUP BY Category;

Write one more query to display maximum salary category wise.

[27] Namrata has created the following table with the name ‘Order’.

Field Constraint
OrderID Primary Key
OrderDate Not Null
OrderAmount
StoreID
Answer the following:

What is the data type of columns OrderId and OrderDate in the table Order?
Namrata is now trying to insert the following row : O102, NULL, 59000, S105

[28] Write SQL query to create a table ‘Event’ with the following structure :

Field Data Type Size Constraint

eventid varchar 5 Primary Key
event varchar 30 not null
location varchar 50 Ahmedabad should be by default
clientid integer Foreign Key Parent table (Client)
eventdate date

[29] How is a Primary key constraint different from a Unique key constraint?

[30] Write two similarities between CHAR and VARCHAR data types.

[31] Consider “TravelPackage” table with “source” column. Entering data for the Location
column is optional. If one enters data for a row with no value for the “source” column,
what value will be saved in the “source” column? Write SQL statement to display the
details of rows in the “travelpackage” table whose location is left blank.

[32] Define: Field/Attribute, Tuple/Record

[33] Define: Degree and Cardinality

[34] Define: Candidate Key, Alternate Key

[35] A NULL value can be inserted into a foreign key? Justify your answer.

[36] Ravindra is confused about what is a domain? Clear his confusion with an example.

[37] Consider the following table – Student

AdmNo RollNo Name Class Marks

1001 1 Shailesh X 76
1002 2 Kamalaa XII 89
1003 3 Dinesh XI 85
Identify the candidate keys and alternate keys.

[38] Enlist any four popular RBMDS software.

[39] A table “clustergames” exists with 4 columns and 6 rows. What is its degree and
cardinality, initially? 2 rows are added to the table and 1 column deleted. What will be the
degree and cardinality now?

[40] Sejal wants to create a table patient. Help her to do the following:

(i) She is confused about how the dates are stored in MySQL? Suggest the date format.

(ii) She want to display all records from the table and she wrote the following command:

show * from patient;

She is not getting the output. Rewrite the correct statement.

[41] What is sorting? Which keyword is used to sort data from the table in SQL?

[42] Rajni wants to apply sorting on a table through SQL. But he is not aware about how to
use order by clause. Explain the order by clause in short to him with example.
Most expected 4 marks questions computer science class 12 Unit 3 Database
Management

[1] Consider the following tables participant and activity and answer the questions :

Table – Participant

ADMNO NAME HOUSE ACTIVITYOCDE

A001 Sagar Patel Red AC001
A002 Kaushik Maheta Blue AC002
A003 Noor Joshi Green AC003
A004 Mahesh Joshi Yellow AC004
A005 Nutan Chauhan Blue AC001
A006 Mahek Patel Yellow AC004
A007 Sidhdharth Patel Blue AC003
A008 Gaurang Vyas Green AC004

Table – activity fields: activitycode, activityname, points

ACTIVITYCODE ACTIVITYNAME POINTS

AC001 Poem Recitation 120
AC002 Maths Quiz 150
AC003 Science Quiz 180
AC004 Sports 200

1. When the table ‘‘PARTICIPANT’’ was first created, the column ‘NAME’ was planned
as the Primary key by the Programmer. Later a field ADMNO had to be set up as
Primary key. Explain the reason.

2. Identify data type and size to be used for column ACTIVITYCODE in table ACTIVITY.

3. Write a query to display Activity Code along with the number of participants
participating in each activity (Activity Code wise) from the table Participant.

4. How many rows will be there in the Cartesian product of the two tables in
consideration here?

5. To display Names of Participants, Activity Code, and Activity Name in alphabetic

ascending order of names of participants.

6. To display Names of Participants along with Activity Codes and Activity Names for
only those participants who are taking part in Activities that have ‘quiz’ in their
Activity Names and Points of activity are above 150.
[2] Consider the following tables SUPPLIER and ITEM and answer the questions

Table – Supplier

SNO SNAME AREA EMAIL

1 A to Z Suppliers Narol a2zsupp@gmail.com
2 Rathi Brothers Company Pvt. Ltd. Naroda rathibrothers@gmail.com
3 Rushan Communications Danilimda rushan786@gmail.com
4 Apex Entreprise Pvt. Ltd. Maninagar apexmaninagar@gmail.com
5 Mahi Transport Co. Ltd. Maninagar mahimaninagar@gmail.com
6 Dhareja Travels Naroda dharejatravelsnar@gmail.com
7 Sameer Suppliers Narol sameersuppliers@gmail.com

Table – Item

INO INAME PRICE SNO

I001 Cargo 28000 1
I002 Cartoon 500 2
I003 LG Mobiles 2600 3
I004 Truck on Rent 55000 1
I005 Reliance SIM 700 3
I006 Packing Material 8000 1
I007 Moterbikes 70000 4

a) Which column should be set as the Primary key for SUPPLIER table ?
b) Mr. Vijay, the Database Manager feels that Email column will not be the right
choice for Primary key. State reason(s) why Email will not be the right choice.
c) Write the data type and size of INo column of ‘ITEM’ table.
d) To display names of Items, SNo and Names of Suppliers supplying those items for
those suppliers who have stores located in Naroda.
e) To display Names of Items, SNo, Price and Corresponding names of their suppliers
of all the items in ascending order of their Price.
f) To display Item Name wise, Minimum and Maximum Price of each item from the
table item. i.e. display IName, minimum price and maximum price for each IName.)
g) What will be the number of rows in the Cartesian product of the above two tables?
[3] Consider the table given below:

Table – Faculty

TEACHERID NAME ADDRESS STATE PHONENUMBER

T001 Avinash Ahmedabad Gujarat 9825741256
T002 Akhilesh Jaipur Rajasthan 9824456321
T003 Shivansh Mumbai Maharashtra 9327045896
T004 Minaxi New Delhi Delhi 9012547863

Table – Course, Fields: coruseid, subject, teacherid, fee

COURSEID SUBJECT TEACHERID FEE

1001 Computer Science T001 6750
1002 Informatics Practices T004 4550

a) Which column is used to relate the two tables?

b) 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.
c) 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,TeacherId,Name,PhoneNumber FROM Faculty,Course
WHERE Faculty.TeacherId = Course.TeacherId AND Fee>=5000;

[4] Write commands in SQL for (1) to (4) and output for (5) and (6).

Table : Store

STOREID NAME LOCATION CITY NOOFEMPLOYEES DATEOPENED SALESAMOUNT

S01 BHAGYA LAXMI SATELLITE AHMEDABAD 7 2018-04-01 25000
S02 PARTH FASHION AKOTA BARODA 2 2010-07-15 45000
S03 KRISHNA SAREES UMARWADA SURAT 8 2020-08-13 85000
S04 SAMEER CLOTHES RELIEF ROAD AHMEDABAD 2 2012-06-02 78000

a) To display name, location, city, SalesAmount of stores in descending order of

SalesAmount.
b) To display names of stores along with SalesAmount of those stores that have
‘fashion’ anywhere in their store names.
c) To display Stores names, Location and Date Opened of stores that were opened
before 1st March, 2015.
d) To display total SalesAmount of each city along with city name.
e) SELECT distinct city FROM store;
[7] Consider the following DEPT and EMPLOYEE tables. Write SQL queries for (1) to (4) and
find outputs for SQL queries (5) to (8).

Table:DEPT

DCODE DEPARTMENT LOCATION

10 AGRICULTURE ANAND
20 MINES NADIAD
30 TPP KHEDA
40 MECHANICAL AHMEDABAD

Table:EMP

ENO NAME DOJ DOB GENDER DCODE

1111 MISKA 2020/03/01 1997/11/15 F 10
1112 AKSHAY 2018/06/05 1998/12/04 M 20
1113 NEEL 2021/07/01 2000/10/20 M 10
1114 ANKITA 2016/04/25 2001/10/27 F 30
1115 MOHIT 2016/12/22 2003/11/02 M 40
a) To display Eno, Name, Gender from the table EMPLOYEE in ascending order of Eno.
b) To display the Name of all the MALE employees from the table EMPLOYEE.
c) To display the Eno and Name of those employees from the table EMPLOYEE who
are born between ‘1997-01-01’ and ‘1999-12-01’.
d) To count and display FEMALE employees who have joined after ‘1999-01-01’.
e) SELECT COUNT(*),DCODE FROM EMPLOYEE GROUP BY DCODE HAVING
COUNT(*)>1;
f) SELECT DISTINCT DEPARTMENT FROM DEPT;
g) SELECT NAME,DEPARTMENT FROM EMPLOYEE E,DEPT D WHERE
E.DCODE=D.DCODE AND ENO<1113
h) SELECT MAX(DOJ), MIN(DOB) FROM EMPLOYEE;
Most expected Questions 2/3 marks interface of python with MySQL
[1] Nisha is trying to create a project on python. Where she wants to store the records in a
database. She learnt about front-end and back-end, But forget what it means right now.
Help her by giving definition of front-end and backend.

[2] What is DB-API?

[3] What does DB-API include?

[4] Man wants to install the mysql connector. Suggest steps to install the same.

[5] Prakash has installed mysql connector. Now he wants to check whether its properly
installed or not. Suggest python command to do this task.

[6] Name any two python mysql connector modules.

[7] What are the main components of mysql.connector module?

[8] What do you mean by a connection and connection object?

[9] What do you mean by cursor object and result set?

[10] What are the parameters of mysql.connector.connect method?

[11] Neel is trying to write the connect method. Help him by filling the given gaps:

import ____________ as ms
cn=ms.________(host=localhost,user='root',passwd='root',database='school')
cr=cn._____()
cn.______()

[12] Fill the proper words in the following code snippet:

cn=mysql.connector.connect(___= ‘localhost’,____='Student',____='root',____='school')

[13] Babita wants to insert a record into the table using a python MySQL connector.
Suggest two methods that are used to execute the query using the cursor object.

[14] What are the methods used to read data from SQL tables?

[15] Differentiate between fetchone() and fetchmany().

[16] Is rowcount method? Explain in short.

[17] Consider the following table: IPL2022

Rank Batter Runs Team

1 Jos Buttler 863 RR
2 KL Rahul 616 LSG
3 Quinton de Kock 508 LSG
4 Hardik Pandya 487 GT
5 Shubman Gill 483 GT
Observe the following given code and write the output:

import mysql.connector as ms
cn=ms.connect(host='localhost',user='root',passwd='root',database='IPL')
cr=cn.cursor()
cr.execute("seleect * from IPL2022")
r=cr.fetchone()
r=cr.fetchone()
r=cr.fetchone()
data=int(r[2])
print(data*2)

[18] Consider the table given in the above question and write the for this code snippet:

import mysql.connector as ms
cn=ms.connect(host='localhost',user='root',passwd='root',database='IPL')
cr=cn.cursor()
cr.execute("seleect * from IPL2022")
r=cr.fetchone()
print("No. of records fetched:",cr.rowcount)
r=cr.fetchmany(2)
print("No. of records fetched:",cr.rowcount)

[19] Consider the tabe given in question no. 17 and write python code to display the top 3
run-scorer.

[20] Consider the table given in question 17 and write python code to display details for
those batsmen who score less than 500 runs.

[21] Write python code to insert this record: (6,’David Miller’,481,’GT’)

[22] Write python code to update short team name GT to full team name Gujarat Titans.

[23] Write code to delete record of David Miller.

[24] The partial code is given to check whether the connection between the interface of
python with MySQL is established or not. Fill in the blanks with appropriate
functions/statements for given statements:

Note the following to establish connectivity between Python and MYSQL:

Username is root
Password is root
Database name – Transport
V_ID Name Model Price
integer varchar integer integer
import ____________ as msql #Statement 1
cn=msql.___________(_______,________,________,_______) #Statement 2
if cn.__________: # Statement 3
print("Successfully Connected...")
else:
print("Something went wrong...")

Write the missing statements as per the given instructions:

a) Write the module name required in #Statement 1
b) Complete the code with the name of a function and parameters in #Statement 2
c) Write a function to check whether the connection is established or not in
#Statement 3

[25] Consider the database and parameters given in [24] and complete the code below
given partial code of fetching records having vehicles model after 2010.

import mysql.connector as mysql

con1=mysql.connect(host="localhost",
user="root",
password="root",
database="Transport")
_______________ #Statement 1
print("Models after 2010 : ")
q="Select * from vehicle where model>2010"
_______________________ #Statement 2
data= _________________ #Statement 3
for rec in data:
print(rec)

Write the following missing statements to complete the code:

a) Statement 1 – to create the cursor object
b) Statement 2 – to execute the query that extracts records of those vehicles whose
model is greater than 2010.
c) Statement 3 – to read the complete result of the query into the object named data.
[26] Consider the following facts for connecting an interface of python with MySQL and
complete the given partial code given for inserting a record into the database table:

The table structure of student is as follows:

RollNo Name Standard Marks

Integer varchar integer float
Note the following to establish connectivity between Python and MySQL:

Username is root
Password is root
The table exists in a “school” database.
The details (RollNo, Name, Standard, and Marks) are to be accepted by the user.
import mysql.connector as mysql
con1=mysql.connect(host="localhost",
user="root",
password="root",
database="school")
mycursor = con1.cursor()
rno=int(input("Enter Roll Number :: "))
name=input("Enter name :: ")
std=int(input("Enter class :: "))
marks=int(input("Enter Marks :: "))
querry=________________________________ #Statement 1
______________________ #Statement 2
______________________ # Statement 3
con1.close()
print("Data Added successfully")

Write the following missing statements to complete the code:

a) Statement 1 – query insert records with all the variables
b) Statement 2 – to execute the command that inserts the record in the table
Student.
c) Statement 3 – to add the record permanently to the database