Oracle Database/SQL

From Wikibooks, open books for an open world
Jump to navigation Jump to search

Retrieving Data Using the SQL SELECT Statement[edit | edit source]

List the capabilities of SQL SELECT statements[edit | edit source]

Selection, projection, join

Execute a basic SELECT statement[edit | edit source]

  • 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 | edit source]

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

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

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

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

Describe various types of functions available in SQL[edit | edit source]

  • Describe the differences between single row and multiple row functions

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

  • 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 | edit source]

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

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

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

Apply conditional expressions in a SELECT statement[edit | edit source]

  • Use conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions[edit | edit source]

Identify the available Group Functions[edit | edit source]

Describe the use of group functions[edit | edit source]

Group data by using the GROUP BY clause[edit | edit source]

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

Displaying Data from Multiple Tables[edit | edit source]

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

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

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

  1. Join a table by using a self join

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

Using Subqueries to Solve Queries[edit | edit source]

Define subqueries[edit | edit source]

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

List the types of subqueries[edit | edit source]

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

Using the Set Operators[edit | edit source]

Describe set operators[edit | edit source]

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

Control the order of rows returned[edit | edit source]

Manipulating Data[edit | edit source]

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

Insert rows into a table[edit | edit source]

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

DELETE client1 WHERE ID = 2;

Update rows in a table[edit | edit source]

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;

Using a set operator to combine multiple queries into a single query[edit | edit source]

Controlling the order of rows returned[edit | edit source]

Defining subqueries[edit | edit source]

Describing the types of problems that the subqueries can solve[edit | edit source]

Listing the types of subqueries[edit | edit source]

Writing single-row and multiple-row subqueries[edit | edit source]

Controlling transactions[edit | edit source]

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

Using DDL Statements to Create and Manage Tables[edit | edit source]

Categorize the main database objects[edit | edit source]

Review the table structure[edit | edit source]

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

Create a simple table[edit | edit source]

"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 empid, empname, empdesignation followed by their datatypes.

Describe how schema objects work[edit | edit source]

Creating Other Schema Objects[edit | edit source]

Create simple and complex views[edit | edit source]

Retrieve data from views[edit | edit source]

Create, maintain, and use sequences[edit | edit source]

Create and maintain indexes[edit | edit source]

Create private and public synonyms[edit | edit source]

Controlling User Access[edit | edit source]

Differentiate system privileges from object privileges[edit | edit source]

Grant privileges on tables[edit | edit source]

View privileges in the data dictionary[edit | edit source]

Grant roles[edit | edit source]

Distinguish between privileges and roles[edit | edit source]

Managing Objects with Data Dictionary Views[edit | edit source]

Explain the data dictionary[edit | edit source]

Find table information[edit | edit source]

Report on column information[edit | edit source]

View constraint information[edit | edit source]

Find view information[edit | edit source]

Verify sequence information[edit | edit source]

Understand synonyms[edit | edit source]

Add comments[edit | edit source]

Manipulating Large Data Sets[edit | edit source]

Manipulate data using sub-queries[edit | edit source]

Describe the features of multi-table inserts[edit | edit source]

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

Merge rows in a table[edit | edit source]

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

Managing Data in Different Time Zones[edit | edit source]

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

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

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

Retrieving Data Using Sub-queries[edit | edit source]

Write a multiple-column sub-query[edit | edit source]

Use scalar sub-queries in SQL[edit | edit source]

SELECT * FROM TAB

Solve problems with correlated sub-queries[edit | edit source]

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

Use the EXISTS and NOT EXISTS operators[edit | edit source]

Use the WITH clause[edit | edit source]

Write a multiple-column sub-query[edit | edit source]

Use scalar sub-queries in SQL[edit | edit source]

Solve problems with correlated sub-queries[edit | edit source]

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

Use the EXISTS and NOT EXISTS operators[edit | edit source]

Use the WITH clause[edit | edit source]

Hierarchical Query[edit | edit source]

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

SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", salary, department_id
FROM hr.employees
CONNECT BY PRIOR employee_id = manager_id
      START WITH manager_id is null
ORDER SIBLINGS BY last_name;

list out all the employees under manager 'Kochhar'

SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", 
       salary, 
       department_id,
       CONNECT_BY_ISLEAF
FROM hr.employees
  CONNECT BY PRIOR employee_id = manager_id
        START WITH last_name = 'Kochhar'
 ORDER SIBLINGS BY last_name;

list out all the manager that 'Lorentz' report to

SELECT LPAD(' ', 4*(level-1))||last_name "Last Name", salary, department_id,
       SYS_CONNECT_BY_PATH(last_name, '/') "Path", CONNECT_BY_ISLEAF
FROM hr.employees
        CONNECT BY employee_id = PRIOR manager_id
        START WITH last_name = 'Lorentz'
 ORDER SIBLINGS BY last_name;
  • 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)


Regular Expression Support[edit | edit source]

List the benefits of using regular expressions[edit | edit source]

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

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