SQL Fundamentals I Everything you need

Private Synonym

Private synonyms are schema objects. Either they must be in your own schema, or they must be qualified with the schema name.

Relational Theory

Projection is the restriction of attributes. Selection is the restriction of tuples. Joining is the interaction of tables.

Public Synonym

Public synonyms exist independently of a schema. A public synonym can be referred to by any user to whom permission has been granted to see it without the need to qualify it with a schema name. Private synonyms must be a unique name within their schema. Public synonyms can have the same name as schema objects.

Single-row subquery

Returns one row. A scalar subquery returns one row with one column - acceptable in any situation where you could use a literal value, a constant, or an expression. Evaluating 1x before parent. Comparison Operators: =,>,<,>=,<=,<>,!=

Multiple-row subquery

Returns sets of rows. Commonly used to generate result sets that will be passed to a DML statement for further processing. Evaluating 1x before parent. Comparison Operators: IN, NOT IN, ANY, ALL

UNION ALL

Returns the combined rows from two queries without sorting or removing duplicates. The result sets come from two queries that must select the same number of columns, and the corresponding columns of the two queries must be of the same data type group.

UNION

Returns the combined rows from two queries, sorting them and removing duplicates. Performs a UNION ALL and then sorts the result across all the columns and removes the duplicates.

MINUS

Runs both queries, sorts the results, and returns only the rows from the first result set that do not appear in the second result set. Returns only the rows in the first result set that do not appear in the second result set, sorting them and removing duplicates.

SELECT

SELECT * | [DISTINCT] col | expression [alias]

System Roles

SELECT_CATALOG_ROLE allows SELECT privileges on all data dictionary views. EXECUTE_CATALOG_ROLE allow EXECUTE privileges for packages and procedures in the data dictionary. DELETE_CATALOG_ROLE allow them to delete records from the system audit table (AUD$). SELECT ANY DICTIONARY allows query access to any object in the SYS schema, including tables created in that schema. It must be granted individually to each user requiring the privilege. It is not included in GRANT ALL PRIVILEGES, nor can it be granted through a role.

SELECT FOR UPDATE

Select * from table for update;

Isolation

States that an incomplete or uncommitted transaction must be invisible to the rest of the world. While in progress only the one session that is executing the transaction is allowed to see the changes, all other sessions must see the unchanged data, not the new values. A read of uncommitted data is known as a dirty read.

Durability

States that once a transaction completes, it must be impossible for the database to lose it. Transactions are absolutely non-reversible.

Consistency

States that the results of a query must be consistent with the state of the database at the time the query started. Requires that the database ensure that changed values are not seen by the query no matter how long the query takes or what other activity is ocuring.

LIKE Wildcards

% is 0 or more characters _ is 1 character 'A\% ESCAPE '\' or 'A$% ESCAPE '$'

Date Default

'DD-MON-RR' 0-49 is 2000, 50-99 is 1900

Operator Order

()[] /* +- || = < > <= >= != <> NOT AND OR

hash join

A hash join technique reads the entire table into memory, converts it into a hash table, and uses a hashing algorithm to locate matching rows; this is more memory and CPU intensive.

Subquery

A query that is nested inside a DML statement or inside another subquery. Can return a set of rows or just one row to its parent query [scalar]. Often referred to as an inner query and the statement within which it occurs is then called the outer query with no limit on nesting. The subquery within a statement must be executed before the parent query that calls it, in order that the results of the subquery can be passed to the parent. SELECT list for column projection FROM WHERE HAVING

Sequence

A sequence is a structure for generating unique integer values. Only one session can read the next value and thus force it to increment. This is a point of serialization, so each value generated will be unique. The sequence mechanism is independent of tables, the row locking mechanism, and commit or rollback processing.

sort merge join

A sort merge join sorts the tables on the join column then merges them together: this is often a compromise between disk, memory, and CPU resources.

ROLLUP

A subclause of GROUP BY that aggregates the aggregate data in the SELECT statement's output - aggregate rows are known as superaggregate rows. Primary benefit with the aggregate function SUM - but works with other aggregates. ROLLUP returns a single summary row for each grouped set of records within a SELECT statement that uses the GROUP BY clause.

Synonym

A synonym is an alternative name for an object. This gives both data independence and location transparency. Tables and views can be renamed or relocated without ever having to change code; only the synonyms need to be adjusted. CREATE [PUBLIC] SYNONYM synonym FOR object ; DROP [PUBLIC] SYNONYM synonym ; ALTER SYNONYM synonym COMPILE;

Modify sequence

ALTER SEQUENCE sequencename [INCREMENT BY number] [MAXVALUE number | NOMAXVALUE] [MINVALUE number | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE number | NOCACHE] [ORDER | NOORDER] ;

ALTER Table

ALTER table(ADD col,MODIFY col,DROP COLUMN col,SET UNUSED COLUMN col,RENAME COLUMN col,READ ONLY,DROP UNUSED COLUMNS);

Atomicity

All parts of a transaction must complete or none of them. The rollback of an incomplete transaction may be manual (ROLLBACK), but it must be automatic and unstoppable in the case of an error.

SAVEPOINT

Allows a programmer to set a marker in a transaction that can be used to control the effect of the ROLLBACK command. Rather than rolling back the whole transaction and terminating it, it becomes possible to reverse all changes made after a particular point but leave changes made before that point intact. SAVEPOINT savepoint;

Star Transformation

An extension of the use of subqueries as an alternative to a join is to enable the star transformation often needed in data warehouse applications. WHERE code1 IN (SELECT....) and code2 IN (SELECT...) and code3 IN (SELECT....)

Index

An index is a sorted list of key values, structured in a manner that makes the search very efficient. With each key value is a pointer to the row in the table. Locating relevant rows via an index lookup is far faster than using a full table scan, if the table is over a certain size and the proportion of the rows to be retrieved is below a certain value. Indexes will improve performance for data retrieval but reduce performance for DML operations.

Database Transactions

Any relational database must be able to pass the ACID test: it must guarantee atomicity, consistency, isolation and durability. Transaction begins with 1st DML statement and terminates with a control statement. Explicit transaction control statements are: COMMIT [or DDL-DCL statement] ROLLBACK [or exit, session end or system crash] SAVEPOINT [sets markers, but will not end a transaction] SELECT FOR UPDATE

CACHE [cs]

For performance, Oracle can preissue sequence values in batches and cache them for issuing to users. The default is to generate and cache the next 20 values.

FK Index

Foreign key constraints are enforced by indexes, but the index must exist on the parent table, not necessarily on the table for which the constraint is defined. A foreign key constraint relates a column in the child table to the primary key or to a unique key in the parent table.

Explicit Data Type Conversion

Functions to convert items from one data type to another are known as explicit data type conversions. These return a value guaranteed to be the type required and offer a safe and reliable method of converting data items. TO_CHAR TO_NUMBER TO_DATE

GROUP BY

GROUP BY cols | expression

Grouping usage

GROUPING is ideal when used in combination with DECODE or string concatenation, or some other SQL function, to process and/or format output that differentiates between aggregate and superaggregate data.

INCREMENT BY [cs]

How much higher (or lower) than the last number issued should the next number be? Defaults to +1 but can be any positive number (or negative number for a descending sequence).

INSERT INTO

INSERT INTO [schema.]table [cols] Values (v1,v2,..); INSERT INTO [schema.]table [cols] subquery;

Oracle-proprietary JOIN syntax

If no joins or fewer than N-1 joins are specified in the WHERE clause conditions, where N refers to the number of tables in the query, then a Cartesian or cross join is performed. WHERE(t1.col_name = t2.col_name) | WHERE(t1.col_name(+) = t2.col_name) | WHERE(t1.col_name) = t2.col_name)(+)

OR REPLACE [CV]

If the view already exists, it will be dropped before being created.

PK Index

Indexes have two functions: to enforce primary key and unique constraints and to improve performance. Indexes are part of the constraint mechanism. An index gives (near) immediate access to key values, so the check for existence can be made virtually instantaneously. When a primary key constraint is defined, Oracle will automatically create an index on the primary key column(s), if one does not exist already.

WITH READ ONLY [CV]

Prevents any DML through the view.

SYSDATE

Takes no parameters and returns the current system date and time according to the database server. By default returns the DD-MON-RR components. select sysdate from dual

Caching sequence values

The CACHE keyword instructs Oracle to pregenerate sequence numbers in batches. This means that they can be issued faster than if they had to be generated on demand.

CUBE

The CUBE operation is something of a three-dimensional version of ROLLUP. CUBE goes beyond the functionality of ROLLUP by calculating subtotals for every possible grouping within the columns selected and grouped. For n expressions, CUBE returns 2 to the nth power groupings.

FORCE or NOFORCE [CV]

The FORCE keyword will create the view even if the detail table(s) in the subquery does not exist. NOFORCE is the default and will cause an error if the detail table does not exist.

GROUPING

The GROUPING function identifies superaggregate or aggregate rows produced by a ROLLUP or CUBE operation in a SELECT . . . GROUP BY statement. It returns a value of the NUMBER datatype, and its value is either a one (1) or a zero (0).

Use B*Tree index

The cardinality (the number of distinct values) in the column is high, and The number of rows in the table is high, and The column is used in WHERE clauses or JOIN conditions. Very efficient way of retrieving rows if the number of rows needed is low in proportion to the total number of rows in the table and if the table is large.

Use bitmap index

The cardinality (the number of distinct values) in the column is low (such as male/female), and The number of rows in the table is high, and, The column is used in Boolean algebra (AND/OR/NOT) operations

MAXVALUE [cs]

The highest number an ascending sequence can go to before generating an error or returning to its START WITH value. The default is no maximum.

INTERSECT

The intersection of two sets is the rows that are common to both sets. Returns only the rows that occur in both queries' result sets, sorting them and removing duplicates.

Rollup Syntax

The keyword ROLLUP is used after the keywords GROUP BY, and is part of the GROUP BY clause. The keyword ROLLUP is followed by a grouping expression list enclosed in parentheses. ROLLUP can be repeated for each grouping in the GROUP BY clause you wish to roll up.

MINVALUE [cs]

The lowest number a descending sequence can go to before generating an error or returning to its START WITH value. The default is no minimum.

nested loop join

The nested loop join technique passes through one table using an index on the other table to locate the matching rows: this is usually a disk-intensive operation.

RowId

The pointer to the row is the rowid. The rowid is an Oracle proprietary pseudocolumn that every row in every table has. Encrypted within it is the physical address of the row. As rowids are not part of the SQL standard, they are never visible to a normal SQL statement, but you can see them and use them if you want.

grouping sets syntax

The reserved words GROUPING SETS must follow GROUP BY. A pair of parentheses follows GROUPING SETS. Enclosed in the parentheses are a series of lists, each of which specifies one or more groups. Each set specifies separate GROUP BY clause groups.

Rollup Groupings

The rows that are displayed as a result of the GROUP BY clause are known as regular rows. The other rows—the aggregated aggregate rows—are known as superaggregate rows. For every n groups, ROLLUP produces n+1 groupings.

Set Theory

The set operators take as their input the results of two or more select statements and from these generate a single result set - known as a compound query. Set operators no implicit type casting - the values must be same data type group. UNION ALL UNION INTERSECT MINUS [EXCEPT]

START WITH [cs]

The starting point for the sequence: the number issued by the first selection. Defaults to 1 but can be anything.

CUBE Syntax

The syntax rules for CUBE are the same as for ROLLUP. The grouping expression list can include multiple GROUP BY groups specified within the GROUP BY clause, each separated within the required parentheses by a comma.

CONSTRAINT constraintname [CV]

This can be used to name the WITH CHECK OPTION and WITH READ ONLY restrictions so that error messages when the restrictions cause statements to fail, will be more comprehensible.

REGEXP_SUBSTR

This function returns the actual substring matching the regular expression pattern you specify.

REGEXP_LIKE

This function searches a character column for a pattern.

REGEXP_INSTR

This function searches a string for a given occurrence of a regular expression pattern. You specify which occurrence you want to find and the start position to search from. This function returns an integer indicating the position in the string where the match is found.

REGEXP_REPLACE

This function searches for a pattern in a character column and replaces each occurrence of that pattern with the pattern you specify.

WITH CHECK OPTION [CV]

This is to do with DML If the subquery includes a WHERE clause, then this option will prevent insertion of rows that wouldn't be seen in the view or updates that would cause a row to disappear from the view. By default, this option is not enabled, which can give disconcerting results.

B*Tree index Definition

Tree structure with root node of the tree points to many nodes at 2nd, 3rd, etc. The leaf nodes of the index tree store the rows' keys, in order, each with a pointer that identifies the physical location of the row.

Other Table Functions

Truncate Table [schema.]table; Drop table [schema.]table; Desc[ribe] [schema.]table; SELECT TABLE_NAME from USER_TABLES

UPDATE

UPDATE table SET col=value where; UPDATE table SET col=subquery where col=subquery;

Data Dictionary Views

USER_TAB_COLUMNS describes the columns of tables, views, and clusters owned by the current user. USER_TAB_PRIVS_MADE lists all grants on objects owned by the user. USER_TAB_PRIVS_RECD lists object grants for which the current user is the grantee. USER_ROLE_PRIVS lists roles granted to the user. SESSION_PRIVS lists the privileges that are currently available to the user. ROLE_TAB_PRIVS contains information about table privileges granted to roles.

Implicit Data Type Conversion

Values that do not share identical data types with function parameters are implicitly converted to the required format if possible. If a function that accepts character input parameter finds a number instead, it is auto converted. If a function that accepts a number or a date parameter encounters a character value, there are specific conditions under which automatic data type conversion occurs. DATE and NUMBER data types are very strict compared to VARCHAR2 and CHAR.

Self-Join

When the join columns originate from the same table a self-join is required. Conceptually the source table is duplicated to create the target table. SELECT t1.name n1, t2.name n2 FROM table1 t1 join table1 t2 ON (t1.Id=t2.Id);

OUTER JOIN

When the source and target table have nonmatching join column values or where not every record in the source table be matched to at least one row in the target table. A RIGHT OUTER JOIN is one of the JOIN operations that allow you to specify a JOIN clause. It preserves the unmatched rows from the second (right) table, joining them with a NULL in the shape of the first (left) table. A LEFT OUTER JOIN B is equivalent to B RIGHT OUTER JOIN A, with the columns in a different order. FULL OUTER JOIN performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition [LEFT | RIGHT | FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)]

NATURAL JOIN

When the source and target tables share identically named columns and where every record in the source table can be matched to at least one row in the target table. FROM TABLE 1 [NATURAL JOIN table2] | [JOIN table USING (column_name)] | JOIN table2 ON (table1.column_name = table2.column_name)]

WHERE

Where clause rows are included based on whether they fulfill the conditions listed and are sometimes known as row-level results. col BETWEEN v1 and v2 col IN (v1,v2) col LIKE 'A%' OR LIKE 'A_' col IS NULL col NOT expression

ROLLBACK

While in progress, Oracle keeps an image of the data as it was before the transaction. The state of the data before the rollback is that the data has been changed, but the info needed to reverse the changes is available. ROLLBACK [TO SAVEPOINT savepoint];

NEXTVAL

a session can select either the next value with the NEXTVAL pseudo column, which forces the sequence to increment. The NEXTVAL will be globally unique: each session that selects it will get a different, incremented, value for each SELECT.

Alternative Quote Operator (q)

q'<..................>' "q<>" q'[..................]' "q[]" q'X..................X' "qX'

CURRVAL

the last (or "current") value issued to that session with the CURRVAL pseudo column. The CURRVAL will be constant for one session until it selects NEXTVAL again. The CURRVAL of a sequence is the last value issued to the current session, not necessarily the last value issued. You cannot select the CURRVAL until after selecting the NEXTVAL.

Restricting Results

1. Consider the entire row-level dataset. 2. Limit the dataset based on any WHERE clause conditions. 3. Segment data into groups specified in the GROUP BY 4. apply any aggregate functions 5. limit group data for HAVING clause.

bitmap index

A bitmap index stores the rowids associated with each key value as a bitmap. Combining of bitmaps is very fast and can be used to implement complex Boolean algebra operations with many conditions on many columns using any combination of AND, OR, and NOT operators. NULL is just another distinct value, which will have its own bitmap.

composite index

A composite index is an index on several columns. Composite indexes can be on columns of different data types, and the columns do not have to be adjacent in the table.

CROSS JOIN

A cross join or Cartesian product derives its names from mathematics, where it is also referred to as a cross product between two sets or matrices. This join creates one row of output for every combination of source and target table rows. The results from a cross join may be used to identify orphan rows or generate a large data set for use in application testing. [CROSS JOIN table2]

Predefined User Roles

CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE, DELETE_CATALOG_ROLE, EXECUTE_CATALOG_ROLE, SELECT_CATALOG_ROLE, RECOVERY_CATALOG_OWNER, HS_ADMIN_ROLE, AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE, SNMPAGENT

CONSTRAINT Definitions

CONSTRAINT col_uk UNIQUE CONSTRAINT col_nn NOT NULL CONSTRAINT col_pk PRIMARY KEY CONSTRAINT col_fk REFERENCES table (col) CONSTRAINT col_ck CHECK (expression)

Creating Sequence [cs]

CREATE SEQUENCE [schema.]sequencename [INCREMENT BY number] [START WITH number] [MAXVALUE number | NOMAXVALUE] [MINVALUE number | NOMINVALUE] [CYCLE | NOCYCLE] [CACHE number | NOCACHE] [ORDER | NOORDER] ;

CREATE INDEX

CREATE [UNIQUE 1 BITMAP] INDEX [schema.]indexname ON [schema.]tablename (column [, column...]) ;

CREATE Table

CREATE [schema.]table (col1 Serial,col2 ENUM ('M','F') [DEFAULT expr]); CREATE [schema.]table AS subquery;

CREATE VIEW [CV]

CREATE | REPLACE [FORCE | NOFORCE] VIEW [schema.]viewname [alias(s)] As subquery [WITH CHECK OPTION [CONSTRAINT name_cko]] [WITH READ ONLY [CONSTRAINT name_wro]]

B*Tree index

Can be either unique or nonunique. A unique index will not permit insertion of two rows with the same key values; a nonunique index will permit as many rows as you want with the same values. Nonunique is the default.

HAVING

Clustering rows using the GROUP BY clause and applying an aggregate function to these groups returns results often referred to as group-level results. The HAVING clause provides the language to restrict group-level results. HAVING(group_conditions)

CYCLE [cs]

Controls the behavior on reaching MAXVALUE or MINVALUE. The default behavior is to give an error, but if CYCLE is specified the sequence will return to its starting point and repeat.

DELETE

DELETE FROM table WHERE col=value;

Date Arithmetic

Difference between two date items represents the number of days between them. Num always refers to number of days. Date1 - Date2 = Num1 Date1 - Num1 = Date2 Date1 = Date2 + Num1

COMMIT

Ends current transaction, making changes both permanent and visible to other sessions. The state of data before commit is that the changes have been made, but all sessions other than the one that made the changes are redirected to copies of the data in its prechanged form. COMMIT; SET AUTOCOMMIT ON SET AUTOCOMMIT OFF

GROUPING SETS

It provides a finer level of detail in specifying which groups you wish to display, with optional subtotals and an optional grand total. With GROUPING SETS, you can be more selective with the results of a GROUP BY clause, and specify particular groups you wish to include in your output, omitting the rest—potentially reducing processing time accordingly.

Nonequijoins

Match column values from different tables based on an inequality expression. The value of the join column in each row in the source table is compared to the corresponding values in the target table. A match is found if the expression used in the join, based on an inequality operator, evaluates to true. JOIN table2 ON (table1.col_name < table2.col_name) | JOIN table2 ON (table1.col_name > table2.col_name) | JOIN table2 ON (table1.col_name <= table2.col_name) | JOIN table2 ON (table1.col_name >= table2.col_name)

Correlated subqueries

More complex method of execution. If a subquery references columns in the parent query, then its result will be dependent on the parent query. Making it possible to evaluate the subquery before evaluating the parent query. It must be evaluated once for every row in the outer query. It can be single or multiple row depending on comparison operator.

ORDER BY

ORDER BY cols | expression | position ASC | DESC NULLS FIRST | NULLS LAST

ORDER [cs]

Only relevant for a clustered database: ORDER forces all instances in the cluster to coordinate incrementing the sequence, so that numbers issued are always in order even when issued to sessions against different instances.

NLS_SESSION_PARAMETERS

Optional national language support parameters (nls_parameters) are useful for specifying the language and format in which the names of date and numeric elements are returned. These are usually absent, and the default values for elements such as day or month names and abbreviations are used. There is a publicly available view called NLS_SESSION_PARAMETERS that contains the NLS parameters for your current session. Example: ALTER SESSION set NLS_CURRENCY='GBP';