Database Management System Laboratory
Experiment 1: Implementation of DDL commands of SQL
CREATE TABLE Students (ID INT, Name VARCHAR(50));
ALTER TABLE Students ADD Age INT;
DROP TABLE Students;
Expected Output:
Table created.
Table altered.
Table dropped.
Database Management System Laboratory
Experiment 2: Implementation of DML commands of SQL
INSERT INTO Students VALUES (1, 'Alice', 20);
UPDATE Students SET Age = 21 WHERE ID = 1;
DELETE FROM Students WHERE ID = 1;
Expected Output:
1 row inserted.
1 row updated.
1 row deleted.
Database Management System Laboratory
Experiment 3: Implementation of different types of functions
SELECT LENGTH('Database'); -- Number function
SELECT AVG(Age) FROM Students; -- Aggregate Function
SELECT UPPER(Name) FROM Students; -- Character Function
SELECT CAST('2024-01-01' AS DATE); -- Conversion
SELECT CURRENT_DATE; -- Date Function
Expected Output:
As per function type.
Database Management System Laboratory
Experiment 4: Implementation of different types of operators
SELECT 10 + 5 AS Sum; -- Arithmetic
SELECT * FROM Students WHERE Age > 18 AND Age < 25; -- Logical
SELECT * FROM Students WHERE Name = 'Alice'; -- Comparison
SELECT DISTINCT Name FROM Students; -- Special
SELECT Name FROM Students UNION SELECT Name FROM Teachers; -- Set
Expected Output:
Respective operator results.
Database Management System Laboratory
Experiment 5: Implementation of different types of Joins
SELECT * FROM Students INNER JOIN Marks ON Students.ID = Marks.StudentID;
SELECT * FROM Students LEFT JOIN Marks ON Students.ID = Marks.StudentID;
SELECT * FROM Students NATURAL JOIN Marks;
Expected Output:
Joined tables.
Database Management System Laboratory
Experiment 6: Study and Implementation of GROUP BY, ORDER BY, INDEX
SELECT Age, COUNT(*) FROM Students GROUP BY Age HAVING COUNT(*) > 1;
SELECT * FROM Students ORDER BY Name;
CREATE INDEX idx_name ON Students(Name);
Expected Output:
Grouped, ordered, and indexed data.
Database Management System Laboratory
Experiment 7: Study & Implementation of Subqueries and Views
SELECT * FROM Students WHERE Age = (SELECT MAX(Age) FROM Students);
CREATE VIEW young_students AS SELECT * FROM Students WHERE Age < 22;
Expected Output:
Subquery results and view created.
Database Management System Laboratory
Experiment 8: Study & Implementation of Constraints
CREATE TABLE Students (ID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Age INT CHECK (Age
>= 18));
Expected Output:
Table with constraints created.
Database Management System Laboratory
Experiment 9: Study & Implementation of Backup, Commit, Rollback
START TRANSACTION;
INSERT INTO Students VALUES (2, 'Bob', 19);
SAVEPOINT sp1;
ROLLBACK TO sp1;
COMMIT;
Expected Output:
Transaction processed with rollback and commit.
Database Management System Laboratory
Experiment 10: Creating Users and Managing Roles
CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'pass';
GRANT SELECT ON DB.* TO 'new_user'@'localhost';
REVOKE SELECT ON DB.* FROM 'new_user'@'localhost';
Expected Output:
User created and privileges managed.
Database Management System Laboratory
Experiment 11: Study & Implementation of PL/SQL
DECLARE
v_name VARCHAR(50);
BEGIN
SELECT Name INTO v_name FROM Students WHERE ID = 1;
DBMS_OUTPUT.PUT_LINE(v_name);
END;
Expected Output:
PL/SQL block executed.
Database Management System Laboratory
Experiment 12: Study & Implementation of SQL Triggers
CREATE TRIGGER before_insert_students
BEFORE INSERT ON Students
FOR EACH ROW
SET NEW.Name = UPPER(NEW.Name);
Expected Output:
Trigger created.
