# Oracle Database/SQL
## Retrieving Data Using the SQL SELECT Statement
### List the capabilities of SQL SELECT statements

Selection, projection, join

### Execute a basic SELECT statement

* Select All Columns:
* Select Specific Columns:
* Use Column Heading Defaults
* Use Arithmetic Operators:
* Understand Operator Precedence
* Learn the DESCRIBE command to display the table structure

## Restricting and Sorting Data
### Limit the rows that are retrieved by a query

* Write queries that contain a WHERE clause to limit the output retrieved
* List the comparison operators and logical operators that are used in a WHERE clause
* Describe the rules of precedence for comparison and logical operators
* Use character string literals in the WHERE clause

### Sort the rows that are retrieved by a query

* Write queries that contain an ORDER BY clause sort the output of a SELECT statement
* Sort output in descending and ascending order

### Use ampersand substitution to restrict and sort output at runtime

the ampersand operator is used to take the input at runtime( ex:-&employeename) and if ampersand is used twice i.e && then it will take the input of single ampersand operator and is used to provide data to the query at runtime.

## Using Single-Row Functions to Customize Output
### Describe various types of functions available in SQL

* Describe the differences between single row and multiple row functions

### Use character, number, and date functions in SELECT statements

* Manipulate strings with character function in the SELECT and WHERE clauses
* Manipulate numbers with the ROUND, TRUNC and MOD functions
* Perform arithmetic with date data
* Manipulate dates with the date functions

## Using Conversion Functions and Conditional Expressions
### Describe various types of conversion functions that are available in SQL

Implicit data type conversion
Implicit conversion occurs when Oracle attempts to convert the values, that do not match the defined parameters of functions, into the required data types.
Explicit data type conversion Explicit conversion occurs when a function like TO_CHAR is invoked to change the data type of a value.

### Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions

* Nest multiple functions
* Apply the NVL, NULLIF, and COALESCE functions to data

### Apply conditional expressions in a SELECT statement

* Use conditional IF THEN ELSE logic in a SELECT statement

## Displaying Data from Multiple Tables
### Write SELECT statements to access data from more than one table using equijoins and nonequijoins
### View data that generally does not meet a join condition by using outer joins

* Join a table by using a self join

### Generate a Cartesian product of all rows from two or more tables
## Manipulating Data
### Describe each data manipulation language (DML) statement
### Insert rows into a table

Inserting data in database is done through "insert" command in oracle.
Syntax:
Example:
By the above query the employee table gets populated by empid:-1 , empname:-'Rahul' and empdesignation:-'Manager'.

### Update rows in a table

To update rows in a table, write:
It will update all the rows present in the table by the given value in the selected field.
We can also add queries to this command to make a real use for example,
You can add your query after the where clause according to your need.
Example:

## Controlling transactions

* Save and discard changes with the COMMIT and ROLLBACK statements
* Explain read consistency

## Using DDL Statements to Create and Manage Tables
### Create a simple table

"Create table" command is used to create table in database.
Syntax:
The above Query will create a table named employee with which contain columns empid, empname, empdesignation followed by their datatypes.

### Describe how schema objects work
## Retrieving Data Using Sub-queries
### Write a multiple-column sub-query
### Use scalar sub-queries in SQL

SELECT * FROM TAB

### Use the WITH clause
## Write a multiple-column sub-query
## Hierarchical Query

Hierarchical Query allows you the transverse through a self-reference table and display the Hierarchical structure. eg. the employee table contain the manager id the employee.
list out the whole hierarchical structure of the employees
list out all the employees under manager 'Kochhar'
list out all the manager that 'Lorentz' report to
* pseudocolumn LEVEL -> root = 1, next level=2,3,4,5...etc
* SYS_CONNECT_BY_PATH(col, '/') shows the full path, 2nd parameter is seperator (9i)
* CONNECT_BY_ROOT(col) return the value of the root node in the current hierarchy (10g)
* pseudocolumn CONNECT_BY_ISLEAF return 1 if the return value is at the last node on the Hierarchy (ie. leaf) (10g)
* order SIBLINGS by re-order the sequence of the output and preserve the hierarchical relationship (10g)
* connect by NOCYCLE prior child = parent
** NOCYCLE means stop tranverse the hierarchy at the level when the child reference back to the root. (10g)
** pseudocolumn CONNECT_BY_ISCYCLE evaluate to "1" if the current row references a parent. (10g)

## Regular Expression Support
### Use regular expressions to search for, match, and replace strings

Regular Expression :
| Class | Expression | Description |
| Anchoring Character | ^ | Start of a line |
| Anchoring Character | -$ | End of a line |
| Quantifier Character | * | Match 0 or more times |
| Quantifier Character | + | Match 1 or more times |
| Quantifier Character | ? | Match 0 or 1 time |
| Quantifier Character | {m} | Match exactly m times |
| Quantifier Character | {m,} | Match at least m times |
| Quantifier Character | {m, n} | Match at least m times but no more than n times |
| Quantifier Character | \n | Cause the previous expression to be repeated n times |
| Alternative and Grouping | | Separates alternates, often used with grouping operator |
| Alternative and Grouping | ( ) | Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section) |
| Alternative and Grouping | [char] | Indicates a character list; most metacharacters inside a character list are understood as literals, with the exception of character classes, and the ^ and - metacharacters |
| Posix Character | [:alnum:] | Alphanumeric characters |
| Posix Character | [:alpha:] | Alphabetic characters |
| Posix Character | [:blank:] | Blank Space Characters |
| Posix Character | [:cntrl:] | Control characters (nonprinting) |
| Posix Character | [:digit:] | Numeric digits |
| Posix Character | [:graph:] | Any [:punct:], [:upper:], [:lower:], and [:digit:] chars |
| Posix Character | [:lower:] | Lowercase alphabetic characters |
| Posix Character | [:print:] | Printable characters |
| Posix Character | [:punct:] | Punctuation characters |
| Posix Character | [:space:] | Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed |
| Posix Character | [:upper:] | Uppercase alphabetic characters |
| Posix Character | [:xdigit:] | Hexidecimal characters |
| Equivalence class | = = | An equivalence classes embedded in brackets that matches a base letter and all of its accented versions. eg, equivalence class '[=a=]' matches ä and â. |
| Match Option | c | Case sensitive matching |
| Match Option | i | Case insensitive matching |
| Match Option | m | Treat source string as multi-line activating Anchor chars |
| Match Option | n | Allow the period (.) to match any newline character |