DBMS RECORD PROGRAMS

 

Database Management System (DBMS)

DBMS is a collection of programs for managing data and simultaneously it support different types of users to create, manage, retrieve, update and store information. The vital functions of the database are that it not only manages database engine which is used to access the data but also the database schema which is used to define the logical structure of a database. Now let us explain all the main points regarding Database Management System.

Definition Of Database Management System: A DBMS is defined as system software that enables to store, modify, manipulate and extract data from a database.

Users of DBMS: There are different types of users and can retrieve data on demand using the applications and interfaces provided by DBMS. They are:

Native user

Online user

Application programmers

Sophisticated users

Database administrators

Uses of DBMS: It is used in wide range in many sectors like banking, airlines, universities, human resources, manufacturing and selling and many more. This is used because of the following useful parameters of it:

Not only efficient but also effective in data management

Easy to understand

Strictly secured

Ability to query processing

Sharing of information

Facility of better decision making

Best access to accurate data in searching

Relational Database Management System(RDBMS)

A Database Management System based on Relational Data Model is known as Relational Database Management System (RDBMS). An RDBMS is a DBMS designed specifically for relational databases. Therefore, RDBMSs are a subset of DBMSs. A relational database refers to a database that stores data in a structured format, using rows and columns. This makes it easy to locate and access specific values within the database. It is "relational" because the values within each table are related to each other. Tables may also be related to other tables. The relational structure makes it possible to run queries across multiple tables at once.

While a relational database describes the type of database an RDMBS manages, the RDBMS refers to the database program itself. It is the software that executes queries on the data, including adding, updating, and searching for values. An RDBMS may also provide a visual representation of the data. For example, it may display data in a tables like a spreadsheet, allowing you to view and even edit individual values in the table. Some RDMBS programs allow you to create forms that can streamline entering, editing, and deleting data.

Relational Data Model was developed by Dr. E.F. CODD. He developed the relational data model by taking the concept from Relational Algebra in  June - 1970. Relational Data Model has some 12 Rules which are named after Codd as Codd Rules. According to Codd a package can be called as RDBMS only if it satisfies the Codd Rules.

Most well known DBMS applications fall into the RDBMS category. Examples include Oracle Database, MySQL, Microsoft SQL Server, and IBM DB2. Some of these programs support non-relational databases, but they are primarily used for relational database management.

SQL*Plus

SQL*Plus is essentially an interactive query tool, with some scripting capabilities. It is a non-GUI, character-based tool that has been around since the dawn of the Oracle age. Using SQL*Plus, you can enter an SQL statement, such as a SELECT query, and view the results. You can also execute Data Definition Language (DDL) commands that allow you to maintain and modify your database. You can even enter and execute PL/SQL code. In spite of SQL*Plus’s age and lack of “flash,” it is a workhorse tool used day in and day out by database administrators, developers, and yes, even end users.

SQL*Plus is the first tool that programmers use to develop a query. In spite of all the fancy, GUI-based SQL generators contained in products such as PowerBuilder, Clear Access, and Crystal Reports, we still find it quicker and easier to build up and test a complex query in SQL*Plus before transferring it to whatever development tool we are using.

Uses for SQL*Plus

Originally developed simply as a way to enter queries and see results, SQL*Plus has been enhanced with scripting and formatting capabilities, and can now be used for many different purposes. The basic functionality is very simple. With SQL*Plus, we can do the following:

Issue a SELECT query and view the results

Insert, update, and delete data from database tables

Submit PL/SQL blocks to the Oracle server for execution

Issue DDL commands, such as those used to create, alter, or drop database objects such as tables, indexes, and users

Execute SQL*Plus script files

Write output to a file

Execute procedures and functions that are stored in a database

Structured Query Language (SQL)

Structured Query Language (SQL) is the set of statements with which all programs and users access data in an Oracle database. Application programs and Oracle tools often allow users access to the database without using SQL directly, but these applications in turn must use SQL when executing the user's request.

It lets us to access and manipulate databases. It became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987. Using SQL can we can execute queries against a database and  can retrieve data from a database.

Data Types in SQL

NUMBER[(precision[,scale])] – used to store numeric data. It has precision and scale

                                                     Precision range(p): 1 to 38

                                                     Scale range(s)      : -84 to 127

CHAR[(size)]     - used to store character data within predefined length

                              Storage maximum   - 2000 bytes

VARCHAR2(size) – used to store variable string data within predefined length

                               Storage maximum   - 4000 bytes

NCHAR[(size)]    - used to store national character data within predefined length

                               Storage maximum   - 2000 bytes

NVARCHAR2(size)  - used to store Unicode string data within predefined length.

                                      We must specify the size of NVARCHAR2 datatype.

                                Storage maximum   - 4000 bytes

DATE                 -   used to store to store valid date-time format with fixed length.

                                Starting date from Jan 1, 4712 BC to Dec 31, 9999 AD.

TIMESTAMP       - used  to store valid date (year, month, day) with

                                 time (hour, minute, second).   

 Data Definition Language(DDL)

Note :All DDL commands are auto-committed. That means it saves all the changes permanently in the database.

COMMAND	DESCRIPTION
Create	To Create a new table or user
Alter Options : Add,Modify,Drop & Rename	To change/Edit table’s structure
Drop	To Drop a table/view
Truncate	To Delete all rows in a table (can’t do rollback)
Rename	To Rename a table/view

 

Create

Syntax:

CREATE TABLE <TABLENAME>(COLNAME 1  DATATYPE[(SIZE)],

                                                                 COLNAME 2 DATATYPE[(SIZE)],

                                                                 COLNAME 3 DATATYPE[(SIZE)],

                                                                  ......

                                                                 COLNAME N DATATYPE[(SIZE)]);

Ex:

CREATE TABLE PRODUCT(PID NUMBER(3),PNAME VARCHAR2(15),QTY NUMBER(4));

 

Alter : ADD

Syntax:

ALTER TABLE <TABNAME> ADD <COLNAME> DATATYPE[(SIZE)];

Ex:

ALTER TABLE PRODUCT ADD EMAIL VARCHAR2(15);

 

Alter : MODIFY

Note : With modify option we can change Only Data type/ Only Size/  both data type and

            size.

 Syntax:

ALTER TABLE <TABNAME> MODIFY COLNAME [new DATATYPE][(new SIZE)];

Changing Only Datatype

Ex:

ALTER TABLE PRODUCT MODIFY PNAME CHAR(15);

Changing Only Size

Ex:

ALTER TABLE PRODUCT MODIFY PNAME CHAR(20);

Changing Both datatype and size

Ex:

ALTER TABLE PRODUCT MODIFY PNAME VARCHAR2(10);

 

Alter : DROP

Syntax:

ALTER TABLE <TABNAME> DROP COLUMN <COLNAME>;

Ex:

ALTER TABLE PRODUCT DROP COLUMN EMAIL;

 

Alter : DROP

Syntax:

ALTER TABLE <TABNAME> RENAME COLUMN <OLDCOLNAME> TO <NEWCOLNAME>;

Ex:

ALTER TABLE RENAME COLUMN PNAME TO PRONAME;

 

Truncate:

Note : TRUNCATE command removes all the records from a table. But table's structure remains unchanged.

 

Syntax:

TRUNCATE TABLE <TABLENAME>;

Ex:

TRUNCATE TABLE PRODUCT;

 

Drop:

DROP command completely removes a table from the database. This command will also destroy the table structure and the data stored in it.

Syntax:

DROP TABLE <TABLENAME>;

Ex:

DROP TABLE PRODUCT;

 

Rename:

RENAME command is used to set a new name for any existing table.

Syntax:

RENAME <OLD_TABLENAME> TO <NEW_TABLENAME>;

Ex:

RENAME PRODUCT TO PROD;

 

Data Manipulation Language(DML)

DML commands are used for manipulating the data stored in the table. DML commands are not auto-committed. It means changes are not permanent to database, they can be rolled back.

COMMAND	DESCRIPTION
Insert	To Insert data into table
Update	To Modify the data in a table
Delete	To Delete data in a table

 

Insert:

Note : input data should be enclosed in quotes, if the datatype of a column is char,varchar2 or date.

Syntax1: It is used if user wants to input values to all existing columns in a table.

INSERT INTO <TABLENAME> VALUES(VAL1,VAL2,...VALN);

Ex:

INSERT INTO PRODUCT VALUES(11,'REXONA',5);

 

Syntax2: It is used if user want to input values to  only specified columns.

INSERT INTO <TABLENAME>(COL2...COL5)  VALUES(VAL2,VAL5);

INSERT INTO PRODUCT(PID,QTY) VALUES(33,'LUX');

 

Syntax3: It is used  to input values interactively.

INSERT INTO <TABLENAME> VALUES(&COL1,&COL2....&COLN);

(OR)

Note: If datatype of a column is CHAR/VARCHAR2/DATE then value should be enclosed in quotes.

Ex:

INSERT INTO PRODUCT VALUES(&SNO,'&SNAME','&CITY');

 

Update:

Syntax1: ( This syntax applies/modifes all the records in a table)

UPDATE <TABLENAME> SET <COLUMNNAME>=<VALUE>;

Ex:

UPDATE PRODUCT SET QTY=45;

Syntax2: (This syntax applies/modifies specified columns based on a given condition by using where clause)

UPDATE <TABLENAME> SET <COLUMNNAME>=<VALUE> WHERE <CONDITION>;

Ex:

UPDATE PRODUCT SET QTY=45 WHERE PID=4;

Or

UPDATE PRODUCT SET QTY=45 WHERE PID>24;

Or

UPDATE PRODUCT SET QTY=45 WHERE PNAME=‘LUX’;

Or

UPDATE PRODUCT SET QTY=45 WHERE PNAME LIKE ‘P%’;

Delete:

Syntax1: ( This syntax deletes all the records(Rows) in a table, Structure remains constant.)

DELETE FROM <TABLENAME>;

Ex:

DELETE FROM PRODUCT;

Syntax2: (This syntax deletes specified columns based on a given condition by using where clause)

Ex:
DELETE FROM PRODUCT WHERE  PNAME LIKE ‘%S’;

Or

DELETE FROM PRODUCT WHERE  PNAME LIKE ‘%S%’ or QTY>50;

 

Data Query Language(DQL)

Data query language is used to fetch data from tables based on a given condition.

COMMAND	DESCRIPTION
Select	To Display one or more than one record(data) in a table

 

Syntax1:  (To display all columns in a table)

select <column list> from <tablename>;

Ex: select pid,pname,qty from product;  (or)

select * from product;

Note : In above command * will be replaced by all column names at run time.

syntax2: (To display all columns/specefied columns by using where clause)

select <column list> from <tablename> where <condition>;

EX: select * from product where pid <40;

select  pname,qty from product where pid >59;

About dual table:

In Oracle, DUAL is a table which is created with every installation of Oracle along with the Data Dictionary. It consists of exactly one column named "dummy" and a single row. The value of that row is "X". DUAL is owned by the super user "SYS"  but DUAL can be accessed and used by every user regardless of their role or assigned permissions.

 

 

GROUP BY CLAUSE

The GROUP BY clause is used in a SELECT statement to group rows into a set of summary rows by values of columns or expressions. The GROUP BY clause returns one row per group. The GROUP BY clause is often used with aggregate functions such as AVG(), COUNT(), MAX(), MIN() and SUM(). In this case, the aggregate function returns the summary information per group.

Ex:

SQL> DESC EMPLOYEES;

 Name                                      Null?           Type

  EMPLOYEE_ID              NOT NULL     NUMBER(6)

 FIRST_NAME                                          VARCHAR2(20)

 LAST_NAME                 NOT NULL      VARCHAR2(25)

 EMAIL                            NOT NULL      VARCHAR2(25)

 PHONE_NUMBER                                 VARCHAR2(20)

 HIRE_DATE                   NOT NULL       DATE

 JOB_ID                          NOT NULL       VARCHAR2(10)

 SALARY                                                   NUMBER(8,2)

 COMMISSION_PCT                               NUMBER(2,2)

 MANAGER_ID                                         NUMBER(6)

 DEPARTMENT_ID                                   NUMBER(4)

Query 1 : Display department_id and sum of salaries of all employees in that department.

SQL> select  department_id,sum(salary) from employees group by department_id;

DEPARTMENT_ID      SUM(SALARY)

          100                       51600

           30                       24900

                                      7000

           90                       58000

           20                      19000

           70                      10000

          110                     20300

           50                      156400

           80                     304500

           40                     6500

           60                     28800

           10                      4400

12 rows selected.

SQL> select  department_id,sum(salary) from employees group by department_id ;

DEPARTMENT_ID SUM(SALARY)

          100                  51600

           30                   24900

                                  7000

           90                   58000

           20                  19000

           70                  10000

          110                 20300

           50                  156400

           80                  304500

           40                  6500

           60                 28800

           10                 4400

12 rows selected.

HAVING CLAUSE

A HAVING clause restricts the results of a GROUP BY in a SelectExpression. The HAVING clause is applied to each group of the grouped table, much as a WHERE clause is applied to a select list. If there is no GROUP BY clause, the HAVING clause is applied to the entire result as a single group. The SELECT clause cannot refer directly to any column that does not have a GROUP BY clause.

Ex:

SQL> select  department_id,sum(salary) from employees group by department_id having department_id >70;

DEPARTMENT_ID SUM(SALARY)

          100                     51600

           90                      58000

          110                     20300

           80                      304500

SQL> select  department_id,sum(salary) from employees group by department_id having department_id between 50 and 90;

DEPARTMENT_ID         SUM(SALARY)

           90                           58000

           70                           10000

           50                           156400

           80                           304500

           60                           28800

 

Query 2: Display departments ids and total number of employees working in each department.

SQL> select department_id,count(department_id) "Total Employees" from employees group by department_id;

DEPARTMENT_ID            Total Employees

          100                                6

           30                                  6

                                                 0

           90                                  3

           20                                  2

           70                                  1

          110                                 2

           50                                45

           80                                34

           40                                  1

           60                                  5

           10                                  1

 12 rows selected.

Note : To filter null values in department_id

SQL> select department_id,count(department_id) "Total Employees" from employees group by department_id having department_id is not null;

DEPARTMENT_ID           Total Employees

          100                               6

           30                                6

           90                                3

           20                                2

           70                                1

          110                               2

           50                              45

           80                              34

           40                                1

           60                                5

           10                                1

11 rows selected.

Note: To display output in ascending order of department_id

SQL> select department_id,count(department_id) "Total Employees" from employees group by department_id having department_id is not null order by 1;

DEPARTMENT_ID             Total Employees

           10                                     1

           20                                     2

           30                                     6

           40                                     1

           50                                    45

           60                                    5

           70                                    1

           80                                    34

           90                                     3

          100                                    6

          110                                     2

11 rows selected.

Functions

Oracle SQL provides functions (built-in) and Oracle allows you to define custom functions using PL/SQL.

**We can see all built-in Oracle functions with this query:

select distinct object_name from all_arguments where package_name = 'STANDARD';

Numeric Functions:

ABS                      Returns the absolute value of a number

Ex:

SQL> select abs(-35) from dual;

 ABS(-35)

----------

        35

ROUND             Returns a number rounded to a certain number of decimal places

Ex1:

SQL> select round(123.546) from dual;

ROUND(123.546)

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

           124

Ex2:

SQL> select round(123.564,1) from dual;

ROUND(123.564,1)

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

           123.6

Ex3:

SQL> select round(123.568,2) from dual;

ROUND(123.568,2)

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

          123.57

CEIL                      Returns smallest integer value that is greater than/equal to a number.

Ex1:

SQL> select ceil(123.123) from dual;

CEIL(123.123)

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

          124

FLOOR              Returns the largest integer value that is equal to or less than a number

Ex:

SQL> select floor(123.923) from dual;

FLOOR(123.923)

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

           123

TRUNC                   Returns a number truncated to a certain number of decimal places

Ex:

SQL> select trunc(123.923) from dual;

TRUNC(123.923)

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

           123

EXP                      Returns e raised to the power of number

Ex:
SQL> select exp(2) from dual;

    EXP(2)

----------

 7.3890561

GREATEST            Returns the greatest value in a list of expressions

Ex:

SQL> select greatest(12,45,78,656,888,3) from dual;

GREATEST(12,45,78,656,888,3)

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

                         888

LEAST              Returns the smallest value in a list of expressions

Ex:

SQL> select least(12,45,78,656,888,3) from dual;

LEAST(12,45,78,656,888,3)

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

                        3

MOD                              Returns the remainder of n divided by m

Ex:

SQL> select mod(13,5) from dual;

 MOD(13,5)

----------

         3

POWER             Returns m raised to the nth power

Ex:

SQL> select power(3,4) from dual;

POWER(3,4)

----------

        81

ROWNUM         Returns a number that represents the order that a row is

Ex1:

SQL> select rownum from dual;

    ROWNUM

----------

         1

Ex2:

SQL> select rownum from std;

    ROWNUM

    ----------

         1

         2

         3

    3 rows selected.

SQRT                 Returns the square root of a number

Ex:

SQL> select sqrt(81) from dual;

  SQRT(81)

----------

         9

String/Char Functions:

ASCII                    Returns ASCII number code that represents the specified character

Ex:

SQL> select ascii('A') from dual;

ASCII('A')

----------

        65

SQL> select ascii('k') from dual;

ASCII('K')

----------

       107

CHR                  Returns the character based on the ASCII number code

Ex:

SQL> select chr(97) from dual;

C

-

a

 

SQL> select chr(122) from dual;

C

-

z

 

CONCAT        Allows you to concatenate only two strings together

Ex:

SQL> select concat('Larry','Ellison') from dual;

CONCAT('

--------

LarryEllison

SQL> select concat('Ora','cle') from dual;

CONCAT(

-------

Oracle

 

Concat with ||        Allows you to concatenate two or more strings together

Ex:

SQL> select 'SQL'||'DEV'||'ELO'||'PER' from dual;

'SQL'||'DEV'

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

SQLDEVELOPER

 

LOWER          Converts all letters in the specified string to lowercase

Ex:

SQL> select lower('DJANGO') from dual;

LOWER(

------

django

UPPER             Converts all letters in the specified string to uppercase

Ex:

SQL> select upper('javabeans') from dual;

UPPER('JA

---------

JAVABEANS

LENGTH              Returns the length of the specified string

Ex:

SQL> select length('Enterprise Edition') from dual;

LENGTH('ENTERPRISEEDITION')

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

                         18

INITCAP         Sets the first character in each word to uppercase and the rest to

                              Lowercase

Ex:

SQL> select initcap('oracle cloud infrastructure') from dual;

INITCAP('ORACLECLOUDINFRAST

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

Oracle Cloud Infrastructure

SUBSTR          Allows you to extract a substring from a string

Ex:

SQL> select substr('goldengate',4,3) from dual;

SUB

---

den

INSTR                       Returns the location of a substring in a string

Ex1:

SQL> select instr('dataguard','tag') from dual;

INSTR('DATAGUARD','TAG')

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

                       3

Ex2:

SQL> select instr('datadata','a') from dual;

INSTR('DATADATA','A')

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

                    2

Note : It returns the position of first occurrence of substring in main string.

 

REPLACE       Replaces a sequence of characters in a string with another set of

                         characters

Ex:

SQL> select replace('goldengate','eng','$') from dual;

REPLACE(

--------

gold$ate

 

LTRIM            Removes all specified characters from the left-hand side of a string

Ex:

SQL> select ltrim('  MANGODB') from dual;

LTRIM('

-------

MANGODB

RTRIM            Removes all specified characters from the right-hand side of a string

Ex:

SQL> select rtrim('MARIADB   ') from dual;

RTRIM('

-------

MARIADB

 

TRIM                         Removes all specified characters either from the beginning or the end

                            of a string

Ex:

SQL> select trim('  INFORMIX  ') from dual;

TRIM('IN

--------

INFORMIX

 

LPAD                         Pads the left-side of a string with a specific set of characters

Ex:

SQL> select lpad('SPARC',8,'@') from dual;

LPAD('SP

--------

@@@SPARC

RPAD                         Pads the right-side of a string with a specific set of characters

Ex:

SQL> select rpad('DBTG',6,'$') from dual;

RPAD('

------

DBTG$$

Date/Time Functions

SYSDATE                         Returns the current system date and time on your local database

Ex:

SQL> select sysdate from dual;

SYSDATE

---------

03-AUG-19

SYSTIMESTAMP                 Returns the current system date and time (including fractional

Ex:

SQL> select systimestamp from dual;

SYSTIMESTAMP

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

03-AUG-19 06.05.02.558000 AM +05:30

 

MONTHS_BETWEEN        Returns the number of months between date1 and date2

Ex:

SQL> select months_between(sysdate,'03-aug-2018') from dual;

MONTHS_BETWEEN(SYSDATE,'03-AUG-2018')

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

                                   12

ADD_MONTHS           Returns a date with a specified number of months added

Ex1:

SQL> select add_months('03-aug-2018',3) from dual;

ADD_MONTH

---------

03-NOV-18

Ex2:

SQL> select add_months(sysdate,3) from dual;

ADD_MONTH

---------

03-NOV-19

LAST_DAY                           Returns the last day of the month based on a date value

Ex:

SQL> select last_day(sysdate) from dual;

LAST_DAY(

---------

31-AUG-19

NEXT_DAY                           Returns the first weekday that is greater than a date

Ex:

SQL> select next_day(sysdate,'saturday') from dual;

NEXT_DAY(

---------

10-AUG-19

EXTRACT                             Returns year / month / day for the given date.

Ex1:

SQL> select extract(year from sysdate) from dual;

EXTRACT(YEARFROMSYSDATE)

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

                    2019

Ex2:

SQL> select extract(year from to_date('13-may-2018','dd-mon-yyyy')) from dual;

EXTRACT(YEARFROMTO_DATE('13-MAY-2018','DD-MON-YYYY'))

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

                                                 2018

 

Ex:3

SQL> select extract(month from sysdate) from dual;

EXTRACT(MONTHFROMSYSDATE)

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

                        8

Ex4:

SQL> select extract(day from sysdate) from dual;

EXTRACT(DAYFROMSYSDATE)

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

                      3

Aggregate Functions

Aggregate functions are SQL functions designed to allow you to summarize data from multiple rows of a table or view. Aggregate functions return a single value based on groups of rows, rather than single value for each row. You can use Aggregate functions in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups. The important Aggregate functions are :

Function       Description

AVG           Returns the average value of a column over a set of rows

COUNT        Returns the number of non-NULL values in a column over a set of rows

MAX           Returns the maximum value of a column over a set of rows

MIN            Returns the minimum value of a column over a set of rows

SUM           Sums the values in a column for a set of rows

 

Ex: AVG()

SQL> select avg(35+45+55) from dual;

AVG(35+45+55)

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

          135

SQL> select avg(id) from std;

   AVG(ID)

----------

        40

Ex:COUNT()

Ex:

SQL> select count (*) from std;

  COUNT(*)

----------

        16

Ex:MAX()

SQL> select max(id) from std;

   MAX(ID)

----------

       123

 

Ex:MIN()

SQL> select min(id) from std;

   MIN(ID)

----------

        12

Ex:SUM()

SQL>select sum(salary) “Total Salary” from employees;

Total Salary

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

26500

 

E-R Diagrams

An Entity Relationship Diagram (ER Diagram) is a Diagram which is used to describe the structure of  ER data model. An ER model is a design or blueprint of a database that can later be implemented as a database.

An ER diagram shows the relationship among entity sets. An entity set is a group of similar entities and these entities can have attributes. In terms of DBMS, an entity is a table in database, so by showing relationship among tables and their attributes, ER diagram shows the complete logical structure of a database.

 

The main components of E-R model are:

Entity / Entity Set

Attributes

Relationship

 

Entity / Entity Set

An entity is an object or component of data in the real world or concept about which you want to store the data and an entity set is a group of similar entities.

Attributes

The characteristics of  any entity is known as an attribute. An attribute describes the property of an entity.

Relationship

Actions between entities are represented in the form of relationships.

 

1. Entity

An entity is an object or component of data. An entity is represented as rectangle in an ER diagram. Entities are of two types. They are Strong Entity and Week Entity.

Strong Entity

It is denoted by a single rectangle. A strong entity always has the primary key in the set of attributes that describes the strong entity.

Weak Entity

Weak entity  is denoted by double rectangle. It is an entity that must defined by a foreign key relationship with another entity, as it cannot be uniquely identified by its own or the existence of an entity is dependent on another entity is known as Week Entity.

For example: A bank account cannot be uniquely identified without knowing the bank to which the account belongs, so Bank_Account is a weak entity and Bank is strong entity, since it’s existence is not dependent on any other entity.

  2. Attribute

An attribute describes the property or characteristics of an entity. An attribute is represented as Oval in an ER diagram. There are four types of attributes:

i. Key attribute

ii. Simple attribute

iii. Composite attribute

iv. Single valued attribute

v. Multivalued attribute

vi. Derived attribute

 

i. Key Attribute

A key(Primary Key) attribute can uniquely identify an entity from an entity set. For example, In the Student entity RollNo can uniquely identify a particular student from a group of students. Key attribute is represented by oval same as other attributes however the text of key attribute is underlined.

             

              

ii.Simple attribute

An attribute is classified as a simple attribute if it cannot be partitioned into smaller components. For example, age and gender of a person. A simple attribute is represented by an oval, same as other attributes.

iii. Composite Attribute

An attribute that is a combination of other attributes is known as composite attribute. For example, In Student entity, the student’s  Address is a composite attribute as an Address is composed of other attributes such as Pin, State, Country.

                              

 

iv. Singlevalued Attribute

If an attribute of a particular entity represents single value for each instance, then it is called a single-valued attribute. For example,  Guru, Nammu and Nidhi are the instances of entity ‘Student’ and each of them is issued a separate RollNumber.  A single oval is used to represent this attribute, , same as other attributes.

v. Multivalued Attribute

An attribute that can hold multiple values is known as multivalued attribute. It is represented with double ovals in an ER Diagram. For example – In Student entity a Studnet can have more than one phone numbers so the stu_phone attribute is multivalued.

                                   

vi. Derived Attribute

A derived attribute is one whose value is dynamic and derived from another attribute. It is represented by dashed oval in an ER Diagram. For example – In Student entity age is a derived attribute as it changes over time and can be derived from another attribute date_of_birth.

             

3.Relationship

 It shows the relationship among entities, a diamond is used to symbolically represent a relationship in the ER diagram. Generally a verb in a sentence signifies a relationship.

Ex:

Ø An employee assigned a project.

Ø Teacher teaches a student.

Ø Author writes a book.

There are four types of relationships(Connectivity of a relationship):

a. One to One

b. One to Many

c. Many to One

d. Many to Many

Degree of relationship

It signifies the number of entities involved in a relationship. Degree of a relationship can be classified into following types:

Unary relationship

If only single entity is involved in a relationship then it is a unary relationship. For example, An employee(manager) supervises another employee.

Binary relationship

When two entities are associated to form a relation, then it is known as a binary relationship. For example, A person works in a company. Most of the times we use only binary relationship in an e-r diagram. The teacher-student example shown above signifies a binary relationship.

Ternary Relationships

As the name signifies, a ternary relationship is associated with three entities.

Other types of relationships include quaternary and N-ary, a quaternary relationship is associated with four entities and N-ary, contains N number of entities involved in the relationship.

 

Converting E-R Diagrams to tables

ER diagram is converted into the tables in relational model.

This is because relational models can be easily implemented by RDBMS like  Oracle,MySQL etc.

Rule-01: For Strong Entity Set With Only Simple Attributes

Attributes of the table will be the attributes of the entity set.

The primary key of the table will be the key attribute of the entity set.

  

                  Schema/Table : Student(Roll_no,Name,Gendre)

Roll_no	Name	Gender

 

Rule-02: For Strong Entity Set With Composite Attributes

A strong entity set with any number of composite attributes will require only one table in relational model.

While conversion, simple attributes of the composite attributes are taken into account and not the composite attribute itself.

       

Schema : Student ( Roll_no, First_name, Last_name, House_no, Street , City)

 

Roll_no	First_name	Last_name	House_no	Street	City

Rule-03: For Strong Entity Set With Multi Valued Attributes

A strong entity set with any number of multi valued attributes will require two tables in relational model.

One table will contain all the simple attributes with the primary key.

Other table will contain the primary key and all the multi valued attributes.

   

        

Schema : Student ( Roll_no, Name,City)

Roll_no	Name	City

Schema : Mobile( Roll_no,Mobile_no)

Roll_no	Mobile_no

Rule-04: Translating Relationship Set into a Table

A relationship set will require one table in the relational model.

Attributes of the table are:

Primary key attributes of the participating entity sets

Its own descriptive attributes if any.

Set of non-descriptive attributes will be the primary key.

 

Schema : WorksIn ( Emp_no , Dept_id , since )

Emp_no	Dept_id	since

 

STUDENT MANAGEMENT SYSTEM : E-R Diagram

 

 

 

7.Key Constraints

 

Key Constraints

Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.

 

Types Of Constraints

NOT NULL

UNIQUE

DEFAULT

CHECK

Key Constraints – PRIMARY KEY, FOREIGN KEY

NOT NULL

NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t provide value for a particular column while inserting a record into a table, it takes NULL value by default. By specifying NOT NULL constraint, we can be sure that a particular column(s) cannot have NULL values.

Ex:

CREATE TABLE STUDENT

(

SID NUMBER(3) NOT NULL,

NAME VARCHAR (35) NOT NULL

);

 

 

 

 

UNIQUE

UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a unique constraint, it means that particular column cannot have duplicate values in a table.

Ex:

CREATE TABLE STUDENT

(

SID NUMBER(3) NOT NULL,

NAME VARCHAR (35) NOT NULL,

PHONE NUMBER(10) UNIQUE

);

 DEFAULT

The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table.

Ex:

CREATE TABLE STUDENT

(

SID NUMBER(3) NOT NULL,

NAME VARCHAR (35) NOT NULL,

PHONE NUMBER(10) UNIQUE

DONATION NUMBER DEFAULT 500;

);

 CHECK

This constraint is used for specifying range of values for a particular column of a table. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range.

Ex:

CREATE TABLE STUDENT

(

SID NUMBER(3) NOT NULL CHECK(SID<100,

NAME VARCHAR (35) NOT NULL,

PHONE NUMBER(10) UNIQUE

DONATION NUMBER DEFAULT 500;

);

 

Key Constraints

PRIMARY KEY

Primary key is an attribute or set of attributes that uniquely identifies each record in a table. It must have unique values and cannot contain nulls.

 

CREATE TABLE STUDENT

(

SID NUMBER(3) PRIMARY KEY,

NAME VARCHAR (35) NOT NULL,

PHONE NUMBER(10) UNIQUE,

CITY VARCHAR2(10) CHECK(CITY IN('VIZAG','HYD','BANGLORE')),

DONATION NUMBER DEFAULT 500

);

 

FOREIGN KEY

Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

STUDENT(SID,NAME,PHONE,CITY,DONATION)

COURSE(CID,CNAME,SID) -- contains Foreign Key that refers Primary Key of STUDENT table.

 

CREATE TABLE COURSE

(

CID NUMBER(4) NOT NULL,

CNAME VARCHAR2(15) NOT NULL,

SID NUMBER(3),

CONSTRAINT FK_COURSE

FOREIGN KEY(SID)

REFERENCES STUDENT(SID)

);

 

 

 

 

NORMALIZATION

It is a technique of organizing the data into multiple related tables, to minimize DATA REDUNDANCY. Repetition of similar data at multiple places. Having multiple data in multiple places occupies extra space in the memory. It also causes other issues – anomalies.

There are three types of anomalies that occur when the database is not normalized. These are

1.Insertion Anomaly

2.Updation Anomaly

3.Deletion Anomaly

 

Insertion Anomaly

To insert redundant data for every new row is a data insertion problem or anomaly.

Deletion Anomaly

Loss of related dataset when some other dataset is deleted.

Updation Anomaly

Because of data redundancy  same data in multiple places may not be updated which leads to inconsistent data.

**Normalization breaks the existing table into multiple tables.

Normalization can be achieved in multiple ways. Few of them are

1. 1st Normal Form

2. 2nd Normal Form

3. 3rd Normal Form

4.BCNF(Boyce-Codd Normal Form) or 3.5 Normal Form

 

1NF

There are 4 basic rules that a table should follow to be in 1st Normal Form

1.Each column should contain atomic values

2.A column should contain values that are of the same type.

3. Each column to have a unique name

4. Order in which data is saved doesn't matter.

If all the above rules are satisfied then we say that the given table is in 1NF.

Let us consider STUDENT table

RNO     NAME       SUBJECT

101       SWETHA   OS,CN

102       RAVI           JAVA

103       KAVYA       C,CPP

 

The above table already satisfied 3 rules of 1NF out of 4. Out of 3 students two have opted for more than one subject. we stored the subject name in a single column. To keep the table in 1NF solution is, break the subject values into atomic values.

RNO     NAME       SUBJECT

101       SWETHA   OS

101       SWETHA   CN

102       RAVI           JAVA

103       KAVYA       C

103       KAVYA       CPP

Although few values are getting repeated values for subject column is atomic for each row. Hence the table is in 1NF.

 

2NF

For a table to be in second normal form. It should follow the following rules.

1. It should be in 1NF

2. It should not have any "Partial Dependencies"

 

Dependency or Functional Dependency

If an non-prime attribute (B) is dependent on a composite key (A) but not on any subset of  the composite key, such dependency is known as Functional Dependency.

Partial Dependency

If a nonprime attribute is functionally dependent on part of a candidate key, then it is said to be Partial Dependency.

  

Let us consider STUDENT table

SID         SNAME         RNO              BRANCH            ADDRESS

1             KAVYA          77                   CSE                          AP

2             RAHUL           99                   IT                             HR

3             VIJAY             68                   CSE                         MH

4             GIRI                59                   CSE                         TN

In above table SID is Primary Key

Consider another table SUBJECT(SUBID(PK),SUBNAME)

SUBID      SUBNAME

    1                JAVA

    2                CPP

    3                PYTHON

    4                ORACLE

 

We have STUDENT table  and  SUBJECT table during exams  consider another table SCORE is created.

Table Name : SCORE

SCOREID      SID   SUBID  MARKS       TNAME

        1                1          1              67             NAMMU

        2                1          2              89             GAYATRI

        3                2          1              99             NAMMU

        4                3          4              85             GURU

        5                2          3              64             MOHAN

 

In SCORE table can say that the Primary Key is scoreid.

If we want to fetch marks of student with sid=10 we get two values.

Becuase we dont know for which subject you are asking,  similarly, if we use subid we don’t know for which student.

 

But sid+subid  together makes a meaningful Primary Key and we can fetch all information using it. It is a composite Primary Key.

 

Hence, sid+subid can uniquely identify any row data in SCORE table.

But, in SCORE table the column tname is only dependent on subid and not on sid,which is part of  Primary Key. This is known as PARTIAL DEPENDENCY.

 

To convert above table into 2NF remove tname from SCORE table and move it to subject table. Now the new SCORE table  is

Table Name : SCORE

SCOREID      SID   SUBID  MARKS

        1                1          1              67             

        2                1          2              89             

        3                2          1              99             

        4                3          4              85             

        5                2          3              64

Table Name : SUBJECT           

SUBID      SUBNAME          TNAME

    1                JAVA                 NAMMU

    2                CPP                    GAYATRI

    3                PYTHON           MOHAN

    4                ORACLE            GURU

 

Now, SCORE table is in 2NF

 

3NF

For a table to be in 3NF

1. It should be in 2NF

2. It should not have Transitive Dependency

 

Transitive Dependency

In a table, if a non-prime attribute is dependent on another non-prime attribute and not on prime attribute, such type of dependency is known as Transitive Dependency.

 

Table Name : SCORE

SCOREID      SID   SUBID  MARKS   EXAMNAME TOTMARKS

        1                1          1              67                JAVA                   100

        2                1          2              89                CPP                        75  

        3                2          1              99                JAVA                   100

        4                3          4              85                ORACLE             200

        5                2          3              64                PYTHON             150

 

Here Primary Key is a composite key made up of  SID+SUBID

If we observe EXAMNAME, depends on SID+SUBID, but TOTMARKS is only dependent on EXAMNAME and not on SID+SUBID.

So, EXAMNAME is not part of Primary Key, which is a non-prime attribute, which is Transitive Dependency. So we need to remove it, to convert the table in 3NF.

Move EXAMNAME and TOTMARKS to a new table EXAM

now SCORE table look like this

Table Name : SCORE

SCOREID      SID   SUBID  MARKS   EXAMNAME

        1                1          1              67                JAVA       

        2                1          2              89                CPP             

        3                2          1              99                JAVA       

        4                3          4              85                ORACLE 

        5                2          3              64                PYTHON 

 

Table Name : EXAM

EXAMNAME       TOTMARKS

        JAVA                      100

        CPP                           75  

        JAVA                      100

        ORACLE                200

        PYTHON                150

Now, there is no Transitive Dependency in SOCRE table, Hence it is in 3NF.

 

Set Operators

The set operators are availed to combine information of similar type from one or more than one table. The set operators look similar to SQL joins although there is a big difference. SQL joins tends to combine columns from different tables, whereas SQL set operators combine rows from distinct queries. There are distinct types of set operators in SQL.

 

Types Of Set Operators

Union

Union all

Intersect

Minus

1. Union

This set operator is used to combine the outputs of two or more queries into a single set of rows and columns having different records.

SQL> select deptno from emp union select deptno from dept;

    DEPTNO

    ----------

        10

        20

        30

        40

2. Union All

This set operator is used to join the outputs of two or more queries into a single set of rows and columns without the removal of any duplicates.

SQL> select deptno from emp union all select deptno from dept;

    DEPTNO

     ----------

        10

        20

        10

        30

        10

        20

       30

       40

8 rows selected.

3. Intersect

This set operator is availed to retrieve the information which is common in both tables.

 

 

SQL> select deptno from emp intersect select deptno from dept;

    DEPTNO

    ----------

        10

        20

        30

4. Minus

This set operator is availed to retrieve the information of one table which is not available in another table.

SQL> select deptno from emp minus select deptno from dept;

           no rows selected

SQL> select deptno from dept minus select deptno from emp;

    DEPTNO

    ----------

        40

Selection Operation:

Selection means which row(s) of a table are to be returned. It deals with elimination or selection of required rows. The type of conditions that we are applying on the columns of a table is considered to be selection operation.

Ex1: select col1,col2,co3 from where sno>15;

In above example “where sno>15” is SELECTION part. It filters rows based on the given predicate.

Ex2: SELECT empno,ename,dno,job from Emp WHERE job='CLERK';

In above example “WHERE job='CLERK' ” is SELECTION part. It filters rows based on the given predicate.

 

Projection Operation:

Projection means choosing which columns the query shall return. It deals with elimination or selection of  required columns. What ever we typed in select clause i.e, 'column list' or '*' or 'expressions' that becomes under projection operation.

Ex1: select col1,col2,co3 from where sno>15;

In above example “col1,col2,col3”  is PROJECTION part. It filters columns, if required.

Ex2: SELECT empno,ename,dno,job from Emp WHERE job='CLERK';

 In above example “empno,ename,dno,job”  is PROJECTION part. It filters columns, if required.

 Join Operations

SQL JOINS are used to retrieve data from multiple tables. A SQL JOIN is performed whenever two or more tables are listed in a SQL statement.

The purpose of a join is to combine the data across tables. A join is actually performed by the where clause which combines the specified rows of tables. If a join involves in more than two tables then Oracle joins first two tables based on the joins condition and then compares the result with the next table and so on.

One picture is worth a thousand words. Have a look at the following picture.

consider the Following (dept & emp)Tables

Table1: dept

DEPTNO	DNAME	LOC
10	INVENTORY	HYDERABAD
20	FINANCE	BANGLORE
30	HR	VIZAG

Table 2 : emp

EMPNO	ENAME	JOB	MGR	DEPTNO
111	NAMMU	ANALYST	444	10
222	MOHAN	CLERK	333	20
333	GURU	MANAGER	111	10
444	KRISHNA	ENGINEER	222	40

Creating Table : dept

create table dept(deptno number(2) primary key,dname varchar2(15),loc varchar2(15));

SQL> desc dept

 Name                                      Null?    Type

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

 DEPTNO                                    NOT NULL NUMBER(2)

 DNAME                                                          VARCHAR2(15)

 LOC                                                                 VARCHAR2(15)

Creating Table : emp

create table emp(empno number(3) primary key,ename varchar2(15),job varchar2(15),mgr number(3),deptno number(2),constraint fk_emp foreign key(deptno) references dept(deptno));

SQL> desc emp

 Name                                      Null?    Type

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

 EMPNO                                     NOT NULL NUMBER(3)

 ENAME                                                         VARCHAR2(15)

 JOB                                                                VARCHAR2(15)

 MGR                                                              NUMBER(3)

 DEPTNO                                                       NUMBER(2)

 

Types Of Joins in Oracle

Cross Join(Cartesian Product)

Ø Inner Join

o   Theta Join(also considered as a Non-Equi Join)

o   Equi Join (a Theta Join contains ONLY “=” in select query)

o   Natural Join

o   Self Join

Ø Outer Join

o   Left outer

o   Right outer

o   Full outer

Ø Using clause

Ø On clause

 

Cross Join(Cartesian Product):

The Cross join is the most generic type of join, it generally means connect anything with anything. Its result is the number of records in the first table multiplied by the number of records in the second table, showing the columns altogether.

Syntax:

Select <column list> from <tablename1>,<tablename2>;

(OR)

 select <column list> from <tablename1> CROSS JOIN <tablename2>;

Ex:

select * from dept,emp;

or

select * from dept CROSS JOIN emp;

output:

Theta Join: If we make a query for join using any relational operator,(e.g., =, <, >, >=,<=., etc.), then that join query is considered as Theta join(Non-Equi Join).

Syntax:

SELECT <column list> FROM <tablename1>,<tablename2> WHERE <tablename1.columnname> >=  <tablename2.columnname>

Ex:

select * from dept d,emp e where d.deptno>=e.deptno;

output:

select * from dept d,emp e where d.deptno<=e.deptno;

select * from dept d,emp e where d.deptno>e.deptno;

select * from dept d,emp e where d.deptno<e.deptno;

select * from dept d,emp e where d.deptno!=e.deptno;

 

Equi Join: If we make a query for join using equality operator only, then that join query is considered as Equi join or we can say a Theta Join is an Equi join if it uses only “=” operator in select query.

Syntax:

SELECT <column list> FROM <tablename1>,<tablename2> WHERE <tablename1.columnname> =<tablename2.columnname>

Ex:

select * from dept d,emp e where d.deptno=e.deptno;

output:

 

Natural Join: a type of Equi Join which occurs implicitly by comparing all the same name columns in both tables.

Natural join does not utilize any of the comparison operators. In this type of join, the attributes should have the same name and domain. In this type of join, there should be at least one common attribute between two relations.

It performs selection forming equality on those attributes which appear in both relations and eliminates the duplicate attributes.

Note: here, the join result has only one column for each pair of same named columns. This will perform an Inner Join by default.

Syntax:

Select <column list> from <tablename 1> NATURAL JOIN <tablename 2>;

Ex:

select * from dept NATURAL JOIN emp;

output:

Self Join:

A self join is a join that joins a table with itself. A self join is useful for comparing rows within a table or querying hierarchical data. It uses other joins such as inner join and left join. In addition, it uses the table alias to assign the table different names in the same query.

Consider the following Employee table

employee

empid

fname

lname

email

phone

doj

mgrid

designation

 

The CEO of the company, who does not report to anyone, has a NULL value in the mgrid column. Other employees, who have a manager, have a numeric value in the mgrid column, which indicates the id of the manager.

 

To retrieve the employee and manager data from the employee table, we need to use a self join.

Syntax1:

SELECT a.column_name, b.column_name...

FROM table1 a, table1 b

WHERE a.common_filed = b.common_field;

Syntax2:

SELECT

    column_list

FROM    T t1 INNER JOIN T t2 ON

    join_predicate;

 

Ex:

SELECT (e.fname || '  ' || e.lname) employee,

                (m.fname || '  ' || m.lname) manager,

                 e.designation

FROM  employee e

LEFT JOIN employee m

ON

m.empid = e.mgrid ORDER BY  manager;

 

OUTER JOINS

course(cid,cname) - to be created first  - cid(pk),cname(not null)

student(sid,sname,cid) - to be created next - sid(pk),sname(not null),cid(fk)

course

 

create table course(cid number(3) primary key,cname varchar2(15) not null);

student

create table student(sid number(2) primary key,sname varchar2(15) not null,cid number(3),constraint fk_student foreign key(cid) references course(cid));

 

 

 

SQL> desc course1;

 Name                                      Null?    Type

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

 CID                                       NOT NULL NUMBER(3)

 CNAME                                              VARCHAR2(15)

SQL> desc course1;

 Name                                      Null?    Type

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

 CID                                       NOT NULL NUMBER(3)

 CNAME                                              VARCHAR2(15)

SQL> desc student1;

 Name                                      Null?    Type

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

 SID                                       NOT NULL NUMBER(3)

 SNAME                                              VARCHAR2(15)

 CID                                                NUMBER(3)

 

SQL> select * from course1;

       CID     CNAME

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

       111       MPC

       222       BIPC

       333       CEC

       444       HEC

       555       MEC

5 rows selected.

SQL> select * from student1;

       SID   SNAME           CID

        11    rajesh                 111

        12    suresh                 222

        15    naresh                 222

        16    nammu               555

        17    guru                    444

        18    murty                  111

        22     suguna

        26     varma

8 rows selected.

LEFT JOIN or LEFT OUTER JOIN

select s.sid,s.sname,c.cname from student1 s LEFT JOIN course1 c ON s.cid=c.cid;

 SID SNAME           CNAME

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

   18        murty             MPC

   11        rajesh             MPC

   15        naresh            BIPC

   12        suresh             BIPC

   17        guru                HEC

   16        nammu            MEC

   26        varma

   22        suguna

 

 

RIGHT JOIN or RIGHT OUTER JOIN

select s.sid,s.sname,c.cname from student1 s RIGHT OUTER JOIN course1 c ON s.cid=c.cid;

   SID SNAME           CNAME

    11        rajesh          MPC

    12        suresh          BIPC

    15        naresh          BIPC

    16        nammu        MEC

    17        guru             HEC

    18        murty           MPC

                                    CEC

 

FULL OUTER JOIN

select s.sid,s.sname,c.cname from student1 s FULL OUTER JOIN course1 c ON s.cid=c.cid;

 

  SID SNAME           CNAME

   18     murty           MPC

   11     rajesh           MPC

   15     naresh          BIPC

   12     suresh          BIPC

   17     guru             HEC

   16     nammu         MEC

   26     varma

   22     suguna

                                 CEC

CARTESIAN JOIN or the CROSS JOIN

SQL> select * from student1 s,course1 c;

       SID    SNAME                  CID        CID    CNAME

        11          rajesh                 111        111        MPC

        12          suresh                 222        111       MPC

        15          naresh                 222        111       MPC

        16          nammu               555        111        MPC

        17          guru                    444        111       MPC

        18          murty                  111        111       MPC

        22          suguna                               111       MPC

        26          varma                                111        MPC

        11          rajesh                   111        222        BIPC

        12          suresh                   222       222        BIPC

        15         naresh                   222        222        BIPC

40 rows selected.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

VIEWS

A database view is a searchable object in a database that is defined by a query.  Though a view doesn’t store data, some refer to a views as “virtual tables, We  can query a view like we can a table.  A view can combine data from two or more table, using joins, and also just contain a subset of information.  This makes them convenient to abstract, or hide, complicated queries.

Syntax:

Create view <view_name> as (select query);

SQL> desc emp;

 Name                                      Null?          Type

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

 EMPNO                              NOT NULL    NUMBER(3)

 ENAME                                                      VARCHAR2(15)

 JOB                                                             VARCHAR2(15)

 MGR                                                           NUMBER(3)

 DEPTNO                                                    NUMBER(2)

 

SQL> create view vemp as select * from emp;

View created.

 

SQL> desc vemp;

 Name                                      Null?          Type

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

 EMPNO                            NOT NULL    NUMBER(3)

 ENAME                                                    VARCHAR2(15)

 JOB                                                           VARCHAR2(15)

 MGR                                                         NUMBER(3)

 DEPTNO                                                   NUMBER(2)

 

 

 

 

 

 

 

 

 

 

SCHEMA : EMPLOYEE

1)Create a table employee with attributes eid,ename,city and sal.

SQL> create table employee(eid number(3),ename varchar2(20),city varchar2(15),sal number(8,2));

SQL> desc employee

 Name                                      Null?    Type

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

 EID                                                NUMBER(3)

 ENAME                                              VARCHAR2(20)

 CITY                                               VARCHAR2(15)

 SAL                                                NUMBER(8,2)

 

 

 

2)add a new column email to employee table.

SQL> alter table employee add email varchar2(20);

SQL> desc employee;

 Name                                      Null?    Type

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

 EID                                                NUMBER(3)

 ENAME                                              VARCHAR2(20)

 CITY                                               VARCHAR2(15)

 SAL                                                NUMBER(8,2)

 EMAIL                                              VARCHAR2(20)

 

3)change the size of eid as 5

SQL> alter table employee modify eid number(5);

SQL> desc employee;

 Name                                      Null?    Type

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

 EID                                                NUMBER(5)

 ENAME                                              VARCHAR2(20)

 CITY                                               VARCHAR2(15)

 SAL                                                NUMBER(8,2)

 EMAIL                                              VARCHAR2(20)

 

4)change the data type of ename as char

SQL> alter table employee modify ename char(20);

SQL> desc employee;

 Name                                      Null?    Type

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

 EID                                                NUMBER(5)

 ENAME                                              CHAR(20)

 CITY                                               VARCHAR2(15)

 SAL                                                NUMBER(8,2)

 EMAIL                                              VARCHAR2(20)

 

5)change the city column name as PLACE

SQL> alter table employee rename column city to place;

 

 

SQL> desc employee;

 Name                                      Null?    Type

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

 EID                                                NUMBER(5)

 ENAME                                              CHAR(20)

 PLACE                                              VARCHAR2(15)

 SAL                                                NUMBER(8,2)

 EMAIL                                              VARCHAR2(20)

 

6)add a new column dob.

SQL> alter table employee add dob date;

SQL> desc employee;

 Name                                      Null?    Type

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

 EID                                                NUMBER(5)

 ENAME                                              CHAR(20)

 PLACE                                              VARCHAR2(15)

 SAL                                                NUMBER(8,2)

 EMAIL                                              VARCHAR2(20)

 DOB                                                DATE

 

7) Insert 5 records into employee table.

SQL> insert into employee values(11,'lavanya','hyd',9999.89,'lavanya@gmail.com','01-jul-1987');

SQL> insert into employee values(12,'sandilya','vizag',8888.84,'sandilya@gmail.com','06-mar-1989')

SQL> insert into employee values(13,'karunya','bangalore',9999.94,'karunya@gmail.com','25-may-1976

SQL> insert into employee values(14,'vijaya','chennai',7777.24,'vijaya@gmail.com','15-dec-1996');

SQL> insert into employee values(15,'mourya','vizag',6666.43,'mourya@gmail.com','13-oct-2001');

 

 

 

 

 

8)display employee name and age of all employees.

SQL> select ename,round((sysdate-dob)/365) age from employee;

ENAME                   AGE

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

lavanya                      32

sandilya                     31

karunya                      43

vijaya                          23

mourya                       18

 

9)display employee id ,age and sal of all employees whose name starts with 's'

SQL> select eid,round((sysdate-dob)/365) age,sal from employee where ename like 's%';

 

     EID        AGE        SAL

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

        12         31    8888.84

 

10)modify all records in employee table by giving 1500 hike in their salary.

SQL> select eid,ename,sal from employee;

       EID ENAME                       SAL

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

        11 lavanya                 9999.89

        12 sandilya                8888.84

        13 karunya                 9999.94

        14 vijaya                  7777.24

        15 mourya                  6666.43

SQL> update employee set sal=sal+1500;

5 rows updated.

SQL> select eid,ename,sal from employee;

       EID ENAME                       SAL

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

        11 lavanya                11499.89

        12 sandilya               10388.84

        13 karunya                11499.94

        14 vijaya                    9277.24

        15 mourya                  8166.43

11)display all records.

SQL> select * from employee;

 EID    ENAME         PLACE           SAL     EMAIL                       DOB

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

11       lavanya        hyd             11499.89  lavanya@gmail.com   01-JUL-87

12       sandilya      vizag           10388.84   sandilya@gmail.com  06-MAR-89

13       karunya       bangalore   11499.94   karunya@gmail.com  25-MAY-76

14       vijaya          chennai       9277.24    vijaya@gmail.com      15-DEC-96

15       mourya       vizag           8166.43    mourya@gmail.com    13-OCT-01

 

12)modify all records in employee table by giving 2.5% hike, whose salary is <10000

SQL> update employee set sal=sal+0.025*sal where sal<10000;

2 rows updated.

SQL> select * from employee;

 EID    ENAME         PLACE           SAL     EMAIL                       DOB

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

11       lavanya        hyd             11499.89  lavanya@gmail.com   01-JUL-87

12       sandilya      vizag           10388.84   sandilya@gmail.com  06-MAR-89

13       karunya       bangalore   11499.94   karunya@gmail.com  25-MAY-76

14       vijaya          chennai       9509.17    vijaya@gmail.com      15-DEC-96

15       mourya       vizag           8370.59    mourya@gmail.com    13-OCT-01

 

13)remove all records in table

SQL> delete from employee;

5 rows deleted.

SQL> select * from employee;

no rows selected

 

14)restore deleted records

SQL> rollback;

Rollback complete.

SQL> select * from employee;

 EID    ENAME         PLACE           SAL     EMAIL                       DOB

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

11       lavanya        hyd             11499.89  lavanya@gmail.com   01-JUL-87

12       sandilya      vizag           10388.84   sandilya@gmail.com  06-MAR-89

13       karunya       bangalore   11499.94   karunya@gmail.com  25-MAY-76

14       vijaya          chennai       9509.17    vijaya@gmail.com      15-DEC-96

15       mourya       vizag           8370.59    mourya@gmail.com    13-OCT-01

 

15)change employee table name as emp.

SQL> rename employee to emp;

Table renamed.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PL/SQL

It is a Oracle's procedural language for SQL. It is used to create block that can be stored in database(stored procedures) or to implement business rules(triggers) or perform any procedural operation(anonymous block)

PL/SQL program units are generally categorized as follows:

--Anonymous Blocks  -- not having a specific name

-- Stored Procedurs     -- have a specific name

 

Subprograms

They are named PL/SQL blocks that are stored in the database.

As they are named and stored, we can invoke them whenever we want.

We can declare them as procedures or as functions.

We generally use a procedure to perform an action and a function to compute and return a value.

Structue of PL/SQL block

PL/SQL programs are written as blocks. Block allows you to group logically related statements and declarations. PL/SQL block is consisting of the following these parts:

--Declarative part

--Executable part

--Exception-handling part

 

[Declare

     declaration of variable

     declaration of cursor

     declaration of exception ]

BEGIN

     executable commands

[EXCEPTION

     exception handlers ]

END;

Cmments in PL/SQL block is of two ways

1. Preceding the comment with two hyphens(--)

    Ex: --This is single line comment

2. Starting the comment with /* and ending it with */

    Ex: /* This is multiline comment */

 

PL/SQL Datatypes

PL/SQL provides a verity of predefined datatypes, which can be divided into four categories

Scalar         : NUMBER,CHAR,VARCHAR2, DATE, BOOLEAN

Composite : RECORD,TABLE and VARRAY

Reference  : REF CURSOR, REF Object type

LOB          : BFILE,BLOB,CLOB and NCLOB

 

PUT & PUT_LINE procedures

Both of these procedures are used to display a NUMBER,VARCHAR or DATE type value. PUT allows us to put multiple pieces that make up a line. PUT_LINE puts the given data followed by end-of-line character.

These procedures are to be prefixed with DBMS_OUTPUT as they are present in that package.

 

/*Program To Perform Arithmetic Operations */[R1]

set serveroutput on

clear screen

declare

x number:=&x;

y number:=&y;

r number;

begin

r:=x+y;

dbms_output.put_line('Arithmatic Operations');

dbms_output.put_line('Sum Of Numbers : '||r);

r:=x-y;

dbms_output.put_line('Difference Of Numbers : '||r);

r:=x*y;

dbms_output.put_line('Product Of Numbers : '||r);

r:=x/y;

dbms_output.put_line('Quotient Of Numbers : '||r);

r:=mod(x,y);

dbms_output.put_line('Remainder Of Numbers : '||r);

end;

/

Output:

Enter value for x: 5

Enter value for y: 7

Arithmatic Operations

Sum Of Numbers : 12

Difference Of Numbers : -2

Product Of Numbers : 35

Quotient Of Numbers : .7142857142

Remainder Of Numbers : 5

 

Contro Structures

if statement is used to test condition and take action based on the result of the condition.

syntax:

if condition1 then

   statemtnt1;

[elsif condition2 then

    statement2;

[else

   statement3;]

end if;

 

/* Program To Compare Three Numbers */[R2]

set serveroutput on

clear screen

declare

a number:=&x;

b number:=&y;

c number:=&z;

begin

if(a>b and a>c) then

   dbms_output.put_line(a||'  is Largest' );

elsif (b>a and b>c) then

dbms_output.put_line(b||'  is Largest');

else

dbms_output.put_line(c||'  is Largest');

end if;

end;

/

Output1:

Enter value for x: 45

Enter value for y: 7

Enter value for z: 8

45  is Largest

Output2:

Enter value for x: 12

Enter value for y: 55

Enter value for z: 34

55  is Largest

Output3:

Enter value for x: 12

Enter value for y: 55

Enter value for z: 77

77  is Largest

 

CASE Expression (similar to swtich in other languages)

It is used to select one of the alternatives based on the value of a selector.

Syntax:

CASE selector

           WHEN expression1 THEN result1

           WHEN expression2 THEN result2

            . . .

           WHEN expressionN THEN resultN

           [ ELSE result ]

END;

SELECT command with INTO option

SELECT command is used only to retrive the data. Storing and using data is to be done explicitly. So Oracle provided INTO clause with SELECT command that is used to specify the variable(s) into which the value(s) retrievd must be copied.

 

Looping Structures

1.LOOP..END LOOP

It is used to repeatedly execute a set of statements indefinetely. We need to use EXIT to terminate loop.

Syntax:

LOOP

     statements;   //infinite loop

     [EXIT [condition]]

END LOOP;

WHILE LOOP

It executes a series of statements as long as the given condition is true.

Syntax:

WHILE condition LOOP

     statements;

END LOOP;

/*Program to Generate Even Numbers, Squares and both of their SUM in a given range */[R3]

set serveroutput on

clear screen

declare

  s number(3):=&StartRange;

  e number(3):=&EndRange;

  sevn number:=0;

  ssevn number:=0;

begin

  while s<=e

  loop

    if(mod(s,2)=0) then

        dbms_output.put_line('Number: '||s||'            Square :'||s*s);

         sevn:=sevn+s;

         ssevn:=ssevn+s*s;

     end if;      

    s:=s+1;

  end loop;

dbms_output.put_line('Sum Of EvenNumbers :'||sevn||'  Even Number Square Sum : '||ssevn);

end;

/

Output:

Enter value for startrange: 2

Enter value for endrange: 12

Number: 2            Square :4

Number: 4            Square :16

Number: 6            Square :36

Number: 8            Square :64

Number: 10            Square :100

Number: 12            Square :144

Sum Of EvenNumbers :42  Even Number Square Sum : 364

FOR LOOP

This looping structue is best suited to cases where we have to repeatedly execute a set of statements by varying a variable from one value to another.

Syntax:

FOR counter IN [REVERSE] lowerrange..upperrange

LOOP

  statements;

END LOOP;

 

Note: values for lowerrange and upperrange may also be expressions REVERSE option is used to start loop at upperrange and goes to lowerrange.

/* Program To Check Given Number is Prime or Not*/[R4]

set serveroutput on

clear screen

declare

n number:=&n;

c number:=0;

begin

for i in 2..n/2

 loop

  if(mod(n,i)=0) then

    c:=c+1;

  end if;

 end loop;

if(c=0) then

dbms_output.put_line('Given Number '||n||' is Prime');

else

dbms_output.put_line('Given Number '||n||' is Not a Prime');

end if;

end;

/

Output1:

Enter value for n: 59

Given Number 59 is Prime

Output2:

Enter value for n: 456

Given Number 456 is Not a Prime

 

/* Program To Display Prime Numbers between 2 to the Given Number */[R5]

set serveroutput on

clear screen

declare

n number:=&StrtNo;

c number;

begin

for o in 2..n

loop

   c:=0;

   for i in 1..o

   loop

   if(mod(o,i)=0) then

     c:=c+1;

   end if;

   end loop;

if(c=2) then

dbms_output.put_line(o);

end if;

end loop;

end;

/

Output:

Enter value for strtno: 5

2

3

5

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

CURSORS

When a SQL statement is processed, Oracle creates a memory area(work area) known as context area, to execute SQL commands and store processing information. A cursor is a pointer to this context area, PL/SQL allows us to access this area, through a name. Cursors used in Oracle are of two types.

 They are

 

Implicit cursor

It is automatically generated by Oracle when an SQL statement is executed, It is used to store information about most recently executed SQL command. These are created by default to process the  DML statements like INSERT, UPDATE, DELETE etc. are executed. SQL is the name used to access this cursor.

 

Orcale provides some attributes known as Implicit cursor's attributes to check the status of DML operations. Some of them are:

 %FOUND - It returns TRUE if the most recent fetch operation fetched a record successfully.

 %NOTFOUND - It returns 'TRUE' if the most recent fetch operation could not able to fetch any record.

 %ROWCOUNT -It returns 'TRUE' if the given cursor is already opened, else it returns 'FALSE'

 %ISOPEN - It returns the numerical value. It gives the actual count of records that got affected by the DML commands.

Ex:create a table customer with attributes ID,NAME,AGE,ADDRESS & SALARY. Fill the data as follows and then execute PL/SQL program given below.

 

ID     NAME       AGE     ADDRESS             SALARY

1        Santhi                     23          Vizag                    20000

2        Vijay             22         Khammam            22000

3        Mohan          24          Bangalore             24000

4        Usha               25         Vijayawada            26000

5        Soumya         21                   Hyderabad            28000

6        Kiran             20         Kakinada              30000

 

Query:Update  customers table by increasing salary of each customer by 5000.

 

/*Program to Demonistrate Implicit Cursor Attributes  */[R6]

set serveroutput on

clear screen

declare

total_rows number(2);

stat boolean;

begin

   update customers

   set salary= salary + 5000 where id>3; 

   if sql%notfound then 

      dbms_output.put_line('No Customers Updated'); 

   elsif sql%found then 

      total_rows := sql%rowcount; 

      dbms_output.put_line( total_rows || ' Customers Updated '); 

   end if;

   if(sql%isopen) then

   dbms_output.put_line('TRUE');

   else

   dbms_output.put_line('FALSE');

   end if;

end;

/

Explicit Cursor

The Explicit cursors are defined by the programmers to gain more control over the context area. It is used to store result of a multi-row query. The data retrieved is stored in this type of cursor for further process. These cursors should be defined in the declaration section of the PL/SQL block. It is created on a SELECT statement which returns more than one row.

 

General Syntax of explicit cursor : CURSOR cursor_name IS select_statement;

We need to follow the steps given below while working with Explicit Cursors

1.Declare the cursor to initialize in the memory.

2.Open the cursor to allocate memory.

3.Fetch the cursor to retrieve data.

4.Close the cursor to release allocated memory.

 

Declare cursor : It defines the cursor with a name and the associated SELECT statement.

Syntax :

CURSOR name IS 

 SELECT statement;

 

Open the cursor: It is used to allocate memory for the cursor and make it available to fetch the rows returned by the SQL statements into it.

Syntax:

OPEN cursor_name;

 

Fetch the cursor: It is used to access one row at a time. We can fetch rows from a cursor once it is opened.

Syntax:

FETCH cursor_name INTO variable_list; 

 

Close the cursor: It is used to release the allocated memory.

Syntax:

Close cursor_name;

 

 /*Program to Demonistrate Explicit Cursor */[R7]

set serveroutput on

clear screen

declare

    c_name customers.name%type; 

   c_age customers.age%type; 

   c_salary customers.salary%type;

   CURSOR c_customers is 

          select id,name,age,salary from customers where name like 's%'; 

  begin 

   OPEN c_customers;

   dbms_output.put_line('NAME    AGE   SALARY'); 

   loop 

      fetch c_customers into c_id, c_name, c_age,c_salary; 

      exit when c_customers%notfound;  

      dbms_output.put_line(c_name || '    ' || c_age || '    ' || c_salary); 

   end loop; 

   close c_customers; 

end; 

/ 

 

Output:

NAME       AGE        SALARY

santhi            23            20000

soumya       21            28000

Database Triggers

Database trigger is a PL/SQL block that is executed on an event in the database. The event is raised to particular data manipulation of a table such as inserting, updating or deleting a row of a table.

 

Triggers may be used for any of the following:

To implement complex business rules which cannot be implemented by integrity constraints.

To audit the process, i.e, keep track of changes made to a table.

To automatically perform an action when another concerned action takes place. For example, updating a table whenever there is an insertion of a row into another table.

 

Types Of Triggers

Triggers are classified into different categories based on whether they are executed on row or statement, where they are fired before or after the event.

 

Row-level Trigger

A row trigger is fired once for each row that is affected by DML command. For example, if an update command updates 100 rows then row-leve trigger is fired 100 times for each row.

Statement-level Trigger

A statement trigger is fired only for once for a DML statement irrespective of the number of rows affected by the statement.

Before Triggers

While defining a trigger, you can specify whether the trigger is to be fired before the command(INSERT,UPDATE and DELETE) is executed or after the command is executed.

 

 

After Trigger

After triggers are fired after the triggering action is completed. For example, if after trigger is associated with INSERT command then it is fired after the row is inserted into the table.

 

Creating a Database Trigger

The following details are to be given at the time of creating a trigger.

Name of Trigger

Table to be associated with

When trigger is to be fired – before or after

Command that invokes the trigger – INSERT, UPDATE or DELETE

Whether row-level trigger or statement-level

Condition to filter rows

PL/SQL block that is to be executed when trigger is fired.

 

Create table : Employees

 

SQL> desc employees

 Name                                      Type

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

 EID                                         NUMBER(6)

 ENAME                                 VARCHAR2(20)

 EMAIL                                   VARCHAR2(25)

 HIRE_DATE                          DATE

 SALARY                                NUMBER(8,2)

 

Ex: Row-level Trigger

/*Write a ROW-level  trigger to check hire_date column of employees table during insertion and updating.If  hire_date is greater than sysdate trigger raises application error.*/[R8]

create or replace trigger trg_emp_hdchk

before insert or update of hire_date

on employees

for each row

begin

if :new.hire_date>sysdate then

    raise_application_error(-20200,'Hire date cannot be after sysdate');

end if;

end;

/

 

Output:

SQL> @trg1.sql

Trigger created.

 

Verifying Trigger is firing or not

SQL> update employees set hire_date=sysdate+1 where employee_id=120;

update employees set hire_date=sysdate+1 where employee_id=120

       *

ERROR at line 1:

ORA-20200: Hire date cannot be after sysdate

ORA-06512: at "HR.TRG_EMP_HDCHK", line 3

ORA-04088: error during execution of trigger 'HR.TRG_EMP_HDCHK'

 

Ex: Statement-level Trigger

/*Write a Statement-level trigger that prevents any changes made to employees table on Sunday.*/[R9]

 

create or replace trigger trg_emp_sun

before insert or update or delete

on employees

begin

if to_char(sysdate,'d')=1 then

    raise_application_error(-20210,'No changes can be made on sunday');

end if;

end;

/

 

Ouputs:

SQL> insert into employees(employee_id) values(121212);

insert into employees(employee_id) values(121212)

         *

ERROR at line 1:

ORA-20210: No changes can be made on sunday

ORA-06512: at "HR.TRG_EMP_SUN", line 3

ORA-04088: error during execution of trigger 'HR.TRG_EMP_SUN'

 

SQL> update employees set employee_id=12 where employee_id=1212;

update employees set employee_id=12 where employee_id=1212

         *

ERROR at line 1:

ORA-20210: No changes can be made on sunday

ORA-06512: at "HR.TRG_EMP_SUN", line 3

ORA-04088: error during execution of trigger 'HR.TRG_EMP_SUN'

 

SQL> delete from employees;

delete from employees

         *

ERROR at line 1:

ORA-20210: No changes can be made on sunday

ORA-06512: at "HR.TRG_EMP_SUN", line 3

ORA-04088: error during execution of trigger 'HR.TRG_EMP_SUN'

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

PROCEDURE

A stored procedure is a  subprogram unit that consists of a group of PL/SQL statements, stored in the database with a name. Each procedure in Oracle has its own unique name by which it can be referred. This subprogram unit is stored as a database object. A stored procedure can be clled from client programs such as PL/SQL, Java programs, PHP programs etc.

 

Advantages of stored subprograms:

Reduced network traffic : as only name is passed from client to server.

Better performance : as procedure is loaded into memory it stays in memory so that subsequent invocations can make use of procedure in memory. Procedures are stored in compiled format, which make them run faster.

Easy maintanance : as a change of business logic needs only chagnge to procedure in the database. All client applications can immediately use new business logic.

Security : as it is possible to grant privillage to users to execute procedure even though no privillage is granted on table, there is no need to grant privillages on the table to others.

 

PARAMETER MODES

When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:

IN - An IN parameter is read-only. You can reference an IN parameter inside a procedure(or function), but you cannot change its value. Oracle uses IN as the default mode. It means that if you don’t specify the mode for a parameter explicitly, Oracle will use the IN mode.

OUT - An OUT parameter is writable. Typically, you set a returned value for the OUT parameter and return it to the calling program. Note that a procedure ignores the value that you supply for an OUT parameter.

IN OUT - An INOUT parameter is both readable and writable. The procedure can read and modify it.

Syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name

    [ (parameter [,parameter]) ]

IS

    [declaration_section]

BEGIN

    executable_section

[EXCEPTION

    exception_section]

END [procedure_name];

Note : In above syntax OR REPLACE option allows you to overwrite the current procedure with the new code.

 

Declaration_section

In this part, you can declare variables, constants, cursors, etc. Unlike an anonymous block, a declaration part of a procedure does not start with the DECLARE keyword.

Executable_section

This part contains one or more statements that implement specific business logic. It might contain only a NULL statement.

Exception-handling part

This part contains the code that handles exceptions

SHOW ERRORS

This command can be used to display errors that were detected by compiler at the time of creating stored procedure. This command has to be used at SQL prompt.

 

/* Create a procedure to display Full details of current system date.*/[R10]

set serveroutput on

create or replace procedure dispdate

is

cdn date;

cursor c1 is

   select sysdate from dual;

begin

  open c1;

  fetch c1 into cdn;

  if c1%found then

      dbms_output.put_line('Details Of  Todays Date Is :');

      dbms_output.put_line('Week Name :'||to_char(cdn,'Day'));

     dbms_output.put_line('Day Number Is :'||to_char(cdn,'DD'));

     dbms_output.put_line('Month Name Is :'||to_char(cdn,'Month'));

     dbms_output.put_line('Year Is :'||to_char(cdn,'YYYY'));

  else

     dbms_output.put_line('No Records Found');

  end if;

  close c1;

end;

/

Output:

Details Of  Todays Date Is :

Week Name :Monday

Day Number Is :23

Month Name Is :September

Year Is :2019

Ex3:/* Create a procedure to display Student Details for a given Student Id. */[R11]

Note: Create table std(id,name,city,dob) then create procedure.

set serveroutput on

create or replace procedure std_dtls(std_id number)

is

sdetails std%ROWTYPE;

cursor c

is

select * into sdetails from std where id>=std_id;

begin

open c; 

-- get student details based on given student id

loop

fetch c into sdetails; 

dbms_output.put_line(sdetails.name || ' ' ||sdetails.city|| '   '||sdetails.dob);

exit when c%notfound;

end loop;

end;

/

Output:

SQL> exec std_dtls(4)

varma    hyd           12-JUL-07

vanaja    vizag        17-JUN-19

suresh    kkd           11-JLY-15

ramesh   hyd           13-AUG-19  

varma     banglore   12-JUL-07

mahesh   chennai     21-FEB-18

 

 

 

 

 

 

 

 

 

FUNCTIONS

A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a single value.

syntax:

CREATE [OR REPLACE] FUNCTION function_name [parameters]

RETURN return_datatype 

IS 

[local Declaration_section ]

BEGIN 

       Executable statements

       Return return_variable; 

[EXCEPTION 

        exception section 

       Return return_variable;] 

END;

/*Create a function that takes employee id and returns salary of given id.*/[R12]

clear screen

set serveroutput on

create or replace function getsal(eid number)

return number

is

sal employees.salary%TYPE;

begin

select salary into sal from employees where employee_id=eid;

return sal;

end;

/

 

Now we have to call the above function as follows

SQL> begin

  2  dbms_output.put_line('Employee Salary Is : '||getsal(199));

  3  end;

  4  /

Output:

Employee Salary Is : 2600

/*Create a function that accept Date Of Birth in DD-MON-YYYY format and return age.*/[R13]

clear screen

set serveroutput on

create or replace function getage(dob IN date)

return number is

age number;

begin

     age:=TRUNC((SYSDATE - dob)/365.25);

 return age;

end;

/

Output:

SQL> begin

  2  dbms_output.put_line('Employee Age Is : '||getage('14-Mar-1993'));

  3  end;

  4  /

Employee Age Is : 26