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

St. Britto Hr. Sec. School - Madurai

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

Time : 2.30 Hours

Part - A

Multiple Choice Question - Answer All Question

How many kinds of placeholders the SQlite3 module supports

Which of the following function retrieves the average of a selected column of rows in a table?

Add()
SUM()
AVG( )
AVERAGE()

All the SQlite commands will be executed using _____ object.

connect
cursor
CSV
python

_____ SQlite command contain the details of each table column headings

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

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

Which of following is fast, flexible and easy to work?

CSV
SQlite
Perl
Ruby

The SQlite command opens the already created database is

Cursor
Sql-comm
Connect
Connection

Which of the following placeholders does the SQlite3 module supports?

q mark style
named style
module style
a and b

Which of the following clause is often used with aggregate functions to group the result?

ORDERBY
WHERE
GROUP BY
DISTINCT

Which of the following clause avoide the duplicate?

Distinct
Remove
Where
GroupBy

Count () returns ________ if there were no matching rows.

0
1
NOTNULL
NULL

______ has a native library of SQlite.

Python
C++
Java
C

Which SQlite method is used to fetch the required number of rows in the database table?

fetch()
fetchamany ()
fetchrows ()
tablerows ()

Which of the following is called the master table?

sqlite_master
sql_master
main_master
master_main

SQLite falls under which database system?

Flat file database system
Relational Database system
Hierarchical database system
Object oriented Database system

Part - B

Generic 2 - Answer All Question

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

What is master table?

What is a cursor in SQL and databases?

How will you sort the data in a table in an ordered way?

Write a note or cursor description.

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

Mention the users who uses the Database.

Write the placeholders which supported by SQlite3 module Execute.

Which method is used to fetch all rows from the database table?

How the cursor object is created?

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)

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

Which method is used to connect a database? Give an example.

What is the reason behind defining a SQL command with triple quotes?

What is the use of AND, OR operators combined ·with WHERE clause?

Part - C

Generic 3 - Answer All Question

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)

import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT Rollno,sname FROM student
WHERE(Birth_date>='2001-01-01' ANDBirth_date<='2001-12-01')")
result = cursor.fetchall()
print(*result,sep="\n")
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)

Explain how the SELECT statement can be used along with GROUP BY class.

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

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

What is SQLite?What is it advantage?

List the classes used in the SQL SELECT statement.

Write the sqlite steps to connect the database.

What is the use of aggregate functions used along with SELECT statement? What are they?

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

Part - D

Generic 5 - Answer All Question

Write in brief about SQLite and the steps used to use it.

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

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

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

Write the Python script to display all the records of the following table using fetchmany()

Icode ItemName Rate

1003 Scanner 10500

1004 Speaker 3000

1005 Printer 8000

1008 Monitor 15000

1010 Mouse 700

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)

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	ItemName	Rate
1003	Scanner	10500
1004	Speaker	3000
1005	Printer	8000
1008	Monitor	15000
1010	Mouse	700

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 - 1 ( Data manipulation through SQL )

St. Britto Hr. Sec. School - Madurai

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

Part - A

Multiple Choice Question - Answer All Question

How many kinds of placeholders the SQlite3 module supports

Which of the following function retrieves the average of a selected column of rows in a table?

All the SQlite commands will be executed using _____ object.

_____ SQlite command contain the details of each table column headings

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

Which of following is fast, flexible and easy to work?

The SQlite command opens the already created database is

Which of the following placeholders does the SQlite3 module supports?

Which of the following clause is often used with aggregate functions to group the result?

Which of the following clause avoide the duplicate?

Count () returns ________ if there were no matching rows.

______ has a native library of SQlite.

Which SQlite method is used to fetch the required number of rows in the database table?

Which of the following is called the master table?

SQLite falls under which database system?

Part - B

Generic 2 - Answer All Question

What is master table?

What is a cursor in SQL and databases?

How will you sort the data in a table in an ordered way?

Write a note or cursor description.

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

Mention the users who uses the Database.

Write the placeholders which supported by SQlite3 module Execute.

Which method is used to fetch all rows from the database table?

How the cursor object is created?

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)

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

Which method is used to connect a database? Give an example.

What is the reason behind defining a SQL command with triple quotes?

What is the use of AND, OR operators combined ·with WHERE clause?

Part - C

Generic 3 - Answer All Question

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)

import sqlite3 connection = sqlite3.connect("Academy.db") cursor = connection.cursor() cursor.execute("SELECT Rollno,sname FROM student WHERE(Birth_date>='2001-01-01' ANDBirth_date<='2001-12-01')") result = cursor.fetchall() print(*result,sep="\n")

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)

Explain how the SELECT statement can be used along with GROUP BY class.

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

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

What is SQLite?What is it advantage?

List the classes used in the SQL SELECT statement.

Write the sqlite steps to connect the database.

What is the use of aggregate functions used along with SELECT statement? What are they?

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

Part - D

Generic 5 - Answer All Question

Write in brief about SQLite and the steps used to use it.

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

Write the Python script to display all the records of the following table using fetchmany() Icode ItemName Rate 1003 Scanner 10500 1004 Speaker 3000 1005 Printer 8000 1008 Monitor 15000 1010 Mouse 700

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

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

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)

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

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)

import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT Rollno,sname FROM student
WHERE(Birth_date>='2001-01-01' ANDBirth_date<='2001-12-01')")
result = cursor.fetchall()
print(*result,sep="\n")

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 DISTINCT (Grade) FROM student where gender='M'")
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

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

Write the Python script to display all the records of the following table using fetchmany()

Icode ItemName Rate

1003 Scanner 10500

1004 Speaker 3000

1005 Printer 8000

1008 Monitor 15000

1010 Mouse 700