SQL

What is SQL?

 
1. SQL is Structured Query Language, which is a computer language for storing, manipulating and retrieving data stored in relational database.
2. SQL is the standard language for Relation Database System. All relational database management systems like MySQL, MS Access, and Oracle, Sybase, Informix, postgres and SQL Server use SQL as standard database language. 

Why SQL? 

Allows users to access data in relational database management systems.

Allows users to describe the data.

Allows users to define the data in database and manipulate that data.

Allows embedding within other languages using SQL modules, libraries & pre-compilers.

Allows users to create and drop databases and tables.

Allows users to create view, stored procedure, functions in a database.

Allows users to set permissions on tables, procedures and views 

 

History: 

1970 -- Dr. E. F. "Ted" of IBM is known as the father of relational databases. He described a
relational model for databases.

1974 -- Structured Query Language appeared.

1978 -- IBM worked to develop Codd's ideas and released a product named System/R.

1986 -- IBM developed the first prototype of relational database and standardized by ANSI. The first relational database was released by Relational Software and its later becoming Oracle. 

SQL Commands:

 
The standard SQL commands to interact with relational databases are CREATE, SELECT,
INSERT, UPDATE, DELETE and DROP. These commands can be classified into groups based on their nature. They are:

DDL Commands
DML Commands
DCL Commands
DRL/DQL Commands
TCL Commands 

Data Definition Language (DDL) Commands:
Command                                     Description
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.
TRUNCATE         Truncates the table values without delete table structure

Data Manipulation Language (DML) Commands:
Command                                         Description
INSERT                                         Creates a record
UPDATE                                       Modifies records
DELETE                                       Deletes records

Data Control Language (DCL) Commands:
Command                                         Description
GRANT                                     Gives a privilege to user
REVOKE                                     Takes back privileges granted from user

Data Query Language (DQL) Commands:
Command                                         Description
SELECT                             Retrieves certain records from one or more tables

CREATE:
• The SQL CREATE TABLE statement is used to create a new table.
• Creating a basic table involves naming the table and defining its columns and each column's data type.
 

Syntax:
• Basic syntax of CREATE TABLE statement is as follows:

CREATE TABLE table name (column1 datatype (size), column2 datatype (size),
column3 datatype (size) ... columnN datatype (size), PRIMARY KEY (one or more
columns)); 

Example:
SQL> create table customers (id number (10) not null, name varchar2 (20) not null, age number (5) not null, address char (25), salary decimal (8, 2), primary key (id));

ALTER:
• SQL ALTER TABLE command is used to add, delete or modify columns in an existing table
Syntax:
• The basic syntax of ALTER TABLE to add a new column in an existing table is as follows:

ALTER TABLE table_name ADD column_name datatype;

EX: ALTER TABLE CUSTOMERS ADD phno number (12);

ii) The basic syntax of ALTER TABLE to DROP COLUMN in an existing table is as follows:

ALTER TABLE table_name DROP COLUMN column_name; 

EX: ALTER TABLE CUSTOMERS DROP column phno;

• The basic syntax of ALTER TABLE to change the DATA TYPE of a column in a table is
as follows:

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

Ex: ALTER TABLE customer MODIFY COLUMN phno number(12);

The basic syntax of ALTER TABLE to ADD PRIMARY KEY constraint to a table is as
follows:

ALTER TABLE table_name ADD PRIMARY KEY (column1, column2...);

Ex: ALTER TABLE customer ADD PRIMARY KEY (id,phno);

TRUNCATE:

 
• SQL TRUNCATE TABLE command is used to delete complete data from an existing table.
Syntax:
The basic syntax of TRUNCATE TABLE is as follows:

TRUNCATE TABLE table name;

EX: TRUNCATE TABLE student;

DROP:
SQL DROP TABLE statement is used to remove a table definition and all data, indexes,
triggers, constraints, and permission specifications for that table.

Syntax:

Basic syntax of DROP TABLE statement is as follows:

DROP TABLE table_name;

EX: DROP TABLE student;

 

PL/SQL Introduction

PL/SQL is a block structured language that enables developers to combine the power of SQL with procedural statements. All the statements of a block are passed to oracle engine all at once which increases processing speed and decreases the traffic.

.

Features of PL/SQL:

 

§  PL/SQL is basically a procedural language, which provides the functionality of decision making, iteration and many more features of procedural programming languages.

§  PL/SQL can execute a number of queries in one block using single command.

§  One can create a PL/SQL unit such as procedures, functions, packages, triggers, and types, which are stored in the database for reuse by applications.

§  PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as exception handling block.

§  Applications written in PL/SQL are portable to computer hardware or operating system where Oracle is operational.

§  PL/SQL Offers extensive error checking.

 

Disadvantages of SQL:

 

§  SQL doesn’t provide the programmers with a technique of condition checking, looping and branching.

§  SQL statements are passed to Oracle engine one at a time which increases traffic and decreases speed.

§  SQL has no facility of error checking during manipulation of data

 

Differences between SQL and PL/SQL:

 

 

 

SQL	PL/SQL
SQL is a single query that is used to perform DML and DDL operations.	PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc.
It is declarative, that defines what needs to be done, rather than how things need to be done	PL/SQL is procedural that defines how the things needs to be done.
Execute as a single statement	Execute as a whole block
Mainly used to manipulate data.	Mainly used to create an application
Cannot contain PL/SQL code in it.	It is an extension of SQL, so it can contain SQL inside it

 

 

Structure of PL/SQL Block:

 

 

 

PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other.

Typically, each block performs a logical action in the program. A block has the following structure:

·         Declare section starts with DECLARE keyword in which variables, constants, records as cursors can be declared which stores data temporarily. It basically consists definition of PL/SQL identifiers. This part of the code is optional.

·         Execution section starts with BEGIN and ends with END keyword. This is a mandatory section and here the program logic is written to perform any task like loops and conditional statements. It supports all DML commands, DDL commands and SQL*PLUS built-in functions as well.

·         Exception section starts with EXCEPTION keyword. This section is optional which contains statements that are executed when a run-time error occurs. Any exceptions can be handled in this section

 

How to Write PL/SQL Program

 

·         SET SERVEROUTPUT ON: It is used to display the buffer used by the dbms_output

·         Displaying Output:

The outputs are displayed by using DBMS_OUTPUT which is a built-in package that enables the user to display output, debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers.

·         The slash (/) tells the SQL*Plus to execute the block.

·         Assignment operator (:=): It is used to assign a value to a variable.

 

Using Comments:

Like in many other programming languages, in PL/SQL also, comments can be put within the code which has no effect in the code. There are two syntaxes to create comments in PL/SQL:

·         Single Line Comment: To create a single line comment, the symbol – – is used.

·         Multi Line Comment: To create comments that span over several lines, the symbol /* and */ is used.

Write a PL/SQL code block to find sum and average of three numbers.

 

Declare

a number:=&a;

b number:=&b;

c number:=&c;

sm number;

av number;

Begin

sm:=a+b+c;

av:=sm/3;

dbms_output.put_line('Sum = '||sm);

dbms_output.put_line('Average = '||av);

End;

 

 

Write a PL/SQL code block to find area of circles with radius greater than 3 and less than equal to 7 and store the result in a table with attributes radius and area.

 

Declare

area number(5,2);

radius number(1):=3;

pi constant number(3,2):=3.14;

Begin

while radius<=7

loop

area:=pi*radius*radius;

insert into areas values (radius,area);

radius:=radius+1;

end loop;

End;

 

Write a PL/SQL code block to find factorial of a number.

Declare

n number;

i number;

f number:=1;

Begin

n:=&n;

for i in 1..n

loop

f:=f*i;

end loop;

dbms_output.put_line(n||'! = '||f);

End;

 

 

Write a PL/SQL code block to find reverse of a number.

Declare

N number;

S NUMBER: = 0;

R NUMBER;

K number;

Begin

N: = &N;

K: = N;

loop

exit WHEN N = 0;

S := S * 10;

R := MOD(N,10);

S := S + R;

N := TRUNC(N/10);

end loop;

dbms_output.put_line('THE REVERSED DIGITS OF '||K||' = '||S);

End;

 

 

Write a PL/SQL code block to find greatest of three numbers.

Declare

a number := &a;

b number := &b;

c number := &c;

Begin

if a>b and a>c then

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

elsif b>a and b>c then

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

else

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

end if;

End;

 

Write a PL/SQL code block to generate Fibonacci series.

Declare

a number:= 0 ;

b number:= 1;

c number;

Begin

dbms_output.put(a||' '||b||' ');

for i in 3..10 loop

c := a + b;

dbms_output.put(c||' ');

a := b;

b := c;

end loop;

dbms_output.put_line(' ');

End;