12th Computer Science Monthly Test - 2 ( Data manipulation through SQL )

St. Britto Hr. Sec. School - Madurai

12th Computer Science Monthly Test - 2 ( Data manipulation through SQL )-Aug 2020

Time : 2.30 Hours

Part - A

Multiple Choice Question - Answer All Question

Which method has a major role in working with python?

cursor()
connect ()
execute()
close

______ has a native library of SQlite.

Python
C++
Java
C

cursor.description will be stored as a

list
set
tuple
dictionary

All the SQlite commands will be executed using _____ object.

connect
cursor
CSV
python

The WHERE clause cannot be combined with

In which class the group functions can be used?

WHERE
HAVING
DISTINCT
GROUP BY

_____ SQlite command contain the details of each table column headings

cursor.description
cursor.connect
cursor.column
cursor.fieldname

In python, the path of a file can be represented as _____

/ or \\
\\ or /
\ or ?
// or ?

Which of the following clause will not 'work in SQlite?

DISTINCT
HAVING
FETCHALL
WHERE

Which of the following is used define a SQL command in SQlite3?

" "
" " " "
' '
" " " " " "

Which of the following operator cannot be used to filter records based on more than one condition?

A table column will be automatically auto incremented in SQlite3 by giving

KEY
PRIMARY KEY
PRIMARYCOLUMN
KEYPRIMARY

Choose the incorrect statement from the following.

(i) Distinct keyword is used to fetch only unique values from the table.
(ii) HAVING Clause in used to extract only those records that fulfill a specified condition.
(iii) GROUP BY clause groups records into summary columns
(iv) Group functions cannot be used in WHERE clause.

i and ii
ii and iv
iii, iv and i
ii and iii

The SQlite command opens the already created database is

Cursor
Sql-comm
Connect
Connection

Which of the following is not an aggregate functions?

SUM
COUNT
MAX
POW

Part - B

Generic 2 - Answer All Question

Write the command to populate record in a table. Give an example.

What is master table?

Mention the users who uses the Database.

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,sep="\n")

import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM student")
print("fetching all records one by one:")
result = cursor.fetchone()
while result is not None:
print(result)
result = cursor.fetchone()

import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT DISTINCT (Grade) FROM student")
result = cursor.fetchall()
print(result)

import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
student_data = [("BASKAR", "C", "M","75.2","1998-05-17"),
("SAJINI", "A", "F","95.6","2002-11-01"),
("VARUN", "B", "M","80.6","2001-03-14"),
("PRIYA", "A", "F","98.6","2002-01-01"),
("TARUN", "D", "M","62.3","1999-02-01") ]
for p in student_data:
format_str = """INSERT INTO Student (Rollno, Sname, Grade, gender,Average, birth_date)
VALUES (NULL,"{name}", "{gr}", "{gender}","{avg}","{birthdate}");"""
sql_command = format_str.format(name=p[0], gr=p[1], gender=p[2],avg=p[3], birthdate = p[4])
cursor.execute(sql_command)
connection.commit()
connection.close()
print("RECORDS ADDED TO STUDENT TABLE ")
import sqlite3
connection = sqlite3.connect ("Academy.db")
cursor = connection.cursor()
# delete
cursor.execute ("""DROP TABLE Student;""")
sql_command = """
CREATE TABLE Student (
Rollno INTEGER PRIMARY KEY , Sname VARCHAR(20), Grade CHAR(1),
gender CHAR(1), Average DECIMAL (5, 2), birth_date DATE);"""
cursor.execute(sql_command)
sql_command = """INSERT INTO Student (Rollno, Sname, Grade, gender, Average, birth_date)
VALUES (NULL, "Akshay", "B", "M","87.8", "2001-12-12");""" cursor.execute(sql_command)
sql_command = """INSERT INTO Student (Rollno, Sname, Grade, gender, Average, birth_date)
VALUES (NULL, "Aravind", "A", "M","92.50","2000-08-17");""" cursor.execute(sql_command)
# never forget this, if you want the changes to be saved:
connection.commit()
connection.close()
print("STUDENT TABLE CREATED")

Part - C

Generic 3 - Answer All Question

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)

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

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

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

What is the use of Where Clause.Give a python statement Using the where clause.

What is the purpose of using

(i) COUNT()
(ii) AVG()
(iii) SUM()
(iv) MAX()
(v) MIN().

Explain how a connect to be made to a database (Academy through python SQlite3.

Part - D

Generic 5 - Answer All Question

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

SUPPLIER

Suppno Name City Icode SuppQty

S001 Prasad Delhi 1008 100

S002 Anu Bangalore 1010 200

S003 Shahid Bangalore 1008 175

S004 Akila Hydrabad 1005 195

S005 Girish Hydrabad 1003 25

S006 Shylaja Chennai 1008 180

S007 Lavanya Mumbai 1005 325

i) Display Name, City and Itemname of suppliers who do not reside in Delhi.
ii) Increment the SuppQty of Akila by 40

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 specification :-

Icode :- integer and act as primary key

Item Name :- Character with length 25

Rate :- Integer

Record to be added :- 1008, Monitor,15000

What is the use of HAVING clause. Give an example python script

In this example we are going to count the number of records by specifying a column
import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT COUNT(AVERAGE) FROM student ")
result = cursor.fetchall()
print(result)

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)
import sqlite3
import io # to access replace()
import csv
# CREATING CSV FILE
d=open('c:/pyprg/sql.csv','w')
c=csv.writer(d)
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
# a=Connection.cursor()
cursor.execute("SELECT * FROM student ORDER BY GENDER DESC,SNAME")
# WRITING THE COLUMN HEADING
co = [i[0] for i in cursor.description]
c.writerow(co)
data=cursor.fetchall()
for item in data:
c.writerow(item)
d.close()
# Reading the CSV File
# replace() is used to eliminate the newline character at the end of each row
with open('c:/pyprg/sql.csv', "r", newline=None) as fd:
# r = csv.reader(fd)
for line in fd:
line = line.replace("\n", "")
print(line)
cursor.close()
connection.close()

Comment

Add Comment

SUPPLIER
Suppno	Name	City	Icode	SuppQty
S001	Prasad	Delhi	1008	100
S002	Anu	Bangalore	1010	200
S003	Shahid	Bangalore	1008	175
S004	Akila	Hydrabad	1005	195
S005	Girish	Hydrabad	1003	25
S006	Shylaja	Chennai	1008	180
S007	Lavanya	Mumbai	1005	325

Icode	:-	integer and act as primary key
Item Name	:-	Character with length 25
Rate	:-	Integer
Record to be added	:-	1008, Monitor,15000

InstaQB

Institutions

12th Standard English Medium

12th Standard Tamil Medium

11th Standard English Medium

11th Standard Tamil Medium

10th Standard English Medium

9th Standard English Medium

8th Standard English Medium

7th Standard English Medium

Class XII

Class XI

12Th Standard

11Th Standard

10Th Standard

9Th Standard

12th Computer Science Monthly Test - 2 ( Data manipulation through SQL )

St. Britto Hr. Sec. School - Madurai

12th Computer Science Monthly Test - 2 ( Data manipulation through SQL )-Aug 2020

Part - A

Multiple Choice Question - Answer All Question

Which method has a major role in working with python?

______ has a native library of SQlite.

cursor.description will be stored as a

All the SQlite commands will be executed using _____ object.

The WHERE clause cannot be combined with

In which class the group functions can be used?

_____ SQlite command contain the details of each table column headings

In python, the path of a file can be represented as _____

Which of the following clause will not 'work in SQlite?

Which of the following is used define a SQL command in SQlite3?

Which of the following operator cannot be used to filter records based on more than one condition?

A table column will be automatically auto incremented in SQlite3 by giving

The SQlite command opens the already created database is

Which of the following is not an aggregate functions?

Part - B

Generic 2 - Answer All Question

Write the command to populate record in a table. Give an example.

What is master table?

Mention the users who uses the Database.

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,sep="\n")

import sqlite3 connection = sqlite3.connect("Academy.db") cursor = connection.cursor() cursor.execute("SELECT * FROM student") print("fetching all records one by one:") result = cursor.fetchone() while result is not None: print(result) result = cursor.fetchone()

import sqlite3 connection = sqlite3.connect("Academy.db") cursor = connection.cursor() cursor.execute("SELECT DISTINCT (Grade) FROM student") result = cursor.fetchall() print(result)

Part - C

Generic 3 - Answer All Question

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)

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

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

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)

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

What is the use of Where Clause.Give a python statement Using the where clause.

What is the purpose of using (i) COUNT() (ii) AVG() (iii) SUM() (iv) MAX() (v) MIN().

Explain how a connect to be made to a database (Academy through python SQlite3.

Part - D

Generic 5 - Answer All Question

What is the use of HAVING clause. Give an example python script

In this example we are going to count the number of records by specifying a column import sqlite3 connection = sqlite3.connect("Academy.db") cursor = connection.cursor() cursor.execute("SELECT COUNT(AVERAGE) FROM student ") result = cursor.fetchall() print(result)

Comment

Other Study material

12th Computer Science Weekly Test - 1 ( Data manip

12th Computer Science Monthly Test - 3 (Importing

12th Computer Science Monthly Test - 3 ( Data vis

12th Computer Science Weekly Test - 4 ( Data manip

12th Computer Science Weekly Test - 3 ( Data manip

12th Computer Science Weekly Test - 2 ( Data manip

12th Computer Science Monthly Test - 1 ( Python an

12th Computer Science Monthly Test - 2 ( Python an

12th Computer Science Monthly Test - 3 ( Python an

12th Computer Science Weekly Test - 1 ( Importing

12th Computer Science Weekly Test - 2 ( Importing

12th Computer Science Weekly Test - 3 ( Importing

12th Computer Science Weekly Test - 4 ( Importing

12th Computer Science Weekly Test - 5 ( Importing

12th Computer Science Monthly Test - 1 ( Importing

Tamilnadu Stateboardszs - Class

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,sep="\n")

import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT * FROM student")
print("fetching all records one by one:")
result = cursor.fetchone()
while result is not None:
print(result)
result = cursor.fetchone()

import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT DISTINCT (Grade) FROM student")
result = cursor.fetchall()
print(result)

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)

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

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

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)

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

What is the purpose of using

(i) COUNT()
(ii) AVG()
(iii) SUM()
(iv) MAX()
(v) MIN().

In this example we are going to count the number of records by specifying a column
import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT COUNT(AVERAGE) FROM student ")
result = cursor.fetchall()
print(result)