16.

(a) User defined tags

17. (b) present the data
18. (d) Video Conferencing
19. Cookies can store a wide range of information, including personally identifiableinformation
(such as your name, home address, email address, or telephone number). Cookies often
store your settings for a website, such as your preferred language orlocation. When you
return to the site, browser sends back the cookies that belong to the site. This allows the site
to present you with information customized to fit your needs.
20. (a) .com - commercial
(b) .org - organization
21. XML tags are created by the user as there are no standard tags. Ex
: <name>Nayana<name>
22. The process of converting domain names (website names) into corresponding IPaddress
with the help of DNS servers is called domain name resolution.
23. (d) XML,HTML
24. (d) Photoshop
25. HTTP - Hyper Text Transfer Protocol
26. Google Chrome or any other valid browser name
27. (B) Extensible Markup Language
28. (D) FTP
29. Web Server
30 a. Most suitable place to install the server is HR center, as this center has maximumnumber of
computers.
b.

c. Switch
95
 d. Repeater may be placed when the distance between 2 buildings is more than 70 meter.
e. WAN, as the given distance is more than the range of LAN and MAN.

TEST YOURSELF: PART I

1. Advantage: Since there is a single common data path connecting all the nodes, thebus topology
uses a very short cable length which considerably reduces the installation cost.
Disadvantage: Fault detection and isolation is difficult. This is because control of the network is
not centralized in any particular node. If a node is faulty on the bus, detection of fault may have to
be performed at many points on the network. The faulty node has then to be rectified at that
connection point.
2. (i). failure in one cable will not affect the entire network
(ii). If the central hub or switch goes down, then all the connected nodes will not be able to
communicate with each other.
3. PAN
4. i. Star Topology should be used.

ii) As per 80 20 rule, SalesDept because it has maximum no. of computers.

iii) Each building should have hub/switch and Modem in case Internet connection is required.
iv) MAN (Metropolitan Area Network)

5. VoIP

6. HTTP is a protocol that is used for transferring hypertext(i.e. text, graphic, image,
sound, video, etc,) between 2 computers and is particularly used on the World Wide
Web (WWW)
7.
(i) Network Interface Card (NIC) is a network adapter used to set up a wired
network. Itacts as an interface between computer and the network.
(ii) A repeater is a device that amplifies a signal being transmitted on the network.

96
 TEST YOURSELF: PART II

1. LAN
2. [Link] Topology
[Link] Topology
3. It is an IP Address. It is used to identify the computers on a network
4. MAN
5. (i)

(ii) The most suitable place/ building to house the server of this organization would be building
Research Lab, as this building contains the maximum number of computers
(iii). a)Repeater : distance between Store to Research Lab is quite large, so a repeater would
ideally be placed.
b)Hub/Switch : Each would be needed in all the buildings to interconnect the group of
cables from the different computers in each building.

6. IP address [Link]
URL : [Link]

97
 DATABASE
A database is an organised 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

Oracle, MySQL Server, MySQL, Sybase, SQLite, PostgreSQL, FoxPro, SAP , dBase

Need for DBMS:

1. Databases reduce redundancy i.e. it removes the duplication of data.
2. 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
3. Databases allows sharing of data
4. Database ensures data security by the process of authentication and does not allow unauthorized
access.
5. Database Maintains integrity
6. 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.

98
Eg.
Table: Student (Relation)
Attributes

Rollno Name Marks

Tuples
101 Manish 34

102 Mohit 37

103 Aryan 23

104 Ruhi 56

Various other terminologies used in relational data model

Domain: The pool of all the possible values that a column can have is referred to as a domain.
Degree of a Relation: Number of attributes/columns in a relation is referred to as degree of a
relation. For example if a table has 6 columns then the degree of a table will be 6

Cardinality of a Relation: Number of rows /tuples in a relation is referred to as the cardinality of a

relation.

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

Keys are of four types:

1. Primary Key
2. Candidate Key
3. Alternate Key
4. Foreign Key
99
Primary Key:
A primary key uniquely identifies each row in a table. A primary key cannot have NULL value. In a table
we can only have one primary key. Eg. in the above table empno is the primary key.
Candidate Key:
All the attributes in a table which have the capability to become a primary key are referred to as a
candidate [Link]. in the above table empno and ename are the attributes which have the capability to
become the primary key so, empno and ename are the candidate keys
Alternate key:
One of the candidate key is made a primary key and all the other remaining candidate keys are called
alternate keys
Eg. if empno is the primary key then the remaining candidate key ename is the alternate key.
Foreign key:
A foreign key is a non key attribute whose values are derived from the primary key of another table.
It is basically used to set the relationship among two tables.
Eg.

100
 SQL COMMANDS
DATA DEFINITION LANGUAGE

CREATE DATABASE<DATABASENAME>; To create a database in the system

SHOW DATABASES; To view the names of all databases in the
system
USE<DATABASENAME>; To open a particular database
DROP DATABASE<DATABASENAME>; To remove a database from the system
DESCRIBE TABLE<TABLENAME>; To view the structure of a table
SHOW TABLES; To view the names of tables in current
database
CREATE TABLE<Tablename> To create a new table in the current
( database:
attributename1 datatype(size) constraint, For example :
attributename2 datatype(size) constraint, CREATE TABLE EMP
. (
. EMPID INT PRIMARY KEY,
); ENAME CHAR(30) NOT NULL,
POST VARCHAR(15)
);
ALTER TABLE To modify the structure of a table
ALTER TABLE<TABLE NAME> ADD ATTRIBUTENAME For adding attributes
DATATYPE(SIZE);
ALTER TABLE<TABLENAME>DROP <ATTRIBUTENAME>; For deleting attribute
ALTER TABLE<TABLENAME>MODIFY For changing datatype, size etc. of an
attribute
DROP TABLE<TABLENAME> To remove a table from the databse

DML: DATA MANIPULATION LANGUAGE

To add new record/row/tuple/ in the

table
DELETE FROM <TABLENAME> WHERE<CONDITION>; to remove tuples from a table
UPDATE <TABLENAME> To modify or change the data in the table
SET<ATTRIBUTENAME>=NEWVALUE
WHERE <CONDITION>;
SELECT <ATTRIBUTE LIST> To view / extract rows from a table
FROM <TABLENAME>
WHERE <CONDITION>;
MYSQL : MATH FUNCTIONS

POW(X,Y) - x raise to the power of y

MOD(X,Y) - Remainder of X/Y Select
ROUND(N,D) - Rounds number N upto given D no. of digits
SQRT(X) Returns square root of X

MYSQL : STRING FUNCTIONS

101
LENGTH(STR) :
Find Number of characters in given string.
Select CONCAT('Wel', 'come');
Joins the given strings one after the other.
UPPER(STR)/UCASE(STR): Select UPPER('Kendriya')
Converts lower case alphabets of given string alphabets to
Upper case. Other charters remain as it is.
LOWER(STR)/LCASE(STR) : Select LOWER('Kendriya')
Converts Upper case alphabets of given string alphabets to 'kendriya'
lower case. Other charters remain as it is. Select LOWER('ORANGE')
'orange'
LTRIM(STR): I am learning
Removes Spaces on left side of given string.
RTRIM(STR) : I am back
Removes Spaces on Right side of given string
TRIM(STR) : ROSE IS RED
Removes both leading (left) and Trailing (right ) Spaces
from given string.
LEFT(STR,N) : Select LEFT('GREAT WORK',4)
extract N characters from left side of given String
RIGHT(STR,N) : Select RIGHT('PYTHON',4)
extract N characters from right side of given String
INSTR(STR,SUBTRING) : Select INSTR("apple", "p");
returns the position of the first occurrence of a string in
another string.
SUBSTR(STR, position, no. of characters) or MID(STR, Select MID('Kendriya',4,2)
position, no. of characters)

102
 Multiple Choice Questions
1 Fill in the blank:
_____ is a non-key attribute, whose values are derived from the primary key of some other
table.
(A) Primary Key (B) Candidate Key (C) Foreign Key (D) Alternate Key
2 An Attribute in a relations a foreign key if it is the _____ key in any other relation.
(A) Candidate Key (B) Foreign Key (C) Primary Key (D) Unique Key
3 An attribute in a relation is a foreign key if it is the ________ key in any other relation.
(A) Candidate Key (B)Foreign Key (C) Primary Key (D)Unique Key
4 _________ is an attribute, whose values are Unique and not null.
(A) Primary Key (B)Foreign Key (C)Candidate Key (D)Alternate Key
5 Select the correct statement, with reference to RDBMS:
a) NULL can be a value in a Primary Key column
b) ' ' (Empty string) can be a value in a Primary Key column
c) A table with a Primary Key column cannot have an alternate key.
d) A table with a Primary Key column must have an alternate key.

6. Layna creates a table STOCK to maintain computer stock in vidyalaya. After creation of the table, she
has entered data of 8 items in the table.

Based on the data given above answer the following questions:

(i) Identify the most appropriate column, which can be considered as Primary key.
(ii) What is the degree and cardinality of the given table.
(iii) If three columns are added and 5 rows are deleted from the table stock, what will be the new
degree and cardinality of the above table?
7. What is the difference between primary, candidate and alternate key? Explain with the help of an
example.
8. Name any two RDBMS software.
9. Rahul is confused between tuple and attributes help him to define Tuple and Attribute with
appropriate example.
10. What is the difference between the cardinality and degree of a relation?

103
 STRUCTURED QUERY LANGUAGE (SQL)
Structured query language (SQL) is a programming language for storing and processing information in a
relational database. A relational database stores information in tabular form, with rows and columns
representing different data attributes and the various relationships between the data values.
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.
Relational Data Model
In this model data is organized into tables. These tables are called relations. Rows of table are known as
tuples and columns are known as attribute.
Domain:
It is a pool of values from which the actual values appearing. For example if there 40 students having
rollno 1 to 40 and we assigning work to these students then domain for rollno is 1 to 40
Tuple: Tuples are row of a table.
Attribute: Attributes are column of relation.
Degree: Total number of attributes (Column) in a table are known as Degree.
Cardinality:
Total number of tuples (Row) in a table are known as Cardinality. Heading row is not included while
calculating cardinality of a table
Candidate Key:
A Candidate Key is the one that is capable of becoming Primary key i.e., a column that has unique value
for each row in the relation and that can not be NULL.
For example if there is a table student having columns (Rollno , AdmNo, Name , fees ,city and DOB) . In
this table Rollno and AdmNo both columns have all the properties to become primary key of table. That
is why Rollno and AdmNo are known as Candidate key.
Primary Key:
It is set of one or more columns that can uniquely identify tuples with in the relation. It cannot be null.
For example in student table RollNo is selected as Primary key.
Alternate Key:
A candidate key that is not primary key is called alternate key. Example is AdmNo because it is candidate
key but not selected as primary key.
Foreign Key:
A non-key attribute of a table , which is primary key in another table. The values of foreign key are
derived from the primary key of base table. For example we are having a table Library having columns
BookNo, BName, RollNo)
Here Bookno is Primary key
RollNo is Foreign key in Library table as it is primary key in student table.

104
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 four types of SQL commands: DDL, DML, DCL, TCL

DDL :- Create , Drop, Alter , Describe

DML-Insert, Update, Delete , Select

DCL- Grant , Revoke

TCL- Commit , Rollback

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.

DESC: This is used to DESCRIBE the structure of table.

DML (Data Manipulation Language):

The SQL commands that deals 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.
SELECT: It is used to display records on screen.

105
DATA TYPES
Data types are means to identify the type of data and associated operations for handling it.
MySQL data types are divided into following categories:

Data type Description

CHAR(size) A FIXED length string its size can be from 0 to 255. Default is 1

VARCHAR(size) A VARIABLE length string, its size can be can be from 0 to 65535

INT(size)/ Integer Number without decimal point. Example int(11) or integer

FLOAT(size, d) / A floating point number. The total number of digits is specified in size. The
Decimal number of digits after the decimal point is specified in the d parameter.
Example float(10,2) . Example 3455738.50

A date. Format: YYYY-MM-DD.

DATE
Example DOB date

DATABASE COMMANDS
1. VIEW EXISTING DATABASE
To view existing database names, the command is:
SHOW DATABASES;
2. CREATING DATABASE IN MYSQL
For creating the database in MySQL, we write the following command:
CREATE DATABASE <databasename>; [Link] order to create a database Student, command is:
CREATE DATABASE Student;
3. ACCESSING A DATABASE
For accessing already existing database,we write:
USE<databasename>;
[Link] access a database named Student, we write command as:
USE Student;

4. DELETING DATABASE
106
For deleting any existing database,the command is:
DROP DATABASE <databasename>;
[Link] delete a database, say student, we write command as:
DROP DATABASE Student;
5. VIEWING TABLE IN DATABASE
In order to view tables present in currently accessed database, command is:
SHOW TABLES;
DDL COMMANDS :-
CREATING TABLES IN MYSQL
Syntax of CREATE TABLE command is:
CREATE TABLE <table-
E.g. In order to create table EMPLOYEE given below:
ECODE ENAME GENDER GRADE GROSS

Create table employee (ecode integer, ename varchar(20),gender char(1),grade char(2),gross integer);
ALTER TABLE
ALTER TABLE command is used to change the structure of the existing table. It can be used to add or drop
new columns or modify the existing columns of table.
Eg. 1. Alter table Employee Add email char(20);
2. ALTER TABLE Employee MODIFY (ename varchar(60));
2. Alter table employee drop email;

DROP TABLE:
DROP TABLE command allows to remove a table from database. Once the DROP command is issued, the
table will no longer be available in the database.
Eg. DROP TABLE employee;
DESC TABLE:
DESC TABLE command display the structure of table on screen.
Eg. DESC employee;

DML Commands

107
INSERTING DATA INTO TABLE:
Syntax:

Or
Insert into <tablename>(<column list> )values(<values list>);

Or

The left out columns will be filled with null values.

DELETE Command
This command removes rows from a table.
Syntax: DELETE FROM <tablename> [WHERE <cond>];
Eg: To remove all the contents of items table, the query is:
DELETE from items;
Eg: To remove the tuples from employee that have gross less than 20000 is :
DELETE from employee WHERE gross<20000;
UPDATE Command
Update Command allows to change some or all the values in an existing rows. Update command specifies
the rows to be changed using the WHERE clause and the new data using the SET keyword.
Eg. UPDATE employee SET gross= 25000;
The above query sets the gross of all records as 25000.

The above query changes the gross and grade values for the record with ecode 1001.
SELECT COMMAND:
It helps to display the records as per our requirement.
Different forms of select command:
1. Select * from employee;
It displays all rows and columns from the table.
2. SELECT ECODE, ENAME FROM EMPLOYEE;
It displays selected columns from the table.

3. FOR DISPLAYING PARTICULAR ROWS.

108
4. ELIMINATING REDUNDANT DATA

The distinct keyword is used to eliminate duplicate records from the table. Eg. Select distinct (gender)
from employee;

DISTINCT(GENDER)

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

CONDITION BASED ON A RANGE

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 operators.

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.

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:

e.g. To display details of employee whose second character in name is:

109
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.

2. To list

3.
Select ecode,
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.
Select * from employee order by ename desc;
Above query gives output in descending order of ename.

Select * from employee ORDER BY ename ASC;

Above query gives output in ascending order of ename.

SQL AGGREGATE FUNCTIONS:

110
 FUNCTIONS: In MYSQL function is a special type of predefined command set that perform some
operation and return a single value. The value that are provided to functions are called parameters or
arguments.
Functions can be divided into two categories.
1. Single row function :- This type of function can apply on each row of [Link] are
(a ) Lower/Lcase: This function convert a string into lower case. Example :
Select Lower(name) from student;

2. Group Row Function : These function can work on group of rows and give you a single result.
(1) AVG ( ) This function computes the average of given data.

Example : Select avg(fees) from student ;

Avg to compute average value

Min to find minimum value
Max to find maximum value Sum to find total value
Count to count non-null values in a column
Count( *) to count total number of rows in a table including null values.
All the aggregate functions ignore null values except count(*).
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;
Placing conditions on Groups HAVING Clause
The HAVING clause places conditions on groups in contrast to WHERE clause that places conditions on
individual rows.
WHERE conditions cannot include aggregate functions but HAVING conditions can do so.

Eg: SELECT avg(gross), sum(gross) from employee GROUP BY grade HAVING

INTEGRITY CONSTRAINTS

111
A constraint is a condition or check applicable on a field or set of fields. Common types of constraints
include:

[Link] Description

1 NOT NULL Ensures that a column cannot have NULL value

2 DEFAULT Provides a default value for a column when none is Specified

3 UNIQUE Ensures that all values in a column are different

4 PRIMARY KEY Used to uniquely identify a row in the table

5 FOREIGN KEY Used to ensure referential integrity of the data

ADDING CONSTRAINT TO A TABLE

ALTER TABLE statement can be used to add constraints to your existing table by using it in following
manner:
Eg: alter table employee add primary key(ecode);
REMOVING CONSTRAINTS FROM A TABLE
Eg: alter table employee drop primary key;
SETTING PRIMARY AND FOREIGN KEY CONSTRAINT:
Eg: CREATE TABLE STUDENT(ROLL_NO integer PRIMARY KEY ,NAME VARCHAR(30),CLASSVARCHAR(3));
CREATE TABLE SCORE(ROLL_NO integer ,MARKS integer, FOREIGN KEY(ROLL_NO) REFERENCES
STUDENT(ROLL_NO));
SQL JOINS
SQL Joins are essential to display data from more than one table. SQL JOIN clause is used to combine rows
from two or more tables, based on a common field between them.
SQL provides various types of joins:
1. Cartesian Product or Cross Join
2. Equi-Join
3. Natural Join.
Cartesian Product (Cross Join)
Cartesian product of two tables is obtained by pairing up each row of one table with each row of the
other table.
The number of columns in the Cartesian product is the sum of the number of columns in both the
tables.
The number of rows in the Cartesian product is the product of rows of the tables.

Example:
112
If A table has 3 Rows and 4 columns and B table has 6 rows and 7 columns then write No of rows and
columns in C table which is Cartesian product of Table A and B
Answer is Rows = 3 X6= 18
Columns = 4+7=11
Equi-Join
A join which is obtained by putting a condition of equality on cross join is called an 'equi join'.
We can extract meaningful information from the Cartesian product by placing some conditions in the
statement.
The join in which columns are compared for equality is called equi-join.
In this type of join we put * in the select list therefore the common column will appear twice in the
output.
Example: Consider the 2 tables emp and dept.

On performing equi-join, the result is as follows:

Note: We see that deptno column appears twice in output.

Natural Join

113
 The join in which only one of the identical columns exists is called natural join.
It is similar to equi-join except that duplicate columns are eliminated in natural join that would
otherwise appear in equi-join.
Example:

Note: We see that deptno column appears only once in output.

MULTIPLE CHOICE QUESTIONS (MCQS)

Q1. Which clause is used for Pattern Matching -

(a) between (b) in (c) like (d) Compare
Q2. The default date format in MySQL is:
(a) DD/MM/YYYY (b) YYYY/MM/DD c) MM-DD-YYYY (d) YYYY-MM-DD
Q3. Which is not a way to represent comment in MySQL?
(a) /* */ (b) -- (c) # (d) //
Q4. The command is used to access database in MySQL is-
(a) Open <databasename>; (b) USE <databasename>;
(c) Access <databasename>; (d) (a)&(b) both
Q.5. Which is a valid CREATE TABLE statement?
(a) Create table emp add(id integer(3)); (b) Create table emp(id integers(3));
(c) Create table emp modified(id integer(3)); (d) Create table emp(id integer(3));

(a) (b)
(c) (d)
Q.7. which keyword is used to retain duplicate value in select command
(a) distict (b) show (c) all (d) like
Q.8 Conditionally retrieval of rows from a table with SELECT, which clause is used?
(a) Where (b) Having (c) Group By (d) Order by
114
Q.9. The key word eliminates duplicate rows from the result of a SELECT statement.
(a) All (b) Unique (c) Distinct (d) IN
Q10. Which operator defines a range of values that the column values must fall in?
(a) In (b) Like (c) Between (d) Is
Q11.
(a) In (b) Like (c) Between (d) Is
Q12. We use operator with select for condition based on pattern matching.
(a) In (b) Like (c) Between (d) Is

Q13. To see all the databases which command is used?

(a)Show database; (b) Show databases; (c) Show database(); (d) Show_all database;

their name in descending order of salary. The correct

SQL statement is:
(a)
(b)
(c)
(d)

Q. 15. In which form MYSQL query results are displayed?

(a) LIST (b)TUPLE (c) ROWS AND COLUMNS (d) LIST AND TUPLES BOTH

ANSWERS
[Link]. Answers [Link]. Answers
1 c 8 a
2 d 9 c
3 d 10 c
4 b 11 a
5 d 12 b
6 d 13 b
14 d
7 c
15 c

VERY SHORT ANSWER QUESTIONS (VSA)

115
 (1 Mark each)
Q1. Answer the following questions:
I. Differentiate between DDL and DML?
II. What is a constraint?
III. What are single row functions ?
IV. Compare CHAR and VARCHAR data types.
V. What are the differences between DELETE and DROP commands of SQL?
VI. What do you understand by MySQL Client?
VII. Differentiate between WHERE and HAVING clause.
VIII. A table "Animals" in a database has 3 columns and 10 records. What is the degree and cardinality
of this table?
IX. What is the difference between commit and rollback command.
X. Which keyword is used to remove redundant data from a relation?
ANSWERS:
(i) Data Definition Language (DDL): This is a category of SQL commands. All the commands which are used
to create, destroy, or restructure databases and tables come under this category. Examples of DDL
commands are - CREATE, DROP, ALTER.
Data Manipulation Language (DML): This is a category of SQL commands. All the commands which are
used to manipulate data within tables come under this category. Examples of DML commands are -
INSERT, UPDATE, DELETE.
(ii) A constraint is a condition or check application on a field or set of fields.
Example: NOT NULL (ensure that column con not have null value), CHECK (make sure that all value
satisfy certain criteria), UNIQUE (ensure that all values in a column are different) etc.
(iii) Single Row Function work with a single row at a time. A single row function returns a result for every row
of a quired table
Examples of Single row functions are Sqrt(), Concat(), Lcase(), Upper(), Day(), etc.
(iv) The CHAR data-type stores fixed length strings such that strings having length smaller than the field size
are padded on the right with spaces before being stored. The VARCHAR on the other hand supports
variable length strings and therefore stores strings smaller than the field size without modification.
(v) DELETE is DML command while DROP is a DDL command. Delete is used to delete rows from a table while
DROP is used to remove the entire table from the database.
(vi) MySQL Clients are programs that connect to MySQL Server and issue queries in predefined format.
(vii) WHERE clause is used to select particular rows that satisfy the condition where having clause is used
in connection with the aggregate function GROUP BY clause. FOR EXAMPLE-
select * from student where marks >80;
Select * from student group by stream having marks>90;
(viii) Degree 3 and Cardinality=10
(ix) COMMIT command permanently saves the changes made during the transaction execution. ROLLBACK
command undoes the changes made during transaction execution.
(x) DISTINCT

116
 SHORT ANSWER QUESTIONS
(2 Marks Each)
Q1. Differentiate between Candidate Key and Primary Key in the context of Relational Database Model.
Ans. A table may have more than one or a combination of attribute(s)that identifies a tuple uniquely. All
such attribute(s) are known as Candidate Keys.
Out of all the Candidate keys, the most appropriate one, which is used for unique identification of the
Tuples, is called the Primary Key.
Q2. Sreenath created the following table STUDENT in his database.
Table : STUDENT

Rollno Name CLASS Mark

1 Ritika 12 40

2 Angad 12 35

3 Kaveri 11 42

4 Lalitha 12 21

5 Daniel 11 44

6 Rabindra 11 39

7 Rabia 11 28

He wants to now count number of students in each CLASS where the number of students is more than 3.
He has executed the following query.
SELECT MAX(MARKS) FROM STUDENT WHERE COUNT(*)>3 GROUP BY CLASS;
But, he got an error. Identify the error and rewrite the query.
Ans. To filter more condition with group by clause HAVING clause is used in place of WHERE clause.
Correct query is
SELECT MAX(MARKS) FROM STUDENT GROUP BY CLASS HAVING COUNT(*)>3;
Q.3. Difference between Where and Having Clause
Answer : WHERE Vs HAVING: WHERE is used to put a condition on individual row of a table whereas
HAVING is used to put condition on individual group formed by GROUP BY clause in a SELECTstatement.
Q.4. Difference between alter command and update command
Ans: 1. Alter is a DDL command while update is a DML command
2 Alter command is used to change the structure of table while update is used to change in records
Q.5. Difference between drop command and drop clause of Alter table .
Ans:- Drop command is used to delete the entire table along with its structure while drop clause is used
with alter table command to drop a column or constraints
Q.6. Difference between Unique and Primary Key

Ans. Primary Key: In a table there can be one primary key with one column or one combination of
column. It cannot be Null.
117
 Unique: In a table there can be Unique constraints can be applied to any number of columns . It
can have Null values.

Q.7. Define Commit and Rollback Commands

Answer : COMMIT-This statement is used to end a transaction and make all changes permanent. Until
atransaction is committed, other users cannot see the changes made to the database.
ROLLBACK- The ROLLBACK statement is used to end a transaction and undo the work done by
user.
Q.8. Difference between Count(*) and Count(name) Functions.
Answer : COUNT(*) will count all rows in the table. It also include rows having NULL value for the
column.
COUNT(NAME) will count only those rows where value of Name is given. If for any row Name
is NULL/ empty then it will not count it.

Q9. Mr. Roger is using a table LIBRARY. It has the following columns:
BCode, BName, Price, author.
He wants to display maximum price Author wise. He wrote the following command:
SELECT Author, Max(Price) FROM LIBRARY;

But he did not get desired result. Rewrite the above query with necessary change to help him get the
desired output and explain the reason.

Ans. SELECT Author, Max(Price) FROM LIBRARY GROUP BY AUTHOR;

Group By clause is used to group the rows together that contain the same values in a specified
[Link] to display maximum price of each author , the table must be grouped author wise using
group by clause.

Q10. Differentiate ORDER BY and GROUP BY with an example.

Ans. Order By: Order by keyword sort the result-set either in ascending or in descending order. This
clause sorts the result-set in ascending order by default. In order to sort the result-set in descending
order DESC keyword is used.
Group By: Group by statement is used to group the rows that have the same value. It is often used
with aggregate functions for example: AVG(), MAX(), COUNT(), MIN() etc.

118
 LONG ANSWER QUESTIONS (6 MARKS)

Q.1. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to(viii), which are basedon the
tables.
TRAINER

TID TNAME CITY HIREDATE SALARY

101 SUNAINA MUMBAI 1998-10-15 90000
102 ANAMIKA DELHI 1994-12-24 80000
103 DEEPTI CHANDIGARG 2001-12-21 82000
104 MEENAKSHI DELHI 2002-12-25 78000
105 RICHA MUMBAI 1996-01-12 95000
106 MANIPRABHA CHENNAI 2001-12-12 69000

COURSE
CID CNAME FEES STARTDATE TID
C201 AGDCA 12000 2018-07-02 101
C202 ADCA 15000 2018-07-15 103
C203 DCA 10000 2018-10-01 102
C204 DDTP 9000 2018-09-15 104
C205 DHN 20000 2018-08-01 101
C206 O LEVEL 18000 2018-07-25 105

(i) Display the Trainer Name, City & Salary in descending order of their Hiredate.
(ii) To display the TNAME and CITY of Trainer who joined the Institute in the
month of December 2001.
(iii) To display TNAME, HIREDATE, CNAME, STARTDATE from tables TRAINER and COURSE
of all those courses whose FEES is less than or equal to 10000.
(iv) To display number of Trainers from each city.
(v) SELECT TID, TNAME, FROM TRAINER WHERE CITY NOT
(vi) SELECT DISTINCT TID FROM COURSE;
(vii) SELECT TID, COUNT(*), MIN(FEES) FROM COURSE GROUP BY TID HAVING COUNT(*)>1;
(viii) SELECT COUNT(*), SUM(FEES) FROM COURSE WHERE STARTDATE< -09-

ANSWER:

(i) SELECT TNAME, CITY, SALARY FROM TRAINER ORDER BY HIREDATE;

(ii) -12- -12-
(iii) SELECT TNAME,HIREDATE,CNAME,STARTDATE FROM TRAINER, COURSE WHERE
[Link]=[Link] AND FEES<=10000;
(iv) SELECT CITY, COUNT(*) FROM TRAINER GROUP BY CITY;

v) TID TNAME
103 DEEPTI
106 MANIPRABHA

119
 vi) DISTINCT TID
101
103
102
104
105
vii) TID COUNT(*) MIN(FEES)
101 2 12000
Viii) COUNT(*) SUM(FEES)
4 65000
Q2. Consider the following tables GAMES and PLAYER. Write SQL commands for the statements to (iv)
and give outputs for SQL queries (v) to (viii).
Table: GAMES

GCode GameName Number PrizeMoney ScheduleDate

101 Carom Board 2 5000 23-Jan-2004
102 Badminton 2 12000 12-Dec-2003
103 Table Tennis 4 8000 14-Feb-2004
105 Chess 2 9000 01-Jan-2004
108 Lawn Tennis 4 25000 19-Mar-2004
Table: PLAYER
PCode Name Gcode
1 Nabi Ahmad 101
2 Ravi Sahai 108
3 Jatin 101
4 Nazneen 103
I. To display the name of all Games with their Gcodes.
II. To display details of those games which are having PrizeMoney more than 7000.
III. To display the content of the GAMES table in ascending order of ScheduleDate.
IV. To display sum of PrizeMoney for each of the Number of participation groupings (as shown in
column Number 2 or 4)
V. SELECT COUNT(DISTINCT Number) FROM GAMES;
VI. SELECT MAX(ScheduleDate),MIN(ScheduleDate) FROM GAMES;
VII. SELECT SUM(PrizeMoney) FROM GAMES;
VIII. SELECT DISTINCT Gcode FROM PLAYER;
ANSWER
I. SELECT GameName,Gcode FROM GAMES;
II. SELECT * FROM GAMES WHERE PrizeMoney>7000;
III. SELECT * FROM GAMES ORDER BY ScheduleDate;
IV. SELECT SUM(PrizeMoney),Number FROM GAMES GROUP BY Number;
V. 2
120
 VI. 19-Mar-2004 12-Dec-2003
VII. 59000
VIII. 101
103
108
Q3. Consider the following tables FACULTY and COURSES. Write SQL commands for the statements (i) to
(iv) and give outputs for SQL queries (v) to (vi).

FACULTY

F_ID Fname Lname Hire_date Salary

102 Amit Mishra 12-10-1998 12000

103 Nitin Vyas 24-12-1994 8000

104 Rakshit Soni 18-5-2001 14000

105 Rashmi Malhotra 11-9-2004 11000

106 Sulekha Srivastava 5-6-2006 10000

COURSES

C_ID F_ID Cname Fees

C21 102 Grid Computing 40000

C22 106 System Design 16000

C23 104 Computer Security 8000

C24 106 Human Biology 15000

C25 102 Computer Network 20000

C26 105 Visual Basic 6000

I. To display details of those Faculties whose salary is greater than 12000.

II. To display the details of courses whose fees is in the range of 15000 to 50000 (both values
included).
III.
IV.
V. Select COUNT(DISTINCT F_ID) from COURSES;
VI. Select Fname,Cname from FACULTY,COURSE where COURSE.F_ID=[Link];

121
 ANSWER
I. Select * from faculty where salary > 12000;
II. Select * from [Link] fees between 15000 and 50000;
III.
IV. Select * from faculty fac,courses cour where fac.f_id = cour.f_id and [Link] = 'Sulekha' order by
cname desc;
V. 4
VI.

Amit Grid Computing

Rakshit Computer Security

Rashmi Visual Basic

Sulekha Human Biology

Q-4 Write SQL Command for (a) to (e) and output of (f)
TABLE : GRADUATE

[Link] NAME STIPEND SUBJECT AVERAGE DIV

1 KARAN 400 PHYSICS 68 I
2 DIWAKAR 450 COMP Sc 68 I
3 DIVYA 300 CHEMISTRY 62 I
4 REKHA 350 PHYSICS 63 I
5 ARJUN 500 MATHS 70 I
6 SABINA 400 CHEMISTRY 55 II
7 JOHN 250 PHYSICS 64 I
8 ROBERT 450 MATHS 68 I
9 RUBINA 500 COMP Sc 62 I
10 VIKAS 400 MATHS 57 II
a) List the names of those students who have obtained DIV I sorted by NAME.
b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a year
assuming that the STIPEND is paid every month.
c) To count the number of students who are either PHYSICS or COMPUTER SC graduates.
d)
e) Add a new column GRADE of character type.
f) Give the output of following sql statement based on table GRADUATE:
I.
II. Select SUM(STIPEND) from GRADUATE WHERE div=2;
III. Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;
IV. Select COUNT(distinct SUBJECT) from GRADUATE;

122
 ANSWER
a)
b) SELECT NAME,STIPEND,SUBJECT, STIPEND*12 from GRADUATE;
c) SELECT SUBJECT,COUNT(*) from GRADUATE group by SUBJECT having

d)
e) ALTER TABLE GRADUATE ADD(GRADE CHAR(2));
f) (i) 63
ii. 800
iii. 475
iv. 4
Q.5. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are based on the
tables.
Table : VEHICLE
CODE VTYPE PERKM
101 VOLVO BUS 160
102 AC DELUXE BUS 150
103 ORDINARY BUS 90
105 SUV 40
104 CAR 20
Note : PERKM is Freight Charges per kilometer , VTYPE is Vehicle Type
Table : TRAVEL
NO NAME TDATE KM CODE NOP
101 Janish Kin 2015-11-13 200 101 32
103 Vedika Sahai 2016-04-21 100 103 45
105 Tarun Ram 2016-03-23 350 102 42
102 John Fen 2016-02-13 90 102 40
107 Ahmed Khan 2015-01-10 75 104 2
104 Raveena 2016-05-28 80 105 4
NO is Traveller Number
KM is Kilometer travelled
NOP is number of travellers travelled in vehicle
TDATE is Travel Date
(i) To display NO, NAME, TDATE from the table TRAVEL in descending order of NO.
(ii) To display the NAME of all the travellers from the table TRAVEL who are travelling by vehicle
withcode 101 or 102.
(iii) To display the NO and NAME of those travellers from the table TRAVEL who travelled
-12- and -04-
(iv) To display all the details from table TRAVEL for the travellers, who have travelled distance
more than 100 KM in ascending order of NOP.
(v) Modify the definition of column VTYPE. Increase its size to 20.
(vi) SELECT COUNT (*), CODE FROM TRAVEL GROUP BY CODE HAVING COUNT(*)>1;
(vii) SELECT DISTINCT CODE FROM TRAVEL;
(viii) SELECT [Link],NAME,VTYPE FROM TRAVEL A,VEHICLE B WHERE [Link]=[Link] ANDKM<90;
123
 ANSWER
(i) SELECT NO, NAME, TDATE FROM TRAVEL ORDER BY NO
(ii) SELECT NAME FROM TRAVEL WHERE OR
SELECT NAME FROM TRAV
(iii) SELECT NO, NAME from TRAVEL WHERE TDATE >= AND TDATE <=
OR
SELECT NO, NAME from TRAVEL WHERE TDATE BETWEEN -04- AND -12-
(iv) SELECT * FROM TRAVEL WHERE KM > 100 ORDER BY
(v) ALTER TABLE VEHICLE MODIFY VTYPE VARCHAR(20);

(vi) COUNT(*) CODE

2 101
2 102
(vii) DISTINCT CODE
101
102
103
104
105
(viii) CODE NAME TYPE
104 Ahmed Khan CAR
105 Raveena SUV

124
 INTERFACE PYTHON WITH SQL DATABASE
Database connectivity
Database connectivity refers to connection and communication between an application and a database
system. The steps are

(i) We use pip install [Link]:This command we use to install library of MySQL with python.
(ii) import [Link]
get any error means this module working properly.
(iii) mydb=[Link] =
To make the connection with MySQL database using connect() function where user, password and
database are as per our system which we assign during installing of MySQL. Mydb is connection object.
(iv)cursor = [Link]() -a database cursor is useful control structure for row by row
processing of records
(v) : It will execute the sql query and store the retrieved
records.
(vi) data = [Link]() : Extract data from result set using fetch() functions.
fetchall() :It will return all the records retrieved in tuple form.
fetchone() :It will return one record from the result set.
fetchmany(n) :It will return number of records as per value of n and by-default only one
record.
(vii) count = [Link]
It is the property of cursor object that return number of rows retrieved.

# TO CREATE A TABLE IN MYSQL USING PYTHON INTERFACE

import [Link]
mydb = [Link](host="localhost",user="root", passwd="system", database="student")
mycursor=[Link]()
[Link]("CREATE TABLE FEES (ROLLNO INT,NAME VARCHAR(20),AMOUNT INT);")
# TO SHOW THE TABLES IN MYSQL USING PYTHON INTERFACE
import [Link]
mydb=[Link](host="localhost",user="root",passwd="system ", database="student")
mycursor=[Link]()
[Link]("SHOW TABLES")
for x in mycursor:
125
 print(x)
#TO DESCRIBE TABLE STRUCTURE USING PYTHON INTERFACE
mydb=[Link](host="localhost",user="root",passwd="system",database="student")
mycursor=[Link]()
[Link]("DESC STUDENT")
for x in mycursor:
print(x)
# TO EXECUTE SELECT QUERY USING A PYTHON INTERFACE
import [Link]
conn = [Link] (host = "localhost",user = "root",passwd = "12345", database="student")
c=[Link]()
[Link]("select * from student")
r=[Link]()
while r is not None:
print(r)
r=[Link]()

# TO EXECUTE SELECT QUERY WITH WHERE CLAUSE USING A PYTHON INTERFACE

import [Link]
conn=[Link](host="localhost",user="root",passwd="12345",database="student")
c=[Link]()
[Link]("select * from student where marks>90")
r=[Link]()
count=[Link]
print("total no of rows:",count)
for row in r:
print(row)
#TO INSERT A RECORD (ROLLNO,NAME,AND MARKS) IN MYSQL TABLE student USING
#PYTHON INTERFACE
import [Link]
mydb= [Link](host="localhost",user="root",passwd="system",database="student")
mycursor=[Link]()
r=int(input("enter the rollno"))
n=input("enter name")
m=int(input("enter marks"))
[Link]("INSERT INTO student(rollno,name,marks) VALUES({},'{}',{})".format(r,n,m))
[Link]()
print([Link],"RECORD INSERTED")

# TO UPDATE A DATA IN A TABLE USING PYTHON INTERFACE

import [Link]
mydb=[Link](host="localhost",user="root",passwd="system",database="student")
126
 mycursor=[Link]()
[Link]("UPDATE STUDENT SET MARKS=100 WHERE MARKS=40")
[Link]()
print([Link],"RECORD UPDATED")

# TO DELETE A RECORD FROM THE TABLE USING PYTHON INTERFACE

import [Link]
mydb=[Link](host="localhost",user="root",passwd="system",database="student")
mycursor=[Link]()
[Link]("DELETE FROM STUDENT WHERE MARKS<50")
[Link]()
print([Link],"RECORD DELETED")

# TO DROP AN ENTIRE TABLE FROM MYSQL DATABASE USING PYTHON INTERFACE

import [Link]
mydb=[Link](host="localhost",user="root",passwd="system", database="student")
mycursor=[Link]()
[Link]("DROP TABLE STUDENT")

# TO ADD A COLUMN IN THE EXISTING TABLE USING PYTHON INTERFACE

import [Link]
mydb=[Link](host="localhost",user="root",passwd="system", database="student")
mycursor=[Link]()

[Link]()

#TO DROP A COLUMN FROM THE TABLE USING PYTHON INTERFACE

import [Link]
mydb=[Link](host="localhost",user="root",passwd="system", database="student")
mycursor=[Link]()

[Link]()

# TO ALTER THE DATATYPE OF A COLUMN IN A TABLE USING PYTHON INTERFACE

import [Link]
mydb=[Link](host="localhost",user="root",passwd="system", database="student")
mycursor=[Link]()
[Link]("ALTER TABLE STUDENT MODIFY GRADE CHAR(3)")
commit(): After executing insert or update query we must commit our transaction using commit
method of connection object.
Eg: [Link]()
rollback(): [Link]() reverts the changes made by the current transaction.
rowcount: This attribute returns the number of rows that were affected by an execute()
127
PARAMETERIZED QUERIES

values user has passed. So for that we have this option parameterized queries. There are two
ways to use parameterized queries:
1. with % formatting pattern
2. with {}.format pattern
With % formatting pattern

This pattern takes the general form f % v, where f is a format and v is the value. Consider the
following code:
import [Link] as msql
import time
mydb=[Link](host='localhost',user='root',passwd='MySQL@123',database='School')
cur=[Link]()

#display records more than 80%

[Link]("select * from students where marks >%s" %(80,))
d=[Link]()
for r in d :
print(r)
#display records having B1 grade

[Link]( "select * from students where grade='%s'" %('B1',) )

d=[Link]()
for r in d :
print(r)
With {}.format pattern

In this pattern you can write {} where the value is placed followed by .format(values).
Consider the following code:
import [Link] as msql
import time
mydb=[Link](host='localhost', user='root', passwd='kvs@123',database='School')
cur=[Link]()
[Link] ( "select * from students where marks >{}" .format(80) )
d=[Link]()
for r in d :
print(r)
[Link]("select * from students where grade='{}'".format('B1'))
d=[Link]()
for r in d :
print(r)

128
 Close the connection
Since the database can keep limited number of connections at a time, we must close the connection
using
[Link]()
Eg: [Link]()
[Link]()
( VERY SHORT ANSWER QUESTIONS- 1 MARK)
Q1. Which command is use to install MySQL library in python?
Ans: pip install MySQL. Connector with path of python
Q2. Which method we use to establish the connection?
Ans: connect() method with connection object.
Q3. Which statement we use to access the MySQL module?
Ans: import [Link]
Q4. What is the Database Connector?
Ans. A database connector is a software that connects an application to any database.
Q5. Which function is used to check the successful connection?
Ans .is_connected() method

SHORT ANSWER QUESTIONS (2 MARKS)

1. What are the steps for creating database connectivity applications?
Ans. 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. What is a connection? What is its role?

Ans. 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. What is a resultset?
Ans. 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. What is a database cursor?
129
Ans. 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. How to retrieve data from a table?
Ans. 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 (3 MARKS)

1. Write a Python code to connect to a database
Ans.
import [Link]

print(mycon)
2. How to create a database in MySQL through Python ?
Ans.
import [Link]

cursor=[Link]( )

3. Write the Python code to display the present databases in MySQL

Ans.
import [Link]

cursor=[Link]()

for i in cursor:
print(i)
4. How to create a table in MySQL through Python ?
Ans.
import [Link]

cursor=[Link]()
sname varchar(30), gender char(2),

5. Write the Python code to insert data into student table of database kvs in MYSQL .
Ans.
import [Link]
,
)
cursor=[Link]()

130
 query= insert into student values( {}, , , , , {}
[Link](query)
[Link]( )
6. How to fetch data in Python from a student table of education database in MySQL ?
Ans.
import [Link]

cursor=[Link]()

for row in cursor:

print(row)
7. Write the Python code to update a record in the student table of education database.
Ans.
import [Link]

cursor=[Link]()

[Link]( )
8. Write the Python code to delete a record from the student table of education database
Ans.
import [Link]

cursor=[Link]()

[Link]( )
9. [Link] want to interface python with mysql and write some code help him to write the code
import_____________.connector #Line1
mydb=[Link]._______
#Line2
cursor=mydb.___________() #Line3
#Line4
data=cursor.__________() # Line 5 To retrieved all records
count=cursor.__________ #Line6 To count total rows
Ans: Line1:-mysql, Line2:-connect, Line3:cursor ,
Line4: execute, Line5: fetchall, Line6: rowcount
10. What is a cursor? Explain how to initiate the same.
ANS A cursor is an object which helps to execute the query and fetch the records from the database.
import [Link]
myconn = [Link] (host='localhost',user='root',password='root',database='XIICS' )
mycursor = [Link]()
[Link]("MySQL Command")
[Link]()
[Link] are the difference between fetchone(),fetchmany(),fetchall()? Hint- Above given
131
 WORKSHEET INTERFACE PYTHON WITH MYSQL (Multiple Choice Questions-1Mark)

1 Identify the name of connector to establish bridge between Python and MySQL
a. [Link]
b. connector
c. [Link]
d. [Link]
Ans [Link]

2 Which function of connection is used to check whether connection to mysql is

successfully done or not?
Import [Link] as msq
con = [Link]( #Connection String ) # Assuming all parameter required as
passed
if :

else:

a. [Link]()
b. [Link]()
c. con.is_connected()
d. con.is_connect()
Ans c. con.is_connected()

3 Which of the following component act as a container to hold all the data returned
from the query and from there we can fetch data one at a time?
a. ResultSet
b. Cursor
c. Container
d. Table
Ans b. Cursor

4 Identify the correct statement to create cursor:

Import [Link] as msq
con = [Link]( #Connection String ) # Assuming all parameter required
as passed mycursor =
a. [Link]()
b. con.create_cursor()
c. con.open_cursor()
d. con.get_cursor()
Ans a) [Link]()

5 What is the difference in fetchall() and fetchone()?

Ans fetchall() function is used to fetch all the records from the cursor in the form of tuple.
fetchone() is used to fetch one record at a time. Subsequent fetchone() will fetch
next records. If no more records to fetch it return None.

132
6 Which attribute of cursor is used to get number of records stored in a cursor
(Assuming cursor name is mycursor)?
a. [Link]
b. mycursor.row_count
c. [Link]
d. [Link]
Ans d. [Link]

7 Which of the Symbols are used for passing parameterized query for execution to
cursor?
a. %
b. {}
c. $
d. Bothaandb
Ans d. Both a and b

8 Which function is used to fetch n number of records from cursor?

a. fetch()
b. fetchone()
c. fetchmany()
d. fetchall()
Ans c. fetchmany()

9 Which cursor function is used to send query to connection?

a. query()
b. execute()
c. run()
d. send()
Ans [Link]()

10 Consider the following Python code is written to access the record of CODE passed
to function:
Complete the missing statements:

def Search(eno):
#Assumebasic setup import, connection and cursor is created
query="select * from emp where empno= ".format(eno)
[Link](query)
results = mycursor.
print(results)
a. {} and fetchone()
b. fetchone() and {}
c. %s and fetchone()
d. %eno and fetchone()
Ans a. {} and fetchone()

133
 UNSLOVED QUESTIONS
(Multiple Choice Questions)
Worksheet 1
[Link] of the following is not a legal method for fetching records from database.
a)fetchone() b)fetchtwo() c)fetchall() d)fetchmany()
2
a)fetch() b)fetchone() c)fetchtuple d)none of these.
3
a)done() b)reflect() c)commit() d)final
[Link] run an sql query from within python you may use cursor.------------- method.
a)query() b)execute() c)commit() d)final()
5.
a)database object b)connection object c)fetch object d)query object
ANSWER KEY FOR WORKSHEET1 (MCQ)
1. b)fetchtwo() 2. b)fetchone() 3. c)commit() 4. b)execute() 5. b)connection object

WORKSHEET 2
[Link] following table represents information on sales representatives of ABC company with the following
data.
Sales man name
Code
Address
commission
salary.
Write Python code to create the above table.
Q2. Write Python mysql connectivity program to retrieve all the data from a table student.
Q3. Consider the following python code for updating the records.
import [Link]
mydb=[Link](host="localhost",user="root",passwd="system",database="student")
mycursor=[Link]()
[Link]("UPDATE STUDENT SET MARKS=95 WHERE MARKS=50")
print([Link],"RECORD UPDATED")
Code is running but the record in actual database is not updating, what could be the possible reason?
Q4. Write a python connectivity program to retrieve data, one record at a time from EMP table for
employees with id<10.
Q5. Write python connectivity program to delete the employee record whose name is read from the
keyboard at execution time.

ANSWER KEY FOR WORKSHEET 2

1. mycursor=[Link]()
[Link]("CREATE TABLE SALESMAN (NAME VARCHAR(20), CODE INT ,
ADDRESS VARCHAR(20), COMMISSION DEC, SALARY FLOAT);")

2. import [Link]
134
 conn=[Link](host="localhost",user="root",passwd="syste
m",database="DB")
c=[Link]()
[Link]("select * from student")
r=[Link]()
while r is not None:
print(r)
r=[Link]()
[Link]()

3. [Link]() function is missing in the python code.

4. import [Link]
conn=[Link](host="localhost",user="root",passwd="system",database="com")
c=[Link]()
[Link]("select * from emp where id>10")
r=[Link]()
while r is not None:
print(r)
r=[Link]()
[Link]()

5. import [Link]
mydb=[Link](host="localhost",user="root",passwd="system",database="DB")
mycursor=[Link]()

[Link]( )

SOLVED SAMPLE PAPER -1

COMPUTER SCIENCES (083)
TIME: 3 HOURS M.M.70

135