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