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.
Uniquely identified a rows/records in any another database table.
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(*);
OR
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(*)
---------------
7
COUNT THE SPECIFIC PERSON
SQL > SELECT
EMP_NAME COUNT(*) FROM EMP_DET WHERE
EMPO_NAME='DHIVAHAR';
COUNT(*)
--------------
2
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)
-------------------------------
7
4
5
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)
--------------------
99
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
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
------
33
53
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)
-----------------
1
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)
------------------------
30
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)
---------------------
9
(ii)SOFTWARE_MARKETING
SQL > SELECT COUNT(SOFT) FROM EV_SOFT;
COUNT(SOFT)
---------------------
6
(iii)PAPER PRESENTATION
SQL > SELECT
COUNT(PAPER) FROM EV_PAPER;
COUNT(PAPER)
-----------------------
13
(iv)POSTAL PRESENTATION
SQL > SELECT
COUNT(POSTAL) FROM EV_POS;
COUNT(POSTAL)
-------------------------
6
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 :101Enter 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);
SNO DATES CUS_NAME BILL_NO PRO_NAME QTY PRICE TOTAL STOCK
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ---------- ---------
103 23-JAN-17 Mani 12345673 powder 1 25 1 50
SQL >SELECT *FROM BILL1 WHERE PRICE IN (SELECT SUM(PRICE) FROM
BILL2);
SNO DATES CUS_NAME BILL_NO PRO_NAME QTY PRICE TOTAL STOCK
------ -------------- ---------------- ------------- ----------------- ---------- ----------- ---------- ---------
107 25-JAN-17 vicky 12345677 munch 5 288 100 50
SQL >SELECT *FROM BILL1 WHERE TOTAL <(SELECT MAX(TOTAL) FROM
BILL2);
SNO DATES CUS_NAME BILL_NO PRO_NAME QTY PRICE TOTAL STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
101 23-Jan-17 Bhuvana 12345671 Soap 6 28 36 20
102 23-Jan-17 Jeya 12345672 Dry_Milk 2 24 10
103 23-Jan-17 Mani 12345673 Powder 1 114 25
105 26-Jan-16 Selvi 12345675 Rrubber 5 5 25
106 23-Jan-17 Bharathi 12345676 Paper 1 70 16
108 31-Jan-17 Bharath 12345678 Pencil 5 7 35
SQL > SELECT *FROM BILL1 WHERE SNO IN(SELECT COUNT(SNO) FROM
BILL2);
SNO DATES CUS_NAME BILL_NO PRO_NAME QTY PRICE TOTAL STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
10 31-JAN-17 Lavanya 123456710 Chocolate 2 45 2 20
SQL > SELECT *FROM BILL1 WHERE PRO_NAME IN (SELECT PRO_NAME
FROM BILL2 PRO_NAME='SOAP');
SNO DATES CUS_NAME BILL_NO PRO_NAME QTY PRICE TOTAL STOCK
------ ---------------- ---------------- ------------- ----------------- ---------- ----------- ---------- -------
101 23-JAN-17 Bhuvana 12345671 Soap 6 6 36 20
SQL > SELECT *FROM BILL1 WHERE PRICE IN (SELECT PRICE FROM BILL2
WHERE PRICE BETWEEN 20 AND 40);
SNO DATES CUS_NAME BILL_NO PRO_NAME QTY PRICE TOTAL STOCK
------ -------------- ---------------- -------------- ----------------- ---------- ----------- ---------- -------
107 25-JAN-17 vicky 12345677 Munch 5 20 100
SQL > SELECT *FROM BILL1 WHERE PRICE IN(SELECT PRICE FROM BILL2
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_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
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 10th,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
IN
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)
IS
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;
END IF;
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)
IS
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)
IS
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
IS
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;
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)
AS
BEGIN
AREA:=L*B;
END RECT;
//AREA OF SQUARE USING
FUNCTION
FUNCTION SQUARE(A IN NUMBER)
RETURN
NUMBER
AS
BEGIN
RETURN A*A;
END SQUARE;
//AREA OF CIRCLE USING
FUNCTION
FUNCTION AREA_CIR(R IN NUMBER)
RETURN
NUMBER
IS
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
IS
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)
AS
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 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 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.
No comments:
Post a Comment