SQL (STRUCTURED QUERY LANGUAGE)

Sequel is also known as SQL (Structured query language), which is used to

communicate with the software known as Relational database
management system.

Database management system:

Information

Data

 DB is a place where we can store the data in a systematic manner.

 System is a software which provides security to the DB.

Operations:

Create/ Insert

Read/ Retrieve

Update/ Modif Data

base
Delete/ Drop

 These operations are called CRUD operations.

DBMS Software:

DBMS software is a software which is used to maintain and manage the

database.

i. The DBMS will provide 2 main features

a. Security
b. Authority
ii. DBMS will store data in either a file format or a table format.
iii. To interact with DBMS we have to use either SQL or No SQL
a. Table: In table format we use SQL which is used to work on
structured data.
b. File: In file format we use No SQL which is used to work on
Non-structured data and the data is stored in JSON format.
 iv. Basically there are 5 types of DBMS software:
a. Network DBMS (File Format)
b. Hierarchical DBMS ( File Format )
c. Object Oriented DBMS ( File Format )
d. Non-Relational DBMS ( File Format )
e. Relational DBMS ( Table Format )

Relational Database management system:

+SECURITY

+AUTHORITY

C

R
h
U

D TABL
E

 RDBMS is a type of DBMS software which is used to manage and

maintain the DB, by storing the data in the form of table format.
 To interact with RDBMS, we need to use SQL.

History of SQL:

 SQL was invented in the year of 1979 by IBM.

 In 1981 sequel was renamed to SQL (oracle).
 The father of SQL is F. Boyce and D. Chamberlin (Data Analyst in
IBM).
 The full form of sequel is simple English query language.

Table v/s File:

TABLE FILE
Structured Non Structured
Operations take less time Take more time
Storage is less Storage is more
SQL is used No SQL is used
Vertical scaling Horizontal and vertical scaling
ACID PROPERITES CAP THEOREM
Schema based Semi schema based

Format of a table:
  Row
 Record
 Tuple

Column Attribute Fields

i. Table is the logical arrangement of rows, columns and cells.
ii. Column are vertical arrangement of line in a table, alternative
name for column is attribute.
iii. Rows are horizontal arrangement of line in the table, alternative
names are record and tuples
iv. Cells are the small area of a table where data is stored.
v. Number of cell in a table depends on the number of rows and
columns in the table.
e.g. 3x3 = 3 rows x 3 columns = 9 cells

Schema validation in a table:

Column validation is the method for checking the accuracy and quality of
the data.

The validation of column can be done by assigning datatypes and by

assigning constraints (optional)

There are 5 datatypes in SQL:

a. CHAR()
b. VARCHAR() / VARCHAR2()
c. DATE
d. NUMBER
e. LARGE OBJECT
i. CHAR Large Object.
ii. BINARY Large Object.
1. CHAR():
 It allows Uppercase, Lowercase, alphanumeric, special character,
including space (no character) and number (0-9).
Syntax: CHAR (size)
 The minimum size can be allowed for char datatype is 2000.
 This datatype follow fixed length of memory allocation.

COMMANDS
The statements which helps us to perform CRUD operations on relational
database management system.

Basically there are 5 statement in SQL

i. DDL(Data definition language)

ii. DML(Data manipulation language)
iii. DCL(Data control language)
iv. TCL(Transaction control language)
v. DQL(Data Query language)

1. Data Definition language:

These statements are used to create/ modify/ destroy the data base
objects.

Data base objects = Tables, Index, View, Trigger, Stored Procedure, [user
defined functions]

There are 5 sub commands in DDL:

a. Create
b. Rename
c. Truncate
d. Drop(flashback, purge)
e. Alter ( 7 operations)
 Create table: It is used to create DB Objects
Syntax:
CREATE TABLE TABLE_NAME
(
COL_NAME1 DATATYPE [CONSTRAINT],
COL_NAME2 DATATYPE [CONSTRAINT],
-------------------------------------------------- ,
[CONSTRAINT REF_NAME PRIMARY KEY (COL_NAME)],
[CONSTRAINT REF_NAME FOREIGN KEY (COL_NAME)] REFERENCES
PARENT_TABLE_NAME(COL_NAME)]
);

Ques> WRITE THE SQL QUERY TO CREATE STUDENT TABLE WITH

ANY 5 COLUMNS (NO PK, FK)
TABLE NAMING:

1. Table name shouldn’t start with any number/ special character.

2. Multiple table should not have same name.
3. In same table we should not have same multiple column.
4. Only one primary key should be allowed in a table.
5. The ref_name which we are using that should not be used for
another table.

Primary key example:

Que> WAQT create the flight table with any 5 columns and make any
column as primary key?

Ans>

 To check which column is primary key

QUES> WAQT create trainers table with any 6 columns and make any one
column as pk?
Ans>

QUES>
QUES3>

 RENAME

It is used to change the existing database object name

Syntax:

RENAME OLD_TABLE_NAME TO NEW_TABLE_NAME;

  It is also used for renaming the column name.

 Truncate
It is used to empty the table.

Syntax:

TRUNCATE TABLE TABLE_NAME;

 Truncated data cannot be restored.

 DROP

It is used to delete the table from the database to recyclebin.

Syntax:

DROP TABLE TABLE_NAME;

o FLASHBACK

From the recyclebin if we want to get the table back to database we can
use flashback command

Syntax:

FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;

o PURGE

If we want to delete the table from recycle bin we need to use purge
operation

Syntax:

PURGE TABLE TABLE_NAME;

**NOTE:

To check the all the table from recyclebin:

SELECT * FROM RECYCLEBIN;

**NOTE:

If we want to drop parent table we need to delete the connection first

(foreign key) than we can drop the parent table.
  ALTER:

It is used to modify the structure of the table.

1. Delete column
ALTER TABLE TABLE_NAME
DROP COLUMN COLUMN_NAME;
2. Add column
ALTER TABLE TABLE_NAME
ADD COLUMN_NAME DATATYPE [CONSTRAINTS];
3. Rename column
ALTER TABLE TABLE_NAME
RENAME COLUMN OLD_NAME TO NEW_NAME;
 4. Change datatype
ALTER TABLE TABLE_NAME
MODIFY COL_NAME NEW_DATATYPE;

5. Drop constraint
ALTER TABLE TABLE_NAME
DROP CONSTRAINT REF_NAME;

6. Make column as optional or mandatory

ALTER TABLE TABLE_NAME
MODIFY COL_NAME EXISTING_DATATYPE NULL/NOT NULLL;

7. Make PK/FK
For PK
ALTER TABLE TABLE_NAME
ADD CONSTRAINT REF_NAME PRIMARY KEY(COL_NAME);
To delete pk
ALTER TABLE TABLE_NAME
DROP CONSTRAINT REF_NAME;

For FK
ALTER TABLE TABLE_NAME
ADD CONSTRAINT REF_NAME FOREIGN KEY(COL_NAME)
REFERENCES PARENT_TAB_NAME(COL_NAME);
 Most of the alter command will support for empty
tables.

NOTE:
  Add column: while adding column to the table if table is
empty we can add by using NOT NULL/ NULL constraints
If table consists of data its not possible to add column
by using NOT NULL.
 Change datatype: table which are empty allow changing the
data type
Otherwise we can only increase the size of the datatype

2. DATA MANIPULATION LANGUAGE (DML):

It is used to INSERT/DELETE/ UPDATE the data from the table.

These statement are also known as non autocommit statement and

also known as transaction statements.

a. DELETE: use to delete the record from the table

Syntax:
DELETE FROM TABLE_NAME
[WHERE CONDITION];
b. UPDATE: use to change the data from the table
Syntax:
UPDTAE TABLE_NAME
SET COL_NAME1 = V1, COL_NAME2 =V2
[WHERE CONDITION];
c. INSERT: use to add the records to the table
There are 3 syntax for INSERT:
I. INSERT INTO TABLE_NAME VALUES(V1, V2, V3…..Vn);
II. INSERT INTO TABLE NAME VALUES(&COL_NAME1,
&COL_NAME2,……,&COL_NAMEn);
 HERE WE CAN GIVE /(FORWARD SLASH) FOR LOOPING
III. INSERT ALL
INTO TABLE_NAME1 (COL_NAME1, COL_NAME2…….,
COL_NAMEn) VALUES(V1, V2, V2……, V3),
INTO TABLE_NAME2 (COL_NAME1, COL_NAME2…….,
COL_NAMEn) VALUES(V1, V2, V2……, V3),
SELECT * FROM DUAL;

** NOTE : We have to commit to save our changes otherwise they are

going to rollback.

3. TRANSACTION CONTROL LANGUAGE:

It is used to control the transaction in SQL.

 The transactions are

i. DELETE(WITHDRAWL)
ii. UPDATE
iii. INSERT(DEPOSIT)

There are basically 3 commands in TCL

a. COMMIT: used to save the transaction into the database.

Syntax: COMMIT;
b. ROLLBACK: used to get back the transaction before commit.
Syntax: ROLLBACK;
c. SAVEPOINTS/ CHECK POINTS/ LEVELS: used to control the
transactions in level wise before commit.
Syntax: SAVEPOINT SAVEPOINT_NAME;
Get back the transactions with levels syntax
ROLLBACK TO SAVEPOINT_NAME;

4. DATA CONTROL LANGUAGE:

It is used to control the data flow between the users

There are 2 commands in DCL:

i. GRANT: used to give access to other users.

Syntax:
GRANT SQL_COMMANDS
ON TABLE_NAME
TO USER_NAME;
ii. REVOKE: use to get back the access from the users.
Syntax:
REVOKE SQL_COMMANDS
ON TABLE_NAME
TO USER_NAME;

To check the privileges:

SELECT * FROM USER_TAB_PRIVS;

5. DATA QUERY LANGUAGE (DQL):

It is used to read or retrieve data from a table/ database.

(Fetch/ Search)
In DQL there are 4 subcommands:

a. Projection
b. Selection
c. Joins
d. Select
1. Projection: it is the process of fetching data from a table only by
selecting the columns.
Syntax:

SELECT * / [DISTINCT] COL_NAME / EXPRESSION

FROM [ALIAS].
TABLE_NAME;
KEY WORDS PARAMETERS

QUES> WAQ to display all the employees name which are present
in the employee table.

**NOTE: (,) column separator help to pass multiple column in select

clause.
(*) used to get all the column from the table.
 While using * it is difficult use any other columns/
expressions.

To use * with columns we have different procedure:

SELECT TABLE_NAME.*, COL_NAME/ EXP
FROM TABLE_NAME;
Here we are generating a new column with our table which is annual
salary:

2. Selection: It is the process of fetching data from table by selecting

both columns and rows.

Syntax:
SELECT * / [DISTINCT] COL_NAME / EXP [ALIAS]
FROM TABLE_NAME
WHERE FILTER CONDITION;

PROCESS:
a. From will executes first
b. Then where clause will executes 2nd.
c. Where will executes row by row.
d. Where clause will executes based on filter condition
If condition true it will select the records else reject
e. The where clause o/p will be in Boolean value (True/False)
f. Finally select will executes and give the result.

Filter condition format:

COL_NAME/ EXP OPERATOR VALUES.
 **Note:
 Where clause is used to filter the records based on
condition it’s also known as filtration process.
 String/ date kind of data in where clause we need to use
single quotes.
 And data is case sensitive.
 While using a date data in where clause we need to follow
only the two formats: ‘DD-MMM-YY’

FLOW OF EXECUTION:
3SELECT ENAME3

1FROM EMP 1

2WHERE DNO = 20;

2
Ename Dno JOB
Smith 10 Clerk
Scott 20 Manager
King 30 Analyst D
Allen 10 Clerk
Miller 20 analyst

3. Joins: it is the process of fetching the data from multiple tables

simultaneously.
4. Select: It is a keyword which is used to display the data, which we
are fetching by projection, selection and joins.

Que: WAQP to display all the salary of employees whose salary

are more than 1000 and less than 3000 ?
SELECT SAL FROM EMP

WHERE SAL>3000 OR SAL<1000;

Que: WAQP to display the details of Smith, King, Allen?

Que: WAQP to display all the details of the employees, the

employee who hired on “17-dec-1980” and “17-nov-1981” ?

Que: WAQP to display all the details of the employees working as

a manager in department number 10 and 20?

Que: WAQP to display all the details of the employees who hired
after 1980 as a manager and a salesman and earning salary less
than 6000 in department number 30 ?
 SELECT * FROM EMP WHERE HIREDATE > ‘01-JAN-1981’ AND ( JOB
=’MANAGER’ OR JOB=’SALESMAN’) AND SAL<6000 AND
DEPTNO=30;

Que: WAQP to display all the details of the employees including

annual salary, 10% increment in the annual salary, 6% decrement
in the halfterm salary, 3% decrement in the quarter term salary,
for the employees the employees who working in department
10,20,30 as a manager and earning salary more than 800 and less
than 6000 and hired before 1988 (use the alias name for all the
expressions)

Expressions in SQL:

The statements which gives the result are known as expressions or the
statement which consists of both operands and operators.

Ex. 2 + 3 = 5

Operands Operator = RESULT

Instead of operands if we are using any columns those types of

statements are known as expressions in SQL.

Ex. SAL * 12 = Annual salary

Ques> WAQ to display Ename, Salary and annual salary of all

the employees.
ALIAS:

It is an alternative name which we can use for any columns /

expressions/ tables for result purpose.

Syntax:

SELECT COL_NAME “ALIAS_NAME”, COL_NAME2 “ALIAS_NAME”…………

FROM TABLE_NAME;

OR

SELECT COL_NAME AS ALIAS_NAME

FROM TABLE_NAME;

DISTINCT:

It is a keyword, which is used to remove repetitive or duplicated

records from the result table.

Syntax:

SELECT DISTINCT COL_NAME1, COL_NAME2

FROM TABLE_NAME;

**NOTE:

1. We can use distinct only once in each select clause and with
multiple column it will give distinct element from the combined
columns.
2. If we are using the distinct it should be the first argument for
select, which means it should come before the column name.
EX.
  It give unique records not unique values

SPECIAL OPERATORS

The operators which are used to perform the special types of tasks in SQL.

There are 4 set of special operators:

a. IN VS NOT IN
b. IS VS IS NOT
c. LIKE VS NOT LIKE
d. BETWEEN VS NOT BETWEEEN

These all the operators are not mandatory, we can also solve the query by
using n number of different ways

1. IN VS NOT IN

These are the special operators which are going to allow multiple values
at the RHS part to make the comparison.

Syntax:

WHERE COL_NAME/EXP IN/ NOT IN (V1, V2, V3…………….,Vn);

Note: These will work for only comparison (=, !=);

2. IS VS IS NOT

These operator are used to compare the NULL values.

Syntax:

WHERE COL_NAME/EXP IS/ IS NOT NULL;

**Note: Null means empty and Zero is a values (0).

3. LIKE VS NOT LIKE

These are used to perform pattern matching operations

Syntax:

WHERE COL_NAME/EXP LIKE/NOT LIKE ‘PATTERN’;

**Note:

In between LIKE/ NOT LIKE it’s not possible to use IN/NOT IN.

‘%’ = allows any type of character

And supports N number of digits.

‘_’ = allows any type of character

 And supports only 1 digit.

FUNCTIONS: The set of information which help us to perform the specific

task is known as functions.

 In general, there are two types of functions:

 User-defined functions
 The functions which are created by the users based on
requirements are knows as user-defined functions.
 In-built functions:
 The functions which are pre-defined in the software are known
as In-built functions or pre-defined functions.

User-defined functions In built functions

We can easily do the We cannot do the
modifications modifications
Only single user can access All the users can access
Also known as dynamic Also known as static
functions functions
It is more secured functions It is less secured as
compared to user defined
functions

In-built functions:

 Single Row Functions.

 The function in which the number of inputs is equals to the
number of outputs are known as single row functions.
 The single row function will execute row by row.
 For example: LENGTH(), UPPER(), SUBSTR()… etc.

 Multi Row Functions.

 The functions in which gives only the one output for the multiple
inputs are known are multi row functions.
 The multi row functions will execute group by group.
 In multi row functions after the aggregation process the
execution process will take place, hence these functions are also
known as aggregate functions.
 For example: MAX(), MIN(), AVG(), SUM(), COUNT()

Rules Of Multi Row Functions:

1. Syntax: MRF_NAME(COL_NAME/EXPRESSION)
MAX(SAL/SAL*12)
 MIN(SAL/SAL*12)

2. All the multi row functions will allow only column / expression as
argument.

# WAQD the maximum salary from the employees:

SELECT MAX(SAL) FROM EMP;

# WAQD the maximum salary and maximum comm from

the employees

 SELECT MAX(SAL,COMM) FROM EMP;

 SELECT MAX(SAL), MAX(COMM) FROM EMP;

3. In Select clause we can use n number of multi row functions.

 SELECT MAX(SAL), MIN(SAL), AVG(SAL), SUM(SAL),

COUNT(SAL), MAX(HIREDATE), MIN(COMM) FROM EMP;

4. In Where clause it is not possible to use any multi row functions.

#WQTD the maximum salary using where clause

 SELECT * FROM EMP WHERE SAL = MAX(SAL);

5. In Select Clause along with multi row functions it is difficult to use

the columns / expression, we can use if we are using group clause
 SELECT COUNT(EMP NO), DEPTNO FROM EMP GROUP BY
DEPTNO;

6. Multi row functions ignores null values.

7. Among all the multi row functions only the count function will allow
the * as a arguement
#WAQD the maximum salary and maximum hiredate and the number of
employees present in the emp table.

 SELECT MAX(SAL), MAX(HIREDATE), COUNT(*) FROM EMP;

#WAQD the total salary and number of employees who are working as a
manager

 SELECT COUNT(*), SUM(SAL) FROM EMP WHERE JOB = ‘MANAGER’;

#WAQD the number of employees working in department number 10 and 30

  SELECT COUNT(*) FROM EMP WHERE DEPTNO IN (10,30);

#WAQD the number of employees are working in each department for the
department 10 and 30

 SELECT COUNT(*), DEPTNO FROM EMP WHERE DEPTNO IN (10,30) GROUP BY

DEPT NO;
 SELECT COUNT(*), DEPTNO FROM EMP WHERE DEPTNO IN (10,30) GROUP BY
ROLLUP (DEPT NO);

#WAQD TO DISPLAY ALL THE DETAILS OF THE EMPLOYEES, THE EMPLOYEE

WHOSE SALARY ARE MORE THAN 2000

 SELECT * FROM EMP WHERE SAL>2000;

 SUB QUERY

It is a query which is written inside a another query is known

as sub query.

1. The inner query will execute first.

2. The inner query will give one result ( this is not a
final result)
3. The output of inner query will be given as input for
outer query.
4. Final the outer query will execute and it will give
the final result.
5. In the sub query always the outer query will
depends on inner query.
6. We can able to nest total 225 inner queries

 Why we have to use sub query?

 If there is any unknown value present in the question, to find
the unknown values we have to write a sub query
 CASE 1:
 The data to be selected and the condition to be executed
both are from same table ( outer query and inner query are
from same table)
 CASE 2
 The data to be selected and the condition to be executed
both are from different table ( outer query and inner query
are from different table)
 EMP

ENAME SAL DN
O
SMITH 1000 10
SCOTT 2000 20
KING 3000 30
ADAMS 5000 10
MILLER 7000 20

 DEPT

DN DNAME LOC
O
10 Developer Pune
20 Testing Delhi
30 Sql Hyd

#WAQTD ALL THE EMPLOYEES NAME THE EMPLOYEES WHOSE SALARY LESS
THAN KING

 SELECT ENAME FROM EMP WHERE SAL<

(SELECT SAL FROM EMP WHERE ENAME = ‘KING’);

#WAQTD ALL THE EMPLOYEES NAME THE EMPLOYEES WHO ARE WORKING IN
SQL DEPARTMENT

 SELECT ENAME FROM EMP DNO =

(SELECT DNO FROM DEPT WHERE DNAME = ‘SQL’);

#WAQTD ALL THE EMPLOYEES THE WHO EMPLOYEES WHO ARE WORKING IN
THE SAME JOB OF SMITH

 SELECT * FROM EMP WHERE JOB =

(SELECT JOB FROM EMP WHERE ENMAE = ‘SMITH’);

#WAQTD THE DEPARTMENT NAME OF SCOTT

 SELECT DNAME FROM DEPT WHERE DEPTNO =

( SELECT DEPTNO FROM EMP WHERE ENAME =
'SCOTT');

ALL THE DETAILS OF THE EMPLOYEES THE EMPLOYEE WHO HIRED AFTER THE
SMITH

 SELECT * FROM EMP WHERE HIREDATE>

(SELECT HIREDATE FROM EMP WHERE ENAME =
‘SMITH’);

#WAQTD ALL THE DETAILS O OF THE EMPLOYEES THE EMPLOYEE WHO ARE
WORKING AS A MANAGER AND EARNING SALARY MORE THAN BLAKE

 SELECT * FROM EMP WHERE JOB = 'MANAGER' AND SAL >

(SELECT SAL FROM EMP WHERE ENAME = 'BLAKE');

#WAQTD ALL THE DETAILS O OF THE EMPLOYEES THE EMPLOYEE WHO ARE
WORKING IN THE SAME JOB OF BLAKE AND EARNING SALARY LESS THAN KING

 SELECT * FROM EMP WHERE JOB =

(SELECT JOB FROM EMP WHERE ENAME = 'BLAKE')
AND
SAL <
(SELECT SAL FROM EMP WHERE ENAME = ‘KING’);
#WAQTD THE DETAILS O OF THE EMPLOYEES THE EMPLOYEE WHO ARE
WORKING IN NEW YORK LOCATION

 SELECT * FROM EMP WHERE DEPTNO =

( SELECT DEPTNO FROM DEPT WHERE LOC = 'NEW
YORK');

#WAQTD ALL THE DETAILS OF THE EMPLOYEE THE EMPLOYEE WHOSE SALARY
ARE LESS THAN KING IN SALES DEPARTMENT

 SELECT * FROM EMP WHERE SAL < (SELECT SAL FROM EMP WHERE ENAME =
'KING') AND DEPTNO = (SELECT DEPTNO FROM DEPT WHERE DNAME =
'SALES');

#WAQTD DEPARTMENT NAME OF THE EMPLOYEES THE EMPLOYEES WHOSE

NAME ENDS WITH ‘H’

 SELECT DNAME FROM DEPT WHERE DEPTNO =

( SELECT DEPTNO FROM EMP WHERE ENAME LIKE
'%H');

#WAQTD DEPARTMENT NAME OF THE EMPLOYEES THE EMPLOYEES WHOSE

NAME ‘R’

 SELECT DNAME FROM DEPT WHERE DEPTNO IN

( SELECT DEPTNO FROM EMP WHERE ENAME LIKE
'%H');

#WAQD TO DISPLAY ALL THE DETAILS OF THE EMPLOYEES, THE EMPLOYEE

WHOSE SALARY ARE MORE THAN ALLEN

 SELECT * FROM EMP WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME =
'ALLEN');

#WAQD ALL THE DETAILS OF THE EMPLOYEES WHO ARE WORKING IN THE SAME
DEPARTMENT OF SMITH

 SELECT * FROM EMP WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE
ENAME = 'SMITH');

Join:
 1. Cartesian Join / Cross --------ERROR RECORDS------- NO USE
2. Inner Join -------Matching Records------ Useful
(FK Drawback of inner join)
3. Natural Join (Cartesian & Inner Join)

Natural join will act as Cartesian Join

If there is no connection between all the between tables, If we use a
natural joins in this case the natural join will give a result as a
cartesian join.

Natural join will act as Inner Join

If there is a connection between all the multiple tables, in this case
the natural join will give a result table as a inner join

Syntax: SELECT COL_NAME / EXP * FROM TABLE_NAME1 NATURAL

JOIN TABLE_NAME2 NATURAL JOIN TABLE_NAMEn [WHERE FILTER
CONDITION];

Note:

 Accessing foreign key will become easy

 Join Condition is not required in order to get the matching
records.
 If we don’t know whether tables are connected with each
other then we can easily use natural join.

#WAQTD THE EMPLOYEE NAME AND LOCATION THE EMPLOYEE

WHO ARE WORKING IN DEPARTMENT NUMBER 30

#INNER JOIN
#NATURAL JOIN

#WAQTD EMPLOYEE NAME AND SALARY AND DEPARTMENT NAME

OF ALL THE EMPLOYEES THE EMPLOYEE WHO ARE WORKING IN
NEW YORK LOCATION AND EARNING SALARY MORE THAN SMITH

SELF JOIN: Joining same table as itself is known as self join

Syntax:
SELECT COL_NAME / EXP / *

FROM TAB_NAME1 ALIAS_NAME , TAB_NAME2 ALIAS_NAME , TAB_NAMEn

WHERE TAB_NAME1.COL_NAME IN TAB_NAME2.CO_NAME AND FILTER

CONDITION

#WAQTD ALL THE EMPLOYEE DETAILS AND THERE MANAGER

DETAIL
#WAQTD EMPLOYEE NAME, SALARY AND EMPLOYEE HIREDATE -----
MANAGER NAME , MANAGER’S SALARY AND MANAGER HIREDATE
--- FOR ALL THE EMPLOYEES.

 SELECT E1.ENAME “ENAME”, E1.SAL “ESAL”, E1.HIREDATE “EHD”,

E2.ENAME “MNAME”, E2.SAL “MSAL”, E2.HIREDATE “MHD” FROM
EMP E1 , EMP E2 WHERE E1.MGR IN E2.EMP

#WAQTD EMPLOYEE NAME AND JOB , MANAGER NAME AND JOB IF

EMPLOYEES ARE WORKING AS CLERK.

#WAQTD THE SMITH MANAGER NAME

  SELECT ENAME AS MNAME FROM EMP WHERE EMPNO = (SELECT
MGR FROM EMP WHERE ENAME = 'SMITH');

#WAQTD EMPLOYEE NAME , MANAGER NAME AND MANAGER’S

MANAGER NAME IF EMPLOYEE SALARY IS LESS THAN MANAGER
MANAGER’S SALARY

***REPLACE, SUBSTRING, INSTRING***

CREATE TABLE PLACE( PNAME VARCHAR(20));

INSERT INTO VALUES(‘KARNATAKA’);

INSERT INTO VALUES(‘PUNJAB);

INSERT INTO VALUES(‘BENGALURU);

#WAQT REPLACE THE 2ND ‘A’ OF KARNATAKA WITH ‘Z’ ?

  SELECT

PNAME,

SUBSTR(PNAME, 1, INSTR('KARNATAKA', 'A', 1, 2) - 1) ||

'Z' || SUBSTR(PNAME, INSTR('KARNATAKA', 'A', 1, 2) + 1) FROM PLACE

WHERE PNAME = 'KARNATAKA'

#WAQT FETCHT THE MIDDLE NAME FROM STRING VALUES PRESENT IN

KKT TABLE?

 SELECT SUBSTR( SUBSTR(PLAYER_NAME,1,INSTR(PLAYER_NAME, ' ',1,2)),

INSTR( SUBSTR(PLAYER_NAME,1,INSTR(PLAYER_NAME, ' ',1,2)), ' ', 1,1))
FROM KKT;

#WAQT TO REPACE THE 2ND ‘9’ PRESENT IN THE NUMBER WITH ‘0’ IN
MOBILE TABLE;

 SELECT MNO, SUBSTR(MNO,1,INSTR(MNO,'9',1,2) - 1) ||'0'|| SUBSTR(MNO,

INSTR(MNO,'9',1,2) + 1) FROM MOBILE;