Edutainment: RDBMS Program

ORACLE

It is a very large and multi-user database management system. Oracle is a relational

database management system developed by 'Oracle Corporation'.Oracle works to efficiently manage

its resource, a database of information, among the multiple clients requesting and sending data in

the network.It is an excellent database server choice for client/server computing. Oracle supports all

major operating systems for both clients and servers,including MSDOS, NetWare, UnixWare, OS/2

and most UNIX flavors.

RDBMS

RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and

for all modern database systems like

· MS SQL Server

· IBM DB2

· Oracle

· MySQL

· Microsoft Access.

SQL

SQL is Structured Query Language, which is a computer language for storing,

manipulating and retrieving data stored in relational database.SQL is the standard language

for Relation Database System. All relational database management systems like MySQL,

MS Access, Oracle, and SQL Server use SQL as standard database language.

MS SQL Server using T-SQL,

Oracle using PL/SQL,

MS Access version of SQL is called JET SQL (native format) etc.

NULL VALUE

A NULL value in a table is a value in a field that appears to be blank, which means a field

with a NULL value is a field with no value.

TABLE

The data in RDBMS is stored in database objects called tables. The table is a collection of related data entries and it consists of columns and rows.

FIELD

Every table is broken up into smaller entities called fields. The fields in the

CUSTOMERS table consist of ID, NAME, AGE, ADDRESS and SALARY.

RECORD OR ROW

A record, also called a row of data, is each individual entry that exists in a table.

COLUMN

A column is a vertical entity in a table that contains all information associated with a

specific field in a table.

SQL commands to interact with relational databases are

Ø CREATE

Ø SELECT

Ø INSERT

Ø UPDATE

Ø DELETE

Ø DROP

DDL - Data Definition Language

CREATE

Creates a new table, a view of a table, or other object in database.

ALTER

Modifies an existing database object, such as a table.

DROP

Deletes an entire table, a view of a table or other object in the database.

DML - Data Manipulation Language

SELECT

Retrieves certain records from one or more tables.

INSERT

Creates a record.

UPDATE

Modifies records.

DELETE

Deletes records.

DCL - Data Control Language

GRANT

Gives a privilege to user

REVOKE

Takes back privileges granted from user

EX.NO:1 BASIC SQL QUERIES

AIM

To implement the Basic SQL queries.

Create a Table

The SQL CREATE TABLE statement is used to create new SQL database.

DATE CONSTRAINTS

NOT NULL constraint

Ensures that a column cannot have NULL value.

DEFAULT Constraint

Provides a default value for a column when none is specified.

UNIQUE Constraint

Ensures that all values in a column are different.

PRIMARY Key

Uniquely identified each rows/records in a database table.

FOREIGN Key

Uniquely identified a rows/records in any another database table.

CHECK Constraint

The CHECK constraint ensures that all values in a column satisfy certain

conditions.

CREATE A TABLE

SYNTAX

CREATE TABLE table_name(column1 datatype,column2 datatype,column3

datatype,..... columnN datatype PRIMARY KEY( one or more columns ));

SQL > CREATE TABLE STUDENT (ROLL_NO VARCHAR2(10),NAME

VARCHAR2(20),CONTACT_NO NUMBER(10),MARK1 INT,

MARK2 INT,TOTAL);

Table created.

INSERT A DATA INTO TABLE

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

SYNTAX

            INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

SQL > INSERT INTO STUDENT VALUES('15us01','Bhuvana Devi',1234567890,90,89,0);

SQL > INSERT INTO STUDENT VALUES('15us02','Jeyalakshmi',9524786714,89,78,0);

SQL > INSERT INTO STUDENT VALUES('15us03','Vijayalakshmi',9047728900,67,98,0);

SQL > INSERT INTO STUDENT VALUES('15us15',Arun Kumar',9787232589,98,90,0);

SQL > INSERT INTO STUDENT VALUES('15us16','Bala',9866523400,89,78,0);

SELECT STATEMENT

SQL SELECT statement is used to fetch the data from a database table which returns

data in the form of result table

SYNTAX

            SELECT column1, column2, columnN FROM table_name;

SQL> SELECT *FROM STUDENT;

FROM

Fetch all the Fields.

ROLLNO NAME CONTACTNO MARK1 MARK2 TOTAL

-------------- --------------- ------------------ ------------- ------------ ----------

15us01 Bhuvana devi 1234567890 90 89 0

15us02 Jeyalakshmi 9524786714 89 78 0

15us03 Vijayalakshmi 9047728900 67 98 0

15us15 Arun Kumar 9787232589 98 90 0

15us16 Bala 9866523400 89 78 0

Update QUERY

The SQL UPDATE Query is used to modify the existing records in a table.

WHERE

WHERE clause with UPDATE query to update selected rows otherwise all the rows

would be affected.

SYNTAX

UPDATE table_name

SET column1 = value1, column2 = value2...., columnN = valueN

WHERE [condition];

SQL >UPDATE STUDENT SET NAME='Bhuvana Devi' WHERE Roll_no='15us01';

SQL >UPDATE STUDENT SET TOTAL=Mark1+Mark2;

ROLLNO NAME CONTACTNO MARK1 MARK2 TOTAL

-------------- --------------- ------------------ ------------ ----------- -----------

15us01 Bhuvana Devi 1234567890 90 89 179

15us02 Jeyalakshmi 9524786714 89 78 167

15us03 Vijayalakshmi 9047728900 67 98 165

15us15 Arun Kumar 9787232590 98 90 188

15us16 Bala 9866523400 89 78 167

ALTER TABLE

The SQL ALTER TABLE command is used to add, delete or modify

columns in an existing table.

SYNTAX

               ALTER TABLE table_name ADD column_name datatype;

Alter table(Add Column GRADE)

SQL> ALTER TABLE STUDENT ADD GRADE varchar2(5);

ROLLNO NAME CONTACTNO MARK1 MARK2 TOTAL GRADE

------------- ---------------- ------------------- ----------- ----------- ---------- ----------

15us01 Bhuvana Devi 1234567890 90 89 179

15us02 Jeyalakshmi 9524786714 89 78 167

15us15 Arun Kumar 9787232590 98 90 188

15us16 Bala 9866523400 89 78 167

15us03 Vijayalakshmi 9047728900 67 98 165

Update (GRADE)

SQL > UPDATE STUDENT SET GRADE='A+' WHERE TOTAL>180;

SQL > UPDATE STUDENT SET GRADE='A' WHERE TOTAL>170;

SQL > UPDATE STUDENT SET GRADE='B' WHERE TOTAL>160;

ROLLNO NAME CONTACTNO MARK1 MARK2 TOTAL GRADE

------------- ---------------- -------------------- ------------ ----------- ---------- ------------

15us01 Bhuvana Devi 1234567890 90 89 179 A+

15us02 Jeyalakshmi 9524786714 89 78 167 B

15us15 Arun Kumar 9787232590 98 90 188 A

15us16 Bala 9866523400 89 78 167 B

15us03 Vijayalakshmi 9047728900 67 98 165 B

Delete QUERY

The SQL DELETE Query is used to delete the existing records from a table.

WHERE clause with DELETE query to delete selected rows, otherwise all the records

would be deleted.

SYNTAX

                    DELETE FROM table_name

               WHERE [condition];

DELETE THE SPECIFIC DATA

SQL > DELETE FROM STUDENT WHERE NAME='Vijayalakshmi';

ROLLNO NAME CONTACTNO MARK1 MARK2 TOTAL GRADE

------------- ----------------- --------------------- ----------- ------------ ---------- -----------

15us01 Bhuvana Devi 1234567890 90 89 179 A+

15us02 Jeyalakshmi 9524786714 89 78 167 B

15us15 Arun Kumar 9787232590 98 90 188 A

15us16 Bala 9866523400 89 78 167 B

1 row deleted.

DROP TABLE

The SQL DROP TABLE statement is used to remove a table definition and

all data, indexes, triggers, constraints, and permission specifications for that table.

SYNTAX

            DROP TABLE table_name;

DROP FOR SPECIFIC COLUMN

SYNTAX

DROP TABLE TABLE_NAME DROP column column-name;

Drop column(contactno)

SQL > ALTER TABLE STUDENT DROP COLUMN CONTACT_NO;

ROLLNO NAME MARK1 MARK2 TOTAL GRADE

-------------- ------------------ ------------ ------------ ---------- -----------

15us01 Bhuvana Devi 90 89 179 A+

15us02 Jeyalakshmi 89 78 167 B

15us15 Arun Kumar 98 90 188 A

15us16 Bala 89 78 167 B

EX.NO:2 TO IMPLEMENT THE VARIOUS LEVEL OF

SELECT QUERIES

AIM

To implement the various level of SELECT queries.

SELECT QUERY

SQL SELECT statement is used to fetch the data from a database table which returns

data in the form of result table.

SYNTAX

            SELECT column1, column2, columnN FROM table_name;

SPECIFIC FIELD DISPLAY

            In which fetch the specific field to display.

Create a table

SQL> CREATE TABLE STUDENT_DET(ROLL_NO VARCHAR2(8) NOT NULL

,NAME VARCHAR2(20),GENDER VARCHAR2(2),

ELECTIVE_PAPER VARCHAR2(20), EPCODE VARCHAR2(10) NOT NULL,

MARK1 INT,MARK2 INT,AVG NUMBER(3),GRADE VARCHAR2(4),

PRIMARY KEY(ROLL_NO),UNIQUE(NAME));

Table is created.

INSERT A DATA INTO TABLE

SQL>INSERT INTO STUDENT_DET VALUES('&ROLL_NO','&NAME','&GENDER',

'&ELECTIVE_PAPER','&ELECTIVE_CODE',&MARK1,&MARK2,&AVG,

&GRADE);

Enter the Roll_no :15us01

Enter the Name :Bhuvana Devi

Enter the Gender :F

Enter the Department :CS

Enter the Elective_paper :Maths

Enter the Epcode :EMn01

Enter the Mark1 :90

Enter the Mark2 :85

Enter the Avg :0

Enter the Grade :A+

UPDATE A DATA

SQL >UPDATE STUDENT_DET SET AVG=(MARK1+MARK2)/2;

SQL >UPDATE STUDENT_DET SET GRADE='A' WHERE AVG>90;

SQL > SELECT *FROM STUDENT_DET;

ROLLNO NAME G DEP ELE_PAPER EPCODE MARK1 MARK2 AVG GRD

------------- ------------- ---- ------------ ------------------- ----------- ---------- ---------- ------- ------

15us01 Bhuvana Devi F Cs Maths EMn01 90 85 88 A+

15us02 Jeyalakshmi F Cs Maths EMn01 89 90 90 A

15us20 Arun M Cs Maths EMn01 87 67 77 B

15us21 Bala M Cs Maths EMn01 98 67 83 A+

(i) To display the specific field.

SQL > SELECT ROLL_NO,NAME,GENDER FROM STUDENT_DET;

ROLLNO NAME G

----------- ------------------- ------

15us01 Bhuvana Devi F

15us02 Jeyalakshmi F

15us20 Arun M

15us21 Bala M

15ma01 kala F

(ii) To display FEMALE candidates of Electivepaper

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,GENDER FROM

STUDENT_DET WHERE GENDER='F';

ROLLNO NAME ELECTIVEPAPER G

------------- ------------------- ------------------------- ------

15us01 Bhuvana Devi Maths F

15us02 Jeyalakshmi Maths F

15ma01 kala Cs F

15cy01 Abi Physics F

15cy02 Anjana Physics F

15py02 Mani Chemistry F

(iii) To display MALE candidate of Electivepaper.

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,GENDER FROM

STUDENT_DET WHERE GENDER='M';

ROLLNO NAME ELECTIVEPAPER G

------------ ---------------- -------------------------- -------

15us20 Arun Maths M

15us21 Bala Maths M

15ma03 Bharathi Cs M

15ma21 Kailash Cs M

(iv) To display COMPUTER SCIENCE Electivepaper students.

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE EPCODE='ECS01';

ROLLNO NAME ELECTIVEPAPER EPCODE

------------ --------------- -------------------------- ------------

15ma01 kala Cs ECs01

15ma03 Bharathi Cs ECs01

15ma21 Kailash Cs ECs01

(v) To display MATHEMATICS Electivepaper students.

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE EPCODE='EMN01';

ROLLNO NAME ELECTIVEPAPER EPCODE

------------- ---------------- -------------------------- -----------

15us01 Bhuvana Devi Maths EMn01

15us02 Jeyalakshmi Maths EMn01

15us20 Arun Maths EMn01

15us21 Bala Maths EMn01

(vi) To display PHYSICS Electivepaper students

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE EPCODE='EPY01';

ROLLNO NAME ELECTIVEPAPER EPCODE

------------- ------------- -------------------------- --------------

15cy01 Abi Physics Eph01

15cy02 Anjana Physics Eph01

15cy45 Anto Physics EPh01

(vii) To display CHEMISTRY Electivepaper students

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE EPCODE='ECY01';

ROLLNO NAME ELECTIVEPAPER EPCODE

---------- --------------- -------------------- -----------

15py02 Mani Chemistry ECy01

15py67 Vicky Chemistry Ecy01

15py68 Vinoth Chemistry Ecy01

(viii) To retrieve the data by using ELECTIVE PAPER(MATHS).

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE ELECTIVE_PAPER='MATHS';

ROLLNO NAME ELECTIVEPAPER

----------- ------------------ ------------------------

15us01 Bhuvana Devi Maths

15us02 Jeyalakshmi Maths

15us20 Arun Maths

15us21 Bala Maths

(ix) To retrieve the data by using ELECTIVE PAPER(PHYSICS).

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE ELECTIVE_PAPER='PHYSICS';

ROLLNO NAME ELECTIVEPAPER

----------- ------------------ ------------------------

15cy01 Abi Physics

15cy02 Anjana Physics

15cy45 Anto Physics

(x) To retrieve the data by using ELECTIVE PAPER(CHEMISTRY).

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE ELECTIVE_PAPER='CHEMISTRY';

ROLLNO NAME ELECTIVEPAPER

----------- --------------- --------------------------

15py02 Mani Chemistry

15py67 Vicky Chemistry

15py68 Vinoth Chemistry

(xi) To retrieve the data by using ELECTIVE PAPER(COMPUTER SCIENCE).

SQL > SELECT ROLL_NO,NAME,ELECTIVE_PAPER,EPCODE FROM

STUDENT_DET WHERE ELECTIVE_PAPER='CS';

ROLLNO NAME ELECTIVEPAPER

----------- --------------- --------------------------

15ma01 kala Cs

15ma03 Bharathi Cs

15ma21 Kailash Cs

(xi) To retrieve the specific data by using ROLLNO.

SQL > SELECT * FROM STUDENT_DET WHERE ROLL_NO='15US01';

ROLLNO NAME G DEP ELEEPAP EPCODE MARK1 MARK2 AVG GRADE

----------- ----------------- --- -------- ------------- ------------ ----------- ------------ ------- -----------

15us01 Bhuvana Devi F Cs Maths EMn01 90 85 88 A+

INSERT A DATA WITH SAME NAME

SQL > INSERT INTO STUDENT_DET VALUES('15US17','ARUN','M',

'PHYSICS','CHEMISTRY','ECY01',89,90,78,'B');

SQL > INSERT INTO STUDENT_DET VALUES('15US18','ARUN','M','CHEMISTRY',

'PHYSICS','EPH01',78,89,67,'B+');

SQL > INSERT INTO STUDENT_DET VALUES('15US19','ARUN','M','MATHS',

'CS','ECS01',89,78,78,'B');

(xii) To retrieve the 3 RECORDS with NAME is SAME.

SQL >SELECT * FROM STUDENT_DET WHERE NAME='ARUN';

ROLLNO NAME G DEP ELECPA EPCODE MARK1 MARK2 AVG GRADE

------------- ----------- --- -------------- ------------ ------------- ------------ ------------ ------- ---------

15us20 Arun M Cs Maths EMn01 87 67 77 B

15us17 Arun M Physics Chemistry ECy01 89 90 78 B

15us18 Arun M Chemistry Physics EPh01 78 89 67 B+

15us19 Arun M Maths Cs ECs01 89 78 78 B

EX.NO:3 TO IMPLEMENT THE SQL BUILT-IN FUNCTION

AIM

To implement the SQL BUILT-IN FUNCTION in SQL.

GROUP BY

The SQL GROUP BY clause is used in collaboration with the SELECT statement to

arrange identical data into groups

ORDER BY

The GROUP BY clause follows the WHERE clause in a SELECT statement and precedes

the ORDER BY clause.

SYNTAX

               SELECT column1, column2

FROM table_name

WHERE [ conditions ]

GROUP BY column1, column2

ORDER BY column1, column2

SQL FUNCTION

SQL has many built-in functions for performing calculations on data.

1.Single value function

2.Group value function

3.List value function

SINGLE VALUE FUNCTION

ABS(X)

The ABS() function returns the absolute value of X.

SYNTAX

SELECT ABS(Value);

CEILING(X)

These functions return the smallest integer value that is not smaller than X.

SYNTAX

SELECT CEILING(Value or X);

FLOOR(X)

This function returns the largest integer value that is not greater than X.

SYNTAX

SELECT FLOOR(Value or X);

SIGN(X)

This function returns the sign of X (negative, zero, or positive) as -1, 0, or 1.

SYNTAX

SELECT SIGN(Value or X);

SIN(X)

This function returns the sine of X.

SYNTAX

SELECT SIN(Value or X);

SQRT(X)

This function returns the non-negative square root of X.

SYNTAX

SELECT SQRT(Value or X);

GROUP VALUE FUNCTION

COUNT(*)

The SQL COUNT aggregate function is used to count the number of rows in a

database table.

SYNTAX

SELECT COUNT(*);

SELECT COUNT(Column_name);

MAXIMUM(Column_name)

The SQL MAX aggregate function allows us to select the highest (maximum)

value for a certain column.

SYNTAX

SELECT MAXIMUM(Column_name);

MINIMUM()

The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column.

SYNTAX

SELECT MINIMIUM(Column_name);

AVERAGE(Column_name)

The SQL AVG aggregate function selects the average value for certain table

column.

SYNTAX

SELECT AVG(Column_name);

SUM(Column_name)

The SQL SUM aggregate function allows selecting the total for a numeric

column.

SYNTAX

SELECT SUM(Column_name);

CONCAT(col1,col2)

SQL CONCAT function is used to concatenate two strings to form a single

string.

SYNTAX

SELECT CONCAT(column_name1,column_name2);

LENGTH(X)

Returns the length of the string.

SYNTAX

SELECT LENGTH(Column_name);

TRUNCATE(X,D)

This function is used to return the value of X truncated to D number of

decimal places.

(i)If D is 0, then the decimal point is removed.

(ii)If D is negative, then D number of values in the integer part of the value is

truncated.

SYNTAX

SELECT TRUNC(Value,No.of decimals);

LOWER(str)

Returns the string str with all characters changed to lowercase according to the current

character set mapping.

SYNTAX

SELECT LOWER(STR);

UPPER (str)

Returns the string str with all characters changed to lowercase according to the current

character set mapping.

SYNTAX

SELECT UPPER(STR);

ROUND(X,D)

This function returns X rounded to the nearest integer. If a second argument,

D, is supplied, then the function returns X rounded to D decimal places. D must be positive or

all digits to the right of the decimal point will be removed.

SYNTAX

SELECT ROUND(X or Value,D);

LIST VALUE FUNCTION

GREATEST()

The GREATEST() function returns the greatest value in the set of input

parameters (col1,col2).

SYNTAX

SELECT GREATEST(COL1,COL2);

LEAST()

The LEAST() function is the opposite of the GREATEST() function. Its purpose

is to return the least-valued item from the value list (col1,col2)

SYNTAX

SELECT LEAST(COL1,COL2);

CREATE A TABLE

SQL > CREATE TABLE EM_DET(EMP_ID INT,EMP_NAME VARCHAR2(20) NOT

NULL,DEPARTMENT VARCHAR2(10),SALARY INT,

PF_AMOUNT INT NULL,

LIC_AMOUNT INT NULL,LOAN_AMOUNT INT NULL,

TOTAL INT,UNIQUE(EMP_NAME),PRIMARY KEY(EMP_ID));

Table is created.

INSERT A DATA INTO THE TABLE

SQL > INSERT INTO EMP_DET VALUES(&EMP_ID,'&EMP_NAME',

'&DEPARTMENT',&SALARY,&PF_AMOUNT,&LIC_AMOUNT,

&LOAN_AMOUNT,&TOTAL);

UPDATE A DATA

SQL > UPDATE EMP_DET SET TOTAL=SALARY-PF-LOAN-LIC;

Enter the Emp_id :1006

Enter the Emp_name :Dhivahar

Enter the Department :Software

Enter the Salary :30000

Enter the PF_amount :800

Enter the LIC_amount :3000

Enter the Loan_amount :6000

Enter the total :20200

SQL >SELECT *FROM EMP_DET;

EMP_ID EMP_NAME DEP SALARY PF LIC LOAN TOTAL

------------- -------------------- ---------------- ------------ ---------- ---------- ------------ ----------

1001 Bhuvana Software 10000 200 1000 5000 3800

1002 Jeya Electronic 10500 100 2000 3000 5400

1003 Kalai Electrical 20000 500 8000 5000 6500

1004 Arun Software 25000 3000 5000 9000 8000

1005 Rajesh Civil 30000 4000 5000 9000 12000

1006 Dhivahar Mechanical 30500 500 8000 10000 12000

1006 Dhivahar Software 30000 800 3000 6000 20200

COUNT

SQL > SELECT COUNT(*) FROM EMP_DET;

COUNT(*)

---------------

COUNT THE SPECIFIC PERSON

SQL > SELECT EMP_NAME COUNT(*) FROM EMP_DET WHERE

EMPO_NAME='DHIVAHAR';

COUNT(*)

--------------

MAXIMUM

SQL > SELECT MAX(SALARY) FROM EMP_DET;

MAX(SALARY)

----------------------

30500

MAXIMUM WITH GROUP

SQL > SELECT EMP_NAME,MAX(SALARY) FROM EMP_DET GROUP BY

EMP_NAME;

EMP_NAME MAX(SALARY)

-------------------- ---------------------

Arun 25000

Bhuvana 10000

Dhivahar 30500

Jeya 10500

Kalai 20000

Rajesh 30000

MINIMUM

SQL > SELECT MIN(SALARY) FROM EMP_DET;

MIN(SALARY)

--------------------

10000

MINIMUM WITH GROUP

SELECT EMP_NAME,MIN(SALARY) FROM EMP_DET GROUP BY EMP_NAME;

EMP_NAME MIN(SALARY)

------------------ -------------------

Arun 25000

Bhuvana 10000

Dhivahar 30000

Jeya 10500

Kalai 20000

Rajesh 30000

MAXIMUM AND MINIMUM

SQL > SELECT MAX(SALARY) MAX,MIN(SALARY) MIN FROM EMP_DET;

MAX MIN

---------- ----------

30500 10000

AVERAGE

SQL > SELECT AVG(SALARY) FROM EMP_DET;

AVG(TOTAL)

-------------------

9700

AVERAGE WITH GROUP

SQL > SELECT EMP_NAME,AVG(SALARY) FROM EMP_DET GROUP BY

EMP_NAME;

EMP_NAME AVG(SALARY)

-------------------- -----------------------

Arun 25000

Bhuvana 10000

Dhivahar 30250

Jeya 10500

Kalai 20000

Rajesh 30000

SUM

SQL > SELECT SUM(SALARY) FROM EMP_DET;

SUM(TOTAL)

-------------------

67900

SUM WITH GROUP

SQL > SELECT EMP_NAME,SUM(SALARY) FROM EMP_DET GROUP BY

EMP_NAME;

EMP_NAME SUM(TOTAL)

-------------------- -------------------

Arun 8000

Bhuvana 3800

Dhivahar 32200

Jeya 5400

Kalai 6500

Rajesh 12000

RANDOM

SQL > SELECT * FROM EMP_DET ORDER BY EMP_NAME;

EMP_ID EMP_NAME DEP SALARY PF LIC LOAN TOTAL

------------- -------------------- --------------- -------------- -------- ---------- ------------- --------------

1004 Arun Software 25000 3000 5000 9000 8000

1001 Bhuvana Software 10000 200 1000 5000 3800

1006 Dhivahar Mechanical 30500 500 8000 10000 12000

1007 Dhivahar Civil 30000 800 3000 6000 20200

1002 Jeya Electronic 10500 100 2000 3000 5400

1003 Kalai Electrical 20000 500 8000 5000 6500

1005 Rajesh Civil 30000 4000 5000 9000 12000

CONCAT

SQL > SELECT CONCAT(EMP_ID,EMP_NAME) FROM EMP_DET;

CONCAT(EMP_ID,EMP_NAME)

--------------------------------------------

1001Bhuvana

1002Jeya

1003Kalai

1004Arun

1005Rajesh

1006Dhivahar

1007Dhivahar

LENGTH

SQL > SELECT LENGTH(EMP_NAME) FROM EMP_DET;

LENGTH(EMP_NAME)

-------------------------------

SIN

SQL > SELECT SIN(99.55) FROM EMP_DET;

SIN(99.55)

---------------

-.83103446

ROUND

SQL > SELECT ROUND(99.55,0) FROM EMP_DET;

ROUND(99.55)

--------------------

100

TRUNCATE

SQL > SELECT TRUNC(99.55,1) FROM EMP_DET;

TRUNC(99.55,1)

----------------------

99.5

SQRT

SQL > SELECT SQRT(SALARY) FROM EMP_DET;

SQRT(SALARY)

-----------------------

100

FLOOR

SQL > SELECT FLOOR(99.55) FROM EMP_DET;

FLOOR(99.55)

--------------------

ABS(99.55)

SQL > SELECT ABS(99.55) FROM EMP_DET;

ABS(99.55)

---------------

99.55

ABS(-99.55)

SQL > SELECT ABS(-99.55) FROM EMP_DET;

ABS(-99.55)

----------------

99.55

UPPERCASE

SQL > SELECT UPPER(EMP_NAME) FROM EMP_DET;

UPPER(EMP_NAME)

-----------------------------

BHUVANA

JEYA

KALAI

ARUN

INITCAP

SQL > SELECT INITCAP(EMP_NAME) FROM EMP_DET;

INITCAP(EMP_NAME)

-------------------------------

Rajesh

Dhivahar

LOWERCASE

SQL > SELECT LOWER(EMP_NAME) FROM EMP_DET;

LOWER(EMP_NAME)

------------------------------

bhuvana

jeya

kalai

arun

rajesh

dhivahar

USERID

SQL > SELECT USERID FROM EMP_DET;

UID

------

GREATEST

SQL > SELECT GREATEST(SALARY,PF) FROM EMP_DET;

GREATEST

-----------------

10000

10500

LEAST

SQL > SELECT LEAST(PF,LIC) FROM EMP_DET;

LEAST
-----------

200

100

ASCENDING

SQL > SELECT EMP_NAME FROM EMP_DET ORDER BY EMP_NAME ASC;

EMPNAME

----------------

Arun

Bhuvana

Dhivahar

DESCENDING

SQL > SELECT EMP_NAME FROM EMP_DET ORDER BY EMP_NAME DESC;

EMP_NAME

------------------

Dhivahar

Bhuvana

Arun

CEILING

SQL > SELECT CEIL(99.55) FROM EMP_DET;

CEIL(99.55)

----------------

100

SIGN

SQL > SELECT SIGN(99.55) FROM EMP_DET;

SIGN(99.55)

-----------------

EX.NO:4 SQL QUERY USING OPERATORS

Aim

Write a SQL query to implement the wildcard character using Like

operator,Relational,Arithmetic and Logical operator.

AND Operator

The AND operator displays a record if both the first condition AND the second

Condition are true.

AND operator is used to set multiple conditions with Where clause.

OR Operator

The OR operator displays a record if either the first condition OR the second

condition is true.

BETWEEN..AND

The operator BETWEEN and AND, are used to compare data for a range of values.

LIKE Operator

The SQL LIKE condition allows to use wildcards to perform pattern matching in a

query.

The LIKE condition is used in the WHERE clause of a SELECT, INSERT,

UPDATE, or DELETE statement.

(i)Using '%' in Wildcard in LIKE condition

'%' A substitute for zero or more characters.

(ii)Using '_' in Wildcard in Like condition

'_' A substitute for a single character.

!= Operator

Checks if the values of two operands are equal or not, if values are not equal then

condition becomes true.

NOT BETWEEN Operator

NOT BETWEEN AND operator checks whether a value is not present between a

starting and a closing expression.

Create a table

SQL > CREATE TABLE EMP(ID INTEGER,NAME VARCHAR2(15),

CITY VARCHAR2(10),COMMISSION_CHARGE VARCHAR2(5),

PRIMARY KEY(ID));

Table is created.

INSERT A DATA INTO TABLE

SQL > INSERT INTO EMP

VALUES(&ID,'&NAME','&CITY',COMMISSION_CHARGE);

Enter the ID :101

Enter the Name :Bhuvana Devi

Enter the City :Chennai

Enter the Commission_charge :1102.5

SQL > SELECT *FROM EMP;

ID NAME CITY COMMISSION_CHARGE

---- ---------------------- --------------- -------------------------------------

101 Bhuvana Devi Chennai 1102.5

102 jemis Coimbatore 8000

103 Ajis India 4500

104 Kayal Bangalore 1080.5

106 Naani Kerala 4300

107 Mani Osure 2000

108 Nalli Chennai 900

(i) Write a SQL query to find those salesman with all information who come from

the city either Chennai or India.

SQL > SELECT *FROM EMP WHERE CITY='CHENNAI' AND CITY='INDIA';

ID NAME CITY COMMISSION_CHARGE

-------- -------------------- --------------- -----------------------------------

101 Bhuvana Devi Chennai 1102.5

103 Ajis India 4500

105 Sanjana India 9500

108 Nalli Chennai 9000

(ii) Write a SQL statement to find all customers with all information whose names are

starting with the character ’S’.

SQL > SELECT *FROM EMP WHERE NAME LIKE NAME='%S';

ID NAME CITY COMMISSION_CHARGE

----- ----------- --------- ----------------------------------

105 Sanjana India 9500

(iii) Write a SQL statement to find all customers with all information whose names are

ending with the character ’A’.

SQL>SELECT *FROM EMP WHERE NAME LIKE NAME='A%';

ID NAME CITY COMMISSION_CHARGE

---- ----------- --------- ------------------------------------

105 Sanjana India 950

106 Nazima kerala 4300

(iv) Write a SQL query to filter the data with all information which commission

amount is with the range 1000 and 10000;

SQL> SELECT *FROM EMP WHERE COMMISSION_CHARGE BETWEEN

1000 AND 5000;

ID NAME CITY COMMISSION_CHARGE

----- -------------------- --------------- -----------------------------------

101 Bhuvana Devi Chennai 1102.5

103 Ajis India 4500

104 Kayal Bangalore 1080.5

106 Nazima Kerala 4300

107 Mani Osure 2000

(V) Write a SQL query to filter with all information which commission amount in

with the range 1000 and 10000 except those commission amount value is 1102.50 and

1080.50.

SQL > SELECT *FROM EMP WHERE COMMISSION_CHARGE BETWEEN

1000 AND 10,000 AND COMMISSION_CHARGE !=1102.50

AND COMMISSION_CHARGE !=1080.50;

ID NAME CITY COMMISSION_CHARGE

----- ------------- --------------- ----------------------------------

102 Jemis Coimbatore 8000

103 Ajis India 4500

105 Sanjana India 9500

106 Nazima Kerala 4300

108 Nalli Chennai 9000

(vi) Write a SQL statement to find those customer name with all other information and

the name started with other than any letter within ‘A’ and ‘L’.

SQL > SELECT NAME FROM EMP WHERE NAME NOT BETWEEN

'A' AND 'L';

NAME

---------------

Jemis

Sanjana

Nazima

Mani

Nalli

(vii) Write a SQL statement to find all information the name containing the first

character is ‘N’ and the fourth character is ‘L’ and the remaining character is

anything.

SQL>SELECT NAME FROM EMP WHERE NAME 'N_ _ L%';

NAME

---------------

Nalli

(viii)Write a SQL statement to find the escape charater in customer name.

SQL>SELECT *FROM EMP WHERE NAME LIKE '% %';

ID NAME CITY COMMISSION_CHARGE

----- ----------------- ---------- --------------------------------

101 Bhuvana Devi Chennai 1102.5

EX.NO:5 A)TO CREATE A INTERCOLLEGIATE REPORT

USING JOINS IN SQL

AIM

Program to create a intercollegiate report using JOIN in SQL.

SQL INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match

between the columns in both tables.

SYNTAX

SELECT TAB1_name.COLUMN,TAB2_name.COLUMN FROM TAB1_name

INNER JOIN TAB2_name ON TAB1_name.ID=TAB2_name.ID;

SQL LEFT JOIN

The LEFT JOIN keyword returns all rows from the left table (table1), with the matching

rows in the right table (table2). The result is NULL in the right side when there is no match.

SYNTAX

SELECT TAB1_name.COLUMN,TAB2_name.COLUMN FROM TAB1_name

LEFT JOIN TAB2_name ON TAB1_name.ID=TAB2_name.ID;

SQL RIGHT JOIN Keyword

The RIGHT JOIN keyword returns all rows from the right table (table2), with the

matching rows in the left table (table1). The result is NULL in the left side when there is

no match.

SYNTAX

SELECT TAB1_name.COLUMN,TAB2_name.COLUMN FROM TAB1_name

RIGHT JOIN TAB2_name ON TAB1_name.ID=TAB2_name.ID;

CRAETE A TABLE

SQL > CREATE TABLE TABLE_NAME(SNO INT,DATES DATE,

SNAME VARCHAR2(20),CNAME VARCHAR2(8),QUIZ VARCHAR2(3) NULL,

SOFT_MARK VARCHAR2(3) NULL,PAPER VARCHAR2(3) NULL,

POSTAL VARCHAR2(3) NULL,LOTNAME VARCHAR2(10) NOT NULL,

PRIMARY KEY(SNO));

Table is created.

INSERT A DATA INTO TABLE

SQL > INSERT INTO TABLE_NAME VALUES(&SNO,TO_DATE

('26 JAN 2017','MMDDYYYY'),'&SNAME','&CNAME','&QUIZ',

'&SOFT_MARK','&PAPER','&POSTAL','&LOTNAME);

Enter the sno :1

Enter the snam e :Bhuvana

Enter the cname :ANJAC

Enter the Quiz :Yes

Enter the soft_mark :Yes

Enter the paper :No

Enter the postal :No

Enter the Lotname :Eclairs

SQL > SELECT *FROM MAINTABLE;

SNO DATES SNAME CNAME QUIZ SOFT PAPER POST LOTNAME

------------- --------------- ------------- ------------------ --------- -------- --------- -------- -------------

1 26-JAN-17 Bhuvana ANJAC Yes Yes No No Eclairs

2 26-JAN-17 Jemi ANJAC Yes No No No Eclairs

3 26-JAN-17 Kalai ANJAC No Yes No No Eclairs

4 26-JAN-17 Najima Kaleeswari Yes No Yes Yes Milkybar

5 26-JAN-17 Muthu Kaleeswari No No No Yes Milkybar

6 26-JAN-17 Mari Kaleeswari Yes Yes No No Milkybar

7 26-JAN-17 Lakshmi SFR No No Yes No Dairymilk

8 26-JAN-17 Latha SFR Yes No Yes No Dairymilk

9 26-JAN-17 Arul SFR No No Yes No Dairymilk

10 26-JAN-17 Jothi Kamarajar Yes Yes No No Safari

11 26-JAN-17 Arun Kamarajar No Yes No No Safari

12 26-JAN-17 Vicky Kamarajar No No No Yes Safari

13 26-JAN-17 Ashok PSR No Yes Yes Yes Lollypop

14 26-JAN-17 Bharathi PSR Yes No No No Lollypop

15 26-JAN-17 Anjana PSR No Yes No Yes Lollypop

16 26-JAN-17 Sanjana MEPCO Yes Yes No No Munch

17 26-JAN-17 Yogesh MEPCO No Yes Yes No Munch

18 26-JAN-17 Kumar MEPCO Yes Yes No Yes Munch

(i)To get eventwise report on

(i)QUIZ

SQL > INSERT INTO MAINTALE(SNO,SNAME,LOTNAME,QUIZ) SELECT

SNO,SNAME,LOTNAME,QUIZ FROM MAINTABLE

WHERE QUIZ='YES';

SNO SNAME LOTNAME QUIZ

------------- --------------- --------------- ------------------

1 26-JAN-17 Bhuvana Yes

2 26-JAN-17 Jemi Yes

4 26-JAN-17 Najima Yes

6 26-JAN-17 Mari Yes

8 26-JAN-17 Latha Yes

10 26-JAN-17 Jothi Yes

14 26-JAN-17 Bharathi Yes

16 26-JAN-17 Sanjana Yes

18 26-JAN-17 Kumar Yes

(ii) SOFTWARE_MARKETING

SQL > INSERT INTO MAINTALE(SNO,SNAME,LOTNAME,SOFT) SELECT

SNO,SNAME,LOTNAME,SOFT FROM MAINTABLE

WHERE SOFT='YES';

SNO SNAME LOTNAME SOFT

------------ --------------- ---------------- ----------

1 26-JAN-17 Bhuvana Yes

3 26-JAN-17 Kalai Yes

6 26-JAN-17 Mari Yes

13 26-JAN-17 Ashok Yes

10 26-JAN-17 Jothi Yes

11 26-JAN-17 Arun Yes

15 26-JAN-17 Anjana Yes

16 26-JAN-17 Sanjana Yes

17 26-JAN-17 Yogesh Yes

18 26-JAN-17 Kumar Yes

(iii) PAPER PARESENTATION

SQL > INSERT INTO MAINTALE(SNO,SNAME,LOTNAME,PAPER) SELECT

SNO,SNAME,LOTNAME,PAPER FROM MAINTABLE

WHERE PAPER='YES';

SNO SNAME LOTNAME PAPER

---------- --------------- --------------- -------------

4 6-JAN-17 Najima Yes

7 26-JAN-17 Lakshmi Yes

8 26-JAN-17 Latha Yes

9 26-JAN-17 Arul Yes

13 26-JAN-17 Ashok Yes

17 26-JAN-17 Yogesh Yes

(iv) POSTAL PRESENTATION

SQL > INSERT INTO MAINTALE(SNO,SNAME,LOTNAME,POSTAL) SELECT

SNO,SNAME,LOTNAME,POSTAL FROM MAINTABLE

WHERE POSTAL='YES';

SNO SNAME LOTNAME POSTAL

---------- --------------- ---------------- --------------

4 26-JAN-17 Najima Yes

5 26-JAN-17 Muthu Yes

12 26-JAN-17 Vicky Yes

13 26-JAN-17 Ashok Yes

15 26-JAN-17 Anjana Yes

18 26-JAN-17 Kumar Yes

INNER JOIN USING SUBQUERY(EVENT TABLE)

(i)QUIZ

SQL > SELECT INTCLG.SNO,INTCLG.CNAME,INTCLG.SNAME,

INTCLG.LOTNAME,EV_Q.QUIZ FROM INTCLG INNER

JOIN EV_Q ON INTCLG.SNO=EV_Q.SNO;

SNO CNAME SNAME LOTNAME QUIZ

------- -------------- --------------- --------------------- ----------

1 26-JAN-17 Bhuvana Eclairs Yes

2 26-JAN-17 Jemi Eclairs Yes

4 26-JAN-17 Najima Milkybar Yes

6 26-JAN-17 Mari Milkybar Yes

8 26-JAN-17 Latha Dairymilk Yes

10 26-JAN-17 Jothi Safari Yes

14 26-JAN-17 Bharathi Lollypop Yes

16 26-JAN-17 Sanjana Munch Yes

18 26-JAN-17 Kumar Munch Yes

(ii)PAPER PRESENTATIOIN

SQL > SELECT INTCLG.SNO,INTCLG.CNAME,INTCLG.SNAME,

INTCLG.LOTNAME,EV_PAPER.PAPER FROM INTCLG

INNER JOIN EV_PAPER ON INTCLG.SNO=EV_PAPER.SNO;

SNO CNAME SNAME LOTNAME PAPER

------- -------------- --------------- -------------------- -----------

4 Kaleeswari Najima Milkybar Yes

7 SFR Lakshmi Dairymilk Yes

8 SFR Latha Dairymilk Yes

9 SFR Arul Dairymilk Yes

13 PSR Ashok Lollypop Yes

17 MEPCO Yogesh Munch Yes

(iii)SOFTWARE_MARKETING

SQL > SELECT INTCLG.SNO,INTCLG.CNAME,INTCLG.LOTNAME,

EV_SOFT.SOFT FROM INTCLG INNER JOIN EV_SOFT

ON INTCLG.SNO=EV_SOFT.SNO;

SNO SNAME CNAME LOTNAME SOFT

------- ------------- -------------------- ---------------- ----------

1 Bhuvana ANJAC Eclairs Yes

3 Kalai ANJAC Eclairs Yes

6 Mari Kaleeswari Milkybar Yes

10 Jothi Kamarajar Safari Yes

11 Arun Kamarajar Safari Yes

13 Ashok PSR Lollypop Yes

15 Anjana PSR Lollypop Yes

16 Sanjana MEPCO Munch Yes

17 Yogesh MEPCO Munch Yes

18 Kumar MEPCO Munch Yes

(iv)POSTAL PRESENTATION

SQL > SELECT INTCLG.SNO,INTCLG.CNAME,INTCLG.LOTNAME,

EV_POS.POSTAL FROM INTCLG INNER JOIN EV_POS

ON INTCLG.SNO=EV_POS.SNO;

SNO SNAME CNAME LOTNAME POSTAL

--------- --------------- -------------------- ------------------ -------------

4 Najima Kaleeswari Milkybar Yes

5 Muthu Kaleeswari Milkybar Yes

12 Vicky Kamarajar Safari Yes

13 Ashok PSR Lollypop Yes

15 Anjana PSR Lollypop Yes

18 Kumar MEPCO Munch Yes

To find the Total Number of Colleges using function

COUNT THE COLLEGE NAME

SQL > SELECT CNAME,COUNT(CNAME) FROM INTCLG GROUP BY CNAME;

CNAME COUNT(CNAME)

------------ ---------------------------

ANJAC 5

Kaleeswari 5

Kamarajar 5

MEPCO 5

PSR 5

SFR 5

TO FIND THE TOTAL NUMBER OF PARTICIPANTS USING FUNCTION.

SQL > SELECT COUNT(SNAME) FROM INTCLG;

COUNT(SNAME)

------------------------

TO GET A COLLEGEWISE REPORT

(i)SFR

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='SFR';

SNO DATES SNAME CNAME LOTNAME QUIZ SOFT PAPER POST

-------- ---------------- ------------ ------------- --------------- ------- -------- --------- --------

7 26-JAN-17 Lakshmi SFR Dairymilk No No Yes No

8 26-JAN-17 Latha SFR Dairymilk Yes No Yes No

9 26-JAN-17 Arul SFR Dairymilk No No Yes No

(ii)ANJAC

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='ANJAC';

SNO DATES SNAME CNAME LOTNAME QUIZ SOFT PAPER POST

-------- ---------------- ------------ ------------- --------------- ------- -------- --------- --------

1 26-JAN-17 Bhuvana ANJAC Eclairs Yes Yes No No

2 26-JAN-17 Jemi ANJAC Eclairs Yes No No No

3 26-JAN-17 Kalai ANJAC Eclairs No Yes No No

(iii)KALEESWARI

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='Kaleeswari';

SNO DATES SNAME CNAME QUIZ SOFT PAPER POST LOTNAME

-------- ---------------- ------------ --------------- ---------- -------- ---------- ------- ----------------

4 26-JAN-17 Najima Kaleeswari Yes No Yes Yes Milkybar

5 26-JAN-17 Muthu Kaleeswari No No No Yes Milkybar

6 26-JAN-17 Mari Kaleeswari Yes Yes No No Milkybar

(iv)PSR

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='PSR';

SNO DATES SNAME CNAME QUIZ SOFT PAPER POST LOTNAME

-------- ---------------- ------------ --------------- ---------- -------- ---------- ------- ----------------

13 26-JAN-17 Ashok PSR No Yes Yes Yes Lollypop

14 26-JAN-17 Bharathi PSR Yes No No No Lollypop

15 26-JAN-17 Anjana PSR No Yes No Yes Lollypop

(v)MEPCO

SQL > SELECT SNO,SNAME,CNAME,LOTNAME,QUIZ,SOFT_MARK,PAPER,

POSTAL FROM INTCLG WHERE CNAME='MEPCO';

SNO DATES SNAME CNAME QUIZ SOFT PAPER POST LOTNAME

-------- ---------------- ------------ --------------- ---------- -------- ---------- ------- ----------------

13 26-JAN-17 Ashok PSR No Yes Yes Yes Lollypop

14 26-JAN-17 Bharathi PSR Yes No No No Lollypop

15 26-JAN-17 Anjana PSR No Yes No Yes Lollypop

COUNT THE EVENTWISE

(i)QUIZ

SQL > SELECT COUNT(QUIZ) FROM EV_Q;

COUNT(QUIZ)

---------------------

(ii)SOFTWARE_MARKETING

SQL > SELECT COUNT(SOFT) FROM EV_SOFT;

COUNT(SOFT)

---------------------

(iii)PAPER PRESENTATION

SQL > SELECT COUNT(PAPER) FROM EV_PAPER;

COUNT(PAPER)

-----------------------

(iv)POSTAL PRESENTATION

SQL > SELECT COUNT(POSTAL) FROM EV_POS;

COUNT(POSTAL)

-------------------------

UPDATE THE ENTRY_DATE

SQL > UPDATE INTCLG SET ENTRY_DATE=TO_DATE('23 JAN

2017','DDMMYYYY') WHERE CNAME='ANJAC';

SNO DATES SNAME CNAME QUIZ SOFT PAPER POST LOT ETRDTE

----- --------------- ------------- --------------- --------- -------- --------- ------- ------------ -------------

1 26-JAN-17 Bhuvana ANJAC Yes Yes No No Eclairs 23-JAN-17

2 26-JAN-17 Jemi ANJAC Yes No No No Eclairs 23-JAN-17

3 26-JAN-17 Kalai ANJAC No Yes No No Eclairs 23-JAN-17

4 26-JAN-17 Najima Kaleeswari Yes No Yes Yes Milkybar 27-JAN-17

5 26-JAN-17 Muthu Kaleeswari No No No Yes Milkybar 27-JAN-17

6 26-JAN-17 Mari Kaleeswari Yes Yes No No Milkybar 27-JAN-17

7 26-JAN-17 Lakshmi SFR No No Yes No Dairymilk 12-JAN-17

8 26-JAN-17 Latha SFR Yes No Yes No Dairymilk 12-JAN-17

9 26-JAN-17 Arul SFR No No Yes No Dairymilk 12-JAN-17

10 26-JAN-17 Jothi Kamarajar Yes Yes No No Safari 18-JAN-17

11 26-JAN-17 Arun Kamarajar No Yes No No Safari 18-JAN-17

12 26-JAN-17 Vicky Kamarajar No No No Yes Safari 18-JAN-17

13 26-JAN-17 Ashok PSR No Yes Yes Yes Lollypop 20-JAN-17

14 26-JAN-17 Bharathi PSR Yes No No No Lollypop 20-JAN-17

15 26-JAN-17 Anjana PSR No Yes No Yes Lollypop 20-JAN-17

16 26-JAN-17 Sanjana MEPCO Yes Yes No No Munch 25-JAN-17

17 26-JAN-17 Yogesh MEPCO No Yes Yes No Munch 25-JAN-17

18 26-JAN-17 Kumar MEPCO Yes Yes No Yes Munch 25-JAN-17

ENTRY_DATE RANGE

SQL > SELECT *FROM INTCLG WHERE ENTRY_DATE BETWEEN '23 JAN 2017'

AND '25 JAN 2017';

SNO DATES SNAME CNAME QUIZ SOFT PAPER POST LOT ETRDT

----- --------------- ------------- --------------- --------- -------- --------- ------- ------------- ------------

1 26-JAN-17 Bhuvana ANJAC Yes Yes No No Eclairs 23-JAN-17

2 26-JAN-17 Jemi ANJAC Yes No No No Eclairs 23-JAN-17

3 26-JAN-17 Kalai ANJAC No Yes No No Eclairs 23-JAN-17

12 26-JAN-17 Vicky Kamarajar No No No Yes Safari 18-JAN-17

13 26-JAN-17 Ashok PSR No Yes Yes Yes Lollypop 20-JAN-17

14 26-JAN-17 Bharathi PSR Yes No No No Lollypop 20-JAN-17

15 26-JAN-17 Anjana PSR No Yes No Yes Lollypop 20-JAN-17

16 26-JAN-17 Sanjana MEPCO Yes Yes No No Munch 25-JAN-17

17 26-JAN-17 Yogesh MEPCO No Yes Yes No Munch 25-JAN-17

18 26-JAN-17 Kumar MEPCO Yes Yes No Yes Munch 25-JAN-17

SPECIFIC DATE

SQL > SELECT *FROM INTCLG WHERE ENTRY_DATE='18 JAN 2017';

SNO DATES SNAME CNAME QUIZ SOFT PAPER POST LOT ETRY_DTE

----- --------------- ------------- --------------- --------- -------- --------- ------- -------- ----------------

10 26-JAN-17 Jothi Kamarajar Yes Yes No No Safari 18-JAN-17

11 26-JAN-17 Arun Kamarajar No Yes No No Safari 18-JAN-17

12 26-JAN-17 Vicky Kamarajar No No No Yes Safari 18-JAN-17

EX.NO:5 B) TO IMPLEMENT THE DATE FUNCTION

Date function is converts in a String to Date.

ADD_MONTHS

ADD_MONTHS function adds the specified number of months to a

datetime value

SYNTAX

               SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;

ADD_MONTHS

Add_months function adds or subtracts the specified number of months from a

datetime expression.

SYNTAX

               SELECT ADD_MONTHS('11-JAN-2012', 1) FROM dual;

LAST_DAY

If the resulting month has fewer days then its last day is set.

SYNTAX

SELECT LAST_DAY(‘date’) FROM DUAL;

DIFFERENCE BETWEEN TWO DATES

The DATEDIFF() function returns the time between two dates.

SYNTAX

SELECT TO_DATE(‘date’)-TO_DATE(date) FROM DUAL;

NEXT DAY

NEXT_DAY function returns the first weekday that is greater than a date.

SYNTAX

SELECT SYSDATE,NEXT_DAY(SYSDATE,'MONDAY')

"NEXT MON" FROM DUAL;

MONTHS_BETWEEN

Number of months between two dates

SYNTAX

SELECT MONTHS_BETWEEN(SYSDATE,DATES) FROM INTCLG

GROUP BY DATES;

ROUND

ROUND function returns a date rounded to a specific unit of measure.

SYNTAX

SELECT SYSDATE,ROUND(SYSDATE,'MONTH') MONTH FROM DUAL;

TO_CHAR

TO_CHAR function converts a number or date to a string.

SYNTAX

SELECT TO_CHAR(SYSDATE,'DAY') "TODAY" FROM DUAL;

EXTRACT

The EXTRACT() function is used to return a single part of a date/time,

such as year, month, day, hour

SYNTAX

SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

ADD_MONTHS

SQL > SELECT ADD_MONTHS(SYSDATE,5) FROM DUAL;

ADD_MONTHS

---------------------

30-JUN-17

LAST_DAY

SQL > SELECT LAST_DAY('12 JAN 2017') AS LAST_DAY FROM dual;

LAST_DAY

----------------

31-JAN-17

DIFFERENCE BETWEEN TWO DATES

SQL > SELECT TO_DATE('23 JAN 2017')-TO_DATE('26 JAN 2017') FROM DUAL;

TO_DATE('23JAN2017')-TO_DATE('26JAN2017')

-----------------------------------------

-3

NEXTDAY

SQL > SELECT SYSDATE,NEXT_DAY(SYSDATE,'MONDAY') "NEXT MON" FROM

DUAL;

SYSDATE NEXT MONTH

-------------- ----------------------

31-JAN-17 06-FEB-17

SQL> SELECT SYSDATE,ROUND(SYSDATE,'MONTH') MONTH FROM DUAL;

SYSDATE MONTH

--------------- ------------

31-JAN-17 01-FEB-17

SQL> SELECT MONTHS_BETWEEN(SYSDATE,DATES) FROM INTCLG

GROUP BY DATES;

MONTHS_BETWEEN(SYSDATE,DATES)

-------------------------------------------------------

.177590352

TODAY

SQL > SELECT TO_CHAR(SYSDATE,'DAY') "TODAY" FROM DUAL;

TODAY

------------

Tuesday

EXTRACT

SQL > SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;

EXTRACT(YEARFROMSYSDATE)

------------------------------------------------

2017

EX.NO:6 A)TO IMPLEMENT THE SUBQUERY

USING SUPER MARKET BILL

AIM

Program to implement the SUBQUERY in SQL using SUPER MARKET BILL.

SUBQUERY

A Subquery or Inner query or Nested query is a query within another SQL query and

embedded within the WHERE clause.A subquery is used to return data that will be used in the

main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements

along with the operators like =, <, >, >=, <=, IN, BETWEEN etc.

SYNTAX

               SELECT column_name [, column_name ]FROM   table1 [, table2 ]

WHERE column_name OPERATOR

(SELECT column_name [, column_name ]

FROM table1 [, table2 ] [WHERE]);

EXIST

If a subquery returns any rows at all, exists subquery is true, and not exists subquery is false.

SYNTAX

               WHERE EXISTS ( SUBQUERY );

INTERSECT

The SQL INTERSECT clause/operator is used to combine two SELECT

statements,but returns rows only from the first SELECT statement that are identical to a row

in the second

SELECT statement

SYNTAX

               SELECT column1 [, column2 ]FROM table1 [, table2 ]

               [WHERE condition]

               INTERSECT

               SELECT column1 [, column2 ]FROM table1 [, table2 ]

               [WHERE condition]

NOT EXIST

If a subquery does not returns any rows at all, exists subquery is true, and not exists

subquery is false.

SYNTAX

               WHERE NOT EXISTS ( SUBQUERY );

CREATE A TABLE

SQL > CREATE TABLE BILL1(SNO NUMBER,DATES DATE,CUS_NAME

VARCHAR2(10),BILL_NO NUMBER NOT NULL,PRO_NAME

VARCHAR2(10),QTY NUMBER NOT NULL,PRICE NUMBER NOT

NULL,TOTAL NUMBER,PRIMARY KEY(BILL_NO));

INSERT A DATA INTO TABLE

Enter the sno               :101

Enter the dates :Bhuvana

Enter the cus_name     :12345671

Enter the pro_name     :Soap

Enter the qty               :6

Enter the price             :6

Enter the total             :0

SQL > SELECT *FROM BILL1;

SNO      DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ----------
101 23-JAN-17       Bhuvana   12345671              soap             6              6         36
102 23-JAN-17              Jeya   12345672       Dry_milk            2              5         10
103 23-JAN-17             Mani   12345673          powder            1             25           1
104 25-JAN-17             Jothi    12345674                pen           10           10       100
105 26-JAN-16             selvi    12345675            rubber            5              5         25
106 23-JAN-17       Bharathi    12345676             paper            1            15         16
107 25-JAN-17            vicky    12345677           munch            5         288        100
108 31-JAN-17         Bharath    12345678            pencil            5             7          35
109 31-JAN-17           prabhu   12345679          horlicks            1        150         150

10 1-JAN-17 lavanya 123456710 chocolate 2 45 90

INSERT A DATA INTO ANOTHER TABLE USING SUBQUERY

SQL > INSERT INTO BILL1 SELECT FROM BILL2 WHERE SNO IN(SELECT SNO

FROM BILL1);

SQL > SELECT *FROM BILL2;

SNO      DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ----------
101 23-JAN-17       Bhuvana   12345671              soap             6              6         36
102 23-JAN-17              Jeya   12345672       Dry_milk            2              5         10
103 23-JAN-17             Mani   12345673          powder            1             25           1
104 25-JAN-17             Jothi    12345674                pen            10           10       100
105 26-JAN-16             selvi    12345675            rubber            5              5         25
106 23-JAN-17       Bharathi    12345676             paper            1            15         16
107 25-JAN-17            vicky    12345677           munch            5         288        100
108 31-JAN-17         Bharath    12345678            pencil            5             7          35
109 31-JAN-17           prabhu   12345679          horlicks            1        150         150
    10 31-JAN-17          lavanya   123456710      chocolate           2          45           90

UPDATE THE DATA INTO TABLE

SQL > UPDATE BILL1 SET PRICE=PRICE*2 WHERE DATES IN (SELECT DATES

FROM BILL2 WHERE DATES='23 JAN 2017';

SNO      DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ----------
101 23-JAN-17       Bhuvana   12345671              soap             6              6         36
102 23-JAN-17              Jeya   12345672       Dry_milk            2              5         10
103 23-JAN-17             Mani   12345673          powder            1             25           1
104 25-JAN-17             Jothi    12345674                pen            10           10       100
105 26-JAN-16             selvi    12345675            rubber            5              5         25
106 23-JAN-17       Bharathi    12345676             paper            1            15         16
107 25-JAN-17            vicky    12345677           munch            5         288        100
108 31-JAN-17         Bharath    12345678            pencil            5             7          35
109 31-JAN-17           prabhu   12345679          horlicks            1        150         150
    10 31-JAN-17          lavanya   123456710      chocolate           2          45           90

SQL >UPDATE BILL1 SET DATES='26 JAN 17' WHERE DATES IN(SELECT DATES

FROM BILL2 WHERE DATES='26 JAN 16');

SNO DATES CUS_NAME BILL_NO PRO_NAME QTY PRICE TOTAL

------ -------------- ----------------- ------------- ----------------- ---------- ---------- ----------

105 26-JAN-17 Selvi 12345675 Rubber 5 5 25

SQL > UPDATE BILL1 SET STOCK=50 WHERE STOCK IN (SELECT STOCK FROM

BILL2);

SNO   DATES CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL STOCK
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ---------- ---------
101 23-JAN-17       Bhuvana   12345671              soap             6              6         36            20
102 23-JAN-17              Jeya   12345672       Dry_milk            2              5         10            50
  103 23-JAN-17             Mani   12345673          powder            1             25           1           50
104 25-JAN-17             Jothi    12345674                pen            10           10       100           50
105 26-JAN-16             selvi    12345675            rubber            5              5         25            50
106 23-JAN-17       Bharathi    12345676             paper            1            15         16            50
107 25-JAN-17            vicky    12345677           munch            5         288        100            50
108 31-JAN-17         Bharath    12345678            pencil            5             7          35            50
109 31-JAN-17           prabhu   12345679          horlicks            1        150         150            50
    10 31-JAN-17          lavanya   123456710      chocolate           2          45           90            50

SQL > SELECT *FROM BILL1 WHERE TOTAL IN(SELECT MIN(TOTAL) FROM

BILL2);

BILL2);

BILL2);

FROM BILL2 PRO_NAME='SOAP');

WHERE PRICE BETWEEN 20 AND 40);

WHERE PRICE BETWEEN 20 AND 100 AND PRICE!=25);

SNO   DATES   CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
107 25-JAN-17             Vicky 12345677            Munch            5           20          100        20
110   31-JAN-17        Lavanya 123456710        Chocolate           2           45            90

SQL > SELECT *FROM BILL1 WHERE CUS_NAME LIKE 'B%';

SNO DATES   CUS_NAME BILL_NO PRO_NAME       QTY    PRICE   TOTAL STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
101     23-JAN-17         Bhuvana 12345671               Soap            6               6         36         20
106     23-JAN-17         Bharathi   12345676              Paper            1             15         16
108     31-JAN-17          Bharath   12345678             Pencil             5              7          35

EXIST

SQL > SELECT SNO,CUS_NAME,TOTAL FROM BILL1 WHERE EXISTS

(SELECT *FROM BILL_DET WHERE BILL1.SNO=BILL2.SNO);

SNO CUS_NAME TOTAL
------- ---------------- -----------
101         Bhuvana             36
102                Jeya             10
103               Mani             25
104               Jothi             100
105              Selvi              25
106        Bharathi              60
107             Vicky             110
108         Bharath               35
109          Prabhu              150
110         Lavanya              90

NOT EXISTS

SQL >SELECT SNO,CUS_NAME,TOTAL FROM BILL1 WHERE NOT EXISTS

(SELECT *FROM BILL2 WHERE BILL1.SNO=BILL2.SNO);

SNO CUS_NAME TOTAL
-------- ----------------- ----------
10 Lavanya 90

INTERSECT

SQL > SELECT BILL1.SNO,BILL2.CUS_NAME,BILL1.DATES FROM

BILL1 RIGHT JOIN BILL2 ON BILL1.SNO=BILL2.SNO

INTERSECT

SELECT BILL1.SNO,BILL1.CUS_NAME,BILL2.DATES FROM BILL1

RIGHT JOIN BILL2 ON BILL1.SNO=BILL2.SNO;

SNO CUS_NAME         DATES
------- ----------------- ---------------
101         Bhuvana     23-JAN-17
102                Jeya     25-JAN-17
103               Mani     31-JAN-17
104               Jothi      31-JAN-17
105               Selvi      27-JAN-17

106 Bharathi 23-JAN-17

EX.NO:6 B)TO IMPLEMENT THE DECODE,CASE,TRANSLATE

AIM

To implement the DECODE,CASE,TRANSLATE in SQL.

DECODE

DECODE function has the functionality of an IF-THEN-ELSE statement.

SYNTAX

DECODE(expression,search,result[,search,result]..[,default] )

CASE

CASEexpressions let you use IF ... THEN ... ELSE logic in SQL statements without

having to invoke procedures.

SYNTAX

CASE [expression ]

WHEN condition_1 THEN result_1

WHEN condition_n THEN result_n

ELSE result

END

TRANSLATE

TRANSLATE function replaces a sequence of characters in a string with another set of

characters.

SYNTAX

TRANSLATE(String1,String to REPLACE);

DECODE

SQL>SELECT IMPORT,DECODE (IMPORT,'USA',

'UNITED STATUS OF AMERICA','UK','UNITED KINGDOM',

'CH','CHINA')AS DECODE FROM BILL2;

IMPORT DECODE

------------- ------------------------------------

USA United status of America

UK United Kingdom

CH China

SQL>SELECT CUS_NAME,CASE

WHEN TOTAL>100 AND TOTAL<200 THEN 'TOTAL IS GREATER THAN 90'

WHEN TOTAL >200 AND TOTAL<300 THEN 'TOTAL IS GREATER THAN 200'

ELSE 'NULL' END AS CASE FROM BILL2;

CUS_NAME CASE

---------------- -----------------------------

Bhuvana NULL

Jeya NULL

Mani NULL

Jothi Total is greater than 90

SQL>SELECT SNO,TRANSLATE(SNO,'1','X') AS TRANSLATE FROM BILL2;

S_NO TRANSLATE

---------------------------

x0x

x02

x03

x04

EX.NO:7 TO IMPLEMENT THE VIEW CONCEPT IN SQL

AIM

To implement the VIEW concept in SQL.

VIEW

A view is nothing more than a SQL statement that is stored in the database with an

associated name.A view can contain all rows of a table or select rows from a table. A view

can be created from one or many tables which depends on the written SQL query to create a

view.

SYNTAX

               CREATE VIEW view_name AS

SELECT column1, column2.....

FROM table_name

WHERE [condition];

DELETE VIEW

               DROP VIEW view_name;

CREATE A TABLE

SQL > CREATE TABLE CUSTOMER(CUS_ID IN NUMBER,

CUS_NAME VARCHAR2(10),CITY VARCHAR(10),ORDER_DATES DATE,

PRIMARY KEY(CUS_ID),ADDRESS VARCHAR2(15),SALESMAN_ID

NUMBER);

SQL > INSERT INTO CUSTOMER VALUES(&CUS_ID,’&CUS_NAME’,’&CITY’,

’&ORDER_DATES’,’&ADDRESS’,SALESMAN_ID);

Enter the cus_id :101

Enter the cus-name :Aaa

Enter the city :Chennai

Enter the address :15,Nehru street

Enter the order_dates :15 JAN 17

Enter the salesman_id :301

CUS_ID CUS_NAME CITY OR_DATE ADDRESS SALESMAN_ID

----------- ------------------- ----------- --------------------- ------------------- -----------------------

101 Aaa Chennai 15 JAN 17 15,Nehru street 301

102 Bbb Delhi 23 JAN 17 20,NRK Nagar 302

103 Ccc Paris 15 JAN 17 20,Gandhi street 303

CREATE TABLE2(SALESMAN)

SQL >CREATE TABLE SALESMAN(SALESMAN_ID NUMBER,SALES_NAME

VARCHAR2(10),CITY VARCHAR2(10),COMMISSION_CHARGE FLOAT,

PRIMARY KEY(SALESMAN_ID));

INSERT A DATA INTO TABLE

SQL > INSERT INTO SALESMAN VALUES(&SALEMAN_ID,

’&SALES_NAME’,’&CITY’,COMMISSION_CHARGE);

Enter the salesman_id :301

Enter the salesman_name :Fff

Enter the city :Chennai

Enter the commission_charge :345.5

SALESMAN_ID SALESMAN_NAME CITY COMMISSION_CHARGE

---------------------- ------------------------------- -------------- -------------------------------------

301 Fff Chennai 345.5

302 Ggg Paris 98.5

303 Hhh London 903.25

CREATE A TABLE3(ORDER)

SQL > CREATE TABLE ORDER(ORDER_NO NUMBER,ORDER_DATE DATE,

PURC_AMOUNT FLOAT,CUS_ID NUMBER,SALESMAN_ID NUMBER,

CUS_NAME VARCHAR2(10));

SQL > INSERT INTO ORDER VALUES(&ORDER_NO,’&ORDER_DATE’,

&PURC_AMOUNT,&CUS_ID,&SALESMAN_ID,’&CUS_NAME’);

Enter the order_no :201

Enter the order_date :2300

Enter the purc_amount :15 JAN 17

Enter the cus_id :101

Enter the salesman_id :301

Enter the cus_name :Ggg

SQL >SELECT *FROM ORDER;

ORDER_NO ORDER_DATE PURC_AMT CUS_ID SALESMAN_ID CUS_NAME

----------------- -------------------- ---------------- ----------- --------------------- ------------------

201 15 JAN 17 2300 101 301 Aaa

202 16 JAN 17 5000 102 302 Bbb

203 23 JAN 17 7800.5 103 303 Ccc

(i)Write a query to create a view for all salesman with columns salesman_id,name and city.

SQL > CREATE OR REPLACE VIEW SALESMAN_VIEW AS SELECT

SALESMAN_ID,SALESMAN_NAME,CITY FROM SALESMAN.

SALESMAN_ID SALESMAN_NAME CITY

---------------------- ------------------------------- --------------

301 Fff Chennai

302 Ggg Paris

303 Hhh London

(ii)Write a query to create a view that shows the number of salesman in each city.

SQL > CREATE OR REPLACE VIEW CITY,COUNT(CITY) FROM SALESMAN;

CITY COUNT(CITY)

-------------- -----------------------

Chennai 1

Paris 1

London 1

(iii)Write a query to create a view that find the salesmn who issued orders on either

August 17th aug,2012 (or) October 10^th,2012.

SQL > CREATE OR REPLACE VIEW SALESMAN_VIEW AS SELECT *FROM

CUSTOMERS WHERE OREDER_DATE IN (SELECT ORDER_DATE FROM

ORDER WHERE ORDER_DATE=’17 AUG 16’ BETWEEEN

OREDER_DATE=’20 JAN 17’;

CUS_ID CUS_NAME CITY OR_DATE ADDRESS SALESMAN_ID

----------- ------------------- ----------- --------------------- ------------------- -----------------------

101 Aaa Chennai 15 JAN 17 15,Nehru street 301

(iv) Write a query using view add subquery find the salesman who issued orders on

October 10,2017.

SQL > CREATE OR REPLACE VIEW SALESMAN_VIEW AS SELECT *FROM

CUSTOMER WHERE ORDER_DATE IN(SELECT ORDER_DATE FROM

ORDER WHERE ORDER_DATE=’15 JAN 17’);

CUS_ID CUS_NAME CITY OR_DATE ADDRESS SALESMAN_ID

----------- ------------------- ----------- --------------------- ------------------- -----------------------

101 Aaa Chennai 15 JAN 17 15,Nehru street 301

(iv) Write a query us create a view that shows the number of orders in each day.

SQL > CREATE OR REPLACE VIEW CUSTOMER_VIEW AS SELECT

COUNT(ORDER_DATE),ORDER_DATE

FROM ORDER GROUP BY ORDER_DATE;

COUNT(ORDER_DATE) ORDER_DATE

--------------------------------- ----------------------

2 15 JAN 17

1 16 JAN 17

1 25 JAN 17

(v)Write a query to create a view to keeping track the number of customers ordering number

of Salesman attached,averge amount of orders and total number of orders in a day.

SQL > SELECT COUNT(CUS_NAME),COUNT(SALESMAN_ID),

AVG(PURC_AMOUNT),FROM ORDER WHERE

ORDER_DATE=’15 JAN 17’ OR ORDER BY CUS_NAME;

(vii)Write specific data in a view

SQL > UPDATE SALESMAN_VIEW SET PURC_AMOUNT=2000 WHERE

CUS_ID=301;

SALESMAN_ID SALESMAN_NAME CITY PURCHASE_AMOUNT

---------------------- ------------------------------- -------------- ----------------------------------

301 Fff Chennai 2000

(viii)Write a specific record in a view

SQL > DELETE FROM SALESMAN_VIEW WHERE SALESMAN_ID=302;

SALESMAN_ID SALESMAN_NAME CITY

---------------------- ------------------------------- --------------

301 Fff Chennai

303 Hhh London

CREATE A FORCE VIEW

SQL > CREATE OR REPLACE VIEW FORCE_VIEW AS SELECT *FROM ORDER

WHERE PURC_AMOUNT>2500;

ORDER_NO ORDER_DATE PURC_AMT CUS_ID SALESMAN_ID CUS_NAME

----------------- -------------------- ---------------- ----------- --------------------- ------------------

202 16 JAN 17 5000 102 302 Bbb

203 23 JAN 17 7800.5 103 303 Ccc

DROP IN A VIEW

SQL >DROP VIEW SALESMAN_VIEW;

View is dropped.

EX.NO:8 TO IMPLEMENT THE SEQUENCE AND INDEX

SEQUENCE

Sequence is a feature supported by some database systems to produce unique values

on demand. Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence.

AUTO_INCREMENT is applied on columns, it automatically increments the column value

by 1 each time a new record is entered into the table.

CACHE

Specify how many values of the sequence the database preallocates and keeps in memory

for faster access.

SYNTAX

CREATE SEQUENCE SEQ_NAME

STRAT WITH VALUE

INCREMENT BY VALUE

MAXIMUM VALUE

CACHE VALUE.

INDEX

Indexes are special lookup tables that the database search engine can use to speed

up data retrieval. Simply put, an index is a pointer to data in a table.An index helps speed up

SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and

INSERT statements. Indexes can be created or dropped with no effect

on the data.

SYNTAX

               CREATE INDEX index_name ON table_name;

DROP INDEX

An index can be dropped using SQL DROP command.

SYNTAX

DROP INDEX index_name;

CREATE A SEQUENCE

CREATE SEQUENCE SEQ_001

START WITH 1

INCREMENT BY 2

MAXVALUE 10

CACHE 5;

CREATE A TABLE

SQL > CREATE TABLE TAB_NAME(ROLLNO NUMBER,NAME

VARCHAR2(10),TOTAL NUMBER GRADE VARCHAR);

INSERT A DATA INTO TABLE

SQL > INSERT INTO TAB_01(ROLLNO,NAME,TOTAL,GRADE)VALUES

(SEQ_001.NEXTVAL,'XXX',495,’FIRST

CLASS WITH DESTINATION’);

SQL > INSERT INTO TAB_01(ROLLNO,NAME,TOTAL,GRADE)VALUES

(SEQ_001.NEXTVAL,'YYY',460,’FIRST

CLASS);

SQL > SELECT *FROM TAB_01;

Rollno Name Total Grade

---------- ----------------- --------------- ----------------------------------

1 XXX 495 First class with Destination

3 YYY 460 First class

5 ZZZ 450 Second class

7 AAA 430 Third class

9 BBB 420 Fourth class

SQL > SELECT *FROM SEQ_001;

Rollno Name Total Grade

---------- ----------------- --------------- ----------------------------------

1 XXX 495 First class with Destination

3 YYY 460 First class

5 ZZZ 450 Second class

7 AAA 430 Third class

9 BBB 420 Fourth class

UPDATE A DATA INTO TABLE

SQL > UPDATE SEQ_001 SET TOTAL=435 WHERE ROLLNO=’15US07’;

Rollno Name Total Grade

---------- ----------------- --------------- ----------------------------------

1 XXX 495 First class with Destination

3 YYY 460 First class

5 ZZZ 450 Second class

7 AAA 435 Third class

9 BBB 420 Fourth class

1 row updated.

DELETE A DATA INTO TABLE

SQL > DELETE FROM SEQ_001 WHERE ROLLNO=’15US09’;

Rollno Name Total Grade

---------- ----------------- --------------- ----------------------------------

1 XXX 495 First class with Destination

3 YYY 460 First class

5 ZZZ 450 Second class

7 AAA 430 Third class

1 row deleted.

CREATE INDEX

SQL > CREATE INDEX IND_NAME ON TAB_NAME(ROLLNO,NAME);

Index is created

DROP INDEX

SQL > DROP INDEX IND_NAME ON TAB_NAME(ROLLNO,NAME);

Index is dropped.

PL/SQL

The PL/SQL programming language was developed by Oracle Corporation in the late

1980s as procedural extension language for SQL and the Oracle relational database.

It is a tool within the Oracle programming environment. SQL* Plus is an interactive tool

that allows you to type SQL and PL/SQL statements at the command prompt.

SYNTAX

               DECLARE

               <declarations section>

               BEGIN

                <executable command(s)>

               EXCEPTION

                <exception handling>

               END;

DECLARATIONS

This section starts with the keyword DECLARE. It is an optional section and defines

all variables, cursors, subprograms, and other elements to be used in the program.

EXECUTABLE COMMANDS

This section is enclosed between the keywords BEGIN and END and it is a

mandatory section. It consists of the executable PL/SQL statements of the program. It should

have at least one executable line of code, which may be just a NULL command to indicate

that nothing should be executed.

EXCEPTION HANDLING

This section starts with the keyword EXCEPTION. This section is again optional and

contains exception(s) that handle errors in the program.

TO FINDTHE FIBONACCI SERIES USING PL/SQL

AIM

Program to find the FIBONACCI SERIES(simple program) in PL/SQL.

CODINGS

//To find the Fibonacci series in p/sql

Declare

C number;

A number;

B number;

I integer;

Begin

Dbms_output.put_line(‘FIBONACCI SERIES’);

FOR I in 0..5 LOOP

C:=a+b;

A:=b;

B:=c;

Dbms_output.put_line(‘c:’||c);

END LOOP;

END;

SQL>Set serveroutput on

SQL>/

FIBONACCI SERIES

C:=0

C:=1

C:=2

C:=3

C:=5

PL/SQL is executed successfully.

EX.NO:9 TO WRITE A PL/SQL CODE WITHIN SQL COMMAND

AIM

Program to write a PL/SQL code within SQL command.

CREATE A TABLE

SQL> CREATE TABLE TABP1(EMP_NO INT,EMP_NAME VARCHAR2(10),

STREET VARCHAR2(10),CITY VARCHAR2(10),JOIN_DATE DATE,

DESIGNATION VARCHAR2(10));

Table created.

INSERT A DATA INTO THE TABLE

SQL> INSERT INTO TABP1 VALUES(&EMP_NO,'&EMP_NAME','&STREET',

'&CITY',’&JOIN_DATE’,’&DESIGNATION’);

Enter value for emp_no : 101

Enter value for emp_name : Dhinesh

Enter value for street : MN street

Enter value for city : Sivakasi

Enter value for join_date :02-jan-17

Enter value for designation :Civil

SQL> SELECT *FROM TABP1;

EMP_NO EMP_NAME STREET CITY JOIN_DATE DESIGNATION

---------------- ----------------- --------------- ------------- --------------------- -----------------------

101 Dhinesh MN street Sivakasi 02-JAN-17 Civil

102 Dhivahar PK street Madurai 15-JAN-17 EEE

103 Arun VS street Chennai 10-JAN-17 M.SC

104 lohu NK street Sivakasi 02-JAN-17 ECE

105 Ram SK street Bangalore 07-JAN-17 ECE

CREATE A TABLE2

SQL>CREATE TABLE TABP2(EMP_NO INT,BASIC INT,HRA INT NULL,DA INT

NULL,TOTAL_DEDUCTION INT,NET_SALARY INT,GROSS_SALARY INT);

Table created.

INSERT A DATA INTO A TABLE

SQL> INSERT INTO TABP2 VALUES(&EMP_NO,&BASIC,

&HRA,&DA,&TOTAL_DEDUCTION,&NET_SALARY,&GROSS_SALARY);

Enter value for emp_no : 101

Enter value for basic : 50000

Enter value for hra : 300

Enter value for da : 100

Enter value for total_deduction : 400

Enter value for net_salary : 4920

Enter value for gross_salary : 49600

EMP_NO BASIC HRA DA TOTAL_DEDUCTION NET_SALARY GROSS_SAl

----------- -------- ------- -------- ---------------------------- -------------------- -------------------

101 50000 300 100 400 49200 49600

102 30000 1000 500 1500 27000 28500

103 40000 2000 1000 3000 15000 19000

104 25000 1500 1000 2500 20000 22500

105 30000 1000 550 1550 26900 28450

1) Write a PL/SQL code to retrieve the employee name, join_date, and designation

from employee database of an employee whose number is input by the user.

DECLARE

ENO TABP1.EMP_NO%TYPE;

ENAME TABP1.EMP_NAME%TYPE;

JOINDATE TABP1.JOIN_DATE%TYPE;

DESIGNATIONS TABP1.DESIGNATION%TYPE;

BEGIN

ENO:=&ENO;

SELECT EMP_NO,EMP_NAME,JOIN_DATE,DESIGNATION INTO

ENO,ENAME,JOINDATE,DESIGNATIONS FROM TABP1

WHERE EMP_NO=ENO;

DBMS_OUTPUT.PUT_LINE('EMP_NAME:' || ENAME);

DBMS_OUTPUT.PUT_LINE('JOIN_DATE:' || JOINDATE);

DBMS_OUTPUT.PUT_LINE('DESIGNATION:' || DESIGNATIONS);

END;

SQL> @D:/ORACLE/PL1.SQL

14 /

ENTER VALUE FOR ENO: 101

OLD 7: ENO:=&ENO;

NEW 7: ENO:=101;

EMP_NAME:DHINESH

JOIN_DATE:02-JAN-17

DESIGNATION:CIVIL

PL/SQL procedure successfully completed.

2) Write a PL/SQL code to calculate tax for an employee of an organization –XYZ and

to display his/her name & tax, by creating a table under employee database as below.

DECLARE

TAX NUMBER:=0;

NET NUMBER;

ENO TABP2.EMP_NO%TYPE;

NAME TABP1.EMP_NAME%TYPE;

BEGIN

ENO:=&ENO;

SELECT NET_SALARY INTO NET FROM TABP2 WHERE

EMP_NO=ENO;

SELECT EMP_NAME INTO NAME FROM TABP1 WHERE

EMP_NO=ENO;

NET:=NET*12;

IF NET>190000 THEN

NET:=NET-190000;

TAX:=NET*0.2;

END IF;

DBMS_OUTPUT.PUT_LINE('NAME OF THE EMPLOYEE IS '||NAME);

DBMS_OUTPUT.PUT_LINE('TAXABLE AMOUNT IS '||TAX);

END;

SQL> set serveroutput on;

SQL> /

ENTER VALUE FOR ENO: 101

OLD 7: ENO:=&ENO;

NEW 7: ENO:=101;

NAME OF THE EMPLOYEE IS DHINESH

TAXABLE AMOUNT IS 80080

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

3)Write a PL/SQL code to display employee number, name and basic of 5 highest paid

employees.

DECLARE

I NUMBER:=0;

CURSOR EC IS SELECT TABP1.EMP_NO,EMP_NAME,BASIC FROM TABP1,TABP2

WHERE TABP1.EMP_NO=TABP2.EMP_NO ORDER BY GROSS_SALARY

DESC;

R EC%ROWTYPE;

BEGIN

OPEN EC;

LOOP

EXIT WHEN I=5;

FETCH EC INTO R;

DBMS_OUTPUT.PUT_LINE(R.EMP_NO||' '||R.EMP_NAME||' '||R.BASIC);

I:=I+1;

END LOOP;

CLOSE EC;

END;

SQL>set serveroutput on

SQL> @d:/oracle/pl3.sql

SQL> /

101 Dhinesh 50000

102 Dhivahar 30000

105 Rajesh 30000

104 lohu 25000

103 Arun 40000

PL/SQL procedure successfully completed.

4) Write a PL/SQL code to update the salary of employees who earn less than the average

salary

Declare

AVERAGE NUMBER;

BS NUMBER;

GS NUMBER;

DIFF NUMBER;

CURSOR EC IS SELECT *FROM EMP_DET;

RW EC%ROWTYPE;

BEGIN’SELECT AVG(BASIC) INTO AVERAGE FROM EMP_DET;

DBMS_OUTPUT.PUT_LINE(‘THE AVERAGE SALARY IS’||AVERAGE);

OPEN EC;

LOOP

FETCH EC INTO RW;

EXIT WHEN EC%NOTFOUND;

IF RW.BASIC<=AVERAGE

THEN

DIFF:=RW.BASIC-AVERAGE;

UPDATE EMP_DET SET

BASIC=AVERAGE,GROSS_SALARY=GROSS_SALARY+DIFF

INTO BS,GS FROM EMP_DET WHERE EMP_NO=RW.EMP_NO;

DBMS_OUTPUT.PUT_LINE(‘EMPLOYEE NO’||EMP_NO);

DBMS_OUTPUT.PUT_LINE(‘OLD BASIC’||RW.BASIC||

’OLD GROSS_SALARY’||RW.GROSS_SALARY);

DBMS_OUTPUT.PUT_LINE(‘UPDATE NEW BASIC=’||BS||

’NEW GROSS_SALARY=’||GS);

END IF;

END LOOP;

CLOSE EC;

END;

SQL>Set serveroutput on

SQL>/

EMPLOYEE NO:101

OLD BASIC:20000

OLD GROSS_SALARY:15000

UPDATE NEW BASIC:25000

NEW GROSS_SALARY:16000

EX.NO:10 TO IMPLEMENT THE PROCEDURE IN PL/SQL

USING STUDENTDETAILS

AIM

To implement the PROCEDURE in pl/sql using student details.

PROCEDURE

Subprograms do not return a value directly, mainly used to perform an action.

SYNTAX

               CREATE [OR REPLACE] PROCEDURE PROCEDURE_NAME

[(PARAMETER_NAME [IN | OUT | IN OUT] TYPE [, ...])]

{IS | AS}

BEGIN

< PROCEDURE_BODY >

END PROCEDURE_NAME;

PARAMETERS MODE

An IN parameter lets you pass a value to the subprogram.

It is a read-only parameter

It is the default mode of parameter passing.

Parameters are passed by reference.

OUT

An OUT parameter returns a value to the calling program.

Its value and reference the value after assigning it.

IN OUT

An IN OUT parameter passes an initial value to a subprogram and returns an updated

value to the caller. It can be assigned a value and its value can be read.

DROP PROCEDURE

SYNTAX

               DROP PROCEDURE procedure-name;

CREATE A PROCEDURE

CREATE OR REPLACE PROCEDURE PR_ST01(ROLLNO IN VARCHAR2,

ST_NAME IN VARCHAR2,MATHS IN

INTEGER,PHYSICS IN INTEGER,CHEMISTRY IN INTEGER)

P_CUTOFF BHUV_ST01.CUT_OFF%TYPE;

P_GRADE BHUV_ST01.GRADE%TYPE;

BEGIN

P_CUTOFF:=(MATHS/2)+(PHYSICS/4)+(CHEMISTRY/4);

IF P_CUTOFF >=190

THEN

P_GRADE:='FIRST CLASS WITH DESTINATION';

ELSE IF P_CUTOFF >=180

THEN

P_GRADE:='FIRST CLASS';

ELSE IF P_CUTOFF >=150

THEN

P_GRADE:='SECOND CLASS';

ELSE

P_GRADE:='FAIL';

END IF;

INSERT INTO BHUV_ST01 VALUES(ROLLNO,ST_NAME

,MATHS,PHYSICS,CHEMISTRY,P_CUTOFF,P_GRADE);

END;

SQL> /

Procedure created.

SQL> EXEC PR_ST01('15US03','EEE',176,156,190);

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT *FROM BHUV_ST01;

ROLLNO NAME MAT PHY CHEMIS CUT_OFF GRADE

------------- ---------- -------- -------- ------------ ---------------- -----------------------------------------

15us01 xxx 190 180 198 190 First class with destination

15us02 yyy 178 198 167 180 First class

15us03 eee 176 156 190 175 Second class

15us04 rrr 178 156 197 177 Second class

15us05 kkk 178 156 0 128 Fail

PROCEDURE UPDATE

CREATE OR REPLACE PROCEDURE UP_PRO(CUT_OFF INTEGER,

GRADE VARCHAR2)

BEGIN

UPDATE BHUV_ST01 SET GRADE='THIRD CLASS' WHERE CUT_OFF<150;

END;

SQL> /

PROCEDURE CREATED.

SQL> EXEC UP_PRO(128,'THIRD CLASS');

PL/SQL PROCEDURE EXEDUTED SUCCESSFULLY .

SQL> SELECT *FROM BHUV_ST01;

ROLLNO NAME MAT PHY CHEMIS CUT_OFF GRADE

------------- ---------- -------- -------- ------------ ---------------- -----------------------------------------

15us01 xxx 190 180 198 190 First class with destination

15us02 yyy 178 198 167 180 First class

15us03 eee 176 156 190 175 Second class

15us04 rrr 178 156 197 177 Second class

15us05 kkk 178 156 0 128 Third class

PROCEDURE USING DELETE

CREATE OR REPLACE PROCEDURE DEL_PRO(ROLLNO1 IN VARCHAR2)

BEGIN

DELETE FROM BHUV_ST01 WHERE ROLLNO=ROLLNO1;

END;

PROCEDURE CREATED.

SQL> EXEC DEL_PRO('15US04');

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

SQL> SELECT *FROM BHUV_ST01;

ROLLNO NAME MAT PHY CHEMIS CUT_OFF GRADE

------------- ---------- -------- -------- ------------ ---------------- -----------------------------------------

15us01 xxx 190 180 198 190 First class with destination

15us02 yyy 178 198 167 180 First class

15us03 eee 176 156 190 175 Second class

15us05 kkk 178 156 0 128 Third class

EX.NO:11 TO IMPLEMENT FUNCTION IN PL/SQL

AIM

To implement the FUNCTION in pl/sql using EB bill.

FUNCTIONS

Subprograms return a single value, mainly used to compute and return a value.

SYNTAX

CREATE FUNCTION FUNC_NAME[PARAMETER…]

RETURN DATATYPE

{IS/AS}

BEGIN

{FUNCTION BODY}

END FUNC_NAME;

CREATE A FUNCTION

//Create a function using EB bill

CREATE OR REPLACE FUNCTION CALC_01(BILLNO1 IN INTEGER,

TOTAL OUT INTEGER)

RETURN INTEGER

PCUR EB_BILL.CUR_READ%TYPE;

PPREV EB_BILL.PREV_READ%TYPE;

AMT INTEGER;

BEGIN

SELECT CUR_READ,PREV_READ INTO PCUR,PPREV FROM EB_BILL WHERE

BILLNO=BILLNO1;

TOTAL:=PCUR-PPREV;

IF TOTAL<=100

THEN

AMT:=TOTAL*3;

ELSE IF TOTAL<=200

THEN

AMT:=TOTAL*5;

ELSE

AMT:=TOTAL*10;

END IF;

RETURN AMT;

END;

Function created.

FUNCTION CALLING

DECLARE

BILLNO1 EB_BILL.BILLNO%TYPE;

NAME1 EB_BILL.NAME%TYPE;

TOTAL EB_BILL.CUR_READ%TYPE;

AMT NUMBER(7,2);

BEGIN

BILLNO1:=&BILLNO1;

SELECT NAME INTO NAME1 FROM EB_BILL WHERE BILLNO=BILLNO1;

AMT:=CALC_01(BILLNO1,TOTAL);

DBMS_OUTPUT.PUT_LINE('EB BILL');

DBMS_OUTPUT.PUT_LINE('-------');

DBMS_OUTPUT.PUT_LINE('NAME:'||NAME1);

DBMS_OUTPUT.PUT_LINE('BILLNO:'||BILLNO1);

DBMS_OUTPUT.PUT_LINE('TOTAL'||TOTAL||'UNIT');

DBMS_OUTPUT.PUT_LINE('AMT:'||AMT);

END;

SQL> SET SERVEROUTPUT ON

SQL >/

ENTER VALUE FOR BILLNO1: 1234

OLD 7: BILLNO1:=&BILLNO1;

NEW 7: BILLNO1:=1234;

EB bill

--------

Name:xxx

Billno:1234

Total-27unit

amt:-81

PL/SQL procedure successfully completed.

EX.NO:12 TO IMPLEMENT THE PACKAGE IN PL/SQL

AIM

To implement the PACKAGE in pl/sql.

PACKAGE

Package is a group of related functions,procedures and cursors.

Package is like a library onv=ce return stored in the database we can used by many

application.

The package has 3 parts

o Package Creation

o Package Definition

o Package Access

SYNTAX

CREATE [OR REPLACE] PACKAGE PAC_NAME

{IS/AS}

PACKAGE SPECIFICATION;

END PAC_NAME;

PACKAGE DEFINITION

CREATE A PACKAGE

//Function and Procedure within Package

CREATE PACKAGE PACK1

PROCEDURE RECT(L IN NUMBER,B IN NUMBER,AREA OUT NUMBER);

FUNCTION SQUARE(A IN NUMBER)

FUNCTION AREA_CIR(R IN NUMBER) RETURN NUMBER;

FUNCTION FACT(X IN NUMBER) RETURN NUMBER;

PROCEDURE CEILING(N IN NUMBER,CEIL OUT NUMBER);

END;

Package created.

CREATE A PACKAGE BODY

//AREA OF RECTANGLE USING PROCEDURE

CREATE OR REPLACE PACKAGE BODY PACK1 AS

PROCEDURE RECT(L IN NUMBER,B IN NUMBER,AREA OUT NUMBER)

BEGIN

AREA:=L*B;

END RECT;

//AREA OF SQUARE USING FUNCTION

FUNCTION SQUARE(A IN NUMBER)

RETURN NUMBER

BEGIN

RETURN A*A;

END SQUARE;

//AREA OF CIRCLE USING FUNCTION

FUNCTION AREA_CIR(R IN NUMBER)

RETURN NUMBER

AREA_CIRCLE NUMBER(7,2);

PI NUMBER(5,2);

BEGIN

PI:=22/7;

AREA_CIRCLE:=PI*R*R;

RETURN AREA_CIRCLE;

END AREA_CIR;

//FACTORIAL USING FUNCTION

FUNCTION FACT(X IN NUMBER)

RETURN NUMBER

F NUMBER;

BEGIN

IF X=0 THEN

F:=1;

ELSE

F:=X*FACT(X-1);

END IF;

RETURN F;

END FACT;

//CEILING USED PROCEDURE

PROCEDURE CEILING(N IN NUMBER,CEIL OUT NUMBER)

BEGIN

CEIL:=N+1.0;

END CEILING;

END PACK1;

package body created.

PACKAGE DEFINTION

DECLARE

AREA1 NUMBER(7,2);

AREA_SQ NUMBER(7,2);

AREA_C NUMBER(7,2);

FACTORIAL NUMBER(7,2);

CE NUMBER(1);

BEGIN

PACK1.RECT(2,3,AREA1);

DBMS_OUTPUT.PUT_LINE('AREA OF RECTANGLE(L=2,B=30):'||AREA1);

AREA_SQ:=PACK1.SQUARE(2);

DBMS_OUTPUT.PUT_LINE('SQUARE(A=2):'||AREA_SQ);

AREA_C:=PACK1.AREA_CIR(2);

DBMS_OUTPUT.PUT_LINE('AREA OF CIRCLE(R=2):'||AREA_C);

FACTORIAL:=PACK1.FACT(5);

DBMS_OUTPUT.PUT_LINE('FACTORIAL(X=5):'||FACTORIAL);

PACK1.CEILING(4.5,CE);

DBMS_OUTPUT.PUT_LINE('CEILING(4.5):'||CE);

END;

AREA OF RECTANGLE(L=2,B=3):6

SQUARE (A=2):4

AREA OF CIRCLE(R=2):12.56

FACTORIAL(X=5):120

CEILING(N=4.5):5

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

EX.NO:13 TO IMPLEMENT THE TRIGGERS

USING EMPLOYEE DETAILS

AIM

To implement the TRIGGER using update command in pl/sql.

TRIGGER

Triggers are stored programs, which are automatically executed or fired when some

events occur.

SYNTAX

Create [or replace ] trigger trigger_name

{before | after |instead of }

{insert [or] | update [or] | delete }

[of col_name]

On table_name

[referencing old as o new as n]

[for each row]

When (condition)

Declare

<Declaration-statements>

Begin

<Executable-statements>

Exception

<Exception-handling-statements>

End;

CREATE A TABLE

SQL>CREATE TABLE TRITAB02(EMP_ID INT,EMP_NAME VARCHAR2(10),

DEPARTMENT VARCHAR2(10),SALARY INT);

Table created.

INSERT A DATA INTO TABLE

SQL>Insert into tritab02(&emp_id,’&emp_name’,’&department’,&salary);

Enter the emp_id :101

Enter the emp_name :Dhinesh

Enter the department :Mechanical

Enter the salary :70000

SQL> select *from tritab02;

EMP_ID EMP_NAME DEPARTMENT SALARY

------------- -------------------- --------------------- --------------

101 Dhinesh Mechanical 20000

102 Dhivahar CS 20000

103 Selva Civil 30000

104 Lohu ECE 30500

105 Ajith Electrical 25030

CREATE TRIGGER USING UPDATE COMMAND

CREATE OR REPLACE TRIGGER TR_EMP

BEFORE UPDATE ON TRITAB02

FOR EACH ROW

WHEN(NEW.EMP_ID>0)

DECLARE

SAL_DIF NUMBER;

BEGIN

SAL_DIF:=:NEW.SALARY-:OLD.SALARY;

DBMS_OUTPUT.PUT_LINE('OLD SALARY:'||:OLD.SALARY);

DBMS_OUTPUT.PUT_LINE('NEW SALARY:'||:NEW.SALARY);

DBMS_OUTPUT.PUT_LINE('SALARY_DIF:'||SAL_DIF);

END;

Trigger created.

SQL> update tritab02 set salary=10000 where emp_name='Dhinesh';

1 row updated.

SQL> set serveroutput on;

SQL> /

old salry:20000

new salry:200000

salary difference:18000

TRIGGER USING INSERT COMMAND

CREATE OR REPLACE TRIGGER TR_INS

AFTER INSERT ON TT

FOR EACH ROW

BEGIN

INSERT INTO TT1(ID,NAME) VALUES(:NEW.ID,:NEW.NAME);

END;

SQL> @D:/ORACLE/TRIINS.SQL

SQL> /

TRIGGER CREATED.

SQL> INSERT INTO TT VALUES(105,'LOHU');

1 row created.

SQL> SELECT *FROM TT;

ID NAME

---------- ----------

105 Lohu

103 Dhinesh

TRIGGER USING DELETE COMMAND

CREATE OR REPLACE TRIGGER TR_DEL

AFTER DELETE ON TT

FOR EACH ROW

BEGIN

DELETE FROM TT1;

END;

SQL> @D:/ORACLE/TRIINS.SQL

SQL> /

Trigger created.

SQL> delete from tt;

1 row deleted.

SIMPLE FORM

AIM

To create a Simple Form.

Step 1: Start→Developer R2.1 and select Form Builder

Step 2:Immediately,you will see the window for Developer/2000 Form Builder for windows

95/NT with the sub-window for Welcome to Form Builder.

Step 3:Select ‘Use the Datablock Wizard’ and click OK—This is the easiest method to

design a New form.

Step 4:You will now see the Welcome to the Datablock Wizard Window.Click Next to

proceed.

Step 5:You will now see the window for the Datablock Wizard.Select Table or View as in

the Figure and click Next.

Step 6:You will now see the window that prompts you to select a table or view—your form

will be created based on this selection.Since no table or view is being shown,click on browse

to look at the list of tables and views in your database.

Step 7:Once you click browse,the connect window will appear.Type in your

username,password and database to connect to the database.

Step 8:You will now see the table’s window.Select current users and tables and click OK.

Step 9:You will now see the list of table’s created in your databse.Select Sude01 and click

OK.

Step 10:You will now see your selected table and its available columns on your screen.Click

on the single right arrow to select the first column to be shown in your form;in this case the

Rollnocolumn.You will now see this column under the database items selected

sub-window.

Step 11:To move the rest of the columns,simply click on the double arrow and this will select

your columns in the databse items.

Step 12:You will now see the congratulations window.Make sure that “Create the data

block,and then call the Layout Wizard” is selected and click on Finish.

Step 13:You will now see the Layout Wizard,prompting you to select the items that you

would like to show in the form.Make sure that the data block selected is students and then

click the double right arrow to move all the columns of the student block from the available

items to the displayed items.Click on the Next to continue.

Step 14:The window with the prompt for the height and width of the items will appear.Click

Nexr to accept the default values.

Step 15:The Layout Wizard will now prompt you to select the layout or view style of your

block.Select Form and click Next.

Step 16:The Layout Wizard will now prompt you to select a title for the form that you are

creating.Type in students Records.Click Next to continue.

Step 17:Congratulations! You have now successfully created your first form.Click Finish

toView your form.

Step 18:You will now see the canvas view of the form that you have created.You can now

add various objects like push buttons,combo boxes and radio buttons to your form to make it

more graphical and user friendly.We will do this in the next lesson.

Step 19:You can now format the form manually.Click on th frame to select it.Then the drag

the frame to make it bigger.

Step 20:You can now space out the data fields to

make your form more visually

appealing.You can do this by simply selecting the data field and dragging it to your

desired area.

EX.NO:1 SIMPLE REPORT

AIM

To create a Simple Report.

Step 1:Invoke the Report Builder,

Click Start→All programs→Oracle Report 9i→Report Builder.

Click OK to create the report using Report Wizard.

Step 2:Click Next to proceed further.Select tabular option.Then Click Next

Step 3:Click Next and type the necessary query.

Step 4:Click next→Connect to the databse.

Step 5:Click >> button to move all columns from available fields to Displayed Field

Box→Next.

Step 6:Accept the default and click Next.

Step 7:Accept the default and click Next.

Step 8:Accept the default and click Next.This completes the design of Tabular Type

Report.Click Finish to come out from Report Wizard to display the result.

Step 9:Save the report in the desired location by clicking File→Save.To Save our Report as

Pdf file then go to the web wizard and then select the pdf znd then click save.

EX.NO:2 BREAK REPORT

AIM

To create a Break Report.

Step 1:You will now see the first screen of the Report Wizard.Type in “List of students by

Major” in the Title box.Next,select the radio button for Group Above in order to create breaks

after record groups for each Major.Now,click Next.

Step 2:You will now see your SQL statement again.You can edit your statement here if you

choose to.At this time we will use the query that we had entered earlier.Click Next.

Step 3:You will now be prompted to select the field that you would like to designate as group

fields.You will find that Oracle has already selected Major.Now,select the next tab,Fields.

Step 4:You will now be asked to select the fields that you will display in your report.We

would like to see all the fields,so click on the double right facing arrows to select all the

fields and click Next.

Step 5:You will noe be prompted to select fields for which you would like to calculate

totals.Let us assume that we have been asked to provide the total number of students in each

major and also the Grand total of the number of students.To do this ,select Rollno,and click

on Count.

Step 6:Your Screen should look like with Count(Rollno) in the Total Column.

Step 7:You can now modify your labels and their width.In this case we have put a colon and

a space after Major and have changed the label for CountRollnoperReport to “Number Of

Students:”and click Next.

Step 8:The final modification involves selecting as appropriate template for the report.In this

case,we will select Corporate2 from the list provided.You are free to select any template of

your choice.Click Finish.

Step 9:Your report should now look like the one in figure.

EX.NO:3 MATRIX REPORT

Step 1:Invoke Report Builder.

Step 2:Select Create the report use the Report Wizard Option→Click OK→Next→Select

Matrix type→click Next.

Step 3:Click Next→Type the necessary query.

Step 4:Transfer corresponding field from Available Fields to Matrix Row Field→Click Next.

Step 5:Transfer the corresponding field from Available Fields→Click Next.

Step 6:Transfer corresponding field from available fields to Matrix Cell Field→Click Next.

Step 7:Click Next→Change the label corresponding to our need and delete the label if it is

not necessary.

Step 8:Click Next twice followed by Finish to display the Report.

EX.NO:4 MASTER DETAIL REPORT

AIM

To design the Master/Detail Report.

PROCEDURE

Step 1:Invoke the Report Designer and select the option BUILD A NEW REPORT

MANUALLY.

Step 2:In that create a query named Q_MASTER with following Select statement.

SQL>SELECT *FROM STUDE01;

Step 3:Create a second query approximately two inches to the right of Q_MASTER.Name the

query as Q_DETAIL,and enter the SELECT statement as follows,

SQL>SELECT *FROM BILL_01;

Step 4:Select the Data Lik tool and then create like between two groups and then edit Data Link

property by double clicking it,if necessary.

Step 5:If you need create a Summary Column in Q_DETAIL group for serial number.The

Summary Column must be the first column in the group.Change its properties as mentioned

below:

Name:Sno

Function:Count

Source:Product

Reset At:Q_MASTER

Data type:Number

Width:3

Step 6:Select Tools→DEFAULT LAYOUT.Click OK.

Step 7:If necessary,align the fields and labels,change the label text and their fonts by editing

its layout.

Step 8:We need to inform Oracle Report that if the texts is larger than the display length,then

the size of the field should be expanded vertically(del_addr property→General layout

tab→Verticals sizing→Expand).

Step 9:Click Apply and OK.

SIMPLE LOGIN FORM USING SQL REPORT

AIM

To Create a Simple Login form using SQL Report.

PROCEDURE

Step 1:Open ORACLE SQL*PLUS and create the table.

Step 2:Insert a record into the table.

Step 3: Start→Developer R2.1 and select Form Builder

Step 4:Immediately,you will see the window for Developer/2000 Form Builder for windows

95/NT with the sub-window for Welcome to Form Builder.

Step 5: Rename the MODULE as LOGIN.

Step 6: Right Click on Data Blocks and click on Layout Editor. Now, you see the Editing

Window.

Step 7: Pick a TEXT_ITEM tool, and draw the two text box on the window.

Step 8: Right click on Text_Item1→Property palette, And change the Name into USER_NAME.

Step 9: Right click on Text_Item2→Property palette, And change the Name into PASSWORD.

Step 10: Pick and draw the two text boxes Fill with USER_NAME and PASSWORD.

Step 11: Pick the Button tool and draw two buttons.

Step 12: Right Click on Button1→Property palette, Change the Name, Label as LOGIN. And

Change Default Button into Yes.

Step 13: Right Click on Button1→Property palette, Change the Name, Label as CANCEL. And

Change Default Button into Yes.

Steps 14: Use the other Rectangle, Oval, etc tools to modify your Login Form.

Step 15: Right Click on LOGIN Button→Smart Triggers→WHEN BUTTON PRESSED.

Step 16: Now PL/SQL window was opened, Here write the ActionScript for the Login Button when it pressed.

Step 17: After finished the ActionScript, Connect with the Oracle Server by pressing Ctrl+J

Step 18: Save and Compile the Program.

Step 19: In Menu bar, Program→Run Form or press Ctrl+R to execute the form.

Step 20: After execution, the Login form window is opened. In this, Enter the User Name and Password and click LOGIN OR CANCEL.

Edutainment

RDBMS Program

GREATEST()

SQL LEFT JOIN

SQL RIGHT JOIN Keyword

No comments:

Post a Comment

Compiler Construction Tool - ANTLR

My Blog List

Followers