Computer Science : Integrating Python with MySql and C++ : Data Manipulation Through SQL
Evaluation
Part – I
Choose the best answer (1 Marks)
1.Which of the following is an organized collection of data?
(A) Database
(B) DBMS
(C) Information
(D) Records
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
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
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
5.Which of the following executes the SQL command to perform some action?
(A) Execute()
(B) Key()
(C) Cursor()
(D) run()
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()
7.The function that returns the largest value of the selected column is
(A) MAX()
(B) LARGE()
(C) HIGH()
(D) MAXIMUM()
8. Which of the following is called the master table?
(A) sqlite_master
(B) sql_master
(C) main_master
(D) master_main
9. The most commonly used statement in SQL is
(A) cursor
(B) select
(C) execute
(D) commit
10. Which of the following clause avoide the duplicate?
(A) Distinct
(B) Remove
(C) Where
(D) GroupBy
Part – II
Answer the following questions (2 Marks)
1. Mention the users who uses the Database.
Ans.
Users of database can be human users, other programs or applications.
2. Which method is used to connect a database? Give an example.
Ans. cursor() method is used to connect a database.
sql.command = “”INSERT
INTO Student (Rollno, Sname, Grade, gender, Average, birth_date)
3. What is the advantage of declaring a column as “INTEGER PRIMARY KEY”
Ans. If a column of a table is declared to be an INTEGER PRIMARY KEY,
then whenever a: NULL is used as an input for this column, the NULL will be
automatically converted into an integer which will be one larger than the
highest value so far used in that column.
4. Write the command to populate record in a table. Give an example.
Ans. . To populate (add record) the table "INSERT" command.
Example :
connection = sqlite3.connect ("Academy.db")
cursor =
connection.cursor()
5. Which method is used to fetch all rows from the database table?
Ans. cursor.fetchall() - fetchall () method is to fetch all rows
from the database table.
Part – III
Answer the following questions (3 Marks)
1. What is SQLite?What is it advantage?
Ans. (i) SQLite is a simple relational database system, which saves
its data in regular data files or even in the internal memory of the computer.
(ii) It is designed to be embedded in applications, instead of
using a separate database server program such as MySQLor Oracle.
(iii) SQLite is fast, rigorously tested, and flexible, making it
easier to work.
2. Mention the difference between fetchone() and fetchmany()
Ans. (i) cursor.fetchone() - The fetchone () method returns the next
row of a query result set or None in case there is no row left.
(ii) cursor.fetchmany() method returns the next number of rows
(n) of the result set.
3. What is the use of Where Clause.Give a python statement Using the where clause.
Ans. (i) The WHERE clause is used to extract only those records that
fulfill a specified condition.
(ii) cursor.execute("SELECT DISTINCT (Grade) FROM student
where gender='M"')
4. Read the following details.Based on that write a python script to display department wise records
database name :- organization.db
Ans. import sqlite3
connection = sqlite3. connect ("organization.db")
cursor = connection . cursor ()
cursor, execute ("SELECT * FROM Employee GROUPBY
Dept")
result = cursor . fetchall ()
print (* result, sep = "\n")
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
Ans. import sqlite3
connection = sqlite3 . connect ("organization, db")
cursor = connection . cursor ()
cursor, execute ("SELECT * FROM Employee ORDER BY Eno
DESC")
result = cursor. fetchall ()
print (*result, sep = "\n"))
Part – IV
Answer the following questions (5 Marks)
1. Write in brief about SQLite and the steps used to use it.
Ans. (i) SQLite is a simple relational database system, which saves
its data in regular data files or even in the internal memory of the computer.
(ii) It is designed to be embedded in applications, instead of
using a separate database server program such as MySQLor Oracle.
(iii) 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 sqlite3
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 ()
(iv) 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.
(v) Cursor in step 3 is a control structure used to traverse and
fetch the records of the database.
(vi) Cursor has a major role in working with Python. All the
commands will be executed using cursor object only.
(vii) To create a table in the database, create an object and
write the SQL command in it.
Example : sql_comm = "SQL statement"
(viii) 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.
(ix) Any changes made in the values of the record should be
saved by the commend "Commit" before closing the "Table
connection".
2. Write the Python script to display all the records of the following table using fetchmany()
Ans. Assume database name, is shop.db and table name is electronics
for the given table.
Python Script:
import sqlite3
connection = sqlite3.connect ("shop.db")
cursor = connection . cursor ()
cusrsor. execute ("SELECT * FROM electronics")
result = cursor. fetchall ()
print (* result, sep = "\n")
3. hat is the use of HAVING clause. Give an example python script
Ans. 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.fetchallO
co = [i[0] for i in cursor.description]
print(co)
print(result)
Output:
[‘gender’, 'COUNT(GENDER)']
[('M', 5)]
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 specification :-
Ans. import sqlite3
connection = sqlite3 . connect ("ABc.db")
cursor = connection . cursor ()
sql_command ="""
CREATE TABLE Item (
Icode INTEGER, Item_Name VARCHAR (25), Rate Integer);”"”
cursor.execute (sql_command)
sql_command = “"”INSERT INTO Item (Icode, Item_name, Rate)
VALUES (1008, "Monitor", 15000);”"”
cursor.execute (sql_command)
connection.commit ()
connection.close ()
5. Consider the following table Supplier and item .Write a python script for (i) to (ii)
i) Display Name, City and Itemname of suppliers who do not reside in Delhi.
ii) Increment the SuppQty of Akila by 40
Ans. (i) import sqlite3
connection = sqlite3.connection ("supplier.db")
cursor = connection.cursor ()
cursor.execute
("SELECT Name, City, Icode FROM Item WHERE City <>
"Delhi")
result = cursor.fetchall ()
print (* result, sep = "\n")
(ii) import sqlite3
conn = sqlite3.connect ("supplier.db")
conn.execute ("UPDATE Item SET Name = ‘Akila’ WHERE SuppQty
= 235)
conn.commit ()
cursor = conn.execute ("SELECT * FROM Item")
for row in cursor:
print (row)
conn.close ()
Related Topics
Privacy Policy, Terms and Conditions, DMCA Policy and Compliant
Copyright © 2018-2023 BrainKart.com; All Rights Reserved. Developed by Therithal info, Chennai.