Experiment No.
1: Basic SQL Functions (CREATE, INSERT, UPDATE, DELETE)
Aim:
To study and implement basic SQL commands: CREATE, INSERT, UPDATE,
DELETE.
Software/Tool Required:
MySQL / Oracle / SQL Server / PostgreSQL (any SQL environment)
Theory:
Structured Query Language (SQL) is used to manage data in a relational database.
- CREATE → Used to create a new table.
- INSERT → Used to add new records.
- UPDATE → Used to modify existing records.
- DELETE → Used to remove records from a table.
Steps / Procedure:
1. Create a database (optional, if not already available).
2. Create a table named Students.
3. Insert multiple records into the table.
4. Update some records in the table.
5. Delete specific records.
6. Display the table after each operation using SELECT * FROM Students;.
Queries & Output:
Step 1: Create Table
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
Course VARCHAR(50)
);
SELECT * FROM Students;
Step 2: Insert Records
INSERT INTO Students (StudentID, Name, Age, Course)
VALUES (1, 'Manas Vedre', 21, 'BSC IT');
INSERT INTO Students (StudentID, Name, Age, Course)
VALUES (2, 'Ritika Sharma', 20, 'BSC CS');
INSERT INTO Students (StudentID, Name, Age, Course)
VALUES (3, 'Siddharth Patil', 22, 'BSC IT');
Step 3: Display Records
SELECT * FROM Students;
StudentID Name Age Course
1 Manas Vedre 21 BSC IT
2 Ritika Sharma 20 BSC CS
3 Siddharth Patil 22 BSC IT
Step 4: Update Record
UPDATE Students
SET Age = 23, Course = 'BSC IT - Data Analytics'
WHERE StudentID = 1;
Step 5: Display Records After Update
SELECT * FROM Students;
StudentID Name Age Course
1 Manas Vedre 23 BSC IT - Data
Analytics
2 Ritika Sharma 20 BSC CS
3 Siddharth Patil 22 BSC IT
Step 6: Delete Record
DELETE FROM Students
WHERE StudentID = 2;
Step 7: Display Records After Delete
SELECT * FROM Students;
StudentID Name Age Course
1 Manas Vedre 23 BSC IT - Data
Analytics
3 Siddharth Patil 22 BSC IT
Code & Output:
Result:
The basic SQL commands CREATE, INSERT, UPDATE, DELETE were successfully
executed and their usage was understood.
Experiment No. 2
Aim: To perform simple queries using SELECT statement.
Sample Table: Student
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Course VARCHAR(30),
Marks INT,
Age INT,
City VARCHAR(30),
Fees DECIMAL(10,2)
);
INSERT INTO Student VALUES
(1, 'Amit', 'IT', 85, 19, 'Mumbai', 25000),
(2, 'Riya', 'CS', 92, 20, 'Pune', 27000),
(3, 'Karan', 'IT', 76, 18, 'Delhi', 23000),
(4, 'Neha', 'CS', 89, 21, 'Mumbai', 30000),
(5, 'Arjun', 'IT', 65, 19, 'Chennai', 20000),
(6, 'Priya', 'CS', 95, 20, 'Pune', 28000),
(7, 'Rahul', 'IT', 72, 22, 'Mumbai', 26000),
(8, 'Sneha', 'CS', 88, 20, 'Delhi', 29000),
(9, 'Vikas', 'IT', 81, 21, 'Pune', 24000),
(10, 'Meera', 'CS', 93, 22, 'Chennai', 31000);
SELECT Statement Queries
1. Display all student details.
SELECT * FROM Student;
2. Display name and course of all students.
SELECT Name, Course FROM Student;
3. Display students who are from Mumbai.
SELECT * FROM Student WHERE City = 'Mumbai';
4. Display students having marks greater than 80.
SELECT Name, Marks FROM Student WHERE Marks > 80;
5. Display students enrolled in IT course.
SELECT Name FROM Student WHERE Course = 'IT';
6. Display students whose age is between 19 and 21.
SELECT Name, Age FROM Student WHERE Age BETWEEN 19 AND 21;
7. Display students not from Pune.
SELECT Name, City FROM Student WHERE City <> 'Pune';
8. Display students whose name starts with ‘R’.
SELECT * FROM Student WHERE Name LIKE 'R%';
9. Display students whose name ends with ‘a’.
SELECT * FROM Student WHERE Name LIKE '%a';
10. Display students from Mumbai or Pune.
SELECT Name, City FROM Student WHERE City IN ('Mumbai','Pune');
11. Display all students sorted by marks in descending order.
SELECT Name, Marks FROM Student ORDER BY Marks DESC;
12. Display distinct course names available.
SELECT DISTINCT Course FROM Student;
13. Display top 3 students with highest marks.
SELECT Name, Marks FROM Student ORDER BY Marks DESC LIMIT 3;
Conclusion
Thus, the above queries demonstrate the use of SELECT statement, conditions, and
sorting, functions in SQL.
Experiment No. 3
Aim: To perform aggregate function using SELECT statement.
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Course VARCHAR(30),
Marks INT,
Age INT,
City VARCHAR(30),
Fees DECIMAL(10,2)
);
INSERT INTO Student VALUES
(1, 'Amit', 'IT', 85, 19, 'Mumbai', 25000),
(2, 'Riya', 'CS', 92, 20, 'Pune', 27000),
(3, 'Karan', 'IT', 76, 18, 'Delhi', 23000),
(4, 'Neha', 'CS', 89, 21, 'Mumbai', 30000),
(5, 'Arjun', 'IT', 65, 19, 'Chennai', 20000),
(6, 'Priya', 'CS', 95, 20, 'Pune', 28000),
(7, 'Rahul', 'IT', 72, 22, 'Mumbai', 26000),
(8, 'Sneha', 'CS', 88, 20, 'Delhi', 29000),
(9, 'Vikas', 'IT', 81, 21, 'Pune', 24000),
(10, 'Meera', 'CS', 93, 22, 'Chennai', 31000);
Aggregate Function Queries
1. Find total number of students.
SELECT COUNT(*) AS Total_Students FROM Student;
2. Find maximum marks obtained.
SELECT MAX(Marks) AS Highest_Marks FROM Student;
3. Find minimum fees paid by any student.
SELECT MIN(Fees) AS Lowest_Fees FROM Student;
4. Find average marks of students.
SELECT AVG(Marks) AS Average_Marks FROM Student;
5. Find total fees collected from students.
SELECT SUM(Fees) AS Total_Fees FROM Student;
6. Display number of students in each course.
SELECT Course, COUNT(*) AS Total_Students FROM Student GROUP BY Course;
7. Display average marks of students course-wise where average is greater than 80.
SELECT Course, AVG(Marks) AS Avg_Marks FROM Student GROUP BY Course
HAVING AVG(Marks) > 80;
Conclusion
Thus, the above queries demonstrate the use of aggregate functions in SQL.
Experiment No. 4
Aim: To perform various String & Math operations in SQL using the 'Student' table.
Code/Query:
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(50),
Course VARCHAR(30),
Marks INT,
Age INT,
City VARCHAR(30),
Fees DECIMAL(10,2)
);
INSERT INTO Student VALUES
(1, 'Amit', 'IT', 85, 19, 'Mumbai', 25000),
(2, 'Riya', 'CS', 92, 20, 'Pune', 27000),
(3, 'Karan', 'IT', 76, 18, 'Delhi', 23000),
(4, 'Neha', 'CS', 89, 21, 'Mumbai', 30000),
(5, 'Arjun', 'IT', 65, 19, 'Chennai', 20000),
(6, 'Priya', 'CS', 95, 20, 'Pune', 28000),
(7, 'Rahul', 'IT', 72, 22, 'Mumbai', 26000),
(8, 'Sneha', 'CS', 88, 20, 'Delhi', 29000),
(9, 'Vikas', 'IT', 81, 21, 'Pune', 24000),
(10, 'Meera', 'CS', 93, 22, 'Chennai', 31000);
SELECT * FROM Student;
Output Table: Student
RollNo Name Course Marks Age City Fees
1 Amit IT 85 19 Mumbai 25000
2 Riya CS 92 20 Pune 27000
3 Karan IT 76 18 Delhi 23000
4 Neha CS 89 21 Mumbai 30000
5 Arjun IT 65 19 Chennai 20000
6 Priya CS 95 20 Pune 28000
7 Rahul IT 72 22 Mumbai 26000
8 Sneha CS 88 20 Delhi 29000
9 Vikas IT 81 21 Pune 24000
10 Meera CS 93 22 Chennai 31000
A] String Operation
1) Convert all names to uppercase
SELECT Name, UPPER(Name) AS UpperName FROM Student;
2) Convert city names to lowercase
SELECT City, LOWER(City) AS LowerCity FROM Student;
3) Find length of each student’s name
SELECT Name, LEN(Name) AS NameLength FROM Student;
4) Concatenate name and course
SELECT Name, Course, CONCAT(Name, ' - ', Course) AS StudentCourse FROM Student;
5) Extract first 3 letters of city
SELECT City, SUBSTRING(City, 1, 3) AS CityCode FROM Student;
6) Replace city 'Mumbai' with 'Bombay'
SELECT Name, City, REPLACE(City, 'Mumbai', 'Bombay') AS UpdatedCity FROM Student;
7) Reverse student names
SELECT Name, REVERSE(Name) AS ReverseName FROM Student;
8) Display first letter of each student’s name
SELECT Name, LEFT(Name, 1) AS FirstLetter FROM Student;
B] Maths Operation
1) To display Square root
SELECT Name, Marks, SQRT(Marks) AS MarksRoot FROM Student;
2) To display Power (Square/Cube etc)
SELECT Name, Marks, POWER(Marks, 2) AS MarksSquare FROM Student;
3) To display Modulus
SELECT Name, Marks, MOD(Marks, 10) AS LastDigit FROM Student;
4) To display Random Number
SELECT RAND() AS RandomNumber;
5) To display Increment/Decrement
SELECT Name, Marks, Marks + 5 AS IncrementedMarks FROM Student;
SELECT Name, Marks, Marks - 5 AS DecrementedMarks FROM Student;
Experiment No. 5
Aim: To study and implement various types of SQL Joins (INNER JOIN, LEFT JOIN,
RIGHT JOIN, FULL OUTER JOIN, CROSS JOIN, and SELF JOIN) in order to combine
and retrieve data from multiple related tables.
1. Theory
A JOIN in SQL is used to combine rows from two or more tables based on a related
column between them. Joins help to retrieve meaningful data that is spread across
multiple tables. Instead of storing all data in a single table, databases follow
normalization and split data into multiple related tables. Using joins, we can connect
these tables logically.
Commonly Used Joins:
INNER JOIN – Returns records that have matching values in both tables.
LEFT JOIN (LEFT OUTER JOIN) – Returns all records from the left table, and the
matched records from the right table.
RIGHT JOIN (RIGHT OUTER JOIN) – Returns all records from the right table, and
the matched records from the left table.
FULL JOIN (FULL OUTER JOIN) – Returns all records when there is a match in
either left or right table.
CROSS JOIN – Returns Cartesian product (all combinations).
SELF JOIN – Joins a table with itself.
2. Sample Database
Student Table
StudentID StudentName CourseID
1 Rohan 101
2 Priya 102
3 Amit 103
4 Neha 101
5 Mehul NULL
Course Table
CourseID CourseName Faculty
101 Database Prof. Rao
102 Networking Prof. Shah
103 Programming Prof. Roy
104 AI Prof. Mehta
3. Types of Joins with Queries & Outputs
a) INNER JOIN
SELECT Student.StudentID, Student.StudentName,
Course.CourseName
FROM Student
INNER JOIN Course ON Student.CourseID = Course.CourseID;
StudentID StudentName CourseName
1 Rohan Database
2 Priya Networking
3 Amit Programming
4 Neha Database
b) LEFT JOIN
SELECT Student.StudentID, Student.StudentName,
Course.CourseName
FROM Student
LEFT JOIN Course ON Student.CourseID = Course.CourseID;
StudentID StudentName CourseName
1 Rohan Database
2 Priya Networking
3 Amit Programming
4 Neha Database
5 Mehul NULL
c) RIGHT JOIN
SELECT Student.StudentName, Course.CourseName
FROM Student
RIGHT JOIN Course ON Student.CourseID = Course.CourseID;
StudentName CourseName
Rohan Database
Priya Networking
Amit Programming
Neha Database
NULL AI
d) FULL OUTER JOIN
SELECT Student.StudentName, Course.CourseName
FROM Student
FULL OUTER JOIN Course ON Student.CourseID =
Course.CourseID;
StudentName CourseName
Rohan Database
Priya Networking
Amit Programming
Neha Database
Mehul NULL
NULL AI
e) CROSS JOIN
SELECT Student.StudentName, Course.CourseName
FROM Student
CROSS JOIN Course;
StudentName CourseName
Rohan Database
Rohan Networking
Rohan Programming
Rohan AI
Priya Database
Priya Networking
... ...
f) SELF JOIN
SELECT S1.StudentName AS Student1, S2.StudentName AS
Student2, S1.CourseID
FROM Student S1
JOIN Student S2 ON S1.CourseID = S2.CourseID
WHERE S1.StudentID < S2.StudentID;
Student1 Student2 CourseID
Rohan Neha 101
