Core Lab 4: Programming Lab – RDBMS 20 30 50
1. Implementation of DDL commands of SQL with suitable examples Create table, Alter table, Drop
Table
2. Implementation of DML commands of SQL with suitable examples Insert, Update, Delete
[Link] a PL/SQL to insert a student record using procedure.
4. Write a PL/SQL to count number of employees in a department using function.
5. Write a PL/SQL to fetch selective records using cursors.
6. Creating Database /Table Space Managing Users: Create User, Delete User Managing roles:-Grant,
Revoke.
7. Create a table for Employee details with Employee Number as primary key and following fields:
Name, Designation, Gender, Age, Date of Joining and Salary. Insert at least ten rows and perform
various queries using any one Comparison, Logical, Set, Sorting and Grouping Operators.
8. Write a PL/SQL to update the rate field by 20% more than the current rate in inventory table
which has the following fields: Prono, ProName and Rate. After updating the table a new field (Alter)
called for Number of item and place for values for the new field without using PL/SQL block.
9. Write a PL/SQL program to implement the concept of Triggers
10. Write a PL/SQL program to implement the concept Procedures
Experiment 1: Implementation of DDL Commands
(CREATE TABLE, ALTER TABLE, DROP TABLE)
Aim
To create, modify, and delete a table using the SQL DDL commands CREATE TABLE,
ALTER TABLE, and DROP TABLE.
Algorithm
1. Start the Oracle SQL environment.
2. Connect to the database.
3. Create the Student table.
4. Execute the CREATE TABLE command.
5. Display the table structure.
6. Alter the table by adding a new column.
7. Execute the ALTER TABLE command.
8. Verify the modified table structure.
9. Drop the table using the DROP TABLE command.
10. Stop.
SQL Program (DDL Commands: CREATE TABLE,
ALTER TABLE, DROP TABLE)
-- Create Table
CREATE TABLE Student
(
RollNo NUMBER(5),
Name VARCHAR2(30),
Age NUMBER(2)
);
-- Display Table Structure
DESC Student;
-- Alter Table (Add a New Column)
ALTER TABLE Student
ADD Address VARCHAR2(50);
-- Display Modified Table Structure
DESC Student;
-- Drop Table
DROP TABLE Student;
Output
Table created.
Name Null? Type
---------- ------- ----------------
ROLLNO NUMBER(5)
NAME VARCHAR2(30)
AGE NUMBER(2)
Table altered.
Name Null? Type
---------- ------- ----------------
ROLLNO NUMBER(5)
NAME VARCHAR2(30)
AGE NUMBER(2)
ADDRESS VARCHAR2(50)
Table dropped.
Result
Thus, the SQL DDL commands CREATE TABLE, ALTER TABLE, and DROP TABLE
were executed successfully.
Experiment 2: DML Commands (INSERT, UPDATE,
DELETE)
Aim
To implement SQL DML commands INSERT, UPDATE, and DELETE on a table.
Algorithm
Step 1: Start the Oracle SQL environment.
Step 2: Create the Student table.
Step 3: Insert records into the table.
Step 4: Display the inserted records using the SELECT statement.
Step 5: Update the required record using the UPDATE statement.
Step 6: Display the updated records using the SELECT statement.
Step 7: Delete the required record using the DELETE statement.
Step 8: Display the remaining records using the SELECT statement.
Step 9: Commit the changes using the COMMIT statement.
Step 10: Stop the execution.
CREATE TABLE Student(
RollNo NUMBER(5),
Name VARCHAR2(30),
Age NUMBER(2)
);
INSERT INTO Student VALUES(101,'Ravi',20);
INSERT INTO Student VALUES(102,'Priya',21);
SELECT * FROM Student;
UPDATE Student
SET Age=22
WHERE RollNo=101;
DELETE FROM Student
WHERE RollNo=102;
SELECT * FROM Student;
COMMIT;
Output
Table created.
1 row inserted.
1 row inserted.
1 row updated.
1 row deleted.
Commit complete.
Result
Thus, the DML commands INSERT, UPDATE, and DELETE were executed successfully.
Experiment 3: PL/SQL Procedure to Insert Student
Record
Aim
To create and execute a PL/SQL procedure to insert a student record.
Step 1: Start the Oracle SQL environment.
Step 2: Create the Student table.
Step 3: Create a PL/SQL procedure named InsertStudent.
Step 4: Declare the input parameters for Roll Number, Name, and Age.
Step 5: Write the INSERT statement inside the procedure.
Step 6: Compile the procedure successfully.
Step 7: Execute the procedure by passing the student details.
Step 8: Commit the transaction to save the record.
Step 9: Display the inserted record using the SELECT statement.
Step 10: Stop the execution.
How to Run Experiment 3: PL/SQL Procedure to Insert
Student Record (Oracle SQL)
Step 1: Create the Student Table
Execute the following code:
CREATE TABLE Student(
RollNo NUMBER,
Name VARCHAR2(30),
Age NUMBER
);
Output:
Table created.
Step 2: Create the Procedure
Execute the following PL/SQL program:
CREATE OR REPLACE PROCEDURE InsertStudent
(
r NUMBER,
n VARCHAR2,
a NUMBER
)
IS
BEGIN
INSERT INTO Student VALUES(r,n,a);
END;
/
Note: The / on the last line tells Oracle to compile the procedure.
Output:
Procedure created.
Step 3: Execute the Procedure
Call the procedure using the EXEC command:
EXEC InsertStudent(101,'Ravi',20);
Output:
PL/SQL procedure successfully completed.
Step 4: Verify the Inserted Record
Display the table contents:
SELECT * FROM Student;
Output:
ROLLNO NAME AGE
------ ------ ---
101 Ravi 20
Complete Execution Order
Run the commands in this order:
CREATE TABLE Student(
RollNo NUMBER,
Name VARCHAR2(30),
Age NUMBER
);
CREATE OR REPLACE PROCEDURE InsertStudent
(
r NUMBER,
n VARCHAR2,
a NUMBER
)
IS
BEGIN
INSERT INTO Student VALUES(r,n,a);
END;
/
EXEC InsertStudent(101,'Ravi',20);
SELECT * FROM Student;
Expected Final Output
Table created.
Procedure created.
PL/SQL procedure successfully completed.
ROLLNO NAME AGE
------ ------ ---
101 Ravi 20
This is the standard execution sequence in Oracle SQL*Plus or Oracle SQL Developer.
PL/SQL Code
CREATE TABLE Student(
RollNo NUMBER,
Name VARCHAR2(30),
Age NUMBER
);
CREATE OR REPLACE PROCEDURE InsertStudent
(
r NUMBER,
n VARCHAR2,
a NUMBER
)
IS
BEGIN
INSERT INTO Student VALUES(r,n,a);
END;
/
EXEC InsertStudent(101,'Ravi',20);
SELECT * FROM Student;
Output
Procedure created.
PL/SQL procedure successfully completed.
ROLLNO NAME AGE
101 Ravi 20
Result
Thus, the student record was inserted successfully using a PL/SQL procedure.
Experiment 4: PL/SQL Function to Count Employees
Aim
To count the number of employees in a department using a PL/SQL function.
Algorithm
Step 1: Start the Oracle SQL environment.
Step 2: Create the Employee table.
Step 3: Insert employee records into the table.
Step 4: Create a PL/SQL function.
Step 5: Accept the department number as input.
Step 6: Count the employees in the department.
Step 7: Return the employee count.
Step 8: Compile the function.
Step 9: Execute the function.
Step 10: Stop the [Link]/SQL Code
CREATE OR REPLACE FUNCTION CountEmp
(dno NUMBER)
RETURN NUMBER
IS
c NUMBER;
BEGIN
SELECT COUNT(*)
INTO c
FROM Employee
WHERE DeptNo=dno;
RETURN c;
END;
/
SELECT CountEmp(10)
FROM Dual;
Output
Function created.
COUNTEMP
--------
5
Result
Thus, the PL/SQL function counted the employees successfully.
Experiment 5: PL/SQL Cursor
Aim
To fetch selected employee records using a PL/SQL cursor.
Algorithm
Step 1: Start the Oracle SQL environment.
Step 2: Create the Employee table.
Step 3: Insert records into the table.
Step 4: Declare a cursor for the required query.
Step 5: Open the cursor.
Step 6: Fetch the records from the cursor.
Step 7: Display the fetched records.
Step 8: Close the cursor.
Step 9: End the PL/SQL block.
Step 10: Stop the [Link]/SQL Code
DECLARE
CURSOR c IS
SELECT EmpNo,Name
FROM Employee
WHERE Salary>30000;
vno [Link]%TYPE;
vname [Link]%TYPE;
BEGIN
OPEN c;
LOOP
FETCH c INTO vno,vname;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vno||' '||vname);
END LOOP;
CLOSE c;
END;
/
Output
101 Ravi
103 Priya
105 Kumar
Result
Thus, selective records were fetched successfully using a PL/SQL cursor.
Experiment 6: Creating Tablespace and Managing Users
Aim
To create a tablespace, create a user, and manage user privileges using GRANT and
REVOKE commands.
Algorithm
Step 1: Start the Oracle SQL environment and log in as SYSDBA.
Step 2: Create a new tablespace with the required data file and size.
Step 3: Create a new database user and assign the tablespace.
Step 4: Set a password for the new user.
Step 5: Grant the required roles and privileges to the user.
Step 6: Log in using the newly created user account.
Step 7: Revoke the required privilege from the user.
Step 8: Delete (drop) the user from the database.
Step 9: Verify that the user and privileges have been updated successfully.
Step 10: Stop the [Link] Code
CREATE TABLESPACE student_ts
DATAFILE '[Link]'
SIZE 20M;
CREATE USER student
IDENTIFIED BY 1234
DEFAULT TABLESPACE student_ts;
GRANT CONNECT, RESOURCE TO student;
REVOKE RESOURCE FROM student;
DROP USER student;
Output
Tablespace created.
User created.
Grant succeeded.
Revoke succeeded.
User dropped.
Result
Thus, the tablespace was created and user management operations were completed
successfully.
Experiment 7: Employee Table and SQL Queries
Aim
To create an Employee table and perform SQL queries using comparison, logical, sorting,
and grouping operators.
Algorithm
Step 1: Start the Oracle SQL environment.
Step 2: Create the Employee table.
Step 3: Define Employee Number as the primary key.
Step 4: Insert at least ten employee records into the table.
Step 5: Execute comparison queries using operators such as =, >, or <.
Step 6: Execute logical queries using AND, OR, or NOT.
Step 7: Execute sorting queries using the ORDER BY clause.
Step 8: Execute grouping queries using the GROUP BY clause.
Step 9: Display the query results using the SELECT statement.
Step 10: Stop the [Link] Code
CREATE TABLE Employee(
EmpNo NUMBER PRIMARY KEY,
Name VARCHAR2(30),
Designation VARCHAR2(20),
Gender CHAR(1),
Age NUMBER,
DOJ DATE,
Salary NUMBER
);
INSERT INTO Employee VALUES(101,'Ravi','Manager','M',35,'10-JAN-20',50000);
SELECT * FROM Employee
WHERE Salary>30000;
SELECT * FROM Employee
WHERE Gender='M'
AND Salary>25000;
SELECT * FROM Employee
ORDER BY Salary DESC;
SELECT Gender,COUNT(*)
FROM Employee
GROUP BY Gender;
Output
Required employee records displayed.
Result
Thus, the Employee table was created and SQL queries were executed successfully.
Experiment 8: Update Inventory Rate
Aim
To update the product rate by 20% and add a new column to the Inventory table.
Algorithm
1. Start Oracle SQL.
2. Create the Inventory table.
3. Insert records.
4. Write a PL/SQL block.
5. Update the rate.
6. Commit the changes.
7. Alter the table.
8. Add a new column.
9. Update the new column values.
10. Stop.
PL/SQL Code
BEGIN
UPDATE Inventory
SET Rate=Rate*1.20;
COMMIT;
END;
/
ALTER TABLE Inventory
ADD Number_of_Items NUMBER;
UPDATE Inventory
SET Number_of_Items=100;
SELECT * FROM Inventory;
Output
PL/SQL procedure successfully completed.
Table altered.
Result
Thus, the inventory rate was updated and a new column was added successfully.
Experiment 9: PL/SQL Trigger
Aim
To implement the concept of a PL/SQL trigger.
Algorithm
Step 1: Start the Oracle SQL environment.
Step 2: Create the Employee table.
Step 3: Create a PL/SQL trigger on the Employee table.
Step 4: Compile the trigger successfully.
Step 5: Insert a new record into the Employee table.
Step 6: Execute the trigger automatically during the insert operation.
Step 7: Display the trigger message.
Step 8: Verify that the record has been inserted successfully.
Step 9: End the PL/SQL block.
Step 10: Stop the [Link]/SQL Code
CREATE OR REPLACE TRIGGER EmpTrigger
BEFORE INSERT
ON Employee
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee Record Inserted');
END;
/
INSERT INTO Employee
VALUES(111,'Kiran','Clerk','M',25,'12-MAR-22',25000);
Output
Trigger created.
Employee Record Inserted.
1 row inserted.
Result
Thus, the PL/SQL trigger was created and executed successfully.
Experiment 10: PL/SQL Procedure
Aim
To create and execute a simple PL/SQL procedure.
Algorithm
Step 1: Start the Oracle SQL environment.
Step 2: Create a PL/SQL procedure.
Step 3: Write the procedure body with the required SQL statements.
Step 4: Compile the procedure successfully.
Step 5: Execute the procedure.
Step 6: Display the output.
Step 7: Verify the result.
Step 8: End the PL/SQL procedure.
Step 9: Exit the Oracle SQL environment.
Step 10: Stop the [Link]/SQL Code
CREATE OR REPLACE PROCEDURE DisplayMessage
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Welcome to PL/SQL');
END;
/
EXEC DisplayMessage;
Output
Procedure created.
Welcome to PL/SQL
PL/SQL procedure successfully completed.
Result
Thus, the PL/SQL procedure was created and executed successfully.