SQL - History - A Journey

• RDBMS language.
• Its ANSI --> standard language for operational relational databases.
• Efficient , easy to learn and use.
• Functionally complete.

How SQL Works

• Data Sublanguage.
• works with logical level.
• common language for all relational databases.

SQL Statement types

• Data Manipulation Language DML

• Data Definition Language DDL

• Data Control Language DCL

• Transaction Control Language TCL

Oracle SQL Development Environments

• Below are the Tools used for SQL:
• Oracle SQL Developer --> debug , export , view reports ,etc
• SQL Plus --> Batch query tool.
• Oracle JDeveloper --> Multi-platform tool for web services.
• Oracle Application Express --> Like a web browser.

Oracle SQL Developer

Concept on RDMS ( Relational Database Management Systems )

• Relational Database --> Collection of relations or two-dimensional tables controlled by server processes.
• Before entering RDMS , What is Data Model ?
• Entity Relationship Model --> its business specifications.
• Then Modelling conventions --> its called Entity Relationship Modelling conventions.

• Relating Multiple Tables --> 1. Each row is unique by primary key 2. Logically a foreign key can be related with multiple tables.

HR Schema Model

SQL Statement

• Not case sensitive.
• one line or more lines.
• keywords can't be across lines or it can't be abbreviated.
• Clauses used in separate lines , what is clause here ? The main clauses are SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, INSERT, UPDATE, DELETE, and JOIN. [ TBD ]
• We can use indents so that one have better enhanced readability.

• dualis a table created by Oracle Database and its owned by SYS.
• dual as one column called DUMMY( data type VARCHAR (1)) and contains one row with value x.
• we can use to compute some expressions.

select * from dual;
select sysdate from dual;

Arithmetic Expression

• +
• "-"
• *
• /

• It reads from LEFT to RIGHT. L--->R

• You can see the difference after using Parenthesis.

select first_name,2*(salary+300), 2*salary+300 from hr.employees;

• NULL--> what is the value of it ? its like unavailable , unassigned , unknown or inapplicable.
• NULLis not as same as ZERO or BLANK SPACE.

Column Aliases

• Rename the column headings
• Column followed by AS.
• without AS also you can declare.

• In the column name , it becomes CAPS. To avoid this you can use "" for AS like below,

-- select first_name AS name , last_name as father_name from hr.employees;
select first_name AS "name" , last_name as "father_name" from hr.employees;
select first_name first  , last_name last  from hr.employees;

Concatenation Operators

• To link the column or character strings to other column.
• ||

select first_name||last_name AS "name"  from hr.employees;

Literal Character Strings

• Its a character , number or a date included in the select statement.
• Date & character should be enclosed with single quotation marks.
• Each character string is the output of each row.

select first_name || ' of ' || last_name OUTPUT from hr.employees;

Alternate Quote Operator

• we can use own quotation mark delimiter.
• specified with 'q'.
• To increase the readability.

select department_name || q'[ Departments Name is :]' || manager_id "Department and Manager" from hr.departments ;
select department_name || 'Departments Name is :' || manager_id "Department and Manager" from hr.departments ;

DISTINCT Keyword

• default select query will display all rows irrespective of duplicate rows.
• To eliminate the duplicate we can use DISTINCT keyword.

-- select department_id from hr.employees ;
select distinct department_id from hr.employees ;

DESCRIBE Command

• Its not a KEYWORD.
• To display table structure.
• Alternatively we can see in SQL developer in the column Tabs.

ORACLE QUESTIONS FOR CLEARING THE LEARNING EXAM