Practical No.
1
OBJECT : Create a student table and insert the data in the student table using Structured
Query Language (SQL), And last print the output.
1. CREATE
CREATE TABLE IF NOT EXISTS student (
number INT UNIQUE,
student_id INT NOT NULL,
roll_number INT UNIQUE,
name VARCHAR(50),
fee_paid DOUBLE NULL,
gender VARCHAR(1),
phone_no BIGINT NULL,
PRIMARY KEY (number)
);
2. INSERT
INSERT INTO student (number, student_id, roll_number, name, fee_paid, gender, phone_no)
VALUES
(1, 23, 69, 'Md Irfan Idrishi', 26650, 'M', 8298492147),
(2, 24, 36, 'Aman', 26650, 'M', 9931928017),
(3, 25, 1, 'Khalid', 0, 'M', 8084523025),
(4, 26, 31, 'Eqbal', 26650, 'M', 7292845570),
(5, 27, 2, 'Zahia', 26650, 'M', 6204296385);
3. SELECT
SELECT * FROM student;
Practical No. 2 (a)
OBJECT : Create a student table and insert the data in the student table to perform DDL
Commands using Structured Query Language (SQL), And last print the output.
1. CREATE
CREATE TABLE Employer (
EmployerID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT CHECK (Age >= 18),
Position VARCHAR(50),
Salary DECIMAL(10,2));
2. ALTER
ALTER TABLE Employer ADD Email VARCHAR(150) UNIQUE;
3. DROP
ALTER TABLE Employer DROP COLUMN Position;
DATA MANIPULATION LANGUAGE
1. CREATE
CREATE TABLE employees (
Id INT PRIMARY KEY,
Name VARCHAR(50) NOT NULL,
Salary INT CHECK (Salary > 0));
2. INSERT
INSERT INTO employees (Id, Name, Salary) VALUES
(1, 'Alice', 50000),
(2, 'Bob', 60000),
(3, 'Charlie', 55000),
(4, 'Eve', 70000);
3. UPDATE
UPDATE employees SET Salary = 65000 WHERE Id = 2;
4. DELETE
DELETE FROM employees WHERE Id = 1;
5. SELECT
SELECT * FROM employees WHERE Salary > 60000;
Practical No. 2 (b)
OBJECT : Create a student table and insert the data in the student table to perform DML
Commands using Structured Query Language (SQL), And last print the output.
1. CREATE
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT CHECK (Age >= 18), -- Ensuring students are adults
Course VARCHAR(50),
Fees DECIMAL(10,2) NOT NULL -- Storing fees with two decimal places);
2. INSERT
INSERT INTO Students (StudentID, Name, Age, Course, Fees) VALUES
(1, 'Rahul', 20, 'Computer Science', 45000.00),
(2, 'Priya', 22, 'Electronics', 47000.00),
(3, 'Ankit', 19, 'Mechanical', 50000.00),
(4, 'Neha', 23, 'Civil Engineering', 52000.00),
(5, 'Amit', 21, 'IT', 48000.00);
Practical No. 3
OBJECT : The objective of this practical is to perform aggregate functions on the
PRODUCT_MAST table using Structured Query Language (SQL) to analyze product data
effectively.
1. CREATE
CREATE TABLE PRODUCT_MAST (
PRODUCT VARCHAR(50),
COMPANY VARCHAR(50),
QTY INT,
RATE DECIMAL(10,2),
COST DECIMAL(10,2);
2. INSERT
INSERT INTO PRODUCT_MAST (PRODUCT, COMPANY, QTY, RATE, COST) VALUES
('Item1', 'Com1', 2, 10, 20),
('Item2', 'Com2', 3, 25, 75),
('Item3', 'Com1', 2, 30, 60),
('Item4', 'Com3', 5, 10, 50),
('Item5', 'Com2', 2, 20, 40),
('Item6', 'Com1', 3, 25, 75),
('Item7', 'Com1', 5, 30, 150),
('Item8', 'Com1', 3, 10, 30),
('Item9', 'Com2', 2, 25, 50),
('Item10', 'Com3', 4, 30, 120);
3. AGGREGATE FUNCTIONS
1. COUNT
SELECT COUNT(*)
FROM PRODUCT_MAST;
Output: 10
2. SUM
SELECT SUM(COST)
FROM PRODUCT_MAST;
Output: 670
3. AVG
SELECT AVG(COST)
FROM PRODUCT_MAST;
Output: 67.00
4. MAX
SELECT MAX(RATE)
FROM PRODUCT_MAST;
Output 30
Practical No. 4
OBJECT : Perform clauses in MYSQL from the given table.
1. CREATE TABLE
CREATE TABLE EmployeeRecords (
Employee_ID INT PRIMARY KEY,
Name VARCHAR(50),
Department VARCHAR(50),
Phone VARCHAR(15),
Age INT
);
2. INSERT
INSERT INTO EmployeeRecords (Employee_ID, Name, Department, Phone, Age) VALUES
(201, 'Rahul', 'IT', '9876543210', 25),
(202, 'Priya', 'HR', '8765432190', 30),
(203, 'Aman', 'IT', '7654321980', 28),
(204, 'Neha', 'Finance', '6543219870', 27);
3. SELECT
SELECT Employee_ID, Name FROM EmployeeRecords;
4. FROM
SELECT * FROM EmployeeRecords;
5. WHERE
SELECT * FROM EmployeeRecords WHERE Age > 27;
6. ORDER
SELECT * FROM EmployeeRecords ORDER BY Name ASC;
7. GROUP
SELECT Department, COUNT(*) FROM EmployeeRecords GROUP BY Department;
8. HAVING
SELECT Department, COUNT(*) FROM EmployeeRecords GROUP BY Department HAVING COUNT(*) > 2;
9. LIMIT
SELECT * FROM EmployeeRecords LIMIT 3;
10. UPDATE
UPDATE EmployeeRecords SET Age = 29 WHERE Name = 'Rahul';
11. DELETE
DELETE FROM EmployeeRecords WHERE Name = 'Neha';
12. DROP
DROP TABLE EmployeeRecords;
