SQL STATEMENTS

---------------
1.DDL(DATA DEFINATION LANGUAGE)
-------------------------------
CREATE
ALTER
TRUNCATE
DROP

2.DML(DATA MANIPULATION LANGUAGE)

---------------------------------
INSERT
UPDATE
DELETE

3.TCL(TRANSACTION CONTROL LANGUAGE)

-----------------------------------
COMMIT
ROLLBACK
SAVEPOINT

4.DCL(DATA CONTROL LANGUAGE)

----------------------------
GRANT
REVOKE

5.DQL(DATA QUERY LANGUAGE)

---------------------------
SELECT
PROJECTION
SELECTION
JOINS

DDL(DATA DEFINATION LANGUAGE)

-----------------------------
CREATE:
-------
IT IS USED TO CREATE DATABASE AND ITS OBJECTS SUCH AS TABLE,VIEW,
PROCEDURE,TRIGGER AND USER..

TO CREATE DATABASE IN MYSQL

----------------------------
SYNTAX:

CREATE DATABASE DATABASE_NAME;

CREATE DATABASE COMPANY;

TO VIEW ALL THE DATABASES PRESENT IN MYSQL

-------------------------------------------
SYNTAX:

SHOW DATABASES;

TO ACCESS ONE DATABASE IN MYSQL

-------------------------------
SYNTAX:

USE DATABASE_NAME;

USE COMPANY;

TO CLEAR SCREEN IN MYSQL SERVER

--------------------------------
SYNTAX:

SYSTEM CLS

TO CLOSE MYSQL SERVER

----------------------
SYNTAX:

EXIT

to view ALL THE TABLES PRESENT IN ONE DATABASE

----------------------------------------------
SYNTAX:

SHOW TABLES;

TO CREATE TABLE
---------------
SYNTAX:1

CREATE TABLE TABLE_NAME

(
COLUMN_NAME_1 DATATYPE SIGNED/UNSIGNED NULL/NOT NULL,
COLUMN_NAME_2 DATATYPE SIGNED/UNSIGNED NULL/NOT NULL,
'
'
'
COLUMN_NAME_N DATATYPE SIGNED/UNSIGNED NULL/NOT NULL,
CONSTRAINT CONSTRAINT_NAME PRIMARY KEY(COLUMN_NAME),
CONSTRAINT CONSTRAINT_NAME UNIQUE(COLUMN_NAME),
CONSTRAINT CONSTRAINT_NAME CHECK(CONDITION),
CONSTRAINT CONSTRAINT_NAME FOREIGN KEY(COLUMN_NAME)REFERENCES
PARENT_TABLE_NAME(COLUMN_NAME)
);
CREATE TABLE STUDENT
(
ID INT UNSIGNED NOT NULL,
NAME VARCHAR(10) NOT NULL,
PHONE BIGINT UNSIGNED NOT NULL,
CONSTRAINT P_ID PRIMARY KEY(ID),
CONSTRAINT U_PH UNIQUE(PHONE),
CONSTRAINT C_PH CHECK(LENGTH(PHONE)=10)
);

PRODUCT:

PID INT UNSIGNED NN PRIMARY KEY : P_PID

PNAME VARCHAR(10) NN
PRICE DECIMAL(7,2) NN CHECK(PRICE>0):CH_PRICE
QUANTITY INT UNSIGNED NULL
CATEGORY VARCHAR(10) NN

CREATE TABLE PRODUCT

(
PID INT UNSIGNED NOT NULL,
PNAME VARCHAR(10) NOT NULL,
PRICE DECIMAL(7,2) NOT NULL,
QUANTITY INT UNSIGNED NULL,
CATEGORY VARCHAR(10) NOT NULL,
CONSTRAINT P_PID PRIMARY KEY(PID),
CONSTRAINT CH_PRICE CHECK(PRICE>0)
);
BRANCH:

BID INT NN PRIMARY KEY:P_BID

BNAME VARCHAR(10) NN
LOC VARCHAR(10) NN
PINCODE INT NN UNIQUE,CHECK(LENGTH(PINCODE)=6)
U_PIN C_PIN

CREATE TABLE BRANCH

(
BID INT NOT NULL,
BNAME VARCHAR(10) NOT NULL,
LOC VARCHAR(10) NOT NULL,
PINCODE INT NOT NULL,
CONSTRAINT P_BID PRIMARY KEY(BID),
CONSTRAINT U_PIN UNIQUE(PINCODE),
CONSTRAINT C_PIN CHECK(LENGTH(PINCODE)=6)
);

TO VIEW STRUCTURE OF THE TABLE

------------------------------
SYNTAX:

DESC TABLE_NAME;

DESC PRODUCT;

TO VIEW CONSTRAINT NAME OF THE CONSTRAINTS

-------------------------------------------
STEP:1

USE INFORMATION_SCHEMA;

STEP:2

SELECT *
FROM TABLE_CONSTRAINTS;

SYNTAX:2 TO CREATE TABLE

------------------------
CREATE TABLE TABLE_NAME
(
COLUMN_NAME_1 DATATYPE SIGNED/UNSIGNED NULL/NOT NULL CONSTRAINT,
COLUMN_NAME_2 DATATYPE SIGNED/UNSIGNED NULL/NOT NULL CONSTRAINT,
'
'
'
'
COLUMN_NAME_N DATATYPE SIGNED/UNSIGNED NULL/NOT NULL CONSTRAINT,
CONSTRAINT FOREIGN KEY(COLUMN_NAME)REFERENCES
PARENT_TABLE_NAME(COLUMN_NAME)
);

FACULTY:

FID INT NN PRIMARY KEY

FNAME VARCHAR(10) NN
DNAME VARCHAR(10) NN
PHONE BIGINT NN UNIQUE,CHECK(LENGTH(PHONE)=10)

CREATE TABLE FACULTY

(
FID INT NOT NULL PRIMARY KEY,
FNAME VARCHAR(10) NOT NULL,
DNAME VARCHAR(10) NOT NULL,
PHONE BIGINT NOT NULL UNIQUE,CHECK(LENGTH(PHONE)=10)
);

CUSTOMER:

CID INT NN PRIMARY KEY AUTO_INCREMENT

CNAME VARCHAR(10) NN
PHONE BIGINT NN UNIQUE,CHECK(LENGTH(PHONE)=10)
ADDRESS VARCHAR(20) NN
GENDER ENUM('MALE','FEMALE') NN
BALANCE DECIMAL(7,2) NULL DEFAULT'420'
ID INT UNSIGNED NULL FOREIGN KEY

CREATE TABLE CUSTOMER

(
CID INT PRIMARY KEY AUTO_INCREMENT,
CNAME VARCHAR(10) NOT NULL,
PHONE BIGINT NOT NULL UNIQUE,CHECK(LENGTH(PHONE)=10),
ADDRESS VARCHAR(20) NOT NULL,
GENDER ENUM('MALE','FEMALE') NOT NULL,
BALANCE DECIMAL(7,2) DEFAULT'420',
ID INT UNSIGNED,
CONSTRAINT FOREIGN KEY(ID)REFERENCES STUDENT(ID)
);

BANK:

ACCOUNT_NUMBER BIGINT NN PRIMARY KEY

ACCOUNT_HOLDER_NAME VARCHAR(20) NN
LOC VARCHAR(10) NN
IFSC_CODE VARCHAR(20) NN UNIQUE,CHECK(LENGTH(IFSC_CODE)=11)
BID INT NULL FOREIGN KEY

CREATE TABLE BANK

(
ACCOUNT_NUMBER BIGINT NOT NULL PRIMARY KEY,
ACCOUNT_HOLDER_NAME VARCHAR(20) NOT NULL,
LOC VARCHAR(10) NOT NULL,
IFSC_CODE VARCHAR(20) NOT NULL UNIQUE,CHECK(LENGTH(IFSC_CODE)=11),
BID INT NULL,
CONSTRAINT FOREIGN KEY(BID)REFERENCES BRANCH(BID)
);

ALTER:
------
IT IS USED TO MODIFY STRUCTURE OF THE TABLE.

1.TO ADD NEW COLUMN_NAME

------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

ADD NEW_COLUMN_NAME NEW_DATATYPE NULL/NOT NULL;

BALANCE DECIMAL(7,3) NULL-----STUDENT

ALTER TABLE STUDENT

ADD BALANCE DECIMAL(7,3) NULL;

2.TO ADD NEW COLUMN AFTER PARTICULAR COLUMN

-------------------------------------------
SYNTAX:
ALTER TABLE TABLE_NAME
ADD NEW_COLUMN_NAME NEW_DATATYPE NULL/NOT NULL AFTER COLUMN_NAME;

ID :EMAIL VARCHAR(10) NULL ----STUDENT

ALTER TABLE STUDENT

ADD EMAIL VARCHAR(10) NULL AFTER ID;

3.TO DROP COLUMN_NAME

----------------------
SYNTAX:

ALTER TABLE TABLE_NAME

DROP COLUMN_NAME;

ALTER TABLE PRODUCT

DROP CATEGORY;

4.TO CHANGE DATATYPE

---------------------
SYNTAX:

ALTER TABLE TABLE_NAME

MODIFY EXISTING_COLUMN_NAME NEW_DATATYPE NULL/NOT NULL;

CHAR(10)

ALTER TABLE BRANCH

MODIFY LOC CHAR(10) NOT NULL;

5.TO CHANGE NULL/NOT NULL

-------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

MODIFY EXISTING_COLUMN_NAME EXISTING_DATATYPE NOT NULL/NULL;

ALTER TABLE BANK

MODIFY IFSC_CODE VARCHAR(20) NULL;

6.TO CHANGE TABLE_NAME

----------------------
SYNTAX:
ALTER TABLE TABLE_NAME
RENAME NEW_TABLE_NAME;

STUDENT:STUDENT_DATA

ALTER TABLE STUDENT

RENAME STUDENT_DATA;

7.TO CHANGE COLUMN_NAME

------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

CHANGE EXISTING_COLUMN_NAME NEW_COLUMN_NAME EXISTING_DATATYPE
NULL/NOT NULL;

ALTER TABLE STUDENT_DATA

CHANGE NAME S_NAME VARCHAR(10) NOT NULL;

ALTER TABLE STUDENT_DATA

CHANGE PHONE CONTACT_NUMBER BIGINT UNSIGNED NOT NULL;

ERROR: WE CANT DROP/RENAME A COLUMN WHICH IS ASSIGNED WITH CHECK

CONSTRAINT.

8.TO ADD CONSTRAINTS:

---------------------
i.TO ADD PRIMARY KEY:
---------------------
SYNTAX:

ALTER TABLE TABLE_NAME

ADD PRIMARY KEY(COLUMN_NAME);

ii.TO ADD UNIQUE CONSTRAINT

---------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

ADD UNIQUE(COLUMN_NAME);

iii.TO ADD CHECK CONSTRAINT

---------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

ADD CHECK(CONDITION);

ALTER TABLE STUDENT_DATA

ADD UNIQUE(EMAIL);

IV.TO ADD FOREIGN KEY

---------------------
SYNTAX:

ALTER TABLE TABLE_NAME

ADD FOREIGN KEY(COLUMN_NAME)REFERENCES
PARENT_TABLE_NAME(COLUMN_NAME);

ALTER TABLE CUSTOMER

ADD FID INT NULL;

ALTER TABLE CUSTOMER

ADD FOREIGN KEY(FID)REFERENCES FACULTY(FID);

V.TO ADD DEFAULT CONSTRAINT

---------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

MODIFY EXISTING_COLUMN_NAME EXISTING_DATATYPE NULL/NOT NULL
DEFAULT'DEFAULT_VALUE';

ALTER TABLE PRODUCT

MODIFY QUANTITY INT UNSIGNED NULL DEFAULT'100';

vI.TO ADD AUTO_INCREMENT

------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

MODIFY EXISTING_cOLUMN_NAME EXISTING_DATATYPE NULL/NOT NULL
AUTO_INCREMENT;

ALTER TABLE PRODUCT

MODIFY PID INT UNSIGNED NOT NULL AUTO_INCREMENT;

TO DROP CONSTRAINTS
-------------------
i.TO DROP PRIMARY KEY
---------------------
SYNTAX:

ALTER TABLE TABLE_NAME

DROP PRIMARY KEY;

ALTER TABLE BANK

DROP PRIMARY KEY;

ALTER TABLE BRANCH

DROP PRIMARY KEY;

ERROR:WE CANT DROP PRIMARY KEY FROM A TABLE IF PRIMARY KEY IS ACTING AS
FOREIGN KEY IN ANOTHER TABLE.

ALTER TABLE PRODUCT

DROP PRIMARY KEY;

ERROR:WE CANT DROP PRIMARY KEY FROM A TABLE IF PRIMARY KEY COLUMN IS
ASSIGNED WITH AUTO_INCREMENT.

ii.TO DROP DEFAULT CONSTRAINT

------------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

ALTER COLUMN COLUMN_NAME
DROP DEFAULT;

ALTER TABLE CUSTOMER

ALTER COLUMN BALANCE
DROP DEFAULT;

iii.TO DROP AUTO_INCREMENT

--------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

MODIFY EXISTING_COLUMN_NAME EXISTING_dATATYPE NULL/NOT NULL;

ALTER TABLE CUSTOMER

MODIFY CID INT NOT NULL;

ALTER TABLE CUSTOMER

DROP PRIMARY KEY;

Iv.TO DROP OTHER CONSTRAINTS(UNIQUE,CHECK AND FOREIGN KEY)

----------------------------------------------------------
SYNTAX:

ALTER TABLE TABLE_NAME

DROP CONSTRAINT CONSTRAINT_NAME;

ALTER TABLE BANK

DROP CONSTRAINT IFSC_CODE;

DML(DATA MANIPULATION LANGUAGE)

-------------------------------
1.INSERT
2.UPDATE
3.DELETE

1.INSERT
--------
IT IS USED TO ADD/INSERT RECORDS INTO EXISTING TABLE.

SYNTAX:1(WHEN WE KNOW COLUMN_NAME AND COLUMN ORDER IN A TABLE)

--------------------------------------------------------------

INSERT INTO TABLE_NAME VALUES(V1,V2,.....VN);

EX:

INSERT INTO PRODUCT VALUES(1,'OLD MONK',300,1);

INSERT INTO PRODUCT VALUES(2,'LIPSTICK',500,1);

INSERT INTO PRODUCT VALUES(3,'SHIRT',250,2),(4,'SUNSCREEN',400,1);

INSERT INTO PRODUCT VALUES(5,'CRICKET BAT',850,2);

ERROR: Data too long for column 'PNAME' at row 1

INSERT INTO PRODUCT VALUES(5,'CRICKETBAT',850,2);

INSERT INTO PRODUCT VALUES('WATCH',5000,6,1);

ERROR:Incorrect integer value: 'WATCH' for column 'PID' at row 1

SYNTAX:2 (WHEN WE KNOW ONLY COLUMN_NAME)

----------------------------------------

INSERT INTO TABLE_NAME(COL1,COL2,......COLN)VALUES(V1,V2,....VN);

EX:

INSERT INTO PRODUCT(PNAME,QUANTITY,PID,PRICE)VALUES('BELT',1,6,100);

INSERT INTO PRODUCT(QUANTITY,PNAME,PRICE,PID)VALUES

(1,'HAND BAG',100,7),(1,'KINDER JOY',40,8);

INSERT INTO PRODUCT(PNAME,PRICE)VALUES('IPHONE',60000);

CREATE TABLE EMPLOYEE

(GENDER ENUM('MALE','FEMALE')
);

INSERT INTO EMPLOYEE(GENDER)VALUES('MALE');

INSERT INTO EMPLOYEE(GENDER)VALUES('OTHER');

INSERT INTO EMPLOYEE(GENDER)VALUES('FEMALE');

SYNTAX:3
--------

INSERT INTO TABLE_NAME(SELECT STATEMENT);

2.UPDATE:
---------
IT IS USED TO MODIFY EXISTING RECORDS IN A TABLE.

SYNTAX:
UPDATE TABLE_NAME
SET COL_NAME_1=V1,COL_NAME_2=V2,.......COL_NAME_N=VN
[WHERE CONDITION];

UPDATE PRODUCT
SET PNAME='BIRYANI';

UPDATE PRODUCT
SET PNAME='CURD RICE'
WHERE PID=1;

KABAB:100
PANNEER:110

UPDATE PRODUCT
SET PNAME='KABAB',PRICE=100
WHERE PID=5;

3.DELETE
---------
IT IS USED TO DELETE EXISTING RECORDS FROM A TABLE.

SYNTAX:

DELETE
FROM TABLE_NAME
[WHERE CONDITION];

DELETE
FROM PRODUCT
WHERE PID=9;

DDL
---
3.TRUNCATE:
-----------
IT IS USED TO DELETE ALL THE RECORDS FROM A TABLE WITHOUT AFFECTING TABLE
STRUCTURE.

SYNTAX:

TRUNCATE TABLE TABLE_NAME;

TRUNCATE TABLE PRODUCT;

4.DROP
------
IT IS USED DROP DATABASE AND ITS OBJECTS SUCH AS
TABLE,VIEW,PROCEDURE,TRIGGER AND USER FROM MYSQL.

i.TO DROP TABLE FROM DATABASE

-----------------------------
SYNTAX:

DROP TABLE TABLE_NAME;

DROP TABLE PRODUCT;

ii.TO DROP DATABASE FROM MYSQL

------------------------------
SYNTAX:

DROP DATABASE DATABASE_NAME;

DROP DATABASE COMPANY;

CREATE DATABASE ZOMATO;

USE ZOMATO;

1.wqtd FIRST NAME ,SALARY AND STATUS OF EMPLOYEES FROM EMPS TABLE???

SELECT FNAME,SAL,STATUS
FROM EMPS;

2.WQTD FIRST NAME,LAST NAME,DATE OF BIRTH AND DATE OF JOINING FROM EMPS
TABLE???

SELECT FNAME,LNAME,DOB,DOJ
FROM EMPS;

3.WQTD DETAILS OF EMPS FROM EMPS TABLE???

SELECT *
FROM EMPS;

4.WQTD FNAME,JOB AND ANNUAL SALARY FROM EMPS TABLE???

SELECT FNAME,JOB,
FROM EMPS;

5.WQTD FNAME,COMMISSION,SALARY WITH 30000 BONUS???

SELECT FNAME,COMM,SAL+30000
FROM EMPS;

6.WQTD FNAME,JOB,CID AND SALARY WITH 10% HIKE????

SELECT FNAME,JOB,CID,SAL+(SAL * 10/100)

FROM EMPS;

HIKE:
-----
COLUMN_NAME+(COLUMN_NAME * %HIKE/100)

7.WQTD FNAME,LNAME,LID,MGR,COMM WITH 5 % HIKE AND SAL WITH 17% HIKE???

SELECT FNAME,LNAME,LID,MGR,COMM+(COMM * 5/100),SAL+(SAL* 17/100)

FROM EMPS;

8.wqtd DETAILS OF THE EMPS ALONG WITH ANNUAL SALARY???

SELECT *,SAL*12
FROM EMPS;

9.WQTD FNAME,JOB AND SAL WITH 8% DEDUCTION???

DEDUCTION:

COLUMN_NAME-(COLUMN_NAME * %DEDUCTION/100)

SELECT FNAME,JOB,SAL-(SAL*8/100)
FROM EMPS;

10.WQTD FNAME,SAL,ANNUAL COMM FROM EMPS??

SELECT FNAME,SAL,COMM*12
FROM EMPS;

11.WQTD FNAME,LNAME,COMM WITH 15% DEDUCTION AND ANNUAL SALARY WITH

11% HIKE??

SELECT FNAME,LNAME,COMM-(COMM * 15/100),(SAL*12)+((SAL*12)*11/100)

FROM EMPS;

12.WQTD FNAME,LNAME,JOB,ANNUAL COMM WITH 50% HIKE AND ANNUAL SALARY

WITH 25% DEDUCTION.

SELECT FNAME,LNAME,JOB,(COMM*12)+((COMM*12)*50/100),(SAL*12)-((SAL*12)*25/100)
FROM EMPS;

ALIASING:
--------
IT IS USED TO PROVIDE ALTERNATIVE NAME FOR A COLUMN IN RESULTANT TABLE.

RULES:
------
1.WITH OR WITHOUT USING AS KEYWORD WE CAN WRITE ALIAS NAME.
2.WE CAN USE MULTIPLE WORDS AS A ALIAS NAME BY USING QUOTES OR BY
CONNECTING IT WITH UNDER_SCORE

SELECT SAL AS SALARY

FROM EMPS;

SELECT SAL SALARY

FROM EMPS;

SELECT SAL*12 ANNUAL_SALARY

FROM EMPS;

SELECT SAL*12 AS 'ANNUAL SALARY'

FROM EMPS;

12.WQTD FNAME,LNAME,JOB,ANNUAL COMM WITH 50% HIKE AND ANNUAL SALARY

WITH 25% DEDUCTION.

SELECT FNAME,LNAME,JOB,(COMM*12)+((COMM*12)*50/100) AS 'ANNUAL COMM WITH

50% HIKE',(SAL*12)-((SAL*12)*25/100) 'ANNUAL SALARY WITH 25% DEDUCTION'
FROM EMPS;
13.WQTD FNAME AS FIRST NAME,LNAME AS LAST NAME AND ANNUAL SALARY???

SELECT FNAME 'FIRST NAME',LNAME 'LAST NAME',SAL*12 'ANNUAL SALARY'

FROM EMPS;

14.WQTD UNIQUE JOB ROLES PRESENT IN EMPS TABLE??

SELECT DISTINCT JOB

FROM EMPS;

DISTINCT:
---------
IT IS USED TO AVOID DUPLICATE VALUES FROM RESULTANT TABLE.

RULES:
-----
1.EITHER * OR DISTINCT MUST BE THE FIRST ARGUMENT IN SELECT CLAUSE
(IF WE ARE USING SEPERATELY)

2.WE CAN USE MULTIPLE COLUMNS ALONG WITH DISTINCT ,IT WILL AVOID
COMBINATION OF DUPLICATE VALUES.

15.WQTD UNIQUE COMBINATION OF STATUS AND MGR FROM EMPS TABLE???

SELECT DISTINCT STATUS,MGR

FROM EMPS;
ORDER OF EXECUTION:
-------------------
1.FROM
2.WHERE
3.SELECT

16.WQTD DETAILS OF EMPS WHOSE FNAME IS DIVYA????

SELECT *
FROM EMPS
WHERE fname='DIVYA';

17.WQTD FNAME AS FIRST NAME,LNAME AS LAST NAME,JOB AND MGR IF EMPLOYEE

IS WORKING AS WAITER???

SELECT FNAME 'FIRST NAME',LNAME 'LAST NAME',JOB,MGR

FROM EMPS
WHERE JOB='WAITER';

18.WQTD DETAILS OF EMPS IF EMPS ARE GETTING SALARY MORE THAN 45000???

SELECT *
FROM EMPS
WHERE SAl>45000;

19.wqtd ALL THE DELIVERY EMPLOYEES DETAILS FROM EMPS TABLE??

SELECT *
FROM EMPS
WHERE JOB='DELIVERY';

20.WQTD DETAILS OF EMPS WHOSE STATUS IS BUSY????

SELECT *
FROM EMPS
WHERE STATUS='BUSY';

21.WQTD FNAME AS FIRST NAME,LNAME,DOB AND DOJ IF EMPLOYEE BORN AFTER

THE YEAR 1993?

SELECT FNAME 'FIRST NAME',LNAME,DOB,DOJ

FROM EMPS
WHERE DOB>'1993-12-31';
OR
WHERE DOB>='1994-01-01';

'YYYY-MM-DD'

22.WQTD DETAILS OF EMPS WHO HIRED AFTER THE MONTH MARCH OF 2020??

SELECT *
FROM EMPS
WHERE DOJ>'2020-03-31';

23.WQTD DETAILS OF EMPS WHO JOINED BEFORE THE YEAR 2019??

SELECT *
FROM EMPS
WHERE DOJ<'2019-01-01';
OR
WHERE DOJ<='2018-12-31';

24.WQTD DETAILS OF EMPS ALONG WITH ANNUAL SALARY IF EMPLOYEE IS GETTING

ANNUAL SALARY MORE THAN 500000???

SELECT *,SAL*12 'ANNUAL SALARY'

FROM EMPS
WHERE (SAL*12)>500000;

OPERATORS:
----------
1.ARITHMATIC OPERATORS(+,-,*,/,%)
2.RELATIONAL OPERATORS(>,<,>=,<=,=,!=)
3.LOGICAL OPERATORS (AND,OR,NOT)
4.SPECIAL OPERATORS (IN,NOT IN,IS,BETWEEN,NOT BETWEEN,LIKE,
NOT LIKE,ALL,ANY)

25.WQTD DETAILS OF EMPS IF EMPS ARE WORKING AS CHEF OR CASHIER???

SELECT *
FROM EMPS
WHERE JOB='CHEF' OR JOB='CASHIER';

26.WQTD FNAME,LNAME,JOB,DOB AND DOJ IF EMPS BORN DURING THE YEAR

1995????

SELECT FNAME,LNAME,JOB,DOB,DOJ
FROM EMPS
WHERE DOB>='1995-01-01' AND DOB<='1995-12-31';

27.WQTD DETAILS OF THE EMPS IF EMPS ARE WORKING AS SECURITY AND GETTING
SALARY LESS THAN 80000???

SELECT *
FROM EMPS
WHERE JOB='SECURITY' AND SAL<80000;

28.WQTD DETAILS OF THE EMPS WHO ARE WORKING AS SECURITY OR MANAGER AND
THEIR STATUS IS AVAILABLE??

SELECT *
FROM EMPS
WHERE (JOB='SECURITY' OR JOB='MANAGER') AND (STATUS='AVAILABLE');

29.WQTD DETAILS OF EMPS IF EMPS ARE WORKING AS SECURITY,CHEF,DELIVERY OR

WAITER AND GETTING SALARY MORE THAN 45000 AND HIRED IN THE YEAR 2018???

SELECT *
FROM EMPS
WHERE (JOB='SECURITY' OR JOB='CHEF' OR JOB='WAITER' OR JOB='DELIVERY') AND
SAL>45000 And (DOJ>='2018-01-01' AND DOJ<='2018-12-31');

IN:
---
IT IS A MULTI VALUE OPERATOR WHICH TAKES MULTIPLE VALUES AT THE RHS AND
SINGLE VALUE AT THE LHS.

SYNTAX:

LHS RHS
COLUMN_NAME/EXPRESSION IN(V1,V2,.....VN);

>IT WORKS ON OR CONDITION.

29.WQTD DETAILS OF EMPS IF EMPS ARE WORKING AS SECURITY,CHEF,DELIVERY OR

WAITER AND GETTING SALARY MORE THAN 45000 AND HIRED IN THE YEAR 2018???

SELECT *
FROM EMPS
WHERE JOB IN('SECURITY','CHEF','DELIVERY','WAITER') AND
SAL>45000 And (DOJ>='2018-01-01' AND DOJ<='2018-12-31');

30.WQTD DETAILS OF EMPS IF EMPS STATUS IS AVAILABLE OR BUSY AND WORKING

AS CLEANER OR CASHIER???

SELECT *
FROM EMPS
WHERE STATUS IN('AVAILABLE','BUSY') AND JOB IN('CLEANER','CASHIER');

31.WQTD DETAILS OF THE ITEMS FROM MENU_ITEMS TABLE IF ITEM NAME IS EITHER
MASALA DOSA OR FISH CURRY AND ITEM PRICE IS LESS THAN 500???

SELECT *
FROM MENU_ITEMS
WHERE NAME IN('MASALA DOSA','FISH CURRY') AND PRICE<500;
32.WQTD DETAILS OF EMPS WHO ARE NOT WORKING AS CHEF,CLEANER AND
CASHIER(WITHOUT USING SPECIAL OPERATOR)

SELECT *
FROM EMPS
WHERE JOB!='CHEF' AND JOB!='CLEANER' AND JOB!='CASHIER';

NOT IN:
-------
IT IS A MULTIVALUE OPERATOR WHICH TAKES MULTIPLE VALUES AT THE RHS AND
SINGLE VALUE AT THE LHS.

SYNTAX:
-------

COLUMN_NAME/EXPRESSION NOT IN(V1,V2,.....VN);

>IT WORKS ON AND CONDITION.

32.WQTD DETAILS OF EMPS WHO ARE NOT WORKING AS CHEF,CLEANER AND

CASHIER(WITHOUT USING SPECIAL OPERATOR)

SELECT *
FROM EMPS
WHERE JOB!='CHEF' AND JOB!='CLEANER' AND JOB!='CASHIER';

SELECT *
FROM EMPS
WHERE JOb not in('CHEF','CLEANER','CASHIER');

33.WQTD DETAILS OF EMPS WHO WERE BORN IN THE YEAR 1992 AND NOT WORKING
AS MANAGER,CHEF AND HIRED IN THE YEAR 2018 BUT NOT AS FEMALE???

SELECT *
FROM EMPS
WHERE DOB>='1992-01-01' AND DOB<='1992-12-31' AND JOB NOT IN('MANAGER','CHEF')
AND
DOJ>='2018-01-01' AND DOJ<='2018-12-31' AND GENDER!='F';

34.WQTD FNAME AND SAL IF EMP SALARY IS MORE THAN OR EQUAL TO 35000 AND
LESS THAN OR EQUAL TO 50000?

SELECT FNAME,SAL
FROM EMPS
WHERE SAL>=35000 AND SAL<=50000;

BETWEEN:
--------
IT IS USED TO INCLUDE SOME RANGE OF VALUES.

SYNTAX:
>= <=
COLUMN_NAME/EXPRESSION BETWEEN LOWER_RANGE_VALUE AND
HIGHER_RANGE_VALUE;

SELECT FNAME,SAL
FROM EMPS
WHERE SAL BETWEEN 35000 AND 50000;

35.WQTD DETAILS OF EMPS WHO BORN IN THE YEAR 1995????

SELECT *
FROM EMPS
WHERE DOB BETWEEN '1995-01-01' AND '1995-12-31';

36.WQTD DETAILS OF EMPS WHO ARE WORKING AS WAITER,CLEANER OR CASHIER

AND GETTING SALARY MORE THAN 35000 AND LESS THAN 50000????

SELECT *
FROM EMPS
WHERE JOB IN('WAITER','CLEANER','CASHIER') AND SAL BETWEEN 35000+0.01 AND
50000-0.01;

37.WQTD EMP FNAME,SAL IF EMPS ARE NOT GETTING SALARY IN THE RANGE OF
35000 TO 45000???

NOT BETWEEN:
------------
IT IS USED AVOID SOME RANGE OF VALUES.

SYNTAX:
< >
COLUMN_NAME/EXPRESSION NOT BETWEEN LOWER_RANGE_VALUE AND
HIGHER_RANGE_VALUE;

SELECT FNAME,SAL
FROM EMPS
WHERE SAL NOT BETWEEN 35000 AND 45000;

38.WQTD DETAILS OF EMPS WHO ARE NOT JOINED IN THE YEAR 2020 AND 2021????

SELECT *
FROM EMPS
WHERE DOJ NOT BETWEEN '2020-01-01' AND '2021-12-31';

39.WQTD DETAILS OF EMPS WHO ARE GETTING SOME COMMISSION????

IS:
---
ITS IS USED TO CHECK WHETHER THE COLUMN IS NULL OR NOT NULL

SYNTAX:

COLUMN_NAME/EXPRESSION IS NULL/NOT NULL;

SELECT *
FROM EMPS
WHERE COMM IS NOT NULL;

40.WQTD DETAILS OF EMPS WHO ARE ACTING AS CUSTOMERS FOR THEIR

COMPANY??

SELECT *
FROM EMPS
WHERE CID IS NOT NULL;

41.WQTD DETAILS OF EMPS IF THEY HAVE REPORTING MANAGERS??

SELECT *
FROM EMPS
WHERE MGR IS NOT NULL;

42.WQTD DETAILS OF EMPS WHO ARE WORKING AS CASHIER,MANAGER OR CLEANER

BUT THEIR STATUS IS NOT AVAILABLE AND OFFLINE AND GETTING SALARY IN THE
RANGE OF 20000 TO 50000 BUT EMPS WERE NOT BORN IN THE YEAR 1995 AND THEY
HAVE REPORTING MANAGER??
SELECT *
FROM EMPS
WHERE JOB IN('CASHIER','MANAGER','CLEANER') AND STATUS NOT
IN('AVAILABLE','OFFLINE') AND
SAL BETWEEN 20000 AND 50000 AND DOB NOT BETWEEN '1995-01-01' AND '1995-12-31'
AND
MGR IS NOT NULL;

43.WQTD DETAILS OF EMPS IF EMPS ARE GETTING SALARY IN THE RANGE OF 40000
TO 100000 AND HIRED IN THE YEAR 2019 AND IN THE DATE 12???

SELECT *
FROM EMPS
WHERE SAL BETWEEN 40000 AND 100000 AND DOJ
IN('2019-01-12','2019-02-12','2019-03-12','2019-04-12','2019-05-12','2019-06-12','2019-07-12','2
019-08-12','2019-09-12','2019-10-12','2019-11-12','2019-12-12');

44.WQTD FNAME OF EMPS WHOSE FNAME IS STARTING WITH K???

SELECT FNAME
FROM EMPS
WHERE FNAME LIKE 'K%';

45.WQTD DETAILS OF EMPS IF EMPS LNAME IS ENDING WITH Y???

SELECT *
FROM EMPS
WHERE LNAME LIKE '%Y';

46.WQTD FNAME,LNAME,SAL AND JOB IF EMPS FNAME CONTAINS ATLEAST 2 A AND

GETTING SALARY MORE THAN 40000???

SELECT FNAME,LNAME,SAL,JOB
FROM EMPS
WHERE FNAME LIKE '%A%A%' AND SAL>40000;

47.WQTD FNAME,LNAME AND JOB IF JOB CONTAINS STRING WAIT IN IT???

SELECT FNAME,LNAME,JOB
FROM EMPS
WHERE JOB LIKE '%WAIT%';
48.WQTD DETAILS OF EMPS WHOSE FNAME STARTING WITH S OR A???

SELECT *
FROM EMPS
WHERE FNAME LIKE 'S%' OR FNAME LIKE 'A%';

49.WQTD DETAILS OF EMPS IF EMP HIRED IN THE YEAR 2019????

SELECT *
FROM EMPS
WHERE DOJ LIKE '2019%';

2019-__-__

YYYY-MM-DD

50.WQTD FNAME,LNAME,DOB IF EMPS WERE BORN IN THE MONTH OF JAN,FEB OR

MARCH???

SELECT FNAME,LNAME,DOB
FROM EMPS
WHERE DOB LIKE '%-01-%' OR DOB LIKE '%-02___' or DOB LIKE '____-03-__';

%01% %02% %03%

A:'1999-01-12' T
B:'1998-10-01' F

51.WQTD DETAILS OF EMPS IF EMPS JOINED IN THE DATE OF 12 OR 01??

SELECT *
FROM EMPS
WHERE DOJ LIKE '%12' OR DOJ LIKE '%01';

52.WQTD DETAILS OF EMPS WHOSE FNAME IS NOT STARTING WITH A????

NOT LIKE:
---------
IT IS USED FOR PATTERN MATCHING.

>IT WILL AVOID THE DETAILS BASED ON THE PATTERN.

SYNTAX:

COLUMN_NAME/EXPRESSION NOT LIKE 'PATTERN_TO_MATCH';

SELECT *
FROM EMPS
WHERE FNAME NOT LIKE 'A%';

53.WQTD DETAILS OF EMPS IF EMPS WERE NOT JOINED IN THE YEAR 2020???

SELECT *
FROM EMPS
WHERE DOJ NOT LIKE '2020%';

54.WQTD DETAILS OF EMPS WHOSE FNAME IS NOT STARTING WITH S AND D???

SELECT *
FROM EMPS
WHERE FNAME NOT LIKE 'S%' AND FNAME NOT LIKE 'D%';

55.WQTD FNAME,LNAME IF LNAME CONTAINS EXACTLY 3 CHARACTERS???

SELECT FNAME,LNAME
FROM EMPS
WHERE LNAME LIKE '___';

56.WQTD FNAME AND ANNUAL SALARY IF ANNUAL SALARY LAST 2ND DIGIT IS 0??

SELECT FNAME,SAL*12 AS 'ANNUAL SALARY'

FROM EMPS
WHERE SAL*12 LIKE '%0_';

57.WQTD DETAILS OF EMPS IF EMP FNAME LAST 3RD CHARACTER IS E AND LNAME
LAST CHARACTER IS R AND WORKING AS WAITER OR MANAGER BUT NOT AS
FEMALE??

SELECT *
FROM EMPS
WHERE FNAME LIKE '%E__' AND LNAME LIKE '%R' AND JOB IN('WAITER','MANAGER')
AND GENDER!='F';

58.WQTD DETAILS OF EMPS IF EMP FNAME LAST 5TH CHARACTER IS H AND EMP JOB
3RD CHARACTER IS I AND EMP IS NOT ACTING AS CUSTOMER FOR HIS COMPANY???

SELECT *
FROM EMPS
WHERE FNAME LIKE '%H____' AND JOB LIKE '__I%' AND CID IS NULL;

59.WQTD DETAILS OF EMPS WHOSE FNAME STARTING WITH VOWELS????

SELECT *
FROM EMPS
WHERE FNAME LIKE 'A%' OR
FNAME LIKE 'E%' OR
FNAME LIKE 'I%' OR
FNAME LIKE 'O%' OR
FNAME LIKE 'U%';

60.WQTD FNAME,LNAME IF FNAME IS STARTING WITH VOWELS AND LNAME IS NOT

ENDING WITH VOWELS??

SELECT FNAME,LNAME
FROM EMPS
WHERE (FNAME LIKE 'A%' OR
FNAME LIKE 'E%' OR
FNAME LIKE 'I%' OR
FNAME LIKE 'O%' OR
FNAME LIKE 'U%') AND
(LNAME NOT LIKE '%A' AND
LNAME NOT LIKE '%E' AND
LNAME NOT LIKE '%I' AND
LNAME NOT LIKE '%O' AND
LNAME NOT LIKE '%U');

61.WQTD NAME OF EMPS FROM PENTAGON TABLE IF NAME CONTAINS ATLEAST 1 %

IN IT???

CREATE TABLE PENTAGON

( ID INT,
NAME VARCHAR(20)
);

insert INTO PENTAGON VALUES(1,'RAKS%HITH'),(2,'SHAR%A%TH'),(3,'PAV_AN');

SELECT NAME
FROM PENTAGON
WHERE NAME LIKE '%\%%';

\ IT IS USED TO REMOVE SPECIAL BEHAVIOUR FROM SPECIAL CHARACTER.

62.WQTD NAME OF EMPS FROM PENTAGON IF NAME CONTAINS ATLEAST 2 % IN IT??

SELECT NAME
FROM PENTAGON
WHERE NAME LIKE '%\%%\%%';

63.WQTD NAME OF EMPS FROM PENTAGON IF NAME 4TH CHARACTER IS _

SELECT NAME
FROM PENTAGON
WHERE NAME LIKE '___\_%';

AGGREGATE FUNCTION/GROUP FUNCTION

---------------------------------
TYPES OF AGGREGATE FUNCTION
----------------------------
1.MAX():
--------
IT IS USED TO OBTAIN MAXIMUM VALUE FROM THE GIVEN COLUMN.

SYNTAX: MAX(COLUMN_NAME/EXPRESSION)

EX:

SELECT MAX(SAL)
FROM EMPS; O/P: 100000

2.MIN():
--------
IT IS USED TO OBTAIN MINIMUM VALUE FROM THE GIVEN COLUMN.

SYNTAX: MIN(COLUMN_NAME/EXPRESSION)

EX:
SELECT MIN(SAL)
FROM EMPS; O/P:20000

3.SUM():
--------
IT IS USED TO OBTAIN TOTAL VALUE FROM THE GIVEN COLUMN.

SYNTAX: SUM(COLUMN_NAME/EXPRESSION)

EX:

SELECT SUM(SAL)
FROM EMPS; O/P:510000

4.AVG():
--------
IT IS USED TO OBTAIN AVERAGE VALUE FROM THE GIVEN COLUMN.

SYNTAX: AVG(COLUMN_NAME/EXPRESSION)

EX:

SELECT AVG(SAL)
FROM EMPS; O/P:51000

5.COUNT():
----------
IT IS USED TO OBTAIN NUMBER OF VALUES PRESENT IN THE GIVEN COLUMN.

SYNTAX: COUNT(*/COLUMN_NAME/EXPRESSION)

EX:

SELECT COUNT(SAL)
FROM EMPS; O/P:10

SELECT COUNT(*)
FROM EMPS; O/P:10

NOTE:ONLY FOR COUNT() WE CAN USE * AS A ARGUMENT.

CHARACTERISTICS OF AGGREGATE FUNCTIONS

--------------------------------------
>IT TAKES N NUMBER OF INPUTS AND GENERATE SINGLE OUTPUT.
>IT EXECUTES GROUP BY GROUP.
>WE CANT USE NORMAL COLUMNS ALONG WITH AGGREGATE FUNCTIONS IN SELECT
CLAUSE.
>WE CAN USE ONLY ONE COLUMN AS ARGUMENT FOR AGGREGATE FUNCTION.
>WE CANT NEST AGGREGATE FUNCTIONS.
>WE CANT USE AGGREGATE FUNCTIONS INSIDE WHERE CLAUSE.
>IT IGNORES NULL VALUES.
>WE CAN USE GROUP BY EXPRESSION ALONG WITH AGGREGATE FUNCTION IN
SELECT CLAUSE.

64.WQTD TOTAL SALARY GIVEN TO ALL THE WAITERS???

SELECT SUM(SAL)
FROM EMPS
WHERE JOB='WAITER';

65.WQTD AVERAGE SALARY,TOTAL SALARY,MINIMUM SALARY AND MAXIMUM SALARY

GIVEN TO THE EMPS IF EMPS FNAME IS STARTING WITH K OR A????

SELECT AVG(SAL),SUM(SAL),MIN(sAL),MAX(sAL)
FROM EMPS
WHERE FNAME LIKE 'K%' OR FNAME LIKE 'A%';

66.WQTD NUMBER OF EMPS WORKING AS DELIVERY OR MANAGER???

SELECT COUNT(*)
FROM EMPS
WHERE JOB IN('DELIVERY','MANAGER');

67.WQTD NUMBER OF UNIQUE JOB ROLES PRESENT IN EMPS TABLE???

SELECT COUNT(DISTINCT JOB)

FROM EMPS;

68.WQTD NUMBER OF EMPS WORKING IN EACH JOB ROLE??

SELECT COUNT(*),JOB
FROM EMPS
GROUP BY JOB;

ORDER OF EXECUTION:
-------------------
1.FROM
2.[WHERE]
3.GROUP BY
4.SELECT

69.WQTD STATUS WISE NUMBER OF EMPS WORKING IN EMPS TABLE???

SELECT COUNT(*),STATUS
FROM EMPS
GROUP BY STATUS;

70.WQTD TOTAL SALARY SPENT BY THE COMPANY IN EACH JOB ROLE IF JOB ROLES
ARE DELIVERY,MANAGER OR WAITER??????

SELECT SUM(sAL),JOB
FROM EMPS
WHERE JOB IN('DELIVERY','MANAGER','WAITER')
GROUP BY JOB;

71.WQTD NUMBER OF EMPS WORKING IN EACH LOCATION EMPS ARE GETTING

SALARY MORE THAN 32000 AND LESS THAN 50000??

SELECT COUNT(*),LID
FROM EMPS
WHERE SAL>32000 AND SAL<50000
GROUP BY LID;

72.WQTD MAXIMUM SALARY OBTAINED PER LOCATION IF EMP IS GETTING SALARY

MORE THAN 25000 AND MAXIMUM SALARY OBTAINED IN LOCATION IS MORE THAN
50000???

SELECT MAX(SAL),LID
FROM EMPS
WHERE SAL>25000
GROUP BY LID
having MAX(sAL)>50000;

ORDER OF EXECUTION:
-------------------
1.FROM
2.WHERE
3.GROUP BY
4.HAVING
5.SELECT

73.WQTD AVERAGE SALARY AND TOTAL SALARY GIVEN TO EMPS PER LOCATION IF
AVERAGE SALARY IS MORE THAN 40000 AND TOTAL SALARY IS MORE THAN 75000???

SELECT AVG(sAL),SUM(SAL),LID
FROM EMPS
GROUP BY LID
HAVING AVG(SAL)>40000 AND SUM(SAL)>75000;

74.WQTD TOTAL SALARY AND NUMBER OF EMPS WORKING IN EACH JOB ROLE IF
MORE THAN 2 EMPS ARE WORKING IN EACH JOB ROLE???

SELECT SUM(sAL),COUNT(*),JOB
FROM EMPS
GROUP BY JOB
HAVING COUNT(*)>2;

75.WQTD MAXIMUM SALARY AND MINIMUM SALARY AND NUMBER OF EMPS WORKING
PER LOCATION IF LOCATION CONTAINS ATLEAST 2 AND ATMOST 2 EMPS WORKING IN
IT??

SELECT MAX(sAL),MIN(sAL),COUNT(*)
FROM EMPS
GROUP BY LID
HAVING COUNT(*)>=2 AND COUNT(*)<=2;

76.WQTD NUMBER OF EMPS GETTING SAME SALARIES/REPEATED SALARY???

SELECT COUNT(*),SAL
FROM EMPS
GROUP BY SAL
HAVING COUNT(*)>1;

77.WQTD NUMBER OF EMPS HAVING SAME GENDER AND WORKING IN SAME JOB
ROLE???

SELECT COUNT(*),GENDER,JOB
FROM EMPS
GROUP BY GENDER,JOB
HAVING COUNT(*)>1;
78.WQTD DETAILS OF EMPS BASED ON THIER SALARY MAXIMUM TO MINIMUM
ORDER???

ORDER BY:
---------
IT IS USED TO ARRENGE THE RECORDS EITHER IN ASCENDING OR DESCENDING
ORDER.

SYNTAX:

SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME
ORDER BY COLUMN_NAME ASC/DESC;

CHARACTERISTICS OF ORDER BY CLAUSE:

-----------------------------------
>IT IS A LAST EXECUTABLE CLAUSE IN A QUERY.
>IT EXECUTES AFTER SELECT CLAUSE.
>BY DEFAULT IT WILL CONSIDER ASCENDING ORDER FOR THE COLUMN.
>NORMALLY IN ALL THE TABLES RECORDS ARE ARRENGED IN ASCENDING ORDER
BASED ON PRIMARY KEY COLUMN.
>WE CAN USE ALIAS NAME INSIDE ORDER BY CLAUSE.
>WE CAN USE MULTIPLE COLUMNS INSIDE ORDER BY CLAUSE.(IT WILL GIVE THE
PRIORITY FOR FIRST COLUMN ORDER,IF THE VALUES ARE SAME FOR FIRST COLUMN
ORDER THEN IT WILL GIVE THE PRIORITY FOR SECOND COLUMN ORDER)

ORDER BY EXECUTION:
-------------------
1.FROM
2.WHERE
3.GROUP BY
4.HAVING
5.SELECT
6.ORDER BY

78.WQTD DETAILS OF EMPS BASED ON THIER SALARY MAXIMUM TO MINIMUM

ORDER???

SELECT *
FROM EMPS
ORDER BY SAL DESC;

79.WQTD FNAME,LNAME AND JOB IF EMP IS WORKING AS SECURITY OR MANAGER OR

CLEANER AND ARRENGE THE RECORDS ACCORDING TO ALPHABATICAL ORDER OF
THEIR FNAME???

SELECT FNAME,LNAME,JOB
FROM EMPS
WHERE JOB IN('SECURITY','MANAGER','CLEANER')
ORDER BY FNAME ASC;

80.WQTD NUMBER OF EMPS WHO ARE HAVING SAME GENDER AND WORKING IN SAME
JOB AND EMPS ARE GETTING SALARY MORE THAN 30000 AND ARRENGE THE JOB IN
ANTI ALPHABATICAL ORDER??

SELECT COUNT(*),GENDER,JOB
FROM EMPS
WHERE SAL>30000
GROUP BY GENDER,JOB
HAVING COUNT(*)>1
ORDER BY JOB DESC;

81.WQTD DETAILS OF FIRST 3 RECORDS FROM EMPS TABLE???

LIMIT:
------
IT IS USED TO DISPLAY SOME SPECIFIC NUMBER OF RECORDS FROM RESULTANT
TABLE.

SYNTAX:

SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME
LIMIT VALUE;

SELECT *
FROM EMPS
LIMIT 3;

82.WQTD DETAILS FIRST RECORD FROM EMPS TABLE??

SELECT *
FROM EMPS
LIMIT 1;

83.WQTD DETAILS 2ND RECORD FROM EMPS TABLE????

SELECT *
FROM EMPS
LIMIT 2;

OFFSET:
-------
IT IS USED SKIP/IGNORE SOME SPECIFIC NUMBER OF RECORDS.

SYNTAX:

SELECT COLUMN_NAME/EXPRESSION
FROM EMPS
LIMIT VALUE OFFSET VALUE;

SELECT *
FROM EMPS
LIMIT 1 OFFSET 1;

84.WQTD 3RD AND 4TH RECORD DETAILS FROM EMPS TABLE??

SELECT *
FROM EMPS
LIMIT 2 OFFSET 2;

85.WQTD 5TH AND 7TH RECORD DETAILS FROM EMPS TABLE??

86.WQTD TOP 3 HIGHEST PAID EMPS DETAILS???

SELECT *
FROM EMPS
ORDER BY SAL DESC
LIMIT 3;

87.WQTD DETAILS LAST 5 RECORDS FROM EMPS TABLE???

SELECT *
FROM EMPS
ORDER BY EID DESC
LIMIT 5;
88.WQTD 3RD MAXIMUM SALARY FROM EMPS TABLE???

SELECT DISTINCT SAL

FROM EMPS
ORDER BY SAL DESC
LIMIT 1 OFFSET 2;

OR

SELECT SAL
FROM EMPS
GROUP BY SAL
ORDER BY SAL DESC
LIMIT 1 OFFSET 2;

89.WQTD 4TH MINIMUM SALARY FROM EMPS TABLE??

SELECT DISTINCT SAL

FROM EMPS
ORDER BY SAL ASC
LIMIT 1 OFFSET 3;

CHARACTER FUNCTIONS:

CHARACTERISTICS OF CHARACTER FUNCTIONS:

----------------------------------------
>IT TAKES N NUMBER OF INPUTS AND GENERATE N NUMBER OF OUTPUTS.
>IT EXECUTES ROW BY ROW.
>WE CAN NEST CHARACTER FUNCTIONS.
>WE CAN USE CHARACTER FUNCTIONS INSIDE WHERE CLAUSE.

TYPES OF CHARACTER FUNCTIONS

----------------------------

1.LOWER():
----------
IT IS USED TO CONVERT GIVEN STRING VALUE INTO LOWERCASE.

EX:

SELECT LOWER('INDIA'); india

2.UPPER():
-----------
IT IS USED TO CONVERT GIVEN STRING VALUE INTO UPPERCASE.

EX:

SELECT UPPER('virat kohli'); VIRAT KOHLI

3.REVERSE():
------------
IT IS USED TO DISPLAY GIVEN STRING VALUE IN REVERSE FORMAT.

EX:

SELECT REVERSE('RAMYA AUNTY');YTNUA AYMAR

90.WQTD DETAILS OF EMPS IF REVERESED FNAME MATCHES WITH THE STRING

NAMA??

SELECT *
FROM EMPS
WHERE REVERSE(FNAME)='NAMA';

4.LENGTH():
-----------
IT IS USED TO OBTAIN TOTAL NUMBER OF CHARACTERS PRESENT IN GIVEN STRING
VALUE.

EX:

SELECT LENGTH('PENTAGON');8

SELECT LENGTH('PENTAGON SPACE'); 14

91.WQTD 2ND LONGEST CITY AS WELL AS THEIR RESPECTIVE LENGTH FROM

LOCATIONS TABLE IF THERE IS MORE THAN 1 2ND LONGEST CITY CHOSE THE ONE
WHICH COMES FIRST WHEN WE ORDERED ALPHABATICALLY??

SELECT CITY,LENGTH(CITY)
FROM LOCATIONS
ORDER BY LENGTH(CITY) DESC,CITY ASC
LIMIT 1 OFFSET 1;

5.CONCAT():
----------
IT IS USED TO ADD/COMBINE TWO OR MORE STRING VALUES.

SYNTAX:

CONCAT('STR1','STR2',.....'STRN');

SAMEPLE O/P:

MR/MISS VISHWAS YOUR SALARY IS 1000 RS.

FNAME SAL

SELECT CONCAT('MR/MISS',' ',FNAME,' YOUR SALARY IS ',SAL,' RS.')

FROM EMPS;

92.WQTD FNAME AND LNAME TOGETHER AS FULL NAME.

I/P:

FNAME:SHARUKH
LNAME:KHAN

O/P:

FULL NAME:
----------
SHARUKH KHAN

SELECT CONCAT(FNAME,' ',LNAME) AS FULL_NAME

FROM EMPS;

93.WQTD FNAME,LNAME AND JOB TOGETHER IN BELOW FORMAT:

I/P:

FNAME:KICCHA
LNAME:SUDEEP
JOB :ACTOR

O/P:

KICCHA SUDEEP (ACTOR)

SELECT CONCAT(FNAME,' ',LNAME,' ','(',JOB,')')
FROM EMPS;

6.SUBSTR():
-----------
IT IS USED TO EXTRACT SOME PART OF THE STRING FROM ORIGINAL STRING.

SYNTAX: SUBSTR('ORIGINAL STRING',POSITION,[LENGTH]);

CASE:1

BENGALURU
123456789

SELECT SUBSTR('BENGALURU',4,3);GAL
SELECT SUBSTR('BENGALURU',5);ALURU

CASE:2

B E N G A L U R U
-9 -8 -7 -6 -5 -4 -3 -2 -1

SELECT SUBSTR('BENGALURU',-3,2);UR
SELECT SUBSTR('BENGALURU',-6);GALURU

95.WQTD FNAME OF EMPS IF FNAME IS STARTING WITH K?????

SELECT FNAME
FROM EMPS
WHERE SUBSTR(FNAME,1,1)='K';

96.WQTD FNAME AND LNAME IF EMPLOYEE LNAME IS ENDING WITH I????

SELECT FNAME,LNAME
FROM EMPS
WHERE SUBSTR(LNAME,-1,1)='I';

97.WQTD DETAILS OF EMPLOYEES IF FNAME STARTING WITH A OR D???

SELECT *
FROM EMPS
WHERE SUBSTR(FNAME,1,1) IN('A','D');
OR
WHERE SUBSTR(FNAME,1,1)='A' OR SUBSTR(FNAME,1,1)='D';

98.WQTD DETAILS OF EMPLOYEES IF EMPLOYEE JOB IS STARTING WITH SEC OR

WAIT???

SELECT *
FROM EMPS
WHERE SUBSTR(JOB,1,3)='SEC' OR SUBSTR(JOB,1,4)='WAIT';

99.WQTD FNAME AND LNAME IF EMPLOYEES FNAME STARTING WITH VOWELS AND
LNAME IS NOT ENDING WITH VOWELS???

SELECT FNAME,LNAME
FROM EMPS
WHERE SUBSTR(FNAME,1,1) IN('A','E','O','I','U') AND SUBSTR(LNAME,-1,1) NOT
IN('A','E','I','O','U');

100.WQTD DETAILS OF EMPS WHO BORN DURING THE YEAR 1995???

SELECT *
FROM EMPS
WHERE SUBSTR(DOB,1,4)=1995;

YYYY-MM-DD
12345678910

101.WQTD FNAME,LNAME,DOJ IF EMPS JOINED IN THE MONTH OF APRIL,MAY,JUNE OR

JULY??

SELECT FNAME,LNAME,DOJ
FROM EMPS
WHERE SUBSTR(DOJ,6,2)IN(04,05,06,07);

102.WQT EXTRACT INITIALS FROM FNAME AND LNAME IN BELOW FORMAT:

I/P:

FNAME:RAMYA

LNAME:AUNTY

O/P:
R.A.

SELECT CONCAT(SUBSTR(FNAME,1,1),'.',SUBSTR(LNAME,1,1),'.')
FROM EMPS;

103.WQTD FNAME AND PASSWORD,THE PASSWORD MUST CONTAIN BELOW

CONDITIONS:

i.FIRST 3 CHARACTERS OF FNAME

ii.LENGTH OF FNAME
iii.LAST 2 CHARACTERS OF JOB ROLE

SELECT FNAME,CONCAT(SUBSTR(FNAME,1,3),LENGTH(FNAME),SUBSTR(JOB,-2,2)) AS
PASSWORD
FROM EMPS;

104.WQTD FIRST HALF OF FNAME FROM EMPS TABLE???

SELECT SUBSTR(FNAME,1,LENGTH(FNAME)/2) AS FIRST_HALF

FROM EMPS;

105.WQTD 2ND HALF OF FNAME FROM EMPS TABLE???

SELECT SUBSTR(FNAME,LENGTH(FNAME)/2+1)
FROM EMPS;

106.WQTD FIRST HALF OF FNAME IN LOWER CASE AND 2ND HALF OF FNAME IN
REVERSE FORMAT BASED ON SAMPLE O/P:

FNAME:SUNDRA

O/p; sunARD

SELECT
CONCAT(LOWER(SUBSTR(FNAME,1,LENGTH(FNAME)/2)),REVERSE(SUBSTR(FNAME,LE
NGTH(FNAME)/2+1)))
FROM EMPS;

107.WQTD FNAME AND PASSWORD FOR KIRAN,THE PASSWORD MUST CONTAIN

BELOW CONDITIONS:

i.2ND HALF OF FNAME

ii.LENGTH OF LNAME
iii.REVERSED LAST 4 CHARACTERS OF JOB ROLE

SELECT
FNAME,CONCAT(SUBSTR(FNAME,LENGTH(FNAME)/2+1),LENGTH(LNAME),REVERSE(SU
BSTR(JOB,-4,4))) PASSWORD
FROM EMPS
WHERE FNAME='KIRAN';

7.REPLACE:
----------
IT IS USED TO REPLACE SUBSTRING FROM NEW STRING IN ORIGINAL STRING.

SYNTAX:

REPLACE('ORIGINAL STRING','SUB STRING','NEW STRING');

SELECT REPLACE('PENTAGON','PENT','HEX');HEXAGON

SELECT REPLACE('PENTAGON','N','S');PESTAGOS

SELECT REPLACE('PENTAGON','R','S');PENTAGON

SELECT REPLACE('PENTAGON','N','');PETAGO

SELECT REPLACE('PENTAGON','n','R');PENTAGON

108.WQTD COUNT OF THE CHARACTER A IN MALAYALAM???

SELECT LENGTH('MALAYALAM')-LENGTH(REPLACE('MALAYALAM','A',''));

109.WQT REPLACE A AND I WITH [A] AND [I] IN FNAME???

FNAME:KIRAN
O/P: K[I]R[A]N

SELECT REPLACE(REPLACE(FNAME,'A','[A]'),'I','[I]')
FROM EMPS;

110.WQTD FNAME WHICH CONTAINS EXACTLY 1 A????

SELECT FNAME
FROM EMPS
WHERE LENGTH(FNAME)-LENGTH(REPLACE(FNAME,'A',''))=1;

NUMBER FUNCTIONS
----------------
CHARACTERISTICS OF NUMBER FUNCTIONS:
------------------------------------
>IT TAKES N NUMBER OF INPUTS AND GENERATE N NUMBER OF OUTPUTS.
>IT EXECUTES ROW BY ROW
>WE CAN NEST NUMBER FUNCTIONS
>WE CAN USE NUMBER FUNCTIONS INSIDE WHERE CLAUSE.

TYPES OF NUMBER FUNCTIONS:

--------------------------
1.ABS():
--------
IT IS USED TO CONVERT NEGATIVE VALUE INTO POSITIVE VALUE.

EX:

SELECT ABS(-18);18
SELECT ABS(18); 18

2.MOD():
--------
IT IS USED TO OBTAIN REMAINDER VALUE.

SYNTAX:

MOD(M,D);

EX:

SELECT MOD(8,2);0
SELECT MOD(8,3);2

111.WQTD DETAILS OF EMPS WHO ARE HAVING EVEN EID????

SELECT *
FROM EMPS
WHERE MOD(EID,2)=0;
3.ROUND():
-----------
IT IS USED TO ROUND OFF THE VALUE UPTO SPECIFIED NUMBER OF DECIMAL
PLACES.

SYNTAX:

ROUND(NUMBER,[DECIMAL_PLACE_VALUE]);

EX:

SELECT ROUND(420.4);420
SELECT ROUND(420.5);421

SELECT ROUND(420.567,2);420.57
SELECT ROUND(420.56743,3);420.567

112.WQTD AVERAGE SALARY OBTAINED IN EACH JOB ROLE AND ROUND OFF THE
AVERAGE SALARY UPTO 2ND DECIMAL PLACE??

SELECT ROUND(AVG(SAL),2),JOB
FROM EMPS
GROUP BY JOB;

4.CEIL:
-------
>IT WILL OBTAIN NEXT INTEGER VALUE FROM THE GIVEN DECIMAL VALUE.
(IF IT IS POSITIVE NUMBER).

>IT WILL OBTAIN CURRENT INTEGER VALUE FROM THE GIVE DECIMAL VALUE.
(IF IT IS NEGATIVE NUMBER)

SELECT CEIL(18.3);19

SELECT CEIL(-18.3);-18

5.FLOOR():
---------
>IT WILL OBTAIN CURRENT INTEGER VALUE FROM GIVEN DECIMAL VALUE.
(IF IT IS POSITIVE NUMBER)

>IT WILL OBTAIN NEXT INTEGER VALUE FROM GIVEN DECIMAL VALUE.
(IF IT IS NEGATIVE NUMBER)

EX:

SELECT FLOOR(18.3);18
SELECT FLOOR(-18.3);-19

6.TRUNCATE():
-------------
IT IS USED TO CUT OFF THE VALUE UPTO SPECIFIED NUMBER OF DECIMAL PLACES
WITHOUT ROUNDING IT.

SYNTAX:

TRUNCATE(NUMBER,DECIMAL_PLACE_VALUE);

EX:

SELECT TRUNCATE(123.4567,2);123.45

SELECT TRUNCATE(123.4567,3);123.456

7.POW():
--------
IT IS USED TO OBTAIN POWER VALUE OF THE GIVEN NUMBER.

EX:

SELECT POW(8,2); 64

8.SQRT():
---------
IT IS USED TO OBTAIN SQUARE ROOT VALUE OF NON NEGATIVE NUMBER.

SELECT SQRT(64);8

SELECT SQRT(-64);NULL
DATE FUNCTIONS
--------------
CHARACTERISTICS OF DATE FUNCTIONS:
----------------------------------
>IT TAKES N NUMBER OF INPUTS AND GENARTE N NUMBER OF OUTPUTS.
>IT EXECUTES ROW BY ROW
>WE CAN NEST DATE FUNCTIONS
>WE CAN USE DATE FUNCTIONS INSIDE WHERE CLAUSE.

TYPES OF DATE FUNCTIONS:

------------------------

1.CURDATE():
------------
IT IS USED TO OBTAIN CURRENT DATE FROM THE SYSTEM.

EX:

SELECT CURDATE(); 2025-08-11

2.SYSDATE()/NOW()
-----------------
IT IS USED TO OBTAINM CURRENT DATE AND TIME FROM THE SYSTEM.

EX:

SELECT SYSDATE();2025-08-11 12:33:51

SELECT NOW(); 2025-08-11 12:33:51

3.YEAR():
--------
IT IS USED TO EXTRACT YEAR FROM THE GIVEN DATE EXPRESSION.

EX:

SELECT YEAR('2020-10-12');2020

113.WQTD NUMBER OF EMPS HIRED IN EACH YEAR AND DISPLAY THE NUMBERS IN
MAXIMUM TO MINIMUM ORDER BASED ON THE COUNT????

SELECT COUNT(*),YEAR(DOJ)
FROM EMPS
GROUP BY YEAR(DOJ)
ORDER BY COUNT(*) DESC;
4.MONTH():
----------
IT IS USED TO EXTRACT MONTH VALUE FROM THE GIVEN DATE EXPRESSION.

EX:

SELECT MONTH('2021-10-12'); 10

114.WQTD DETAILS OF ORDERS FROM ORDERS TABLE IF ORDERS ORDERED IN THE

MONTH DECEMBER AND STATUS IS DELIVERED.

SELECT *
FROM ORDERS
WHERE MONTH(ORDER_DATE)=12 AND STATUS='DELIVERED';

5.DAY():
--------
IT IS USED TO EXTRACT DATE VALUE FROM THE GIVEN DATE EXPRESSION.

EX:

SELECT DAY('2022-11-12');12

6.DATEDIFF():
-------------
IT IS USED TO OBTAIN DAY DIFFERENCE BETWEEN TWO DATE VALUES.

EX:

SELECT DATEDIFF(CURDATE(),'2025-08-09'); 2

115.WQTD DETAILS OF EMPS WHO WERE HIRED IN LEAP YEAR ALSO IN THE DATE
20????

SELECT *
FROM EMPS
WHERE MOD(YEAR(DOJ),4)=0 AND DAY(DOJ)=20;

116.WQTD FIRST HIRED EMPLOYEE DOJ????

SELECT DOJ
FROM EMPS
ORDER BY DOJ ASC
LIMIT 1;

OR

SELECT MIN(DOJ)
FROM EMPS;

117.WQTD LAST HIRED EMPLOYEE DOJ??

SELECT MAX(DOJ)
FROM EMPS;

7.DATE_ADD():
------------
IT IS USED TO ADD SOME TIME INTERVAL FOR A GIVEN DATE VALUE.

SYNTAX:

DATE_ADD('DATE VALUE',INTERVAL VALUE UNIT);

INTERVAL:IT IS A KEYWORD USED TO ADD/SUBTRACT SOME TIME INTERVAL.

VALUE:THE AMOUNT OF TIME WHICH WE ARE ADDING.

UNIT:UNIT OF TIME INTERVAL(EX:YEAR,MONTH,..DAY)

118.WQT ADD 2 YEAR FOR CURRENT DATE????

SELECT DATE_aDD(CURDATE(),INTERVAL 2 YEAR);

119.wqt add 3 YEAR 3 MONTH FOR THE BELOW DATE:

2023-10-12

SELECT DATE_aDD('2023-10-12',INTERVAL 39 MONTH);

OR

SELECT DATE_ADD(DATE_aDD('2023-10-12',INTERVAL 3 YEAR),INTERVAL 3 MONTH);

8.DATE_SUB():
-------------
IT IS USED TO SUBTRACT SOME TIME INTERVAL FROM THE GIVEN DATE VALUE.
SYNTAX:

DATE_sUB('DATE VALUE',INTERVAL VALUE UNIT);

120.WQT SUBTRACT 5 YEAR FROM THE LAST HIRED EMPLOYEE DOJ???

SELECT DATE_SUB(MAX(DOJ),INTERVAL 5 YEAR)

FROM EMPS;

OR

SELECT DATE_SUB(DOJ,INTERVAL 5 YEAR)

FROM EMPS
ORDER BY DOJ DESC
LIMIT 1;

9.DATE_FORMAT():
----------------
IT IS USED EXTRACT SOME TIME INTERVAL FROM GIVEN DATE-TIME EXPRESSION.

SYNTAX:

DATE_FORMAT('DATE VALUE','DATE_FORMAT_PATTERN');

YYYY : %Y SELECT DATE_FORMAT(NOW(),'%Y');2025

YY : %y SELECT DATE_FORMAT(NOW(),'%y');25
MONTH : %M SELECT DATE_FORMAT(NOW(),'%M');AUGUST
MM : %m SELECT DATE_FORMAT(NOW(),'%m');08
MON : %b SELECT DATE_FORMAT(NOW(),'%b');AUG
DD : %d SELECT DATE_FORMAT(NOW(),'%d');12
DAY : %W SELECT DATE_FORMAT(NOW(),'%W');TUESDAY
DAY : %a SELECT DATE_FORMAT(NOW(),'%a');TUE
HH24 : %H SELECT DATE_FORMAT(NOW(),'%H');14
HH12 : %h SELECT DATE_FORMAT(NOW(),'%h');02
MINUTE : %i SELECT DATE_FORMAT(NOW(),'%i');59
SECOND : %s SELECT DATE_FORMAT(NOW(),'%s');19
TIME : %T SELECT DATE_FORMAT(NOW(),'%T');15:00:49
AM/PM : %p SELECT DATE_FORMAT(NOW(),'%p');PM
TIME
AM/PM : %r SELECT DATE_FORMAT(NOW(),'%r');03:02:25 PM

121.WQTD DATE-TIME EXPRESSION FOR THE CURRENT DATE AND TIME IN BELOW
FORMAT:

25-AUGUST-12 03 PM TUESDAY

SELECT DATE_FORMAT(NOW(),'%y-%M-%d %h %p %W');

122.WQTD FNAME AND CURRENT EXPERIENCE OF THE EMPS INTERMS OF YEAR???

SELECT FNAME,YEAR(CURDATE())-YEAR(DOJ) AS CUURENT_EXPERIENCE

FROM EMPS;

123.WQTD FNAME AND AGE OF THE EMPS INTERMS OF YEAR IF EMPLOYEE AGE IS
MORE THAN 35 YEAR????

SELECT FNAME,YEAR(CURDATE())-YEAR(DOB) AS AGE

FROM EMPS
WHERE YEAR(CURDATE())-YEAR(DOB)>35;

RULES OF SUBQUERY:
-----------------
>THE COLUMN_NAME SELECTED IN INNER QUERY AND THE COLUMN_NAME WRITTEN
INSIDE THE OUTER QUERY SHOULD BE OF SAME DATATYPE.

WHERE SAL>(SELECT FNAME INVALID

INT CHAR(10)

WHERE SAL>(SELECT SAL VALID

INT INT

WHERE SAL>(SELECT COMM VALID

INT INT

>IN INNER QUERY WE CAN SELECT ONLY ONE COLUMN.

WHERE SAL>(SELECT SAL,COMM INVALID

124.WQTD DETAILS OF EMPS WHO ARE GETTING SALARY LESS THAN JAHNAVI????

SELECT *
FROM EMPS
WHERE SAL<(SELECT SAL
FROM EMPS
 WHERE FNAME='JAHNAVI');

125.WQTD FNAME,LNAME,JOB AND SALARY IF EMPS ARE WORKING SAME AS

MURALI'S JOB ROLE???

SELECT FNAME,LNAME,JOB,SAL
FROM EMPS
WHERE FNAME!='MURALI' AND JOB=(SELECT JOB
FROM EMPS
WHERE FNAME='MURALI');

126.WQTD DETAILS OF EMPS IF EMPS FNAME 2ND CHARACTER IS A AND EMPLOYEE

LNAME LAST 2ND CHARACTER IS ALSO A AND GETTING SALARY LESS THAN
JAHNAVI??

SELECT *
FROM EMPS
WHERE FNAME LIKE '_A%' AND SUBSTR(LNAME,-2,1)='A' AND SAL<(SELECT SAL
FROM EMPS
WHERE FNAME='JAHNAVI');

SUBSTR(FNAME,2,1)='A' AND LNAME LIKE '%A_'

127.WQTD DETAILS OF EMPS WHO ARE GETTING 2ND MAXIMUM SALARY??

SELECT *
FROM EMPS
WHERE SAL=(SELECT DISTINCT SAL
FROM EMPS
ORDER BY SAL DESC
LIMIT 1 OFFSET 1);

128.WQTD FNAME AND LNAME TOGETHER AS FULL NAME ALONG WITH THEIR
SALARY,JOB AND LID IF EMPLOYEE IS WORKING IN THE LOCATION SAME AS SURESH
LOCATION AND GETTING SALARY MORE THAN PRIYA???

SELECT CONCAT(FNAME,' ',LNAME) AS FULL_NAME,SAL,JOB,LID

FROM EMPS
WHERE LID=(SELECT LID
FROM EMPS
WHERE FNAME='SURESH') AND SAL>(SELECT SAL
FROM EMPS
WHERE FNAME='PRIYA');
129.WQTD DETAILS OF EMPS WHO ARE ELDER THAN PRIYA???

SELECT *
FROM EMPS
WHERE DOB<(SELECT DOB
FROM EMPS
WHERE FNAME='PRIYA');

2001< 2004

130.WQTD CITY NAME OF EMPLOYEE KIRAN???

SELECT CITY
FROM LOCATIONS
WHERE LID=(SELECT LID
FROM EMPS
WHERE FNAME='KIRAN');

131.WQTD DETAILS OF EMPS WHO ARE LIVING IN THE STATE KARNATAKA???

SELECT *
FROM EMPS
WHERE LID=(SELECT LID
FROM LOCATIONS
WHERE STATE='KARNATAKA');

132.WQTD CITY AND STATE OF THE CUSTOMER VIRAT KOHLI???

SELECT CITY,STATE
FROM LOCATIONS
WHERE LID=(SELECT LID
FROM CUSTOMERS
WHERE NAME='VIRAT KOHLI');

133.WQTD MENU_ITEM DETAILS WHICH BELONGS TO SPICE HUB RESTAURANT???

SELECT *
FROM MENU_ITEMS
WHERE RESTAURANT_ID=(SELECT RESTAURANT_ID
FROM RESTAURANTS
WHERE NAME='SPICE HUB');
134.WQTD DETAILS OF EMPS WHO ARE WORKING AS SECURITY OR MANAGER IN
MUMBAI CITY?

SELECT *
FROM EMPS
WHERE JOB IN('SECURITY','MANAGER') AND LID=(SELECT LID
FROM LOCATIONS
WHERE CITY='MUMBAI');

135.WQTD DETAILS OF CUSTOMER WHO HAS GIVEN REVIEW WITH RATING 4???

SELECT *
FROM CUSTOMERS
WHERE CID=(SELECT CID
FROM REVIEWS
WHERE RATING=4);

136.WQTD DETAILS OF CUSTOMERS WHO ARE LIVING IN CHENNAI OR JAIPUR CITY???

SELECT *
FROM CUSTOMERS
WHERE LID IN(SELECT LID
FROM LOCATIONS
WHERE CITY IN('CHENNAI','JAIPUR'));

ERROR: Subquery returns more than 1 row

types OF SUBQUERY:
------------------
1.SINGLE-ROW SUBQUERY
2.MULTI-ROW SUBQUERY
3.CORRELATED SUBQUERY

1.SINGLE-ROW SUBQUERY:
----------------------
IF INNER QUERY RETURNS SINGLE VALUE WE CAN CONSIDER THAT QUERY AS
SINGLE ROW SUBQUERY.

>HERE,WE CAN USE BOTH NORMAL OPERATORS(=,!=,<,>) AND

SPECIAL OPERATORS(IN,NOT IN,ALL,ANY)

2.MULTI-ROW SUBQUERY:
---------------------
IF INNER QUERY RETURNS MORE THAN ONE VALUE WE CAN CONSIDER THAT QUERY
AS MULTI ROW SUBQUERY.

>HERE,WE CAN USE ONLY SPECIAL OPERATORS(IN,NOT IN,ALL,ANY)

137.WQTD ORDER DETAILS OF THE CUSTOMER WHO HAS GIVEN RATING 5???

SELECT *
FROM ORDERS
WHERE ORDER_ID IN(SELECT ORDER_ID
FROM CUSTOMERS
WHERE CID IN(SELECT CID
FROM REVIEWS
WHERE RATING=5));

138.WQTD DETAILS OF EMPS WHO ARE GETTING SALARY LESS THAN ALL THE
WAITERS???

SELECT *
FROM EMPS
WHERE SAL<ALL(SELECT SAL
FROM EMPS
WHERE JOB='WAITER');

ALL:
----
IT IS A MULTIVALUE OPERATOR WHICH TAKES MULTIPLE VALUES AT THE RHS AND
SINGLE VALUE AT THE LHS ALONG WITH RELATIONAL OPERATORS.

SYNTAX:

LHS RHS
COLUMN_NAME/EXPRESSION </>/<=/>= ALL(V1,V2,......VN);

>IT WORKS ON AND CONDITION.

A:1000 T
B:2000 F
C:10000 F

SAL<ALL(2000,3000,10000)
1000<ALL(
2000<ALL(
10000<ALL(

SELECT *
FROM EMPS
WHERE SAL< (SELECT MIN(SAL)
FROM EMPS
WHERE JOB='WAITER');

139.WQTD DETAILS OF EMPS WHO ARE GETTING SALARY LESS THAN ANY ONE OF
THE WAITER.?

ANY:
----
IT IS A MULTI VALUE OPERATOR WHICH TAKES MULTIPLE VALUES AT THE RHS AND
SINGLE VALUE AT THE LHS ALONG WITH RELATIONAL OPERATORS.

SYNTAX:

COLUMN_NAME/EXPRESSION </>/<=/>= ANY(V1,V2,.....VN)

>IT WORKS ON OR CONDITION.

A:1000 T
B:2000 T
C:10000 F

SAL<ANY(2000,3000,10000)

1000<ANY(
2000<ANY(
10000<ANY(

SELECT *
FROM EMPS
WHERE SAL<ANY(SELECT SAL
FROM EMPS
WHERE JOB='WAITER');

SELECT *
FROM EMPS
WHERE SAL< (SELECT MAX(SAL)
 FROM EMPS
WHERE JOB='WAITER');

140.WQTD DETAILS OF CUSTOMERS WHO ORDERED SOME ITEM????

SELECT *
FROM CUSTOMERS
WHERE ORDER_ID IN(SELECT ORDER_ID
FROM ORDERS);

SELECT *
FROM CUSTOMERS
WHERE ORDER_ID IS NOT NULL;

141.WQTD NAME OF THE CUSTOMERS WHOSE ORDERS FAILED IN THE PAYMENT???

SELECT NAME
FROM CUSTOMERS
WHERE ORDER_ID IN(SELECT ORDER_ID
FROM ORDERS
WHERE ORDER_ID IN(SELECT ORDER_ID
FROM PAYMENTS
WHERE STATUS='FAILED'));

142.WQTD DETAILS OF EMPS WHO DELIVERED THE ITEM TO THE CUSTOMER WHO
BELONGS TO KOLKATA CITY??

SELECT *
FROM EMPS
WHERE EID IN(SELECT EID
FROM ORDERS
WHERE STATUS='DELIVERED' AND ORDER_ID IN(SELECT ORDER_ID
FROM CUSTOMERS
WHERE LID IN(SELECT LID
FROM LOCATIONS
WHERE CITY='KOLKATA')));

143.WQTD DETAILS OF MURALI'S MANAGER????

select *
FROM EMPS
WHERE EID IN(SELECT MGR
FROM EMPS
WHERE FNAME='MURALI');

144.wqtd city name of divya's manager???

SELECT CITY
FROM LOCATIONS
WHERE LID IN(SELECT LID
FROM EMPS
WHERE EID IN(SELECT MGR
FROM EMPS
WHERE FNAME='DIVYA'));

145.WQTD DETAILS OF AMAN'S MANAGER'S MANAGER???

SELECT *
FROM EMPS
WHERE EID IN(SELECT MGR
FROM EMPS
WHERE EID IN(SELECT MGR
FROM EMPS
WHERE FNAME='AMAN'));

146.WQTD DETAILS OF EMPS WHO ARE REPORTING TO JAHNAVI???

SELECT *
FROM EMPS
WHERE MGR IN(SELECT EID
FROM EMPS
WHERE FNAME='JAHNAVI');

147.WQTD DETAILS OF EMPS WHO ARE REPORTING TO ARJUN'S MANAGER????

SELECT *
FROM EMPS
WHERE MGR IN(SELECT EID
FROM EMPS
WHERE EID IN(SELECT MGR
FROM EMPS
 WHERE FNAME='ARJUN'));

148.WQTD LOCATION DETAILS OF THE EMPS WHO ARE REPORTING TO FAIZAN'S

MANAGER'S MANAGER??

SELECT *
FROM LOCATIONS
WHERE LID IN(SELECT LID
FROM EMPS
WHERE MGR IN(SELECT EID
FROM EMPS
WHERE EID IN(SELECT MGR
FROM EMPS
WHERE EID IN(SELECT MGR
FROM EMPS
WHERE FNAME='FAIZAN'))));
OR

SELECT *
FROM LOCATIONS
WHERE LID IN(SELECT LID
FROM EMPS
WHERE MGR IN(SELECT MGR
FROM EMPS
WHERE EID IN(SELECT MGR
FROM EMPS
WHERE FNAME='FAIZAN')));

JOINS:
------
IT IS USED TO RETRIEVE DATA FROM MULTIPLE TABLES SIMULTANEOUSELY.

TYPES OF JOINS:
---------------
1.CROSS JOIN/CARTESIAN JOIN
2.INNER JOIN
3.OUTER JOIN:i.LEFT OUTER JOIN/LEFT JOIN
ii.RIGHT OUTER JOIN/RIGHT JOIN
4.SELF JOIN
5.NATURAL JOIN

1.CROSS JOIN:
-------------
IT IS USED TO MERGE THE RECORDS OF ONE TABLE WITH THE RECORDS OF
ANOTHER TABLE.

ANSI:AMERICAN NATIONAL STANDARD INSTITUE

SYNTAX:

SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME T1 CROSS JOIN TABLE_NAME T2;

149.WQTD CARTESIAN PRODUCT FROM LOCATIONS AND CUSTOMERS TABLE???

SELECT *
FROM LOCATIONS L CROSS JOIN CUSTOMERS C;

DRAWBACK:
---------
IT WILL OBTAIN MORE NUMBER OF UNMATCHED RECORDS COMPARE TO MATCHED
RECORDS.

INNER JOIN:
-----------
IT IS USED TO OBTAIN MATCHED RECORDS FROM DIFFERENT TABLES.

SYNTAX:

SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME T1 INNER JOIN TABLE_NAME T2
ON JOIN_CONDITION;

ON:IT IS A KEYWORD USED TO PASS JOIN CONDITION.

JOIN CONDITION:IT IS USED TO CONNECT DIFFERENT TABLES.

T1.COLUMN_NAME=T2.COLUMN_NAME

150.WQTD EMPLOYEE FNAME ALONG WITH HIS CITY FROM EMPS AND LOCATIONS
TABLE??

EMPS:E
LOCATIONS:L

SELECT E.FNAME AS EMPLOYEE_NAME,L.CITY AS EMPLOYEE_CITY

FROM EMPS E INNER JOIN LOCATIONS L
ON E.LID=L.LID;

151.WQTD ITEM NAME AND RESTAURANT NAME IF ITEM NAME IS FISH CURRY???

MENU_ITEMS:M
RESTAURANTS:R

SELECT M.NAME AS ITEM_NAME,R.NAME AS RESTAURANT_NAME

FROM MENU_ITEMS M INNER JOIN RESTAURANTS R
ON M.RESTAURANT_ID=R.RESTAURANT_ID
WHERE M.NAME='FISH CURRY';

152.WQTD AVERAGE RATING PER RESTAURANT NAME???

REVIEWS:R
RESTAURANTS:RR

SELECT AVG(R.RATING),RR.NAME AS RESTAURANT_NAME

FROM REVIEWS R INNER JOIN RESTAURANTS RR
ON R.RESTAURANT_ID=RR.RESTAURANT_ID
GROUP BY RR.NAME;

153.WQTD TOP 2 RESTAURANT NAME WHICH GOT HIGHEST RATING???

REVIEWS:R
RESTAURANTS:RR

SELECT RR.NAME AS RESTAURANT_NAME,R.RATING

FROM REVIEWS R INNER JOIN RESTAURANTS RR
ON R.RESTAURANT_ID=RR.RESTAURANT_ID
ORDER BY R.RATING DESC
LIMIT 2;

154.WQTD ORDER DETAILS AND CUSTOMER NAME ALONG WITH DELIVERY PERSON
NAME???

ORDERS:O
CUSTOMERS:C
EMPS:E
SELECT O.*,C.NAME CUSTOMER_NAME,E.FNAME AS DELIEVRY_PERSON_NAME
FROM ORDERS O INNER JOIN CUSTOMERS C INNER JOIN EMPS E
ON O.ORDER_ID=C.ORDER_ID AND O.EID=E.EID;

155.WQTD CUSTOMER NAME AND THEIR ORDER DETAILS WITH PENDING PAYMENTS
BUT ACCEPTED STATUS???

CUSTOMERS:C
ORDERS:O
PAYMENTS:P

SELECT C.NAME,O.*,P.STATUS
FROM CUSTOMERS C INNER JOIN ORDERS O INNER JOIN PAYMENTS P
ON C.ORDER_ID=O.ORDER_ID AND O.ORDER_ID=P.ORDER_ID
WHERE P.STATUS='PENDING' AND O.STATUS='ACCEPTED';

156.WQTD EMPLOYEE FNAME WHO DELIVERED MOST ORDERS??

EMPS:E
ORDERS:O

SELECT E.FNAME DELIVERY_PERSON_NAME,COUNT(*)

FROM EMPS E INNER JOIN ORDERS O
ON E.EID=O.EID
WHERE O.STATUS='DELIVERED'
GROUP BY E.FNAME
ORDER BY COUNT(*) DESC
LIMIT 1;

157.WQTD REVIEW DETAILS OF THE RESTAURANT ALONG WITH CUSTOMER NAME???

REVIEWS:R
RESTAURANTS:RR
CUSTOMERS:C

SELECT R.*,RR.NAME AS RESTAURANT_NAME,C.NAME CUSTOMER_NAME

FROM REVIEWS R INNER JOIN RESTAURANTS RR INNER JOIN CUSTOMERS C
ON R.RESTAURANT_ID=RR.RESTAURANT_ID AND R.CID=C.CID;

158.WQTD CUSTOMER NAME WHO MADE THE HIGHEST PAYMENT???

CUSTOMERS:C
ORDERS:O
PAYMENTS:P

SELECT C.NAME AS CUSTOMER_NAME,SUM(P.AMOUNT)

FROM CUSTOMERS C INNER JOIN ORDERS O INNER JOIN PAYMENTS P
ON C.ORDER_ID=O.ORDER_ID AND O.ORDER_ID=P.ORDER_ID
WHERE P.STATUS='COMPLETED'
GROUP BY C.NAME
ORDER BY SUM(P.AMOUNT) DESC
LIMIT 1;

159.WQTD CUSTOMER NAME AND DELIVERY PERSON NAME IF CUSTOMER AND

DELIVERY PERSON IS LIVING IN DIFFERENT CITY??

CUSTOMERS:C
ORDERS:O
EMPS:E

SELECT C.NAME AS CUSTOMER_NAME,E.FNAME DELIVERY_PERSON_NAME

FROM CUSTOMERS C INNER JOIN ORDERS O INNER JOIN EMPS E
ON C.ORDER_ID=O.ORDER_ID AND O.EID=E.EID
WHERE C.LID!=E.LID;

160.WQTD REVENUE GENERATED BY EACH STATE???

PAYMENTS:P
LOCATIONS:L
CUSTOMERS:C
ORDERS:O

SELECT SUM(P.AMOUNT) AS REVENUE,L.STATE

FROM CUSTOMERS C INNER JOIN ORDERS O INNER JOIN PAYMENTS P INNER JOIN
LOCATIONS L
ON C.ORDER_ID=O.ORDER_ID AND P.ORDER_ID=O.ORDER_ID AND C.LID=L.LID
WHERE P.STATUS='COMPLETED'
GROUP BY L.STATE;

161.WQTD MONTHWISE REVENUE GENERATED IN THE YEAR 2024??

PAYMENTS:P
ORDERS:O

SELECT SUM(P.AMOUNT) REVENUE,MONTH(O.ORDER_DATE)

FROM PAYMENTS P INNER JOIN ORDERS O
ON P.ORDER_ID=O.ORDER_ID
WHERE P.STATUS='COMPLETED' AND YEAR(O.ORDER_DATE)=2024
GROUP BY MONTH(O.ORDER_DATE);

162.WQTD CUSTOMERS WHO ORDERED FROM RESTAURANTS IN A SAME CITY???

customers:c
ORDERS:O
RESTAURANTS:R
LOCATIONS:L

SELECT C.NAME CUSTOMER_NAME,R.CITY AS RESTAURANT_CITY,L.CITY

CUSTOMER_CITY
FROM CUSTOMERS C INNER JOIN ORDERS O INNER JOIN RESTAURANTS R INNER
JOIN LOCATIONS L
ON C.ORDER_ID=O.ORDER_ID AND R.RESTAURANT_ID=O.RESTAURANT_ID AND
C.LID=L.LID
WHERE R.CITY=L.CITY;

SELF JOIN:
----------
IT IS USED TO OBTAIN MATCHED RECORDS FROM SAME TABLES.

SYNTAX:

SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME T1 JOIN TABLE_NAME T2
ON JOIN_CONDITION;

163.WQTD EMPLOYEE FNAME ALONG WITH HIS MANAGER FNAME FROM EMPS
TABLE???

E1:FOR EMPS
E2:FOR MANAGERS

SELECT E1.FNAME AS EMP_NAME,E2.FNAME AS MANAGER_NAME

FROM EMPS E1 JOIN EMPS E2
ON E1.MGR=E2.EID;

164.WQTD EMPLOYEE FNAME,SALARY AND HIS MANAGER FNAME,SALARY IF

EMPLOYEE IS GETTING SALARY MORE THAN 35000 AND MANAGER IS GETTING
SALARY LESS THAN 500000??
E1:FOR EMPS
E2:FOR MANAGERS

SELECT E1.FNAME AS EMP_NAME,E1.SAL AS EMP_SALARY,E2.FNAME AS

MANAGER_NAME,
E2.SAL AS MANAGER_SALARY
FROM EMPS E1 JOIN EMPS E2
ON E1.MGR=E2.EID
WHERE E1.SAL>35000 AND E2.SAL<500000;

165.WQTD EMPLOYEE FNAME,DOB AND MANAGER FNAME AND DOB IF EMPLOYEE IS

ELDER THAN HIS MANAGER???

E1:FOR EMPS
E2:FOR MANAGERS

SELECT E1.FNAME EMP_NAME,E1.DOB EMP_DOB,E2.FNAME MANAGER_NAME,E2.DOB

MANAGER_DOB
FROM EMPS E1 JOIN EMPS E2
ON E1.MGR=E2.EID
WHERE E1.DOB<E2.DOB;

166.WQTD FNAME OF AMAN'S MANAGER'S MANAGER????

E1:FOR AMAN
E2:FOR AMAN MANAGER
E3:FOR AMAN MANAGER MANAGER

SELECT E3.FNAME
FROM EMPS E1 JOIN EMPS E2 JOIN EMPS E3
ON E1.MGR=E2.EID AND E2.MGR=E3.EID
WHERE E1.FNAME='AMAN';

167.WQTD DETAILS OF EMPS WHO ARE GETTING SALARY MORE THAN KIRAN????

E1:FOR EMPS
E2:FOR KIRAN

SELECT E1.*
FROM EMPS E1 JOIN EMPS E2
ON E1.SAL>E2.SAL
WHERE E2.FNAME='KIRAN';
168,WQTD DETAILS OF EMPS WHO ARE REPORTING TO PRIYA IF PRIYA IS GETTING
SALARY MORE THAN KIRAN???

E1:FOR EMPS
E2:FOR PRIYA
E3:FOR KIRAN

SELECT E1.*
FROM EMPS E1 JOIN EMPS E2 JOIN EMPS E3
ON E1.MGR=E2.EID AND E2.SAL>E3.SAL
WHERE E2.FNAME='PRIYA' AND E3.FNAME='KIRAN';

169.WQTD EMPLOYEE FNAME,JOB,MANAGER FNAME,JOB ALONG WITH THEIR CITY

NAMES
IF EMPLOYEE IS WORKING AS DELIVERY OR CHEF AND EMPLOYEE CITY IS EITHER
JAIPUR OR DELHI AND MANAGER IS WORKING AS WAITER????

E1:FOR EMPLOYEES
E2:FOR MANAGERS
L1:EMPLOYEE CITY
L2:MANAGER CITY

SELECT E1.FNAME EMP_NAME,E1.JOB EMP_JOB,E2.FNAME MANAGER_NAME,E2.JOB

MANAGER_JOB,L1.CITY EMPLOYEE_CITY,L2.CITY MANAGER_CITY
FROM EMPS E1 JOIN EMPS E2 INNER JOIN LOCATIONS L1 INNER JOIN LOCATIONS L2
ON E1.MGR=E2.EID AND E1.LID=L1.LID AND E2.LID=L2.LID
WHERE E1.JOB IN('DELIVERY','CHEF') AND L1.CITY IN('JAIPUR','DELHI') AND
E2.JOB='WAITER';

1.LEFT OUTER JOIN

------------------

IT IS USED TO OBTAIN MATCHED AND UNMATCHED RECORDS OF LEFT TABLE.

SYNTAX:

SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME T1 LEFT OUTER JOIN TABLE_NAME T2
ON T1.COLUMN_NAME=T2.COLUMN_NAME;

170.WQTD MATCHED AND UNMTACHED RECORDS OF CUSTOMERS TABLE:

CUSTOMERS:LEFT
LOCATIONS:RIGHT

SELECT *
FROM CUSTOMERS C LEFT OUTER JOIN LOCATIONS L
ON C.LID=L.LID;

RIGHT OUTER JOIN

-----------------
IT IS USED TO OBTAIN MATCHED AND UNMATCHED RECORDS OF RIGHT TABLE.

SYNTAX:

SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME T1 RIGHT OUTER JOIN TABLE_NAME T2
ON T1.COLUMN_NAME=T2.COLUMN_NAME;

170.WQTD MATCHED AND UNMTACHED RECORDS OF LOCATIONS TABLE:

CUSTOMERS:LEFT
LOCATIONS:RIGHT

SELECT *
FROM CUSTOMERS C RIGHT OUTER JOIN LOCATIONS L
ON C.LID=L.LID;

171.WQTD CITY NAME WHERE NO CUSTOMERS ARE LIVING???

SELECT L.CITY
FROM CUSTOMERS C RIGHT OUTER JOIN LOCATIONS L
ON C.LID=L.LID
WHERE C.LID IS NULL;

172.WQTD CUSTOMER NAME WHO HAS NOT GIVEN REVIEW???

SELECT C.NAME
FROM CUSTOMERS C LEFT OUTER JOIN REVIEWS R
ON C.CID=R.CID
WHERE R.CID IS NULL;

173.WQTD RESTAURANT NAME WHICH DONT HAVE ANY REVIEW??

SELECT R.NAME
FROM RESTAURANTS R LEFT OUTER JOIN REVIEWS RR
ON R.RESTAURANT_ID=RR.RESTAURANT_ID
WHERE RR.CID IS NULL;

NATURAL JOIN
------------
IT IS USED TO OBTAIN MATCHED RECORDS FROM MULTIPLE TABLES BASED ON
COMMEN ATTRIBUTE.

SYNTAX:

SELECT COLUMN_NAME/EXPRESSION
FROM TABLE_NAME T1 NATURAL JOIN TABLE_NAME T2;

SELECT *
FROM CUSTOMERS C NATURAL JOIN LOCATIONS L;

SELECT *
FROM EMPS E1 NATURAL JOIN EMPS E2;

SET OPERATORS:
--------------
1.UNION
2.UNION ALL
3.INTERSECTION

A:{1,2,3,4,5} B:{5,6,7}

A UNION B:{1,2,3,4,5,6,7}

A UNION ALL B:{1,2,3,4,5,5,6,7}

A INTERSECTION B:{5}

UNION:
------
IT IS USED TO RETRIVE THE DATA FROM MULTIPLE TABLES VERTICALLY.

>IT WILL AVOID DUPLICATE VALUES IN THE OUTPUT.

UNION ALL:
----------
IT IS USED TO RETRIEVE THE DATA FROM MULTIPLE TABLES VERTICALLY.

>IT WILL INCLUDE THE DUPLICATES VALUES IN THE OUTPUT.

(SELECT FNAME
FROM EMPS)
UNION
(SELECT FNAME
FROM EMPS); 10 FNAME

(SELECT FNAME
FROM EMPS)
UNION ALL
(SELECT FNAME
FROM EMPS); 20 FNAME

RULES:
------
1.WE SHOULD USE SEMICOLEN FOR THE LAST QUERY
2.WE SHOULD USE ROUND BRACES FOR THE QUERIES
3.WE SHOULD USE SAME NUMBER OF COLUMNS IN SELECT CLAUSES.

174.WQTD MATCHED AND UNMATCHED RECORDS FROM BOTH CUSTOMERS AND

LOCATIONS TABLE.

CUSTOMERS:LEFT
LOCATIONS:RIGHT

(SELECT *
FROM CUSTOMERS C LEFT OUTER JOIN LOCATIONS L
ON C.LID=L.LID)
UNION
(SELECT *
FROM CUSTOMERS C RIGHT OUTER JOIN LOCATIONS L
ON C.LID=L.LID);

175.WQTD DETAILS OF 3RD AND 5TH RECORD FROM EMPS TABLE???

(SELECT *
FROM EMPS
LIMIT 1 OFFSET 2)
UNION
(SELECT *
FROM EMPS
LIMIT 1 OFFSET 4);

176.WQTD DETAILS OF 6TH,7TH,2ND AND 10TH RECORD FROM EMPS TABLE??

(SELECT *
FROM EMPS
LIMIT 2 OFFSET 5)
UNION
(SELECT *
FROM EMPS
LIMIT 1 OFFSET 1)
UNION
(SELECT *
FROM EMPS
LIMIT 1 OFFSET 9);

CREATE DATABASE DEMO;

TO DISABLE AUTOCOMMIT IN MYSQL

------------------------------

SET AUTOCOMMIT=0;

CREATE TABLE EMPLOYEE

(
ID INT,
NAME VARCHAR(20)
);

INSERT INTO EMPLOYEE VALUES(1,'YASH'),(2,'PRABHAS');

BASICALLY ALL THE DDL COMMANDS ARE AUTOCOMMIT COMMANDS.

TCL(TRANSACTION CONTROL LANGUAGE)

---------------------------------
1.COMMIT
2.ROLLBACK
3.SAVEPOINT

1.COMMIT:
---------
IT IS USED TO SAVE ALL THE TRANSACTIONS PERMENENTLY INSIDE THE DATABASE.

SYNTAX:
------
COMMIT;

2.ROLLBACK:
-----------
IT IS USED TO ROLLOUT THE OPERATIONS UPTO PREVIOUSELY USED COMMIT
STATEMENT.

SYNTAX:

ROLLBACK;

>WE CAN GET BACK DELETED RECORD BY USING ROLLBACK IF COMMIT IS NOT USED
AFTER DELETE OPERATION.

3.SAVEPOINT:
------------
IT IS USED TO MARK THE POSITION BETWEEN THE TRANSACTIONS.

SYNTAX:

SAVEPOINT SAVEPOINT_NAME;

>HERE DATA WILL BE STORED TEMPORARILY BUT NOT PERMENENTLY INSIDE THE
DATABASE.

>WE CAN ROLL OUT THE OPERATIONS UPTO CERTAIN SAVEPOINTS.

SYNTAX:

ROLLBACK TO SAVEPOINT_NAME;

DCL(DATA CONTROL LANGUAGE)

---------------------------
1.GRANT
2.REVOKE

1.GRANT:
--------
IT IS USED GRANT THE PERMISSION OF THE DATA FROM ONE USER TO ANOTHER
USER.

SYNTAX:
-------

GRANT SQL_STATEMENT ON TABLE_NAME TO 'USERNAME'@'HOSTNAME';

TO VIEW ALL THE USERS PRESENT IN MYSQL:

---------------------------------------
STEP:1

USE INFORMATION_SCHEMA;

STEP:2

SELECT *
FROM USER_ATTRIBUTES;

TO CHECK THE ACTIVE USER IN MYSQL:

----------------------------------

SELECT USER();

TO CREATE USER IN MYSQL:

------------------------
SYNTAX:

CREATE USER 'USERNAME'@'HOSTNAME'IDENTIFIED BY'PASSWORD';

HOSTNAME:LOCALHOST,%

USERNAME:PENTAGON
HOSTNAME:LOCALHOST
PASSWORD:SQL

CREATE USER'PENTAGON'@'LOCALHOST'IDENTIFIED BY'SQL';

TO USE MYSQL USER ACCOUNT IN COMMAND PROMPT:

--------------------------------------------
SYNTAX:

mysql -u username -p

GRANT SELECT ON EMPS TO 'PENTAGON'@'LOCALHOST';

GRANT UPDATE ON EMPS TO 'PENTAGON'@'LOCALHOST';

GRANT ALL ON EMPS TO 'PENTAGON'@'LOCALHOST';

ALL:IT IS USED GRANT ALL THE PERMISSIONS AT A TIME.

2.REVOKE:
---------
IT IS USED TO GETBACK THE PERMISSION OF THE DATA FROM ANOTHER USER.

SYNTAX:

REVOKE SQL_STATEMENT ON TABLE_NAME FROM 'USERNAME'@'HOSTNAME';

DELETE

REVOKE DELETE ON EMPS FROM 'PENTAGON'@'LOCALHOST';

REVOKE ALL ON EMPS FROM 'PENTAGON'@'LOCALHOST';

DATABASE_NAME.*

TO DROP USER FROM MYSQL:

------------------------

SYNTAX:

DROP USER 'USERNAME'@'HOSTNAME';

PENTAGON

DROP USER'PENTAGON'@'LOCALHOST';

KEY ATTRIBUTES:THE ATTRIBUTES WHICH ARE ELIGIBLE TO BECOME PRIMARY KEY

will BE KNOWN AS KEY ATTRIBUTES
NON KEY ATTRIBUT:THE ATTRIBUTES WHICH ARE NOT ELIGIBLE TO BECOME PRIMARY
KEY WILL BE KNOWN AS NON KEY ATTRIBUTES

SUPER KEY ATTRIBUTE: A ATTRIBUTE OR COMBINATION OF ATTRIBUTES USED

UNIQULY IDENTIFIED THE RECORDS WILL BE KNOWN AS SUPER KEY ATTRIBUTES

CANDIDATE KEY:IT IS smallest SUBSET AMONG SUPER KEY ATTRIBUTES

>IN A TABLE WE CAN HAVE MULTIPLE CANDIDATE KEY BUT SINGLE PRIMARY KEY

PRIMARY KEY:A ATTRIBUTE WHICH IS USED TO UNIQUELY IDENTIFIED THE RECORDS

FOREIGN KEY:A ATTRIBUTE WHICH IS USED TO ESTABLISH CONNECTION BETWEEN

MULTIPLE TABLES

COMPOSITE KEY:IT IS A COMBINATION OF TWO OR MORE ATTRIBUTES AMONG SUPER

KEY ATTRIBUTES

COMPOUND KEY:IF THE COMPOSITE KEY ATTRIBUTE CONTAINS ATLEAST ONE

FOREIGN KEY THEN WE CAN CONSIDER IT AS COMPOUND KEY ATTRIBUTE

177.WQTD FNAME OF EMPS WHO ARE GETTING SALARY MORE THAN AVERAGE
SALARY IN THIER JOB ROLE.

E1:FOR EMPS
E2:TO FIND AVERAGE SALARY

SELECT E1.FNAME
FROM EMPS E1
WHERE E1.SAL>(SELECT AVG(E2.SAL)
FROM EMPS E2
WHERE E1.JOB=E2.JOB);

CORRELATED SUBQUERY:
---------------------
HERE BOTH OUTER QUERY AND INNER QUERY MUTUALLY DEPENDS ON EACH OTHER.

WORKING PRINCIPLE OF CORRELATED SUBQUERY:

------------------------------------------
>FIRST OUTER QUERY EXECUTES PARTIALLY.
>INNER QUERY WILL EXECUTES FOR EACH RECORD OF OUTER QUERY TABLE.
>OUTER QUERY EXECUTES COMPLETELY AND RETURNS FINAL OUTPUT.
178.WQTD FNAME OF EMPLOYEES WHO ARE YOUNGER THAN THEIR MANAGERS???

E1:FOR EMPS
E2:FOR MANAGERS

SELECT E1.FNAME
FROM EMPS E1
WHERE E1.DOB>(SELECT E2.DOB
FROM EMPS E2
WHERE E1.MGR=E2.EID);
DEPENDENCY:
-----------
IF ONE ATTRIBUTE DEPENDS ON ANOTHER ATTRIBUTE THEN THE PROCESS WILL BE
KNOWN AS DEPENDENCY.

TYPES OF DEPENDENCY:
--------------------
1.TOTAL FUNCTIONAL DEPENDENCY
2.PARTIAL FUNCTIONAL DEPENDENCY
3.TRANSITIVE FUNCTIONAL DEPENDENCY

RANKING FUNCTIONS/WINDOW FUNCTIONS

-----------------------------------
IT IS USED TO ASSIGN RANKS FOR ALL THE RECORDS IN A TABLE.

SYNTAX:
-------

SELECT RANKING_FUNCTION() OVER([PARTITION BY COLUMN_NAME] ORDER BY

COLUMN_NAME ASC/DESC)
FROM TABLE_NAME;

OVER:IT IS A CLAUSE USED TO PASS RANKING FUNCTIONS INSIDE SELECT CLAUSE.

PARTITION BY:IT IS A CLAUSE USED TO CREATE THE GROUPS,IT RESETS THE RANKS
AFTER EACH GROUP.

TYPES OF RANKING FUNCTIONS:

---------------------------
1.ROW_NUMBER()
2.RANK()
3.DENSE_RANK()
1.ROW_NUMBER():
---------------
IT IS USED TO ASSIGN UNIQUE RANKS FOR ALL THE RECORDS IN A TABLE.

SYNTAX:
-------

SELECT ROW_NUMBER() OVER([PARTITION BY COLUMN_NAME] ORDER BY

COLUMN_NAME ASC/DESC)
FROM TABLE_NAME;

SELECT FNAME,JOB,SAL,ROW_NUMBER() OVER(PARTITION BY JOB ORDER BY SAL

DESC) AS 'RANK'
FROM EMPS;

SELECT FNAME,JOB,SAL,ROW_NUMBER() OVER(ORDER BY SAL DESC) AS 'RANK'

FROM EMPS;

DRAWBACK:
---------
IT WILL ASSIGN DIFFERENT RANKS FOR THE TIED RECORDS.

2.RANK()
--------
IT IS USED TO ASSIGN RANKS FOR ALL THE RECORDS IN A TABLE.

>IT WILL ASSIGN SAME RANK FOR THE TIED RECORDS.

SYNTAX:
-------
SELECT RANK() OVER([PARTITION BY COLUMN_NAME] ORDER BY COLUMN_NAME
ASC/DESC)
FROM TABLE_NAME;

SELECT FNAME,JOB,SAL,RANK() OVER(ORDER BY SAL DESC) AS 'RANK'

FROM EMPS;

DRAWBACK:
---------
IT SKIPS NEXT RANKING NUMBERS.

3.DENSE_RANK():
---------------
IT IS USED TO ASSIGN RANKS FOR ALL THE RECORDS IN A TABLE.

>IT WILL ASSIGN SAME RANKS FOR THE TIED RECORDS ALSO IT REMAINS NEXT
RANKING NUMBERS IN SEQUENTIAL ORDER.

SYNTAX:
-------
SELECT DENSE_RANK() OVER([PARTITION BY COLUMN_NAME] ORDER BY
COLUMN_NAME ASC/DESC)
FROM TABLE_NAME;

SELECT FNAME,JOB,SAL,DENSE_RANK() OVER(ORDER BY SAL DESC) AS 'RANK'

FROM EMPS;

179.WQTD TOP 2 HIGHEST PAID EMPLOYEES FNAME,SALARY AND ASSIGN UNIQUE

RANKS FOR THE EMPS???

SELECT FNAME,SAL,ROW_NUMBER() OVER(ORDER BY SAL DESC) AS 'RANK'

FROM EMPS
LIMIT 2;

1.IS IT POSSIBLE TO CREATE DUPLICATE TABLE???

YES....

SYNTAX:

CREATE TABLE TABLE_NAME(SELECT * FROM TABLE_NAME);

EMPS:EMPS1

CREATE TABLE EMPS1(SELECT * FROM EMPS);

2.IS IT POSSIBLE TO CREATE DUPLICATE TABLE WITHOUT RECORDS???

YES....

SYNTAX:

CREATE TABLE TABLE_NAME(SELECT * FROM TABLE_NAME WHERE

FALSE_CONDITION);

EMPS2:EMPS
CREATE TABLE EMPS2(SELECT * FROM EMPS WHERE FNAME='RAMYA');

CREATE TABLE EMPS2(SELECT * FROM EMPS LIMIT 0);

3.IS IT POSSIBLE TO ADD RECORDS FROM ONE TABLE TO ANOTHER TABLE???

YES...(TABLES SHOULD HAVE SAME STRUCTURE)

INSERT INTO TABLE_NAME(SELECT STATEMENT);

EMPS2:DELIVERY BOYS DATA FROM EMPS TABLE

INSERT INTO EMPS2(SELECT * FROM EMPS WHERE JOB='DELIVERY');

INSERT INTO EMPS2(SELECT * FROM LOCATIONS WHERE CITY='MUMBAI');

SUBTABLE:

WAITERS_DATA:WAITERS RECORD FROM EMPS TABLE

CREATE TABLE WAITERS_DATA(SELECT * FROM EMPS WHERE JOB='WAITER');

VIEW:
-----
>IT IS A VIRTUAL TABLE.
>IT DOESN'T OCCUPY ANY MEMORY INSIDE THE DATABASE.
>TO OVERCOME THE DRAWBACK OF SUBTABLE WE USE VIEW.

TO CREATE VIEW:
---------------
SYNTAX:

CREATE VIEW VIEW_NAME AS(SELECT STATEMENT);

WAITER:WAITERS RECORD FROM EMPS TABLE

CREATE VIEW WAITER AS(SELECT * FROM EMPS WHERE JOB='WAITER');

SYNTAX:
-------
TO DROP VIEW:
DROP VIEW VIEW_NAME;

DROP VIEW WAITER;

CASE STATEMENT:
---------------
IT IS USED TO PASS CERTAIN CONDITIONS WHILE DISPLAYING RECORDS.

SYNTAX:

SELECT CASE
WHEN CONDITION_1 THEN VALUE_1
WHEN CONDITION_2 THEN VALUE_2
'
'
'
WHEN CONDITION_N THEN VALUE_N

ELSE DEFAULT_VALUE
END AS ALIAS_NAME

FROM TABLE_NAME;

180.WQTD FNAME AND IF GENDER='M' PRINT IT AS SIGMA_MALE ,IF GENDER='F' PRINT

IT AS DADS LIL PRINCES ELSE PRINT IT AS OTHER.

ROLE:

SELECT FNAME,CASE
WHEN GENDER='M' THEN 'SIGMA MALE'
WHEN GENDER='F' THEN 'DADS LIL PRINCES'
ELSE 'OTHER'
END AS ROLE
FROM EMPS;

181.WQTD FNAME,DOJ AND IF EMPLOYEES HIRED BEFORE 2020 PRINT IT AS SENIOR

ELSE PRINT IT AS JUNIOR???

SELECT FNAME,DOJ,CASE
WHEN YEAR(DOJ)<2020 THEN 'SENIOR'

ELSE 'JUNIOR'
END AS ROLE
FROM EMPS;