DATABASE MANAGEMENT SYSTEM
LABS PRACTICALS RECORDS
INFORMATION TECHNOLOGY
Dr.B.R AMBEDKAR NATIONAL INSTITUTE OF
TECHNOLOGY
JALANDHAR-144011,PUNJAB(INDIA)
Submitted To:
Submitted by:
HARSH VERMA
Batta
14124012
Sahil
IT,4th
SEM
LAB WORK 1
Sql queries using
Query1:For creating a table
CREATE TABLE "EMPLOYEE"
(
"EMP_ID" NUMBER(4,0),
"EMP_NAME" VARCHAR2(20),
"SALARY" NUMBER(8,3),
"MANAGER_ID" NUMBER(4,0),
"D_NO" NUMBER(4,0),
PRIMARY KEY ("EMP_ID") ,
FOREIGN KEY ("MANAGER_ID")
REFERENCES "EMPLOYEE" ("EMP_ID") ,
FOREIGN KEY ("D_NO")
REFERENCES "DEPARTMENT" ("DEPT_NO")
)
/
Output table created sucessfully.
Query2:Describing the structure of table created
describe employee
output:
Query3:
Describe department//department table looks like this
Output:
Note we can insert in the tables created using insert command
One such illustration has been made for a tuple of the relation employee
Like this:
Insert into employee values(1,daljit singh,23098.65,NULL,2)
Select * from employee
This how we add data to our tables(relations);
In this case employee table looks like:
And the department table looks like:
Query 1:Select * from employee where emp_name like %ingh
Result:
Query 2:select * from employee where manager_id IS NULL
Query 3: select * from employee where salary between 12221 and 60000
Query :select salary from employee where d_no=(
Select d_no from employee where emp_id=5)
Problem: To display employee id,name and department name of all
employee
Query: select emp_id,emp_name,dept_no from employee cross join department where
employee.d_no=department.dept_no
Problem: To display details of the manager of the employee
with employee id=3.
Query: select * from employee where emp_id =(select manager_id from
where emp_id=3)
employee
Problem:to display the average salary of all employees
Query:select avg(salary) from employee
Print the details of the department whose employee has the
maximum salary
Query:select * from department where dept_no=(select d_no from employee where
employee.salary=(select max(salary) from employee))
LAB WORK 2
Alter statements(for a table stud)_-these statements are used to alter the
structure of table.
Query-alter table stud add marks number(4)
Query- alter table stud add primary key(rollno)
->For creating the creating a table department:
Query:Create table dept(dno number(5) primary key,dname varchar(20),location
varchar2(20));
->to Inserting the values in the table created:
Query:insert into dept(dno,dname,location)values(:a,:b,:c);
->For creating the table employee as a referencing relation to table
Department:
Query:create table employee(emp_id number(5) primary key,e_name
varchar(20),salary number(6),manager_id number(5) references
employee(emp_id),dno number(6) references dept(dno))
->Inserting the values in the employee table:
Query:insert into
employee(emp_id,e_name,salary,manager_id,dno)values(:a,:b,:c,:d,:e)
->For finding the length of each employee name:
Query:select length(e_name) from employee
->For concatenating two attributes in a single one:
Query: select dname||location from dept
->Using basic mathematical functions on the default table dual:
->Floor functions gives the lower bound of the decimal value:
Query: select floor(5.5) from dual
->mod function gives the remainder of 2 numbers:
Query: select mod(10,3) from dual
->power gives one number raised with power to another number:
Query: Select power(2,3) from dual
->ceil provides the upper bound of the given number:
Query: select ceil(2.5) from dual
->initcap capatalizes the initials of the given entity:
Query: select Initcap(e_name) from employee where e_name like '%ji'
Query: select log(10,10) from dual
->greatest gives the largest of the given list:
Query: select greatest(10.3,9.8,5.7) from dual
->least gives the smallest of the given list:
Query: select least(1.4,6.4,8.9,1) from dual
->Using Aggregate functions
Query: select max(salary) from employee
->count function provides the no. of entites but by use of distinct
eliminates the duplicate values:
Query: select count(distinct emp_id) from employee
Problem:Give the name of the employee whose salary is maximum:
Query: select e_name,salary from employee where salary=(select max(salary) from
employee)
Problem-to give details of all managers from employee table
Query-create or replace view A(e) as select distinct manager_id from employee;
Query- select * from employee ,A where employee.emp_id=A.e
Problem-select details of employees where salary is minimum
Query-Select * from employee where salary=(Select min(salary) from employee)
->group by keyword provides the ability to group the rows on some
conditions while applying aggregate functions:
Problem-Find out the employees in each department
Query-select dno,count(*) from employee group by dno
->having keyword is used for making a condition when group by keyword
is used or it is used for applying conditions on the result of the aggregate
functions:
Problem-Display the deptno having employees greater than 1.
Query-Select dno,count(*) from employee group by dno having count(*)>1
Problem-Display the Display the deptno having employees greater than 1
except dept 1
Query- select dno,count(*) from employee group by dno having count(*)>1 and dno
not in(1)
Problem-Display the details of the employee in order of count
Query-Select dno,count(*) from employee group by dno order by count(*)
Problem-Display the details of the employee in order of count
Query-Select dno,count(*) from employee group by dno order by count(*) desc
LAB WORK 3
->Using union ,intersection and minus keywords
Problem-Find out details of the employee who works for dept1 or having
salary more than 5000 using union
Query- select * from employee where dno in(1) union select * from employee where
salary>5000
Problem: Find out details of the employee who works for dept1 or having
salary more than 5000 using intersection
Query: select * from employee where dno=1 intersect select * from employee
where salary>5000;
Problem-Find out the details of the employees where deptnumber is 1 2 or
3 using union and minus
Query- select * from employee minus select * from employee where dno not in(1)
union select * from employee minus select * from employee where dno not in(2)
union select * from employee minus select * from employee where dno not in(3)
->Applying various joins in tables
Problem:Apply left outer join on employee to dept
Query:select * from employee left outer join dept on employee.dno=dept.dno
Problem:Apply right outer join on employee to dept
Query: select * from employee right outer join dept on employee.dno=dept.dno
Problem:Apply inner join on employee to dept
Query: select * from employee inner join dept on employee.dno=dept.dno
Problem: Apply natural join on employee to dept
Query:select * from emp natural join dept
LAB WORK 4
PROCEDURAL SQL CONCEPTS
->Procedural sql contains declaration block declaration block ,executable
block which contains the commands to be executed and a block for
exception handling.
->A simple example calculating the area of the circle
declare
p constant number(4,2):=3.14;
radius number(4,2);
ar number(10,2);
begin
radius:=:a;
ar:=p*power(radius,2);
insert into area values(radius,ar);
dbms_output.put_line('area is'||ar);
end;
output:For input as 3
->Here the table area is initially created that is:
Create table area(radius number(4,2),ar number(10,2))
After excecuting the above code the table data is:
Using if section
Format:
If
Then
Else if
Then
.
Endif;
An example: To find the greatest to three numbers
declare
a number(2);
b number(2);
c number(2);
begin
a:=:ad;
b:=:bd;
c:=:cd;
if a>b
then
if a>c
then
dbms_output.put_line('a is largest');
else
dbms_output.put_line('c is largest');
end if;
else
if c>b
then
dbms_output.put_line('c is largest');
else
dbms_output.put_line('b is largest');
end if;
end if;
end;
Output:
(Provided a being given largest value)
Using while loop
Problem: print the area of circle continuously with taking radius 1 to 10
declare
p constant number(4,2):=3.14;
radius number(4,2);
ar number(10,2);
begin
radius:=1;
while radius<=10 loop
ar:=p*power(radius,2);
insert into area values(radius,ar);
dbms_output.put_line('area is'||ar);
radius:=radius+1;
end loop;end;
Output:
Contents of area table becomes:
Using for loop
Problem: print the area of circle continuously with taking radius 1 to 10
declare
p constant number(4,2):=3.14;
radius number(4,2);
ar number(10,2);
begin
radius:=1;
for radius in 1..10 loop
ar:=p*power(radius,2);
dbms_output.put_line('area is'||ar);
end loop;
end;
Output:
