I MCA B Database System Lab 245213234
EX NO 1
DDL COMMANDS
1. CREATE :
SQL> create table students ( student_id number(10) primary key,name varchar2(15),age
number(3),department varchar2(15),join_date date);
2. ALTER :
SQL> Alter table students add email varchar2(30);
SQL> Alter table students modify name varchar(20);
SQL> Alter table students rename column Department to Branch;
SQL> Alter table students drop column Age;
SQL> Alter table students rename to student_info;
3. RENAME TABLE:
SQL> rename student_info to students;
4. DROP TABLE:
SQL> Drop table students;
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
OUTPUT:
a)
SQL> desc students;
Name Null? Type
STUDENT_ID NOT NULL NUMBER(10)
NAME VARCHAR2(15)
AGE NUMBER(3)
DEPARTMENT VARCHAR2(15)
JOIN_DATE DATE
b)
SQL> Desc student_info;
Column Name Null Data Type
STUDENT_ID NOT NULL NUMBER(10)
NAME VARCHAR2(15)
AGE NUMBER(3)
DEPARTMENT VARCHAR2(15)
JOIN_DATE DATE
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
EX NO 2
DML COMMANDS
SQL> create table emp( emp_id number(4) primary key,emp_name varchar2(15),salary
number(10,2),department varchar2(15),job_title varchar2(25));
a) INSERT:
SQL> insert into emp values(2,'Jane Smith',60000.00,'HR','HR Manager');
SQL> insert into emp values(3,'Sam Brown',45000.00,'Finance','Accountant');
SQL> insert into emp values(4,'Lisa White',55000.00,'Marketing','Marketing Specialist');
SQL> insert into emp values(5,'Tom Harris',48000.00,'Sales','Sales Executive');
b) UPDATE:
SQL> update emp set salary = 55000.00 where emp_id = 5;
SQL> update emp set department = 'Operations' where department='Sales';
SQL> update emp set job_title='Senior Analyst' where emp_id = 1;
SQL> update emp set salary=salary * 1.10 where department='HR';
SQL> update emp set emp_name='Samuel Brown' where emp_id=3;
c) DELETE:
SQL> delete from emp where emp_id=5;
SQL> delete from emp where salary < 50000;
SQL> delete from emp where emp_name like 'A%';
SQL> delete from emp where emp_id between 10 and 15;
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
d) SELECT:
SQL> select * from emp;
i) SUM:
SQL> select sum(salary) as total_salary from emp;
SQL> select sum(salary) from emp where job_title = 'HR Manager';
SQL> select sum(salary) from emp where salary > 50000;
SQL> select sum(salary) from emp where department = 'IT';
ii) ALL:
SQL> select emp_name from emp where salary > all (select salary from emp where
department = 'Marketing');
SQL> select emp_name from emp where salary < all (select salary from emp where
department = 'IT');
SQL> select emp_name from emp where salary >= all (select salary from emp where
department = 'Finance');
SQL> select emp_name from emp where salary > all (select salary from emp where salary <
40000);
SQL> select emp_name from emp where salary > all (select avg(salary) from emp where
department = 'Marketing');
iii) DISTINCT:
SQL> select distinct emp_name from employee where emp_name like '%John%';
SQL> select distinct department from employee;
SQL> select distinct department from employee;
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
iv) BETWEEN :
SQL> select emp_name from employee where salary between 50000 and 70000;
v) LIKE:
SQL> select emp_name from employee where job_title like '_________';
SQL> select emp_name from employee where job_title like '__________';
SQL> select emp_name from employee where job_title like '%Specialist%';
SQL> select emp_name from employee where department between 'C' and 'M';
vi) IN:
SQL> select emp_name from employee where department in ('Finance','IT','operations');
SQL> select emp_name from employee where salary in (50000,60000,70000,80000,90000);
SQL> select emp_name from employee where salary in (50000,60000,70000,80000,90000);
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
OUTPUT:
SQL> desc emp;
Name Null? Type
EMP_ID NOT NULL NUMBER(4)
EMP_NAME VARCHAR2(15)
SALARY NUMBER(10,2)
DEPARTMENT VARCHAR2(15)
JOB_TITLE VARCHAR2(25)
A)INSERT:
SQL> select * from emp;
EMP_ID EMP_NAME SALARY DEPARTMENT JOB_TITLE
1 John Doe 50000 IT Software Engineer
2 Jane Smith 60000 HR HR Manager
3 Sam Brown 45000 Finance Accountant
4 Lisa White 55000 Marketing Marketing Specialist
5 Tom Harris 48000 Sales Sales Executive
6 Anna Lee 53000 IT Data Analyst
7 Robert King 62000 HR Recruiter
8 Emily Davis 47000 Finance Auditor
9 Daniel Scott 52000 IT Network Engineer
10 Grace Hall 51000 Marketing SEO Specialist
11 Raja 54000 Sales Sales Manager
12 Priya 58000 Finance Financial Manager
13 Preman 62000 IT DevOps Engineer
14 Sree Devi 57000 Marketing Content Writer
15 Lalitha 49000 Sales Business Analyst
C) DELETE:
EMP_ID EMP_NAME SALARY DEPARTMENT JOB_TITLE
1 John Doe 50000 IT Senior Analyst
2 Jane Smith 66000 HR HR Manager
4 Lisa White 55000 Marketing Marketing Specialist
7 Robert King 68200 HR Recruiter
9 Daniel Scott 52000 IT Network Engineer
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
D) SELECT:
I)SUM:
>
TOTAL_SALARY
815200
>
SUM(SALARY)
66000
>
SUM(SALARY)
576200
>
SUM(SALARY)
217000
II)ALL:
EMP_NAME
Priya
Preman
Jane Smith
Robert King
EMP_NAME
Lalitha
Tom Harris
Emily Davis
Sam Brown
EMP_NAME
Priya
Preman
Jane Smith
Robert King
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
EMP_NAME
Sam Brown
Emily Davis
Tom Harris
Lalitha
John Doe
Grace Hall
Daniel Scott
Anna Lee
Raja
Lisa White
Sree Devi
Priya
Preman
Jane Smith
Robert King
EMP_NAME
Jane Smith
Lisa White
Robert King
Priya
Preman
Sree Devi
III)DISTINCT
EMP_NAME
Alice Johnson
John Doe
DEPARTMENT
IT
HR
Finance
Sales
Marketing
DEPARTMENT
IT
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
HR
Finance
Sales
Marketing
IV) BETWEEN:
EMP_NAME
John Doe
Jane Smith
Eve Green
Grace Gray
Ivy Purple
Jack Silver
Mona White
Oscar Green
Quincy Red
Rachel Pink
V) LIKE:
EMP_NAME
Eve Green
EMP_NAME
Jane Smith
Alice Johnson
EMP_NAME
Jane Smith
Alice Johnson
EMP_NAME
Mona White
Quincy Red
Rachel Pink
EMP_NAME
John Doe
Jane Smith
Alice Johnson
Diana Black
Eve Green
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
Frank Blue
Ivy Purple
Jack Silver
Mona White
Nina Black
Oscar Green
Quincy Red
VI) IN:
EMP_NAME
John Doe
Alice Johnson
Diana Black
Frank Blue
Ivy Purple
Jack Silver
Nina Black
Oscar Green
Quincy Red
EMP_NAME
Jane Smith
Bob Brown
Quincy Red
EMP_NAME
Jane Smith
Bob Brown
Quincy Red
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
EX NO 3
OPERATORS
SQL> SELECT * FROM STUDENT ;
a) LOGICAL OPERATORS (AND, OR, NOT):
SQL> select * from student where age>20 and course='MCA';
SQL> SELECT * FROM STUDENT WHERE AGE < 18 OR COURSE = 'BBA';
SQL> SELECT * FROM STUDENT WHERE COURSE = 'B.Sc' OR COURSE = 'B.com';
SQL> select * from student where gender = 'F' and course='MBA';
SQL> select * from student where not gender = 'M';
B) SET OPERATORS (UNION, INTERSET, MINUS):
SQL> select * from archived_student;
SQL> select name from student union select name from archived_student;
SQL> select name from student intersect select name from archived_student;
SQL> select name from student minus select name from archived_student;
SQL> select course from student union select course from archived_student;
SQL> select name from archived_student minus select name from student;
SQL> select * from student order by name;
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
C)
I) ORDER BY:
SQL> select * from student order by age desc;
11 rows selected.
SQL> select * from student order by gender,name;
11 rows selected.
II) GROUP BY:
SQL> select course,count(*) as total_students from student group by course;
SQL> select age,course,count(*) from student group by age,course;
SQL> select gender,avg(age) as avg_age from student group by gender;
SQL> select course,count(student_id) from student group by course;
III) HAVING:
SQL> select course,avg(age) from student group by course having avg(age) < 25;
SQL> select gender, avg(age) from student group by gender having avg(age) > 20;
SQL> select course,avg(age) from student group by course having avg(age) < 25;
IV) EXISTS:
SQL> select * from student where exists (select 1 from student where gender = 'F');
SQL> select name from student where exists ( select 1 from archived_student);
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
OUTPUT:
STUDENT_ID NAME AGE G COURSE
101 Aditi Rao 20 F MCA
102 Ravi Kumar 22 M MBA
103 Suresh Iyer 19 M B.Sc
104 Anjali Sharma 21 F M.Sc
105 Priya Menon 18 F B.com
106 Rahul Singh 24 M MCA
107 Ramesh Karnan 20 M B.sc
108 Aditi Singh 23 F BBA
109 Ramesh Babu 22 M B.com
110 Priyanka Mohan 24 F BBA
111 Shalini 19 F MBA
A)LOGICAL OPERATORS:
STUDENT_ID NAME AGE G COURSE
106 Rahul Singh 24 M MCA
STUDENT_ID NAME AGE G COURSE
108 Aditi Singh 23 F BBA
110 Priyanka Mohan 24 F BBA
STUDENT_ID NAME AGE G COURSE
103 Suresh Iyer 19 M B.Sc
105 Priya Menon 18 F B.com
109 Ramesh Babu 22 M B.com
STUDENT_ID NAME AGE G COURSE
111 Shalini 19 F MBA
STUDENT_ID NAME AGE G COURSE
101 Aditi Rao 20 F MCA
104 Anjali Sharma 21 F M.Sc
105 Priya Menon 18 F B.com
108 Aditi Singh 23 F BBA
110 Priyanka Mohan 24 F BBA
111 Shalini 19 F MBA
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
B)SET OPERATORS:
SQL> select * from archived_student;
Student Details
STUDENT_ID NAME AGE G COURSE
101 Aditi Rao 20 F MCA
102 Ravi Kumar 22 M MBA
103 Suresh Iyer 19 M B.sc
114 Atchaya 20 F MCA
115 Gayathri 21 F MCA
116 Divya Sri 20 F BBA
106 Rahul Singh 24 M MCA
105 Priya Menon 18 F B.com
Names (14 Rows)
NAME
Aditi Rao
Aditi Singh
Anjali Sharma
Atchaya
Divya Sri
Gayathri
Priya Menon
Priyanka Mohan
Rahul Singh
Ramesh Babu
Ramesh Karnan
Ravi Kumar
Shalini
Suresh Iyer
Names (5 Rows)
NAME
Aditi Rao
Priya Menon
Rahul Singh
Ravi Kumar
Suresh Iyer
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
Names (6 Rows)
NAME
Aditi Singh
Anjali Sharma
Priyanka Mohan
Ramesh Babu
Ramesh Karnan
Shalini
COURSE
B.Sc
B.com
B.sc
BBA
M.Sc
MBA
MCA
Names (3 Rows)
NAME
Atchaya
Divya Sri
Gayathri
c)
I)ORDERBY
First Group of Students
STUDENT_ID NAME AGE G COURSE
101 Aditi Rao 20 F MCA
108 Aditi Singh 23 F BBA
104 Anjali Sharma 21 F M.Sc
105 Priya Menon 18 F B.com
110 Priyanka Mohan 24 F BBA
106 Rahul Singh 24 M MCA
109 Ramesh Babu 22 M B.com
107 Ramesh Karnan 20 M B.sc
102 Ravi Kumar 22 M MBA
111 Shalini 19 F MBA
103 Suresh Iyer 19 M B.Sc
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
Second Group of Students
STUDENT_ID NAME AGE G COURSE
106 Rahul Singh 24 M MCA
110 Priyanka Mohan 24 F BBA
108 Aditi Singh 23 F BBA
102 Ravi Kumar 22 M MBA
109 Ramesh Babu 22 M B.com
104 Anjali Sharma 21 F M.Sc
101 Aditi Rao 20 F MCA
107 Ramesh Karnan 20 M B.sc
103 Suresh Iyer 19 M B.Sc
111 Shalini 19 F MBA
105 Priya Menon 18 F B.com
Third Group of Students
STUDENT_ID NAME AGE G COURSE
101 Aditi Rao 20 F MCA
108 Aditi Singh 23 F BBA
104 Anjali Sharma 21 F M.Sc
105 Priya Menon 18 F B.com
110 Priyanka Mohan 24 F BBA
111 Shalini 19 F MBA
106 Rahul Singh 24 M MCA
109 Ramesh Babu 22 M B.com
107 Ramesh Karnan 20 M B.sc
102 Ravi Kumar 22 M MBA
103 Suresh Iyer 19 M B.Sc
II) GROUPBY:
Total Students by Course
COURSE TOTAL_STUDENTS
MBA 2
B.Sc 1
B.sc 1
B.com 2
MCA 2
M.Sc 1
BBA 2
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
Age and Course Distribution
AGE COURSE COUNT(*)
24 MCA 1
24 BBA 1
20 MCA 1
21 M.Sc 1
23 BBA 1
19 B.Sc 1
20 B.sc 1
22 B.com 1
19 MBA 1
18 B.com 1
22 MBA 1
Average Age by Gender
G AVG_AGE
M 21.4
F 20.8333333
Student Count by Course
COURSE COUNT(STUDENT_ID)
MBA 2
B.Sc 1
B.sc 1
B.com 2
MCA 2
M.Sc 1
BBA 2
III) HAVING
Average Age by Course
COURSE AVG(AGE)
MBA 20.5
B.Sc 19
B.sc 20
B.com 20
MCA 22
M.Sc 21
BBA 23.5
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
EX NO 4
JOIN OPERATIONS
Table1:
Creation:
SQL> create table emp(emp_id int primary key,emp_name varchar(100),department_id int);
Insertion:
SQL> insert into emp values(1,'john',10);
SQL> insert into emp values(2,'stefan',20);
SQL> insert into emp values(3,'kai',40);
SQL> insert into emp values(4,'damon',30);
SQL> insert into emp values(5,'grace',50);
SQL> insert into emp values(6,'joe',70);
SQL> insert into emp values(7,'ram',60);
Table2:
Creation:
SQL> create table department(dept_id int primary key,dept_name varchar(100));
Insertion:
SQL> insert into department values(10,'HR');
SQL> insert into department values(20,'IT');
SQL> insert into department values(30,'Finance');
SQL> insert into department values(40,'Marketing');
SQL> insert into department values(50,'Design');
SQL> insert into department values(60,'Quality Assurance');
SQL> insert into department values (70,'Research');
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
INNER JOIN:
SQL> select e.emp_name,d.dept_name from emp e inner join department d on
e.department_id = d.dept_id;
LEFT JOIN:
SQL> select e.emp_name,d.dept_name from emp e left join department d on
e.department_id=d.dept_id;
RIGHT JOIN:
SQL> select e.emp_name,d.dept_name from emp e right join department d on
e.department_id=d.dept_id;
FULL OUTER JOIN:
SQL> select e.emp_name,d.dept_name from emp e full outer join department d on
e.department_id=d.dept_id;
OUTPUT:
SQL> select * from emp;
EMP_ID EMP_NAME DEPARTMENT_ID
1 John 10
2 Stefan 20
3 Kai 40
4 Damon 30
5 Grace 50
6 Joe 70
7 Ram 60
SQL> select * from department;
DEPT_ID DEPT_NAME
10 HR
20 IT
30 Finance
40 Marketing
50 Design
60 Quality Assurance
70 Research
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
INNER JOIN:
EMP_NAME DEPT_NAME
John HR
Stefan IT
Kai Marketing
Damon Finance
Grace Design
Ram Quality Assurance
LEFT JOIN:
EMP_NAME DEPT_NAME
John HR
Stefan IT
Kai Marketing
Damon Finance
Grace Design
Joe Research
Ram Quality Assurance
RIGHT JOIN:
EMP_NAME DEPT_NAME
John HR
Stefan IT
Damon Finance
Kai Marketing
Grace Design
Ram Quality Assurance
Joe Research
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
FULL OUTER JOIN:
EMP_NAME DEPT_NAME
John HR
Stefan IT
Kai Marketing
Damon Finance
Grace Design
Ram Quality Assurance
Joe Research
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
EX NO 5
Functions
Table Creation:
CREATE TABLE employees (
emp_id NUMBER PRIMARY KEY,
emp_name VARCHAR2(50),
department VARCHAR2(50),
salary NUMBER(10, 2),
hire_date DATE
);
Insert :
SQL>INSERT INTO employees (emp_id, emp_name, department, salary, hire_date)
VALUES (1, 'Alice', 'HR', 50000, TO_DATE('2021-06-15', 'YYYY-MM-DD'));
SQL>INSERT INTO employees (emp_id, emp_name, department, salary, hire_date)
VALUES (2, 'Bob', 'Finance', 60000, TO_DATE('2020-04-20', 'YYYY-MM-DD'));
SQL>INSERT INTO employees (emp_id, emp_name, department, salary, hire_date)
VALUES (3, 'Charlie', 'IT', 70000, TO_DATE('2022-11-01', 'YYYY-MM-DD'));
SQL>INSERT INTO employees (emp_id, emp_name, department, salary, hire_date)
VALUES (4, 'Diana', 'IT', 65000, TO_DATE('2023-03-10', 'YYYY-MM-DD'));
SQL>INSERT INTO employees (emp_id, emp_name, department, salary, hire_date)
VALUES (5, 'Eve', 'HR', 55000, TO_DATE('2019-12-25', 'YYYY-MM-DD'));
COMMIT;
EMP_ID EMP_NAME DEPARTMENT SALARY HIRE_DATE
1 Alice HR 50000.00 2021-06-15
2 Bob Finance 60000.00 2020-04-20
3 Charlie IT 70000.00 2022-11-01
4 Diana IT 65000.00 2023-03-10
5 Eve HR 55000.00 2019-12-25
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
STRING FUNCTIONS
Uppercase Employee Names
SQL>SELECT UPPER(emp_name) AS Uppercase_Name FROM employees;
Concatenate Employee Name and Salary
SQL>SELECT emp_name || ' earns ' || salary AS Employee_Salary_Info FROM employees;
Extract First 3 Letters of Employee Names
SQL>SELECT SUBSTR(emp_name, 1, 3) AS Short_Name FROM employees;
AGGREGATE FUNCTIONS
Find Total Salary of All Employees
SQL>SELECT SUM(salary) AS Total_Salary FROM employees;
Calculate Average Salary by Department
SQL>SELECT department, AVG(salary) AS Average_Salary FROM employees GROUP
BY department;
Find the Maximum Salary in the Company
SQL>SELECT MAX(salary) AS Maximum_Salary FROM employees;
DATE FUNCTIONS
Find Employees Hired After 2020
SQL>SELECT emp_name, hire_date FROM employees WHERE hire_date >
TO_DATE('2020-01-01', 'YYYY-MM-DD');
Calculate Years of Service for Each Employee
SQL>SELECT emp_name, ROUND(MONTHS_BETWEEN(SYSDATE, hire_date) / 12, 2)
AS Years_of_Service FROM employees;
Find the Day of the Week Each Employee Was Hired
SQL>SELECT emp_name, TO_CHAR(hire_date, ‘DAY’) AS Hire_Day FROM employees;
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
MATHEMATICAL FUNCTIONS
Find 15% Bonus of Each Employee's Salary
SQL>SELECT emp_name, salary, salary * 0.15 AS Bonus FROM employees;
Round Off Salaries to the Nearest Thousand
SQL>SELECT emp_name, ROUND(salary, -3) AS Rounded_Salary FROM employees;
Calculate the Square Root of Each Salary
SQL>SELECT emp_name, SQRT(salary) AS Salary_Square_Root FROM employees;
OUTPUT:
STRING FUNCTIONS:
i)
UPPERCASE_NAME
ALICE
BOB
CHARLIE
DIANA
EVE
ii)
EMPLOYEE_SALARY_INFO
Alice earns 50000
Bob earns 60000
Charlie earns 70000
Diana earns 65000
Eve earns 55000
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
iii)
SHORT_NAME
Ali
Bob
Cha
Dia
Eve
AGGREGATE FUNCTION:
i)
TOTAL_SALARY
300000
ii)
DEPARTMENT AVERAGE_SALARY
HR 52500
Finance 60000
IT 67500
iii)
MAXIMUM_SALARY
70000
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
DATE FUNCTION:
i)
EMP_NAME HIRE_DATE
Alice 15-JUN-21
Bob 20-APR-20
Charlie 01-NOV-22
Diana 10-MAR-23
ii)
EMP_NAME YEARS_OF_SERVICE
Alice 3.61
Bob 4.76
Charlie 2.23
Diana 1.87
Eve 5.08
iii)
EMP_NAME HIRE_DAY
Alice TUESDAY
Bob MONDAY
Charlie TUESDAY
Diana FRIDAY
Eve WEDNESDAY
PG DEPARTMENT OF COMPUTER APPLICATION
I MCA B Database System Lab 245213234
MATHEMATICAL FUNCTION:
i)
EMP_NAME SALARY BONUS
Alice 50000 7500
Bob 60000 9000
Charlie 70000 10500
Diana 65000 9750
Eve 55000 8250
ii)
EMP_NAME ROUNDED_SALARY
Alice 50000
Bob 60000
Charlie 70000
Diana 65000
Eve 55000
iii)
EMP_NAME SALARY_SQUARE_ROOT
Alice 223.606798
Bob 244.948974
Charlie 264.575131
Diana 254.950976
Eve 234.520788
PG DEPARTMENT OF COMPUTER APPLICATION
