Class 12 – CS 083 Practical List – Database Management

by

Class 12 – Practical – Database Management

1. Create a student table and insert data. Implement the following SQL commands on the student table:

  • ALTER table to add new attributes / modify data type / drop attribute
  • UPDATE table to modify data ORDER By to display data in ascending / descending order
  • DELETE to remove tuple(s) GROUP BY and find the min, max, sum, count and average

SQL commands for creating a student table, inserting data, and performing the requested operations:

Create a Student Table and Insert Data

CREATE TABLE IF NOT EXISTS Students (
    StudentID INT PRIMARY KEY,
    FirstName VARCHAR(255),
    LastName VARCHAR(255),
    Age INT,
    GPA DECIMAL(3, 2)
);

INSERT INTO Students (StudentID, FirstName, LastName, Age, GPA)
VALUES
    (1, 'John', 'Doe', 18, 3.75),
    (2, 'Jane', 'Smith', 19, 3.88),
    (3, 'Bob', 'Johnson', 20, 3.65);

ALTER TABLE to Add New Attributes

ALTER TABLE Students
ADD COLUMN Major VARCHAR(255);

ALTER TABLE to Modify Data Type

ALTER TABLE Students
MODIFY COLUMN GPA FLOAT;

ALTER TABLE to Drop an Attribute

ALTER TABLE Students
DROP COLUMN Major;

UPDATE Table to Modify Data

UPDATE Students
SET GPA = 3.90
WHERE StudentID = 1;

ORDER BY to Display Data in Ascending Order

SELECT *
FROM Students
ORDER BY GPA ASC;

ORDER BY to Display Data in Descending Order

SELECT *
FROM Students
ORDER BY GPA DESC;

DELETE to Remove a Tuple

DELETE FROM Students
WHERE StudentID = 3;

GROUP BY and Aggregate Functions (MIN, MAX, SUM, COUNT, AVG)

SELECT
    MIN(GPA) AS MinGPA,
    MAX(GPA) AS MaxGPA,
    SUM(GPA) AS TotalGPA,
    COUNT(*) AS StudentCount,
    AVG(GPA) AS AvgGPA
FROM Students;

2. Integrate SQL with Python by importing suitable module.

To integrate SQL with Python, you can use the sqlite3 module, which is included in the Python standard library and provides a simple way to work with SQLite databases. Here’s an example of how to create a SQLite database, create a student table, insert data, and perform SQL operations in Python:

import sqlite3

# Connect to the SQLite database (creates a new database if it doesn't exist)
connection = sqlite3.connect("student_database.db")

# Create a cursor object to execute SQL commands
cursor = connection.cursor()

# Create a student table
cursor.execute('''CREATE TABLE IF NOT EXISTS Students (
                    StudentID INTEGER PRIMARY KEY,
                    FirstName TEXT,
                    LastName TEXT,
                    Age INTEGER,
                    GPA REAL
                )''')

# Insert data into the student table
data = [
    (1, 'John', 'Doe', 18, 3.75),
    (2, 'Jane', 'Smith', 19, 3.88),
    (3, 'Bob', 'Johnson', 20, 3.65)
]

cursor.executemany('INSERT INTO Students VALUES (?, ?, ?, ?, ?)', data)

# Commit the changes to the database
connection.commit()

# Perform SQL operations
cursor.execute("SELECT * FROM Students WHERE GPA > 3.7")
results = cursor.fetchall()

for row in results:
    print(row)

# Close the cursor and the database connection
cursor.close()
connection.close()

In this Python code:

  • We import the sqlite3 module.
  • We connect to the SQLite database using sqlite3.connect() and create a cursor object.
  • We create the student table, insert data, and commit the changes to the database.
  • We execute SQL commands, in this case, selecting students with a GPA greater than 3.7 and printing the results.
  • Finally, we close the cursor and the database connection.

This example demonstrates how to integrate SQL with Python using the sqlite3 module. You can adapt this code for other database systems by changing the connection method and SQL syntax accordingly.

Leave a Comment