ALTER TABLE <table_name>
ADD <column_name> <data_type>;

ALTER TABLE <table_name>
DROP <>;

ALTER TABLE countries
ADD ( CONSTRAINT countr_reg_fk
        FOREIGN KEY (region_id)
           REFERENCES regions(region_id)
    ) ;

UPDATE <table_name>
SET <column_name> = <value>
WHERE <condition>;

ALTER TABLE <table_name>
DROP <column_name>;

DELETE FROM <table_name>
WHERE <column_name>=value;

DESC <table_name>
//gives the tottal structure of the table

SELECT * FROM <table_name>;
// entire table is displayed

'q operator ' :
select concat(last_name , q'['s department id = ]')
from employees;

output : Fay's department id = 30

DISTINCT OPERATOR:

SELECT DISTINCT <column_name>
FROM <table_name>;

Important :
> character string and dates are enclosed within single quotes ('')
> character strings are case sensitive
> date values are format sensitive
      for oracle date format : 'DD-MON-YY'
                                        eg: '12-JAN-98'
     for MYSQL , date format : 'YYYY/MM/DD'

Comparison Operators :
< , > , >= , <= , <>(not equal to) , BETWEEN..AND... , IN ( ) , LIKE ' ' , IS NULL , IS NOT NULL
For MYSQL :

WHERE salary BETWEEN 3000 AND 3400

WHERE department_id IN ('20','30') [OR Logic Used] // ie depatment_id is 20 or 30 .
WHERE department_id IN (20,30)  // is also valid
WHERE last_name IN ('Fay' , 'Grant'); // Strings have to be in single quotes
WHERE last_name IN (Fay , Grant); // ERROR cause Strings have to be in single quotes
WHERE hire_date IN ('1994-05-02' , '2003-08-23'); // Dates have to be in single quotes
WHERE hire_date IN (1994-05-02 , 2003-08-23); // ERROR because Dates have to be in single quotes

LIKE operator is used for STRINGS , searching strings or characters , operators used are :
 %s = denotes any number of characters before s , but last alphabet should be s.
 _s  = denotes that there should be only one single alphabet before s , it can be anything , but only 1.
WHERE   last_name LIKE '%a%';
WHERE    hire_date  LIKE '%94%';
WHERE   department_id LIKE '%0';

IS NULL :
WHERE commission_pct IS NULL ;
WHERE department_id IS NOT NULL;

LOGICAL OPERATORS :
AND , OR , NOT

AND , OR can be applied easily ..

For NOT Operator :
WHERE department_id NOT IN (20,30);
WHERE salary NOT BETWEEN 2000 AND 3000;
WHERE last_name NOT LIKE '%a';
WHERE commission_pct IS NOT NULL;

ORDER BY :

ORDER BY department_id ASC; (default)
ORDER BY department_id DESC;

CONCATENATION OPERATOR :

-- SQL Server / Microsoft Access
SELECT FirstName + ' ' + LastName As FullName FROM Customers

-- Oracle
SELECT FirstName || ' ' || LastName As FullName FROM Customers

-- MySQL
SELECT CONCAT(FirstName, ' ', LastName) As FullName FROM Customers

Functions :

1. Single Row functions -
>operates on a single row and returns a single output
>examples : Date functions ,
            Character functions [lower,upper,initcap,concat,substr,lpad,rpad,
                                 length],
            General functions - [NVL,NVL2,NULLIF,COALESCE,CASE.DECODE] ,
            Number functions ,
            Conversion functions -[explicit(by the user) , implicit(by the server)]

IMPLICIT Function - server can convert :
                   1.char --> number/date data type
                     eg : where hire_date > '1992-09-08'
                     here,the date is automatically converted into date datatyp
                   2.number --> char data type 
                    eg: if user enters a value 200000 as a number into 
                        a column that accepts only char, then it automatically
                        gets updated as 2.

EXPLICIT Function : Three different types :
1. TO_CHAR(Number,'format') : to_char(salary,'$999999')

   TO_CHAR(Date,'format') : format can be the following 
                       YYYY-MM-DD = 1994-08-04
                       YEAR-MONTH-DAY=Nineteen ninety four august four
                       dd "of" month = 13 of  october 
                       ddspth = thirteenth 

2. TO_NUMBER(Date/char)

3. TO_DATE(Number/char,format)

GENERAL Functions :

1. NVL(exp1,exp2) - converts null value(exp1) to actual value(exp2)
2. NVL2(exp1,exp2,exp3) - if exp1!=null,return exp2..else return exp3
3. NULLIF(exp1,exp2) - check if both exp's are equal,then return null 
                       else return exp1
4. COALESCE(exp1,exp2,exp3,....,expn) - returns first not null exp

CONDITIONAL EXPRESSIONS :

1.CASE
2.DECODE

2. Multiple Row Functions - operates on multiple rows

GROUP FUNCTIONS :

Operates on sets on rows and gives one result per group.

Group function is placed after the SELECT keyword.
A query can have multiple Group functions at a time.

eg: SELECT AVG(salary), MIN(salary) ,MAX(salary) , SUM(salary)
    FROM employees
    WHERE job_id LIKE '%REP%';

eg: SELECT MIN(hire_date) , MAX(hire_date)
    FROM employees;

eg :SELECT MAX(last_name) , MIN(last_name)
    FROM employees;

HENCE , MAX() and MIN() Can be used with haracter , numbers , date data types.
AVG() , STDDEV() , VARIANCE() , SUM() --> Only with NUMERIC data types.

The COUNT() function returns the number of rows that matches a specified criteria.

There are three different types in which we can use Count():
1.Count(*)    returns all the no:of rows (null values included)
2.Count(Expr) returns all NON NULL values in the column identified by Expr
3.Count(DISTINCT Expr) returns NON NULL , NOT DUPLICATE values



eg: SELECT COUNT(*)  // returns 20
    FROM employees; 

    SELECT COUNT(commision_pct) //returns 4
    FROM employees;

    SELECT COUNT(DISTINCT department_id) // returns 7
    FROM employees;

    SELECT AVG(NVL(commision_pct,0)) //considers null values as 0.
    FROM employees;

GROUPING ROWS:

Rows in a table can be divided or separated by using GROUP BY clause.

RULES TO BE FOLLOWED:
1. All the columns that are not in the Group Function , have to be included
   in the GROUP BY clause 
   eg : SELECT department_id , AVG(salary)
        FROM employees
        GROUP BY department_id;

That is , it is not mandatory that department_id has to be in the SELECT
list , but IF department_id IS PRESENT in the SELECT list , 
then it is MUST that GROUP BY clause WILL have department_id 

GROUPING MORE THAN ONE COLUMN :
SELECT department_id , job_id , avg(salary)
FROM employees
GROUP BY department_id , joib_id
ORDER BY job_id;

WHERE CLAUSE with Group By Clause :
SELECT department_id , job_id , avg(salary)
FROM employees
WHERE department_id >40 //this WHERE is correct but image's WHERE is incorrect.
GROUP BY department_id , job_id
ORDER BY job_id;

We can correct the error using HAVING Clause as follows:
SELECT department_id , AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary)>8000;

The HAVING Clause basically refines from the final output , ie the output is 
avg(salary) , now by applying HAVING Clause , we display only those average 
salaries that are greater than 8000.

NESTING GROUP FUNCTIONS:
SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

GROUP BY Clause is MANDATORY in Nesting Group Functions .

DISPLAYING DATA FROM MULTIPLE TABLES :

Joins :
1. Natural join
2. Outer join 
3. Cross join

Basic Syntax :

SELECT column_name1,2,..
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING(column_name)] |
[JOIN table2 ON (table1.column_name = table2.column_name)] |
[LEFT | RIGHT | FULL OUTER JOIN table2 ON (table1.column_name=table2.column_name)] |
[CROSS JOIN table2 ] ;

USING CLAUSE:

When several columns in both tables have same names but different data types,then USING clause is used that specifies which column has to be chosen to join the tables.

FROM table1 JOIN table2
USING(column_name that has same name + same data type in both tables)

ON CLAUSE :

When we specify the column names

FROM table1 
JOIN table2
ON   (table1.column_name=table2.column_name);

1.NATURAL Join is based on the COMMON column name , that has same values in
 both the tables. eg: departments and employees , both the table had a common
column 'department_id' . Hence the tables can be joined together using NATURAL JOIN

CONSTRAINTS while creating a table :
1.NOT NULL
2.PRIMARY KEY
3.FOREIGN KEY
4.CHECK
5.DEFAULT
6.UNIQUE

USING Clause :
When same name columns but different datatypes hence we specify from our side .

eg: SELECT department_id , department_name , location_id , city
    FROM departments
    NATURAL JOIN locations
    USING (department_id);

SELF JOIN :

Joining a table to itself is called self join.

INNER JOIN :

It displays ONLY matched rows , any unmatched rows are not returned. Queries using NATURAL JOIN , USING(),ON() uses inner joins

OUTER JOIN :

It displays unmatched rows :

1. LEFT OUTER JOIN : It returns inner join rows + unmatched rows from left table.
                     Displays all the rows of left table even though there is no
                     match between the two tables .

2. RIGHT OUTER JOIN : It returns inner join rows + unmatched rows from right table.
                      Displays all the rows of left table even though there is no                             match between the two tables .

3. FULL OUTER JOIN : It returns inner join rows + results of left and right outer joins.

CROSS JOIN : It joins all the rows in table1 with all the rows in table2.
           

SUB QUERIES : 

Two types :

1.sub-queries that return singe value as a result:

#SELECT job_id
 FROM employees
 WHERE job_id = ( SELECT job_id 
                 FROM employees
                 WHERE employee_id =141);

#USING GROUP FUNCTIONS :
SELECT last_name , job_id
FROM employees
WHERE salary = ( SELECT MIN(salary)
                 FROM employees);

#USING HAVING CLAUSE :
SELECT department_id , MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > ( SELECT MIN(salary)
                       FROM employees
                       WHERE department_id =50 );

2.sub-queries that return multiple values as a result:

SELECT salary , last_name
FROM employees
WHERE salary < ANY 
                   ( SELECT salary
                     FROM employees
                     WHERE job_id = 'IT_PROG');

hence it compares all the values with the returned values (say 5000,7840,5880 are the returned values , hence the names of those employees whose salary is less than 7840 will be selected ) 

SELECT last_name , salary 
FROM employees
WHERE salary < ALL 
                    ( SELECT salary
                      FROM employees
                      WHERE job_id = 'IT_PROG');

Hence here , the output name will have salary < 5000 ,if previous test cases are considered.