SQL and PL/SQL

Where clause, Group By clause, Having clause, Order By clause & Select.

Max. columns in a table is 255. Max. Char size is 255, Long is 64K & Number is 38 digits.

Cannot Query on a long column.

Char, Varchar2 Max. size is 2000 & default is 1 byte.

Number(p,s) p is precision range 1 to 38, s is scale -84 to 127.

Long Character data of variable length upto 2GB.

Date Range from Jan 4712 BC to Dec 4712 AD.

Raw Stores Binary data (Graphics Image & Digitized Sound). Max. is 255 bytes.

Mslabel Binary format of an OS label. Used primarily with Trusted Oracle.

SELECT column_name(s) FROM table_name

SELECT DISTINCT column_name(s) FROM table_name

SELECT column FROM table WHERE column operator value

SELECT column FROM table WHERE column LIKE pattern

SELECT column,SUM(column) FROM table GROUP BY column

SELECT column,SUM(column) FROM table GROUP BY column HAVING SUM(column) condition value

Note that single quotes around text values and numeric values should not be enclosed in quotes. Double quotes may be acceptable in some databases.

Indexes are optional structures associated with tables used to speed query execution and/or guarantee uniqueness. Create an index if there are frequent retrieval of fewer than 10-15% of the rows in a large table and columns are referenced frequently in the WHERE clause. Implied tradeoff is query speed vs. update speed. Oracle automatically update indexes. Concatenated index max. is 16 columns.

Synonyms is the alias name for table, views, sequences & procedures and are created for reasons of Security and Convenience.

Two levels are Public - created by DBA & accessible to all the users. Private - Accessible to creator only. Advantages are referencing without specifying the owner and Flexibility to customize a more meaningful naming convention.

= Equal

<> or != Not equal

> Greater than

< Less than

>= Greater than or equal

<= Less than or equal

BETWEEN Between an inclusive range

LIKE Search for a pattern

Multiple columns can be returned from a Nested Subquery.

CREATE TABLE - creates a new database table

ALTER TABLE - alters (changes) a database table

DROP TABLE - deletes a database table

CREATE INDEX - creates an index (search key)

DROP INDEX - deletes an index

Mutating Table is a table that is currently being modified by an Insert, Update or Delete statement. Constraining Table is a table that a triggering statement might need to read either directly for a SQL statement or indirectly for a declarative Referential Integrity constraints. Pseudo Columns behaves like a column in a table but are not actually stored in the table. E.g. Currval, Nextval, Rowid, Rownum, Level etc.

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader : the data itself and the control file. The control file describes the data to be loaded. It describes the Names and format of the data files, Specifications for loading data and the Data to be loaded (optional). Invoking the loader sqlload username/password controlfilename <options>.

Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

SELECT column_name FROM table_name WHERE column_name IN (value1,value2,..)

Sort the Rows:

SELECT column1, column2, ... FROM table_name ORDER BY columnX, columnY, ..

SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC

SELECT column1, column2, ... FROM table_name ORDER BY columnX DESC, columnY ASC

DELETE FROM table_name WHERE column_name = some_value

Delete All Rows:

DELETE FROM table_name or DELETE * FROM table_name

INSERT INTO table_name VALUES (value1, value2,....)

INSERT INTO table_name (column1, column2,...) VALUES (value1, value2,....)

JOIN is the form of SELECT command that combines info from two or more tables.

Types of Joins are Simple (Equijoin & Non-Equijoin), Outer & Self join.

Equijoin returns rows from two or more tables joined together based upon a equality condition in the WHERE clause.

Non-Equijoin returns rows from two or more tables based upon a relationship other than the equality condition in the WHERE clause.

Outer Join combines two or more tables returning those rows from one table that have no direct match in the other table.

Self Join joins a table to itself as though it were two separate tables.

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete. Use CRSQ to answer multipart questions whose answer depends on the value in each row processed by parent statement.

Intersect is the product of two tables listing only the matching rows.