Practical 1
Aim: To create a "Student" table with student id, name, and
marks as attributes where the student id is the primary key.
Theory
A table is a collection of related data organized in rows and columns.
The CREATE TABLE command is a Data Definition Language (DDL) command used to
create a new table in a database.
A Primary Key:
• Uniquely identifies each record
• Cannot contain duplicate values
• Cannot contain NULL values
Query
CREATE DATABASE School;
USE School;
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Marks INT
);
Result
The Student table is created successfully.
👇 Write these on the blank page of your practical notebook as output
Output
Query OK, 0 rows affected
Practical 2
Aim: To insert the details of a new student in the “Student”
table.
Theory
The INSERT INTO command is a Data Manipulation Language (DML) command.
It is used to add records into a table.
Each record represents one row of data.
Query
INSERT INTO Student
VALUES
(1, 'Rahul', 78),
(2, 'Ananya', 92),
(3, 'Vikas', 65),
(4, 'Sneha', 88),
(5, 'Arjun', 55);
SELECT * FROM Student;
Result
Records are inserted successfully into the Student table.
👇 Write these on the blank page of your practical notebook as output
Output
Query OK, 5 rows affected
+-----------+--------+-------+
| StudentID | Name | Marks |
+-----------+--------+-------+
| 1 | Rahul | 78 |
| 2 | Ananya | 92 |
| 3 | Vikas | 65 |
| 4 | Sneha | 88 |
| 5 | Arjun | 55 |
+-----------+--------+-------+
Practical 3
Aim: To delete the details of a student from the “Student” table.
Theory
The DELETE command is a DML command used to remove records from a table.
The WHERE clause specifies which record should be deleted.
⚠ Without WHERE → All records may get deleted.
Query
DELETE FROM Student
WHERE StudentID = 5;
SELECT * FROM Student;
Result
The GST and final price are calculated successfully.
👇 Write these on the blank page of your practical notebook as output
Output
Query OK, 1 row affected
+-----------+--------+-------+
| StudentID | Name | Marks |
+-----------+--------+-------+
| 1 | Rahul | 78 |
| 2 | Ananya | 92 |
| 3 | Vikas | 65 |
| 4 | Sneha | 88 |
+-----------+--------+-------+
Practical 4
Aim: To display the details of students with marks more than
80.
Theory
The SELECT command is used to retrieve data from a table.
The WHERE clause filters records based on conditions.
Relational operators are used for comparison.
Query
SELECT *
FROM Student
WHERE Marks > 80;
Result
Students scoring more than 80 marks are displayed successfully.
👇 Write these on the blank page of your practical notebook as output
Output
+-----------+--------+-------+
| StudentID | Name | Marks |
+-----------+--------+-------+
| 2 | Ananya | 92 |
| 4 | Sneha | 88 |
+-----------+--------+-------+
Practical 5
Aim: To find the minimum, maximum, sum, and average of
marks in the “Student” table.
Theory
Aggregate functions are used to perform calculations on multiple rows of data and return a
single result.
Common aggregate functions:
• MIN() → Returns minimum value
• MAX() → Returns maximum value
• SUM() → Returns total sum
• AVG() → Returns average value
These functions are commonly used for data analysis.
Query
SELECT
MIN(Marks) AS Minimum_Marks,
MAX(Marks) AS Maximum_Marks,
SUM(Marks) AS Total_Marks,
AVG(Marks) AS Average_Marks
FROM Student;
Result
The aggregate values are calculated successfully.
👇 Write these on the blank page of your practical notebook as output
Output
+---------------+---------------+-------------+---------------+
| Minimum_Marks | Maximum_Marks | Total_Marks | Average_Marks |
+---------------+---------------+-------------+---------------+
| 65 | 92 | 323 | 80.7500 |
+---------------+---------------+-------------+---------------+
Practical 6
Aim: To find the total number of customers from each country
using GROUP BY clause.
Theory
The GROUP BY clause is used to arrange rows having similar values into groups.
The COUNT() function counts the number of records in each group.
This helps in category-wise data analysis.
Query
CREATE TABLE Customer (
CustomerID INT PRIMARY KEY,
CustomerName VARCHAR(50),
Country VARCHAR(30)
);
INSERT INTO Customer VALUES
(1, 'Aman', 'India'),
(2, 'Riya', 'India'),
(3, 'John', 'USA'),
(4, 'David', 'USA'),
(5, 'Sara', 'Canada');
SELECT Country, COUNT(*) AS Total_Customers
FROM Customer
GROUP BY Country;
Result
The total number of customers from each country is displayed successfully.
👇 Write these on the blank page of your practical notebook as output
Output
+---------+-----------------+
| Country | Total_Customers |
+---------+-----------------+
| Canada | 1 |
| India | 2 |
| USA | 2 |
+---------+-----------------+
Practical 7
Aim: To display the “Student” table in descending order of
marks.
Theory
The ORDER BY clause is used to arrange records in ascending or descending order.
• ASC → Ascending order
• DESC → Descending order
This query sorts student records based on marks.
Query
SELECT *
FROM Student
ORDER BY Marks DESC;
Result
The student records are displayed in descending order of marks successfully.
👇 Write these on the blank page of your practical notebook as output
Output
+-----------+--------+-------+
| StudentID | Name | Marks |
+-----------+--------+-------+
| 2 | Ananya | 92 |
| 4 | Sneha | 88 |
| 1 | Rahul | 78 |
| 3 | Vikas | 65 |
+-----------+--------+-------+
Practical 8
Aim: To use mathematical functions in SQL.
Theory
Mathematical functions are used to perform numeric calculations in SQL.
Common functions:
• POWER(x, y) → Raises x to the power y
• ROUND(x, d) → Rounds value up to specified decimal places
• MOD(x, y) → Returns remainder after division
These functions help in performing calculations directly within queries.
Query
SELECT
POWER(2, 3) AS Power_Value,
ROUND(45.6789, 2) AS Rounded_Value,
MOD(17, 5) AS Remainder;
Result
The mathematical functions are executed successfully.
👇 Write these on the blank page of your practical notebook as output
Output
+-------------+---------------+-----------+
| Power_Value | Rounded_Value | Remainder |
+-------------+---------------+-----------+
| 8 | 45.68 | 2 |
+-------------+---------------+-----------+
Practical 9
Aim: To use text functions in SQL.
Theory
Text functions are used to manipulate and analyze string values.
Common text functions:
• UPPER() → Converts text to uppercase
• LOWER() → Converts text to lowercase
• LENGTH() → Returns length of string
• LEFT() → Extracts characters from left side
• RIGHT() → Extracts characters from right side
These functions simplify text processing operations.
Query
SELECT
UPPER('informatics') AS Upper_Text,
LOWER('PRACTICAL') AS Lower_Text,
LENGTH('Database') AS Text_Length,
LEFT('Computer', 3) AS Left_Text,
RIGHT('Computer', 4) AS Right_Text;
Result
The text functions are executed successfully.
👇 Write these on the blank page of your practical notebook as output
Output
+--------------+------------+-------------+-----------+------------+
| Upper_Text | Lower_Text | Text_Length | Left_Text | Right_Text |
+--------------+------------+-------------+-----------+------------+
| INFORMATICS | practical | 8 | Com | uter |
+--------------+------------+-------------+-----------+------------+
Practical 10
Aim: To use date functions in SQL.
Theory
Date functions are used to retrieve and manipulate date values.
Common date functions:
• NOW() → Displays current date and time
• YEAR() → Extracts year
• MONTHNAME() → Displays month name
• DAYNAME() → Displays day name
These functions are useful for date analysis and formatting.
Query
SELECT
NOW() AS Current_DateTime,
YEAR('2025-07-15') AS Year_Value,
MONTHNAME('2025-07-15') AS Month_Name,
DAYNAME('2025-07-15') AS Day_Name;
Result
The date functions are executed successfully.
👇 Write these on the blank page of your practical notebook as output
Output
+---------------------+------------+------------+----------+
| Current_DateTime | Year_Value | Month_Name | Day_Name |
+---------------------+------------+------------+----------+
| 2025-07-15 10:30:00 | 2025 | July | Tuesday |
+---------------------+------------+------------+----------+
Practical 11
Aim: To display grouped data using HAVING clause.
Theory
The HAVING clause is used to apply conditions on grouped data.
Difference:
• WHERE → Filters rows before grouping
• HAVING → Filters groups after grouping
It is commonly used with aggregate functions.
Query
SELECT Country, COUNT(*) AS Total_Customers
FROM Customer
GROUP BY Country
HAVING COUNT(*) > 1;
Result
The grouped records are filtered successfully using the HAVING clause.
👇 Write these on the blank page of your practical notebook as output
Table reference used:
Table: Customer
+------------+--------------+---------+
| CustomerID | CustomerName | Country |
+------------+--------------+---------+
| 1 | Aman | India |
| 2 | Riya | India |
| 3 | John | USA |
| 4 | David | USA |
| 5 | Sara | Canada |
+------------+--------------+---------+
Output
+---------+-----------------+
| Country | Total_Customers |
+---------+-----------------+
| India | 2 |
| USA | 2 |
+---------+-----------------+
Practical 12
Aim: To use SUBSTRING() function in SQL.
Theory
The SUBSTRING() function is a text function used to extract a part of a string.
Syntax:
SUBSTRING(string, start_position, length)
• start_position → Starting index
• length → Number of characters to extract
This function is useful for text analysis and manipulation.
Query
SELECT
SUBSTRING('Informatics', 1, 5) AS Extracted_Text;
Result
The substring is extracted successfully.
👇 Write these on the blank page of your practical notebook as output
Output
+----------------+
| Extracted_Text |
+----------------+
| Infor |
+----------------+
Practical 13
Aim: To use TRIM() function in SQL.
Theory
The TRIM() function removes unwanted spaces from the beginning and end of a string.
It improves data cleanliness and formatting.
This function is commonly used while processing user-entered text values.
Query
SELECT
TRIM(' Database Management ') AS Trimmed_Text;
Result
The extra spaces are removed successfully using TRIM() function.
👇 Write these on the blank page of your practical notebook as output
Output
+---------------------+
| Trimmed_Text |
+---------------------+
| Database Management |
+---------------------+
Practical 14
Aim: To use the COUNT() function in SQL.
Theory
The COUNT() function is an aggregate function used to count records in a table.
Types:
• COUNT(*) → Counts all rows
• COUNT(column_name) → Counts non-NULL values
It is widely used in data analysis and reporting.
Query
SELECT COUNT(*) AS Total_Students
FROM Student;
Result
The total number of student records is counted successfully.
👇 Write these on the blank page of your practical notebook as output
Output
+----------------+
| Total_Students |
+----------------+
| 4 |
+----------------+
Practical 15
Aim: To perform Equi Join using two tables.
Theory
An Equi Join is a type of join where matching records from two tables are combined using
equality condition (=).
It is used to retrieve related data stored in different tables.
This query demonstrates relational database connectivity.
Query
CREATE TABLE Course (
StudentID INT,
CourseName VARCHAR(50)
);
INSERT INTO Course VALUES
(1, 'Python'),
(2, 'SQL'),
(3, 'Data Analysis');
SELECT [Link], [Link], [Link]
FROM Student, Course
WHERE [Link] = [Link];
Result
The Equi Join operation is performed successfully.
👇 Write these on the blank page of your practical notebook as output
Table references used:
Table1: Student
+------------+----------+-------+
| StudentID | Name | Marks |
+------------+----------+-------+
| 1 | Rahul | 78 |
| 2 | Ananya | 92 |
| 3 | Vikas | 65 |
| 4 | Sneha | 88 |
+------------+----------+-------+
Table2: Course
+------------+---------------+
| StudentID | CourseName |
+------------+---------------+
| 1 | Python |
| 2 | SQL |
| 3 | Data Analysis |
+------------+---------------+
Output
+-----------+--------+---------------+
| StudentID | Name | CourseName |
+-----------+--------+---------------+
| 1 | Rahul | Python |
| 2 | Ananya | SQL |
| 3 | Vikas | Data Analysis |
+-----------+--------+---------------+