--------------------------------BASIC SQL --------------------------------------

*** SQL : STRUCTURED QUERY LANGUAGE ***

>>> WHAT IS MEAN BY SQL?

----It IS a standard programming language used to communicate with and manage
databases.
----SQL allows users to perform various operations on databases such as querying
data, inserting
----and updating records, creating and modifying database schemas, and managing
access permissions.
----IT HAS FOUR TYPES OF COMMANDS. LIKE DML, DDL, DCL, TCL.
----IT IS PROGRAMMING LANGUAGE USED TO INTERACT WITH RELATIONAL DATABASE.

>>> WHAT IS THE DIFFERENCE BETWEEN DBMS AND RDBMS?

* DBMS * * RDBMS *
1. DBMS STANDS FOR DATABASE MANAGEMENT SYSTEM. 1. RDBMS STANDS FOR RELATIONAL
DATABASE MANAGEMENT SYSTEM.
2. IN DBMS DATA STORED IN FILE FORMAT. 2. IN RDBMS DATA STOERD IN THE
TABLE FORMAT
3. NO CONNECTION BETWEEN DATA. 3. DATA IN THE FORM OF TABLES
AND LINKED TOGETHER.
4. DBMS SUPPORT A SINGLE USER. 4. RDBMS SUPPORT MULTIPLE USER.
5. DATA STORED IN SMALL QUANTITY. 5. DATA STORED IN LARGE AMOUNT.
6. THE SOFTWARE & HARDWARE REQUIREMENT IS LOW. 6. THE SOFTWARE & HARDWARE
REQUIREMENT IS HIGH.
7. THERE IS ONLY LOW SECURITY WHILE HANDLING THE DATA. 7. IT FEATURES
MULTIPLE LAYERS OF SECURITY WHILE HANDLING THE DATA.

>>> WHAT CAN SQL DO?

1. SQL can execute queries against a database
2. SQL can retrive data from a database
3. SQL can insert records in a database
4. SQL can update records in a database
5. SQL can delete records from a database
6. SQL can create new databases
7. SQL can create new tables in a database
8. SQL can create stored procedures in a database
9. SQL can create views in a database
10. SQL can set permissions on tables, procedures, and views

>>> RATE SQL OUT OF 5?

----I WOULD give SQL a 5 out of 5. SQL is a powerful and essential language for
managing
----and querying databases. Its simplicity combined with its robust capabilities
for
----data manipulation, schema definition, and data retrieval makes it a fundamental
tool
----for anyone working with databases. Whether you're a beginner or an experienced
database
----professional, SQL's versatility and widespread use across various
----database systems make it a top choice for working with data efficiently and
effectively.

>>> WHAT IS MEAN BY DATA?

----COLLECTION OF RAW INFORAMTION.
>>> WHAT IS MEAN BY DATA WAREHOUSE?
----

>>> WHAT IS MEAN BY DATA SUBSET?

----

>>> WHAT IS MEAN BY DATABASE?

----COLLECTION OF MEANINGFUL AND LOGICAL INFORAMTION.
----DATABASE ARE COLLECTION OF ORGANIZED INFORMATION THAT CAN EASILY ACCESSED
MANAGED & UPDATED.
----DATABASE : IT IS COMING FROM FRONT END DEVELOPER
----DATABASE IS COLLECTION OF DATA IN A FORMAT THAT CAN BE EASILY ACCESSED
(DIGITAL)

>>> WHAT IS DBMS?

----DBMS stands for Database Management System.
----It's a software suite that facilitates the creation, organization, retrieval,
management,
----and manipulation of databases.
----A SOFTWARE APPLICATION USED TO MANAGE OUR DATABASE IS CALLED DBMS.

>>> INVENTOR OF RDBMS?

----DONALD D CHAMBERLIN, RAYMOND F BOYCE, EDGAR FRANK CODD, 1970.
----AND CODD IS ALSO KNOWN FOR THE FATHER OF RELATIONAL DATABASE.

>>>WHAT IS MEAN BY KEYWORDS?

----KEYWORDS IN SQL ARE THE RESERVED WORDS THAT ARE USED TO PERFORM VARIOUS
OPERATION IN THE DATABASE.
----SOME EXAMPLES OF SQL KEYWORDS ARE ADD, ALTER, TABLE, AND, AS, SELECT, WHERE
ETC.

>>> WHAT IS MEAN BY CLAUSE?

----IT IS A PART OF SQL STATEMENT.
----AND A STATEMENT IS A PART OF TWO OR MORE CLAUSE.

>>>WHAT IS MEAN BY TRIGGER?

----A DATABASE TRIGGER IS PROCEDURAL CODE THAT IS AUTOMATICALLY EXECUTED IN
RESPONSE
----TO CERTAIN EVENTS ON A PARTICULAR TABLE OR VIEW IN A DATABASE.
----THE TRIGGER IS MOSTLY USED FOR MAINTAINING THE INTERGRITY.

>>> WHAT IS MEAN BY QUERY?

----A QUERY IN SQL IS A COMMAND THAT RETRIVES DATA FROM ONE OR MORE TABLES IN
DATABASE.
----QUERIES ARE USED TO EXTRACT SPECIFIC INFORMATION FROM A DATABASE
----BY FILTERATION, SORTING, GROUPING, AND CALCULATING DATA.
----SQL QUERIES CAN BE USED TO RETRIVE DATA FROM A SINGLE TABLE OR MULTIPLE TABLES
AT ONCE.

>>> WHAT IS MEAN BY SYNTAX?

----THE SET OF RULES THAT DEFINES THE COMBINATION OF SYMBOL THAT ARE CONSIDERED TO
BE CORRECTLY STRUCTURED STATEMENT.

>>> WHAT IS VARIABLE?

----AN OBJECT THAT CAN HOLD A SINGLE DATA VALUE OF A SPECIFIC TYPE.

>>> WHAT IS THE DIFFERENCE BETWEEEN SQL & MYSQL?

----MYSQL IS A SOFTWARE
----SQL IS A DATABASE LANGUAGE.
----SQL IS HELPS TO MANAGE THE DATA IN RELATIONAL DATABASE.
----MY SQL IS HELPS TO MANAGE RELATIONAL DATABASE USING SQL.

>>> WHAT IS ATTRIBUTE?

----IT IS A NAME OF COLUMN OR HEADING OF COLUMN.

>>> LIST DOWN THE DBMS SOFTWARE NAME.

1. MY SQL
2. SQL SERVER
3. MS ACCESS
4. ORACLE
5. SYBASE
6. INFORMIX
7. POSTGRES
8. DB2.

*** RDBMS (RELATIONAL DATA BASE MANAGEMENT SYSTEM) ***

----ALL TABLE CONNECTED THROUGH PRIMARY KEY OR FOREIGN KEY.
----RDBMS USES SQL QUERIES TO ACCES THE DATA IN DATABASE.

...TABLE : COLLECTION OF ROWS AND COLUMN.

...SCHEMA / USER / OWNER: LOGICAL REPRESENTATION
CREATE SCHEMA RAJVEER;

*** SQL COMMANDS ***

1. DML : DATA MANUPULATION LANGUAGE.

----SELECT / INSERT / UPDATE / DELETE / MERGE.
----IT WILL NOT CHANGE THE ACUTAL TABLE DATA. ONLY PLAYING WITH DATA.

2. DDL : DATA DEFINATION LANGUAGE.

----DROP / RENAME/ COMMENT /CREATE / ALTER / TRUNCATE.
----DR CAT
----IT WILL CHANGE THE OBJECT OF THE TABLE.

3. DCL : DATA CONTROL LANGUAGE.

----GRANT / REVOKE.
----GIVE ACCESS OR PERMISSION.
----FOR EX. WHATSAPP ADMIN
----IT IS CONTROL THE ACCESS OF DATA.

4. TCL : TRANSACTION CONTROL LANGUAGE.

----COMMIT / ROLLBACK / SAVE_POINT
----REAL TIME DATA MANUPULATING.
----CONTROL THE TRANSACTION LIKE INSERT, UPDATE, DELETE.

>>> WHAT ARE THE DIFFERENT COMMANDS? AND EXPLAIN?

>>> DIFFERENCE BETWEEN DDL & DML?
* DML * * DDL *
1. DML WORKS ON THE ACTUAL DATA ON THE TABLE. 1. DDL DEALS WITH
THE STRUCTURE (DEF OR METADATA) OF THE TABLE.
2. IN DML CHANGES ARE TEMPORARY. 2. IN DDL CHANGES
ARE PERMANENT.
3. DML COMMANDS ARE NOT AUTOSAVE. 3. DDL COMMANDS ARE
AUTOSAVE.
WITH THE HELP OF TCL COMMAND (COMMIT)
(SAVE) MAKE THE CHANGES PERMANENTLY.
4. FOR EX. SELECT, UPDATE, INSERT, DELETE. 4. FOR EX. CREATE,
ALTER, DROP, RENAME, COMMENT.

_________________________________CHAPTER 1______________________________________

*** SELECT ***

----IT IS ONLY FETCH THE DATA AS PER OUR REQUIREMNT, IT WILL SHOW THE DATA FOR ONLY
RESULT.
----NOT CHANGING THE ACTUAL DATA FROM THE TABLE.
----IT IS USED TO SELECT ALL DATA OR SPECIFIC DATA FROM DATABASE.
----IT IS ACCESS THE RECORD FROM ONE OR MORE DATABASE TABLES AND VIEWS.

*** CAPABILITIES OF SELECT COMMAND ***

1. PROJECTION:
----FETCHING OR EXTRCTING THE SPECIFIC COLUMN FROM TABLE.

2. SELECTION:
----RETRIVING THE SPECIFIC ROW FROM THE TABLE.

3. JOIN:
----RETRIVE THE DATA FROM TWO OR MORE TABLE.

....FROM: IT IS ONE KIND OF CLAUSE.

...(* ARESTETIK ) IT IS USED FOR FETCH ALL DATA. OR WE ALSO USE 1 (ONE) FOR FETCH
THE ALL VALUE.
...WE NEED TO USE SEMICOLON AT THE END OF QUERY.
SELECT * FROM TAB

SELECT FIRST_NAME SALARY FROM EMPLOYEES

----IF WE NOT PUT COMMA BETWEEN TWO COLUMN NAME THEN IT IS ASSUMING COLUMN ALISES
NEXT WORD.

>>> I WANT THE ALL ROWS AND COLUMN FROM EMPLOYEE TABLE?
SELECT * FROM EMPLOYEES;

>>> I WANT THE ALL ROWS AND COLUMN FROM DEPARTMENT TABLE?
SELECT * FROM DEPARTMENTS;

>>> FETCH THE ONLY FIRST_NAME FROM EMPLOYEE TABLE?

SELECT FIRST_NAME FROM EMPLOYEES;

>>> FENCH THE Lex FIRST_NAME FROM EMPLOYEE TABLE?

SELECT * FROM EMPLOYEES WHERE FIRST_NAME='Lex';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME='Bruce';
SELECT * FROM EMPLOYEES WHERE LAST_NAME='King';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME='David';
----THE NAME OF THE EMPLOYEE FIRST NAME IS CASE SENSITIVE.

>>> FENCH THE 107 EMPLOYEE ID FROM THE EMPLOYEE TABLE?

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=107;
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=50;

>>> FENCH THE HIRE DATE WHICH IS 17-JUN-87

SELECT * FROM EMPLOYEES WHERE HIRE_DATE='17-JUN-87';
SELECT * FROM EMPLOYEES WHERE HIRE_DATE='25-JUNE-97';
SELECT * FROM EMPLOYEES WHERE HIRE_DATE='5-FEB-98';
SELECT * FROM EMPLOYEES WHERE HIRE_DATE='16-AUG-94';
SELECT 3000+5000+8000 FROM DUAL;

>>>FETCH THE EMPLOYEES WHOSE SALARY IS 9000, 12000, 5000, AND 24000
SELECT * FROM EMPLOYEES WHERE SALARY=9000;
SELECT * FROM EMPLOYEES WHERE SALARY=12000;
SELECT * FROM EMPLOYEES WHERE SALARY=5000;
----NO RECORD FOUND BCZ THERE IS NO DATA AVAILABLE, WHOSE SALARY IS 5000.
SELECT * FROM EMPLOYEES WHERE SALARY=24000;

>>> FENCH THE EMPLOYEE NAME WHOSE NAME IS 'Steven King' ONLY
SELECT * FROM EMPLOYEES WHERE FIRST_NAME='Steven' AND LAST_NAME='King';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME='David' AND LAST_NAME='Lee'

*** DESCRIBE ***

----IT IS USED TO DISPLAY STRUCTURE OF SPECIFIC TABLE
----IT IS ONLY SHOWING THE STRUCTURE OF THE TABLE NOT SHOWING THE DATA.
----IF WE RUN THE DESC COMMAND THEN WE CAN SEE FOLLOWING INFORMATION FROM THAT
TABLE
----COLUMN NAME, NULL / NOT NULL VALUES /DATA TYPE ALONG WITH SIZE.

>>> WHAT IS MEAN BY DESCRIBE AND WHAT IS THE USE OF THIS OPERATOR IN SQL?
----TO SEE THE STRUCTURE OF THE TABLE AND OTHER INFORMATION.

DESC EMPLOYEES;
DESC DEPARTMENTS;
DESC LOCATIONS

*** DATATYPE ***

>>> WHAT IS MEAN BY DATATYPE AND WHY IT IS REQUIRED IN THE DATABASE?

----DATATYPE MEANS WHAT KIND OF DATA STORE IN THIS COLUMN.

----IT IS SHOWING THE TYPE OF DATA.
----LIKE NUMBER(4,0) FOR NUMERIC DATA.
----(4,0) FOUR USED FOR THE RANGE OF THE NUMBER AND ZERO USED FOR DECIMAL POINT.
----VARCHAR2(10) FOR CHARACTER DATA. AND THE 10 IT IS A LIMIT OF THE LETTER IN THAT
FORMAT.
----DATE DATATYPE : THIS DATA TYPE USED FOR ONLY DATE FUNCTION.
----DECIMAL(10,2) means you can have a decimal number with a maximal total
precision of 10 digits.
----Two (2) of them after the decimal point and eight (8) before.

....VARCHAR2(15),VARCHAR2(100)
...NUMBER(5,0), NUMBER (4,2)
 *** ARITHMATIC OPERATION ***
AS PER BODMASS RULE IT IS EXECUTING.
----DIVISION(/)
----MULTIPILICATION(*)
----ADD(+)
----SUBSTRACTION(-)

>>> WE WANT FIRST_NAME,SALARY, ADDITIONAL 500 SALARY FROM EACH EMPLOYEES

AND BONUS NAME ADD TO NEW COLUMN?
SELECT FIRST_NAME, SALARY, SALARY+500 AS BONUS FROM EMPLOYEES;

>>> WE NEED TO INCREASE THE EMPLOYEES SALARY BY 10%?

SELECT SALARY , (SALARY*10/100) FROM EMPLOYEES;
SELECT SALARY , SALARY+(SALARY*0.10) FROM EMPLOYEES;
SELECT SALARY , (SALARY*0.1) AS BONUS_AMT FROM EMPLOYEES;
SELECT SALARY, SALARY+(15/100*SALARY) AS BONUS_SALARY FROM EMPLOYEES;
SELECT SALARY, SALARY *0.09 FROM EMPLOYEES;

>>> WE NEED TO INCREASE THE EMPLOYEES SALARY BY 5%?

SELECT SALARY , SALARY*0.05 BONUS FROM EMPLOYEES;

>>> HOW MUCH MONEY REQUIRED. IF WE INCREASED EMPLOYEE SALARY BY 5%?

SELECT SUM(SALARY*0.05) AS BONUS FROM EMPLOYEES;

>>> IF WE INCREASE SALARY BY 5% THEN HOW MUCH MONEY REQUIRED. GIVE ME THE SUM OF 5%
SALARY?
SELECT SUM(SALARY*0.05) FROM EMPLOYEES;

\\ AS \\
----COLOUMN ALISES
----TO CHANGE THE HEADER OF COLUMN IN RESULT.
----TWO WAY TO CHANGE THE HEADING OF THE TABLE.
----EITHER WE USE AS OPERATOR OR ONLY PUT NAME.

>>> SYNONYM AND TABLE ALISES DIFFERENCE?

SELECT FIRST_NAME, SALARY, SALARY+1000 DIWALI_BONOUS FROM EMPLOYEES;

SELECT FIRST_NAME, SALARY, SALARY+1000 AS DIWALI_BONOUS FROM EMPLOYEES;
SELECT FIRST_NAME BONUS,SALARY, LAST_NAME DIWALI FROM
EMPLOYEES; ...DOUBT
SELECT FIRST_NAME, SALARY, SALARY +1000 FROM EMPLOYEES
----THIS HEADING COME AS A OPERATIONAL HEADING.
SELECT SALARY,COMMISSION_PCT,COMMISSION_PCT/5 AS NEW_COMMISSION FROM EMPLOYEES
SELECT FIRST_NAME,LAST_NAME,SALARY,SALARY+5000 AS DIWALI_BONOUS FROM EMPLOYEES;
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,SALARY*45/100 AS DIWALI_BONOUS FROM
EMPLOYEES;
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY,(SALARY*25/100)+SALARY AS AWARD_BONOUS FROM
EMPLOYEES;
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,(SALARY*0.25)+SALARY AS
DIWALI_BONOUS FROM EMPLOYEES;
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,(SALARY*0.45)+SALARY AS
DIWALI_BONOUS FROM EMPLOYEES;
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,SALARY*0.25 AS DIWALI_BONOUS FROM
EMPLOYEES;
SELECT FIRST_NAME||' '||LAST_NAME AS FULL_NAME FROM EMPLOYEES;
SELECT FIRST_NAME,LAST_NAME,SALARY,SALARY*2 AS DATA1 FROM EMPLOYEES;
SELECT FIRST_NAME,LAST_NAME,SALARY,(SALARY*0.1)+SALARY AS DATA1 FROM EMPLOYEES;
SELECT FIRST_NAME,LAST_NAME,SALARY,(SALARY*10/100)+SALARY AS DATA1 FROM EMPLOYEES;
SELECT SALARY,EMPLOYEE_ID,FIRST_NAME,SALARY+5000 AS DATA1 FROM EMPLOYEES
SELECT EMPLOYEE_ID EMPNO,SALARY SAL, SALARY+2 "INC SAL" FROM EMPLOYEES;

*** DOUBLE QUOTE ***

---- ( " " )
----DISPLAY THE COLOUMN HEADING AS IT IS.
----USED FOR EX...FIRST LETTER CAPITAL OR OTHER SMALL LETTER.

SELECT FIRST_NAME||' '||LAST_NAME AS "full_name" FROM EMPLOYEES

SELECT SALARY , SALARY*0.5 AS "biG_BONUS" FROM EMPLOYEES

*** NULL ***

----A NULL IS VALUE THAT IS UNAVAILABLE, UNASSIGNED, UNKNOWN, INAPPLICABLE.
----IF WE PERFORM ARITHAMATIC OPERATIOPN ON NULL VALUE IT WILL GIVES NULL AS A
OUTPUT.
----BUT IT IS NOT SAME AS A ZERO.

>>> WHAT IS NULL AND NOT NULL?

SELECT COMMISSION_PCT, COMMISSION_PCT*0.5 FROM EMPLOYEES;

\\ IS NULL \\
----IT IS USED TO SEARCH NULL VALUE

>>> HOW TO FIND NULL VALUES?

>>> DIFFERENCE BETWEEN IS NULL AND NOT NULL?

SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL;

SELECT * FROM EMPLOYEES WHERE MANAGER_ID IS NULL;

\\ IS NOT NULL \\
----IT IS USED TO SEARCH NOT NULL VALUE

SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NOT NULL;

SELECT * FROM EMPLOYEES WHERE MANAGER_ID IS NOT NULL;

*** CONCATINATION OPERATER ( || ) ***

----IT IS USED FOR MERGE COLOUMN
----IT IS USED FOR MERGE TWO OR MORE THAN TWO COLOUMN
----IT IS DENOTED AS FOR EX....(FIRST_NAME||LAST_NAME)... TWO VERTICAL LINES.

SELECT 'MY JOB NAME IS '||FIRST_NAME FROM EMPLOYEES;

SELECT FIRST_NAME||' '||LAST_NAME AS FULL_NAME FROM EMPLOYEES;
SELECT FIRST_NAME||LAST_NAME||EMAIL AS FULL_ADDRESS FROM EMPLOYEES;
SELECT FIRST_NAME||' BYE '||LAST_NAME AS FULL_NAME FROM EMPLOYEES;
SELECT EMPLOYEE_ID||' '||FIRST_NAME||' '||LAST_NAME||' '||EMAIL||'
'||PHONE_NUMBER||' '||HIRE_DATE||' '||JOB_ID||' '||SALARY
AS FULL_DETAIL FROM EMPLOYEES;
SELECT 10+20||FIRST_NAME||(30+40) AS DATA_1 FROM EMPLOYEES;
----HERE, FIRST SOLVE THE BRACKET THEN SOLVE ANOTHER QUERY THAT'S WHY IT IS RUN.
SELECT 10+20||FIRST_NAME||30+40 AS DATA_1 FROM EMPLOYEES;
----BUT IN THIS CASE, AFTER THE CHARACTER NUMBER NOT ALLOWABLE.
SELECT 10+20||FIRST_NAME||'30+40' AS DATA_1 FROM EMPLOYEES;
SELECT 10+20,FIRST_NAME,30+40 AS DATA_1 FROM EMPLOYEES; ----IT IS SUCCESSFULLY
EXECUTING.
SELECT SALARY||EMPLOYEE_ID FROM EMPLOYEES;

*** DISTINCT ***

----IT IS REMOVE THE REPEATED VALUE, AND TAKE ONLY ONE UNIQUE VALUE IN ROWS OR
COLUMN.
----NOT TAKING THE SAME VALUE AT THE END OF THE RESULT.
----REMOVE REPEATED VALUE FROM TABLE.
----IT IS ALWAYS USED AFTER SELECT WORD.
----IT IS APPLLICABLE FOR ALL TABLE.
----KEY WORD.
----IF WE APPLIED DISTINCT ON MULTIPLE COLUMN THEN IT WILL CHECK ALL VALUE SAME
----THEN IT WILL REMOVE DUPLICATION.

>>> DISTINCT KEY WORD ON MULTIPLE COLUMN?

>>> EXPLAIN INTERNAL EXECUTION OF DISTINCT KEYWORD ON MULTIPLE COLUMN?
>>> HOW TO DELETE DUPLICATE RECORD FROM THE TABLE?

SELECT DISTINCT EMPLOYEE_ID FROM EMPLOYEES;

SELECT DISTINCT FIRST_NAME FROM EMPLOYEES;
SELECT DISTINCT LAST_NAME FROM EMPLOYEES;
SELECT DISTINCT SALARY FROM EMPLOYEES;
SELECT DEPARTMENT_ID FROM EMPLOYEES;
SELECT DISTINCT DEPARTMENT_ID ,MANAGER_ID,FIRST_NAME,LAST_NAME,SALARY FROM
EMPLOYEES;
SELECT DISTINCT DEPARTMENT_ID, FIRST_NAME FROM EMPLOYEES;

>>> HOW TO GET DISTINCT RECORD WITHOUT USING DISTINCT?

SELECT FIRST_NAME FROM EMPLOYEES
GROUP BY FIRST_NAME
ORDER BY FIRST_NAME;

SELECT FIRST_NAME FROM EMPLOYEES

UNION
SELECT FIRST_NAME FROM EMPLOYEES;

SELECT FIRST_NAME FROM EMPLOYEES

INTERSECT
SELECT FIRST_NAME FROM EMPLOYEES;

*** QUOTE OPERATER ***

----q'[ IT'S MY JOB NAME AND THAT IS ]'
----IF WE WRITE AT THE STARTING THEN QUERY WILL ACCEPT THE APPOSTROPY.
----AND IF WE PUT APPOSTROPY AT THE MIDDLE THEN IT WILL NOT SHOW ERROR AT THE TIME
OF EXECUTION.

SELECT q'[ IT'S MY JOB NAME AND THAT IS ]' || JOB_ID FROM EMPLOYEES;
SELECT q'[ THIS EMPLOYEE'S USELESS ]'||FIRST_NAME FROM EMPLOYEES;

\\ SINGEL QUOTE \\
----IT IS USED TO INDICATE THE BEGINNING AND END OF A STRING IN SQL.
----NUMRIC DATA IS NOT REQUIERD SINGEL QUOTE.
----SINGEL QUOTE IS ALSO USED FOR CHARACTER SPACING.
----IT IS CASE SENSITIVE FOR EX...'Lex'

SELECT FIRST_NAME||' '||LAST_NAME AS FULL_NAME FROM EMPLOYEES;

SELECT FIRST_NAME||' BYE '||LAST_NAME AS FULL_NAME FROM EMPLOYEES;
SELECT FIRST_NAME||' 500 '||LAST_NAME AS FULL_NAME FROM EMPLOYEES;
SELECT * FROM EMPLOYEES WHERE FIRST_NAME='Lex';
SELECT * FROM EMPLOYEES WHERE HIRE_DATE='17-JUN-87';
SELECT EMPLOYEE_ID||10+20||SALARY FROM EMPLOYEES;
SELECT EMPLOYEE_ID||'10+20'||SALARY FROM EMPLOYEES;
SELECT EMPLOYEE_ID||'10'+'20'||SALARY FROM EMPLOYEES;
SELECT FIRST_NAME||10+20||SALARY FROM EMPLOYEES; ...WE CAN NOT TAKE AFTER CHARACTER
NUMERIC VALUE.

*** WHERE ***

----SYNTAX : WHERE < CONDITION >
----FILTERING THE ROWS / RECORDS / RESULTS
----CONDITION : COLUMN_NAME COMPARISON OPERATOR VALUE
----WHERE : IT IS ONE KIND OF CLAUSE
----FILTER ROWS
----WORKS ON ROWS DATA, NOT ON AGGREATED FUNCTION

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=10;

SELECT * FROM EMPLOYEES WHERE JOB_ID= 'IT_PROG';

>>> WHAT IS THE DIFFERENCE BETWEEN HAVING CLAUSE AND WHERE CLAUSE.

*** CONDITIONAL OR COMPARATIVE SYMBOL ***

----( = < > <= >= != )----

SELECT * FROM EMPLOYEES WHERE SALARY>15000

SELECT * FROM EMPLOYEES WHERE FIRST_NAME!='David'
SELECT * FROM EMPLOYEES WHERE SALARY>12000

>>> DISPLAY DETAILS OF JOBS WHERE THE SALARY IS GREATER TAHN 10000?
SELECT * FROM EMPLOYEES WHERE SALARY >10000;

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID <= 20;

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID >=20;

\\ LOGICAL OPERATOR \\
----( NOT, AND, OR )
----THIS IS FOR AND OPERATOR ( T + T = T ) ( T + F = F ) ( F + T = F ) ( F + F = F)
----THIS IS FOR OR OPERATOR ( T + T = T ) ( T + F = T ) ( F + T = T ) ( F + F = F )
----THIS IS FOR NOT OPERATOR, T = F , F = T
----QUERY EXECUTE SEQUENCE GIVEN BELOW...
----NOT
----AND
----OR

SELECT * FROM EMPLOYEES WHERE JOB_ID='AD_PRES' OR JOB_ID='IT_PROG';

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=101 OR EMPLOYEE_ID=103;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=105 OR EMPLOYEE_ID=107;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=101 AND DEPARTMENT_ID=90;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID>107 AND SALARY>5000;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID>110 AND SALARY>10000;
SELECT * FROM EMPLOYEES WHERE SALARY>=2000 AND SALARY<=5000;
SELECT * FROM EMPLOYEES WHERE SALARY>1000 AND SALARY<10000;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=101 OR EMPLOYEE_ID=107 OR
EMPLOYEE_ID=110;
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID>103 OR (EMPLOYEE_ID>120 AND
SALARY>5500);
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID>103 AND (EMPLOYEE_ID>120 OR
SALARY>5500);
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE'A%' OR (EMPLOYEE_ID>104 AND
SALARY<5500);
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID > 103 OR (FIRST_NAME='Lex' AND
SALARY> 5500);
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE'A%' AND (EMPLOYEE_ID>104 AND
SALARY<5500);

\\ RANGE OPERATOR \\
----IT IS AN RANGE OPERATOR

>>> DISPLAY ENAME AND WHO HAVE SALARY GREATER THAN 15000 AND LESS THAN 50000 (USING
2 WAYS)?

SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 10000 AND 15000;

SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 15000 AND 24000;
SELECT * FROM EMPLOYEES WHERE SALARY BETWEEN 10000 AND 20000 OR FIRST_NAME IN
('Lex','David','John');
SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '17-AUG-91' AND '17-AUG-94';
SELECT * FROM EMPLOYEES WHERE HIRE_DATE BETWEEN '17-AUG-91' AND '17-AUG-95';
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID BETWEEN 20 AND 30;

>>> DISPLAY THE FIRST NAME AND JOIN DATE OF THE EMPLOYEES WHO JOINED BETWEEN 2002
AND 2005?
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY') BETWEEN
1980 AND 1985;
SELECT FIRST_NAME,HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'MM') BETWEEN 01
AND 06;
SELECT FIRST_NAME,HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'DD') BETWEEN 01
AND 15;
SELECT FIRST_NAME,HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'MON') BETWEEN
'JAN' AND 'JUN';
SELECT FIRST_NAME,HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'MON') BETWEEN
'JAN' AND 'JUN';

>>> DISPLAY EMPLOYEES WHO JOINED AFTER 1987;

SELECT * FROM EMPLOYEES WHERE TO_CHAR (HIRE_DATE,'YYYY') > 1987
SELECT * FROM EMPLOYEES WHERE TO_CHAR (HIRE_DATE,'YYYY') BETWEEN 1987 AND 2023;

>>> DISPLAY EMPLOYEES WHO JOINED AFTER 1992 TO 2010;

SELECT * FROM EMPLOYEES WHERE
TO_CHAR(HIRE_DATE,'YYYY') > 1992 AND TO_CHAR(HIRE_DATE,'YYYY') < 2010
SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY') BETWEEN 1992 AND 2010;

\\ IN OPERATOR \\
----IT IS AN MATCHING LIST OPERATOR IT'S DISPLAY INCLUSIVE LIST DATA
----IT IS OPTIMIZING THE QUERY AND SAVE LOT OF TIME.

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IN (101,103,105)

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IN (110,111,107)
SELECT * FROM EMPLOYEES WHERE FIRST_NAME IN ('Lex','David');
SELECT * FROM EMPLOYEES WHERE SALARY IN (17000,5000,10000);
SELECT * FROM EMPLOYEES WHERE MANAGER_ID IN (140, 145);
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (90,10,50);

\\ NOT IN OPERATOR \\
----IT IS AN NON MATCHING LIST OPERATOR IT'S DISPLAY EXCLUSIVE LIST DATA

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN (104,108,109)

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN (101,103,105)

\\ LIKE OPERATOR \\
----IT IS AN A PATTERN SEARCHING OPERATOR. IT IS ALSO KNOWN AS WILD CARD OPERATOR.
----SEARCH OPERATOR OR STRING SEARCH

>>> EXPLAIN PATTERN OPERATOR?

SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE'D%';

SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE 'H%';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE'__e%';
SELECT * FROM EMPLOYEES WHERE LAST_NAME LIKE '__E%';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '___d%a__';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '___da__';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE'S%' OR FIRST_NAME LIKE 'N%';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'S%' AND LAST_NAME LIKE 'K%';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME NOT LIKE 'S%';
SELECT * FROM EMPLOYEES WHERE FIRST_NAME NOT LIKE 'H%'
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '_a%l'
SELECT * FROM EMPLOYEES WHERE (FIRST_NAME LIKE '%n' OR FIRST_NAME LIKE '%l')
AND SALARY>8000
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'D%' AND FIRST_NAME LIKE
'%d'
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE 'D%d'
SELECT FIRST_NAME FROM EMPLOYEES WHERE FIRST_NAME LIKE

*** ASCENDING ORDER ***

----SORT THE DATA IN SMALLEST TO LARGEST

*** DESC ORDER ***

----SORT THE DATA IN LARGEST TO SMALLEST
----IF THERE IS A ORDER BY NOT MENTIONED SORTING DONE BY ASCENDING ORDER BY
DEFAULT.
----AT THE TIME OF SORTING, IF DUPLICATION IN THE FIRST COLUMN THEN DATA SORT BY
NEXT COLUMN.

>>> ORDER BY CLAUSE ON MULTIPLE COLUMN?

SELECT * FROM EMPLOYEES ORDER BY EMPLOYEE_ID;...IF THERE IS NOT MENTIONED THE

ORDER. THEN IT WILL SORT IN THE ASCENDING ORDER.
SELECT * FROM EMPLOYEES ORDER BY SALARY ASC;
SELECT * FROM EMPLOYEES ORDER BY FIRST_NAME ASC;
SELECT * FROM EMPLOYEES ORDER BY EMPLOYEE_ID DESC;
SELECT * FROM EMPLOYEES ORDER BY JOB_ID DESC;
SELECT * FROM EMPLOYEES ORDER BY EMPLOYEE_ID DESC,SALARY ASC ...SORTING DONE BY
FIRST MENTIONED COLUMN.
SELECT * FROM EMPLOYEES ORDER BY SALARY ASC, EMPLOYEE_ID DESC ...SORTING DONE BY
FIRST MENTIONED COLUMN.
SELECT * FROM EMPLOYEES ORDER BY COMMISSION_PCT DESC, EMPLOYEE_ID DESC
----NULL VALUE ON TOP AND THE OTHER ARE DESC
SELECT * FROM EMPLOYEES ORDER BY COMMISSION_PCT DESC, EMPLOYEE_ID ASC
SELECT * FROM EMPLOYEES ORDER BY DEPARTMENT_ID;
SELECT SALARY,EMPLOYEE_ID,FIRST_NAME,SALARY+5000 AS DATA1 FROM EMPLOYEES
ORDER BY DATA1;
SELECT SALARY,EMPLOYEE_ID,FIRST_NAME,SALARY+5000 AS DATA1 FROM EMPLOYEES
ORDER BY DATA1 DESC;
SELECT DEPARTMENT_ID,SALARY FROM EMPLOYEES ORDER BY DEPARTMENT_ID DESC,SALARY DESC;
SELECT DEPARTMENT_ID,SALARY FROM EMPLOYEES ORDER BY DEPARTMENT_ID DESC,SALARY DESC;
SELECT DEPARTMENT_ID,SALARY FROM EMPLOYEES ORDER BY DEPARTMENT_ID DESC
SELECT DEPARTMENT_ID FROM EMPLOYEES ORDER BY DEPARTMENT_ID DESC

\\ DESCENDING ORDER \\
----SORT THE DATA IN LARGEST TO SMALLEST

SELECT * FROM EMPLOYEES ORDER BY EMPLOYEE_ID DESC

SELECT * FROM EMPLOYEES ORDER BY EMPLOYEE_ID DESC,SALARY ASC
SELECT * FROM EMPLOYEES ORDER BY DEPARTMENT_ID DESC
SELECT * FROM EMPLOYEES ORDER BY SALARY DESC

SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES E JOIN DEPARTMENTS D

ON E.DEPARTMENT_ID=D.DEPARTMENT_ID;

*** SUBSTITUTION VARIABLE ***

\\ SINGEL AMPERSAND \\ DOUBLE AMPERSAND \\
----IT IS USED TO ACCEPT VALUE AT RUNTIME OR DYNAMICALLY THAT TIME WE USE THIS
VARIABLE.
----IT IS ALSO FAST AND QUICK
----IT IS HOLD ONE VALUE AT A TIME
----ACCEPT THE VALUE FROM USER AT RUNTIME
ADVANTAGE :
1. SAVE MEMORY
2. PERFORMANCE INCERASE
3. FAST EXECUTION OF QUERY

*** SINGEL AMPERSAND ***

SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=&X1;
SELECT * FROM EMPLOYEES WHERE FIRST_NAME='&X1';
SELECT &X1,LAST_NAME FROM EMPLOYEES WHERE &X1='&X2';
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=&X1 OR
SALARY>&X1
SELECT EMPLOYEE_ID,FIRST_NAME,&X1 FROM EMPLOYEES WHERE SALARY>&X2
SELECT EMPLOYEE_ID,FIRST_NAME,&X1,&X2 FROM EMPLOYEES
WHERE EMPLOYEE_ID IN(&X3,102,103,104)
SELECT EMPLOYEE_ID,&X1+3000 AS DATA FROM EMPLOYEES;
SELECT &X1+&X2 DATA FROM EMPLOYEES;
SELECT FIRST_NAME,SALARY||&X1||'HELLO' AS DATA FROM EMPLOYEES;
SELECT * FROM EMPLOYEES WHERE SALARY>&X1 ORDER BY &X2 ....ORDER BY CLAUSE;
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '&X1'
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE '&X1'
SELECT DISTINCT &X1 FROM EMPLOYEES;

*** DOUBLE AMPERSAND ***

----IT IS USED FOR REUSEABILITY
----TO STORE A VALUE

SELECT EMPLOYEE_ID,FIRST_NAME,&&X1,&&X2 FROM EMPLOYEES ORDER BY &X1

SELECT EMPLOYEE_ID,FIRST_NAME,&&X2 FROM EMPLOYEES ORDER BY &X1
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=&&X1;

UNDEFINE X2
UNDEFINE X1
----TO BREAK OR REMOVE THE VALUES

DEFINE X1
DEFINE X2
----TO STORE THE LAST VALUE.

-------------------------------- CHAPTER 2 -------------------------------------

*** FUNCTION ***

----IT IS A POWERFULL FEATURE OF SQL.
----FUNCTION TAKE ARGUMENTS AND ALWAYS RETURN OUTPUT.

>>> WHAT IS FUNCTION IN SQL?

...IT CAN DO FOLLOWING TASK :

1. CALCULATION OF THE DATA.
2. MODIFY THE DATA AS PER REQUIREMENT FOR THE INDIVIJUAL DATA ITEMS.
3. MANUPULATE THE DATA FOR GROUP OF ROWS.
4. FORMAT THE DATES AND NUMBER AS PER REQUIREMENT.
5. CONVERT THE DATA TYPES OF DATA.

*** SUM ***

----IT IS USED FOR ADDITION OF COLUMN
----BENEFITS OF THIS SUM FUNCTION.
1. CODE OPTIMIZATION
2. ACCURACY
3. LESS TIME CONSUMING.

SELECT SUM(SALARY) FROM EMPLOYEES

SELECT SUM (EMPLOYEE_ID) FROM EMPLOYEES
SELECT SUM(DEPARTMENT_ID) FROM EMPLOYEES
SELECT SUM (MANAGER_ID) FROM EMPLOYEES;

*** TYPES OF FUNCTION ***

1. SINGLE ROW FUCTION.
----PERFORM THE TASK ON EACH ROW AND GIVES SEPERATE OUTPUT FOR THE EACH ROW.
----IT ACCEPT ONE INPUT AND PRODUCE ONE OUTPUT.
----IT EXECUTE ROW BY ROW.

2. MULTIPLE ROWS FUNCTION / GROUP BY / AGGREGATE FUNCTION

----IT WILL SEARCH MULTIPLE VALUE AND GIVES SINGEL OUTPUT FOR THE GROUP OF ROWS.
----IT ACCEPT MULTIPLE INPUT AND PRODUCE ONE OUTPUT.
----IT EXECUTE ON GROUP OF ROWS.
----SELECT LOWER (FIRST_NAME),SUM(SALARY) FROM EMPLOYEES.
----IT WILL GIVE ERROR BCZ SINGEL ROW FUNCTION AND MULTIPLE ROW FUNCTION
----CAN NOT BE DISPLAYED AT THE SAME TIME.
 *** THERE IS A FIVE TYPES OF SINGLE ROW FUNCTION.
1. CHARACTER FUNCTION
2. NUMBER FUNCTION
3. DATE FUNCTION
4. CONVERSION FUNCTION
5. GENRAL FUNCTION

*** CHARACTER FUNCTION ***

----THERE IS A TWO TYPES OF CHARACTER FUNCTION
1. CASE MANUPULATION
2. CHARACTER MANUPULATION

1. CASE MANUPULATION
A. UPPER
B. LOWER
C. INITCAP

*** UPPER ***

----IT IS USED TO CONVERT STRING INTO UPPER CASE

SELECT FIRST_NAME,LOWER(FIRST_NAME),UPPER(FIRST_NAME)FROM EMPLOYEES

SELECT FIRST_NAME, UPPER(FIRST_NAME) FROM EMPLOYEES;

*** LOWER ***

----IT IS USED TO CONVERT STRING INTO LOWER CASE
SELECT FIRST_NAME, LOWER(FIRST_NAME) NEW_NAME FROM EMPLOYEES;

*** INITCAP ***

----EACH FIRST LETTER IS CAPITAL
----IT IS USED TO CONVERT CHARACTER STRING FIRST LETTER INTO CAPITAL CASE

SELECT INITCAP (FIRST_NAME) FROM EMPLOYEES

SELECT FIRST_NAME, INITCAP(FIRST_NAME) AS NEW_NAME FROM EMPLOYEES;

\\ DUAL TABLE \\
----IT IS AN DUMMY TABLE
----IT HAS AN ONE ROW AND ONE COLUMN
----IT IS AN SYSTEM DEFINED TABLE
----IT IS USED WITH FUNCTION FOR USER DEFINED DATA
----IMAGINARY TABLE
----COLUMN DATA TYPE: VARCHAR2

>>> WHAT IS DUAL TABLE?

SELECT 30+20 FROM DUAL;

DESC DUAL;
SELECT * FROM DUAL
SELECT INITCAP ('hello hI wELCOME') FROM DUAL;
SELECT INITCAP (LOWER(UPPER('WELCOME'))) FROM DUAL;
SELECT UPPER(LOWER('HELLO')||'byE') AS DATA FROM DUAL;
SELECT LOWER('HELLO,HII') FROM DUAL;
SELECT 'ONKAR MOHITE' NEW_NAME FROM DUAL
SELECT SYSDATE FROM DUAL;
SELECT 7+7 FROM DUAL;
SELECT 'AJay',UPPER('AJAy'),LOWER('AJAY'),INITCAP('ajAY') FROM DUAL;
SELECT * FROM EMPLOYEES WHERE UPPER(FIRST_NAME)='LEX' ******** DOUBT
SELECT * FROM EMPLOYEES WHERE LOWER(FIRST_NAME)='lex'
SELECT * FROM EMPLOYEES WHERE INITCAP(FIRST_NAME)='Lex'

2. CHARACTER MANUPULATION
A. CONCAT
B. SUBSTR
C. INSTR
D. LPAD
E. RPAD
F. TRIM
G. REPLACE
H. LENGTH

>>> LIST OUT CHARACTER IN FUNCTION?

A...CONCAT FUNCTION :
>>> WHAT IS THE DIFFERENCE BETWEEN CONCAT FUNCTION AND CONCATINATION OPERATOR?
----THE DIFFERENCE BETWEEN THIS CONCAT FUNCTION AND CONCAT OPERATOR
----IT WILL MERGE ONLY TWO COLUMN AT A TIME. NOT MERGE MORE THAN TWO COLUMN.
----CONCAT FUNCTION ACCEPT MAX TWO ARGUMENTS / TWO COLUMN OR STRINGS.
----CONCAT NOT USE VERTICAL LINE FOR JOIN THE TWO COLUMN
----IT IS USES PARANTHESIS FOR JOIN TWO COLUMN OR STRINGS.
----ONE IS CONCATINATION OPERATOR AND ANOTHER IS A CONCAT FUNCTION.
----SYNTAX: CONCAT(COL_NAME1, COL_NAME2)

SELECT CONCAT (FIRST_NAME,LAST_NAME)FROM EMPLOYEES;

SELECT CONCAT (FIRST_NAME,SALARY) AS DIWALI_BONOUS FROM EMPLOYEES;
SELECT CONCAT (FIRST_NAME,SALARY) DIWALI_BONOUS FROM EMPLOYEES;
SELECT CONCAT ('HELLO','HI') FROM DUAL;
SELECT CONCAT (UPPER (FIRST_NAME),'HELLO') FROM EMPLOYEES;
SELECT CONCAT (EMPLOYEE_ID||FIRST_NAME,SALARY||LAST_NAME) AS DATA FROM EMPLOYEES;
----IF WE USE COMMA IN BETWEEN BOTH OF THE MERGE, THEN IT WILL BE MERGE AND QUERY
EXECUTE.
SELECT CONCAT('ABC','XYZ') FROM DUAL

\\ NESTED FUNCTION \\
----IT IS WORK INNER TO OUTTER
----FUNCTION INSIDE FUNCTION

SELECT INITCAP (LOWER(UPPER('WELCOME'))) FROM DUAL

SELECT UPPER(LOWER('HELLO')||'byE') AS DATA FROM DUAL
SELECT FIRST_NAME,UPPER('SALARY'||LAST_NAME) FROM EMPLOYEES
SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE UPPER (FIRST_NAME) LIKE'D%' *** DOUBT
SELECT CONCAT(CONCAT(FIRST_NAME,LAST_NAME),EMPLOYEE_ID) FROM EMPLOYEES

*** SUBSTRING ***

----IT IS SEPERATE THE SUB STRING FROM THE MAIN STRING.
----IT IS USED TO EXTRACT PART OR PORTION FROM GIVEN STRING
----IT IS ALLOW THREE INPUT
----IT IS EXTRACT NO OF CHARACTER
----THE SECOND LETTER IS STARTING OF THE ARGUMENT.
----AND THE LAST ARGUMENT COUNT THE VALUES AND GIVE THAT MUCH OUTPUT IN THE RESULT.

----IT IS ALSO USE LIKE A LIKE OPERATOR.

SELECT * FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,4)='id';
SELECT * FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,1,1)='D';
SELECT * FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,1,2)='Da';
SELECT SUBSTR('HELLO',1,3) AS DATA FROM DUAL;
SELECT SUBSTR('WELCOME',2,4) AS DATA FROM DUAL;
SELECT SUBSTR('HELLO WELCOME',8,2) FROM DUAL;
SELECT SUBSTR('WELCOME',3,3) FROM DUAL;
SELECT SUBSTR('WELCOME',-5,4) FROM DUAL;
SELECT SUBSTR('WELCOME',-4,2) FROM DUAL;
SELECT SUBSTR('WELCOME',-4 ) FROM DUAL;
SELECT SUBSTR(FIRST_NAME,1,3) AS DATA, SUBSTR (FIRST_NAME,4) FROM EMPLOYEES;
SELECT SUBSTR('HELLOWORLD',5,2) FROM DUAL
SELECT SUBSTR (FIRST_NAME,2,1) FROM EMPLOYEES
SELECT FIRST_NAME FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,1,1) =
UPPER(SUBSTR(FIRST_NAME,-1))
SELECT FIRST_NAME FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,2,1) =
UPPER(SUBSTR(FIRST_NAME,-2,1))

>>> WE WANT TO FETCH THE FIRST NAME LAST TWO LETTER IS en.
SELECT * FROM EMPLOYEES WHERE SUBSTR (FIRST_NAME,4)='en'

>>> THE CLIENT WANT MIDDLE THIRD LETTER IS E?

SELECT * FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,3,1)='e';
SELECT * FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,3,1)='e' AND
SUBSTR(LAST_NAME,3,1)='n';

>>> WE WANT AJAY SHINDE NAME WITH TWO DIFFERENT COLUMN.

SELECT SUBSTR('AJAY SHINDE',1,5) AS FIRST_NAME, SUBSTR('AJAY SHINDE',6) AS
LAST_NAME FROM DUAL

>>> FETCH A SUCH EMP WHOSE ENAME CONTAIN SECOND LETTER AS A 'i'
SELECT * FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,2,1)='i'

>>> FETCH A SUCH EMP WHOSE ENAME CONTAIN THE LAST LETTER AS A 'l'
SELECT * FROM EMPLOYEES WHERE SUBSTR (FIRST_NAME,-1,1)='l'
SELECT * FROM EMPLOYEES WHERE SUBSTR(FIRST_NAME,-1)='d';

>>> FETCH A FIRST THREE LETTER FROM FIRST NAME AND MAKE IT CAPITAL?
AND THE LENGTH OF FIRST_NAME SHOULD BE MORE THAN 4?
SELECT UPPER(SUBSTR(FIRST_NAME,1,3)) FROM EMPLOYEES WHERE LENGTH (FIRST_NAME)>4
SELECT UPPER(SUBSTR(FIRST_NAME,1,3)) FROM EMPLOYEES;

\\ LENGTH FUNCTION \\
----IT IS USE FOR MEASURE THE CHARACTER
----SPACE IS ALSO MEASURE AS A CHARACTER

SELECT LENGTH ('HELLO HI') FROM DUAL

SELECT LENGTH ('FIRST_NAME') FROM DUAL
SELECT LENGTH (CONCAT(FIRST_NAME,LAST_NAME)) AS DATA FROM EMPLOYEES
SELECT FIRST_NAME FROM EMPLOYEES WHERE LENGTH (FIRST_NAME)>6

>>> FETCH THE EMPLOYEE WHOSE LAST NAME LENGTH IS LESS THAN FOUR AND WE WANT THERE
LAST TWO WORD.
SELECT UPPER(SUBSTR(LAST_NAME,2,2)) FROM EMPLOYEES WHERE LENGTH(LAST_NAME)<4;
SELECT LAST_NAME,SUBSTR(LAST_NAME,2,2) FROM EMPLOYEES WHERE LENGTH(LAST_NAME)<4;
SELECT UPPER(FIRST_NAME),LOWER(EMAIL) FROM EMPLOYEES
WHERE LENGTH(FIRST_NAME)=LENGTH(EMAIL)
SELECT ROUND(SYSDATE-'01-01-2011') FROM DUAL;

\\ INSTRING \\
----IT IS USED TO DISPLAY THE POSITION OF SPECIFIC CHARACTER
----GIVES THE POSITION OF THE CHARACTER OR SUBSTRING FROM THE MAIN STRING OR INPUT.
----THIS IS CASE SENSITIVE BCZ WE PUT IN SINGEL QUOTE
----GIVES THE POSITION OF THE CHARACTER OR SUBSTRING FROM THE MAIN STRING OR INPUT.
----SYNTAX : INSTR(STRING/COL_NAME, CHARACTER OR SUBSTRING, START_POSITION,
OCCURANCE)
----IF CHARACTER OR SUBSTRING -OCCURANCE MULTIPLIES

SELECT INSTR('EMPLOYEES','E',1,3) FROM DUAL;

SELECT INSTR('HELLO','E') FROM DUAL
SELECT INSTR('HELLO','L') FROM DUAL;
SELECT INSTR('HELLO','L',1,2) FROM DUAL;
SELECT INSTR('HELLO','L',3,2) FROM DUAL;
SELECT INSTR('HEL_LO','_') FROM DUAL
SELECT INSTR('ajay','a') FROM DUAL
SELECT INSTR('ajay','a',2,1) FROM DUAL;
----THE THIRD VALUE IS FOR START POSITION AND THE FOURTH VALUE IS GIVE THE ANOTHER
VALUE THAT IS
----PRESENT IN THE SINGEL QUOTE.
SELECT INSTR('ajay','a',1,3) FROM DUAL;
SELECT INSTR('ajayj','a',1,2) FROM DUAL;
SELECT INSTR('ajayj','j',1,2) FROM DUAL;
SELECT INSTR('ajay','a',1,1) FROM DUAL;
SELECT INSTR('ajayaj','aj',1,2) FROM DUAL;

>>>WE WANT ALL THE FIRST NAME FROM EMPLOYEE TABLE WHOSE NAME HAVE 'D'.
SO PLS DISPLAY THE POSITION OF THAT CHARACTER?
SELECT FIRST_NAME,INSTR(FIRST_NAME,'D',1,1) FROM EMPLOYEES;
SELECT FIRST_NAME,INSTR(FIRST_NAME,'d',1,1) AS DATA1 FROM EMPLOYEES;

\\ LPAD \\
----IT IS USED TO GIVE PADDING TO THE STRING BY LEFT SIDE
----If you have a product ID 'ABC123', and you want it to be 10 characters long
----LPAD('ABC123', 10, '0') will return '0000ABC123'.
----This ensures that all product IDs in the result set are 10 characters long,
----padded with zeros on the left if needed.
----Real-world scenarios might involve formatting IDs, codes, or any data
----where consistent length or formatting is essential, especially for display or
reporting purposes.

SELECT LPAD('HELLO',10,'#')FROM DUAL

SELECT LPAD('HELLO',15,'*')FROM DUAL
SELECT LPAD('ONKAR',20,'&')FROM DUAL
SELECT LPAD('I AM A KING',15,'#') FROM DUAL
----IT IS USED FIRST CHARACTER THEN USED THE ADDITIONAL VALUE THAT IS WE ARE
GIVING.

>>> EXPLAIN LPAD AND RPAD?

\\ RPAD \\
----IT IS USED TO GIVE PADDING TO THE STRING BY RIGHT SIDE

SELECT RPAD ('HELLO',10,'#') FROM DUAL

SELECT RPAD ('ONKAR',20,'$') FROM DUAL
SELECT SALARY ,RPAD (SALARY,7,'*') FROM EMPLOYEES;
SELECT SALARY, RPAD (SALARY,7,0) FROM EMPLOYEES

SELECT RPAD(EMPLOYEE_ID,LENGTH(EMPLOYEE_ID)+SUBSTR(EMPLOYEE_ID,-2),'*') FROM

EMPLOYEES;

\\ REPLACE \\
----IT IS USED TO REPLACE STRING X BY Y
----REPLACE THE GIVEN STRING / CHARACTER WITH OLD CHARACTER OR STRING.
----REPLACE(OLD_VALUE,NEW_VLUE)
----WHEN WE WANT TO CHANGE THE TYPICAL LETTER OR SOME LETTER FROM DATABASE
----THAT TIME WE USE THIS REPLACE.

SELECT 'JACK AND JUE', REPLACE ('JACK AND JUE','J','') FROM DUAL;
----OUTPUT WE GET BUT BY DEFAULT IT TAKE BLANK VALUE FOR REPLACE
SELECT 'JACK AND JUE', REPLACE ('JACK AND JUE','J','BL') FROM DUAL;
SELECT REPLACE('JACK AND JUI','J','WE') FROM DUAL;
SELECT REPLACE ('HELLO','L','T') FROM DUAL;
SELECT FIRST_NAME,REPLACE (FIRST_NAME,'avi','JAY') FROM EMPLOYEES;
SELECT REPLACE (FIRST_NAME,'A','a') FROM EMPLOYEES;
SELECT REPLACE (7777,7,8) FROM DUAL;

\\ TRIM \\
----IT IS USED TO REMOVE LEADIGNG (STARTING) AND TRAILING (ENDING) ZERO OR SPACE
FROM GIVEN STRING

SELECT TRIM (000005) FROM DUAL;

SELECT TRIM (000005000) FROM DUAL;
SELECT TRIM (500000)FROM DUAL;
SELECT TRIM (' HELLO ') FROM DUAL; ...IT WILL TRIM FROM LEFT SIDE TO WRITE
SIDE. IF ANY LETTER OR NUMBER FOUND IN BETWEEN OF THEM THEN IT WILL STOP TRIMING.
SELECT TRIM ('000HELLO123') FROM DUAL; ...IF WE PUT IN THE SINGEL QUOTE THEN ZERO
IS ALSO VALUE OR CHARACTER THAT ISS WHY IT IS NOT TRIM.
SELECT TRIM ('IL' FROM 'PATIL')FROM DUAL; ...TRIM SET SHOULD HAVE ONLY ONE
CHARACTER.
SELECT TRIM ('O' FROM 'ONKAR') FROM DUAL;
SELECT TRIM ('H' FROM 'HELLO')FROM DUAL;

*** NUMBER FUNCTION ***

----ROUND
----TRUNC
----MOD

*** ROUND ***

----IF THE DECIMAL VALUE IS GREATER THAN OR EQUAL TO FIFTY OR FIVE
----THEN IT WILL ADD ONE IN THE ACTUAL VALUE.
----SYNTAX : ROUND (VALUE/COL_NAME, DECIMAL)

SELECT ROUND (4350.00,-4) FROM DUAL;

SELECT ROUND (6350.00,-4) FROM DUAL;
SELECT ROUND (5350.00,-4) FROM DUAL;
SELECT ROUND (55350.00,-4) FROM DUAL;
SELECT ROUND (1243.50,+1) FROM DUAL;
SELECT ROUND (1243.55,+1) FROM DUAL;
SELECT ROUND (1243.99,+1) FROM DUAL;
SELECT ROUND (1243.55,+2) FROM DUAL;
SELECT ROUND (1243.0,-1) FROM DUAL;
SELECT ROUND (1246.0,-1) FROM DUAL;
SELECT ROUND (1250.00,-2) FROM DUAL;
SELECT ROUND (0.5) FROM DUAL;
SELECT ROUND (0.4) FROM DUAL;
SELECT ROUND (0.5,1) FROM DUAL;
SELECT ROUND (0.6,1) FROM DUAL;
SELECT ROUND (0.6,-1) FROM DUAL;
SELECT ROUND (70.1,-1) FROM DUAL;
SELECT ROUND (704.1,-1) FROM DUAL;
SELECT ROUND (705.1,-1) FROM DUAL;
SELECT ROUND (28.7634,+2) FROM DUAL;
SELECT ROUND (28.7654,+2) FROM DUAL;
SELECT ROUND (28.49,0) FROM DUAL;
SELECT ROUND (65.49,-2) FROM DUAL;
SELECT ROUND (28.50,0) FROM DUAL;
SELECT ROUND (25.49,-2) FROM DUAL;
SELECT ROUND (125.49,-2) FROM DUAL;
SELECT ROUND (165.49,-2) FROM DUAL;
SELECT ROUND (165.49,1) FROM DUAL;
----THE NEXT VALUE IS NOT GREATER THAN FIVE THAT'S' WHY IT WILL NOT TAKE ONE IN
GIVEN VALUE.
SELECT ROUND (85.49,-2) FROM DUAL; ...IF THE NEXT NO IS GREATER THAN THE FIVE THEN
IT WILL ADD ONE IN GIVEN VALUE
SELECT ROUND (789.26,-2 ) FROM DUAL;
SELECT ROUND (589.26,-2 ) FROM DUAL;
SELECT ROUND (3214.24,-3 ) FROM DUAL;
SELECT ROUND (3614.24,-3 ) FROM DUAL;
SELECT ROUND (98.24287,3) FROM DUAL;
SELECT ROUND (785978.24,-5) FROM DUAL;
SELECT ROUND (785978.24,-4) FROM DUAL;
SELECT ROUND (50.50,-2 ) FROM DUAL;
SELECT ROUND (111.99,1) FROM DUAL
SELECT ROUND (28.74,1) FROM DUAL
SELECT ROUND (28.78,1) FROM DUAL
SELECT ROUND (28.56,0) FROM DUAL
SELECT ROUND (28.46,0) FROM DUAL
SELECT 45.926, ROUND (54.924,-1), ROUND (45.924,2) FROM DUAL;

\\ TRUNC \\
----IT IS USED TO TRUNC (DISPLAY) VALUE TILL THE POSITION DOES NOT CHECK THE VALUE
----SYNTAX : TRUNC (COL_NAME/ VALUE,DECIMAL)

SELECT TRUNC (26.78) FROM DUAL

SELECT TRUNC (999.25,-2) FROM DUAL ....IF YOU ADD MINUS SIGN THEN IT WILL TRUNC THE
VALUE IN THE LEFT HAND SIDE DIRECTION (NEGATIVE DIRECTION)
SELECT TRUNC (67827.25,-2) FROM DUAL;
SELECT TRUNC (67827.25,-4) FROM DUAL;
SELECT TRUNC (67.25,-2) FROM DUAL;
SELECT TRUNC (4365.2569,2) FROM DUAL ....IF WE ADD +2 VALUE IN THE QUERY THEN IT
WILL TRUNC THE DECIMAL POINT VALUE FROM RIGHT SIDE.
SELECT TRUNC (15.24,-4) FROM DUAL;
SELECT SALARY,TRUNC (SALARY,-3) FROM EMPLOYEES
SELECT SALARY, TRUNC (SALARY,-4) FROM EMPLOYEES
SELECT TRUNC (26.78,-1) FROM DUAL;
 \\ MOD \\
----IT IS USED TO RETURN REMAINDER OF VALUES
----IT WILL SHOWING THE REMAINING VALUE AT THE BOTTOM OF THE DIVISION
----SYNTAX : MOD(VALUE_1 / VALUE_2)=REMAINDER
----USES TO FIND THE ODD AND EVEN NUMBER.

SELECT MOD (17,3) FROM DUAL

SELECT MOD (22,3)FROM DUAL
SELECT MOD (25,4) FROM DUAL
SELECT MOD (35,6)FROM DUAL
SELECT MOD (4,2) FROM DUAL
SELECT MOD (10,4) FROM DUAL
SELECT MOD (54,5) FROM DUAL
SELECT MOD (20,3) FROM DUAL;
SELECT MOD (20,15) FROM DUAL;

>>> DISPLAY THE DETAIL OF THOSE EMPLOYEE WHOSE EMPLOYEE ID IS EVEN ?

SELECT EMPLOYEE_ID,FIRST_NAME FROM EMPLOYEES WHERE MOD (EMPLOYEE_ID,2)=0;

>>> DISPLAY THE DETAIL OF THOSE EMPLOYEE WHOSE EMPLOYEE ID IS ODD ?

SELECT EMPLOYEE_ID,FIRST_NAME FROM EMPLOYEES WHERE MOD (EMPLOYEE_ID,2)!=0;

*** DATE FUNCTION ***

A. SYSDATE
B. NEXT_DAY
C. LAST_DAY
D. ADD_MONTHS
E. MONTHS_BETWEEN
F. ROUND DATE BY MONTH
G. ROUND DATE BY YEAR
H. TRUNC DATE BY MONTH
I. TRUNC DATE BY YEAR

\\ SYSDATE \\
----IT IS AN DATE FUNCTION. IT IS USED TO RETURN CURRENT DATE
----DATE IS AN SENSITIVE FORMAT AND IT IS A STANDARD FORMAT: DD-MM-YY

ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YY HH:MI:SS';

SELECT SYSDATE ,CURRENT_TIMESTAMP FROM DUAL;
SELECT SYSDATE FROM DUAL

>>> DISPLAY THE SAME DATE AS TODAYS DATA BUT FROM LAST YEAR USING DATA FUNCTION?
SELECT (SYSDATE-365) FROM DUAL

----AIRTHMATIC OPERATION ON DATE VALUE WE CAN PERFORM (+ , - ) OPERATION.

----WE CAN'T PERFORM ( \ , * ) OPERATION

SELECT SYSDATE FROM DUAL;

SELECT SYSDATE +2 FROM DUAL
SELECT SYSDATE -2 FROM DUAL
SELECT SYSDATE /2 FROM DUAL....ERROR
SELECT SYSDATE *2 FROM DUAL....ERROR
SELECT SYSDATE +10 FROM DUAL
SELECT SYSDATE +365 FROM DUAL
SELECT HIRE_DATE, HIRE_DATE +5 FROM EMPLOYEES

----ALTER SESSION SET NLS_DATE='DD-MON-YY HH24: MI : SS';

 \\ ADD MONTHS \\
----IT IS USSED TO ADD SPECIFIC MONTHS IN GIVEN DATE

SELECT ADD_MONTHS (SYSDATE,2 ) FROM DUAL

SELECT ADD_MONTHS (SYSDATE,4 ) FROM DUAL
SELECT ADD_MONTHS (SYSDATE,15) FROM DUAL

\\ MONTHS_BETWEEN \\
----IT IS USED TO CALCULATE NO OF MONTHS BETWEEN TWO DATES

SELECT MONTHS_BETWEEN(SYSDATE,HIRE_DATE) FROM EMPLOYEES;

SELECT MONTHS_BETWEEN(SYSDATE,'22-JUN-2001') FROM DUAL;
SELECT MONTHS_BETWEEN(SYSDATE,'22-JUN-2001')/12 FROM DUAL;
SELECT MONTHS_BETWEEN (SYSDATE,'01-JAN-2011')/12 FROM DUAL;
SELECT (MONTHS_BETWEEN (SYSDATE,HIRE_DATE)/12) >30 FROM EMPLOYEES
WHERE TO_CHAR(SYSDATE-HIRE_DATE) > 30
SELECT (((SYSDATE-HIRE_DATE)/30)/12) AS NEW_DATE FROM EMPLOYEES

\\ LAST_DAY \\
----IT IS USED TO FENCH THE LAST DAY OF MONTH

SELECT HIRE_DATE, LAST_DAY(HIRE_DATE ) FROM EMPLOYEES;

SELECT LAST_DAY (SYSDATE)FROM DUAL

\\ NEXT DAY \\
----IT IS USED FOR DISPLAY THE NEXT DAY (DATE) FROM SPECIFICATION DATE
----IT TAKES TWO ARGUMENT IN THE QUERY.
----SYNTAX: NEXT_DAY(DATE,'SUNDAY')

SELECT NEXT_DAY (SYSDATE,'SUNDAY') FROM DUAL

SELECT HIRE_DATE,NEXT_DAY(HIRE_DATE,'SUNDAY')FROM EMPLOYEES;
SELECT NEXT_DAY (SYSDATE,'MONDAY') FROM EMPLOYEES
SELECT HIRE_DATE,NEXT_DAY(HIRE_DATE,'MONDAY')FROM EMPLOYEES;

\\ ROUND DATE BY MONTH \\

----IT IS CHECK FOR NO OF DAYS IF DAYS > 15 THEN MONTH INCREASE BY 1 ELES DISPLAY
FROM START OF THE MONTH ONLY
----SYNTAX: ROUND(DATE,'MONTH')

SELECT ROUND (SYSDATE,'MONTH') FROM DUAL

SELECT HIRE_DATE,ROUND (HIRE_DATE,'MONTH') FROM EMPLOYEES;

\\ ROUND DATE BY YEAR \\

----IT IS CHECK FOR NO OF MONTHS IF MONTHS >6 THEN YEAR INCREASE BY 1 ELES DISPLAY
FROM START THAT YEAR ONLY
----SYNTAX: ROUND (DATE, 'YEAR')

SELECT ROUND (SYSDATE ,'YEAR')FROM DUAL

SELECT HIRE_DATE,ROUND(HIRE_DATE,'YEAR')FROM EMPLOYEES

\\ TRUNC DATE BY MONTH \\

----START FROM THE MONTH ONLY
----SYNTAX: TRUNC (DATE, 'MONTH')
SELECT TRUNC (SYSDATE,'MONTH') FROM DUAL
SELECT FIRST_NAME,HIRE_DATE, TRUNC (HIRE_DATE,'MONTH')FROM EMPLOYEES;

>>> WE WANT ALL THE EMPLOYEE WHO JOINED OR HIRED IN THE 1994 YEAR?
SELECT FIRST_NAME,HIRE_DATE FROM EMPLOYEES WHERE TRUNC (HIRE_DATE,'YEAR')='1-JAN-
94'
SELECT FIRST_NAME,HIRE_DATE FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')=1994;

>>> WE WANT ALL THE EMPLOYEE WHO JOINED OR HIRED IN THE 1987 YEAR?
SELECT HIRE_DATE, FIRST_NAME FROM EMPLOYEES WHERE TRUNC (HIRE_DATE,'YEAR')='1-JAN-
87';

\\ TRUNC DATE BY YEAR \\

----START FROM THE YEAR ONLY
----SYNTAX: TRUNC (DATE,'YEAR') FROM DUAL

SELECT TRUNC (SYSDATE,'YEAR')FROM DUAL

SELECT TRUNC (HIRE_DATE,'YEAR') FROM EMPLOYEES;

\\ CONVERSION FUNCTION (TYPE CASTING) \\

----TO_CHAR(VALUE,FORMAT)
----TO_NUMBER
----TO_DATA
----CONVERT ONE DATA TYPE INTO ANOTHER DATA TYPE AS KNOWN AS CASTING.
----DATE IS MUST BE IN SINGEL QUOTE & IT IS FORMAT SENSITIVE.

YYYY = FULL YEAR IN NUMBER

YEAR = FULL YEAR NAME IN CHARACTER
MONTH = FULL MONTH NAME IN CHARACTER
MON = FIRST THREE LETTER OF THE MONTH IN CHARACTER
MM = FULL MONTH IN NUMBER
DAY = FULL DAY IN CHARACTER (LIKE SUNDAY, MONDAY)
DY = THREE LETTER OF THE DAY IN CHARACTER (LIKE SUN, MON)
DD = ALL DAY IN NUMBER.

1. DATE TO CHARACTER CONVERSION

2. NUMBER TO CHARACTER FUNCTION

SELECT TO_CHAR(SYSDATE,'MONTH-YEAR') FROM DUAL

SELECT SYSDATE, TO_CHAR(SYSDATE,'DD') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DAY-MON-YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DAY-MONTH-YEAR') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DY-MONTH-YYYY') FROM DUAL;
SELECT TO_CHAR(SYSDATE,'DD-MON-YEAR') FROM DUAL;

>>> DISPLAY DETAIL OF THOSE EMPLOYEE WHOSE HIRE DATE IN MONTH OF NOVEMBER.
SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'MON') ='NOV';
SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'MM') ='09';

>>> DISPLAY DETAIL OF THOSE EMPLOYEE WHOSE HIRE DAY IS MONDAY.

SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'DAY')='MONDAY';
SELECT * FROM EMPLOYEES WHERE TO_NUMBER(HIRE_DATE,'DAY')='MONDAY';

>>> WE CAN PUT DOUBLE QUOTE FOR PRINT SAME NAME AS IT IN THE GIVEN DATE?
SELECT TO_CHAR (SYSDATE,'DD-"MY JOINING MONTH IS"-MON-YYYY') FROM DUAL;
>>> 'DDSPTH' USED FOR TO PRINT THE DAY OF EMP IN CHARACTER.
----WHEN WE PUT DDSPTH THEN DAY IS PRINT IN CHARACTER LETTER.
SELECT HIRE_DATE ,TO_CHAR(HIRE_DATE,'DDSPTH') AS DATE_OF_EMP FROM EMPLOYEES;

>>> DISPLAY DAY OF EMPLOYEES IN CHARACTER?

SELECT FIRST_NAME,SALARY, TO_CHAR(HIRE_DATE,'DAY') FROM EMPLOYEES;
SELECT FIRST_NAME,SALARY,HIRE_DATE FROM EMPLOYEES WHERE
TO_CHAR(HIRE_DATE,'DY')='SUN';

*** TO_DATE ***

----IT IS CONVERTING THE ANY CHARACTER DATE INTO THE DATE FORMAT

CREATE TABLE DATE1 (DATE_OF_COL DATE,

COL_NUMBER NUMBER(10),COL_STRING VARCHAR2(100));
SELECT * FROM DATE1;
SELECT TO_DATE('1-JUN-87')+2 FROM DUAL;
INSERT INTO DATE1(DATE_OF_COL) VALUES ('22-JUN-2001');
INSERT INTO DATE1(DATE_OF_COL) VALUES (TO_DATE('JUN-22-2002','MON-DD-YYYY'));
INSERT INTO DATE1(DATE_OF_COL) VALUES (TO_DATE('2003-06-11','YYYY-MM-DD'));
INSERT INTO DATE1(DATE_OF_COL) VALUES (TO_DATE('2001-JUNE-22','YYYY-MONTH-DD'));
INSERT INTO DATE1(COL_NUMBER) VALUES (143568.23);
INSERT INTO DATE1(COL_NUMBER) VALUES (TO_NUMBER(452385.23,'L9,99,999.00'));
INSERT INTO DATE1(COL_NUMBER) VALUES (TO_NUMBER(325.23,'L9,99.00')); ...DOUBT

\\ NUMBER > CHAR \\

----( 9 ) REPRESENT A NUMBER
----( 0 ) FORCES A ZERO TO BE DISPLAYED
----( $ ) PLACES A FLOATING DOLLER SIGN
----( L ) USES THE FLOATING LOCAL CURRENCY SYMBOL
----( . ) PRINT A DECIMAL POINT
----( , ) PRINT A COMMA AS THOUSAND INDICATOR

SELECT TO_CHAR (24000,'$99,999.00') FROM DUAL;

SELECT TO_CHAR(24000,'L99,999.00') FROM DUAL;
SELECT FIRST_NAME, SALARY, TO_CHAR(SALARY,'L9,99,999.00') FROM EMPLOYEES;
SELECT TO_CHAR (4578,'L9,999.00000') FROM DUAL
SELECT FIRST_NAME,SALARY, TO_CHAR (SALARY,'L9,99,999.00000') FROM EMPLOYEES

ALTER SESSION SET NLS_CURRENCY ='Rs.';

----IT WILL USE FOR THE CHANGE THE CURRENCY SYMBOL IN QUERY.
SELECT 5678923, TO_CHAR (5678923,'L99,99,999.00') FROM DUAL;

\\ CHAR > NUMBER \\

SELECT TO_NUMBER('$10','L99') FROM DUAL

*** GENRAL FUNCTION ***

----IT IS WORK ON NULL VALUE.
----1) NVL()
----2) NVL2()
----3) NULLIF()
----4) COALESCE ()

*** NVL() ***

----IT WILL CONVERT NULL VALUE INTO ACTUAL VALUE
----IT WILL REPLACE THE NULL VALUE INTO GIVEN VALUE
----WE CAN NOT CONVERT NUMBER VALUE COLUMN WITH CHARACTER DATA INPUT.

SELECT FIRST_NAME,COMMISSION_PCT,HIRE_DATE,NVL(COMMISSION_PCT,0)AS DATA1,

NVL(LAST_NAME,'NO MIDDLE NAME')
AS DATA2, NVL(HIRE_DATE,SYSDATE)AS DATA3 FROM EMPLOYEES;
SELECT COMMISSION_PCT, NVL (COMMISSION_PCT,0) FROM EMPLOYEES
SELECT COMMISSION_PCT, NVL (COMMISSION_PCT,0)+2000 FROM EMPLOYEES
SELECT FIRST_NAME,COMMISSION_PCT,NVL( COMMISSION_PCT,0) AS DATA1 FROM EMPLOYEES
SELECT FIRST_NAME,COMMISSION_PCT,NVL( COMMISSION_PCT,1) AS DATA1 FROM EMPLOYEES
SELECT MANAGER_ID , NVL(MANAGER_ID, 2) AS DATA1 FROM EMPLOYEES
SELECT MANAGER_ID , NVL(MANAGER_ID, 0) AS DATA1 FROM EMPLOYEES
SELECT MANAGER_ID , NVL(FIRST_NAME,'NOT HAVING MGR') AS DATA1 FROM EMPLOYEES
SELECT NVL(COMMISSION_PCT,0) AS DATA1, NVL(MANAGER_ID,1) AS DATA2 FROM EMPLOYEES
SELECT LAST_NAME, NVL(LAST_NAME,'NO MIDDLE NAME') AS DATA1 FROM EMPLOYEES
SELECT NVL(COMMISSION_PCT,0)+5000 AS DATA1 FROM EMPLOYEES

*** NVL2() ***

----IT DIFFERENTIATE EXPRESSION 1ST FOR VALUE 2ND EXPRESSION FOR NULL
----IT WILL DIFFERTIATE THE NULL VALUE AND NON NULL VALUE.
----THE 1ST VALUE GIVE OUT PUT WHEN IT IS NOT NULL
----IF VALUE NON NULL USE THE SECOND ARGUMENT AND
----IF VALUE IS NULL THEN IT WILL SHOW THE THIRD ARGUMENT.
----HERE WE USE THREE ARGUMENT ....NVL2(COMMISSION_PCT,'ONKAR','OM')

>>> WE WANT TO ADD SALARY + COMMISSION_PCT. AND ALSO WE WANT OUTPUT FROM ALL THE
ROWS AND COLUMN WITHOUT NULL VALUE.
SELECT EMPLOYEE_ID , SALARY , COMMISSION_PCT,
NVL2(COMMISSION_PCT,SALARY+COMMISSION_PCT,SALARY) FROM EMPLOYEES

SELECT FIRST_NAME, COMMISSION_PCT,NVL2(COMMISSION_PCT,'I AM EXPERIENCE','I AM

FRESHER')
AS DATA1 FROM EMPLOYEES;
SELECT FIRST_NAME, COMMISSION_PCT,NVL2(COMMISSION_PCT,'I AM RICH','I AM POOR')
AS DATA1 FROM EMPLOYEES
SELECT FIRST_NAME, COMMISSION_PCT,
NVL2(COMMISSION_PCT,EMPLOYEE_ID,SALARY)AS DATA1 FROM EMPLOYEES;
SELECT COMMISSION_PCT, NVL2(COMMISSION_PCT,'ONKAR', 'ADITYA') FROM EMPLOYEES;

SELECT name, NVL2(email, 'Email Available', 'No Email Provided') AS email_status

FROM employees;
----FOR REPORT GENRATION PURPOSE WE NEED THIS FUNCTION

\\ NULLIF() \\
----IF BOTH THE VALUES ARE SAME THEN IT WILL RETRUN NULL OTHERWISE IT WILL RETURN
1ST EXPRESSION
----(IT IS COMPARE TWO VALUES)
----COMPARE TWO VALUES (ARGUMENTS)
----IF BOTH VALUES SAME THEN RETURN NULL OTHERWISE IT WILL RETURN FIRST VALUE.

SELECT FIRST_NAME , LAST_NAME, NULLIF (LENGTH (FIRST_NAME), LENGTH(LAST_NAME))

AS DATA1 FROM EMPLOYEES;
SELECT FIRST_NAME, LAST_NAME,
LENGTH(FIRST_NAME), LENGTH (LAST_NAME),
NULLIF(LENGTH(FIRST_NAME),LENGTH(LAST_NAME)) AS
DATA1 FROM EMPLOYEES
SELECT FIRST_NAME , LAST_NAME, NULLIF (LENGTH (FIRST_NAME),LENGTH(LAST_NAME)) AS
DATA1 FROM EMPLOYEES

*** COALESCE ***

----IT DIFFERENTE EXPRESSION. 1sT EXPRESSION FOR VALUE, AND 2nd EXPRESSION FOR
NULL.
----IT OVERCOME THE DRAWBACK OF NVL FUNCTION (MULTIPLE COLOUMN PARAMETER CAN ADD)
----COALESCE(ARGL1,ARGL2,ARGL3,ARGL4,ARGL5...AND MANY MORE ARGUMENTS)
----IT WILL ACCEPT MULTIPLE ARGUMENTS. AND REQUIRED MIN TWO ARGUMENTS.
----FIRST NON NULL VALUE AMONG ALL ARGUMENTS
----IT WILL GIVE NON NULL VALUE.

SELECT FIRST_NAME ,EMPLOYEE_ID, COMMISSION_PCT ,COALESCE

(EMPLOYEE_ID ,EMPLOYEE_ID,200)
AS DATA1 FROM EMPLOYEES;
SELECT COMMISSION_PCT ,COALESCE (COMMISSION_PCT,EMPLOYEE_ID ,200)
AS DATA1 FROM EMPLOYEES;
SELECT FIRST_NAME ,EMPLOYEE_ID, COMMISSION_PCT ,COALESCE
(COMMISSION_PCT,200,EMPLOYEE_ID)
AS DATA1 FROM EMPLOYEES;
SELECT COMMISSION_PCT,MANAGER_ID,COALESCE (COMMISSION_PCT,MANAGER_ID,500) FROM
EMPLOYEES
SELECT COMMISSION_PCT,SALARY, COALESCE ( COMMISSION_PCT,SALARY,100) FROM EMPLOYEES
SELECT COMMISSION_PCT, MANAGER_ID, COALESCE (MANAGER_ID, COMMISSION_PCT,5000) FROM
EMPLOYEES;
SELECT COMMISSION_PCT, MANAGER_ID, COALESCE (MANAGER_ID, COMMISSION_PCT) FROM
EMPLOYEES;
SELECT COMMISSION_PCT,MANAGER_ID,COALESCE (COMMISSION_PCT,MANAGER_ID,500) FROM
EMPLOYEES

*** MULTIPLE ROWS FUNCTION / GROUP BY / AGGREGATE FUNCTION ***

----IT WILL SEARCH MULTIPLE VALUE AND GIVES SINGEL OUTPUT FOR THE GROUP OF ROWS.
----TO PERFORM CALCULATION ON MULTIPLE ROWS OF A SINGLE COLUMN.
----IT ALWAYS RETURN SINGLE VALUE.

>>> WHY MULTIPLE ROW FUNCTION?

1. AVG
2. SUM
3. COUNT
4. MAX
5. MIN

SELECT AVG (SALARY) FROM EMPLOYEES;

SELECT COUNT(SALARY) FROM EMPLOYEES;
SELECT MAX (SALARY ) FROM EMPLOYEES;
SELECT MIN (SALARY) FROM EMPLOYEES;
SELECT SUM (SALARY) FROM EMPLOYEES;
SELECT MAX (SALARY ), MIN (SALARY), SUM (SALARY),AVG (SALARY) , COUNT(SALARY) FROM
EMPLOYEES;
SELECT DEPARTMENT_ID,LAST_NAME,SUM(SALARY) FROM EMPLOYEES
SELECT COUNT(NVL(COMMISSION_PCT,0)) FROM EMPLOYEES;
SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES; ...IT WILL CALCULATE ONLY VALUE NOT
CALCULATING THE NULL.

----MULTIPLE USE OF COUNT.

SELECT COUNT(*) FROM EMPLOYEES;
SELECT COUNT(1) FROM EMPLOYEES;
SELECT COUNT(SALARY) FROM EMPLOYEES;
SELECT COUNT(COMMISSION_PCT) FROM EMPLOYEES;
SELECT COUNT(MANAGER_ID) FROM EMPLOYEES;
SELECT COUNT(DISTINCT(MANAGER_ID)) FROM EMPLOYEES;
SELECT COUNT(DISTINCT SALARY ) FROM EMPLOYEES;
SELECT COUNT(DISTINCT (SALARY )) FROM EMPLOYEES;
SELECT COUNT (DISTINCT DEPARTMENT_ID) FROM EMPLOYEES;
SELECT COUNT (DEPARTMENT_ID) FROM EMPLOYEES;

>>> FIND NUMBER OF EMPLOYEE WHOSE SALARY IS LESS THAN 15000?

SELECT COUNT(*) FROM EMPLOYEES WHERE SALARY > 15000;

*** GROUP BY ***

----TO CREATE GROUPS BASED ON ANY COLUMNS.
----NUMBER OF GROUP IS EQUAL TO DISTINT VALUES OF THAT COLUMN.
----IT WILL SHOW ONLY DISTICT VALUE.
----COLUMN MENTIONED IN SELECT MUST BE PRESENT IN THE GROUP BY CLAUSE.

>>> GROUPWISE 3RD MIN DATA QUERY?

SELECT DEPARTMENT_ID,MAX (SALARY ), MIN (SALARY), SUM (SALARY),AVG (SALARY) ,

COUNT(SALARY),COUNT(EMPLOYEE_ID) FROM EMPLOYEES
GROUP BY DEPARTMENT_ID HAVING COUNT(EMPLOYEE_ID) >2;

SELECT COMMISSION_PCT,MAX (SALARY ), MIN (SALARY), SUM (SALARY),AVG (SALARY) ,

COUNT(SALARY) FROM EMPLOYEES GROUP BY COMMISSION_PCT;

SELECT JOB_ID,DEPARTMENT_ID,MAX (SALARY ), MIN (SALARY), SUM (SALARY),AVG

(SALARY) ,
COUNT(SALARY) FROM EMPLOYEES GROUP BY JOB_ID,DEPARTMENT_ID;

SELECT JOB_ID,DEPARTMENT_ID,MAX (SALARY ), MIN (SALARY), SUM (SALARY),AVG

(SALARY) ,
COUNT(SALARY) FROM EMPLOYEES WHERE SALARY >20000 GROUP BY JOB_ID,DEPARTMENT_ID

SELECT JOB_ID,DEPARTMENT_ID,MAX (SALARY ), MIN (SALARY), SUM (SALARY),AVG

(SALARY) ,
COUNT(SALARY) FROM EMPLOYEES WHERE SALARY >20000 GROUP BY JOB_ID,DEPARTMENT_ID
HAVING COUNT(SALARY)>=1
ORDER BY DEPARTMENT_ID

SELECT JOB_ID,DEPARTMENT_ID,MAX (SALARY ), MIN (SALARY), SUM (SALARY),AVG

(SALARY) ,
COUNT(SALARY) FROM EMPLOYEES GROUP BY JOB_ID,DEPARTMENT_ID ORDER BY DEPARTMENT_ID

SELECT JOB_ID,DEPARTMENT_ID,MAX (SALARY ), MIN (SALARY), SUM (SALARY),AVG

(SALARY) ,
COUNT(SALARY) FROM EMPLOYEES GROUP BY JOB_ID,DEPARTMENT_ID ORDER BY JOB_ID;

SELECT DEPARTMENT_ID,EMPLOYEE_ID, MIN(SALARY) FROM EMPLOYEES GROUP BY

DEPARTMENT_ID,EMPLOYEE_ID

*** HAVING CLAUSE ***

----APPLY FILTER ON RESULT OF GROUP FUNCTION
----IT WILL APPLY ON IMAGINARY TABLE.
----WHEN GROUP BY EXECUTION THEN USE HAVING.
----IT COMES WITH GROUP BY CLAUSE.
----EXECUTION SEQUENCE: WHERE CLAUSE THEN EXECUTE GROUP BY.
----WORKS ON AGGREGATED DATA.

>>> HAVING CLAUSE DRAWBACK?

SELECT DEPARTMENT_ID,AVG(SALARY) FROM EMPLOYEES WHERE EMPLOYEE_ID > 103

HAVING AVG (SALARY) > 9000 GROUP BY DEPARTMENT_ID;

SELECT DEPARTMENT_ID,AVG(SALARY),SUM (SALARY) FROM EMPLOYEES

HAVING SUM(SALARY) > 25000 AND AVG(SALARY) > 5000 GROUP BY DEPARTMENT_ID;

SELECT HIRE_DATE FROM EMPLOYEES HAVING COUNT(HIRE_DATE) < =1

GROUP BY HIRE_DATE;

>>> WHAT ARE THE DIFFERENT CLAUSES IN THE SQL? AND EXPLAIN ?
1. WHERE
2. FROM
3. HAVING
4. WITH

*** DENORMALIZATION ***

----CREATING BIG TABLE USING SMALL SMALL TABLE IS CALLED DENORMALIZATION.

DISADVANTAGE:-
1. DUPLICATION
2. WASTE OF MEMORY
3. COMPLEXITY.
4. LOW PERFORMANCE.
5. DATA SECURITY.
6. HUGE RISK OF DATA LOSS.

*** NORMALIZATION ***

----DIVIDE BIG TABLE INTO SMALL SMALL CHUNKS OR A TABLE KNOWN AS NORMALIZATION

>>> NORMALIZATION AND RULES?

>>> WHAT IS NORMALIZATION?
>>> WHY TO USE NORMALIZATION?
>>> NORMALIZATION AND DENORMALIZATION WITH EXAMPLE?
>>> EXPLAIN 1:M REALTION?

*** RDBMS HAVE 3 RELATIONS ***

1 : 1
1 : M ...RELATION
M : M

*** PRIMARY KEY RULE ***

----DUPLICATION NOT ALLOW
----NULL VALUE NOT ALLOWED
----OTHER COLUMN OF THAT TABLE STRICTLY DEPEND ON PRIMARY KEY COLUMN

*** FOREIGN KEY ***

----DUPLICATION ALLOWED
----NULL ALLOW
----MUST BE PRIMARY KEY FIRST.
 *** PARTITION BY ***
----TO GET DATA FROM MULTIPLE TABLE
----IT HAS DRAWBACK THAT'S WHY GROUP BY CLAUSE IS DEVELOPED.
----PARTION BY CLAUSE EXECUTED ON EVERY ROW IT WILL GIVE EACH ROW WITH GROUP
RESULT.

>>> WHAT IS PARTITION BY?

SELECT DEPARTMENT_ID,FIRST_NAME,SUM(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID;

...ERROR BCZ WHERE TO WRITE SUM OF SALARY IN FRONT OF WHOM.
...WE CAN WRITE SUM OF SALARY IN FRONT OF EVERYONE USING PARTION BY CLAUSE
...IT WAS POSIBLE EARLIER BY PARTION BY CLAUSE BUT IT IS NOT LOGICAL RESULT
...HENCE IT GIVES ERROR AND WE STARTED USING GROUP BY CLAUSE.

SELECT DEPARTMENT_ID,FIRST_NAME,SUM(SALARY)OVER(PARTION BY DEPARTMENT_ID)

AS DATA1 FROM EMPLOYEES;
----WE WILL GET ALL RECORDS IN THIS PARTION BY CLAUSE.
----IT GIVE AGGREGATED COLUMN WITH EACH RECORD IN THE TABLE.

SELECT DEPARTMENT_ID,SALARY, FIRST_NAME,

SUM(SALARY)OVER(PARTION BY DEPARTMENT_ID) AS DATA1 FROM EMPLOYEES;

*** CONDITONAL EXPRESSION ***

1) CASE EXPRESSION
2) DECODE FUNCTION

*** CASE EXPRESSION ***

----CASE IS A EXPRESSION.
----CASE STATEMENT USED IN SQL/PLSQL
----CASE TREATS NULL STATEMENT DIFFERENTLY.
----CASE EXPECTS DATATYPE CONSISTENTLY
----CASE IS SLOWER THAN DECODE
----WHEN, THEN, ELSE
----THERE IS A TWO TYPES OF CASE

1. SIMPLE CASE
2. SEARCH CASE

SELECT FIRST_NAME , SALARY,

CASE FIRST_NAME WHEN 'Steven' THEN SALARY+5000
WHEN 'Lex' THEN SALARY+6000
ELSE SALARY END AS BONUS FROM EMPLOYEES;

SELECT FIRST_NAME,LAST_NAME,
CASE WHEN SALARY BETWEEN 30000 AND 20000 THEN 1
WHEN SALARY BETWEEN 10000 AND 19000 THEN 2
WHEN SALARY BETWEEN 1000 AND 9000 THEN 3
ELSE SALARY END AS FLAG FROM EMPLOYEES;

SELECT FIRST_NAME,LAST_NAME,
CASE WHEN SALARY BETWEEN 30000 AND 20000 THEN 'A'
WHEN SALARY BETWEEN 10000 AND 19000 THEN 'B'
WHEN SALARY BETWEEN 1000 AND 9000 THEN 'C'
ELSE SALARY END AS FLAG FROM EMPLOYEES;

SELECT FIRST_NAME,LAST_NAME,
CASE WHEN SALARY < 30000 THEN 'A'
 WHEN SALARY < 20000 THEN 'B'
WHEN SALARY < 10000 THEN 'C'
ELSE SALARY END AS FLAG FROM EMPLOYEES;

SELECT FIRST_NAME,LAST_NAME,
CASE WHEN SALARY > 30000 THEN 1
WHEN SALARY > 20000 THEN 2
WHEN SALARY > 10000 THEN 3
ELSE SALARY END AS FLAG FROM EMPLOYEES;

SELECT FIRST_NAME,SALARY,
CASE SALARY WHEN 5000 THEN SALARY+5000
WHEN 10000 THEN SALARY +10000
WHEN 12000 THEN SALARY+12000
ELSE SALARY END AS EXCEPTIONAL_BONUS FROM EMPLOYEES;

SELECT FIRST_NAME,SALARY,
CASE WHEN FIRST_NAME LIKE 'A%' THEN SALARY+5000
WHEN FIRST_NAME LIKE 'S%' THEN SALARY+7000
WHEN FIRST_NAME LIKE '%X' THEN SALARY+3000
ELSE SALARY-4000 END AS NEW_SALARY FROM EMPLOYEES;
SALERY IS NULL

SELECT COMMISSION_PCT,
CASE WHEN COMMISSION_PCT IS NULL THEN 'COMMISSION_PCT IS NULL'
WHEN COMMISSION_PCT IS NOT NULL THEN 'IT IS OK FOR INCREMENT'
ELSE 'NOT FOUND' END FROM EMPLOYEES;

>>> WHICH IS FASTER CASE AND DECODE? AND WHY?

*** DECODE ***

----DECODE IS A FUNCTION
----DECODE FUNCTION IN ORACLE THAT CAN BE USED IN SQL ONLY.
----DECODE TREATS NULL AS NULL
----DECODE DOES NOT EXPECTS DATA TYPE CONSISTENTLY
----DECODE IS FASTER THAN CASE

SELECT FIRST_NAME,SALARY,
DECODE(FIRST_NAME,'Steven',SALARY+5000, 'David',SALARY+6000,SALARY)
AS DATA1 FROM EMPLOYEES

SELECT FIRST_NAME, SALARY,

DECODE (FIRST_NAME,'Lex' ,SALARY+2000,'Bruce',SALARY+4000,SALARY)
AS DATA1 FROM EMPLOYEES

SELECT COMMISSION_PCT,
DECODE (COMMISSION_PCT,NULL,'WE CAN NOT ADD VALUE',
0.3,'WE CAN ADD VALUE',COMMISSION_PCT)
AS DATA1 FROM EMPLOYEES;

---------------------------- CHAPTER 3 (JOIN) ----------------------------------

>>> CAN WE DISPLAY TWO COLUMN FROM DIFFERENT TABLE WITHOUT USING JOIN?
----YES' WE CAN, BUT DATA IS NOT LOGICALLY CORRECT.
SELECT FIRST_NAME,DEPARTMENT_NAME FROM EMPLOYEES,DEPARTMENTS
 \\ JOIN \\
----IT IS USED TO SHOW DATA FROM MULTIPLE TABLE
----RETRIVE THE DATA FROM MORE THAN ONE TABLE.
----THE PURPOSE OF JOIN IS TO COMBINE THE DATA ACROSS TABLE.
----A JOIN IS ACTUALLY PERFORM BY THE WHERE CLAUSE WHICH COMBINES THE SPECIFIED
ROWS OF THE TABLE.

>>> WHAT IS JOIN AND THEIR TYPES?

>>> CREATE 4 TABLE AND RETRIVE DATA USING JOIN QUERY?
>>> WHAT IS JOIN? AND HOW CAN YOU JOIN TWO TABLES?
>>> WHAT IS JOIN?
>>> WHAT IS NEED OF JOIN IN SQL?
>>> EXPLAIN TYPES OF JOIN?
>>> WHY JOIN?
>>> CAN WE DISPLAY TWO COLUMN FROM DIFFERENT TABLE WITHOUT USING JOIN?
>>> EVEN AFTER USING JOIN DO CROSS PRODUCT OCCURS? AND EXPLAIN?
>>> DIFFERENCE BETWEEN UNION AND JOIN?
>>> DIFFERENCE BETWEEN IN AND EXIST?

*** CARTESIAN PRODUCT ***

----ROWS OF THE FIRST TABLE MULTIPLY WITH ALL ROWS OF OTHER TABLE.

\\ TYPES OF JOIN \\
----1}EQUI JOIN / INNER JOIN
----2)NON-EQUI JOIN.
----3)OUTER JOIN.

*** EQUI JOIN / INNER JOIN ***

----IT IS USED FOR MATCHING DATA.
1. NATURAL JOIN
2. SELF JOIN.

--THIS ARE THE CLAUSES.

A. USING CLAUSE.
B. ON CLAUSE.

A. NATURAL JOIN.
----COLUMN NAME MUST BE SAME
----DATA TYPE MUST BE SAME.
----ONLY ONE COMMON OR SAME COLUMN WITH SAME DATA TYPE AND
----ALSO SAME NAME IN BOTH THE TABLE. AND IT IS A MANDATORY REQUIREMENT OF THIS
NATURAL JOIN.
----IT WILL JOIN COLUMN HORIZONTAL.
----IT WILL MATCH FIRST TABLE FIRST (ROW) VALUE WITH ANOTHER TABLE ALL (ROW)VALUE
----THEN IT WILL SHOW THE RESULT AND MATCH THE SECOND TABLE INPUT WITH FIRST TABLE.

>>> DRAWBACK OF NATURAL JOIN?

SELECT * FROM EMPLOYEES E NATURAL JOIN DEPARTMENTS D

SELECT SALARY, EMPLOYEE_ID,DEPARTMENT_ID, MANAGER_ID, LOCATION_ID FROM EMPLOYEES
NATURAL JOIN DEPARTMENTS;
SELECT MANAGER_ID, DEPARTMENT_ID FROM EMPLOYEES
NATURAL JOIN DEPARTMENTS;
SELECT * FROM EMPLOYEES NATURAL JOIN LOCATIONS
SELECT * FROM EMPLOYEES NATURAL JOIN DEPARTMENTS;
SELECT FROM T1 NATURAL JOIN T2;
SELECT * FROM T2 NATURAL JOIN T1;
SELECT * FROM T1;
SELECT * FROM T1 NATURAL JOIN T2 WHERE T_ID=1;
SELECT * FROM DEPARTMENTS;
SELECT SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID=60;
SELECT SUM(SALARY) FROM DEPARTMENTS NATURAL JOIN EMPLOYEES WHERE DEPARTMENT_ID=60;
SELECT FIRST_NAME,DEPARTMENT_NAME FROM DEPARTMENTS NATURAL JOIN EMPLOYEES
WHERE DEPARTMENT_NAME LIKE 'A%';

SELECT * FROM DEPT_1 NATURAL JOIN EMP_0

SELECT D_NO,E_NAME,E_SAL,D_NAME FROM DEPT_1 NATURAL JOIN EMP_0 WHERE D_NAME='IT'
SELECT SUM(E_SAL) FROM DEPT_1 NATURAL JOIN EMP_0 WHERE D_NAME='IT';
SELECT D_NO,E_NAME FROM DEPT_1 NATURAL JOIN EMP_0 WHERE E_NAME LIKE 'V%';
SELECT E_NAME,D_CITY,D_NAME FROM EMP_0 NATURAL JOIN DEPT_1
WHERE D_CITY IN ('PUNE','NASHIK');
SELECT * FROM DEPT_1, EMP_0 WHERE DEPT_1.D_NO=EMP_0.D_NO;
SELECT * FROM DEPT_1 NATURAL JOIN EMP_0 WHERE E_SAL < 5000;
SELECT * FROM DEPT_1 NATURAL JOIN EMP_0 WHERE E_SAL > 4000;

SELECT * FROM DEPT_1

SELECT * FROM EMP_0

B. USING CLAUSE:
----IF WE HAVE MORE THAN ONE COMMON COLUMN / SAME COLUMN THEN WE USE THIS CLAUSE.
----IF COLUMN NAME IS SAME BUT DATA TYPE IS DIFFERENT THAT TIME WE USE THIS CLAUSE.
----TO MATCH SPECIFIC COLUMN EXPLICITLY WE NEED TO USE USING CLAUSE.

SELECT * FROM T3 NATURAL JOIN T4;

----IT WILL CHECK ONLY MATCHING DATA BUT THERE IS NO MATCHING DATA.
SELECT * FROM T3 JOIN T4 USING(CNO);

SELECT * FROM R1;

SELECT * FROM R2;
SELECT * FROM R1 NATURAL JOIN R2;
SELECT * FROM R1 JOIN R2 ON;

C. ON CLAUSE
----IF COLUMN NAME MAY BE DIFFERENT BUT DATA TYPE MUST BE SAME THEN IT WILL SHOW
THE RESULT.
----IT WILL ACCEPT DIFFERENT NAME AND GIVE THE RESULT.
----IT IS THE MAIN JOIN.
----USING SELF JOIN TO IDENTIFY AND DELETE DUPLICATES.

*** SELF JOIN ***

----JOIN THE TABLE WITH ITSELF.

>>> FIND THE EMPLOYEES WITH SALARY MORE THAN THEIR MANAGERS SALARY?
SELECT * FROM EMPLOYEES;
SELECT * FROM EMPLOYEES E1 JOIN EMPLOYEES E2
ON (E1.MANAGER_ID = E2.EMPLOYEE_ID) AND ([Link] > [Link]);

SELECT * FROM EMPLOYEES E1 JOIN EMPLOYEES E2

ON (E1.EMPLOYEE_ID = E2.MANAGER_ID) AND ([Link] < [Link]);

>>> FIND THE EMPLOYEES WITH SALARY IS SAME WITH MANAGERS SALARY?
SELECT E1.FIRST_NAME AS EMPLOYEE_NAME,E2.FIRST_NAME AS
MANAGER_NAME,E1.EMPLOYEE_ID ,
E2.MANAGER_ID ,[Link] AS EMPLOYEE_SALARY, [Link] AS MANAGER_SALARY
FROM EMPLOYEES E1 JOIN EMPLOYEES E2
ON (E1.MANAGER_ID = E2.EMPLOYEE_ID) AND ([Link] = [Link]);

>>> I WANT MANAGER ID PLUS MANAGER NAME IN THE SINGEL TABLE?

SELECT a.EMPLOYEE_ID EMP_ID,
a.FIRST_NAME EMP_NAME,
a. MANAGER_ID MGR_ID,B.FIRST_NAME AS MGR_NAME
FROM EMPLOYEES A JOIN EMPLOYEES B ON A.MANAGER_ID=B.EMPLOYEE_ID;

>>> DELETE DUPLICATE RECORD?

SELECT EMPLOYEE_ID, FIRST_NAME,B.EMPLOYEE_ID, LAST_NAME, FROM EMPLOYEES A JOIN
SELECT * FROM EMPLOYEES JOIN DEPARTMENTS (DEPARTMENT_ID);
SELECT * FROM EMPLOYEES E JOIN DEPARTMENTS D
ON(E.DEPARTMENT_ID=D.DEPARTMENT_ID)

SELECT E.FIRST_NAME AS EMP_NAME,M.FIRST_NAME AS M_NAME FROM EMPLOYEES E JOIN

EMPLOYEES M
ON(E.EMPLOYEE_ID=M.MANAGER_ID);

SELECT DEPARTMENT_ID FROM EMPLOYEES

UNION
SELECT DEPARTMENT_ID FROM DEPARTMENTS
UNION
SELECT NULL AS DEPARTMENT_ID FROM LOCATIONS

*** NON EQUI JOIN ***

----IT WILL SHOW NON-MATCH DATA.
--LOGICAL EXAMPLE :- TO FIND A PROFIT OF A PRODUCT BY COMPARING PURCHASE COST AND
SELLING COST.

>>> EXPLAIN NON-EQUI JOIN LOGICAL EXAMPLE

SELECT * FROM DEPT_1, EMP_0 WHERE DEPT_1.D_NO > EMP_0.D_NO;

SELECT * FROM DEPT_1, EMP_0 WHERE DEPT_1.D_NO < EMP_0.D_NO;
SELECT * FROM T3, T4 WHERE [Link] > [Link];
SELECT * FROM T3, T4 WHERE [Link] < [Link];

SELECT * FROM Y2;

SELECT * FROM Y3;
SELECT * FROM Y2 A JOIN Y3 B ON ( [Link] < [Link]);

SELECT * FROM PURCHASE_COST;

SELECT * FROM SELLING_COST;
SELECT * FROM PURCHASE_COST A JOIN SELLING_COST B ON
(A.P_COST > B.S_COST);
SELECT * FROM PURCHASE_COST A JOIN SELLING_COST B ON
(A.P_COST < B.S_COST);
SELECT * FROM PURCHASE_COST A JOIN SELLING_COST B ON
(A.P_COST = B.S_COST);

*** CROSS JOIN ***

----FIRST ROW MATCHES WITH THE SECOND COLUMN OF THE TABLE.
----CROSS PRODUCT BETWEEN TWO TABLES

SELECT * FROM DEPT_1 CROSS JOIN EMP_0;

SELECT * FROM T3 CROSS JOIN T4;

*** OUTER JOIN ***

----MATCHING AS WELL AS NON-MATCHING DATA IT WILL SHOWING.
----IT IS DIVIDED IN THREE TYPES.
A. LEFT OUTER JOIN.
B. RIGHT OUTER JOIN.
C. FULL OUTER JOIN.

>>>LOGICAL EXAMPLE AND DIFFERENCE BETWEEN LEFT OUTER JOIN AND RIGHT OUTER JOIN?

A. LEFT OUTER JOIN

----MATCHING DATA, AND UNMATCHED DATA FROM LEFT TABLE

>>> DIFFERENCE BETWEEN LEFT OUTER JOIN AND MINUS OPERATOR?

SELECT FIRST_NAME FROM EMPLOYEES A LEFT OUTER JOIN DEPARTMENTS B ON [Link] =[Link];
SELECT * FROM DEPT_1 A LEFT OUTER JOIN EMP_0 B ON A.D_NO=B.D_NO;
SELECT * FROM T1 A LEFT OUTER JOIN T2 B ON A.T_ID = B.T_ID
SELECT * FROM T3;
SELECT * FROM T4;
SELECT * FROM T3 A LEFT OUTER JOIN T4 B ON [Link] = [Link];

B. RIGHT OUTER JOIN

----MATCHING DATA, AND UNMATCHED DATA FROM RIGHT TABLE

SELECT * FROM DEPT_1 A RIGHT OUTER JOIN EMP_0 B ON A.D_NO = B.D_NO;

SELECT * FROM T3 A RIGHT OUTER JOIN T4 B ON [Link] = [Link];

C. FULL OUTER JOIN

----IT WILL SHOWING MATCHING DATA AS WELL AS UNMATCHED DATA FROM RIGHT SIDE AS WELL
AS LEFT SIDE.

SELECT * FROM DEPT_1 A FULL OUTER JOIN EMP_0 B ON A.D_NO = B.D_NO;

SELECT * FROM T3 A FULL OUTER JOIN T4 B ON [Link] = [Link];

--EX:-
SELECT * FROM TABLE_1;
SELECT * FROM TABLE_2;

SELECT * FROM TABLE_1 A JOIN TABLE_2 B ON ([Link] = [Link]); ---EQUI / INNER JOIN
SELECT * FROM TABLE_1 A JOIN TABLE_2 B ON ([Link] > [Link]); ---NON-EQUI JOIN
SELECT * FROM TABLE_1 A LEFT OUTER JOIN TABLE_2 B ON ([Link] = [Link]); ---LEFT
OUTER JOIN
SELECT * FROM TABLE_1 A RIGHT OUTER JOIN TABLE_2 B ON ([Link] = [Link]); ---RIGHT
OUTER JOIN
SELECT * FROM TABLE_1 A FULL OUTER JOIN TABLE_2 B ON ([Link] = [Link]); ---FULL
OUTER JOIN
SELECT * FROM TABLE_1 NATURAL JOIN TABLE_2; ---NATURAL
JOIN
SELECT * FROM TABLE_1 CROSS JOIN TABLE_2; ---CROSS
JOIN

CREATE TABLE OP1(SR_NO NUMBER(30),NAME VARCHAR2(30));

CREATE TABLE OP2(SR_NO NUMBER(30),SAL NUMBER(30));
INSERT INTO OP2 VALUES(&X1,&X2);
SELECT * FROM OP1;
SELECT * FROM OP2;

SELECT * FROM OP1 A LEFT OUTER JOIN OP2 B

ON (A.SR_NO=B.SR_NO);

SELECT * FROM OP1 A FULL OUTER JOIN OP2 B

ON (A.SR_NO=B.SR_NO);

SELECT * FROM OP1 A RIGHT OUTER JOIN OP2 B

ON (A.SR_NO=B.SR_NO);

SELECT * FROM OP1 A JOIN OP2 B

ON (A.SR_NO=B.SR_NO);

SELECT * FROM OP1 A NATURAL JOIN OP2 B

ON (A.SR_NO=B.SR_NO);

-----------------------------------CHAPTER 4 ------------------------------------

>>> DISPLAY DEPARTMENT WISE SUM OF SALARY?

SELECT DEPARTMENT_ID,SUM(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID

*** SUBQUERY ***

----WE USE TWO QUERY IN THE SINGLE QUERY WITH THE HELP OF BRACKET.
----QUERY INSIDE THE QUERY.
----ONE SELECT STATEMENT INSIDE THE ANOTHER STATEMENT.
----INSIDE QUERY IS INNER QUERY
----OUTSIDE QUERY IS OUTER QUERY
----WE USE IN THE FORM OF CLAUSE AND ALSO WHERE CLAUSE.

>>> TELL ME SUBQUERY WITH THEIR EXAMPLE AND TYPES?

>>> SINGLE ROW SUBQUERY AND EXAMPLE?
>>> WHY WE USE SUBQUERY?

SELECT * FROM EMPLOYEES WHERE SALARY >

(SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME='Lex');

SELECT * FROM EMPLOYEES WHERE SALARY <

(SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME='Diana');

SELECT * FROM EMPLOYEES WHERE SALARY <

(SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME='David');
----WHEN INNER QUERY RETURN MORE THAN ONE ROW THEN IT WILL NOT SHOW ERROR

>>>WE WANT EMPLOYEE ID 101 SALARY THEN WHO HAVE GREATER THAN THIS VALUE PLS
DISPLAY?
SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID =101
SELECT * FROM EMPLOYEES WHERE SALARY > 20000 ...THIS IS REGULAR QUERY.
----THIS IS THE OPTIMISED QUERY FROM ABOVE.
SELECT * FROM EMPLOYEES WHERE SALARY >
(SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID =101)

>>> NEED TO FETCH THE EMP WORKING WITH KNOWN EMP. HOW YOU CAN FETCH THE DATA FROM
DATABASE?
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=
(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=101)

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN

(SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=90)

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=90)

SELECT * FROM EMPLOYEES WHERE SALARY > ANY

(SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME IN ('Lex','AJAY'));

SELECT * FROM EMPLOYEES WHERE SALARY > ANY

(SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME LIKE 'C%');

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID IN

(SELECT EMPLOYEE_ID FROM EMPLOYEES WHERE JOB_ID='FI_ACCOUNT');

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE SALARY > 6000);

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE SALARY > 6000);

SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID IN

(SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME='HR');

SELECT AVG(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID IN

(SELECT DEPARTMENT_ID FROM DEPARTMENTS WHERE DEPARTMENT_NAME='SALES');

SELECT D.*,E.* FROM DEPARTMENTS D JOIN EMPLOYEES E ON

(D.DEPARTMENT_ID=E.DEPARTMENT_ID)
WHERE DEPARTMENT_NAME='IT';

SELECT SALARY FROM EMPLOYEES HAVING COUNT(SALARY)=3 AND

(SELECT SALARY FROM EMPLOYEES ORDER BY SALARY DESC);

>>> DISPLAY MAX SALARY FROM EMPLOYEES?

SELECT MAX(SALARY) FROM EMPLOYEES;

>>> DISPLAY SECOND MAX SALARY FROM EMPLOYEES?

SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY < (SELECT MAX(SALARY) FROM
EMPLOYEES);

>>>DISPLAY EXCEPT FIRST ALL SALARY BY DESC ORDER?

SELECT SALARY FROM EMPLOYEES WHERE SALARY <
(SELECT MAX(SALARY) FROM EMPLOYEES);

>>> DISPLAY THIRD MAX SALARY FROM EMPLOYEES?

SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY <
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY < (SELECT MAX(SALARY) FROM
EMPLOYEES))
>>> DISPLAY FOURTH HIGHEST SALARY FROM EMPLOYEES?
SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY <
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY <
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY <
(SELECT MAX(SALARY) FROM EMPLOYEES)));

>>> DISPLAY MIN SALARY FROM EMPLOYEES?

SELECT MIN(SALARY) FROM EMPLOYEES

>>> DISPLAY SECOND MIN SALARY FROM EMPLOYEES?

SELECT MIN(SALARY) FROM EMPLOYEES WHERE SALARY > (SELECT MIN(SALARY) FROM
EMPLOYEES)

>>> DISPLAY THIRD MIN SALARY FROM EMPLOYEES?

SELECT MIN(SALARY) FROM EMPLOYEES WHERE SALARY >
(SELECT MIN(SALARY) FROM EMPLOYEES WHERE SALARY >
(SELECT MIN(SALARY) FROM EMPLOYEES));

>>> SUBQUERY AND THERE TYPES AND EXECUTION OF SUBQUERY?

*** TYPES OF SUB QUERY ***

----THERE IS TWO TYPES OF SUBQUERY

1. SINGLE ROW SUBQUERY

----OUTPUT IS ONLY ONE VALUE
----WHEN INNER QUERY RETURN ONE ROW IS KNOWN AS SINGLE ROW SUBQUERY.
OPERATOR: > < >= <= != =

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID=

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=101)

2. MULTIPLE ROW SUBQUERY.

----OUTPUT IS MULTIPLE
----WHEN INNER QUERY RETURN MORE THAN ONE ROW IS KNOWN AS MULTIPLE ROW SUBQUERY.

>>> WHY MULTIPLE ROW SUBQUERY?

-WE USE FOLLOWING OPERATOR FOR THIS QUERY.

1. IN = IT IS THE MAIN OPERATOR FROM BELOW. MATCHING LIST RETURN IN THE RESULT.
2. ANY = ANY ONE FROM MULTIPLE VALUES. IF ANY CONDITION IS SATISFY THEN IT WILL
RETURN RESULT.
3. ALL = ALL VALUES SHOULD BE TRUE. IF ALL VALUE TRUE THEN IT WILL RETURN RESULT.

*** IN ***
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=90);

SELECT * FROM EMPLOYEES WHERE MANAGER_ID IN

( SELECT MANAGER_ID FROM EMPLOYEES WHERE JOB_ID='IT_PROG')

SELECT * FROM EMPLOYEES WHERE MANAGER_ID IN

( SELECT MANAGER_ID FROM EMPLOYEES WHERE JOB_ID='PU_CLERK')

SELECT * FROM EMPLOYEES WHERE SALARY IN

(SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME LIKE 'A%');

*** ANY ***

----find employees whose salary is greater than any salary in a certain department.
SELECT * FROM EMPLOYEES WHERE SALARY > ANY
(SELECT salary FROM employees WHERE DEPARTMENT_ID=90);

>>> WE WANT DEPARTMENT ID IS GREATER THAN 90,100,5? FETCH WITHOUT GOT ERROR?
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = ANY
(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID IN (90,100,5))

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID >= ANY

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=90)

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID >= ANY

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=&X1)

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID < ANY

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=&X1)

*** ALL ***

----ALL CONDITION SHOULD BE TRUE THEN IT WILL RETURN VALUE.
----find departments where all employees have a job title of 'Manager'.
----find all employees whose salary is greater than all salaries in a specific
department.

SELECT department_id, department_name FROM departments WHERE 'Manager' = ALL

(SELECT job_title FROM DEPARTMENTS WHERE departments.department_id =
departments.department_id);

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID > ALL

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=90)

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID > ALL

(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID=50)

SELECT * FROM EMPLOYEES WHERE SALARY < ALL

(SELECT SALARY FROM EMPLOYEES WHERE FIRST_NAME LIKE 'A%');

>>> DISPLAY THE DETAIL OF 1ST AND 3RD HIGHEST EMP SALARY BASED ON THERE SALARY?
SELECT * FROM EMPLOYEES WHERE SALARY >=
(SELECT MAX(SALARY) FROM EMPLOYEES
WHERE SALARY < (SELECT MAX(SALARY) FROM EMPLOYEES));
SELECT * FROM (SELECT SALARY FROM EMPLOYEES ORDER BY SALARY DESC) WHERE ROWNUM <=2;
SELECT * FROM (SELECT SALARY FROM EMPLOYEES ORDER BY SALARY DESC) WHERE ROWNUM <=5;

*** MULTIPLE COLUMN COMPARISON ***

----HERE WE ARE USING MORE THAN ONE COLUMN IN SUBQUERY.
----THEIR IS A TWO TYPES OF COMPARISON
1. PAIRWISE COMPARISON
----HERE USE MULTIPLE COLUMN IN THE INNER QUERY AND OUTER QUERY.

2. NON PAIRWISE COMPARISON

----HERE WE USE MULTIPLE COLUMN WITH THE HELP OF AND,OR OPERATOR.

...DOUBT
 *** ROWNUM ***
----SYSTEM DEFINED COLUMN.
----USED TO GENRATE ROW NUMBER FOR TEMPORARY PURPOSE.
----IT IS USED TO GIVEN NUMBER TO COLUMN OR FOR NUMBERING.

>>> FIND 2 MAX SALARY FROM EMPLOYEES?

>>> LAST 20TH ROW FROM TABLE?

>>> DISPLAY FIRST THREE HIGHEST SALARY?

SELECT * FROM (SELECT * FROM EMPLOYEES ORDER BY SALARY DESC) WHERE ROWNUM <=3

>>> DISPLAY TOP 10 SALARY OF EMPLOYEES?

SELECT * FROM (SELECT * FROM EMPLOYEES ORDER BY SALARY DESC) WHERE ROWNUM <=10

>>>DISPLAY TOP 100 EMPLOYEE BY DESC ORDER?

SELECT * FROM (SELECT * FROM EMPLOYEES ORDER BY SALARY DESC) WHERE ROWNUM <=100

>>> DISPLAY TOP 30 SALARY BY DESC?

SELECT * FROM (SELECT * FROM EMPLOYEES ORDER BY SALARY DESC) WHERE ROWNUM <=30

>>> USE ROWNUM ON EMPLOYEE TABLE?

SELECT ROWNUM, FIRST_NAME FROM EMPLOYEES

SELECT SALARY FROM EMPLOYEES E1

WHERE 2= (SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEES E2
WHERE ([Link] <= [Link]);

SELECT E1.FIRST_NAME, [Link], E1.DEPARTMENT_ID

FROM EMPLOYEES E1
WHERE 2=(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEES E2
WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
AND [Link] <= [Link])

SELECT E1.FIRST_NAME, [Link], E1.DEPARTMENT_ID

FROM EMPLOYEES E1
WHERE 3=(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEES E2
WHERE E1.DEPARTMENT_ID = E2.DEPARTMENT_ID
AND [Link] <= [Link])

>>> DIFFERENCE BETWEEN SET AND JOIN? AND WHICH IS FASTER?

>>> LIMITATION OF SET OPERATION?
>>> EXPLAIN SET OPERATION?
>>> DIFFERENCE BETWEEN UNION AND UNION ALL?

*** SET OPERATOR ***

----TO COMBINE THE RESULT OF MORE THAN ONE QUERY.
----IT WILL MARGE TWO SELECT QUERY RESULT.
----ARRANGE THE COMBINED RESULT IN VERTICAL MANNER (STACKING OF RESULT)
----COLUMN FROM BOTH THE QUERY SHOULD BE SAME IN NUMBER AND DATA TYPE.
----FINAL RESULT TAKE THE COLUMN FROM THE FIRST QUERY.
----ORDER BY CLAUSE SHOULD BE AT THE END OF THE QUERY
----AND COLUMN IN ORDER BY CLAUSE SHOULD BE FROM THE FIRST QUERY.

*** TYPES OF SET ***

1. UNION
----UNION OPERATOR COMBINES RESULT SET OF TWO OR MORE SELECT STATEMENT.
----IT IS REMOVE DUPLICATE ROWS FROM THE RESULT.
----SHOW RESULT IN ASCENDING ORDER.
----MERGE TWO ROWS FROM TWO COLUMN AND GET RESULT WITHOUT DUPLICATION
----THE SET OPERATOR ALLOW US TO PERFORM ALGEBRIC AMONG TWO OR MORE TABLE OR
RELATION
----AND PRODUCE A RESULT DEPENDING ON ITS OPERATION.
1. SAME NUMBER OF COLUMN.
2. COLUMN MUST HAVE SIMILAR DATATYPE.
3. COLUMN MUST HAVE IN SAME ORDER.

>>> DIFFERENCE BETWEEN UNION AND UNION ALL?

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10, 20)

UNION
SELECT * FROM EMPLOYEES WHERE FIRST_NAME IN ('Lex');

>>> FIND MAX SALARY AND MIN SALARY IN SINGLE QUERY DEPARTMENT WISE?
SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID IN (30,40,60)
UNION
SELECT MIN(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID IN (30,40,60);

>>> FIND THE MAX SALARY AND MIN SALARY IN SINGLE QUERY?
SELECT 'EMP IS GETTING MAX SALARY' AS MAX , FIRST_NAME,LAST_NAME,SALARY
FROM EMPLOYEES WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEES)
UNION
SELECT 'EMP IS GETTING MIN SALARY' AS MIN, FIRST_NAME,LAST_NAME,SALARY
FROM EMPLOYEES WHERE SALARY IN
(SELECT MIN(SALARY) FROM EMPLOYEES);

SELECT * FROM EMPLOYEES WHERE SALARY IN (SELECT MAX(SALARY) FROM EMPLOYEES)

UNION
SELECT * FROM EMPLOYEES WHERE SALARY IN (SELECT MIN(SALARY) FROM EMPLOYEES);

SELECT DEPARTMENT_ID FROM EMPLOYEES

UNION
SELECT DEPARTMENT_ID FROM DEPARTMENTS

SELECT T_ID,TNAME FROM T1

UNION
SELECT T_ID,T_NAME FROM T2
----FIRST TABLE COLUMN NAME ON THE RESULT
----AND ASCENDING ORDER ACCORDING TO FIRST COLUMN.

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10, 20)

UNION
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (30);

SELECT DEPARTMENT_ID FROM DEPARTMENTS

UNION
SELECT DEPARTMENT_ID FROM EMPLOYEES
----FROM DIFFERENT TABLE ALSO WE ADD OR MAKE SET OF THE TABLE.

SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10, 20)

UNION
SELECT FIRST_NAME,LAST_NAME FROM EMPLOYEES WHERE DEPARTMENT_ID IN (30)
2. UNION ALL
----IT IS NOT REMOVE DUPLICATE VALUES FROM RESULT.
----CONSIDER THE DUPLICATE
----NO ORDER IN THE RESULT
----IT IS FASTER THAN UNION BCZ THERE IS NO SORTING OF DATA.
----IT DOES NOT SORT THE DATA BCZ OF DUPLICATION
----COMBINATION OF TWO COLUMN WITH DUPLICATION

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10, 20)

UNION ALL
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10,30);

SELECT DEPARTMENT_ID FROM DEPARTMENTS

UNION ALL
SELECT DEPARTMENT_ID FROM EMPLOYEES

SELECT * FROM T1
UNION ALL
SELECT * FROM T2

3. INTERSECT
----IT IS ONLY GIVES THE COMMON ROWS AND ASCENDING ORDER IN THE RESULT.
----IT IS NOT SHOWING THE DUPLICATION

>>> EXPLAIN INTERSECT OPERATION WITH LOGICAL OPERATION?

>>> INTERSECT AND MINUS SET OPERATOR? AND WHERE I USED THESE OPERATOR IN MY
PROJECT?

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10, 20)

INTERSECT
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10,30);

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10, 20,90)

INTERSECT
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (90,30);

SELECT DEPARTMENT_ID FROM DEPARTMENTS

INTERSECT
SELECT DEPARTMENT_ID FROM EMPLOYEES

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10,60, 20)

INTERSECT
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10,20,30,60);

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10,20)

INTERSECT
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10,20,30,60);

SELECT * FROM T1
INTERSECT
SELECT * FROM T2

4. MINUS
----GIVES THE RECORD WHICH IS PRESENT IN FIRST QUERY BUT NOT PRESENT IN THE SECOND
QUERY.
----DATA SORT IN ASCENDING ORDER.

>>> EXPLAIN MINUS OPERATION WITH LOGICAL LOGICAL EXAMPLE?

SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10, 20)
MINUS
SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN (10,30);

SELECT * FROM T1
MINUS
SELECT * FROM T2

SELECT * FROM EMPLOYEES

MINUS
SELECT * FROM EMPLOYEES WHERE ROWNUM <

*** CREATE TABLE ***

----INT : IT IS USED FOR NUMERIC DATA. NOT INCLUDE DECIMAL NO IN THIS.
----VARCHAR : VARIABLE LENGHT OF CHARACTER, IT ALLOCATE ONLY REQUIRED MEMORY.
----NUMBER : IT IS USED FOR GENRAL NUMBER, FOR EX, NUMBERS AND DECIMAL NUMBERS ALSO
INCLUDE IN THIS.
----FLOAT : IT IS USED FOR DECIMAL NO.
----SYNTAX: CREATE TABLE TABLE_NAME (COL_1_NAME DATA_TYPE, COL_2_NAME
DATATYPE,COL_3 DATA_TYPE
CREATE TABLE EMP_DETAIL (ID INT,NAME VARCHAR2(20),SALARY NUMBER(10));
DESC EMP;

CREATE TABLE STUDENT_TAB

(S_NO NUMBER, S_NAME VARCHAR2(100),S_MARK NUMBER,S_RESULT VARCHAR2(1));

INSERT INTO STUDENT_TAB VALUES(1,'ONKAR',85,'P');

INSERT INTO STUDENT_TAB VALUES(2,'NIKHIL',30,'F');
INSERT INTO STUDENT_TAB VALUES(3,'SURAJ',95,'P');
INSERT INTO STUDENT_TAB VALUES(4,'NAIM',98,'P');
INSERT INTO STUDENT_TAB VALUES(5,'ASHITOSH',58,'P');

>>> INSTED OF INSERTING ROW BY ROW. WE CAN INSERT DATA WITH THE HELP OF PROCEDURE?

CREATE TABLE EMP_1 (ID_NUM NUMBER(20) CONSTRAINT ID_PK PRIMARY KEY,ID_NAME

VARCHAR2(20),
ID_SAL NUMBER(20));

CREATE OR REPLACE PROCEDURE P1(N1 NUMBER,N2 VARCHAR2,N3 NUMBER)

IS
BEGIN
INSERT INTO EMP_1 VALUES(N1,N2,N3);
COMMIT;
DBMS_OUTPUT.PUT_LINE('ROW INSERTED');
END;

SHOW ERROR
SET SERVEROUTPUT ON
EXECUTE P1(&X1,'&X2',&X3);

SELECT * FROM EMP_1;

ROLLBACK EMP_1 WHERE ID_NUM=2;
DELETE EMP_1 WHERE ID_NUM=4;
DELETE EMP_1 WHERE ID_NUM=5;
COMMIT EMP_1;

CREATE TABLE T6
(DNO NUMBER(10) ,CONSTRAINT RPQ PRIMARY KEY (DNO),DNAME VARCHAR2(15));

CREATE TABLE T5
(N1 NUMBER(10) CONSTRAINT PQR PRIMARY KEY ,DNAME VARCHAR2(15));

>>> CREATE TABLE U1

(EID PRIMARY KEY NOT NULL,
ENAME NOT NULL SHOULD BE 4 CHAR,
HIRE_DATA DATE,
SALARY SHOULD BE 5 NUMBER)

CREATE TABLE U123

(EID NUMBER(20)PRIMARY KEY NOT NULL,
ENAME VARCHAR2(4) NOT NULL,
HIRE_DATE DATE,
SALARY NUMBER(5));

*** INSERT COMMAND ***

----INSERT INTO TABLE_NAME (COL1, COL2, COL3, ...) VALUES ('A',1,'C');
----INSERT INTO TABLE_NAME ('A',1,'C');
NOTE ----IF WE WANT TO PUT VALUES FOR THE ALL COLUMN NO NEED TO MENTION COLUMN
NAMES.
NOTE ----IF WE WANT TO INSERT ONLY SPECIFIC COLUMN THEN NEED TO MENTION THOSE
COLUMN NAMES.
NOTE ----IF VALUES IS CHARACTER PUT INSIDE THE SINGLE QUOTE.(' ')

SELECT * FROM EMPLOYEES

INSERT INTO STUDENT_NAME (EMPLOYEE_ID,
FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANA
GER_ID,DEPARTMENt_ID)
VALUES (99,'XX','YY','ZZ',91,'22-JUN-2001','AA',123,0.5,12345,77)

INSERT INTO EMP_1 (EMPNO,JOB,COMM,DEPTNO) SELECT EMPNO,JOBS, COMM , DEPTNO, FROM

[Link] WHERE DEPTNO=10

*** CREATE NEW TABLE USING EXISTING TABLE ***

----IT WILL COPY DATA ALONG WITH STRUCTURE.

>>> CAN WE DISPLAY STRUCTURE AS WELL AS DATA INTO ANOTHER TABLE USING EXISTING
TABLE?
AND WHAT IS THE SYNTAX?

SELECT * FROM EMP1

CREATE TABLE EMP1 AS SELECT * FROM EMPLOYEES;
CREATE TABLE T4 AS SELECT * FROM EMPLOYEES;
CREATE TABLE V1 AS SELECT * FROM EMPLOYEES
SELECT * FROM V1
SELECT * FROM T4;
DESC EMP1;
INSERT INTO T4 (SELECT * FROM EMPLOYEES);
CREATE TABLE EMP2 AS SELECT FIRST_NAME,SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID
<110;
 *** CREATE NEW TABLE USING EXISTING TABLE AND COPY ONLY STRUCTURE ***

>>> CREATE TABEL STRUCTURE WITH THE EXISTING TABLE?

CREATE TABLE T7 AS SELECT * FROM EMPLOYEES WHERE 1 = 2;
SELECT * FROM T7;
CREATE TABLE G1 AS SELECT * FROM EMPLOYEES WHERE 1=2
SELECT * FROM G1

>>> HOW TO CREATE EMPTY TABLE STRUCTURE WITH THE EXISTING TABLE?

>>> INTERVIEW QUESTION:

COPY THE DATA FROM ONE TABLE TO OTHER?
INSERT INTO T7 SELECT * FROM EMPLOYEES
SELECT * FROM T7

*** DATA TYPE ***

----DATA TYPE ARE USED TO SPECIFY WHAT KIND OF DATA OR VALUES
----CAN BE STORED IN A VARIABLE OR IN A TABLE.

1. INTEGER: 50,60.
----VALUES KIND OF DATA WITHOUT DECIMAL POINT.

2. FLOAT: 25.45,65.35
----VALUES KIND OF DATA WITH DECIMAL POINT NUMBER. THIS KIND OF DATA TYPE

3. NUMBER: 50,45. OR 50.45,36.13

----NUMBER HAVE CAPABILITY TO STORE INTEGER AS WELL AS FLOAT KIND OF DATA STORE
CAPACITY.

4. CHAR: 'AJAY'(10)
----FIXED LENGTH OF CHAR

5. VARCHAR2: 'AJAY'(10)
----VARIABLE LENGTH OF CHAR

6. BOOLEAN: TRUE OR FALSE

----WHEN TRUE OR FALSE KIND OF DATA THEN WE USE THIS.

7. DATE: '22-JUN-2001'
----WHEN DATE TYPE DATA THEN USE THIS FUNCTION.

>>> DIFERENCE BETWEEN VARCHAR AND VARHCHAR2

*** CONSTRAINT ***

----IT ENFORCE RULES ON COLUMN.

>>> WHAT ARE CONSTRAINTS?

1. PRIMARY KEY:
----DUPLICATION NOT ALLOWED.
----NULL VALUE NOT ALLOW.

>>> CAN WE DEFINE PRIMARY KEY ON DUPLICATION TABLE?

>>> DIFFERENCE BETWEEN PRIMARY KEY AND UNIQUE KEY?

2. FOREIGN KEY:
----DUPLICATE ALLOW
----NULL VALUE ALLOW
----MUST BE FIRST PRIMARY KEY

>>> WHAT IS FOREIGN KEY?

3. UNIQUE KEY:
----DUPLICATION NOT ALLOW.
----NULL VALUE ALLOW

>>> NOT NULL AND UNIQUE KEY DIFFERENCE?

4. NOT NULL:
----DUPLICATION ALLOW.
----NULL NOT ALLOW.

5. CHECK:
----DUPLICATION ALLOW
----NULL VALUE ALLOW
----IT IS DEPEND ON USER DEFINED CONDITION.

*** UPDATE ***

----MODIFY THE DATA AS PER REQUIREMENT.
----WITHOUT WHERE CLAUSE IN UPDATE COMMAND MODIFY THE ENTIRE COLUMN.
----WITH WHERE MODIFY SPECIFIC ROW.
----SYNTAX:
UPDATE TABLE_NAME
SET FIRST_NAME='ONKAR'
WHERE EMPLOYEE_ID=101;

----IF WE WANNA MODIFY THE MULTIPLE COLUMN THEN SYNTAX IS:

UPDATE TABLE_NAME
SET FIRST_NAME='AJAY', SALARY=SALARY+5000, EMPLOYEE_ID=22
WHERE EMPLOYEE_ID=101;

*** COMMIT ***

----WHEN YOU DO COMMIT THEN IT WILL SAVE PERMANENTLY.
----AFTER COMMIT SAVEPOINT AND ROLLBACK DOES NOT WORK.

>>> WHO IS MORE FASTER COMMIT AND ROLLBACK?

COMMIT T1;
COMMIT EMPLOYEES;
COMMIT T2;
COMMIT CRICKET_DATA;

*** DELETE ***

----IT REMOVE THE DATA FROM THE TABLE.
----WITHOUT WHERE ALL DATA DELETED.

>>> DIFFERENCE BETWEEN DELETE AND TRUNCATE?

>>> SYNTAX OF DELETE. CAN WE USE DELETE COMMAND WITHOUT WHERE CLAUSE?

DELETE FROM EMPLOYEES1 ...THEN ALL DATA OF EMP TABLE WILL BE DELETED.

DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID=&X1; ...ONLY SPECIFIC ROW DELETED.

 *** SAVEPOINT ***
----IT IS AN POINTER OR MARKER.
----IT USED TO TELL THE COMPILER ROLLBACK THE TRANSACTION TILL THE POINTER.
----AFTER COMMIT SAVEPOINT AND ROLLBACK DOES NOT WORK.
----WE INSERT THE DATA LIKE SAVEPOINT A, SAVEPOINT B, SAVEPOINT C
----IF WE RUN SAVEPOINT A THEN OTHER SAVEPOINT DISCARDED.
----SAVEPOINT WORKS ONLY WITH ROLLBACK.

CREATE TABLE CRICKET_DATA

(JERSEY_NO NUMBER(20) CONSTRAINT JERSEY_ID PRIMARY KEY,
PLAYER_NAME VARCHAR2(20),RUNS NUMBER(20),DATE_ODI DATE,
AGAINST VARCHAR2(20));

CREATE OR REPLACE PROCEDURE CRI_ODI(N1 NUMBER,N2 VARCHAR2,N3 NUMBER,N4 DATE,N5

VARCHAR)
IS
BEGIN
INSERT INTO CRICKET_DATA VALUES(N1,N2,N3,N4,N5);
DBMS_OUTPUT.PUT_LINE('DATA INSERTED');
END;

SAVEPOINT A;
SAVEPOINT B;
SAVEPOINT C;
ROLLBACK T0 SAVEPOINT A;

SET SERVEROUTPUT ON
EXECUTE CRI_ODI(&X1,'&X2',&X3,'&X4','&X5');
SELECT * FROM CRICKET_DATA;
DELETE CRICKET_DATA;

CREATE TABLE D_1 (NUM_1 NUMBER(20));

INSERT INTO D_1 VALUES (&X1);
SELECT * FROM D_1
SAVEPOINT L;
SAVEPOINT K;
SAVEPOINT J;
ROLLBACK TO J;
ROLLBACK TO J

CREATE TABLE SAVE

(SAVE_1 NUMBER(20));
INSERT INTO SAVE VALUES(&X1);
SAVEPOINT C11;
SAVEPOINT C12;
SELECT * FROM SAVE;
ROLLBACK TO C12;
COMMIT;

*** TRUNCATE ***

----REMOVE THE DATA FROM THE TABLE.
----IT IS AUTOSAVE
----DDL COMMAND
----ROLLBACK NOT POSIBLE
----REMOVING ROWS FASTER THAN DELETE COMMAND.
----SYNTAX: TRUNCATE TABLE TABLE_NAME;
----REMOVING ROWS WITH THE HELP OF TRUNCATE IS MUCH MORE FASTER THAN DELETE.
----USE TO REMOVE THE ENTIRE ROWS FROM THE TABLE.
----IT IS DELELTING THE ENTIRE DATA WITH IN SHORT TIME AND WITH HIGH PERFORMANCE
----AS COMPARED TO DELETE
----DELETE IS NOT AUTOSAVE
----TRUNCATE IS AUTOCOMMIT
----IT WILL DELETE ONLY THE DATA (ENTIRE DATA) NOT STRUCTURE OF THE TABLE.
----DELETE HAVE LOW PERFORMACE OR TAKE MUCH MORE TIME TO AS COMPARED TO TRUNCATE
----WE CAN USE FILTER ON DELETE UT WE CAN NOT USE FILTER ON TRUNCATE
----DROP IS REMOVE THE TABLE STRUCTURE AS WELL AS TABLE DATA
----IT IS VERY MUCH FASTER THAN THE ALL THE COMMAND.
----IT IS ALSO AUTO SAVE BCZ OF THE DDL COMMAND.

>>> WHAT IS TRUNCATE?

>>> DIFFERENCE BETWEEN DELETE AND TRUNCATE, DROP?

TRUNCATE TABLE T1;

SELECT * FROM T1;

CREATE TABLE TEST_1 AS SELECT * FROM EMPLOYEEES;

TRUNCATE
SELECT * FROM OM;
SELECT * FROM OM_RELATIVES;
TRUNCATE TABLE OM_RELATIVES;

*** DROP ***

----ROMOVE STRUCTURE OF THE TABLE PLUS DATA OF THE TABLE
----IT IS AUTOSAVE
----DDL COMMAND
----ROLLBACK NOT POSIBLE.

>>> DROP TRUNCATE DIFFERENCE?

>>> DISABLE AND DROP DIFFERENCE?

>>> WHAT IS MEAN BY KEY?

----A KEY IS AN ATTRIBUTE AND SET OF ATTRIBUTE THAT UNIQUELY IDENTIFY
----EACH RECORD OF RELATION IN THE TABLE

*** COMPOSITE PRIMARY KEY ***

----DEFINE PRIMARY KEY MORE THAN ONE COLUMN / ATTRIBUTE KNOWN AS COMPOSITE PRIAMRY
KEY.

>>> HOW TO IMPLEMENT COMPOSITE PRIMARY KEY?

>>> WHAT IS COMPOSITE PRIMARY KEY?

CREATE TABLE ACCT45

(A_NO NUMBER(10),PAN_NO NUMBER(10),A_NAME VARCHAR2(20),
CONSTRAINT A_NO_PAN_NO PRIMARY KEY(A_NO,PAN_NO));

*** COMPOSITE FOREIGN KEY ***

----DEFINE FOREIGN KEY MORE THAN ONE COLUMN KNOWN AS COMPOSITE FOREIGN KEY.

CREATE TABLE CHILD45

(A_NO NUMBER(10),PAN_NO NUMBER(10),C_NAME VARCHAR2(20),
CONSTRAINT A_NO_PAN_NO FOREIGN KEY (A_NO,PAN_NO)REFERENCE ACCT45 (A_NO,PAN_NO));

*** DEFAULT ***

----THIS CONSTRAINT IS USED TO APPLY DEFAULT VALUES FOR COLUMN
CREATE TABLE H1 (H_NO NUMBER(10) DEFAULT 0,
F_NAME VARCHAR2(20) DEFAULT 'OM',
T_DATE DATE DEFAULT SYSDATE);

SELECT * FROM H1
INSERT INTO H1 VALUES (DEFAULT,'RAM',DEFAULT)
INSERT INTO H1 (T_DATE) VALUES('15-JUN-2010')

*** DATA TYPE ***

1. CHAR (SIZE)
----IT IS USED FOR TO STORE THE CHARACTER DATA IN THE ROW AND COLUMN.
----THE BASIC DIFFERENCE BETWEEN CHAR AND VARCHAR IS. THE CHAR IS USED FOR FIXED
LENGTH AND VARCHAR NOT HAVE FIXED LENGTH.
----SIZE IS FIXED
----IF WE ADD LESS NO OF VALUES IN THE DATA TYPE THEN BY DEFAULT IT IS ADD NULL AT
NOT GIVEN VALUES.

2. VARCHAR2(SIZE)
----IT IS USED FOR TO STORE THE CHARACTER DATA
----AND THIS IS USED FOR VARIABLE LENGTH
----IT WILL TAKE ONLY GIVEN SIZE ONLY IN HARDISK
----AND ALSO IT WILL TAKE LESS SPACE IN THE HARDISK
----EX. VARCHAR (5,2) IS A 222.22 ONLY
----IF THE GIVEN VALUE IN VARCHAR IN THE DECIMAL IS MORE THAN GIVEN VALUE IS OK.

3. NUMBER ()
----IT IS USED FOR TO STORE THE NUMERIC DATA

4. NUMBER (t_d,p)
----IT IS USED FOR TO SAVE THE NUMBER AS WELL AS DECIMAL VALUE
----LIKE ...NUMBER (5)/ NUMBER (5,2)

5. DATE
----IT IS USED FOR TO SAVE THE DATA AND TIME IN THE DATATYPE

*** LOB VARIABLE LARGE OBJECT TYPES ***

6. LONG
----IT IS FOR THE LONG CHARACTER DATA

7. BLOB
----BINNARY LARGE OBJECT.
----GENRALLY USED TO STORE UNSTRUCTURED
----WE CAN STORE PHOTO, PICTURE, IMAGE, PDF

8. CLOB
----CHARACTER LARGE OBJECT.

9. NCLOB
----NATIONAL CHARACTER LARGE OBJECT.

10. BFILE
----BINARY FILE (MOVIES, RECORD, AUDIO)

*** CONSTRAINT ***

----USES TO APPLY RULES / LIMITATION / RESTRICTION ON DATA WHILE INSERTING INTO THE
TABLE
----IT PREVENT THE DELETION OF DATA OR TABLE
~ NOTE
----CONSTRAINTS APPLY ON THE COLUMN WHILE CREATING THE TABLE
----WE NEED TO MENTION THE NAME OF CONSTRAINTS.
----WE CAN APPLY MORE THAN ONE CONSTRAINTS ON THE SAME COLUMN

CONSTRAINTS TYPES:
1. PRIMARY KEY
2. FOREIGN KEY

1. PRIMARY KEY
----IT WILL ALLOW ONLY DISTICT VALUE OR NOT ALLOW NULL VALUE.
----FOR EX. DEPARTMENT_ID, EMPLOYEE_ID.

2. FOREIGN KEY
----IT WILL ALLOW DUPLICATION AND NULL ALSO.
----BUT VALUES MUST BE FROM THE PRIMARY KEY COLUMN OF THE PARENT TABLE.
----(REFERENTIAL INTEGRATY)

3. NOT NULL
----NOT ALLOW THE NULL VALUE IN THE COLUMN.

4. UNIQUE
----NOT ALLOW THE DUPLICATES.

5. CHECK
----IT ALLOW THE DATA IF IT IS SATISFYING THE CONDITION MENTIONED IN THE CHECK
CONSTRAINTS.

*** PRIMARY KEY ***

CREATE TABLE TEST_6 (EMP_ID NUMBER(2) CONSTRAINT EMP_ID_PRI PRIMARY KEY , EMP_NAME
VARCHAR2 (5));
INSERT INTO TEST_6 VALUES (1, 'AA');
INSERT INTO TEST_6 VALUES (2s, 'BB');
SELECT * FROM TEST_1;
DELETE FROM TEST_1 WHERE (EMP_NAME)='AA';
INSERT INTO TEST_1 VALUES (

*** CHECK CONSTRAINT ***

----HERE WE CAN APPLY RULE BASED ON THE CONDITION.
----EX. SALARY SHOULD BE MIN 5000.

CREATE TABLE TEST_DATA_1 (EMP_NO NUMBER(2), EMP_NAME VARCHAR2(5), SAL NUMBER(5));

INSERT INTO TEST_DATA VALUES (1, 'AA', 1000);
INSERT INTO TEST_DATA VALUES (1,'AA', 6000);
SELECT * FROM TEST_DATA;

CREATE TABEL TEST_DATA_1 (EMP_NO NUMBER(2), EMP_NO

>>> HOW TO DELETE DUPLICATE RECORD IN THE TABLE?

--WE CAN DELETE DUPLICATE RECORD IN FOUR WAYS.
1. USING GROUP BY CLAUSE.
2. USING SELF JOIN.
3. USING COMMON TABLE EXPRESSION.
4. USING RANK FUCNTION.
DELETE FROM T11 WHERE T_ID IN
(SELECT COUNT(*) FROM T11 GROUP BY SALARY
HAVING COUNT(*) > 1);

>>> DISPLAY THE DUPLICATE SALARY FROM DISTINCT SALARY?

SELECT DISTINCT(SALARY) FROM T11
GROUP BY SALARY HAVING COUNT(SALARY) > 1

ROLLBACK T11;
SELECT * FROM T11;

DELETE T_ID.E1
FROM T11 E1, T11 E2
ON (E1.T_ID=E2.T_ID)
AND (E1.T_ID > E2.T_ID);

>>> DISPLAY MONTH WISE JOINING OF EMPLOYEE?

SELECT TO_CHAR(HIRE_DATE,'MON'),COUNT(TO_CHAR(HIRE_DATE,'MON'))
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'MON');

>>> DISPLAY YEAR WIEE JOINING OF EMPLOYEE?

SELECT TO_CHAR(HIRE_DATE,'YYYY'),COUNT(TO_CHAR(HIRE_DATE,'YYYY'))
FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'YYYY') ORDER BY TO_CHAR(HIRE_DATE,'YYYY');

SELECT DISTINCT TO_CHAR(HIRE_DATE,'YYYY') FROM EMPLOYEES

SELECT MIN(SALARY) FROM EMPLOYEES

UNION
SELECT MAX(SALARY) FROM EMPLOYEES

>>> DISPLAY 3RD AND 7TH HIGHEST DATA QUERY?

>>> HOW MUCH CLAUSES IN SQL?

>>> PRECISION AND SCALE?

>>> DELETE DUPLICATE DATA FROM THE TABLE?
>>> DISPLAY LAST THREE ROW FROM THE TABLE?
>>> COPY AND REPLACE DATA FROM T1 AND T2 WITHOUT DUPLICATION ?
>>> KEYS IN DBMS (PK, FK, COMPOSITE KEY, CANDIDATE KEY, SECONDARY KEY, SUPER KEY)
>>> WHAT IS NORMALIZATION AND WHY IS IT NEEDED?
>>> DIFFERENCE BETWEEN GROUP BY AND ORDER BY?
>>> DIFFERENCE BETWEEN CHAR AND VARCHAR?
>>> FIND NtH HIGHEST SALARY IN TABLE?
SELECT SALARY FROM EMPLOYEES WHERE SALARY < (SELECT MAX(SALARY) FROM EMPLOYEES)
>>> DISPLAY LOWEST THREE SALARY USING CORELATED SUBQUERY?
>>> EXAMPLE OF ROWID?
>>> WHAT IS ACID PROPERTY?
>>> DIFFERENCE BETWEEN INNER & OUTER JOIN?
>>> CASE & DECODE?
>>> TYPES OF CASE MANUPULATION?
>>> WHAT IS MEAN BY ACID PROPERTY?
>>> HOW WE CAN APPLY DISTINCT ON MULTIPLE TABLE?
>>> CONCATINATION OPERATOR AND CONCATINATION FUNCTION?
>>> WHAT IS THE USE OF NOT CONDITIONAL OPERATOR?
>>> WHAT IS CURSOR? WHAT IS CURSOR FOR LOOP?
>>> HOW WE CAN COPY DATA FROM ONE TABLE TO ANOTHER?
>>> HOW WE CAN CREATE THE STRUCTURE OR COPY THE STRUCTURE OF TABLE?

---------------------------- END OF BASIC SQL ----------------------------------

--------------------------------ADVANCE SQL------------------------------------

>>> EXPLAIN READ CONSISTENCY?

>>> WITH CLAUSE AND LOGICAL EXAMPLE?
>>> WHAT IS VIEW?

SHARE LOCK
EX
ROW LEVEL

>>> WHY WE CAN NOT DELETE PRIMARY KEY TABLE OR MASTER TABLE OR PARENT TABLE
DIRECTLY?
----WHEN EVER TRYING TO DELETE ANY REFERENCE COLUMN VALUE THEN IT IS NOT POSIBLE
BECAUSE IT IS
----CORRESPONDING VALUES ARE INSIDE THE CHILD TABLE
----BUT IF YOU FORCELY WANT TO DELETE THE REFERENCE COLUMN VALUE THEN IT CAN
POSIBLE
----WITH THE HELP OF ON DELETE CLAUSE.

>>> HOW TO DELETE REFERENCE COLUMN RECORD OF MASTER TABLE?

*** ON DELETE CASCADE ***

----DELETE DATA FROM PARENT AS WELL AS FROM CHILD TABLE
----IT WILL DELETE DATA FROM BOTH THE TABLE
----IF THERE IS DEPENDENCY AND WANTS TO DELETE DATA FROM PARENT THEN USE ON DELETE
CASCADE
----IT WILL DELETE DATA FROM PARENT AS WELL AS FROM CHILD ALSO.

CREATE TABLE Y1 (PNO NUMBER(10) PRIMARY KEY,

PNAME VARCHAR2(20)); ----
PARENT TABLE

CREATE TABLE P2 (Q_NO NUMBER(10),Q_NAME VARCHAR2(10),

PNO NUMBER,
CONSTRAINT PNO FOREIGN KEY (PNO) REFERENCES Y1 (PNO) ON DELETE CASCADE); ----
CHILD TABLE

INSERT INTO Y1 VALUES(&X1,'&X2');

INSERT INTO P2 VALUES(&A1,'&A2',&A3);

SELECT * FROM Y1;

SELECT * FROM P2;

DELETE FROM Y1 WHERE PNO=3;

*** ON DELETE SET NULL ***

----IT WILL DELETE DATA FROM PARENT TABLE BUT PUT NULL VALUE TO CHILD TABLE
----IF PARENT KEY TABLE SOME DEPARTMENT DELETE THEN IT WILL PUT NULL IN CHILD TABLE
----IF WE WANTS KEEP CHILD RECORDS AS IT IS THEN USE ON DELETE SET NULL
----IT JUST REMOVE DEPENDENCY NOT REMOVE RECORDS.

CREATE TABLE R11(RNO NUMBER(10)CONSTRAINT R_NO PRIMARY KEY,

R_NAME VARCHAR2(20)); ---PARENT TABLE

CREATE TABLE U11(UNO NUMBER(10),UNAME VARCHAR2(20),RNO NUMBER(10),

CONSTRAINT RNO_FK
FOREIGN KEY (RNO) REFERENCES R11(RNO) ON DELETE SET NULL); ---CHILD TABLE

INSERT INTO R11 VALUES (&X1,'&X2');

INSERT INTO U11 VALUES (&A1,'&A2',&A3);

SELECT * FROM R11;

SELECT * FROM U11;

DELETE FROM R11 WHERE RNO=4;

*** DATA CONSISTENCY LOCK ***

----LOCK USING DML COMMANDS
----UNLOCK USING TCL COMMANDS
...USING COMMIT AND ROLLBACK.

*** DATA CONSISTENCY ***

----WITH COMMIT AND ROLLBACK YOU CAN ENSURE DATA CONSISTENCY
----AN AUTOMATIC COMMIT OCCURS WHEN

TWO TYPES OF LOCK

1. SHARED LOCK
2. ROW EXCLUSIVE LOCK

*** IMPLICIT TRANSACTION PROCESSING ***

----IF SYSTEM FAILURE THEN ORACLE OR SERVER ROLL BACK THE ACTION AT THE LAST
COMMIT.
----IN THIS CASE ORACLE PERFORM ROLLBACK UP TO LAST COMMIT.

*** STATE OF DATA BEFORE COMMIT OR ROLLBACK

----CHANGED (INSERT /UPDATA /DELETE) DATA OR NEW DATA NOT VISIBLE TO OTHER USER.
----ROWS ARE LOCKED WHEN YOU PERFORM UPDATE IN THE TABLE. AND THE OTHER USER CAN
NOT CHANGE THE ROWS.

*** STATE OF DATA AFTER COMMIT OR ROLLBACK

----NOW (INSERT /UPDATA /DELETE) CHANGES ARE VISIBLE TO OTHER USERS
----ROWS ARE UNLOCKED ...NOW THEIR USER CAN CHANGE THAT ROWS.

READ CONSISTENCY : CONSISTENT VIEW OF DATA

READER : SELECT
WRITER : INSERT /UPDATE / DELETE

*** DATABASE OBJECTS ***

1. TABLE
----BASIC UNIT TO STORE THE DATA IN THE DATABASE.
----IT IS USED TO STORE INFORMATION.

2. VIEW
----LOGICAL SUBSET OF DATA FROM ONE OR MORE TABLES /MIRROR IMAGE OF TABLE.
----ADVANTAE OF THIS VIEW IS TO PROTECT THE DATABASE.
----LOGICALLY REPRESENT SUBSET OF DATA FROM ONE OR MORE TABLE

3. INDEX
----IT IS USED TO IMPROVE THE PERFORMANCE OF THE QUERY.

4. SEQUENCE
----GENRATE NUMERIC VALUES
----USED TO GENRATE PRIMARY KEY VALUES.

5. SYNONYM
----GIVES THE ALTERNATIVE NAME TO DATABASE OBJECT.
----IT IS USED TO GIVE ALTERNATE NAME TO OBJECT.

*** TABLE ***

----IT IS TYPE OF DATABASE OBJECT USED TO STORE THE DATA IN THE DATABASE IN FORM OF
COLUMN AND ROWS.
----CREATE TABLE TABLE_NAME (COL_1 DATA TYPE(SIZE),COL_2 DATA TYPE (SIZE),COL_3
DATA TYPE(SIZE))
----DDL IS AUTO SAVE COMMAND.
----INT : IT IS USED FOR NUMERIC DATA. NOT INCLUDE DECIMAL NO IN THIS.
----VARCHAR : VARIABLE LENGHT OF CHARACTER, IT ALLOCATE ONLY REQUIRED MEMORY.
----NUMBER : IT IS USED FOR GENRAL NUMBER, FOR EX, NUMBERS AND DECIMAL NUMBERS ALSO
INCLUDE IN THIS.
----FLOAT : IT IS USED FOR DECIMAL NO.

1.
CREATE TABLE OM_RELATIVES (NAME_OF_RELATIVES CHAR(20), CONTACT NUMBER(10));
DESC OM_RELATIVES;
INSERT INTO OM_RELATIVES (NAME_OF_RELATIVES ,CONTACT ) VALUES ('ALKA',9403485209);
INSERT INTO OM_RELATIVES (NAME_OF_RELATIVES,CONTACT) VALUES ('ULKA', 7775881196);
SELECT * FROM OM_RELATIVES;

2.
CREATE TABLE DATA_TEST (SR_NO NUMBER(5), NAME CHAR(5));
DESC DATA_TEST;
INSERT INTO DATA_TEST (SR_NO, NAME) VALUES (1, 'AAA')
SELECT * FROM DATA_TEST

3.
CREATE TABLE DATA_TEST_1 (SR_NO NUMBER(5), NAME VARCHAR(5));
DESC DATA_TEST_1

4.
CREATE TABLE EMP_DETAIL (ID INT, NAME VARCHAR2(20),SALARY NUMBER(10));
DESC EMP;

5.
CREATE TABLE STUDENT_TAB
(S_NO NUMBER, S_NAME VARCHAR2(100), S_MARK NUMBER, S_RESULT VARCHAR2(1));

INSERT INTO STUDENT_TAB VALUES(1,'ONKAR',85,'P');

INSERT INTO STUDENT_TAB VALUES(2,'NIKHIL',30,'F');
INSERT INTO STUDENT_TAB VALUES(3,'SURAJ',95,'P');
INSERT INTO STUDENT_TAB VALUES(4,'NAIM',98,'P');
INSERT INTO STUDENT_TAB VALUES(5,'ASHITOSH',58,'P');

CREATE TABLE DATA_TEST_2 (SR_NO NUMBER(5),NAME VARCHAR(5)); ... AND ALWAYS REMEMBER
THE SIZE OF COLUMN.
INSERT INTO DATA_TEST_2 VALUES (1.1, 'ADI') ; ...VALUE ADDED BUT NOT SHOWING THE
DECIMAL VALUE IF YOU ARE NOT MENTIONING THE IN THE VARCHAR (5,0)
INSERT INTO DATA_TEST_2 VALUES (1, 'ADI') ;
INSERT INTO DATA_TEST_2 VALUES (2, 'YESH');
SELECT * FROM DATA_TEST_2;

CREATE TABLE DATA_TEST_3 (SR_NO NUMBER(5),NAME VARCHAR(5,2));

>>> DIFFERENCE BETWEEN TABLE AND VIEW?

*** VIEW ***

----VIEW IS A LOGICAL TABLE BASED ON ONE OR MORE TABLE OR VIEWS.
----THE TABLE UPON WHICH A VIEW IS BASE ARE CALLED BASE TABLE.
----VIEW NOT OCCUPY ANY KIND OF MEMORY
----VIEW IS FASTER THAN TABLE
----IF WE ADD SOME DATA IN VIEW THEN IT WILL ALSO ADD IN ORIGNAL TABLE.
----IF WE ADD SOME DATA IN TABLE THEN IT WILL ALSO ADD IN VIEW.
----VIRTUAL TABLE.
----IT IS LOGICAL SUBSET FROM ONE OR MORE THAN ONE BASE TABLE
----VIEWS ARE VIRTUAL DATA OR MIRROR DATA FROM PHYSICAL TABLE
----LOGICAL SUBSET OR COMBINATION OF DATA FROM ONE TABLE OR MANY TABLES.
----CREATE ON TABLE OR CREATED FOR SELECTED STATEMENT
----VIEW DON'T HAVE ITS OWN DATA.
----IT IS ONE WINDOW WHICH CAN SEE THE DATA AND LIMITED CHANGE THE DATA
----IT IS JUST A MIRROR IMAGE OF TABLE DATA
----WE CAN MAKE MULTIPLE VIEWS OF ANY OF THE DATA
----WE CAN MODIFY THE LIMITED DATA.

ADVANTAGES:
1. TO RESTRICT ACCESS TO THE DATA.
2. TO MAKE COMPLEX QUERY EASY
3. TO PROVIDE DATA INDEPENDECIES
4. TO DISPLAY THE DIFFERENT VIEWS OF SAME DATA

$ FEATURES
----NUMBER OF TABLES
----CONTAINS FUNCTION
----CONTAINS GROUPPING REALTED DATA
----DML OPERATION

NOTE :- IT DOES NOT CONTAIN ANY DATA ITSELF.

VIEWS ARE USED FOR SECURITY PURPOSE BCZ THEY PROVIDE ENCAPSULATION OF THE
NAME OF THE TABLE
DATA IS IN THE VIRTUAL TABLE NOT STORED PERMANANTELY
VIEW DISPLAY ONLY SELECTED DATA
VIEW IS ALSO KNOWN AS "STORED SELECT STATEMENT".

SYNTAX:- CREATE VIEW VIEW_NAME AS SELECT * FROM TABLE_NAME WHERE CONDITION

CREATE VIEW EMP_V AS SELECT EMPLOYEE_ID,SALARY, DEPARTMENT_ID FROM EMPLOYEES
WHERE JOB_ID= 'AD_PRES'

*** TYPES OF VIEW ***

A. SIMPLE VIEW.
B. COMPLEX VIEW.
C. FORCE VIEW.

--CONSTRAINT
D. READ ONLY VIEW.
E. WITH CHECK OPTION VIEW.

A. SIMPLE VIEW.
----HERE WE USE ONLY ONE TABLE TO MAKE VIEW.
----THE VIEW WHICH CONTAIN A SUB-QUERY THAT RETRIVES
----FROM ONE BASE TABLE OR VIEW IS CALLED AS SIMPLE VIEW
----ONLY ONE TABLE FOR VIEW
----NO CONTAIN FUNCTION
----NO GROUPPING RELATED DATA
----WE CAN PERFORM DML OPERATION

SYNTAX:- CREATE VIEW VIEW_NAME AS SELECT COL_NAME,COL_NAME FROM TABLE_NAME WHERE ID

> 118;
CREATE VIEW VIEW_NAME AS SELECT * FROM TABLE_NAME;

CREATE VIEW EMP_4 AS SELECT * FROM EMPLOYEES

SELECT * FROM EMP_4;

----IF WE WANT TO MAKE NEW VIEW ON EXISTING VIEW NAME

CREATE OR REPLACE VIEW VIEW_NAME AS SELECT * FROM TABLE_NAME WHERE COL_NAME=101;

----IF WE APPLY WHERE CONDITION ON VIEW THEN ONLY WHERE CONDITION DATA IS INSERTED
----IF IN CASE WE INSERT OTHER DATA ON THIS VIEW THEN IT IS INSERT ON TABLE BUT NOT
INSERTED ON VIEW.

B. COMPLEX VIEW.
----HERE WE USE TWO OR MORE TABLE TO MAKE A VIEW
----COMPLEX VIEW CONTAIN SUB-QUERIES WHERE SUB-QUERIES CONTAIN QUERY.
----THE VIEW WHICH IS CONSTRUCTED WITH THE HELP OF MORE THAN ONE BASE TABLE
----THEN IT IS CALLED COMPLEX VIEW.
----ONR OR MORE TABLE FOR VIEW
----CONTAINS FUNCTION YES
----CONTAINS GROUPPING DATA
----WE CAN NOT USE DML OPERATION ON COMPLEX VIEW

SYNTAX:- CREATE VIEW VIEW_NAME AS SELECT [Link],[Link],[Link] FROM STUDENTS S,

MARKS M
WHERE [Link]=[Link];

CREATE VIEW EMP_DEPT AS

SELECT E.EMPLOYEE_ID,E.FIRST_NAME,D.DEPARTMENT_ID FROM EMPLOYEES E , DEPARTMENTS D
WHERE E.DEPARTMENT_ID=D.DEPARTMENT_ID;

SELECT * FROM EMP_DEPT;

CREATE VIEW VIEW_NAME AS SELECT * FROM TABLE_NAME_1 UNION SELECT * FROM

TABLE_NAME_2;

DROP VIEW VIEW_NAME;

C. FORCE VIEW.
----HERE TABLE IS NOT AVAILABLE BUT FORCEFULLY WE MAKE VIEW ON THIS TABLE NAME.
----FORCE VIEW IS USED BASICALLY FOR THE SITUATION WHEN WE CREATE A VIEW USING A
TABLE
----BUT THE TABLE IS NOT EXIST AT THAT TIME WE USE FORCE VIEW.
SYNTAX:- CREATE FORCE VIEW VIEW_NAME AS SELECT * FROM TABLE_NAME;

CREATE FORCE VIEW NEW_TABLE AS SELECT * FROM N1;

CREATE FORCE VIEW N1 AS SELECT * FROM N2;

D. READ ONLY VIEW.

----WE CAN ONLY READ THE DATA WE CAN NOT PERFORM ANY KIND OF WRITE OPTION.
----IT ONLY ALLOW TO PERFORM READ OPERATION ON BASE TABLE.
----IT RESTRICT TO PERFORM WRITE OPERATION
----BUT IF WE INSERT SOME RECORD ON TABLE THEN IT IS VISIBLE IN VIEW.
SYNTAX:- CREATE VIEW VIEW_NAME AS SELECT * FROM TABLE_NAME WITH READ ONLY.

E. WITH CHECK OPTION VIEW.

----IN WITH CHECK OPTION VIEW IF THE GIVEN CONDITION SATISFY IN SELECTED COLUMN
----THEN ALLOW TO PERFORM WRITE OPERATION OTHERWISE RESTRICTED.
----WE CAN PERFORM THE DML OPERATION ON ONLY RECORDS WHICH ARE A PART OF THE VIEW.
----SYNTAX: CREATE OR REPLACE VIEW VIEW_NAME AS SELECT .......WITH CHECK OPTION
----SYNTAX: CREATE OR REPLACE VIEW VIEW_NAME AS SELECT .......WITH CHECK OPTION
CONSTRAINT
----IF WE ARE INSERTING 30 DEPARTMENT ID THEN IT WILL ALLOW ONLY 30 DEPARTMENT ID
----TO INSERT IN THE VIEW;

SYNTAX:- CREATE VIEW VIEW_NAME AS SELECT * FROM TABLE_NAME WHERE CONDITION WITH
CHECK OPTIONS.
CREATE TABLE EMP_V AS SELECT * FROM EMPLOYEES;
CREATE OR REPLACE VIEW EMP_V AS SELECT * FROM EMPLOYEE WHERE DEPARTMENT_ID=30
INSERT INTO EMP_V (EMPNO, ENAME , DEPTNO ) VALUES (999, 'AAA' , 30)

*** BENFITS OF VIEWS ***

1. VIEWS CAN HIDE COMPLEXITY (JOINS)
2. VIEWS CAN BE USED AS A SECURITY MECHANISM

NOTE:- THERE IS NO RECORD IN THE VIEW, IT ONLY HOLDS THE DEFINATION OF TABLE AND
FETCHES DATA
FROM TABLE AND SHOWS IT.
VIEWS ARE STORED AS PERMANENT QUERY OBJECT IN DB.

>>> DIFFERENCE BETWEEN SIMPLE VIEW AND COMPLEX VIEW?

SIMPLE VIEW COMPLEX VIEW
1. IT IS CREATED FROM ONLY ONE TABLE. IT IS CREATED FROM TWO OR MORE
TABLE.
2. GROUP FUNCTION, GROUP BY CLAUSE NOT ALLOWED. ALLOWED
3. WE CAN RUN DML COMMAND IN SIMPLE. NOT ALWAYS

----VIEWS ARE NOT DEPEND ON OTHER TABLE

----VIEWS ARE DEPENDENT ON TABLE.

*** MATERLIZED VIEW ***

----MUST BASE TABLE NEED TO CREATE MATERLIZED VIEW.
----IT INCREASE THE PERFORMANCE OF THE QUERY THEY SHOULD BE USED FOR REPORTING
----INSETED OF A TABLE FOR FASTER EXEUTION.
----MATERLIZED VIEW OCCUPY SPACE / ALLOCATE MEMORY.
----IS SAME AS VIEW BUT ONLY IT IS USES SPACE ON HARD DISK TO STORE THE DATA.
----WHEN WE USE THIS THEN IT WILL CREATE VIEW BUT IT WILL ONLY USE SPACE ON THE
HARD DISK
----THIS VIEW STORE THE DATA IN MEMORY OR ON DISK
----DATA ACCESS SPEED OR RESPONSE SPEED IS FAST AS COMPARED TO NORMAL VIEW
----IF WE CHANGED ON MAIN TABLE THEN WE HAVE TO REFRESH THE DATA ON MATERILIZED
VIEW THEN IT WILL ADD ON THE MATERILIZED VIEW.
----WE CAN CREATE THE M-VIEWS ON SUCH TABLE WHERE DATA IS NOT UPDATED FREQUNTLY.
----WE NEED DATA FOR FREQUENTLY BASIS.
----WHEN DATA IS UPDATED ON MONTHLY BASIS THEN WE WILL USE THIS MATRILIZED VIEW.
----SYNTAX: CREATE OR REPLACE MATERILIZED VIEW VIEW_NAME AS SELECT .....;
----SYNTAX: TO REFRESH THE M-VIEWS; ...IT IS USED FOR THE RERESH THE DATA IN
MATRILIZED VIEW;

BEGIN
DBMS_SNAPSHOT.REFRESH(M-VIEW_NAME);
END;

BEGIN
DBMS_MVIEW.REFRESH('MVIEW_NAME')
END;

CREATE OR REPLACE VIEW EMP_V AS SELECT * FROM EMPLOYEES;

CREATE OR REPLACE VIEW MATERILIZED VIEW EMP_V AS SELECT * FROM EMPLOYEES WHERE
DEPTARTMENT_ID=30;
SELECT * FROM EMP_V;

CREATE MATERIALIZED VIEW EMP_11 AS SELECT * FROM EMPLOYEES;

*** REMOVE THE VIEW ***

----USE TO REMOVE THE VIEW FROM DATABASE
----SYNTAX: DROP VIEW VIEW_NAME

DROP VIEW EMP_V;

SELECT * FORM EMPLOYEES;
SELECT * FROM EMPLOYEES;

*** DML OPERATION ON VIEW ***

1. VIEW MUST BE SIMPLE
2. WE CAN PERFORM DML ON BASE TABLE AS WELL AS VIEW
3. CHANGES WILL REFLECT IN BOTH VIEW AS WELL AS BASE TABLE.

*** DENYING DML OPERATIONS ***

----USE TO CREATE A SUCH VIEW THROUGH WHICH WE CAN SEE THE DATA
----WE CAN'T PERFORM ANY DML THROUGH VIEW
----SYNTAX: CREATE OR REPLACE VIEW VIEW_NAME AS SELECT ....WITH READ ONLY;
----CAN NOT PERFORM A DML OPERATION ON A RED ONLY VIEW.

CREATE OR REPLACE VIEW EMP_V_READ AS SELECT * FROM EMPLOYEES WITH READ ONLY;
SELECT * FROM EMP_V_READ;

*** MODIFYING VIEW ***

----NO NEED TO ALTER THE VIEW OR DROP THE VIEW
----SYNTAX: CREATE OR REPLACE VIEW VIEW_NAME AS SELECT .....)
CREATE VIEW EMP_10 AS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID =10;
SELECT * FROM EMP_10;

CREATE OR REPLACE VIEW EMP_10 AS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN

(10,30);
SELECT * FROM EMP_10;

CREATE OR REPLACE VIEW EMP_10 AS SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID IN

(30);
SELECT * FROM EMP_10;

*** INDEX ***

----INDEX ARE USED TO RETRIVE DATA FROM THE DATABASE MOVE QUICKLY THAN OTHERWISE
----USERS CAN NOT SEE THE INDEXES.
----USED TO SPEED UP SEARCHES / QUERIES
----UPDATING A TABLE WITH INDEXES TAKES MORE TIME THAN UPDATING A TABLE WITHOUT
INDEX
----INDEXING IS USED TO OPTIMIZE THE PERFORMANCE OF
----DATABASE BY MINIMIZING THE NO OF DISK ACCESS REQUIRED WHEN A QUERY IS
PROCESSED.
----IT IS USED WHEN TABLE IS TOO LARGE.
----IT IS USED TO INCREASE PERFORMANCE,OR RETRIVAL SPEED OF SQL QUERIES.
----INDEX IS A DATABASE OBJECT THAT CONTAINS ENTRY FOR THE EACH VALUE
----THAT PRESENT IN THE INDEXED COLUMN OF TABLE.

SYNTAX:- CREATE INDEX INDEX_NAME ON TABLE_NAME (COL_NAME,COL_NAME);

>>> HOW TO FIND THE LIST OF INDEXES FOR GIVEN TABLE?

>>> EXPLAIN B-TREE INDEX?
>>> EXPLAIN BITMAP INDEX?
>>> EXPLAIN FUNCTION BASED INDEX?
>>> EXPLAIN REVERSE KEY INDEX?
>>> WHEN TO CHOOSE WHAT TYPE OF INDEX?
>>> HOW TO KNOW INDEX IS BEING USED?
>>> HOW TO MONITER INDEX USAGE?
>>> WHAT ARE THE BENEFITS / DRAWBACK OF INDEX?

>>>HOW TO CHOOSE COLUMN WHEN CREATING INDEX?

----COLUMN THAT APPEAR IN WHERE CLAUSE OR JOIN CONDITION.
----INDEX SHOULD BE AVOIDED IN TABLE THAT HAVE FREQUENT, LARGE UPDATES OR INSERT
OPERATION

NOTE:-
----DO NOT GIVE INDEX ON ALL COLUMN OF TABLE BCZ IT DECREASE PERFORMANCE.
----AND IT ALLOCATES MEMORY FOR THAT.
----WHEN TABLE UPDATE FREQUENTLY DO NOT FIVE INDEX.

----ADVANTAGES:
1. TO SPEED UP THE DATA RETRIVAL OR SPEED FETCHING.
2. USED & MAINTAINED BY ORACLE SEVER AUTOMATICALLY.

ADVANTAGE:
----WHERE WE CAN CREATE INDEX:
1. INDEX IS CREATED COLUMNS WHICH ARE FREQUENTLY USED IN WHERE CLAUSE.
2. WHERE WE WANT TO FETCH VERY LESS DATA FROM VERY HUGE DATA
 *** TYPES ***
1. CLUSTERED INDEX / UNIQUE INDEX / B-TREE
2. NON CLUSTER INDEX / BITMAP / NON-UNIQUE INDEX

>>> WHY INDEX?

1. CLUSTERED INDEX / UNIQUE INDEX / B-TREE

----IT IS AUTOMATICALLY CREATED WHEN WE DEFINE PRIMARY KEY OR UNIQUE KEY ON COLUMN.
----WHEN WE CREATE PRIMARY OR UNIQUE CONSTRAINTS ON COLUMN THEN THIS INDEX
AUTOMATICALLY CREATE
----COLUMN HAS UNIQUE VALUES (HIGH CARDINALITY)
----NAME OF INDEX IS SAME AS THE NAME OF CONSTRAINT.

2. NON CLUSTER INDEX / BITMAP / NON-UNIQUE INDEX

----IT NEEDS TO DEFINE EXPLICITLY OR MANUALLY.
----WE CAN NOT RETRIVE INDEX INFORMATION LIKE VIEW.
----USER CREATE THIS INDEX BY USING CREATE STATEMENT ON COLUMN WHICH HAVING MAY BE
LOTS OF DUPLICATES VALUES
----LOW CARDINALITY.
----SYNTAX: CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME);

>>> HOW TO CHECK INDEX?

----BY CHECKING PERFORMANCE TIME TO RETRIVE DATA.
FOR EX, SELECT * FROM EMPLOYEES WHERE FIRST_NAME='Lex';
----BEFORE CREATING INDEX IT WAS TOOK 10 MIN TO RETRIVE DATA FROM DB.
----AFTER CREATING INDEX IT TAKE ONLY 10 SEC TO RETRIVE DATA FROM DB.
----SO BY CHECKING PERFORMANCE TIME WE CAN DECIDE WHETHER INDEX IS USED OR NOT.

DROP INDEX INDEX_NAME;

----IT WILL DROP INDEX.

CREATE INDEX_TYPE INDEX INDEX_NAME ON TABLE_NAME (COL_NAME)

CREATE INDEX NEW_INDEX ON T4 (CNO);
SELECT * FROM T4

CREATE TABLE TEST_2 (SR_NO NUMBER(1));

SELECT * FROM EMPLOYEES;
CREATE INDEX INDEX_NAME ON TABLE_NAME (COL_NAME)
CREATE INDEX INDEX SR_NO_IND ON TEST_1 (SR_NO)

DROP INDEX INDEX_NAME;

*** SEQUENCE ***

----IT IS USED TO LOAD DATA
----IT IS MOSTLY USED IN PRIMARY KEY AND UNIQUE KEY.
----FOR EACH NEW TABLE, CREATE NEW SEQUENCE.

CREATE SEQUENCE SEQUENCE_NAME

START WITH INITIAL VALUE
INCREMENT BY INCREMENT VALUE
MIN VALUE MINIMUM VALUE
MAX VALUE MAXIMUM VALUE
CYCLE/ NO CYCLE

CREATE SEQUENCE T47

INCREMENT BY 1
 START WITH 1
MAXVALUE 1000
NOCYCLE
CACHE 10;

CREATE TABLE T567 (T6 NUMBER(30),NAME VARCHAR2(30));

INSERT INTO T567 VALUES ([Link],'&X2')
SELECT * FROM T567
SELECT * FROM T4;

CREATE SEQUENCE R123

INCREMENT BY 1
START WITH 1
MAX VALUE 100
MIN VALUE 0
CYCLE;

CREATE SEQUENCE SEQ_DUMMY

START WITH 10
INCREMENT BY 10
MAXVALUE 50
CYCLE
CACHE 2;

CREATE SEQUENCE SEQ_DUMMY_12

START WITH 10
INCREMENT BY 10
MIN VALUE 10
MAXVALUE 50
CYCLE
CACHE 2;

SELECT DEPARTMENT_ID_SEQ.NEXTVAL FROM DUAL;

SELECT DEPARTMENT_ID_SEQ.CURRVAL FROM DUAL;

>>> WHAT IS SEQUENCE?

>>> CACHE IN SEQUENCE?

*** PSEUDO COLUMN OF SEQUENCE ***

1. NEXTVAL: IT SHOWS NEXT VALUE OF COLUMN.

2. CURRVAL: IT SHOWS CURRENT VALUE OF COLUMN.

*** DRAWABCK OF SEQUENCE ***

1. SEQUENCE DOES NOT ROLLBACK.
2. IF WE ARE USING SAME SEQUENCE FOR DIFFERENT TABLE OR ANOTHER TABLE THEN IT
STARTS FROM
CURRENT VALUE. FOR THIS WE HAVE TO CREATE A NEW SEQUENCE OR NEED TO DROP THE
PREVIOUS SEQUENCE.

*** MODIFYING SEQUENCE ***

----SYNTAX: ALTER SEQUENCE SEQ_NAME .....
----NOTE: CANNOT ALTER STARTING SEQUENCE NUMBER.

ALTER SEQUENCE DEPARTMENT_ID_SEQ START WITH 30;

*** USE OF CYCLE ***

----IF YOUR SEQUENCE VALUE REACHES TO MAX VALUES THEN IT WILL AGAIN START WITH
START VALUE
----( BUT INCASE OF NOCYCLE IT WILL THROW THE ERROR)
----WITHOUT CYCLE.
----IT WILL GIVE THE SEQUENCE UPTO 10 VALUE AFTER THAT IT WILL SHOW THE RESULT
ERROR AT THE RESULT.

CREATE SEQUENCE AA_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 10 NOCYCLE NOCACHE;

----WITH CYCLE OPTION.

----IT WILL GIVE THE RESULT UPTO 10 IN THE SEQUENCE AFTER THE 10 IT WILL REPEAT THE
VALUE LIKE A CYCLE.

CREATE SEQUENCE AA_SEQ INCREMENT BY 1 START WITH 1 MAXVALUE 10 CYCLE NOCYCLE;

*** USE OF CACHE ***

----INCASE OF CACHE SOME NUMBER ARE ALREADY PRESENT IN BUFFER OR CACHE MEMORY
----SO THAT ACCESSING IS VERY FAST OR INCREASES THE PERFORMANCE

*** SYNONYM ***

----IT IS USED TO GIVE TEMPORARY NAME TO THE TABLE.
----USE OF THIS DATABSE OBJECT
----IT IS ONE DATABASE OBJECT WHICH CREATE ALTERNATIVE NAME FOR DATABASE OBJECTS.
----SYNTAX TO CREATE SYNONYM: CREATE SYNONYM SYNONYM_NAME FOR OBJECT_NAME.
----SYNTAX TO REMOVE SYNONYM: DROP SYNONY SYNONYM_NAME;
SYNTAX:- CREATE SYNONYM SYN_NAME FOR TABLE_NAME

CREATE SYNONYM DUMY_NAME FOR EMPLOYEES

SELECT * FROM DUMY_NAME;
DROP SYNONYM DUMY_NAME;

CREATE TABLE T3 (SR_NO NUMBER(2));

SELECT * FROM T3;
CREATE SYNONYM T1_NEW FOR T3;
SELECT * FROM T1_NEW;
DROP SYNONYM T1_NEW;

*** DATA DICTIONARY VIEW ***

----DATA DICTIONARY VIEW IS USED TO MANAGE INFO OF SCHEMA OBJECT.
----TAB IS DATA DICTIONARY VIEW.
----IT IS A ONE VIEW.
----WHICH SHOW THE INFORMATION ABOUT SCHEMA OBJECTS FOR USER ALL AND DBA AND V$.

A. USER:- CURRENTLY LOGIN USER.

B. ALL:- ALL USER OF SYSTEM DEVELOPERS.
C. DBA:- ADMINISTRATOR

1. USER: WHAT IS IN YOUR SCHEMA / WHAT YOU OWN.

2. ALL: EXPANDED USER VIEW / WHAT YOU CAN ACCESS.
3. DBA: WHAT IS IN ALL USERS OR ALL THE DATA / SCHEMA.
4. V$: PERFORMANCE RELATED INFO / DATA.

SYNTAX:
SELECT * FROM DICTIONARY;
SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE 'USER%';
SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE 'DBA%';
SELECT * FROM DICTIONARY WHERE TABLE_NAME LIKE 'V$';
SELECT * FROM OBJECT;

1. USER_OBJECT
2. USER_TABLES
3. USER_TAB_COLUMN
4. USER_CONSTRAINTS
5. USER_VIEW
6. ALL_OBJECTS
7. ALL_TABLES DBA_TABLES

1. TABLE:- IF WE WANT TO SEE ALL TABLES CREATED BY CURRENTLY LOGGING USER

SELECT * FROM ALL_TABLES
SELECT * FROM USER_TABLES
SELECT * FROM DBA_TABLES;

SELECT * FROM USER_TAB_COLUMNS

SELECT * FROM ALL_TAB_COLUMNS;
SELECT * FROM DBA_TAB_COLUMNS;

2. VIEWS:-
SELECT * FROM USER_VIEWS;
SELECT * FROM ALL_VIEWS;
SELECT * FROM DBA_VIEWS;

3. SEQUENCE:-
SELECT * FROM USER_SEQUENCES
SELECT * FROM ALL_SEQUENCES;
SELECT * FROM DBA_SEQUENCES;

4. INDEX:-
SELECT * FROM USER_INDEXES;
SELECT * FROM ALL_INDEXES;
SELECT * FROM DBA_INDEXES;

5. SYNONYM:-
SELECT * FROM USER_SYNONYMS;
SELECT * FROM ALL_SYNONYMS;
SELECT * FROM DBA_SYNONYMS;

6. CONSTRAINT:-
SELECT * FROM USER_CONSTRAINTS;
SELECT * FROM ALL_CONSTRAINTS;
SELECT * FROM DBA_CONSTRAINTS;

SELECT * FROM USER_CONS_COLUMNS;

SELECT * FROM ALL_CONS_COLUMNS;
SELECT * FROM DBA_CONS_COLUMNS;

SELECT * FROM USER_CONSTRAINTS

WHERE TABLE_NAME='EMPLOYEES'
----WE CAN SEE THE TABLE COLUMN.

THERE ARE TWO TYPES OF DATA DICTIONARY VIEW

1. STATIC
2. DYNAMIC
>>> DIFFERENCE BETWEEN STATIC VIEW AND DYNAMIC VIEW?
STATIC VIEW DYNAMIC VIEW
1. STATIC VIEW STARTS WITH PREFIX USER, DYNAMIC VIEW STARTS WITH PREFIX
ALL, DBA.
2. STATIC VIEW IS NOT REAL TIME DYNAMIC VIEW IS REAL TIME
3. STATIC VIEW GIVEN INFORMATION WHICH DYNAMIC VIEW GIVES INFORMATION OF
ALL CHANGE
IS UPDATED BEFORE 1 HOUR. IMEDIATELY
IT IS MOSTLY USED FOR PERFORMANCE
TUNING
(TO INCREASE THE PERFORMANCE OF
DB WITH SQL QUERIES)

*** DCL COMMAND ***

----IT STANDS FOR DATA CONTROL LANGUAGE.
----IT GIVES PERMISSION TO USER AND TAKE BACK THAT PERMISSION FROM USER
----SQL PROVIDES TWO DCL COMMANDS TO MANIPULATE THE PRIVILAGES.
1. GRANT
2. REVOKE

1. GRANT:-
----IT HELPS TO PROVIDE ANY KIND OF ACCESS TO ANY USERS.
----GRANT STATEMENT IS USED TO GIVE PERMISSION TO USER. (DBA GIVES PERMISSION TO
USER)

-CMD ACCESS:-
SQLPLUS
USERNAME:- SYS AS SYSDBA
PASSWORD:- pass
CREATE USER OM IDENTIFIED BY PASS QUOTA 50M ON SYSTEM;
GRANT CREATE USER TO OM;
GRANT CREATE TABLE TO OM;
GRANT CREATE SESSION TO OM;

-DML ACCESS:-
GRANT INSERT ON HR.T1 TO OM;
GRANT UPDATE ON HR.T1 TO OM;
GRANT SELECT ON HR.T1 TO OM;
GRANT DELETE ON HR.T1 TO OM;
GRANT ALTER ON HR.T1 TO OM;

2. REVOKE:-
----IT IS USED TO TAKE BACK THE ACCESS FROM USERS.
----REVOKE STATEMNT IS USED TO TAKE BACK THAT PERMISSION FROM USER.

REVOKE ALL PRIVILEGES ON T1 FROM OM; --THIS WILL REMOVE ALL PERMISSION FROM USER
EXCEPT CONNECT.
REVOKE DELETE ON HR.T1 FROM OM;
REVOKE UPDATE ON HR.T1 FROM OM;
REVOKE ALTER ON HR.T1 FROM OM;
REVOKE INSERT ON HR.T1 FROM OM;
REVOKE SELECT ON HR.T1 FROM OM;

REVOKE CONNECT FROM OM; --THIS WILL TAKE BACK CONNECT PRIVILEGES FROM
USER.

-PRIVILAGES CAN BE DIVIDED INTO TWO PARTS

1. SYSTEM PRIVILAGES (DDL)
2. OBJECT PRIVILAGES (DQL,DML)

>>> WHAT IS PREVILEGE? AND EXPLAIN?

----PRIVILAGE IS A PERMISSION GIVEN BY DBA.
----IT PROVIDES RIGHT TO EXECUTE A PARTICULAR TYPE OF SQL STATEMENTS
----IT ALSO GIVES RIGHT TO CONNECT THE DATABASE AND CREATE A TABLE IN YOUR SCHEMA.

*** WITH GRANT OPTION ***

----PASSING THE PRIVILEGES ALONG THE USER ONE USER CAN PASS PRIVILEGES TO OTHER
USER.

*** ROLE ***

----IT IS COLLECTION OF PREVILEGES OR PERMISSION
----IT IS LOGICAL FOLDER.
----WHEN WE WANT TO GIVE MULTIPLE GRANT IN SINGLE QUERY THAT TIME WE USE ROLE.

CREATE ROLE R;
GRANT R TO CREATE USER,
CREATE SESSION,
CREATE TABLE TO OM;

*** MULTITABLE INSERT ***

----MULTI-TABLE INSERT STATEMENT CAN BE USED IN DATA WAREHOUSING SYSTEM TO TRANSFER
DATA FROM
----ONE OR MORE OPERATIONAL SOURCES TO SET OF TARGET TABLE.

1. UNCONDITIONAL INSERT ALL.

2. CONDITIONAL INSERT ALL
3. CONDITIONAL INSERT FIRST
4. PIVOTING INSERT ALL

1. UNCONDITIONAL INSERT ALL.

----IT WILL COPY ALL DATA AND PASTE TO MULTIPLE TABLE.

CREATE TABLE A11 (ENO_1 NUMBER(20), E_NAME VARCHAR2(20),ESAL NUMBER(20));

CREATE TABLE A22 (TNO NUMBER(20), T_NAME VARCHAR2(20));
CREATE TABLE A33 (SAL_1 NUMBER(20));
CREATE TABLE A44 (ENO_12 NUMBER(20), SAL_2 NUMBER(20));

INSERT ALL
INTO A22 VALUES (ENO_1,E_NAME)
INTO A33 VALUES (ESAL)
INTO A44 VALUES (ENO_1,ESAL)
SELECT * FROM A11;

SELECT * FROM A22;

SELECT * FROM A33;
SELECT * FROM A44;

2. CONDITIONAL INSERT ALL.

----IF CONDITION IS SATISFY THEN IT WILL INSERT THE DATA
----IN MULTIPLE TABLE AND ROW BY ROW CONDITION
INSERT ALL
WHEN ESAL > 1000 THEN
INTO A22 VALUES (ENO_1,E_NAME)
WHEN ENO_1 < 30 THEN
INTO A33 VALUES (ESAL)
WHEN ENO_1 > 20 THEN
INTO A44 VALUES (ENO_1,ESAL)
SELECT * FROM A11;

DELETE FROM A22;

DELETE FROM A33;
DELETE FROM A44;

3. CONDITIONAL FIRST INSERT.

----IF CONDITION IS SATISFY THEN IT WILL INSERT THE DATA
----IN MULTIPLE TABLE AND ROW BY ROW CONDITION
----BUT IF THE DATA IS EXHAUSTED ON FIRST TWO TABLE THEN
----IT WILL NOT PUT ANY KIND OF DATA TO OTHER TABLE

INSERT FIRST
WHEN ESAL > 1000 THEN
INTO A22 VALUES (ENO_1,E_NAME)
WHEN ENO_1 < 30 THEN
INTO A33 VALUES (ESAL)
WHEN ENO_1 > 20 THEN
INTO A44 VALUES (ENO_1,ESAL)
SELECT * FROM A11;

4. PIVOTING INSERT.
----TO MERGE ROWS INTO COLUMN.

SELECT * FROM T1;

SELECT * FROM T2;

*** MERGE ***

----IT IS USED TO PERFORM INSERT, UPDATE (REPLACE) SIMULTENEOUSLY.
----IT IS DML COMMAND.
----MERGE FAILS WHEN THERE IS DUPLICATION.

MERGE INTO Z2 USING Z1

ON ([Link] = [Link])
WHEN MATCHED THEN
UPDATE SET [Link] = [Link],
[Link] = [Link]
DELETE WHERE [Link]=20
WHEN NOT MATCHED THEN
INSERT VALUES ([Link],[Link],[Link])

CREATE TABLE Z1 (PNO NUMBER(20),PNAME VARCHAR2(20),PCOST NUMBER(20));

CREATE TABLE Z2 (ZNO NUMBER(20),ZNAME VARCHAR2(20),ZCOST NUMBER(20));
INSERT INTO Z1 VALUES (&X1,'&X2',&X3);
INSERT INTO Z2 VALUES (&C1,'&C2',&C3);
SELECT * FROM Z1;
SELECT * FROM Z2;
 *** ALTER THE TABLE ***
----REQUIREMENT: TO CHANGE THE ALREADY PRESENT TABLE LIKE ADD THE NEW COLUMN.
----RENAME THE COLUMN NAME
----CHANGE THE SIZE AND TYPE OF COLUMN
----DROP THE COLUMN ETC.
----THE ALTER COMMAND ALLOW US TO ALTERING US TO STRUCTURE OF OBJECT.
----LIKE ADD A NEW COLUMN, DROP A COLUMN, RENAME A COLUMN, ADD CONSTRAINTS ETC.

*** ALTER ***

----ALTER WORKS ON COLUMN OF TABLE
----INSERT WORKS ON ROW OF TABLE.

1. ADD
----ADD THE NEW COLUMN IN EXISTING TABLE.
----WHEN WE WANT TO ADD ANY NEW COLUMN IN EXISTING TABLE THAT TIME WE USE THIS
CONCEPT.
SYNTAX:- ALTER TABLE TABLE_NAME ADD (COL_DEFINATION)...SIZE, CHAR, NUMBER.

>>> HOW TO ADD A COLUMN IN TABLE?

ALTER TABLE TEST_DATA ADD (NEW_COLUMN, NUMBER(5));

ALTER TABLE TEST_DATA_1 ADD (SALARY,NUMBER(2));
ALTER TABLE OM_RELATIVES ADD(XYZ VARCHAR2(5), LONG_RELATIVES VARCHAR2(5));
ALTER TABLE T1 ADD (HIRE_DATE1 DATE, LAST_NAME VARCHAR2(20));

2. MODIFY
----MANUPULATE THE DATA TYPE AND SIZE .
----SYNTAX: ALTER TABLE TABLE_NAME MODIFY(COL_NAME, NEW_DATA_TYPE(NEW_SIZE));

ALTER TABLE T1 MODIFY (TNAME VARCHAR2(30));

ALTER TABLE TEST_3 MODIFY (NAME VARCHAR2(7));
ALTER TABLE T1 MODIFY (LAST_NAME NUMBER(10));
ALTER TABLE T1 MODIFY (LAST_NAME VARCHAR(10));

3. RENAME
----CHANGE THE COLUMN NAME
----SYNTAX: ALTER TABLE TABLE_NAME RENAME OLD_COLUMN_NAME TO NEW_COLUMN;

ALTER TABLE T1 RENAME LAST_NAME TO SIR_NAME;

ALTER TABLE T1 RENAME T1 TO T111; ...DOUBT
SELECT * FROM T1;

4. DROP
----REMOVE THE COLUMN FROM THE TABLE.
----SYNTAX: ALTER TABLE TABLE_NAME DROP COLUMN COL_NAME;
----ONLY ONE COLUMN AT A TIME, AFTER DROPPING WE CAN NOT RECOVERED.
----A COLUMN CAN NOT BE DROPPED IF IT IS PART OF CONSTRAINT OR PART OF AN INDEX KEY

----UNLESS THE CASCADE OPTION IS ADDED.

ALTER TABLE TEST_3 DROP COLUMN FULL_NAME;

ALTER TABLE T1 DROP COLUMN LAST_NAME;

5. ALTER SEQUENCE
----WHEN WE WANT TO ALTER THE SEQUENCE THAT TIME WE USE THIS.
ALTER SEQUENCE sequence_name
INCREMENT BY increment_value
START WITH start_value
MAXVALUE max_value
MINVALUE min_value
CYCLE | NOCYCLE;

*** SET UNUSED OPTION ***

----WE USE THE SET UNUSED OPTIO TO MARK ONE OR MORE COLUMN IS UNUSED.
----YOU USE DROP UNUSED COLUMN OPTION TO REMOVE THE COLUMN THAT ARE MARKED AS
UNUSED.
SYNTAX :- ALTER TABLE TABLE_NAME SET UNUSED (COL_NAME);

ALTER TABLE T1 SET UNUSED (HIRE_DATE1);

ALTER TABLE T1 DROP UNUSED COLUMNS;
----SETTING A COLUMN TO BE UNUSED ARE SIMILAR TO THOSE OF DROPPING OF THOSE OF
DROPPING A COLUMN

*** ADDING CONSTRAINT SYNTAX ***

----ADD OR DROP A CONSTRAINT, BUT NOT MODIFY IT'S STRUCTURE.
----ENABLE OR DISABLE CONSTRAINT.
----ADD NOT NULL CONSTRAINT BY USING THE MODIFY CLAUSE.

ALTER TABLE T1 ADD CONSTRAINT PRIMARY KEY TYPE (T_ID); ...DOUBT

ALTER TABLE T1 MODIFY T_ID PRIMARY KEY;
ALTER TABLE T1 ADD CONSTRAINT EMP-MGR-FK
FOREIGN KEY (MGR_ID)
REFERENCES T1 (EMP_ID)

*** COMMENT ***

----SYNTAX: COMMENT ON TABLE TABLE_NAME IS "OK";
----TO CHECK THE ANY COMMENT ON ANY TABLES

SELECT * FROM USER_TAB_COMMENTS

COMMENT ON TABLE EMPLOYEES IS 'THIS IS EMP INFO TABLE';
COMMENT ON TABLE EMPLOYEES IS ' ';
SELECT * FROM T1
SELECT * FROM USER_COL_COMMENTS;
COMMENT ON COLUMN T_ID IS 'NAME OF EMP'

*** COMMENTING TO THE CODE ***

----THERE IS TWO TYPES OF COMMENTING TO THE CODE
1. --
2. /* */

*** RENAME ***

----IT IS A DDL COMMAND.
----IT IS USED TO RENAME TABLE NAME, COLUMN NAME.
----IT IS PERMANENTLY RENAME THE TABLE NAME.

CREATE TABLE USING / SUBQUERY: USING SELECT STATEMENT:(CTS)

*** FLASHBACK ***

----IT IS USED TO RESTORE DROPPED TABLE.

FLASHBACK TABLE

>>> HOW TO ENABLE AND DISABLE FLASH BACK IN SQL?

ALTER TABLE TABLE_NAME FLASHBACK ON;
ALTER DATABASE FLASHBACK ON;
ALTER TABLE TABLE_NAME FLASHBACK OFF;
ALTER DATABASE FLASHBACK OFF;

*** GROUPING SETS ***

----USE TO DEFINE MULTIPLES GROUPS IN GROUP BY CLAUSE ( ROLLUP/ CUBE)
----FINAL RESULT IS THE COMBINATION OF RESULT OF THE TWO DIFFERENT GROUP WITH UNION
ALL OPERATOR.

SELECT DEPARTMENT_ID, JOB_ID,MANAGER_ID,AVG(SALARY)

FROM EMPLOYEES
GROUP BY ROLLUP ((DEPARTMENT_ID, JOB_ID), (JOB_ID, MANAGER_ID))

SELECT DID,SUM(SAL) FROM P11 GROUP BY DID ORDER BY DID;

SELECT DID,ENAME FROM P11

GROUP BY DID,ENAME

SELECT * FROM P11

*** ROLLUP ***

----ROLLUP GROUPING OPERATOR PRODUCE REGULAR GROUPS ALONG WITH SUB TOTAL
----OF VALUE WITH THEIR GRANT TOTAL.

SELECT DID,ENAME,SUM(SAL) FROM P11

GROUP BY ROLLUP(DID,ENAME);

*** CUBE ***

----IT IS USED TO PRODUCE RESULT SET OF ROLLUP ALONG WITH CROSS TABULATION OF ROWS.
----LOGICAL EX:- IF WE WANT TO FIND PRODUCT WISE, CUSTOMER WISE PROFIT THEN WE USE
CUBE.

SELECT ENAME,SUM(SAL) FROM P11

GROUP BY ENAME;

SELECT ENAME,DID,SUM(SAL)
FROM P11 GROUP BY CUBE (DID,ENAME);

*** GROUPING FUNCTION ***

----IT USE TO DIFFERENTIATE ACUTAL VALUE AND PRODUCE NULL VALUE BY 0 AND 1;
--ACTUAL ZERO WHEN VALUE :- 0
--PRODUCE ONE WHEN NULL :- 1

SELECT DID,ENAME,SUM(SAL),GROUPING (ENAME)FROM P11

GROUP BY ROLLUP (DID,ENAME);

*** GROUPING SETS ***

----

SELECT DID,EID,ENAME,ADDRESS,SUM(SAL) FROM P11

GROUP BY (DID,ENAME,EID,ADDRESS);

SELECT DID,ENAME,SUM(SAL)FROM P11

GROUP BY DID,ENAME
UNION ALL
SELECT EID,ADDRESS, SUM(SAL) FROM P11
GROUP BY EID,ADDRESS;
SELECT * FROM P11;

*** CORRELATED SUBQUERY ***

----EACH SUBQUERY IS EXECUTED ONCE FOR EVERY ROW OF THE OUTER QUERY.
----IT DOES ROW BY ROW EXECUTION.
----WHILE EXECUTION IT DOES NOT CHECK DUPLICATION.
----TO DISPLAY DATA OUTER QUERY GOES TO INNER QUERY.
----TO COMPARE DATA INNER QUERY GOES TO OUTER QUERY.
----HENCE IT IS CALLED CORRELATED QUERY.

SELECT * FROM EMPLOYEES E1 WHERE 5> =(SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES E2

WHERE E2.DEPARTMENT_ID=E1.DEPARTMENT_ID);

SELECT * FROM EMPLOYEES E1 WHERE 3 <=(SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEES E2

WHERE E2.DEPARTMENT_ID=E1.DEPARTMENT_ID);

*** CONCANATED GROUPPING ***

----HERE WE CAN USE ROLLUP & CUBE AT A TIME IN SINGEL GROUP BY CLAUSE IN SINGLE
QUERY.

SELECT DEPARTMENT_ID, JOB_ID ,SUM(SALARY)

FROM EMPLOYEES
GROUP BY ROLLUP (DEPARTMENT_ID), CUBE (JOB_ID);
SELECT * FROM EMPLOYEES;

*** CORELATED SUBQUERY ***

----IT IS PROCESSING ROW BY ROW
----INNER QUERY IS EXECUTES EVERY TIME FOR EACH VALUE/ ROW OF THE OUTER QUERY.
----INNER QUERY DEPEND ON OUTER QUERY AND OUTER QUERY DEPEND ON INNER QUERY.
----INNER QUERY GOES TO OUTER TO MATCH THE RESULT
----AND OUTER QUERY GOES TO INNER TO DISPLAY THE RESULT
----BOTH QUERY GOES TO DEPEND ON EACH OTHER.

SELECT * FROM EMPLOYEES E1 WHERE SALARY >

(SELECT AVG(SALARY) FROM EMPLOYEES E2 WHERE E1.DEPARTMENT_ID=E2.DEPARTMENT_ID)

SELECT * FROM EMPLOYEES E1 WHERE SALARY >

(SELECT AVG(SALARY) FROM EMPLOYEES E2 WHERE E1.DEPARTMENT_ID=E2.DEPARTMENT_ID)

SELECT * FROM EMPLOYEES E1 WHERE 2 =

(SELECT COUNT(*) FROM EMPLOYEES E2 WHERE E1.DEPARTMENT_ID=E2.DEPARTMENT_ID)

SELECT * FROM EMPLOYEES E1 WHERE 3 =

(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEES E2 WHERE [Link] > =[Link])

SELECT * FROM EMPLOYEES E1 WHERE 3 =

(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEES E2 WHERE [Link] > = [Link] )
SELECT * FROM EMPLOYEES E1 WHERE 7 =
(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEES E2 WHERE [Link] >= [Link])

SELECT * FROM EMPLOYEES E1 WHERE 1 =

(SELECT COUNT(DISTINCT SALARY) FROM EMPLOYEES E2 WHERE DEPARTMENT_ID IN
(SELECT DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID))

*** INLINE VIEW ***

SELECT (SELECT DEPARTMENT_NAME FROM DEPARTMENTS
WHERE DEPARTMENTS.DEPARTMENT_ID=DEPARTMENTS.DEPARTMENT_ID)
DEPARTMENT_ID FROM EMPLOYEES GROUP BY DEPARTMENT_ID

SELECT department_id, salary AS third_salary

FROM (SELECT department_id,salary,
ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees)
ranked_employees WHERE salary_rank = 3;

>>> FETCH DETAILS OF EMPLOYEES WHOSE SALARY IS GREATER THAN EMPLOYEES NO 101.
SELECT * FROM EMPLOYEES WHERE SALARY > (SELECT SALARY FROM EMPLOYEES WHERE
EMPLOYEE_ID= 101);
SELECT * FROM EMPLOYEES;

>>> FETCH EMPLOYEES DETAILS WHO EARN MORE THAN AVG SALARY OF THEIR RESPECTIVE
DEPARTMENT
SELECT * FROM EMPLOYEES A WHERE SALARY >
(SELECT AVG(SALARY) FROM EMPLOYEES B WHERE B.DEPARTMENT_ID=A.DEPARTMENT_ID);

SELECT * FROM T1 E1 WHERE 1=

(SELECT COUNT(*) FROM T2 E2 WHERE E2.T_SAL>=[Link]);

SELECT * FROM T1 E1 WHERE 1=

(SELECT COUNT(*) FROM T2 E2 WHERE [Link]>=E2.T_SAL);

SELECT * FROM EMPLOYEES E1 WHERE 1>=

(SELECT COUNT(*) FROM EMPLOYEES E2 WHERE E2.MANAGER_ID=E1.EMPLOYEE_ID);

SELECT * FROM T3;

SELECT * FROM T4;

*** CORELATED DELETE ***

>>> DELETE T4 USING T3
DELETE FROM T4 WHERE CNO IN
(SELECT CNO FROM T3 WHERE [Link]=[Link]);
ROLLBACK T3;

*** CORELATED UPDATE ***

UPDATE T3 USING T4
UPDATE T3 SET CNAME=
(SELECT CNAME FROM T4 WHERE [Link]=[Link]);

*** EXISTS OR NOT EXISTS ***

----IN THIS PROCESS EXISTANCE OF DATA OR ROWS OF THE OUTER QUERY IS CHECKED INSIDE
THE RESULT OF INNER QUERY.
----IF EXISTANCE FOUND THEN CONDITION IS TRUE AND SEARCH PROCESS STOPS.
----IF EXISTANCE NOT FOUND THEN CONDITION IS FALSE AND SEARCH PROCESS CONTINUES
STILL LAST ROW.
----IMPACT THE PERFORMANCE IN POSITIVE WAY I.E BETTER WAY.
----WE USE JOIN AND OTHER FUCNTION TO FETCH THE RESULT BUT IT TAKE TOO MUCH TIME AS
WELL AS NOT GIVING ACCURACY.

>>> FIND OUT SUCH A EMP AT LEAST ONE EMP IS REPORTING.

SELECT * FROM EMPLOYEES A WHERE EXISTS
( SELECT 'X' FROM EMPLOYEES B WHERE B.MANAGER_ID = A.EMPLOYEE_ID);

>>> FIND OUT SUCH A EMP AT LEAST ONE EMP IS NOT REPORTING.
SELECT * FROM EMPLOYEES A WHERE NOT EXISTS
( SELECT 'X' FROM EMPLOYEES B WHERE B.MANAGER_ID = A.EMPLOYEE_ID);

>>> FIND ALL DEPARTMENTS IN WHICH NO EMPLOYEE ARE PRESENT.

SELECT * FROM EMPLOYEES A WHERE NOT EXISTS
(SELECT 'X' FROM EMPLOYEES B WHERE B.DEPARTMENT_ID=A.DEPARTMENT_ID);

*** WITH CLAUSE ***

----USE TO AVOID THE REPETATION OF THE SUB CODE IN SAME MAIN CODE OR PROCEDURE OR
FUNCTION.
----CODE COMPACT
----IT MAKES THE ANOTHER TABLE.
----COMMON TABLE EXPRESSION.
----IT IS USED TO HOLD THE RESULT OF SQL STATEMENT IN TEMPORARY VARIABLE

ADVANTAGE:-
1. CODE OPTIMIZATION.
2. INCREASE PERFORMACE.
3. SAVE MEMEORY.

WITH
DEPT_10_SAL_14300 AS
(SELECT * FROM EMPLOYEES WHERE DEPARTMENT_ID = 20 AND SALARY > 500);
SELECT * FROM DEPT_10_SAL_14300 WHERE SALARY =5000;

WITH T1 AS (SELECT SUM(SALARY),DEPARTMENT_ID AS DUMMY FROM EMPLOYEES

GROUP BY DEPARTMENT_ID)
SELECT SUM(DUMMY) FROM T1;

*** HIERARCHICAL DATA ***

----DATA IN WHICH DATA ITEMS ARE RELATED TO EACH OTHER BY HIERARICAL RELATIONSHIP.
----HIERARICAL RELATIONSHIP : IN THIS RELATIONSHIP ONE DATA ITEM IS THE PARENT OF
ANOTHER DATA ITEM.
----FOR EX. FAMILY TREE, ORGANISATION CHART, COMPUTER DIRECTORY STRUCTURE.

*** HIERARICHICAL QUERY ***

----A QUERY WHICH HANDLE / FETCH THE HEIRARICHICAL DATA.

*** COMPONENTS OF HEIRARICHICAL QUERY ***

1. START WITH:
----FROM WHERE WALK START.
----ROOT ROW.

2. CONNECT BY:
----RELATIONSHIP BETWEEN PARENT & CHILD ROWS.

3. PRIOR:
----IT WILL FETCH ENTIRE DATA BELOW THE ROOT ROW.

4. LEVEL:
----IT IS ONE PSEUDO COLUMN.
----DEFINE DEPTH OR LEVEL OF EACH ROW IN THE TREE.

>>>
SELECT * FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY EMPLOYEE_ID = MANAGER_ID; ---IT WILL FETCH OR SHOW THE ROOT ROW ONLY.

SELECT * FROM EMPLOYEES

START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID= MANAGER_ID; ----IT WILL GIVE THE ROOT ROW ALONG WITH
PRIOR DATA.

SELECT LEVEL, A.* FROM EMPLOYEES A

START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID= MANAGER_ID; ----IT WILL ASIGN THE LEVEL OR DEPTH TO
EACH ROW IN THE RESULT.

SELECT LEVEL, A.* FROM EMPLOYEES A

START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID= MANAGER_ID
ORDER BY LEVEL; ----IT WILL GIVE THE ROOT

*** PSEUDO COLUMN ***

1. LEVEL

2. ROWID
----IT IS ONE PSEUDO COLUMN
----ROWID IS A UNIQUE IDENTIFICATION NUMBER OF ROW
----ASSIGNS TO THE ROWS WHEN INSERTING INTO THE TABLE.

SELECT ROWID,A.* FROM EMPLOYEES A;

SELECT ROWID,ROWNUM, A.* FROM EMPLOYEES A;

DELETE FROM T123 WHERE TNO IN

(SELECT TNO FROM EMPLOYEES GROUP BY TNO

3. ROWNUM
----IT IS ONE PSEUDO COLIUMN
----IT ASSIGN THE NUMBER TO THE ROWS OF THE RESULT.
----ROWNUM IS TEMPORARY ASSIGNED TO RESULT.

SELECT ROWNUM, A.* FROM EMPLOYEES A;

SELECT ROWNUM, A.* FROM EMPLOYEES A WHERE ROWNUM=1;
SELECT ROWNUM, A.* FROM EMPLOYEES A WHERE ROWNUM=2; ...NO DATA FOUND
SELECT ROWNUM, A.* FROM EMPLOYEES A WHERE ROWNUM <=2;
SELECT ROWNUM, A.* FROM EMPLOYEES A WHERE ROWNUM <=10;
SELECT ROWID,ROWNUM,A.* FROM EMPLOYEES A;

>>> FIND LAST &N TH NUMBER OF RECORD WITH HELP OF MINUS?

SELECT * FROM EMPLOYEES
MINUS
SELECT * FROM EMPLOYEES WHERE ROWNUM <= (SELECT COUNT (*)-&N FROM EMPLOYEES)

>>> FIND WHICH DEPARTMENT HAVE NO EMPLOYEES?

SELECT DEPARTMENT_ID FROM DEPARTMENTS
MINUS
SELECT DEPARTMENT_ID FROM EMPLOYEES

>>> KEEP DISTINCT RECORDS AND DELETE DUPLICATE RECORD FROM THE TABLE?
DELETE FROM T3 E1 WHERE ROWID!=
(SELECT MAX(ROWID) FROM T3 E2 WHERE [Link]=[Link])

SELECT ROWID,T3.* FROM T3;

SELECT * FROM T4;
ROLLBACK T3

*** VERY IMP ***

*** WINDOW FUNCTION / ANALYTICAL FUNCTION ***
----COMMON USE: USES TO ASSIGN THE NUMBER TO THE RESULT
----WINDOWING / ANALYTICAL FUNCTION () OVER (ORDER BY COL_NAME)

1. RANK()
----IF DUPLICATE VALUES PRESENT THEN ASSIGN SAME RANK AND FORM THE GAP
----IT INTERNALLY SKIPS NUMBER IF DATA SAME.
----IF DATA IS SAME THEN IT IS REPEAT NO
----BUT INTERNALLY IT IS COUNT THE NO AND GO WITH NEXT NUMBER EVERY TIME.
----LIKE THAT (1,2,3,4,5,5,7,8,8,10)
----LOGICAL EXAMPLE OF RANK IS - TO FIND THE STUDENT WHO IS TOP 10;

SELECT SALARY, RANK()OVER(ORDER BY SALARY DESC) FROM EMPLOYEES

SELECT EMPLOYEE_ID, DEPARTMENT_ID, RANK()OVER (ORDER BY DEPARTMENT_ID) FROM
EMPLOYEES;
SELECT EMPLOYEE_ID, DEPARTMENT_ID, RANK()OVER (ORDER BY EMPLOYEE_ID) FROM
EMPLOYEES;
SELECT FIRST_NAME,SALARY,RANK()OVER(ORDER BY SALARY DESC) AS DATA1 FROM EMPLOYEES;

>>> WE WANT EMPLOYEE_ID,SALARY,DEPARTMENT_ID AND PARTITION BY ON DEPARTMENT_ID AND

SALARY
RANKING IS DEPEND ON DESC ORDER?
SELECT employee_id, salary,DEPARTMENT_ID,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS
dept_salary_rank
FROM employees;

SELECT FIRST_NAME, LAST_NAME

FROM (SELECT FIRST_NAME, SALARY,RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY
SALARY)
AS DUP_RECORD FROM EMPLOYEES) EMPLOYEES WHERE DEPARTMENT_ID > 1;

DELETE FROM T3 WHERE ROWID NOT IN (

SELECT MAX(ROWID) FROM T3 GROUP BY CNO);
SELECT * FROM T3
ROLLBACK T3

SELECT FIRST_NAME, SALARY FROM (SELECT FIRST_NAME, SALARY,RANK() OVER (PARTITION BY

DEPARTMENT_ID ORDER BY SALARY)
AS DUP_RECORD FROM EMPLOYEES) EMPLOYEES WHERE DEPARTMENT_ID > 1;

SELECT FIRST_NAME, SALARY, RANK()OVER(ORDER BY SALARY DESC) AS DATA_1,

DENSE_RANK () OVER (ORDER BY SALARY DESC) AS DATA_2 FROM EMPLOYEES;

2. DENSE_RANK()
----IF DUPLICATE VALUES PRESENT THEN ASSIGN SAME RANK AND NOT FORM THE GAP.
----DOES NOT SKIP RANK NUMBER.
----IF DATA SAME THEN IT IS REPEAT NUMBER AND THEN CONTINUE FROM NEXT NUMBER.

SELECT EMPLOYEE_ID, DEPARTMENT_ID, DENSE_RANK ()OVER (ORDER BY DEPARTMENT_ID) FROM

EMPLOYEES;
SELECT FIRST_NAME, SALARY,DENSE_RANK()OVER(ORDER BY SALARY DESC) AS DATA1 FROM
EMPLOYEES;

3. ROW_NUMBER()
----ASSIGN NUMERIC SEQUENTIAL NUMBER TO RESULT.
----SYNTAX: WINDOWING_FUNCTION()OVER (ORDER BY COL_NAME);

SELECT EMPLOYEE_ID, DEPARTMENT_ID, ROW_NUMBER()OVER (ORDER BY DEPARTMENT_ID) FROM

EMPLOYEES;

>>> ARRANGE THE EMPLOYEES BASED SALARY (FROM LOWEST TO HIGHEST) IN EACH
DEPARTMENT.
SELECT EMPLOYEE_ID,FIRST_NAME, SALARY, DEPARTMENT_ID, RANK() OVER (PARTITION BY
DEPARTMENT_ID ORDER BY SALARY )R1 FROM EMPLOYEES;
SELECT EMPLOYEE_ID,FIRST_NAME, SALARY, DEPARTMENT_ID, RANK() OVER (PARTITION BY
DEPARTMENT_ID ORDER BY SALARY DESC )R1 FROM EMPLOYEES;

SELECT T_ID,TNAME,SALARY, ROWID,ROWNUM FROM T1;

SELECT * FROM EMPLOYEES WHERE ROWNUM <=2;

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID, DENSE_RANK () OVER

(PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC ) R1 FROM EMPLOYEES;

SELECT * FROM RECYCLEBIN

FLASHBACK TABLE U1 TO TIMESTAMP 2023-11-[Link];
FLASHBACK TABLE USER_DETAILS TO SCN 4144375;
FLASHBACK SCN

SELECT * FROM EMPLOYEES START WITH FIRST_NAME='Lex' CONNECT BY PRIOR

EMPLOYEE_ID=MANAGER_ID;
----TOP DOWN APPROACH

SELECT * FROM EMPLOYEES START WITH FIRST_NAME='Nancy' CONNECT BY PRIOR

MANAGER_ID=EMPLOYEE_ID;
----BTM UP APPROACH

*** REGULAR EXPRESSION ***

----REGULAR EXPRESSION SUPPORT IN SQL TO SEARCH,
----MATCH AND REPLACE STRINGS IN TERMS OF REGULAR EXPRESSION.

FUNCTION:-
1. REGEXP_SUBSTR
2. REGEXP_INSTR
3. REGEXP_LIKE
4. REGEXP_REPLACE

>>> DISPLAY THE DETAILS OF THOSE EMPLOYEE WHOSE NAME START WITH A,B,..Z.
SELECT * FROM EMPLOYEES WHERE FIRST_NAME LIKE 'A%' OR FIRST_NAME LIKE 'B%' ...AND
SO ON

SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE(FIRST_NAME,'[A-B]+')

SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (PHONE_NUMBER,'[0-9]{3}.[0-9]{3}.[0-9]
{4}');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (PHONE_NUMBER,'[[:digit:]]{3}.[[:digit:]]
{3}.[[:digit:]]{4}');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (PHONE_NUMBER,'\d{3}.\d{3}.\d{4}');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME,'[:AEIOU:]');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME,'[A-C]');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME,'^[A-C]');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME,'^[^A-C]');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (PHONE_NUMBER,'^[:0:]');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (PHONE_NUMBER,'\d{5}$');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (LAST_NAME,'K(i|o|h)');
SELECT FIRST_NAME FROM EMPLOYEES WHERE REGEXP_LIKE (FIRST_NAME,'My|se');
SELECT * FROM EMPLOYEES WHERE REGEXP_LIKE (FIRST_NAME,'(V|A|J)');

--------------------------END OF ADVANCE SQL -----------------------------------

*** PRACTISE SQL QUESTION ***

1. Display details of jobs where the minimum salary is greater than 10000.
SELECT JOB_ID FROM EMPLOYEES WHERE SALARY > 10000;
SELECT * FROM JOBS WHERE MIN_SALARY > 10000;

2. Display the first name and join date of the employees who joined between 2002
and
2005.
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'YYYY') BETWEEN 2002 AND 2005;

3. Display first name and join date of the employees who is either IT Programmer or
Sales
Man.
SELECT FIRST_NAME, HIRE_DATE FROM EMPLOYEES WHERE JOB_ID='IT_PROG' OR
JOB_ID='ST_MAN'

4. Display employees who joined after 1st January 2008.

SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY') > 2008

5. Display details of employee with ID 150 or 160.

SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=150 OR EMPLOYEE_ID=160;

6. Display first name, salary, commission pct, and hire date for employees with
salary less
than 10000.
SELECT FIRST_NAME, SALARY, COMMISSION_PCT, HIRE_DATE FROM EMPLOYEES WHERE SALARY <
10000

7. Display job Title, the difference between minimum and maximum salaries for jobs
with
max salary in the range 10000 to 20000.
SELECT JOB_TITLE,MAX_SALARY-MIN_SALARY FROM JOBS WHERE MAX_SALARY BETWEEN 10000 AND
20000

8. Display first name, salary, and round the salary to thousands.

SELECT FIRST_NAME,SALARY, ROUND(SALARY,-3) FROM EMPLOYEES

9. Display details of jobs in the descending order of the title.

SELECT * FROM JOBS ORDER BY JOB_TITLE DESC;

10. Display employees where the first name or last name starts with S.
SELECT * FROM EMPLOYEES WHERE FIRST_NAME='S%' AND LAST_NAME='S%';

11. Display employees who joined in the month of May.

SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'MON')='MAY';

12. Display details of the employees where commission percentage is null and salary
in the
range 5000 to 10000 and department is 30
SELECT * FROM EMPLOYEES WHERE COMMISSION_PCT IS NULL AND DEPARTMENT_ID=30 AND
(SALARY BETWEEN 5000 AND 10000)

13. Display first name and date of first salary of the employees.
SELECT FIRST_NAME,HIRE_DATE, LAST_DAY(HIRE_DATE) FROM EMPLOYEES;

14. Display first name and experience of the employees.

SELECT FIRST_NAME,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) FROM EMPLOYEES
SELECT FIRST_NAME, TRUNC((SYSDATE-HIRE_DATE)/365) FROM EMPLOYEES

15. Display first name of employees who joined in 2001.

SELECT FIRST_NAME FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'YYYY')=2001;

16. Display first name and last name after converting the first letter of each name
to upper
case and the rest to lower case.
SELECT INITCAP(FIRST_NAME),INITCAP(LAST_NAME) FROM EMPLOYEES

17. Display the first word in job title

SELECT SUBSTR(JOB_TITLE,1,INSTR(JOB_TITLE,' ')-1) FROM JOBS;
SELECT SUBSTR(JOB_TITLE,1,INSTR(JOB_TITLE,' ')) FROM JOBS

18. Display the length of first name for employees where last name contain
character ‘b’ after
3rd position.
SELECT LENGTH(FIRST_NAME) FROM EMPLOYEES WHERE
LAST_NAME LIKE '___%b%';

SELECT LENGTH(first_name),LAST_NAME FROM employees

WHERE INSTR(SUBSTR(last_name, 4), 'b') > 0;

19. Display first name in upper case and email address in lower case for employees
where the
first name and email address are same irrespective of the case.
SELECT UPPER(first_name), LOWER(email) FROM employees
WHERE UPPER(first_name) = UPPER(email);

20. Display employees who joined in the current year.

SELECT * FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'DD-MON-YYYY')='SYSDATE'

21. Display the number of days between system date and 1st January 2011.
SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,'1-JAN-2011')*30) FROM DUAL;

22. Display how many employees joined in each month of the current year.
SELECT COUNT(TO_CHAR(HIRE_DATE,'YYYY')) FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'YYYY')='SYSDATE'
GROUP BY TO_CHAR(HIRE_DATE,'YYYY');

SELECT COUNT(TO_CHAR(HIRE_DATE,'MON')) FROM EMPLOYEES

WHERE TO_CHAR(HIRE_DATE,'YYYY')=1994
GROUP BY TO_CHAR(HIRE_DATE,'MON');

23. Display manager ID and number of employees managed by the manager

SELECT MANAGER_ID,COUNT(*) FROM EMPLOYEES GROUP BY MANAGER_ID

24. Display employee ID and the date on which he ended his previous job.
SELECT EMPLOYEE_ID,END_DATE FROM JOB_HISTORY;

25. Display number of employees joined after 15th of the month.

SELECT COUNT(*) FROM EMPLOYEES WHERE TO_CHAR(HIRE_DATE,'DD') >15

26. Display the country ID and number of cities we have in the country.
SELECT country_id, COUNT(*) FROM LOCATIONS GROUP BY COUNTRY_ID;

27. Display average salary of employees in each department who have commission
percentage.
SELECT TRUNC(AVG(SALARY)) FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
GROUP BY DEPARTMENT_ID

28. Display job ID, number of employees, sum of salary, and difference between
highest
salary and lowest salary of the employees of the job.
SELECT JOB_ID,COUNT(EMPLOYEE_ID),SUM(SALARY),MAX(SALARY)-MIN(SALARY)
FROM EMPLOYEES GROUP BY JOB_ID

>>> DIFFERENCE BETWEEN MAX(SALARY) AND MIN SALARY?

SELECT MAX(SALARY)-MIN(SALARY) FROM EMPLOYEES
SELECT MAX(SALARY),MIN(SALARY) FROM EMPLOYEES

29. Display job ID for jobs with average salary more than 10000.
SELECT JOB_ID,AVG(SALARY) FROM EMPLOYEES GROUP BY JOB_ID HAVING AVG(SALARY)>10000

30. Display years in which more than 10 employees joined.

SELECT TO_CHAR(HIRE_DATE,'YYYY'),COUNT(*) FROM EMPLOYEES
GROUP BY TO_CHAR(HIRE_DATE,'YYYY') HAVING COUNT(TO_CHAR(HIRE_DATE,'YYYY'))>10

31. Display departments in which more than five employees have commission
percentage
SELECT DEPARTMENT_ID,COUNT(*) FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
GROUP BY DEPARTMENT_ID HAVING COUNT(EMPLOYEE_ID)>5

32. Display employee ID for employees who did more than one job in the past.
SELECT EMPLOYEE_ID,COUNT(*) FROM JOB_HISTORY GROUP BY EMPLOYEE_ID HAVING
COUNT(EMPLOYEE_ID)>1

33. Display job ID of jobs that were done by more than 3 employees for more than
100 days.
SELECT job_id FROM job_history WHERE (END_DATE-START_DATE) >100
GROUP BY job_id
HAVING COUNT(DISTINCT employee_id) > 3

34. Display department ID, year, and Number of employees joined

SELECT DEPARTMENT_ID,COUNT(EMPLOYEE_ID) FROM EMPLOYEES
GROUP BY DEPARTMENT_ID

SELECT d.department_id, TO_CHAR(e.HIRE_DATE,'YYYY'),COUNT(*)

FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_id, TO_CHAR(e.HIRE_DATE, 'YYYY')
ORDER BY d.department_id;

35. Display departments where any manager is managing more than 5 employees
SELECT MANAGER_ID,COUNT(*) FROM EMPLOYEES GROUP BY MANAGER_ID HAVING
COUNT(EMPLOYEE_ID)>5

SELECT d.department_id, d.department_name FROM departments d JOIN employees e

ON d.department_id = e.department_id
WHERE e.employee_id IN
(SELECT manager_id
FROM employees
GROUP BY manager_id
HAVING COUNT(*) > 5);

36. Change salary of employee 115 to 8000 if the existing salary is less than 6000.
UPDATE EMPLOYEES
SET SALARY=SALARY+8000
WHERE EMPLOYEE_ID=115 AND SALARY >6000

UPDATE EMPLOYEES
SET SALARY=SALARY+8000
WHERE EMPLOYEE_ID=101 AND SALARY >6000

37. Insert a new employee into employees with all the required details.
INSERT INTO EMPLOYEES VALUES
(208,'XX','YY','ZZ',5151238282,'22-JUN-1990','AC_MGR',6000,NULL,101,110)

INSERT INTO employees VALUES

(207, 'John', 'Doe', 'example', '1234567890',
TO_DATE('1990-01-08', 'YYYY-MM-DD'), 'AC_MGR', 60000,NULL,101, 110);
DELETE FROM EMPLOYEES WHERE EMPLOYEE_ID IN (207,208)

38. Delete department 20.

DELETE FROM DEPT_VIEW WHERE DEPARTMENT_ID=20
DELETE FROM EMPLOYEES WHERE DEPARTMENT_ID=20

39. Change job ID of employee 110 to IT_PROG if the employee belongs to department
10
and the existing job ID does not start with IT
UPDATE EMPLOYEES
SET JOB_ID='IT_PROG'
WHERE EMPLOYEE_ID=110 AND DEPARTMENT_ID=10 AND JOB_ID NOT LIKE 'IT%';
40. Insert a row into departments table with manager ID 120 and location ID in any
location
ID for city Tokyo.
INSERT INTO DEPARTMENTS VALUES (281,'NEW',120,1700);

41. Display department name and number of employees in the department

SELECT E1.DEPARTMENT_NAME, COUNT(*)
FROM DEPARTMENTS E1 JOIN EMPLOYEES E2
ON (E1.DEPARTMENT_ID=E2.DEPARTMENT_ID)
GROUP BY E1.DEPARTMENT_ID,E1.DEPARTMENT_NAME

42. Display job title, employee ID, number of days between ending date and starting
date for
all jobs in department 30 from job history.
SELECT JOB_ID,EMPLOYEE_ID,MONTHS_BETWEEN (END_DATE,START_DATE)
FROM JOB_HISTORY WHERE DEPARTMENT_ID=30;

43. Display department name and manager first name.

SELECT E1.DEPARTMENT_NAME,E2.FIRST_NAME FROM DEPARTMENTS E1 JOIN EMPLOYEES E2
ON (E1.MANAGER_ID=E2.EMPLOYEE_ID)

44. Display department name, manager name, and city

SELECT D.DEPARTMENT_NAME,E.FIRST_NAME,[Link] FROM DEPARTMENTS D JOIN EMPLOYEES E
ON D.MANAGER_ID=E.EMPLOYEE_ID
JOIN LOCATIONS L ON D.LOCATION_ID=L.LOCATION_ID;

45. Display country name, city, and department name.

SELECT D.COUNTRY_NAME,[Link],E.DEPARTMENT_NAME
FROM COUNTRIES D JOIN LOCATIONS L
ON (D.COUNTRY_ID=L.COUNTRY_ID)
JOIN DEPARTMENTS E
ON (L.LOCATION_ID=E.LOCATION_ID);

46. Display job title, department name, employee last name, starting date for all
jobs from
2000 to 2005
SELECT J.JOB_TITLE,D.DEPARTMENT_NAME,E.LAST_NAME,E.HIRE_DATE
FROM DEPARTMENTS D JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID=E.MANAGER_ID)
JOIN JOBS J
ON (J.JOB_ID=E.JOB_ID);

47. Display job title and average salary of employees

SELECT J.JOB_TITLE,AVG(SALARY) FROM JOBS J JOIN EMPLOYEES E
ON (J.JOB_ID=E.JOB_ID) GROUP BY JOB_TITLE

48. Display job title, employee name, and the difference between maximum salary for
the job
and salary of the employee.
SELECT E.FIRST_NAME,J.JOB_TITLE,J.MAX_SALARY-[Link] FROM EMPLOYEES E JOIN JOBS J
ON (E.JOB_ID=J.JOB_ID);

49. Display last name, job title of employees who have commission percentage and
belongs
to department 30.
SELECT E.LAST_NAME,J.JOB_TITLE FROM EMPLOYEES E JOIN JOBS J
ON (E.JOB_ID=J.JOB_ID) WHERE COMMISSION_PCT IS NOT NULL AND DEPARTMENT_ID=30;
50. Display details of jobs that were done by any employee who is currently drawing
more
than 15000 of salary..
SELECT J.JOB_TITLE,E.FIRST_NAME FROM JOBS J JOIN EMPLOYEES E
ON (J.JOB_ID=E.JOB_ID) WHERE SALARY > 15000;

51. Display employee name if the employee joined before his manager
SELECT E.FIRST_NAME, E.HIRE_DATE FROM EMPLOYEES E JOIN EMPLOYEES J
ON (E.MANAGER_ID = J.EMPLOYEE_ID)
WHERE (E.HIRE_DATE > J.HIRE_DATE)

52. Display employee name, job title for the jobs employee did in the past where
the job was
done less than six months.
SELECT E.FIRST_NAME,J.JOB_TITLE,JH.START_DATE,JH.END_DATE FROM EMPLOYEES E JOIN
JOBS J
ON (E.JOB_ID=J.JOB_ID) JOIN JOB_HISTORY JH
ON (E.JOB_ID=JH.JOB_ID)
WHERE MONTHS_BETWEEN(END_DATE,START_DATE)

53. Display employee name and country in which he is working.

SELECT E.FIRST_NAME,C.COUNTRY_NAME FROM COUNTRIES C JOIN LOCATIONS L
ON (C.COUNTRY_ID=L.COUNTRY_ID)
JOIN DEPARTMENTS D
ON (L.LOCATION_ID=D.LOCATION_ID)
JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)

54. Display department name, average salary and number of employees with commission

within the department.

SELECT DEPARTMENT_ID,AVG(SALARY),COUNT(*) FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL GROUP BY DEPARTMENT_ID

SELECT D.DEPARTMENT_NAME,AVG(SALARY),COUNT(*)
FROM DEPARTMENTS D JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)
WHERE COMMISSION_PCT IS NOT NULL GROUP BY D.DEPARTMENT_NAME

SELECT d.department_name,AVG([Link]),
COUNT(CASE WHEN e.commission_pct IS NOT NULL THEN e.employee_id END)
FROM departments d JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

55. Display the month in which more than 5 employees joined in any department
located in
Sydney.
SELECT TO_CHAR(E.HIRE_DATE,'MON') FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID=D.DEPARTMENT_ID)
JOIN LOCATIONS L
ON (D.LOCATION_ID=L.LOCATION_ID)
WHERE CITY='Sydney' AND GROUP BY TO_CHAR(E.HIRE_DATE,'MON') HAVING
COUNT(EMPLOYEE_ID) >5

SELECT TO_CHAR(HIRE_DATE,'MM') FROM employees e JOIN departments d

ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE TO_CHAR(HIRE_date,'YYYY') = TO_CHAR(SYSDATE, 'YYYY')
AND TO_CHAR(HIRE_date,'MM') = TO_CHAR(SYSDATE, 'MM')
 AND [Link] = 'Sydney'
GROUP BY TO_CHAR(HIRE_DATE, 'MM')
HAVING COUNT(*) > 5;

56. Display details of departments in which the maximum salary is more than 10000.
SELECT D.DEPARTMENT_ID,MAX(SALARY) FROM DEPARTMENTS D JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)
GROUP BY D.DEPARTMENT_ID HAVING MAX(SALARY) > 10000

57. Display details of departments managed by ‘Smith’.

SELECT D.DEPARTMENT_ID,D.DEPARTMENT_NAME FROM DEPARTMENTS D JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID) WHERE E.LAST_NAME='Smith';

SELECT * FROM DEPARTMENTS WHERE DEPARTMENT_NAME='Smith'

SELECT * FROM LOCATIONS;
SELECT * FROM EMPLOYEES WHERE LAST_NAME='Smith'
SELECT * FROM JOBS
SELECT * FROM JOB_HISTORY

58. Display jobs into which employees joined in the current year
SELECT J.JOB_TITLE,E.HIRE_DATE FROM EMPLOYEES E JOIN JOBS J
ON (E.JOB_ID=J.JOB_ID) WHERE TO_CHAR(HIRE_DATE,'DD-MON-YYYY')=SYSDATE

59. Display employees who did not do any job in the past
SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID NOT IN
(SELECT EMPLOYEE_ID FROM JOB_HISTORY)

60. Display job title and average salary for employees who did a job in the past.
SELECT J.JOB_TITLE,AVG([Link]) FROM EMPLOYEES E JOIN JOBS J
ON (J.JOB_ID=E.JOB_ID)
JOIN JOB_HISTORY JH
ON (E.EMPLOYEE_ID=JH.EMPLOYEE_ID)
WHERE EMPLOYEE_ID IN
(SELECT EMPLOYEE_ID FROM JOB_HISTORY);

SELECT * FROM JOB_HISTORY;

SELECT * FROM JOBS;
SELECT * FROM EMPLOYEES;

61. Display country name, city, and number of departments where department has more
than
5 employees.
SELECT C.COUNTRY_NAME,[Link],COUNT(D.DEPARTMENT_ID) FROM COUNTRIES C JOIN LOCATIONS
L
ON (C.COUNTRY_ID=L.COUNTRY_ID)
JOIN DEPARTMENTS D
ON (L.LOCATION_ID=D.LOCATION_ID)
JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)
GROUP BY C.COUNTRY_NAME,[Link] HAVING COUNT(E.EMPLOYEE_ID)>5

62. Display details of manager who manages more than 5 employees.

SELECT MANAGER_ID,COUNT(*) FROM EMPLOYEES GROUP BY MANAGER_ID HAVING
COUNT(EMPLOYEE_ID)>5

63. Display employee name, job title, start date, and end date of past jobs of all
employees
with commission percentage null.
SELECT E.FIRST_NAME||' '||E.LAST_NAME,J.JOB_TITLE,JH.START_DATE,JH.END_DATE
FROM JOBS J JOIN JOB_HISTORY JH
ON (J.JOB_ID=JH.JOB_ID)
JOIN DEPARTMENTS D
ON (JH.DEPARTMENT_ID=D.DEPARTMENT_ID)
JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)
WHERE COMMISSION_PCT IS NULL

64. Display the departments into which no employee joined in last two years.
SELECT DEPARTMENT_ID

65. Display the details of departments in which the max salary is greater than
10000 for
employees who did a job in the past.
SELECT D.DEPARTMENT_NAME,J.JOB_TITLE,J.MAX_SALARY FROM DEPARTMENTS D JOIN
JOB_HISTORY JH
ON (D.DEPARTMENT_ID=JH.DEPARTMENT_ID)
JOIN JOBS J
ON (JH.JOB_ID=J.JOB_ID)
JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID=E.EMPLOYEE_ID)
WHERE J.MAX_SALARY > 10000

SELECT d.department_id, d.department_name, MAX([Link]) AS max_salary

FROM departments d
JOIN employees e ON d.department_id = e.department_id
JOIN job_history jh ON e.employee_id = jh.employee_id
WHERE jh.end_date IS NOT NULL
GROUP BY d.department_id, d.department_name
HAVING MAX([Link]) > 10000;

66. Display details of current job for employees who worked as IT Programmers in
the past.
SELECT

67. Display the details of employees drawing the highest salary in the department.
SELECT DEPARTMENT_ID,MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT_ID

SELECT D.DEPARTMENT_ID,MAX(SALARY)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)
GROUP BY D.DEPARTMENT_ID

68. Display the city of employee whose employee ID is 105.

SELECT [Link] FROM LOCATIONS L JOIN DEPARTMENTS D
ON (L.LOCATION_ID=D.LOCATION_ID)
JOIN EMPLOYEES E
ON (D.DEPARTMENT_ID=E.DEPARTMENT_ID)
WHERE EMPLOYEE_ID=105

SELECT * FROM LOCATIONS;

SELECT * FROM DEPARTMENTS;
SELECT * FROM EMPLOYEES;

69. Display third highest salary of all employees

SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY <
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE SALARY <
(SELECT MAX(SALARY) FROM EMPLOYEES))

70. Display department name, manager name, and salary of the manager for all
managers
whose experience is more than 5 years.
SELECT D.DEPARTMENT_NAME,E.FIRST_NAME,[Link],E.MANAGER_ID FROM DEPARTMENTS D JOIN
EMPLOYEES E
ON (D.MANAGER_ID=E.MANAGER_ID)
WHERE ROUND((SYSDATE-HIRE_DATE)/365) >5
SELECT FROM EMPLOYEES

-------------------------------- THE END ---------------------------------------

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)

SELECT T_ID,TNAME,SALARY FROM T1

1. To fetch ALTERNATE records from a table. (EVEN NUMBERED)

SELECT MOD(EMPLOYEE_ID,2)+EMPLOYEE_ID FROM EMPLOYEES

select * from emp where rowid in (select decode(mod(rownum,2),0,rowid, null) from

emp);

2. To select ALTERNATE records from a table. (ODD NUMBERED)

select * from emp where rowid in (select decode(mod(rownum,2),0,null ,rowid) from
emp);
3. Find the 3rd MAX salary in the emp table.
select distinct salARY from empLOYEES E1 where 3 =
(select count(distinct salARY) from empLOYEES e2 where [Link] <= [Link]);

4. Find the 3rd MIN salary in the emp table.

select distinct salary from employees e1 where 3 = (select count(distinct salary)
from employees
e2 where [Link] >= [Link]);

5. Select FIRST n records from a table.

select * from emp where rownum <= &n;

6. Select LAST n records from a table

select * from emp minus select * from emp where rownum <= (select count(*) - &n
from emp);

7. List dept no., Dept name for all the departments in which there are no
employees in the department.
select * from dept where deptno not in (select deptno from emp);
alternate solution: select * from dept a where not exists (select * from emp b
where [Link] = [Link]);
altertnate solution: select empno,ename,[Link],dname from emp a, dept b where
[Link](+) = [Link] and empno is null;
8. How to get 3 Max salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b
where [Link] <= [Link]) order by [Link] desc;
9. How to get 3 Min salaries ?
select distinct sal from emp a where 3 >= (select count(distinct sal) from emp b
where [Link] >= [Link]);
10. How to get nth max salaries ?
select distinct hiredate from emp a where &n = (select count(distinct sal) from
emp b where [Link] >= [Link]);
11. Select DISTINCT RECORDS from emp table.
select * from emp a where rowid = (select max(rowid) from emp b where
[Link]=[Link]);
12. How to delete duplicate rows in a table?
delete from emp a where rowid != (select max(rowid) from emp b where
[Link]=[Link]);
13. Count of number of employees in department wise.
select count(EMPNO), [Link], dname from emp a, dept b where [Link](+)=[Link]
group by [Link],dname;
14. Suppose there is annual salary information provided by emp table. How to
fetch monthly salary of each and every employee?
select ename,sal/12 as monthlysal from emp;
15. Select all record from emp table where deptno =10 or 40.
select * from emp where deptno=30 or deptno=10;
16. Select all record from emp table where deptno=30 and sal>1500.
select * from emp where deptno=30 and sal>1500;
17. Select all record from emp where job not in SALESMAN or CLERK.
select * from emp where job not in ('SALESMAN','CLERK');
18. Select all record from emp where ename in 'BLAKE','SCOTT','KING'and'FORD'.
select * from emp where ename in('JONES','BLAKE','SCOTT','KING','FORD');
19. Select all records where ename starts with ‘S’ and its lenth is 6 char.
select * from emp where ename like'S____';
20. Select all records where ename may be any no of character but it should end
with ‘R’.
select * from emp where ename like'%R';
21. Count MGR and their salary in emp table.
select count(MGR),count(sal) from emp;
22. In emp table add comm+sal as total sal .
select ename,(sal+nvl(comm,0)) as totalsal from emp;
23. Select any salary <3000 from emp table.
select * from emp where sal> any(select sal from emp where sal<3000);
24. Select all salary <3000 from emp table.
select * from emp where sal> all(select sal from emp where sal<3000);
25. Select all the employee group by deptno and sal in descending order.
select ename,deptno,sal from emp order by deptno,sal desc;
26. How can I create an empty table emp1 with same structure as emp?
Create table emp1 as select * from emp where 1=2;
27. How to retrive record where sal between 1000 to 2000?
Select * from emp where sal>=1000 And sal<2000
28. Select all records where dept no of both emp and dept table matches.
select * from emp where exists(select * from dept where [Link]=[Link])
29. If there are two tables emp1 and emp2, and both have common record. How can I
fetch all the recods but common records only once?
(Select * from emp) Union (Select * from emp1)
30. How to fetch only common records from two tables emp and emp1?
(Select * from emp) Intersect (Select * from emp1)
31. How can I retrive all records of emp1 those should not present in emp2?
(Select * from emp) Minus (Select * from emp1)
32. Count the totalsa deptno wise where more than 2 employees exist.
SELECT deptno, sum(sal) As totalsal
FROM emp
GROUP BY deptno
HAVING COUNT(empno) > 2

---------------------- END OF ADVANCE SQL ------------------------------

*** PL/SQL ALL CODE - ONKAR MOHITE ***

----IT IS EXTENSION OF THE SQL.

----PL STANDS FOR THE PROCEDURAL LANGUAGE.
----PROVIDE BLOCK STRUCTURE TO CODE (WHICH NEED TO BE EXECUTE)
----MAINTAINANCE OR CHANGES IS VERY EASY BECAUSE OF THIS BLOCK STRUCTURE.
----PROVIDES CONTROL STRUCTURE USING CONDITIONAL STATEMENT.
----PROVIDES REUSEABLE CODE.(PREPARATION IS ONE TIME BUT EXECUTION IS MANY TIMES).

*** PL/SQL BLOCK STRUCTURE ***

1. DECLARE (OPTIONAL)
----TO DECALRE THE VARIABLE, CURSORS, EXCEPTION_VARIABLE, COLLECTIONS, RECORDS.

2. BEGIN (MANDATORY)
----SQL STATEMENTS
----CONDITIONS / CONTROL STRUCTURE.

3. EXCEPTION (OPTIONAL)
----ACTIONS TO THE VARIOUS ERRORS WHEN CODE FAILS.

4. END; (MANDATORY)

*** PL/SQL BLOCK TYPES ***

1. UN-NAMED BLOCK / SIMPLE BLOCK(ANONYMOUS BLOCK):
----THIS BLOCK DON'T HAVE NAME.
----WE CAN'T STORE THIS AS A DATABASE OBJECT PERMANENTLY.
----WE CAN WRITE THE MULTIPLE SQL STATEMENT INSIDE THE BEGIN
----IT IS TEMPORARY.
----WE CAN NOT REUSE IT.

*** THERE IS TWO TYPES OF NAMED BLOCK ***

1. PROCEDURE:
CREATE OR REPLACE PROCEDURE PRO_NAME..... --MAY OR MAY NOT RETURN

2. FUNCTION:
CREATE OR REPLACE FUNCTION FUN_NAME..... --ALWAYS RETURN SOMETHINGS.

2. NAMED BLOCK/ PROCEDURAL BLOCK:

----THIS BLOCK HAS NAME.
----WE CAN STORE THIS AS A DATABASE OBJECT PERMANENTLY.

CREATE OR REPLACE PROCEDURE P_NAME

IS/AS
VARIABLE/ CURSOR/ EXCEPTION ---IN THIS DECLARE IS INBUILD.
BEGIN (MANDATORY) ---IT IS NAMED PERMANENTLY WE CAN CALL
IT BY NAME
SQL+PLSQL COMMANDS
EXCEPTION (OPTIONAL)
ERROR HANDLING
END (MANDATE)

3. FUNCTIONAL BLOCK:-
CREATE OR REPLACE FUNCTION F_NAME
RETURN DATA TYPE
IS/ AS ---FUNCTION MUST RETURN VALUE.
VARIABLE / EXCEPTION / CURSOR
BEGIN (MANDATE)
SQL + PLSQL COMMANDS
EXCEPTION (OPTIONAL)
ERROR HANDLING
END (MANDATE)

>>> TYPES OF VARIABLE IN PL/SQL BLOCK.

1. LOCAL / SCALER VARIABLE.
2. REFERENCE VARIABLE.
3. COMPOSITE VARIABLE / USER DEFINED TYPE VARIABLE.
4. LOB VARIABLE. (LARGE OBJECT VARIABLE)
5. NON-PLSQL / BIND OR LARGE GLOBAL OR HOST VARIABLE.

1. LOCAL VARIABLE / SCALAR VARIABLE:-

----SCOPE OF VARIABLE WITH IN BLOCK. NOT ACCESIBLE OUTSIDE THE BLOCK.

2. REFERENCE VARIABLE:-
----AT RUN TIME IT WILL GET THE VALUE FROM USER.
&X1, :NEW, :OLD

3. COMPOSITE VARIABLE.
----WHEN VARIABLE STORE MORE THAN ONE VALUE THIS VARIABLE KNOWN AS COMPOSITE
VARIABLE.

>>> ACCEPT TWO NUMBER FROM USER AND DISPLAY TOTAL?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
N2 NUMBER(30):=&X2;
N3 NUMBER(30):=0;
BEGIN
N3:=N1+N2;
DBMS_OUTPUT.PUT_LINE(N3);
END;

>>> ACCEPT TWO NO FROM USER AND DISPLAY THE REMAINDER AND QUOTIENT?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
N2 NUMBER(30):=&X2;
N3 NUMBER(30);
N4 NUMBER(30);
BEGIN
N3:=MOD(N1,N2);
N4:=N1/N2;
DBMS_OUTPUT.PUT_LINE('TWO NUMBER REMAINDER IS'||' '||N3
||' '||'TWO NUMBER QUOTIENT IS ='||N4);
END;
>>> ACCEPT ONE NUMBER FROM USER AND DISPLAY 25% FROM THAT NUMBER?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(20):=&X1;
N2 NUMBER(30):=0;
BEGIN
N2:=N1*0.25;
DBMS_OUTPUT.PUT_LINE('25 % OF USER VALUE'||' '||N2);
END;

>>> ACCEPT DATA FROM USER AND DISPLAY IT IN GIVEN FORMAT -(MONDAY-JUNE-2023)
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 DATE:='&X1';
N2 DATE;
BEGIN
N2:=N1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(N2,'DAY-MONTH-YYYY'));
END;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 DATE:='&X1';
N2 VARCHAR2(30);
BEGIN
N2:=TO_CHAR(N1,'DAY-MONTH-YYYY');
DBMS_OUTPUT.PUT_LINE(N2);
END;

>>> TAKE ONE FIRST NAME FROM USER AND DISPLAY HIS SURNAME IN CAPITAL?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 VARCHAR2(30):='&X1';
N2 VARCHAR2(30);
BEGIN
N2:=UPPER(SUBSTR(N1,INSTR(' ',1,2)));
DBMS_OUTPUT.PUT_LINE(N2);
END;

>>> ACCEPT TWO NUMBER FROM USER AND DO DIVISION,

MULTIPLICATION,SUBSTRACTION,ADDITION?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
N2 NUMBER(30):=&X2;
DIV NUMBER(30);
MUL NUMBER(30);
ADD NUMBER(30);
SUB NUMBER(30);
BEGIN
DIV:=N1/N2;
MUL:=N1*N2;
ADD:=N1+N2;
SUB:=N1-N2;
DBMS_OUTPUT.PUT_LINE('DIVISION IS ='||DIV||' '||'MULTIPLICATION IS ='||MUL||
' '||'ADDITION IS ='||ADD||' '||'SUBSTRACTION IS ='||SUB);
END;

>>> TRACE THE OUTPUT.

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER (10):=7;
N2 NUMBER(10):=5;
BEGIN
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
N1:=N2+N2;
N2:=N1+N2;
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
N1:=N1-N2;
N2:=N1+N2;
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
N2:=N1+N2+10;
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
END;

>>> TRACE THE OUTPUT?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE ---WE CAN NOT DIVIDE WITH NULL
N1 NUMBER (10):=7;
N2 NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
N1:=N1+N2;
N2:=N1+N2;
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
N1:=N1-N2;
N1:=N1+N2;
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
N2:=N1+N2+10;
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
END;

>>> TRACE THE OUTPUT?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER (10):=15; --- (:= ASSIGNMENT OPERATOR)
N2 NUMBER DEFAULT 20; ---BY DEFAULT VARIABLE TAKE THE
VALUE.
BEGIN
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
N1:=N1+N2;
N2:=N1+N2;
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
END;

>>> TRACE THE OUTPUT?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER (10):=15;
N2 NUMBER DEFAULT 20;
BEGIN
V1 DEFAULT 25; ---ERROR
DBMS_OUTPUT.PUT_LINE(N1||' '||N2);
END;

>>> TRACE THE OUTPUT?

SET SERVEROUTPUT ON
DECLARE
V1 CONSTANT NUMBER(10):=7; ---CONSTANT KEYWORD.
BEGIN
V1:=25;
DBMS_OUTPUT.PUT_LINE(V1);
END;

>>> TRACE THE OUTPUT?

SET SERVEROUTPUT ON
DECLARE
V1 CONSTANT NUMBER(10):=7;
BEGIN
DBMS_OUTPUT.PUT_LINE(V1);
END;

>>> TRACE THE OUTPUT?

SET SERVEROUTPUT ON
DECLARE
V1 NUMBER(10) NOT NULL:=7; ---NOT NULL KEYWORD
BEGIN
DBMS_OUTPUT.PUT_LINE(V1);
END;

>>> TRACE THE OUTPUT?

SET SERVEROUTPUT ON
DECLARE
V1 CONSTANT NUMBER(10) NOT NULL:=7; ---CONSTANT VARIABLE FOR EX. PI VALUE
BEGIN
V1:=50;
DBMS_OUTPUT.PUT_LINE(V1);
END;

>>> TRACE THE OUTPUT.

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
X1 NUMBER(30):=4;
BEGIN
DBMS_OUTPUT.PUT_LINE(X1);
X1:=15;
DBMS_OUTPUT.PUT_LINE(X1);
X1:=X1+4;
DBMS_OUTPUT.PUT_LINE(X1);
X1:=X1-7;
DBMS_OUTPUT.PUT_LINE(X1);
END;

>>> TRACE THE OUTPUT?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
X1 NUMBER(30):=20;
X2 NUMBER(30):=15;
BEGIN
DBMS_OUTPUT.PUT_LINE(X1||' '||X2);
X1:=X1+X2;
DBMS_OUTPUT.PUT_LINE(X1||' '||X2);
X2:=X1+X2;
DBMS_OUTPUT.PUT_LINE(X1||' '||X2);
X1:=X1+X2+X1;
DBMS_OUTPUT.PUT_LINE(X1||' '||X2);
X2:=X1;
DBMS_OUTPUT.PUT_LINE(X1||' '||X2);
END;

*** NESTED BLOCK ***

SET SERVEROUTPUT ON
DECLARE
N1 NUMBER(30):=10;
BEGIN
DECLARE
N2 NUMBER(30):=30;
BEGIN
DBMS_OUTPUT.PUT_LINE('I AM INNER BLOCK'||' '||N2);
END;
DBMS_OUTPUT.PUT_LINE('I AM OUTER BLOCK'||' '||N1);
END;

2. EXAMPLE:-
SET SERVEROUTPUT ON
DECLARE
N1 NUMBER(30):=10;
BEGIN
DECLARE
N2 NUMBER(30):=30;
BEGIN
DBMS_OUTPUT.PUT_LINE('I AM INNER BLOCK'||' '||N1);
END;
DBMS_OUTPUT.PUT_LINE('I AM OUTER BLOCK'||' '||N1);
END;

[Link].
SET SERVEROUTPUT ON
DECLARE
X1 NUMBER(30):=10;
BEGIN
DECLARE
X2 NUMBER(30):=30;
BEGIN
DBMS_OUTPUT.PUT_LINE('I AM INNER BLOCK'||' '||X1||' '||X2);
END;
DBMS_OUTPUT.PUT_LINE('I AM OUTER BLOCK'||' '||X1);
END;

4. WE CAN NOT CALL INNER BLOCK IN OUTSIDE BLOCK?

SET SERVEROUTPUT ON
DECLARE
X1 NUMBER(30):=10;
BEGIN
DECLARE
X2 NUMBER(30):=30;
BEGIN
DBMS_OUTPUT.PUT_LINE('I AM INNER BLOCK'||' '||X1||' '||X2);
END;
DBMS_OUTPUT.PUT_LINE('I AM OUTER BLOCK'||' '||X1||' '||X2);
END;

>>> TO PRINT THE HELLO ALL MESSAGE THROUGH THE PL/SQL BLOCK?
SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
DBMS_OUTPUT.PUT_LINE('HELLO ALL');
END;

>>> DISPLAY SYSDATE USING PL/SQL?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V1 DATE;
BEGIN
SELECT SYSDATE INTO V1 FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V1);
END;

>>> DISPLAY EMPLOYEES FIRST NAME IN PL/SQL?

SET SERVEROUTPUT ON
DECLARE
X VARCHAR2(20);
BEGIN
SELECT FIRST_NAME INTO X FROM EMPLOYEES
WHERE EMPLOYEE_ID=101;
DBMS_OUTPUT.PUT_LINE(X);
END;

>>> IF VARIABLE NAME IS SAME THEN NEED TO CALL WITH MENTION?

SET SERVEROUTPUT ON
BEGIN <<BCA>>
DECLARE
X1 NUMBER(30):=10;
BEGIN
DECLARE
X1 NUMBER(30):=30;
BEGIN
DBMS_OUTPUT.PUT_LINE('I AM INNER BLOCK'||' '||BCA.X1);
DBMS_OUTPUT.PUT_LINE('I AM INNER BLOCK'||' '||X1);
END;
DBMS_OUTPUT.PUT_LINE('I AM OUTER BLOCK'||' '||X1);
END;
END BCA;

>>> TRACE THE OUTPUT?

SET SERVEROUTPUT ON
DECLARE
V1 NUMBER(30):=7;
BEGIN
 DECLARE
V2 NUMBER(30):=15;
BEGIN
V1:=V1+V2;
DBMS_OUTPUT.PUT_LINE(V1||' '||V2);
END;
DBMS_OUTPUT.PUT_LINE(V1);
END;

>>> INNER BLOCK TAKE USER TWO VALUE AND DISPLAY HIS ADDITION IN OUTER BLOCK AND
25%?
SET SERVEROUTPUT ON
DECLARE
D1 NUMBER(30);
TOTAL NUMBER(30);
BEGIN
DECLARE
N1 NUMBER(30):=&X1;
N2 NUMBER(30):=&X2;
BEGIN
TOTAL:=N1+N2;
DBMS_OUTPUT.PUT_LINE(TOTAL);
END;
D1:=(TOTAL*0.25);
DBMS_OUTPUT.PUT_LINE(D1);
END;

>>> INSERT DATA IN T3 TO T4;

SET SERVEROUTPUT ON
DECLARE
X1 NUMBER(30):=&X1;
X2 VARCHAR2(30):='&X2';
X3 VARCHAR2(30):='&X3';
BEGIN
INSERT INTO T3 VALUES (X1,X2,X3);
DBMS_OUTPUT.PUT_LINE('DATA INSERTED');
END;

>>> REMOVE ROW FROM T3 TABEL?

SET SERVEROUTPUT ON
DECLARE
X1 NUMBER(30):=&X1;
BEGIN
DELETE FROM T3 WHERE CNO=X1;
DBMS_OUTPUT.PUT_LINE('ROW DELETED');
END;

>>> UPDATE ANY ROW OF THE T3 TABLE?

SET SERVEROUTPUT ON
DECLARE
X1 NUMBER(30):=&X1;
BEGIN
UPDATE T3 SET CLOC='BEED' WHERE CNO =X1;
DBMS_OUTPUT.PUT_LINE('ROWS UPDATED');
END;

>>> DISPLAY ANY ROW FROM T3 TABLE?

SET SERVEROUTPUT ON
DECLARE
X1 NUMBER(30);
X2 VARCHAR2(30);
X3 VARCHAR2(30);
BEGIN
SELECT CNO,CNAME,CLOC INTO X1,X2,X3 FROM T3 WHERE CNAME='A';
DBMS_OUTPUT.PUT_LINE(X1||' '||X2||' '||X3);
END;

>>> MERGE DATA FROM T3 TO T4;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
T3 Z1%ROWTYPE;
T4 Z2%ROWTYPE;
BEGIN
MERGE INTO Z2 USING Z1
ON ([Link] = [Link])
WHEN MATCHED THEN
UPDATE SET [Link] = [Link],
[Link] = [Link]
DELETE WHERE [Link]=20
WHEN NOT MATCHED THEN
INSERT VALUES ([Link],[Link],[Link]);
DBMS_OUTPUT.PUT_LINE('SUCCESS');
END;

>>> TAKE ONE EMPLOYEE_ID FROM USER AND DISPLAY HIS FULL NAME AND SALARY?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 VARCHAR2(30);
N2 VARCHAR2(30);
N3 NUMBER(30);
N4 NUMBER(30):=&X1;
BEGIN
SELECT FIRST_NAME,LAST_NAME,SALARY INTO N1,N2,N3 FROM EMPLOYEES
WHERE EMPLOYEE_ID=N4;
DBMS_OUTPUT.PUT_LINE('FULL_NAME ='||' '||N1||' '||N2||' '||N3);
END;

>>> TAKE ONE EMPLOYEE ID FROM USER AND DISPLAY THE 18% OF SALARY?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30);
N2 NUMBER(30):=&X1;
BEGIN
SELECT SALARY INTO N1 FROM EMPLOYEES WHERE EMPLOYEE_ID=N2;
DBMS_OUTPUT.PUT_LINE(N1*0.18);
END;

>>> TAKE A ONE EMPLOYEE_ID FROM USER AND DISPLAY HIS JOINING MONTH?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 DATE;
N2 NUMBER(30):=&X1;
BEGIN
SELECT HIRE_DATE INTO N1 FROM EMPLOYEES WHERE EMPLOYEE_ID=N2;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(N1,'MONTH'));
END;

>>> TAKE A EMPLOYEE ID FROM USER AND DISPLAY FIRST THREE LETTER AND PADDING FROM
RIGHT SIDE?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 EMPLOYEES.FIRST_NAME%TYPE;
N2 VARCHAR2(30):=&X2;
BEGIN
SELECT RPAD(FIRST_NAME,LENGTH(FIRST_NAME)*2,'#') INTO N1 FROM EMPLOYEES WHERE
EMPLOYEE_ID=N2;
DBMS_OUTPUT.PUT_LINE(N1);
END;

>>> TAKE ONE EMPLOYEE IS FROM USER AND DISPLAY THE

HELLO EMPLOYEE_NAME YOUR SALARY FOR MONTH NAME IS SALARY?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
N2 VARCHAR2(30);
N3 DATE;
N4 NUMBER(30);
BEGIN
SELECT FIRST_NAME,HIRE_DATE,SALARY INTO N2,N3,N4 FROM EMPLOYEES WHERE
EMPLOYEE_ID=N1;
DBMS_OUTPUT.PUT_LINE('HELLO '||N2||' YOUR SALARY FOR THIS '||
TO_CHAR(N3,'MONTH')||'IS '||N4);
END;

>>> USING CASE FETCH THE CATEGORY WITH THEIR MARKING?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
RESULT1 VARCHAR2(30);
BEGIN
RESULT1:= CASE WHEN N1 BETWEEN 80 AND 100 THEN 'EXCELLENT'
WHEN N1 BETWEEN 60 AND 80 THEN 'GOOD'
ELSE 'PASS' END;
DBMS_OUTPUT.PUT_LINE(RESULT1);
END;

>>> TAKE ENAME FROM USER AND DISPLAY THE MSG GIVEN BY THAT USER?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 VARCHAR2(30):='&X1';
NAME1 VARCHAR2(20);
BEGIN
NAME1:=CASE WHEN N1='AJAY' THEN 'HELLO'
WHEN N1 IN('VIJAY','AMAR') THEN 'HII'
WHEN N1='JAY' THEN 'HEY'
ELSE 'WELCOME' END;
DBMS_OUTPUT.PUT_LINE(NAME1);
END;
>>> TAKE A USER DATE AND DISPLAY THE MONTH OF JOINING?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 DATE:='&X1';
N2 VARCHAR2(30);
N3 VARCHAR2(30);
BEGIN
N2:=TO_CHAR(N1,'MON');
N3:=CASE N2 WHEN 'JUN' THEN 'JOINING IN JUNE'
WHEN 'AUG' THEN 'JOINING IN AUG'
WHEN 'OCT' THEN 'JOINING IN OCT'
ELSE 'INVALID MONTH' END;
DBMS_OUTPUT.PUT_LINE(N3);
END;

*** IF ELSE ***

A. GENRAL IF-ELES:
SYNTAX: IF
THEN
ELSE
END IF;
END;

B. NESTED IF-ELSE:
SYNTAX:IF
THEN
IF
THEN
ELSE
END IF;
ELSE
IF
ELSE
END IF;
END IF;

C. LADDER IF ELSE:
SYNTAX: IF
THEN
ELSIF
THEN
ELSIF
THEN
ELSE
END IF;
END;

*** GENRAL IF-ELES ***

>>> EMPLOYEE_ID=?
FIRST_NAME=?
SALARY=?
ENTER_CHOICE=1. SEARCH
 2. MODIFY
3. REMOVE
4. REGISTER
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=&X1;
N2 VARCHAR2(20):='&X2';
N3 NUMBER(10):=&X3;
CHOICE VARCHAR2(20):='&X4';
BEGIN
IF CHOICE='SEARCH'
THEN
SELECT TNO,TNAME,TCOST INTO N1,N2,N3 FROM T1
WHERE TNO=N1;
DBMS_OUTPUT.PUT_LINE(N1||N2||N3);
ELSIF CHOICE='MODIFY'
THEN
UPDATE T1
SET TNAME=N2
WHERE TNO=N1;
DBMS_OUTPUT.PUT_LINE('DATA UPDTAE');
ELSIF CHOICE = 'REMOVE'
THEN
DELETE FROM T1
WHERE TNO =N1 ;
DBMS_OUTPUT.PUT_LINE('Data Removed');
ELSIF CHOICE = 'REGISTER'
THEN
INSERT INTO T1(TNO, TNAME,TCOST)
VALUES (N1,N2,N3);
DBMS_OUTPUT.PUT_LINE('Data Registered'||N1||N2||N3);
END IF;
END;

>>> TAKE TWO NUMBER FROM USER AND DISPLAY THE MAX NO?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
N2 NUMBER(30):=&X2;
BEGIN
IF N1 > N2
THEN
DBMS_OUTPUT.PUT_LINE('FIRST NUMBER IS MAX:= '||N1);
ELSE
DBMS_OUTPUT.PUT_LINE('SECOND NUMBER IS MAX:= '||N2);
END IF;
END;

>>> IF N1 IS GREATER THAN N2 THEN RETURN 'N1 IS GREATER THAN N2'

IF IT IS NOT GREATER THEN RETURN 'N2 IS GREATER THAN N2'? USING SINGLE AMPERSAND?
SET SERVEROUTPUT ON
DECLARE
N1 NUMBER(20):=&X1;
N2 NUMBER(20):=&X2;
BEGIN
IF N1>N2
THEN
DBMS_OUTPUT.PUT_LINE('N1 IS GREATER THAN N2');
ELSE
DBMS_OUTPUT.PUT_LINE('N2 IS GREATER THAN N1');
END IF;
END;

>>> PLS CHECK N1 IS ODD OR EVEN? USING SINGEL AMPERSAND?

SET SERVEROUTPUT ON
DECLARE
N1 NUMBER(10):=&X1;
BEGIN
IF N1 MOD 2 =0
THEN
DBMS_OUTPUT.PUT_LINE('IT IS EVEN');
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS ODD');
END IF;
END;

>>> PLS CHECK THE GIVEN VALUE IS DIVISIBLE BY 5 OR NOT?

SET SERVEROUTPUT ON
DECLARE
N1 NUMBER(10):=&X1;
BEGIN
IF N1 MOD 5=0
THEN
DBMS_OUTPUT.PUT_LINE('DIVISIBLE BY 5 ');
DBMS_OUTPUT.PUT_LINE('it is ok');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT DIVISIBLE BY 5');
END IF;
END;

>>> IF USER INPUT DATA BETWEEN JAN TO AUG THEN IT WILL RETURN YES IT IS BETWEEN.
OTHERWISE IT WILL RETURN IT IT NOT BETWEEN JAN TO AUG.
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
EDATE DATE:='&X1';
N1 NUMBER(10);
BEGIN
N1:=TO_CHAR(EDATE,'MM');
IF N1 BETWEEN 1 AND 8
THEN
DBMS_OUTPUT.PUT_LINE('YES IT IS BETWEEN JAN TO AUG');
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS NOT BETWEEN JAN TO AUG');
END IF;
END;

>>> IF NAME START WITH N THEN IT WILL RETURN FULL NAME IF IT IS NOT THEN IT WILL
RETURN NOT N?
SET SERVEROUTPUT ON
DECLARE
V1 VARCHAR2(10) :='&X1';
BEGIN
IF V1 LIKE 'N%'
THEN
DBMS_OUTPUT.PUT_LINE('NAME START WITH N');
ELSE
DBMS_OUTPUT.PUT_LINE ('NAME NOT START WITH N');
END IF;
END;

>>> CHECK FIRST_NAME START WITH D OR NOT?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 VARCHAR2(10);
BEGIN
SELECT FIRST_NAME INTO N1 FROM EMPLOYEES
WHERE EMPLOYEE_ID=&X1;
IF N1 LIKE 'D%'
THEN
DBMS_OUTPUT.PUT_LINE ('N1 START FROM D:'||' '||N1);
ELSE
DBMS_OUTPUT.PUT_LINE ('N1 NOT STARTING FROM D');
END IF;
END;

>>> CHECK THE PERSON AGE CLASS WITH THERE AGE?

SET SERVEROUTPUT ON
DECLARE
AGE NUMBER:=&x1;
BEGIN
IF AGE <= 18
THEN
DBMS_OUTPUT.PUT_LINE('I AM A CHILD');
ELSE
DBMS_OUTPUT.PUT_LINE('I AM A NOT A CHILD');
END IF;
END;

>>> CHECK THE TABLE HAVING DATA OR NOT? OR COUNT OF TABLE?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
VOL NUMBER(20);
BEGIN
SELECT COUNT (*) INTO VOL FROM EMPLOYEES;
IF VOL > 0
THEN
DBMS_OUTPUT.PUT_LINE('TABLE HAVING DATA');
DBMS_OUTPUT.PUT_LINE(VOL);
ELSE
DBMS_OUTPUT.PUT_LINE ('TABLE NOT HAVING DATA');
DBMS_OUTPUT.PUT_LINE(VOL);
END IF;
END;

>>> TAKE A NAME FROM USER AND CHECK THE FIRST NAME LENGTH IS GREATER THAN 5 OR NOT?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 VARCHAR2(30):='&X1';
BEGIN
IF LENGTH(N1) > 5
THEN
DBMS_OUTPUT.PUT_LINE('IT IS GREATER THAN 5');
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS NOT GREATER THAN 5');
END IF;
END;

>>> TAKE A EMPLOYEE_ID FROM USER AND DISPLAY FIRST NAME AND SALARY? WHEN FIRST NAME
START WITH
'S' OR SALARY > 15000 THEN PRINT HELLO OTHERWISE BYE?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
N2 VARCHAR2(30);
N3 NUMBER(30);
BEGIN
SELECT FIRST_NAME,SALARY INTO N2,N3 FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
IF N2 LIKE 'S%' AND N3 > 15000
THEN
DBMS_OUTPUT.PUT_LINE(N2||' HELLO '||N3);
ELSE
DBMS_OUTPUT.PUT_LINE(' BYE ');
END IF;
END;

>>> TAKE A EMPLOYEE ID FROM USER. IF SALARY IS GREATER THAN 15000 THEN ADD 20%
BONUS
IF IT IS NOT THEN ADD 8%?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
N2 NUMBER(30);
BEGIN
SELECT SALARY INTO N2 FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
IF N2 > 15000
THEN
DBMS_OUTPUT.PUT_LINE(N2*0.2);
ELSE
DBMS_OUTPUT.PUT_LINE(N2*0.08);
END IF;
END;

>>> IF EMP NO IS > 100 AND NAME START WITH 'N' AND SALARY > 5000
THEN INSERT VALUES IN MO TABLE OTHERWIE INSERT INTO SM TABLE?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
N2 VARCHAR2(30):='&X2';
N3 NUMBER(30):=&X3;
BEGIN
IF N1 > 10 AND N2 LIKE 'N%' AND N3 > 100
THEN
INSERT INTO MO VALUES (8,'JAYWANT',1200);
DBMS_OUTPUT.PUT_LINE('DATA INSERTED IN MO TABLE');
ELSE
INSERT INTO SM VALUES (30,'F','KOKAN');
DBMS_OUTPUT.PUT_LINE('DATA INSERTED IN SM TABLE');
END IF;
END;

*** NESTED IF-ELES ***

>>> DISPLAY FIRST_NAME AND SALARY. IF SALARY IS IN BETWEEN 15000 AND 25000
THEN CHECK FIRST NAME END WITH N?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 VARCHAR2(10);
N2 NUMBER(10);
BEGIN
SELECT FIRST_NAME, SALARY INTO N1, N2 FROM EMPLOYEES
WHERE EMPLOYEE_ID=&X1;
IF N2 BETWEEN 15000 AND 25000
THEN
IF N1 LIKE '%N'
THEN
DBMS_OUTPUT.PUT_LINE(N1);
ELSE
DBMS_OUTPUT.PUT_LINE('NAME NOT ENDING WITH N');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID SALARY');
END IF;
END;

>>> IF EMPLOYEE ID IS EVEN THEN CHECK HIRE DATE IS GREAATER THAN `1987
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
SAL NUMBER(10);
JOIN1 DATE;
EMP_ID NUMBER(10):=&X1;
BEGIN
SELECT SALARY, HIRE_DATE INTO SAL, JOIN1 FROM EMPLOYEES WHERE EMPLOYEE_ID=EMP_ID;
IF MOD (EMP_ID,2) =0
THEN
IF TO_CHAR(JOIN1,'YYYY') > 1987
THEN
DBMS_OUTPUT.PUT_LINE(SAL||JOIN1);
ELSE
DBMS_OUTPUT.PUT_LINE('HIRE DATE IS LESSER THAN 1987');
END IF;
ELSE
IF SAL MOD 5=0
THEN
DBMS_OUTPUT.PUT_LINE('IT IS DIVISIBLE BY 5');
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS NOT DIVISIBLE BY 5');
END IF;
END IF;
END;
>>> FIRST CHECK EMP SALARY IS BETWEEN 15000 AND 25000.
IF IT IS THEN CHECK HIS NAME LAST WORD IS N.
IF IT IS NOT FIT IN ABOVE CONDITION THEN INVALID EMP?
SET SERVEROUTPUT ON
DECLARE
NAME_1 VARCHAR2(10);
SAL NUMBER(10);
BEGIN
SELECT FIRST_NAME, SALARY INTO NAME_1, SAL FROM EMPLOYEES
WHERE EMPLOYEE_ID='&X1';
IF
SAL BETWEEN 15000 AND 25000
THEN
IF NAME_1 LIKE '%n'
THEN
DBMS_OUTPUT.PUT_LINE ('THIS EMPLOYEE IS ELIGIBLE FOR HIKE');
ELSE
DBMS_OUTPUT.PUT_LINE ('THIS EMPLOYEE IS NOT ELIGIBLE FOR HIKE');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('THIS EMP IS FAILED THE FIRST_TEST');
END IF;
END;

>>> IF N1 IS GREATER THAN 50 THEN CHECK IT IS EVEN OR ODD.

IF IT IS NOT GRATER THAN 50 THEN RETURN OUTPUT N1 IS LESS THAN 50.
SET SERVEROUTPUT ON
DECLARE
N1 NUMBER(10):=&X1;
BEGIN
IF N1>50
THEN
IF N1 MOD 2 =0
THEN
DBMS_OUTPUT.PUT_LINE('N1 IS EVEN');
ELSE
DBMS_OUTPUT.PUT_LINE('N1 IS ODD');
END IF;
DBMS_OUTPUT.PUT_LINE('N1 IS GREATER THAN 50');
ELSE
DBMS_OUTPUT.PUT_LINE ('N1 IS LESS THAN 50');
END IF;
END;

>>> SET A USER NAME AND PASSWORD. USER NAME START WITH A AND MUST HAVE @
AND THE LENGTH SHOULD BE 6. AND PASSWORD MUST HAVE # AND PASSWORD LAST NUMBER IS
123
AND LENGTH SHOULD BE 8?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 VARCHAR2(20):='&USERNAME';
N2 VARCHAR(20):='&PASSWORD';
BEGIN
IF N1 LIKE 'A%' AND N1 LIKE '%@%' AND LENGTH (N1)=6
THEN
IF N2 LIKE '%#%' AND N2 LIKE '%123' AND LENGTH (N2)=8
THEN
 DBMS_OUTPUT.PUT_LINE('USER NAME AND PASSWORD SUCCESSFUL:'||' '||N1||' '||N2);
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID PASSWORD');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID USERNAME');
END IF;
END;

>>> SET A USER NAME AND PASSWORD. USER NAME START WITH A AND MUST HAVE @
AND THE LENGTH SHOULD BE 6. AND PASSWORD MUST HAVE # AND PASSWORD LAST NUMBER IS
123
AND LENGTH SHOULD BE 8?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
X1 VARCHAR2(20):='&USER_NAME';
X2 VARCHAR2(20):='&PASSWORD';
BEGIN
IF X1 LIKE 'A%' AND X1 LIKE '%@%' AND LENGTH (X1)=6
THEN
IF X2 LIKE '%#%' AND X2 LIKE '%123' AND LENGTH (X2)=8
THEN
DBMS_OUTPUT.PUT_LINE ('LOGIN SUCCESFUL');
ELSE
DBMS_OUTPUT.PUT_LINE('PASS WORD ERROR');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE ('INVALID LOGIN DETAIL');
END IF;
END;

>>> FIRST CHECK N1>N2. IF IT IS THEN CHECK N1 IS EVEN OR ODD.

IF N1 IS NOT GRATER THAN N2 THEN CHECK N2 IS DIVISIBLE BY 5 OR NOT.
SET SERVEROUTPUT ON
DECLARE
N1 NUMBER (10):=&X1;
N2 NUMBER (10):=&X2;
BEGIN
IF N1>N2
THEN
IF N1 MOD 2=0
THEN
DBMS_OUTPUT.PUT_LINE('IT IS EVEN');
ELSE
DBMS_OUTPUT.PUT_LINE('IS IS ODD');
END IF;
ELSE
IF N2 MOD 5 =0
THEN
DBMS_OUTPUT.PUT_LINE('IT IS DIVISIBLE BY 5');
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS NOT DIVISIBLE BY 5');
END IF;
END IF;
END;
 *** LADDER IF-ELSE ***

>>> COMBINATION OF ALL THREE IF-ELSE?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10) :=&X1;
CHOICE NUMBER(10):=&X2;
BEGIN
IF CHOICE =5
THEN
IF MOD 5 =0
THEN
DBMS_OUTPUT.PUT_LINE ('IT IS DIVISIBLE BY 5');
ELSE
DBMS_OUTPUT.PUT_LINE ('IT IS NOT DIVISIBLE BY 5');
END IF;
ELSIF
CHOICE =7
THEN
IF MOD 7=0
THEN
DBMS_OUTPUT.PUT_LINE ('IT IS DIVISIBLE BY 7');
ELSE
DBMS_OUTPUT.PUT_LINE ('IT IS NOT DIVISIBLE BY 7');
ELSIF
CHOICE=2
THEN
IF MOD 2 =0
THEN
DBMS_OUTPUT.PUT_LINE ('IT IS DIVISIBLE BY 2');
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS NOT DIVISBLE BY 2');
ELSE
DBMS_OUTPUT.PUT_LINE ('INVALID OPERATOR');
END IF;
END;

>>> CREATE CALCULATOR USING PL/SQL LADDER IF-ELSE?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=&X1;
N2 NUMBER(10):=&X2;
OP VARCHAR2(20):='&X3';
RESULT NUMBER(10);
BEGIN
IF OP='+'
THEN
RESULT:= N1+N2;
DBMS_OUTPUT.PUT_LINE ('ADDITION IS '||RESULT);
ELSIF
OP='-'
THEN
RESULT:=N1-N2;
DBMS_OUTPUT.PUT_LINE ('SUBSTRACTION IS '||RESULT);
ELSIF
OP='*'
THEN
RESULT:=N1*N2;
DBMS_OUTPUT.PUT_LINE ('MULTIPLICATION IS '||RESULT);
ELSIF
OP='/'
THEN
RESULT :=N1/N2;
DBMS_OUTPUT.PUT_LINE ('DIVISION IS '||RESULT);
ELSE
DBMS_OUTPUT.PUT_LINE ('INVALID OPERATOR');
END IF;
END;

>>> IF EMPLOYEE SAL BETWEEN 10K TO 15K THEN PRINT AVG.

IF SAL BETWEEN 20K TO 15K THEN PRINT GOOD.
IF SAL BETWEEN 30K TO 35K THEN PRINT BEST.
OTHERWISE PRINT POOR SALARY?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
SAL NUMBER(10);
BEGIN
SELECT SALARY INTO SAL FROM EMPLOYEES
WHERE EMPLOYEE_ID=&X1;
IF SAL BETWEEN 10000 AND 15000
THEN
DBMS_OUTPUT.PUT_LINE ('AVG');
ELSIF
SAL BETWEEN 20000 AND 25000
THEN DBMS_OUTPUT.PUT_LINE ('GOOD');
ELSIF
SAL BETWEEN 30000 AND 35000
THEN
DBMS_OUTPUT.PUT_LINE('BEST');
ELSE
DBMS_OUTPUT.PUT_LINE ('POOR');
END IF;
END;

>>> IF CITY IS PUNE OR MUMBAI THEN PRINT HELLO. CITY IS NAGPUR THEN PRINT BYE.
OR CITY IS SOLAPUR OR NASHIK THEN PRINT WELCOME?
AND IF IT IS NOT AVAILABLE THEN PRINT INVALID?
SET SERVEROUTPUT ON
DECLARE
CITY VARCHAR2(10):='&X1';
BEGIN
IF CITY = 'PUNE' OR CITY = 'MUMBAI'
THEN
DBMS_OUTPUT.PUT_LINE ('HELLO');
ELSIF
CITY = 'NAGPUR'
THEN DBMS_OUTPUT.PUT_LINE ('BYE');
ELSIF
CITY = 'SOLAPUR' OR CITY= 'NASHIK'
THEN
DBMS_OUTPUT.PUT_LINE ('WELCOME');
ELSE
DBMS_OUTPUT.PUT_LINE ('INVALID CITY');
END IF;
END;

>>> DISPLAY SPPU MARKING SYSTEM AND GRADE DISTRIBUTION?

SET SERVEROUTPUT ON
DECLARE
MARKS NUMBER(10):=&X1;
BEGIN
IF MARKS >=50 AND MARKS< 60
THEN
DBMS_OUTPUT.PUT_LINE ('SECOND CLASS');
ELSIF
MARKS>=60 AND MARKS<70
THEN
DBMS_OUTPUT.PUT_LINE ('FIRST_CLASS');
ELSIF
MARKS >=70 AND MARKS <=100
THEN
DBMS_OUTPUT.PUT_LINE ('DISTINCTION');
ELSE
DBMS_OUTPUT.PUT_LINE ('FAIL');
END IF;
END;

>>> IF STUDENT MARKS IS GREATER THAN 50 AND LESS THAN 60 THEN PRINT SECOND CLASS.
IF STUDENT MARKS IS GREATER THAN 60 AND LESS THAN 70 THEN PRINT FIRST CLASS.
IF STUDENT MARKS IS GREATER THAN 70 AND LESS THAN OR EQUAL TO 100 THEN PRINT
DISTINCTION.
IF MARKS LESS THAN ALL THE FORMAT THEN HE/SHE IS FAIL?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
SPPU_MARKS NUMBER(10):=&X1;
BEGIN
IF SPPU_MARKS >= 60 AND SPPU_MARKS <80
THEN
DBMS_OUTPUT.PUT_LINE('FIRST_CLASS');
ELSIF
SPPU_MARKS >=80 AND SPPU_MARKS <=100
THEN
DBMS_OUTPUT.PUT_LINE('FIRST CLASS WITH DISTINCTION');
ELSIF
SPPU_MARKS >=40 AND SPPU_MARKS <60
THEN
DBMS_OUTPUT.PUT_LINE ('SECOND CLASS');
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL');
END IF;
END;

>>> DISPLAY THE STUDENT GRADE. IF STUDENT HAVE EXCELLENT THEN PRINT A+
IF STUDENT HAVE MEDIUM THEN PRINT B+ OTHERWISE F+?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
GRADE VARCHAR2(50):=EXCELLENT;
BEGIN
IF GRADE < EXCELLENT
THEN
DBMS_OUTPUT.PUT_LINE('A+');
ELSIF GRADE <MEDIUM
THEN
DBMS_OUTPUT.PUT_LINE('B+');
ELSE
DBMS_OUTPUT.PUT_LINE('F');
END IF;
END;

>>> CHECK THE PERSON AGE CLASS WITH THERE AGE?

SET SERVEROUTPUT ON
DECLARE
AGE NUMBER:=&x1;
BEGIN
IF AGE <18
THEN
DBMS_OUTPUT.PUT_LINE('I AM A CHILD');
ELSIF AGE <40
THEN
DBMS_OUTPUT.PUT_LINE('I AM A MIDDLE AGE');
ELSE
DBMS_OUTPUT.PUT_LINE('MY AGE IS MORE THAN 40 ');
END IF;
END;

>>> TAKE A EID FROM USER AND MEASURE THE LENGTH AND DISPLAY THE DETAILS AS PER SIZE
OF CHARACTER?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=&X1;
N2 VARCHAR2(30);
N3 VARCHAR2(30);
BEGIN
SELECT LENGTH(FIRST_NAME||LAST_NAME) INTO N2 FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
IF N2 > 7 AND N2 < 10
THEN
DBMS_OUTPUT.PUT_LINE('THE SIZE OF CHAR IS 7 TO 10');
ELSIF
N2 > 12 AND N2 < 16
THEN
DBMS_OUTPUT.PUT_LINE('THE SIZE OF CHAR IS 12 TO 16');
ELSIF
N2 > 20 AND N2 < 25
THEN
DBMS_OUTPUT.PUT_LINE('THE SIZE OF CHAR IS 20 TO 25');
ELSE
DBMS_OUTPUT.PUT_LINE('THE SIZE OF CHAR IS GREATER THAN 26');
END IF;
END;

>>> TAKE A CHOICE AND EMPLOYEE ID FROM USER AND DISPLAY THE DETILS AS PER CHOICE?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N2 VARCHAR2(30):='&X2';
N1 NUMBER(30):=&X1;
N3 VARCHAR2(30);
N4 VARCHAR2(30);
BEGIN
IF N2='SEARCH'
THEN
SELECT FIRST_NAME,LAST_NAME INTO N3,N4 FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
DBMS_OUTPUT.PUT_LINE(N3||' '||N4);
ELSIF
N2='REGISTER'
THEN
INSERT INTO MO VALUES (7,'ANIL',5500);
DBMS_OUTPUT.PUT_LINE('DATA INSERTED');
ELSIF
N2='MODIFY'
THEN
UPDATE MO SET T_NAME='SHAM'
WHERE T_ID=2;
DBMS_OUTPUT.PUT_LINE('ROWS UPDATED');
ELSIF
N2='REMOVE'
THEN
DELETE FROM MO WHERE T_ID=5;
DBMS_OUTPUT.PUT_LINE('ROWS DELETED');
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID CHOICE');
END IF;
END;

*** LOOPS (ITERATIVE CONTROLS) ***

* THERE IS THREE TYPES OF LOOPS:

1. BASIC LOOP(DO WHILE LOOP):

----ATLEAST IT WILL RETURN ONE OUTPUT.
----WE NEED TO PUT EXIT CONDITION INSIDE THE BASIC LOOP TO COME OUT FROM THE LOOP.
----WE CAN HANDLE THE EXIT PART BY TWO WAY
A. IF-THEN
B. EXIT WHEN

2. WHILE LOOP
----HERE WE PUT CONDITION TO ENTER INTO THE LOOP
----IT WILL RETURN SOMETHING OR SOMETIMES NOT RETURN ANYTHING.

3. FOR LOOP ***

----WHEN WE KNOW THE START POINT AND END POINT LIKE 1 TO 10.
SYNTAX: FOR I IN 1..4
LOOP
END LOOP

>>> PRINT 1 TO 10 NUMBER USING PL/SQL BASIC LOOP?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER (10):=1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (N1);
N1:= N1+1;
EXIT WHEN N1>10;
END LOOP;
END;

*** FOR LOOP ***

----WHEN WE KNOW THE START POINT AND END POINT LIKE 1 TO 10.

>>> PRINT 1 TO 10 NUMBER USING PL/SQL FOR LOOP?

SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
FOR N1 IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE (N1);
END LOOP;
END;

>>> WE WANT TO DISPLAY THE FULL TABLE OF 8 USING FOR LOOP?

SET SERVEROUTPUT ON
DECLARE
N1 NUMBER (10):=&X1;
BEGIN
DBMS_OUTPUT.PUT_LINE('MULTIPICATION OF '||' '||N1);
FOR i IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(N1*I);
END LOOP;
END;

>>> DISPLAY SUM OF EVEN NO FROM 1ST TO 10TH?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
EVEN NUMBER(10):=0;
BEGIN
FOR I IN 1..10
LOOP
IF I MOD 2=0
THEN
EVEN:=EVEN+I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('EVEN NO TOTAL'||' '||EVEN);
END;

>>> DISPLAY EVEN NO FROM 1ST TO 10TH?

SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
FOR I IN 1..10
LOOP
IF I MOD 2=0
THEN
DBMS_OUTPUT.PUT_LINE('EVEN NO TOTAL'||' '||I);
END IF;
END LOOP;
END;

>>> DISPLAY SUM OF 1 TO 10 NUMBER?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TOTAL NUMBER (10):=0;
BEGIN
FOR i IN 1..10
LOOP
TOTAL :=TOTAL +i;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('SUM OF 10 NO IS '||TOTAL );
END;

>>> DISPLAY SUM OF 100 NUMBER?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TOTAL NUMBER (10):=0;
BEGIN
FOR i IN 1..100
LOOP
TOTAL :=TOTAL +i;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('SUM OF 100 NO IS '||TOTAL );
END;

>>> DISPLAY FIRST NAME, SALARY, HIRE_DATE OF FIRST 10 EMPLOYEE FROM EMPLOYEE
TABLE?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 VARCHAR2(20);
N2 NUMBER(20);
N3 DATE;
BEGIN
FOR I IN 100..109
LOOP
SELECT FIRST_NAME,SALARY,HIRE_DATE INTO N1,N2,N3
FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
DBMS_OUTPUT.PUT_LINE(N1||' '||N2||' '||N3||' ' );
END LOOP;
END;

>>> PRINT:= 1 2 3
2 3 4
 3 4 5

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
FOR ROW IN 1..3
LOOP
N1:=ROW;
FOR COL IN 1..3
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:= 2 4 6
8 10 12
14 16 18

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=2;
BEGIN
FOR ROW IN 1..3
LOOP
FOR COL IN 1..3
LOOP
IF N1 MOD 2=0
THEN
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+2;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:= 7 8 9
10 11 12
13 14 15

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=7;
BEGIN
FOR ROW IN 1..3
LOOP
FOR COL IN 1..3
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:= 1 2 3
* * *
4 5 6
* * *

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=0;
BEGIN
FOR ROW IN 1..4
LOOP
FOR COL IN 1..3
LOOP
IF ROW MOD 2=0
THEN
DBMS_OUTPUT.PUT(' *');
ELSE
N1:=N1+1;
DBMS_OUTPUT.PUT(' '||N1);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:= 1 2 3
# # #
7 8 9
# # #

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=1;
N2 NUMBER(10):=0;
BEGIN
FOR ROW IN 1..4
LOOP
FOR COL IN 1..3
LOOP
IF ROW MOD 2=0
THEN
DBMS_OUTPUT.PUT(' #');
ELSE
DBMS_OUTPUT.PUT(N1);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> * # * #
 * # * #
* # * #

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
FOR ROW IN 1..3
LOOP
FOR COL IN 1..4
LOOP
IF COL MOD 2=0
THEN
DBMS_OUTPUT.PUT(' '||'#');
ELSE
DBMS_OUTPUT.PUT(' '||'*');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:= 1
2 3
4 5 6

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=1;
BEGIN
FOR ROW IN 1..3
LOOP
FOR COL IN 1..ROW
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:= 1
1 2
1 2 3

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
FOR ROW IN 1..3
LOOP
N1:=1;
FOR COL IN 1..ROW
 LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:= 1
2 3
3 4 5

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
FOR ROW IN 1..3
LOOP
N1:=ROW;
FOR COL IN 1..ROW
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
###
###
###

SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
FOR I IN 1..3
LOOP
FOR COL IN 1..3
LOOP
DBMS_OUTPUT.PUT('#');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
****
****
****

SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
FOR I IN 1..3
LOOP
 FOR COL IN 1..4
LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
1 2 3
4 5 6
7 8 9

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=1;
BEGIN
FOR I IN 1..3
LOOP
FOR I IN 1..3
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
7 8 9
10 11 12
13 14 15

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=7;
BEGIN
FOR I IN 1..3
LOOP
FOR I IN 1..3
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
1 2 3
2 3 4
3 4 5

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
FOR I IN 1..3
LOOP
N1:=I;
FOR I IN 1..3
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
2 4 6
8 10 12
14 16 18

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=2;
BEGIN
FOR I IN 1..3
LOOP
FOR I IN 1..3
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+2;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
*
**
***

SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
FOR ROW IN 1..3
LOOP
FOR I IN 1..ROW
LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
1
 2 3
4 5 6

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=0;
BEGIN
FOR ROW IN 1..3
LOOP
FOR I IN 1..ROW
LOOP
N1:=N1+1;
DBMS_OUTPUT.PUT(' '||N1);
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
1
1 2
1 2 3

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
FOR ROW IN 1..3
LOOP
N1:=1;
FOR I IN 1..ROW
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE (' ');
END LOOP;
END;

1.
*
* *
* * *

SET SERVEROUTPUT ON
DECLARE
n NUMBER(30) := 5;
BEGIN
FOR i IN 1..n
LOOP

FOR j IN 1..(n - i)
LOOP
DBMS_OUTPUT.PUT(' ');
 END LOOP;

FOR k IN 1..(2 * i - 1) LOOP

DBMS_OUTPUT.PUT(' *');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;
/

2. *
* *
* * *
* *
*

>>> PRINT:=
****
****
****
****

SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
FOR I IN 1..4
LOOP
FOR COL IN 1..4
LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>> PRINT:=
***
**
*

SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
FOR ROW IN 1..3
LOOP
FOR COL IN ROW..3
LOOP
DBMS_OUTPUT.PUT('*');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;
3. WHILE LOOP
----HERE WE PUT CONDITION TO ENTER INTO THE LOOP
----IT WILL RETURN SOMETHING OR SOMETIMES NOT RETURN ANYTHING.

>>> PRINT 1 TO 10 NUMBER USING PL/SQL while LOOP?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=1;
BEGIN
WHILE N1<=10
LOOP
DBMS_OUTPUT.PUT_LINE(N1);
N1:=N1+1;
END LOOP;
END;

>>> PRINT 1 TO 5 NUMBER USING WHILE LOOP?

SET SERVEROUTPUT ON
DECLARE
X NUMBER:=0;
BEGIN
WHILE X < 5
LOOP
X:=X+1;
DBMS_OUTPUT.PUT_LINE (X);
END LOOP;
END;

>>> PRINT 10T TABLE USING WHILE LOOP?

SET SERVEROUTPUT ON
DECLARE
X NUMBER:=0;
BEGIN
WHILE X < 10
LOOP
x:=x+1;
DBMS_OUTPUT.PUT_LINE (X*10);
END LOOP;
END;

1. DISPLAY MULTIPLICATION TABLE?

SET SERVEROUTPUT ON
DECLARE
X NUMBER(10):=&X1;
BEGIN
FOR I IN 1..10
LOOP
DBMS_OUTPUT.PUT_LINE(I*X);
END LOOP;
END;

2. DISPLAY EVEN NO?

SET SERVEROUTPUT ON
BEGIN
FOR I IN 1..10
LOOP
IF( I MOD 2)=0
THEN
DBMS_OUTPUT.PUT_LINE (I);
END IF;
END LOOP;
END;

3. DISPLAY SUM OF 1 TO 10?

SET SERVEROUTPUT ON
DECLARE
TOTAL NUMBER (10):=0;
BEGIN
FOR I IN 1..10
LOOP
TOTAL:=TOTAL+I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(TOTAL);
END;

4. DISPLAY ODD NUMBER ?

SET SERVEROUTPUT ON
BEGIN
FOR I IN 1..10
LOOP
IF( I MOD 2)!=0
THEN
DBMS_OUTPUT.PUT_LINE (I);
END IF;
END LOOP;
END;

4. DISPLAY SUM OF ODD NUMBER?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TOTAL NUMBER(10):=0;
BEGIN
FOR I IN 1..20
LOOP
IF( I MOD 2)!=0
THEN
TOTAL:=TOTAL+I;
DBMS_OUTPUT.PUT_LINE('TOTAL SUM OFF ODD NUMBER '||' '||TOTAL);
END IF;
END LOOP;
END;
2. DISPLAY SUM OF EVEN NO ?

SET SERVEROUTPUT ON
DECLARE
TOTAL NUMBER(10):=0;
BEGIN
FOR I IN 1..10
LOOP
IF I MOD 2=0
THEN
TOTAL:=TOTAL+I;
DBMS_OUTPUT.PUT_LINE ('EVEN NUMBER SUM IS'||' '||TOTAL);
END IF;
END LOOP;
END;

5. CHECK IS IT DIVISIBLE BY 5 OR NOT?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
X NUMBER(10):=&X1;
BEGIN
WHILE X!=0
LOOP
IF X MOD 5=0
THEN
DBMS_OUTPUT.PUT_LINE('DIVISIBLE BY 5');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT DIVISIBLE BY 5');
END IF;
END LOOP;
END;

>>> DISPLAY 1 TO 50 EVEN NUMBER TOTAL COUNT?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TOTAL NUMBER(10):=0;
BEGIN
FOR I IN 1..50
LOOP
IF(I MOD 2)!=0
THEN
TOTAL:=TOTAL+I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(TOTAL);
END;

>>> DISPLAY 1 TO 50 ODD NUMBER TOTAL COUNT?

SET VERIFY OFF

SET SERVEROUTPUT ON
DECLARE
TOTAL NUMBER(10):=0;
BEGIN
FOR I IN 1..50
LOOP
IF( I MOD 2)!=0
THEN
TOTAL:=TOTAL+I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(TOTAL);
END;

>>> DISPLAY FIRST_NAME, SALARY, HIRE_DATE FOR 1ST 10 EMPLOYEES?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
X1 VARCHAR2(20);
X2 NUMBER(10);
X3 DATE;
BEGIN
FOR I IN 100..110
LOOP
SELECT FIRST_NAME,SALARY,HIRE_DATE INTO X1,X2,X3 FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
DBMS_OUTPUT.PUT_LINE(X1||' '||X2||' '||X3);
END LOOP;
END;

>>> DISPLAY ODD MANAGER ID FROM EMPLOYEES?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
MGR_ID NUMBER(10);
BEGIN
FOR I IN 100..110
LOOP
SELECT MANAGER_ID INTO MGR_ID FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
IF (MGR_ID MOD 2)!=0
THEN
DBMS_OUTPUT.PUT_LINE('ODD MANAGER ID IS'||' '||MGR_ID);
END IF;
END LOOP;
END;

>>> DISPLAY JOINING DATA OF FIRST 10 EMPLOYEES?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
JOIN_DATE DATE;
BEGIN
FOR I IN 100..110
LOOP
SELECT HIRE_DATE INTO JOIN_DATE FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
DBMS_OUTPUT.PUT_LINE('FIRST 10 EMPLOYEE JOINING DATE IS'||' '||JOIN_DATE);
END LOOP;
END;

10. DISPLAY AVG SALARY FOR 1ST 10 EMPLOYEE WITHOUT USING AVG FUNCTION?
10. DISPLAY SUM OF SALARY USING LOOP?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
ADDITION NUMBER(10):=0;
CNT NUMBER (10):=0;
AVG_SAL NUMBER(10);
BEGIN
FOR I IN 100..109
LOOP
SELECT SALARY INTO N1 FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
ADDITION:=ADDITION+N1;
DBMS_OUTPUT.PUT_LINE(ADDITION);
CNT:=CNT+1;
END LOOP;
END;
AVG_SAL:=ADDITION/CNT;
DBMS_OUTPUT.PUT_LINE('AVG SAL OF FIRST 10 EMPLOYEE IS '||' '||AVG_SAL);
END;

>>> CHECK FIRST_NAME START FROM D FOR 1ST 10 EMPLOYEES?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
D_NAME VARCHAR2(20);
BEGIN
FOR I IN 100..110
LOOP
SELECT FIRST_NAME INTO D_NAME FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
IF D_NAME LIKE 'D%'
THEN
DBMS_OUTPUT.PUT_LINE(D_NAME);
END IF;
END LOOP;
END;

>>> DISPLAY SUM OF SALARY OF 1ST 10 EMPLOYEES?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10):=0;
N2 NUMBER(10):=0;
N3 NUMBER(10);
N4 NUMBER(10);
BEGIN
FOR I IN 100..109
LOOP
SELECT SALARY INTO N4 FROM EMPLOYEES WHERE EMPLOYEE_ID=I;
N1:=N1+1;
N2:=N2+N4;
END LOOP;
N3:=N2/N1;
DBMS_OUTPUT.PUT_LINE(N3);
END;

>>> DISPLAY SUM OF ALTERNATIVE NUMBERS OF 1 TO 50?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TOTAL NUMBER(10):=0;
BEGIN
FOR I IN 1..50
LOOP
IF(I MOD 2)!=0
THEN
TOTAL:=TOTAL+I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(TOTAL);
END;

>>> PRINT * LIKE A BOX. NEED 4 ROWS AND 4 COLUMN?

SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
FOR ROW IN 1..4
LOOP
FOR I IN ROW..4
LOOP
DBMS_OUTPUT.PUT(' *');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
FOR ROW IN 1..3
LOOP
N1:=ROW;
FOR I IN 1..ROW
LOOP
 DBMS_OUTPUT.PUT_LINE(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

SET SERVEROUTPUT ON
BEGIN
FOR ROW IN 1..3
LOOP
FOR COL IN ROW..3
LOOP
DBMS_OUTPUT.PUT(' *');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

SET SERVEROUTPUT ON
DECLARE
N1 NUMBER(10):=1;
BEGIN
FOR ROW IN 1..3
LOOP
FOR COL IN 1..3
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>>
*
* * *
* * * * *
* * * * * * *
* * * * * * * * *
* * * * * * *
* * * * *
* * *
*

SET SERVEROUTPUT ON
DECLARE
n NUMBER(30) := 5;
BEGIN
FOR i IN 1..n
LOOP
FOR j IN 1..(n - i)
LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;

FOR k IN 1..(2 * i - 1) LOOP

DBMS_OUTPUT.PUT(' *');
 END LOOP;

DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
FOR i IN REVERSE 1..(n - 1)
LOOP
FOR j IN 1..(n - i)
LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;

FOR k IN 1..(2 * i - 1) LOOP

DBMS_OUTPUT.PUT(' *');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>>>

* * * * * * * * *
* * * * * * *
* * * * *
* * *
*
* * * * * * *
* * * * *
* * *
*

SET SERVEROUTPUT ON
DECLARE
n NUMBER(30) := 5;
BEGIN
FOR i IN REVERSE 1..n
LOOP
FOR j IN 1..(n - i)
LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;

FOR k IN 1..(2 * i - 1) LOOP

DBMS_OUTPUT.PUT(' *');
END LOOP;

DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
FOR i IN REVERSE 1..(n - 1)
LOOP
FOR j IN 1..(n - i)
LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;

FOR k IN 1..(2 * i - 1) LOOP

 DBMS_OUTPUT.PUT(' *');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;
/

>>>
*
* * *
* * * * *
* * * * * * *
* * * * * * * * *
*
* * *
* * * * *
* * * * * * *

SET SERVEROUTPUT ON
DECLARE
n NUMBER(30) := 5;
BEGIN
FOR i IN 1..n
LOOP
FOR j IN 1..(n - i)
LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;

FOR k IN 1..(2 * i - 1) LOOP

DBMS_OUTPUT.PUT(' *');
END LOOP;

DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
FOR i IN 1..(n - 1)
LOOP
FOR j IN 1..(n - i)
LOOP
DBMS_OUTPUT.PUT(' ');
END LOOP;

FOR k IN 1..(2 * i - 1) LOOP

DBMS_OUTPUT.PUT(' *');
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;
/

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=1;
BEGIN
FOR ROW IN 1..3
LOOP
FOR COL IN 1..ROW
LOOP
DBMS_OUTPUT.PUT(' '||N1);
N1:=N1+2;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>---
1 2 3 4
# # # #
5 6 7 8
# # # #

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=1;
BEGIN
FOR ROW IN 1..4
LOOP
FOR COL IN 1..4
LOOP
IF MOD(ROW , 2) =0
THEN
DBMS_OUTPUT.PUT(' # ');
ELSE
DBMS_OUTPUT.PUT(' '||N1||' ');
N1:=N1+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>---
1 2 3
# # #
7 8 9
# # #

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(30):=1;
BEGIN
FOR ROW IN 1..4
LOOP
FOR COL IN 1..3
LOOP

IF MOD(ROW , 2) =0
THEN
DBMS_OUTPUT.PUT(' # ');
ELSE
DBMS_OUTPUT.PUT(' '||N1||' ');
 END IF;
N1:=N1+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

>---
A B C D
1 2 3 4
E F G H
5 6 7 8

SET SERVEROUTPUT ON;

DECLARE
N VARCHAR2:='A';
BEGIN
FOR I IN 1..4
LOOP
FOR J IN 1..4
LOOP
DBMS_OUTPUT.PUT(N);
N:=N+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END LOOP;
END;

SET SERVEROUTPUT ON;

DECLARE
result_str VARCHAR2(20);
BEGIN
FOR i IN 1..4 LOOP
FOR j IN 1..4 LOOP
result_str := result_str || CHR(64 + j);
END LOOP;
DBMS_OUTPUT.PUT_LINE(result_str);
result_str := NULL;
END LOOP;
END;
/

>---
A
AB
ABC
ABCD

SET SERVEROUTPUT ON;

DECLARE
N VARCHAR2(1):='A';
BEGIN
FOR I IN 1..4
LOOP
FOR J IN 1..4
LOOP
DBMS_OUTPUT.PUT(N);
 N := CHR(ASCII(N) + 1);
END LOOP;
DBMS_OUTPUT.NEW_LINE;
END LOOP;
END;
/

SELECT CHR(65) FROM DUAL;

SELECT CHR(90) FROM DUAL;

SELECT CHR(97) FROM DUAL;

SELECT CHR(122) FROM DUAL;

SELECT ASCII('A') FROM DUAL;

>---

*** DECLARATION AND INITIALIZATION VARIABLE ***

----WE CAN DECLARE THE VARIABLE IN THE DECLARE SECTION ONLY.
1. USING SELECT (INTO CLAUSE)
2. USING DIRECT METHOD (:=)
----WE CAN INITIALIZE THE VARIABLE IN THE DECLARATION AS WELL AS EXECUTION (BEGIN).
NOTE: IN DIRECT METHOD WE CAN USE := OR DEFAULT KEY WORD TO INITIALIZE THE
VARIABLE.
----VARIABLE IS TEMPORARY STORAGE OF THE VALUE WHEN WE STORE OR INITILIZE
----IT WILL STORE ONLY ONE VALUE OR ROW.

SET SERVEROUTPUT ON
DECLARE
X DATE:=SYSDATE;
BEGIN
DBMS_OUTPUT.PUT_LINE(X);
END;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
X DATE; ---DECLARATION OF VARIABLE
BEGIN
SELECT SYSDATE INTO X FROM DUAL; ---INITIALIZATION OF VARIABLE
DBMS_OUTPUT.PUT_LINE(X); ---USE OF VARIABLE.
END;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
X DATE:=SYSDATE;
BEGIN
X:= SYSDATE+7;
DBMS_OUTPUT.PUT_LINE('DATA ADDED');
DBMS_OUTPUT.PUT_LINE(X);
END;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V_SAL NUMBER(5);
BEGIN
SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=101;
DBMS_OUTPUT.PUT_LINE ('SALARY OF THE MENTIONED EMPLOYEE IS:'||' '||V_SAL);
END;

*** USE OF SUBSTITUTION VARIABLE ***

----WITH HELP SUBSTITUTION VARIABLE WE CAN CREATE ONE DYNAMIC QUERY WHICH

*** COMMENTING OF THE CODE ***

----SINGLE LINE = WE WILL USE --
----MULTIPLE LINE = WE WILL USE /* */

SET SERVEROUTPUT ON
DECLARE
V_SAL NUMBER(5);
BEGIN
SELECT SALARY INTO V_SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=101;
DBMS_OUTPUT.PUT_LINE ('SALARY OF THE MENTIONED EMPLOYEE IS :'||V_SAL);
END;

*** NESTED BLOCK ***

----BLOCK INSIDE THE BLOCK
----VARIABLE OF OUTER BLOCK = GLOBAL
----VAIABLE OF INNER BLOCK = LOCAL

***SCOPE VARIABLE ***

----WE CAN ACCESSED THE OF OUTER BLOCK INSIDE THE INNER BLOCK
----WE CAN NOT ACCESSED OR USE THE VARIABLE OF INNER BLOCK INSIDE THE OUTER BLOCK

SET SERVEROUTPUT ON
DECLARE
OUTER_VAR VARCHAR2(70):='OUTSIDE VALUE';
BEGIN
DBMS_OUTPUT.PUT_LINE (OUTER_VAR);
END;

DECLARE
OUTER_VAR VARCHAR2(70):='OUTSIDE VALUE';
BEGIN

SET SERVEROUTPUT ON
DECLARE
INNER_VAR VARCHAR2(70):='INNER VALUE';
BEGIN

DBMS_OUTPUT.PUT_LINE ('FROM INNER BLOCK-> '||INNER_VAR);

END;
DBMS_OUTPUT.PUT_LINE ('FROM OUTER BLOCK ->'|| OUTER_VAR);
END;

*** CONTROL STRUCTURE ***

----USE TO CONTROL THE SQL STATEMENTS INSIDE THE BEGIN SECTION OF THE PLSQL BLOCK
BASED ON CONDITION.
----WE USES - IF - THEN - ELSE
----WE USE - IF - ELSEIF - ELSE
----GIVE THE REMARK BASED ON PROVIDED VALUE OF AGE.

*** COMPOSITES DATA TYPE ***

1. PL/SQL RECORD:
----STORE MORE THAN ONE COLUMN VALUE OR FIELD VALUE OR ENTIRE ROW OF THE TABLE.

*** THERE ARE TWO TYPES OF PL/SQL RECORD ***

1. BY USING REFERENCE (% ROW TYPE )
A. USING %ROWTYPE ATTRIBUTE:
----SYNTAX: VAR_NAME TABLE_NAME%ROWTYPE;

B. USING %TYPE ATTRIBUTE:

----SYNTAX: VAR_NAME TABLE_NAME.COL_NAME%TYPE

NOTE:
----TO ACCESS THE VALUES FROM VARIABLE WE CAN USE NAME OF THAT REFERENCED TABLE.
----(E.G, VAR_NAME.SAL)

SYNTAX:
DECLARE
TYPE NAME IS RECORD
(FILLED1 DATA_TYPE(SIZE),
FIELD2 DATA_TYPE(SIZE),
FIELD3 DATA_TYPE(SIZE));
VAR_NAME RECORD_NAME;

>>> PRINT THE DETAILS OF THE EMPLOYEE USING PLSQL BLOCK (RECORDS DATA TYPE)
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
X NUMBER(20);
BEGIN
SELECT SALARY INTO X FROM EMPLOYEES WHERE EMPLOYEE_ID=101;
DBMS_OUTPUT.PUT_LINE(X);
END;

SET SERVEROUTPUT ON
DECLARE
EMP_INFO EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO EMP_INFO FROM EMPLOYEES WHERE EMPLOYEE_ID= 101;
DBMS_OUTPUT.PUT_LINE (EMP_INFO.SALARY);
END;

SET SERVEROUTPUT ON
DECLARE
EMP_INFO EMPLOYEES%ROWTYPE;
LEN NUMBER;
BEGIN
SELECT * INTO EMP_INFO FROM EMPLOYEES WHERE EMPLOYEE_ID= 101;
DBMS_OUTPUT.PUT_LINE (EMP_INFO.SALARY);
DBMS_OUTPUT.PUT_LINE (EMP_INFO.ENAME||' '||EMP_INFO.HIREDATE);
LENGTH:= LENGTH(EMP_INFO.ENAME);
DBMS_OUTPUT.PUT_LINE (LENGTH);
END;

DECLARE
TYPE EMP_RECO IS RECORD (NAME VARCHAR2(10), SALARY NUMBER(9), HIRE_DATE DATE;
BEGIN
DBMS_OUTPUT.PUT_LINE (EMP_INFO.ENAME||' '||EMP_INFO.HIREDATE);

END;

2. BY DECLARING RECORD IN DECLARATION SECTION (RECORDS TYPE)

2. PL/SQL COLLECTIONS:
----STORE MULTIPLES VALUE OF SOME COLUMN OR FIELD OR MULTIPLES ROWS.

SET SERVEROUPUT ON
DECLARE
V_SAL NUMBER;
BEGIN
SELECT SALARY INTO V_SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID= 101;
END;

*** PL/SQL VARIABLE ***

*** COMPOSITE DATA TYPE ***

*** PL/SQL COLLECTIONS ***

----STORE THE MULTIPLES ROWS VALUES FOR ANY SINGLE COLUMN OR MULTIPLE COLUMN

*** TYPES OF PL/SQL COLLECTIONS ***

1. ASSOCIATIVE ARRAY ( INDEX BY TABLE ):
----NUMBER OF ELEMENTS: UNBOUNDED
----WHERE CAN CREATE: IN PL/SQL BLOCK

2. NESTED TABLE:
----NUMBER OF ELEMENTS: UNBOUNDED
----WHERE CAN CREATE: IN PL/SQL BLOCK, AS WELL AS SCHEMA LEVEL AS A DATABASE
OBJECT.

3. VARIABLE SIZE ARRAY (VARRAY):

----NUMBER OF ELEMENTS: BOUNDED.
----WHERE CAN CREATE: IN PL/SQL BLOCK AS WELL AS SCHEMA LEVEL AS A DATABASE OBJECT.

*** ASSOCIATIVE ARRAY ( INDEX BY TABLE ) ***

----IT IS SET OF KEY-VALUE PAIR.
----EACH KEY IS UNIQUE, IT USE TO ACCESS VALUES ASSOCIATED WITH THAT RESPECTIVE
KEY, IT MAY BE STRING OR INTEGER.
----THIS TYPE OF COLLECTION CAN NOT BE DECLARE AS A DATABASE OBJECT.
----SYNTAX: TYPE TYPE_NAME IS TABLE OF DATA_TYPE INDEX BY DATA TYPE;
----VAR_NAME TYPE_NAME;

>>> PRINT THE SALARY OF ANY SPECIFIC EMPLOYEE USING PL/SQL BLOCK?
SET SERVEROUTPUT ON
DECLARE
SAL [Link]%TYPE;
BEGIN
SELECT SALARY INTO SAL FROM EMPLOYEES WHERE EMPLOYEE_ID=101;
DBMS_OUTPUT.PUT_LINE (SAL);
END;

SET SERVEROUTPUT ON
DECLARE
TYPE EMPLOYEES_SAL IS TABLE OF [Link]%TYPE INDEX BY PLS_INTEGER;
SALARY EMPLOYEE_SAL;
BEGIN
SELECT SALARY BULK COLLECT INTO SALARY FROM EMPLOYEES
DBMS_OUTPUT.PUT_LINE (SALARY);
END;

*** PL/SQL METHOD ***

1. FIRST: GIVES FIRST / MIN KEY FROM COLLECTION.
2. LAST: GIVES LAST / MIN KEY FROM COLLECTION.

*** COMPOSITE DATA TYPE ***

----IT HAS TWO TYPES.
A. RECORD
B. COLLECTION (PL/SQL ARRAY)

A. RECORD
----WHEN VARIABLE STORE MULTIPLE TYPES OF VALUE KNOWN AS RECORD.
----BUT IT WILL SAVE ONLY ONE ROW.
N1: NUMBER
CHAR
DATE

B. COLLECTION.
----WHEN VARIABLE STORE SIMILAR TYPES OF VALUES KNOWN AS COLLECTION.
N2: CHAR
CHAR
CHAR

EXAMPLE OF RECORD:
>>> DISPLAY FIRST_NAME, HIRE_DATE, SALARY USING ONE VARIABLE AND USING RECORD?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE T IS RECORD
(N1 VARCHAR2(20), N2 NUMBER(10), N3 DATE);
V1 T;
BEGIN
SELECT FIRST_NAME, SALARY, HIRE_DATE INTO V1 FROM EMPLOYEES
WHERE EMPLOYEE_ID=105;
DBMS_OUTPUT.PUT_LINE(V1.N1||' '||V1.N2||' '||V1.N3);
END ;

>>> FETCH THE FIRST_NAME , SALARY, HIRE_DATE USING RECORD AND LOOP IN PL/SQL?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE T IS RECORD
(N1 VARCHAR2(20), N2 NUMBER(10), N3 DATE);
V1 T;
BEGIN
FOR I IN 100..109
LOOP
SELECT FIRST_NAME, SALARY, HIRE_DATE INTO V1 FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
DBMS_OUTPUT.PUT_LINE(V1.N1||' '||V1.N2||' '||V1.N3);
END LOOP;
END;

>>> DISPLAY FIRST NAME, SALARY AND HIRE_DATE USING RECORD AND LOOP AND USING
%ROWTYPE?
AND WE PASTED FULL EMPLOYEE TABLE FORMAT IN THE VARIABLE AND WHAT WE WANT WE ARE
COLLECTING?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V1 EMPLOYEES%ROWTYPE;
BEGIN
FOR I IN 100..109
LOOP
SELECT * INTO V1 FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
DBMS_OUTPUT.PUT_LINE(V1.FIRST_NAME||' '||[Link]);
END LOOP;
END ;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE T IS VARRAY (5) OF
VARCHAR2(20);
V T;
BEGIN
V:=T('JAY','AJAY','VIJAY','AMAR','AMIT');
FOR I IN 1..5
LOOP
DBMS_OUTPUT.PUT_LINE(V(I));
END LOOP;
END;
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE T IS VARRAY(5) OF
VARCHAR2(20);
V T:=T(); ----CONSTRUCTOR
N1 NUMBER(10):=1;
BEGIN
FOR I IN 100..104
LOOP
[Link];
SELECT FIRST_NAME INTO V(N1) FROM EMPLOYEES WHERE EMPLOYEE_ID=I;
DBMS_OUTPUT.PUT_LINE(V(N1));
N1:=N1+1;
END LOOP;
END;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE T IS VARRAY(5) OF
VARCHAR2(20);
V T:=T(); ----CONSTRUCTOR
BEGIN
[Link];
SELECT FIRST_NAME BULK COLLECT INTO V FROM EMPLOYEES WHERE EMPLOYEE_ID < 104;
FOR I IN 1..4
LOOP
DBMS_OUTPUT.PUT_LINE(V(I));
END LOOP;
END;

>>> COPY DATA FROM T1 TO T2 USING RECORD? ...REMAINING

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V1 EMPLOYEES%ROWTYPE;
BEGIN
SELECT * INTO V1 FROM EMPLOYEES
WHERE EMPLOYEE_ID=110;
INSERT INTO EMPLOYEE_ID VALUES V1;
DBMS_OUTPUT.PUT_LINE('DATA COPIED T1 TO T2');
END;

>>> REPLACE T2 USING T1? ...REMAINING

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V1 T1%ROWTYPE;
BEGIN
SELECT * INTO V1 FROM T1
WHERE TNO=2;
UPDATE T2
SET ROW=V1
WHERE CNO=2
DBMS_OUTPUT.PUT_LINE ('ROW UPDATED');
END;

>>> DISPLAY 1ST 10 EMP FULL_NAME, JOINING_DATE, USING PL/SQL RECORD?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V1 EMPLOYEES%ROWTYPE;
BEGIN
FOR I IN 100..109
LOOP
SELECT * INTO V1 FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
DBMS_OUTPUT.PUT_LINE('FULL NAME'||' '||V1.FIRST_NAME||V1.LAST_NAME||' '||
V1.HIRE_DATE);
END LOOP;
END;

>>> ENAME, SALARY, & 25% SALARY FOR 1ST 10 EMP USING RECORD?
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V1 EMPLOYEES%ROWTYPE;
BEGIN
FOR I IN 100..109
LOOP
SELECT * INTO V1 FROM EMPLOYEES WHERE EMPLOYEE_ID=I;
DBMS_OUTPUT.PUT_LINE(V1.FIRST_NAME||' '||V1. SALARY||' '||[Link]*0.25);
END LOOP;
END;

>>> COPY ALL DATA FROM EMPLOYEES TO EMP1 TABLE USING PL/SQL RECORD? ...REMAINING
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
EMP1 EMPLOYEES%ROWTYPE;
BEGIN
FOR I IN 100..109
LOOP
SELECT * INTO EMP1 FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
INSERT INTO EMPLOYEE_ID VALUES EMP1;
DBMS_OUTPUT.PUT_LINE('ROW INSERTED');
END LOOP;
END;

*** COLLECTION TYPES ***

A. PL/SQL ARRAY (INDEX BY TABLE)
B. VARRAY
C. NESTED TABLE.

A. ARRAY

>>>DISPLAY FIRST 10 EMP FULL NAME USING PL/SQL COLLECTION?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE T IS TABLE OF
EMPLOYEES%ROWTYPE INDEX BY PLS_INTEGER;
V T;
BEGIN
FOR I IN 100..109
LOOP
SELECT * INTO V(I) FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
END LOOP;

FOR J IN 100..109
LOOP
DBMS_OUTPUT.PUT_LINE(V(J).FIRST_NAME||V(J).LAST_NAME);
END LOOP;
END;

>>> DISPLAY FULL NAME USING PL/SQL COLLECTION?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE T IS TABLE OF
EMPLOYEES%ROWTYPE INDEX BY PLS_INTEGER;
V T;
BEGIN
SELECT * BULK COLLECT INTO V FROM EMPLOYEES
WHERE EMPLOYEE_ID<110;

FOR J IN 100..109
LOOP
DBMS_OUTPUT.PUT_LINE(V(J).FIRST_NAME||V(J).LAST_NAME);
END LOOP;
END;

>>> DISPLAY SALARY AND FETCH THE SALARY USER WANT?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE SALARY IS VARRAY(5) OF NUMBER(5);
SAL SALARY;
BEGIN
SAL := SALARY (100,200,300,400,500);
DBMS_OUTPUT.PUT_LINE(SAL(3));
END;

>>> FETCH THE NUMBER?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE SALARY IS VARRAY(10) OF NUMBER(10);
SAL_1 SALARY;
BEGIN
SAL_1:=SALARY(10,4,9,5,0,1,7,2,8,3);
DBMS_OUTPUT.PUT_LINE(SAL_1(6));
END;

>>> DISPLAY FIRST-NAME, HIRE_DATE, AND 25% SALARY USING PL/SQL COLLECTION?

SET VERIFY OFF

SET SERVEROUTPUT ON
DECLARE
TYPE T IS TABLE OF
VARCHAR2(60) INDEX BY PLS_INTEGER;
V T;
BEGIN
FOR I IN 100..109
LOOP
SELECT FIRST_NAME||' '||HIRE_DATE||' '||(SALARY*0.25) INTO V(I)
FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
END LOOP;
FOR J IN 100..109
LOOP
DBMS_OUTPUT.PUT_LINE(V(J));
END LOOP;
END;

>>> DISPLAY 10 EMP HIRE_DATE USING PL/SQL COLLECTION?

SET VERIFY OFF

SET SERVEROUTPUT ON
DECLARE
TYPE T IS TABLE OF
VARCHAR2(50) INDEX BY PLS_INTEGER;
V T;

BEGIN
FOR I IN 100..109
LOOP
SELECT HIRE_DATE INTO V(I)
FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
END LOOP;
FOR J IN 100..109
LOOP
DBMS_OUTPUT.PUT_LINE(V(J));
END LOOP;
END;

>>> DISPLAY 10 EMP SALARY USING PL/SQL COLLECTION?

SET VERIFY OFF

SET SERVEROUTPUT ON
DECLARE
TYPE T IS TABLE OF
VARCHAR2(50) INDEX BY PLS_INTEGER;
V T;

BEGIN
FOR I IN 100..109
LOOP
SELECT SALARY INTO V(I)
FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
END LOOP;
FOR J IN 100..109
LOOP
DBMS_OUTPUT.PUT_LINE(V(J));
END LOOP;
END;

>>> DISPLAY FIRST_NAME, HIRE_DATE OF 10 EMP USING PL/SQL COLLETION?

SET VERIFY OFF

SET SERVEROUTPUT ON
DECLARE
TYPE T IS TABLE OF
VARCHAR2(50) INDEX BY PLS_INTEGER;
V T;

BEGIN
FOR I IN 100..109
LOOP
SELECT first_name||HIRE_DATE INTO V(I)
FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
END LOOP;
FOR J IN 100..109
LOOP
DBMS_OUTPUT.PUT_LINE(V(J));
END LOOP;
END;

>>> DISPLAY 10 EMP SALARY IN ARRAY & DISPLAY SUM OF SALARY FOR ARRAY?

SET VERIFY OFF

SET SERVEROUTPUT ON
DECLARE
TYPE T IS TABLE OF
VARCHAR2(50) INDEX BY PLS_INTEGER;
V T;
TOTAL NUMBER(10):=0;

BEGIN
FOR I IN 100..109
LOOP
SELECT SALARY INTO V(I)
FROM EMPLOYEES
WHERE EMPLOYEE_ID=I;
END LOOP;

FOR J IN 100..109
LOOP
TOTAL:=TOTAL+V(J);
END LOOP;
DBMS_OUTPUT.PUT_LINE(TOTAL);
END;

*** INDEX BY TABLE WITH %ROWTYPE DATA TYPE ***

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
TYPE EMPLOYEE_INFO IS TABLE OF EMPLOYEES%ROWTYPE INDEX BY PLS_INTEGER;
BEGIN
SELECT * BULK COLLECT INTO EMPLOYEE_INFO FROM EMPLOYEES;
DBMS_OUTPUT.PUT_LINE('OK');
END;

***PL/SQL CURSOR ***

----IT IS NAMED CONTROL STRUCTURE WHICH IS USED IN PL/SQL BLOCK.
----WHEN WE DECLARE THE CURSOR TEMPORARY MEMORY ALLOCATED TO THE CURSOR BY THE
DATABASE.
----USE OF THE CURSOR TO STORE OR HOLD THE ENTIRE RESULT OF THE SELECT STATEMENT.
----FURTHUR WE CAN USE THAT RESULT OR DATA FOR PROCESSING IN EXECUTION SECTION
WITHOUT RUNNING THE SELECT STATEMENT AGAIN
----SET OF RESULT OF ROWS OR RECORD HOLD BY THE CURSOR IS REFER AS A ACTIVE SET.
----SYNTAX: CURSOR CURSOR_NAME IS SELECT.

*** TYPES OF CURSOR ***

1. IMPLICIT CURSOR
----DECLARE AND MANAGED BY DATABASE ITSELF

2. EXPLICIT CURSOR
----DECLARE AND MANAGED BY DEVELOPER / USER / PROGRAMER.

EXECUTION:

OPEN CURSOR_NAME;
LOOP
FETCH CURSOR_NAME INTO VAR_NAME;
EXIT WHEN CURSOR_NAME%NOTFOUND
END LOOP;
CLOSE CURSOR_NAME;

>>> PRINT ALL ENAME OF THE TABLE USING CURSOR?

SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT FIRST_NAME FROM EMPLOYEES;
V_NAME VARCHAR2(10);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V_NAME;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE (V_NAME);
END LOOP;
CLOSE C1;
END;

*** CURSOR ATTRIBUTES ***

----USE OF ATTRIBUTES = CURSUR_NAME ATTRIBUTES_NAME (C1%NOTFOUND)

1. %NOTFOUND:
----TRUE IF MOST RECENT FETCH DOES NOT RETURN ROW OR RECORD.

2. %FOUND:
----TRUE IF MOST RECENT FETCH RETURN ROW OR RECORD.

3. %ISOPEN:
----TRUE IF CURSOR IS OPEN.

4. %ROWCOUNT:
----TOTAL ROWS OR RECORDS RETURN BY CURSOR.

SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES;
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
SALARY NUMBER(5);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO EMP_ID, SALARY;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES;
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
SALARY NUMBER(5);
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO EMP_ID, SALARY;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('EMP_ID-> '|| EMP_ID||' '||'SAL-> '||SAL);
END LOOP;
CLOSE C1;
END;

SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES;
EMP_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
SALARY NUMBER(5);
BEGIN
----OPEN C1;
IF NOT C1%ISOPEN THEN
OPEN C1;
END IF;

LOOP
FETCH C1 INTO EMP_ID, SALARY;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ('EMP_ID-> '|| EMP_ID||' '||'SAL-> '||SAL);
END LOOP;
CLOSE C1;
END;

*** CURSOR FOR LOOP ***

----IT IS SIMPLE AND COMPACT SYNTAX WISE.
----NO NEED TO USE EXIT AND ENTRY CONDITIONS USING CURSOR ATTRIBUTES.
----NO NEED TO DECLARE VARIABLE.
----NO NEED TO FETCH THE CURSOR.
----NO NEED TO OPEN AND CLOSE THE CURSOR
----COL_NAME

SYNTAX: FOR I IN CURSOR_NAME

LOOP
END LOOP:

SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE('OK');
END LOOP;
END;

SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT EMPLOYEE_ID, SALARY FROM EMPLOYEES;
BEGIN
FOR I IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(I.EMPLOYEE_ID||' '||[Link]);
END LOOP;
END;

*** CURSOR ***

----CURSOR IS USED TO STORE GROUP OF ROWS
----CURSOR IS A POINTER WHICH POINT SQL PRIVATE MEMORY.
----IT HAS TWO TYPES
A. IMPLICIT CURSOR
B. EXPLICIT CURSOR

A. IMPLICIT CURSOR
----IT DECLARE AND MANAGE INTERNALLY BY ORACLE SERVER. IT HAS THREE ATTRIBUTES.

1. SQL%ROWCOUNT:
---IT USED TO RETURN NUMBER OF MODIFY ROW PRESENT IN IMPLICIT CURSOR

2. SQL%FOUND:
----IT USED TO RETURN BOOLEAN VALUE TRUE OR FALSE
----IF DATA FOUND IN IMPLICIT CURSOR BOOLEAN FLAG RETURN TOO ELSE FALG RETURN FALSE
3. SQL%NOTFOUND:
----IT USED TO RETURN BOOLEAN VALUE TRUE OR FALSE
----IF DATA NOT FOUND IN IMPLICIT CURSOR BOOLEAN FLAG RETURN TOO ELSE FALG RETURN
FALSE

B. EXPLICIT CURSOR:
----EXPLICITLY DECLARE BY PROGRAMER. IT HAS FOUR ATTRIBUTES
1. %ROWCOUNT:
---IT USED TO RETURN NUMBER OF MODIFY ROW PRESENT IN EXPLICITE CURSOR

2. %FOUND:
----IT USED TO RETURN BOOLEAN VALUE TRUE OR FALSE
----IF DATA FOUND IN EXPLICIT CURSOR BOOLEAN FLAG RETURN TOO ELSE FALG RETURN FALSE

3. %NOTFOUND:
----IT USED TO RETURN BOOLEAN VALUE TRUE OR FALSE
----IF DATA NOT FOUND IN EXPLICIT CURSOR BOOLEAN FLAG RETURN TOO ELSE FALG RETURN
FALSE

4. %ISOPEN:
----IT USED TO RETURN BOOLEAN VALUE TRUE OR FALSE IF CURSOR ALREADY OPEN
----BOOLEAN FLAG RETURN TRUE ELSE RETURN FALSE.

>>> DISPLAY THE DELETED ROWS USING PL/SQL?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
DELETE FROM DATA_TEST_2;
N1:=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(N1||' ROWS DELETED');
END;

>>> DISPLAY THE COUNT OF ROWS. IF ROWS NOT FOUND THEN PRINT MSG DATA ALREADY
DELETED?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
IF SQL%FOUND
THEN
N1:=SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE(N1||' ROWS DETECTED');
ELSE
DBMS_OUTPUT.PUT_LINE('TABLE ALREADY DELETED DO NOT TRY TO DELETED THE ROWS');
END IF;
END;

>>> ...REMAINING

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
UPDATE TEST_1
SET TEST_1_NAME='OM'
WHERE EMP_NO=1

>>> DISPLAY THE NOT FOUND DATA. IF IT IS DATA AVAILABLE THEN PRINT DATA DETECTED?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(20);
BEGIN
IF SQL%NOTFOUND
THEN
DBMS_OUTPUT.PUT_LINE('TABLE ALREADY DELETED DO NOT TRY TO DELETED THE ROWS');
ELSE
DBMS_OUTPUT.PUT_LINE(N1||' ROWS DETECTED');
END IF;
END;

>>> ...REMAINING

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
N1 NUMBER(10);
BEGIN
UPDATE EMPLOYEES
SET FIRST_NAME='Akash'
WHERE EMPLOYEE_ID=&X1;
N1:=SQL%ROWCOUNT;
IF SQL%FOUND
THEN
DBMS_OUTPUT.PUT_LINE(N1||'ROW UPDATED');
ELSE
DBMS_OUTPUT.PUT_LINE('INVALID');
END IF;
END;

SELECT * FROM EMPLOYEES

*** EXPLICIT CURSOR ***

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR C1 IS SELECT FIRST_NAME,SALARY
FROM EMPLOYEES
WHERE EMPLOYEE_ID >100;
V1 VARCHAR(10);
V2 NUMBER(10);
BEGIN
OPEN C1;
FETCH C1 INTO V1, V2;
DBMS_OUTPUT.PUT_LINE(V1||V2);
CLOSE C1;
END;

*** CURSOR WITH RECORD ***

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR C1 IS SELECT * FROM EMPLOYEES;
V1 C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.EMPLOYEE_ID||V1.FIRST_NAME);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

*** PARAMETERIZED CUROSR ***

----WE USE THIS FOR TO OVERCOME OF DRAWBACK OF SINGLE AMPESAND
---WE FETCH THE VALUE MULTIPLE TIME USING THIS PARAMETRIZED CUROSR
---

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR C1(N1 NUMBER) IS SELECT * FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
V1 C1%ROWTYPE;
BEGIN
OPEN C1 (50);
LOOP
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.EMPLOYEE_ID);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;

OPEN C1(80);
LOOP
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.EMPLOYEE_ID||V1.FIRST_NAME||V1.LAST_NAME);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

SELECT * FROM EMPLOYEES;

>>> DISPLAY DEPARTMENT NUMBER=?, MANAGER_ID=?, DISPLAY ENAME, DEPARTMENT_ID, USING

CURSOR?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR C1(N1 NUMBER, N2 NUMBER) IS SELECT * FROM EMPLOYEES
WHERE EMPLOYEE_ID=N1 OR MANAGER_ID=N2;
V1 C1%ROWTYPE;
BEGIN
OPEN C1 (90,100);
LOOP
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.EMPLOYEE_ID||' '||V1.DEPARTMENT_ID||' '||
V1.MANAGER_ID||' '||V1.FIRST_NAME);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;

OPEN C1 (50,120);
LOOP
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.EMPLOYEE_ID||' '||V1.DEPARTMENT_ID||' '||
V1.MANAGER_ID||' '||V1.FIRST_NAME);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

>>> DISPLAY DEPARTMENT NAME=?, FIRST_NAME, LAST_NAME USING PARAMETERIZED CUROSOR?

SELECT * FROM DEPARTMENTS;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR C1(N1 VARCHAR) IS SELECT * FROM DEPARTMENTS
WHERE DEPARTMENT_NAME='N1';
V1 C1%ROWTYPE;
BEGIN
OPEN C1 ('IT');
LOOP
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.DEPARTMENT_NAME);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

OPEN C1 (N1);
LOOP
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.EMPLOYEE_ID||' '||V1.DEPARTMENT_ID||' '||
V1.MANAGER_ID||' '||V1.FIRST_NAME);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR C1(N1 NUMBER) IS SELECT SUM(SALARY),AVG(SALARY),MAX(SALARY),MIN(SALARY)
FROM EMPLOYEES WHERE DEPARTMENT_ID=N1;
V1 NUMBER(10);
V2 NUMBER(10);
V3 NUMBER(10);
V4 NUMBER(10);
BEGIN
OPEN C1 (90);
LOOP
FETCH C1 INTO V1,V2,V3,V4;
DBMS_OUTPUT.PUT_LINE(V1||' '||V2||' '||V3||' '||V4);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;

OPEN C1(60);
LOOP
FETCH C1 INTO V1,V2,V3,V4;
DBMS_OUTPUT.PUT_LINE(V1||' '||V2||' '||V3||' '||V4);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR C1 IS SELECT * FROM EMPLOYEES;
BEGIN
FOR V IN C1
LOOP
DBMS_OUTPUT.PUT_LINE(V.EMPLOYEE_ID||V.FIRST_NAME);
END LOOP;
END;

*** UN-NAMED CURSOR ***

SET SERVEROUTPUT ON
SET VERIFY OFF
BEGIN
FOR V IN (SELECT * FROM EMPLOYEES)
LOOP
DBMS_OUTPUT.PUT_LINE(V.EMPLOYEE_ID||' '||V.FIRST_NAME);
END LOOP;
END;

*** %ISOPEN ***

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR C1 IS SELECT * FROM EMPLOYEES;
V1 C1%ROWTYPE;
BEGIN
IF C1%ISOPEN
THEN
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.EMPLOYEE_ID||V1.FIRST_NAME);
ELSE
OPEN C1;
FETCH C1 INTO V1;
DBMS_OUTPUT.PUT_LINE(V1.EMPLOYEE_ID||V1.FIRST_NAME);
END IF;
CLOSE C1;
END;

*** CURRENT OF CLAUSE ***

>>> UPDATE THE SALARY OF EMP USING CURRENT OF CLAUSE?

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR C1 IS SELECT * FROM EMPLOYEES FOR UPDATE OF SALARY;
V1 C1%ROWTYPE;
BEGIN
OPEN C1;
FETCH C1 INTO V1;
FETCH C1 INTO V1;
UPDATE EMPLOYEES
SET SALARY =SALARY+3000
WHERE CURRENT OF C1;
DBMS_OUTPUT.PUT_LINE('ROW UPDATED');
CLOSE C1;
END;

>>>
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
CURSOR C1(N1 VARCHAR2)IS SELECT FIRST_NAME, DEPARTMENT_NAME FROM EMPLOYEES
E INNER JOIN DEPARTMENTS D
ON(E.DEPARTMENT_ID=D.DEPARTMENT_ID)
WHERE DEPARTMENT_NAME=N1;
V1 VARCHAR2(20);
V2 VARCHAR2(30);
BEGIN
OPEN C1('Sales');
LOOP
FETCH C1 INTO V1,V2;
DBMS_OUTPUT.PUT_LINE(V1||V2);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
OPEN C1('IT');
LOOP
FETCH C1 INTO V1,V2;
DBMS_OUTPUT.PUT_LINE(V1||V2);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
CURSOR C1(N1 NUMBER)IS SELECT SUM(SALARY) AS DATA1,AVG(SALARY) AS DATA2,MAX(SALARY)
AS DATA3
FROM EMPLOYEES
WHERE DEPARTMENT_ID=N1;
V1 NUMBER(10);
V2 NUMBER(10);
V3 NUMBER(10);
BEGIN
OPEN C1(50);
LOOP
FETCH C1 INTO V1,V2,V3;
DBMS_OUTPUT.PUT_LINE(V1||V2||V3);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
OPEN C1(80);
LOOP
FETCH C1 INTO V1,V2,V3;
DBMS_OUTPUT.PUT_LINE(V1||V2||V3);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

SET VERIFY OFF

SET SERVEROUTPUT ON
DECLARE
CURSOR C1(N1 VARCHAR2)IS SELECT FIRST_NAME,MAX_SALARY FROM EMPLOYEES E JOIN JOBS J
ON(E.JOB_ID=J.JOB_ID)
WHERE JOB_TITLE=N1;
V1 VARCHAR2(20);
V2 NUMBER(20);
BEGIN
OPEN C1('President');
LOOP
FETCH C1 INTO V1,V2;
DBMS_OUTPUT.PUT_LINE(V1||V2);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
OPEN C1('Accountant');
LOOP
FETCH C1 INTO V1,V2;
DBMS_OUTPUT.PUT_LINE(V1||V2);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

SET VERIFY OFF

SET SERVEROUTPUT ON
DECLARE
CURSOR C1(N1 VARCHAR)IS SELECT EMPLOYEE_ID,FIRST_NAME,HIRE_DATE FROM EMPLOYEES
WHERE TO_CHAR(HIRE_DATE,'MON')=N1;
V1 NUMBER(10);
V2 VARCHAR2(20);
V3 DATE;
BEGIN
OPEN C1('JUN');
LOOP
FETCH C1 INTO V1,V2,V3;
DBMS_OUTPUT.PUT_LINE(V1||V2||V3);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;

*** EXCEPTION HANDLING ***

------------ PROCEDURE -------------- FUNCTION ------------ TRIGGER ------------

*** PL/SQL OBJECT ***

A. PROCEDURE
B. TRIGGER
C. FUNCTION
D. PACKAGE

*** PROCEDURE & FUNCTION DIFFERENCE ***

*** PROCEDURE ***

A. PERFORM TASK
B. EXPLICIT CALL USING EXECUTE COMMAND
C. MAY OR MAY NOT RETURN VALUE
D. FUCNTION CAN CALL INSIDE PROCEDURE
E. DML COMMAND ALLOW

*** FUNCTION ***

A. PERFORM CALCULATION
B. IMPLICITLY CALL I.E DIRECTLY IN SQL STATEMENT
C. MUST RETAIN VALUE
D. PROCEDURE LOGICALLY NOT ALLOW TO CALL IN FUCNTION
E. LOGICALLAY NOT ALLOW

*** PROCEDURE ***

>>> WE WANT TO FETCH THE FULL NAME, SALARY 20% AND HIRE DATE DAY OF ANY OF THE
EMPLOYEE?
CREATE OR REPLACE PROCEDURE X1 (N1 NUMBER)
IS
V1 VARCHAR2(20);
V2 VARCHAR2(20);
V3 NUMBER(10);
BEGIN
SELECT FIRST_NAME||LAST_NAME AS FULL_NAME, TO_CHAR(HIRE_DATE,'DAY'),SALARY*0.2
INTO V1,V2,V3 FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
DBMS_OUTPUT.PUT_LINE(V1||' '||V2||' '||V3);
END;

SET SERVEROUTPUT ON
EXECUTE X1(&X2);

>>> UPDATE SOME DATA IN ANY TABLE USING PL/SQL PROCEDURE?

CREATE OR REPLACE PROCEDURE A1 (N1 VARCHAR2,N2 NUMBER)
IS
BEGIN
UPDATE EMPLOYEES
SET FIRST_NAME=N1
WHERE EMPLOYEE_ID=N2;
DBMS_OUTPUT.PUT_LINE('DATA INSERTED');
END;

SET SERVEROUTPUT ON
EXECUTE A1('&X1',&X2);

>>> DELETE SOME ROWS FROM THE TABLE USING PL/SQL PROCEDURE?
CREATE OR REPLACE PROCEDURE A2(N1 NUMBER)
IS
BEGIN
DELETE T1
WHERE TNO=N1;
DBMS_OUTPUT.PUT_LINE('DATA DELETED');
END;

SHOW ERROR

SET SERVEROUTPUT ON
EXECUTE A2(&X1);

>>> DISPLAY SUM,MIN,AVG SALARY OF ANY OF THE DEPARTMENT?

CREATE OR REPLACE PROCEDURE X4 (N1 NUMBER)
IS
V1 NUMBER(10);
V2 NUMBER(10);
V3 NUMBER(10);
BEGIN
SELECT SUM(SALARY),AVG(SALARY),MIN(SALARY) INTO V1,V2,V3 FROM EMPLOYEES
WHERE DEPARTMENT_ID=N1;
DBMS_OUTPUT.PUT_LINE(V1||' '||V2||' '||V3);
END;

SET SERVEROUTPUT ON
EXECUTE X4(&X1);
>>> COPY DATA FROM ONE TABLE TO ANOTHER TABLE USING PROCEDURE?
CREATE OR REPLACE PROCEDURE X8 (N1 NUMBER)
IS
BEGIN
INSERT INTO T2 (SELECT * FROM T1 WHERE TNO=N1);
DBMS_OUTPUT.PUT_LINE('DATA COPY');
END;

SHOW ERROR ...IT IS FOR FINDING THE ERROR

SET SERVEROUTPUT ON
EXECUTE X8(&X1); ...THIS REQUIRED FOR EXCUTING THE PROCEDURE.

>>> WE HAVE SOME INPUT LIKE 'SEARCH, MODIFY, REGISTER, REMOVE'.

IF WE ARE GIVING ONE OF INPUT THEN IT WILL SEARCH LIKE THAT?
SET SERVEROUTPUT ON
SET VERIFY OFF
CREATE OR REPLACE PROCEDURE A3(A5 VARCHAR2,N1 NUMBER,N2 VARCHAR2)
IS
V1 EMPLOYEES%ROWTYPE;
V2 T2%ROWTYPE;
BEGIN
IF A5='SEARCH'
THEN
SELECT * INTO V1 FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
DBMS_OUTPUT.PUT_LINE(V1.FIRST_NAME||V1.LAST_NAME);
ELSIF
A5='MODIFY'
THEN
UPDATE EMPLOYEES
SET FIRST_NAME=N2
WHERE EMPLOYEE_ID=N1;
DBMS_OUTPUT.PUT_LINE('DATA MODIFY');
ELSIF
A5='REGISTER'
THEN
INSERT INTO T2 (SELECT * FROM T1 WHERE TNO=N1);
DBMS_OUTPUT.PUT_LINE('DATA COPY');
ELSIF
A5='REMOVE'
THEN
DELETE T1
WHERE TNO=N1;
DBMS_OUTPUT.PUT_LINE('DATA DELETED');
END IF;
END;

SHOW ERROR

SET SERVEROUTPUT ON
EXECUTE A3 ('&X9',&X8,'&X7');

*** NESTED PROCEDURE ***

----NESTED QUERY NOT SHOWING RESULT DUE TO PRIVATE TASK
----AND OUTER QUERY IS SHOWING RESULT BCZ IT IS PUBLIC TASK.
>>> DISPLAY THE SALARY 15% USING NESTED PROCEDURE?
SET SERVEROUTPUT ON
SET VERIFY OFF
CREATE OR REPLACE PROCEDURE AK(N1 NUMBER)
IS
PROCEDURE AK1
IS
V1 NUMBER(20);
BEGIN
V1:=N1*15;
DBMS_OUTPUT.PUT_LINE('LOAN INTEREST IS :'||V1);
END;
BEGIN
AK1;
DBMS_OUTPUT.PUT_LINE('TASK COMPLETED');
END;

SET SERVEROUTPUT ON
EXECUTE AK(10000);

*** FUNCTION ***

>>> DISPLAY 18% OF 500 USING FUNCTION?

CREATE OR REPLACE FUNCTION TAX18

RETURN NUMBER
IS
BEGIN
RETURN 500*0.18;
END;

----BOTH WAY WE CAN PRINT.

SELECT TAX18() FROM DUAL;
SET SERVEROUTPUT ON
DECLARE
V1 NUMBER(10);
BEGIN
V1:=TAX18();
DBMS_OUTPUT.PUT_LINE(V1);
END;

>>> USING FUNCTION WE CAN GET SALARY 18%?

CREATE OR REPLACE FUNCTION TAX18(N1 NUMBER)

RETURN NUMBER
IS
BEGIN
RETURN N1*0.18;
END;

SELECT SALARY ,TAX18(SALARY) FROM EMPLOYEES;

>>> WE CAN GIVE USER VALUE AND THEN CHECK IT IS EVEN OR NOT ?
CREATE OR REPLACE FUNCTION TAX17(N1 NUMBER)
RETURN VARCHAR2
IS
BEGIN
IF N1 MOD 2=0
THEN
RETURN 'EVEN';
ELSE
RETURN 'ODD';
END IF;
END;

SHOW ERROR
SELECT TAX17(&X2) FROM DUAL;

>>> USING FUCNTION WE HAVE TO DELETE THE DATA OF ANY TABLE?

CREATE FUNCTION F7
RETURN NUMBER
IS
BEGIN
DELETE FROM T1;
DBMS_OUTPUT.PUT_LINE('DATA DELETED');
RETURN 0;
END;

SHOW ERROR
SELECT F7()FROM DUAL; ...IT WILL NOT RETURN OUTPUT BCZ THIS INSERT, DELETE, AND
UPDATE
IT IS DML COMMAND SO CAN PERFORM ON THIS FUNCTION BUT WE HAVE TO USE ANONYMOUS
BLOCK FOR THAT.

SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
V1 NUMBER(20);
BEGIN
V1:=F7();
DBMS_OUTPUT.PUT_LINE(V1);
END;
ROLLBACK T1;

>>> DISPLAY 25% TAX OF ANY VALUE USING FUNCTION ?

CREATE OR REPLACE FUNCTION F2 (N1 NUMBER)

RETURN VARCHAR2
IS
V1 NUMBER(20);
FUNCTION F3
RETURN NUMBER
IS
BEGIN
RETURN N1*0.25;
END;
BEGIN
V1:=F3();
RETURN '25% TAX IS '||V1;
END;

SHOW ERROR
SET SERVEROUTPUT ON
SELECT F2(500) FROM DUAL;

>>> DISPLAY MAX NO OF NESTED QUERY WITH GIVING 2 USER INPUT AND THEN CHECK
WITH OUTER QUERY IT IS ODD OR EVEN?

CREATE OR REPLACE PROCEDURE AB(N1 NUMBER,N2 NUMBER)

IS
PROCEDURE AB1
IS
BEGIN
IF N1 > N2
THEN
DBMS_OUTPUT.PUT_LINE(N1);
ELSIF
N2 > N1
THEN
DBMS_OUTPUT.PUT_LINE(N2);
END IF;
END;
BEGIN
AB1;
IF N1> N2
THEN
IF N1 MOD 2=0
THEN
DBMS_OUTPUT.PUT_LINE('IT IS EVEN');
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS ODD');
END IF;
ELSE
IF N2 > N1
THEN
IF N2 MOD 2=0
THEN
DBMS_OUTPUT.PUT_LINE('IT IS EVEN');
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS ODD');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS NOT EXIST');
END IF;
END IF;
END;

SHOW ERROR
SET SERVEROUTPUT ON
EXECUTE AB(&X1,&X2);

>>> GET THE VALUE FROM USER AND DISPLAY THE JOINING MONTH OF EMP?
CREATE OR REPLACE FUNCTION JOIN1(N1 NUMBER)
RETURN VARCHAR2
IS
V1 VARCHAR2(20);
BEGIN
SELECT TO_CHAR(HIRE_DATE,'MON') INTO V1 FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
RETURN V1;
END;

SHOW ERROR
SET SERVEROUTPUT ON
SELECT JOIN1(&X1) FROM DUAL;

>>> CREATE A CALCI USING FUCNTION?

CREATE OR REPLACE FUNCTION W1(OP VARCHAR2,N1 NUMBER, N2 NUMBER)

RETURN NUMBER
IS
RESULT NUMBER(10);
BEGIN
IF OP='/'
THEN
RESULT:=N1/N2;
RETURN RESULT;
ELSIF
OP='*'
THEN
RESULT:=N1*N2;
RETURN RESULT;
ELSIF
OP='+'
THEN
RESULT:=N1+N2;
RETURN RESULT;
ELSIF
OP='-'
THEN
RESULT:=N1-N2;
RETURN RESULT;
ELSE
RETURN 0;
END IF;
END;

SET SERVEROUTPUT ON
SELECT W1('&X1',&X2,&X3) FROM DUAL;

>>> TAKE 3 VALUE FROM USER AND RETURN THERE MIN VALUE?

CREATE OR REPLACE FUNCTION Q2 (N1 NUMBER, N2 NUMBER, N3 NUMBER)

RETURN NUMBER
IS
BEGIN
IF N1 < N2 AND N1 < N3
THEN
RETURN N1;
ELSIF
N2 < N1 AND N2 < N3
THEN
RETURN N2;
ELSIF
N3 < N1 AND N3 < N2
THEN
RETURN N3;
ELSE
RETURN 0;
END IF;
END;

SET SERVEROUTPUT ON
SELECT Q2(&N1,&N2,&N3) FROM DUAL;

>>> TAKE 3 VALUE FROM USER AND RETURN THERE MAX VALUE?

CREATE OR REPLACE FUNCTION Q1(N1 NUMBER, N2 NUMBER, N3 NUMBER)

RETURN NUMBER
IS
BEGIN
IF N1 > N2 AND N1 > N3
THEN
RETURN N1;
ELSIF
N2 > N1 AND N2 > N3
THEN
RETURN N2;
ELSIF
N3 > N1 AND N3 > N2
THEN
RETURN N3;
ELSE
RETURN 0;
END IF;
END;

SELECT Q1(&N1,&N2,&N3) FROM DUAL;

>>>
CREATE OR REPLACE FUNCTION F1
RETURN VARCHAR2
IS
V2 NUMBER(20);
FUNCTION F2
RETURN NUMBER
IS
V1 NUMBER(20);
COUNT1 NUMBER(20):=0;
BEGIN
FOR I IN 100..109
LOOP
SELECT SALARY INTO V1 FROM EMPLOYEES WHERE EMPLOYEE_ID=I;
COUNT1:=COUNT1+V1;
END LOOP;
RETURN COUNT1;
 END;
BEGIN
V2:=F2();
IF V2 MOD 5=0
THEN
RETURN 'IT IS DIVISIBLE BY 5'||' '||F2;
ELSE
RETURN 'IT IS NOT DIVISIBLE BY 5'||' '||F2;
END IF;
END;

SHOW ERROR
SET SERVEROUTPUT ON
SELECT F1 FROM DUAL;

>>> CREATE A LOGIN PROCEDURE USING FUNCTION?

CREATE OR REPLACE FUNCTION Q4 (N1 VARCHAR2,N2 VARCHAR2)

RETURN VARCHAR2
IS
BEGIN
IF N1 LIKE 'A%' AND N1 LIKE '%@%' AND LENGTH(N1)=6
THEN
IF N2 LIKE '%#%' AND N2 LIKE '%123' AND LENGTH (N2)=8
THEN
RETURN 'LOGIN SUCCESS';
ELSE
RETURN 'LOGIN FAIL';
END IF;
ELSE
RETURN 'USER NAME FAIL';
END IF;
END;

SET SERVEROUTPUT ON
SELECT Q4 ('&X1','&X2') FROM DUAL;

>>> MAKE A CODE USING PROCEDURE AND ALSO USE IN AND OUT FOR PARAMETERIZED CURSOR?

CREATE OR REPLACE PROCEDURE Q5(N1 IN NUMBER,N2 OUT VARCHAR2)

IS
BEGIN
SELECT FIRST_NAME INTO N2 FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
END;

VARIABLE V1 VARCHAR2;
EXECUTE Q5(105,:V1);
PRINT V1;

>>> USING IN OUT FOR PARAMETRIZED CURSOR. DISPLAY FIRST_NAME USING PROCEDURE?

CREATE OR REPLACE PROCEDURE Q6(N1 IN OUT VARCHAR2)

IS
BEGIN
SELECT FIRST_NAME INTO N1 FROM EMPLOYEES WHERE EMPLOYEE_ID=N1;
END;
VARIABLE V1 VARCHAR2;
BEGIN
:V1:=105;
END;
EXECUTE Q6(:V1);
PRINT V1;

*** SYS_REFCURSOR ***

----IT IS ONE KIND OF DATA TYPE.
----IT IS DATA TYPE FOR ONLY CURSOR.
----IT IS RETURN GROUP OF ROWS.

>>> USING SYS_REFCURSOR WE CAN FETCH THE DATA OF ALL EMPLOYEE TABLE?

CREATE PROCEDURE E4 (C1 OUT SYS_REFCURSOR)

IS
BEGIN
OPEN C1 FOR SELECT * FROM EMPLOYEES;
END;

CREATE OR REPLACE PROCEDURE E1

IS
C2 SYS_REFCURSOR;
X1 EMPLOYEES%ROWTYPE;
BEGIN
E4(C2);
LOOP
FETCH C2 INTO X1;
EXIT WHEN C2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(X1.FIRST_NAME||' '||X1.LAST_NAME);
END LOOP;
END;

SET SERVEROUTPUT ON
EXECUTE E1;

>>> WE CAN USE PROCEDURE INSIDE FUNCTION?

CREATE OR REPLACE PROCEDURE R1

IS
V1 NUMBER(10);
FUNCTION F1
RETURN NUMBER
IS
BEGIN
RETURN 750;
END;
BEGIN
V1:=F1();
DBMS_OUTPUT.PUT_LINE(V1);
END;

SET SERVEROUTPUT ON
EXECUTE R1;
>>> WE CAN USE FUNCTION INSIDE PROCEDURE?

CREATE OR REPLACE FUNCTION R2

RETURN NUMBER
IS
PROCEDURE R3
IS
BEGIN
DELETE FROM T1;
DBMS_OUTPUT.PUT_LINE('DATA DELETED');
END;
BEGIN
R3;
RETURN 15;
END;

SET VERIFY OFF

SET SERVEROUTPUT ON
DECLARE
X1 NUMBER(10);
BEGIN
X1:=R2();
DBMS_OUTPUT.PUT_LINE(X1);
END;

CREATE OR REPLACE FUNCTION K1(N4 IN OUT NUMBER,N2 OUT VARCHAR2)

RETURN NUMBER
IS
N3 NUMBER(20);
BEGIN
SELECT EMPLOYEE_ID,FIRST_NAME,SALARY INTO N4, N2 , N3 FROM EMPLOYEES
WHERE EMPLOYEE_ID=N4;
RETURN N3;
END;

VARIABLE X1 VARCHAR2;
VARIABLE X2 NUMBER;
SELECT K1 (&X3,:X1,:X2) FROM DUAL;
PRINT X1;
PRINT X2;

>>> DISPLAY FULL NAME, SALARY, HIRE_DATE AND GET EMPLOYEE_ID FROM USER AND DISPLAY
ABOVE?
USING PROCEDURE RETURN?

CREATE OR REPLACE PROCEDURE G(N4 IN NUMBER,N1 OUT VARCHAR2,N2 OUT NUMBER)

IS
N3 DATE;
BEGIN
SELECT FIRST_NAME||LAST_NAME,SALARY,HIRE_DATE INTO N1,N2,N3
FROM EMPLOYEES WHERE EMPLOYEE_ID=N4;
DBMS_OUTPUT.PUT_LINE(N3);
END;

SHOW ERROR
VARIABLE Z1 VARCHAR2;
VARIABLE Z2 NUMBER;
BEGIN
:

VARIABLE V1 VARCHAR2;
BEGIN
:V1:=105;
END;
EXECUTE Q6(:V1);
PRINT V1;

CREATE PROCEDURE B2(N1 IN NUMBER,N2 OUT NUMBER,N3 OUT NUMBER,N4 OUT NUMBER)
IS
BEGIN
SELECT SUM(SALARY),AVG(SALARY),COUNT(SALARY)INTO N2,N3,N4 FROM EMPLOYEES
WHERE DEPARTMENT_ID=N1;
END;
VARIABLE SUM NUMBER;
VARIABLE AVG NUMBER;
VARIABLE COUNT NUMBER;
SET SERVEROUTPUT ON
EXECUTE B2( &X1, :SUM, :AVG, :COUNT);
PRINT SUM;
PRINT AVG;
PRINT COUNT;

CREATE PROCEDURE M10(N1 IN NUMBER, N2 OUT VARCHAR2,N3 OUT NUMBER,N4 OUT VARCHAR2)
IS
BEGIN
SELECT FIRST_NAME ||LAST_NAME,SALARY,TO_CHAR(HIRE_DATE,'DD-MON-YY')INTO N2,N3,N4
FROM EMPLOYEES
WHERE EMPLOYEE_ID=N1;
END;
VARIABLE FULL_NAME VARCHAR2;
VARIABLE SALARY NUMBER;
VARIABLE HIRE_DATE VARCHAR2;
SET SERVEROUTPUT ON
EXECUTE M10(&X1,:FULL_NAME,:SALARY,:HIRE_DATE);
PRINT FULL_NAME;
PRINT SALARY;
PRINT HIRE_DATE;

*** TRIGGER ***

----TRIGGER HAS TWO TYPES.
1. STATEMENT LEVEL TRIGGER
2. ROW LEVEL TRIGGER

*** STATEMENT LEVEL TRIGGER ***

----WHEN TRIGGER BODY EXECUTE FOR MODIFY AND NON-MODIFY ROWS IS KNOWN AS STATEMENT
LEVEL TRIGGER.
----DEFAULT TYPE OF TRIGGER IS STATEMENT LEVEL TRIGGER

*** ROW LEVEL TRIGGER ***

----WHEN TRIGGER BODY EXECUTE FOR MODIFY ROWS TRIGGER KNOWN AS ROW LEVEL TRIGGER.

*** TRIGGER HAS TWO CATEGORY ***

1. APPLCATION TRIGGER
2. DATA BASE TRIGGER

1. APPLCATION TRIGGER
----SCOPE OF TRIGGER WITH IN SPECIFIC APPLICATION IS KNOWN AS APPLICATION TRIGGER.

2. DATA BASE TRIGGER

----SCOPE OF TRIGGER WITH IN SPECIFIC DATABASE IS KNOWN AS DATA BASE TRIGGER.

SYNTAX:
CREATE OR REPLACE TRIGGER T1
TIMMING/ EVENT FOR TIMMING ...AFTER/BEFORE/INSTED OF =VIEW
ON TABLE/ VIEW FOR EVENT ...DML/DDL
FOR EACH ROW ROW LEVEL TRIGGER
WHEN CONDITION
BEGIN
TRIGGER BODY
END;

>>> CREATE EVENT ON T1 TABLE. IF DELETE HAPPEN ON T1 TABLE THEN INSERT ON T2 TABLE?
CREATE OR REPLACE TRIGGER T11
AFTER DELETE
ON T1
BEGIN
INSERT INTO T2 VALUES(21,'MEENA',2500);
DBMS_OUTPUT.PUT_LINE('DATA INSERTED'); ---IT IS NOT REQUIRED IN TRIGGER.
END;

DELETE FROM T1 WHERE CNO=10;

SELECT * FROM T2;
SELECT * FROM T1;
I
>>> CREATE TRIGGER ON T1 TABLE? AND IF UPDATE, DELETE, INSERT IN T1 THEN UPDATE T2?
CREATE OR REPLACE TRIGGER C2
AFTER DELETE OR INSERT OR UPDATE
ON T3
BEGIN
UPDATE T2
SET T_NAME='VIJAY'
WHERE T_ID=10;
END;

SHOW ERROR
SELECT * FROM T2;
DELETE FROM T3 WHERE CNO=20; ---IF ONLY EVENT HAPPEN ON BACKROUND THEN
AUTOMATICALLY ACTION HAPPEN
ROLLBACK T3

>>> CREATE TRIGGER ON T1 TABLE. BEFORE UPDATE,INSERT,DELETE RAISE EXCEPTION ERROR?

CREATE OR REPLACE TRIGGER C3
BEFORE DELETE OR UPDATE OR INSERT
ON T1
BEGIN
IF INSERTING
THEN
RAISE_APPLICATION_ERROR(-20003,'INSERTING NOT ALLOW');
ELSIF DELETING
THEN
RAISE_APPLICATION_ERROR(-20002,'DELETING NOT ALLOW');
ELSE
DBMS_OUTPUT.PUT_LINE('UPDATING DONE');
END IF;
END;

SHOW ERROR
DELETE FROM T1;
INSERT INTO T1 VALUES(45,'SINGH',4000);
UPDATE T1 SET CNO=30 WHERE CNO=20;
SELECT * FROM T1;

*** PRAGMA AUTONOMOUS TRANSACTION ***

----IT WILL LIMIT THE COMMIT OR LIMIT THE BLOCK
----NEED TO PUT IN DECLARATION.
----WITH THE HELP OF THIS TRANSACTION WE CAN USE TCL COMAND IN TRIGER
----WITH THE HELP OF THIS TRANSACTION WE CAN USE ALL TCL COMMAND.
----IT USED TO KEEP SCOPE OF TCL COMMAND WITH IN PL/SQL BLOCK.

SELECT * FROM USER_SOURCE;

----IT WILL SHOW THE CODE WITH THE HELP OF DICTIONARY VIEW
----IT IS USED TO DISPLAY SOURCE CODE OF PROCEDURE, AND FUNCTION

SELECT * FROM USER_TRIGGERS;

----IT IS USED TO DISPLAY SOURCE CODE OF TRIGGER.
----IT IS ONLY SHOWING THE DATA OF TRIGGER

>>> MAKE A TRIGGER ON ACCOUNT TABLE AND WATCH THE OLD AND NEW TABLE TRANSACTION
HISTORY DETAILS?
CREATE TABLE ACCOUNT(A_NO NUMBER(20), A_NAME VARCHAR2(20),A_BAL NUMBER(20));
SELECT * FROM ACCOUNT;

CREATE TABLE TRANSACTION_HISTORY

(OLDA_NO NUMBER,NEWA_NO NUMBER,OLDA_NAME VARCHAR2(20),NEWA_NAME VARCHAR2(20),
OLDA_BAL NUMBER(20),NEWA_BAL NUMBER(20),TDATE TIMESTAMP, USER_DETAIL VARCHAR2(20));
SELECT * FROM ACCOUNT;
SELECT * FROM TRANSACTION_HISTORY;

CREATE OR REPLACE TRIGGER AUDIT_TR

AFTER INSERT OR UPDATE OR DELETE
ON ACCOUNT
FOR EACH ROW
BEGIN
INSERT INTO TRANSACTION_HISTORY VALUES
(:OLD.A_NO,:NEW.A_NO,:OLD.A_NAME,:NEW.A_NAME,
:OLD.A_BAL,:NEW.A_BAL,SYSDATE,USER);
END;

>>> KEEP AN EYE WATCH ON ACCOUNT 'AMAR'?

CREATE OR REPLACE TRIGGER AUDIT_TR
AFTER INSERT OR UPDATE OR DELETE
ON ACCOUNT
FOR EACH ROW
WHEN (NEW.A_NAME='AMAR')
BEGIN
INSERT INTO TRANSACTION_HISTORY VALUES
(:OLD.A_NO,:NEW.A_NO,:OLD.A_NAME,:NEW.A_NAME,
:OLD.A_BAL,:NEW.A_BAL,SYSDATE,USER);
END;

SELECT * FROM ACCOUNT;

SELECT * FROM TRANSACTION_HISTORY;
SELECT * FROM T1;

>>> GIVE LIMITATION IN WHEN CONDITION? IF SALARY IS GREATER THAN 15000 THEN RAISE
EXCEPTION?
CREATE OR REPLACE TRIGGER T111
BEFORE INSERT OR UPDATE
ON T2
FOR EACH ROW
BEGIN
IF :NEW.T_SAL < 15000
THEN
RAISE_APPLICATION_ERROR(-20002,'PLS ENTER VALID SALARY');
END IF;
END;
SHOW ERROR
INSERT INTO T2 VALUES (10,'SAM',10000);
SELECT * FROM T2;

>>> CREATE A TABLE FOR LOG IN OR LOG OUT DATA ENTRY PURPOSE
AND IT WILL EXECUTING AUTOMATICALLY AND RECORDED ALL THE DETAILS?

CREATE TABLE USER_DETAIL (U_NAME VARCHAR2(20), IN_OUT TIMESTAMP,STATUS

VARCHAR2(20));
SELECT * FROM USER_DETAIL;

CREATE OR REPLACE TRIGGER R1

AFTER LOGON
ON SCHEMA
BEGIN
INSERT INTO USER_DETAIL VALUES (USER,SYSDATE,'LOG ON SUCCESS');
END;

CREATE OR REPLACE TRIGGER R2

BEFORE LOGOFF
ON SCHEMA
BEGIN
INSERT INTO USER_DETAIL VALUES (USER,SYSDATE,'LOG OFF SUCCESS');
END;

>>> MAKE A LOGIN AND PASSWORD WITH THE HELP OF TRIGGER?

AND WHEN YOU DO LOGIN DATE AND TIME MUST BE NOTED ON DATA BASE TABLE?

CREATE TABLE USER_DETAILS (U_ID VARCHAR2(20),PASS VARCHAR2(20),DATE1 TIMESTAMP);

SELECT * FROM USER_DETAILS;
DROP TABLE USER_DETAILS;

CREATE OR REPLACE TRIGGER W13

BEFORE INSERT OR UPDATE OR DELETE
ON USER_DETAILS
FOR EACH ROW
BEGIN
IF :NEW.U_ID LIKE 'S%' AND LENGTH(:NEW.U_ID)=8
THEN
IF :[Link] LIKE '%@123%' AND LENGTH(:[Link])=6
THEN
DBMS_OUTPUT.PUT_LINE('LOG IN SUCCESSFUL');
ELSE
RAISE_APPLICATION_ERROR(-20002,'INVALID PASSWORD');
END IF;
ELSE
RAISE_APPLICATION_ERROR(-20003,'INVALID USERNAME');
END IF;
END;

SHOW ERROR
SELECT * FROM USER_DETAILS;
DELETE USER_DETAILS;
INSERT INTO USER_DETAILS VALUES('SHAHADEV','@123RO',SYSDATE);

>-- INSERT/UPDATE/DELETE ONLY ALLOW ON MON TO FRI AND [Link] TO [Link] ONLY?
>--WRITE THE TRIGGER CODE FOR IT?
CREATE OR REPLACE TRIGGER B11
BEFORE INSERT OR UPDATE OR DELETE
ON DUMMY
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE,'DY') BETWEEN 'MON' AND 'FRI'
THEN
IF TO_CHAR(SYSTIMESTAMP,'HH12:AM')IN(10,11,12)
OR TO_CHAR(SYSTIMESTAMP,'HH12:PM')IN(1,2,3,4,5,6,7)
THEN
DBMS_OUTPUT.PUT_LINE('THIS IS GOOD TIME PLS INSERT THE DATA');
ELSE
RAISE_APPLICATION_ERROR(-20001,'NOT ALLOW IN THIS TIME AT LAST');
END IF;
ELSE
RAISE_APPLICATION_ERROR(-20002,'NOT ALLOW IN THIS TIME');
END IF;
END;

SHOW ERROR
INSERT INTO DUMMY VALUES(1,'OM');
SELECT TO_CHAR(SYSDATE,'DY') FROM DUAL

SELECT SYSTIMESTAMP FROM DUAL

SELECT TO_CHAR(SYSTIMESTAMP,'TIME') FROM DUAL

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) AS CURRENT_HOUR FROM DUAL;

SELECT * FROM DUMMY;

create or replace procedure p90(v1 varchar2)
is
n1 varchar2(10);
begin
select first_name into n1 from employees where employee_id=v1;
dbms_output.put_line(n1);
end;
/

set serveroutput on;

execute p90(&v1);

SET SERVEROUTPUT ON;

BEGIN
P90(&N);
END;

create or replace trigger jn7y

after insert
on t3
for each row
begin
execute p90(&N);
end;

INSERT INTO T3 VALUES(20,'C','NASIK')

*** PACKAGE ***

----PL/SQL PACKAGE IS COLLECTION OF SUB PROGRAM LIKE PROCEDURE ,FUNCTION.
----WITHOUT SPECIFIATION WE CAN NOT WRITE BODY
----BODY IS HIDEEN FROM USER BUT SPECIFICATION IS OPEN FOR USER
----DESC PACKAGE IS USED TO DISLAY STRUCTURE OF DEFINATION OF GIVEN PACKAGE.

1. MODULARITY.
----DIVIDE SEPRATE MODILE

2. ABSTRACTION
----HIDING AND IMPLEMENTATION DETAILS.

3. ENCAPSULATION
----WRAPPING PROCEDURE AND FUCNTION IN SINGEL UNIT

4. PERFORMANCE.
----EXECUTE OR COMPILE GROUP OF PROGRAM

5. CODE OPTIMISATION
----REDUCE LINE OF CODE

6. OVERLOADING
----SAME FUNCTION WITH DIFFERENCT PARAMETER IN SAME PACKAGE

7. SAVE MEMORY
----SAVE LOT OF MEMORY

8. NAMING COLLISTION.
----AVOID NAMING COLLISION.

*** PACKAGE SYNTAX IS DIVIDE IN TWO PARTS

1. PACKAGE SPECIFICATION
----IT INCULDE PROCEDURE FUNCTION DEFINATION

2. PACKAGE BODY.
----IT INCLUDE IMPLEMENTATION (SOURCE CODE)

CREATE OR REPLACE PACKAGE HOSP

IS
PROCEDURE Y1; ...PUBLIC
FUNCTION F1 RETURN NUMBER;
V NUMBER(10):=800;
END;

CREATE OR REPLACE PACKAGE BODY HOSP

IS
PROCEDURE Y1
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('I AM PROCEDURE');
END; ...PRIVATE
FUNCTION F1 RETURN NUMBER
IS
BEGIN
RETURN V* 0.25;
END;
END;

SHOW ERROR
SET SERVEROUTPUT ON
EXECUTE HOSP.Y1;

SELECT HOSP.F1() FROM DUAL;

CREATE OR REPLACE PACKAGE HOSP

IS
PROCEDURE Y1; ----PUBLIC
FUNCTION F1 RETURN NUMBER;
V NUMBER(10):=800;
END;

CREATE OR REPLACE PACKAGE BODY HOSP

IS
PROCEDURE Y2
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('I AM PRIVATE PROCEDURE');
END;
FUNCTION F1 RETURN NUMBER
IS
BEGIN
RETURN V* 0.25;
END;
PROCEDURE Y1
IS
BEGIN
Y2;
DBMS_OUTPUT.PUT_LINE('I AM PUBLIC PROCEDURE');
END;
END;

SET SERVEROUTPUT ON
EXECUTE HOSP.Y1;

CREATE OR REPLACE PACKAGE HOSP

IS
FUNCTION ADD(V1 NUMBER,V2 NUMBER)RETURN NUMBER;
FUNCTION ADD(X1 NUMBER,X2 NUMBER,X3 NUMBER)RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY HOSP

IS
FUNCTION ADD (V1 NUMBER,V2 NUMBER)RETURN NUMBER
IS
BEGIN
RETURN V1+V2;
END;
FUNCTION ADD(X1 NUMBER,X2 NUMBER,X3 NUMBER)RETURN VARCHAR2
IS
BEGIN
RETURN X1+X2+X3||'TOTAL OF 3 NO IS';
END;
END;

SELECT [Link](2,5,7) FROM DUAL;

*** BODY LESS PACKAGE ***

----VARIABLE PERMANANTLY SAVE.
----WE CAN CALL WHENEVER WE WANT.
----PERMANANTLY STORE AND GLOBALLY WORK.

CREATE OR REPLACE PACKAGE HOSP

IS
V1 NUMBER(30):=500;
V2 VARCHAR2(20):='AJAY';
V3 DATE:=SYSDATE;
END;

SET SERVEROUTPUT ON
EXECUTE DBMS_OUTPUT.PUT_LINE(HOSP.V1);

HOW TO DROP PACKAGE?

1. DROP PACKAGE HOSP; ...DROP SPECIFICATION + BODY.
2. DROP PACKAGE BODY HOSP; ...DROP ONLY
----WE CAN NOT DELETE SPECIFICATION ONLY.

*** DYNAMIC SQL ***

CREATE OR REPLACE PROCEDURE T81
IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE T1';
END;

SHOW ERROR
SELECT * FROM T1;
SET SERVEROUTPUT ON
EXECUTE T81;

CREATE OR REPLACE PROCEDURE T8(TNO NUMBER)

IS
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE'||TNO;
DBMS_OUTPUT.PUT_LINE('TABLE DROPPED');
END;

SET SERVEROUTPUT ON
EXECUTE T8('&X1');

CREATE OR REPLACE PROCEDURE U1 (TNAME VARCHAR2)

IS
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM' ||TNAME;
DBMS_OUTPUT.PUT_LINE('DATA DELETED');
END;
EXECUTE U1('T5');

CREATE OR REPLACE PROCEDURE U2 (TNAME VARCHAR2)

IS
BEGIN
DELETE FROM TNAME;
DBMS_OUTPUT.PUT_LINE('DATA DELETED');
END;

SET SERVEROUTPUT ON
EXECUTE U2('T5');

SELECT * FROM T3;

1)public
--specification
create or replace package bank
is
procedure pr1;
function f1 return number;
v number(10):=500;
end;
--implimentation
create or replace package body bank
is
procedure pr1
is
begin
dbms_output.put_line('I am public procedure becoz mla specification madhye
declare kelay');
 end;
function f1 return number
is
begin
return v;
end;
end;

--pacakage call
set serveroutput on;
execute bank.pr1;

select bank.f1() from dual;

--or
set serveroutput on;
begin
dbms_output.put_line(bank.v);
end;

--2)private + public
--specification
create or replace package bankk
is
procedure pr1;
function f1 return number;
v number(10):=500;
end;
--implimentation
create or replace package body bankk
is
procedure pr2
is
begin
dbms_output.put_line('I am private procedure becoz mla specification madhye
declare kel nahi');
end;
function f1 return number
is
begin
return v;
end;
procedure pr1
is
begin
dbms_output.put_line('I am public procedure becoz mla specification madhye
declare kelay');
pr2;
end;
end;
/

--pacakage call
set serveroutput on;
execute bankk.pr1;

select bankk.f1() from dual;

--or
set serveroutput on;
begin
dbms_output.put_line(bankk.v);
end;

--3)overloading or polimorisom

create or replace package bank

is
function add1 (n1 number,n2 number) return number;
function add1 (n1 number,n2 number,n3 number) return number;
function add1 (n1 number,n2 number,n3 number,n4 number) return number;
end;

create or replace package body bank

is
function add1 (n1 number,n2 number) return number
is
begin
return n1+n2;
end;
function add1 (n1 number,n2 number,n3 number) return number
is
begin
return n1+n2+n3;
end;
function add1 (n1 number,n2 number,n3 number,n4 number) return number
is
begin
return n1+n2+n3+n4;
end;
end;
/

select bank.add1(1,2) from dual;

select bank.add1(1,2,3) from dual;
select bank.add1(1,2,3,4) from dual;

--4)body less package

create or replace package pi
is
v1 number(10):=3.14;
end;

set serveroutput on;

declare
r number(10):=5;
begin
dbms_output.put_line('Area of circle is :' || (pi.v1)*r*r);
end;
/

--
--specification + body drop hote
drop package package_name;
--only body drop hote
drop package body package_name;
--HW 1)create package for below operation
--1)accept details from user add into t1 table
--2)remove details from t1 table for given tno
--3)modify salary for eno=?
--4)display details of given dno=? from employee table
--5)calculate 20% bonus on salary for eno=?
--6)eno=? return joining day of employees

--specification
create or replace package package1
is
procedure p1 (v1 number,v2 varchar2,v3 number);
procedure p2 (v1 number);
procedure p3 (v1 number,v2 number);
procedure p4 (d_no in number,d out sys_refcursor);
function f1 (v1 number) return number;
function f2 (v1 number) return varchar2;
end;

--implimentation
create or replace package body package1
is
procedure p1 (v1 number,v2 varchar2,v3 number)
is
begin
insert into et values(v1,v2,v3);
dbms_output.put_line('Data inserted');
end;

procedure p2 (v1 number)

is
x number(10);
begin
select e_id into x from et where e_id = v1;
if x = v1 then
delete from et where e_id = v1;
dbms_output.put_line('Row deleted');
else
dbms_output.put_line('ID is not available');
end if;
exception
when no_data_found then
dbms_output.put_line('ID is not available');
end;

procedure p3 (v1 number,v2 number)

is
begin
update et set e_salary=v2 where e_id=v1;
dbms_output.put_line('modify salary');
end;

procedure p4 (d_no in number,d out sys_refcursor)

is
begin
open d for select * from employees where department_id=d_no;
end;
 function f1 (v1 number)
return number
is
sal number(10);
begin
select (salary+salary*0.20) into sal from employees where employee_id=v1;
return sal;
end;

function f2 (v1 number)

return varchar2
is
dy varchar2(100);
begin
select to_char(hire_date,'day') into dy from employees where employee_id=v1;
return dy;
end;
end;

--call
select * from et;

set serveroutput on;

execute package1.p1(&eid, '&name', &salary);

set serveroutput on;

execute package1.p2(&eid_remove);

set serveroutput on;

execute package1.p3(&eid,&sal_modify);

set serveroutput on;

declare
x SYS_REFCURSOR;
x1 employees%rowtype;
begin
package1.p4(&dno,x);
loop
fetch x into x1;
exit when x%notfound;
dbms_output.put_line(x1.employee_id ||' '|| x1.first_name);
end loop;
end;

select package1.f1(&eid) from dual;

select package1.f2(&eid) from dual;
--or
set serveroutput on;
declare
bonus number(10);
j_day varchar2(100);
begin
bonus:= package1.f1(&eid);
dbms_output.put_line('Bonus Salary is: ' || bonus);

j_day:=package1.f2(&eid);
dbms_output.put_line('joining day is: ' || j_day);
end;
--question
--HW 2)create package for below operation
--1)max no (n1,n2)
--2)max no (n1,n2,n3)
--3)max no (n1,n2,n3,n4)
create or replace package max_no
is
function f1 (n1 number,n2 number) return number;
function f2 (n1 number,n2 number,n3 number) return number;
function f3 (n1 number,n2 number,n3 number, n4 number) return number;
end;

create or replace package body max_no

is
function f1 (n1 number,n2 number)
return number
is
begin
if n1>n2 then
return n1;
else
return n2;
end if;
end;

function f2 (n1 number,n2 number,n3 number)

return number
is
begin
if n1>n2 and n1>n3 then
return n1;
elsif n2 > n1 and n2>n3 then
return n2;
else
return n3;
end if;
end;

function f3 (n1 number,n2 number,n3 number,n4 number)

return number
is
begin
if n1>n2 and n1>n3 and n1>n4 then
return n1;
elsif n2 > n1 and n2>n3 and n2>n4 then
return n2;
elsif n3 > n1 and n3>n2 and n3>n4 then
return n3;
else
return n4;
end if;
end;
end;

select max_no.f1(&n1,&n2) from dual;

select max_no.f2(&n1,&n2,&n3) from dual;
select max_no.f3(&n1,&n2,&n3,&n4) from dual;

--[Link] package
--[Link](e_no,ename)
--[Link](e_no,ename,e_sal)
--[Link](e_no,ename,hire_date)

create or replace package display1

is
procedure display(eno number,ename varchar2,d1 out sys_refcursor);

procedure display(eno number,ename varchar2,sal number,d1 out sys_refcursor);

procedure display(eno number,ename varchar2,hdate varchar2,d1 out sys_refcursor);

end;

--
create or replace package body display1
is
procedure display(eno number,ename varchar2,d1 out sys_refcursor)
is
begin
open d1 for select * from employees where employee_id=eno or
first_name=ename;
end;

procedure display(eno number,ename varchar2,sal number,d1 out sys_refcursor)

is
begin
open d1 for select * from employees where employee_id=eno or first_name=ename
or salary=sal;
end;

procedure display(eno number,ename varchar2,hdate varchar2,d1 out sys_refcursor)

is
begin
open d1 for select * from employees where employee_id=eno or first_name=ename
or hire_date=hdate;
end;

end;

--Call

set serveroutput on;

declare
v1 sys_refcursor;
v2 employees%rowtype;
begin
[Link](&eno,'&ename',v1);
loop
fetch v1 into v2;
exit when v1%notfound;
dbms_output.put_line(v2.first_name);
end loop;
end;
set serveroutput on;
declare
v1 sys_refcursor;
v2 employees%rowtype;
begin
[Link](&eno,'&ename',&sal,v1);
loop
fetch v1 into v2;
exit when v1%notfound;
dbms_output.put_line(v2.first_name||[Link]);
end loop;
end;

set serveroutput on;

declare
v1 sys_refcursor;
v2 employees%rowtype;
begin
[Link](&eno,'&ename','&hdate',v1);
loop
fetch v1 into v2;
exit when v1%notfound;
dbms_output.put_line(v2.first_name||[Link]||v2.hire_date);
end loop;
end;

--HW 4)create package for below operation

--1)pr1(employee no ?) display employees ,departments,jobs details
--2)pr2(region id ?) display employees ,departments details
--3)f1(employee no ?) return MAx salay+min salary from jobs table
--4)f2(department no ?) return 2nd max salary

create or replace package pf

is
procedure p1 (v1 number);
procedure p2 (v1 number);
function f1 (v1 number) return number;
function f2 (v1 number) return number;
end;

create or replace package body pf is

procedure p1(v1 number)
is

cursor c1 (v1 number) is

select e., d., j.*
from employees e
inner join departments d on (e.department_id = d.department_id)
inner join jobs j on (e.job_id = j.job_id)
where e.employee_id = v1;
x1 c1%rowtype;
begin
open c1(v1);
fetch c1 into x1;
dbms_output.put_line(x1.first_name || x1.department_name ||x1.job_id);
close c1;
end;
 procedure p2(v1 in number)
is
cursor c1 is
select e., d.
from regions r
inner join countries cc on (r.region_id = cc.region_id)
inner join locations l on (cc.country_id = l.country_id)
inner join departments d on (l.location_id = d.location_id)
inner join employees e on (d.department_id = e.department_id)
where cc.region_id = v1;
x c1%rowtype;
begin
open c1;
fetch c1 into x;
dbms_output.put_line(x.first_name || x.department_name);
close c1;
end;

function f1 (v1 number)

return number
is
x number (10);
begin
select j.max_salary + j.min_salary into x
from jobs j
inner join employees e on (j.job_id = e.job_id)
where e.employee_id = v1;
return x;
end;

function f2(v1 number)

return number
is
x number (10);
begin
select salary into x from employees e1
where 2 = (select count(distinct salary) from employees e2
where e1.department_id=v1 and [Link] >= [Link] and
e1.department_id = e2.department_id);
return x;
end;
end;

>>> CREATE A PROCEDURE TO TRUNCATE A GIVEN TABLE?