Samacheer Kalvi 12th Computer Science Solutions Chapter 15 Data Manipulation Through SQL

Students can Download Computer Science Chapter 15 Data Manipulation Through SQL Questions and Answers, Notes Pdf, Samacheer Kalvi 12th Computer Science Book Solutions Guide Pdf helps you to revise the complete Tamilnadu State Board New Syllabus and score more marks in your examinations.

Tamilnadu Samacheer Kalvi 12th Computer Science Solutions Chapter 15 Data Manipulation Through SQL

Samacheer Kalvi 12th Computer Science Data Manipulation Through SQL Text Book Back Questions and Answers

PART – 1
I. Choose The Correct Answer

Question 1.
Which of the following is an organized collection of data?
(a) Database
(b) DBMS
(c) Information
(d) Records
Answer:
(a) Database

Question 2.
SQLite falls under which database system?
(a) Flat file database system
(b) Relational Database system
(c) Hierarchical database system
(d) Object oriented Database system
Answer:
(b) Relational Database system

Question 3.
Which of the following is a control structure used to traverse and fetch the records of the database?
(a) Pointer
(b) Key
(c) Cursor
(d) Insertion point
Answer:
(c) Cursor

Question 4.
Any changes made in the values of the record should be saved by the command
(a) Save
(b) Save As
(c) Commit
(d) Oblige
Answer:
(c) Commit

Question 5.
Which of the following executes the SQL command to perform some action?
(a) Execute( )
(b) Key( )
(c) Cursor( )
(d) run( )
Answer:
(a) Execute( )

Question 6.
Which of the following function retrieves the average of a selected column of rows in a table?
(a) Add( )
(b) SUM( )
(c) AVG( )
(d) AVERAGE( )
Answer:
(c) AVG( )

Question 7.
The function that returns the largest value of the selected column is ……………………………
(a) MAX( )
(b) LARGE( )
(c) HIGH( )
(d) MAXIMUM( )
Answer:
(a) MAX( )

Question 8.
Which of the following is called the master table?
(a) sqlite master
(b) sqlmaster
(c) main_master
(d) master_main
Answer:
(a) sqlite master

Question 9.
The most commonly used statement in SQL is
(a) cursor
(b) select
(c) execute
(d) commit
Answer:
(b) select

Question 10.
Which of the following clause avoid the duplicate?
(a) Distinct
(b) Remove
(c) Where
(d) GroupBy
Answer:
(a) Distinct

PART – II
II. Answer The Following Questions

Question 1.
Mention the users who use the Database?
Answer:
Users of databases can be human users, other programs or applications.

Question 2.
Which method is used to connect a database? Give an example?
Answer:
Step 1: create a connection using connect () method and pass the name of the database file
Connecting to a database in step 2 means passing the name of the database to be accessed. If the database already exists the connection will open the same. Otherwise, Python will open a new database file with the specified name.
# connecting to the database
connection = sqlite3.connect (“Academy.db”)

Question 3.
What is the advantage of declaring a column as “INTEGER PRIMARY KEY”?
Answer:

• If a column of a table is declared to be an INTEGER PRIMARY KEY, then whenever a NULL will be used as an input for this column, the NULL will be automatically converted into an integer which will one larger than the highest value so far used in that column.
• If the table is empty, the value 1 will be used.

Question 4.
Write the command to populate record in a table. Give an example?
Answer:
To populate (add record) the table “INSERT” command is passed to SQLite. “execute” method executes the SQL command to perform some action. In most cases, you will not literally insert data into a SQL table. You will rather have a lot of data inside of some Python data type e.g. a dictionary or a list, which has to be used as the input of the insert statement.

Question 5.
Which method is used to fetch all rows from the database table?
Answer:
The fetchall() method is used to fetch all rows from the database table.
Example:
import sqlite3
connection = sqlite3.
connect(” Academy. db”)
cursor = connection.cursor()
cursor.execute(“SELECT * FROM student”)
print(“Fetchall method:”)
result = cursor.fetchall()
for r in result:
print(r)
OUTPUT:
Fetchall method:
(1, ‘Akshay’, ‘B7, ‘M’, 87.8, ‘2001-12-12’)
(2, ‘Aravind’, ‘A’, ‘M’, 92.5, ‘2000-08-17’)
(3, ‘BASKAR’, ‘C’, ‘M’, 75.2, ‘1998-05-17’)
(4, ‘SAJINT, ‘A’, ‘F’, 95.6, ‘2002-11-01’)
(5, ‘VARUN’, ‘B’, ‘M’, 80.6, ‘2001-03-14’)
(6, ‘PRIYA’, ‘A’, ‘F’, 98.6, ‘2002-01-01’)
(7, ‘TARUN’, ‘D’, ‘M’, 62.3, ‘1999-02-01’)

PART – III
III. Answer The Following Questions

Question 1.
What is SQLite?What is its advantage?
Answer:

1. SQLite is a simple relational database system, which saves its data in regular data files or even in the internal memory of the computer.
2. It is designed to be embedded in applications, instead of using a separate database server program such as MySQLor Oracle.
3. SQLite is fast, rigorously tested, and flexible, making it easier to work. Python has a native library for SQLite.

Question 2.
Mention the difference between fetchone( ) and fetchmany( )
Displaying A record using fetchone( )
Answer:
The fetchone( ) method returns the next row of a query result set or None in case there is no row left.
Example
import sqlite3
connection= sqlite3 ,connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“SELECT * FROM student”)
print(“\nfetch one:”)
res = cursor. fetchone( )
print(res)
OUTPUT
fetch one:
(1, ‘Akshay’, ’B’, ’M’, 87.8, ‘2001-12-12’)
Displayingusing fetchmany( )
Displaying specified number of records is done by using fetchmany( ). This method returns the next number of rows (n) of the result set.
Example : Program to display the content of tuples using fetchmany( )
import sqlite3
connection = sqlite3.connect(” Academy, db”)
cursor = connection. cursor( )
cursor.execute(“SELECT * FROM student”)
print(“fetching first 3 records:”)
result= cursor. fetchmany(3)
print( result)
OUTPUT
fetching first 3 records:
[ (1, ‘Akshay’, ’B’, ‘M’, 87.8, ‘2001-12-12’), (2, ‘Aravind’, ‘A’, ’M’, 92.5, ‘2000-08-17’), (3, ‘BASKAR’, ‘C, TVT, 75.2, ‘1998-05-17’)]

Question 3.
What is the use of Where Clause.Give a python statement Using the where clause?
Answer:
The WHERE clause is used to extract only .those records that fulfill a specified condition. In this example we are going to display the different grades scored by male students from “student table” import sqlite3
connection = sqlite3.connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“SELECT DISTINCT (Grade) FROM student where gender=’M'”)
result = cursor. fetchall( )
print(*result,sep=”\n”)
OUTPUT
(‘B’,)
(‘A’,)
(‘C’,)
(‘D’,)

Question 4.
Read the following details.Based on that write a python script to display department wise records
database name:- organization.db
Table name:- Employee
Columns in the table:- Eno, EmpName, Esal, Dept
Display departmentwise records: import sqlite3
conn = sqlite3.connect(“organization.db”)
c = conn.execute(“select * from Employee group by dept”)
for row in c:
print(row)
conn.close( )
Output:
(1000, ‘Ram’, 5000, ’Mech’)
(2000, ‘Hari’, 6500, ‘Mech’)
(1127, ’Vijay’, 10,000, ’cs )
(1130,’Raja’, 11,000,’cs’)

Question 5.
Read the following details.Based on that write a python script to display records in
desending order of
Eno
database name:- organization.db
Table name:- Employee
Columns in the table:- Eno, EmpName, Esal, Dept
import sqlite3
connection = sqlite3.connect(“organizatoin.db”)
cursor = connection. cursor( )
cursor.execute(“select * from Employee order by Eno Desc”)
result = cursor. fetchall( )
print(result).
Output:
(2000, ‘Hari’, 6500, ‘Mech’)
(1130, ‘Raja’, 11,000, ‘cs’)
(1127, ‘Vijay’, 10,000, ‘cs’)
(1000, ‘Ram’, 5000, ‘Mech’)

PART – IV
Answer The Following Questions

Question 1.
Write in brief about SQLite and the steps used to use it?
Answer:

• SQLite is a simple relational database system, which saves its data in regular data files or even in the internal memory of the computer.
• SQLite is designed to be embedded in applications, instead of using a separate database server program such as MySQLor Oracle.
• SQLite is fast, rigorously tested, and flexible, making it easier to work.
• Python has a native library for SQLite.

To use SQLite,
Step 1: import sqlite3
Step 2 : create a connection using connect o method and pass the name of the database file
Step 3 : Set the cursor object cursor = connection, cursor 0

• Connecting to a database in step2 means passing the name of the database to be accessed. If the database already exists the connection will open the same. Otherwise, Python will open a new database file with the specified name.
• Cursor in step 3 is a control structure used to traverse and fetch the records of the database.
• Cursor has a major role in working with Python. All the commands will be executed using cursor object only.
• To create a table in the database, create, an object and write the SQL command in it. Example: sql_comm = “SQL statement”
• For executing the command use the cursor method and pass the required sql command as a parameter. Many number of commands can be stored in the sqlcomm and can be executed one after other.
• Any changes made in the values of the record should be saved by the commend “Commit” before closing the “Table connection”.

Question 2.
Write the Python script to display all the records of the following table using fetchmany( )
Answer:

import sqlite3
connectoin = sqlite3.connect(“company.db”) cursor = connection.cursor() cursor.execute(“Select * from product”) print(“Displaying Records”) result = cursor.fetchmany(5) print(*result, Sep = “\n”)
Output:
Displaying records (1003, ‘Scanner’, 10500)
(1004, ’Speaker’, 3000)
(1005, ‘Printer’, 8000)
(1008,’Monitor’, 15000)
(1010, ‘Mouse’, 700)

Question 3.
What is the use of the HAVING clause? Give an example python script?
Answer:

• HAVING clause is used to filter data based on the group functions.
• This is similar to the WHERE condition but can be used, only with group functions like COUNT (), AVG (), SUM (), MAX (), MIN ().

Group functions cannot be used in
WHERE Clause but can be used in
HAVING clause.

Example:
import sqlite3
connection = sqlite3. connect
(” Academy, db”)
cursor = connection.cursorO
cursor execute(“SELECTGENDER, COUNT (GENDER) FROM Student GROUP BY GENDER HAVING COUNT( GENDER > 3”)
result = cursor.fetchall()
co = [i[o] for i in cursor, description]
print(co)
print(result)
Output:
[“gender’, ‘COUNT(GENDER)’] [(M’, 5)]

Question 4.
Write a Python script to create a table called ITEM with the following specification?
Add one record to the table.
Name of the database:- ABC
Name of the table:- Item
Column name and speculation:-

import sqlite 3
connection = sqlite3.connect(“ABC.db”)
cursor = connectoin.cursor( )
sql_command -“””CREATE TABLE Item
(
Icode INTEGER PRIMARY KEY,
ItemName VARCHAR(25), Rate INTEGER);”””
cursor.execute(sql_command)
sql_command=””” INSERT INTO ITEM(lcode, ItemName, Rate) VALUES(1008, ‘Monitor’, 15000);”””
cursor.execute(sql_command)
connection.commit( )
connection.close( )
print(“Table created”)
Output:
Table created

Question 5.
Consider the following table Supplier and item. Write a python script for (i) to (ii)
Answer:

1. Display Name, City, and Item name of suppliers who do not reside in Delhi.
2. Increment the SuppQty of Akila by 40

Answer:
1. import sqlite3
connection = sqlite3.connect(“ABC.db”)
cursor = connection,cursor( )
cursor.execute(“SELECT Supplier.Name, Supplier.city, Item.ItemName FROM Supplier, Item
where Supplier.Icode = Item.Icode AND Supplier.city NOT IN ‘Delhi'”)
co=[i[0] for i in cursor.description]
print(co)
result = cursor. fetchall( )
for r in result:
print(r).
Output:
[‘Name’, ‘City’, ‘ItemName’]
[‘Anu’, ‘Bangalore’, ‘Mouse’]
[‘Shahid’, ‘Bangalore’, ‘Monitor’]
[‘Akila’, ‘Hydrabad’, ‘Printer’]
[‘Girish’, ‘Hydrabad’, ‘Scanner’]
[‘Shylaja’, ‘Chennai’, ‘Monitor’]
[‘Lavanya’, ‘Mumbai’, ‘Printer’]

2. Increment the suppQty of Akila by 40 import sqlite3
connection = sqlite3.connect(“ABC.db”)
cursor = connection.cursor( )
cursor.execute(“UPDATE Supplier SET SuppQty = SuppQty + 40 where Name = ‘Akila'”)
cursor.commit( )
result = cursor. fetchall( )
print(result)
connection.close( )
Output:
(S004, ‘Akila’, ‘Hydrabad’, 1005, 235)

Practice Programs

Question 1.
Create an interactive program to accept the details from the user and store it in a csv file using Python for the following table?
Answer:
Database name;- DB1
Table name : Customer

import sqlite3 import io import csv
d = open(‘c:/pyprg/sql.csv’, ‘w’) c = csv.writer(d)
connection = sqlite3.connect(“dbl.db”)
cursor = connection.cursor( )
cursor.execute(“create table customer(cust_Id, cust_Name, Address, Phone_no, City)”)
print(“Enter 3 customer details:”)
print(“Enter 3 customer Id :”)
cid = [int(input( )) for i in range (3)]
print(“Enter customer names :”)
cname = [input( ) for i in range (3)]
print(“Enter their Address:”)
add = [input( ) for i in range (3)]
int(“Enter their phone numbers:”)
ph = [int(input( )) for i in range (3)]
print(“Enter their cities:”)
city = [input( ) for i in range (3)]
n = len(cname)
for i in range (n):
cursor.execute(“insert into customer values (?,?,?,?,?)”, (cid[i], cname[i], add[i], ph[i], city[i]))
cursor.execute(“Select * from customer “)
co = [i[0] for i in cursor, description]
c.writerow(co)
data = cursor. fetchall( )
for item in data:
c.writerow(item)
– d.close( )
with open(‘c:/pyprg/sql.csv’, “r”, newline = None) as fd:
for line in fd:
line = line.replace(“\n”,” “)
print(line)
cursor. close( )
connection. close( )
OUTPUT:
Enter 3 customer details:
Enter 3 customer Id:
C008
C010
C012
Enter customer names:
Sandeep
Anurag Basu
Hrithik
Enter their Address:
14/1 Pritam Pura
15/A, Park Road
7/2 Vasant Nagar
Enter their Phone Numbers:
41206819
61281921
26121949
Enter their cities:
Delhi
Kolkata
Delhi
Displaying Data:
(‘cust_Id’, ‘cust_Name’, ‘Address’, ‘Phone_no’, ‘city’)
(C008, ‘Sandeep’, ’14/1 Pritampura’, ‘41206819’, ’Delhi’)
(C010, ‘Anurag Basu’, ’15A, Park Road’, ’61281921’, ’Kolkata’)
(C012, ’Hrithik’, ’7/2 Vasant Nagar’, ’26121949’, ’Delhi’)

Question 2.
Consider the following table GAMES. Write a python program to display the records for question (i) to (iv) and give outputs for SQL queries (v) to (viii)?
Table: GAMES

1. To display the name of all Games with their Geodes in descending order of their scheduled date.
2. To display details of those games which are having Prize Money more than 7000.
3. To display the name and game name of the Players in the ascending order of the Game name.
4. To display sum of PrizeMoney for each of the Number of participation groupings (as shown in column Number 4)
5. Display all the records based on GameName

1. To display the name of all Games with their Geodes in descending order of their schedule date.
import sqlite3
conn = sqlite3.connect(“Games.db”)
cursor = conn.cursor( )
cursor.execute(“Select GameName, Geode from Games order by ScheduleDate Desc “)
result = cursor,fetchall( )
print(*result, sep = “\n”)
conn.close( )
Output:
(’Table Tennis’, 108)
(’Table Tennis’, 103)
(’Carom Board’, 101)
(’Carom Board’, 105)
(’Badminton1, 102)

2. To display details of those games which are having Prize Money more than 7000.
import sqlite3
conn = sqlit3.connect(“Games.db”)
cursor = conn.cursor( )
cursor.execute(” Select * from Games where prize money > 7000″)
result = cursor.fetchall( )
print(*result, sep = “\n”)
conn.close( )
Output:
(102, ‘Vidhya’, ‘Badminton’, 2, 12000, ’12-12-2013′)
(103, ‘Guru’, ’Table Tennis’, 4, 8000, ’02-14-2014’)
(105, ‘Keerthana’, ‘Carom Board’, 2, 9000, ’01-01-2014′)
(108, ’Krishna’, ’Table Tennis’, 4, 25000, ’03-19-2014′)

3. To display the name and gamename of the Players in the ascending order of Gamename.
import sqlite3
conn = sqlite3.connect(“Games.db”)
cursor = conn.cursor( )
cursor.execute(” Select Name, GameName from games order by GameName “)
result = cursor.fetchall( )
print(*result, sep = “\n”)
conn.close( )
Output:
(‘Vidhya’, ‘Badminton’)
(‘Padmaja’, ‘Carom Board’)
(‘Keerthana’, ‘Carom Board’)
(‘Guru’, ‘Table Tennis’)
(‘Krishna’, ‘Table Tennis’)

4. To display sum of PrizeMoney for each of the Numberof participation groupings (as shown in column Number 4)
import sqlite3
conn = sqlite3.connect(“Games.db”)
cursor = conn.cursor( )
cursor.execute(“Select Sum(Number * Prizemoney) from games”)
result = cursor, fetchall( )
print(result)
conn.close( )
Output:
[(184000)]

5. Display all the records based on GameName
import sqlite3
conn = sqlite3.connect(“Games.db”)
cursor = conn.cursor( )
cursor.execute(“Select * from games group by gamename”)
result = cursor. fetchall( )
print(*result, sep = “\n”)
conn.close( )
Output:
(‘Carom Board’, 101, ‘Padmaja’, 2, 5000, ’01-23-2014′)
(’Carom Board’, 105, ‘Keerthana’, 2, 9000, ’01-01-2014′)
(‘Badminton’, 102, ‘Vidhya’, 2, 12000, ’12-12-2013′)
(‘Table Tennis’, 103, ‘Guru’, 4, 8000, ’02-14-2014′)
(’Table Tennis’, 108, ‘Krishna’, 4, 25000, ’03-19-2014′)

Samacheer Kalvi 12th Computer Science Data Manipulation Through SQL Additional Questions and Answers

PART – 1
I. Choose The Correct Answer

Question 1.
The ………………………….. is a software application for the interaction between users and the databases.
Answer:
Database Management System

Question 2.
………………….. statement in SQL is used to retrieve or fetch data from a table in a database.
a) select
b) inset
c) create
d) fetch
Answer:
a) select

Question 3.
Which method is used to create a connection with the database file?
(a) connect( )
(b) pass( )
(c) link( )
(d) create( )
Answer:
(a) connect( )

Question 4.
All the commands will be executed using ……………………………. object only.
Answer:
cursor

Question 5.
…………………..keyword is used to fetch only the unique value.
a) UNIQUE
b) DISTINCT
c) GROUPBY
d) HAVING
Answer:
b) DISTINCT

Question 6.
What is the extension for database files?
(a) dot
(b) database
(c) .db
(d) .dot
Answer:
(c) .db

Question 7.
The SQL commands have to be defined with ……………………………. quotes.
(a) single
(b) double
(c) triple
(d) No quotes
Answer:
(c) triple

Question 8.
A column which is labelled like ………………………………. is automatically auto-incremented in sqlite3.
Answer:
Integer primary key

Question 9.
…………………..command updates some or all data values in a database.
a) MODIFY
b) SELECT
c) UPDATE
d) CHANGE
Answer:
c) UPDATE

Question 10.
Which command is used to populate the records in the table?
(a) populate
(b) create
(c) pop
(d) Insert
Answer:
(d) Insert

Question 11.
Which command in SQL is used to retrieve or fetch data from a table in the database?
(a) Select
(b) Fetch
(c) retrieve
(d) create
Answer:
(a) Select

Question 12.
Which method is used to fetch all rows from the database table?
(a) Fetch( )
(b) fetchall( )
(c) printall( )
(d) retrieveall( )
Answer:
(b) fetchall( )

Question 13.
Which method run the SQL command to perform some action.
a) run
b) select
c) execution
d) execute
Answer:
d) execute

Question 14.
What will be the result of fetchone( ) method if no row is left?
(a) 1
(b) 2
(c) 3
(d) none
Answer:
(d) none

Question 15.
Which one of the following methods displays the specified number of records?
(a) fetchone( )
(b) fetchmany( )
(c) fetchall( )
(d) fetchsome( )
Answer:
(b) fetchmany( )

Question 16.
Which one of the following is a newline character?
(a) \n
(b) \r
(c) \t
(d) \nl
Answer:
(a) \n

Question 17.
The path of a file can be either represented as…………… in Python.
a) /or\\
b) \\ or /
c) \ or ?
d) // or ?
Answer:
a) /or\\

Question 18.
SQL provides various clauses that can be used in the …………………………….. statements.
Answer:
SELECT

Question 19.
The clauses in SQL can be called through
(a) C
(b) C++
(c) Python script
(d) DOS
Answer:
(c) Python script

Question 20.
In SQL, the …………………………. clause is used to extract only those records that fulfill a specified condition.
(a) why
(b) what
(c) where
(d) how
Answer:
(c) where

Question 21.
Which clause returns are recorded for each group?
(a) Select
(b) group in
(c) group with
(d) group by
Answer:
(d) group by

Question 22.
Identify the statement which is wrong?
(a) Group by clause is used with aggregate functions
(b) group by clause groups records into summary rows
(c) group by clause is used to filter data
Answer:
(c) group by clause is used to filter data

Question 23.
Pick the odd one out.
(i) count, max, min, OR, SUM
(ii) AND, OR, MAX, NOT
(iii) COUNT, NULL, AVG, SUM
Answer:
(i) OR, (ii) MAX, (iii) NULL

Question 24.
How many types of sorting are there?
(a) 2
(b) 3
(c) 4
(d) 5
Answer:
(a) 2

Question 25.
Which command is used to display the records in ascending or descending order?
(a) Group by
(b) order by
(c) group with
(d) order with
Answer:
(b) order by

Question 26.
Identify the Incorrect pair
(a) Group by – 1. Aggregate functions
(b) order by – 2. Sorting data
(c) Having – 3. filter data
(d) where – 4. Max, min
(a) 2
(b) (1)
(c) (4)
(d) (3)
Answer:
(c) d

Question 27.
Find the Incorrect statement?
(a) The WHERE clause can be combined with AND, OR, NOT
(b) Having clause is used to filter data based on the group function
(c) WHERE cannot be used with NOT
Answer:
(c) WHERE cannot be used with NOT

Question 28.
Which operators are used to filtering records based on more than one condition?
(a) AND
(b) NOT
(c) OR
(d) a & c
Answer:
(d) a & c

Question 29.
How many values are returned from the aggregate functions?
(a) 1
(b) 2
(c) 3
(d) 4
Answer:
(a) 1

Question 30.
Find the correct answer.
(i) count functions returns the number of rows in a table satisfying the criteria
(ii) count returns 0 if there were no matching rows
(iii) Null values are counted
(a) (i), (ii) – True
(b) (ii), (iii) – true
(c) (i), (ii), (iii) – True
(d) (i),.(ii), (iii) – False
Answer:
(a) (i), (ii) – True

Question 31.
Find the correct statement.
(a) A record can be deleted using SQL command
(b) A record can be deleted with python
(c) both are true
Answer:
(c) both are true

Question 32.
Which command is used to accpet data during run time in python?
(a) Insert( )
(b) input( )
(c) create( )
(d) update( )
Answer:
(b) input( )

Question 33.
The two kinds Of placeholders in sqlite3 module are ………………………….. and ………………………….
Answer:
qmarkstyle, namedstyle

Question 34.
…………………………… is used to display the field names of the table
Answer:
cursor description

Question 35.
In cursor description o_index refers to the column name
Answer:
O

Question 36.
A) Query result can be stored in csv file.
R) To display the query output in a tabular form
(a) R is the reason for A
(b) R is wrong
(c) A is wrong
(d) both are not related
Answer:
(a) R is the reason for A

Question 37.
The path of a python file can be represented as ……………………….. and ………………………………
(a) /, //
(b) \, \\
(c) /, \\
(d) \, //
Answer:
(c) /, \\

Question 38.
Which table holds the key information about the database tables?
(a) page
(b) select
(c) primary
(d) Master
Answer:
(d) Master

Question 39.
………………………. functions are used to do operations from the values of the column and a single value is returned.
Answer:
Aggregate

Question 40.
Which function returns the smallest value of the selected columns?
(a) MIN( )
(b) MINIMUM( )
(c) SMALL( )
(d) LEAST( )
Answer:
(a) MIN( )

PART – II
II. Answer The Following Questions

Question 1.
Write a note on the “Select” command?
Answer:
“Select” is the most commonly used statement in SQL. The SELECT Statement in SQL is used to retrieve or fetch data from a table in a database. The syntax for using this statement is “Select*from table_name” and all the table data can be fetched in an object in the form of a list of lists.

Question 2.
Define cursor.
Answer:

• A cursor in SQL and databases is a control structure to traverse over the records in a database. So it’s used for the fetching of the results.
• The cursor object is created by calling the cursor() method of connection.
• The cursor is used to traverse the records from the result set.

Question 3.
What is the use of the distinct clause in SQL?
Answer:
The distinct clause is helpful when there is a need of avoiding the duplicate values present in any specific columns/table. When we use a distinct keyword only the unique values are fetched.

Question 4.
List some aggregate functions in SQL?
Answer:

1. COUNTO
2. SUM( )
3. MIN( )
4. AVG( )
5. MAX( )

PART – III
III. Answer The Following Questions

Question 1.
What is meant by cursor? How is it created?
Answer:
A cursor in SQL and databases is a control structure to traverse over the records in a database. So it’s used for the fetching of the results.
Cursor is used for performing all SQL commands.
The cursor object is created by calling the cursor( ) method of connection. The cursor is used to traverse the records from the result set.

Question 2.
Write a short note on fetchall( ), fetchone( ) and fetchmany( ) commands?
Answer:
cursor. fetchall( ) -fetchall ( )method is to fetch all rows from the database table
cursor. fetchone( ) – The fetchone ( ) method returns the next row of a query result set or None in case there is no row left.
cursor.fetchmany( ) method that returns the next number of rows (n) of the result set.

Question 3.
How will you use order by clause in SQL. Explain with sample program?
Answer:
The ORDER BY Clause can be used along with the SELECT statement to sort the data of specific fields in an ordered way. It is used to sort the result-set in ascending or descending order. In this example name and Rollno of the students are displayed in alphabetical order of names.
Example
import sqlite3
connection= sqlite3.connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“SELECT Rollno,sname FROM student Order BY sname”)
result = cursor.fetchall( )
print(*result,sep=” \n”)
OUTPUT
(1, ‘Akshay’)
(2, ‘Aravind’)
(3, ‘BASKAR’)
(6, ‘PRIYA’)
(4, ‘SAJINI’)
(7, ‘TARUN’)
(5, ‘VARUN’)

Question 4.
What is the use of the SELECT statement?
Answer:

• “SELECT” is the most commonly used statement in SQL.
• The SELECT Statement in SQL is used to retrieve or fetch data from a table in a database.
• The syntax for using this statement is “Select * from table_name” and all the table data can be fetched in an object in the form of a list of lists.

Question 5.
Write a program to count the number of records in a table?
Example 1 :In this example we are going to count the number of records( rows)
import sqlite3
connection = sqlite3.connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“SELECT COUNT(*) FROM student”)
result = cursor. fetchall( )
print( result)
Output:
[(7,) ]

Question 6.
Write a program to display list of tables created in a database?
Example
Answer:
import sqlite3
con= sqlite3 .connect(‘Academy.db’)
cursor = con.cursor( )
cursor.execute(“SELECT name FROM sqlitemaster WHERE type-table’;”)
print(cursor. fetchall( ))
OUTPUT
[(‘Student’,), (‘Appointment’,), (‘Person’,)]

PART – IV
IV. Answer The Following Questions

Question 1.
Explain OR, AND and NOT operator in SQL?
Answer:
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. In this example you are going to display the details of students who have scored other than ‘A’, or ‘B’ from the “student table”
Example for WHERE WITH NOT Operator
Example
import sqlite3
connection= sqlite3.connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“SELECT *FROM student where grade< >’A’ and Grade< >’B'”)
result = cursor. fetchall( )
print(*result,sep=”\n”)
OUTPUT
(3, ‘BASKAR’, ’C, ’M’, 75.2, T998-05-17′)
(7, ‘TARUN’, ‘D’, ‘M’, 62.3, ‘1999-02-01’)
Example for WHERE WITH AND Operator
In this example we are going to display the name, Rollno and Average of students who have scored an average between 80 to 90% (both limits are inclusive)
Example
import sqlite3
connection = sqlite3.connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“SELECT Rollno, Same, Average FROM student WHERE (Average>=80 AND Average<=90)”)
result = cursor.fetchall( )
print(*result,sep=”\n”)
OUTPUT
(1, ‘Akshay’, 87.8)
(5, ‘VARUN’, 80.6)
Example for WHERE WITH OR Operator
In this example we are going to display the name and Rollno of students who have not scored an average between 60 to 70%
Example import sqlite3
connection = sqlite3.connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“SELECT Rollno,sname FROM student WHERE (Average<60 OR Average> 70)”)
result = cursor. fetchall( )
print(*result,sep=”\n”)
OUTPUT
(1, ‘Akshay’)
(2, ‘Aravind’)
(3, ‘BASKAR’)
(4, ‘SAJINI’)
(5, ‘VARUN’)
(6, ‘PRIYA’)

Question 2.
Explain Aggregate Functions with suitable examples.
Answer:
Aggregate Functions:
Aggregate Functions are used to do operations from the values of the column and a single value is returned.
Commonly used Aggregate Functions are:

• COUNT()
• AVG()
• SUM()
• MAX()
• MIN()

COUNT() function:
The SQL COUNT() function returns the number of rows in a table satisfying the criteria specified in the WHERE clause. COUNT() returns () if there were no matching rows.

Example:
import sqlite3
connection = sqlite3.connect(” Academy.db”)
cursor = connection.cursor()
cursor.executeÇ’SELECT COUNT(*)
FROM student “)
result = cursor.fetchall()
print(result)
Output:
[(7,)]

AVG() function:
AVG() function retrieves the average of a selected column of rows in a table.

Example:
SQL statement in the python program
finds the average mark of all students:
import sqlite3
connection = sqlite3.cormect(”Academy.db”)
cursor = connection.cursor()
cursor.execute(“SELECT AVG(AVERAGE) FROM student “)
result = cursor.fetchall()
print(result
OUTPUT:
[(84.65714285714286,)]

SUMO function:
SUM ( ) function retrieves the sum of a selected column of rows in a table.

Example:
SQL statement in the python program
finds the sum of all average in the Average
field of “Student table”:

import sqlite3
connection = sqlite3.connect(”Academy. db”)
cursor = connection.cursor()
cursor.execute(” SELECT SUM(AVERAGE) FROM student”)
result = cursor.fetchall()
print (resu It)
OUTPUT:
[(592.6,)]

MAX() and MIN() functions:

• The MAXO function returns the largest value of the selected column.
• The MINO function returns the smallest value of the selected column.

Example:
Coding to show the highest and least
average student’s name:
import sqlite3
connection = sqlite3.
connect(“Organization.db”)
cursor = connection.cursor()
print(“Displaying the name of the Highest Average”)
cursor.execute(’ SELECT sname,max(AVERAGE) FROM student
result = cursor.fetchall()
print (result)
print(”Displaying the name of the Least Average”)
cursor.execute(” SELECT sname,min(AVERAGE) FROM student
result = cursor.fetchall()
print(result)
OUTPUT:
Displaying the name of the Highest
Average
((‘PRIYA’, 98.6)]
Displaying the name of the Least Average
[(‘TARUN’, 62.3)]

Question 3.
Considers the appointments and students of the following table and writes the python script to display the following output?

Answer:
Given:
Student → Table Rollno, Sname, grade, gender, average, birth-date
Appointment → rollno, duty, age
Example
import sqlite3
connection = sqlite3.connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“””DROP TABLE Appointment;”””)
sql_command = “””
CREATE TABLE Appointment(rollnointprimarkey,Dutyvarchar(10),age int)”””
cursor.execute(sqlcommand)
sql command – ‘””INSERT INTO Appointment (Rollno,Duty ,age)
VALUES (“1”, “Prefect”, “17”);”””
cursor, execute(sql_command)
sql_command =”””INSERT INTO Appointment (Rollno, Duty, age)
VALUES (“2”, “Secretary”, “16”);”””
cursor.execute(sql_command)
# never forget this, if you want the changes to be saved:
connection.commit( )
cursor.execute(“SELECT student.rollno,student.sname,Appointment.
Duty,Appointment.Age FROM student,Appointment where student.
rollno= Appointment.rollno”)
#print (cursor.description) to display the field names of the table
co= [i[0] for i in cursor.description]
print(co)
# Field informations can be readfrom cursor.description.
result = cursor. fetchall( )
for r in result:
print(r)
OUTPUT
[‘Rollno’, ‘Sname’, ‘Duty’, ‘age’]
(1, ‘Akshay’, ‘Prefect’, 17)
(2,’Aravind’,’Secretary’, 16)