Scribd
Upload
46 views4 pages

Class 12 SQL Practical File Guide

The document outlines SQL commands for creating a database named SchoolDB and five tables: STUDENT, TEACHER, LIBRARY, FEES, and ATTENDANCE. It includes examples of inserting, deleting, and selecting data, as well as using various SQL clauses such as ORDER BY, WHERE, and GROUP BY. Additionally, it demonstrates the use of aggregate functions to analyze student marks.

Uploaded by

hiralal141164
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
46 views4 pages

Class 12 SQL Practical File Guide

The document outlines SQL commands for creating a database named SchoolDB and five tables: STUDENT, TEACHER, LIBRARY, FEES, and ATTENDANCE. It includes examples of inserting, deleting, and selecting data, as well as using various SQL clauses such as ORDER BY, WHERE, and GROUP BY. Additionally, it demonstrates the use of aggregate functions to analyze student marks.

Uploaded by

hiralal141164
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 IP Practical File - SQL Section

1. Create Database

CREATE DATABASE SchoolDB;

USE SchoolDB;

2. Create FIVE Tables

-- STUDENT Table

CREATE TABLE STUDENT (

RollNo INT PRIMARY KEY,

Name VARCHAR(50),

Class INT,

Section CHAR(1),

Marks INT

);

-- TEACHER Table

CREATE TABLE TEACHER (

TID INT PRIMARY KEY,

TName VARCHAR(50),

Subject VARCHAR(30),

Salary INT

);

-- LIBRARY Table

CREATE TABLE LIBRARY (

BookID INT PRIMARY KEY,

Title VARCHAR(100),

Author VARCHAR(50),

Price DECIMAL(6,2)

);
Class 12 IP Practical File - SQL Section

-- FEES Table

CREATE TABLE FEES (

RollNo INT,

AmountPaid DECIMAL(8,2),

PaymentDate DATE,

Mode VARCHAR(20),

FOREIGN KEY (RollNo) REFERENCES STUDENT(RollNo)

);

-- ATTENDANCE Table

CREATE TABLE ATTENDANCE (

RollNo INT,

Date DATE,

Status CHAR(1),

FOREIGN KEY (RollNo) REFERENCES STUDENT(RollNo)

);

3. Insert Queries

INSERT INTO STUDENT VALUES (101, 'Riya Sharma', 12, 'A', 85);

INSERT INTO STUDENT VALUES (102, 'Amit Verma', 12, 'B', 72);

INSERT INTO TEACHER VALUES (1, 'Mrs. Gupta', 'Maths', 45000);

INSERT INTO TEACHER VALUES (2, 'Mr. Singh', 'IP', 50000);

INSERT INTO LIBRARY VALUES (201, 'Python Programming', 'Sumita Arora', 350.00);

INSERT INTO LIBRARY VALUES (202, 'Data Science Basics', 'A.K. Sharma', 450.00);

INSERT INTO FEES VALUES (101, 15000.00, '2025-04-05', 'Online');

INSERT INTO FEES VALUES (102, 15000.00, '2025-04-10', 'Cash');

INSERT INTO ATTENDANCE VALUES (101, '2025-07-01', 'P');


Class 12 IP Practical File - SQL Section

INSERT INTO ATTENDANCE VALUES (102, '2025-07-01', 'A');

4. Delete Query

DELETE FROM STUDENT WHERE RollNo = 102;

5. Drop Query

DROP TABLE ATTENDANCE;

6. Select Queries

SELECT * FROM STUDENT;

SELECT Name, Marks FROM STUDENT;

7. ORDER BY Clause

SELECT * FROM STUDENT ORDER BY Marks DESC;

8. ALL CONDITION CLAUSES

-- WHERE

SELECT * FROM TEACHER WHERE Salary > 40000;

-- AND

SELECT * FROM STUDENT WHERE Class = 12 AND Section = 'A';

-- OR

SELECT * FROM STUDENT WHERE Section = 'A' OR Section = 'B';

-- BETWEEN

SELECT * FROM LIBRARY WHERE Price BETWEEN 300 AND 400;


Class 12 IP Practical File - SQL Section

-- IN

SELECT * FROM STUDENT WHERE RollNo IN (101, 103);

-- LIKE

SELECT * FROM TEACHER WHERE TName LIKE 'M%';

9. GROUP BY Clause

SELECT Section, COUNT(*) FROM STUDENT GROUP BY Section;

10. UPDATE Query

UPDATE STUDENT SET Marks = 90 WHERE RollNo = 101;

11. Aggregate Functions

SELECT MIN(Marks) FROM STUDENT;

SELECT MAX(Marks) FROM STUDENT;

SELECT AVG(Marks) FROM STUDENT;

SELECT SUM(Marks) FROM STUDENT;

Common questions

Powered by AI

Foreign keys in the 'FEES' and 'ATTENDANCE' tables, referencing the 'RollNo' of the 'STUDENT' table, ensure referential integrity. They establish parent-child relationships between these tables and the 'STUDENT' table, meaning a record must exist in 'STUDENT' before entries in 'FEES' or 'ATTENDANCE' are valid. This prevents orphan records and maintains coherence within the database .

The 'TEACHER' table is structured with columns such as 'TID', 'TName', 'Subject', and 'Salary', allowing efficient organization of teacher data. 'TID' serves as a primary key ensuring each teacher is uniquely identifiable. This facilitates effective management and retrieval of teacher records. However, potential drawbacks could include the lack of contact information fields, which might limit communication ability with the teachers .

The IN clause in SQL simplifies querying by checking if a column's value is within a specified list. This reduces the need for multiple OR conditions. For the 'STUDENT' table, 'SELECT * FROM STUDENT WHERE RollNo IN (101, 103);' quickly retrieves records with specific roll numbers, enhancing query readability and execution efficiency .

The LIKE operator in SQL is used for pattern matching within string fields, offering a flexible way to filter records. In the 'TEACHER' table, 'SELECT * FROM TEACHER WHERE TName LIKE 'M%';' retrieves records where teacher names start with 'M'. While powerful for searching based on partial matches, LIKE can be inefficient on large datasets due to its demand on processing resources and potential to miss data if patterns are not well-defined .

The DELETE query removes specific records from a table based on a condition without affecting its structure, as demonstrated by 'DELETE FROM STUDENT WHERE RollNo = 102;'. It allows selective data management while preserving the table's integrity for future data use. In contrast, the DROP query eliminates the entire table along with all its data, as shown with 'DROP TABLE ATTENDANCE;'. This action is irreversible and should be used with caution to avoid data loss .

The 'ORDER BY' clause in SQL is used to sort the results of a query in either ascending or descending order based on one or multiple columns. For instance, sorting student records by 'Marks' in descending order allows educators to quickly identify top-performing students. The query 'SELECT * FROM STUDENT ORDER BY Marks DESC;' does exactly this, providing a structured, prioritized list of students from highest to lowest marks .

The 'STUDENT' table ensures data integrity by using the 'RollNo' column as a primary key. This enforces uniqueness across all entries, preventing duplicate roll numbers and ensuring each student is uniquely identifiable. Additionally, it maintains referential integrity with other tables like 'FEES' and 'ATTENDANCE' via foreign key relationships .

An UPDATE query changes existing records, ensuring data remains current and accurate. In the 'STUDENT' table, 'UPDATE STUDENT SET Marks = 90 WHERE RollNo = 101;' modifies Riya Sharma's marks. This capability is critical for correcting errors and reflecting new information, such as a re-evaluated exam score, which contributes to maintaining reliable and up-to-date data .

Aggregate functions like MIN, MAX, AVG, and SUM offer significant benefits in decision-making by providing insights such as the range of marks, average performance, and total marks obtained by all students. For instance, SELECT AVG(Marks) FROM STUDENT; calculates the average marks, aiding in assessing overall student performance. However, limitations include a loss of detailed individual data and dependency on accurate, up-to-date entries to ensure meaningful results .

The 'GROUP BY' clause in SQL collects data across multiple records and groups them into rows corresponding to columns that share common values, often used with aggregate functions. In the 'STUDENT' table, 'SELECT Section, COUNT(*) FROM STUDENT GROUP BY Section;' groups students by their section and counts the number of students in each section, providing insights into class distribution .

You might also like