Samacheer Kalvi 12th Computer Science Guide Chapter 15 Data Manipulation Through SQL

Tamilnadu State Board New Syllabus Samacheer Kalvi 12th Computer Science Guide Pdf Chapter 15 Data Manipulation Through SQL Text Book Back Questions and Answers, Notes.

Tamilnadu Samacheer Kalvi 12th Computer Science Solutions Chapter 15 Data Manipulation Through SQL

12th Computer Science Guide Data Manipulation Through SQL Text Book Questions and Answers

I. Choose the best answer (I Marks)

Question 1
Which of the following is an organized collection of data?
a) Database
b) DBMS
c) Information
d) Records
Answer:
a) Database

Question 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
Answer:
b) Relational Database system

Question 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
Answer:
c) Cursor

Question 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
Answer:
c) Commit

Question 5.
Which of the following executes the SQL command to perform some action?
a) execute()
b) Key()
c) Cursor()
d) run()
Answer:
a) execute()

Question 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()
Answer:
c) AVG()

Question 7.
The function that returns the largest value of the selected column is
a) MAX()
b) LARGE ()
c) HIGH ()
d) MAXIMUM ()
Answer:
a) MAX()

Question 8.
Which of the following is called the master table?
a) sqlite_master
b) sql_master
c) main_master
d) master_main
Answer:
a) sqlite_master

Question 9.
The most commonly used statement in SQL is
a) cursor
b) select
c) execute
d) commit
Answer:
b) select

Question 10.
Which of the following clause avoid the duplicate?
a) Distinct
b) Remove
c) Wher
d) Group By
Answer:
a) Distinct

II. Answer the following questions (2 Marks)

Question 1.
Mention the users who use the Database.
Answer:
Users of databases can be human users, other programs, or applications.

Question 2.
Which method is used to connect a database? Give an example.
Answer:

• Connect()method is used to connect a database
• Connecting to a database 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.

Example:
import sqlite3
# connecting to the database
connection = sqlite3.connect (“Academy.db”)
# cursor
cursor = connection. cursor()

Question 3.
What is the advantage of declaring a column as “INTEGER PRIMARY KEY”
Answer:
If a column of a table is declared to be an INTEGER PRIMARY KEY, then whenever a NULL will be used as an input for this column, the NULL will be automatically converted into an integer which will one larger than the highest value so far used in that column. If the table is empty, the value 1 will be used.

Question 4.
Write the command to populate record in a table. Give an example.
Answer:

• To populate (add record) the table “INSERT” command is passed to SQLite.
• “execute” method executes the SQL command to perform some action.

Example:
import sqlite3
connection = sqlite3.connect
(“Academy.db”)
cursor = connection.cursor()
CREATE TABLE Student ()
Rollno INTEGER PRIMARY KEY,
Sname VARCHAR(20), Grade CHAR(1),
gender CHAR(l), Average DECIMAL
(5,2), birth_date DATE);”””
cursor.execute(sql_command)
sqLcommand = “””INSERT INTO Student (Rollno, Sname, Grade, gender, Average, birth_date)
VALUES (NULL, “Akshay”, “B”, “M”, “87.8”, “2001-12-12″);”””
cursof.execute(sql_ command) sqLcommand .= “””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(“Records are populated”)
OUTPUT:
Records are populated

Question 5.
Which method is used to fetch all rows from the database table?
Answer:
Displaying all records using fetchall( )
The fetchall( ) method is used to fetch all rows from the database table
result = cursor.fetchall( )

III. Answer the following questions (3 Marks)

Question 1.
What is SQLite? What is its advantage?
Answer:

• SQLite is a simple relational database system, which saves its data in regular data files or even in the internal memory of the computer.
• SQLite is designed to be embedded in applications, instead of using a separate database server program such as MySQL or Oracle.
• SQLite is fast, rigorously tested, and flexible, making it easier to work.

Question 2.
Mention the difference between fetchone() and fetchmany().
Answer:

fetchone()	fetchmany()
The fetchone() method returns the next row of a query result set	fetchmany() method returns the next number of rows (n) of the result set. .
Example: r=cursor. fetchoneQ	Example: r=cursor. fetchmanyQ)

Question 3.
What is the use of the Where Clause? Give a python statement Using the where clause.
Answer:

• The WHERE clause is used to extract only those records that fulfill a specified condition.
• The WHERE clause can be combined with AND, OR, and NOT operators.
• The AND and OR operators are used to filter records based on more than one condition.

Example:
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”)
OUTPUT:
(‘B’,)
(‘A’,)
(‘C’,)
(‘D’,)

Question 4.
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

Contents of Table: Employee

Eno	EmpName	Esal	Dept
1001	Aswin	28000	IT
1003	Helena	32000	Accounts
1005	Hycinth	41000	IT

Coding:
import sqlite3
connection = sqlite3. connect
(” organization, db”)
cursor = connection, cursor ()
sqlcmd=””” SELECT *FROM
Employee ORDER BY Dept”””
cursor, execute (sqlcmd)
result = cursor, fetchall ()
print (“Department wise Employee List”)
for i in result:
print(i)
connection. close()
Output:
Department wise Employee List
(1003,’Helena’,32000, ‘Accounts’)
(1001,’Aswin’,28000,’IT’)
(1005,’Hycinth’,41000,’IT’)

Question 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
Contents of Table: Employee

Eno	EmpName	Esal	Dept
1001	Aswin	28000	IT
1003	Helena	32000	Accounts
1005	Hycinth	41000	IT

Coding:
import sqlite3
connection = sqlite3 . connect
(“organization, db”)
cursor = connection, cursor ()
cursor, execute (“SELECT *
FROM Employee ORDER BY Eno DESC”)
result = cursor, fetchall ()
print (“Department wise
Employee List in descending order:”)
for i in result:
print(i)
connection.close()
Output:
Department wise Employee List in descending order:
(1005,’Hycinth’,41000,’IT’)
(1003,’Helena’,32000, ‘Accounts’)
(1001,’Aswin’,28000,’IT’)

IV. Answer the following questions (5 Marks)

Question 1.
Write in brief about SQLite and the steps used to use it.
Answer:
SQLite is a simple relational database system, which saves its data in regular data files or even in the internal memory of the computer. It is designed to be embedded in applications, instead of using a separate database server program such as MySQL or Oracle. 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 sqliteS
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( )

1. 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.
2. Cursor in step 3: is a control structure used to traverse and fetch the records of the database.
3. Cursor has a major role in working with Python. All the commands will be executed using cursor object only.

To create a table in the database, create an object and write the SQL command in it.
Example:- sql_comm = “SQL statement”
For executing the command use the cursor method and pass the required sql command as a parameter. Many commands can be stored in the SQL command can be executed one after another. Any changes made in the values of the record should be saved by the command “Commit” before closing the “Table connection”.

Question 2.
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

Answer:
Database : supermarket
Table : electronics
Python Script:
import sqlite3
connection = sqlite3.connect
(” supermarket. db”)
cursor = connection.cursor()
cursor.execute
(“SELECT * FROM electronics “)
print(“Fetching all 5 records :”)
result = cursor.fetchmany(5)
print(*result,sep=” \ n”)
Output:
Fetching all 5 records :
(1003,’Scanner’ ,10500)
(1004,’Speaker’,3000)
(1005,’Printer’,8000)
(1008,’Monitor’,15000)
(1010,’Mouse’,700)

Question 3.
What is the use of HAVING clause. Give an example python script.
Answer:
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. fetchall( )
co= [i[0] for i in cursor, description]
print(co)
print( result)
OUTPUT
[‘gender’, ‘COUNT(GENDER)’]
[(‘M’, 5)]

Question 4.
Write a Python script to create a table called ITEM with the following specifications.
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
Answer:
Coding:
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 (sqlcmd)
connection, commit ()
print(“Table Created”)
cursor. execute(SELECT *FROM ITEM”)
result=cursor.fetchall():
print(“CONTENT OF THE TABLE
print(*result,sep=”\n”)
connection, close ()
Output:
Table Created
CONTENT OF THE TABLE :
(1008, ‘Monitor’, 15000)

Question 5.
Consider the following table Supplier and item.
Write a python script for
i) Display Name, City and Item name of suppliers who do not reside in Delhi.
ii) Increment the SuppQty of Akila by 40
Name of the database : ABC
Name of the table : 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:
Coding:
import sqlite3
connection = sqlite3.
connection/’ABC.db”)
cursor = connection, cursor ()
sqlcmd=”””(“SELECT SUPPLIER. Name,SUPPLIER.City,Item.ItemName FROM SUPPLIER,Item WHERE SUPPLIER.City NOT IN(“Delhi”) and SUPPLIER.Icode=Item.Icode” ” ”
cursor, execute(sqlcmd)
result = cursor.fetchall()
print(” Suppliers who do not reside in Delhi:”)
for r in result:
print(r)
conn.commit ()
conn, close ()
Output:
Suppliers who do not reside in Delhi:
(‘ Anu’ / Bangalore’/Mouse’)
(‘Shahid7,’Bangalore7,’Monitor’)
(‘Akila’/Hydrabad’,’Printer’)
(‘Girish’/Hydrabad’/Scanner’)
(‘Shylaja’/Chennai’/Monitor’)
(‘ La vanya’/ Mumbai’/ Printer’)

ii) Increment the SuppQty of Akila by 40: import sqlite3
connection = sqlite3.
connection/’ABC.db”)
cursor = connection, cursor ()
sqlcmd=”””UPDATE SUPPLIER
SET Suppqty=Suppqty+40 WHERE
Name=’Akila”””
cursor, execute(sqlcmd)
result = cursor.fetchall()
print
(“Records after SuppQty increment:”)
for r in result:
print(r)
conn.commit ()
conn, close ()
Output:
Records after SuppQty increment:
(‘S001′ /PrasadVDelhi’,1008,100)
(‘S002′ ,’Anu’ /Bangalore’,1010,200)
(‘S003′ /Shahid’/Bangalore’, 1008,175)
(‘S004’/Akila’/Hydrabad’,1005,235)
(‘S005′ /Girish’/Hydrabad’, 003,25)
(‘S006′ /Shylaja’/Chennai’,1008,180)
(‘S007′ ,’Lavanya’,’Mumbai’,1005,325)

12th Computer Science Guide Data Manipulation Through SQL Additional Questions and Answers

I. Choose the best answer (I Marks)

Question 1.
…………………… command is used to populate the table.
a) ADD
b) APPEND
c) INSERT
d) ADDROW
Answer:
c) INSERT

Question 2.
Which has a native library for SQLite?
(a) C
(b) C++
(c) Java
(d) Python
Answer:
(d) Python

Question 3.
………………….. method is used to fetch all rows from the database table.
a) fetch ()
b) fetchrowsAll ()
c) fectchmany ()
d) fetchall ()
Answer:
d) fetchall ()

Question 4.
………………….. method is used to return the next number of rows (n) of the result set.
a) fetch ()
b) fetchmany ()
c) fetchrows ()
d) tablerows ()
Answer:
b) fetchmany ()

Question 5.
How many commands can be stored in the sql_comm?
(a) 1
(b) 2
(c) 3
(d) Many
Answer:
(d) Many

Question 6.
…………………..clause is used to extract only those records that fulfill a specified condition.
a) WHERE
b) EXTRACT
c) CONNECT
d) CURSOR
Answer:
a) WHERE

Question 7.
…………………..clause is used to sort the result-set in ascending or descending order.
a) SORT
b) ORDER BY
c) GROUP BY
d) ASC SORT
Answer:
b) ORDER BY

Question 8.
………………….. clause is used to filter database on the group functions?
a) WHERE
b) HAVING
c) ORDER
d) FILTER
Answer:
b) HAVING

Question 9.
What will be the value assigned to the empty table if it is given Integer Primary Key?
(a) 0
(b) 1
(c) 2
(d) -1
Answer:
(b) 1

Question 10.
The sqlite3 module supports ………………. kinds of placeholders:
a) 1
b) 2
c) 3
d) 5
Answer:
b) 2

Question 11.
……………… has a native library of SQlite.
a) Python
b) C++
c) Java
d) C
Answer:
a) Python

Question 12.
All the SQlite commands will be executed using……………… object only
a) connect
b) cursor
c) CSV
d) python
Answer:
b) cursor

Question 13.
Which method returns the next row of a query result set?
(a) Fetch ne( )
(b) fetch all( )
(c) fetch next( )
(d) fetch last( )
Answer:
(a) Fetch ne( )

Question 14.
…………… function returns the number of rows in a table satisfying the criteria specified in the WHERE clause.
a) Distinct
b) count
c) Having
d) Counter
Answer:
b) count

Question 15.
Count () returns …………… if there were no matching rows.
a) 0
b) 1
c) NOT NULL
d) NULL
Answer:
a) 0

Question 16.
…………… contains the details of each column headings
a) cursor, description
b) cursor.connect
c) cursor.column
d) cursor.fieldname
Answer:
a) cursor, description

Question 17.
Which one of the following is used to print all elements separated by space?
(a) ,
(b) .
(c) :
(d) ;
Answer:
(a) ,

II. Answer the following questions (2 and 3 Marks)

Question 1.
Write the SQLite steps to connect the database.
Answer:
Step 1: Import sqlite3
Step 2: Create a connection using connect o method and pass the name of the database file.
Step 3 : Set the cursor object cursor = connection, cursor ()

Question 2.
Mention the frequently used clauses in SQL?
Answer:

1. DISTINCT
2. WHERE
3. GROUP BY
4. ORDER BY
5. HAVING

Question 3.
Write a Python code to create a database in SQLite.
Answer:
Python code to create a database in SQLite:
import sqlite3
connection = sqlite3.connect (“Academy.db”)
cursor + connection.cursorQ

Question 4.
Define: sqlite_master
Answer:
sqlite_master is the master table which holds the key information about the database tables.

Question 5.
Give a short note on GROUP BY class.
Answer:

• The SELECT statement can be used along with the GROUP BY clause.
• The GROUP BY clause groups records into summary rows. It returns one record for each group.
• It is often used with aggregate functions (COUNT, MAX, MIN. SUM, AVG) to group the result -set by one or more columns.

Question 6.
Write short notes on

1. COUNT ()
2. AVG ()
3. SUM ()
4. MAX ()
5. MIN ()

Answer:

1. COUNT ( ) function returns the number of rows in a table.
2. AVG () function retrieves the average of a selected column of rows in a table.
3. SUM () function retrieves the sum of a selected column of rows in a table.
4. MAX( ) function returns the largest value of the selected column.
5. MIN( ) function returns the smallest value of the selected column.

Question 7.
Write a program to count the number of male and female students from the student table
Example
Answer:
import sqlite3
connection= sqlite3.connect(“Academy.db”)
cursor = connection.cursor( )
cursor.execute(“SELECT gender,count(gender) FROM student Group BY gender”)
result = cursor. fetchall( )
print(*result,sep=”\n”)
OUTPUT
(‘F’, 2)
(‘M’, 5)

Question 8.
Explain Deletion Operation with a suitable example.
Answer:
Deletion Operation:
Similar to Sql command to delete a record, Python also allows to delete a record.
Example: Coding to delete the content of Rollno 2 from “student table”

Coding:
# 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.commitQ
print(“Total number of rows deleted conn.total_changes)
cursor =conn.execute(“SELECT * FROM
Student”)
for row in cursor:
print(row)
conn.close()
OUTPUT:
Total number of rows deleted : 1
(1, ‘Akshay’, ‘B’, ‘M’, 87.8, ‘2001-12-12’)
(3, ‘BASKAR’, ‘C’, ‘M’, 75.2, ‘1998-05-17’)
(4, ‘SAJINI’, ‘A’, ‘F, 95.6, ‘2002-11-01’)
(5, ‘VARUN’, ‘B’, ‘M’, 80.6, ‘2001-03-14’)
(6, ‘Priyanka’, ‘A’, ‘F, 98.6, ‘2002-01-01’)
(7, ‘TARUN’, ‘D’, ‘M’, 62.3, ‘1999-02-01’)

Question 9.
Explain Table List with suitable example.
Answer:
Program to display the list of tables created in a database:
Coding:
import sqlite3
con = sqlite3.connect(‘Academy.db’)
cursor = con.cursor()
cursor.execute(“SELECT name FROM sqlite_master WHERE type=’table’;”) print(cursor.fetchall())
OUTPUT:
[(‘Student’,), (‘Appointment’,), (‘Person’,)]

Question 10.
Write a short note on cursor. fetchall(),cursor.fetchone(),cursor. fetchmany()
Answer:
cursor.fetchall():
cursor.fetchall() method is to fetch all rows from the database table .
cursor.fetchone():
cursor.fetchone() method returns the next row of a query result set or None in case there is no row left. cursor.fetchmany:
cursor.fetchmany() method that returns the next number of rows (n) of the result set

Question 11.
How to create a database using SQLite? Creating a Database using SQLite:
Answer:
# Python code to demonstrate table creation and insertions with SQL
# importing module import sqlite3
# connecting to the database connection = sqlite3.connect (“Academy.db”)
# cursor
cursor = connection.cursor()
In the above example a database with the name “Academy” would be created. It’s similar to the sql command “CREATE DATABASE Academy;”

Question 12.
Explain fetchall() to display all records with suitable examples?
Answer:
Displaying all records using fetchall():
The fetchall() method is used to fetch all rows from the database table.

Example:
import sqlite3
connection = sqlite3.connect(” Academy.db”)
cursor = connection.cursor()
cursor.execute(“SELECT FROM student”)
print(“fetchall:”)
result = cursor.fetchall()
for r in result:
print(r)
OUTPUT:
fetchall:
(1, ‘Akshay’, ‘B’, ‘M’, 87.8, ‘2001-12-12’)
(2, ‘Aravind’, ‘A’, ‘M’, 92.5, ‘2000-08-17’)
(3, ‘BASKAR’, ‘C’, ‘M’, 75.2, ‘1998-05-17’)
(4, ‘SAJINT, ‘A’, ‘F’, 95.6, ‘2002-11-01’)
(5, ‘VARUN’, ‘B’, ‘M’, 80.6, ‘2001-03-14’)
(6, ‘PRIYA’, ‘A’, ‘F’, 98.6, ‘2002-01-01’)
(7, ‘TARUN’, ‘D’, ‘M’, 62.3, ‘1999-02-01’)

Question 13.
Explain fetchone() to display a single record(one row) with a suitable example?
Answer:
Displaying A record using fetchone():
The fetchoneQ method returns the next row of a query result set or None in case there is no row left.

Example:
import sqlite3
connection = sqlite3.
connect(” Academy.db”)
cursor = connection.cursor()
cursor.execute(“SELECT * FROM student”)
print(“\nfetch one:”)
res = cursor.fetchone()
print(res)
OUTPUT:
fetch one:
(1, ‘Akshay’, ‘B’, ‘M’, 87.8, ‘2001-12-12’)

Question 14.
Explain fetchone() to display all records with suitable examples?
Answer:
Displaying all records using fetchone(): Using while ioop and fetchone() method we can display all the records from a table.

Example:
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()
OUTPUT:
fetching all records one by one:
(1, ‘Akshay’, ‘B’, ‘M’, 87.8, ‘2001-12-12’)
(2, ‘Aravind’, ‘A’, ‘M’, 92.5, ‘2000-08-17’)
(3, ‘BASKAR’, ‘C’, ‘M’, 75.2, ‘1998-05-17’)
(4, ‘SAJINI’, ‘A’, ‘F’, 95.6, ‘2002-11-01’)
(5, ‘VARUN’, ‘B’, ‘M’, 80.6, ‘2001-03-14’)
(6, ‘PRIYA’, ‘A’, ‘F’, 98.6, ‘2002-01-01’)
(7, ‘TARUN’, ‘D’, ‘M’, 62.3, ‘1999-02-01’)
Chapter 15.indd 297 70-02-2019 15:40:10

Question 15.
Explain fetchmany() to display a specified number of records with suitable example?
Answer:
Displayingusing fetchmany():
Displaying specified number of records is done by using fetchmany(). This method returns the next number of rows (n) of the result set.

Example : Program to display the content of tuples using fetchmany()
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)
OUTPUT:
fetching first 3 records:
[(1, ‘Akshay’, ‘B’, ‘M’, 87.8, ‘2001-12-12′),
(2,’ Aravin d’, ‘A’, ‘M’, 92.5, /2000-08-17′),
(3, ‘BASKAR’, ‘C’, ‘M’, 75.2, ‘1998-05-17’)]

II. Answer the following questions (5 Marks)

Question 1.
Explain clauses in SQL with suitable examples.
Answer:

• SQL provides various clauses that can be used in the SELECT statements.
• These clauses can be called through a python script.
• Almost all clauses will work with SQLite.

The various clauses is:

• DISTINCT
• WHERE
• GROUPBY
• ORDER BY.
• HAVING

Data of Student table:
The columns are Rollno, Sname, Grade,
gender, Average, birth_date
(1, ‘Akshay’, ‘B’, ‘M’, 87.8, ‘2001-12-12’)
(2, ‘Aravind’, ‘A’, ‘M’, 92.5, ‘2000-08-17’)
(3, ‘BASKAR’, ‘C’, ‘M’, 75.2, ‘1998-05-17’)
(4, ‘SAJINT, ‘A’, ‘F’, 95.6, ‘2002-11-01’)
(5, ‘VARUN’, ‘B’, ‘M’, 80.6, ‘2001-03-14’)
(6, ‘PRIYA’, ‘A’, ‘F’, 98.6, ‘2002-01-01’)
(7, ‘TARUN’, ‘D’, ‘M’, 62.3, ‘1999-02-01’)

i) SQL DISTINCT CLAUSE

• The distinct clause is helpful when there is a need of avoiding the duplicate values present in any specific columns/ table.
• When we use a distinct keyword only the unique values are fetched.

Example:
Coding to display the different grades scored by students from “student table”:

import sqlite3
connection = sqlite3.connect(” Academy, db”)
cursor = connection.cursorQ cursor.execute(“SELECT DISTINCT (Grade) FROM student”)
result = cursor.fetchall()
print (result)
OUTPUT:
[(‘B’,), (‘A’,), (‘C’,), (‘D’,)]

Without the keyword “distinct” in the “Student table” 7 records would have been displayed instead of 4 since in the original table there are actually 7 records and some are with duplicate values.

ii) SQL WHERE CLAUSE
The WHERE clause is used to extract only those records that fulfill a specified condition.

Example:
Coding to display the different grades scored by male students from “student table”.
import sqlite3
connection = sqlite3.connec
(“Academy.db”)
cursor = connection.cursor()
cursor.execute(” SELECT DISTINCT i (Grade) FROM student where gender=’M”)
result = cursor.fetchall()
print(*result/sep=”\n”)
OUTPUT:
(‘B’,)
(‘A’,)
(‘C’,)
(‘D’,)

iii) SQL GROUP BY Clause :

• The SELECT statement can be used along with the GROUP BY clause.
• The GROUP BY clause groups records into summary rows.
• It returns one records for each group,
• It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

Example:
Coding to count the number of male and ; female from the student table and display j the result.

Coding:
import sqlite3;
connection = sqlite3.connect(“Academy.db”)
cursor = connection.cursor()
cursor.execute(“SELECT gender,count(gender) FROM student Group BY gender”)
result = cursor.fetchall() j
print(*result,sep=”\n”)
OUTPUT:
(‘F’, 2)
(‘M’, 5)

iv) SQL ORDER BY Clause

• The ORDER BY clause can be used along with the SELECT statement to sort the data of specific fields in an ordered way.
• It is used to sort the result-set in ascending or descending order.

Example
Coding to display the name and Rollno of the students in alphabetical order of names . import sqlite3
connection = sqlite3.connect(” Academy.db”)
cursor = connection.cursor()
cursor.execute(“SELECT Rollno,sname FROM student Order BY sname”)
result = cursor.fetchall()
print (*result, sep=” \ n”)
OUTPUT
(1, ‘Akshay’)
(2, ‘Aravind’)
(3, ‘BASKAR’)
(6, ‘PRIYA’)
(4, ‘SAJINI’)
(7, ‘TARUN’)
(5, ‘VARUN’)

v) SQL HAVING Clause

• Having clause is used to filter data based on the group functions.
• Having clause 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.fetchall()
co = [i[0] for i in cursor, description]
print(co)
print(result)
OUTPUT:
[‘gender7,’ COUNT (GENDER)’ ]
[(‘M’, 5)]

Question 2.
Write a python program to accept 5 students’ names, their ages, and ids during run time and display all the records from the table?
Answer:
In this example we are going to accept data using Python input() command during runtime and then going to write in the Table called “Person”
Example
# 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_d =[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’)
OUTPUT
Enter 5 students names:
RAM
KEERTHANA
KRISHNA
HARISH
GIRISH
Enter their ages respectively:
28
12
21
18
16
Enter their ids respectively:
1
2
3
4
5
Displaying All the Records From Person Table
(‘RAM’, 28, 1)
(‘KEERTHANA’, 12, 2)
(‘KRISHNA’, 21, 3)
(‘HARISH’, 18,4)
(‘GIRISH’, 16, 5)

Question 3.
Write a Python program to store and retrieve the following data in SQLite3.
Database Schema:

Field	Type	Size	Constrain
Rollno	INTEGER		PRIMARY KEY
Sname	VARCHAR	20
Gender	CHAR	1
Average	DECIMAL	5,2

Date to be inserted as tuple:

Rolling	Sname	Gender	Average
1001	KULOTHUNGAN
1002	KUNDAVAI
1003	RAJARAJAN
1004	RAJENDRAN
1005	AVVAI

Answer:
Python Program:
import sqlite3
connection = sqlite3.connect (“Academy.db”)
cursor = connection.cursor()
cursor.execute (“””DROP TABLE Student;”””)
sql_command = “”” CREATE TABLE Student ( Rollno INTEGER PRIMARY KEY ,
Sname VARCHAR(20), Grade CHAR(l), gender CHAR(l), Average DECIMAL (5, 2));””” cursor.execute(sql_command)
sql_command = “””INSERT INTO Student VALUES (1001, “KULOTHUNGAN”, “M”, “75.2”);”””
sql_command = “””INSERT INTO Student VALUES (1002, “KUNDAVAI”, “F”, “95.6”);”””
sql_command = “””INSERT INTO Student VALUES (1003, “RAJARAJAN”, “M”, “80.6”);”””
sql_command = “””INSERT INTO Student VALUES (1004, “RAJENDRAN”, “M”, “98.6”);”””
sql_command = “””INSERT INTO Student VALUES (1005, “AVVAI”, “F”, “70.1”);”””
cursor.execute (sql_ command)
connection.commit()
connection.close()