Jitendra Patel - 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.
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

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.

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 Consisting of DDL,DML,DCL,TCL COMMANDS. 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 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 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 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

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; 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.Next page