SQL PL/SQL Programming

Jitendra JD Patel

Description

This programming book is specially written for those who are interested in understanding Structured Query Language and PL-SQL concepts in the Computer Engineering and Information technology field and wants to gain enhance knowledge about power of SQL Language in Relational Database Management System Development.

The manual covers practical point of view in all aspects of SQL and PL/SQL including DDL,DML,DCL sublanguages, also there are practices for Views, Group by, Having Clause. All PL-SQL concepts like Condition and Loop Structures, Functions and Procedures, Cursor, Triggers, Locks are illustrated using best examples.

Sujets

• Langages
• Computers
• Informatique
• SQL

Informations

• Publié par: eBookIt.com
• Date de parution: 21 février 2013
• Nombre de lectures: 5
• EAN13: 9781456612115
• Langue: English

Extrait

SQL PL/SQL Programming
By Jitendra Patel
Overview

This programming book is specially written for those who are interested in understanding Structured Query Language and PL-SQL concepts in the Computer Engineering and Information technology field and wants to gain enhance knowledge about power of SQL Language in Relational Database Management System Development.
The manual covers practical point of view in all aspects of SQL and PL/SQL including DDL,DML ,DCL sublanguages, also there are practices for Views, Group by, Having Clause. All PL-SQL concepts like Condition and Loop Structures, Functions and Procedures, Cursor, Triggers, Locks are illustrated using best examples.
Copyright © 2012 Jitendra Patel
No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages resulting from the use of the information contained herein.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as possible, but no warranty or fitness is implied. The information provided is on an "as is" basis. The authors and the publisher shall have neither liability nor responsibility to any person or entity with respect to any loss or damages arising from the information contained in this book.
Contents
INTRODUCTION TO SQL .. 1
FEATURES OF SQL: 1
SQL COMMANDS .. 2
DDL .. 2
DML .. 2
DCL .. 3
TCL .. 3
SYNTAX’S OF COMMANDS .. 3
CREATE,INSERT , UPDATE, DELETE, RENAME, TRUNCATE, ON TABLES . 5
CREATING TABLES WITH CONSTRAINTS .. 8
(NOT NULL) 8
(UNIQUE) 9
(PRIMARY KEY) 10
(CHECK) 11
( FOREIGN KEY ) 12
CREATING AND DROPING OF VIEWS .. 14
DCL AND TCL COMMANDS .. 16
CREATING A USER .. 16
IMPLEMENTING COMMINT, SAVEPOINT, ROLLBACK .. 16
DRL-DATA RETRIEVAL LANGUAGE .. 21
IMPLEMENTING SELECT COMMANDS . 21
SUBQUERIES (APPLYING IN, ALL, ANY, EXISTS, NOT EXISTS,UNION,INTERSECT,MINUS) 26
SQL In-Built FUNCTIONS: 33
CONVERSION FUNCTIONS(TO_CHAR) 36
STRING FUNCTIONS . 37
DATE FUNCTIONS . 38
NUMBER FUNCTIONS . 40
INTRODUCTION TO PL/SQL .. 43
PL/SQL BLOCK .. 43
SYNTAX's of CONTROL STATEMENTS in PL/SQL .. 44
BRANCHING STATEMENTS . 44
SELECTION IN PL/SQL .. 45
ITERATIONS IN PL/SQL .. 46
PLSQL Programs . 46
TO SWAP TWO NUMBERS WITH OUT TAKING THIRD VARIABLE .. 46
TO SWAP TWO NUMBERS BY TAKING THIRD VARIABLE .. 47
TO FIND THE LARGEST OF TWO NUMBERS . 48
TO FIND THE LARGEST OF THREE NUMBERS . 49
TO FIND THE TOTAL AND AVERAGE OF 6 SUBJECTS AND DISPLAY THE GRADE .. 50
TO CHECK WHETHER THE GIVEN NUMBER IS AN ARMSTRONG NUMBER OR NOT . 51
TO FIND THE SUM OF DIGITS IN A GIVEN NUMBER .. 52
TO DISPLAY THE NUMBER IN REVERSE ORDER .. 53
TO DISPLAY NUMBER IN REVERSE ORDER USING STRING FUNCTION .. 53
TO CHECK WHETHER THE GIVEN NUMBER IS PRIME OR NOT . 54
TO FIND THE FACTORIAL OF A GIVEN NUMBER .. 55
TO GENERATE FIBONACCI SERIES . 55
TO CALCULATE THE AREA OF A CIRCLE .. 56
A PL/SQL CODE BLOCK TO WITHDRAW BALANCE FROM ACCOUNT . 57
PL/SQL PROGRAMS- FUNCTIONS .. 59
CUBE .. 59
FACTORIAL .. 59
IN- PROCEDURE .. 60
OUT-PROCEDURE .. 61
BEFORE TRIGGER .. 61
AFTER TRIGGER .. 62

INTRODUCTION TO SQL

SQL (Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.

SQL is a programming language for querying and modifying data and managing databases. SQL was standardized first by the ANSI and (later) by the ISO. Most database management systems implement a majority of one of these standards and add their proprietary extensions. SQL allows the retrieval, insertion, updating, and deletion of data.


A database management system also includes management and administrative functions. Most -- if not all -- implementations also include a Command-line Interface (SQL/CLI) that allows for the entry and execution of the language commands, as opposed to only providing an API intended for access from a GUI.

The first version of SQL was developed at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. This version, initially called SEQUEL, was designed to manipulate and retrieve data stored in IBM's original relational database product, System R. IBM patented their version of SQL in 1985, while the SQL language was not formally standardized until 1986, by the American National Standards Institute (ANSI) as SQL-86. Subsequent versions of the SQL standard have been released by ANSI and as International Organization for Standardization (ISO) standards.


Originally designed as a declarative query and data manipulation language, variations of SQL have been created by SQL database management system (DBMS) vendors that add procedural constructs, control-of-flow statements, user-defined data types, and various other language extensions. With the release of the SQL:1999 standard, many such extensions were formally adopted as part of the SQL language via the SQL Persistent Stored Modules (SQL/PSM) portion of the standard.
Common criticisms of SQL include a perceived lack of cross-platform portability between vendors, inappropriate handling of missing data (see Null (SQL)), and unnecessarily complex and occasionally ambiguous language grammar and semantics.

FEATURES OF SQL:

SQL is both an easy-to-understand language and a comprehensive tool for managing data. Some of the major features of SQL are
• Vendor independence
• Portability across computer systems
• SQL standards
• IBM endorsement and commitment (DB2)
• Microsoft commitment (SQL Server , ODBC, and ADO)
• Relational foundation
• High-level, English-like structure
• Interactive, ad hoc queries
• Programmatic database access
• Multiple views of data
• Complete database language
• Dynamic data definition
• Client/server architecture
• Enterprise application support
• Extensibility and object technology
• Internet database access
• Java integration (JDBC)
• Industry infrastructure



SQL COMMANDS

SQL Consisting of DDL,DML,DCL,TCL COMMANDS.

DDL

Data Definition Language (DDL) statements are used to define the database structure or schema.
DDL Commands: Create , Alter ,Drop , Rename, Truncate

CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
RENAME - rename an object


DML

Data Manipulation Language (DML) statements are used for managing data within schema objects

DML Commands: Insert ,Update, Delete, Select


INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
SELECT - retrieve data from the a database


DCL

Data Control Language (DCL) statements is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.



DCL Commands: Grant, Revoke

GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the GRANT command

TCL


Transaction Control (TCL) statements are used to manage the changes made by DML statements. It allows statements to be grouped together into logical transactions.

TCL Commands: Commit, Rollback, Save point

COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can later roll back
ROLLBACK - restore database to original since the last COMMIT



SYNTAX’S OF COMMANDS
CREATE TABLE
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
);
ALTER A TABLE
To add a column in a table
ALTER TABLE table_name
ADD column_name datatype;

To delete a column in a table


ALTER TABLE table_name
DROP COLUMN column_name;

DROP TABLE

DROP TABLE table_name;
TRUNCATE TABLE

TRUNCATE TABLE table_name;


INSERT

INSERT INTO table_name
VALUES (value1, value2, value3,...);

( O R )

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...);

UPDATE

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value;



DELETE

DELETE FROM table_name
WHERE some_column=some_value;

SELECT

SELECT column_name(s)
FROM table_name;

CREATE,INSERT , UPDATE, DELETE, RENAME, TRUNCATE, ON TABLES

SQL>CREATE TABLE STUDENT(SNO NUMBER(5),SNAME VARCHAR2(15),DOJ DATE);

OUTPUT:-TABLE CREATED


SQL> INSERT INTO STUDENT100VALUES(&SNO,'&SNAME','&DOJ');




OUTPUT:-

Enter value for sno: 501
Enter value for sname: ABI
Enter value for doj: 12-OCT-07
old 1: INSERT INTO STUDENT100 VALUES(&SNO,'&SNAME','&DOJ')
new 1: INSERT INTO STUDENT100 VALUES(501,'ABI','12-OCT-07')

1 row created.


SQL> /

Enter value for sno: 502
Enter value for sname: ASHOK
Enter value for doj: 03-OCT-07
old 1: INSERT INTO STUDENT100 VALUES(&SNO,'&SNAME','&DOJ')
new 1: INSERT INTO STUDENT100 VALUES(502,'ASHOK','03-OCT-07')

1 row created.


SQL> /

Enter value for sno: 503
Enter value for sname: BHAVYA
Enter value for doj: 10-OCT-07
old 1: INSERT INTO STUDENT100 VALUES(&SNO,'&SNAME','&DOJ')
new 1: INSERT INTO STUDENT100 VALUES(503,'BHAVYA','10-OCT-07')

1 row created.
SQL> /

Enter value for sno: 504
Enter value