dbms lab

STUDY OF SQL QUERIES

DATA DEFINITION LANGUAGE

The DDL commands are:

Create Table Alter Table Drop Table Create View Drop View

EMPLOYEE DETAILS:

TABLE STRUCTURE

FIELD NAME DATA TYPE SIZE

EMP_ID NUMBER 4

EMP_NAME ALPHANUMERIC 14

CITY ALPHANUMERIC 14

DESIGNATION ALPHANUMERIC 14

SALARY NUMBER 12

1. Create Command: - Syntax:

create table < table name> (column-name data type (size) constraints);

Description:The create command when applied with above specification creates the fields of

different data type.

2. Alter Command:- Syntax:

a) alter table <table name> add(column-name data type (size));

Description:The alter command when used with add allows us to add an additional column to an

already existing table.

Syntax: b) alter table <table name> modify(column-name data type (size));

Description:The alter command when used with modify redefines the column with the given values but cannot change the

column names.

Syntax: a) alter table <table name> drop(column-name);

Description: The alter command when used with drop deletes the specified column in the table.

3. Drop Command:- Syntax: Drop Table <Table_Name> ;

Description: A table can be dropped (deleted) by using a drop table command.

4. Create View Command:- Syntax:

Create view <view-name > as select <column-name / * > from <table-name>where <condition>

Description:A view is named, derived, virtual table. A view takes the output of a query and treats it

as a table; therefore, a view can be thought of as a “stored query “ or a “virtual table”. The tables upon which a view is based are called base tables.

5. Drop View Command:- Syntax: Drop view <view-name>

Description: A View can be dropped (deleted) by using a drop view command.

6. Truncate Command: Syntax: Truncate table <table-name>;

Description: The details in the table is deleted but the table structure remains.

7. Rename Command : Syntax: Rename <oldtable-name> to <newtable-name>; Description: The old table name is replaced with the new table name.

PROGRAM TO LEARN DDL COMMANDS

CREATE TABLE:SQL> create table empp(emp_id number(4),emp_name varchar(14),city varchar(14),designation varchar(14),salary number(14));

Table created.

SQL> desc empp;

Name Null? Type ----------------------------------------- -------- ----------------------- EMP_ID NUMBER(4) EMP_NAME VARCHAR2(14) CITY VARCHAR2(14) DESIGNATION VARCHAR2(14) SALARY NUMBER(14)

ALTER TABLE:SQL> alter table empp add(doj date,basic number(10,2));

Table altered.

SQL> desc empp;

Name Null? Type ----------------------------------------- -------- ------------------ EMP_ID NUMBER(4) EMP_NAME VARCHAR2(14) CITY VARCHAR2(14) DESIGNATION VARCHAR2(14) SALARY NUMBER(14) DOJ DATE BASIC NUMBER(10,2)

SQL> alter table empp modify(salary number(8));

Table altered.

SQL> desc empp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_ID NUMBER(4) EMP_NAME VARCHAR2(14) CITY VARCHAR2(14) DESIGNATION VARCHAR2(14) SALARY NUMBER(8) DOJ DATE BASIC NUMBER(10,2)

SQL> alter table empp drop(doj,basic);Table altered.

SQL> desc empp; Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_ID NUMBER(4) EMP_NAME VARCHAR2(14) CITY VARCHAR2(14) DESIGNATION VARCHAR2(14) SALARY NUMBER(8)

CREATE VIEW:

SQL> create view emppview as select * from empp;View created.

SQL> desc empp view;

Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_ID NUMBER(4) EMP_NAME VARCHAR2(14) CITY VARCHAR2(14) DESIGNATION VARCHAR2(14) SALARY NUMBER(8)

DROP VIEW:

SQL> drop view emppview;View dropped.

SQL> desc emppview;ERROR:ORA-04043: object emppview does not exist

DROP TABLE:

SQL> drop table empp;Table dropped.

TRUNCATE COMMAND:

SQL> truncate table empp;Table truncated.

SQL>desc empp;

Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_ID NUMBER(4) EMP_NAME VARCHAR2(14) CITY VARCHAR2(14) DESIGNATION VARCHAR2(14) SALARY NUMBER(14)SQL>rename empp to employ;Table renamed.

SQL> desc empp;ERROR:ORA-04043: object empp does not exist

SQL>desc employ;

Name Null? Type ----------------------------------------- -------- ---------------------------- EMP_ID NUMBER(4) EMP_NAME VARCHAR2(14) CITY VARCHAR2(14) DESIGNATION VARCHAR2(14) SALARY NUMBER(14)

RESULT:

Thus the DDL command are executed and verified.

DATA MANIPULATION LANGUAGE

The DML commands are: Insert Delete Update

1. Insert:

Syntax:

Insert into <table-name> values (val1, val2, val3,….,valn);

Description:

The ‘insert into’ command insert the values in the specified table .In the insert into SQL sentence the columns and values have a one to one relationship (i.e) the first value described into the first column, the second value described being inserted into the second column and so on.

2. Delete:

Syntax:

Delete from <table-name> [where <condition>];

Description:

The delete in SQL is used to remove rows from table. To remove1. All the rows from a table.

(Or)2. A select set of rows from a table.

3. Update:

Syntax:

Update <table-name> set fieldname=<expression> [where condition];

Description:

The update command is used to change or modify data values in a table. To update1. All the rows from a table. (Or)2. A select set of rows from a table.

PROGRAM TO LEARN DML COMMANDS

INSERT COMMAND:

SQL> insert into empp values (&emp_id,'&emp_name','&city','&designation','&salary');Enter value for emp_id: 100Enter value for emp_name: jamesEnter value for city: bangaloreEnter value for designation: modleaderEnter value for salary: 20000

old 1: insert into empp values (&emp_id,'&emp_name','&city','&designation','&salary')new 1: insert into empp values(100,'james','bangalore','modleader','20000')

1 row created.

SQL> /Enter value for emp_id: 101Enter value for emp_name: ganeshEnter value for city: chennaiEnter value for designation: softengiEnter value for salary: 21000old 1: insert into empp values(&emp_id,'&emp_name','&city','&designation','&salary')new 1: insert into empp values(101,'ganesh','chennai','softengi','21000')

1 row created.

SQL> /Enter value for emp_id: 103Enter value for emp_name: priyaEnter value for city: calcuttaEnter value for designation: managerEnter value for salary: 50000old 1: insert into empp values (&emp_id,'&emp_name','&city','&designation','&salary')new 1: insert into empp values(103,'priya','calcutta','manager','50000')

1 row created.

SQL> select * from empp;

EMP_ID EMP_NAME CITY DESIGNATION SALARY----------- ----------------- -------------- -------------- ---------- ------------- 100 james bangalore modleader 20000 101 ganesh chennai softengi 21000 103 priya calcutta manager 50000

UPDATE COMMAND:

SQL> update empp set designation='engineer'where emp_id=101;1 row updated.


EMP_ID EMP_NAME CITY DESIGNATION SALARY----------- ----------------- -------------- -------------- ---------- ----------------- 100 james bangalore modleader 20000 101 ganesh chennai engineer 21000 103 priya calcutta manager 50000

DELETE COMMAND:

SQL> delete from empp where emp_id=100;

1 row deleted.

SQL> select * from empp;EMP_ID EMP_NAME CITY DESIGNATION SALARY----------- ----------------- -------------- -------------- ---------- ------------- 101 ganesh chennai engineer 21000 103 priya calcutta manager 500

RESULT:Thus the DML commands are executed and verified.

DATA QUERY LANGUAGE

The DQL commands are: Select Group by Having Order by Null

1. Select Statement:

Syntax: Select <attribute list> from <table list>[where clause];

Description: Select command is used to retrieve data from one or more tables or columns. The attribute list is a list of

attributes name whose values are displayed by query. A missing where clause indicate no condition on tuple selection. The condition is a Boolean expression that identifies the tuples to be retrieved by the query.

Distinct Clause:

Syntax: Select distinct <column name> from <table name>;

Description: Displays the distinct values from a table by eliminating the duplicate values. It performsGrouping of the specified fields when queried with distinct statement.

2. Order By Clause:

Syntax: Select <column name> from <table name> order by <column name>;

Description: Order By sorts the output of any specified column or expression. The Order By clause must always have task in any select statement. The default order is ascending order. we should specify the keyword ‘desc’ if we want it in descending order.

3. Group By Clause:

Syntax: Select <column name> from <table name > group by <column name>;

Description:. The Group By clause specifies the grouping of function to appear in the select clause. So that the value resulting from group of tuples appear along with the values of grouping attributes are SUM ,AVERAGE , MAX, MIN, COUNT,STDDEV

4. Having Clause:

Syntax: Select <column name> from <table name> where <condition> Group by <condition> having<condition>;

Description: The ‘having’ clause is used to specify certain conditions on rows, retrieved by using group by clause. This clause should be preceded by a ‘group by’ clause.

5. Null Command:

Syntax: Select <column-name> NVL(substitution column,substituted value) from <table name> where <condition>;

Description: The NVL function helps in substituting the value in the null fields.but this function only displays the change and does not update the columns.

PROGRAM TO LEARN DQL COMMANDS

SELECT COMMAND:


EMP_ID EMP_NAME CITY DESIGNATION SALARY----------- ------------- ---- -------------- -------------- ---------- ------------ 100 james bangalore modleader 20000 101 ganesh chennai engineer 21000

102 priya calcutta manager 50000

103 kamal coimbatore projlead 40000

104 vishnu bombay ceo 15000

105 kirthika chennai accountant 15000

6 rows selected.

SELECT AND WHERE CLAUSE:

1. List down employee records whose salary is >15000.

SQL> select * from empp where salary>15000;

EMP_ID EMP_NAME CITYDESIGNATION SALARY----------- ----------------- -------------- -------------- ---------- ------------- 100 james bangalore modleader 20000101 ganesh chennai engineer 21000102 priya calcutta manager 50000 103 kamal coimbatore projlead 40000

2. List down the emp_id,employee name and salary fields.

SQL> select emp_id,emp_name,salary from empp;

EMP_ID EMP_NAME SALARY

---------- -------------- --------------------------- 100 james 20000 101 ganesh 21000 102 priya 50000 103 kamal 40000 104 vishnu 15000 105 kirthika 15000

6 rows selected.

SELECT-ORDER BY:

3. List down the employee salary in ascending order.

SQL> select * from empp order by salary;

EMP_ID EMP_NAME CITY DESIGNATION SALARY----------- ----------------- -------------- -------------- ---------- -------------104 vishnu bombay ceo 15000105 kirthika chennai accountant 15000100 james bangalore modleader 20000101 ganesh chennai engineer 21000103 kamal coimbatore projlead 40000102 priya calcutta manager 500006 rows selected.

4. List down the employee salary in descending order.SQL> select * from empp order by salary desc;

EMP_ID EMP_NAME CITY DESIGNATION SALARY----------- ----------------- -------------- -------------- ---------- ------------- 102 priya calcutta manager 50000103 kamal coimbatore projlead 40000101 ganesh chennai engineer 21000100 james bangalore modleader 20000104 vishnu bombay ceo 15000 105 kirthika chennai accountant 150006 rows selected.

SELECT DISTINCT:


EMP_ID EMP_NAME CITY DESIGNATION SALARY----------- ----------------- -------------- -------------- ---------- -------------100 james bangalore modleader 20000101 ganesh chennai engineer 21000102 priya calcutta manager 50000103 kamal coimbatore projlead 40000104 vishnu bombay ceo 15000105 kirthika chennai accountant 15000106 subu bombay engineer 25000 107 priya bangalore ceo 200008 rows selected.

SQL> select distinct city from empp;

CITY--------------bangalorebombaycalcuttachennaicoimbatore

SELECT – GROUP BY:

SQL> select designation, max(salary) from empp group by designation;

DESIGNATION MAX(SALARY)-------------- ------------------------------accountant 15000ceo 20000engineer 25000manager 50000modleader 20000projlead 40000

6 rows selected.

SELECT – HAVING CLAUSE:

SQL> select max(salary),min(salary),sum(salary) from empp group by designation having designation='ceo ';

MAX(SALARY) MIN(SALARY) SUM(SALARY)--------------------------------------- ----------- -------------- 20000 15000 35000

NULL COMMAND:SQL> select * from empp;

EMP_ID EMP_NAME CITY DESIGNATION SALARY---------- -------------- -------------- -------------- ----------------------------100 james bangalore modleader 20000101 ganesh chennai engineer 21000102 priya calcutta manager 50000103 kamal coimbatore projlead 40000104 vishnu bombay ceo 15000105 kirthika chennai accountant 15000106 subu bombay engineer 25000107 vijay bangalore ceo 20000

8 rows selected.

SQL> select emp_id,NVL(emp_name,'priya')from empp whereemp_id =107

EMP_ID NVL(EMP_NAME, 'priya')---------- ------------------------------------- 107 priya


EMP_ID EMP_NAME CITYDESIGNATION SALARY---------- -------------- -------------- ------------------------- ------------- 100 james bangalore modleader 20000101 ganesh chennai engineer 21000

102 priya calcutta manager 50000 103 kamal coimbatore projlead 4000 104 vishnu bombay ceo 15000 105 kirthika chennai accountant 15000 106 subu bombay engineer 25000 107 vijay bangalore ceo 20000

8 rows selected.

BETWEEN CLAUSE:


EMP_ID EMP_NAME CITY DESIGNATION SALARY---------- -------------- -------------- -------------- ---------------------------- 100 james bangalore modleader 20000 101 ganesh chennai engineer 1000 102 priya calcutta manager 50000103 kamal coimbatore projlead 7600104 vishnu bombay ceo 5000105 kirthika chennai accountant 15000

6 rows selected.

SQL> select * from empp where salary between 20000 and 55000;

EMP_ID EMP_NAME CITY DESIGNATION SALARY---------- -------------- -------------- -------------- --------------------------- 100 james bangalore modleader 20000 101 ganesh chennai engineer 21000 102 priya calcutta manager 50000

SQL> select * from empp where salary not between 20000 and 55000;

EMP_ID EMP_NAME CITY DESIGNATION SALARY---------- -------------- -------------- -------------- ---------------------------- 103 kamal coimbatore projlead 57600 104 vishnu bombay ceo 15000 105 kirthika chennai accountant 15000

IN AND NOT IN PREDICATE:

SQL> select * from empp where city in ('chennai','bombay');

EMP_ID EMP_NAME CITY DESIGNATION SALARY---------- -------------- -------------- -------------- ----------------------------101 ganesh chennai engineer 21000 104 vishnu bombay ceo 15000 105 kirthika chennai accountant 15000

SQL> select * from empp where city not in ('chennai','bombay');

EMP_ID EMP_NAME CITY DESIGNATION SALARY---------- ------------- - -------------- -------------- --------------------------- 100 james bangalore modleader 20000102 priya calcutta manager 50000

103 kamal coimbatore projlead 57600

RESULT:

Thus the DQL commands are executed and verified.

TCL COMMANDSTRANSACTION CONTROL LANGUAGE

The DCL commands are: Commit Rollback Save point

1. COMMIT:

Syntax:SQL> set auto commit on ;SQL> set auto commit off ;SQL>commit ;

Description:

Commit command tells the DBMS to make permanent changes made to temporary copies of the data updating the permanent database tables to match the updated temporary copies.

2. ROLL BACK:

Syntax:SQL> rollback ;

Description:

Rollback tells the DBMS to undo any changes made to the DBMS after the most recent commit.

3. SAVE POINT:

Syntax: SQL>Savepoint<name>;

Description:

Save point are like markers to divide a very lengthy transaction to smaller ones. They are used to identify a point in transaction to which we can later rollback. Thus savepoint is used in conjunction with rollback to rollback portions of the current transaction.

PROGRAM TO LEARN TCL COMMANDS

ROLLBACK:

SQL> select * from itemfile;

ITEMCODE ITEMCLOSE PDT_CTGYQTY_HAND RELEVEL MAXLEVEL ITEMRATE---------------------------- ---------- ---------- ---------- ---------- ---------- 1 eraser stationary 100 50 200 3 2 chain fancy 40 98 200 25 3 tomato vegetable 460 54 157 5 4 pen stationary 85 50 80 6

4 rows selected.

SQL> delete from itemfile where itemcode=4;

1 row deleted.

SQL> select * from itemfile; ITEMCODE ITEMCLOSE PDT_CTGY QTY_HAND RELEVEL MAXLEVEL ITEMRATE---------- -------------- -------------- -------------- ---------------------------- 1 eraser stationary 100 50 200 3 2 chain fancy 40 98 200 25 3 tomato vegetable 460 54 157 5

3 rows delected.

SQL> rollback;Rollback complete.


ITEMCODE ITEMCLOSE PDT_CTGY QTY_HAND RELEVEL MAXLEVEL ITEMRATE---------- -------------- -------------- -------------- ---------------------------- 1 eraser stationary 100 50 200 3 2 chain fancy 40 98 200 25 3 tomato vegetable 460 54 157 5 4 pen stationary 85 50 80 6

COMMIT:

SQL> delete from itemfile where itemcode=4;1 row deleted.

SQL> commit;Commit complete.


ITEMCODE ITEMCLOSE PDT_CTGY QTY_HAND RELEVEL MAXLEVEL ITEMRATE---------- -------------- -------------- -------------- ---------------------------- 1 eraser stationary 100 50 200 3 2 chain fancy 40 98 200 25 3 tomato vegetable 460 54 157 5SQL> rollback;

Rollback complete.


ITEMCODE ITEMCLOSE PDT_CTGY QTY_HAND RELEVEL MAXLEVEL ITEMRATE---------- -------------- -------------- -------------- ---------------------------- 1 eraser stationary 100 50 200 3 2 chain fancy 40 98 200 25 3 tomato vegetable 460 54 157 5

SAVEPOINT:

SQL> savepoint s1;Savepoint created.

SQL> update itemfile set itemrate=20 where itemcode=3;1 row updated.

SQL> savepoint s2;Savepoint created.

SQL> update itemfile set itemrate=16 where itemcode=1;1 row updated.



SQL> rollback to s2;

Rollback complete.



SQL> rollback to s1;

Rollback complete.


ITEMCODE ITEMCLOSE PDT_CTGY QTY_HAND RELEVEL MAXLEVEL ITEMRATE---------- -------------- -------------- -------------- ---------------------------- 1 eraser stationary 100 50 200 3 2 chain fancy 40 98 200 25

3 tomato vegetable 460 54 157 5

RESULT:

Thus the DCL commands are executed and verified.

DCL COMMANDSDATA CONTROL LANGUAGE

The DCL commands are: Grant Revoke

1. GRANT:

Syntax: SQL>grant <privileges> on <table name> to user ;

Description:Grant gives specific SQL statement access or individual data objects to a user or a group of users.

2. REVOKE:

Syntax: SQL> Revoke <privileges> on <tablename> from user ;

Description:

Revoke removes specific SQL statement access previously granted on individual database objects from a user or group of users.

PREDICATES:SQL>select*from itemlist;

CODE ITEM PRICE-------- -------- ---------1 Pen 20.52 notebook 70.753 Bat 99.99

1. ALL

Syntax:Select*from table_name where column_name >all(column_value)

Example:Sql>select *from itemlist where code>ALL(I);

CODE ITEM PRICE-------- -------- ---------- 2 Notebook 70.75 3 Bat 99.99

2. SOME:

Syntax Select column_name from table_name where col_name2>some(selectcolumn_name from table_name where collum _name3=’value’);

Example:SQL>select item from itemlist where code>some(select code from itemlist where price=20.5);

ITEM--------NotebookBat

3. ANYSyntax: Select column_name from table name where column_name>any (select column_name from table_name where column_name=’value’);Example: SQL>select item from itemlist where price>any(select price from itemlist where code=2);

ITEM----------Bat

4. LIKESYNTAX: Select*from table_name ehere column_name like(‘value’)Example: SQL>select *from itemlist where item like(‘P%’); CODE ITEM PRICE ----------- ------------ ------------- 1 PEN 20.5*************************************************************************** 2. ENTITY INTEGRITY CONSTRAINTS:

PRIMARY KEY:

Syntax: Create table table _name (field datatype) constraints <label> primary key(column_name);

SQL> Create table note (id number(6)constraint flo not null, num number(6)primary key);

Table created

SQL> desc note;

NAME NULL? TYPE-------------- -------------- -------------ID NOTNULL NUMBER (6)

NUM NOTNULL NUMBER (6)SQL> create table micky (id number (5),name varchar(6) constraints mouse primary key(id,name));

Table created NAME NULL? TYPE ---------------- ---------------- --------------- ID NUMBER (5) NAME VARCHAR (6)

UNIQUE:

Syntax: Create table table _name (field datatype) constraints <label> unique (column_name);

SQL> create table jeep (id number(5) constraints van unique);Table created

SQL> desc jeep; NAMES NULL? TYPE---------------- ------------------ ----------------- ID NUMBER (5)SQL>insert into jeep(&id);

Enter the value for id:41 row createdSQL> insert into jeep(&id);Error at line1:] REFERENCE INTEGRITY MODEL:

Syntax: Create table table _name ( datatypes ……..) constraints from references table_name (column_name);

SQL> create table jeep(id number(5)constraint from references jeep(id));Table created SQL>desc jeep; NAME NULL? TYPE ------------ -------------- ------------- ID NUMBER (5)

BUILT-IN FUNCTION1. Group function or Aggregate function:

Sl.No Function Description1 AVG Determines the Average of the specified values of column, Ignoring null values.2 COUNT Determines the total number of values of a specified column.3 MAX Determines the maximum value of a specified column, ignoring null values. 4 MIN Determines the minimum values of a specified column, ignoring null values.5 SUM Determines the sum of a specified column, ignoring null values.6 ATDDEV Determines the standard derivation of a specified column , ignoring null values. 7 VARIANCE Determines the variance of expression , ignoring null values.

2. Character Function:

Sl.No Function Description1 LLOR CONCAT Concatenates two string together changes the first letter of a word or series of words

into upper case.2 INIT CAP Find the location of character in a string otherwise return 0.3 INSTR Find the Location of character in a string otherwise return 0.4 LENGTH Returns the length of a string.5 LOWER Converts every letter in a string to lower case.6 UPPER Converts every letter in a string to upper case.7 LPAD Makes a string a certain length by a adding a certain set of character to the left.8 RPAD Makes a string a certain length by a adding a certain set of character to the Right.9 LTRIM Trim all the occurrence of any one of a set of character of the Left side of string.10 RTRIM Trim all the occurrence of any one of a set of character of the Right side of string.11 SOUNDEX Finds words that sound like examples.12 SUBSTR Clip out a piece of a string.13 CHR Return the character of the specified ASCII values.14 ASCII Return the ASCII values of the specified character. 15 TRANSLATE Replace the character by character.16 REPLACE Replace the specified string or character by the existing string or character if that

string in found case sensitive.

3. Number Function:

Sl.No Function Description1 ABS Return absolute values.2 CELL Smallest integer large than or equal to value.3 FLOOR Largest integer smaller than or equal to value.4 SART Returns squares not of the specified by the number. 5 POWER Return power specified by number.6 SIGN Return-1 of the number is negative;

Return 1 if the number is positive and return 0 if the number is 0.7 TRUNC Truncates the number after the decimal depending on the specified integer.8 ROUND Round the expression to the specified number of decimals.9 EXP Returns e raised to n power.10 MOD Return module value.11 LN Nateual Logarithm values.12 LOL Base 10 Loguith value.13 VSIZE Storade size of a value.14 GREATEST Greatest value of a list.15 LEAST Least value of a llist.

4. Date Function:

Sl.No Function Description1 SYS DATE Returns system date2 ADD_MONTHS Add or substracts months to or from a date, return date as result.3 NEXT_DAY Returns the date of next specified day of the week after the date.4 CASE_DAY Returns the date of the last day of the months specified.5 MONTHS_BETWEEN Returns number of months between dates.6 ROUND Round the date d by the specified format, if format is not specified it defaults to ‘DD’.

Which date to the neatest day.7 TRUNC Rounds the date d truncates to the unit specified by omitted is defaults to ‘DP’ which

truncates to the nearest day.

5. Convertion Function:

Sl.No Function Description1 TO_CHAR Convert the date‘d’ to character format ‘f’.2 TO_DATE Convert the date‘d’ to date format ‘f’.3 DECODE Records the specified date to another representation.4 TO_NUMBER Convert the character to number.

CHARACTER FUNCTION:

SQL> select * from stud;

ROLLNO NAME DEPT MARKS---------- --------------------------- -------------- ---------- 3 mohan cse 50 4 parandha bme 78 5 rajan cse 78 1 kumaran cse 90 2 midhun ece 80

SQL> select upper(name) from stud;

UPPER(NAME)---------------MOHANPARANDHARAJANKUMARANMIDHUN

SQL> select lower(name) from stud;

LOWER(NAME)---------------mohanparandharajankumaranmidhun

SQL> select initcap(name) from stud;

INITCAP(NAME)---------------MohanParandhaRajanKumaranMidhun

SQL> select substr('girija',1,4) from dual;

SUBS----giri

SQL> select ltrim('welcome','wel') from dual;

LTRI----come

SQL> select rtrim('welcome','come') from dual;

RTR---wel

SQL> select lpad('welcome',13,'$') from dual;

LPAD('WELCOME-------------$$$$$$welcome

SQL> select rpad('welcome',13,'$') from dual;

RPAD('WELCOME-------------welcome$$$$$$

SQL> select name from stud where name like 'm%';

NAME---------------mohanmidhun

SQL> select replace('jack jill joy','j','b') from dual;

REPLACE('JACK-------------back bill boy

SQL> select translate('jack jall joy','ja','b') from dual;

TRANSLATE('-----------bck bll boy

DATE FUNCTION:SQL> select add_months('25-dec-07',4) from dual;

ADD_MONTH---------25-APR-08

SQL> select last_day('25-dec-07') from dual;

LAST_DAY(---------31-DEC-07

SQL> select months_between('15-dec-07','13-aug-07') from dual;

MONTHS_BETWEEN('15-DEC-07','13-AUG-07')--------------------------------------- 4.06451613

SQL> select next_day('1-jul-07','friday') from dual;

NEXT_DAY(---------06-JUL-07

SQL> select greatest('22-may-07','22-dec-07') from dual;

GREATEST(---------22-may-07

AGGREGATE FUNCTION:

SQL> select avg(marks) from stud;

AVG(MARKS)---------- 75.2

SQL> select min(marks) from stud;

MIN(MARKS)---------- 50

SQL> select max(marks) from stud;

MAX(MARKS)---------- 90

SQL> select count(marks) from stud;COUNT(MARKS)------------ 5

SQL> select sum(marks) from stud;SUM(MARKS)

---------- 376

SQL> select stddev(1755) from dual;STDDEV(1755)------------ 0

SQL> select variance(1755) from dual;VARIANCE(1755)-------------- 0

NUMERIC FUNCTIONS:

SQL> SELECT ABS(-15) FROM DUAL; ABS(-15)---------- 15

SQL> select abs(-15) from dual; ABS(-15)---------- 15

SQL> select power(2,3) from dual;POWER(2,3)---------- 8SQL> select round(189.9999,2) from dual;ROUND(189.9999,2)----------------- 190SQL> select trunc(189.9999,2) from dual;TRUNC(189.9999,2)----------------- 189.99

SQL> select mod(10,3) from dual; MOD(10,3)---------- 1SQL> select sign(16) from dual; SIGN(16)---------- 1

SQL> select sign(-16) from dual; SIGN(-16)---------- -1SQL> select sin(45) from dual; SIN(45)----------.850903525

SQL> select cos(45) from dual; COS(45)

----------.525321989

SQL> select sinh(60) from dual; SINH(60)----------5.7100E+25

SQL> select cosh(60) from dual; COSH(60)----------5.7100E+25

SQL> select ceil(134.78) from dual;CEIL(134.78)------------ 135SQL> select floor(134.78) from dual;FLOOR(134.78)------------- 134

SQL> select ln(35) from dual;

LN(35)----------3.55534806

SQL> select log(10,1000) from dual;

LOG(10,1000)------------ 3

SQL> select sqrt(25) from dual;

SQRT(25)---------- 5

Operators

RELATIONAL/COMPERISONOPERATIONS

LOGICALOPERATORS

SPECIALOPERATORS

ARITHMETICOPERATORS

SETOPERATORS

= AND In + Union > OR Between - Union all < NOT Like * Intersect >= Is / Minus <= Any <> All != The

Set Operators

1. Union Description:

Multiple queries can be put together and their output combined using union clause with duplication, the union clause merges the output of 2 or more queries into single set of rows and columns.

SQL> select c_name from borrower union select c_name from depositor;

C_NAME----------adamscurryhayesjacksonjohnsonjoneslindsaysmith

2. Union All Description: Multiple queries can be put together and their output combined using union all clause with duplicate.

SQL> select c_name from borrower union all select c_name from depositor;

C_NAME----------adamscurryhayesjacksonjonessmithsmithwillamshayesjohnsonjohnsonjoneslindsay

smithturner15 rows selected.

3. Intersection Description:

Multiple queries can be put together and their output combine using union clause intersect clause outputOnly rows produced by both the queries intersected.

SQL> select c_name from borrower intersect select c_name from depositor;

C_NAME----------hayesjonessmith

4. Minus Description: Multiple queries can be put together and their output can be combines using minus clauses. The minus Clause output the rows produced by both the first query, after filtering the rows retrieved by second query.

SQL> select c_name from borrower minus select c_name from depositor;

C_NAME----------adamscurryjacksonwillams

JOINS AND NESTED QUERIESAIM: To study about various joins and nested queries.

JOINS: Joins queries are used to retrieve data from multiple tables.

TYPES OF JOINS:

1. EQUI JOIN2. CARTESIAN JOIN3. OUTER JOIN

RIGHT OUTER JOIN LEFT OUTER JOIN

TABLE STRUCUUTRE:

SQL> select * from empl;

EMP_ID EMP_NAME CITY SALARY------------ ------------------ ---------- ------------

10 Allwin Pdy 50000 20 Evelin Chennai 65000 30 Jesus World 500000 40 Computer Chennai 44000 50 Poornima Pdy 50000 60 Dravid Pdy 50000

6 rows selected.

SQL> select * from dept1;

DEPT_NO EMP_NAME LOCATION-------------- ----------------- ---------------- 30 Jesus World 60 Dravid Chennai 50 Poornima Delhi 20 CPU Compaq

SQL> select * from grade;

GRADE LOWEST_SALARY HIGHEST_SALARY----------- --------------------------- --------------------------- 1 700 1200 2 1201 1400 3 1401 2000 4 2001 5000 5 10000 50000 6 50000 500016 rows selected.

Equi Join:-

Description:-

Equi joins are done by equating tables based on a criteria.It uses ‘=’ symbol.

1)List the employee details with their department name and location.

SQL> select location,salary,dept1.dept_no from empl,dept1 where empl.emp_name=dept1.emp_name;

LOCATION SALARY DEPT_NO---------------- ------------ -------------Chennai 50000 60World 500000 30Delhi 50000 50

Cartesian Join:-

Cartesian join is all combination of rows from different tables.

2)List all the different possible rows from emp & dept tables.

SQL> select e.emp_name,e.salary,d.emp_name,d.location from empl e,dept1 d;

EMP_NAME SALARY EMP_NAME LOCATION--------------- ------------ ----------------- ---------------Allwin 50000 Jesus WorldEvelin 65000 Jesus WorldJesus 500000 Jesus WorldComputer 44000 Jesus WorldPoornima 50000 Jesus WorldDravid 50000 Jesus WorldAllwin 50000 Dravid ChennaiEvelin 65000 Dravid ChennaiJesus 500000 Dravid ChennaiComputer 44000 Dravid ChennaiPoornima 50000 Dravid Chennai

EMP_NAME SALARY EMP_NAME LOCATION--------------- ------------ ------------------ ---------------Dravid 50000 Dravid ChennaiAllwin 50000 Poornima DelhiEvelin 65000 Poornima DelhiJesus 500000 Poornima DelhiComputer 44000 Poornima DelhiPoornima 50000 Poornima DelhiDravid 50000 Poornima DelhiAllwin 50000 CPU CompaqEvelin 65000 CPU CompaqJesus 500000 CPU Compaq

Computer 44000 CPU Compaq

EMP_NAME SALARY EMP_NAME LOCATION--------------- ------------ ----------------- ---------------Poornima 50000 CPU CompaqDravid 50000 CPU Compaq

24 rows selected.

Non Equi Join:-

Non equi joins are other than ‘=’ operator if join is formed using any one of the relational operators(<,>,!=,<>,>=,<=).

3) List the ename & grade of the employees deprnding on their salary.

SQL> select e.emp_name,g.grade from empl e,grade g where e.salary>=g.lowest_salary and e.salary<=g.highest_salary;

EMP_NAME GRADE----------------- ----------Allwin 5Computer 5Poornima 5Dravid 5Allwin 6Poornima 6Dravid 6

7 rows selected.

Outer Join:-

Description:-

Outer joins retrieves the row returned by equi join as well as the row that do not match any row from other tables.

Right Outer Join:-

4)a.List the name,department & deptno from emp,dept tables even if the department contain no employees.

SQL> select e.emp_name,d.emp_name,d.dept_no from empl e,dept1 d where e.emp_name=d.emp_name(+);

EMP_NAME EMP_NAME DEPT_NO----------------- ------------------ --------------AllwinComputerDravid Dravid 60EvelinJesus Jesus 30Poornima Poornima 50

6 rows selected.

4)b.List the all the field from emp,dept tables even if the department contain no employees.

SQL> select * from empl natural right outer join dept1;

EMP_NAME EMP_ID CITY SALARY DEPT_NO LOCATION----------------- ------------ ---------- ------------ --------------- ----------------

Jesus 30 World 500000 30 WorldPoornima 50 Pdy 50000 50 DelhiDravid 60 Pdy 50000 60 ChennaiCPU 20 Compaq

Left Outer Join:-

SQL> select e.emp_name,d.emp_name,d.dept_no from empl e,dept1 d where e.emp_name(+)=d.emp_name;

EMP_NAME EMP_NAME DEPT_NO----------------- ----------------- -------------- CPU 20Dravid Dravid 60Jesus Jesus 30Poornima Poornima 50

SQL> select * from empl natural left outer join dept1;

EMP_NAME EMP_ID CITY SALARY DEPT_NO LOCATION----------------- ------------ ---------- ------------ --------------- ----------------

Jesus 30 World 500000 30 WorldDravid 60 Pdy 50000 60 ChennaiPoornima 50 Pdy 50000 50 DelhiComputer 40 Chennai 44000Allwin 10 Pdy 50000Evelin 20 Chennai 65000

6 rows selected.

PL/SQL PROGRAMS

1. ADDITION OF TWO NUMBERSAIM:

To write the PL\SQL program to find the addition of two numbers.

PROGRAM

declarex integer;y integer;z integer;beginx:=&x;y:=&y;z:=x+y;dbms_output.put_line('The Sum of two number is ='||z);end;

OUTPUT :

SQL>/

SQL> set serveroutput on;

Enter value for x: 10old 6: x:=&x;new 6: x:=10;

Enter value for y: 20old 7: y:=&y;new 7: y:=20;

The Sum of two number is =30

PL/SQL procedure successfully completed.

RESULT:Thus the PL\SQL program to find the addition of two numbers are executed and verified.

FACTORIAL OF A NUMBER

AIM:

To write the PL/SQL program to find the factorial of a number.

PROGRAM:

declaref number:=1;i number;n number:=&n;beginfor i in 1..nloopf:=f*i;end loop;dbms_output.put_line('The factorial of a given no is:'||f);end;

SAMPLE INPUT OUTPUT :

SQL> set serveroutput on;SQL>/Enter value for n: 6old 4: n number:=&n;new 4: n number:=6;The factorial of a given no is:720


RESULT:

Thus the PL/SQL program to find the factorial of a number is executed and verified.

PALINDROME

AIM:

To write the PL/SQL program to check whether the given string is a Palindrome.

PROGRAM:

declarelen number;a integer;str1 varchar(10):='&str1';str2 varchar(10);beginlen:=length(str1);a:=len;for i in 1..aloopstr2:=str2||substr(str1,len,1);len:=len-1;end loop;if (str1=str2) thendbms_output.put_line(str1 || ' is a palindrome');elsedbms_output.put_line(str1 || ' not a palindrome');end if;end;


SQL> set serveroutput on;SQL>/Enter value for str1: lirilold 4: str1 varchar(10):='&str1';new 4: str1 varchar(10):='liril';liril is a palindrome


SQL> /Enter value for str1: faithold 4: str1 varchar(10):='&str1';new 4: str1 varchar(10):='faith';faith not a palindrome


RESULT:

Thus the PL/SQL program to check the given string is a Palindrome or not is executed and verified.

FIBONACCI

AIM:

To write the PL/SQL program to find the Fibonacci of a given number.

PROGRAM:declarei number;c number;n number:=&n;a number:=-1;b number:=1;beginfor i in 1..nloopc:=a+b;dbms_output.put_line(c);a:=b;b:=c;end loop;end;

SAMPLE INPUT OUTPUT :SQL> set serveroutput on;SQL>/Enter value for n: 5old 4: n number:=&n;new 4: n number:=5;01123PL/SQL procedure successfully completed.

RESULT:Thus the PL/SQL program to find the Fibonacci of a given number is executed and

verified.SUM OF SERIES

AIM:

To write the PL/SQL program to find the Sum of series.

PROGRAM:

declaren number:=&n;i number;begini:=n*(n+1);n:=i/2;dbms_output.put_line('The sum of series is:'||n);end;

(or)

declaren number;s number;i number;beginn:=&n;s:=0;for i in 1..nloops:=s+i;end loop;dbms_output.put_line('sum='||s);end;SAMPLE INPUT OUTPUT :

SQL> set serveroutput on;SQL>/Enter value for n: 5

old 2: n number:=&n;new 2: n number:=5;The sum of series is:15PL/SQL procedure successfully completed.RESULT:

Thus the PL/SQL program to find the Sum of series is executed and verified.TRIGGERS

AIM:To write a PL/SQL program using triggers to replicate the record.

TABLE STRUCTURE 1:Table name: trig11


ROLL NO NUMERIC 3NAME ALPHANUMERIC 10MARK1 NUMERIC 3MARK2 NUMERIC 3MARK3 NUMERIC 3

SQL> create table trig11(rlno number(3),name varchar(10),mark1 number(3),mark2 number(3),mark3 number(3));Table created.

TABLE STRUCTURE 1:Table name: trig12


ROLL NO NUMERIC 3NAME ALPHANUMERIC 10MARK1 NUMERIC 3MARK2 NUMERIC 3MARK3 NUMERIC 3

SQL> create table trig12(rlno number(3),name varchar(10),mark1 number(3),mark2 number(3),mark3 number(3));Table created.

PROGRAM:

create or replace trigger cc after insert or delete or update on trig11for each rowbeginif inserting theninsert into trig12 values(:new.rlno,:new.name,:new.mark1,:new.mark2,:new.mark3);elsif deleting thendelete from trig12 where rlno=:old.rlno;elseupdate trig12 set mark1=:new.mark1,mark2=:new.mark2,mark3=:new.mark3 where rlno=:old.rlno;end if;end;

SAMPLE INPUT OUTPUT:

SQL> /Trigger created.

SQL> select * from trig11;

RLNO NAME MARK1 MARK2 MARK3---------- ---------- ---------- -------------------------- ----------

2 ss 78 76 583 tt 98 76 544 uu 76 56 35

SQL> select * from trig12;

RLNO NAME MARK1 MARK2 MARK3---------- ------ ---- ------------------------- ---------- ---------- 2 ss 78 76 58 3 tt 98 76 54 4 uu 76 56 35

RESULT:

Thus the PL/SQL block program using triggers is executed and verified.

TRIGGERS

AIM:

To write a PL/SQL program to display the old and new values of a record after updating using triggers.

PROGRAM:

create or replace trigger xxx after update on sivafor each rowbegindbms_output.put_line('mark1 old value:'||:old.mark1);dbms_output.put_line('mark2 new value:'||:new.mark1);dbms_output.put_line('mark2 old value:'||:old.mark2);dbms_output.put_line('mark2 new value:'||:new.mark2);dbms_output.put_line('mark3 old value:'||:old.mark3);dbms_output.put_line('mark3 new value:'||:new.mark3);end;


SQL>/Trigger created.

SQL> update siva set mark1=90,mark2=89,mark3=99 where rlno=2;mark1 old value:99mark2 new value:90mark2 old value:78mark2 new value:89mark3 old value:67mark3 new value:991 row updated.

SQL> select * from siva;RLNO NAME MARK1 MARK2 MARK3-------------------------- ----- ---------- ---------- ---------- 1 ramya 100 98 87 2 siva 99 78 67 3 sang 75 87 65SQL> select * from siva;

RLNO NAME MARK1 MARK2 MARK3---------- --------------------- ---------- ---------- ---------- 1 ramya 100 98 87 2 siva 90 89 99 3 sang 75 87 65

RESULT:

Thus the PL/SQL block program using triggers is executed and verified.

TRIGGERS

AIM:

To write a PL/SQL program to restrict the entries into the table using triggers.

PROGRAM:

create or replace trigger triggn after insert or delete or update on sivafor each rowbeginif inserting thenraise_application_error(-2006,'insertion not possible');elsif deleting thenraise_application_error(-2007,'deletion is not possible');elseraise_application_error(-2008,'updation is not possible');end if;end;

SQL> select * from siva;

RLNO NAME MARK1 MARK2 MARK3---------- ----- ---------- ---------- ------------------------- 1 ramya 100 98 87 2 siva 90 89 99 3 sang 90 89 99

SQL> insert into siva values(4,'priya',56,65,67);1 row created.

SQL> select * from siva;

RLNO NAME MARK1 MARK2 MARK3--------------------- ------ ----- ---------- ---------- ---------- 1 ramya 100 98 87 2 siva 90 89 99 3 sang 90 89 99 4 priya 56 65 67

SQL> /Trigger created.

SQL> insert into siva values(5,'kavi',78,87,65);insert into siva values(5,'kavi',78,87,65)*ERROR at line 1:ORA-21000: error number argument to raise_application_error of -2006 is out ofrangeORA-06512: at "SCOTT.TRIGGN", line 3ORA-04088: error during execution of trigger 'SCOTT.TRIGGN'

SQL> delete from siva where rlno=4;delete from siva where rlno=4*ERROR at line 1:ORA-21000: error number argument to raise_application_error of -2007 is out ofrangeORA-06512: at "SCOTT.TRIGGN", line 5ORA-04088: error during execution of trigger 'SCOTT.TRIGGN'

SQL> update siva set mark1=99 where rlno=4;update siva set mark1=99 where rlno=4*ERROR at line 1:ORA-21000: error number argument to raise_application_error of -2008 is out ofrangeORA-06512: at "SCOTT.TRIGGN", line 7ORA-04088: error during execution of trigger 'SCOTT.TRIGGN'

RESULT:

Thus the PL/SQL block program using triggers is executed and verified.EXCEPTION HANDLING

Error condition in PL/SQL is termed as an exception. There are two types of Exception. They are

Predefined Exception User-defined Exception

An Exception is raised when an error occurs. In case of an error, normal execution stops and the control is immediately transferred to the exception handling part of the PL\SQL block. Predefined Exception is raised automatically by the system during run time, whereas user-defined exceptions are raised explicitly using RAISE statements.

PREDEFINED EXCEPTION:

Syntax:

BeginSequence_of_statements;ExceptionWhen <exception_name> thenSequence_of_statements;When others then /* the last exception in the exception handler*/Sequence_of_statements;End;

USER-DEFINED EXCEPTION:

User-defined exception should be declared only in the declarative part of the PL/SQL block.

Syntax:<exception_name> exception;The Syntax for a raise statement is as follows.

raise <exception_name>;

USER-DEFINED EXCEPTION

AIM:

To write a userdefined exception handling program using PL/SQL.

PROGRAM:

declareco number;exceeds_value exception;beginselect count(*) into co from employe;if co <5 theninsert into employe values (&empno,'&empname','&empst',&empsal);elseraise exceeds_value;end if;exceptionwhen exceeds_value thendbms_output.put_line('rows will not be inserted');end;


SQL> select * from employe;

EMPNO EMPNAME EMPST SALERY---------- ------------------------- ---------- ----------1 radha leader 200002 kavi manager 400003 malini analyst 140004 arthi admin 21000

SQL> /Enter value for empno: 5Enter value for empname: rajeswariEnter value for empst: softwareEnter value for empsal: 25000

old 7: insert into employe values (&empno,'&empname','&empst',&empsal);new 7: insert into employe values (5,'rajeswari','software',25000);


SQL> /Enter value for empno: 6Enter value for empname: madhavanEnter value for empst: leaderEnter value for empsal: 24000old 7: insert into employe values (&empno,'&empname','&empst',&empsal);new 7: insert into employe values (6,'madhavan','leader',24000);rows will not be inserted


SQL> select * from employe;

EMPNO EMPNAME EMPST SALERY---------- ------------------------- ---------- ----------------------

1 radha leader 200002 kavi manager 400003 malini analyst 140004 arthi admin 210005 rajeswari software 25000

RESULT:

Thus the PL/SQL block program using user-defined exception handler is executed and verified.

PREDEFINED EXCEPTION

AIM:

To write a predefined exception handling program using PL/SQL .

PROGRAM:

declareempid number(5);emname varchar(50);beginempid:=&empid;select empname into emname from employe where empno=empid;dbms_output.put_line('The employe name is:'||emname);exceptionwhen no_data_found thendbms_output.put_line('data not found');end;


Enter value for empid: 1old 5: empid:=&empid;new 5: empid:=1;The employe name is:radha


SQL> /Enter value for empid: 5old 5: empid:=&empid;new 5: empid:=5;data not foundPL/SQL procedure successfully completed.

RESULT:

Thus the PL/SQL block program using pre-defined exception handler is executed and verified.

CURSORS

AIM:

Create a PL/SQL block that deletes student’s records whose department is CSE and display the number of records deleted and the remaining number of records.

PROGRAM:

declarecursor c is select * from studdd cse;a c% rowtype;n number:=0;beginopen c;loopfetch c into a;exit when c% notfound;if a.dept='cse' thendelete from studdd where dept='cse';n:=n+1;end if;

end loop;dbms_output.put_line('Deleted record '||n);dbms_output.put_line('Remaining records '||(c% rowcount-n));close c;commit;end;


SQL> /Deleted record 2Remaining records 3


SQL> select * from studdd;

RLNO NAME DEPT---------- -------- ------1 abhilash cse2 kamala eee3 mareesh bme4 samson cse5 saranya it

7 rows selected.

SQL> select * from studdd;

RLNO NAME DEPT---------- -------- ------2 kamala eee3 mareesh bme5 saranya it

RESULT:

Thus the PL/SQL block program using cursor is executed and verified.

CURSORS

AIM:Create a PL/SQL block to determine the top 5 scores from the student table and to

insert these records into a new table.

PROGRAM:declarecursor c is select * from studddd order by total desc;a studddd % rowtype;n number:=0;beginopen c;loopfetch c into a;exit when c% notfound or c% rowcount>5;insert into detail1 values(a.rollno,a.name,a.dept,a.total);end loop;close c;commit;end;


SQL>/PL/SQL procedure successfully completed.

SQL> select * from studddd;

ROLLNO NAME DEPT TOTAL---------- -------- -----------------------------1 sv cse 2872 archu cse 2893 rekka cse 2904 aarthi cse 280

5 thamarai it 3006 tharani bme 1507 ranjani cse 2007 rows selected.SQL> select * from detail1;

ROLLNO NAME DEPT TOTAL---------- -------- ------ ----------5 thamarai it 3003 rekka cse 2902 archu cse 2891 sv cse 2874 aarthi cse 280

RESULT:


CURSORS

AIM:

Create a PL/SQL block to update the total and grade of the student table by comparing marks in the individual subject.

PROGRAM:

declarecursor c is select * from studdd1;a c% rowtype;tot number;g char;beginopen c;loopfetch c into a;exit when c% notfound;tot:=a.m1+a.m2+a.m3;if a.m1>50 and a.m2>50 and a.m3>50 theng:='H';elseg:='F';end if;update studdd2 set total=tot,grade=g where rollno=a.rollno;end loop;commit;close c;end;


SQL> /


SQL> select * from studdd1;

ROLLNO NAME M1 M2 M3--------------------------- -------- ---------- ---------- ---------- 1 svs 60 67 67 2 rekka 56 67 78 3 archu 78 45 30

SQL> alter table studdd1 add(total number,grade varchar(6));

Table altered.

SQL> desc studdd1;Name Null? Type----------------------------------------- -------- ----------------------------ROLLNO NUMBER(7)NAME VARCHAR2(8)M1 NUMBER(8)M2 NUMBER(8)M3 NUMBER(8)TOTAL NUMBERGRADE VARCHAR2(6)

SQL> select * from studdd1;

ROLLNO NAME M1 M2 M3 TOTAL GRADE---------- -------- ---------- ----------------------- ---------- ---------- ------ 1 svs 60 67 67 194 H 2 rekka 56 67 78 201 H 3 archu 78 45 30 153 F

RESULT:


A Function is a subprogram that computes a value. The syntax forcreating a function is given below.

Create or replace function < function_name > [ argument ]return datatype is

(local declaration)

Begin

(executable statements)

[Exception]

(exception handlers)

end;

AIM:

To write a PL/SQL block using function to check whether the quantity on hand and reorder level exceeded the maximum level.

PROGRAM:

create or replace function items(it varchar2)return number isargs number;qtyhand number;relevel number;maxlevel number;beginselect q_hand,re_level,max_level into qtyhand,relevel,maxlevel from itemfilewhere icode=it;if(qtyhand+relevel)>maxlevel thenargs:=maxlevel;return args;elseargs:=(qtyhand+relevel);

return args;end if;end;

SQL>/Function created.

SQL> declarea varchar2(5);b number;begina:='&a';b:=items(a);dbms_output.put_line('the values returned is ' || b);end;

SAMPLE INPUT OUTPUT:SQL>/Enter value for a: i201old 5: a:='&a';new 5: a:='i201';the values returned is 90PL/SQL procedure successfully completed.


ICODE IDESC P_CATGORY Q_HAND RE_LEVEL MAX_LEVEL I_RATE---------- ---------- --------------- ---------- ---------- ---------- ------------ ------- -----------

i201 nuts spares 40 50 300 12

i202 bolts spares 246 125 300 16.5

i204 holders spares 120 30 75 112

i205 covers accessories 246 15 50 400

i203 panels accessories 246 30 150 4000

i206 brackets spares 246 73 200 1326 rows selected.

RESULT:

Thus the PL/SQL block program using function is executed and verified

FUNCTION

AIM:

To write a PL/SQL block using function to find the grade of the student using cursors.

PROGRAM:

SQL> create or replace function grade(m1 number,m2 number,m3 number)return char isbeginif m1>50 and m2>50 and m3>50 thenreturn 'PASS';elsereturn 'FAIL';end if;end;

SQL> /Function created.

SQL> declarecursor c is select id,m1,m2,m3 from student;a c%rowtype;beginopen c;loopfetch c into a;exit when c%notfound;update student set grade=grade(a.m1,a.m2,a.m3) where id=a.id;end loop;commit;close c;end;


SQL>/PL/SQL procedure successfully completed.SQL> select * from student;

ID M1 M2 M3 TOTAL GRADE-----------------------------------------------------------------cs01 45 90 50 185cs02 90 70 60 220cs03 60 50 45 155cs04 70 80 70 220cs05 50 40 40 130

SQL> select * from student;

ID M1 M2 M3 TOTAL GRADE----------------------------------------------------------------cs01 45 90 50 185 FAILcs02 90 70 60 220 PASScs03 60 50 45 55 FAILcs04 70 80 70 220 PASScs05 50 40 40 130 FAIL

RESULT:

Thus the PL/SQL block program using function is executed and verified

PROCEDURE

A procedure is a subprogram that performs a specific action.

SYNTAX:

Create or replace procedure <proc_name>[parameter list]is<local declaration>Begin(executable statements)[exception] (exception handlers)End;

NOTE:

While declaring variables in the declarative part of the procedure body,we should not specify the width of the datatype.Eg.Procedure pro(name char(40)) isBegin

(set of statements);End;

In the above example,char(40) should be replaced by char.

SYNTAX TO EXECUTE:

Sql>exec <proc_name>(parameters);

AIM:

To write a PL/SQL block to check whether the quantity on hand is less than the reorder level then order more quantity of the item and update the table so that the changes are reflected using procedure(in mode).

PROGRAM:

create or replace procedure itemfiles(orders in number) isqtyhand number;re_level number;max_level number;beginselect qty_hand,relevel,maxlevel into qtyhand,re_level,max_level from itemfile where itemcode=orders;if qtyhand<re_level thenupdate itemfile set qty_hand=re_level+qtyhand where itemcode=orders;elsedbms_output.put_line('itemlevel ok');end if;exceptionwhen no_data_found thendbms_output.put_line('no data returned');end ;


SQL> /

Procedure created.

SQL> exec itemfiles(2);itemlevel ok



ITEMCODE ITEMCLOSE PDT_CTGY QTY_HAND RELEVEL MAXLEVEL ITEMRATE---------- ---------- ---------- ---------- ---------- ---------- ----------1 eraser stationary 100 50 200 32 chain fancy 150 98 200 253 tomato vegetable 460 54 157 54 pen stationary 35 50 80 6

SQL> exec itemfiles(4);PL/SQL procedure successfully completed.


ITEMCODE ITEMCLOSE PDT_CTGY QTY_HAND RELEVEL MAXLEVEL ITEMRATE---------- ---------- ---------- ---------- ---------- ---------- ----------1 eraser stationary 100 50 200 32 chain fancy 150 98 200 253 tomato vegetable 460 54 157 54 pen stationary 85 50 80 6

RESULT:

Thus the PL/SQL block program using procedure is executed and verified.

PROCEDURE

AIM:

To write a PL/SQL block to check whether the quantity on hand is less than the reorder level then order more quantity of the item and update the table so that the changes are reflected using procedure(in & out mode).

PROGRAM:

create or replace procedure itemfiles(orders number,b out number) isqtyhand number;re_level number;max_level number;beginselect qty_hand,relevel,maxlevel into qtyhand,re_level,max_level from itemfile where itemcode=orders;if qtyhand<re_level thenb:=re_level+qtyhand ;elsedbms_output.put_line('itemlevel ok');end if;exceptionwhen no_data_found thendbms_output.put_line('no data returned');end ;

SQL>/Procedure created.


ITEMCODE ITEMCLOSE PDT_CTGY QTY_HAND RELEVEL MAXLEVEL ITEMRATE---------- ---------- ---------- ---------- ---------- ---------- ----------- 1 eraser stationary 100 50 200 3 2 chain fancy 40 98 200 25 3 tomato vegetable 460 54 157 5 4 pen stationary 85 50 80 6declarea number:=&a;b number;beginitemfiles(a,b);dbms_output.put_line('The value of b is :'||to_char(b));end;SQL>/Enter value for a: 2old 2: a number:=&a;new 2: a number:=2;The value of b is :138


RESULT:

Thus the PL/SQL block program using procedure is executed and verified.

PACKAGES

Package Specification:

Syntax:Create package <package_name> is <declaration>Begin(executable statements)

End [package_name]

Package Body

Syntax:Create package body <package_name> is declaration>Begin(executable statements)End [body_name];

AIM:

To write a PL/SQL package that contains a procedure and functions to check whether the given number is a prime or not; odd or even and positive or negative.

PROGRAM:

Package Specification:create or replace package packs isprocedure display;function prime(n number)return varchar;function odd(n number)return varchar;function positive(n number)return varchar;end packs;

SAMPLE INPUT OUTPUT:SQL>/Package created.

Package Bodycreate or replace package body packs asprocedure display isa varchar(15);m number;beginm:=&m;a:=prime(m);if a='prime' thendbms_output.put_line(m || 'is a prime');elsedbms_output.put_line(m || 'is not prime');end if;a:=odd(m);if a='odd' thendbms_output.put_line(m || 'is odd');elsedbms_output.put_line(m || 'is even');end if;a:=positive(m);if a='positive' thendbms_output.put_line(m || 'is a positive number');elsedbms_output.put_line(m || 'is a negative number');end if;end display;function prime(n number)return varchar isi number;flag number;beginflag:=0;for i in 2..n/2loopif mod(n,i)=0 thenflag:=1;return 'Not prime';end if;

end loop;if flag=0 thenreturn 'prime';end if;end prime;function odd(n number)return varchar isbeginif mod(n,2)=0 thenreturn 'even';elsereturn 'odd';end if;end odd;function positive(n number)return varchar isbeginif n>0 thenreturn 'positive';elsereturn 'negative';end if;end positive;end packs;

SAMPLE INPUT OUTPUT:SQL> /Enter value for m: 7old 6: m:=&m;new 6: m:=7;

Package body created.

SQL> exec packs.display;7is a prime7is odd7is a positive number


RESULT:

Thus the PL/SQL block program using package is executed and verified.

dbms lab

Documents

table table

table alter table

sql truncate table empp

drop table command

basic table

specified table

old table

virtual table