DBMS LAB SYNTAX
Table creation:
create table tablename
(
Field1 datatype1(size1);
……..
Fieldn datatypen(sizen);
)
For characters: varchar2(size)
For numbers: number( numberOfDigits)
OR
number(numberOfDigits, numberOfdecimals);
for date: field1 date; (DD-MMM-YY format).
Command to get structure:
Sql> Desc tablename
Inserting records into table:
Inserting multiple attribute values:
insert into tablename
values(val1,val2,val3,……);
// it must match number of fields and datatype of field
Inserting some attributes value:
Insert into tablename(Attributename1,attributename2…)
Values(attributeval1,attributeval2….);
ALTER COMMAND:
1.add extra columns:
Alter table tablename
Add(field1 datatype1(size), ………);
2. modify:
Alter table tablename
Modify fieldname datatype(size);
3. to remove column:
Alter table tablename
Drop column columnname;
4. to rename column:
Alter table tablename
Rename column oldname to newname;
TRUNCATE:
// deletes all the content in the table
Truncate table tablename;
DROP:
// deletes table from the database
Drop table tablename;
RENAME:
// to rename table.
Rename oldtablename to newtablename;
DATA MANIPULATION LANGUAGE(DML):
DELETE:
//to delete one or more records from the table
Delete from tablename
[where condition] ;
// delete from tablename; is similar to TRUNCATE
UPDATE:
// to modify contents in the table
Update tablename
Set field1 = val1, field2 = val2 ……………….
[where condition];
SELECT:
// to display records based on given conditions.
Sql> Select clause
From clause
[where clause]
[group by clause]
[having clause]
[order by clause];
DISTINCT/UNIQUE:
// to display unique values.
Select distinct fieldname from tablename;
CREATING ALIAS:
// to temporarily change fieldname while displaying
Select fieldname as aliasname from tablename;
CONSTRAINTS:
// restricts data put into table.
1. NOT NULL:
// does not allow null values
Using create:
Create table tablename
(
Field1 datetype not null;
……………
)
Using alter:
Alter table tablename
Modify fieldname datatype not null;
2. UNIQUE:
// does not allow duplicate values
Using create:
Create table tablename
(
Field1 datetype not null;
……………
Constraint constraintname unique(fieldname)
);
Using alter:
Alter table tablename
Add constraint constraintname unique(field1,field2,….);
3. PRIMARY KEY:
// unique + not null
// one table can have only one primary key
Using create:
Create table tablename
(
Field1 datetype not null;
……………
Constraint constraintname primary key(fieldname)
);
Using alter:
Alter table tablename
Add constraint constraintname primary key(field1);
4. CHECK:
// allows the values which lie between given boundaries only
Using create:
Create table tablename
(
Field1 datetype not null;
……………
Constraint constraintname check(fieldname between x and y)
);
Using alter:
Alter table tablename
Add constraint constraintname check(field1 between x and y);
5. FOREIGN KEY:
// establishes relation between two tables
// values of foreign key are subset of primary key
Using create:
Create table tablename
(
Field1 datetype not null;
……………
Constraint constraintname foreign key(fieldname1) references
tablename2(fieldname2)
);
Using alter:
Alter table tablename
Add constraint constraintname foreign key(field1) references
tablename2(field2);
Note:
// can be used to modify the foreign key when primary key gets
updated or deleted
constraint constraintname foreign key(field1) references
tablename2(field2) on delete cascade;
or
on update set null;
DROPPING A CONSTRAINT:
Alter table tablename
Drop constraint constraintname;
MORE ON SELECT STATEMENT:
1. ORDER BY:
// used to sort the records in either ascending or descending order
Order by field1 [asc/desc] [field2[asc/desc]……………..];
// one field mandatory
// default is ascending order
2. WHERE CLAUSE:
// used to filter the records based on given condition
1.relational and logical operators: > or < or >= or <= or != or AND
or OR
Select * from tablename
Where field1 > x and field2!=y ……..;
2. membership operators: IN , NOT IN
Select * from tablename
Where field1 in (x,y,z,……..) or field2 not in (m,k,l….);
3. range operators: BETWEEN , NOT BETWEEN
Select * from tablename
Where field1 between x and y; (both inclusive)
4. pattern matching: LIKE, NOT LIKE
// underscore (_) : single character
// percentage (%) : 0 or more characters
Select * from tablename
Where field1 LIKE ‘_ _ _ _’ and field2 NOT LIKE ‘%s’; (both inclusive)
5. null operators : IS NULL, IS NOT NULL
Select * from tablename
Where field1 is null and field2 is not null;
SQL AGGREGATE FUNCTIONS:
1. SUM() :
// used to calculate sum of all the attribute values
Select sum(field1) from tablename;
2. AVG() :
// used to calculate average of all the attribute values
Select avg(field1) from tablename;
3. COUNT():
// used to find number of records
Select count(field1) from tablename;
4. MAX():
// gives maximum value from given attribute values
Select max(field1) from tablename;
5. MIN():
// gives minimum value from given attribute values
Select min(field1) from tablename;
NESTED QUERIES:
// a query inside another query is called as nested query.
EXAMPLE:
Select * from tablename
Where field1 in (select field2 from tablename where field2 > x);
GROUP BY CLAUSE:
// used to group records based on duplicate values
Example:
Select count(emp),deptno from emp group by deptno;
SET OPERATORS:
1. UNION:
Select field1 from tablename
Where field1 = x
UNION
Select field1 from tablename
Where field1 = y;
2. INTERSECT:
Select field1 from tablename
Where field1 = x
INTERSECT
Select field1 from tablename
Where field1 < y;
3. MINUS:
Select field1 from tablename
Where field2 = x
MINUS
Select field1 from tablename
Where field2 = y;
JOINS:
1.CROSS JOIN:
//every record of one relation is mapped with every record of another
relation.
Select * from tablename1,tablename2;
2.NATURAL JOIN:
// there must be atleast one common field.
// common fields are displayed only once.
Select * from tablename1 natural join tablename2;
3.OUTER JOIN: (LEFT,RIGHT,FULL)
// returns all matched records and all unmatched records of any one or
both tables based on left, right or full outer join.
Select * from tablename1 left join tablename2 on [condition];
CORRELATED NESTED QUERIES:
// every outer record executes inner record once.
// outer record variables are used in inner record.
EXAMPLE:
Select * from emp e
Where sal > (select avg(sal) from emp f where f.deptno = e.deptno);
PL SQL:
[ DECLARE
// required variables are declared here
BEGIN
………
END;
/
DATATYPES:
Number(size)
Number(size,decimals)
Char(size)
Varchar2(size)
Boolean
%type
%rowtype
To print output:
// in sql > set serveroutput on;
//in program
Dbms_output.put_line();
IMPORTANT: to assign values use ‘:=’ and not ‘=’.
To open editor:
Sql> ed d:/foldername/filename.sql
To execute:
Sql> @ d:/foldername/filename.sql
IF STATEMENT:
IF condition then
………..
ELSIF condition2 then
…………..
[
ELSE
………
]
END IF;
LOOPS:
1.simple loop:
LOOP
……..
Exit when condition;
END LOOP;
2. while loop:
While condition
LOOP
……..
End loop;
3.for loop:
for var in [reverse] lowerbound..upperbound //(inclusive)
LOOP
……
END LOOP;
SELECT STATEMENT:
Select field1,field2.. into var1,var2…. From tablename
Where condition;
Note: if data type is unknown, %type is used.
Example:
Num student.sno%type;
%ROWTYPE:
// used to create a table variable which can store all the attribute
values similar to a structure variable
Example:
Recvar student%rowtype;
Select * into recvar from student where sno = 501;
CURSORS:
//used to iterate through the records of a table.
SYNTAX:
1. declare:
Cursor cursorname IS select statement;
2. open the cursor:
Open cursorname;
3. fetch the records:
Fetch cursorname into <variables>;
4. close the cursor:
Close cursorname;
NOTE:
// while using loop
LOOP
Fetch cursorname into <variables>;
EXIT WHEN cursorname%NOTFOUND; //(to exit when reached end)
End loop;
PROCEDURES:
SYNTAX:
Create or replace procedure procedurename[(var1 datatype1,……)]
IS
………….. // this is the declaration section
BEGIN
……….
END;
/
// variables can be IN or OUT or IN OUT
// IN – constant
// OUT – change in value affects actual parameter
// IN OUT – can modify and return
NOTE: sql> show errors; // shows errors
FUNCTIONS:
SYNTAX:
Create or replace function functionname[(var1 datatype1,……)]
Return datatype
IS
………….. // this is the declaration section
BEGIN
……….
Return x;
END;
/
EXAMPLE PROBLEMS ON PROCEDURES AND FUNCTIONS:
1. Write a PL/SQL procedure to display output as follows:
EMPLOYEE SUMMARY DETAILS
------------------------------------------------------------------------------------------
Number of Departments: 4
Number of Employees: 14
Name of the President: KING
Department Number (Max. no.of Employees): 30
Department Number (Min. no. of Employees): 40
____________________________________________________________
END SUMMARY
SOLUTION:
create or replace procedure empdisplay
is
dcount number(3);
ecount number(3);
pname emp.ename%type;
dmax number(3);
dmin number(3);
begin
select count(deptno) into dcount from dept;
select count(empno) into ecount from emp;
select ename into pname from emp where job = 'PRESIDENT';
select deptno into dmax from emp having count(empno) = (select
max(count(empno)) from emp group by deptno) group by deptno;
select dept.deptno into dmin from dept left join emp on dept.deptno
= emp.deptno having count(empno) = (select min(count(empno)) from
dept left join emp on dept.deptno = emp.deptno group by dept.deptno)
group by dept.deptno;
dbms_output.put_line(''||' '||'EMPLOYEE SUMMARY DETAILS'||'
');
dbms_output.put_line('----------------------------------------------
-------');
dbms_output.put_line('Number of Departments: '||dcount);
dbms_output.put_line('Number of Employees: '||ecount);
dbms_output.put_line('Name of the President: '||pname);
dbms_output.put_line('Department Number(Max. no. of Employees):
'||dmax);
dbms_output.put_line('Department Number(Min. no. of Employees):
'||dmin);
dbms_output.put_line('----------------------------------------------
-------');
dbms_output.put_line(''||' '||'END SUMMARY'||'
');
end;
/
2. Write a PL/SQL procedure or function to display output as follows:
EMPLOYEE DETAILS
------------------------------------------------------------------------------------------
Number of Employees: 14
Name of the President: KING
Name of the Employee (Highest Salary):
Name of the Employee (Lowest Salary):
Name of the Employee (Most Senior):
Name of the Employee (Most Junior):
____________________________________________________________
END
SOLUTION:
create or replace procedure empshow3
is
ecount number(3);
pname emp.ename%type;
maxsalname emp.ename%type;
minsalname emp.ename%type;
seniorname emp.ename%type;
juniorname emp.ename%type;
begin
select count(empno) into ecount from emp;
select ename into pname from emp where job = 'PRESIDENT';
select ename into maxsalname from emp where sal = (select max(sal)
from emp);
select ename into minsalname from emp where sal = (select min(sal)
from emp);
select ename into seniorname from emp where hiredate = (select
min(hiredate) from emp);
select ename into juniorname from emp where hiredate = (select
max(hiredate) from emp);
dbms_output.put_line('| '||'EMPLOYEE DETAILS'||'
|');
dbms_output.put_line('----------------------------------------------
-------');
dbms_output.put_line('Number of Employees: '||ecount);
dbms_output.put_line('Name of the President: '||pname);
dbms_output.put_line('Name of the Employee(highest salary):
'||maxsalname);
dbms_output.put_line('Name of the Employee(lowest salary):
'||minsalname);
dbms_output.put_line('Name of the Employee(most senior):
'||seniorname);
dbms_output.put_line('Name of the Employee(most junior):
'||juniorname);
dbms_output.put_line('----------------------------------------------
-------');
dbms_output.put_line('| '||'END'||'
|');
end;
/
1. 3. Write a PL/SQL procedure or function to display output as follows:
EMPLOYEE and DEPARTMENT DETAILS
------------------------------------------------------------------------------------------
Number of Departments: 4
Deptno Deptname
10 ----
20 -----
30 ----
40 ------
Number of Employees in Each Department:
Deptno No.Of Emplotees
10 ----
20 -----
30 ----
40 ------
____________________________________________________________
END
SOLUTION:
create or replace procedure deptshow
is
dcount number(3);
cursor cdept is select deptno,dname from dept;
cursor cemp is select dept.deptno,count(empno) from dept left join
emp on dept.deptno = emp.deptno group by dept.deptno;
deptnoo dept.deptno%type;
empcount number(2);
rowdept cdept%rowtype;
begin
open cdept;
open cemp;
select count(deptno) into dcount from dept;
dbms_output.put_line('| '||' EMPLOYEE AND
DEPARMENT DETAILS'||' |');
dbms_output.put_line('----------------------------------------------
-----------------------------');
dbms_output.put_line('Number of Departments: '||dcount);
dbms_output.put_line('Deptno'||' '||'Deptname');
loop
fetch cdept into rowdept;
exit when cdept%NOTFOUND;
dbms_output.put_line(rpad(rowdept.deptno,10,' ')||rowdept.dname);
end loop;
close cdept;
dbms_output.put_line(' ');
dbms_output.put_line('Number of Employees in each department: ');
dbms_output.put_line(' ');
dbms_output.put_line('Deptno'||' '||'No.Of Employees');
loop
fetch cemp into deptnoo,empcount;
exit when cemp%NOTFOUND;
dbms_output.put_line(rpad(deptnoo,15,' ')||empcount);
end loop;
close cemp;
dbms_output.put_line('----------------------------------------------
-----------------------------');
dbms_output.put_line('| '||' END
'||' |');
end;
/
1. 4. Write a PL/SQL procedure or function to display output as follows:
EMPLOYEE SALARY DETAILS
------------------------------------------------------------------------------------------
Number of Employees: 14
Total Salary of All Employees: ----
Deptno Total Salary
10 ----
20 -----
30 ----
40 ------
Highest Salary Details:
Employeename salary Deptno
Lowest Salary Details:
Employeename salary Deptno
____________________________________________________________
END
SOLUTION:
create or replace procedure empfinal
is
ecount number(3);
cursor cdept is select dept.deptno,sum(sal) from dept left join emp
on dept.deptno = emp.deptno group by dept.deptno;
deptnoo dept.deptno%type;
ttsal number(6);
tsal number(6);
hsal emp%rowtype;
lsal emp%rowtype;
begin
open cdept;
select count(empno) into ecount from emp;
select sum(sal) into tsal from emp;
select ename,sal,deptno into hsal.ename,hsal.sal,hsal.deptno from
emp where sal = (select max(sal) from emp);
select ename,sal,deptno into lsal.ename,lsal.sal,lsal.deptno from
emp where sal = (select min(sal) from emp);
dbms_output.put_line('| '||' EMPLOYEE SALARY
DETAILS'||' |');
dbms_output.put_line('----------------------------------------------
-----------------------------');
dbms_output.put_line('Number of Employees: '||ecount);
dbms_output.put_line('Total Salary of All Employees: '||tsal);
dbms_output.put_line('Deptno'||' '||'Total Salary');
loop
fetch cdept into deptnoo,ttsal;
if ttsal is null then
ttsal:=0;
end if;
exit when cdept%NOTFOUND;
dbms_output.put_line(rpad(deptnoo,15,' ')||ttsal);
end loop;
close cdept;
dbms_output.put_line(' ');
dbms_output.put_line('Highest salary details: ');
dbms_output.put_line('Employeename'||' '||'salary'||'
'||'Deptno');
dbms_output.put_line(hsal.ename||' '||hsal.sal||'
'||hsal.deptno);
dbms_output.put_line('Lowest salary details: ');
dbms_output.put_line('Employeename'||' '||'salary'||'
'||'Deptno');
dbms_output.put_line(lsal.ename||' '||lsal.sal||'
'||lsal.deptno);
dbms_output.put_line('----------------------------------------------
-----------------------------');
dbms_output.put_line('| '||' END
'||' |');
end;
/
NOTE:
// to execute procedures
Sql> execute procedurename[parameters];
