ORACLE SQL - BASIC

--****************************************************
--RETRIEVING DATA USING THE SQL SELECT STATEMENT
--****************************************************
SELECT * FROM EMP;
SELECT * FROM DEPT;

--****************************************************
--RESTRICTING AND SORTING DATA
--****************************************************
SELECT * FROM EMP WHERE DEPTNO = 10;
SELECT * FROM EMP WHERE SAL > 800;
SELECT * FROM EMP WHERE SAL >= 800;
SELECT * FROM EMP WHERE DEPTNO IN (10,20);
SELECT * FROM EMP WHERE MGR IS NULL;
SELECT * FROM EMP WHERE HIREDATE BETWEEN '17-DEC-1980' AND '01-MAY-1981';
SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 2000;
SELECT * FROM EMP WHERE ENAME LIKE '%MART%';
SELECT * FROM EMP ORDER BY ENAME DESC;
SELECT * FROM EMP ORDER BY ENAME ASC;

--****************************************************
--USING SINGLE – ROW FUNCTIONS TO CUSTOMIZE OUTPUT
---DUAL IS A DUMMY TABLE THAT YOU CAN USE TO VIEW RESULTS FROM FUNCTIONS AND CALCULATIONS.
--****************************************************
SELECT SYSDATE FROM DUAL;
SELECT  CONCAT('HELLO' , 'WORLD' )  FROM DUAL;
SELECT  SUBSTR('HELLOWORLD', 1,5)   FROM DUAL;
SELECT  LENGTH('HELLOWORLD')  FROM DUAL;
SELECT  INSTR('HELLOWORLD', 'W' )  FROM DUAL;
SELECT  LPAD(SAL, 10, '*' )  FROM EMP;
SELECT  RPAD(SAL, 10, '*' )  FROM EMP;
SELECT  REPLACE('JACK AND JUE', 'J' , 'BL' )   FROM DUAL;
SELECT  TRIM('H' FROM 'HELLOWORLD')  FROM DUAL;
SELECT  ROUND(45.923, 2), ROUND(45.923,0), ROUND (45.923, -1) FROM DUAL;
SELECT  TRUNC(45.923, 2), TRUNC(45.923), TRUNC (45.923, -1) FROM DUAL;

--****************************************************
--USING CONVERSION FUNCTIONS AND CONDITIONAL EXPRESSIONS
--****************************************************
SELECT ENAME, TO_CHAR(HIREDATE, 'DD MONTH YYYY') AS HIREDATE FROM EMP;  --TO CHAR WITH DATE CONVERSION
SELECT TO_CHAR ( SAL, '$99,999.00' ) AS SALARY FROM EMP WHERE ENAME = 'SMITH' ;  ---TO_CHAR FUNCTION WITH NUMBERS

--NVL (EXPR1, EXP2)
SELECT ENAME, SAL, NVL(COMM, 0), (SAL*12) + (SAL*12*NVL(COMM, 0)) ANNUAL_SAL FROM EMP;

--NVL2 (EXPR1, EXPR2, EXPR3)
SELECT ENAME, SAL, COMM, NVL2( COMM, 'SAL+COMM','SAL') INCOME FROM EMP --WHERE DEPTNO IN (10,20);
SELECT * FROM EMP;

--NULLIF (EXPR1, EXPR2)
SELECT ENAME, LENGTH(ENAME) AS EXPR1, JOB, LENGTH(JOB) AS EXPR2, NULLIF( LENGTH ( ENAME), LENGTH( JOB) ) RESULT FROM EMP;

---CASE EXPRESSION
SELECT ENAME, JOB, SAL AS CURR_SAL,
CASE JOB WHEN 'MANAGER' THEN 1.10*SAL
  WHEN 'SALESMAN' THEN 1.15*SAL
WHEN 'CLERK' THEN 1.20*SAL
ELSE SAL END "REVISED SALARY"
FROM EMP;

---DECODE EXPRESSION
SELECT ENAME, JOB, SAL AS CURR_SAL,
DECODE(JOB, 'MANAGER' , 1.10*SAL,
'SALESMAN',  1.15*SAL,
'CLERK', 1.20*SAL, SAL) AS REVISED_SALARY
FROM EMP;


--****************************************************
--REPORTING AGGREGATED DATA USING THE GROUP FUNCTIONS
--****************************************************
--AVG --COUNT --MAX --MIN --SUM
SELECT AVG(SAL), MAX (SAL), MIN(SAL), SUM(SAL) FROM EMP WHERE JOB LIKE '%MANAGER%';
SELECT AVG(COMM) FROM EMP;  --GROUP FUNCTIONS IGNORE NULL VALUES IN THE COLUMN
SELECT AVG(NVL(COMM, 0)) FROM EMP;  --THE NVL FUNCTION FORCES GROUP FUNCTIONS TO INCLUDE NULL VALUES

--COUNT(EXPR) RETURN THE NUMBER OF ROWS WITH NUMBER OF ROWS WITH NON-NULL VALUES FOR EXPR
SELECT COUNT(*) FROM EMP WHERE DEPTNO = 10;  --RETURN THE NUMBER OF ROWS IN TABLE
SELECT COUNT(COMM) FROM EMP WHERE DEPTNO = 10;
SELECT COUNT(DISTINCT DEPTNO) FROM EMP;

--GROUP BY
SELECT DEPTNO, AVG(SAL)
FROM EMP
GROUP BY DEPTNO;

--RESTRICTING GROUP RESULTS WITH THE HAVING CLAUSE
/*When you use the HAVING clause, the Oracle server restricts group as follows:
Rows are grouped
The group function is applied.
Groups matching the HAVING clause are displayed.*/
/*
SELECT column, group_function
FROM table
[ WHERE condition ]
[ GROUPBY group_by_expression ]
[ HAVING group_condition ]
[ ORDER BY column ];
*/
SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MAX(SAL) > 1000 ;

--****************************************************
--DISPLAYING DATA FROM MULTIPLE TABLES USING JOINS
--****************************************************
--Method-1
SELECT EMP.ENAME, EMP.JOB, DEPT.DNAME, DEPT.LOC
FROM EMP JOIN DEPT
USING ( DEPTNO ) ;

--Method-2
SELECT EMP.ENAME, EMP.JOB, DEPT.DNAME, DEPT.LOC
FROM EMP JOIN DEPT
ON (EMP.DEPTNO = DEPT.DEPTNO) ;

--Method-2
SELECT EMP.ENAME, EMP.JOB, DEPT.DNAME, DEPT.LOC
FROM EMP
INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;


--****************************************************
--USING SUBS QUERIES TO SOLVE QUERIES
--****************************************************
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'SMITH');

SELECT *
FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'SMITH')
AND JOB = 'MANAGER';

SELECT DEPTNO, MIN(SAL)
FROM EMP
GROUP BY DEPTNO
HAVING MIN(SAL) >
( SELECT MIN(SAL)
FROM EMP
WHERE DEPTNO = 20) ;



--****************************************************
--USING THE SET OPERATORS
--****************************************************
SELECT DEPTNO
FROM EMP
UNION   ---Display each combination only once. The UNION operator returns results from both queries after eliminating duplications.
SELECT DEPTNO
FROM DEPT;


SELECT DEPTNO
FROM EMP
UNION ALL   ---The UNION ALL operator returns results from both queries, including all duplications.
SELECT DEPTNO
FROM DEPT;

SELECT DEPTNO
FROM EMP
INTERSECT   ---The INTERCEST operator returns rows that are common to both queries.
SELECT DEPTNO
FROM DEPT;


SELECT DEPTNO
FROM DEPT
MINUS  ---The MINUS operator returns rows in the first query that are not present in the second query.
SELECT DEPTNO
FROM EMP;

/*SUMMARY
UNION to return all distinct rows
UNION ALL to return all rows, including duplicates
INTERSECT to return all rows that are shared by both queries
MINUS to return all distinct rows that are selected by the first query, but not by the second
ORDER BY only at the very end of the statement
*/


--****************************************************
--MANAGING TABLES USING DML STATEMENTS Add new rows to a table // Modify existing rows in a table // Remove existing rows from table
--****************************************************
SELECT * FROM DEPT;
INSERT INTO DEPT VALUES(50,'MARKETING','INDIA');
UPDATE DEPT SET LOC = 'MUMBAI' WHERE DEPTNO = 50;
DELETE FROM DEPT WHERE DEPTNO = 50;

COMMIT;
SAVEPOINT TRANS_1;
ROLLBACK;

--****************************************************
--INTRODUCTION TO DATA DEFINITION LANGUAGE
--****************************************************
CREATE TABLE
DATA TYPES
CONSTRAINTS
INDEXES
DB OBJECTS