Scribd
Upload
16 views2 pages

MySQL & Python SQL Programs for Class 12

The document outlines a practical list for Class 12 focusing on MySQL programs and Python SQL connectivity for the academic year 2025-26. It includes the creation and manipulation of various database tables such as TEAM, STUDENT, Employee, and JOBS, along with Python functions for inserting, displaying, searching, updating, and deleting employee records. Each program demonstrates SQL commands and Python integration to manage and query the databases effectively.

Uploaded by

jayantmittal1978
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
16 views2 pages

MySQL & Python SQL Programs for Class 12

The document outlines a practical list for Class 12 focusing on MySQL programs and Python SQL connectivity for the academic year 2025-26. It includes the creation and manipulation of various database tables such as TEAM, STUDENT, Employee, and JOBS, along with Python functions for inserting, displaying, searching, updating, and deleting employee records. Each program demonstrates SQL commands and Python integration to manage and query the databases effectively.

Uploaded by

jayantmittal1978
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Class 12 – MySQL Programs & Python SQL

Connectivity (Practical List 2025-26)

Program 1 — Sports Database & TEAM Table


CREATE DATABASE Sports;
USE Sports;
CREATE TABLE TEAM (TeamID INT CHECK (TeamID BETWEEN 1 AND 9), TeamName
VARCHAR(50) CHECK (CHAR_LENGTH(TeamName) >= 10), PRIMARY KEY(TeamID));
DESCRIBE TEAM;
INSERT INTO TEAM VALUES (1,'Team Titan');
INSERT INTO TEAM VALUES (2,'Team Rockers');
INSERT INTO TEAM VALUES (3,'Team Magnet');
INSERT INTO TEAM VALUES (4,'Team Hurricane');
SELECT * FROM TEAM;

Program 2 — STUDENT Table + ALTER/MODIFY/DROP


CREATE TABLE STUDENT (Student_No INT PRIMARY KEY, Name CHAR(5), GAME
VARCHAR(30), Grade1 CHAR(1), SUPW VARCHAR(30), Grade2 CHAR(1), Address
VARCHAR(100));
INSERT INTO STUDENT VALUES (1,'Aman ','Cricket','A','Robotics','B','Delhi');
INSERT INTO STUDENT VALUES (2,'Ria ','Football','C','Art','C','Mumbai');
INSERT INTO STUDENT VALUES (3,'Asha ','Badminton','B','Music','A','Noida');
INSERT INTO STUDENT VALUES (4,'Vik ','Cricket','C','Dance','B','Ghaziabad');
INSERT INTO STUDENT VALUES (5,'Neil ','Football','B','Coding','A','Faridabad');
ALTER TABLE STUDENT ADD COLUMN class VARCHAR(10);
ALTER TABLE STUDENT MODIFY Name VARCHAR(10);
ALTER TABLE STUDENT DROP COLUMN Address;
SELECT Name FROM STUDENT WHERE Grade1='C' OR Grade2='C';
SELECT Name FROM STUDENT ORDER BY Name DESC;
SELECT DISTINCT GAME FROM STUDENT;
SELECT SUPW FROM STUDENT WHERE Name LIKE 'A%';

Program 3 — Employee & Salary Table


CREATE TABLE Employee (Eid VARCHAR(5) PRIMARY KEY, Name VARCHAR(100), Depid INT,
Qualification VARCHAR(50), Sex CHAR(1));
CREATE TABLE Salary (Eid VARCHAR(5), Basic INT, D_A INT, HRA INT, Bonus INT, FOREIGN
KEY(Eid) REFERENCES Employee(Eid));
SELECT Depid, COUNT(*) FROM Employee GROUP BY Depid;
SELECT Name FROM Employee WHERE Name LIKE 'H%';
ALTER TABLE Salary ADD COLUMN Total_Sal INT;
UPDATE Salary SET Total_Sal = Basic + D_A + HRA + Bonus;
SELECT MAX(Basic) FROM Salary WHERE Bonus > 40;
SELECT Sex, COUNT(*) FROM Employee GROUP BY Sex;
SELECT DISTINCT Depid FROM Employee;

Program 4 — EMPLOYEE + JOBS Join Queries


CREATE TABLE JOBS (JobID INT PRIMARY KEY, JobTitle VARCHAR(50));
CREATE TABLE EMPLOYEE (EmpID VARCHAR(5) PRIMARY KEY, Name VARCHAR(100),
JobID INT, Sales INT, FOREIGN KEY(JobID) REFERENCES JOBS(JobID));
SELECT [Link], [Link], [Link], [Link] FROM EMPLOYEE e JOIN JOBS j ON
[Link]=[Link];
SELECT [Link], [Link], [Link] FROM EMPLOYEE e JOIN JOBS j ON [Link]=[Link]
WHERE [Link] > 1300000;
SELECT [Link], [Link] FROM EMPLOYEE e JOIN JOBS j ON [Link]=[Link] WHERE
[Link] LIKE '%SINGH%';
UPDATE EMPLOYEE SET JobID=104 WHERE EmpID='E4';

Python Program 1 — Insert & Display Employees


import [Link]
def connect(): return [Link](host='localhost', user='root',
password='your_password', database='Sports')
def insert_employee(eid,name,dept): conn=connect(); cur=[Link](); [Link]('INSERT
INTO Employee VALUES (%s,%s,%s)',(eid,name,dept)); [Link](); [Link]()
def display(): conn=connect(); cur=[Link](); [Link]('SELECT * FROM Employee');
print([Link]()); [Link]()

Python Program 2 — Search Employee


def search_emp(eid): conn=connect(); cur=[Link](); [Link]('SELECT * FROM Employee
WHERE Eid=%s',(eid,)); row=[Link](); print(row if row else 'Not Found'); [Link]()

Python Program 3 — Update Employee


def update_emp(eid,new_name): conn=connect(); cur=[Link](); [Link]('UPDATE
Employee SET Name=%s WHERE Eid=%s',(new_name,eid)); [Link](); [Link]()

Python Program 4 — Delete Employee


def delete_emp(eid): conn=connect(); cur=[Link](); [Link]('DELETE FROM Employee
WHERE Eid=%s',(eid,)); [Link](); [Link]()

Common questions

Powered by AI

'SELECT' and 'JOIN' queries are critical in relational databases as they allow for comprehensive data retrieval. 'SELECT' queries retrieve specific data based on conditions, while 'JOIN' operations enable combining rows from two or more tables based on related columns. This allows complex queries that can extract meaningful connections across tables, such as retrieving full employee records with specific jobs, optimizing the efficiency and comprehensiveness of data analysis .

The GROUP BY clause enhances SQL queries by allowing aggregation of data around one or more columns, facilitating summary statistics like COUNT, SUM, AVG, etc. This is essential for data analytics, as it allows reporting on distinct groups within datasets. For instance, grouping Employee data by Depid can provide insights into departmental size. By summarizing data, it supports data-driven decision-making, enabling queries that analyze trends and patterns rather than just raw data retrieval .

Using VARCHAR allows storage flexibility as it occupies only as much space as needed for the string, potentially saving storage space. This is beneficial when the field length is highly variable. However, it can lead to fragmentation in disk storage, lowering performance in some queries. CHAR, a fixed-length datatype, ensures uniform storage space, which can enhance query performance due to alignment but may waste space for shorter entries. The choice depends on balance between space efficiency and retrieval performance .

Python functions facilitate interaction with MySQL databases by providing an interface for CRUD operations—Create, Read, Update, Delete. They enable dynamic data manipulation, such as inserting, updating, or deleting entries in tables. These functions help automate and streamline database tasks, allowing modifications via scripts, which increases speed, reduces manual errors, and provides repeatable operations. For example, insert_employee and delete_emp functions execute SQL commands directly through Python, enhancing programming flexibility and control .

In the Employee and Salary tables, primary and foreign keys establish essential links between related records. The Eid field is a primary key in the Employee table, which ensures each employee record is unique. The Salary table also uses the Eid field as a foreign key, which references the Employee table's Eid, establishing a relationship that ensures each salary entry corresponds to a valid employee record, thus enforcing referential integrity .

The 'UPDATE' SQL command is pivotal for recalculating fields to ensure data accuracy and relevance. For instance, in the Salary table, the 'UPDATE' command recalculates the 'Total_Sal' field by summing up Basic, D_A, HRA, and Bonus. This recalculation can automatically adjust total salary figures across all entries, keeping the data current without manual adjustments. Such practices enhance operational efficiency and data integrity, vital for maintaining accurate financial records .

Data type constraints in relational databases dictate how data is stored, influencing both efficiency and accuracy. They define the type of data that can be stored in each field, such as INT, VARCHAR, or CHAR, which affects storage space allocation and retrieval speed. For example, in the STUDENT table, using CHAR(1) for grades ensures minimal storage for small data, while VARCHAR allows variable length storage, decreasing waste when field lengths differ. These constraints guarantee data integrity by restricting entry types to valid formats .

The TEAM table in the 'Sports' database enforces data integrity constraints through two main mechanisms: it uses a CHECK constraint on the TeamID to ensure values are between 1 and 9, and it applies a CHECK constraint on TeamName requiring it to have a minimum character length of 10. Additionally, it designates the TeamID as the PRIMARY KEY to ensure uniqueness, preventing duplicate entries of teams .

The 'ALTER TABLE' command is versatile and can be used to add new columns, modify existing columns, or drop columns from a table. For instance, in the STUDENT table, the command was employed to add a new column 'class', modify the 'Name' column to allow VARCHAR(10), and drop the 'Address' column, demonstrating its flexibility in restructuring database tables .

FOREIGN KEY constraints in database tables provide substantial advantages, such as enforcing referential integrity by linking tables through keys, which ensures that relationships between tables remain consistent. However, they also pose challenges, such as constraints leading to complex locking scenarios during updates or deletions, potentially impacting performance. They require careful planning of database architecture to avoid cyclical dependencies that can complicate data insertion or deletion workflows .

You might also like