==================================================================================================================
LIKE OPERATOR----------------31072024

=> USED FOR PATTERN COMPARISON
 SELECT * FROM TABLENAME WHERE COLNAME LIKE PATTERN

% => 0 OR MANY CHARACTERS
_ => FOR EXACTLY ONE CHARACTER

==============================================================================
=> DISPLAY THE DETAILS OF THE EMPLOYEES WHOSE NAME STARTS WITH 'S'?

SELECT * FROM emp WHERE ename LIKE 'S%';

--SACHIN
--SMITH
--SCOTT

==============================================================================
=> DISPLAY THE DETAILS OF THE EMPLOYEES WHOSE NAME ENDS WITH 'S'?
SELECT * FROM emp WHERE ename LIKE '%S';
--JONES
--ADAMS
--JAMES

==============================================================================
=> DISPLAY THE DETAILS OF THE EMPLOYEES WHO NAME CONTAINS 'S'.

SELECT * FROM emp WHERE ename LIKE '%S%';
--SACHIN
--ramesh
--SMITH
--JONES
--SCOTT
--ADAMS
--JAMES


==============================================================================
=> DISPLAY THE DETAILS OF THE EMPLOYEES WHERE 3RD CHARACTERS IN THEIR NAME SHOULD BE 'a'

--SELECT * FROM emp WHERE ename LIKE '%____%'

--BLE_JAS

update emp set ename ='BLE_JAS' WHERE ename ='BLAKE'

--BLAKE
--CLARK
--ADAMS


==============================================================================
=> DISPLAY THE DETAILS OF THE EMPLOYEES WHERE 'a' IS 3rd CHARACTER FROM THE LAST IN THEIR NAME

SELECT * FROM emp WHERE ename LIKE '%a__'

--WARD
--CLARK
--ADAMS

==================================================================================
DISPLAY THE DETAILS OF THE EMPLOYEES WHO JOINED IN 2021

SELECT * FROM emp WHERE HIREDATE >= '2021-01-01' AND HIREDATE <= '2021-12-31';

SELECT * FROM emp WHERE HIREDATE  BETWEEN '2021-01-01'  AND '2021-12-31';

SELECT * FROM emp WHERE HIREDATE LIKE  '%2021%'

===================================================================================
DISPLAY EMPOLYEE DETAILS WHERE NAME STARTS FROM 'a', 'j','s'
SELECT  * FROM emp WHERE ename LIKE 'a%'
                                           OR    ename LIKE 'j%'
                                          OR    ename LIKE 's%'
SELECT * FROM emp

SELECT * FROM emp WHERE ename LIKE '[al]%'


SELECT * FROM emp WHERE ename LIKE 'all%'

=====================================================================================
DISPLAY THE EMPOYEE DETAILS WHERE NAME STARTS WITH 'a' - 'h'

SELECT * FROM emp  WHERE ename LIKE '[a-h]%'

=====================================================================================
select * from emp

select * from emp where comm <> NULL

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

IS OPERATOR
It is not possible to test for NULL values with comparison operators, such as =, <, or <>.

===> IS OPERATOR IS USED FOR NULL COMPARISON
 
WHERE COLNAME = NULL ===> NOT POSSIBLE
WHERE COLNAME <> NULL ===> NOT POSSIBLE

FOR NULL COMPARISON WE USE  
=>   IS NULL 

=>   IS NOT NULL

SELECT * FROM emp WHERE ename =''



SELECT * FROM emp where ename ='smith'

update emp set ename ='' where ename is NULL

--=============================================================================================

--DISPLAY THE DETAILS OF EMPLOYEES WHOSE COMMISSION IS NOT YET DECIDED

SELECT * FROM emp WHERE COMM IS NULL

--FROM EMP1 TABLE SHOW ME THE RECORDS WHOSE MANAGER IS NOT YET DECIDED

SELECT * FROM emp WHERE MGR IS NULL

--FROM EMP1 TABLE DISPLAY THE RECORDS OF EMPLOYEES WHO TAKE COMMISION

SELECT * FROM emp WHERE COMM IS NOT NULL

--==========================================================================================
--==========================================================================================

select * from emp

SELECT COLNAMES 
FROM 
JOINS 
WHERE 
GROUP BY 
HAVING 

ORDER BY 

EXECUTION WILL TAKE PLACE

FROM 
JOINS 
WHERE ---------------SELECT WILLL BE USED AFFTER WHERE CLAUSE
GROUP BY 
HAVING 
SELECT  ALIAS 
ORDER BY --------------AFTER SELECT-------------ACCESS



ORDER BY 

=>IF WE FETCH SOME RECORDS FROM THE TABLE WITHOUT ANY SORTING THEN THERE ORDER IS NOT GUARRANTEED

=>BUT IF WANT THE FETCHED RECORDS IN A PARTICULAR ORDER WE NEED SORTING..

=>FOR SORTING WE USE ORDER BY CLAUSE

=> ORDER BY CLAUSE IS USED TO SORT THE DATA BASED ON ONE OR MORE COLUMS OR BY AN EXPRESSION

COMPLETE SELECT STATEMENT MAY HAVE ==> SELECT  FROM WHERE GROUP BY HAVING ORDER BY

BUT THE ACTUAL EXECUTION WILL BE =>   FROM WHERE  GROUP BY HAVING  SELECT ORDER BY


SELECT COLUMNS FROM TABLE NAME ORDER BY COL1 [ASC/DESC], [COL2] [ASC/DESC].........

DEFAULT ORDER IS ASC

===> DISPLAY THE EMPLOYEE LIST NAME WISE IN ASCENDING ORDER from EMP1 TABLE

SELECT * FROM emp1 ORDER BY ENAME

SELECT * FROM emp1 ORDER BY ename ASC

SELECT * FROM emp1 ORDER BY ename DESC

===> DISPLAY THE EMPLOYEE LIST SALARY  WISE IN DESCENDING ORDER from EMP1 TABLE AND ONLY SHOW THEIR NAME AND SALARY

SELECT ename, sal FROM emp ORDER BY sal DESC

===> DISPLAY THE EMPLOYEE LIST AS PER THEIR  SENIORITY LEVEL from EMP1  IN DESCENDING ORDER

SELECT * FROM emp ORDER BY HIREDATE 

=-->  DISPLAY THE EMPLOYEE LIST DEPT WISE ASC AND WITH IN THE DEPARTMENT SALARY WISE DESC

SELECT * FROM emp ORDER BY DEPT ASC,SAL DESC


=> DISPLAY THE DETAILS OF THE STUDENTS IN ORDER OF THEIR TOTAL MARKS IN DESCENDING ORDER

SELECT * FROM STUDENTS1

SELECT MATH + PHY + CHEM AS TOTAL,* FROM STUDENTS1
ORDER BY MATH + PHY + CHEM DESC

SELECT MATH + PHY + CHEM AS TOTAL,* FROM STUDENTS1
--WHERE TOTAL > 100

ORDER BY TOTAL DESC
 






SELECT *, MATH + PHY + CHEM AS TOTAL FROM STUDENTS1 ORDER BY MATH + PHY + CHEM DESC

SELECT *, MATH + PHY + CHEM AS TOTAL FROM STUDENTS1 ORDER BY TOTAL DESC


============================================================================================================
 DISPLAY THE DETAILS OF THE STUDENTS IN ORDER OF THEIR TOTAL MARKS IN DESCENDING ORDER, INCASE THERE IS TIE THEN SORT BY
MATHS IN DESC, IN CASE OF FURTHER TIE SORT BY PHY DESC

SELECT *, MATH + PHY + CHEM AS TOTAL FROM STUDENTS1 ORDER BY TOTAL DESC,math DESC,phy DESC

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

DISPLAY AVERAGE MARKS OF EACH STUDENT IN FRONT OF HIM/HER AND ALSO SORT THE RESULT AS PER THEIR AVERAGE MARKS DESC

SELECT *, (MATH + PHY + CHEM)/3 AS 'AVG' FROM STUDENTS1 ORDER BY 'AVG' DESC

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

=> DISPLAY EMPLOYEES WORKING AS CLERK, MANAGER  AND ARRANGE THE LIST SAL WISE DESC ORDER

SELECT * FROM emp1 WHERE job IN ('CLERK', 'MANAGER') ORDER BY JOB ,SAL DESC

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


DISTINCT
===========

=> USED TO ELIMINATE THE DUPLICATES FROM THE SELECT STATEMENT OUTPUT

SELECT DISTINCT COL1, COL2, .....FROM TABLENAME [WHERE ] [ORDER BY]

=> DISPLAY THE COUNTRIES WHERE ASIANPAINTS IS OPERATING..ENSURING THAT THERE IS NO DUPLICATE

SELECT * FROM EMP

SELECT DISTINCT * FROM EMP------------------------------------------

SELECT DISTINCT JOB FROM EMP

--ANALYST
--CLERK
--MANAGER
--PRESIDENT
--SALESMAN



SELECT DISTINCT COUNTRY FROM ASIANPAINTS;

=>=> DISPLAY THE CITIES WHERE ASIANPAINTS IS OPERATING..ENSURING THAT THERE IS NO DUPLICATE

SELECT DISTINCT CITY FROM ASIANPAINTS;

=>=> DISPLAY THE  COUNTRY AND CITIES WHERE ASIANPAINTS IS OPERATING..ENSURING THAT THERE IS NO DUPLICATE

SELECT DISTINCT  COUNTRY, CITY FROM ASIANPAINTS;

=> IT WILL DISPLAY THE DISTINCT EXISITING COMBINATION OF COUNTRY AND CITY

=> DISPLAY THE DIFFERENT CITIES IN INDIA WHERE ASIAN PAINTS IS OPERATING

SELECT DISTINCT   CITY FROM ASIANPAINTS WHERE COUNTRY = 'INDIA';

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

=> ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
NOT ALLOWED=>SELECT DISTINCT  COUNTRY, CITY FROM ASIANPAINTS ORDER BY ENAME
=> ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

ALLOWED =>
SELECT DISTINCT  COUNTRY, CITY FROM ASIANPAINTS ORDER BY CITY
======================================================================================================
TOP CLAUSE

SELECT * FROM EMP 

SELECT TOP 50 PERCENT * FROM EMP


=> LIMITING THE NUMBER OF RECORDS

=> DISPLAY TOP 5 ROW FROM EMP1

SELECT TOP 5 * FROM EMP1

=> IT WILL GIVE ME 5 ROWS FROM EMP1 DEPENDING ON ACCESS OF SQL SERVER

=> TO GUARRANTY UR RESULT ..YOU SHOULD SORT THEM AND FOR SORTING WE USE ORDER BY

=> DISPLAY THE TOP 5  SALARIES  AND THE CORRESPONDING EMPLOYEE NAMES FROM EMP1 TABLE

SELECT TOP 5 ENAME, SAL FROM EMP1 ORDER BY SAL DESC

=> => DISPLAY THE TOP 10 PERCENT  SALARIES  AND THE CORRESPONDING EMPLOYEE NAMES FROM EMP1 TABLE


SELECT TOP 10 PERCENT ENAME, SAL FROM EMP1 ORDER BY SAL DESC
===============================================================================

TOP WITH TIES---

RETURNS TWO OR MORE ROWS THAT TIES FOR LAST PLACE IN LIMITED RESULT SET

SELECT TOP 2 ENAME, SAL FROM EMP ORDER BY SAL DESC;

SELECT TOP 2 WITH TIES ENAME, SAL FROM EMP ORDER BY SAL DESC;

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

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 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

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