Prog 1 : Create a table called Employee & execute the following.
Employee(EMPNO,ENAME,JOB, MANAGER_NO, SAL, COMMISSION)
1. Insert the any three records in the employee table contains attributes
EMPNO,ENAME JOB, MANAGER_NO, SAL, COMMISSION and use rollback. Check
the result.
2. Add Primary key constraint and NOT NULL constraint to the employee table.
3. Insert NULL values to the employee table and verify the result.
-------------------------------------------------------------------------
SQL> CREATE TABLE Employee (
2 EMPNO INT,
3 ENAME VARCHAR(10),
4 JOB VARCHAR(10),
5 MANAGER_NO INT,
6 SAL DECIMAL(10, 2),
7 COMMISSION DECIMAL(10, 2)
8 );
Table created.
SQL> Desc Employee;
Name Null? Type
----------------------------------------- -------- ---------------------
EMPNO NUMBER(38)
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MANAGER_NO NUMBER(38)
SAL NUMBER(10,2)
COMMISSION NUMBER(10,2)
1. Insert the any three records in the employee table contains attributes EMPNO,ENAME
JOB, MANAGER_NO, SAL, COMMISSION and use rollback.
Check the result.
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,COMMISSION)
2 VALUES (101, 'Rajath', 'Developer', 1002, 32540, 500);
1 row created.
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,COMMISSION)
2 VALUES ('', 'Ellyse', 'Analyst', 1003, 50000, NULL);
1 row created.
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,COMMISSION)
2 VALUES (101, 'Ahaan', 'Developer', 1002, 32540, 500);
1 row created.
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,COMMISSION)
2 VALUES (102,NULL, 'Developer', 1002, 32540, 500);
1 row created.
SQL> select * from Employee;
EMPNO ENAME JOB MANAGER_NO SAL COMMISSION
---------- ---------- ---------- ---------- ---------- ----------
101 Rajath Developer 1002 32540 500
Ellyse Analyst 1003 50000
101 Ahaan Developer 1002 32540 500
102 Developer 1002 32540 500
SQL> rollback;
Rollback complete.
SQL> select * from Employee;
no rows selected
2. Add Primary key constraint and NOT NULL constraint to the employee table.
SQL> ALTER TABLE Employee
2 ADD CONSTRAINT pk_emp PRIMARY KEY (EMPNO);
Table altered.
SQL> Desc Employee;
Name Null? Type
----------------------------------------- -------- ---------------------
EMPNO NOT NULL NUMBER(38)
ENAME VARCHAR2(10)
JOB VARCHAR2(10)
MANAGER_NO NUMBER(38)
SAL NUMBER(10,2)
COMMISSION NUMBER(10,2)
SQL> ALTER TABLE Employee MODIFY
2 (
3 ENAME VARCHAR(10) NOT NULL,
4 JOB VARCHAR(10) NOT NULL,
5 MANAGER_NO INT NOT NULL,
6 SAL DECIMAL(10, 2) NOT NULL,
7 COMMISSION DECIMAL(10, 2) NOT NULL
8 );
Table altered.
SQL> Desc Employee;
Name Null? Type
----------------------------------------- -------- ---------------------
EMPNO NOT NULL NUMBER(38)
ENAME NOT NULL VARCHAR2(10)
JOB NOT NULL VARCHAR2(10)
MANAGER_NO NOT NULL NUMBER(38)
SAL NOT NULL NUMBER(10,2)
COMMISSION NOT NULL NUMBER(10,2)
3. Insert NULL values to the employee table and verify the result.
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,COMMISSION)
2 VALUES (101, 'Rajath', 'Developer', 1002, 32540, 500);
1 row created.
SQL> select * from Employee;
EMPNO ENAME JOB MANAGER_NO SAL COMMISSION
---------- ---------- ---------- ---------- ---------- ----------
101 Rajath Developer 1002 32540 500
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,COMMISSION)
2 VALUES (101, 'Ahaan', 'Developer', 1002, 32540, 500);
INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.PK_EMP) violated
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,COMMISSION)
2 VALUES (102,NULL, 'Developer', 1002, 32540, 500);
VALUES (102,NULL, 'Developer', 1002, 32540, 500)
*
ERROR at line 2:
ORA-01400: cannot insert NULL into ("SYSTEM"."EMPLOYEE"."ENAME")
PROG 2 : Create a table called Employee that contain attributes EMPNO, ENAME,
JOB, MGR, SAL & execute the following.
1. Add a column commission with domain to the Employee table.
2. Insert any five records into the table.
3. Update the column details of job.
4. Rename the column of Employ table using alter command.
5. Delete the employee whose Empno is 105.
( MGR : Manager ID )
-------------------------------------------------------------------------
SQL> CREATE TABLE Employee(
2 EMPNO INT PRIMARY KEY,
3 ENAME VARCHAR(20),
4 JOB VARCHAR(20),
5 MGR INT,
6 SAL DECIMAL(10, 2)
7 );
Table created.
SQL> desc Employee;
Name Null? Type
----------------------------------------- -------- ---------------------
EMPNO NOT NULL NUMBER(38)
ENAME VARCHAR2(20)
JOB VARCHAR2(20)
MGR NUMBER(38)
SAL NUMBER(10,2)
1. Add a column commission with domain to the Employee table
SQL> ALTER TABLE Employee
2 ADD COMMISSION DECIMAL(10, 2);
Table altered.
SQL> desc Employee;
Name Null? Type
----------------------------------------- -------- ---------------------
EMPNO NOT NULL NUMBER(38)
ENAME VARCHAR2(20)
JOB VARCHAR2(20)
MGR NUMBER(38)
SAL NUMBER(10,2)
COMMISSION NUMBER(10,2)
2. Insert any five records into the table.
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION)
2 VALUES(105, 'David ', 'Assistant', 101, 3500.00, 40.00);
1 row created.
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION)
2 VALUES(106, 'Smitha', 'Developer', 101, 3500.00, 40.00);
1 row created.
SQL> INSERT INTO Employee(EMPNO, ENAME, JOB, MGR, SAL, COMMISSION)
2 VALUES (203, 'Charlie', 'Developer', 201, 5000.00, 180.00);
1 row created.
SQL> INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION)
2 VALUES (205, 'Ellyse', 'Assistant', 201, 4000.00, 120.00);
1 row created.
INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION)
2 VALUES (206, 'John', 'Designer', 201, 7000.00, 140.00);
1 row created.
SQL> Select * from Employee;
EMPNO ENAME JOB MGR SAL COMMISSION
---------- ---------- ---------------- ---------- ---------- ----------
105 David Assistant 101 3500 40
106 Smitha Developer 101 3500 40
203 Charlie Developer 201 5000 180
205 Ellyse Assistant 201 4000 120
206 John Designer 201 7000 140
3. Update the column details of job
SQL> UPDATE Employee
2 SET JOB = 'Senior ' || JOB
3 WHERE JOB IN ('Manager', 'Analyst', 'Developer', 'Designer',
'Assistant');
5 rows updated.
SQL> Select * from Employee;
EMPNO ENAME JOB MGR SAL COMMISSION
---------- ---------- ---------------- ---------- ---------- ----------
105 David Senior Assistant 101 3500 40
106 Smitha Senior Developer 101 3500 40
203 Charlie Senior Developer 201 5000 180
205 Ellyse Senior Assistant 201 4000 120
206 John Senior Designer 201 7000 140
4. Rename the column of Employ table using alter command.
SQL> ALTER TABLE Employee
2 RENAME COLUMN JOB TO POSITION;
Table altered.
SQL> desc Employee;
Name Null? Type
----------------------------------------- -------- ---------------------
EMPNO NOT NULL NUMBER(38)
ENAME VARCHAR2(20)
POSITION VARCHAR2(20)
MGR NUMBER(38)
SAL NUMBER(10,2)
COMMISSION NUMBER(10,2)
5. Delete the employee whose Empno is 105.
SQL> DELETE FROM Employee
2 WHERE EMPNO = 105;
1 row deleted.
SQL> Select * from Employee;
EMPNO ENAME POSITION MGR SAL COMMISSION
---------- ---------- ---------------- ---------- ---------- ----------
106 Smitha Senior Developer 101 3500 40
203 Charlie Senior Developer 201 5000 180
205 Ellyse Senior Assistant 201 4000 120
206 John Senior Designer 201 7000 140
PROG 3 : Queries using aggregate functions(COUNT,AVG,MIN,MAX,SUM),Group by,
Orderby for table Employee(E_id, E_name, Age, Salary)
1. Create Employee table containing all Records E_id, E_name, Age, Salary.
2. Count number of employee names from employee table.
3. Find the Maximum age from employee table.
4. Find the Minimum age from employee table.
5. Find salaries of employee in Ascending Order.
6. Find grouped salaries of employees.
-------------------------------------------------------------------------
1. Create Employee table containing all Records E_id, E_name, Age, Salary.
SQL> create table Employee (
2 E_Id int primary key,
3 E_Name varchar(20),
4 Age int,
5 Salary Decimal(10,2)
6 );
Table created.
SQL> desc Employee;
Name Null? Type
----------------------------------------- -------- ---------------------
E_ID NOT NULL NUMBER(38)
E_NAME VARCHAR2(20)
AGE NUMBER(38)
SALARY NUMBER(10,2)
SQL> insert into Employee (E_Id, E_Name, Age, Salary)
2 values (1, 'Alice', 30, 50000);
1 row created.
SQL> insert into Employee (E_Id, E_Name, Age, Salary)
2 values (2, 'David', 22, 45000);
1 row created.
SQL> insert into Employee (E_Id, E_Name, Age, Salary)
2 values (3, 'Charlie', 25, 55000);
1 row created.
SQL> insert into Employee (E_Id, E_Name, Age, Salary)
2 values (4, 'Perry', 28, 60000);
1 row created.
SQL> insert into Employee (E_Id, E_Name, Age, Salary)
2 values (5, 'Smith', 35, 50000);
1 row created.
SQL> select * from Employee;
E_ID E_NAME AGE SALARY
---------- -------------------- ---------- ----------
1 Alice 30 50000
2 David 22 45000
3 Charlie 25 55000
4 Perry 28 60000
5 Smith 35 50000
2. Count number of employee names from employee table.
SQL> select COUNT(E_Id) AS NumberOfEmployees from Employee;
NUMBEROFEMPLOYEES
-----------------
5
3. Find the Maximum age from employee table.
SQL> SELECT MAX(Age) AS MaximumAge FROM Employee;
MAXIMUMAGE
----------
35
4. Find the Minimum age from employee table.
SQL> SELECT MIN(Age) AS MinimumAge FROM Employee;
MINIMUMAGE
----------
22
5. Find salaries of employee in Ascending Order.
SQL> SELECT Salary FROM Employee
2 ORDER BY Salary ASC;
SALARY
----------
45000
50000
50000
55000
60000
6. Find grouped salaries of employees.
SQL> SELECT Salary, COUNT(E_id) AS NumberOfEmployees
2 FROM Employee
3 GROUP BY Salary;
SALARY NUMBEROFEMPLOYEES
---------- -----------------
50000 2
45000 1
55000 1
60000 1
4. Create a row level trigger for the customers table that would fire for
INSERT or UPDATE or DELETE operations performed on the CUSTOMERS
table. This trigger will display the salary difference between the old &
new Salary.
CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY)
SQL> SET SERVEROUTPUT ON;
SQL> CREATE TABLE CUSTOMERS
2 (
3 ID NUMBER PRIMARY KEY,
4 NAME VARCHAR2(20),
5 AGE NUMBER,
6 ADDRESS VARCHAR2(50),
7 SALARY NUMBER
8 );
Table created.
SQL> CREATE OR REPLACE TRIGGER trg_salary_difference
2 AFTER INSERT OR UPDATE OR DELETE ON CUSTOMERS
3 FOR EACH ROW
4 DECLARE
5 v_old_salary [Link]%TYPE;
6 v_new_salary [Link]%TYPE;
7 v_salary_diff NUMBER;
8 BEGIN
9 IF INSERTING THEN
10 v_old_salary := 0;
11 v_new_salary := :[Link];
12 ELSIF UPDATING THEN
13 v_old_salary := :[Link];
14 v_new_salary := :[Link];
15 ELSIF DELETING THEN
16 v_old_salary := :[Link];
17 v_new_salary := 0;
18 END IF;
19 v_salary_diff := v_new_salary - v_old_salary;
20 DBMS_OUTPUT.PUT_LINE('Salary difference:'|| v_salary_diff);
21 END;
22 /
Trigger created.
SQL> INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)
2 VALUES (5, 'John Doe', 30, '123 Elm St', 55000);
Salary difference: 55000
1 row created.
SQL> UPDATE CUSTOMERS SET SALARY = 60000 WHERE ID = 5;
Salary difference: 5000
1 row updated.
SQL> DELETE FROM CUSTOMERS WHERE ID = 5;
Salary difference: -60000
1 row deleted.
Note:
The trigger trg_salary_difference is designed to respond to INSERT, UPDATE, and
DELETE operations on the CUSTOMERS table. It calculates the difference between
the old and new salary values and outputs this difference. For INSERT operations, it
considers the old salary as 0 (since it's a new record). For UPDATE operations, it
calculates the difference between the updated value and the existing value. For
DELETE operations, it calculates the difference as the negative value of the old salary
(since the record is being removed). This setup ensures that any change in the salary
is automatically logged whenever a relevant operation is performed on the
CUSTOMERS table.
PROG 5: Create cursor for Employee table & extract the values from the table.
Declare the variables ,Open the cursor & extract the values from the cursor. Close the
cursor. Employee(E_id, E_name, Age, Salary) .
Cursor is a Temporary Memory or Temporary Work Station. It is Allocated by
Database Server at the Time of Performing DML(Data Manipulation Language)
operations on the Table by the User. Cursors are used to store Database Tables.
SQL> Create table Employee (
2 E_id NUMBER PRIMARY KEY,
3 E_name VARCHAR2(20),
4 Age NUMBER,
5 Salary NUMBER
6 );
Table created.
SQL> desc Employee;
Name Null? Type
----------------------------------------- -------- -----------------
E_ID NOT NULL NUMBER
E_NAME VARCHAR2(20)
AGE NUMBER
SALARY NUMBER
Insert Sample Data :
SQL> INSERT INTO Employee VALUES (1, 'John Doe', 30, 50000);
1 row created.
SQL> INSERT INTO Employee VALUES (2, 'Jane Smith', 25, 60000);
1 row created.
SQL> INSERT INTO Employee VALUES (3, 'Emily Johnson', 40, 70000);
1 row created.
SQL> INSERT INTO Employee VALUES (4, 'Michael Brown', 35, 55000);
1 row created.
SQL> select * from Employee;
E_ID E_NAME AGE SALARY
---------- -------------------- ---------- ----------
1 John Doe 30 50000
2 Jane Smith 25 60000
3 Emily Johnson 40 70000
4 Michael Brown 35 55000
Create the Procedure named Fetch_Employee_Data to fetch and display data from
the Employee table:
SQL> CREATE OR REPLACE PROCEDURE Fetch_Employee_Data IS
2 v_E_id Employee.E_id%TYPE;
3 v_E_name Employee.E_name%TYPE;
4 v_Age [Link]%TYPE;
5 v_Salary [Link]%TYPE;
6 CURSOR emp_cursor IS
7 SELECT E_id, E_name, Age, Salary
8 FROM Employee;
9 BEGIN
10 OPEN emp_cursor;
11 LOOP
12 FETCH emp_cursor INTO v_E_id, v_E_name, v_Age, v_Salary;
13 EXIT WHEN emp_cursor%NOTFOUND;
14 DBMS_OUTPUT.PUT_LINE('E_id: ' || v_E_id || ', E_name: ' ||
v_E_name || ', Age: ' || v_Age || ', Salary: ' || v_Salary);
15 END LOOP;
16 CLOSE emp_cursor;
17 END;
18 /
Procedure created.
Execute the procedure and display the output:
SQL> SET SERVEROUTPUT ON;
SQL> BEGIN
2 Fetch_Employee_Data;
3 END;
4 /
E_id: 1, E_name: John Doe, Age: 30, Salary: 50000
E_id: 2, E_name: Jane Smith, Age: 25, Salary: 60000
E_id: 3, E_name: Emily Johnson, Age: 40, Salary: 70000
E_id: 4, E_name: Michael Brown, Age: 35, Salary: 55000
PL/SQL procedure successfully completed.
Drop Procedure:
SQL> DROP PROCEDURE Fetch_Employee_Data;
Procedure dropped.
Drop the Table Employee :
SQL> DROP Table Employee;
Table dropped.
SQL>
PROG6: Write a PL/SQL block of code using parameterized Cursor, that
will merge the data available in the newly created table N_RollCall with the data
available in the table O_RollCall. If the data in the first table already exist in the second
table, then that data should be skipped.
Solution:
To accomplish this task in MySQL, we can use a stored procedure with a
parameterized cursor to merge data from one table (N_RollCall) into another table
(O_RollCall) while skipping existing data. We’ll iterate through the records of
N_RollCall and insert them into O_RollCall only if they do not already exist.
1. Create the Tables:
Create N_RollCall table :-
SQL> create table N_RollCall (
2 student_id INT PRIMARY KEY,
3 student_name VARCHAR(20),
4 birth_date DATE
5 );
Table created.
Create O_RollCall table with common data :-
SQL> create table O_RollCall (
2 student_id INT PRIMARY KEY,
3 student_name VARCHAR(20),
4 birth_date DATE
5 );
Table created.
SQL> desc N_RollCall;
Name Null? Type
----------------------------------------- -------- -----------------
STUDENT_ID NOT NULL NUMBER(38)
STUDENT_NAME VARCHAR2(20)
BIRTH_DATE DATE
SQL> desc O_RollCall;
Name Null? Type
----------------------------------------- -------- -----------------
STUDENT_ID NOT NULL NUMBER(38)
STUDENT_NAME VARCHAR2(20)
BIRTH_DATE DATE
2. Add Sample Records to both tables
Insert common data into O_RollCall :
SQL> INSERT INTO O_RollCall (student_id, student_name, birth_date)
2 VALUES (1, 'Alex', TO_DATE('1995-08-15', 'YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO O_RollCall (student_id, student_name, birth_date)
2 VALUES (2, 'Smitha', TO_DATE('1996-10-01', 'YYYY-MM-DD'));
1 row created.
Insert common data into N_RollCall :
SQL> INSERT INTO N_RollCall (student_id, student_name, birth_date)
2 VALUES (1, 'Alex', TO_DATE('1995-08-15', 'YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO N_RollCall (student_id, student_name, birth_date)
2 VALUES (2, 'Smitha', TO_DATE('1996-10-01', 'YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO N_RollCall (student_id, student_name, birth_date)
2 VALUES(3, 'perry', TO_DATE('1996-08-15', 'YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO N_RollCall (student_id, student_name, birth_date)
2 VALUES(4, 'Bob', TO_DATE('2000-01-23', 'YYYY-MM-DD'));
1 row created.
SQL> INSERT INTO N_RollCall (student_id, student_name, birth_date)
2 VALUES(5, 'Ahaan', TO_DATE('1999-02-25', 'YYYY-MM-DD'));
1 row created.
SQL> select * from O_RollCall;
STUDENT_ID STUDENT_NAME BIRTH_DAT
---------- -------------------- ---------
1 Alex 15-AUG-95
2 Smitha 01-OCT-96
SQL> select * from N_RollCall;
STUDENT_ID STUDENT_NAME BIRTH_DAT
---------- -------------------- ---------
1 Alex 15-AUG-95
2 Smitha 01-OCT-96
3 perry 15-AUG-96
4 Bob 23-JAN-00
5 Ahaan 25-FEB-99
Define the merge_rollcall_data stored procedure to merge records from N_RollCall
into O_RollCall, skipping existing records:
SQL> CREATE OR REPLACE PROCEDURE merge_rollcall_data IS
2 CURSOR n_cursor IS
3 SELECT student_id, student_name, birth_date
4 FROM N_RollCall;
5 v_student_id N_RollCall.student_id%TYPE;
6 v_student_name N_RollCall.student_name%TYPE;
7 v_birth_date N_RollCall.birth_date%TYPE;
8 BEGIN
9 OPEN n_cursor;
10 LOOP
11 FETCH n_cursor INTO v_student_id, v_student_name, v_birth_date;
12 EXIT WHEN n_cursor%NOTFOUND;
13 BEGIN
14 -- Check if the data already exists in O_RollCall
15 INSERT INTO O_RollCall (student_id, student_name, birth_date)
16 VALUES (v_student_id, v_student_name, v_birth_date);
17 EXCEPTION
18 WHEN DUP_VAL_ON_INDEX THEN
19 -- If a duplicate is found, skip to the next record
20 NULL;
21 END;
22 END LOOP;
23 CLOSE n_cursor;
24 END;
25 /
Procedure created.
Note:
The stored procedure merge_rollcall_data uses a cursor (n_cursor) to iterate
through the records of the N_RollCall table.
Inside the cursor loop (cursor_loop), each record (n_id, n_name, n_date) from
N_RollCall is fetched and checked against the O_RollCall table.
If the record does not already exist in O_RollCall (checked using NOT EXISTS), it is
inserted into O_RollCall.
The cursor loop continues until all records from N_RollCall have been processed.
The cursor is then closed (CLOSE n_cursor).
Execute the Stored Procedure :
Execute the merge_rollcall_data stored procedure to merge records from
N_RollCall into O_RollCall while skipping existing records:
SQL> CALL merge_rollcall_data();
Call completed.
Verify Records in O_RollCall :
After executing the procedure, verify the records in the O_RollCall table to
confirm that new records from N_RollCall have been inserted, while existing common
records have been skipped:
SQL> SELECT * FROM O_RollCall;
STUDENT_ID STUDENT_NAME BIRTH_DAT
---------- -------------------- ---------
1 Alex 15-AUG-95
2 Smitha 01-OCT-96
3 perry 15-AUG-96
4 Bob 23-JAN-00
5 Ahaan 25-FEB-99