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

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

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( )

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

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( )

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

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

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

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 uses the Database?
Answer:
Users of database can be human users, other programs or applications.

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

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.

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

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.

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

Question 5.
Which method is used to fetch all rows from the database table?
Answer:
Displaying all records using fetchall( )
The fetchall( ) method is used to fetch all rows from the database table
result = cursor.fetchall( )

PART – III
III. Answer The Following Questions

Question 1.
What is SQLite?What is it 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.

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

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’)]

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

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’,)

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

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’)

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

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. It 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 sqliteS
Step 2 create a connection using connect ( ) method and pass the name of the database File
Step 3 Set the cursor object cursor = connection.cursor( )

  1. 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.
  2. Cursor in step 3: is a control structure used to traverse and fetch the records of the database.
  3. 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 sql comm and can be executed one after other. Any changes made in the values of the record should be saved by the command “Commit” before closing the “Table connection”.

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

Question 2.
Write the Python script to display all the records of the following table using fetchmany( )
Answer:
Samacheer Kalvi 12th Computer Science Solutions Chapter 15 Data Manipulation Through SQL
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)

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

Question 3.
What is the use of HAVING clause. Give an example python script?
Answer:
Having clause is used to filter data based on the group functions. This is similar to WHERE condition but can be used only with group functions. 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. cursor( )
cursor.execute(“SELECT GENDER,COUNT(GENDER) FROM Student GROUP BY GENDER HAVING COUNT(GENDER)>3 “)
result = cursor. fetchall( )
co= [i[0] for i in cursor, description]
print(co)
print( result)
OUTPUT
[‘gender’, ‘COUNT(GENDER)’]
[(‘M’, 5)]

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

Question 4.
Write a Python script to create a table called ITEM with following specification?
Add one record to the table.
Name of the database:- ABC
Name of the table:- Item
Column name and speculation:-
Samacheer Kalvi 12th Computer Science Solutions Chapter 15 Data Manipulation Through SQL
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

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

Question 5.
Consider the following table Supplier and item .Write a python script for (i) to (ii)
Answer:
Samacheer Kalvi 12th Computer Science Solutions Chapter 15 Data Manipulation Through SQL

  1. Display Name, City and Itemname 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 user and store it in a csv file using Python for the following table?
Answer:
Database name;- DB1
Table name : Customer
Samacheer Kalvi 12th Computer Science Solutions Chapter 15 Data Manipulation Through SQL
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’)

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

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
Samacheer Kalvi 12th Computer Science Solutions Chapter 15 Data Manipulation Through SQL

  1. To display the name of all Games with their Geodes in descending order of their schedule date.
  2. To display details of those games which are having Prize Money more than 7000.
  3. To display the name and gamename of the Players in the ascending order of Gamename.
  4. To display sum of PrizeMoney for each of the Numberof 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.
Which has a native library for sqlite?
(a) C
(b) C++
(c) Java
(d) Python
Answer:
(d) Python

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

Question 3.
Which method is used to create a connection with 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

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

Question 5.
How many commands can be stored in the sql_comm?
(a) 1
(b) 2
(c) 3
(d) Many
Answer:
(d) Many

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

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

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.
What will be the value assigned to the empty table if it is given Integer Primary Key?
(a) 0
(b) 1
(c) 2
(d) -1
Answer:
(b) 1

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

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

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

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 returns the next row of a query result set?
(a) Fetch ne( )
(b) fetch all( )
(c) fetch next( )
(d) fetch last( )
Answer:
(a) Fetch ne( )

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

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( )

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

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

Question 17.
Which one of the following is used to print all elements separated by space?
(a) ,
(b) .
(c) :
(d) ;
Answer:
(a) ,

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

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

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

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 record for each group?
(a) Select
(b) group in
(c) group with
(d) group by
Answer:
(d) group by

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

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

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

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 asscending or descending order.
(a) Group by
(b) order by
(c) group with
(d) order with
Answer:
(b) order by

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

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

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

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 filter records based on more than one condition?
(a) AND
(b) NOT
(c) OR
(d) a & c
Answer:
(d) a & c

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

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

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

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( )

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

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

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

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) /, \\

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

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

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

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 note on “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 list of lists.

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

Question 2.
Mention the frequently used clauses in SQL?
Answer:

  1. DISTINCT
  2. WHERE
  3. GROUP BY
  4. ORDER BY
  5. HAVING

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

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

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.

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

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.

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

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’)

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

Question 4.
Write a program to count the number of male and female students from the student table
Example
Answer:
import sqlite3
connection= sqlite3.connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“SELECT gender,count(gender) FROM student Group BY gender”)
result = cursor. fetchall( )
print(*result,sep=”\n”)
OUTPUT
(‘F’, 2)
(‘M’, 5)

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,) ]

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

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’)

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

Question 2.
Write python program to accept 5 students names, their ages and ids during run time and display all the records from the table?
Answer:
In this example we are going to accept data using Python input() command during runtime and then going to write in the Table called “Person”
Example
# code for executing query using input data
import sqlite3
#creates a database in RAM
con =sqlite3.connect(“Academy,db”)
cur =con.cursor( )
cur.execute(“DROP Table person”)
cur.execute(“create table person (name, age, id)”)
print(“Enter 5 students names:”)
who =[input( ) for i in range(5)]
print(“Enter their ages respectively:”)
age =[int(input()) for i in range(5)]
print(“Enter their ids respectively:”)
p_d =[int(input( ))for i in range(5)]
n =len(who)
for i in range(n):
#This is the q-mark style:
cur.execute(“insert into person values(?,?,?)”, (who[i], age[i], p_id[i]))
#And this is the named style:
cur.execute(“select *from person”)
#Fetches all entries from table
print(“Displaying All the Records From Person Table”)
print (*cur.fetchall(), sep=’\n’)
OUTPUT
Enter 5 students names:
RAM
KEERTHANA
KRISHNA
HARISH
GIRISH
Enter their ages respectively:
28
12
21
18
16
Enter their ids respectively:
1
2
3
4
5
Displaying All the Records From Person Table
(‘RAM’, 28, 1)
(‘KEERTHANA’, 12, 2)
(‘KRISHNA’, 21, 3)
(‘HARISH’, 18,4)
(‘GIRISH’, 16, 5)

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

Question 3.
Considers the following tables appointments and students and write the python script to display the following output?
Samacheer Kalvi 12th Computer Science Solutions Chapter 15 Data Manipulation Through SQLAnswer:
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)

Leave a Comment

Your email address will not be published. Required fields are marked *