Notes in Progress!

SQL STATEMENTS

There are four types of SQL statements.

1. Data Manipulation Language. (DML)

2. Data Definition Language. (DDL)

3. Transaction Control. (TC)

4. Data Control Language. (DCL)

Detailed description of these commands is as follows:

TYPE	COMANDS
Data Manipulation Language (DML)	Insert Update Delete Merge
Data Definition Language. (DDL)	Create Alter Drop Rename Truncate
Transaction Control. (TC)	Commit Rollback Savepoint
Data Control Language (DCL)	Grant Revoke

CAPABILITIES OF SQL SELECT STATEMENTS

A select statement retrieves information from the database. Using a select statement you can do following things:

PROJECTION:

Using projection capability in SQL you can choose number of columns from your table. These columns can be one, few or many depending upon your situation.

SELECTION:

You can use the selection capability in SQL to choose the rows in a table that you want.

JOINING:

You can use join capability in SQL to bring together data that is stored in different tables by creating a link between them.

KEYWORD:

A keyword refers to an individual SQL element.

For example SELECT & FROM are keywords.

* is used to select every thing from a table.

SELECT STATEMENT

To select particular columns from a table separate the column names by a comma.

IMPORTANT POINTS ABOUT SQL STATEMENTS:

1. SQL statements are not case sensitive.
2. SQL statements can be on one or more lines.
3. KEYWORDS cannot be abbreviated or spilt across the lines.
4. Clauses are usually placed on separate lines.
5. Indents are used to enhance the readability.
6. Try to use KEYWORDS in upper case.

COLUMN HEADING DEFAULTS

iSQL*Plus:

- Default heading justification: Center.

- Default heading display: Uppercase

SQL*Plus:

- Character and Date column headings column headings are left justified

- Number column headings are right-justified

- Default heading display: Uppercase.

The various arithmetic operators available in SQL are:

OPERATOR	DESCRIPTION
+	Add
-	Subtract
*	Multiply
/	Divide

You can use Arithmetic Operators in any clause of SQL statements expect in FROM clause

OPERATOR PRECEDENCE

1. Multiplication and Division take priority over addition and subtraction.
2. Operators of same priority are evaluated from left to right.
3. Parenthesis is used to force prioritized evaluation and to clarify statements.

DEFINING A NULL VALUE

A null value is a value that is unavailable, unassigned, unknown or inapplicable. A null value is not same as zero or a blank space because zero is a number and a space is a character. If a column value in a arithmetic is null the result is also null. For example if we divide a number by a zero we get an error on the other hand if we divide a number by a null we get a null or an unknown value.

DEFINING A COLUMN ALIAS

A column alias renames a column heading. To use a column alias type the new name of column immediately after the old name separated by space or separated by the keyword “as”. If the new name of the column contains spaces, special characters or is case sensitive (by default the column headings are in UPPER-CASE) the write the new column name in double quotes.

CONCANTENATION OPERATOR

You can link columns to other columns using a concatenate operator. Columns on both sides of operator are combined to make a single output column.

LITERAL CHARACTER STRINGS

SQL> select ename ||' is a ' || job as "Employe detls" from emp;

Employe detls

-------------------------

SMITH is a CLERK

ALLEN is a SALESMAN

WARD is a SALESMAN

JONES is a MANAGER

MARTIN is a SALESMAN

BLAKE is a MANAGER

CLARK is a MANAGER

SCOTT is a ANALYST

KING is a PRESIDENT

TURNER is a SALESMAN

ADAMS is a CLERK

Employe detls

-------------------------

JAMES is a CLERK

FORD is a ANALYST

MILLER is a CLERK

14 rows selected.

Here ‘is a’ literal character.

DUPLICATE ROWS

By default select statement also shows duplicate entries of rows. To eliminate duplicate entries use DISTINCT keyword in the select clause. You can use distinct clause in front of multiple columns. In that case you will get distinct combination of multiple columns.

WHERE CLAUSE:

Character strings and dates in the where clause must be enclosed in single marks (‘ ‘). Number constants, however, should not be enclosed in single quotations marks. Entries in tables are case sensitive and hence use them appropriately with WHERE clause.

COMPARISON CONDITIONS

OPERATOR	MEANING
=	EQUAL TO
>	GREATER THAN
>=	GREATER THAN OR EQUAL TO
<	LESS THAN
<=	LESS THAN OR EQUAL TO
<>	NOT EQUAL TO

OTHER COMPARISON CONDITIONS

OPERATOR	MEANING
BETWEEN …..AND….	BETWEEN TWO VALUES (INCLUSIVE)
IN (SET)	MATCH ANY OF A LIST OF VALUES
LIKE	MATCH A CHARACTER PATTER
IS NULL	IS A NULL VALUE

BETWEEN……AND……

EG:

SQL> select ename, sal from emp

2 where sal between 1000 and 4000;

ENAME SAL

---------- ----------

ALLEN 1600

WARD 1250

JONES 2975

MARTIN 1250

BLAKE 2850

CLARK 2450

SCOTT 3000

TURNER 1500

ADAMS 1100

FORD 3000

MILLER 1300

11 rows selected.

We have to specify the lower limit first.

IN CONDITION

It is used for testing specified values from a set. It can be used for any data type. Dates or Characters have to be enclosed in single quotation marks.

LIKE CONDITION

Like operator is used when you don’t know the exact value to be searched for. You can use escape identifier to search for actual % and _ symbols. Use backslash in front of the symbol (% or _) to be searched for and then followed by ESCAPE ‘\’. This asks oracle server to treat \ as escape character.

%-Represents any sequence of zeros or more characters.

_- Represents exactly one character.

NULL CONDITION

The null condition includes IS NULL and IS NOT NULL. The null value means value is unavailable, unassigned, unknown or inapplicable. You cannot use ‘=’ because NULL value cannot be equal to or not equal to.

LOGICAL CONDITIONS:

Operator	Meaning
AND	Returns true if both conditions are true.
OR	Returns true if al east one condition is true.
NOT	Returns true if following condition is false.

RULES OF PRECEDENCE:

Order evaluated	Operator
1	Arithmetic Operators
2.	Concatenate Operator.
3.	Comparison Operator
4.	IS, [NOT] NULL, LIKE, [NOT] IN
5.	[NOT] BETWEEN
6.	NOT LOGICAL OPERATOR
7.	AND LOGICAL OPERATOR
8.	OR LOGICAL OPERATOR

ORDER BY CLAUSE

There are two order by clauses:

(i) ASC

(ii) DESC

EG OF ASCENDING ORDER:

SQL>select * from emp

order by empno asc;

By default order by clause returns values sorted in ascending order. Thus the query

SQL>select * from emp

order by emp;

is same as above query.

EG OF DESCENDING ORDER;

SQL> select * from emp

order by hiredate desc;

Important points for ORDER BY CLAUSE:

1. Default sort order is ascending.
2. For order by clause in ascending, numeric values are shown first, for example 1-400.
3. For order by clause in ascending, date values are displayed with earliest value first.
4. For order by clause in ascending, character values are displayed in alphabetical order.
5. Null values are displayed first for ascending sequences and last for ascending sequences.

6. 1. You can also sort the query results by more than one column

FUNCTIONS:

There are two types of SQL functions:

1. Single row functions.

2. Multiple row functions.

Single Row Functions operate on single rows and return one value per row. Various types of Single Row Functions are as follows:

1. Character.

2. Number.

3. Date.

4. Conversion.

5. General.

Multiple row functions operate on multiple rows and returns one value per multiple rows.

CHARACTER FUNCIONS:

Accepts a character I/P and returns a character or number value.

NUMBER FUNCTIONS:

Accepts a number value and returns a number value.

DATE FUNCTIONS:

It operates on date data type values.

CONVERT FUNCTIONS:

It converts value of one data type to data of another data type.

GENERAL FUNCTIONS:

These include:

1. NVL

2. NVL2

3. NULLIF

4. COALSECE

5. CASE

6. DECODE

Character functions are of two types:

1. Case manipulation functions. ( LOWER, UPPER, INITCAP)

2. Character manipulation functions. (CONCAT, SUBSTR, LPAD, RPAD, LENGTH, INSTR, TRIM & REPLACE)

LOWER: Converts an alpha character value to lower case.

UPPER: Converts an alpha character to upper case.

INITCAP: Converts first character of the word to upper case and at the same time making other characters of the word to lower case.

CONCAT: Concatenates first character value to second character value, equivalent to concatenation operator.

LENGTH: Extracts a string of determined length.

INSTR: Determines the position of given character.

LPAD: Pads the value right justified.

RPAD: Pads the value left justified.

TRIM: Trims heading or trailing characters from a character string.

NUMBER FUNCTIONS:

1. ROUND: Rounds column, expression or value to n decimal places or if n is omitted no decimal places. If n is negative numbers to left of decimal are rounded.

2. TRUNC: Truncates column, expression or value to n decimal places or if n is omitted then by default to zero decimal places.

3. Returns the value of m divided by n.

DUAL TABLE:

Dual table is owned by sys user. It contains one dummy column and one row with value X

SAMPLE FORMAT ELEMENTS OF VALID DATE FORMATS

SCC or CC	Century or Server prefixes B.C. date with
Years in dates YYYY or SYYYY	Year or Server prefixes B.C. date with
YYY or YY or Y	Last “three”, “two” or “one” digit of a year.
Y,YYY	Year with comma in this postion.
IYYY, IYY, IY, I	Four, three, two or one digit year based on ISO Format
SYEAR OR YEAR	Server prefixes B.C. date with.
BC/AD	BC/AD Indicator
B.C./A.D.	B.C./A.D. Indicator with periods
Q	Quarter of year.
MM	Month: Two digit value.
MONTH	Name of Month.
MON	Name of month: Three letter Abbreviation.
RM	Roman Numeral Month.
WW, W	Month of year, Month of week respectively
DDD, DD, D	Day of year, month, week.
DAY	Name of day padded with blanks to a length of nine characters.
DY	Name of day: Three letter abbreviation
J	Julian Day; Number of days since 31st December, 5713 B.C.

A foreign key is a column or set of columns which refer to primary key in the same table or another table. In other words it acts as link between two tables. In language of layman you can say that foreign key is that column which is common to multiple tables.

Guidelines for Primary key and Foreign key:

1. You cannot use duplicate values in primary key.

Data conversion is of two types:

1. Implicit data type conversion.

2. Explicit data type conversion.

IMPLICIT DATA TYPE CONVERSIONS

For assignments Oracle server can automatically convert a “varchar2” or “char” data-type in to “number” or “date” data-type. Similarly a “number” or “date” data-type can be converted to “varchar2” data-type.

For Expression evaluation Oracle server can automatically convert a “varchar2” or “char” data-type in to “number” or “date” data-type

A foreign key is a column or set of columns which refer to primary key in the same table or another table. In other words it acts as link between two tables. In language of layman you can say that foreign key is that column which is common to multiple tables.

Guidelines for Primary key and Foreign key:

1. You cannot use duplicate values in primary key.

Data conversion is of two types:

1. Implicit data type conversion.
2. Explicit data type conversion.

IMPLICIT DATA TYPE CONVERSIONS

For assignments Oracle server can automatically convert a “varchar2” or “char” data-type in to “number” or “date” data-type. Similarly a “number” or “date” data-type can be converted to “varchar2” data-type.

For Expression evaluation Oracle server can automatically convert a “varchar2” or “char” data-type in to “number” or “date” data-type

© Centre Of Discovery