Oracle Database/SQL

From Wikibooks, open books for an open world
< Oracle Database
Jump to: navigation, search

Contents

Retrieving Data Using the SQL SELECT Statement[edit]

List the capabilities of SQL SELECT statements[edit]

Selection, projection, join

Execute a basic SELECT statement[edit]

  • 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 Data[edit]

Limit the rows that are retrieved by a query[edit]

  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[edit]

  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[edit]

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[edit]

Describe various types of functions available in SQL[edit]

   * Describe the differences between single row and multiple row functions

Use character, number, and date functions in SELECT statements[edit]

   * 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[edit]

Describe various types of conversion functions that are available in SQL[edit]

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[edit]

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

Apply conditional expressions in a SELECT statement[edit]

   * Use conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions[edit]

Identify the available Group Functions[edit]

Describe the use of group functions[edit]

Group data by using the GROUP BY clause[edit]

Include or exclude grouped rows by using the HAVING clause[edit]

Displaying Data from Multiple Tables[edit]

Write SELECT statements to access data from more than one table using equijoins and nonequijoins[edit]

Join a table to itself by using a self-join[edit]

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

  1. Join a table by using a self join

Generate a Cartesian product of all rows from two or more tables[edit]

Using Subqueries to Solve Queries[edit]

Define subqueries[edit]

Describe the types of problems that the subqueries can solve[edit]

List the types of subqueries[edit]

Write single-row and multiple-row subqueries[edit]

Using the Set Operators[edit]

Describe set operators[edit]

Use a set operator to combine multiple queries into a single query[edit]

Control the order of rows returned[edit]

Manipulating Data[edit]

Describe each data manipulation language (DML) statement[edit]

Insert rows into a table[edit]

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 table[edit]

Delete rows from a table[edit]

Control transactions[edit]

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

Using DDL Statements to Create and Manage Tables[edit]

Categorize the main database objects[edit]

Review the table structure[edit]

List the data types that are available for columns[edit]

Create a simple table[edit]

"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 creation[edit]

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 work[edit]

Creating Other Schema Objects[edit]

Create simple and complex views[edit]

Retrieve data from views[edit]

Create, maintain, and use sequences[edit]

Create and maintain indexes[edit]

Create private and public synonyms[edit]

Controlling User Access[edit]

Differentiate system privileges from object privileges[edit]

Grant privileges on tables[edit]

View privileges in the data dictionary[edit]

Grant roles[edit]

Distinguish between privileges and roles[edit]

Managing Schema Objects[edit]

Add constraints[edit]

Create indexes[edit]

Create indexes using the CREATE TABLE statement[edit]

Create function-based indexes[edit]

Drop columns and set column UNUSED[edit]

Perform FLASHBACK operations[edit]

Create and use external tables[edit]

Managing Objects with Data Dictionary Views[edit]

Explain the data dictionary[edit]

Find table information[edit]

Report on column information[edit]

View constraint information[edit]

Find view information[edit]

Verify sequence information[edit]

Understand synonyms[edit]

Add comments[edit]

Manipulating Large Data Sets[edit]

Manipulate data using sub-queries[edit]

Describe the features of multi-table inserts[edit]

Use the different types of multi-table inserts[edit]

Merge rows in a table[edit]

Track the changes to data over a period of time[edit]

Managing Data in Different Time Zones[edit]

Use data types similar to DATE that store fractional seconds and track time zones[edit]

Use data types that store the difference between two date-time values[edit]

Practice using the multiple data-time functions for globalize applications[edit]

Retrieving Data Using Sub-queries[edit]

Write a multiple-column sub-query[edit]

Use scalar sub-queries in SQL[edit]

SELECT * FROM TAB

Solve problems with correlated sub-queries[edit]

Update and delete rows using correlated sub-queries[edit]

Use the EXISTS and NOT EXISTS operators[edit]

Use the WITH clause[edit]

Regular Expression Support[edit]

List the benefits of using regular expressions[edit]

Use regular expressions to search for, match, and replace strings[edit]

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