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

St. Britto Hr. Sec. School - Madurai

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

Time : 1.30 Hours

Part - A

Multiple Choice Question - Answer All Question

Which of the following is an organized collection of data?

Database
DBMS
Information
Records

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

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

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

Distinct
Count
Having
Counter

SQLite falls under which database system?

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

The WHERE clause cannot be combined with

The table's field names can be displayed using

cursor.connect
cursor.execute
cursor.commit
cursor.description

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

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

Which SQlite keyword is used to fetch only the unique values from the database table?

UNIQUE
DISTINCT
GROUPBY
HAVING

Which SQlite clause is used to filter data base on the group functions?

WHERE
HAVING
ORDER
FILTER

_____ SQlite command contain the details of each table column headings

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

cursor.description will be stored as a

list
set
tuple
dictionary

The command to modify the values in the existing table

MODIFY
SELECT
UPDATE
CHANGE

_______ is a simple relational database system.

Cython
Boost
MySQL
SQlite

The function that returns the largest value of the selected column is

MAX()
LARGE()
HIGH()
MAXIMUM()

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

0
1
NOTNULL
NULL

Part - B

Generic 5 - Answer All Question

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(sql_command)
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 read from cursor.description.
result = cursor.fetchall()
for r in result:
print(r)

# 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_id =[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' )

import sqlite3
con = sqlite3.connect('Academy.db')
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())

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

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

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)

# code for delete operation
import sqlite3
# database name to be passed as parameter
conn = sqlite3.connect("Academy.db")
# delete student record from database
conn.execute("DELETE from Student where Rollno='2'")
conn.commit()
print("Total number of rows deleted :", conn.total_changes)
cursor =conn.execute("SELECT * FROM Student")
for row in cursor:
print(row)
conn.close()
import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT AVG(AVERAGE) FROM student ")
result = cursor.fetchall()
print(result)

Comment

Add Comment

Icode	ItemName	Rate
1003	Scanner	10500
1004	Speaker	3000
1005	Printer	8000
1008	Monitor	15000
1010	Mouse	700

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

St. Britto Hr. Sec. School - Madurai

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

Part - A

Multiple Choice Question - Answer All Question

Which of the following is an organized collection of data?

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

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

SQLite falls under which database system?

The WHERE clause cannot be combined with

The table's field names can be displayed using

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

Which SQlite keyword is used to fetch only the unique values from the database table?

Which SQlite clause is used to filter data base on the group functions?

_____ SQlite command contain the details of each table column headings

cursor.description will be stored as a

The command to modify the values in the existing table

_______ is a simple relational database system.

The function that returns the largest value of the selected column is

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

Part - B

Generic 5 - Answer All Question

import sqlite3 con = sqlite3.connect('Academy.db') cursor = con.cursor() cursor.execute("SELECT name FROM sqlite_master WHERE type='table';") print(cursor.fetchall())

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("Academy.db") cursor = connection.cursor() cursor.execute("SELECT AVG(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
con = sqlite3.connect('Academy.db')
cursor = con.cursor()
cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
print(cursor.fetchall())

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("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT AVG(AVERAGE) FROM student ")
result = cursor.fetchall()
print(result)