LEARN SQL (PART-1)

Data Manipulation Language (DML)	v  SELECT v  INSERT v  UPDATE v  DELETE v  MERGE
Data Definition Language (DDL)	v  CREATE v  ALTER v  DROP v  RENAME v  TRUNCATE v  COMMENT
Data Control Language (DCL)	v  GRANT v  REVOKE
Data Transaction Control (TCL)	v  COMMIT v  ROLLBACK v  SAVEPOINT

SQL STATEMENT

 

 

 

 

Limiting the rows that are selected	Select employee_id, last_name, job_id from employees where department_id= 90
Character string and dates	Select last_name, job_id, department_id from employees where last_name = ‘Whalen’
Comparison Conditions   >,<,<=,>=,<>,=, between, and, like is null	Select last_name, salary, from employees where salary between 2500 and 3500;
	Select employee_id, last_name, salary from emplyees where manager_id in (100, 101, 201);
	Select first_name from employees where first_name like ‘S%’;
	Select last_name, manager_id from employees where manager_id is null;
Logical Conditions   AND, OR, NOT	Select employee_id, last_name, job_id, salary from employees where salary >=1000 AND job_id like ‘%MAN%’;
	Select employee_id, last_name, job_id, salary from employees where job_id NOT IN (‘IT_PROG’, ‘ST_CLERK’, ‘SA_REP’)
Rules of Precedence     ·        Arithmetic operations (+,-,/,*) ·        Concatenation operator (||) ·        Comparison conditions  (>,=,<, ) ·        Logical Operators:       Is (not) NULL,       LIKE,       (NOT) IN,       (NOT) BETWEEN; Not equal to; Not logical condition; AND logical condition;                          OR logical condition;	Select last_name, job_id, salary from employees Where job_id = ‘SA_REP’ OR job_id = ‘AD_PRES’ AND salary > 1000;
	Select last_name, job_id, department_id, hire_date from employees Order by hire_date;
	Select employee_id, last_name, salary*12 annualsal from employees Order by annualsal, last_name desc;
Substitution Variable   Temporary store values with single-ampersand(&), Double-ampersand(&&) substitution	Select employee_id, last_name, salary, department_id from employees where employee_id = &employee_num;
	Select employee_id, last_name, job_id, &&column_name from employees order by &column_name;

SELECT Statement & Restricting and Shorting Data
Single Row Function & Group Function

 

Single Row Functions
v Character Function ·        Case-Manipulation                         lower,UPPER, Initcap   ·        Character-Manipulation CONCAT, SUBSTR, LENGTH INSTR,LPAD | RPAD, TRIM REPLACE, TRIM, REPLACE	SELECT employee_id, last_name, department_id FROM employees WHERE LOWER/Upper/InITCAP(last_name) = 'higgins';
	Function                                                                Result -REPLACE--------('JACK and JUE','J','BL')--------- BLACK and BLUE -LENGTH---------('HelloWorld')------------------- 10 -INSTR-------------('HelloWorld', 'W')------------ 6 -LPAD---------------(salary,10,'*')------------------ *****24000 -RPAD--------------(salary, 10, '*')----------------- 24000***** -CONCAT----------('Hello', 'World')-------------- HelloWorld -TRIM---------------('H' FROM 'HelloWorld')---- elloWorld -SUBSTR------------('HelloWorld',1,5)------------ Hello
v Number Round, Trunc, MOD	SELECT ROUND(45.923,2), ROUND(45.923), ROUND(45.923,-1) FROM DUAL;
	Function                                                    Result -MOD----------(1600, 300)------------------ 100 ROUND--------(45.926, 2)-------------------45.93 TRUNC--------(45.926, 2)-------------------45.92
v Date Date, Time	SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS FROM employees WHERE department_id = 90;
	SELECT employee_id, hire_date, MONTHS_BETWEEN (SYSDATE, hire_date) TENURE, ADD_MONTHS (hire_date, 6) REVIEW, NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date) FROM employees WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 70;
	Function                              Result MONTHS_BETWEEN----- Number of months between two dates ADD_MONTHS------- Add calendar months to date NEXT_DAY------------ Next day of the date specified LAST_DAY------------- Last day of the month ROUND---------------- Round date TRUNC---------------- Truncate date
v Conversion Functions ·        Implicit data type Conversion ·        Explicit data type conversion	SELECT employee_id, TO_CHAR(hire_date, 'MM/YY') Month_Hired FROM employees WHERE last_name = 'Higgins';
	SELECT last_name, TO_CHAR(hire_date, 'fmDdspth "of" Month YYYY fmHH:MI:SS AM') HIREDATE FROM employees;
v General Functions • NVL= Converts a null value to an actual value • NVL2 = If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type. • NULLIF = Compares two expressions and returns null if they are equal; returns the first expression if they are not equal  • COALESCE = Returns the first non-null expression in the expression list	SELECT last_name, salary, commission_pct, (salary*12) + (salary*12*commission_pct) AN_SAL FROM employees;
	• NVL (expr1, expr2) • NVL2 (expr1, expr2, expr3) • NULLIF (expr1, expr2) • COALESCE (expr1, expr2, ..., exprn)
v Conditional Expressions ·       Provide the use of IF-THEN-ELSE logic within a SQL statement Use two method: ·       CASE expression ·       DECODE function	CASE Expression: Facilitates conditional inquiries by doing the work of an IF-THEN-ELSE statement. Select last_name, job_id, salary, Case job_jd when ‘IT_PROG’ Then 1.10*salary When ‘SA_CLERK’ Then 1.15*salary When ‘SA_REP’ Then 1.20*salary Else salary end “Reviced_Salary” From Employees;
	DECODE function: Facilittates conditional inqueries by doing the work of a case expression an IF-THEN-ELSE statement. Select last_name, job_id, salary, Decode (job_id ‘IT_PROG’, 1.10*salary ‘ST_CLERK’, 1.15*salary ‘SA_RIP’, 1.20*salary salary) REVICED_SALARY From employees;
Multiple-Row (Group) Functions
Group functions operate on sets of rows to give one result per group.
v Several Group Functions ·       AVG ·       COUNT ·       MAX ·       MIN ·       STDDEV ·        SUM VARIENCE	Select AVG(salary), MAX(salary), MIN(salary), Sum(salary), Count(commission_pct) from employees where department_id = 80;
v USING GROUP BY CLAUSE	SELECT department_id DEPT_ID, job_id, SUM(salary) From employees GROUP BY department_id, job_id;
v USING HAVING CLAUSE	SELECT job_id, sum(salary) Total_Salary from employees Where job_id not like ‘%REP’ GROUP BY job_id HAVING sum(salary)>10000 Order by sum(salary);

 

 

DISPLAYING DATA FROM MULTIPLE TABLES

A join is used to view information from multiple tables.
v Types of Join ·       Cross Joins ·       Natural Joins ·       Using Joins ·       Full Outer joins ·       Arbitrary join conditions for outer joins

 

 

L)