Basic Commands

Data Definition Language (DDL) statements are used

to define the database structure or schema. Some
examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including
all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
Data Manipulation Language (DML) statements are used
for managing data within schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the
records remain
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
Table

Employee
DCL

Data Control Language (DCL) statements. Some
examples:
GRANT - gives user's access privileges to database
REVOKE - withdraw access privileges given with the
GRANT command
TCL

Transaction Control (TCL) statements are used to manage the
changes made by DML statements. It allows statements to be
grouped together into logical transactions.
COMMIT - save work done
SAVEPOINT - identify a point in a transaction to which you can
later roll back
ROLLBACK - restore database to original since the last COMMIT
SET TRANSACTION - Change transaction options like isolation
level and what rollback segment to use
ALTER
ALTER TABLE table_name ADD column_name
column-definition;

ALTER TABLE table_name MODIFY column_name

column_type;

ALTER TABLE table_name DROP COLUMN

column_name;
ALTER
ALTER TABLE table_name RENAME COLUMN
old_name to new_name;

ALTER TABLE table_name RENAME TO

new_table_name;

ALTER TABLE table_name ADD CONSTRAINT

constraint_name PRIMARY KEY (column1,
column2, ... column_n);
Primary Keys
CREATE TABLE table_name ( column1 datatype
null/not null, column2 datatype null/not null, ...
CONSTRAINT constraint_name PRIMARY KEY
(column1, column2, ... column_n) );

CREATE TABLE table_name ( column1 datatype

null/not null primary key, column2 datatype null/not
null, ...)
Foreign Keys
CREATE TABLE table_name ( column1 datatype
null/not null, column2 datatype null/not null, ...
CONSTRAINT fk_column FOREIGN KEY (column1,
column2, ... column_n) REFERENCES parent_table
(column1, column2, ... column_n) );
Update
UPDATE table SET column1 = expression1, column2 =
expression2, ... column_n = expression_n WHERE
conditions;
Check Constraint
CREATE TABLE table_name ( column1 datatype
null/not null, column2 datatype null/not null, ...
CONSTRAINT constraint_name CHECK
(column_name condition) [DISABLE] );
Unique Constraint
CREATE TABLE table_name ( column1 datatype
[ NULL | NOT NULL ], column2 datatype [ NULL |
NOT NULL ], ... CONSTRAINT constraint_name
UNIQUE (column1, column2, ... column_n) );
Logical Operators : AND and OR operator
AND & OR operator
SELECT ID, NAME, SALARY FROM CUSTOMERS
WHERE SALARY > 2000 AND age < 25;

SELECT ID, NAME, SALARY FROM CUSTOMERS

WHERE SALARY > 2000 OR age < 25;
NOT logical Operator
SELECT first_name, last_name, games 
FROM student_details 
WHERE NOT games = 'Football'
Comparison Operators
SELECT first_name, last_name 
FROM student_details 
WHERE first_name LIKE 'S%';

SELECT first_name, last_name 

FROM student_details 
WHERE first_name LIKE '_a%'; 
Not between (comparison)
SELECT * FROM customers WHERE customer_id
NOT BETWEEN 3000 AND 3500;
Comparison Operators
SELECT first_name, last_name, age 
FROM student_details 
WHERE age BETWEEN 10 AND 15;

The IN operator is used when you want to compare a

column with more than one value. It is similar to an
OR condition.
Comparison Operators
SELECT first_name, last_name, subject 
FROM student_details 
WHERE subject IN ('Maths', 'Science'); 

SELECT first_name, last_name 

FROM student_details 
WHERE games IS NULL;

The IS NULL operator is used to display all the rows

for columns that do not have a value.
Comparison Operators
Aliases for columns
SQL Aliases are defined for columns and tables. Basically aliases
is created to make the column selected more readable.

SELECT first_name AS Name FROM student_details;

or
SELECT first_name Name FROM student_details;

Output:
Name
-------------
Rahul Sharma
Anjali Bhagwat
Aliases for tables
SELECT s.first_name FROM student_details s; 

In the above query, alias 's' is defined for the table
student_details and the column first_name is selected
from the table.
Aliases is more useful when
There are more than one tables involved in a query,
Functions are used in the query,
The column names are big or not readable,
Order By
The ORDER BY clause is used in a SELECT statement
to sort results either in ascending or descending order.

Oracle sorts query results in ascending order by

default.
Order By
SELECT column-list 
FROM table_name [WHERE condition] 
[ORDER BY column1 [, column2, .. columnN] [DESC]];

SELECT name, salary FROM employee ORDER BY

salary;
Order By
SELECT name, salary 
FROM employee 
ORDER BY name, salary DESC; 

The above query sorts only the column 'salary' in

descending order and the column 'name' by ascending
order.
SQL GROUP Functions
SQL COUNT (): This function returns the number of
rows in the table that satisfies the condition specified in
the WHERE condition. If the WHERE condition is not
specified, then the query returns the total number of rows
in the table.

SELECT COUNT (*) FROM employee 

WHERE dept = 'Electronics'; 
SQL GROUP Functions
SQL DISTINCT(): This function is used to select the
distinct rows.

For Example: If you want to select all distinct

department names from employee table, the query
would be:

SELECT DISTINCT dept FROM employee;

SQL GROUP Functions
SQL MAX(): This function is used to get the
maximum value from a column.

To get the maximum salary drawn by an employee, the

query would be:

SELECT MAX (salary) FROM employee;

SQL GROUP Functions
SQL MIN(): This function is used to get the minimum
value from a column.

To get the minimum salary drawn by an employee, he

query would be:

SELECT MIN (salary) FROM employee;

SQL GROUP Functions
SQL AVG(): This function is used to get the average
value of a numeric column.

To get the average salary, the query would be

SELECT AVG (salary) FROM employee;

SQL GROUP Functions
SQL SUM(): This function is used to get the sum of a
numeric column

To get the total salary given out to the employees,

SELECT SUM (salary) FROM employee;

Group By
SELECT product, SUM(sale) AS "Total sales" FROM
order_details GROUP BY product;

SELECT department, MIN(salary) AS "Lowest salary"

FROM employees GROUP BY department;

The Oracle GROUP BY clause is used in a SELECT

statement to collect data across multiple records and
group the results by one or more columns.
SQL HAVING Clause

Having clause is used to filter data based on the group

functions. This is similar to WHERE condition but is
used with group functions. Group functions cannot be
used in WHERE Clause but can be used in HAVING
clause.
having
SELECT dept, SUM (salary) 
FROM employee 
GROUP BY dept 
HAVING SUM (salary) > 25000 

If you want to select the department that has total

salary paid for its employees more than 25000, the sql
query would be like;
Exists
The Oracle EXISTS condition is used in combination
with a subquery and is considered "to be met" if the
subquery returns at least one row. It can be used in a
SELECT, INSERT, UPDATE, or DELETE statement.
Exists/Not Exists
SELECT * FROM customers WHERE EXISTS (SELECT
* FROM order_details WHERE
customers.customer_id = order_details.customer_id);

SELECT * FROM customers WHERE NOT EXISTS

(SELECT * FROM order_details WHERE
customers.customer_id = order_details.customer_id);
NOT IN
SELECT * FROM customers WHERE customer_name
NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft');
UNION
The Oracle UNION operator is used to combine the
result sets of 2 or more Oracle SELECT statements. It
removes duplicate rows between the various SELECT
statements.

SELECT expression1, expression2, ... expression_n

FROM tables WHERE conditions UNION SELECT
expression1, expression2, ... expression_n FROM tables
WHERE conditions;
UNION
SELECT supplier_id FROM suppliers UNION SELECT
supplier_id FROM order_details;

In this Oracle UNION operator example, if a

supplier_id appeared in both the suppliers and
order_details table, it would appear once in your result
set.

There must be same number of expressions in both

SELECT statements with similar data types.
UNION ALL
SELECT supplier_id FROM suppliers UNION ALL
SELECT supplier_id FROM orders;

The Oracle UNION ALL operator does not remove

duplicates.
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables WHERE conditions MINUS SELECT
expression1, expression2, ... expression_n FROM tables
WHERE conditions;
MINUS
Each SELECT statement within the MINUS query must have the
same number of fields in the result sets with similar data types.

SELECT supplier_id FROM suppliers MINUS SELECT

supplier_id FROM orders;

This Oracle MINUS example returns all supplier_id values that

are in the suppliers table and not in the orders table. What this
means is that if a supplier_id value existed in the suppliers table
and also existed in the orders table, the supplier_id value would
not appear in this result set.
INTERSECT
SELECT expression1, expression2, ... expression_n
FROM tables WHERE conditions INTERSECT SELECT
expression1, expression2, ... expression_n FROM tables
WHERE conditions;

Each SELECT statement within the INTERSECT must

have the same number of fields in the result sets with
similar data types.
INTERSECT
SELECT supplier_id FROM suppliers INTERSECT
SELECT supplier_id FROM orders;

SELECT supplier_id FROM suppliers WHERE

supplier_id <= 99 INTERSECT SELECT supplier_id
FROM orders WHERE quantity > 25;
GRANT
You can grant users various privileges to tables. These
privileges can be any combination of SELECT, INSERT,
UPDATE, DELETE, REFERENCES, ALTER, INDEX, or
ALL

GRANT SELECT, INSERT, UPDATE, DELETE ON

suppliers TO smithj;
GRANT
GRANT ALL ON suppliers TO smithj;

GRANT SELECT ON suppliers TO public;

REVOKE
REVOKE privileges ON object FROM user;

REVOKE DELETE ON suppliers FROM anderson;

REVOKE ALL ON suppliers FROM anderson;

REVOKE ALL ON suppliers FROM public;

JOIN
SELECT columns FROM table1 INNER JOIN table2
ON table1.column = table2.column;

SELECT suppliers.supplier_id,
suppliers.supplier_name, orders.order_date FROM
suppliers INNER JOIN orders ON
suppliers.supplier_id = orders.supplier_id;
JOIN
LEFT JOIN
SELECT columns FROM table1 LEFT [OUTER] JOIN
table2 ON table1.column = table2.column;

SELECT suppliers.supplier_id,
suppliers.supplier_name, orders.order_date FROM
suppliers LEFT OUTER JOIN orders ON
suppliers.supplier_id = orders.supplier_id;
RIGHT JOIN
SELECT columns FROM table1 RIGHT [OUTER] JOIN
table2 ON table1.column = table2.column;

SELECT orders.order_id, orders.order_date,

suppliers.supplier_name FROM suppliers RIGHT
OUTER JOIN orders ON suppliers.supplier_id =
orders.supplier_id;
RIGHT JOIN
RIGHT JOIN
FULL JOIN
SELECT columns FROM table1 FULL [OUTER] JOIN
table2 ON table1.column = table2.column;

SELECT suppliers.supplier_id,
suppliers.supplier_name, orders.order_date FROM
suppliers FULL OUTER JOIN orders ON
suppliers.supplier_id = orders.supplier_id;
SQL INSERT INTO SELECT Statement
With SQL, you can copy information from one table
into another.

The INSERT INTO SELECT statement copies data

from one table and inserts it into an existing table.
SQL INSERT INTO SELECT Statement
INSERT INTO table2
SELECT * FROM table1;

INSERT INTO table2

(column_name(s))
SELECT column_name(s)
FROM table1;
DELETE WITH EXIST
You may wish to delete records in one table based on
values in another table.

Since you can't list more than one table in the SQL
Server FROM clause when you are performing a delete,
you can use the SQL Server EXISTS clause.
DELETE WITH EXIST
DELETE FROM employees WHERE EXISTS ( SELECT
* FROM contacts WHERE contacts.contact_id =
employees.employee_id AND contacts.contact_id <
100 );

This SQL Server DELETE example would delete all

records in the employees table where there is a record
in the contacts table whose contact_id is less than 100,
and the contact_id matches the employee_id.
UPPER() LOWER()
UPDATE STUDENTS SET LASTNAME =
UPPER(LASTNAME) WHERE LASTNAME = 'Jones'

UPDATE STUDENTS SET LASTNAME =

LOWER(LASTNAME) WHERE LASTNAME = 'Jones'
FUNCTIONS
POWER( m, n )

CEIL( number )

FLOOR( number )
ANY
SELECT column_name(s) FROM table_name WHERE
column_name ANY (value1,value2,...);

SELECT * FROM emp WHERE sal = any (800,1000);

SELECT * FROM emp WHERE sal > any (800,1000);

SELECT * FROM emp WHERE empno <= any (7369,

7521, 7782, 7900);