Practical 1: Create a table named uscs_employee having attributes such as: Employee id,

Employee name, Employee’s department number, Employee’s date of joining, Employee’s

salary, Employee’s email_id and Employee’s contact number.
Query:
CREATE TABLE uscs_employee
(employee_id VARCHAR2(10), employee_name CHAR(25), employee_department_no NUMBER(3),
employee_date_of_joining DATE, employee_salary NUMBER(8,2), employee_email_id VARCHAR2(30),
employee_contact_no NUMBER(12) );

i) Insert at least 10 tuples in table.

Query:
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1001','DIKSHANT PANTHRI','111','14-09-2023','50000.00','dikshant@gmail.com','9897202251');
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1002','DIWAKAR GAUTAM','222','14-08-2023','60000.00','diwakar@gmail.com','6578202251');
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1003','NITISH KHADKA','333','14-07-2023','70000.00','nitish@gmail.com','3512202251');
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1004','SUMIT KUMAR','444','14-06-2023','80000.00','sumit@gmail.com','6554202251');
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1005','DEEPANSH SHARMA','555','14-05-2023','90000.00','deepansh@gmail.com','2584202251');
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1006','PRIYA RATHOD','666','14-04-2023','30000.00','priya@gmail.com','9165202251');
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1007','LAVANYA RAWAT','777','14-03-2023','40000.00','lavanya@gmail.com','3586202251');
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1008','ANUSHKA SHARMA','888','14-02-2023','35000.00','anushka@gmail.com','9897204611');
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1009','SRISHTI MISHRA','991','14-12-2022','55000.00','srishti@gmail.com','9897206844');
INSERT INTO uscs_employee (employee_id, employee_name, employee_department_no,
employee_date_of_joining, employee_salary, employee_email_id, employee_contact_no)
VALUES ('UU1010','SHAGUN VERMA','999','14-11-2022','45000.00','shagun@gmail.com','6486202251');

Output:
ii) Display employee’s complete details including employee_id, employee_name,
employee_department_no, employee_date_of_joining, employee_salary,
employee_email_id, and employee_contact_no.
Query:
SELECT * FROM uscs_employee;

Output:
iii) Display employee’s complete details including employee_id, employee_name,
employee_department_no, employee_date_of_joining, employee_salary,
employee_email_id, and employee_contact_no who work in department number 444.
Query:
SELECT * FROM uscs_employee WHERE employee_department_no LIKE '444';

Output:
iv) Display employee_id, employee_name, employee_date_of_joining who work in
department number 333.
Query:
SELECT employee_id, employee_name, employee_date_of_joining FROM uscs_employee WHERE
employee_department_no LIKE '333';

Output:

v) Delete the employee’s details having employee_id as UU1003.

Query:
DELETE FROM uscs_employee WHERE employee_id LIKE 'UU1003';

Output:

vi) Update employee_contact_no to 9592929295 having employee_id as UU1007.

Query:
UPDATE uscs_employee SET employee_contact_no='9592929295'
WHERE employee_id LIKE 'UU1007';

Output:
Practical 2: Implement DDL and DML on the uim_employee table. Consider the
following employee table:
Attribute Datatype Size
employee_id Varchar2 10
employee_name Char 25
employee_department_no Number 03
employee_date_of_joining Date -
employee_salary Number 8,2
employee_email_id Varchar2 30
employee_contact_no Number 12

Query:
CREATE TABLE uim_employee
(employee_id VARCHAR2(10), employee_name CHAR(25), employee_department_no NUMBER(3),
employee_date_of_joining DATE, employee_salary NUMBER(8,2), employee_email_id varchar2(30),
employee_contact_no NUMBER(12));

Output:

i) Add a new column named employee_address having data type as varchar2, size 30 in the
uim_employee table.
Query:
ALTER TABLE uim_employee
ADD employee_address VARCHAR2(30);

Output:

ii) Update the addresses of existing uim_employees in the table.

Query:
UPDATE uim_employee
SET employee_address = 'DEHRADUN';

Output:

iii)Drop the column named employee_date_of_joining from the table.

Query:
ALTER TABLE uim_employee
DROP COLUMN employee_date_of_joining;

Output:

iv) Modify the size of the column named employee_contact_no to 14.

Query:
ALTER TABLE uim_employee
MODIFY emplolyee_contact_no NUMBER(14);

Output:
v) Rename the table to employee_details from the table name uim_employee.
Query:
ALTER TABLE uim_employee
RENAME TO employee_details;

Output:

vi) Truncate as the records from the employee_details table.

Query:
TRUNCATE TABLE employee_details;

Output:

vii) Drop the table named employee_details.

Query:
DROP TABLE employee_details;

Output:
Practical 3: Implement of keys and constraints concept. Create a table named
uim_student having attributes such as: student's roll number, student's name, student's date
of birth, student's course, student's house address, student's contact number, student's
aadhaar number. The attribute named: student_roll_no has a PRIMARY KEY constraint,
student_name has NOT NULL constraint, student_ aadhaar_no as UNIQUE constraint.
Consider the following table:

Attribute Datatype Size Constraint

student_roll_no Number 3 PRIMARY KEY
student_name Char 25 NOT NULL
student_date_of_birt Date - -
h
student_course Varchar 15 -
student_address Varchar2 30 -
student_contact_no Number 10 -
student_aadhaar_no Number 12 UNIQUE

Query:
CREATE TABLE uim_student
(student_roll_no NUMBER(3), student_name CHAR(25), student_date_of_birth DATE, student_course
VARCHAR(15),
student_address VARCHAR2(30), student_contact_no NUMBER(10),
student_aadhaar_no NUMBER(12));

Output:

i) Describe the structure of uim student table.

Query:
DESCRIBE uim_student;

Output:

ii) Insert few tuples in the table.

Query:
INSERT INTO uim_student
(student_roll_no,student_name,student_date_of_birth,student_course,student_address,student_contact_no,
student_aadhaar_no)
VALUES ("111","DIKSHANT PANTHRI","10-09-2004","BCA","HARIDWAR","9897202251",
"841631486484");
INSERT INTO uim_student
(student_roll_no,student_name,student_date_of_birth,student_course,student_address,student_contact_no,
student_aadhaar_no)
VALUES ("222","KARTIK KANT GUPTA","15-10-2004","BCA","VIKASNAGAR","6485634814",
"698488915351");
INSERT INTO uim_student
(student_roll_no,student_name,student_date_of_birth,student_course,student_address,student_contact_no,
student_aadhaar_no)
VALUES ("333","SHIVAKSHI RAWAT","28-11-2003","BCA","DEHRADUN","9684346486",
"634858431446");
Output:
iii) Examine the error message by inserting same student_roll_no values for two rows.
Write the error message and reason.
Query:
INSERT INTO uim_student
(student_roll_no,student_name,student_date_of_birth,student_course,student_address,student_contact_no,
student_aadhaar_no)
VALUES ("111","ABHINAV SINGH","11-09-2004","BCA","HARIDWAR","9726842517",
"841631486348");

Output:

iv) Examine the error message by NOT inserting student_name value in a row in the table.
Write the error message and reason.
Query:
INSERT INTO uim_student
(student_roll_no,student_date_of_birth,student_course,student_address,student_contact_no,student_aadha
ar_no)
VALUES ("444","14-01-2004","BCA","DEHRADUN","9519842517", "654831486348");

Output:

v) Examine the error message by inserting same student aadhaar_no values for two rows.
Write the error message and reason
Query:
INSERT INTO uim_student
(student_roll_no,student_name,student_date_of_birth,student_course,student_address,student_contact_no,
student_aadhaar_no)
VALUES ("444","ADARSH","05-08-2002","BCA","PREMNAGAR","4985634814", "698488915351");

Output:
Practical 4: Implement of foreign key concept using two tables named: uscs_employee
and uscs_department. The employee has employee’s id, employee’s name, employee’s
department number. The department table has department name and department location.
Consider the following two table:

Table name: uscs_employee Table name: uscs_department

Attribute Data Size Constraint Attribute Data Size Constraint
Type Type
employee_ Char 8 - department_ Char 8 -
id id
employee_ Varchar 20 - department_ Varchar 20 -
name 2 name 2
employee_ Number 3 FOREIGN department_ Number 3 FOREIGN
departmen KEY location KEY
t_no
Ans.
i) Display the structure of uscs_employee table.
Query:
desc uscs_employee;

Output:

ii) Display the structure of uscs_department table.

Query:
DESC uscs_department;

Output:

iii) Insert at least three department details in uscs_department table.

Query:
mysql> INSERT INTO uscs_department Values('101','BCA','USCS Building'); Query OK, 1
row affected (0.00 sec)
mysql> INSERT INTO uscs_department Values('102','BSc IT','USCS Building'); Query OK,
1 row affected (0.00 sec)
mysql> INSERT INTO uscs_department Values('103','MCA','USCS Building'); Query OK,
1 row affected (0.00 sec)
iv) Display the data of uscs_department table.

Query:
select * from uscs_department;
Output: -
v) Insert employee’s details working in the corresponding department as in the
uscs_department table.
Query:
mysql> INSERT INTO uscs_employee Values('111','Aman Yadav','101'); Query OK, 1
row affected (0.00 sec)
mysql> INSERT INTO uscs_employee Values('222','Kamal Singh','102'); Query OK, 1
row affected (0.00 sec)
mysql> INSERT INTO uscs_employee Values('333','Armaan Malik','103'); Query OK, 1
row affected (0.00 sec)

Output:

vi) Display the data of uscs_employee table.

Query:
select * from uscs_employee;
Output:

vii) Examine the error message by inserting a value in employee_department_no which

is NOT there in uscs_department table’s department_no. Write the error message and
reason.
Query:
INSERT INTO uscs_employee Values('444','Lakshay Sharma','104');
Output:

viii) Delete any department number from uscs_department table and examine its effects
in uscs_employee table.
Query:
DELETE FROM uscs_department WHERE department_no = 103;
Output: -
Practical 5: Create a table named employee_contact_details from
employee table by taking the attribute named: employee_id.
Consider the table below:
Table name: employee_contact_details
Attribute Datatype Size
employee_id employee_id from employee table

i) Display the contents of employee_contact_details table.

Query:
SELECT * FROM employee_contact_details;
Output:

ii) Add a new column employee_contact_no having datatype as Number

and size as 12 in employee_contact_details table.
Query:
ALTER TABLE employee_contact_details ADD employee_contact_no NUMBER(12);
Output:

iii) Display the contents of employee_contact_details table.

Query:
SELECT * FROM employee_contact_details;
Output:

iv) Update the contact details of existing employees.

Query:
UPDATE employee_contact_details
SET employee_contact_no='9782564132' WHERE employee_id='UU1001';
UPDATE employee_contact_details
SET employee_contact_no='9785263412' WHERE employee_id='UU1002';
UPDATE employee_contact_details
SET employee_contact_no='9996663354' WHERE employee_id='UU1003';
UPDATE employee_contact_details
SET employee_contact_no='97845251634' WHERE employee_id='UU1004';
UPDATE employee_contact_details
SET employee_contact_no='9753159852' WHERE employee_id='UU1005';

Output:

v) Display the updated content of employee_contact_details table.

Query:
SELECT * FROM employee_contact_details;

Output:
Practical 6: Create a table named uscs_book having the attributes related
to book_id, book_name and book_theme attribute can have only two values:
BCA or MCA. Consider the table below:
Table name: uscs_book
Attribute Datatype Size Constraint
book_id Number 10 PRIMARY KEY
book_name Varchar2 25 NOT NULL
book_theme Char 4 CHECK

CREATE TABLE uscs_book (book_id NUMBER(10)

PRIMARY KEY, book_name VARCHAR2(25) NOT NULL,
book_theme CHAR(4), CHECK (book_theme IN (‘IT’,
‘MGT’));

i) Describe the structure of uscs_book table.

Query:
Describe uscs_book;

Output:

ii) Insert few tuples in the uscs_book table.

Query:
INSERT INTO uscs_book (book_id, book_name, book_theme)
VALUES (1, ‘Introduction to Databases’, ‘IT’);
INSERT INTO uscs_book (book_id, book_name, book_theme)
VALUES (2, ‘Management Principles’, ‘MGT’);

Output:

iii) Display the contents of uscs_book table.

Query:
SELECT * FROM uscs_book;

Output:

iv) Examine the error message by inserting a value other than IT/MGT in
column named book_theme. Write the error message and reason.
Query:
INSERT INTO uscs_book (book_id, book_name, book_theme)
VALUES (3, ‘Introduction to SQL Queries’, ‘MGMT’);

Output:
Practical 7: Extract the table from both the table by performing
join. Given two tables named: uscs_employee and uscs_department.
The employee table has attributes related to the employee’s id,
employee’s name and employee’s department number. The
department table has attributes related to the department number,
department name and department location. Consider the following
two tables:
Table name: uscs_employee
Attribute Datatype Size Constraint
employee_id Varchar2 8 -
employee_name Char 20 -
employee_department_n Number 3 FOREIGN KEY
o

Table name: uscs_department

Attribute Datatyp Size Constraint
e
department_no Number 8 PRIMARY KEY
department_name Char 20 -
department_location Varchar2 3 -

CREATE TABLE uscs_department (department_no NUMBER

(8) PRIMARY KEY, department_name CHAR (20),
department_location VARCHAR2(3));
CREATE TABLE uscs_employee (employee_id VARCHAR2(8),
employee_name CHAR (20), employee_department_no
NUMBER (30), FOREIGN KEY (employee_department_no)
REFERENCE uscs_department (department_no));

i) Display the structure of uscs_employee table

Query:
DESCRIBE uscs_employee;

Output:

ii) Display the structure of uscs_department table

Query:
DESCRIBE uscs_department;

Output:

iii) Insert at least three department details in the department table.

Query:
INSERT INTO uscs_department (department_no, department_name,
department_location)
VALUES (111, ‘USCS’, ‘BCA’);
 INSERT INTO uscs_department (department_no, department_name,
department_location)
VALUES (112, ‘USCS’, ‘MCA);
INSERT INTO uscs_department (department_no, department_name,
department_location)
VALUES (113, ‘USCS’, ‘BSc IT);

Output:

iv) Display the data of uscs_department table.

Query:
SELECT * FROM uscs_department;

Output:

v) Insert employee’s details in uscs_employee table who working the

corresponding departments as in the uscs_department table.
Query:
INSERT INTO uscs_employee (employee_id, employee_name,
employee_department_no)
VALUES (‘UU1001’, ‘KAMLESH PATHAK’, 111);
INSERT INTO uscs_employee (employee_id, employee_name,
employee_department_no)
VALUES (‘UU1002’, ‘MUNISH YADAV’, 112);
INSERT INTO uscs_employee (employee_id, employee_name,
employee_department_no)
VALUES (‘UU1003’, ‘KUSHI RAI’, 113);

Output:

vi) Display the data of uscs_employee table.

Query:
SELECT * FROM uscs_employee;

Output:
Practical 8: The data in the table can be grouped based on certain
attributes. Consider the book table having attributes as book_id,
book_name, book_theme and book_price. Consider the table below:
Table name: uscs_book
Attribute Datatype Size Constraint
book_no Number 10 PRIMARY KEY
book_name Varchar2 25 NOT NULL
book_theme Char 4 CHECK
Note: the book_theme can either be 'cs' or 'it' only.
CREATE TABLE uscs_book (book_no NUMBER (10)
PRIMARY KEY, book_name VARCHAR2 (25) NOT NULL,
book_theme CHAR (4), CHECK (book_theme IN (‘CS’, ‘IT’));
i) Add a new column named book_price having data type as Number and
size as 7,2 in the exisiting table named uscs_book.
Query:
ALTER TABLE uscs_book
ADD book_price NUMBER (7,2);

Output:

ii) Display the contents of uscs_book table.

Query:
SELECT * FROM uscs_book;

Output:

iii)Update the book prices of available books in the uscs_book table.

Query:
UPDATE uscs_book
SET book_price = '540' WHERE book_id = '111';

UPDATE uscs_book
SET book_price = '720' WHERE book_id = '112';

UPDATE uscs_book
SET book_price = '570' WHERE book_id = '113';

Output:

iv) Display the contents after updation in uscs_book table.

Query:
SELECT * FROM uscs_book;

Output:

v) Find the sum of all the book price based on the book theme.
 Query:
SELECT MAX (book_price) FROM uscs_book
GROUP BY book_theme;

Output:

vi) Display the maximum book_price in each group of book_theme.

Query:
SELECT book_theme, MAX(book_price) AS max_price FROM uscs_book
GROUP BY book_theme;

Output:
Practical 9: There are numerous aggregate functions that can be
performed on table(s). Consider the attributes in the employee table
as: employee’s id, employee’s name, employee’s department number,
employee’s Designation, employee’s date of joining, employee’s
salary, employee’s email id, employee’s contact no, employee’s
aadhaar number.
Table name: uu_employee
Attribute Datatyp Size Constraint
e
employee_id Varchar2 10 PRIMARY KEY
employee_name Char 25 NOT NULL
employee_department_no Number 03 NOT NULL
employee_designation Varchar2 15 NOT NULL
employee_date_of_joining Date - NOT NULL
employee_salary Number 8,2 NOT NULL
employee_email_id Varchar2 30 NOT NULL
employee_contact_no Number 12 NOT NULL
employee_aadhaar_no Number 12 UNIQUE

CREATE TABLE uu_employee (employee_id VARCHAR2 (10)

PRIMARY KEY, employee_name CHAR (25) NOT NULL,
employee_department_no NUMBER (03) NOT NULL,
employee_designation VARCHAR2 (15) NOT NULL,
employee_date_of_joining DATE () NOT NULL,
employee_salary NUMBER (8,2) NOT NULL,
employee_email_id VARCHAR2 (30) NOT NULL,
employee_contact_no NUMBER (12) NOT NULL,
employee_aadhaar_no NUMBER (12) UNIQUE);
i) Insert few tuples in the uu_employee table.
Query:
INSERT INTO uu_employee
VALUES (‘UU1001’, ‘Aman Kumar’, ‘111’, ‘Employee’, ‘12-Nov-2021’, ‘120000’,
‘amankumar12@gmail.com’, ‘9805635345’, ‘988893341234’);

INSERT INTO uu_employee

VALUES (‘UU1002’, ‘Mohan Verma’, ‘112’, ‘Employee’, ’10-Oct-2022’, ‘200000’,
‘Mohanverma23@gmail.com’, ‘9089889445’, ‘767578578589’);

INSERT INTO uu_employee

VALUES (‘UU1003’, ‘Shruti Srivastav’, ‘111’, ‘Employee’, ‘9-April-2020’,
‘56000’, ‘shrutisrivastav12@gmail.com’, ‘9089765467’, ‘1112353564’);

Output:

ii) Display the contents of uu_employee table

Query:
SELECT * FROM uu_employee;
 Output:

iii)Calculate the sum of salaries of all the employees.

Query:
SELECT SUM(employee_salary) AS total_salary FROM uu_employee;

Output:

iv) Calculate the sum of salaries of employees working in department

number 222.
Query:
SELECT SUM(employee_salary) AS department_total_salary FROM uu_employee
WHERE employee_department_no = 222;

Output:
Practical 10: Demonstrate the following based on the
uu_employee table.
i) Count the number of employees in the uu_employee table.
Query:
SELECT COUNT (*) AS employee_count FROM uu_employee;

Output:

ii) Display the maximum salary of employees having

employee_designation as “Assistant Professor”.
Query:
SELECT MAX(employee_salary) AS max_salary FROM uu_employee
WHERE Employee_Designation = 'Assistant Professor';

Output:

iii)Display the minimum salary of employees having employee_designation

as “Professor”.
Query:
SELECT MIN(employee_salary) AS min_salary FROM uu_employee
WHERE Employee_Designation = 'Professor';

Output:

iv) Calculate the average of salaries of all the employees.

Query:
SELECT AVG(employee_salary) AS average_salary FROM uu_employee;
Output:
Practical 11: Implement the single-row character function on the
customer table. The customer table as attributes related to customer’s
id, customer’s first name, customer’s last name, customer’s contact
number, customer’s house number, customer’s street of address,
customer’s city of address, customer’s state of address and
customer’s email id.
Table name: uscs_customer
Attribute Datatype Size Constraint
customer_id Varchar2 10 PRIMARY KEY
customer_first_name Char 25 NOT NULL
customer_last_name Char 25 -
customer_contact_no Number 03 NOT NULL
customer_house_no Number 03 -
customer_home_stree Varchar2 15 -
t
customer_home_city Varchar2 15 -
customer_home_state Varchar2 15 -
customer_email_id Varchar2 - -

CREATE TABLE uscs_customer (customer_id VARCHAR2(10)

PRIMARY KEY, customer_first_name CHAR (25) NOT NULL,
customer_last_name CHAR (25), customer_contact_no
NUMBER (03) NOT NULL, customer_house_no NUMBER
(03), customer_home_street VARCHAR2 (15),
customer_home_city VARCHAR2 (15), customer_home_state
VARCHAR2 (15), customer_email_id VARCHAR2(20));

i) Insert few tuples in the uscs_customer table

Query:
INSERT INTO uscs_customer (customer_id, customer_first_name,
customer_last_name, customer_contact_no, customer_house_no,
customer_home_street, customer_home_city, customer_home_state,
customer_email_id)
VALUES (‘UU1001’, ‘Ramesh’, ‘Sharma’, ‘9907907968’, ‘H308’, ‘Jhajra’,
‘Dehradun’, ‘Uttarakhand’, ‘rameshsharma22@gmail.com’);

INSERT INTO uscs_customer (customer_id, customer_first_name,

customer_last_name, customer_contact_no, customer_house_no,
customer_home_street, customer_home_city, customer_home_state,
customer_email_id)
VALUES (‘UU1002’, ‘Shakti’, ‘Rawat’, ‘9907905446’, ‘G408’, ‘Jhajra’,
‘Dehradun’, ‘Uttarakhand’, ‘shaktirawat66@gmail.com’);

INSERT INTO uscs_customer (customer_id, customer_first_name,

customer_last_name, customer_contact_no, customer_house_no,
customer_home_street, customer_home_city, customer_home_state,
customer_email_id)
VALUES (‘UU1003’, ‘Rakesh’, ‘Yadav’, ‘9907905487’, ‘D405’, ‘Jhajra’,
‘Dehradun’, ‘Uttarakhand’, ‘rakeshyadav99@gmail.com’);
 Output:

ii) Concatenate customer_first_name and customer_last_name in display.

Query:
SELECT customer_first_name, customer_last_name AS full_name FROM
uscs_customer;

Output:

iii)Display the customer_first_name in upper case.

Query:
SELECT UPPER(customer_first_name) AS uppercase_first_name FROM
uscs_customer;

Output:

iv) Display the customer_ home_city in with first letter as capital and
remaining in lower case (e.g. Dehradun).
Query:
SELECT INITCAP (customer_ home_city) AS formatted_home_city FROM
uscs_customer;

Output:
Practical 12: Implement the date functions on uu_employee table.
Consider the attributes in the employee table as: employee’s id,
employee’s name, employee’s department number, employee’s
designation, employee’s date of joining, employee’s salary,
employee’s email id, employee’s contact no, employee’s aadhaar
number and employee’s date of resigning.
Table name: employee_table
Attribute Datatype Size Constraint
employee_id Varchar2 10 PRIMARY KEY
employee_name Char 25 NOT NULL
employee_department_no Number 033 NOT NULL
employee_designation Varchar2 15 NOT NULL
employee_date_of_joining Date - NOT NULL
employee_salary Number 8,2 NOT NULL
employee_email_id Varchar2 30 NOT NULL
employee_contact_no Number 12 NOT NULL
employee_aadhaar_no Number 12 UNIQUE
employee_date_of_resigning Date - -

CREATE TABLE employee_table (employee_id

VARCHAR2(10) PRIMARY KEY, employee_name CHAR(25)
NOT NULL, employee_department_no NUMBER(033) NOT
NULL, employee_designation VARCHAR2(15) NOT NULL,
employee_date_of_joining DATE() NOT NULL,
employee_salary NUMBER(8,2) NOT NULL,
employee_email_id VARCHAR2(30) NOT NULL,
employee_contact_no NUMBER(12) NOT NULL,
employee_aadhaar_no NUMBER(1) NOT NULL,
employee_date_of_resigning DATE());
i) Describe the structure of uu_employee table.
Query:
DESCRIBE uu_employee;

Output:

ii) Add a new column named employee_date_of_resigning having date data

type in uscs_employee table
Query:
ALTER TABLE uu_employee
ADD COLUMN employee_date_of_resigning DATE;

Output:

iii)Update the data in uu_employee table those who have resigned

(wherever applicable)
Query:
 UPDATE uu_employee
SET employee_date_of_resigning = ‘2023-12-05’
WHERE employee_date_of_resigning IS NOT NULL;

Output:

iv) Display the number of months between employee’s joining and

resignation. (Note: There might be few employees who have resigned).
Query:
SELECT MONTH_BETWEEN (Employee_Date_Of_Resigning,
Employee_Date_Of_Joining) AS months_between FROM uu_employee WHERE
Employee_Date_Of_Resigning IS NOT NULL;

Output:

v) Display the last day of the month in which employees have resigned.
Query:
SELECT LAST_DAY(Employee_Date_Of_Joining) AS Last_Day_Of_Joining,
LAST_DAY(Employee_Date_Of_Resigning) AS Last_Day_Of_Resigning FROM
uu_employee
WHERE Employee_Date_Of_Resigning IS NOT NULL;

Output:

vi) Calculate the working employee’s experience in the current organization.

Query:
SELECT MONTHS_BETWEEN (SYSDATE, Employee_Date_Of_Joining) / 12 AS
Years_Experience FROM uu_employee
WHERE Employee_Date_Of_Resigning IS NULL;

Output: