Oracle Database/SQL

List the capabilities of SQL SELECT statements
Selection, projection, join

Execute a basic SELECT statement

 * Select All Columns:

Select * from table_name; Select column1, column2 from tables_name; Select 12 salary+100 from emp --sell value is 2. Result: 12 * cell's value + 100  --i.e. 12 * 2 + 100= 124 Type- DESCRIBE table_name; *NOTE: Your Oracle user and/or schema must have permissions/privaliages or be within the schema to describe the table. You can use the data_dictionary views to get the table info.
 * Select Specific Columns:
 * Use Column Heading Defaults
 * Use Arithmetic Operators:
 * Understand Operator Precedence
 * Learn the DESCRIBE command to display the table structure

Limit the rows that are retrieved by a query

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

Sort the rows that are retrieved by a query

 * 1) Write queries that contain an ORDER BY clause sort the output of a SELECT statement
 * 2) 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.

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

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

View data that generally does not meet a join condition by using outer joins

 * 1) Join a table by using a self join

Insert rows into a table
Inserting data in database is done through "insert" command in oracle.

Syntax: INSERT INTO [table name][column1,column2,.....] values(value1,value2,....);

Example: insert into employee values(1,'Rahul','Manager');

By the above query the employee table gets populated by empid:-1, empname:-'Rahul' and empdesignation:-'Manager'.

Delete rows from a table
DELETE client1 WHERE ID = 2;

Update rows in a table
To update rows in a table, write: update [table name] set [column name] = [your value];

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,

update [table name] set [column name] = [value] where [column name]>=[value];

You can add your query after the where clause according to your need.

Example: UPDATE client1 SET address = 'the middle of nowhere' WHERE id = 1;

Controlling transactions

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

Create a simple table
"Create table" command is used to create table in database.

Syntax: create table employee(empid number,empname varchar2(20),empdesignation(varchar2(20)));

The above Query will create a table named employee with which contain columns,  ,   followed by their datatypes.

Use scalar sub-queries in SQL
SELECT * FROM TAB

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)