LSP_Structured Query Language

STRUCTURED QUERY LANGUAGE (SQL)

	SQL is a non-procedural language.
Features:
	It is a non-procedural language.
	It is an English-like language.
	It can process a single record as well as sets of records at a time.
	It is different from a third generation language (C & COBOL). All SQL statements define what is to be done rather than how it is to be done.
	SQL is a data sub-language consisting of three built-in languages:
	o Data Definition Language (DDL). o Data Manipulation Language (DML). o Data Control Language (DCL).
	It insulates the user from the underlying structure and algorithm.
	SQL has facilities for defining database views, security, integrity constraints, transaction controls, etc.
DATA DEFINITION LANGUAGE (DDL) :
	The DDL defines a set of commands used in the creation and modification of schema objects such as tables, indexes, views, etc. These commands provide the ability to create, alter and drop these objects. These commands are related to the management and administration of the databases.
CREATE TABLE :
	CREATE TABLE <TABLE NAME>( COLUMN_NAME1 DATA TYPE (COLUMN WIDTH)[CONSTRAINTS], COLUMN_NAME2 DATA TYPE (COLUMN WIDTH)[CONSTRAINTS], ………………………………………………………………………………. ); where table name assigns the name of the table, column name defines the name of the field, data type specifies the data type for the field and column width allocates specified size to the field.
Note:
	• Table name should start with an alphabet. • In table name, blank spaces and single quotes are not allowed. • Reserve words of that DBMS can not be used as table name. • Proper data types and size should be specified. • Unique column name should be specified.
Different column constraints are :
	• NOT NULL • UNIQUE • PRIMARY KEY • CHECK • DEFAULT • REFERENCES
Examples:
	CREATE TABLE product( pno number(4) PRIMARY KEY, pname char(30) NOT NULL, qoh number(5) DEFAULT (100), class char(1) NOT NULL, rate number(8,2) NOT NULL, CHECK((class= ‘A’ AND rate < 4500) OR (class = ‘C’ AND rate > 4500)) ); CREATE TABLE prodtrans( ppno number(4), ptype char(1) CHECK(ptype in(‘P’, ‘S’)), qty number(5), FOREIGN KEY ppno REFERENCES product(pno) ON DELETE CASCADE);
On delete Cascade:
	Using this option whenever a parent row is deleted in a referenced table then all the corresponding child rows are deleted from the referencing table. This constraint is a form of referential integrity constraint.
ALTER TABLE :
	This command is used for modification of existing structure of the table in the following situation:
	• When a new column is to be added to the table structure. • When the existing column definition has to be changed, i.e., changing the width of the data type or the data type itself. • When integrity constraints have to be included or dropped. • When a constraint has to be enabled or disabled.
	ALTER TABLE <table name> ADD(<column name> <data type> …); ALTER TABLE <table name> MODIFY (<column name> < data type>…); ALTER TABLE <table name> ADD CONSTRAINT < constraint name> <constraint type> (field name); ALTER TABLE <table name> ENABLE/DISABLE <constraint name>; ALTER TABLE <table name> DROP <constraint name>;
DROP TABLE :
	To delete the existing object from the database. DROP TABLE <table name>;

DATA MANIPULATION LANGUAGE :
	Data Manipulation Language(DML) defines a set of commands that are used to query and modify data within existing schema objects. DML statements consists of SELECT, INSERT, UPDATE and DELETE statements.
SELECT Statement :
	This statement is used for retrieving information from the databases. It can be coupled with many clauses.
• Using Arithmetic Operator :
	SELECT pno, pname, qoh, qoh+200 FROM PRODUCT;
• Using Column Aliases :
	SELECT PNO, PNAME “Product Name” FROM PRODUCT;
• Concatenation operator :
	Select pname \|\|class “Name and Class” from product;
• To eliminate duplicate rows (distinct operator) :
	Select distinct pname from product;
• Special comparison operators used in where clause :
	o Between … and… It gives range between two values(inclusive) o In (list) match any of a list of values o Like – match a character pattern
• Is null operator :
	To find the value of an attribute, its values is not specified.
• Order by clause :
	o It is used in the last portion of the select statement o By using this rows can be sorted. o By default it takes ascending order o DESC: is used for sorting in descending order o Sorting by column which is not in select list is possible o Sorting by column Alias
• Aggregate Function :
	o count, min, max, avg. o These functions help in getting consolidated information from a group of tuples.
• Group by clause :
	o It is used to group data
• Having clause :
	o This clause is used for creating conditions on grouped information.

UPDATE Command :
	UPDATE < TABLE NAME> SET <COLUMN NAME> = <VALUE> WHERE <CONDITION>;

DELETE Command :
	DELETE FROM <TABLE NAME> WHERE<CONDITION>;

DATA CONTROL LANGUAGE :
	The data control basically refers to commands that allow system and data privileges to be passed to various users. These commands are normally available to database administrator.
Create User :
	CREATE USER <USERNAME> IDENTIFIED BY <PASS WORD>;
Grant :
	It is used to provide database access permission to users. It is of two types: • System Level Permission • Object Level Permission
	GRANT CREATE SESSION TO MMM; - this is a system level permission GRANT SELECT ON EMP TO MMM; - this is a object level permission GRANT SELECT, INSERT ON EMP TO MMM; GRANT ALL ON EMP TO MMM; GRANT ALL ON EMP TO PUBLIC; - all permission to all users on EMP.
Revoke :
	It is used to cancel the permission granted. REVOKE ALL ON EMP TO MMM; REVOKE SELECT ON EMP TO MMM;
Drop :
	A user-id can be deleted by using drop statement; DROP USER MMM;