Skip to content

Instantly share code, notes, and snippets.

@omprakash201194
Last active December 19, 2020 07:15
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save omprakash201194/a20626ed4590f65c5a23cd8564f78f4b to your computer and use it in GitHub Desktop.
Save omprakash201194/a20626ed4590f65c5a23cd8564f78f4b to your computer and use it in GitHub Desktop.
Oracle SQL Commands A Beginner’s Tutorial

Create Table

CREATE TABLE table
(column_1 data_type_1,
column_2 data_type_2,
...
PRIMARY KEY (columns)
); 

Example

CREATE TABLE product (
    p_code      VARCHAR2(6),
    p_name      VARCHAR2(15),
    price       NUMBER(4, 2),
    launch_dt   DATE,
    PRIMARY KEY ( p_code )
);
Note:

When creating a table, you should always add a primary key, even though a primary key is optional. A primary key is a column or a set of columns that uniquely identify every row in the table.


Adding Data

INSERT INTO table
(column_1,
column_2,
... )
VALUES (value_1,
value_2,
... )
);

Example

INSERT INTO product (
    p_code,
    p_name,
    price,
    launch_dt
) VALUES (
    1,
    'Nail',
    10.0,
    '31-MAR-2013'
);
Note:

You can only add one row in an INSERT statement. Issue a COMMIT command to persist (confirm the storage of) the rows


Updating Data

UPDATE table_name
SET column_1 = new_value_1 [,
column_2 = new_value_2,
... ]
[WHERE condition];

Example

UPDATE product
SET
    price = price - ( price * 0.05 );
Note:

You specify which rows to update in the WHERE clause. Without a WHERE clause, all rows will be updated. With a WHERE clause, only rows that meet the condition will be updated. If no row meets the condition in the WHERE clause, nothing will be updated.

Example: Updating the price column with a WHERE clause

UPDATE product
SET
    price = price - ( price * 0.05 )
WHERE
    p_code = 9;

Deleting Data

DELETE FROM table
[WHERE condition];

Example

DELETE FROM product
WHERE
    p_name = 'Nut';
Note:

Without the WHERE condition, all rows will be deleted and the product table will be empty. You cannot delete some of the columns in a row; the DELETE statement deletes the whole row. If you need to change the content of a specific column, use the UPDATE statement.


Select Statement

SELECT column_names FROM table_name [WHERE condition];

Example

SELECT * FROM product;

Selecting Specific Columns

SELECT p_name, price FROM product;

Selecting Rows with WHERE

SELECT column_names FROM table_name [WHERE condition];

Example

SELECT p_name, price FROM product WHERE price = 15;

Using the != comparison operator

SELECT p_name, price FROM product WHERE p_name != 'Nut';

Compound Conditions

Using the AND and OR logical operator you can combine predicates to form a compound condition. Only rows that satisfy the compound condition will be returned by the query.

Example

SELECT
    *
FROM
    product
WHERE
    ( launch_dt >= '30-MAR-13'
      OR price > 15 )
    AND ( p_name != 'Nail' );

Evaluation Precedence and the Use of Parentheses

If a compound condition contains both the OR condition and the AND condition, the AND condition will be evaluated first because AND has a higher precedence than OR. However, anything in parentheses will have an even higher precedence than AND. For example, the SELECT statement has an OR and an AND, but the OR condition is in parentheses so the OR condition is evaluated first. If you remove the parentheses in the SELECT statement, the query will return a different result.

Example

SELECT
    *
FROM
    product
WHERE
    launch_dt >= '30-MAR-13'
    OR price > 15
    AND p_name != 'Nail';
Note

Without the parentheses, the compound condition price > 15 AND p_name != 'Nail' will be evaluated first, resulting in the Screw and Super_Nut. The result is then OR-ed with the launch_dt >= 30-MAR-13'


The NOT logical operator

You can use NOT to negate a condition and return rows that do not satisfy the condition.

Example

SELECT
    *
FROM
    product
WHERE
    NOT ( launch_dt >= '30-MAR-13'
          OR price > 15
          AND p_name != 'Nail' );

Using NOT on one predicate

SELECT
    *
FROM
    product
WHERE
    ( launch_dt >= '30-MAR-13'
      OR price > 15 )
    AND NOT ( p_name != 'Nail' );

The BETWEEN Operator

The BETWEEN operator evaluates equality to any value within a range. The range is specified by a boundary, which specifies the lowest and the highest values

SELECT columns FROM table
WHERE column BETWEEN(lowest_value, highest_value);

The boundary values are inclusive, meaning lowest_value and highest_value will be included in the equality evaluation.

Example

SELECT
    *
FROM
    product
WHERE
    price BETWEEN 15 AND 25;

The IN Operator

The IN operator compares a column with a list of values

SELECT columns FROM table
WHERE column IN(value1, value2, ...);

Example

SELECT * FROM product WHERE price IN (10, 25, 50);

The LIKE Operator

The LIKE operator allows you to specify an imprecise equality condition

SELECT columns FROM table
WHERE column LIKE ' ... wildcard_character ... ';

Example

SELECT * FROM product WHERE p_name LIKE 'N__' OR p_name LIKE 'Sc%';

Escaping the Wildcard Character

If the string you specify in the LIKE operator contains an underscore or a percentage sign, SQL will regard it as a wild character

Example

SELECT * FROM product WHERE p_name LIKE '%\_%' ESCAPE '\';

Combining the NOT operator

Example

SELECT * FROM product WHERE price NOT BETWEEN 15 AND 25;

Using IS NULL

Example

SELECT * FROM product WHERE price IS NULL;
Note:

Invalid usage of the equal operator on NULL

SELECT * FROM product WHERE price = NULL;

Executing the query produces no output.


Column Aliases

SELECT column_1 AS alias1, column_2 AS alias2, ...
FROM table;

Example

SELECT
    p_code,
    p_name AS "PRODUCT NAME"
FROM
    product;

Expressions

An output column can also be an expression. An expression in the SELECT clause can include columns, literal values, arithmetic or string operators, and functions

Example

SELECT
    p_code,
    'p_name in Uppercase: ' || upper(p_name) AS "PRODUCT NAME",
    ( price * 100 ) AS "NORMALIZED_PRICE",
    to_char(launch_dt, 'DD/MM/YYYY') AS "LAUNCH_DATE"
FROM
    product;

Limiting the Number of Rows

You can limit the number of output row by using the ROWNUM pseudo column

SELECT columns FROM table(s)
WHERE conditions AND ROWNUM < count;

Example

SELECT * FROM product WHERE price > 10 AND ROWNUM < 4;

The DISTINCT Keyword

A query may return duplicate rows. Two rows are duplicates if each of their columns contains exactly the same data. If you don’t want to see duplicate output rows, use DISTINCT in your SELECT clause. You can use DISTINCT on one column or multiple columns.

Example - Using DISTINCT on a single column

SELECT DISTINCT price FROM product ORDER BY price;

Example - Using DISTINCT on Multiple Columns

SELECT DISTINCT price, launch_dt FROM product ORDER BY price;

Aggregate Functions

You can manipulate your query output further by using aggregate functions.

MAX(column): The maximum column value
MIN(column): The minimum column value
SUM(column): The sum of column values
AVG(column): The average column value
COUNT(column): The count of rows
COUNT(*): The count of all rows including NULL

Example

SELECT
    MAX(price),
    MIN(price),
    SUM(price),
    AVG(price),
    COUNT(price),
    COUNT(*)
FROM
    product;

The CASE expression

expressionCASE allows you to have dynamic query output in which a column value may vary depending on the value of the column. CASE comes in two flavors: Simple and Searched

The Simple CASE

SELECT columns,
CASE column
WHEN equal_value1
THEN output_value1
WHEN equal_value2
THEN output_value2
WHEN ...
[ELSE else_value]
END AS output_column
FROM table
WHERE ... ;

In the Simple CASE, column_name is compared to equal_values in the WHEN clause, starting from the first WHEN and down to the last WHEN. If column_name matches a WHEN value, the value right after the THEN clause is returned and the CASE process stops. If column_name matches none of the WHEN values, else_value is returned if there exists an ELSE clause. If column_name matches none of the WHEN values but no ELSE clause exists, NULL will be returned.

Example

SELECT
    p_code,
    p_name,
    CASE price
        WHEN 10   THEN
            'Cheap'
        WHEN 15   THEN
            'Medium'
        WHEN 25   THEN
            'Expensive'
        ELSE
            'Others'
    END AS price_cat
FROM
    product;

The Searched CASE

The case in the Simple CASE compares a column with various values. On the hand, the case in the Searched CASE can be any condition

SELECT columns,
CASE
WHEN condition1
THEN output_value1
WHEN condition2
THEN output_value2
WHEN ...
ELSE else_value
END AS output_column
FROM table
WHERE ... ;

Example

SELECT
    p_code,
    p_name,
    CASE
        WHEN ( price <= 10
               AND p_name NOT LIKE 'Nut%' ) THEN
            'Cheap'
        WHEN price BETWEEN 11 AND 25 THEN
            'Medium'
        WHEN price > 25
             AND to_char(launch_dt, 'YYYYMMDD') > '20130329' THEN
            'Expensive'
        WHEN price IS NULL THEN
            'Not valid'
        ELSE
            'Others'
    END AS product_cat
FROM
    product;

Ordering Output Rows

To order the output, use the ORDER BY clause. The ORDER BY clause must appear last in a SELECT statement.

SELECT columns FROM
table
WHERE condition ORDER BY column(s)

You can order output rows in one of the following methods.

  • by one or more columns
  • in ascending or descending direction
  • by using the GROUP BY clause
  • by using UNION and other set operators

Ordering by One Column

SELECT * FROM product ORDER BY p_name;

Direction of Order

The default direction is ascending

SELECT * FROM product ORDER BY p_name DESC;

Multiple Columns

To order by more than one column, list the columns in the ORDER BY clause. The sequence of columns listed is significant. The order will be conducted by the first column in the list, followed by the second column, and so on. For example, if the ORDER BY clause has two columns, the query output will first be ordered by the first column. Any rows with identical values in the first column will be further ordered by the second column.

SELECT * FROM product ORDER BY launch_dt, price;

Different Directions on Different Columns

You can apply different order directions on ordered columns too

SELECT * FROM product ORDER BY launch_dt, price DESC;

Ordering with a WHERE clause

If your SELECT statement has both the WHERE clause and the ORDER BY clause, ORDER BY must appear after the WHERE clause.

SELECT
    *
FROM
    product
WHERE
    p_name = 'Nut'
ORDER BY
    p_name,
    p_code DESC;

Storing Query Output

You can store a query output into a new or existing table.

CREATE TABLE new_table AS SELECT ... ;

Example

CREATE TABLE nut_product
    AS
        SELECT
            *
        FROM
            product
        WHERE
            p_name LIKE '%Nut%';

To store a query output into an existing table

INSERT INTO existing_table AS SELECT ... ;

Example

INSERT INTO non_nut
    SELECT
        *
    FROM
        product
    WHERE
        p_name NOT LIKE '%Nut%';

The GROUP BY Clause

In a query the GROUP BY clause appears after the WHERE clause and before the ORDER clause, if any.

SELECT columns,
aggregate_function(group_columns)
FROM table(s)
WHERE condition
GROUP BY group_columns
ORDER BY column(s);

Grouping on one column

SELECT
    launch_dt,
    MAX(price) max,
    MIN(price) min,
    SUM(price) sum,
    AVG(price) avg,
    COUNT(price) count,
    COUNT(*) AS "COUNT(*)"
FROM
    product
GROUP BY
    launch_dt
ORDER BY
    launch_dt;

Grouping on multiple columns

You can group by more than one column. If you do that, rows having the same value on all the columns will form a group

SELECT
    price,
    launch_dt,
    MAX(price) max,
    MIN(price) min,
    SUM(price) sum,
    AVG(price) avg,
    COUNT(price) count,
    COUNT(*) "COUNT(*)"
FROM
    product
GROUP BY
    price,
    launch_dt
ORDER BY
    price,
    launch_dt;

The HAVING Keyword

SELECT columns,
aggregate_function(group_columns)
FROM table(s)
WHERE condition
GROUP BY group_columns
HAVING aggregate_condition
ORDER BY columns;

The WHERE condition can be used to select individual rows. On the other hand, the HAVING condition is used for selecting individual groups. Only groups that satisfy the condition in the HAVING clause will be returned by the query. In other words, the HAVING condition is on the aggregate, not on a column. If present, the HAVING clause must appear after the GROUP BY

Example

SELECT
    price,
    launch_dt,
    MAX(price) max,
    MIN(price) min,
    SUM(price) sum,
    AVG(price) avg,
    COUNT(price) count,
    COUNT(*) "COUNT(*)"
FROM
    product
GROUP BY
    price,
    launch_dt
HAVING
    COUNT(price) > 1
ORDER BY
    price,
    launch_dt;

Joins

Primary Keys and Foreign Keys

A primary key is a column, or a set of columns, which uniquely identifies every row in a table. A foreign key is a column, or a set of columns, which is used to relate to the primary key of another table. The process of using the foreign key/primary key to relate rows from two tables is called joining.

While a primary key must be unique, a foreign key does not have to be unique. You can have a foreign key in more than one row

Even though the use of primary and foreign keys is not an absolute requirement for joining tables, their absence may cause you to incorrectly join tables.

Querying Multiple Tables

To query data from multiple tables, use the JOIN keyword to specify the related columns from two tables. The JOIN clause of a SELECT statement joins related rows from two or more tables, based on their primary key/foreign key relationship.

SELECT columns FROM table_1, table_2, ... table_n
WHERE table_1.primary_key = table_2.foreign_key
AND table_2.primary_key = table_n.foregin_key;

Example - A two table join

SELECT
    c_name,
    p_code,
    c_order.qty,
    c_order.order_dt
FROM
    c_order
    JOIN customer ON c_order.c_no = customer.c_no;

Using Table Aliases

In a join query, different tables can have columns with identical names. To make sure you refer to the correct column of a table, you need to qualify it with its table

Example - Using table aliases

SELECT
    c_name,
    p_code,
    o.qty,
    o.order_dt
FROM
    c_order    o
    JOIN customer   c ON o.c_no = c.c_no;

Example - A three table join

SELECT
    c_name,
    p_name,
    o.qty,
    o.order_dt
FROM
    c_order    o
    JOIN customer   c ON o.c_no = c.c_no
    JOIN product    p ON o.p_code = p.p_code;

JOIN and WHERE

SELECT
    c_name,
    p_name,
    o.qty,
    o.order_dt
FROM
    c_order    o
    JOIN customer   c ON o.c_no = c.c_no
    JOIN product    p ON o.p_code = p.p_code
WHERE
    p_name NOT LIKE 'Super%';

Joining on More than One Column

The preceding joins were on one column. Tables can also be joined on more than one column.

SELECT columns FROM table_1, table_2
WHERE table_1.column_1 = table_2.column_1
AND table_1.column_2 = table_2.column_2
...
AND table_1.column_n = table_2.column_n;

Example - A multiple columns join

SELECT
    o.c_no,
    o.p_code,
    o.order_dt,
    ship_qty,
    ship_dt,
    qty
FROM
    shipment   s
    JOIN c_order    o ON s.c_no = o.c_no
                      AND s.p_code = o.p_code
                      AND s.order_dt = o.order_dt;

Outer Joins

While an inner join query produces only related rows from the joined tables, an outer join query produces all rows from one table even when some of the rows do not have matching rows from the other table There are three subtypes of outer joins, LEFT, RIGHT, and FULL

Left outer join

All rows from the table on the left of the left outer join will be in the output whether or not there are matching rows from the table on its right.

SELECT columns FROM table_1 RIGHT OUTER JOIN table_2 ON
table_1.column = table_2.column ... ;
Example
SELECT
    o.*,
    ship_dt
FROM
    c_order    o
    LEFT OUTER JOIN shipment   s ON o.p_code = s.p_code
                                  AND o.c_no = s.c_no;
Full outer join

The full outer join returns all rows from both tables whether or not there are matching rows from the opposite table.

SELECT columns
FROM table_1 FULL OUTER JOIN table_2
ON table_1.column = table_2.column … ;
Example
SELECT
    o.*,
    s.*
FROM
    c_order    o
    FULL OUTER JOIN shipment   s ON o.p_code = s.p_code
                                  AND o.c_no = s.c_no;
Self-Joins
SELECT columns
FROM table alias_1
JOIN table alias_2
ON alias_1.column_x = alias_2.column_y;
Example
SELECT
    prod.p_code,
    prod.p_name,
    subst.p_code   subst_p_code,
    subst.p_name   subst_name
FROM
    product   prod
    LEFT OUTER JOIN product   subst ON prod.s_code = subst.p_code
ORDER BY
    prod.p_code;
Natural Joins

If two tables have columns that share a name, you can naturally join the two tables on these columns. In a natural join, you do not need to specify the columns that the join should use

SELECT columns FROM table_1 NATURAL JOIN table_2 ... ;
Example
SELECT * FROM c_order NATURAL JOIN customer;
Example - Mixing natural join with different column names
SELECT
    *
FROM
    c_order   o
    NATURAL RIGHT JOIN product   p
WHERE
    o.order_dt = p.launch_dt;
The USING Keyword

A natural join will use all columns with the same names from the joined tables. If you want your query to join only on some of these identically named columns, instead of using the NATURAL keyword, use the USING keyword.

SELECT columns
FROM table_1
JOIN table_2 USING (column);
Example
SELECT
    p_code,
    SUM(s.ship_qty)
FROM
    c_order    o
    JOIN shipment   s USING ( p_code )
GROUP BY
    p_code;

Subqueries

A subquery is a query nested within another query. The containing query is called an outer query. A subquery in turn can have a nested query, making it a multiple nested query

Single-Row Subqueries

A single-row subquery is a subquery that returns a single value. A singlerow subquery can be placed in the WHERE clause of an outer query. The return value of the subquery is compared with a column of the outer query using one of the comparison operators.

Example
SELECT
    *
FROM
    product
WHERE
    price = (
        SELECT
            MAX(price)
        FROM
            product   p
            INNER JOIN c_order   o ON p.p_code = o.p_code
    );

The column and subquery result do not have to be the same column, but they must have compatible data types

Multiple-Row Subqueries

A subquery that returns more than one value is called a multiple-row subquery. This type of subquery also occurs in the WHERE clause of an outer query, however instead of using a comparison operator, you use IN or NOT IN in the WHERE clause.

Example
SELECT
    *
FROM
    product
WHERE
    price IN (
        SELECT
            MAX(price)
        FROM
            product   p
            INNER JOIN c_order   s ON p.p_code = s.p_code
        GROUP BY
            p.launch_dt
    );

The ALL and ANY Operators

In addition to IN and NOT IN, you can also use the ALL and ANY operators in a multiple-row subquery. With ALL or ANY you use a comparison operator.

Example - Using ALL
SELECT
    *
FROM
    product
WHERE
    price >= ALL (
        SELECT
            MAX(price)
        FROM
            product   p
            INNER JOIN c_order   o ON o.p_code = o.p_code
        GROUP BY
            p.launch_dt
    )
ORDER BY
    p_code;
Example - Using the equal comparison to ANY value
SELECT
    *
FROM
    product
WHERE
    price = ANY (
        SELECT
            MAX(price)
        FROM
            product   p
            INNER JOIN c_order   o ON p.p_code = o.p_code
        GROUP BY
            p.launch_dt
    )
ORDER BY
    p_code;
Multiple Nested Subqueries

A subquery can contain another query, making it a query with multiple nested subqueries.

Example - Query with multiple nested subqueries
SELECT
    customer.*
FROM
    customer
WHERE
    c_no IN (
        SELECT
            c_no
        FROM
            c_order
        WHERE
            p_code IN (
                SELECT
                    p_code
                FROM
                    product
                WHERE
                    p_name NOT LIKE '%Nut%'
            )
    );

Correlated Subqueries

All the preceding subqueries are independent of their outer queries. A subquery can also be related to its outer query, where one or more column from the outer query table is (are) related to the column(s) of the subquery table in the WHERE clause of the subquery. This type of subquery is called the correlated subquery.

SELECT
    customer.*
FROM
    customer
WHERE
    c_no IN (
        SELECT
            c_no
        FROM
            c_order   o
            JOIN product   p ON o.p_code = p.p_code
        WHERE
            p_name NOT LIKE '%Nut%'
            AND customer.c_no = o.c_no
    );

Compound Queries

You can combine the results of two or more SELECT statements using the UNION ALL, UNION, INTERSECT, or MINUS operators. The number of output columns from every statement must be the same and the corresponding columns must have identical or compatible data types.

UNION ALL

When you combine two or more queries with the UNION ALL operator, the overall output will be the total rows from all the queries

SELECT
    p_code,
    p_name,
    'FIRST QUERY' query
FROM
    product p
WHERE
    p_name LIKE '%Nut%'
UNION ALL
SELECT
    p.p_code,
    p_name,
    'SECOND_QUERY' query
FROM
    c_order   o
    INNER JOIN product   p ON o.p_code = p.p_code;
Example - Ordering output rows of a compound query
SELECT
    p_code,
    p_name,
    'FIRST QUERY' query
FROM
    product p
WHERE
    p_name LIKE '%Nut%'
UNION ALL
SELECT
    p.p_code,
    p_name,
    'SECOND_QUERY' query
FROM
    c_order   o
    INNER JOIN product   p ON o.p_code = p.p_code
ORDER BY
    p_code;

UNION

UNION is similar to UNION ALL. However, with UNION duplicate rows will be returned once only

Example
SELECT
    p_code,
    p_name
FROM
    product p
WHERE
    p_name LIKE '%Nut%'
UNION
SELECT
    p.p_code,
    p_name
FROM
    c_order   o
    INNER JOIN product   p ON o.p_code = p.p_code
ORDER BY
    p_code;

UNION

UNION is similar to UNION ALL. However, with UNION duplicate rows will be returned once only

Example
SELECT
    p_code,
    p_name
FROM
    product p
WHERE
    p_name LIKE '%Nut%'
UNION
SELECT
    p.p_code,
    p_name
FROM
    c_order   o
    INNER JOIN product   p ON o.p_code = p.p_code
ORDER BY
    p_code;

INTERSECT

When you combine two or more queries with the INTERSECT operator, the output will consist of rows common to all the participating SELECT statements. In other words, only if a row is returned by all the SELECT statements will the row be included in the final result

Example
SELECT
    p_code,
    p_name
FROM
    product p
WHERE
    p_name LIKE '%Nut%'
INTERSECT
SELECT
    p.p_code,
    p_name
FROM
    c_order   o
    INNER JOIN product   p ON o.p_code = p.p_code
ORDER BY
    p_code;

MINUS

When you combine two SELECT statements using the MINUS operator, the final output will be rows from the first query that are not returned by the second query

Example
SELECT
    p_code,
    p_name
FROM
    product p
WHERE
    p_name LIKE '%Nut%'
MINUS
SELECT
    p.p_code,
    p_name
FROM
    c_order   o
    INNER JOIN product   p ON o.p_code = p.p_code
ORDER BY
    p_code;

Views

A view is effectively a predefined query. You create and use views most frequently for the following purposes:

  • Hiding table columns (for security protection)
  • Presenting pre-computed columns (in lieu of table columns)
  • Hiding queries (so that the query outputs are available without running the queries)

Creating and Using Views

You create a view using the CREATE VIEW statement.

The SELECT statement at the end of the CREATE VIEW statement is the predefined query. When you use a view its predefined query is executed. Since a query result is a table that is not persisted (stored) in the database, a view is also known as a virtual table. The table in the SELECT statement of a view is known as a base table. One of the reasons you use a view is when you have a table you need to share with other people. If you don’t want some of the table columns viewable by others, you can use a view to hide those columns. You would then share the view and restrict access to the base table.

Example - Using a view to hide columns
CREATE VIEW product_v (
    p_code,
    p_name
) AS
    SELECT
        p_code,
        p_name
    FROM
        product;

The product_v view can now be used just as you would any database table

SELECT * FROM product_v WHERE p_name NOT LIKE '%Nut%';

Note that within a database a view name must be unique among all the views and tables in the database. Another use of the view is to derive computed columns not available in the base table(s)

Example - A view with a computed column
CREATE VIEW product_sell_v
(p_no , p_name, sell_price
) AS
SELECT p_code, p_name, (price + margin) FROM product;
Example - Hiding Query
CREATE VIEW prod_subs_v AS
    SELECT
        prod.p_code,
        prod.p_name,
        subst.p_code   subst_p_code,
        subst.p_name   subst_name
    FROM
        product   prod
        LEFT OUTER JOIN product   subst ON prod.s_code = subst.p_code
    ORDER BY
        prod.p_code;

Nested Views

A view can be based on another view. Such a view is called a nested view.

Example
CREATE VIEW ps_noname_v (
    p_no,
    sell_price
) AS
    SELECT
        p_no,
        sell_price
    FROM
        product_sell_v;

Managing Views

You can easily manage your views in Oracle. To see all views in the current database, execute the following statement.

SELECT VIEW_NAME FROM USER_VIEWS;

To delete a view, use the DROP VIEW statement. The syntax for the DROP VIEW statement is as follows

DROP VIEW view_name;

Built-in Functions

The Oracle database provides functions that you can use in your queries. These built-in functions can be grouped into numeric functions, character functions, datetime functions, and functions for handling null values

Numeric Functions

ABS

ABS(n) returns the absolute value of n

SELECT p_code, price, (price – 20), ABS(price – 20.00) FROM product;
ROUND

ROUND(n, d) returns a number rounded to a certain number of decimal places. The argument n is the number to be rounded and d the number of decimal places.

SELECT p_code, price, ROUND (price, 1) FROM product;
SIGN

SIGN(n) returns a value indicating the sign of n. This function returns -1 for n < 0, 0 for n = 0, and 1 for n > 0.

SELECT p_code, price, SIGN(price – 15) FROM product;
TRUNC

TRUNC(n, d) returns a number truncated to a certain number of decimal places. The argument n is the number to truncate and d the number of decimal places

SELECT p_code, price, TRUNC(price, 1) FROM product;

Character Functions

CONCAT

CONCAT(string1, string2) concatenates string1 and string2 and returns the result. If you pass a number as an argument, the number will first be converted to a string.

SELECT p_code, CONCAT(CONCAT(p_name, ' -- ') , price) FROM product;

You can also use the || operator to concatenate strings

LOWER and UPPER

LOWER(str) converts str to lowercase and UPPER(str) converts str to uppercase

SELECT p_name, LOWER(p_name), UPPER(p_name) FROM product;
LENGTH

LENGTH(str) returns the length of string str. The length of a string is the number of characters in it.

SELECT p_name, LENGTH(p_name) FROM product;
SUBSTR

SUBSTR(str, start_position, [length]) returns a substring of str starting from the position indicated by start_position. If length is not specified, the function returns a substring from start_position to the last character in str. If length is present, the function returns a substring which is length characters long starting from start_position. If length is less than 1, the function returns an empty string.

SELECT SUBSTR(phone, 3) FROM customer;

Datetime Functions

CURRENT_DATE

CURRENT_DATE() returns the current date (the current date of the Oracle server at the time you run the query).

SELECT p_code, launch_dt, CURRENT_DATE FROM product;
TO_CHAR

TO_CHAR(dt, fmt_specifier) converts a date (dt) to a string in the format specified by fmt_specifier. In the following example, the launch_dt column is formatted with a format specifier that has three components:

  • DD - the day of the month
  • MONTH - the long name of the month in uppercase
  • YYYY - the year
SELECT p_code, TO_CHAR(launch_dt, 'DD MONTH YYYY') reformatted_dt
FROM product;

NULL-related functions

COALESCE

COALESCE(expr-1, expr-2, ..., expr-n) returns the first expression from the list that is not NULL

SELECT p_name, price, min_price,
COALESCE((price * 0.9), min_price, 5.0) sale_price
FROM product;
NULLIF

NULLIF (expr1, expr2) compares expr1 and expr2. If they are equal, the function returns null. If they are not equal, the function returns expr1

SELECT
    p_code,
    p_name,
    nullif(p.price, op.price) current_price
FROM
    product       p
        JOIN old_product   op USING ( p_code );
NVL

NVL (expr1, expr2) returns exprs1 if expr1 is not NULL; otherwise, it returns expr2.

SELECT
    p_code,
    p.p_name,
    p.price current_price,
    nvl(op.price, p.price) old_price
FROM
    product       p
        LEFT OUTER JOIN old_product   op USING ( p_code );

PL/SQL

PL/SQL, short for Procedural Language extension to SQL, complements SQL with a procedural programming language. PL/SQL is a feature of the Oracle database. The objective of this is to introduce some of the most commonly used PL/SQL features such as

  • row-by-row processing of query output
  • if-then-else decision logic
  • exception handling
  • user-defined functions

Row-by-row Processing

You can write a PL/SQL program to process query output row-by-row sequentially. The structure of the PL/SQL program for row-by-row processing is as follows.

BEGIN
    FOR output_row_variable IN (
        SELECT ...)
    LOOP
        row processing statements;
    END LOOP;
END;
Example
BEGIN
    FOR invc IN (
        SELECT
            c_no,
            c_name,
            p_name,
            qty,
            price     unit_prc,
            ( qty * price ) total_prc,
            sysdate   invoice_dt,
            launch_dt
        FROM
            c_order
            NATURAL JOIN product
            NATURAL JOIN customer
    ) LOOP
        INSERT INTO invoice VALUES (
            invc.c_no,
            invc.c_name,
            invc.p_name,
            invc.qty,
            invc.unit_prc,
            invc.total_prc,
            invc.invoice_dt
        );

    END LOOP;
END;

Note that to execute a PL/SQL program you need to enter the forward slash / after the last END;.

If-Then-Else Decision Logic

You can use an if statement to branch in a program

Example
BEGIN
    FOR invc IN (
        SELECT
            c_no,
            c_name,
            p_name,
            qty,
            price     unit_prc,
            ( qty * price ) total_prc,
            sysdate   invoice_dt,
            launch_dt
        FROM
            c_order
            NATURAL JOIN product
            NATURAL JOIN customer
    ) LOOP
        IF invc.launch_dt IS NOT NULL THEN
            INSERT INTO invoice VALUES (
                invc.c_no,
                invc.c_name,
                invc.p_name,
                invc.qty,
                invc.unit_prc,
                invc.total_prc,
                invc.invoice_dt
            );

        ELSE
            NULL;
        END IF;
    END LOOP;
END;

Exception Handling

HandlingPL/SQL allows you to handle errors (or exceptions) in your program using the EXCEPTION statement. Its syntax is as follows.

EXCEPTION
WHEN exception_name
THEN exception_handling_statement;
Example
DECLARE
    invc invoice%rowtype;
BEGIN
    SELECT
        *
    INTO invc
    FROM
        invoice
    WHERE
        c_no = '&c_no_prompt'
      AND p_name = '&p_name_prompt'
      AND to_char(invoice_dt, 'DD-MON-YY') = '&invoice_dt_prompt';

    dbms_output.put_line(invc.c_name
        || ' - '
        || invc.p_name
        || ' - '
        || invc.total_prc);

EXCEPTION
    WHEN no_data_found THEN
        dbms_output.put_line('Error: The invoice does not exist!');
END;

To see the exception-handler’s message, issue a set serveroutput on command before you execute the program.

User-defined Functions

Using PL/SQL you can write your own functions. The syntax for a userdefined function is as follows.

CREATE FUNCTION FUNCTION name(parameters)
RETURN data_type
IS
Variable_declarations
BEGIN
Processing_statements
EXCEPTION
Exception_handling statements
END;
Example
CREATE FUNCTION calc_new_price (
    exist_price         NUMBER,
    change_percentage   NUMBER
) RETURN NUMBER IS
BEGIN
    RETURN exist_price + ( exist_price * change_percentage );
END;

Using the function

UPDATE product SET price = calc_new_price(price, 0.1) ;

The Data Dictionary

The data dictionary of a database contains data about the data in the database. This data about data is also known as metadata. The data in the data dictionary is stored as tables. As such, you can use your SQL skills gained so far to query the data dictionary of your database. The tables and their data are maintained by the Oracle database system. You should not access the dictionary tables directly. Instead, use the Oracle-supplied views of the dictionary

The Dictionary View

The dictionary view is one of the views in the data dictionary. It returns all the views available in your dictionary. The view has two columns and can have over 650 rows

Example
SELECT * FROM dictionary;

####The User_Catalog View The user_catalog view stores the names of your tables and views

SELECT * FROM user_catalog;

The User_Tab_Cols View

You can get table and view names from the User_Catalog view. To see the details of each table and view, can you query the user_tab_cols view.

SELECT
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale
FROM
    user_tab_cols
WHERE
    table_name = 'PRODUCT';

The User_Procedures View

The user_procedures view contains information about your stored programs, including functions, procedures and triggers.

SELECT object_name, object_type FROM user_procedures;

The User_Source View

This view contains the source code of your stored functions

SELECT line, text FROM user_source WHERE name = 'CALC_NEW_PRICE';

Indexes

An index entry of the Index section of a book points to the location of the word/phrase indicated by the entry. The index entry helps you find the pages containing the word/phrase. Similarly, a column index of a table can speed up your finding data in a database. If your query has a condition on a column (or columns) that is (are) not indexed, the table will be fully scanned and the query will be slower than if an index was available. The topics covered are as follows.

  • Creating an index
  • Multi-column indexes
  • Bit map and join bit map indexes
  • Function-based indexes
  • Deleting an index

Creating an Index

To create an index on a column or columns of a table, use this statement.

CREATE INDEX index ON table (columns);
Example
CREATE INDEX p_name_ix ON product (p_name);

Unique Index Names and Columns

You cannot have duplicate index names. In addition, you cannot have the same column(s) indexed more than once.

Multi-Column Indexes

An index can be based on multiple columns. A multi-column index is useful when you need to search on rows having the same value on an indexed column. For instance, if your query has to search on the p_name column of the product table and there can be more than one row with the same p_name but with different launch dates, it would help if you create an index on both p_name and launch_dt.

CREATE INDEX p_name_launch_ix ON product (p_name, launch_dt);

Bitmap Indexes

The indexes created in Listings C.1 and C.2 are ordinary indexes, which are technically called B-tree indexes. Another type of index, the bitmap index, can be a better choice for tables whose rows will not be changed concurrently, such as in a low-volume data entry environment. A product table is an example of such low data maintenance and therefore is suitable for bitmap indexes. By contrast, the customer order table gets rows inserted more frequently and is not a good candidate for bitmap indexes.

CREATE BITMAP INDEX order_bix ON c_order (p_code, c_no);

Bitmap Join Indexes

The bitmap join index is a variant of the bitmap index. The bitmap join index is specifically created to expedite column retrieval from a joined table in a join query.

CREATE BITMAP INDEX c_name_bji ON
    c_order ( c.c_name ) FROM c_order o, customer c WHERE
        o.c_no = c.c_no;

Function-based Indexes

There are often cases where capitalization is used inconsistently when entering data into a table. Sometimes, a column value is entered in all capitals, sometimes in lowercase, and sometimes in mixed cases.

Example - Using the UPPER function in a query
SELECT * FROM customer WHERE UPPER(c_name) LIKE '%STORE';

For this query to run faster, you should also create an index that is based on the UPPER-ed version of the c_name column

Example - A function-based index
CREATE INDEX func_name_ix ON
    customer ( upper(c_name) );

Deleting An Index

To delete an index of any type, use the DROP INDEX statement

DROP INDEX s_name_bji;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment