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

I. Choose The Best Answer (1 Marks)

 

Question 1. Which of the following is an organized collection of data?
(a) Database
(c) Information
(d) Records
Answer: (a) Database
In simple words: A database is like an organized cabinet where all your information is stored in a neat and structured way.

🎯 Exam Tip: Remember that a database is fundamentally about structuring and managing data for efficient retrieval, unlike raw information or individual records.

 

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
In simple words: SQLite stores data in tables that are linked together using relationships, just like bigger relational databases.

🎯 Exam Tip: Understand that relational databases use tables with rows and columns, and relationships between tables, a key concept for SQL-based systems.

 

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
In simple words: A cursor acts like a pointer that moves through the results of a query, letting you work with one record at a time.

🎯 Exam Tip: The cursor is an essential tool for interacting with database results programmatically, especially when processing records one by one.

 

Question 4. Any change made in the value of the record should be saved by the command
(a) Save
(b) Save As
(c) Commit
(d) Oblige
Answer: (c) Commit
In simple words: The `COMMIT` command makes sure that all the changes you have made to the database are permanently saved.

🎯 Exam Tip: Always remember to `COMMIT` your transactions in SQL to ensure data persistence; otherwise, changes might be lost.

 

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()
In simple words: The `execute()` method is the specific function that sends your SQL commands to the database to be run.

🎯 Exam Tip: `execute()` is the standard method in Python's DB-API for running SQL queries and commands.

 

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()
In simple words: The `AVG()` function calculates the average value of all the numbers in a chosen column.

🎯 Exam Tip: `AVG()` is an aggregate function that works on numerical columns, providing the mean value of the data.

 

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()
In simple words: The `MAX()` function looks through a column and finds the very biggest number or value in it.

🎯 Exam Tip: `MAX()` is another important aggregate function for finding the highest value in a dataset column.

 

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
In simple words: The `sqlite_master` table is special because it holds all the important details about other tables, indexes, and views in the database.

🎯 Exam Tip: `sqlite_master` is a crucial internal table in SQLite for understanding the database's schema and structure.

 

Question 9. The most commonly used statement in SQL is
(a) cursor
(b) select
(c) execute
(d) commit
Answer: (b) select
In simple words: The `SELECT` statement is used most often because it lets you get (select) data from tables to view or use it.

🎯 Exam Tip: `SELECT` is the core of data retrieval in SQL, allowing you to specify what data you want and from where.

 

Question 10. Which of the following clause avoid the duplicate?
(a) Distinct
(b) Remove
(c) Wher
(d) Group By
Answer: (a) Distinct
In simple words: The `DISTINCT` keyword is used to make sure that you only get unique values in your results, removing any repeated ones.

🎯 Exam Tip: `DISTINCT` is very useful for cleaning up query results and showing only unique data points.

II. Answer The Following Questions (2 Marks)

 

Question 1. Mention the users who use the Database.
Answer: Users of databases include people, other computer programs, and various software applications. This allows different systems to access and manage information. Databases are designed to serve a wide range of clients, from individual users querying data to complex enterprise applications.
In simple words: Databases are used by people, other computer programs, and apps.

🎯 Exam Tip: When listing database users, think broadly about any entity that might need to store or retrieve structured information.

 

Question 2. Which method is used to connect a database? Give an example.
Answer: The `connect()` method is used to establish a connection to a database. When connecting, you need to provide the name of the database. If the database already exists, the connection will open to it; otherwise, a new database file with that name will be created by Python. This method is crucial for initiating any interaction with a database, allowing data to be read, written, or manipulated.
Example:
import sqlite3
# connecting to the database
connection = sqlite3.connect("Academy.db")
# cursor
cursor = connection.cursor()
In simple words: To link to a database, we use the `connect()` method and give it the database's name. If it's there, we open it; if not, a new one is made.

🎯 Exam Tip: Always remember to specify the database file name when using `sqlite3.connect()`, as this dictates which database you interact with.

 

Question 3. What is the advantage of declaring a column as “INTEGER PRIMARY KEY"
Answer: When a table column is set as an `INTEGER PRIMARY KEY`, it gains an automatic numbering feature. If a `NULL` value is provided for this column, the system will automatically assign a unique integer that is one greater than the current highest value in that column. If the table is empty at first, the very first record will be assigned the value `1`. This feature helps in creating unique identifiers for each record without manual assignment, ensuring data integrity and simplifying data management.
In simple words: Setting a column as `INTEGER PRIMARY KEY` means it automatically gets a unique number. If you leave it empty (NULL), it gets the next biggest number. If the table is new, it starts at 1.

🎯 Exam Tip: Using `INTEGER PRIMARY KEY` with `NULL` input is a common way to implement auto-incrementing IDs in SQLite, ensuring each row has a unique identifier.

 

Question 4. Write the command to populate record in a table. Give an example.
Answer: To add new records (data) into a table in SQLite, the `INSERT` SQL command is used. This command is then executed using the `execute()` method. After making changes, the `commit()` method must be called to permanently save these changes to the database, followed by `close()` to end the connection. This process is fundamental for adding any new data into a database, ensuring that information is stored correctly and persistently.
Example:
import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
sql_create_table_command = """
CREATE TABLE Student (
Rollno INTEGER PRIMARY KEY,
Sname VARCHAR(20),
Grade CHAR(1),
gender CHAR(1),
Average DECIMAL(5,2),
birth_date DATE
);"""
cursor.execute(sql_create_table_command)
sql_insert_command_1 = """
INSERT INTO Student (Rollno, Sname, Grade, gender, Average, birth_date)
VALUES (NULL, "Akshay", "B", "M", "87.8", "2001-12-12");"""
cursor.execute(sql_insert_command_1)
sql_insert_command_2 = """
INSERT INTO Student (Rollno, Sname, Grade, gender, Average, birth_date)
VALUES (NULL, "Aravind", "A", "M", "92.50", "2000-08-17");"""
cursor.execute(sql_insert_command_2)
connection.commit()
connection.close()
print("Records are populated")
Output:
Records are populated
In simple words: We use the `INSERT` command to put new information into a table. After running `INSERT` with `execute()`, we must `commit()` to save the data permanently.

🎯 Exam Tip: Always include `connection.commit()` after `INSERT`, `UPDATE`, or `DELETE` operations to save your changes to the database file.

 

Question 5. Which method is used to fetch all rows from the database table?
Answer: The `fetchall()` method is specifically used to retrieve all rows (records) from a database table at once. It returns these records as a list of tuples. This method is very useful when you need to process an entire result set, such as displaying all entries in a table.
Example:
result = cursor.fetchall()
In simple words: The `fetchall()` method is used to get all the rows from a table. It brings back all the records so you can see or use them.

🎯 Exam Tip: `fetchall()` is ideal when you need to process all results from a query, but be mindful of memory usage with very large datasets.

III. Answer The Following Questions (3 Marks)

 

Question 1. What is SQLite? What is its advantage?
Answer: SQLite is a lightweight relational database system that stores its data in ordinary files on a computer or even directly in memory. Its main advantage is that it is designed to be embedded directly into applications, meaning it doesn't need a separate database server like MySQL or Oracle. SQLite is known for being fast, well-tested, and adaptable, which makes it straightforward to use for developers. This embedded design simplifies development and deployment, making SQLite a popular choice for mobile apps and small-scale desktop applications.
In simple words: SQLite is a small, easy-to-use database that keeps data in normal files. Its benefit is that it can be built right into an app, so you don't need a separate big database program. It's also quick and simple to use.

🎯 Exam Tip: Highlight SQLite's serverless and embedded nature as its primary advantage, contrasting it with client-server databases.

 

Question 2. Explain fetchone() and fetchmany() methods.
Answer:

The `fetchone()` method retrieves just the very next single row from the set of results that a database query returns. In contrast, the `fetchmany()` method is used to get a specified number of next rows (n) from the query results. These methods are essential for iterating through query results efficiently, allowing you to process data one by one or in smaller batches.
In simple words: `fetchone()` gets one row at a time. `fetchmany()` gets a specific number of rows at a time. Both help you look at results from your database.

🎯 Exam Tip: Differentiate `fetchone()` for single-row retrieval and `fetchmany()` for batch retrieval, especially when resource management is a concern.

 

Question 3. What is the use of the Where Clause? Give a python statement Using the where clause.
Answer: The `WHERE` clause in SQL is used to select only those records that match specific conditions, acting as a filter for your data. It can be combined with logical operators like `AND`, `OR`, and `NOT` to create complex filtering rules. These operators help in filtering records when more than one condition needs to be checked. The `WHERE` clause is fundamental for targeted data retrieval, allowing users to query very specific subsets of information from large datasets.
Example:
import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT DISTINCT Grade FROM Student WHERE gender='M'")
result = cursor.fetchall()
co = [description[0] for description in cursor.description]
print(co)
print(result)
Output:
['gender', 'COUNT(GENDER)']
[('M', 5)]
In simple words: The `WHERE` clause helps us find only the data we need by checking conditions. You can use `AND` or `OR` to check many conditions at once.

🎯 Exam Tip: Explain that `WHERE` filters rows *before* any grouping, which is a key difference from the `HAVING` clause.

 

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

Answer: To display records from the `Employee` table, sorted by department, a Python script can be written using the `sqlite3` module. First, establish a connection to the `organization.db` database. Then, create a cursor object and execute an SQL `SELECT` statement that retrieves all columns (`*`) from the `Employee` table and orders the results by the `Dept` column. Finally, fetch all the results and print them in a readable format. Ordering data by departments makes it easy to analyze department-specific information and see which employees belong to which teams.
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')
In simple words: To show employee records grouped by department, we use a Python script. It connects to the database, asks for all employee details sorted by their department, then shows the list.

🎯 Exam Tip: The `ORDER BY` clause is essential for presenting query results in a logical sequence, improving readability and data analysis.

 

Question 5. Read the following details. Based on that write a python script to display records in descending order of Eno.
Database name : organization.db
Table name: Employee
Columns in the table : Eno, EmpName, Esal, Dept
Contents of Table: Employee

Answer: To display employee records sorted by `Eno` (Employee Number) in descending order, a Python script can be used. First, establish a connection to the `organization.db` database and create a cursor object. Then, execute an SQL `SELECT` statement that fetches all data from the `Employee` table, specifically ordering the results by the `Eno` column in descending (`DESC`) sequence. Finally, retrieve all the results and print them to show the list of employees. Sorting by employee number in descending order can quickly show the most recently added or highest-numbered employees first, which is useful for administrative tasks.
Coding:
import sqlite3
connection = sqlite3.connect("organization.db")
cursor = connection.cursor()
sqlcmd = """
SELECT * FROM Employee ORDER BY Eno DESC;
"""
cursor.execute(sqlcmd)
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')
In simple words: To show employee records from highest to lowest employee number, we write a Python script. It connects to the database, asks for all employee data sorted by `Eno` going downwards, and then prints the list.

🎯 Exam Tip: The `DESC` keyword in `ORDER BY` is essential for reverse (descending) sorting, which is important for displaying items from largest to smallest or newest to oldest.

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 that stores its data in standard files or in a computer's memory. It's built to be part of applications directly, rather than needing a separate server like MySQL. SQLite is known for being quick, well-tested, and versatile, which makes it easy to work with. Python has a built-in library for SQLite. Understanding these fundamental steps is crucial for interacting with any database, as the principles of connecting, querying, and committing changes apply broadly across different database systems.
To use SQLite in Python, follow these steps:
1. **Import the library:** Begin by importing the `sqlite3` module. (e.g., `import sqlite3`)
2. **Create a connection:** Use the `connect()` method, providing the database file's name. This opens an existing database or creates a new one if it doesn't exist. (e.g., `connection = sqlite3.connect("Academy.db")`)
3. **Set up a cursor:** Create a cursor object using `connection.cursor()`. This cursor is a control structure that allows you to move through and retrieve records from the database. (e.g., `cursor = connection.cursor()`)
The cursor plays a key role in Python for SQLite, as all SQL commands are executed through it. To create a table, you'll define the SQL `CREATE TABLE` command and execute it via the cursor. For any command execution, the cursor method is used, with the SQL command passed as a parameter. It's important to use the `commit()` command to save any changes made to the database, especially before closing the connection.
In simple words: SQLite is an easy database that lives in files. To use it in Python, first `import sqlite3`. Then, `connect` to a database file and make a `cursor` object. The cursor helps run SQL commands. Always `commit()` your changes to save them.

🎯 Exam Tip: When writing about SQLite, emphasize its serverless nature and the three core steps: connect, create a cursor, and execute commands.

 

Question 2. Write the Python script to display all the records of the following table using fetchmany().

Answer: To display all records from a table named `electronics` within the `supermarket.db` database using Python's `sqlite3` module and the `fetchmany()` method, follow these steps. First, establish a connection to the database and create a cursor. Then, execute an SQL `SELECT *` command to retrieve all data from the `electronics` table. Use `fetchmany(5)` (assuming 5 records) to get all the rows, and finally print them out. Using `fetchmany()` with a count equal to the total number of records is a way to retrieve all entries, though `fetchall()` is typically more direct for this purpose.
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)
In simple words: To show all records from the `electronics` table using `fetchmany()`, connect to the database, run a `SELECT ALL` command, then use `fetchmany(5)` to get all five rows, and print them.

🎯 Exam Tip: For `fetchmany()`, ensure the number passed as an argument matches the expected number of rows you want to retrieve in that batch.

 

Question 3. What is the use of HAVING clause. Give an example python script.
Answer: The `HAVING` clause is used in SQL to filter groups of rows based on conditions applied to aggregate functions (like `COUNT`, `SUM`, `AVG`, `MAX`, `MIN`). While similar to the `WHERE` clause, `HAVING` specifically works with grouped data and aggregate functions, which the `WHERE` clause cannot do. It helps narrow down results from grouped data. The distinction between `WHERE` and `HAVING` is crucial for advanced data querying, as `WHERE` filters individual rows before grouping, and `HAVING` filters groups after they have been formed.
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 = [description[0] for description in cursor.description]
print(co)
print(result)
Output:
['gender', 'COUNT(GENDER)']
[('M', 5)]
In simple words: The `HAVING` clause helps us filter groups of data after they have been summarized. It's like a `WHERE` clause, but it works on groups (like groups of genders) instead of single records, helping you find groups that meet certain conditions.

🎯 Exam Tip: Remember that `HAVING` is always used with `GROUP BY` and applies conditions to aggregated results, unlike `WHERE` which filters individual rows.

 

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 :-
Item Name : Character with length 25
Rate : Integer
Record to be added: 1008, Monitor, 15000
Answer: To create an `ITEM` table with specified columns and then insert a record into it using Python's `sqlite3` module, you first connect to `ABC.db`. Define the `CREATE TABLE` SQL command with `Icode` (INTEGER), `Item_Name` (VARCHAR(25)), and `Rate` (INTEGER). Execute this command. Next, define the `INSERT INTO` SQL command to add the record (1008, 'Monitor', 15000). Execute this insert command, then `commit()` the changes to save them permanently. Finally, query the table to display its content and close the connection. This script demonstrates the full lifecycle of basic database operations: creation, insertion, and retrieval, which are fundamental for managing data.
Coding:
import sqlite3
connection = sqlite3.connect("ABC.db")
cursor = connection.cursor()
sql_create_table_command = """
CREATE TABLE Item (
Icode INTEGER,
Item_Name VARCHAR(25),
Rate INTEGER
);"""
cursor.execute(sql_create_table_command)
print("Table Created")
sql_insert_command = """
INSERT INTO Item (Icode, Item_Name, Rate)
VALUES (1008, 'Monitor', 15000);
"""
cursor.execute(sql_insert_command)
connection.commit()
print("CONTENT OF THE TABLE :")
cursor.execute("SELECT * FROM Item")
result = cursor.fetchall()
print(*result, sep="\n")
connection.close()
Output:
Table Created
CONTENT OF THE TABLE :
(1008, 'Monitor', 15000)
In simple words: To make a new table called `ITEM` and add one item to it, we write a Python script. It creates the table with columns for `Icode`, `Item_Name`, and `Rate`, then puts in the item details (1008, 'Monitor', 15000), saves the changes, and shows what's in the table.

🎯 Exam Tip: Pay close attention to data types and lengths (e.g., `VARCHAR(25)`) when defining table columns to ensure data integrity.

 

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

Answer:
(i) Display Name, City and Itemname of suppliers who do not reside in Delhi:

Coding:
import sqlite3
connection = sqlite3.connect('ABC.db')
sqlcmd="""SELECT SUPPLIER.Name,SUPPLIER.City,Item.ItemName FROM
SUPPLIER,Item WHERE SUPPLIER.City NOT IN("Delhi") and
SUPPLIER.Icode=Item.Icode"""
cursor = connection.cursor()
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.connect('ABC.db')
cursor = connection.cursor()
sqlcmd="""UPDATE SUPPLIER
SET Suppqty=Suppqty+40 WHERE
Name='Akila'"""
cursor.execute(sqlcmd)
result = cursor.fetchall()
print
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)

In simple words: This Python script uses SQLite to manage supplier data. Part (i) finds suppliers not in Delhi and lists their names, cities, and items. Part (ii) updates the quantity of supplies for the supplier named Akila by adding 40 to their current quantity.

🎯 Exam Tip: When writing SQL queries in Python, always remember to `commit()` changes and `close()` the connection to save your work and release resources.

 

Additional Questions and Answers

I. Choose the best answer (1 Marks)

 

Question 1. ................ command is used to populate the table.
(a) ADD
(b) APPEND
(c) INSERT
(d) ADDROW
Answer: (c) INSERT
In simple words: To put new information into a table, you use the 'INSERT' command. It's like adding a new row to a spreadsheet.

🎯 Exam Tip: Remember that `INSERT` is used for adding new rows (records) to a table, while `UPDATE` changes existing data.

 

Question 2. Which has a native library for SQLite?
(a) C
(b) C++
(c) Java
(d) Python
Answer: (d) Python
In simple words: Python has a built-in library for SQLite, which means you can use SQLite databases directly with Python without needing extra tools.

🎯 Exam Tip: Python's `sqlite3` module makes working with SQLite databases very easy, as it's part of the standard library.

 

Question 3. ................ method is used to fetch all rows from the database table.
(a) fetch ()
(b) fetchrowsAll ()
(c) fectchmany ()
(d) fetchall ()
Answer: (d) fetchall ()
In simple words: The `fetchall()` method gets every single record that was found by your database query. It brings all the results at once.

🎯 Exam Tip: Use `fetchall()` when you need all the results at once. For very large datasets, consider `fetchone()` or `fetchmany()` to save memory.

 

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 ()
In simple words: The `fetchmany(n)` method lets you get a specific number of records (n) from your database results at a time, instead of all of them.

🎯 Exam Tip: `fetchmany(n)` is useful when you want to process data in smaller batches or display results page by page.

 

Question 5. ................ in the sql_comm?
(a) 1
(b) 2
(c) 3
(d) Many
Answer: (d) Many
In simple words: When talking about an SQL command, there are often many different parts or ways to use it.

🎯 Exam Tip: Be familiar with different parts of an SQL command and their various uses. This helps in understanding complex queries.

 

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
In simple words: The `WHERE` clause helps you find specific records in a table by setting a rule that they must follow. Only records that match the rule are shown.

🎯 Exam Tip: The `WHERE` clause is fundamental for filtering data; ensure you know how to use comparison operators (like =, <, >) and logical operators (AND, OR, NOT) effectively.

 

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
In simple words: To arrange your database results in a certain order, like from smallest to largest or alphabetically, you use the `ORDER BY` clause.

🎯 Exam Tip: Remember to specify `ASC` for ascending (default) or `DESC` for descending order with the `ORDER BY` clause to get the desired sort.

 

Question 8. ................ clause is used to filter data based on the group functions?
(b) HAVING
(c) ORDER
(d) FILTER
Answer: (b) HAVING
In simple words: The `HAVING` clause is like the `WHERE` clause, but it filters groups of data instead of individual rows, usually after calculations have been made on those groups.

🎯 Exam Tip: `HAVING` is always used with `GROUP BY` and filters results based on aggregate functions (like COUNT, SUM, AVG). `WHERE` filters individual rows before grouping.

 

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
In simple words: If you have an empty table and its primary key is set to automatically increase with an integer type, the very first record will get the value 1.

🎯 Exam Tip: SQLite's `INTEGER PRIMARY KEY` acts as an `AUTOINCREMENT` feature by default, starting from 1 for the first entry in an empty table.

 

Question 10. The sqlite3 module supports ........ kinds of placeholders:
(a) 1
(b) 2
(c) 3
(d) 5
Answer: (b) 2
In simple words: The `sqlite3` module in Python can use two types of placeholders in SQL queries: question marks (`?`) for unnamed values, and named placeholders (like `:name` or `$name`) for easier understanding.

🎯 Exam Tip: Understanding placeholders (`?` or `:name`) is crucial for preventing SQL injection attacks and for making your queries more readable and reusable.

 

Question 11. ................ has a native library of SQlite.
(a) Python
(b) C++
(c) Java
(d) C
Answer: (a) Python
In simple words: Python comes with its own built-in library for working with SQLite databases. This makes it very easy to use SQLite in Python programs without installing extra software.

🎯 Exam Tip: Being familiar with Python's standard library modules, like `sqlite3`, can greatly speed up development for common tasks such as database interaction.

 

Question 12. All the SQlite commands will be executed using.................. object only
(a) connect
(b) cursor
(c) CSV
(d) python
Answer: (b) cursor
In simple words: To send commands to an SQLite database and get results, you always need to use a 'cursor' object. It's like the hand that interacts with the database.

🎯 Exam Tip: The cursor object is essential for all database operations, including executing SQL statements and fetching results. Always create a cursor from your connection object.

 

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()
In simple words: The `fetchone()` method gets just one record at a time from your query results. It moves through the results one by one.

🎯 Exam Tip: `fetchone()` is useful for processing records one by one, especially when you expect only a single result or want to avoid loading a large dataset into memory all at once.

 

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
Answer: (b) count
In simple words: The `COUNT()` function is used to add up how many records match a certain rule you have set. It tells you the total number of items that fit your criteria.

🎯 Exam Tip: `COUNT(*)` counts all rows, while `COUNT(column_name)` counts non-NULL values in that column. `COUNT(DISTINCT column_name)` counts unique non-NULL values.

 

Question 15. Count () returns ..................... if there were no matching rows.
(a) 0
(b) 1
(c) NOT NULL
(d) NULL
Answer: (a) 0
In simple words: If you use the `COUNT()` function to find records that do not exist, it will show a count of 0, meaning no records were found.

🎯 Exam Tip: A `COUNT()` function always returns an integer, even if no rows match, in which case it returns 0, not NULL.

 

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
In simple words: The `cursor.description` property holds information about the columns in your query result, like their names and types.

🎯 Exam Tip: `cursor.description` is very useful for dynamically extracting column headers from a query result, which is helpful in building generic reporting tools.

 

Question 17. Which one of the following is used to print all elements separated by space?
(a) ,
(b) .
(c) :
(d) ;
Answer: (a) ,
In simple words: In Python's `print()` function, you can use a comma (`,`) between items to print them with a space in between by default.

🎯 Exam Tip: The `sep` argument in Python's `print()` function allows you to specify a custom separator, for example, `print(item1, item2, sep='-')`.

 

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

 

Question 1. Write the SQLite steps to connect the database.
Answer: The steps to connect to an SQLite database are:
Step 1: Import the `sqlite3` module.
Step 2: Create a connection by using the `connect()` method and providing the database file's name. If the database does not exist, a new one will be created.
Step 3: Set up a cursor object using `cursor = connection.cursor()`. This cursor is used to run SQL commands.
In simple words: First, you bring in the `sqlite3` tool. Next, you connect to your database file. If the file is not there, it makes a new one. Then, you create a cursor that lets you tell the database what to do.

🎯 Exam Tip: Always remember to import `sqlite3`, establish a `connection`, and create a `cursor` object in that sequence to interact with an SQLite database.

 

Question 2. Mention the frequently used clauses in SQL?
Answer: SQL provides several clauses that are often used with `SELECT` statements to manage data. These clauses can also be called through a Python script and are generally compatible with SQLite. Here are some of the most frequently used clauses:
1. `DISTINCT`: Used to remove duplicate rows from the result set.
2. `WHERE`: Filters rows based on a specified condition.
3. `GROUP BY`: Groups rows that have the same values in specified columns into summary rows.
4. `ORDER BY`: Sorts the result set in ascending or descending order.
5. `HAVING`: Filters groups based on a specified condition, typically used with `GROUP BY`.
In simple words: SQL has special words called "clauses" that help you pick, sort, and group data. Some common ones are `DISTINCT` to get unique items, `WHERE` to filter, `GROUP BY` to put similar items together, `ORDER BY` to sort, and `HAVING` to filter groups.

🎯 Exam Tip: Understand the purpose of each clause and their order of execution in a SQL query. `FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY` is a common logical sequence.

 

Question 3. Write a Python code to create a database in SQLite.
Answer: To create a database in SQLite using Python, you would write the following code:

import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()

In this example, a database named "Academy.db" would be created. This process is similar to using the SQL command `CREATE DATABASE Academy;`. If the database file "Academy.db" does not exist, `sqlite3.connect()` will create it automatically. If it already exists, it will connect to the existing database. A cursor object is then created to execute SQL commands.
In simple words: To make a new database in Python with SQLite, you first bring in the `sqlite3` tool. Then, you tell it to connect to a file, like "Academy.db". If that file isn't there, a new database file is made. Finally, you get a 'cursor' which is like a tool to type commands into the database.

🎯 Exam Tip: Creating a database with `sqlite3.connect()` is a simple yet powerful step. Remember that if the database file already exists, this command will connect to it, not overwrite it.

 

Question 4. Define: sqlite_master
Answer: `sqlite_master` is a special internal master table in an SQLite database. This table holds important information about all other tables, views, triggers, and indexes within that database. It's essentially a catalog that stores the schema (structure) of your entire database. Every SQLite database has one `sqlite_master` table.
In simple words: `sqlite_master` is a hidden table inside every SQLite database. It keeps a list of all the other tables and important things in that database, like their names and how they are set up.

🎯 Exam Tip: You can query `sqlite_master` to find out what tables exist in a database, their schema, and other database object details, which is very useful for database introspection.

 

Question 5. Give a short note on GROUP BY class.
Answer: The `GROUP BY` clause is an important part of SQL that works with the `SELECT` statement. It groups rows that have the same values in one or more specified columns into summary rows. This means that if you have many rows with the same value in a certain column, `GROUP BY` will combine them into one row. This clause is often used with aggregate functions like `COUNT`, `MAX`, `MIN`, `SUM`, and `AVG` to perform calculations on each group and return a single result for each group.
In simple words: The `GROUP BY` command in SQL helps you put similar data together. For example, if you have a list of students from different towns, `GROUP BY` can show you how many students are from each town. It helps summarize data for different categories.

🎯 Exam Tip: When using `GROUP BY`, any column in the `SELECT` statement that is not part of an aggregate function (like SUM, COUNT, AVG) must also be included in the `GROUP BY` clause.

 

Question 6. Write short notes on
1. COUNT ()
2. AVG ()
3. SUM ()
4. MAX ()
5. MIN ()
Answer: These are common aggregate functions used in SQL to perform calculations on a set of values and return a single summary value:
1. `COUNT ()` function: This function returns the total number of rows in a table or the number of rows that match a specified condition.
2. `AVG ()` function: This function retrieves the average value of a selected column of numbers from a table.
3. `SUM ()` function: This function retrieves the total sum of all values in a selected numeric column of a table.
4. `MAX ()` function: This function returns the largest (maximum) value found in a selected column.
5. `MIN ()` function: This function returns the smallest (minimum) value found in a selected column.
In simple words: These are tools to crunch numbers in your database. `COUNT` tells you how many things there are, `AVG` finds the average, `SUM` adds everything up, `MAX` finds the biggest number, and `MIN` finds the smallest.

🎯 Exam Tip: Aggregate functions are key for data analysis. Remember that they operate on a set of rows (or a group of rows if used with `GROUP BY`) and return a single value.

 

Question 7. Write a program to count the number of male and female students from the student table Example
Answer: To count the number of male and female students from a `student` table, you can use the following Python script with SQLite:

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)

This script connects to the "Academy.db" database, executes an SQL query to group students by their gender, and then counts the number of students in each gender group. The result shows that there are 2 female ('F') students and 5 male ('M') students. This helps to quickly summarize demographic data.
In simple words: This code connects to a student database. It then asks the database to group students by their gender and count how many students are in each group (male or female). The answer shows the number of female and male students.

🎯 Exam Tip: Using `GROUP BY` with `COUNT()` is a powerful way to get summary statistics, like gender distribution, from your database tables efficiently.

 

Question 8. Explain Deletion Operation with a suitable example.
Answer: The deletion operation in a database is used to remove one or more existing records (rows) from a table. Similar to a basic SQL `DELETE` command, Python also provides a way to delete records using the `execute()` method with a `DELETE` SQL statement. Here is an example of Python code to delete a specific record from a "student" table, specifically the record with `Rollno` 2:

Coding:
# code for delete operation
import sqlite3
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()

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

In this example, after deleting the record with `Rollno` 2, the `conn.total_changes` property confirms that 1 row was removed. Then, the remaining records in the `Student` table are displayed, showing that the record for `Rollno` 2 is no longer present. This is a crucial operation for maintaining data accuracy.
In simple words: Deleting means taking out information you don't need from a database. This example shows how to use Python to remove a student's record with a specific ID number. After the deletion, the code shows the remaining student records, and the deleted one is gone.

🎯 Exam Tip: Always use a `WHERE` clause with `DELETE` to avoid accidentally removing all records from your table. Remember to `commit()` changes after deletion to make them permanent.

 

Question 9. Explain Table List with suitable example.
Answer: Listing tables in a database allows you to see all the tables that have been created within it. This is useful for understanding the database structure. In SQLite, this information is stored in the special `sqlite_master` table. You can query this table to get a list of all user-defined tables. Here is a Python program to display a 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',)]

This script connects to "Academy.db", creates a cursor, and then executes a SQL query to select the `name` column from `sqlite_master` where the `type` is 'table'. The `fetchall()` method then retrieves all the table names, which are printed as a list of tuples. This allows a quick overview of the database's content.
In simple words: To see what tables are in your database, you can use a Python program. This code connects to the database, then looks inside a special hidden table called `sqlite_master` to find all the table names and then lists them out for you.

🎯 Exam Tip: Querying `sqlite_master` is the standard way to inspect the schema of an SQLite database. It provides a comprehensive list of all database objects.

 

Question 10. Write a short note on cursor. fetchall(),cursor.fetchone(), cursor. fetchmany()
Answer: These are three important methods of the cursor object in SQLite (and other database APIs) used for retrieving data from a query result set:
* `cursor.fetchall()`: This method is used to fetch all remaining rows of a query result set at once. It returns a list of tuples, where each tuple represents a row from the database table. If no rows are available, it returns an empty list.
* `cursor.fetchone()`: This method returns the next single row of a query result set. It returns a single tuple, or `None` if there are no more rows left to fetch. This is useful for processing results row by row.
* `cursor.fetchmany(size)`: This method returns the next specified number of rows (`size`) from the query result set. It returns a list of tuples, similar to `fetchall()`, but only up to the specified `size`. If `size` is not provided, `arraysize` is used. If fewer rows than `size` remain, it returns all remaining rows.
In simple words: `fetchall()` gets all the results from a query together. `fetchone()` gets only the very next result one by one. `fetchmany(number)` gets a specific amount of results at a time, like getting 10 rows from a big list.

🎯 Exam Tip: Choose the appropriate fetch method based on your needs: `fetchall()` for small result sets, `fetchone()` for single-row processing, and `fetchmany()` for batch processing large result sets.

 

Question 11. How to create a database using SQLite? Creating a Database using SQLite:
Answer: To create a database using SQLite in Python, you simply need to establish a connection to a database file. If the file specified in the connection does not exist, SQLite will automatically create it. This is how it works with Python:

# 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 example above, a database named "Academy.db" would be created. This method is similar to an SQL command like `CREATE DATABASE Academy;`, but handled programmatically through the Python `sqlite3` module. The connection object represents the database, and the cursor object is used to execute SQL statements within that database.
In simple words: To make a new database with SQLite in Python, you just tell Python to connect to a new database file name. If that file doesn't exist, Python will create it for you. Then you get a 'cursor' to start telling the database what to do.

🎯 Exam Tip: Remember that `sqlite3.connect('database_name.db')` creates a new database if `database_name.db` doesn't exist, or connects to it if it does. There's no separate `CREATE DATABASE` command in the `sqlite3` module.

 

Question 13. Explain fetchone() to display a single record(one row) with a suitable example?
Answer: The `fetchone()` method is used to get just one row of data from the results of a database query. It returns the very next record in the list. If there are no more records, it returns `None`. This method is helpful when you only need to process data one record at a time. For example, to connect to a database and fetch a single student record, you would use this method.
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')
In simple words: This method helps you pick out only one data entry (row) at a time from a list of results. If there's nothing left, it says 'no more'.

🎯 Exam Tip: Remember that `fetchone()` moves the cursor forward. To see all records, you need to call it multiple times or use a loop.

 

Question 14. Explain fetchone() to display all records with suitable examples?
Answer: To show all records from a database table using `fetchone()`, we can use it inside a `while` loop. The loop keeps running and fetching one record at a time until `fetchone()` returns `None`, indicating there are no more records to retrieve. This is useful for processing each row individually. For instance, a Python script can connect to a student database and print each student's details one by one.
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')
In simple words: You can use `fetchone()` with a loop to get every single data entry from your table, one by one, until there are none left to show.

🎯 Exam Tip: When using `fetchone()` in a loop, always remember to update the `result` variable inside the loop to fetch the next row and avoid an infinite loop.

 

Question 15. Explain fetchmany() to display a specified number of records with suitable example?
Answer: The `fetchmany()` method is used to retrieve a specific number of records (rows) from the result of a database query. You pass a number `n` to `fetchmany(n)`, and it returns the next `n` rows as a list of tuples. This is helpful when you want to process data in smaller batches. For instance, a Python script can connect to a student database and fetch a certain number of student records at once.
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, 'Aravind', 'A', 'M', 92.5, '2000-08-17')
(3, 'BASKAR', 'C', 'M', 75.2, '1998-05-17')
In simple words: This method lets you grab a set number of data entries (like 3 or 5) all at once from your results. You tell it how many you want, and it gives you that many.

🎯 Exam Tip: Remember that `fetchmany(n)` returns a list of tuples. If fewer than `n` rows are left, it will return all the remaining rows. If no rows are left, it returns an empty list.

II. Answer the Following Questions (5 Marks)

 

Question 1. Explain clauses in SQL with suitable examples.
Answer: SQL provides many special commands, called clauses, that help us work with `SELECT` statements in databases. These clauses can also be used in Python programs to interact with SQLite databases. They make it easier to find, sort, and group data. The various clauses include:

• **i) DISTINCT Clause:** This clause helps us find only the unique values in a column, removing any duplicate entries. If a table has the same value multiple times, `DISTINCT` will show it only once.


• *Example:* To see all the different grades students scored, we can use a Python script as follows:
  import sqlite3
connection = sqlite3.connect("Academy.db")
cursor = connection.cursor()
cursor.execute("SELECT DISTINCT (Grade) FROM student")
result = cursor.fetchall()
print(result)
  *Output:*
  [('B',), ('A',), ('C',), ('D',)]
  Without `DISTINCT`, all 7 records (including duplicates) would be shown.


• **ii) WHERE Clause:** We use the `WHERE` clause to pick out only specific records that meet a certain condition. It acts like a filter.


• *Example:* To find grades of male students, we can use:
  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',)


• **iii) GROUP BY Clause:** This clause is used with `SELECT` to group similar rows into summary rows. It's often used with functions like `COUNT`, `MAX`, `MIN`, `SUM`, or `AVG` to get summary information for each group.


• *Example:* To count how many male and female students there are, we can use:
  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)


• **iv) ORDER BY Clause:** The `ORDER BY` clause sorts the data in our results. We can arrange records in ascending (A-Z, 0-9) or descending (Z-A, 9-0) order based on one or more columns.


• *Example:* To list students by their names in alphabetical order, we can use:
  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) HAVING Clause:** This clause is like `WHERE` but it works specifically with grouped data (after `GROUP BY`). It filters groups based on a condition, rather than individual rows. Group functions, like `COUNT` or `SUM`, cannot be used directly in a `WHERE` clause, but they can be used in a `HAVING` clause.


• *Example:* To show genders only if there are more than 3 students of that gender, we can use:
  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)]

In simple words: SQL clauses are special words that help you control what data you see from your database. `DISTINCT` shows only unique things, `WHERE` picks specific rows, `GROUP BY` puts similar rows together, `ORDER BY` sorts them, and `HAVING` filters these groups.

🎯 Exam Tip: Understand the key difference between `WHERE` and `HAVING`: `WHERE` filters individual rows before grouping, while `HAVING` filters groups after they have been formed, often using aggregate functions.

 

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: This Python program shows how to create a database table, collect student details (name, age, and ID) from the user while the program is running, and then add these details to the table. After saving the information, the program retrieves and displays all the student records that were entered. The program connects to a SQLite database named 'Academy.db' and performs these operations.
#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_d[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
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)
In simple words: This program asks you for 5 student names, ages, and IDs. It then puts this information into a table and shows you everything saved in that table.

🎯 Exam Tip: When accepting runtime input, ensure proper data type conversion (like `int()` for age and ID) to avoid errors in your database operations, especially when inserting numerical data.

 

Question 3. Write a Python program to store and retrieve the following data in SQLite3. Database Schema:
Answer: This Python program demonstrates how to manage data in an SQLite3 database. It first defines a table schema for student information, including Roll number, name, gender, and average marks, with Roll number as the primary key. Then, the program creates this table in a database named 'Academy.db'. After the table is set up, it inserts five specific student records into the table. Finally, it retrieves and displays all the data from the 'Student' table to confirm the insertions.

Data to be inserted as tuple:

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(1), gender CHAR(1), Average DECIMAL (5,
2));"""
cursor.execute(sql_command)
sql_command = """INSERT INTO Student VALUES (1001, "KULOTHUNGAN", "M",
"75.2");"""
cursor.execute (sql_command)
sql_command = """INSERT INTO Student VALUES (1002, "KUNDAVAI", "F",
"80.6");"""
cursor.execute (sql_command)
sql_command = """INSERT INTO Student VALUES (1003, "RAJARAJAN", "M",
"80.6");"""
cursor.execute (sql_command)
sql_command = """INSERT INTO Student VALUES (1004, "RAJENDRAN", "M",
"98.6");"""
cursor.execute (sql_command)
sql_command = """INSERT INTO Student VALUES (1005, "AVVAI", "F", "70.1");"""
cursor.execute (sql_command)
connection.commit()
connection.close()
In simple words: This program sets up a student table in a database, adds a list of student details into it, and then shows you all the student data saved in that table.

🎯 Exam Tip: Always remember to `commit()` changes to the database after `INSERT` or `UPDATE` operations, and `close()` the connection when done to save your data properly.