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:
- SQL statements are not case sensitive.
- SQL statements can be on one or more lines.
- KEYWORDS cannot be abbreviated or spilt across the lines.
- Clauses are usually placed on separate lines.
- Indents are used to enhance the readability.
- 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
- Multiplication and Division take priority over addition and subtraction.
- Operators of same priority are evaluated from left to right.
- 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:
- Default sort order is ascending.
- For order by clause in ascending, numeric values are shown first, for example 1-400.
- For order by clause in ascending, date values are displayed with earliest value first.
- For order by clause in ascending, character values are displayed in alphabetical order.
- Null values are displayed first for ascending sequences and last for ascending sequences.
- SQL STATEMENTS
You can also sort the query results by more than one column
SQL STATEMENTS FUNCTIONS:
There are two types of SQL functions:
Single row functions.
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:
Character.
Number.
Date.
Conversion.
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:
NVL
NVL2
NULLIF
COALSECE
CASE
DECODE
Character functions are of two types:
Case manipulation functions. ( LOWER, UPPER, INITCAP)
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:
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.
TRUNC: Truncates column, expression or value to n decimal places or if n is omitted then by default to zero decimal places.
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:
You cannot use duplicate values in primary key.
Data conversion is of two types:
Implicit data type conversion.
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:
- You cannot use duplicate values in primary key.
Data conversion is of two types:
- Implicit data type conversion.
- 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
