UNIT 3

DATABASE MANAGEMENT

A database is an organized collection of interrelated data that serves many applications. Its is generally
a computer record keeping system. In a database we can not only store the data but we can also change
the data as per the user requirement. These databases are generally managed by special software called
DBMS (Database Management System)

Database Management System (DBMS):

It is software which is responsible for storing, manipulating, maintaining and utilizing the databases.
Database System:
A database along with the DBMS is referred to as database system.
Eg: Oracle, MySQL Server, MySQL, Sybase, SQLite, PostgreSQL, FoxPro, SAP , dBase
Need for DBMS:
• Databases reduce redundancy i.e. it removes the duplication of data.
85 | P a g e
 • Database controls inconsistency i.e. when two copies of the same data do not agree to each other
it is called inconsistency. By controlling redundancy, the inconsistency is also controlled
• Databases allows sharing of data
• Database ensures data security by the process of authentication and does not allow unauthorized
access. Database Maintains integrity
• Database is maintained in a standard format which helps to interchange the data between two
systems.

RELATIONAL DATABASE MODEL

In Relational Database Model, the data is stored in the form of tables i.e. rows and columns.
• In Relational Database Model a table is referred to as a Relation.
• In Relation Database Model a column is referred to as an attribute
• In relational database model a row is referred to as a tuple.

Relation: A Relation is logically related data organized in the form of tables.

Attribute/ Field: Column of a table is called Attribute or Field.
Tuple/ Entity/ Record: Rows of a table is called Tuple or Record.
Domain: It is collection of values from which the value is derived for a column.
Degree - Number of columns (attributes) in a table.
Cardinality - Number of rows (Records) in a table.
Keys:
In a relation each record should be unique i.e. no two records can be identical in a database. A key
attribute identifies the record and must have unique values.
Primary Key – A primary is an attribute or set of attributes in a relation that uniquely identifies tuples
(rows) in that relation.
Candidate Key –It is an attribute or a set of attributes or keys participating for Primary Key, to uniquely
identify each tuples in that relation.
Alternate Key – A candidate key that is not the primary key is called alternate key or secondary key.
Foreign Key – Foreign keys are the attributes of a relation that points to the primary key of another
relation

86 | P a g e
Multiple choice Questions(MCQ):
1 DBMS stands for_____________
a)Data Base Management Software b) Data Base Maintenance System
c)Data Basic Management System d) Data Base management system
2 In RDBMS, R stands for_________
a)Relational b) Rotational c) Rational d)None of the above
3 A Database contains one or more_______
a)Data b) Tables c) Files d)Links
4 What is not true in respect of DBMS?
a)Database enforces standards b)Database increases redundancy
c)Database facilitates sharing of data d) Database helps to maintain integrity
5 Cardinality is total ___________
a)number of rows in a table b)number of columns in a table
c)number of data items in a table d) none of the above
6 Degree refers to total_________
a) number of rows in a table b) number of columns in a table
c) number of data items in a table d) none of the above
7 Data about data is _________
a) Data redundancy b) Meta Data
b) Database schema d) None of the above
8 Repetition of data is called _________
a) Data redundancy b) Data Description
c) Data inconsistency d) None of the above
9 Mismatched redundant copies of data is known as data ___________
a)Dependence b) Inconsistency c) Isolation d) Redundancy
10 A ________________is an organized collection of structured data.
a)Database b) File c) DBMS d) Information
11 A data ______________ is a set of rules that define valid data.
a)Query b) Constraint c) Dictionary d) All of the above
12 A relational database consists of a collection of ______________
a)Fields b) Records c) Keys d) Tables
13 A row in a database is called ______________
a)Fields b) Records c) Keys d) Tables
14 The term ____________ is used to refer to a field in a table.
a)Attribute b) Row c) Tuple d) Instance
15 Which of the following statements is not true about relational database?
a) Relational data model is the most widely used data model.
b) The data is arranged as a collection of tables in relational database.
c) Relational database increases data redundancy and inconsistency.
d) None of the above.
ANSWERS:

1 d 2 a 3 b 4 b
5 a 6 b 7 b 8 a
9 b 10 a 11 b 12 d
13 b 14 a 15 c

87 | P a g e
Very Short Answer Questions
1 What is meant by a database?
2 Define primary key?Give an example.
3 What do you mean by candidate key?
4 What is meant by degree and cardinality of a table?
5 What is meant by DBMS?
6 What is meant by database schema?
7 What is meant by data constraint?
8 What is meant by relation?
Very Short Answer Questions: Answers
1 A database is an organized collection of structured information, or inter-related data,
typically stored in a computer system.
2 A primary key is a column or set of columns that contain values that uniquely identify
each row in a table.
For example Rno can be primary key of the table student.
Table:Student
RNO NAME MARK
100 Tanay 30
101 Kiran 50
102 Manu 30
3 It is an attribute or a set of attributes capable of being the Primary Key, to uniquely
identify each record in that table.

4 Degree refers to the number of attributes/columns in a relation. Cardinality refers to the

number of tuples/rows in a relation.
5 RDBMS (Relational Database Management System) is the software used to store,
manage, query, and retrieve data stored in a relational database.
6 Database schema is also called the visual or logical architecture as it tells us how the data
are organized in a database.
7 Restrictions or limitations on the type of data that can be inserted in one or more
columns of a table to ensure accuracy and reliability of data in the database.
8 A relation is a named, two dimensional table storing logically related data.

STRUCTURED QUERY LANGUAGE(SQL)

SQL(Structured Query Language) is a language that is used to manage data that is held in a relational
database management system. It uses tables to manipulate and retrieve information from databases for
analysis.
By using SQL commands, one can search for any data in the database and perform other functions
like creating tables, adding records, modifying data, removing rows, dropping tables etc.
• SQL can execute queries against a database
• SQL can retrieve data from a database
• SQL can insert, update and delete records in a database
• SQL can create new databases,new tables in a database
• SQL can create stored procedures in a database
• SQL can create views in a database
88 | P a g e
 • SQL can set permissions on tables, procedures, and views
SQL Commands
• SQL commands are instructions. It is used to communicate with the database. It is also used
to perform specific tasks, functions, and queries of data.
• SQL can perform various tasks like create a table, add data to tables, drop the table, modify
the table, set permission for users.
Types of SQL Commands
• There are five types of SQL commands: DDL, DML, DCL, TCL

DDL or Data Definition Language

DDL or Data Definition Language actually consists of the SQL commands that can be used to define the
database schema. It simply deals with descriptions of the database schema and is used to create and modify
the structure of database objects in the database. DDL is a set of SQL commands used to create, modify,
and delete database structures but not data.
List of DDL commands:
CREATE: This command is used to create the database or its objects (like table, index, function,
views, store procedure, and triggers).
DROP: This command is used to delete objects from the database.
ALTER: This is used to alter the structure of the database.
DML (Data Manipulation Language):
The SQL commands that deal with the manipulation of data present in the database belong to DML or
Data Manipulation Language and this includes most of the SQL statements. It is the component of the
SQL statement that controls access to data and to the database. Basically, DCL statements are grouped
with DML statements.
List of DML commands:
INSERT : It is used to insert data into a table.
UPDATE: It is used to update existing data within a table.
DELETE : It is used to delete records from a database table.
Difference between DDL and DML:
DDL DML
It stands for Data Definition Language. It stands for Data Manipulation Language.

89 | P a g e
 It is used to create database schema and can be used to It is used to add, retrieve or update the data.
define some constraints as well.
It basically defines the column (Attributes) of the table. It add or update the row of the table. These
rows are called as tuple.
DATATYPES
• Text Data types
Char(size) – fixed length of size bytes
Varchar(size)-variable length of size bytes
Varchar2(size)-variable length of size bytes
• Number Data types
Integer(size)or Int- It represents a number without decimal point
Float(Size)-It represents a floating point number
Real-Same as float but no size argument is used
• Date data type
Date , Time

CONSTRAINTS
A Constraint is a condition or check applicable on a field or set of fields.
Types of Constraints:
• Unique Constraint :-This ensures that no rows have the same value in the specified column(s)
Example
CREATE TABLE EMP (ecode integer unique,
ename char(20),sex char(1),
grade char(2));
Unique constraint applied on ecode of EMP table ensures that no rows have the same ecode
value .
• Primary key Constraint:-
This declares a column as the primary key of the table. This is similar to unique constraint except
that one column (or one group of columns) can be applied in this constraint .
The primary key cannot allow NULL values but Unique key allows NULL values.
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is
created:
CREATE TABLE Persons
(ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int PRIMARY KEY (ID));
• Not null: -This constraint ensures column should not be NULL
Example:
CREATE TABLE EMP(
ecode integer Not null unique,
ename char(20),
sex char(1),
grade char(2));

90 | P a g e
DATABASE COMMANDS IN MYSQL
• CREATE DATABASE
CREATE DATABASE is the SQL command used for creating a database in MySQL.
Imagine you need to create a database with name “movies”. You can create a database in MySQL by
executing following SQL command
Syntax: mysql>CREATE DATABASE movies;
• SHOW DATABASES
You can see list of existing databases by running following SQL command.
Syntax: mysql>SHOW DATABASES;
• USE
You can use SQL command USE to select a particular database.
Syntax: mysql>USE database_name;
• DROP DATABASE
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax: mysql>DROP DATABASE database_name;
CREATE TABLE
The CREATE TABLE statement is used to create a new table in a database.
Syntax:
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ....);
Example: The following example creates a table called "Persons" that contains five columns:
PersonID, LastName, FirstName, Address, and City:
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255) );
SHOW TABLES
We can get the number of table information of a database using the following statement:
mysql> SHOW TABLES;
DESCRIBE TABLE
Use the DESCRIBE command to show the structure of the table, such as column names, constraints on
column names, etc. The DESC command is a short form of the DESCRIBE command. Both DESCRIBE and
DESC commands are equivalent.
Syntax The following are the syntax to display the table structure:
mysql> DESCRIBE | DESC table_name;
ALTER TABLE
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. The ALTER
TABLE statement is also used to add and drop various constraints on an existing table.
ALTER TABLE - ADD Column/Attribute
• ALTER TABLE - ADD A COLUMN
ALTER TABLE table_name ADD column_name datatype;
Example ALTER TABLE Customers
ADD Email varchar(255);

• ALTER TABLE - DROP COLUMN

91 | P a g e
 To delete a column in a table, use the following syntax
ALTER TABLE table_name DROP COLUMN column_name;
Example ALTER TABLE Customers DROP COLUMN Email;

• ALTER TABLE- ADD PRIMARY KEY

To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the
following SQL:
ALTER TABLE table_name ADD PRIMARY KEY (Column_name);
Example : ALTER TABLE Persons ADD PRIMARY KEY (ID);

• ALTER TABLE-DROP PRIMARY KEY

To drop a PRIMARY KEY constraint, use the following SQL:
ALTER TABLE table_name DROP PRIMARY KEY;
Example : ALTER TABLE Persons DROP PRIMARY KEY;

DROP TABLE
The DROP TABLE statement is used to drop an existing table in a database.
Syntax DROP TABLE table_name;
DROP TABLE Shippers;

INSERT:
The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Syntax:
It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

2. If you are adding values for all the columns of the table, you do not need to specify the column names
in the SQL query. However, make sure the order of the values is in the same order as the columns in
the table. Here, the INSERT INTO syntax would be as follows:
INSERT INTO table_name VALUES (value1, value2, value3, ...);

DELETE:
The DELETE statement is used to delete existing records in a table.
DELETE Syntax:
DELETE FROM table_name WHERE condition;
Note: Be careful when deleting records in a table! Notice the WHERE clause in the DELETE statement.
The WHERE clause specifies which record(s) should be deleted. If you omit the WHERE clause, all
records in the table will be deleted!
The following SQL statement deletes all rows in the "Customers" table, without deleting the table:
DELETE FROM Customers;

UPDATE
The UPDATE statement is used to modify the existing records in a table.
UPDATE Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE Table
92 | P a g e
The following SQL statement updates the first customer (CustomerID = 1) with a new contact person
and a new city.
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;

SELECT
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
SELECT Syntax: SELECT column1, column2, ... FROM table_name;
Here, column1, column2, ... are the field names of the table you want to select data from. If you want to
select all the fields available in the table, use the following syntax:
SELECT * FROM table_name;

WHERE Clause:
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
WHERE Syntax: SELECT column1, column2, ...FROM table_name WHERE condition;

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:
Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
BETWEEN Between a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column
AND, OR and NOT Operators
The WHERE clause can be combined with AND, OR, and NOT operators.
The AND and OR operators are used to filter records based on more than one condition:
• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.
• The NOT operator displays a record if the condition(s) is NOT TRUE.
AND Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
NOT Syntax
SELECT column1, column2, ...
93 | P a g e
 FROM table_name
WHERE NOT condition;

IN Operator
The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

BETWEEN Operator
The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
BETWEEN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

LIKE Operator
The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
• The percent sign (%) represents zero, one, or multiple characters
• The underscore sign (_) represents one, single character
The percent sign and the underscore can also be used in combinations!
LIKE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

LIKE Operator Description

WHERE CustomerName LIKE 'a%' Finds any values that start with "a"
WHERE CustomerName LIKE '%a' Finds any values that end with "a"
WHERE CustomerName LIKE '%or%' Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%' Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a_%' Finds any values that start with "a" and are at least 2
characters in length
WHERE CustomerName LIKE 'a__%' Finds any values that start with "a" and are at least 3
characters in length
WHERE ContactName LIKE 'a%o' Finds any values that start with "a" and ends with "o"

SQL Aliases
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of that query.
An alias is created with the AS keyword.
Alias Column Syntax: SELECT column_name AS alias_name FROM table_name;

94 | P a g e
Alias Table
Syntax: SELECT column_name(s) FROM table_name AS alias_name;

DISTINCT
The SELECT DISTINCT statement is used to return only distinct (different) values.
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the
different (distinct) values.
SELECT DISTINCT Syntax:
SELECT DISTINCT column1, column2, ...FROM table_name;
SELECT Example Without DISTINCT
The following SQL statement selects all (including the duplicates) values from the "Country" column in
the "Customers" table:
Eg: SELECT Country FROM Customers;
Now, let us use the SELECT DISTINCT statement and see the result.
SELECT DISTINCT Examples
The following SQL statement selects only the DISTINCT values from the "Country" column in the
"Customers" table:
SELECT DISTINCT Country FROM Customers;
The following SQL statement lists the number of different (distinct) customer countries:
SELECT COUNT(DISTINCT Country) FROM Customers;

NULL value
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or update a record without adding a
value to this field. Then, the field will be saved with a NULL value. It is not possible to test for NULL
values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
IS NULL Syntax
SELECT column_names FROM table_name WHERE column_name IS NULL;
IS NOT NULL Syntax
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
The IS NULL Operator
The IS NULL operator is used to test for empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address" field:
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NULL;
The IS NOT NULL Operator
The IS NOT NULL operator is used to test for non-empty values (NOT NULL values).
The following SQL lists all customers with a value in the "Address" field:
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address IS NOT NULL;

ORDER BY
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in
descending order, use the DESC keyword.
ORDER BY Syntax
SELECT column1, column2, ...
FROM table_name
95 | P a g e
 ORDER BY column1, column2, ... ASC|DESC;
Example
SELECT * FROM Customers
ORDER BY Country;
ORDER BY DESC Example
The following SQL statement selects all customers from the "Customers" table, sorted DESCENDING by
the "Country" column:
SELECT * FROM Customers
ORDER BY Country DESC;
ORDER BY Several Columns Example
The following SQL statement selects all customers from the "Customers" table, sorted by the "Country"
and the "CustomerName" column. This means that it orders by Country, but if some rows have the same
Country, it orders them by CustomerName:
Eg: SELECT * FROM Customers ORDER BY Country, CustomerName;

AGGREGATE (GROUP) FUNCTIONS

• Aggregate functions are the functions that operate on a set of rows to give one result per group.
• These sets of rows on which group function is applied may be the whole table or the table split
into groups.
Types of Group Functions
Function Description
sum( ) Find the sum of numeric values
avg( ) Find the average of numeric values
count( ) Counts the number of rows in a table
max( ) Find the maximum among all the values in a column
min( ) Find the minimum among all the values in a column
Remember the following points about group functions:
• All group functions, except count(*) ignore NULL values
• Functions -sum( ),avg( ) are used with NUMERIC data.
• Functions -min( ) and max( ) can be used with any data type.
Example: Consider the following table employee

sum( ), average( ), minimum( ), maximum( ) function

Q: Find the sum, average, minimum, maximum value of salaries of employees in the employee table

96 | P a g e
count( ) function
Count ( ) has got three formats:

count(*)
This function returns the number of rows in the table that satisfy the criteria of select statement.
In its counting, it includes duplicate rows and rows with NULL values in any of the column
Example:
Q: Count the number of employees in the employee table.

count(<col name>)
This function returns the number of not null values in the specified column, but includes duplicate values
in counting
Example

Q: Count the number of grades of employees in the employee table.

count(DISTINCT <col name>)

This function returns the number of unique, not null values in the specified column.
Example
Q: Count the number of different grades of the employee

97 | P a g e
Grouping Records (Group by clause)
• To divide the rows in a table into smaller groups of information, group by clause is used.
• It combines all identical rows in a group of fields.
• A column name is used for grouping
Syntax: -
SELECT [DISTINCT] <COL LIST> FROM <TABLE NAME>
[WHERE <CONDITION>]
[GROUP BY < GROUP BY EXPR>]
[HAVING <CONDITION>]
ORDER BY <COL NAME>/<EXPR> ASC/DESC];
NOTE -
• Group by expression specifies the columns whose values determine the basics for grouping rows
• WHERE clause is always before GROUP BY if required.
Example
Q. Display the no of employees in each zone.

Q. Display the no of employees in each zone whose salary is greater than 32000

Having clause
• This clause is used to restrict rows resulting after grouping.
• Steps followed in execution of select with group by and having clause-
1. Rows are grouped according to the columns in the group by clause.
2. Then the group function is applied.
3. Groups matching with having clauses are displayed.

98 | P a g e
Example
Q. Display only whose departments with sum of salaries whose total salary is greater than 70000.

Cartesian Product (Cross Join or Unrestricted Join)

• Returns all the rows in the two tables listed in the query.
• Each row of the first table is paired with all the rows in the second table.
• This happens when there is no relationship between two tables.
Example- Consider the following tables

Q: To display the name of the employees and their department name.

99 | P a g e
JOINS IN MYSQL
• A join is used when data from two or more tables is required.
• Rows in one table can be joined to the rows in another table based on the common values existing
in corresponding columns of two tables.
• Joins are used to retrieve data from tables related to each other with primary- foreign key
relationships.
• There are many types of joins:
EQUI JOIN
• Specified columns from the joining tables are checked for equality.
• Values from joining tables are retrieved only if the condition in where clause is satisfied.
SYNTAX:-
SELECT <column_name (s)>
FROM <table_name1>, <table_name2>, ...., <table_nameN>
WHERE <table_name1>.<column_name> = <table_name2>.<column_name>;
Q: To display the name of the employee and their department

Note-You should always qualify the columns when joining tables having the same name as
corresponding columns. To qualify the columns we use “.” (dot) operator.
Natural Join
This clause is based on all the columns in the two tables that have the same name. It selects the rows
from two tables that have equal values in the matched columns.
SYNTAX:-
SELECT [column_names / *] FROM table_name1 NATURAL JOIN table_name2;
Example- consider the same tables employee and department.
Q: To display the name of employee and department of all employee.

Note-No need to specify the column names to join. Works with same column name in both the tables.
The Resulting table has unique columns.

100 | P a g e
MULTIPLE CHOICE QUESTIONS
1 Which of the following SQL commands is used to use/select a particular database?
a. use b. select c. view d. project
2 Which SQL command is used to define and maintain physical structure or schema of table
in database like creating, altering and deleting database object such as table and
constraints?
a. DDL b. DML c. DCL d. TCL
3 Which commands is used to show all table in current using database?
a. display tables; b. show tables; c. view tables; d. select all tables;
4 Identify the MySQL Commands that belongs to DML category :
a. ALTER b. DROP c. DELETE d. CREATE
5 Which command is used in where clause to search NULL values in a particular column?
a. IS NULL b. IN NULL c. NOT NULL d. IS NOT NULL
6 Wild card operator (%,_) are used with?
a. count b. max c. like d. min
7 Prapti is presently working in the database SUBJECT. She wants to change and go to
the database RECORD. Choose the correct statement in MySQL to go to the database
RECORD.
a. GO TO DATABASE RECORD; b. USE DATABASE RECORD;
c. CHANGE DATABASE RECORD; d. USE RECORD;
8 Which SQL clause is used in database table to eliminate duplicate rows from the query
result?
a. group by b. distinct c. describe d. duplicate
9 Which SQL function is used to count the entire number of row in database table?
a. count b. count(*) c. max d. min
10 Which SQL function is used to determine the no. of row or non-null values?
a. min b. max c. count d. sum
ANSWERS
1 a 2 a 3 b 4 c 5 a
6 c 7 b 8 b 9 b 10 c
ASSERTION AND REASONING QUESTIONS
Directions: In the following questions, A statement of Assertion (A) is followed by a statement of
Reason (R). Mark the correct choice as:
(A) Both A and R are true and R is the correct explanation of A
(B) Both A and R are true and R is not the correct explanation of A
(C) A is true but R is false
(D) A is false but R is true
1 Assertion(A): The resultset refers to a logical set of records that are fetched from the
database executing an SQL Query.
Reason (R): Resultset stored in a cursor object can be extracted by using fetch(…)
functions.
2 Assertion(A): In SQL, aggregate function avg( )calculates the average value on a set of
values and produce a single result.
Reason (R): The aggregate functions are used to perform some fundamental arithmetic
tasks such as min( ), max( ), sum( ) etc.

101 | P a g e
 3 Assertion(A): Primary key is a set of one or more attributes that identify tuples in a
relation.
Reason (R): The primary key constraint is a combination of the NOT NULL and UNIQUE
constraints.
4 Assertion(A): Foreign key is a non-key attribute whose value is derived from primary key
of another table.
Reason (R): Each foreign key refers a candidate key in a relation.
5 Assertion(A): The SELECT statement in SQL is used to retrieve data from one or more
tables.
Reason(R): The SELECT statement can be used to retrieve all columns or a subset of
columns from a table.
ANSWERS
1 b 2 B 3 a 4 b 5 a
SHORT ANSWER TYPE QUESTIONS
1 Deepika wants to remove all rows from the table BANK. But she needs to keep the structure
of the table. Which command is used to implement the same?
2 While creating table ‘customer’, Rahul forgot to add column ‘price’. Which command is used
to add new column in the table. Write the command to implement the same.
3 Mitali is a database programmer, She has to write the query from EMPLOYEE table to search
for the employee who are not getting any commission, for this she has written the query as:
SELECT * FROM EMPLOYEE WHERE commission=null; But the query is not producing the
correct output, help her and correct the query so that she gets the desired output.
4 Which clause is used to eliminate the duplicate rows from output?
5 Which command is used to see information like name of columns, data type, size.
6 Differentiate between order by and group by clause in SQL with appropriate example.
7 Categorize the following commands as DDL or DML: INSERT, UPDATE, ALTER, DROP.
8 Muneer has created a database “school” and table “student”. Now he wants to view all the
databases present in his laptop. Help him to write SQL command for that , also to view the
structure of the table he created
9 Ms. Minakshi has just created a table named “Staff” containing Columns Sname,
Department and Salary. After creating the table, she realized that she has forgotten to add a
primary key in the table. Help her in writing an SQL command to add a primary key - StaffId
of integer type to the table [Link], write the command to insert the following record
in the table: StaffId – 111,Sname- Shalu,Department: Marketing,Salary: 45000
10 Meera working as database developer in Syntel Pvt Ltd Company Agra. She is designing as
SQL table names A & B .If a MySQL table A has 5 columns and 6 rows and another table B has
3 columns and 4 rows, then what will be the degree and cardinality of the cartesian product
of A and B?
11 Sunil decides to delete phoneno column from a table student . Write the SQL command to
remove the column in the student table. Also mention the type of SQL command.
12 Write SQL command to remove the Primary Key constraint from a table, named M_ID is the
primary key of the table MOBILE.
13 Write SQL command to make the column M_ID the Primary Key of an already existing table,
named MOBILE.
14 What constraint should be applied on a table column so that duplicate values are not allowed
in that column, but NULL is allowed.

102 | P a g e
ANSWERS:
1 delete from BANK;
2 ALTER TABLE customer add price int;
3 SELECT * FROM EMPLOYEE WHERE commission is null;
4 Distinct
5 DESCRIBE or DESC
6 ORDER BY is used to sort the result set based on one or more columns, either in ascending
(ASC) or descending (DESC) order whereas group by is used to group rows that have the
same values in specified columns into summary rows, often used with aggregate functions.
Eg: SELECT name, salary FROM employees ORDER BY salary DESC;
This will list all employees and their salaries, sorted by salary from highest to lowest.
SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY
department;
This will group employees by department and show the average salary for each department.
7 DDL –ALTER , DROP
DML-INSERT , UPDATE
8 SHOW DATABASES;
SHOW TABLES;
9 ALTER TABLE Staff ADD StaffId INT;
ALTER TABLE Staff ADD CONSTRAINT pk_staff PRIMARY KEY (StaffId);
INSERT INTO Staff (StaffId, Sname, Department, Salary) VALUES (111, 'Shalu', 'Marketing',
45000);
10 Degree = 8 Cardinality = 24
11 ALTER TABLE student DROP phoneno
It’s a DDL command.
12 ALTER TABLE mobile DROP PRIMARY KEY;
13 ALTER TABLE mobile ADD PRIMARY KEY (M_ID);
14 UNIQUE constraint makes sure that duplicate values are not allowed in that column, but NULL
will be allowed.
SHORT ANSWER QUESTIONS II
1 Meenu has been entrusted with the management of NSE Database. She needs to access some
information from STOCK and TRADERS tables for a survey analysis.
Help him extract the following information by writing the desired SQL queries as mentioned
below.

Table Name: TRADERS

TCODE TNAME CITY
T01 RELIANCE DIGITAL MUMBAI
T02 TATA DIGITAL BHUBANESWAR
T03 BIRLA DIGITAL NEW DELHI
Table name: STOCK
SCODE SNAME QTY PRICE BRAND TCODE
1001 COMPUTER 90 45000 DELL T01
1006 LCD PROJECTOR 40 42000 NEC T02
1004 IPAD 100 55000 APPLE T01
1003 DIGITAL CAMERA 160 15000 SAMSUNG T02

103 | P a g e
 1005 LAPTOP 600 35000 HP T03
Write SQL queries for the following:
(i) Display the SNAME, QTY, PRICE, TCODE, and TNAME of all the stocks in the STOCK and
TRADERS tables.
(ii) Display the details of all the stocks with a price >= 35000 and <=50000 (inclusive).
(iii) Display the SCODE, SNAME, QTY*PRICE as the “TOTAL PRICE” of BRAND “NEC” or
“HP” in ascending order of QTY*PRICE.
iv) Display the number of stock items in each TCODE.
OR
To display the Cartesian Product of these two tables
2 Consider the following tables employees, empsalary.
Table : Employees
Empid Firstname Lastname Address City
010 Ravi Kumar Raj nagar GZB
105 Harry Waltor Gandhi nagar GZB
152 Sam Tones 33 Elm St. Paris
215 Sarah Ackerman 440 U.S. 110 Upton
244 Manila Sengupta 24 Friends street New Delhi
300 Robert Samuel 9 Fifth Cross Washington
335 Ritu Tondon Shastri Nagar GZB
400 Rachel Lee 121 Harrison St. New York
441 Peter Thompson 11 Red Road Paris
Table: EmpSalary
Empid Salary Benefits Designation
010 75000 15000 Manager
105 65000 15000 Manager
152 80000 25000 Director
215 75000 12500 Manager
244 50000 12000 Clerk
300 45000 10000 Clerk
335 40000 10000 Clerk
400 32000 7500 Salesman
441 28000 7500 salesman
Write the SQL commands for the following:
a) To show first name, last name, address and city of all employees who lives in Paris.
b) To display the details of Employees table in descending order of First name.
c) To display the first name, last name and salary of all employees from the tables
Employee and EmpSalary, who are working as Manager.
Give the Output of following SQL commands:
d) Select designation, sum(salary) from empsalary group by
designation having count(*) > 2;
OR
d) Select sum(benefits) from empsalary where designation =’clerk’;
3 Consider the table PRODUCT and CLIENT given below:
PR_ID PR_NAME MANUFACTURER PRICE QTY
BS101 BATH SOAP PEARSE 45.00 25
104 | P a g e
 SP210 SHAMPOO SUN SILK 320.00 10
SP235 SHAMPOO DOVE 455.00 15
BS120 BATH SOAP SANTOOR 36.00 10
TB310 TOOTH BRUSH COLGATE 48.00 15
FW422 FACE WASH DETOL 66.00 10
BS145 BATH SOAP DOVE 38.00 20
C_ID C_NAME CITY PR_ID
01 DREAM MART COCHIN BS101
02 SHOPRIX DELHI TB310
03 BIG BAZAR DELHI SP235
04 LIVE LIFE CHENNAI FW422
Write SQL Queries for the following:
i) Display the details of those clients whose city is DELHI
ii) Increase the Price of all Bath soap by 10
iii) Display the details of Products having the highest price
iv) Display the product name, price, client name and city with their corresponding
matching product Id.
4 Write SQL Commands for (a) to (e) and write the outputs for (f) on the basis of table:
FURNITURE
NO ITEM NAME TYPE DATEOFSTOCK PRICE DISCOUNT
1 White Lotus Double Bed 2002-02-23 3000 25
2 Pink feathers Baby Cot 2002-01-29 7000 20
3 Dolphin Baby Cot 2002-02-19 9500 20
4 Decent Office Table 2002-02-01 25000 30
5 Comfort zone Double Bed 2002-02-12 25000 30
6 Donald Baby cot 2002-02-24 6500 15
7 Royal Finish Office Table 2002-02-20 18000 30
8 Royal tiger Sofa 2002-02-22 31000 30
9 Econo sitting Sofa 2001-12-13 9500 25
10 Eating Paradise Dinning Table 2002-12-19 11500 25
1. To show all the information about the Baby cots from the furniture table.
2. To list the itemname which are priced at more than 15000 from the furniture table.
3. To list itemname and type of those items, in which dateofstock is after 2002-02-01 from
the furniture table in descending order of itemname
4. To display itemname and dateofstock of those items, in which the discount percentage
is more than 25 from the furniture table.

ANSWERS:
1 i)select sname, qty, price , [Link], tname
from stock, traders where [Link] = [Link];
ii) select * from stock where price between 35000 and 50000;
iii) select scode, sname, qty * price as "total price" from stock
where brand in ('nec', 'hp') order by qty * price asc;
iv) select tcode, count(*) as stock_count from stock group by tcode;
2 i) select firstname, lastname, address, city from employees where city = 'paris';

105 | P a g e
 ii) select * from employees order by firstname desc;
iii) select e. firstname, e. lastname, s. salary from employees e
join empsalary s on [Link] = [Link] where [Link] = 'manager';
iv) manager 215000
clerk 135000
or
32000
3 A) D_NAME
GUPTA
HANEEF
B) D_DEPT
ENT
MEDICINE
ORTHO
CARDIOLOGY
SKIN
C) D_NAME EXPERIENCE
DEEPTI 6
SUMAN 7
JOSEPH 10
GUPTA 12
HANEEF 12
VEENA 12
4 i) select * from client where city = 'delhi';
ii) update product set price = price + 10 where pr_name = 'bath soap';
iii) select * from product where price = (select max(price) from product);
iv) select p.pr_name, [Link], c.c_name, [Link] from product p join client c on p.pr_id = c.pr_id;
5 i) select * from furniture where lower(type) = 'baby cot';
ii) select item_name from furniture where price > 15000;
iii) select item_name, type from furniture where dateofstock > '2002-02-01'
order by item_name desc;
iv) select item_name, dateofstock from furniture where discount > 25;

INTERFACE PYTHON WITH MySQL DATABASE

1. Installation of [Link] module

a) USE COMMAND : pip install mysql_connector_python

b) After Installation, check by connecting to MYSQL.

import [Link] as m
con = [Link](host='localhost',user='root', passwd='1234', database='class')
106 | P a g e
 print(con.is_connected( ))
>>> True (OUTPUT)
The output came True, meaning successful connection with MYSQL has been established
2. Steps involved in MySQL-Python connectivity
1. Open python editor
2. Import the package required (import [Link])
3. Open CONNECTION to the database
4. Create a CURSOR instance
5. Execute the QUERY via CURSOR object
6. Extract data from CURSOR object
7. Clean up the environment
3. Importing [Link] in python
• import [Link]
Or
• import [Link] as m
Note: “m” is an alias, which can be used in place of “[Link]” whenever a function
of module “[Link]” is required. We can use any valid IDENTIFIER as an alias.
4. Open a connection to MySQL Database
To create connection, connect( ) function is used
Its syntax is:
import [Link] as m
con = connect (host= H , user= U, passwd= P , database= D)
• host ‘H’ means where the MySQL database is hosted, generally it is given as “localhost”
• user ‘U’ means user by which we connect with mysql generally it is given as “root”
• passwd ‘P’ is the password of MySQL for user “root”
• database ‘D’ is the name of database whose data(table) we want to use

After forming connection, check by using is_connected( )

is_connected( ) : This function returns True if connection has been formed.
import [Link] as m
con = [Link](host='localhost',user='root', passwd='1234', database='class')
print(con.is_connected( ))
>>> True (OUTPUT)
The output came True, meaning successful connection with MYSQL has been established.
5. Creating the CURSOR Object :
• cursor object in MySQL-Python connectivity acts as a middleman between your Python
code and the MySQL database.
• It's used to execute SQL queries and fetch results from the database.
• Cursor object can be created using Connection object.
import [Link] as m
con = [Link](host='localhost',user='root', passwd='1234', database='class')

107 | P a g e
 cur = [Link]( )
Here ‘cur’ is the cursor object created on ‘con’ connection object. What the Cursor Object Does:
a) Execute SQL Commands.
b) Fetch Result from Database.
6. Executing Queries using execute( ) function with cursor object.
• The execute( ) function accepts the QUERY in the form of a STRING.
• The RESULT of the query is received in the cursor object.
import [Link] as m
con= [Link](host='localhost', user='root', passwd='1234', database='class')
cur = [Link]( )
query = "SELECT * FROM users"
[Link](query)
7. Fetching the Result
• Fetching/Extracting the Data/Result stored inside CURSOR object after Executing the
QUERY.
• For this we have 3 functions which are applied on the CURSOR object ‘cur’.
a) fetchall( ): It will return list of all the records retrieved in tuple form.
b) fetchone( ): It will return one record from the result set.
c) fetchmany(n): It will return number of records as per value of n and by default only
one records in tuple form.
The SYNTAX for extracting data –
import [Link] as m
con= [Link](host='localhost', user='root', passwd='1234', database='class')
cur = [Link]( )
query = "SELECT * FROM users"
[Link](query)
data = [Link]( )
or
data = [Link]( )
or
data = [Link](n)
for row in data:
print(row) # Displaying Each Record of the TABLE.
As ROWS of the table are received inside a TUPLE, we can display each ROW by iterating over
data/result extracted from the cursor in python.

8. ROWCOUNT
• It is the property of the cursor object that returns the number of rows fetched from the
cursor object till that moment and not the number of records the executed query will give.
SYNTAX –
count = [Link]
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( ) # ASSUME TABLE HAS 10 ROWS

108 | P a g e
 [Link]("select * from student") # All 10 ROWS are now in CURSOR object
data = [Link]( ) # Only 1 ROW is fetched from CURSOR till now
print([Link]) # Output = 1
data = [Link](4) # Total 5 ROWS have been fetched from CURSOR
print([Link]) # Output = 5
data = [Link]( ) # Total 6 ROWS have been fetched from CURSOR
print([Link]) # Output = 6
data = [Link]( ) # Remaining 4 ROWS have been fetched as well
print([Link]) # Output = 10
9. commit( )
After executing insert or update query we must commit our transaction using commit method of
connection object.
Eg: [Link]( )
10. [Link]( )
Closing the connection, Since the database can keep limited number of connections at a time, we
must close the connection using connection_object.close( ).
Eg: [Link]( )

STATIC QUERIES
Queries which are formed without passing any python object/variable/data.
Now we can execute any MySQL query through Python. Below are few examples static queries.
a) TO CREATE A TABLE IN MYSQL USING PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("CREATE TABLE FEES (ROLLNO INT, NAME VARCHAR(20), AMOUNT INT);")
b) TO SHOW THE TABLES IN MYSQL USING PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("SHOW TABLES")
for data in cur:
print(data)
c) TO DESCRIBE TABLE STRUCTURE USING PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("DESC STUDENT")
for data in cur:
print(data)
d) TO EXECUTE SELECT QUERY USING A PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("select * from student")

109 | P a g e
 r=[Link]( )
while r is not None:
print(r)
r=[Link]( )
e) TO EXECUTE SELECT QUERY WITH WHERE CLAUSE USING A PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("select * from student where marks>90")
r=[Link]( )
count=[Link]
print("total no of rows:",count)
for row in r:
print(row)
f) TO UPDATE A DATA IN A TABLE USING PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("UPDATE STUDENT SET MARKS=100 WHERE MARKS=40")
[Link]( )
print([Link],"RECORD UPDATED")
g) TO DELETE A RECORD FROM THE TABLE USING PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("DELETE FROM STUDENT WHERE MARKS<50")
[Link]( )
print([Link],"RECORD DELETED")
h) TO DROP AN ENTIRE TABLE FROM MYSQL DATABASE USING PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("DROP TABLE STUDENT")
i) TO ADD A COLUMN IN THE EXISTING TABLE USING PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("ALTER TABLE STUDENT ADD AGE NT”)
[Link]( )
j) TO DROP A COLUMN FROM THE TABLE USING PYTHON INTERFACE
import [Link] as m
con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("ALTER TABLE DROP AGE ”)
[Link]( )
k) TO ALTER THE DATATYPE OF A COLUMN IN A TABLE USING PYTHON INTERFACE
import [Link] as m

110 | P a g e
 con =[Link](host="localhost",user="root", passwd="system", database="student")
cur=[Link]( )
[Link]("ALTER TABLE STUDENT MODIFY GRADE CHAR(3)")

PARAMETERIZED QUERIES
Parameterize the query to add python variables/object into the string query to access values as
per the user’s input.
Example: to display record of a particular rollno.
There are two ways to use parameterized queries:
a) with { }.format pattern
b) with fstring & { } braces
1) Using {}.format pattern
(a)
rn = int(input(‘Enter Roll no. ‘)) # SUPPOSE USER ENTERS 10 AS ROLL NO
query = "select * from student where rollno = { }".format(rn)
[Link](query)
Note: “Here the format function will assign the value 10 in place of { } braces inside the
string query. python will convert it to-> query = "select * from student where rollno = 10"

(b)
Suppose we want to display the data based on the column name & value given by user
col_name = eval(input(‘Enter Column Name ‘)) # User entered Rollno
col_value = eval(input(‘Enter Column Value ‘)) # User entered 12
query = "select * from student where { } = { }".format(col_name , col_value)
# python will convert it to ->>> query = "select * from student where Rollno = 12 "
[Link](query)

(c)
Suppose we want to display the data from a specific class & section given by user
cls = eval(input(‘Enter Class ‘)) # User entered 12
sec = eval(input(‘Enter Section ‘)) # User entered A
query = "select * from student
where class = { } and section = ‘{ }’ ".format(cls , sec)
# python will convert it to ->>> query = "select * from student
where class = 12 and section = ‘A’ "
[Link](query)

2) Using fstring & { } braces

(a)
rn = int(input(‘Enter Roll no. ‘)) # SUPPOSE USER ENTERS 10 AS ROLL NO
query = f"select * from student where rollno = {rn}"
[Link](query)

Note: Here we add ‘f’ before the string & write the variable inside { } braces which
assigns the value 10 in place of {rn} inside the string query. python will convert it to->
query= "select * from student where rollno = 10"
111 | P a g e
 (b)
Suppose we want to display the data based on the column name & value given by user
col_name = eval(input(‘Enter Column Name ‘)) # User entered Rollno
col_value = eval(input(‘Enter Column Value ‘)) # User entered 12
query = f”select * from student where { col_name } = { col_value } “
[Link](query)
Note: python will convert it to ->>> query = "select * from student where Rollno = 12 "

(c)
Suppose we want to display the data from a specific class & section given by user
cls = eval(input(‘Enter Class ‘)) # User entered 12
sec = eval(input(‘Enter Section ‘)) # User entered A
query = f ”select * from student where class = {cls} and section = ‘{sec}’ “
[Link](query)

Note: python will convert it to ->>> query = "select * from student where class = 12
and section = ‘A’ "

Multiple Choice Questions

1 Which of the following command is used to connect Python with MySQL?
a) [Link]( ) b) [Link]( )
c) [Link]( ) d) [Link]( )
2 Which module needs to be imported to use MySQL in Python?
a) mysql b) sqlite3 c) MySQL d) [Link]
3 What does the cursor( ) method do in MySQL Python connector?
a) Executes the SQL commands directly
b) Establishes a connection to the database
c) Creates a cursor object to interact with the database
d) Creates a database
4 Which method is used to execute SQL queries in Python?
a) executeQuery( ) b) run( ) c) execute( ) d) query( )
5 Which method is used to retrieve all rows from the executed query?
a) fetchone( ) b) fetchall( ) c) getrows( ) d) read( )
6 After performing an INSERT operation, which method is used to save the changes in the
database?
a) commit( ) b) save( ) c) flush( ) d) update( )
7 Which of the following is not a valid parameter in [Link]( )?
a) host b) username c) user d) password
8 What is the purpose of the close( ) method?
a) To close the database file b) To shut down the MySQL server
c) To close the cursor or connection d) To delete the database
9 Which of the following is correct for selecting a database after connection?
a) [Link]("mydb") b) [Link]("mydb")
c) [Link]("USE mydb") d) [Link]("mydb")
10 What is the output type of fetchall( )?
a) List of strings b) Tuple of strings
c) List of tuples d) Dictionary
112 | P a g e
Answers
1 C 2 D 3 C 4 C 5 B
6 A 7 B 8 C 9 C 10 C

Assertion Reasoning Questions

Each question contains Assertion (A) and Reason (R). Choose the correct option from:
a) Both A and R are true and R is the correct explanation of A.
b) Both A and R are true but R is not the correct explanation of A.
c) A is true but R is false.
d) A is false but R is true.

1 Assertion (A): The cursor( ) method is used to execute SQL queries in Python.
Reason (R): The cursor object allows interaction with the database.
2 Assertion (A): [Link]( ) is used to delete all records from a table.
Reason (R): fetchall( ) retrieves all records returned by a SELECT query.
3 Assertion (A): [Link]( ) requires parameters like host, user, password,
and database to connect successfully.
Reason (R): [Link]( ) is used to establish a connection between Python
and MySQL.
4 Assertion (A): commit( ) is used after every SELECT query to save the fetched results.
Reason (R): commit( ) saves changes made by INSERT, UPDATE, and DELETE operations.
5 Assertion (A): The close( ) method is mandatory after the database operations are
completed.
Reason (R): Keeping database connections open unnecessarily may lead to resource
leakage.
Answers
1 A 2 D 3 A 4 D 6 A

VERY SHORT ANSWER QUESTIONS

1 Which command is use to install MySQL library in python?
2 Which method we use to establish the connection?
3 Which statement we use to access the MySQL module?
4 What is the Database Connector?
5 Which function is used to check the successful connection?
Answers
1 pip install MySQL. Connector
2 connect( ) method with connection object.
3 import [Link]
4 A database connector is a software that connects an application to any database.
5 .is_connected( ) method

SHORT ANSWER QUESTIONS

1 What are the steps for creating database connectivity applications?
2 What is a connection? What is its role?
3 What is a resultset?

113 | P a g e
 4 What is a database cursor?
5 How to retrieve data from a table?
Answers
1 To create database connectivity, follow the given steps:
Step 1: Start Python
Step 2: Import [Link]
Step 3: Open a connection to the database
Step 4: Create a cursor instance
Step 5: Execute a query
Step 6: Extract data from result set
Step 7. Clean up the environment
2 A connection (represented by the connection object) is the session between the application
program and database. To do anything with database, one must have a connection object.
3 A result set refers to a logical set of records that are fetched from the database by executing
a query and made available to the application program.
4 A database cursor is a special control structure that facilitates row by row processing of
records in the result set, i.e., the set of records retrieved as per the query.
5 There are multiple ways to retrieve data:
i. fetchall( ) – fetches all the remaining rows of a query result, current pointer position
forwards
ii. fetchone( ) – fetches the next row as a sequence; returns None when no more data
iii. fetchmany(n) :It will return number of records as per value of n and by-default only one
record.
LONG ANSWER QUESTIONS
1 Write a Python code to connect to a database
2 How to create a database in MySQL through Python ?
3 Write the Python code to display the present databases in MySQL
4 How to create a table in MySQL through Python ?
5 Write the Python code to insert data into student table of database kvs .
6 How to fetch data in Python from a student table of education database?
7 Write the Python code to update a record in the student table of education database.
8 Write the Python code to delete a record from the student table of education database
9 [Link] want to interface python with mysql and write some code help him to write the
code
import_____________.connector as m #Line1
mydb=m.________(host=”localhost”,user=”root”,
passwd=”tiger”,database=” choo ”) # Line2
cursor=mydb.___________( ) #Line3
cursor._______________(“select * from student”) #Line4
data=cursor.__________( ) #Line5 To retrieved all records
count=cursor.__________ #Line6
10 What is the difference between fetchone( ), fetchmany( ), fetchall( )?
Answers
1 import [Link] as m
Mycon=[Link](host=”localhost”,user=”root”, password=”tiger”,database=”project”)
print(mycon)

114 | P a g e
 2 import [Link] as m
mycon= [Link](host=”localhost”,user=”root”,password=”tiger”)
cursor=[Link]( )
[Link](“create database education”)
[Link]( )
3 import [Link] as m
mycon= [Link](host=”localhost”,user=”root”,password=”tiger”)
cursor=[Link]( )
[Link](“show databases”)
for i in cursor:
print(i)
4 import [Link] as m
mycon= [Link](host=”localhost”,user=”root”, password=”tiger”)
cursor=[Link]( )
query = “create table student(admn_no int primary key, sname varchar(30), gender char(2),
DOB date, stream varchar(10), mark float”
[Link](query)
[Link]( )
5 import [Link] as m
mycon= [Link](host=”localhost”,user=”root”,password=”tiger”)
cursor=[Link]( )
no=int(input(“Enter admission no “))
n=input(“Enter name “)
g=input(“Enter gender ”)
dob=input(“Enter DOB “)
st=input(“Enter stream “)
m=float(input(“Enter m rk ”))
query= “insert into student values( {}, ‘{}’ , ‘{}’ , ’{}’ , ’{}’ , {} )”.format( no,n,g,dob, st,m)
[Link](query)
[Link]( )
6 import [Link] as m
mycon= [Link](host=”localhost”,user=”root”,password=”tiger”,
database = ‘education’)
cursor=[Link]( )
[Link](“select * from student”)
for row in cursor:
print(row)
7 import [Link] as m
mycon= [Link](host=”localhost”,user=”root”,password=”tiger”, database = ‘education’)
cursor=[Link]( )
[Link](“update student set marks =67 where admn_no=306”)
[Link]( )
8 import [Link] as m
mycon= [Link](host=”localhost”,user=”root”,password=”tiger”, database = ‘education’)
cursor=[Link]( )
[Link](“delete from student where admn_no=308 ”)
[Link]( )

115 | P a g e
 9 Line1:-mysql, Line2:-connect, Line3:cursor, Line4: execute, Line5: fetchall, Line6: rowcount
10 fetchone( )
• Returns: A single row from the result set as a tuple (or None if no more rows).
fetchmany(size)
• Returns: A list of up to size rows, each as a tuple.
fetchall( )
• Returns: A list of all remaining rows as tuples in the result set.

LONG ANSWER QUESTIONS

1 A table, named STATIONERY, in ITEMDB database, has the following structure:
Field Type
itemNo int(11)
itemName varchar(15)
price float
qty int(11)
Assume the following for Python-Database connectivity:
Host: localhost, User: root, Password: Pencil
Write the following Python function to perform the specified operation:
Add( ): To input details of an item and store it in the table STATIONERY.
Display( ): The function should then retrieve and display all records from the STATIONERY
table where the Price is greater than 120.
2 Sartaj has created a table named CLASS in MYSQL database, CLASS:
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, DB – SC
Sartaj, now wants to display the records of students whose fee is more than
5000. Help Sartaj to write the program in Python.
3 Kabir wants to write a program in Python to insert the following record in the table named CLASS
in MYSQL database SC, CLASS:
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, DB – SC
The values of fields rno, name, DOB and fee has to be accepted from
the user. Help Kabir to write the program in Python.
4 Rehaan wants to write a program in Python to insert the following record in the table named
EMP in MYSQL database COMPANY:
a. eno(Empno)- integer
b. ename(Name) - string
c. DOB (Date of birth) – Date
d. Salary – float
116 | P a g e
 Note the following to establish connectivity between Python and MySQL:
Username – root, Password – password, Host - localhost
The values of fields eno, name, DOB and salary has to be accepted from the user. Help Rehaan to
write the program in Python.

Answers:
1 import [Link] as m
mycon= [Link](host=”localhost”,user=”root”,password=”Pencil”)
mycursor=[Link]( )
def Add(mycursor):
[Link](‘use ITEMDB’)
n = int(input(‘Enter no. of Items to be entered: ’))
for i in range(n):
ino = int(input(‘Enter Item no: ’))
inm = input(‘Enter Item name: ’)
ipr = float(input(‘Enter Item price: ’))
iq = int(input(‘Enter Item quantity: ’))
query = f ”insert into STATIONARY values({ino},’{inm}’,{ipr},{iq})”
[Link](query)
[Link]( )
def Display(mycursor):
[Link](‘use ITEMDB’)
query = ‘Select * from STATIONARY where price > 120’
[Link](query)
data = [Link]( )
for record in data:
print(‘Item No = ‘ , record[0])
print(‘Item Name = ‘ , record[1])
print(‘Item Price = ‘ , record[2])
print(‘Item Qty = ‘ , record[3])
Add(mycursor)
Display(mycursor)
[Link]( )
2 def Display(mycursor):
query = ‘Select * from CLASS where Fee > 5000’
[Link](query)
data = [Link]( )
for record in data:
print(‘Roll No = ‘ , record[0])
print(‘Name = ‘ , record[1])
print(‘DOB = ‘ , record[2])
print(‘Fee = ‘ , record[3])
import [Link] as m
mycon=[Link](host=”localhost”,user=”root”,password=”tiger”, database=’SC’)
mycursor=[Link]( )
Display(mycursor)

117 | P a g e
 [Link]( )
3 def Add(mycursor):
n = int(input(‘Enter no. of Student Records to be entered: ’))
for i in range(n):
rn = int(input(‘Enter Student Roll No: ’))
nm = input(‘Enter Student name: ’)
dob = input(‘Enter Student DOB as ‘YYYY-MM-DD’ : ’))
fee = float(input(‘Enter Student Fee: ’))
query = f ”insert into CLASS values({rn},’{nm}’,’{dob}’,{fee})”
[Link](query)
[Link]( )
import [Link] as m
mycon=[Link](host=”localhost”,user=”root”,password=”tiger”, database=’SC’)
mycursor=[Link]( )
Add(mycursor)
[Link]( )
4 def Add(mycursor):
[Link](‘use COMPANY’)
n = int(input(‘Enter no. of Records to be entered: ’))
for i in range(n):
en = int(input(‘Enter Student Roll No: ’))
enm = input(‘Enter Student name: ’)
dob = input(‘Enter Student DOB as ‘YYYY-MM-DD’ : ’))
sal = float(input(‘Enter Student Fee: ’))
query = f ”insert into EMP values({en},’{enm}’,’{dob}’,{sal})”
[Link](query)
[Link]( )
import [Link] as m
mycon=[Link](host=”localhost”,user=”root”,password=”password”)
mycursor=[Link]( )
Add(mycursor)
[Link]( )

118 | P a g e
 Data Base Management(Questions and PYQ)

Q. Replicated data in Multiple data files in a Database is OR

called. In SQL which of the following will select only one copy
a)Data Redundancy b) Data inconsistency of each set of duplicate rows from a table?
c) Data Integrity d) None OR
The Select Statement ,when combined with………clause
Q. In a Relational database ,a table is referred to as. to eliminate duplicate records.
Ans :relation Ans: DISTINCT
[Link] is the minimum number column required to Q. While creating a table which constraint does not
create a table?. allow insertion of duplicate values in the table?
[Link] OR
Q. In a Relational database ,a row or record is referred Which of the following types of table constraints will
to as. prevent the entry of duplicate rows.
Ans: Tuple Ans: Unique
Q. In a Relational database ,a field or attribute is [Link] constraint is used to ensure that a column
referred to as. cannot have NULL values?
Ans: Column [Link] NULL
[Link] relational database consist of a collection of [Link] constraint make sure that all values in a column
Ans: Column/field/attribute satisfy certain criteria.
Q.A(An)……..in a Table represent a logical relationship Ans. Check.
among a set of values. Q which aggregate function in SQL displays the number
Ans:Keys of values in the specified column ignoring the NULL
[Link] term …….used to refer to a record in a table. values.
Ans: Tuple/row Ans: count()
[Link] term …….used to refer to a field in a table. Q which aggregate function in SQL displays the number
Ans: Column/attribute of values in the specified column not ignoring / including
Q.A ………is a property of the entire relation , which the NULL values.
ensure through its value that each tuple is unique in a OR
relation. In SQL the Aggregate function which will display the
Ans: Keys Cardinality of the table.
[Link] Primary key is selected from the set of…….. Ans: count(*)
OR Q. The Design of Database is known as…….
The Attribute which have all the properties to become a Ans: Database Schema
primary key. Q. A RDBMS must comply with at least……… rules.
Ans:Candidate Key Ans:6
Q………….refers to the attribute which uniquely identify a Note: According to E.F codd There are 13 rules for perfect
record/tuple/row in a table. RDBMS but no RDBMS follow all these [Link] must
Ans: Primary Key comply with at least 6 rules.
Q………… refers to the attribute whose value is derived [Link] command is used to change or switch the
from the primary key of some other table. database?
OR Ans Use Database_Name;
Which key ensure the referential integrity of the data Eg: let database name is employee.
into table. Use employee;
Ans:Foregin key Q. Which command is use to display all the databases in
Q. In Relational database Model the cardinality refers the SQL sever.
to………… Ans: Show databases;
Ans: Number of tuples /row/record [Link] command is use to delete the database.
Q. In Relational database Model the Domain refers Ans. Drop databasename;
to………… Eg: let database name is employee.
Ans: Number of columns/attributes /field drop employee;
Q. In a relational models, Cardinality actually refers to. Q Which command is use to create a table.
Ans: Number of Rows/Tuples Ans: Create
Q.……. Keyword is used to eliminate the duplicate from [Link] command is used to see information like name
the query result. of column,data type size etc?
OR OR
Which clause is used to eliminate the duplicate rows Which command is used to see the structure of a Table
from the output.? Ans: Describe/Desc
 Data Base Management(Questions and PYQ)

Q Which Command is use to add/Delete column or Q Which clause is used in SQL to filter the groups based
attribute in/from a table? condition.
Ans: Alter Command
[Link] command is use to delete a table from the Ans:Having
Database. Q which SQL filter used after records after the group by
Ans: Drop operation.
[Link] command is used to add new record or row in a
table? a) Order by b) Where c) Having d) Join
[Link] Q To find the total number of rows in a table including
[Link] command is use to select/display/ duplicates which aggregate function would you use
show/retrieve /fetch data from the table.
Ans. Select a) Sum() b) count() c) max() d) count(*)
[Link] of the following aggregate functions can be
[Link] command is used to change the existing
used in conjunction with the HAVING clause to filter
information or data of table?
groups based on their average value?
OR
Which command is use to modify the rows in the table? (A) COUNT() (B) SUM() (C) AVG() (D) MIN()
Ans: Update
[Link] command is use to delete the record or row [Link] aggregate function can be used to find the
from the table? cardinality of a table?
Ans: Delete (A) sum() (B) count() (C) avg() (D) max()
[Link] Clause use in Command to search for NULL
values in any column? Q Which SQL data type is used to store decimal number
Ans: IS NULL with exact precision.
[Link]……. Clause of select query allow us to select any
a) Float b) Double c) decimal d) varchar
these rows in the result satisfied condition.
Ans: Where Q Which SQL command is used to remove the table form
[Link] operator is used for pattern matching? database.
Ans:Like
[Link] clause is used to see the output of query in Ans:Drop
ascending and descending order. [Link] Datatype is most suitable for storing a persons
OR phone number.
The Clause which display the record from the table in
sorted form. a)varchar(n) b) char(n) c) text d) int
Ans: Order by
Q Which Datatype is most suitable for storing a persons
Q. which clause is used to group the column with
aggregate function. email address.
Ans. Group by a)varchar(n) b) char(n) c) text d) int
[Link] to remove the row from table student
Ans: Delete from student; Q which SQL data type is best suited for storing variable
[Link] does SQL stands for? length character string up to specified maximum.
Ans: Structured Query Language
a)varchar(n) b) char(n) c) text d) int
[Link] command is used to create a new database?
Ans. Create database database_name; Q Sagar wants to find records with NULL values in one of
[Link] will be the output of the following command the columns. Which clause does he have to use?
Select count(*) from employee;
a)The total number of employee in the table. Ans:NOT NULL

b)The number of employee with null value in id column. Q. Which SQL command is used to define a new table in
a database?
c) The number of employee with not null value in id
column. (A) INSERT INTO (B) UPDATE (C) ALTER TABLE (D) CREATE
TABLE
b) The average number of employee per department.
Ans. b)
 Data Base Management(Questions and PYQ)

[Link] is the output of the following SQL statement ? Q Which command is used to view all tables of
database?
Select Ang(salary) from employee where
department=’sales’; Ans: Show tables;
a)The average salary of all employee in the table. [Link] the table EMPLOYEES and DEPRTMENT and
answer the question .
b)The average salary of employee in the sales
department. Table:EMPLOYEES
c) The highest salary of all employee in the sales ENO ENAME DOJ DNO
department. E1 Nusrat 2001-11-21 D3
E2 Kabir 2005-10-25 D1
b) The lowest salary of all employee in sales department.
Ans. b)
Table:Department
[Link] is output of the following SQL statement ?
ENO DNAME
Select *from employee where salary between 40000 D1 ACCOUNTS
and 50000; D2 HR
D3 ADMIN
a)All employees whose salary is equal or greater than
40000 and equal or less than 50000.
1. What is the Degree of the table EMPLOYEES?
b)All employee whose salary is equal to 40000 or 50000.
What is the Cardinality of table DEPARTMENT?
c) All employee whose salary is exactly 40000 or 50000.
Ans: Degree of table Emplyees=4
b) All employee whose salary is exactly 40000 0r greater
Cardinality Of table Department=3
than 50000.
[Link] is primary key ? explain
Ans. a)
Ans: A primary key is a attribute which uniquely identify a
Q what is the full from of DDL and DML?
row or record which is never NULL or duplicate.
Ans:DDL(Data Definition Language)
[Link] is selection and projection?
DML(Data Manipulation Language)
Ans. Selection: Operation upon a relation to select a
[Link] is the output of the following command? horizontal subset of relation.
Select department, count(*) from employees group by Projection: Operation upon a relation to select a vertical
department having count(*)>5; subset of relation.

a)The number of employees in each department with QWrite the output of the following commands on table
more than 5 employees. CUSTOMERS and PURCHASES.
b) The department with more than 5 employee and their Table:CUSTOMERS
total number of employees.
CNO CNAME CITIES
c) The departments with less than 5 employees and their C1 Sanyam Delhi
total number of employee. C2 Shruti Delhi
C3 Mehar Mumbai
b) The number of departments with more than 5
C4 Sakshi Chennai
employee.
C5 Ritish Indore
Ans: b) C6 Rahul Delhi
C7 Ameer Chennai
Q Which SQL aggregate function is used to count all
C8 Minakshi Bangalore
record of a table.
C9 Anshul Mumbai
Ans: Count(*)
 Data Base Management(Questions and PYQ)

Table: Purchases OR
Sno Qty Pur_Date Cno SELECT COUNT(CNAME),CITIES FROM CUSTOMERS
S1 15 2018-12-25 C2 GROUP BY CITIES;
S2 10 2018-11-10 C1
iv) SELECT * FROM PURCHASE WHERE QTY>15;
S3 12 2018-11-10 C4
S4 7 2019-01-12 C7 Q.
S5 11 2019-02-12 C2
Table:Teachers
S6 10 2018-10-12 C6
TID NAME AGE DEPT DOJ SAL GENDER
S7 5 2019-05-09 C8 T118 Navin 40 Computer 2010- 12000 M
S8 20 2019-05-09 C3 01-10
T107 Chetna 37 History 2008- 20000 F
S9 8 2018-05-09 C9
03-24
S10 15 2018-11-12 C5 T105 Sandeep 46 Maths 2006- 30000 M
S11 6 2018-08-04 C7 12-12
T110 Sangeeta 35 History 2010- 25000 F
(i) Select count(distinct cities ) from customers; 07-01
(ii) Select max(Pur_Date) from Purchases; T101 Rudransh 42 Maths 2004- 40000 M
(iii) Select CNAME,Qty ,Pur_Date from 09-05
T121 Neeraj 38 Physics 2011- 28000 M
Customers,Purchases where 04-01
[Link]=[Link] and Qty [Link] show the information about the teachers of the
in(10,20); history department.
Ans (i) 5 [Link] list the names of teacher earing a salary between
Explanation: Total entry in cities column is 9 but distinct 20000 and 30000
value of cities is 5.
[Link] count the number of male teacher.
(ii) 2019-05-09
[Link] gender wise total number of teachers.
Explanation: max function will first search for maximum
year value that is 2019 in table then it will search for [Link] list the name , age of teachers of female teacher in
max month then max day. descending order of date of joining.

(iii) [Link] the salary by 10% for maths department.

CNAME Qty Pur_Date [Link] delete the record the teacher Neeraj.
Sanyam 10 2018-11-10
Ans.
Rahul 10 2018-10-12
[Link] * FROM TEAHERS WHERE
Mehar 20 2019-05-09
DEPT=’HISTORY’;
[Link] NAME FROM TEACHERS WHERE SAL
Q. Write SQL queries which are based on the table BETWEEN 20000 AND 30000;
Customers and Purchases given in question no. 26 [Link] COUNT(GENDER) FROM TEACHERS
WHERE GENDER=’M’;
i) To display details of all customers whose cities [Link] GENDER,COUNT(GENDER) FROM
are neither Delhi nor Mumbai TEACHERS GROUP BY GENDER;
ii) To display the CNAME and Cities of all [Link] NAME,AGE FROM TEACHERS
Customers is ascending order of their CNAME. WHERE GENDER=’F’ OREDR BY DOJ DESC;
iii) To display the numbers of Customers along with [Link] TEACHERS SET
their respective cities in each of the cities . SETL=SAL+(SAL*10)/100 WHERE
iv) To display details of all Purchases whose DEPT=’MATHS’;
Quantity is more than 15. 7. DELETE FROM TEACHERS WHERE
Ans: NAME=’NEERAJ’;

i) SELECT * FROM CUSTOMERS WHERE CITIES

NOT IN (DELHI,MUMBAI);
ii) SELECT CNAME,CITIES FROM CUSTOMERS
ORDER BY CITIES;
iii) SELECT COUNT(*),CITIES FROM CUSTOMERS
GROUP BY CITIES;
 Data Base Management(Questions and PYQ)

Q29. [Link] DISTINCT(DEPT)FROM HOSPITAL;

TABLE:Hospital
Pno Name Age Dept DOA charge Gen [Link] COUNT(*) FROM HOSPITAL GROUP BY DEPT;
1 Mayak 65 Surgery 23- 600 M Q.
02-
2018 Table:Trainer
2 Babita 24 ENT 01- 400 F TID TNAME CITY HIREDATE SALARY
10- 101 Sunaina Mumbai 1998-10-15 90000
2019 102 Anamika Delhi 1994-12-24 80000
103 Deepti Chandigar 2001-12-21 82000
3 Kashish 45 Ortho 19- 400 M h
12- 104 Meenakshi Delhi 2002-12-25 78000
2018 105 Richa Mumbai 1996-01-12 95000
4 Tarun 12 Surgery 01- 600 M 106 Maniparbha Chennai 2001-12-12 69000
10-
2018
Table:Course
5 Manisha 36 ENT 12- 400 F
01- CID CNAME FEES START DATE TID
2018 C201 AGDCA 12000 2018-07-02 101
6 Imran 16 ENT 24- 400 M C202 ADCA 15000 2018-07-15 103
02- C203 DCA 10000 2018-10-01 102
2018 C204 DDTP 9000 2018-09-15 104
7 Ankita Null Cardio 20- 800 F C205 DHN 20000 2018-08-01 101
08- C206 O LEVEL 18000 2018-07-25 105
2018 [Link] the Trainer Name ,City and salary in
8 Zoya 45 Gyneco 22- 500 F descending order of their hire date.
02-
2018 [Link] display the TNAME and city of trainer of joined
9 Kush 19 Cardio 13- 800 M institute in the month of December 2001.
01-
[Link] display the TNAME,HIRE DATE
2019
,CNAME,STARTDATE from the table Trainer and course
10 Shalini 31 Medicine 19- 300 F
whose fees is less or equal to 10000.
02-
2018 [Link] display number of trainer from each city.
[Link] show all the information about the
patients of cardiology Department. 5. select tid ,tname from trainer where city not in
[Link] list the names of female patients who (‘Delhi’,’Mumbai’)
are either in orthopaedic or surgery Ans:
department.
[Link] TNAME,CITY,SALARY FROM TRAINER ORDER BY
[Link] list the name of patients with their date of HIREDATE DESC;
admission in ascending order.
2. SELECT TNAME,CITY FROM TRAINER WHERE HIREDATE
[Link] count the number of patients with age>30 BETWEEN ‘2001-12-01, AND ‘2001-12-31’;
[Link] display various department [Link] TNAME,HIREDATE,CNAME,STARTDATE FROM
[Link] display the number of patients in each department. TRAINER,COURSE WHERE [Link]=[Link] AND
FEES<=10000;
ANS:[Link] * FROM HOSPITAL WHERE
DEPT=’CARDIOLOGY’; [Link] COUNT(*) FROM TRAINER GROUP BY CITY;

[Link] NAME FROM HOSPITAL WHERE GEN=”F” AND 5. SELECT TID ,TNAME FROM TAINER WHERE CITY NOT
DEPT IN(‘CAERDIOLOGY’,’SURGERY’); IN(‘DELHI’,’MUMBAI’);

[Link] NAME,DOA FROM HOSPITAL ORDER BY DOA;

[Link] COUNT(*) FROM HOSPITAL WHERE AGE>30; [Link] will be the output of the following statement
based on the table which are given in Q30.
 Data Base Management(Questions and PYQ)

1. SELECT DISTINCT TID FROM COURSE; ANS:

2. SELECT TID ,COUNT(*),MIN(FEES) FROM COURSE
1. MIN(PRICE) MAX(PRICE)
GROUP BY TID HAVING COUNT(*)>1;
200 4500
3. SELECT COUNT(*),SUM(FEE) FROM COURSE
2.
WHERE STARTDATE<’2018-09-15’;
COMPANY COUNT(*)
ANS: LOGITECH 2
CANON 2
1. Distinct Tid 3.
101
PROD_NAME QTY_SOLD
103 MOUSE 3
102 KEYBOARD 2
104 JOYSTICK 2
105 4.
2. PROD_NAME COMPANY QUARTER
Tid count(*) min(fee) MOUSE LOGITECH 2
101 2 12000 LASER CANON 1
3. PRINTER
KEYBOARD LOGITECH 2
count(*) sum(fee)
JOYSTICK IBALL 1
4 65000
SPEAKER CREATIVE NULL
DESKIET CANON NULL
Q32. PRINTER

PROD_ID PROD_NAME PRICE COMPANY TYPE

P001 MOUSE 200 LOGITECH INPUT Q33.
P002 LASER 4000 CANON OUTPUT
PRINTER Table: Customers
P003 KEYBOARD 500 LOGITECH INPUT
P004 JOYSTICK 1000 IBALL INPUT CNo CNAME ADDRESS
P005 SPEAKER 1200 CREATIVE OUTPUT 101 Richa Jain Delhi
P006 DESKJET 4500 CANON OUTPUT 102 Surbhi Sinha Chennai
PRINTER 103 Lisa Thomas Bangalore
Table:Computer 104 Imran Ali Delhi
105 Roshan Singh Chennai
Table:Sales Table:Trasaction
PROD_ID QTY_SOLD QUARTER TRNO CNO AMOUNT TYPE DOT
P002 4 1 T001 101 1500 CREDIT 2017-11-23
P003 2 2 T002 103 2000 DEBIT 2017-05-12
P001 3 2 T003 102 3000 CREDIT 2017-06-10
P004 2 1 T004 103 12000 CREDIT 2017-09-12
What will be the output of the following statements. T005 101 1000 DEBIT 2017-09-05
[Link] MIN(PRICE),MAX(PRICE) FROM COMPUTER; 1.) TO DISPLAY DETAILS OF ALL TRANSATION OF
TYPE CREDIT FROM TABLE TRANSATION.
2. SELECT COMPANY,COUNT(*) FROM COMPUTER 2.) TO DISPLAY THE CNO AND AMOUNT OF ALL
GROUP BY COMPANY HAVING COUNT(COMPANY)>1; TRANSACTION DONE IN THE MONTH SEPTEMBER
[Link] PROD_NAME,QTY_SOLD FROM COMPUTER C 2017 FROM THE TABLE TRANSACION.
,SALES S WHERE C.PROD_ID=S.PROD_ID AND 3.) TO DISPLAY THE LAST DATE OF TRANSATION
TYPE=’INPUT’ (DOT) FROM THE TABLE TRANSACTION FOR THE
CUSTOMER HAVING CNO AS 103.
[Link] PROD_NAME,COMPANY,QUARTER FROM 4.) TO DISPLAY ALL CNO,CNAME AND DOT OF THOSE
COMPUTER C,SALES S WHERE C.PROD_ID=S.PROD_ID; CUSTOMER FROM TABLE CUSTOMER AND
TRANSACTION WHO HAVE DONE TRANSACTION
MORE THAN OR EQUAL TO 2000.
 Data Base Management(Questions and PYQ)

ANS: [Link]=[Link] AND

QUARTER=1;
1.) SELECT * FROM TRANSACTION WHERE
TYPE=”CREDIT”; QWhat will be the output of the following statement
2.) SELECT CNO,AMOUNT FROM TRANSACTION based on the tables given in question no 34.
WHERE MONTH(DOT)=09 AND YEAR(DOT)=2017;
1. Select max(price),min(Qty) from watches;
3.) SEELCT MAX(DOT) FRO TRANSACTION WHERE
2. Select watch_name ,price,type from watches w
CNO=103;
sales s where [Link]=[Link];
4.) SELECT CNO,CNAME ,DOT FROM CUSTOMER,
TRANSACTION WHERE Ans: 1.
[Link]=[Link] AND
AMOUNT>=2000; Max(price) Min(Qty)
25000 100
Q34. TABLE:WATCHES

WATCHID WATCH_NAME PRICE TYPE QTY 2.

W001 HIGH TIME 10000 UNISEX 100
W002 LIFE TIME 15000 LADIES 150 Watch_name Price type
W003 WAVE 20000 GENTS 200 High Fashion 7000 unisex
W004 HIGH FASHION 7000 UNISEX 250
W005 GOLDEN TIME 25000 GENTS 100 Q36 Table: Salesperson
TABLE:SALES
code Name Salary Itcode
ATCHID QTY_STORE QUARTER 1001 Sandeep jha 60000 12
W001 10 1 1002 Yograj singh 70000 15
W003 5 1 1003 Tenzin Jack 45000 12
W002 20 2 1005 Anokhi Raj 50000 17
W003 10 2 1004 Tarana sen 55000 17
W001 15 3 Table:Item
W002 20 3
Itcode Itemtype Turnover
W005 10 3
15 Stationary 3400000
W003 15 4
17 Hoistery 6500000
12 Bakery 10090000
1.) TO DISPALY ALL THE DETAILS OF THOSE WATCHES 1.) To display the code and Name of all salesperson
WHOSE NAME ENDS WITH TIME. having item code “17” from the table
2.) TO DISPLAY WATCH NAME AND PRICE THOSE salesperson.
WATCHES WHICH HAVE PRICE RANGE BETWEEN 2.) To display all detail from table Salesperson in
5000-15000 descending order of salary.
3.) TO DISPLAY TOTAL QTY IN STORE OF UNISEX TYPE 3.) To display the number of sales person dealing in
WATCHES. each type of item (use Itcode for the same)
4.) TO DISPLAY WATCH NAME AND QUANTITY SOLD 4.) To display Name of all the sales person table
IN FIRST QUARTER. along with their corresponding itemtype from
the table Item.
ANS: 5.) Select max(salary) from salesperson;
1.) SELECT * FROM WATCHES WHERE WATCH_NAME 6.) Select distinct Itcode from salesperson:
LIKE ‘%TIME’; 7.) Select code,name,itcode from salesperson
2.) SELECT WATCH_NAME ,PRICE FROM WATHES s,item i where [Link]=[Link] and
WHERE PRICE BETWEEN 5000 AND 15000; turnover>=7000000;
3.) SELECT SUM(PRICE) FROM WATCHES WHERE 8.) Select sum(salary) from salesperson where
TYPE=’UNISEX’; itcode=”12”;
4.) SELECT WATCH_NAME,QTY_SOLD FROM Ans:
WATCHES,SALE WHERE
1.) SELECT CODE,NAME FROM SALESPERSON WHERE
ITCODE=”17”;
 Data Base Management(Questions and PYQ)

2.) SELECT * FROM SALESPERSON ORDER BY SALES start Min(fare)

DESC; Delhi 4000
3.) SELECT TYPE,COUNT(*) FROM SALESPERSON Mumbai 5000
GROUP BY TYPE;
4.) SELECT NAME FROM SALESPERSON ,ITEM WHERE
Q38 Table: Customer
[Link]=[Link]; Cno Name Gender Address Phone
5.) 70000 1001 Suresh Male A-123,west street 9310010010
6.) 1002 Anita Female C-24,Court Lane 9121211212
1003 Harjas Male T-1 Woods Avenu 9820021001
ITCODE
101
103 Table:Transact
102
7.) TNO CNO Amount TType Tdate
T1 1002 2000 DEBIT 2021-09-25
CODE NAME ITCODE T2 1003 1500 CREDIT 2022-01-28
1001 SANDEEP JHA 12 T3 1002 3500 CREDIT 2021-12-31
1003 Tenzin Jack 12 T4 1001 1000 DEBIT 2022-01-10
8.) [Link] the statement to delete the records from table
transact whose amount is less than 1000.
SALARY
10500 2. Write a query to display the total amount of all Debit
Q37. and all credit.

Table :Travel [Link] a query to display the Name and corresponding

amount of all customers who made Transaction type of
T_id Start Eno T_date Fare credit.
101 Delhi Chennai 2021-12-25 4500
[Link] the SQL statement to change the phone no of
102 Delhi Bengaluru 2021-11-20 4000
customer whose CNO is 1002 , 9988117700 in the table
103 Mumbai Chennai 2021-12-10 5500
customer.
104 Delhi Mumbai 2019-12-20 4500
105 Mumbai Bengaluru 2022-01-15 5000 Ans:
1.) SELECT START,END FROM TRAVEL WHERE
1. DELETE FROM TRANSACT WHERE AMOUNT<1000;
FARE<=4000;
2. SELECT TTYPE,SUM(AMOUNT) FROM TRANSACT
2.) SELECT T_ID,FARE FROM TRAVEL WHERE T_DATE GROUP BY TTYPE;
LIKE ‘2021-12-%’; 3. SELECT NAME,AMOUNT FROM
3.) SELECT T_ID,T_DATE FROM TRAVEL WHERE CUSTOMER,TRANSACT WHERE
END=’CHENNAI’ ORDER BY FARE; [Link]=[Link] AND
4.) SELECT START,MIN(FARE) FROM TRAVEL GROUP BY TTYPE=’CREDIT’;
START; 4. UPDATE CUSTOMER SET PHONE=9988117700
WHERE CNO=1002;
Ans:
Q39.
1.
Table:Passanger
Start End
Delhi Bengaluru Pno Name Gender Fno
2. 1001 Suresh M F101
1002 Anita F F104
T_id Fare 1003 Harjas M F102
101 4500 1004 Nita F F103
3. Table:Flight
T_id T_date Fno Start End F_Date Fare
101 2021-12-25 F101 Mumbai Chennai 2021-12-25 4500
103 2021-12-10 F102 Mumbai Bengaluru 2021-11-20 4000
4. F103 Delhi Chennai 2021-12-10 5500
 Data Base Management(Questions and PYQ)

F104 Kolkata Mumbai 2021-12-20 4500 [Link]=[Link] AND GENDER=’F’

F105 Delhi Bengaluru 2021-01-15 5000 AND DOT BETWEEN 2019-01-01 AND 2019-12-31;
[Link] the query to change the fare to 6000 of the
[Link] GENDER,COUNT(*) FROM CUSTOMER GROUP
flight whose FNO is F104.
BY GENDER;
2. Write a query to display total number of male and
[Link] BALANCE FROM TRANSACTION ORDER BY
female passengers.
DOT;
[Link] a query to display the name corresponding
Q41 Table: Stock
fare and F_date of all passengers who have a flight to
start from Delhi. Itcode Itname Dcode Qty Unitprc Stkdate
444 Drawing 101 10 21 31-june-
[Link] a query to delete the records of flights which
copy 2009
end at Mumbai.
445 Sharpner 102 25 13 21-apr-
Ans: 2010
450 Eraser 101 40 6 11-Dec-
1. UPDATE FLIGHT SET FARE=6000 WHERE
2010
FNO=”F104”;
452 Gel pen 103 80 10 03-Jan-
2. SELECT GENDER,COUNT(*) FROM PASSENGER
2010
GROUP BY GENDER;
457 Geometry 101 65 65 15-Nov-
3. SELECT NAME,FARE,F_DATE FROM
2009
PASSENGER,FLIGHT WHERE
467 Parker 102 102 109 27-Oct-
[Link]=FLIGHT.FN0 AND START =’DELHI’;
pen 2009
4. DELETE FROM FLIGHT WHERE END=’MUMBAI’;
469 Office File 103 103 34 13-Sep-
Q40. 2010
Table :customer
Table:Dealers
CNO CNAME Gender
101 Archna Jain F Dcode Dname Location
102 Surbhi Sinha F 101 Vikash Stationers Lanka Varanasi
103 Aisa F 102 Bharat Drawing Luxa Varanasi
104 Imran Ali M Emporium
105 Roshan Singh M 103 Banaras Book Bansphatak
Table:transaction Corporation Varanasi
[Link] Display All the information about items containing
TRNO CNO BALANCE DOT the word “pen” in the field item name in the table
T001 101 1500 2019-12-23 Stock.
T002 103 2000 2019-05-12
T003 102 3000 2019-06-10 [Link] all the item sold by Vikas stationers.
T004 103 12000 2019-01-12 3. List all the Itname and stkdate in ascending order by
T005 101 1000 2019-09-05 stkdate.
1. Display all information about the customer whose
name start with ‘a’. [Link] all the Itname,Qty and Dname for all the item for
2. To display Name and Balance of female customer the items Quantity more than 40.
whose transaction date (DOT) from jan to dec
5. List all the detail of items for which unit price in
2019.
more than 10 and less tan equal to 50.
3. To display the total number of customer for each
gender. Ans:
4. To display the balance in ascending order
according to date of transaction(DOT). 1. SELECT * FROM STOCK WHERE ITNAME
LIKE’%PEN%’;
ANS: 1. SELECT * FROM CUSTOMER WHERE NAME LIKE 2. SELECT * FROM STOCK,DELEARS WHERE
‘A%’; [Link]=[Link] AND
DNAME=’VIKASSTATIONERS;
[Link] CNAME,BALANCE FROM
3. SELECT ITNAME ,STKDATE ORDER BY STKDATE;
CUSTOMER,TRANSACTION WHERE
 Data Base Management(Questions and PYQ)

4. SELECT ITNAME,QTY,DNAME FROM STOCK,DELEARS [Link](A): A Database consist of number of

WHERE [Link]=[Link] ANT tables.
QTY>40; Reason(R): Each Table Comprises of rows and
5. SELECT * FROM STOCK WHERE UNITPR BETWEEN records.
10 AND 50; Ans:C (Explanation:Database consist of number of
[Link] Table Comprises of rows(records) and
Column(attributes).
Q44. Assertion(A): Database Scheme is the design
of a Database.
Q42 Write the output of the following SQl Queries Reason(R):Database Scheme is also called the
Table:Doctor visual and logical architecture.
Ans: B
DNO DNAME FEES Q45: Assertion(A): The column of a relation are
D1 AMITABH 1500 attributes.
D2 ANIKET 1000 Reason(R):Tuples are referred as field.
D3 NIKHIL 1500 Ans:C
D4 ANJANA 1500
Q46. : Assertion(A): The number of attribute in a
relation are called the Degree of relation.
Table:Patient Reason(R):The number of Tuples in a relation is
PNO PNAME ADMDATE DNO called the cardinality of the relation.
P1 NOOR 2021-12-25 D1 Ans: B
P2 ANNIE 2021-11-20 D2
P3 PRAKESH 2020-12-10 NULL Q47 Assertion(A): The IN operator in SQL is used to
P4 HARMEET 2019-12-20 D1 match values with a list of values.
[Link] DNAME,PNAME from Dector natural join Reason(R):The IN operator can only be used with
patient; the Where Clause in SQL.
[Link] Pname,Admdate,fees from patient p,Doctor D Ans B
where [Link]=[Link] and fees>1000;
Q48 Assertion(A): The ORDER BY clause is SQL is used to
Ans: sort the result in ascending or descending order.
1. Reason(R):The ORDER BY clause can only used
Dname Pname with Select Statement in SQL.
AMITABH NOOR Ans B
ANIKET ANNIE
AMITABH HARMEET Q49. Assertion(A): The BETWEEN operator in SQL is used
2. to match a value within a Range of Values.

PNAME ADMDATE FEES Reason(R):The BETWEEN operator can only be

NOOR 2021-12-25 1500 used with numerical values in SQL.
HARMEET 2019-12-20 1500 Ans A
Q50. i) Assertion(A): The LIKE operator in SQL is used to
Assertion and Reason Based Questions: match a value with a Pattern.
(a) Both A and R are true and R is the correct Reason(R):The % character can be used with LIKE
explanation for A operator in SQL.
(b) Both A and R are true and R is not the correct Ans B
explanation for A
ii) Assertion(A): SQL stands for structured Query
(c) A is True but R is False
Language.
(d) A is false but R is True
Reason(R):SQL is a Language used to manage
relational Database.
 Data Base Management(Questions and PYQ)

Ans:A If u skip the size of CHAR It is compulsory to give

data type than by default the size in VARCHAR data
it will be one. type.
Ex Char() [Link](size)

[Link] Between Order By and Group By

iii) Assertion(A): The Primary key constraint is SQL is
used to ensure that each row in a table is unique and Ans.
not NULL.
Order By Group By
Reason(R):The Primary key constraint is acomination of It is used to display It is used to group the date on
NOTNULL and UNIQUE constraint. the information in the basics of particular
ascending order or column.
Ans:A descending order
Q51 Difference Between Drop and Delete Command. It is not mandatory It is mandatory to use
to use aggregate aggregate function with group
Ans. function in order by by
command command.(count,max,min,sum
Drop Command Delete Command
etc)
It is used to delete the It is used to delete one or
Synatx: Synatx:
entire table including its
more record or row from
Select * from table Select aggregate function from
structure the table based on
name order by tablename group by column
condition
columnname; name;
Drop is DDL command Delete is DML command
Structure of table also Structure not deleted
deleted only record deleted Q55 Difference between WHERE clause and HAVING
Syntax: Drop Table Syntax: Delete from clause.
Tablename; tablename where
condition; Ans:
[Link] Between DDL command and DML WHERE HAVING
command. The WHERE clause select The HAVING clause select
Ans. the record after satisfy a the record after satisfy a
specified condition it is specified condition it is
DDL DML not use with Group by. Always use with Group
DDL stands for Data DML stands for Data by.
Definition Language. Manipulation Language. The WHERE clause select The HAVING clause
DDL are used to DML Commands deal with the rows before Grouping. select rows after
specify the values of database. Grouping
Database The WHERE function The HAVING clause can
Scheme/Structure don’t contain Aggregate contain the aggregate
DDL commands DML affect the record in the function. function.
affect the Database Table Syntax: Synatx:
or Table Select * from Table name Select Aggregate function
CREATE,ALTER,DROP SELECT,INSERT,DELETE,UPDATE Where Condition ; from table name group
,TRUNCATE are DDL are DML commands by column name having
commands condition;
Q53. Differentiate Between CHAR and VARCHAR.
Ans. Q56 Difference between ALTER and UPDATE command.
Char Varchar Ans:
It is fixed length It is Variable length
character data type character data Type ALTER UPDATE
It hold Maximum 255 It hold maximum 65535
characters Characters
 Data Base Management(Questions and PYQ)

Alter Command is used Update command is used Table Result

to add, delete ,modify to update the value in the
Ans: Cardinality([Link] Rows):-06
the column in table. table.
It is a DDL command IT is DML command. Degree([Link] Column):-04
Syntax: Synatx:
Alter table tableanme Update tablename set Q58: observe the following table and answer the parts
add columnname columnname=newvalue (i) and (ii):
datatype(size); where condition;

Q56 i)Difference between Primary key and Foreign key.

Ans:
Primary key Foreign key
It must contain Unique It can contain Duplicate i) In the above table, can we have Qty as primary key.
values values Ans: Item code
It can’t contain NULL It may contain NULL
value values ii) What is the cardinality and degree of the above table.
A Table or Database have A database have more
Ans: Cardinality: 5
only one primary key. than on foreign key.
It is use to identify a It is use to make relation Degree: 4
record in a table ship between tables
uniquely. [Link] the table ‘Club’ given below

ii) Difference between Candidate key and Alternate key.

Ans:
Candidate Key Alternate Key
A minimal set of attribute A candidate key that not [Link] is the cardinality and degree of the given table..
that uniquely identify a become a primary key is
row in table is called called alternate key Ans: Cardinality :4 Degree:5
candidate key [Link] a new column Contact_No has been added and
three more members have joined the club then what is
cardinality and Degree.

[Link] the following PARTICIPANTS and EVENTS Ans: Cardinality : 7 Degree :6

table cerefully and write the name of the RDBMS Q60.i) A table ‘customer’ has 10 columns but no row.
operation which will be used to produce the output as Later, 10 new rows are inserted and 3 rows are deleted
shown in RESULT? Also, find the Degree and Cardinality in the table. What is the degree and cardinality of the
of the RESULT. table customer.
Ans: Cardinality: 0+10-3=7
Degree : 10
ii. . A table ‘student’ has 3 columns and 10 rows and
another table ‘student 2’ has the same columns as
student but 15 rows. 5 rows are common in both the
tables. If we take union, what is the degree and
cardinality of the resultant table ?
Ans: Degree: 3 Cardinality: 10+15-5=20
iii A table ‘student’ has 4 columns and 10 rows and
‘student 2’ has 5 columns and 5 rows. If we take
 Data Base Management(Questions and PYQ)

cartesian product of these two tables, what is the NAME EMI

degree and cardinality of the resultant table ? K.S DHALL 48
Ans: Degree: 4+5=9
c) SELECT COUNT(*) FROM LOAN WHERE NAME
Cardinality: 10*5=50
LIKE”%SHARMA”;
Note:In Case of cartesian Product Add [Link] columns d) SELECT NAME, MAX(LOANAMT) FROM LOAN;
and multiply No. of rows to calculate Degree and e) SELECT COUNT(*) FROM LAON WHERE
cardinality Respectively. INTRATE=”NULL”;

Q61.
Consider the Table Loan Given Below Q62. Consider the following table named GYM with th
following details.
Accno Name Loan EMI Int Intrest
amt rate Prcode Prname Unit price Manufacturer
1001 R.K 300000 36 12 1200 P101 Cross 25000 Avon Fitness
GUPTA trainer
1002 S.P 500000 48 10 1800 P102 Tread Mill 32000 A G Fitile
SHARMA P103 Massage 20000 Fit Express
1003 K.P JAIN 300000 36 NULL 1600 Chair
1004 M.P 800000 60 10 2250 P104 Vibration 22000 Avon Fitness
YADAV Trainer
1005 S.P 200000 36 12.5 4500 P105 Bike 13000 Fit Express
SINLIA a) Display the name of all products in the store.
1006 P. 700000 60 12.5 3500 b) Display the name of all products with unit price
SHARMA less than 20000.
1007 K.S 800000 48 NULL 3800 c) Display the details of all products with unit price
DHALL range 20000 and 30000.
d) Display the name of all products by the
manufacturer “Avon Fitness “ in descending
a) Write a Query to get the output. order of unit price.
Name e) Remove all the product row manufactured by
S.P SINGH “Avon Fitness”
K.S DHALL f) Change the unit price of all the rows by applying
M.P YADAV a 10% increased on all the products.
[Link] g) Display the details of all product with
manufacturer name starting with string ‘A’.
b) What will be the output of the following h) Change the unit price of all the rows by applying
command. a 10% increased on all the products which are
Select name,EMI from loans where manufacture by Fit express.
loanamt>500000 and intrate is NULL;
c) John has given the following command to Ans:
display the count of all the loan holders whose a) Select Prname from gym;
name ends with “Sharma” b) Select prname from gym where unitprice
Select count() from Loans where name like <20000;
‘Sharma%’; c) Select * from gym where unitprice between
d) State the command to display the maximum 20000 and 30000;
Loan amount and Customer name. d) Select prname from gym where
e) Help Ramesh to display the count of loan manufacturer=’avon fitness’ order by unit
holders whose interest is Null. price desc;
e) Delete from gym where manufacturer
Ans:
=’avon fitness’;
a) SELECT NAME FROM LOAN WHERE f) Update gym set
LOANAMT>500000; unitprice=unitprice+(unitprice*10)/100;
b) g) Select* from Gym where manufacturer
like’a%’;
 Data Base Management(Questions and PYQ)

h) Update gym set (D) Names of all customers whose age is not between 25
unitprice=unitprice+(unitprice*10)/100 and 35
where manufacturer=”fit Express’;

[Link] SELECT statement when combined with

clause,………………. returns records without repetition.
(A) DISTINCT (B) DESCRIBE (C) UNIQUE (D) NULL Q What will be the output of the following query?
Q2. In SQL, the aggregate function which will display the Select count(*) from order where order_date between
Cardinality of Table. ‘2023-01-01’ and ‘2023-12-31’;
(A) sum () (B) count (*) (C) avg () (D) sum (*) a) The number of order placed in year 2023
Q3. 14. Which of the following is not a DDL command in b) The total value of orders placed in the in year
SQL? 2023
(A) DROP (B) CREATE (C) UPDATE (D) ALTER c) The earliest order date in the year 2023
Q4. 9. If a table which has one Primary key and two d) The number of unique order in the year 2023
alternate keys. How many Candidate keys will this table
have Q What will be the output of the following SQL query.

A)1 (B) 2 (C) 4 (D) 3 SELECT name,MAX(Salary) from employee group by

name;
Q4.1 A table has 1 Primary Key, 1 Alternate Key, and 1
Composite Key. How many Candidate Keys does the table A) The Maximum salary for each employee

A)1 (B) 2 (C) 4 (D) 3 B) The Maximum Salary for each department

In a table with attributes A, B and C, where A is a Primary C) The name of the employee with the highest
Key and B and C are non-key attributes, which of the salary
following statements is true about the Candidate Keys of D) The maximum salary of all employees combined
this table?
Q What will be the output of the query?
(A) The table has no Candidate Keys.
SELECT * FROM products WHERE product_name LIKE
(B) B and Care Candidate Keys. 'App%’;
(C) A is the only Candidate Key. (A) Details of all products whose names start with
(D) Both A and B can be Candidate Keys. 'App’

[Link] SQL command can change the degree of a (B) Details of all products whose names end with
table? Ans:alter 'App’

Q5.1 A table product has 5 columns and 10 rows. Another (C) Names of all products whose names start with
table transaction has 6 columns and 18 rows. What will 'App’
be the degree and cardinality of product × transaction? (D) Names of all products whose names end with
Ans Degree =5+6=11 cardinality= 10x18=180 'App'

10. What will be the output of the following query? Q. What will be the output of the following SQL query.

SELECT name FROM customers WHERE age BETWEEN 25 SELECT distinct department from employee where
AND 35; salary>50000;

(A) Names of all customers whose age is exactly 25 or 35 A) All distinct departments with a salary greater than
50000
(B) Names of all customers whose age is between 25 and
35, inclusive B) All departments with a salary than 50000 including
duplicate
(C) Names of all customers whose age is less than 25 or
greater than 35
 Data Base Management(Questions and PYQ)

C) Departments of all employees with salary greater Assertion and Reason:

than 50000
a) Both are True R is the correct explanation of
D) Distinct salaries of employee greater than 50000 A
b) Both are True R is not correct explanation of
A
[Link] will be the output of the query? c) A is true R is False
d) A is False R is True
SELECT AVG(salary) FROM employees WHERE
department = 'Sales’;
(A) Average salary of all employees. [Link] (A): A SELECT command in SQL can
have both WHERE and HAVING clauses.
(B) Average salary of employees in the 'Sales'
department. Reason (R): WHERE and HAVING clauses are used
to check conditions, therefore, these can be used
(C) Total salary of all employees in the 'Sales' interchangeably.
department
Ans A is true R is False
(D) Maximum salary of employees in the 'Sales'
department. [Link] (A): The UPDATE command in SQL is
used to modify the existing record in the Table.
Reason(R): The Update Command require Where
clause to specify which record should be modified
; otherwise all records in the table will be
updated.
Ans: Both are True R is the correct explanation
of A
[Link] (A): A DELETE command in SQL is use
to remove the record from the Table.
Reason (R): The DELETE Command require Where
clause to specify which record should be deleted
; otherwise all records in the table will deleted.
Ans: Both are True R is the correct explanation
of A
[Link] (A): A DROP command in SQL is use to
remove the entire table from Database.
Reason (R): When the DROP command is executed ,all
data , structure, permission related to the table get
removed.
Ans: Both are True R is the correct explanation of A

Q Assertion(A): SQL stands for structured Query

Language.
Reason(R): SQL is a language which is use to manage
relational Database Mangement.
Ans: Both are True R is the correct explanation of A
 Data Base Management(Questions and PYQ)

Q Assertion(A): A Create Table command is use to create

a new Table.
Reason(R): The Create Table Statement specifies the
attribute of a Table and their Data Types.
Ans: Both are True R is the correct explanation of A
Q Assertion(A): The PRIMARY KEY constraint in SQL is
used to ensure that each row in Table is unique .
Reason(R): The primary key constraint is a combination of
NOT NULL and UNIQUE constraint
Ans: Both are True R is the correct explanation of A
Q Assertion(A): The SELECT statement in SQL is used to
retrieve data from one or more Tables.
Reason(R): The SELECT statement can be used to retrieve
all columns and subset of columns from the Table.
Ans: Both are True R is the correct explanation of A
Q Assertion(A): The WHERE clause in SQL is used to filter
data based on a specified condition.
Reason(R): The Where Clause is always required when
using the SELECT statement in SQL.
Ans A is TRUE But R is False
 Structured Query Language

Short Answer Type Questions

Question 1:
Write queries for (i) to (iv) and find ouputs for SQL queries (v) to (viii), which are based
on the tables.

Table :
VEHICLE

Note:

• PERKS is Freight Charges per kilometer.

• Km is kilometers Travelled
• NOP is number of passangers travelled in vechicle.

1. To display CNO, CNAME, TRAVELDATE from the table TRAVEL in descending

order of CNO.
2. To display the CNAME of all customers from the table TRAVEL who are travelling
by vechicle with code Vo1 or Vo2
3. To display the CNO and CNAME of those customers from the table TRAVEL who
travelled between ‘2015-1231’ and ‘2015-05-01’.
4. To display all the details from table TRAVEL for the customers, who have travel
distacne more than 120 KM in ascending order of NOE
5. SELECT COUNT (*), VCODE FROM TRAVEL GROUP BY VCODE HAVING
COUNT (*) > 1;
6. SELECT DISTINCT VCODE FROM TRAVEL :
7. SELECT [Link], CNAME, VEHICLETYPE FROM TRAVEL A, VEHICLE B
WHERE A. VCODE = B. VCODE and KM < 90;
8. SELECT CNAME, KM*PERKM FROM TRAVEL A, VEHICLE B WHERE
[Link] = [Link] AND A. VCODE ‘V05’;

Answer:
Question 2:
Consider the following tables SCHOOL and ADMIN and answer this question :
Give the output the following SQL queries :

1. Select Designation Count (*) From Admin Group By Designation Having Count (*)
<2;
2. SELECT max (EXPERIENCE) FROM SCHOOL;
3. SELECT TEACHER FROM SCHOOL WHERE EXPERIENCE >12 ORDER BY
TEACHER;
4. SELECT COUNT (*), GENDER FROM ADMIN GROUP BY GENDER;
Answer:

Question 3:
Write SQL qureries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are
based on the tables TRANSPORT and TRIE

Note:

• PERKS is Freight Charages per kilometer

• TTYPE is Transport Vehicle Type
Note:

• NO is Driver Number
• KM is Kilometer travelled
• NOP is number of travellers travelled in vehicle
• TDATE is Trip Date

1. To display NO, NAME, TDATE from the table TRIP in descending order of NO.
2. To display the NAME of the drivers from the table TRIP who are traveling by
transport vehicle with code 101 or 103.
3. To display the NO and NAME of those drivers from the table TRIP who travelled
between ‘2015-02-10’ and ‘2015-04-01’.
4. To display all the details from table TRIP in which the distance travelled is more
than 100 KM in ascending order of NOP
5. SELECT COUNT (*), TCODE From TRIP
GROUP BY TCODE HAVNING COUnT (*) > 1;
6. SELECT DISTINCT TCODE from TRIP;
7. SELECT [Link], NAME, TTYPE
FROM TRIP A, TRANSPORT B
WHERE A. TCODE = B. TCODE AND KM < 90;
8. SELECT NAME, KM *PERKM
FROM TRIP A, TRANSPORT B
WHERE A. TCODE = B. TCODE AND A. TCODE = 105′;

Answer:

1. SELECT NO, NAME, TDATE FROM TRIP ORDER BY NO;

2. SELECT NAME FROM TRIP
WHERE TCODE = 101 OR TCODE = 103;
 3. SELECT NO AND NAME FROM TRIP
WHERE ‘2015-02-10’ < TDATE < ‘2015-04-01’;
4. SELECT NO, NAME, TDATE, KM, TCODE FROM TRIP
WHERE KM >100 ORDER BY NOP;
5. TO DISPLAY THE MORE THAN ONE COUNT OF TCODE FROM THE TABLE
TRIP
6. TO DISPALY SEPERATE TCODE OF TABLE TRIP
7. TO DISPAY THE NAME AND CODE OF THOSE TRANS PORTERS, WHO
HAVE TRAVELLED MORE THAN 90 KMS.
8. TO DISPLAY THE NAME AND EXPENDITARE OF A TRANSPORTER WHO
HAVE TCODE AS 105.

Question 4:
Write SQL query to add a column total price with datatype numeric and size 10, 2 in a
table product.

Answer:
ALTER TABLE PRODUCT ADD TOTAL PRICE NUMBER (10,2).

Question 5:
Sonal needs to display name of teachers, who have “0” as the third character in their
name. She wrote the following query.
SELECT NAME FROM TEACHER WHERE NAME = “$$0?”;
But the query is’nt producing the result. Identify the problem.

Answer:
The wildcards are incorrect. The corrected query is SELECT NAME FROM TEACHER
WHERE NAME LIKE ‘_ _0%’.

Question 6:
Deepika wants to remove all rows from the table BANK. But he needs to maintain the
structure of the table. Which command is used to implement the same?

Answer:
DELETE FROM BANK.

Question 7:
While creating table ‘customer’, Rahul forgot to add column ‘price’. Which command is
used to add new column in the table. Write the command to implement the same.

Answer:
ALTER TABLE CUSTOMER ADD PRICE NUMBER (10, 2).

Question 8:
What is the use of wildcard
Answer:
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

Question 9:
Differentiate between DELETE and DROP table commands ?

Answer:
DELETE command is used to remove infor¬mation from a particular row or rows. If
used without condition, it will delete all row information but not the structure of the table.
It is a DML command.
DROP table command is used to remove the entire structure of the table and
information. It is a DDL command

Long Answer Type Questions

Question 1:
Write SQL commands for the queries (i) to (iv) and output for (v) & (viii) based on a
table COMPANY and CUSTOMER.

1. To display those company name which are having prize less than 30000.
2. To display the name of the companies in reverse alphabetical order.
3. To increase the prize by 1000 for those customer whose name starts with „S?
 4. To add one more column totalprice with decimal] 10,2) to the table customer
5. SELECT COUNT(*) , CITY FROM COMPANY GROUP BY CITY;
6. SELECT MIN(PRICE), MAX(PRICE) FROM CUSTOMER WHERE QTY>10;
7. SELECT AVG(QTY) FROM CUSTOMER WHERE NAME LIKE “%r%;
8. SELECT PRODUCTNAME,CITY, PRICE
FROM COMPANY, CUSTOMER WHERE
COMPANY. CID=[Link] AND
PRODUCTNAME=”MOBILE”;

Answer:

1. SELECT NAME FROM COMPANY WHERE [Link]=CUSTOMER. CID

AND
PRICE < 30000;
2. SELECT NAME FROM COMPANY ORDER BY NAME DESC;
3. UPDATE CUSTOMER
SET PRICE = PRICE + 1000
WHERE NAME LIKE ‘S%’;
4. ALTER TABLE CUSTOMER
ADD TOTALPRICE DECIMAL(10,2);

5.
6. 50000,70000
7. 11

8.

Question 2:
Consider the following tables SCHOOL and ADMIN and answer this question :
Write SQL statements for the following:

1. To display TEACHERNAME, PERIODS of all teachers whose periods are more

than 25.
2. To display all the information from the table SCHOOL in descending order of
experience.
3. To display DESIGNATION without dupli¬cate entries from the table ADMIN.
4. To display TEACHERNAME, CODE and corresponding DESIGNATION from
tables SCHOOL and ADMIN of Male teachers.

Answer:

1. SELECT TEACHERNAME, PERIODS

FROM SCHOOL WHERE PERIODS>25:
2. SELECT * FROM SCHOOL;
3. SELECT DISTINCT DESIGNATION FROM ADMIN;
4. SELECT [Link]
DESIGNATION FROM
[Link] = [Link]
WHERE GENDER = MALE;
Question 3:
Write SQL commands for the queries (i) to (iv) and output for (v) to (viii) based on the
tables Watches’ and Sale given below.

1. TO DISPLAY ALL THE DETAILS OF THOSE WATCHES WHOSE NAME ENDS

WITH ‘TIME’
2. TO DISPLAY WATCH’S NAME AND PRICE OF THOSE WATCHES WHICH
HAVE PRICE RANGE IN BE-TWEEN 5000-15000.
3. TO DISPLAY TOTAL QUANTITY IN STORE OF UNISEX TYPE WATCHES.
4. TO DISPLAY WATCH NAME AND THEIR QUANTITY SOLD IN FIRST
QUARTER;
5. SELECT MAX (PRICE), MIN(QTY_STORE) FROM WATCHES;
6. SELECT QUARTER, SUM(QTY SOLD) FROM SALE GROUP BY QUARTER;
7. SELECT WATCH_NAME, PRICE, TYPE FROM WATCHES W, SALE S WHERE
W. WAT£H1D!=[Link]; (viii) SELECT WATCH_NAME, QTYSTORE, SUM
(QTY_SOLD), QTY_STORESUM (QTYSOLD) “STOCK” FROM WATCHES W,
SALE S WHERE W. WATCHID = [Link] GROUP BY [Link];

Answer:

1. SELECT * FROM WATCHES WHERE WATCH_NAME LIKE ‘%TIME’

(Vi mark for SELECT query) (Vi mark for where clause)
 2. SELECT WATCH_NAME, PRICE WATCH WHERE PRICE BETWEEN 5000 AND
15000;
(Vi mark for SELECT query) (Vz mark for where clause)
3. SELECT SUM (QTY STORE) FROM WATCHES WHERE TYPE LIKE ‘UNISEX’;
(Vz mark for SELECT query) (Vi mark for where clause)
4. SELECT WATCHNAME, QTY SOLD FROM WATCHES W,SALE S WHERE W.
WATCHID = S. WATCHID
AND QUARTER = 1;

Question 4:
Answer the questions (a) and (b) on the basis of the following tables SHOP and
ACCESSORIES.
(a) Write the SQL queries:

1. To display Name and Price of all the Accessories in ascending order of their Price.
2. To display Id and SName of all Shop located in Nehru Place.
3. To display Minimum and Maximum Price of each Name of Accessories.
4. To display Name, Price of all Accessories and their respective SName where they
are available.

(b) Write the output of the following SQL

1. SELECT DISTINCT NAME FROM ACCESSORIES WHERE PRICE> =5000;

2. SELECT AREA, COUNT(*) FROM SHOPPE GROUP BY AREA;
3. SELECT COUNT (DISTINCT AREA) FROM SHOPPE;
4. SELECT NAME, PRICE*0.05 DISCOUNT FROM ACCESSORIES WHERE SNO
IN (‘S02‘,S03‘);

Answer:
(a)

1. SELECT Name, Price FROM ACCESSORIES ORDER BY Price Asc;

2. SELECT ID SName FROM SHOP WHERE Area=”Nehru Place”;
3. SELECT Name, max (Price); min(Price) FROM ACCESSORIES, Group By Name;
4. SELECT Name,price, Sname FROM
ACCESSORIES, SHOP WHERE SHOE
ID=[Link];
(b)

Question 5:
Write SQL queries for:

1. To display name, fee, gender, joinyear about the applicants, who have joined
before 2010.
2. To display names of applicants, who are playing fee more than 30000.
3. To display names of all applicants in ascending order of their joinyear.
4. To display the year and the total number of applicants joined in each YEAR from
the table APPLICANTS.
 5. To display the C_ID (i.e., CourselD) and the number of applicants registered in the
course from the APPLICANTS and table.
6. To display the applicant’s name with their respective course’s name from the
tables APPLICANTS and COURSES.
7. Give the output of following SQL statements:
• SELECT Name, Joinyear FROM APPLICANTS
WHERE GENDER=’F’ and C_ID=’A02′;
• SELECT MIN (Joinyear) FROM
APPLICANTS
WHERE Gender=’m’;
• SELECT AVG (Fee) FROM APPLICANTS
WHERE C_ID=’A0T OR C_ID=’A05′;
• SELECT SUM- (Fee), C_ID FROM C_ ID
GROUP BY C_ID
HAVING COUNT(*)=2;

Answer:

1. SELECT NAME,FEE,GENDER,JOINYEAR
FROM APPLICANTS
WHERE J OINYE AR <2010
2. SELECT NAME FROM APPLICANTS WHERE FEE >30000
3. SELECT NAME FROM APPLICANTS ORDERBY JOINYEAR ASC
4. SELECT YEAR, COUNT]*) FROM
APPLICANTS GROUP BY YEAR;
5. SELECT C_ID, COUNT]*) FROM
APPLICANTS, COURSES GROUP BY ID
WHERE APPLICANTS.C_ID=COURSES. C_ID
6. SELECT NAME,COURSE FROM
APPLICANTS, COURSES
WHERE APPLICANTS. C_ID=COURSES. C_ID
• Avisha 2009
• 2009
• 67
• 55000 A01

Question 6:
Write SQL queries for (a) to (g) and write the output for the SQL queries mentioned
shown in (hi) to (h4) parts on the basis of table ITEMS and TRADERS :

1. To display the details of all the items in ascending order of item names (i.e.,
INAME).
2. To display item name and price of all those items, whose price is in the range of
10000 and 22000 (both values inclusive).
3. To display the number of items, which are traded by each trader. The expected
output of this query should be:
4. To display the price, item name and quantity (i.e., qty) of those items which have
quantity more than 150.
5. To display the names of those traders, who are either from DELHI or from
MUMBAI.
6. To display the names of the companies and the names of the items in descending
order of company names.
7. Obtain the outputs of the following SQL queries based on the data given in tables
ITEMS and TRADERS above.
• SELECT MAX (PRICE), MIN (PRICE) FROM ITEMS;
• SELECT PRICE*QTY
FROM ITEMS WHERE CODE-1004;
• SELECT DISTINCT TCODE FROM ITEMS;
• SELECT INAME, TNAME FROM ITEMS I, TRADERS T WHERE
[Link]=[Link] AND QTY< 100;

Answer:

1. SELECT INAME FROM ITEMS ORDER BY

INAME ASC;
2. SELECT INAME, PRICE FROM ITEMS WHERE PRICE => 10000 AND PRICE
=< 22000; (c) SELECT TCODE, COUNT (CODE) FROM ITEMS GROUP BY
TCODE;
3. SELECT PRICE, INAME, QTY FROM ITEMS WHERE (QTY> 150);
 4. SELECT TNAME FROM TRADERS WHERE (CITY = “DELHI”) OR (CITY =
“MUMBAI”)
5. SELECT COMPANY, INAME FROM ITEMS
ORDER BY COMPANY DESC;
6. (hi) 38000
1200
(h2)1075000
(h3)T01
T02
TO3
(h4) LED SCREEN 40 DISP HOUSE INC CAR GPS SYSTEM ELECTRONICS
SALES

Question 7:
Write SQL queries for (a) to (f) and write the outputs for the SQL queries mentioned
shown in (gl) to (g4) parts on the basis of tables PRODUCTS and SUPPLIERS

1. To display the details of all the products in ascending order of product names (i.e.,
PNAME).
2. To display product name and price of all those products, whose price is in the
range of 10000 and 15000 (both values inclusive).
3. To display the number of products, which are supplied by each suplier. i.e., the
expected output should be;
• S01 2
• S02 2
• S03 1
 4. To display the price, product name and quantity (i.e., qty) of those products which
have quantity more thhn 100.
5. To display the names of those suppliers, who are either from DELHI or from
CHENNAI.
6. To display the name of the companies and the name of the products in
descending order of company names.
7. Obtain the outputs of the following SQL queries based on the data given in tables
PRODUCTS and SUPPLIERS above.
• SELECT DISTINCT SUPCODE FROM PRODUCTS;
• SELEC MAX (PRICE), MIN (PRICE) FROM PRODUCTS;
• SELECT PRICE*QTY
FROM PRODUCTS WHERE PID = 104; (g4)
• SELECT PNAME, SNAME
FROM PRODUCTS P, SUPPLIERS S WHERE E SUPCODE = S.
SUPCODE
AND QTY>100;

Answer:

1. SELECT * FROM PRODUCTS ORDER BY PNAME ASC;

2. SELECT PNAME, PRICE FROM PRODUCTS WHERE ((PRICE => 10000) AND
(PRICE = < 15000));
3. SELECT SUPCODE, COUNT (PID) FROM PRODUCTS GROUP BY SUPCODE;
4. SELECT PRICE, PNAME, QTY FROM PRODUCTS WHERE (QTY > 100);
5. SELECT SNAME FROM SUPPLIERS WHERE ((CITY = “DELHI”) OR (CITY =
“CHENNAI”));
6. SELECT COMPANY, PNAME FROM PRO-DUCTS ORDER BY COMPANY
DESC; 4
7. SOI1
(gl)s02
s03
(g2) 28000
1100
(g3) 550000
(g4) PNAME SNAME Vi
DIGITAL CAMERA 14 X GETALL INC
PENDRIVE16 GB GETALL INC

Question 8:
Consider the following tables CARDEN and CUSTOMER and answer (b) and (c) parts
of this question:

1. Give a suitable example of a table with sample data and illustrate Primary and
Alternate Keys in it.
2. Write SQL commands for the following statements:
• To display the names of all the silver coloured cars.
• To display names of car, make and capacity of cars in descending order of
their sitting capacity.
• To display the highest charges at which a vehicle can be hired from
CARDEN.
• To display the customer name and the corresponding name of the cars hired
by them.
3. Give the output of the following SQL queries:
• SELECT COUNT(DISTINCT Make) FROM CARDEN;
• SELECT MAX(Charges), MIN (Charges) FROM CARDEN;
• SELECT COUNTS), Make FROM CARDEN;

Answer:

1. Primary Key of CARDEN = Ccode CARDEN

Alternate Key = CarName:
Primary key of Customer = Code
Alternate Key of Customer = Cname 2
2. SELECT CarName From CARDEN
WHERE Color = “SILVER”;
3. SELECT CarName, Make, Capacity From
CARDEN ORDER BY Capacity DESC;
4. SELECT MAX(Charges) Frm CARDEN;
5. ELECT Cname, CarName From
CARDEN, CUSTOMER WHERE
CARDEN. Ccode = CUSTOMER. Ccode;
 6. (i) 4
(ii) MAX(Charges) MIN (Charges)
35 112
(iii) 5
(iv) SX4
C Class

Question 9:
Consider the following tables CABHUB and CUSTOMER and answer (b) and (c) parts
of this question :

1. Give a suitable example of a table with sample data and illustrate Primary and
Candidate Keys in it.
2. Write SQL commands for the following statements:
• To display the names of all the white coloured vehicles.
• To display name of vehicle name and capacity of vehicles in ascending
order of their sitting capacity.
• To display the highest charges at which a vehicle can be hired from
CABHUB.
• To display the customer name and the corresponding name of the vehicle
hired by them.
3. Give the output of the following SQL queries :
• SELECT COUNT(DISTINCT Make) FROM CABHUB;
• SELECT MAX(Charges), MIN(Charges)
• FROM CABHUB;
• SELECT COUNT (*) Make FROM CABHUB;
• SELECT Vehicle FROM CABHUB WHERE Capacity=4;

Answer:
 1. Primary key of CABHUB = Vcode alternate key of CABHUB = Vehicle
Name. Primary key of Customer = Ccode Alternate Key of CUSTOMER = Cname.
2. (i) SELECT VehicleName FROM CABHUB
WHERE Colour = “WHITE”;
3. SELECT VehicleName, capacity From CABHUB ORDER BY Capacity
ASC;
4. SELECT MAX(Charges) FROM CABHUB;
5. SELECT Cname,VehicleName FROM CABHUB, CUSTOMER WHERE
CUSTOMER. Vcode=CABHUB. Vcode;
6. (i) 4
(ii) MAX(Charges) MIN (Charges)
35 12
(iii) 5
(iv) SX4
C Class

Question 10:
Consider the following tables EMPLOYEE and DEPARTMENT and answer (a) and (b)
parts of this question.

1. Write SQL commands for the following statements:

• To display all DepName along with the DepCde in descending order of
DepCde.
 • To display the average age of Employees in DepCde as 103.
• To display the name of DepHead of the Employee named “Sanjeev P”
• To display the details of all employees who has joined before 2007 from
EMPLOYEE table.
2. Give the output of the following SQL queries:
• SELECT COUNT (DISTINCT DepCde) FROM EMPLOYEE;
• SELECT MAX(JoinDate), MIN (JointDate) FROM EMPLOYEE;
• SELECT TName, DepHead FROM EMPLOYEE E, DEPARTMENT D
WHERE [Link] = [Link];
• SELECT COUNT (*) FROM EMPLOYEE WHERE Salary > 60000 AND Age
> 30;

Answer:

(a)

1. SELECT DEPNAME, DEPARTME-NT.

DepCde FROM EMPLOYEE, DEPART
MENT WHERE EMPLOYEE. DepCDE=
DEPARTMENT. DepCde Order by DepCde DESC;
2. Select AVG (Age) from EMPLOYEE WHERE DepCde=”103″;
3. SELECT DeptHead FROM DEPART
MENT WHERE Employee. TName=
“Sanjeev P” AND EMPLOYEE. DepCde
= DEPARTMENT. DepCde;
4. SELECT * from EMPLOYEE WHERE
joinDate<’01-JAN-2007′;
Question 11:
Consider the following tables WORKER and PAYLEVEL and answer (a) and (b) parts of
this question:

(a) Write SQL commands for the following statements:

1. To display the name of all Workers in descending order of DOB.

2. To display NAME and DESIGN of those Workers, whose PLEVEL is either P001
or
3. To display the content of all the workers table, whose DOB is in between ’19-JAN-
1984′ and ’18-JAN-1987′.
4. To add a new row with the following:
19, ‘DayaKishore’, ‘Operator’, ‘P003′, ’19- Sep-2008’, ‘ll-Jul-1984’

(b) Give the output of the following SQL queries :

1. SELECT COUNT (PLEVEL), PLEVEL FROM WORKER GROUP BY PLEVEL;

2. SELECT MAX(DOB), MIN(DOJ) FROM WORKER;
3. SELECT Name,PAY FROM WORKER
W,PAYLEVEL P WHERE [Link]=
[Link] AND [Link]<13;
4. SELECT PLEVEL, PAYLEVEL
WHERE PLEVEL=”POO3″;

Answer:
(a)

1. SELECT NAME FROM WORKER

ORDER BY DOBDESC;
2. SELECT NAME, DESIGN FROM WORKER WHERE PLEVEL=”POOO1″ OR
PLEVEL=”POO2″;
3. SELECT * FROM WORKER WHERE
DOB BETWEEN ’19-JAN-1984 AND ’18-JAN-1987′;
4. INSERT INTO WORKER VALUES (19,”DayaKISHORE”, “oPERATOR”,
“P0003”,’19-Sep-2008′,’11-Jul-1984′)’

(b)

Question 12:
Consider the following tables EMPLOYEE and SALGRADE and answer (b) and (c)
parts of this question:
(a) What do you understand by Selection and Projection operations in relational
algebra ?
(b) Write SQL commands for the following statements :

1. To display the details of all EMPLOYEES in descending order of DOJ.

2. To display NAME and DESIGN of those EMPLOYEES, whose SAL-GRADE is
either S02 or S03.
3. TO display the content Of all the EMPLOYEES table, whose DOJ is in between
’09-Feb-2006′ and ’08-Aug-2009′.
4. To add a new row with the following:
109, ‘HarishRoy’, ‘HEAD-IT’, ‘SOX, ’09-
Sep-2007′, ’21-Apr-1983’

(c) Give the output of the following SQL queries :

1. SELECT COUNT(SGRADE), SGRADE

FROM EMPLOYEE GROUP BY
SGRADE;
2. SELECT MIN(DOB), MAX(DOJ) FROM EMPLOYEE;
3. SELECT NAME, SALARY FROM
EMPLOYEE E, SAL-GRADE S WHERE
[Link]= [Link] AND [Link]<103′;
4. SELECT SGRADE, SALARY +HRA FROM SALGRADE WHERE SGRADE
=SGRADE=’S02;’

Answer:

Projection(ff): In relational algebra, a projection is a unary operafion. The result of

such projecion is defined as the set obtained when the components Of the tuple R are
restriceted to the set {a1……an}. It discards (or excludes) the other attributes.
Selection( ): In relational algebra, a selection
is a unary operation written as (R) or (R) where:

1. a and b are attribute names.

2. p is a binary operation in the set.
3. v is a value constant.
4. R is a relation.
The selection (R) selects all those tuples in R for which 9 holds between the a and the b

(b)

1. SELECT FROM EMPLOYEE ORDER BY DOJ DESC;

2. SELECT NAME, DESIGN FROM EMPLOYEE WHERE SGRADE – “S02” OR
SGRADE = “SO3;
3. SELECT * FROM EMPLOYEE WHERE DOJ BETWEEN ’09-FEB-2006′ AND ’08-
AUG -200%
4. INSERT INTO EMPLOYEE VALUES(109, “HARSH RAY”, “HEAD-IT.S02”, ’09-
SEP-
2007′, ’21-APR-1983′);

Question 13:
Consider the following tables GAMES and PLAYER and answer (b) and (c) parts of this
question :
(a)What do you understand by primary key and candidate keys ?
(b)Write SQL commands for the following statements:

1. To display the name of all GAMES with their GCodes.

2. To display details of those GAMES which are having PrizeMoney more than 7000.
3. To display the content of the GAMES table in ascending order of Schedule Date.
4. To display sum of PrizeMoney for each type of GAMES.

(c) Give the output of the following SQL queries:

1. SELECT COUNT(DISTINCT Number) FROM GAMES;

2. SELECT MAX(ScheduleDate), MIN(Sche- duleDate) FROM GAMES
3. SELECT Name, GameName FROM GAMES G, PLAYER P
WHERE ([Link]=PGcode AND [Link]- zeMoney>10000);
4. SELECT DISTINCT Geode FROM PLAYER;

Answer:

(a) An attribute or set of attributes which are used to identify a tuple uniquely is known
as a primary key. If a table has more than one such attributes which identify a tuple
uniquely than all such attributes are known as candidate keys.

(b)

1. SELECT GameName, GCode FROM GAMES;

2. SELECT * FROM Games WHERE PrizeMoney >7000;
3. SELECT * FROM Games ORDER BY ScheduleDate;
4. SELECT SUM(Pnzemoney) FROM Games GROUPBY Type;

(c)

1. 2
2. 19-Mar-2004 12-Dec-2003
3. Ravi Sahai Lawn Tennis
4. 101 108 103
Question 14:
Consider the following tables ACTIVITY and COACH and answer (a) and (b) parts of
this question :

(a) Write SQL commands for the following statements:

1. To display the names of all activities with their Acodes in descending order.
2. To display sum of PrizeMoney for the Activities played in each of the Stadium
separately.
3. To display the coach’s name and acodes in ascending order of Acode from the
table Coach.
4. 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:

1. SELECT COUNT (DISTINCT Participants Num) FROM ACTIVITY;

2. SELECT MAX (Schedule Date), Min (Schedule Date) FROM ACTIVITY;
3. SELECT Name, Activity Name FROM ACTIVITY A, COACH C
WHERE [Link]=[Link] AND [Link]- cipants Num=10;
4. SELECT DISTINCT Acode FROM COACH;

Answer:

(a)
 1. SELECT Acodes, ActivityName FROM ACTIVITY ORDER BY ACode DESC;
2. SELECT SUM(PrizeMoney) FROM ACTIVITY GROUP BY Stadium;
3. SELECT Name, Acode FROM COACH ORDER BY Acode;
4. SELECT * FROM ACTIVITY WHERE SchduleDate < ’01-Jan-2004′ ORDER BY
ParticipantsNum;

(b)

1. 3
2. 12-Dec-2003 19-Mar-2004
3. Ravinder Discuss Throw
4. 1001
1008
1003

Question 15:
Consider the following tables RESORT and OWNEDBY and answer (a) and (b) parts of
this question:

(a)Write SQL commands for the following statements:

 1. To display the RCODE and PLACE of all ‘5 STAR’ resorts in the alphabetical
order of the place from table RESORT.
2. To display the maximum and minimum rent for each type of resort from table
RESORT.
3. To display the details of all resorts which are started after 31-DEC-05 from table
RESORT.
4. Display the OWNER of all ‘5 STAR’ resorts from tables RESORT and OWNEDBY.

(b)Give output for the following SQL queries:

1. SELECT MIN(RENT) FROM RESORT Where PLACE = ‘KERALA’;

2. SELECT TYPE, START DATE FROM RESORT Where TYPE ‘2 STAR’
ORDERBY STARTDATE,
3. SELECT PLACE, OWNER FROM
OWNEDBY Where PLACE LIKE “%A”;
4. SELECT RCODE, RENT FROM RESORT, OWNEDBY WHERE (RESORT
PLACE= OWNEDBY. PLACE AND TYPE = ‘3 STAR’);

Answer:

(a)

1. SELECT RCODE, PLACE FROM RESORT mere TYPE = “5 STAR” ORDER

BY PLACE;
2. SELECr MAX (RENT), MIN (RENT) FROM RESORT GROUP BY TYPE;
3. SELECT FROM RESORT WHERE OSWAAL (BSE Question Bank. COMPUTER
SCIENCE – PYTHON, STARTDATE > ’31-DEC-05′;
4. SELECT OWNER FROM RESOR OWNEDBY B WHERE ([Link] START’ AND
[Link] [Link]);

(b)
Question 16:
Consider the following tables STORE and SUPPLIERS and answer (a) and (b) parts of
this question:

(a) Write SQL commands for the following statements:

1. To display details of all the items in the STORE table in ascending order of
LastBuy.
2. To display ItemNo and Item name of those items from STORE table whose Rate
is more than 15 Rupees.
3. To display the details of those items whose supplier code (Scode) is 22 or
Quantity in Store (Qty) is more than 110 from the table Store.
4. To display minimum Rate of items for each supplier individually as per Scode from
the table STORE.

(b) Give the output of the following SQL queries:

1. SELECT COUNT(DISTINCT Scode) FROM STORE;

2. SELECT Rate* Qty FROM STORE WHERE ItemNo=2004;
3. SELECT Item, Sname FROM STORE S, Suppliers P
4. WHERE [Link]=PScode AND ItemNo=2006;
5. SELECT MAX(LastBuy) FROM STORE;

Answer:

(a)
 1. SELECT * FROM STORE ORDER BY LastBuy ASC;
2. SELECT ItemNo, Item FROM STORE WHERE Rate > 15;
3. SELECT * FROM STORE WHERE (Scode = ’22’ OR Qty >’110′);
4. SELECT Sname, MIN(Rate) FROM STORE, SUPPLIERS WHERE STORE.
Scode = [Link] GROUP BY Sname;

(b)

1. 3
2. 880
3. Item Sname
Gel Pen Classic Premium Stationers
4. 24-Feb-10

Question 17:
Consider the following tables STOCK and DEALERS and answer (a) and (b) parts of
this question:

(a)Write SQL commands for the following statements:

1. To display the details of all Items in the STOCK table in ascending order of
StockDate.
2. To display ItemNo and Item name of those items from STOCK table whose
UnitPrice is more than Rupees 10.
3. To display the details of those items whose dealer code (Dcode) is 102 or quantity
in STOCK (Qty) is more than 100 from the table Stock.
 4. To display maximum UnitPrice of items for each dealer individually as per Dcode
from the table STOCK.

(b)Give the output of the following SQL queries:

1. SELECT COUNT(DISTINCT Dcode)

FROM STOCK;
2. SELECT Qty* UnitPrice FROM STOCK WHERE ItemNo=5006;
3. SELECT Item, Dname FROM STOCK S, Dealers D WHERE [Link]=[Link]
AND ItemNo = 5004;
4. SELECT MIN (StockDate) FROM STOCK;

Answer:

(a)

1. SELECT*FROM STOCK ORDER BY StockDate;

2. SELECT Item No, Item FROM STOCK WHERE UnitPrice >10;
3. SELECT *FROM DEALERS, STOCK
WHERE ([Link]=”102″OR [Link] >100 and DEALERS.
DCODE = [Link]);
4. SELECT MAX (Unitprice) FROM DEALERS, STOCK ORDER BY STOCK. Dcode
WHERE [Link] = [Link];

(b)

1. 3
2. 4400
3. Item Dname
Eraser Big Clear Deals
4. 01-Jan-09
 REVISION – COMPUTER SCIECE – XII STD.
[Link]. 27 (2 MARKS)
MySQL COMMANDS AND DIFFERENCES
Points to Remember:

Click Here to view MySql Notes

Sample Questions:

(2)
Answer:

(3)
Answer:
(4)

Answer:

DROP DELETE

Data Definition Language (DDL) Data Manipulation Language (DML)

Removes entire table or database, including data Removes one or more specific rows
and definition. from a table.

Deletes the table structure entirely. Keeps the table structure.

(5)

Answer:

ALTER Command UPDATE Command

Data Definition Language (DDL) Data Manipulation Language (DML)

Modifies the structure/definition of a table

Modifies existing data/records within the
(e.g., adding/dropping columns, changing data
rows of a table.
types, adding constraints).

Does not change existing data, but may affect

Changes existing data in one or more
it (e.g., a newly added column is NULL by
columns based on specified conditions.
default).
 REVISION – COMPUTER SCIECE – XII STD.
[Link]. 32 (4 MARKS)
MySQL QUERIES – One Table

Points to Remember:
• Identify the Table name and Column names.
• SELECT Command:
It helps to display the records as per our requirement.

Different forms of select command:

1. Select * from employee;
o It displays all rows and columns from the table.
[Link] ecode, ename from employee;
o It displays selected columns from the table.
[Link] displaying particular rows.
o Syntax: select * from <tablename> where <cond>;
o Eg. Select * from employee where gender=’M’;
4. ELIMINATING REDUNDANT DATA
o The distinct keyword is used to eliminate duplicate records from the table.
o Eg. Select distinct (gender) from employee;
DISTINCT(GENDER)
M
F
5. USING COLUMN ALIASES
The columns that we select in a query can be given a different name, [Link] alias name for
output purpose.
Syntax: SELECT <columnname> AS column_alias , <columnname> AS column_alias
FROM<tablename>;
[Link] ecode as “EMP_Code” from employee;

CONDITION BASED ON A RANGE

o The BETWEEN operator defines a range of values that the column values must fall into
make the condition true. The range include both lower value and upper value.
[Link] display ECODE,ENAME and GRADE of those employees whose salary is between
40000 and 50000,
Command is:
SELECT ECODE , ENAME ,GRADE FROM EMPLOYEE WHERE GROSS
BETWEEN 40000 AND 50000;

NOTE: For displaying records not in the specified range, we have to use not between operator.

CONDITION BASED ON A LIST

The in operator is used to display records based on a list of values.
Eg. To display details of employees who have scored A,B and C grades.
Select * from employee where grade in (‘A’,’B’,’C’);
Note: For displaying records that do not match in the list, we have to use not in operator.

CONDITION BASED ON PATTERN MATCHES

LIKE operator is used for pattern matching in SQL.
Patterns are described using two special wildcard characters: % and _ (underscore)
1. percent(%) – The % character matches any substring.
2. underscore(_)– The _ character matches any single character.
[Link] display names of employee whose name starts with R in EMPLOYEE table, the command is:
select ename from employee where ename like “R%”;
e.g. To display details of employee whose second character in name is:
select * from employee where ename like ‘_e%’;
SEARCHING FOR NULL
The NULL value in a column can be searched for in a table using IS NULL in the WHERE clause.
E.g. to list employee details whose salary contain NULL, we use the command:
Select * from employee where gross is null;
Note: For listing employees who earn salary, then it is:
Select * from employee where gross is not null;

Relational Operators
To compare two values, a relational operator is used. The result of the comparison is true or false.
Relational Operators recognized by SQL: =, >, <, <=, >=, <> (not equal or !=)
Eg. Select * from employee where ecode <> 1001;
Above query will not display those employee details whose ecode column value is 1001.

Logical Operators- (OR, AND, NOT)

1) To list the employee details having grades E2 or E3.
Select ecode, ename, grade, gross from employee where (grade=‘E2’ OR grade=‘E3’);
2) To list all the employees’ details having grades as ‘E4’ but with gross < 9000.
Select ecode, ename, grade, gross from employee where grade=‘E4’ and gross< 9000;
3) To list all the employees’ details whose grades are other than ‘G1’.
Select ecode, ename, grade, gross from employee where (NOT grade= ‘G1’);

Sorting Results- ORDER BY clause

Results of SQL query can be sorted in a specific order using ORDER BY clause.
The ORDER BY clause allows sorting of query results by one or more columns. The sorting can be
done either in ascending or descending order.
Eg. Select * from emp order by ename;
Above query arranges the records in alphabetical order of ename value. By default order by clause
arranges in ascending order.

TO DISPLAY RECORDS IN DESCENDING ORDER

❖ Select * from employee order by ename desc;
Above query gives output in descending order of ename.
❖ Select * from employee ORDER BY grade DESC, ename ASC;
Above query displays records first in the descending order of grade and within the same grade,
employees are displayed in the ascending order of Ename.

SQL AGGREGATE FUNCTIONS:

All the aggregate functions ignore null values except count(*).
o Avg – to compute average value
o Min – to find minimum value
o Max – to find maximum value
o Sum – to find total value
o Count – to count non-null values in a column
o Count( *) – to count total number of rows in a table including null values.
 Examples:
Select avg(gross) from employee;
Select min(gross) from employee where deptno= 10;
Select count(*) from emp where gross> 10000;
Select count (DISTINCT gender) from employee;

GROUP BY Clause
GROUP BY clause is used in SELECT statements to display the table contents based on similar values
in a column into groups.
Eg: To calculate the number of employees in each grade, the query is:
SELECT grade, count(*) from employee group by grade;
HAVING clause :
The HAVING clause in MySQL is used to filter the results of a GROUP BY query based
on aggregate functions. It is necessary because the WHERE clause cannot be used with
aggregate functions (e.g., SUM(), COUNT(), AVG(), MIN(), MAX()).

SELECT column_name(s), AGGREGATE_FUNCTION(column_name)

FROM table_name
WHERE condition(s) -- Optional: filters individual rows before grouping
GROUP BY column_name(s)
HAVING aggregate_condition(s) -- Mandatory: filters the groups after aggregation
ORDER BY column_name(s);

Examples:
To find departments that have more than a certain number of employees ( 5 employees)
SELECT Department, COUNT(EmployeeID) AS num_employees
FROM Employee
GROUP BY Department
HAVING COUNT(EmployeeID) > 5;

To find customers whose total order amount exceeds a specific value (100) :
SELECT customer_id, SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY customer_id
HAVING SUM(price * quantity) > 100;

Combining WHERE and HAVING

To filter out non-undergraduate employees first, and then find departments where the sum of salaries
for the remaining employees is greater than 20 lacs:
SELECT Department, SUM(Salary)
FROM Employee
WHERE Education <> 'UG'
GROUP BY Department
HAVING SUM(Salary) >= 20;
Sample Question:
(1)
(2)
Answer:
(3)

Answer:
(4)
(5)

Answer:
(6)
Answer:
(7)

Answer:
 REVISION – COMPUTER SCIECE – XII STD.
[Link]. 34 (4 MARKS)
MySQL JOINS – Two Tables

Points to Remember:
• Identify the common column name in both the tables.
• Use the same column names in SQL queries
• Use Natural Join or Where [Link]=[Link] for joining two tables
Example:SELECT * FROM FEE NATURAL JOIN CLASS WHERE [Link] > 5000;
(OR) SELECT * FROM FEE, CLASS WHERE [Link] = [Link] AND
[Link] > 5000;
• Any aggregate functions like AVG, SUM needs, use GROUP BY clause.
If you need to apply any aggregate condition use HAVING clause.
(Note: Having clause cannot be used without Group By)
Example:
select [Link], name, count(bno) from students s, bills b
where [Link]=[Link]
group by [Link] having count(bno)>1;

Sample Question:
(1)
(2)
(3)
(4)
(5)
(6)
(7)
 REVISION – COMPUTER SCIECE – XII STD.
[Link]. 35 (4 MARKS)
Python-MySQL connectivity
Points to Remember:
Steps for creating Database connectivity:
[Link] the package mysql connector
syntax: import [Link]
[Link] a connection to a Database
Syntax:
ConnectionObject= [Link](host=“hostname",user=“username",
password=“Passwaord",database=“Databasename")
3. Create a Cursor
Syntax: CursorObject=[Link]()
4. Execute a Query:
Synatax: [Link](“Sql query”)
[Link] data from result set:
Syntax: [Link]()
[Link]()
[Link](n)
[Link]
[Link] up the Environment:
Syntax: [Link]()
[Link] the database:
[Link]()

Extract data from result set:

[Link](): Return all records in the form of list or list of tuple.
if no more record is found it will return empty list.
[Link](): Return one record as a tuple.
If no more record is found ,it will return None.
[Link](n): Return ‘n’ numbers of records to fetch and return a list or list of tuple.
If no more records to fetch, it will return empty list.
If u will not pass the value of n by default its value is 1.
[Link] : it will return the number of rows retrieved from the cursor.

Parameterized Query:
Sometimes we need to run queries which are based on some parameters or values that are provided
from outside, such queries are called parameterised queries.

i) String template with % formatting – Old style:

In this style formatting use the form : “ f ” % ( v , ) where f is a template string and v specifies the
value(s) to be formatted. For this we have to write %s operator in place of the value to be provided
as a parameter; and provide the value for %s placeholder in the form of a tuple.
Syntax
“Select * from employee where columnname= %s” % (value,)
For example:
“Select * from employee where salary > %s ;” %(10000,)
“Select * from employee where salary > %s and department = ’%s’ ;” % (10000,’accounts’)
ii) String template with { } formatting –New style:
Syntax:
“select * from employee where columnname={} and department=‘{}’ “.format (value1,’value2’)
Example:
“select * from employee where salary>{} and department=‘{}’ “.format (10000,’Accounts’)

Performing Queries :
“Insert into tablename values({},{})”.format(value1,value2)
“Update tablename set columnname={} where columnname={}”.format(newvalue, oldvalue)
“Delete from tablename where condition={}”.format(value)

Insert and Update queries make change to the data base unlike SELECT you must commit your
query after executing INSERT and UPDATE command.

Synatx: [Link]()

Sample Question:
(1)
(2)

(3)