Data Manipulation Through SQL: Book Back Questions and Answers

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