Last modified on 19 August 2014, at 10:13

Oracle Database/SQL

Retrieving Data Using the SQL SELECT StatementEdit

List the capabilities of SQL SELECT statementsEdit

Selection, projection, join

Execute a basic SELECT statementEdit

  • Select All Columns:
 
Select * from table_name;
  • Select Specific Columns:
 Select column1, column2 from tables_name;
  • Use Column Heading Defaults
  • Use Arithmetic Operators:
 Select 12 salary+100 from emp --sell value is 2.
 Result: 12 * cell's value + 100   --i.e. 12 * 2 + 100= 124
  • Understand Operator Precedence
  • Learn the DESCRIBE command to display the table structure
 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.

Restricting and Sorting DataEdit

Limit the rows that are retrieved by a queryEdit

  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 queryEdit

  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 runtimeEdit

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 OutputEdit

Describe various types of functions available in SQLEdit

   * Describe the differences between single row and multiple row functions

Use character, number, and date functions in SELECT statementsEdit

   * 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 ExpressionsEdit

Describe various types of conversion functions that are available in SQLEdit

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 functionsEdit

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

Apply conditional expressions in a SELECT statementEdit

   * Use conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group FunctionsEdit

Identify the available Group FunctionsEdit

Describe the use of group functionsEdit

Group data by using the GROUP BY clauseEdit

Include or exclude grouped rows by using the HAVING clauseEdit

Displaying Data from Multiple TablesEdit

Write SELECT statements to access data from more than one table using equijoins and nonequijoinsEdit

Join a table to itself by using a self-joinEdit

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

  1. Join a table by using a self join

Generate a Cartesian product of all rows from two or more tablesEdit

Using Subqueries to Solve QueriesEdit

Define subqueriesEdit

Describe the types of problems that the subqueries can solveEdit

List the types of subqueriesEdit

Write single-row and multiple-row subqueriesEdit

Using the Set OperatorsEdit

Describe set operatorsEdit

Use a set operator to combine multiple queries into a single queryEdit

Control the order of rows returnedEdit

Manipulating DataEdit

Describe each data manipulation language (DML) statementEdit

Insert rows into a tableEdit

Inserting data in database is done through "insert" command in oracle.

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

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

Update rows in a tableEdit

Delete rows from a tableEdit

Control transactionsEdit

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

Using DDL Statements to Create and Manage TablesEdit

Categorize the main database objectsEdit

Review the table structureEdit

List the data types that are available for columnsEdit

Create a simple tableEdit

"Create table" comand 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 empid , empname , empdesignation followed by their datatypes/

Explain how constraints are created at the time of table creationEdit

Concept In essence, constraints safeguard and validate the data.

Primary Key and Unique constraints both ensure the data is not duplicated. Primary Key also ensure the data is not null. Oracle will automatically generate index for Primary Key and Unique constraints. A table can only have one Primary Key, but it can have multiple unique constraints.

Foreign Key ensure the data exists in the column of the parent table it refer to. Each parent record can have multiple child records, but each child can relate to ONLY one parent record. A column with Foreign Key may not necessary to have an index.


Foreign Key can only refer to column with Primary Key or Unique Constraint

   create table tblA (colX number, colY char);
   create table tblB (colX number);
   alter table tblB add (constraint colX_FK foreign key (colX) references tblA(colX));
   -- ORA-02270: no matching unique or primary key for this column-list
   alter table tblA add (constraint colX_PK primary key (colX));
   alter table tblB add (constraint colX_FK foreign key (colX) references tblA(colX));
   -- alter table success.


A table can ONLY have one Primary Key, but it can have multiple UNIQUE key. if the child table(s) require to referencing column other than primary key, the column on the parent table must have UNIQUE constraint.

   alter table tblA add (constraint colY_PK primary key (colY));
   -- ORA-02260: table can have only one primary key

Cannot create PK or Unique on a column contains duplicate data

   insert into tblA values(1,'A');
   insert into tblA values(2,'A');
   alter table tblA add (constraint colY_UK unique (colY));
   -- ORA-02299: cannot validate (HR.COLY_UK) - duplicate keys found


   delete from tblA where colx = 2;
   alter table tblA add (constraint colY_UK unique (colY));
   -- alter table success.
   create table tblC (colY char);
   alter table tblC add (constraint colY_FK foreign key (colY) references tblA(colY));
   -- alter table success.


Insert data into a column with FK, the value must already exist in the column that the FK reference to.

   insert into tblC values ('B');
   -- ORA-02291: integrity constraint (HR.COLY_FK) violated - parent key not found
   insert into tblC values ('A');
   -- 1 rows inserted


As long as a foreign key exist, the parent table can truncate/delete the data or disable the PK or Unique constraint

   truncate table tblA;
   -- ORA-02266: unique/primary keys in table referenced by enabled foreign keys


Find out the constraint information in Oracle

   desc all_constraints;
   select
     a.owner, a.table_name, a.constraint_name,
     a.constraint_type, a.status, a.r_owner, a.r_constraint_name,
     b.table_name as r_table_name, b.status as r_status
   from all_constraints a
     left join all_constraints b on a.owner = b.owner and a.r_constraint_name = b.constraint_name
   where a.table_name like 'TBL%';
   select *
   from all_cons_columns
   where table_name like 'TBL%';

Disable constraint that have foreign key refer to is not allowed, in order to do this, you have to disable the foreign key first.

   alter table tblA disable constraint colX_PK;
   -- ORA-02297: cannot disable constraint (HR.COLX_PK) - dependencies exist
   alter table tblA disable constraint colY_UK;
   -- ORA-02297: cannot disable constraint (HR.COLY_UK) - dependencies exist
   alter table tblC disable constraint colY_FK;
   alter table tblB disable constraint colX_FK;
   alter table tblA disable constraint colX_PK;
   alter table tblA disable constraint colY_UK;
   truncate table tblA;

If the data in parent table is deleted, re-enable the foreign key that contain data reference to the missing data is not allowed.

   select * from tblC;
   alter table tblA enable constraint colY_UK;
   alter table tblC enable constraint colY_FK;
   -- ORA-02298: cannot validate (HR.COLY_FK) - parent keys not found

Generate a sql statements to disable all the Foreign Key on a specified table

   select
     'alter table '||a.owner||'.'||a.table_name||
     ' disable constraint '||a.constraint_name||';' as STMT
   from all_constraints a, all_constraints b
   where a.constraint_type = 'R'
     and a.r_constraint_name = b.constraint_name
     and a.r_owner = b.owner
     and b.table_name = 'TBLA';

Describe how schema objects workEdit

Creating Other Schema ObjectsEdit

Create simple and complex viewsEdit

Retrieve data from viewsEdit

Create, maintain, and use sequencesEdit

Create and maintain indexesEdit

Create private and public synonymsEdit

Controlling User AccessEdit

Differentiate system privileges from object privilegesEdit

Grant privileges on tablesEdit

View privileges in the data dictionaryEdit

Grant rolesEdit

Distinguish between privileges and rolesEdit

Managing Schema ObjectsEdit

Add constraintsEdit

Create indexesEdit

Create indexes using the CREATE TABLE statementEdit

Create function-based indexesEdit

Drop columns and set column UNUSEDEdit

Perform FLASHBACK operationsEdit

Create and use external tablesEdit

Managing Objects with Data Dictionary ViewsEdit

Explain the data dictionaryEdit

Find table informationEdit

Report on column informationEdit

View constraint informationEdit

Find view informationEdit

Verify sequence informationEdit

Understand synonymsEdit

Add commentsEdit

Manipulating Large Data SetsEdit

Manipulate data using sub-queriesEdit

Describe the features of multi-table insertsEdit

Use the different types of multi-table insertsEdit

Merge rows in a tableEdit

Track the changes to data over a period of timeEdit

Managing Data in Different Time ZonesEdit

Use data types similar to DATE that store fractional seconds and track time zonesEdit

Use data types that store the difference between two date-time valuesEdit

Practice using the multiple data-time functions for globalize applicationsEdit

Retrieving Data Using Sub-queriesEdit

Write a multiple-column sub-queryEdit

Use scalar sub-queries in SQLEdit

SELECT * FROM TAB

Solve problems with correlated sub-queriesEdit

Update and delete rows using correlated sub-queriesEdit

Use the EXISTS and NOT EXISTS operatorsEdit

Use the WITH clauseEdit

Regular Expression SupportEdit

List the benefits of using regular expressionsEdit

Use regular expressions to search for, match, and replace stringsEdit

Regular Expression
Class Expression Description
Anchoring Character ^ Start of a line
-$ End of a line
Quantifier Character * Match 0 or more times
+ Match 1 or more times
? Match 0 or 1 time
{m} Match exactly m times
{m,} Match at least m times
{m, n} Match at least m times but no more than n times
\n Cause the previous expression to be repeated n times
Alternative and Grouping Separates alternates, often used with grouping operator ()
( ) Groups subexpression into a unit for alternations, for quantifiers, or for backreferencing (see "Backreferences" section)
[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
[:alpha:] Alphabetic characters
[:blank:] Blank Space Characters
[:cntrl:] Control characters (nonprinting)
[:digit:] Numeric digits
[:graph:] Any [:punct:], [:upper:], [:lower:], and [:digit:] chars
[:lower:] Lowercase alphabetic characters
[:print:] Printable characters
[:punct:] Punctuation characters
[:space:] Space characters (nonprinting), such as carriage return, newline, vertical tab, and form feed
[:upper:] Uppercase alphabetic characters
[: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
i Case insensitive matching
m Treat source string as multi-line activating Anchor chars
n Allow the period (.) to match any newline character