Procedural SQL

Entity Relationship Diagram

Normalization

Keywords

All keywords:

Keyword	Use case
`SAVE`

Basics

Pre-defined Variables in SQL*Plus (Section 13.17.1 in Oracle Documentation)

Types of Variables in SQL*Plus

Three types of Variables (in SQL*Plus) (Section 6 in Oracle Documentation):

Substitution Variable:

Definition: Using DEFINE keyword, we can create a reusable variable for a SQL Script.

It can be referenced using ampersands (&) symbol. Ex:

                                            
SELECT &SORTCOL, SALARY
FROM &MYTABLE
WHERE SALARY>12000;

It prompts user to enter the value to store in the variable (including blanks and punctuation).

After you enter a value at the prompt, SQL*Plus lists the line containing the substitution variable twice: once before substituting the value you enter and once after substitution. To switch off this output, we can use the following command:

                                            
SET VERIFY OFF;

If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example:
Query:

                                            
SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='&X.5';

Input:

                                            
Enter value for X:  20

Interpretation:

                                            
SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='205';

If you want to append a period immediately after a substitution variable name, then use two periods together. For example:
Query:

                                            
spool &myfile..log

Interpretation:

                                            
spool &myfile.log

Bind Variable:

Bind variables are variables you create in SQL*Plus and then reference in PL/SQL or SQL. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. For example: Defining Variable:

                                            
VARIABLE ret_val NUMBER

Query (Assigning value):

                                            
BEGIN
    :ret_val:=4;
END;
/

Verify or print value on screen (Method 1 - SQL*Plus):

                                            
PRINT RET_VAL

Verify or print value on screen (Method 2 - SQL Query):

                                            
select :ret_val from dual;

Execute the procedure with a SQL*Plus bind variable as the parameter. For example (usage sequence): Step 1 - Package Creation:

                                            
CREATE OR REPLACE PACKAGE BODY EmpPack AS
    PROCEDURE EmpInfoRpt (emp_cv IN OUT EmpInfoTyp) AS
    BEGIN
        OPEN emp_cv FOR SELECT EMPLOYEE_ID, SALARY
        FROM EMP_DETAILS_VIEW
        WHERE JOB_ID='SA_MAN' ;
    END;
END;
/

Step 2 - Package Execution:

                                            
VARIABLE cv REFCURSOR
EXECUTE EmpPack.EmpInfoRpt(:cv)

Step 3 - Printing Result:

                                            
PRINT cv

NEED TO WORK ON EXAMPLE 6-6

System Variable: The system variables are not affected if you create substitution variables with the same name.

Setting Column as `UNUSED`

The SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. This is a feature available in Oracle8i and later. Specifying this clause does not actually remove the target columns from each row in the table (that is, it does not restore the disk space used by these columns). Therefore, the response time is faster than if you executed the DROP clause. Unused columns are treated as if they were dropped, even though their column data remains in the table as rows. After a column has been marked as unused, you have no access to that column.

Query to set a Column as UNUSED:

                            
ALTER TABLE Department
SET UNUSED (last_name);

Query to remove UNUSED columns:

                            
ALTER TABLE Department
DROP UNUSED COLUMNS;

SET UNUSED feature is useful when you just dont want to drop the column during peak periods. Performs the operation very fast because it is a data dictionary update. No data is modified.

Oracle SQL Example

Joins

You can get more info at https://highlightjs.org/. Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem. Nulla consequat massa quis enim. Donec pede justo, fringilla vel, aliquet nec, vulputate eget, arcu. In enim justo, rhoncus ut, imperdiet a, venenatis vitae, justo. Nullam dictum felis eu pede mollis pretium. Integer tincidunt. Cras dapibus. Vivamus elementum semper nisi. Aenean vulputate eleifend tellus. Aenean leo ligula, porttitor eu, consequat vitae, eleifend ac, enim.

Operators

Contraints

Restricting Data

Data Types

Sorting Data

There are two ways to sort data:

Type	Example
Using Column Name	In this, we will simply use the column name to in the `ORDER BY` clause. `SELECT last_name, first_name FROM employees WHERE last_name >= 'B' ORDER BY last_name;`
Positional Sorting	In this, the numeric position (numerical value) of the column in the selected column list can be provided in `ORDER BY` clause instead of column name. This numerical position starts with 1 (not with 0). `SELECT last_name, first_name FROM employees ORDER BY 2;`

Functions

Temporary Table

Derived Table

External Table

Domains

Indexes

Error Handling

Window Functions

Functions

Views

Stored Procedures

Packages

Triggers

Collections

Profiler

Performance Optimization

Grouping Sets

Rollup

Cube

Rank Logic

CTE

Managing Database Concurrency

Accuracy of Statistics

Dynamic Management

Object Management

Query Plans

Transact-SQL

Entity Relationship Diagram

Code Block Example

                    
                        function $initHighlight(block, cls) 
                        export  $initHighlight;

Typography

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus.

Bullet Points:

Lorem ipsum dolor sit amet consectetuer.
Aenean commodo ligula eget dolor.
Aenean massa cum sociis natoque penatibus.

Lorem ipsum dolor sit amet consectetuer.
Aenean commodo ligula eget dolor.
Aenean massa cum sociis natoque penatibus.

Quote Example:

You might not think that programmers are artists, but programming is an extremely creative profession. It's logic-based creativity.
John Romero

Table Example:

#	First	Last	Handle
1	Mark	Otto	mdo
2	Jacob	Thornton	fat
3	Larry	the Bird	twitter

Embed A Tweet:

1969:
-what're you doing with that 2KB of RAM?
-sending people to the moon

2017:
-what're you doing with that 1.5GB of RAM?
-running Slack
— I Am Devloper () November 3, 2017

Video Example

Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Aenean commodo ligula eget dolor. Aenean massa. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Donec quam felis, ultricies nec, pellentesque eu, pretium quis, sem. Nulla consequat massa quis enim.

Entity Relationship Diagram

Normalization

Keywords

Basics

Pre-defined Variables in SQL*Plus (Section 13.17.1 in Oracle Documentation)

Types of Variables in SQL*Plus

Setting Column as UNUSED

Joins

Operators

Contraints

Restricting Data

Data Types

Sorting Data

Functions

Temporary Table

Derived Table

External Table

Domains

Indexes

Error Handling

Window Functions

Functions

Views

Stored Procedures

Packages

Triggers

Collections

Profiler

Performance Optimization

Grouping Sets

Rollup

Cube

Rank Logic

CTE

Managing Database Concurrency

Accuracy of Statistics

Dynamic Management

Object Management

Query Plans

Transact-SQL

Entity Relationship Diagram

Code Block Example

Code Block Example

Code Block Example

Typography

Bullet Points:

Quote Example:

Table Example:

Embed A Tweet:

Video Example

Promo Section Heading

Evaluation Metrics

Log Loss

F1 Score

Average Precision

Accuracy

RMSE

MSE

MAE

R2

AMPE

Spearman

Pearson

Setting Column as `UNUSED`