SQL Descriptions

*. DESCRIPTIONS::

a.DDL[Data Definition Language]:These statements are used to define the database structure or schema.
Examples:

* CREATE - to create objects in the database
syntax: CREATE TABLE
(,...);

* ALTER - alters the structure of the database
syntax: ALTER TABLE ADD/MODIFY (,...);

* DROP - delete objects from the database
syntax: DROP TABLE ;

* TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
syntax: TRUNCATE TABLE ;

* RENAME - rename an object
syntax: RENAME TO ;

DML[Data Manipulation Language]:These statements are used for managing data within schema objects.
Examples:

* INSERT - insert data into a table
syntax: INSERT into values(,colname>,...);

* UPDATE - updates existing data within a table
syntax: UPDATE SET col=value[col n1,...] WHERE(condition);

* DELETE - deletes all records from a table, the space for the records remain
syntax: DELETE from WHERE ;

DRL[Data Retrieving Language]:These statements are used for retrieving the data from the database.
Examples:

* SELECT - retrives the selected data from the table
syntax: SELECT(distinct/all(*)) from WHERE(condition);

DCL [Data Control Language]:These statements controls the privileges provided to the user in the database.
Examples:

* GRANT - gives user's access privileges to database
syntax: GRANT ALL/privilege names( ON TO );

* REVOKE - withdraw access privileges given with the GRANT command
syntax: REVOKE ALL/privilege names( ON from );


TCL[Transaction Control]:These statements are used to manage the changes made by DML statements.
It allows statements to be grouped together into logical transactions.
Examples:

* COMMIT - save work done
syntax: COMMIT;

* SAVEPOINT - identify a point in a transaction to which you can later roll back
syntax: SAVEPOINT s1;

* ROLLBACK - restore database to original since the last COMMIT
syntax: ROLLBACK s1;

*. BUILT-IN FUNCTIONS:
CHARACTER FUNCTIONS:

1.LOWER : Converts given string into lower-case.
syntax : SELECT lower() from ;

2.UPPER : Converts given string into upper-case.
syntax : SELECT upper() from ;

3.INITCAP: This functions prints the initial letter as upper-case letter.
syntax : SELECT INITCAP() from ;

4.CONCAT : Combines two strings using the symbol '||'.
syntax : SELECT ,CONCAT('name is',) from ;

5.LPAD/RPAD: padding of the string is done with the given symbol
syntax : SELECT LPAD('',,'');

: SELECT RPAD('',,'');

6.SUBSTR : Selects substring from a given string.
syntax : SELECT substr(,,) from DUAL;

7.INSTR : Used to find the position of a string in a given string.
syntax : SELECT INSTR(','');

8.LTRIM/RTRIM: Trims the string with the given characters.
syntax : LTRIM(,);
: RTRIM(,);

9.LENGTH : Defines number of characters in a given string or column.
syntax : SELECT LENGTH(<string.) as length from ;

10.TRANSLATE : Translates given string character by character.
syntax : SELECT TRANSLATE('','','');

11.REPLACE : Translate given string word by word.
syntax : SELECT REPLACE('','','');

* Types of Group Functions
->AVG
->COUNT
->MAX
->MIN
->STDDEV
->SUM
->VARIANCE

-> AVG and SUM for numeric data.
SQL> SELECT AVG(SAL),SUM(SAL) from ;
-> MIN and MAX for any datatype.
SQL> SELECT MIN(hiredate), MAX(hiredate) from ;
-> COUNT(*) returns the number of rows in a table.
SQL> SELECT COUNT(*) FROM emp WHERE deptno = 30;
-> COUNT(expr) returns the number of nonnull rows.
SQL> SELECT COUNT(comm) FROM emp WHERE deptno=30;

->The NVL function forces group functions to include null values.
SQL> SELECT AVG(NVL(comm,0)) FROM emp;

Creating Groups of Data: GROUP BY Clause:
SQL> SELECT column,
2 group_function(column)
3 FROM table[WHEREcondition]
4 [GROUP BYgroup_by_expression]
5 [ORDER BYcolumn];

->Divide rows in a table into smaller groups by using the GROUP BY clause.
->All columns in the SELECT list that are not in group functions must be in the GROUP BY clause.
->Any column or expression in the SELECT list that is not an aggregate function must be in the GROUP BY.
->Cannot use the WHERE clause to restrict groups.
->Use the HAVING clause to restrict groups.

HAVING CLAUSE:
->Use the HAVING clause to restrict groups
►Rows are grouped.
►The group function is applied.
►Groups matching the HAVING clause are displayed.
SQL> SELECT column,group_function
2 FROM table[WHEREcondition]
3 [GROUP BYgroup_by_expression]
4 [HAVINGgroup_condition]
5 [ORDER BYcolumn];

Types of Subqueries:
->Single-Row Subqueries
->Multiple-Row Subqueries
Operator Meaning
IN Equal to any member in the list
ANY Compare value to each value returned by the subquery
ALL Compare value to every value returned by the subquery
->Multiple-Column Subqueries

CONSTRAINTS:
->Constraints enforce rules at the table level.
->Constraints prevent the deletion of a table if there are dependencies.
->The following constraint types are valid in Oracle:
►NOT NULL
►UNIQUE
►PRIMARY KEY
►FOREIGN KEY
►CHECK
DEFINING CONSTRAINTS:
SQL>CREATE TABLE [schema.]table
2 (column datatype
3 [DEFAULT expr]
4 [column_constraint],...[table_constraint][,...]);
->NOT NULL constraint:
►Ensures that null values are not permitted for the column.
SQL> CREATE TABLE emp1(
2 empno NUMBER(4),
3 enameVARCHAR2(10) NOT NULL);
->UNIQUE KEY constraint:
►Defined at either the table level or the column level.
SQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE(dname));
->PRIMARY KEY constraint:
levelSQL> CREATE TABLE dept(
2 deptno NUMBER(2),
3 dname VARCHAR2(14),
4 loc VARCHAR2(13),
5 CONSTRAINT dept_dname_uk UNIQUE (dname),
6 CONSTRAINT dept_deptno_pk PRIMARY KEY(deptno));
->FOREIGN KEY constraint:
FOREIGN KEY
Defines the column in the child table at the table constraint level.
REFERENCES
Identifies the table and column in the parent table.
ON DELETE CASCADE
Allows deletion in the parent table and deletion of the dependent rows in the child table.
->CHECK constraint:
Defines a condition that each row must satisfy
Expressions that are not allowed:
►References to CURRVAL, NEXTVAL, LEVEL, and ROWNUM pseudocolumns
►Calls to SYSDATE, UID, USER, and USERENV functions
►Queries that refer to other values in other rows.

VIEWING constraints:
SQL> SELECT constraint_name, constraint_type,
2 search_condition
3 FROM user_constraints
4 WHERE table_name = 'EMP';

VIEW: Logically represents subsets of data from one or more tables
CREATING a view:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]];
->Modify the EMPVU10 view by using CREATE OR REPLACE VIEW clause.
REMOVE view:
SQL>DROP VIEW view;

SEQUENCE: Generates primary key values.
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n| NOMAXVALUE}]
[{MINVALUE n| NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n| NOCACHE}];
->NEXTVAL returns the next available sequence value.
It returns a unique value every time it is referenced, even for different users.
CURRVAL obtains the current sequence value.
NEXTVAL must be issued for that sequence before CURRVAL contains a value.

INDEX: schema object
CREATE INDEX index ON table(column[, column]...);

SYNONYM: Alternative name for an object.
CREATE [PUBLIC] SYNONYM synonym FOR object;

PL/SQL is an extension to SQL with design features of programming languages.
DECLARE –Optional
–Variables, cursors, user-defined exceptions
•BEGIN –Mandatory
–SQL statements
–PL/SQL statements
•EXCEPTION –Optional
–Actions to perform when errors occur
•END; –Mandatory.

SQL Cursor: cursor is a private SQL work area.
There are two types of cursors:
►Implicit cursor ►Explicit cursors

Implicit cursor attributes verify the outcome of DML statements:
► SQL%ROWCOUNT ► SQL%FOUND
► SQL%NOTFOUND ► SQL%ISOPEN
Explicit cursors are defined by the programmer.

What is an exception?
►Identifier in PL/SQL that is raised during execution
Sample predefined exceptions:
►NO_DATA_FOUND
►TOO_MANY_ROWS ►INVALID_CURSOR
►ZERO_DIVIDE ►DUP_VAL_ON_INDEX.
Packages: Group logically related PL/SQL types,items and subgroups.
Consists of 2 types: -Specification.
-Body.