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

St. Britto Hr. Sec. School - Madurai

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

Time : 1.30 Hours

Part - A

Multiple Choice Question - Answer All Question

The WHERE clause cannot be combined with

______ function returns the number of rows in a table satisfying the criteria specified in the where class?

Distinct
Count
Having
Counter

Which method has a major role in working with python?

cursor()
connect ()
execute()
close

Which method is SQlite is used create a connection with a database file created?

cursor( )
lite()
connect()
connection()

Which values cannot be counted?

Integer
String
Float
Null

______ has a native library of SQlite.

Python
C++
Java
C

_______ is a simple relational database system.

Cython
Boost
MySQL
SQlite

Which of the following executes the SQL command to perform some action?

Execute()
Key()
Cursor()
run()

______ program can interact as a user of a SQL database.

C++
Python
Java
C

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

Part - B

Generic 2 - Answer All Question

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

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)
How will you sort the data in a table in an ordered way?

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

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

Part - C

Generic 3 - Answer All Question

List the classes used in the SQL SELECT statement.
Write the sqlite steps to connect the database.

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

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

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)

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

Part - D

Generic 5 - Answer All Question

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

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

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

Comment

Add Comment

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

St. Britto Hr. Sec. School - Madurai

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

Part - A

Multiple Choice Question - Answer All Question

The WHERE clause cannot be combined with

______ function returns the number of rows in a table satisfying the criteria specified in the where class?

Which method has a major role in working with python?

Which method is SQlite is used create a connection with a database file created?

Which values cannot be counted?

______ has a native library of SQlite.

_______ is a simple relational database system.

Which of the following executes the SQL command to perform some action?

______ program can interact as a user of a SQL database.

Part - B

Generic 2 - Answer All Question

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

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)

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

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

Part - C

Generic 3 - Answer All Question

List the classes used in the SQL SELECT statement.

Write the sqlite steps to connect the database.

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

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

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)

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

Part - D

Generic 5 - Answer All Question

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)

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

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

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 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 * 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 Rollno,Same,Average FROM student WHERE (Average>=80 AND Average<=90)")
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)

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