SQL

The commercial database system requires a more user friendly query language.
SQL i.e. structured query language ,it uses a combination of relational algebra and relational calculus constructs.

The user can write SQL statement and submit it to the database.
DBMS will retrieve the appropriate data from disk and return it to user.

Basic structure:-

The SQL expression consists of 3 clauses or components.

Select<list of attributes>
From<table name>
Where<condition>;

Parts of SQL

DDL Commands

• Create Table
• Drop Table
• Alter Table

DML Commands

• Insert
• Update
• Delete

A relational database consists of multiple related tables. A table consists of rows and columns. Tables allow you to store structured data like customers, products, employees, etc.

To create a new table, you use the CREATE TABLE statement. The following illustrates the basic syntax of the CREATE TABLE statement:

CREATE TABLE [IF NOT EXISTS] table_name ( column1 datatype(length) column_contraint, column2 datatype(length) column_contraint, column3 datatype(length) column_contraint, table_constraints );

In above syntax,
First, specify the name of the table after the CREATE TABLE keywords.
Second, creating a table that already exists will result in a error.

The IF NOT EXISTS option allows you to create the new table only if it does not exist. When you use the IF NOT EXISTS option and the table already exists, PostgreSQL issues a notice instead of the error and skips creating the new table.

Third, specify a comma-separated list of table columns.
Each column consists of the column name, the kind of data that column stores, the length of data, and the column constraint.
The column constraints specify rules that data stored in the column must follow.

Finally, specify the table constraints including primary key, foreign key, and check constraints.

The following statement creates the accounts table

CREATE TABLE accounts ( user_id integer PRIMARY KEY, username VARCHAR ( 50 ) UNIQUE NOT NULL, password VARCHAR ( 50 ) NOT NULL, email VARCHAR ( 255 ) UNIQUE NOT NULL );

Insert Command

The PostgreSQL INSERT statement allows you to insert a new row into a table.

The following illustrates the most basic syntax of the INSERT statement:
INSERT INTO table_name(column1, column2, …) VALUES (value1, value2, …);

In this syntax:
First, specify the name of the table (table_name) that you want to insert data after the INSERT INTO keywords and a list of comma-separated columns (colum1, column2, ....).

Second, supply a list of comma-separated values in a parentheses (value1, value2, ...) after the VALUES keyword.
The columns and values in the column and value lists must be in the same order.

E.g.Create Table Links & insert values in it.
CREATE TABLE links ( id integer PRIMARY KEY, url VARCHAR(255) NOT NULL, name VARCHAR(255) NOT NULL, description VARCHAR (255), last_update DATE );

The following statement inserts a new row into the links table

INSERT  INTO links  VALUES('https://www.postgresqltutorial.com','PostgreSQL Tutorial');
The statement returns the following output:
INSERT 0 1

In previous example To insert character data, you enclose it in single quotes (‘) for example 'PostgreSQL Tutorial'.

If you omit required columns in the INSERT statement, PostgreSQL will issue an error.
In case you omit an optional column, PostgreSQL will use the column default value for insert.

In this example, the description is an optional column because it doesn’t have a NOT NULL constraint. Therefore, PostgreSQL uses NULL to insert into the description column.

PostgreSQL automatically generates a sequential number for the serial column so you do not have to supply a value for the serial column in the INSERT statement.

Drop table

Name
DROP TABLE -- remove a table
Synopsis
DROP TABLE [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]
Description
DROP TABLE removes tables from the database.

Only its owner may destroy a table. To empty a table of rows without destroying the table, use DELETE or TRUNCATE.

DROP TABLE always removes any indexes, rules, triggers, and constraints that exist for the target table. However, to drop a table that is referenced by a view or a foreign-key constraint of another table, CASCADE must be specified.

(CASCADE will remove a dependent view entirely, but in the foreign-key case it will only remove the foreign-key constraint, not the other table entirely.)

Parameters

IF EXISTS

Do not throw an error if the table does not exist. A notice is issued in this case.

Name

The name (optionally schema-qualified) of the table to drop.

CASCADE

Automatically drop objects that depend on the table (such as views).

RESTRICT

Refuse to drop the table if any objects depend on it. This is the default
Examples.

To destroy two tables, films and
distributors
DROP TABLE films, distributors;

Introduction to PostgreSQL ALTER TABLE statement

To change the structure of an existing table, you use PostgreSQL ALTER TABLE statement.

The following illustrates the basic syntax of the ALTER TABLE statement

ALTER TABLE  table_name  action;

PostgreSQL provides you with many actions

• Add a column
• Drop a column
• Change the data type of a column
• Rename a column
• Set a default value for the column.
• Add a constraint to a column.
• Rename a table

To add a new column to a table, you use ALTER TABLE ADD COLUMN statement

ALTER TABLE  table_name ADD COLUMN column_name datatype column_constraint;

To drop a column from a table, you use ALTER TABLE DROP COLUMN statement

ALTER TABLE table_name DROP COLUMN column_name;

To rename a column, you use the ALTER TABLE RENAME COLUMN TO statement

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

ALTER TABLE examples

Let’s create a new table called links for practicing with the ALTER TABLE statement.

CREATE TABLE links ( link_id integer PRIMARY KEY, title VARCHAR (512) NOT NULL, url VARCHAR (1024) NOT NULL );

To add a new column named active, you use the following statement

ALTER TABLE links ADD COLUMN active boolean;

The following statement removes the active column from the links table

ALTER TABLE links DROP COLUMN active;

To change the name of the title column to link_title, you use the following statement

ALTER TABLE links RENAME COLUMN title TO link_title;

The following statement adds a new column named target to the linkstable

ALTER TABLE links ADD COLUMN target VARCHAR(10);

To drop a column of a table, you use the DROP COLUMN clause in the ALTER TABLE statement as follows

ALTER TABLE table_name DROP COLUMN column_name;

When you remove a column from a table, PostgreSQL will automatically remove all of the indexes and constraints that involved the dropped column.

If the column that you want to remove is used in other database objects such as views, triggers, stored procedures, etc., you cannot drop the column because other objects are depending on it.

In this case, you need to add the CASCADE option to the DROP COLUMN clause to drop the column and all of its dependent objects:

ALTER TABLE table_name DROP COLUMN column_name CASCADE;

If you want to drop multiple columns of a table in a single command, you use multiple DROP COLUMN clause like this

ALTER TABLE table_name DROP COLUMN column_name1, DROP COLUMN column_name2, ...;

Notice that you need to add a comma (,) after each DROP COLUMN clause.
If a table has one column, you can use drop it using the ALTER TABLE DROP COLUMN statement.

The table has no column then. This is possible in PostgreSQL, but not possible according to SQL standard.

Let’s look at some examples to see how the ALTER TABLE DROP COLUMN statement works.

To rename a column of a table, you use the ALTER TABLE statement with RENAME COLUMN clause as follows

ALTER TABLE table_name RENAME COLUMN column_name TO new_column_name;

Code language: SQL (Structured Query Language) (sql)In this statement:

First, specify the name of the table that contains the column which you want to rename after the ALTER TABLE clause.

Second, provide name of the column that you want to rename after the RENAME COLUMN keywords.

Third, specify the new name for the column after the TO keyword.

The COLUMN keyword in the statement is optional therefore you can omit it like this

ALTER TABLE table_name RENAME column_name TO new_column_name;

To rename a column, you use the ALTER TABLE RENAME COLUMN TO statement

ALTER TABLE table_name RENAME COLUMN column_name  TO new_column_name;

To change a default value of the column, you use ALTER TABLE ALTER COLUMN SET DEFAULT or  DROP DEFAULT

ALTER TABLE table_name  ALTER COLUMN column_name [SET DEFAULT value | DROP DEFAULT];

To change the NOT NULL constraint, you use ALTER TABLE ALTER COLUMN statement

ALTER TABLE table_name ALTER COLUMN column_name [SET NOT NULL| DROP NOT NULL];

To add a CHECK constraint, you use ALTER TABLE ADD CHECK statement

ALTER TABLE table_name ADD CHECK expression;

Generally, to add a constraint to a table, you use ALTER TABLE ADD CONSTRAINT statement

ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_definition;

The PostgreSQL UPDATE statement allows you to modify data in a table. The following illustrates the syntax of the UPDATE statement

UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;

In this syntax: First, specify the name of the table that you want to update data after the UPDATE keyword.

Second, specify columns and their new values after SET keyword.
The columns that do not appear in the SET clause retain their original values.

Third, determine which rows to update in the condition of the WHERE clause.
The WHERE clause is optional.

If you omit the WHERE clause, the UPDATE statement will update all rows in the table.
When the UPDATE statement is executed successfully, it returns the following command tag:

UPDATE count

The count is the number of rows updated including rows whose values did not change.

The PostgreSQL DELETE statement allows you to delete one or more rows from a table.
The following shows basic syntax of the DELETE statement

DELETE FROM table_name WHERE condition;

In this syntax:
First, specify the name of the table from which you want to delete data after the DELETE FROM keywords.

Second, use a condition in the WHERE
clause to specify which rows from the table to delete.

The WHERE clause is optional.
If you omit the WHERE clause, the DELETE statement will delete all rows in the table.
The DELETE statement returns the number of rows deleted.

It returns zero if the DELETE statement did not delete any row.

Constraints are the rules enforced on data columns on table.

These are used to prevent invalid data from being entered into the database.
This ensures the accuracy and reliability of the data in the database.

Constraints could be column level or table level.
Column level constraints are applied only to one column whereas table level constraints are applied to the whole table.

Defining a data type for a column is a constraint in itself.
For example, a column of type DATE constrains the column to valid dates.
The following are commonly used constraints available in PostgreSQL.

NOT NULL Constraint − Ensures that a column cannot have NULL value.

UNIQUE Constraint − Ensures that all values in a column are different.

PRIMARY Key − Uniquely identifies each row/record in a database table.

FOREIGN Key − Constrains data based on columns in other tables.

CHECK Constraint − The CHECK constraint ensures that all values in a column satisfy certain conditions.

NOT NULL Constraint

By default, a column can hold NULL values.
If you do not want a column to have a NULL value, then you need to define such constraint on this column specifying that NULL is now not allowed for that column.
A NOT NULL constraint is always written as a column constraint.

A NULL is not the same as no data; rather, it represents unknown data.

Example
For example, the following PostgreSQL statement creates a new table called COMPANY1 and adds five columns, three of which, ID and NAME and AGE, specify not to accept NULL values −

CREATE TABLE COMPANY1( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );

UNIQUE Constraint

The UNIQUE Constraint prevents two records from having identical values in a particular column. In the COMPANY table, for example, you might want to prevent two or more people from having identical age.

Example
For example, the following PostgreSQL statement creates a new table called COMPANY3 and adds five columns.
Here, AGE column is set to UNIQUE, so that you cannot have two records with same age

CREATE TABLE COMPANY3( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR(50), SALARY REAL DEFAULT 50000.00 );

PRIMARY KEY Constraint

The PRIMARY KEY constraint uniquely identifies each record in a database table.
There can be more UNIQUE columns, but only one primary key in a table.
Primary keys are important when designing the database tables. Primary keys are unique ids.

We use them to refer to table rows. Primary keys become foreign keys in other tables, when creating relations among tables. Due to a 'longstanding coding oversight', primary keys can be NULL in SQLite. This is not the case with other databases
A primary key is a field in a table, which uniquely identifies each row/record in a database table. Primary keys must contain unique values. A primary key column cannot have NULL values.

A table can have only one primary key, which may consist of single or multiple fields. When multiple fields are used as a primary key, they are called a composite key.
If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).

Example
You already have seen various examples above where we have created COMAPNY4 table with ID as primary key

CREATE TABLE COMPANY4( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );

FOREIGN KEY Constraint

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table.

We say this maintains the referential integrity between two related tables.
They are called foreign keys because the constraints are foreign; that is, outside the table.

Foreign keys are sometimes called a referencing key.

Example
For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns.

CREATE TABLE COMPANY7( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL );

For example, the following PostgreSQL statement creates a new table called DEPARTMENT1, which adds three columns.
The column EMP_ID is the foreign key and references the ID field of the table COMPANY6.

CREATE TABLE DEPARTMENT2( ID INT PRIMARY KEY NOT NULL, DEPT CHAR(50) NOT NULL, EMP_ID INT references COMPANY7(ID) );

CHECK Constraint

The CHECK Constraint enables a condition to check the value being entered into a record.
If the condition evaluates to false, the record violates the constraint and is not entered into the table.

Example
For example, the following PostgreSQL statement creates a new table called COMPANY5 and adds five columns.
Here, we add a CHECK with SALARY column, so that you cannot have any SALARY as Zero.

CREATE TABLE COMPANY5( ID INT PRIMARY KEY NOT NULL, NAME TEXT NOT NULL, AGE INT NOT NULL, ADDRESS CHAR(50), SALARY REAL CHECK(SALARY > 0) );

Postgre SQL SELECT statement is used to fetch the data from a database table, which returns data in the form of result table.
These result tables are called result-sets.

Syntax

The basic syntax of SELECT statement is as follows

SELECT column1, column2, columnN FROM table_name;

Here, column1, column2...are the fields of a table, whose values you want to fetch.
If you want to fetch all the fields available in the field then you can use the following

syntax

SELECT * FROM table_name;
If you want to fetch all the fields of CUSTOMERS table, then use the following

query

testdb=# SELECT * FROM COMPANY;
Select empno from employee;
Select empno,age,salary from employee where empname=‘abc’;

String Operation

Database consists of some string values.
The most commonly used operation on string is pattern matching.
The  operator used for pattern matching is Like.

The Patterns are case sensitive, i.e. uppercase characters do not match lowercase.
With keyword we can use two characters:
Percent (%)
It matches with any substring. It means that % stands for more number of characters.
(2) Underscore(_):
It matches with any single character.It means _stands for one character only.

Consider following examples

(a) "mad%":It matches with any string that begins with “mad”string .
It may have characters after “mad”.
For example:  madagascar, madake, madam,

(b) "%mi%": It matches any string containing "mi" in it.
The result may have characters before and after it.
For example: millar, milly, smith, etc.

c)”---”:It matches with any string which has exactly 3 characters in it.
For example: jam, sam, mat etc.

d)”-a-”:It matches with any string which has exactly 3 characters and the middle character is "a".
For example: cat, bat, mat etc.

Q1.Select the row from branch table whose branch name starts with letter "B".
SELECT * FROM BranchWHERE branch_name like 'B%';

Q2. Select tuples from customer table whose name of customer table has  character 'l' at third position.
SELECT *FROM Customer WHERE cust_name like "_ _ l%"

SQL Set Operation

The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

• Union
• UnionAll
• Intersect
• Minus

Union

The SQL Union operation is used to combine the result of two or more SQL SELECT queries.

In the union operation, all the number of datatype and columns must be same in both the tables on which UNION operation is being applied.

The union operation eliminates the duplicate rows from its resultset.

Syntax
SELECT column_name FROM table1 UNION SELECT column_name FROM table2; 
Union SQL query will be:
SELECT * FROM First UNION SELECT * FROM Second; 

Union All

Union All operation is equal to the Union operation. It returns the set without removing duplication and sorting the data.

Syntax

SELECT column_name FROM table1 
UNION ALL 
SELECT column_name FROM table2; 

Example: Using the above First and Second table.

Union All query will be like

SELECT * FROM First  
UNION ALL 
SELECT * FROM Second; 

Intersect

It is used to combine two SELECT statements. The Intersect operation returns the common rows from both the SELECT statements.
In the Intersect operation, the number of datatype and columns must be the same.
It has no duplicates and it arranges the data in ascending order by default.

Syntax
SELECT column_name FROM table1 
INTERSECT  SELECT column_name FROM table2; 

Example:
Using the above First and Second table.
Intersect query will be:
SELECT * FROM First  
INTERSECT 
SELECT * FROM Second; 

Minus

It combines the result of two SELECT statements. Minus operator is used to display the rows which are present in the first query but absent in the second query.
It has no duplicates and data arranged in ascending order by default.

Syntax

SELECT column_name FROM table1 
MINUS 
SELECT column_name FROM table2; 

Example
Using the above First and Second table.
Minus query will be
SELECT * FROM First  
MINUS 
SELECT * FROM Second;

SQL aggregation function is used to perform the calculations on multiple rows of a single column of a table. 

It returns a single value.

It is also used to summarize the data.

In database management an aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.

Various Aggregate Functions

• Count() 
• Sum()
• Avg() 
• Min()
• Max()

Count Functions

COUNT function is used to Count the number of rows in a database table.

It can work on both numeric and non-numeric data types.

COUNT function uses the COUNT(*) that returns the count of all the rows in a specified table. 

COUNT(*) considers duplicate and Null. Now let us understand each Aggregate function with a example:

AVG() function

Avg(salary) = Sum(salary) / count(salary) = 310/5

Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4

AVG Function

This function returns the average value of the numeric column that is supplied as a parameter.

Example: Write a query to select average salary from employee table.

Select AVG(salary) from Employee;

Sum Function

This function sums up the values in the column supplied as a parameter.

Example: Write a query to get the total salary of employees.

Select SUM(salary) from Employee;

Sum(): 

sum(salary):  Sum all Non Null values of Column salary i.e., 310

sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.

Sum function is used to calculate the sum of all selected columns. It works on numeric fields only.

Syntax

SUM()  

or  

SUM( [ALL|DISTINCT] expression )  

Example: SUM()

SELECT SUM(COST)  

FROM PRODUCT_MAST;  

Output:

670 

Example: SUM() with WHERE

SELECT SUM(COST)  

FROM PRODUCT_MAST  

WHERE QTY>3; 

Max Function

The MAX function is used to find maximum value in the column that is supplied as a parameter. 

It can be used on any type of data.

Example − Write a query to find the maximum salary in employee table.

Select MAX(salary) from Employee;

MAX Function

MAX function is used to find the maximum value of a certain column. 

This function determines the largest value of all selected values of a column.

Syntax

MAX()  

or  

MAX( [ALL|DISTINCT] expression )  

Example:

SELECT MAX(RATE)  

FROM PRODUCT_MAST;  

30 

Max(salary): Maximum value in the salary i.e., 80.

MIN Function

MIN function is used to find the minimum value of a certain column. This function determines the smallest value of all selected values of a column.

Syntax

MIN()  

or  

MIN( [ALL|DISTINCT] expression )  

Example:

SELECT MIN(RATE)  

FROM PRODUCT_MAST;  

Output:

10

Min(salary): Minimum value in the salary column except NULL i.e., 40.

The SQL GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. This GROUP BY clause follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.

Syntax

The basic syntax of a GROUP BY clause is shown in the following code block. The GROUP BY clause must follow the conditions in the WHERE clause and must precede the ORDER BY clause if one is used.

SELECT column1, column2 FROM table_name WHERE [ conditions ] GROUP BY column1, column2 ORDER BY column1, column2

If you want to know the total amount of the salary on each customer, then the GROUP BY query would be as follows.

SQL> SELECT NAME, SUM(SALARY) FROM CUSTOMERS GROUP BY NAME;

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

Syntax

The basic syntax of the ORDER BY clause is as follows

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC]; You can use more than one column in the ORDER BY clause. Make sure whatever column you are using to sort that column should be in the column-list.

The following code block has an example, which would sort the result in an ascending order by the NAME and the SALARY −

SQL> SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;

The following code block has an example, which would sort the result in the descending order by NAME.

SQL> SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

The SQL DISTINCT keyword is used in conjunction with the SELECT statement to eliminate all the duplicate records and fetching only unique records.

There may be a situation when you have multiple duplicate records in a table. While fetching such records, it makes more sense to fetch only those unique records instead of fetching duplicate records.

Syntax

The basic syntax of DISTINCT keyword to eliminate the duplicate records is as follows 

SELECT DISTINCT column1, column2,.....columnN FROM table_name WHERE [condition]

First, let us see how the following SELECT query returns the duplicate salary records.

SQL> SELECT SALARY FROM CUSTOMERS ORDER BY SALARY;

This would produce the following result, where the salary (2000) is coming twice which is a duplicate record from the original table.

Now, let us use the DISTINCT keyword with the above SELECT query and then see the result.

SQL> SELECT DISTINCT SALARY FROM CUSTOMERS ORDER BY SALARY;

This would produce the following result where we do not have any duplicate entry.

The HAVING Clause enables you to specify conditions that filter which group results appear in the results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax

The following code block shows the position of the HAVING Clause in a query.

SELECT FROM WHERE GROUP BY HAVING ORDER BY The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. 

The following code block has the syntax of the SELECT statement including the HAVING clause −

SELECT column1, column2 FROM table1, table2 WHERE [ conditions ] GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2

SQl having Example

The following SQL statement lists the number of customers in each country. Only include countries with more than 5

customers

Example

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5;

The following SQL statement lists the number of customers in each country, sorted high to low (Only include countries with more than 5 customers):

Example

SELECT COUNT(CustomerID), Country

FROM Customers

GROUP BY Country

HAVING COUNT(CustomerID) > 5

ORDER BY COUNT(CustomerID) DESC;

Nested Queries in SQL

In nested queries, a query is written inside a query. The result of inner query is used in execution of outer query. We will use STUDENT, COURSE, STUDENT_COURSE tables for understanding nested queries.

There are mainly two types of nested queries

Independent Nested Queries

In independent nested queries, query execution starts from innermost query to outermost queries. The execution of inner query is independent of outer query, but the result of inner query is used in execution of outer query. Various operators like IN, NOT IN, ANY, ALL etc are used in writing

independent nested queries

IN: If we want to find out S_ID who are enrolled in C_NAME ‘DSA’ or ‘DBMS’, we can write it with the help of independent nested query and IN operator. 

Note: If we want to find out names of STUDENTs who have either enrolled in ‘DSA’ or ‘DBMS’, it can be done as:

Select S_NAME from STUDENT where S_ID IN

(Select S_ID from STUDENT_COURSE where C_ID IN

(SELECT C_ID from COURSE where C_NAME=’DSA’ or C_NAME=’DBMS’));

NOT IN: If we want to find out S_IDs of STUDENTs who have neither enrolled in ‘DSA’ nor in ‘DBMS’, it can be done as:

Select S_ID from STUDENT where S_ID NOT IN(Select S_ID from STUDENT_COURSE where C_ID IN

(SELECT C_ID from COURSE where C_NAME=’DSA’ or C_NAME=’DBMS’));

The innermost query will return a set with members C1 and C3. Second inner query will return those S_IDs for which C_ID is equal to any member of set (C1 and C3 in this case) which are S1, S2 and S4. The outermost query will return those S_IDs where S_ID is not a member of set (S1, S2 and S4). So it will return S3.

Co-related Nested Queries

In co-related nested queries, the output of inner query depends on the row which is being currently executed in outer query. e.g.; If we want to find out S_NAME of STUDENTs who are enrolled in C_ID ‘C1’, it can be done with the help of co-related nested query as:

Select S_NAME from STUDENT S where EXISTS ( select * from STUDENT_COURSE SC where S.S_ID=SC.S_ID and SC.C_ID=’C1’);

For each row of STUDENT S, it will find the rows from STUDENT_COURSE where S.S_ID = SC.S_ID and SC.C_ID=’C1’. If for a S_ID from STUDENT S, atleast a row exists in STUDENT_COURSE SC with C_ID=’C1’, then inner query will return true and corresponding S_ID will be returned as output.

Also Read

1. File Orgnization in DBMS
2. Introduction to DBMS
3. Conceptual design and ER model in DBMS
4. Relational Database design in DBMS