Scribd
Upload
11 views20 pages

DBMS

The document outlines a series of SQL experiments focusing on Data Definition Language (DDL) commands and SQL queries. It includes creating, altering, and dropping tables, as well as inserting and manipulating data across various tables such as Student, Employee, Department, and Product. Additionally, it covers the use of constraints, joins, aggregation functions, and operations related to a Library Management System and sales orders.

Uploaded by

guptad1927
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
11 views20 pages

DBMS

The document outlines a series of SQL experiments focusing on Data Definition Language (DDL) commands and SQL queries. It includes creating, altering, and dropping tables, as well as inserting and manipulating data across various tables such as Student, Employee, Department, and Product. Additionally, it covers the use of constraints, joins, aggregation functions, and operations related to a Library Management System and sales orders.

Uploaded by

guptad1927
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

Experiment - 1

Objective - Experiments based on DDL commands – CREATE, ALTER, DROP and


TRUNCATE.

Code:

1) CREATE:
/* =====================================================
EXPERIMENT: DDL COMMANDS (CREATE, ALTER, TRUNCATE, DROP)
===================================================== */

/* =======================
1. CREATE TABLE
======================= */

CREATE TABLE Student (


id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);

-- View table structure (optional)


DESCRIBE Student;

/* =======================
2. ALTER TABLE
======================= */

-- Add new column


ALTER TABLE Student
ADD email VARCHAR(100);

-- Modify existing column


ALTER TABLE Student
MODIFY name VARCHAR(100);

-- Drop a column
ALTER TABLE Student
DROP COLUMN age;

-- View updated structure


DESCRIBE Student;

/* =======================
3. INSERT SAMPLE DATA
(To demonstrate TRUNCATE)
======================= */

INSERT INTO Student (id, name, email)


VALUES (1, 'Rahul', 'rahul@[Link]');

INSERT INTO Student (id, name, email)


VALUES (2, 'Anita', 'anita@[Link]');

-- View data
SELECT * FROM Student;

/* =======================
4. TRUNCATE TABLE
======================= */
TRUNCATE TABLE Student;

-- Check table after truncate


SELECT * FROM Student;

/* =======================
5. DROP TABLE
======================= */

DROP TABLE Student;

Output:
Experiment - 2

Objective - Execute the following queries: a) The NOT NULL b) The UNIQUE Constraint c)
The PRIMARY KEY Constraint d) The CHECK Constraint e) Define Integrity Constraints in
ALTER table Command.

Code:
/* =====================================================
DDL & Constraints Demo with Output
===================================================== */

/* =======================
1. CREATE TABLES
======================= */

CREATE TABLE Employee ( emp_id


INT PRIMARY KEY, emp_name
VARCHAR(50) NOT NULL, emp_age
INT
);

CREATE TABLE Department (


dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) UNIQUE
);

CREATE TABLE Product (


product_id INT PRIMARY KEY,
product_name VARCHAR(50),
price DECIMAL(10,2),
quantity INT,
CHECK (price > 0 AND quantity >= 0)
);

/* =======================
2. INSERT DATA
======================= */
INSERT INTO Employee (emp_id, emp_name, emp_age) VALUES (1, 'Rahul', 25);
INSERT INTO Employee (emp_id, emp_name, emp_age) VALUES (2, 'Anita', 30);

INSERT INTO Department (dept_id, dept_name) VALUES (1, 'HR');


INSERT INTO Department (dept_id, dept_name) VALUES (2, 'Finance');

INSERT INTO Product (product_id, product_name, price, quantity) VALUES (1, 'Laptop',
50000, 10);
INSERT INTO Product (product_id, product_name, price, quantity) VALUES (2, 'Mobile',
15000,
5);

/* =======================
3. ALTER TABLE FOR ADDITIONAL CONSTRAINTS
======================= */

-- Add NOT NULL to emp_age (if needed)


ALTER TABLE Employee
MODIFY emp_age INT NOT NULL;

-- Add UNIQUE constraint to emp_name


ALTER TABLE Employee
ADD CONSTRAINT unique_emp_name UNIQUE (emp_name);

-- Add CHECK constraint to emp_age


ALTER TABLE Employee
ADD CONSTRAINT chk_age CHECK (emp_age >= 18 AND emp_age <= 65);

/* =======================
4. SELECT DATA TO SHOW OUTPUT
======================= */

-- Show Employee Table


SELECT * FROM Employee;

-- Show Department Table


SELECT * FROM Department;
-- Show Product Table
SELECT * FROM Product;

Output:
Experiment - 3
Objective - Writing SQL statements Using MYSQL: a) Writing basic SQL SELECT statements.
b) Restricting and sorting data. c) Displaying data from multiple tables. d) Aggregating data
using group function. e) Manipulating data. f) Creating and managing tables.

Code:
/* =====================================================
ALL-IN-ONE MYSQL SCRIPT FOR LAB DEMONSTRATION
===================================================== */

/* =======================
1. DROP TABLES IF THEY EXIST
======================= */
DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Department;
DROP TABLE IF EXISTS Product;

/* =======================
2. CREATE TABLES
======================= */

CREATE TABLE Employee ( emp_id


INT PRIMARY KEY, emp_name
VARCHAR(50) NOT NULL, emp_age
INT, emp_salary DECIMAL(10,2),
dept_id INT
);

CREATE TABLE Department (


dept_id INT PRIMARY KEY,
dept_name VARCHAR(50) UNIQUE
);
CREATE TABLE Product (
product_id INT PRIMARY KEY,
product_name VARCHAR(50) NOT NULL,
price DECIMAL(10,2) CHECK(price > 0),
quantity INT DEFAULT 0
);

/* =======================
3. INSERT SAMPLE DATA
======================= */

INSERT INTO Employee VALUES


(1, 'Rahul', 25, 50000, 101),
(2, 'Anita', 30, 60000, 102),
(3, 'Suresh', 28, 55000, 101),
(4, 'Pooja', 35, 70000, 103);

INSERT INTO Department VALUES


(101, 'HR'),
(102, 'Finance'),
(103, 'IT');

INSERT INTO Product VALUES


(1, 'Laptop', 50000, 10),
(2, 'Mobile', 15000, 20);

/* =======================
4. BASIC SELECT STATEMENTS
======================= */
SELECT * FROM Employee;
SELECT emp_name, emp_salary FROM Employee;
/* =======================
5. RESTRICTING AND SORTING DATA
======================= */
-- Restrict data using WHERE
SELECT * FROM Employee WHERE emp_age > 28;

-- Using AND, OR
SELECT * FROM Employee WHERE emp_age > 28 AND dept_id = 103;

-- Sorting data
SELECT * FROM Employee ORDER BY emp_salary DESC;
SELECT * FROM Employee ORDER BY emp_name ASC;

/* =======================
6. DISPLAYING DATA FROM MULTIPLE TABLES
======================= */
-- INNER JOIN
SELECT e.emp_name, e.emp_salary, d.dept_name
FROM Employee e
JOIN Department d ON e.dept_id = d.dept_id;

-- LEFT JOIN
SELECT e.emp_name, e.emp_salary, d.dept_name
FROM Employee e
LEFT JOIN Department d ON e.dept_id = d.dept_id;

/* =======================
7. AGGREGATING DATA USING GROUP FUNCTIONS ======================= */
SELECT COUNT(*) AS total_employees FROM Employee;
SELECT AVG(emp_salary) AS avg_salary FROM Employee;
SELECT dept_id, SUM(emp_salary) AS total_salary FROM Employee GROUP BY dept_id;
SELECT MAX(emp_salary) AS max_salary, MIN(emp_salary) AS min_salary FROM Employee;
SELECT dept_id, AVG(emp_salary) AS avg_salary
FROM Employee
GROUP BY dept_id
HAVING AVG(emp_salary) > 55000;

/* =======================
8. MANIPULATING DATA
======================= */
-- Insert new record
INSERT INTO Employee VALUES (5, 'Neha', 29, 65000, 102);

-- Update record
UPDATE Employee SET emp_salary = 72000 WHERE emp_name = 'Pooja';

-- Delete record
DELETE FROM Employee WHERE emp_id = 3;

-- Show updated Employee table


SELECT * FROM Employee;

/* =======================
9. CREATING AND MANAGING TABLES
======================= */
-- Add new column to Product
ALTER TABLE Product ADD category VARCHAR(50);

-- Modify price column


ALTER TABLE Product MODIFY price DECIMAL(12,2);

-- Drop quantity column


ALTER TABLE Product DROP COLUMN quantity;

-- Show Product table after modifications


DESCRIBE Product;
SELECT * FROM Product;
/* =======================
10. CLEAN UP (DROP TABLES)
======================= */
-- Uncomment below lines to drop tables at the end
-- DROP TABLE Employee;
-- DROP TABLE Department;
-- DROP TABLE Product;

Output:
Experiment – 4
Objective- Create any Application Using any web application that performs Create,
Insert, Delete, Update Operations on database Using JDBC.

Code:

-- DBMS LAB PROJECT


- Library Management System
- 1. CREATE TABLES
CREATE TABLE IF NOT EXISTS books (
book_id INT PRIMARY KEY
AUTO_INCREMENT, title VARCHAR(100)
NOT NULL, author VARCHAR(100), price
DECIMALquantity INT

CREATE TABLE IF NOT EXISTS members (


member_id INT PRIMARY KEY
AUTO_INCREMENT, name VARCHAR(100),
email VARCHAR(100), phone VARCHAR(15)

-- 2. INSERT DATA
INSERT INTO books (title, author, price, quantity) VALUES
('Database Systems', I KorthI, 550.00, 1 0),
('Operating Systems', 'Galvin', 650.00, 5),
('Computer Networks', 'Tanenbaum', 500.00, 7);
INSERT INTO members (name, email, phone) VALUES
('Rahul Sharma', 'rahul@[Link]', '987654321 0'),
('Priya Verma', 'priya@[Link]', 1 9123456780);
-- 3. SELECT DATA
SELECT * FROM books;
SELECT * FROM members;
-- 4. UPDATE DATA
UPDATE books
SET price = 600.00
WHERE book_id = 1;
-- 5. DELETE DATA
DELETE FROM members
WHERE member_id = 2;
-- 6. ADDITIONAL OPERATIONS
-- Search book by tle
SELECT * FROM books
WHERE tle LIKE
-- Sort books by price
SELECT * FROM books
ORDER BY price DESC;
-- Count total books
SELECT AS total_books FROM books;

-- END OF DBMS LAB PROGRAM

OUTPUT :
Experiment – 5

Objective - Execute the queries related to Group By and having Clause on tables
SALES_ORDER.

Code:
GROUP BY & HAVING CLAUSE TABLE:
SALES_ORDER
/* 1. Create Table */
CREATE TABLE SALES_ORDER (
ORDER_ID INT PRIMARY KEY,
CUSTOMER_ID INT,
PRODUCT_ID INT,
ORDER_DATE DATE,
QUANTITY INT,
UNIT_PRICEDECIMAL

STATUS VARCHAR(20)

/* 2. Insert Sample Records */


INSERT INTO SALES_ORDER VALUES
(1, 101, 1001, 2024-01-10 1, 5, 2000, DELIVERED'),
(2, 101, 1002, '2024-01-15', 3, 5000, 'PENDING'),
(3, 102, 1001, 12024-02-05, 10, 2000, 'DELIVERED'),
(4, 103, 1003, '2024-02-20', 7, 3000, 'CANCELLED'),
(5, 102, 1002, 2024-03-01 1, 2, 5000, DELIVERED'),
(6, 101, 1003, 2024-03-10 1, 6, 3000, DELIVERED'),
(7, 104,1001, 2024-03-15, 20, 2000, PENDING'),
(8, 105, 1002, '2024-04-01 1 5, 5000, 'DELIVERED'),
(9, 103, 1003, '2024-04-05, 4, 3000, DELIVERED'),
(1 0, 104, 1002, '2024-04-10', 8, 5000, 'DELIVERED');

GROUP BY QUERIES

/* 1 . Total Sales Amount per Customer */

SELECT CUSTOMER_ID,

SUM(QUANTITY * UNIT_PRICE) AS TOTAL_SALES

FROM SALES_ORDER

GROUP BY CUSTOMER_ID;

/* 2. Total Sales per Customer > 50000 */

SELECT CUSTOMER_ID,

SUM(QUANTITY * UNIT_PRICE) AS TOTAL_SALES

FROM SALES_ORDER

GROUP BY CUSTOMER_ID

HAVING SUM(QUANTITY * UNIT_PRICE) > 50000;

/* 3. Number of Orders per Customer */


SELECT CUSTOMER-ID,

COUNT(ORDER_ID)AS TOTAL_ORDERS

FROM SALES_ORDER

GROUP BY CUSTOMER_ID;

/* 4. Customers Who Placed More Than 2 Orders */

SELECT CUSTOMER_ID,

COUNT(ORDER_ID)AS TOTAL_ORDERS

FROM SALES_ORDER

GROUP BY CUSTOMER_ID
HAVING COUNT(ORDER_ID) > 2;

/* 5. Total Quantity Sold per Product */

SELECT PRODUCT_ID,

SUM(QUANTITY) AS TOTAL_QUANTITY

FROM SALES_ORDER

GROUP BY PRODUCT_ID,•
/* 6. Products With Total Quantity > 20 */
SELECT PRODUCT_ID,

SUM(QUANTITY) AS TOTAL_QUANTITY

FROM SALES_ORDER

GROUP BY PRODUCT_ID

HAVING SUM(QUANTITY) > 20;

/* 7. Maximum Order Amount per Customer */


SELECT CUSTOMER_ID,

MAX(QUANTITY * UNIT_PRICE) ASMAX_ORDER_AMOUNT

FROM SALES_ORDER

GROUP BY CUSTOMER_ID;

/* 8. Average Order Value per Customer > 10000 */

SELECT CUSTOMER_ID,

AVG(QUANTITY * UNIT_PRICE) ASAVG_ORDER_VALUE

FROM SALES_ORDER

GROUP BY CUSTOMER-ID

HAVING AVG(QUANTITY * UNIT_PRICE) > 1 0000;

/* 9. Total Sales by Order Status */

SELECT STATUS,

SUM(QUANTITY * UNIT_PRICE) AS TOTAL_SALES

FROM SALES_ORDER

GROUP BY STATUS;
/* 10. Order Status Having Total Sales < 40000 */

SELECT STATUS,

SUM(QUANTITY * UNIT_PRICE) AS TOTAL_SALES


FROM SALES_ORDER

GROUP BY STATUS

HAVING SUM(QUANTITY * UNIT_PRICE) < 40000;

Output:

You might also like