System Analysis
Adv. RDBMS & Design
& Dev. Tools
(DCO-316)
(DCO-303)
Submitted By: Submitted To:
Inam Ul Haque Mohsin Mohd.
Dr. Md. Sadique Riaz
Nurul Islam
Roll No. 23DCS8033
Computer Engineering Section
University Polytechnic
Jamia Millia Islamia, New Delhi -110025
-
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;
Practical:-05
OBJECT :- write a program in my sql code create student table with column
name, student id ,student name, age course. 1:- insert dublicate record in student
table. 2:-count dublicate record. and 3:-count unique records . 5:- delete all
dublicate record from student table.
Solve:-
CREATE DATABASE IF NOT EXISTS school;
USE school;
DROP TABLE IF EXISTS student;
CREATE TABLE student (
student_id INT,
name VARCHAR(100),
student_name VARCHAR(100),
age INT,
course VARCHAR(100)
);
-- 2. Insert duplicate records into student table
INSERT INTO student (student_id, name, student_name, age, course) VALUES
(1, 'MD EQUBAL AHMAD', 'EQUBAL', 20, 'Math'),
(2, 'RAJA ALAM', 'RAJA', 21, 'Physics'),
(3, 'MINHAZ ALAM', 'MINAHJ', 22, 'Chemistry'),
(1, 'MDEQUBAL AHMAD', 'EQUBAL', 20, 'Math'),-- Duplicate
(2, 'RAJA ALAM', 'RAJA', 21, 'Physics'); -- Duplicate
-- 3. Count duplicate records
-- Duplicate = same values for all columns, and appear more than once
SELECT
student_id, name, student_name, age, course, COUNT(*) AS count
FROM
student
GROUP BY
student_id, name, student_name, age, course
HAVING
COUNT(*) > 1;
-- 4. Count unique records (appear only once)
SELECT
COUNT(*) AS unique_record_count
FROM (
SELECT
student_id, name, student_name, age, course
FROM
student
GROUP BY
student_id, name, student_name, age, course
HAVING
COUNT(*) = 1
)
AS unique_records;
SELECT * FROM student;
23DCS8039
Practical No:-06
Object: To create a documentation on Data Control Language (DCL).
Data Control Language (DCL) in MySQL
Data Control Language (DCL) is a subset of SQL used to control access and permissions
to database objects such as tables, views, and procedures. It ensures database security by
managing user privileges.
MySQL DCL allows administrators to determine who can access the database and what
actions they can perform.
Common DCL Commands
1. GRANT
Used to assign privileges to a user or role.
Syntax:
GRANT privilege_list ON database_name.table_name TO 'username'@'host';
Example:
GRANT SELECT, INSERT ON employees.* TO 'john'@'localhost';
2. REVOKE
Used to remove previously granted privileges.
Syntax:
REVOKE privilege_list ON database_name.table_name FROM 'username'@'host';
Example:
REVOKE INSERT ON employees.* FROM 'john'@'localhost';
3. SHOW GRANTS
Displays the privileges of a user.
Syntax:
SHOW GRANTS FOR 'username'@'host';
Example:
SHOW GRANTS FOR 'john'@'localhost';
4. RENAME USER (Optional)
Renames an existing user account.
16
23DCS8039
Syntax:
RENAME USER 'old_name'@'host' TO 'new_name'@'host';
5. DROP USER
Deletes a user account and its privileges.
Syntax:
DROP USER 'username'@'host';
Example:
DROP USER 'john'@'localhost';
Types of Privileges
Privilege Description
SELECT Read data from a table
INSERT Add new rows
UPDATE Modify existing rows
DELETE Remove rows
ALL PRIVILEGES Grants all possible privileges
EXECUTE Run stored routines or procedures
USAGE Create a user without any privileges
17
23DCS8039
Practical No:-07
Object: To create a calculator using access.
Design View:
Form View:
18
23DCS8039
Code:
Private Sub Addition_Click()
Text12 = Val(Text8) + Val(Text10)
End Sub
Private Sub Command31_Click()
Text12 = Val(Text8) / Val(Text10)
End Sub
Private Sub Command32_Click()
Text12 = Val(Text8) * Val(Text10)
End Sub
Private Sub Command33_Click()
Text12 = Val(Text8) - Val(Text10)
End Sub
19