Question Set A

cbse 12th Standard Computer Science Data Base Management Important Questions And Answers Study Material - QB365

Download CBSE Class 12th Standard CBSE Computer Science question papers, sample papers, important questions, and previous year solved papers in PDF format. Get free study materials, NCERT solutions, and exam preparation resources for Class 12th Standard CBSE Computer Science

Questions:

SECTION B

Answer any 7 questions each question carries 2 marks

What is relation? Define the relational data model.

Give a suitable example of a table with sample data and illustrate primary and candidate keys in it.

Differentiate between cardinality and degree of a table with the help of an example.

A table CUSTOMER has 10 columns but no row. Later, 10 new rows are inserted and 3 rows are deleted in the table. What is the degree and cardinality of the table CUSTOMER?

A table STUDENT has 3 columns and 10 rows and another table STUDENT1 has the same number of columns and 15 rows. 5 rows are common in both the tables. If we take union, what is the degree and cardinality of the resultant tabla?

List the most common parameters and the usage that are passed to communicate with the database.

Write a code to use Parameterized query and prepared statement in Python

What is the use of wildcard?

What is join in SQL?

10.

Define Equi join.

SECTION C

Answer any 5 questions each question carries 3 marks

11.

Explain database.

12.

What is Hierarchical model?

13.

Define database management system.

14.

What is the need of database system?

15.

Write a output for SQL queries
(i) to
(ii),which are based on the table: STUDENT given below:

ROLL NO	NAME	CLASS	DOB	GENDER	CITY	MARKS
1	NANDHA	X	06-06-1995	M	AGRA	551
2	SAURABH	XII	07-05-1993	M	MUMBAI	462
3	SONAL	XI	06-05-1994	F	DELHI	400
4	TRISLA	XII	08-08-1995	F	MUMBAI	450
5	STORE	XII	08-10-1995	M	DELHI	369
6	MARISLA	XI	12-12-1994	F	DUBAI	250
7	NEHA	X	08-12-1995	F	MOSCOW	377
8	NISHANT	X	12-06-1995	M	MOSCOW	489

(i) SELECT COUNT(*), CIty FROM STUDENT GROUP BY CITY HAVING COUNT(*) >1;
(ii) SELECT MAX(DOB),MIN(DOB) FROM STUDENT
(iii) SELECT NAME,GENDER FROM STUDENT WHERE CITY="Delhi";

16.

Define the following:
(i) Self Join
(ii) Non-equi join
(iii) Natural join

SECTION D

Answer any 2 questions each question carries 4 marks

17.

Consider the table Product with following structure.
P_ID, ProductName, Manufacturer, Price.
Write Python code to increase price of all products by 50.

18.

Write Python code to increase age of all employees by 1 year in the Employee table
of database HTMdb with userid HRMan and password HRMANexe@pwd.

19.

Write SQL queries for (i) to (iv), which are based on the table: STUDENT

ROLL NO	NAME	CLASS	DOB	GENDER	CITY	MARKS
1	NANDHA	X	06-06-1995	M	AGRA	551
2	SAURABH	XII	07-05-1993	M	MUMBAI	462
3	SONAL	XI	06-05-1994	F	DELHI	400
4	TRISLA	XII	08-08-1995	F	MUMBAI	450
5	STORE	XII	08-10-1995	M	DELHI	369
6	MARISLA	XI	12-12-1994	F	DUBAI	250
7	NEHA
8	NISHANTH

(i) To display the records from table STUDENT in alphabetical order as per the name of the student.
(ii) To display Class, DOB and City whose marks is between 450 and 551.
(iii) To display Name, Class and total number of students who have scored more than 450 marks, class wise.
(iv) To increase marks of all students by 20 whose class is "XII".

SECTION E

Answer any 3 questions each question carries 5 marks

20.

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii),
which are based on the tables.
Table: CUSTOMER

CNO	CNAME	ADDRESS
101	Richa Jain	Delhi
102	Surbhi Sinha	Chennai
103	Lisa Thomas	Bangalore
104	ImranAIi	Delhi
105	Roshan Singh	Chennai

Table: TRANSACTION

TRNO	CNO	AMOUNT	TYPE	DOT
T 001	101	1500	Credit	2017-11-23
T002	103	2000	Dedit	2017-05-12
T003	102	3000	Credit	2017-06-10
T003	103	12000	Credit	2017-09-12
T005	101	1000	Dedit	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 date of transaction (DOT) from the table TRANSACTION for the customer having CNO as 103.
(iv) To display all CNO, CNAME and DOT (date of transaction) of those CUSTOMERS from 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;

21.

Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the tables:
TABLE: BOOK

CODE	BNAME	TYPE
F101	The Priest	Fiction
L102	German easy	Literature
C103	Tarzan in the lost world	Comic
F102	Untold story	Fiction
C102	War heroes	Comic

TABLE: MEMBER

MNO	MNAME	CODE	ISSUE DATE
M101	RAGHAV SINHA	L 02	2016-10-13
M103	SARTHAK JOHN	F102	2017-02-23
M102	ANISHA KHAN	C101	2016-06-12

(i) To display all details from table MEMBER in descending order of ISSUEDATE.
(ii) To display the CODE and BNAME of all Fiction Type books from the table Book.
(iii) To display the TYPE and number of books in each TYPE from the table BOOK.
(iv) To display all MNAME and ISSUEDATE of those members from table MEMBER who
have books issued (i.e., ISSUEDATE) in the year 2017.
(v) SELECT MAX (ISSUEDATE) FROM MEMBER;
(vi) SELECT DISTINCT TYPE FROM BOOK;
(vii) SELECT A.CODE, BNAME, MNO.MNAME
FROM BOOK A, MEMBER B WHERE A.CODE= B.CODE;
(viii) SELECT BNAME FROM BOOK WHERE TYPE NOT IN ("FICTION", "COMIC");

22.

Write SQL commands for the queries (i) to (iv) and outputs for (v) to (viii) based on a tables COMPANY and CUSTOMER.
COMPANY

CID	NAME	CITY	PRODUCT NAME
111 222 333 444 555 666	SONY NOKIA ONIDA SONY BLACKBERRY DELL	DELHI MUMBAI DELHI MUMBAI MADRAS DELHI	TV MOBILE TV MOBILE MOBILE LAPTOP

CUSTOMER

CUSTID	NAME	PRICE	QTY	CID
101 102 103 104 105 106 107	ROHAN SHARMA DEEPAK KUMAR MOHAN KUMAR SAHIL BANSIL NEHA SONI SONAL AGGARWAL ARUN SINGH	70000 50000 30000 35000 25000 20000 50000	20 10 5 3 7 5 15	222 666 111 333 444 333 666

(i) To display those company name which are having price less than 30000.
(ii) To display the name of the companies in reverse alphabetical order.
(iii) To increase the price by 1000 for those customer whose name starts with S?
(iv) To add one more column totalprice with decimal(10,2) to the table customer
(v) SELECT COUNT(*) ,CITY FROM COMPANY GROUP BYCITY;
(vi) SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY> 10 ;
(vii) SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE "%r%;
(viii) SELECT PRODUCTNAME,CITY, PRICE FROM COMPANY, CUSTOMER WHERE COMPANY.CID=CUSTOMER.CID AND PRODUCTNAME="MOBILE" ;

23.

Consider the following tables SCHOOL and ADMIN and answer the following question :
SCHOOL

CODE	TEACHER NAME	SUBJECT	DOJ	PERIODS	EXPERIENCE
1001 1009 1203 1045 1123 1167 1215	Ravi Shankar Priya Rai Lisa Anand Yashraj Ganan Harish B Umesh	English Physics English Maths Physics Chemistry Physics	12-093-2000 03-09-1998 09-04-2000 24-08-2000 16-07-1999 19-10-1999 11-05-1998	24 26 27 24 28 27 22	10 12 5 15 3 5 16

ADMIN

CODE	GENDER	DESIGNATION
1001 1009 1203 1045 1123 1167 1215	MALE FEMALE FEMALE MALE MALE MALE MALE	VICE PRINCIPAL CO -ORDINATOR CO -ORDINATOR HOD SENIOR TEACHER SENIOR TEACHER HOD

Write SQL statements for the following:
(i) To display TEACHERNAME, PERIODS of all teachers whose periods are more than 25.
(ii) To display all the information from the table SCHOOL in descending order of experience.
(ill) To display DESIGNATION without duplicate entries from the table ADMIN.
(iv) To display TEACHERNAME, CODE and corresponding DESIGNATION from tables SCHOOL and ADMIN of Male teachers.

SECTION A

Sec A consists of 18 questions each question carries 1 marks

24.

Some of the examples of databases are ___________.

(a)

IBM DB2

(b)

Oracle

(c)

MySQL

(d)

All of the above

25.

SQL stands for __.

(a)

Structured Query Language

(b)

Simple Query language

(c)

Simple Query Locator

(d)

None of the above

26.

SQLs are helpful for _.

(a)

Manipulating data in the database

(b)

Declaring constraints

(c)

Retrieving data from the database

(d)

All of the above

27.

Which of the following points do we have to keep in mind while using SQL:

(a)

SQL is case insensitive

(b)

Always end with a semicolon

(c)

To enter multiple lines, we don’t write a semicolon. (;)

(d)

All of the above

28.

Which of the following datatypes has reserved space, meaning the datatype has a fixed length?

(a)

Char(n)

(b)

Varchar(n)

(c)

Both a. and b.

(d)

None of the above

29.

Which of the following datatypes does not have a fixed length? It stores only that much length which the input character has

(a)

Char(n)

(b)

Varchar(n)

(c)

Both a. and b.

(d)

None of the above

30.

Which of the following datatypes holds decimal point numbers?

(a)

INT

(b)

BIGINT

(c)

FLOAT

(d)

CHAR

31.

Which of the following constraints helps to identify the missing/unknown/not applicable value?

(a)

NULL

(b)

NOT

(c)

EMPTY

(d)

PRIMARY KEY

32.

Which of the following constraints ensures that all the values in the column are distinct/unique?

(a)

PRIMARY KEY

(b)

UNIQUE

(c)

KEY DEFAULT

(d)

NULL

33.

Which of the following constraints is used to specify the column if no value is provided?

(a)

PRIMARY KEY

(b)

UNIQUE VALUE

(c)

DEFAULT

(d)

NULL

34.

The column which can uniquely identify each row/record in a table is known as _.

(a)

FOREIGN KEY

(b)

PRIMARY KEY

(c)

DELTA KEY

(d)

All of the above

35.

DDL stands for __.

(a)

Data Dynamic Language

(b)

Data Declaring Language

(c)

Data Definition Language

(d)

None of the above

36.

To create a database, we use which of the following SQL statements?

(a)

Make Database database name;

(b)

Create database database name;

(c)

Create database name;

(d)

Make database name;

37.

To know the names of existing databases, we use the statement __.

(a)

Show Database;

(b)

Show database name;

(c)

Show database database name;

(d)

Show * Database;

38.

Which of the following SQL commands is required for creating a table?

(a)

Create table name(…)

(b)

Create table(…)

(c)

Create table table name(…)

(d)

All of the above

39.

Which of the following SQL commands is used to view the structure of an already created table?

(a)

DES table name;

(b)

DESCRIBE table name;

(c)

DESCRIBE table table name;

(d)

DES table table name;

40.

After creating a table, we may require adding/removing an attribute or modifying the datatype of an existing table. Which of the following SQL commands can be used?

(a)

MODIFY table table name;

(b)

MODIFY table name;

(c)

ALTER table table name;

(d)

ALTER table name;

41.

If you want to remove a table from the database, which of the following SQL commands can be used?

(a)

DROP table table name;

(b)

REMOVE table table name;

(c)

DEL table table name;

(d)

None of the above

Visit QB365-SLMS - https://www.qb365.in/slms/

Answers Key:

SECTION B

Answer any 7 questions each question carries 2 marks

A relation is a table having atomic values, unique row and unordered rows and columns. The relational model represent data and relationship among data by a collection of tables known as relation, each of which has a number of columns with unique names.

A table may have more than one such attribute/ group of attributes that identifies a row/tuple uniquely, all such attribute(s) are known as candidate keys. Out of the candidate keys, one is selected as primary key.

Cardinality is defined as the number of rows in a table. Degree is the number of columns in a table.
e.g. consider the following table:
Table: Account

Acno	Cname
A 101 A 102 A 103	Rahul Riya Darsh

Cardinality: 3
Degree : 2

Degree = 10 (No. of columns)
Cardinality = 10 - 3 = 7 (no. of rows)

Degree: 3
Cardinality: 10 + 15- 5 = 20

Parameter	Usage
Dsn	Data source name. This usually includes the name of your database and the server where it is running.
Host	Host, or network system name, on which the database runs.
Database	Name of the database
User	User name for connecting to the database
Password	Password for the given user name

import mysql.connector
connection=mysql.connector connect(host= 'localhost', database--python_ db', user='pynative', password='pynative@#29')
cursor = connection.cursor(preparede=True)
#this will return MySQLCursorPrepared object

The wildcard operators are used with the LIKE operator to search a value similar to a specific pattern in a column. There are 2 wildcard operators.
% - represents 0, 1 or many characters
- - represents a single number or character

A join is a query that combines rows from two or more tables. In a join query, more than one tables are listed in FROM clause. The function of combining data from multiple tables is called joining. Joins are used when we have to select data from two or more tables

10.

Equi join is a simple SQL join condition that uses equal sign as a comparison operator.
Syntax
SELECTcolumnl, column2, column3
FROM Tablel, Table2
WHERE Tablel. column1 = Table2.column1;

SECTION C

Answer any 5 questions each question carries 3 marks

11.

A database consists of a number of tables. Each . table comprises of ro~s(records) and columns (attributes). Each record contains values for the corresponding attributes. The values of the attributes for a record are interrelated. For example, different cars have different values for the same specifications {length, color, engine capacity etc.)

12.

The hierarchical model was developed by IBM in 1968. The data is organised in a tree structure where the nodes tepresent the records and the branches of thetree represent the fields. Since the data is organized in a tree structure, the parent node has the links to its child nodes. If we want to search a record, we have to traverse the tree from the root through all its parent nodes to reach the specific record. Thus, searching for a record is very time consuming.

13.

A database management system (DBMS)is a set of programs that enables the users to define, create and maintain the database and provides controlled access to this database. The primary goal of DBMS is to provide a way to store and retrieve database information that is both convenient and efficient. Data in a database can be added, deleted, changed, sorted, searched, etc., using a DBMS,e.g.MySQL Ingres, MS-Access, Oracle, etc. The purpose of a DBMS is to bridge the gap between the information and data. The data stored in memory or on a disk must be converted to usable information.

14.

The need of database systems arose in the early 1960s,in response to the traditional file processing system. In the file processing system, the data is stored in the form of files and a number of application programs are written by programmers to add, modify, delete and retrieve data to and from appropriate files.New application programs are added to the system as the need arises. Now, suppose a need arises to keep additional information about the publishers of the books that include phone number and email id. Tofulfill this need, the system creates a new file PUBLISHER which includes name, address, phone number and e-mail id of publishers.

15.

(i) 2 Mumbai
2 Delhi
2 Moscow
(ii) MAX(DOB) MIN(DOB)
07-05-1993
08-12-1995
(iii) NAME GENDER
Sonal F
Store M

16.

(i) Self Join: It is a Jom where we join a particular table to itself. Here in this case, it is necessary to ensure that the join statement defines an ALIAS name for both the copies of tables to avoid column ambiquity.
(ii) Non-equi join: It is used to return the result from two or more tables, where exact join is not possible. The SQL non-equi join uses comparison operators instead of the equal sign like>, <, >=, <= along with conditions.
(iii) Natural Join: It is a type of equi join and is structured in such a way that, columns with same name of associated tables will appear once only

SECTION D

Answer any 2 questions each question carries 4 marks

17.

db MySQLdb.connect('localhost', 'PId' "PID@pwd', 'sales')
cursor = db.cursor()
sql = """Update Product set Price = Price-Su?"
try:
cursor.execute(sql)
db.commit()
except:
db.rollback()
cursor.close()
db.close()

18.

import MySQLdb
db = MySQLdb.connect('localhost', 'HRMan','HRMANexe@pwd', 'HTMdb')
cursor = db.cursor()
sql = """UPDATE Employee set Age=Age+l1
where SEX='%c' /('M')"""
try:
db.execute(sql)
db.commit()
except:
db.rollback()
db.close()

19.

(i) SELECT * FROM STUDENT ORDER BYNAME;
(ii) SELECT CLASS,DOB,CITY FROM STUDENT WHERE MARKS BETWEEN 450 AND 551
(iii)SELECT NAME,CLASS ,COUNT(*) FROM STUDENT GROUP BYCLASS HAVING MARKS>450;
(iv) UPDATE STUDENT SETMARKS= MARKS+20 WHERE class="XII";

SECTION E

Answer any 3 questions each question carries 5 marks

20.

(i) SELECT" FROM TRANSACTION WHERE TYPE ="Credit";
(ii) SELECT CNO,AMOUNT FROM TRANSACTION WHERE (MONTH (DOT) = "September" AND YEAR(DOT) =2017);
(iii) SELECT MAX (DOT) FROM TRANSACTION WHERE CNO="103":
(iv) SELECT CNO, CNAME, DOT FROM CUSTOMER.C, TRANSACTION.T
C.CNO=T.CNO AND
AMOUNT> =2000;
(v) 4 4375
(vi) 101 2 1500
103 2 12000
(vii) 102 Surbhi Sinha
105 Roshan Singh
(viii) 101
103
102

21.

(i) SELECT * FROM MEMBER ORDER BY ISSUEDATE DESC;
(ii) SELECT CODE, BNAME FROM BOOK WHERE TYPE= 'Fiction';
(iii) SELECT COUNT(*), TYPE FROM BOOK GROUP BY TYPE;
(iv) SELECT MNAME, ISSUE DATE FROM MEMBER WHERE ISSUEDATELike '2017 %';
(v) MAX (ISSUE DATE)
2017-02-23
(vi) DISTINCT (TYPE)
Fiction
Literature
Comic
(vii)

CODE	BNAME	MNO	MNAME
Ll02	German easy	M101	RAGHAV SINHA
F102	Untold Story	M103	SARTHAKJOHN
ClO1	Tarzan in the lost World	M102	ANISHA KHAN

(viii)B NAME
German easy

22.

(i) SELECT NAME FROM COMPANY WHERE COMPANY.CID=CUSTOMER. CID AND PRICE < 30000;
(ii) SELECT NAME FROM COMPANY ORDER BYNAME DESC;
(iii) UPDATE CUSTOMER SET PRICE = PRICE + 1000 WHERE NAME LIKE 'S%';
(iv) ALTERTABLECUSTOMER ADD TOTALPRICE DECIMAL(10,2);
(v)

3	DELHI
2	MUMBAI
1	MADRAS

(vi) 50000, 70000
(vii) 13.33
(viii)

MOBILE	MUMBAI	70000
MOBILE	MUMBAI	25000

23.

(i) SELECT TEACHERNAME,PERIODS FROM SCHOOL WHERE PERIODS> 25;
(ii) SELECT * FROM SCHOOL ORDER BY EXPERIENCEDESC;
(ill) SELECT DISTINCT DESIGNATION FROM ADMIN;
(iv) SELECTTEACHERNAME,CODE, DESIGNATION FROM SCHOOL, ADMIN WHERE SCHOOL.CODE = ADMIN.CODE AND GENDER = MALE;

SECTION A

Sec A consists of 18 questions each question carries 1 marks

24.