UNIT 2: Advanced SQL

2.1 Data Types:

CHAR(size) Fixed-length character data, size characters long.
Maximum size=255; default=1 byte. Padded on right with
blanks to full length of size.
DATE Valid dates range from Jan 1, 4712 B.C. to Dec 31, 4712
A.D.
NUMBER For NUMBER column with space for 40 digits, plus space
for a decimal point and sign. Numbers may be expressed
in two ways: first, with numbers 0 to 9, the signs + and -,
and a decimal point(.); second, in scientific notation, e.g.
1.85E3 for 1850. Valid values are 0 and positive and
negative numbers from 1.0E-130 to 9.99…E125.

VARCHAR:
The Varchar data type is used to store the character values. It is a variable-length data
type i.e. we can change the size of the character at the time of the execution. Hence, it
is also called a Dynamic datatype.
It is used to store normal characters and alphanumeric characters too. The Varchar
data type can store a character string of a maximum length of 4000 bytes. Also, for
every one character, one byte is stored in the memory. VARCHAR is an ANSI
Standard that is used to distinguish between Null and Empty Strings.
VARCHAR2 is the same as VARCHAR in the oracle database. The main difference
is that VARCHAR is ANSI Standard and VARCHAR2 is Oracle standard.
The VARCHAR2 data type specifies a variable-length character string in the database
character set.
LONG:
LONG is an Oracle data type for storing character data of variable length up to 2
Gigabytes in length (bigger version of the VARCHAR2 datatype). Note that a table can
only have one LONG column.
CLOB and NCLOB:
o Oracle Database provides large object (LOB) data types that can be used to store
images, sound, video, PDFs, and large amounts of text.
o The CLOB (Character Large Object) and NCLOB (National Character Large
Object) types can store character data. These data types are commonly used to
store large text and XML files. The primary difference between these types is
that the CLOB type uses 1 byte per character to store characters in the ASCII
 character set while the NCLOB type uses 2 or 3 bytes per character to store
characters in the Unicode character set.
In Oracle, CLOB data type stores variable-length character data (character large object)
in the database character set that can be single-byte or multibyte (supports more than
4 GB).
CREATE TABLE countries
(
name VARCHAR2(90),
notes CLOB
);
-- Insert some data
INSERT INTO countries VALUES ('Greece', 'Greece is a country in south-east Europe. Athens is
the capital...');
# 1 row created.

NCLOB:
An SQL NCLOB is a built-in type that stores a Character Large Object using the
National Character Set as a column value in a row of a database table.
RAW and LONGRAW
RAW and LONG RAW hold bytes that are not interpreted as characters, for example,
pixel values in a bit-mapped graphics image. Character data can be transformed when
passed through a gateway between networks. For example, character data passed
between machines using different languages (where single characters may be
represented by differing numbers of bytes) can be significantly changed in length. Raw
data is never converted in this way.
RAW is a data type used to store binary data, or data which is byte oriented (for
example, graphics or audio files). One of the most important things to note about RAW
data is that it can only be queried or inserted; RAW data cannot be manipulated. RAW
data is always returned as a hexadecimal character value. In SQL, its maximum size is
2000 bytes.
LONG RAW is an Oracle data type for storing binary data of variable length up to 2
Gigabytes in length. Note that a table can only have one LONG RAW column.
2.2 ROWID Pseudocolumn & Dual table:
ROWID Pseudocolumn
Pseudocolumn: A pseudo-column behaves like a table column but is not actually
stored in the table. You can select from pseudo-columns, but you cannot insert,
update, or delete their values.
For each row in the database, the ROWID pseudocolumn returns the address of the row.
Oracle Database rowid values contain information necessary to locate a row:
• The data block in the data file in which the row resides
• The position of the row in the data block.
Usually, a rowid value uniquely identifies a row in the database. Rowid values have
several important uses:
• They are the fastest way to access a single row.
• They can show you how the rows in a table are stored.
• They are unique identifiers for rows in a table.
You should not use ROWID as the primary key of a table. If you delete and reinsert a
row with the Import and Export utilities, for example, then its rowid may change. If you
delete a row, then Oracle may reassign its rowid to a new row inserted later.
Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause
of a query, these pseudocolumn values are not actually stored in the database. You
cannot insert, update, or delete a value of the ROWID pseudocolumn.
Example
This statement selects the address of all rows that contain data for employees in
department 20:
SELECT ROWID, last_name
FROM employees
WHERE department_id = 20;
DUAL table:
The DUAL is special one row; one column table present by default in all Oracle
databases. The owner of DUAL is SYS but DUAL can be accessed by every user. The
table has a single
the following command displays the structure of DUAL table:
DESC DUAL;
Output:
Name Type
--------------------------- ------
DUMMY VARCHAR2(1)
The following command displays the content of the DUAL table:
SELECT * FROM DUAL;
Output:
DUMMY
----------
X
An example of using the DUAL table would be:
SELECT SYSDATE FROM DUAL;
This would return the system’s current date to your screen.
SYSDATE
03/JUL/22

Selecting from the DUAL table is useful for computing a constant expression with the
SELECT statement
Oracle Query:
SELECT 2+2 FROM DUAL;
Output:
2+2 = 4
2.3 Date function:
➢ SYSDATE
This function returns the current date of the Operating system in which the Oracle
database is installed.
Syntax:
SYSDATE
Example:
SELECT sysdate from dual;
➢ SYSTIMESTAMP
The SYSTIMESTAMP function returns the date and time of the database.
The important part to remember is that it returns the time of the database, not your local
time.
So, if you’re accessing a database in a different time zone, it will return the time and
timezone of the place where the database is stored, not your local time zone.
For example, if your database is in London but you are in New York, the
SYSTIMESTAMP will return a time that is 5 hours ahead of your time.
It includes the date, time, and fractional seconds.
SELECT SYSTIMESTAMP FROM dual;
Result:
10/SEP-/22 08:57:17.067000000 AM +10:00

➢ TO_CHAR (date conversion)

It is used to convert a date from DATE value to a specified date format.
Syntax:
TO_CHAR(expression, date_format)
Parameters:
• expression: It refers to the DATE or an INTERVAL value which needs to be
converted. The expression can be of type DATE OR TIMESTAMP
• date_format: It refers to the specified format in which we are going to convert
the expression. It is optional parameter.
Example:
In this example we are going to convert the system date or current date into a string
value in a format DD-MM-YYYY.
Code:
SELECT TO_CHAR( sysdate, 'MM-DD-YYYY' )NEW_DATE FROM dual;
OUTPUT:
NEW_DATE
12-23-2022
➢ LAST_DAY
The LAST_DAY function returns the last day of the month of the specified date value.
The syntax is:
LAST_DAY (input_date)
Let’s see an example:
SELECT
SYSDATE, LAST_DAY(SYSDATE) AS last_of_month
FROM dual;
SYSDATE LAST_OF_MONTH

23-DEC-22 31-DEC-22

➢ NEXT_DAY
The NEXT_DAY function returns the date of the next specified weekday that comes
after the specified value.
It takes two parameters:
NEXT_DAY (input_date, weekday)
The input date is the date to start from, and the weekday is the name of the day you’re
looking for.
For example, running SYSDATE on today’s date will show 15 Sep 2022, and it’s a
Tuesday.
To find the next Thursday that appears after today, use this function:
SELECT
NEXT_DAY (SYSDATE, 'TUESDAY')
FROM dual;
Result:
15/SEP/22
➢ MONTHS_BETWEEN
The MONTHS_BETWEEN function allows you to find the number of months between
two specified dates.
You specify two dates as parameters, and a number is returned:
MONTHS_BETWEEN (date1, date2)
Date1 is usually the later date.
If date1 is greater than date2, the value is positive. Otherwise, it is negative.
The returned value can be a whole or a decimal number. So, it returns a partial month
value.
Let’s see an example:
SELECT
MONTHS_BETWEEN ('12-MAY-2022', '10-FEB-2022')
FROM dual;
Result:
3.064516129
➢ ADD_MONTHS
The ADD_MONTHS function allows you to input a date value, and a number of
months, and return input date value plus the number of months you supply.

another date value. The value returned is the input date value plus the number of months
you supply.
So, if you start with Jan 10th 2022, and add 3 months, the function will return Apr 10th,
2022.
The syntax is:
ADD_MONTHS (input_date, number_of_months)
An example of this function is:
SELECT SYSDATE,
ADD_MONTHS(SYSDATE, 5) AS new_date
FROM dual;
SYSDATE NEW_DATE
10/SEP/22 10/FEB/23
 ➢ ROUND
The ROUND function allows you to round a date value to a format you specify.
This function is often used with numbers, but can also be used with dates.
If you use it with a date value, you can specify a DATE or TIMESTAMP value. You
can specify any format mask, but the default is the nearest day, and is returned as a
DATE.
The syntax is:
ROUND (input_date, round_to)
If I wanted to round a date to the nearest month, I would use something like this:
SELECT
ROUND(SYSDATE, 'MM')
FROM dual;
Result:
01/SEP/22
This shows a value of 1 Sep because the specified date (SYSDATE, or 10 Sep) has
been rounded forwards to this date.
➢ TO_DATE
Converts a character field to a date field.
Syntax:
TO_DATE(char[,fmt])
Example:
SELECT
TO_DATE('15-12-2022','DD/MM/YY')"DATE"
FROM dual;
Output:

DATE
15-DEC-22

➢ TRUNC
The TRUNC function, like the round function, works with numbers as well as dates.
It truncates or removes a part of the date to the format you specify.
The syntax is:
TRUNC (input_date, format_mask)
If you don’t specify a format mask, then the function will truncate the value to the
nearest day. This is helpful if you want to remove the time part of a date value.
For example, to show only the date part of today’s date:
SELECT
TRUNC(SYSDATE)
FROM dual;
Result:
10/SEP/22
This shows only the date part of today.
2.4 Concepts of Index
Indexes are special lookup tables that the database search engine can use to speed up
data retrieval. Simply put, an index is a pointer to data in a table. An index in a database
is very similar to an index in the back of a book.
For example, if you want to reference all pages in a book that discusses a certain topic,
you first refer to the index, which lists all the topics alphabetically and are then referred
to one or more specific page numbers.
An index helps to speed up SELECT queries and WHERE clauses, but it slows down
data input, with the update and the insert statements. Indexes can be created or dropped
with no effect on the data.
Creating an index involves the CREATE INDEX statement, which allows you to name
the index, to specify the table and which columns to index, and to indicate whether the
index is in an ascending or descending order.
Indexes can also be unique, like the UNIQUE constraint, in that the index prevents
duplicate entries in the column or combination of columns on which there is an index.
➢ The CREATE INDEX Command
Single-Column Indexes
A single-column index is created based on only one table column.
The basic syntax is as follows.
 CREATE INDEX index_name
ON table_name (column_name);

Unique Indexes
Unique indexes are used not only for performance, but also for data integrity. A unique
index does not allow any duplicate values to be inserted into the table. The basic syntax
is as follows.
CREATE UNIQUE INDEX index_name
on table_name (column_name);

Composite Indexes
A composite index is an index on two or more columns of a table. Its basic syntax is as
follows.
CREATE INDEX index_name
on table_name (column1, column2);

➢ The DROP INDEX Command

An index can be dropped using SQL DROP command.
The basic syntax is as follows −
DROP INDEX index_name;
VIEW INDEX Command
This command shows the list of all the indexes in the database.
SELECT index_name, table_name FROM user_indexes;
to view the indexes of the particular table use the where clause.
SELECT index_name, table_name
FROM user_indexes where table_name='EMPLOYEE';
2.5 JOIN Queries:
The join clause allows us to retrieve data from two or more related tables into a
meaningful result set. We can join the table using a SELECT statement and a join
condition. It indicates how SQL Server can use data from one table to select rows from
another table. In general, tables are related to each other using foreign key constraints.
In a JOIN query, a condition indicates how two tables are related:
o Choose columns from each table that should be used in the join. A join condition
indicates a foreign key from one table and its corresponding key in the other
table.
o Specify the logical operator to compare values from the columns like =, <, or >.

SQL mainly supports three types of JOINS, and each join type defines how two tables
are related in a query.

The following are types of joins support in SQL:

1. INNER JOIN
2. OUTER JOIN
3. CROSS JOIN

2.5.1 INNER JOIN

The inner join is used to select all matching rows or columns in both tables or as long
as the defined condition is valid in SQL.
This keyword will create the result-set by combining all rows from both the tables where
the condition satisfies i.e. value of the common field will be the same.
The following visual representation explains how INNER JOIN returns the matching
records from table1 and table2:

Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column
INNER JOIN table3
ON table1.matching_column = table3.matching_column;
matching_column: Column common to both the tables.
Example 1:
SELECT [Link], [Link]
FROM Orders INNER JOIN Customers
ON [Link] = [Link];
Example 2:
SELECT [Link], [Link], [Link]
FROM Orders
INNER JOIN Customers ON [Link] = [Link]
INNER JOIN Shippers ON [Link] = [Link];
2.5.2 OUTER JOIN
OUTER JOIN in SQL returns all records from both tables that satisfy the join
condition. In other words, this join will not return only the matching record but also
return all unmatched rows from one or both tables.
We can categories the OUTER JOIN further into three types:
o LEFT OUTER JOIN
o RIGHT OUTER JOIN
o FULL OUTER JOIN

1. LEFT OUTER JOIN

The LEFT OUTER JOIN retrieves all the records from the left table and
matching rows from the right table. It will return NULL when no matching record
is found in the right-side table. Since OUTER is an optional keyword, it is also known
as LEFT JOIN.
The below visual representation illustrates the LEFT OUTER JOIN:
 LEFT OUTER JOIN Syntax
The following syntax illustrates the use of LEFT OUTER JOIN in SQL Server:
SELECT column_lists FROM table1
LEFT [OUTER] JOIN table2
ON [Link] = [Link];
Or
Syntax:
SELECT table1.column1, table1.column2, table2.column1,…..
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Example
We can demonstrate the LEFT OUTER JOIN using the following command:
SELECT S.admission_no, S.first_name, S.last_name, [Link],Fee.amount_paid
FROM Student S
LEFT OUTER JOIN Fee ON S.admission_no = Fee.admission_no;
2. RIGHT OUTER JOIN
The RIGHT OUTER JOIN retrieves all the records from the right-hand table and
matched rows from the left-hand table. It will return NULL when no matching
record is found in the left-hand table. Since OUTER is an optional keyword, it is also
known as RIGHT JOIN.
The below visual representation illustrates the RIGHT OUTER JOIN:

RIGHT OUTER JOIN Syntax

The following syntax illustrates the use of RIGHT OUTER JOIN in SQL Server:
SELECT column_lists
FROM table1
RIGHT [OUTER] JOIN table2 ON [Link] = [Link];
 Example
The following example explains how to use the RIGHT OUTER JOIN to get records
from both tables:
SELECT Student.admission_no, Student.first_name, Student.last_name, [Link]
e, Fee.amount_paid FROM Student
RIGHT OUTER JOIN Fee ON Student.admission_no = Fee.admission_no;
3. FULL OUTER JOIN
The FULL OUTER JOIN in SQL Server returns a result that includes all rows
from both tables. The columns of the right-hand table return NULL when no
matching records are found in the left-hand table. And if no matching records are
found in the right-hand table, the left-hand table column returns NULL.
The FULL OUTER JOIN:
FULL OUTER JOIN Syntax:
SELECT column_lists
FROM table1
FULL [OUTER] JOIN table2 ON [Link] = [Link];
Example
The following example explains how to use the FULL OUTER JOIN to get records
from both tables:
SELECT Student.admission_no, Student.first_name, Student.last_name, [Link]
e, Fee.amount_paid FROM Student
FULL OUTER JOIN Fee ON Student.admission_no = Fee.admission_no;
2.5.3 CROSS JOIN
CROSS JOIN in SQL combines all of the possibilities of two or more tables and
returns a result that includes every row from all contributing tables. It's also known
as CARTESIAN JOIN because it produces the Cartesian product of all linked
tables. The Cartesian product represents all rows present in the first table multiplied
by all rows present in the second table.
The below visual representation illustrates the CROSS JOIN. It will give all the
records from table1 and table2 where each row is the combination of rows of both
tables:
 CROSS JOIN Syntax
The following syntax illustrates the use of CROSS JOIN in SQL Server:
ANSI STYLE:
SELECT column_lists
FROM table1
CROSS JOIN table2;
Example
We can demonstrate the CROSS JOIN using the following command:
SELECT Student.admission_no, Student.first_name, Student.last_name,[Link]
, Fee.amount_paid
FROM Student
CROSS JOIN Fee

❖ THETA-STYLE
INNER JOIN:
SELECT table1.column1, table1.column2, table2.column1, ….
FROM table1, table2
WHERE table1.column1=table2.cloumn1
AND condition
ORDER BY column1, column2
OUTER JOIN:
In theta style (+) indicates that all the rows from the first table will be displayed even
though there exist no matching rows in the second table
LEFT JOIN:
SELECT [Link], [Link],[Link], C.CNTC_TYPE, C.CNTC_DATA
FROM CNTC_DTLS C, EMP_MSTR E
WHERE E.EMP_NO=C.CODE_NO(+);
RIGHT JOIN:
SELECT [Link], [Link], [Link], C.CNTC_TYPE, C.CNTC_DATA
FROM CNTC_DTLS C, EMP_MSTR E
WHERE C.CODE_NO(+)=E.EMP_NO;
QUESTION:
STUDENTS
stud_id stud_name dept_id
1 Ravi 10
2 Priya 20
3 Ankit 10
4 Neha 40
5 Karan 20
6 Riya null

DEPARTMENTS
dept_id dept_name
10 Computer Science
20 Information Technology
30 Civil
40 Mechanical
50 Electrical

COURSES
course_id course_name dept_id
101 Database Systems 10
102 Data Structures 20
 103 Thermodynamics 40
104 Structural Analysis 30

JOIN Questions.
Basic Joins
1. List students and their courses
2. Display student name, department name, and course name
Outer Joins
3. Show all students even if department or course is missing
4. Show all departments even if no students are enrolled
5. Show all courses even if no students exist
Advanced
6. List students which are not enrolled in any department
7. Display departments that have no students and no course

SOLUTION:
1. SELECT STUD_ID, STUD_NAME, COURSE_NAME FROM STUD S
INNER JOIN COURSE C ON S.DEPT_ID=C.DEPT_ID
2. SELECT STUD_NAME, DEPT_NAME, COURSE_NAME FROM STUD
S
INNER JOIN DEPART D ON S.DEPT_ID=D.DEPT_ID
INNER JOIN COURSE C ON D.DEPT_ID=C.DEPT_ID
3. SELECT STUD_ID, STUD_NAME, DEPT_NAME, COURSE_NAME
FROM STUD S
LEFT JOIN DEPART D ON S.DEPT_ID=D.DEPT_ID
LEFT JOIN COURSE C ON S.DEPT_ID=C.DEPT_ID
4. SELECT STUD_ID, STUD_NAME, DEPT_NAME FROM STUD S
RIGHT JOIN DEPART D ON S.DEPT_ID=D.DEPT_ID
5. SELECT STUD_ID, STUD_NAME, COURSE_NAME FROM STUD S
RIGHT JOIN COURSE C ON S.DEPT_ID=C.DEPT_ID
 6. SELECT STUD_ID, STUD_NAME FROM STUD S LEFT JOIN DEPART
D ON S.DEPT_ID=D.DEPT_ID WHERE S.DEPT_ID IS NULL
7. SELECT DEPT_NAME FROM DEPART D
LEFT JOIN STUD S ON S.DEPT_ID=D.DEPT_ID
LEFT JOIN COURSE C ON D.DEPT_ID=C.DEPT_ID WHERE C.DEPT_ID
IS NULL AND S.DEPT_ID IS NULL
2.6 Subquery:
In SQL a Subquery can be simply defined as a query within another query. In other
words, we can say that a Subquery is a query that is embedded in WHERE clause of
another SQL query.
• You can place the Subquery in a number of SQL
clauses: WHERE clause, HAVING clause, FROM clause. Subqueries can be
used with SELECT, UPDATE, INSERT and DELETE statements along with
expression operator. It could be equality operator or comparison operator such
as =, >, =, <= and Like operator.
• A subquery is a query within another query. The outer query is called as main
query and inner query is called as subquery.
• Subqueries are on the right side of the comparison operator. ORDER BY
command cannot be used in a Subquery.

Subqueries with the INSERT Statement

Subqueries also can be used with INSERT statements. The INSERT statement uses the
data returned from the subquery to insert into another table. The selected data in the
subquery can be modified with any of the character, date or number functions.
The basic syntax is as follows.
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ * | column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]

Example
To insert records into 'agent1' table from 'agents' table with the following condition -
1. 'working_area' of 'agents' table must be 'London',
the following SQL statement can be used:
SQL Code:
INSERT INTO agent1
SELECT * FROM agents
WHERE working_area="London";
Or
INSERT INTO agent1(CITY)
SELECT CITY FROM agents
WHERE working_area="London";
Subqueries with the UPDATE Statement
The subquery can be used in conjunction with the UPDATE statement. Either single or
multiple columns in a table can be updated when using a subquery with the UPDATE
statement.
The basic syntax is as follows.
UPDATE tablename
SET column_name = new_value
WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE ] )
• Example: To update name of the students to shiv in Student2 table whose
location is same as raju or ravi from student1 table .
table1: Student1

NAME ROLL_NO LOCATION PHONE_NUMBER

Ram 101 chennai 9988773344

Raju 102 mumbai 9090909090

Ravi 103 delhi 8989898989

Table2: Student2

NAME ROLL_NO LOCATION PHONE_NUMBER

Raj 111 chennai 8787878787

Sai 112 mumbai 6565656565

Sri 113 coimbatore 7878787878

• To update name of the students to shiv in Student2 table whose location is

same as raju or ravi from student1 table.
UPDATE Student2
SET NAME= ‘shiv’
WHERE LOCATION IN ( SELECT LOCATION FROM Student1
WHERE NAME IN (‘Raju’,’Ravi’));

Subqueries with the DELETE Statement

The subquery can be used in conjunction with the DELETE statement like with any
other statements mentioned above.
The basic syntax is as follows.
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME) [ WHERE) ]
Example
Q-If we want to delete those orders from 'neworder' table which advance_amount are
less than the maximum advance_amount of 'orders' table, the following SQL can be
used:
Sample table: orders
ord_num ord_amnt advance_amount ord_date cust_code agent_code
---------- ---------- ----------------------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
 200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005

Sample table: neworder

ord_num ord_amnt advance_amount ord_date cust_code agent_code
---------- ---------- -------------- --------- --------------- --------------- -----------------
200114 3500 2000 15-AUG-08 C00002 A008
200122 2500 400 16-SEP-08 C00003 A004
200118 500 100 20-JUL-08 C00023 A006
200119 4000 700 16-SEP-08 C00007 A010
200121 1500 600 23-SEP-08 C00008 A004
200130 2500 400 30-JUL-08 C00025 A011
200134 4200 1800 25-SEP-08 C00004 A005
200108 4000 600 15-FEB-08 C00008 A004
200103 1500 700 15-MAY-08 C00021 A005

SQL Code:
DELETE FROM neworder
WHERE advance_amount < (SELECT MAX(advance_amount) FROM orders);
➢ SQL Subqueries Example:
We have the following two tables 'student' and 'marks' with common field 'StudentID'.

student marks
Q-write a query to identify and retrieve details of all students who get better marks
than that of the student who's StudentID is 'V002',
but we do not know the marks of 'V002'.
- To solve the problem, we require three queries.
 • One query returns the marks (stored in Total_marks field) of 'V002' and
• a second query identifies the students who get better marks than the result of the
first query.
• A third query that returns the details of the student
SQL Code:
SELECT * FROM student
WHERE studentid IN(SELECT studentid FROM marks WHERE
total_marks>(SELECT total_marks FROM marks WHERE studentid= ‘V002’));
Query result:

Consider the following employees and departments tables from the sample database:

You can use a subquery in many places such as:

• With the IN or NOT IN operator
• With comparison operators
• With the ANY or ALL operator
• In the FROM clause
• In the SELECT clause
SQL subquery examples
❖ SQL subquery with the IN or NOT IN operator
The following example uses a subquery with the NOT IN operator to
Q. find all employees who do not locate at the location 1700:
SELECT employee_id, first_name, last_name
FROM employees
WHERE
 department_id NOT IN (SELECT department_id FROM departments
WHERE location_id = 1700)
ORDER BY first_name, last_name;

❖ SQL subquery with the comparison operator

The following syntax illustrates how a subquery is used with a comparison operator:
comparison_operator (subquery)
where the comparison operator is one of these operators:
• Equal (=)
• Greater than (>)
• Less than (<)
• Greater than or equal (>=)
• Less than or equal (<=)
• Not equal (!=) or (<>)
Q. The following example finds the employees who have the highest salary:
SELECT
employee_id, first_name, last_name, salary
FROM employees
WHERE
salary = (SELECT MAX(salary) FROM employees)

In this example, the subquery returns the highest salary of all employees and the outer
query finds the employees whose salary is equal to the highest one.
Q- The following statement finds all employees who salaries are greater than the
average salary of all employees:
SELECT
employee_id, first_name, last_name, salary FROM employees
WHERE
salary > (SELECT AVG (salary) FROM employees);

In this example, first, the subquery returns the average salary of all employees. Then,
the outer query uses the greater than operator to find all employees whose salaries are
greater than the average.
Q- find the second highest salary,
SELECT MAX(SALARY) FROM Employee WHERE SALARY < (SELECT
MAX(SALARY) FROM Employee);

Q - find the 3rd highest or nth salary

We use DENSE_RANK () function to specify a unique rank number within the partition
as per the specified column value. It is similar to the Rank function with a small
difference.
In the SQL RANK function DENSE_RANK (), if we have duplicate values, SQL
assigns different ranks to those rows as well. Ideally, we should get the same rank for
duplicate or similar values.
DENSE_RANK () Syntax:
DENSE_RANK() OVER([partition by columnname] ORDER BY columname)
RANK () Syntax:
RANK () OVER ([partition by columnname] ORDER BY columname)
PARTITION BY groups the rankings. When the value changes for the column
specified here, the rankings start all over again. Let’s say we added subject to the
student’s test scores. If you partitioned the data by subject, it would give you the ranking
of each score, grouped by subject.
ORDER BY specifies the column whose values you wish to rank. In the example
earlier, grades would be specified after ORDER BY. You can order by descending or
ascending values.
If you partitioned the data by subject, it would give you the ranking of each score,
grouped by subject.
SELECT student_name, DENSE_RANK() OVER(PARTITION BY subject ORDER
BY grades DESC) AS grade_ranking

find the 3rd highest or nth MARKS

SELECT * FROM ( select enrollment,
marks , dense_rank() over (order by marks desc )ranking from Science)
where ranking=3
❖ SQL subquery with the ALL operator
The syntax of the subquery when it is used with the ALL operator is as follows:
comparison_operator ALL (subquery)
The following condition evaluates to true if x is greater than every value returned
by the subquery.
x > ALL (subquery)
For example, suppose the subquery returns three value one, two, and three. The
following condition evaluates to true if x is greater than 3.
x > ALL (1,2,3)
Q- The following example finds all employees whose salaries are greater than the
lowest salary of every department:
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE
salary >= ALL (SELECT MIN(salary)
FROM employees
GROUP BY department_id)

❖ SQL subquery with the ANY operator

The following shows the syntax of a subquery with the ANY operator:
comparison_operator ANY (subquery)
For example, the following condition evaluates to true if x is greater than any value
returned by the subquery. So the condition x > any(1,2,3) evaluates to true if x is
greater than 1.
x > ANY (subquery)
Note that the SOME operator is a synonym for the ANY operator so you can use them
interchangeably.

Q- The following query finds all employees whose salaries are greater than or equal to
the highest salary of every department.
SELECT
employee_id, first_name, last_name, salary
FROM
employees
WHERE
salary >= SOME (SELECT MAX(salary)
FROM employees
GROUP BY department_id);
In this example, the subquery finds the highest salary of employees in each department.
The outer query looks at these values and determines which employee’s salaries are
greater than or equal to any highest salary by department.
❖ SQL Subquery in the SELECT clause
A subquery can be used anywhere an expression can be used in the SELECT clause.
Q- finds the salaries of all employees, their average salary, and the difference
between the salary of each employee and the average salary.
SELECT employee_id, first_name, last_name, salary,
(SELECT ROUND(AVG(salary), 0) FROM employees) average_salary,
salary - (SELECT ROUND(AVG(salary), 0) FROM employees) difference FROM
employee ORDER BY first_name , last_name;