===========================================================================================

OFFESET -FETCH 
================

=> IT NEEDS ORDER BY CLAUSE

OFFSET ---HOW MANY ROWS TO SKIP -----FOR OFFEST FETCH IS NOT MANDATORY
FETCH ---HOW MANY ROWS TO FETCH AFTER OFFSET----- BUT FOR FETCH OFFSET IS MANDATORY

SELECT ENAME, SAL, JOB FROM EMP

=> WITHOUT FETCH

SELECT ENAME, SAL, JOB FROM EMP order by sal desc

SELECT ENAME, SAL, JOB FROM EMP ORDER BY SAL DESC OFFSET 1 ROWS

=> WITH FETCH = > 

SELECT ENAME, SAL, JOB FROM EMP order by sal desc

SELECT ENAME, SAL, JOB FROM EMP ORDER BY SAL DESC OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY

=> I WANT TO SEE THE RECORD OF THE PERSON HAVING TOP SALARY USING OFFSET -FETCH

SELECT ENAME, SAL, JOB FROM EMP ORDER BY SAL DESC OFFSET 0 ROWS FETCH NEXT 1 ROW ONLY

SELECT distinct top 2  ENAME, SAL, JOB FROM EMP 
ORDER BY SAL DESC 


SELECT distinct top 2  ENAME, SAL, JOB FROM EMP 
ORDER BY SAL DESC 

select * or required columns or distinct columns or top 1/10 distinct columns
from
where
group by 
having 
order by 
limit

==============================================================================================================

=>ALREADY HAVE A TABLE KNOWN AS EMP1 ---> CREATE A TABLE FOR DEPT 10 HAVING NAME JOB SAL


select * from emp 
where dept = 10


CREATE TABLE DEPT20(ENAME VARCHAR(20), JOB VARCHAR(20), SAL SMALLMONEY);

--=> INSERT- SELECT
--================

INSERT INTO DEPT20 SELECT ENAME, JOB, SAL FROM EMP WHERE DEPT =20

--=>ALSO INSERT DEPT 20 DATA INTO SAME DEPTNO10 TABLE ---NAME AND JOB

select * from DEPT10

INSERT INTO DEPT10(ENAME, JOB) SELECT ENAME, JOB FROM EMP WHERE DEPT =20

==========================================================================================================
CAN YOU CREATE A TABLE WITHOUT GUI OPTION OR WITHOUT CREATE STATEMENT

COPY OR CLONE OF THE TABLE
 
CREATE A CLONE OF THE TABLE EMP1 WITHOUT USING CREATE STATEMENT OR GUI OPTION

SELECT * INTO EMP22   FROM EMP
where 1= 2

SELECT * INTO EMP22   FROM EMP
where 1=1


----SELECT - INTO
-----------------
--SELECT * INTO EMP22   FROM EMP

--=> NEW TABLE EMP2 WILL BE AUTOMATICALLY CREATED

--CREATE A NEW TABLE  WITHOUT CREATE STATEMENT HAVING ALL RECORDS OF DEPT 10 FROM EMP1

--SELECT * INTO EMP3 FROM EMP1 WHERE DEPT =10

--=> JUST CREATE STRUCTURE OF TABLE EMP1 AS A NEW TABLE WITHOUT CREATE COMMAND

--SELECT * INTO EMP4 FROM EMP1 WHERE 1=2

==================================================================================================
-----------DML STATEMENTS
==================================================================================================

UPDATE COMMAND
=> USED TO MODIFY THE TABLE DATA
=> USING UPDATE COMMAND WE CAN UPDATE ALL THE ROWS OR SPECIFIC ROWS
=> USING UPDATE COMMAND WE CAN UPDATE SINGLE COLUMN OR MULTIPLE COLUMNS


=> UPDATE ALL EMPLOYEES COMMISION WITH 500....

SELECT * FROM EMP 

UPDATE EMP SET COMM = 500 WHERE COMM IS NULL 
  
SELECT CAST(20 AS DECIMAL(9,2))/CAST(100 AS DECIMAL(9,2))

=> UPDATE THE EMPLOYEE COMMISION TO 1000 WHOSE EMPNO IS 7369


=> UPDATE THE EMPLOYEE COMMISION TO 2000 WHOSE COMMISION IS NULL FROM EMP2


=> INCREMENT SALARY BY 20% AND COMMISION BY 10% FOR THOSE WORKING AS CLERK AND JOINED IN 2021. IN EMP2


UPDATE EMP
SET 
SELECT 
SAL , SAL + SAL * 0.20,
COMM , COMM+ COMM * 0.10
FROM EMP E
WHERE JOB ='CLERK'
AND HIREDATE LIKE '%2021%'


UPDATE EMP
SET 
SAL = SAL + SAL * 0.20,
COMM = COMM+ COMM * 0.10
FROM EMP E
WHERE JOB ='CLERK'
AND HIREDATE LIKE '%2021%'

SELECT 
SAL --= SAL + SAL * 0.20,
COMM --= COMM+ COMM * 0.10
FROM EMP E
WHERE JOB ='CLERK'
AND HIREDATE LIKE '%2021%'




UPDATE 
SAL = SAL + SAL * 0.20,
COMM = COMM+ COMM * 0.10
FROM EMP 
WHERE JOB ='CLERK'
AND HIREDATE LIKE '%2021%'


SELECT 
SAL , SAL + SAL * 0.20,
COMM ,COMM+ COMM * 0.10
FROM EMP 
WHERE JOB ='CLERK'
AND HIREDATE LIKE '%2021%'


====================================================================================================================

INCREMENT THE SALARIES OF EMPLOYEES AS PER FOLLOWING RULES IN EMP

DEPTNO                      INCREMENT IN SALARY
10                                     10%
20                                    15%
30                                   20%
OTHERS                         5%

ONE WAY  IS TO UPDATE ONE BY ONE EACH DEPARTMENT SALARY LIKE FOLLOWING

UPDATE EMP
SET SAL = 1.1*SAL,SAL = 1.5 *SAL,SAL = 2.0*SAL 
WHERE DEPT IN (10,20,30)

UPDATE EMP
SET SAL = 1.5*SAL WHERE DEPT = 20

UPDATE EMP
SET SAL = 2.0*SAL WHERE DEPT = 30

CASE STATEMENTS 


SELECT SAL,CASE DEPT WHEN 10 THEN SAL+ ROUND(ROUND(SAL*CAST(0.10 AS DECIMAL(5,2)),2),2)
				     WHEN 20 THEN SAL+ ROUND(ROUND(SAL*CAST(0.15 AS DECIMAL(5,2)),2),2)
				     WHEN 30 THEN SAL+ ROUND(ROUND(SAL*CAST(0.20 AS DECIMAL(5,2)),2),2)
				ELSE SAL + SAL * 0.05 END
FROM EMP 


UPDATE EMP 
SET SAL =
CASE DEPT WHEN 10 THEN SAL+ ROUND(ROUND(SAL*CAST(0.10 AS DECIMAL(5,2)),2),2)
WHEN 20 THEN SAL+ ROUND(ROUND(SAL*CAST(0.15 AS DECIMAL(5,2)),2),2)
WHEN 30 THEN SAL+ ROUND(ROUND(SAL*CAST(0.20 AS DECIMAL(5,2)),2),2)
ELSE SAL + SAL * 0.05 END
FROM EMP 

UPDATE EMP
SET SAL = CASE DEPT
		WHEN 10
			THEN SAL + ROUND(ROUND(SAL * CAST(0.10 AS DECIMAL(5, 2)), 2), 2)
		WHEN 20
			THEN SAL + ROUND(ROUND(SAL * CAST(0.15 AS DECIMAL(5, 2)), 2), 2)
		WHEN 30
			THEN SAL + ROUND(ROUND(SAL * CAST(0.20 AS DECIMAL(5, 2)), 2), 2)
		ELSE SAL + SAL * 0.05
		END
FROM EMP



BETTER WAY IS TO USE CASE STATEMENT

CASE STATEMENT ---
---------------------------
=> USED TO IMPLEMENT IF THEN ELSE
==> SIMILAR TO SWITCH 
=>  USING A CASE STATEMENT WE CAN RETURN VALUES BASED ON GIVEN CONDITION
=> ELSE IS OPTIONAL
=> CASE STATEMENT IS OF TWO TYPES

1. SIMPLE CASE
2. SEARCHED CASE

1 . SIMPLE CASE:  HERE CONDITION IS BASED ON EQUALITY CHECK

SYNTAX=>

CASE EXPR/COLNAME
WHEN VALUE1 THEN RETURN EXPR1
WHEN VALUE2 THEN RETURN EXPR2
WHEN VALUE3 THEN RETURN EXPR3

[ELSE RETURN EXPR]
END

======================================================
DEPTNO                      INCREMENT IN SALARY
10                                     10%
20                                    15%
30                                   20%
OTHERS                         5%


UPDATE EMP2
SET SAL = CASE DEPTNO
	WHEN 10 THEN 1.1*SAL
	WHEN 20 THEN 1.15*SAL
	WHEN 30 THEN 1.2*SAL
	ELSE 1.05*SAL
	END



=================================================================================

1. SIMPLE CASE 

SELECT SAL,CASE DEPT WHEN 10 THEN SAL+ ROUND(ROUND(SAL*CAST(0.10 AS DECIMAL(5,2)),2),2)
				     WHEN 20 THEN SAL+ ROUND(ROUND(SAL*CAST(0.15 AS DECIMAL(5,2)),2),2)
				     WHEN 30 THEN SAL+ ROUND(ROUND(SAL*CAST(0.20 AS DECIMAL(5,2)),2),2)
				ELSE SAL + SAL * 0.05 END
FROM EMP 

2. SEARCHED CASE

SELECT 
SAL, ENAME,	DEPT,		 
CASE WHEN SAL > 3000 AND DEPT = 10 THEN 'HIG SAL'
			WHEN SAL > 2000 AND SAL <3000 THEN 'MEDIUM SAL'
			WHEN SAL < 2000 THEN 'LOW SAL' 
			ELSE  'VERY LOW' ------------OPTIONAL 
			END AS RATING
FROM EMP 
ORDER BY RATING



SELECT 
SAL, ENAME,	DEPT,		 
CASE WHEN SAL > 3000 AND DEPT = 10 THEN 'HIG SAL'
			WHEN SAL > 2000 AND SAL <3000 THEN 'MEDIUM SAL'
			WHEN SAL < 2000 THEN 'LOW SAL' 
			ELSE  'VERY LOW' ------------OPTIONAL 
			END AS RATING
FROM EMP 
ORDER BY CASE WHEN SAL > 3000 AND DEPT = 10 THEN 'HIG SAL'
			WHEN SAL > 2000 AND SAL <3000 THEN 'MEDIUM SAL'
			WHEN SAL < 2000 THEN 'LOW SAL' 
			ELSE  'VERY LOW' ------------OPTIONAL 
			END


=> USE SEARCHED CASE WHEN CONDITION IS OTHER THAN EQUALITY CHECK
     CASE
    WHEN BOOLEAN CONDITION THEN RETURN EXPR1
    WHEN BOOLEAN CONDITION THEN RETURN EXPR2
    [ELSE RETURN EXPR]
    END
=================================================================================
DISPLAY ENAME , SAL AND SALRANGE FROM EMP2--ACCORDING TO FOLLOWING CONDITION

IF SAL > 3000 IN SAL RANGE DISPLAY HISAL
    SAL< 3000 IN SAL RANGE DISPLAY LOWSAL
   SAL = 300 IN SAL RANGE DISPLAY   AVGSAL

SELECT  FROM EMP 


==================>
SELECT ENAME, SAL, CASE
                                       WHEN SAL > 3000 THEN 'HISAL'
                                       WHEN SAL < 3000 THEN 'LOWSAL'
                                        ELSE 'AVGSAL'
                                        END AS SALRANGE  
                                        FROM EMP2
==================================================================================

=> INCREMENT THE SAL AS PER FOLLOWING CONDITION

IF SAL > 3000, INCREMENT BY 10%
   SAL >2000 BUT LESS OR EQUAL TO 3000 THEN 20%      2000< SAL
   ELSE BY 30%
========================>
UPDATE EMPUPDATED 
                               SET SAL = CASE
                                                WHEN SAL > 3000 THEN 1.1*SAL
		             WHEN SAL > 2000  THEN 1.2*SAL
		             ELSE 1.3*SAL
		             END
=====================================================================================
UPDATE EVERYONES COMMISION EQUAL TO MINIMUM SALARY  IN THE ORGANIZATION

UPDATE EMPUPDATED
SET COMM = (SELECT MIN(SAL) FROM EMPUPDATED)
=====================================================================================

SORT THE RESULT IN EMP1 TABLE SUCH THAT WHEN IT IS DEPT NO 10 THEN SORT BY SAL ASC AND IF DEPTNO 20 THEN SORT BY SAL DESC
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
IN OUR STUDENTS NEW ..WE HAVE STUDENTS LIST ALONG WITH THEIR MARKS..
IN SUBJECT S1 IF STUDENT SECURED ATLEAST 35 MARKS MARKS HIM 'PASS' ELSE 'FAIL'

=>WHEN ALL THE CONDITIONS ARE FALSE OR NOT MET AND ELSE CLAUSE IS NOT MENTIONED THEN CASE WILL RETURN NULL

SELECT SNO, SNAME, CASE WHEN S1>=35 THEN 'PASS'  END AS S1 FROM STUDENTSNEW

=>IN ABOVE COMMAND WHERE EVER S1<35, CASE WILL RETURN NULL
=============================================================================================================================