UNIT-3

What is Relational Model?

Relational Model represents how data is stored in Relational Databases. A relational database
stores data in the form of relations (tables). Consider a relation STUDENT with attributes
ROLL_NO, NAME, ADDRESS, PHONE and AGE shown in Table 1.

STUDENT

ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 18

 Attribute: Attributes are the properties that define a relation. e.g.; ROLL_NO, NAME
 Relation Schema: A relation schema represents name of the relation with its attributes. e.g.;
STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is relation schema for
STUDENT. If a schema has more than 1 relation, it is called Relational Schema.
 Tuple: Each row in the relation is known as tuple. The above relation contains 4 tuples, one of
which is shown as:
 Relation Instance: The set of tuples of a relation at a particular instance of time is called as
relation instance. Table 1 shows the relation instance of STUDENT at a particular time. It can
change whenever there is insertion, deletion or updation in the database.
 Degree: The number of attributes in the relation is known as degree of the relation.
The STUDENT relation defined above has degree 5.
 Cardinality: The number of tuples in a relation is known as cardinality. The STUDENT relation
defined above has cardinality 4.
 Column: Column represents the set of values for a particular attribute. The
column ROLL_NO is extracted from relation STUDENT.
 NULL Values: The value which is not known or unavailable is called NULL value. It is
represented by blank space. e.g.; PHONE of STUDENT having ROLL_NO 4 is NULL.
Constraints in Relational Model

While designing Relational Model, we define some conditions which must hold for data present in
database are called Constraints. These constraints are checked before performing any operation
(insertion, deletion and updation) in database. If there is a violation in any of constrains, operation
will fail.
Domain Constraints: These are attribute level constraints. An attribute can only take values
which lie inside the domain range. e.g,; If a constrains AGE>0 is applied on STUDENT relation,
inserting negative value of AGE will result in failure.
Key Integrity: Every relation in the database should have atleast one set of attributes which
defines a tuple uniquely. Those set of attributes is called key. e.g.; ROLL_NO in STUDENT is a
key. No two students can have same roll number. So a key has two properties:
 It should be unique for all tuples.
 It can’t have NULL values.
Referential Integrity: When one attribute of a relation can only take values from other attribute of
same relation or any other relation, it is called referential integrity. Let us suppose we have 2
relations
STUDENT

ROLL_NO NAME ADDRESS PHONE AGE BRANCH_CODE

1 RAM DELHI 9455123451 18 CS

2 RAMESH GURGAON 9652431543 18 CS

3 SUJIT ROHTAK 9156253131 20 ECE

4 SURESH DELHI 18 IT
BRANCH_CODE of STUDENT can only take the values which are present in BRANCH_CODE of
BRANCH which is called referential integrity constraint. The relation which is referencing to other
relation is called REFERENCING RELATION (STUDENT in this case) and the relation to which
other relations refer is called REFERENCED RELATION (BRANCH in this case).

ANOMALIES

An anomaly is an irregularity, or something which deviates from the expected or normal state.
When designing databases, we identify three types of anomalies: Insert, Update and Delete.
Insertion Anomaly in Referencing Relation:
We can’t insert a row in REFERENCING RELATION if referencing attribute’s value is not present
in referenced attribute value. e.g.; Insertion of a student with BRANCH_CODE ‘ME’ in STUDENT
relation will result in error because ‘ME’ is not present in BRANCH_CODE of BRANCH.
Deletion/ Updation Anomaly in Referenced Relation:
We can’t delete or update a row from REFERENCED RELATION if value of REFERENCED
ATTRIBUTE is used in value of REFERENCING ATTRIBUTE. e.g; if we try to delete tuple from
BRANCH having BRANCH_CODE ‘CS’, it will result in error because ‘CS’ is referenced by
BRANCH_CODE of STUDENT, but if we try to delete the row from BRANCH with
BRANCH_CODE CV, it will be deleted as the value is not been used by referencing relation. It can
be handled by following method:
ON DELETE CASCADE: It will delete the tuples from REFERENCING RELATION if value used
by REFERENCING ATTRIBUTE is deleted from REFERENCED RELATION. e.g;, if we delete a
row from BRANCH with BRANCH_CODE ‘CS’, the rows in STUDENT relation with
BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be deleted.
ON UPDATE CASCADE: It will update the REFERENCING ATTRIBUTE in REFERENCING
RELATION if attribute value used by REFERENCING ATTRIBUTE is updated in REFERENCED
RELATION. e.g;, if we update a row from BRANCH with BRANCH_CODE ‘CS’ to ‘CSE’, the rows
in STUDENT relation with BRANCH_CODE CS (ROLL_NO 1 and 2 in this case) will be updated
with BRANCH_CODE ‘CSE’.
SUPER KEYS:
Any set of attributes that allows us to identify unique rows (tuples) in a given relation are known as
super keys. Out of these super keys we can always choose a proper subset among these which
can be used as a primary key. Such keys are known as Candidate keys. If there is a combination
of two or more attributes which is being used as the primary key then we call it as a Composite
key.

Properties of Relations
o Name of the relation is distinct from all other relations.
o Each relation cell contains exactly one atomic (single) value
o Each attribute contains a distinct name
o Attribute domain has no significance
o tuple has no duplicate value
o Order of tuple can have a different sequence

SQL
o SQL stands for Structured Query Language. It is used for storing and managing data in relational
database management system (RDMS).
o It is a standard language for Relational Database System. It enables a user to create, read, update
and delete relational databases and tables.
o All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server use SQL as their standard
database language.
o SQL allows users to query the database in a number of ways, using English-like statements.

Rules:

SQL follows the following rules:

o Structure query language is not case sensitive. Generally, keywords of SQL are written in
uppercase.
 o Statements of SQL are dependent on text lines. We can use a single SQL statement on one or
multiple text line.
o Using the SQL statements, you can perform most of the actions in a database.
o SQL depends on tuple relational calculus and relational algebra.

SQL process:
o When an SQL command is executing for any RDBMS, then the system figure out the best way to
carry out the request and the SQL engine determines that how to interpret the task.
o In the process, various components are included. These components can be optimization Engine,
Query engine, Query dispatcher, classic, etc.
o All the non-SQL queries are handled by the classic query engine, but SQL query engine won't
handle logical files.

Characteristics of SQL
o SQL is easy to learn.
o SQL is used to access data from relational database management systems.
o SQL can execute queries against the database.
o SQL is used to describe the data.
o SQL is used to define the data in the database and manipulate it when needed.
o SQL is used to create and drop the database and table.
o SQL is used to create a view, stored procedure, function in a database.
o SQL allows users to set permissions on tables, procedures, and views.
Advantages of SQL
There are the following advantages of SQL:

High speed

Using the SQL queries, the user can quickly and efficiently retrieve a large amount of records from a
database.

No coding needed

In the standard SQL, it is very easy to manage the database system. It doesn't require a substantial
amount of code to manage the database system.

Well defined standards

Long established are used by the SQL databases that are being used by ISO and ANSI.

Portability

SQL can be used in laptop, PCs, server and even some mobile phones.

Interactive language

SQL is a domain language used to communicate with the database. It is also used to receive answers to
the complex questions in seconds.

Multiple data view

Using the SQL language, the users can make different views of the database structure.

SQL Data type

o SQL Data type is used to define the values that a column can contain.
o Every column is required to have a name and data type in the database table.

Data type of SQL:

1. Binary Data types

There are three types of binary Data types which are given below:

Data Type Description

binary It has a maximum length of 8000 bytes. It contains fixed-length binary data.

varbinary It has a maximum length of 8000 bytes. It contains variable-length binary data.

image It has a maximum length of 2,147,483,647 bytes. It contains variable-length binary data.

2. Approximate Numeric Datatype :

The subtypes are given below:

Data type From To Description

float -1.79E + 308 1.79E + 308 It is used to specify a floating-point value e.g. 6.2, 2.9 etc.

real -3.40e + 38 3.40E + 38 It specifies a single precision floating point number

3. Exact Numeric Datatype

The subtypes are given below:

Data type Description

int It is used to specify an integer value.

smallint It is used to specify small integer value.

bit It has the number of bits to store.

decimal It specifies a numeric value that can have a decimal number.

numeric It is used to specify a numeric value.

4. Character String Datatype

The subtypes are given below:

Data Description
type

char It has a maximum length of 8000 characters. It contains Fixed-length non-unicode

characters.

varchar It has a maximum length of 8000 characters. It contains variable-length non-unicode

characters.

text It has a maximum length of 2,147,483,647 characters. It contains variable-length non-

unicode characters.

5. Date and time Datatypes

The subtypes are given below:

Datatype Description

date It is used to store the year, month, and days value.

time It is used to store the hour, minute, and second values.

timestamp It stores the year, month, day, hour, minute, and the second value.

SQL Commands
o SQL commands are instructions. It is used to communicate with the database. It is also used to
perform specific tasks, functions, and queries of data.
o SQL can perform various tasks like create a table, add data to tables, drop the table, modify the
table, set permission for users.

Types of SQL Commands

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.

1. Data Definition Language (DDL)

o DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
o All the command of DDL are auto-committed that means it permanently save all the changes in
the database.

Here are some commands that come under DDL:

o CREATE
o ALTER
 o DROP
o TRUNCATE

a. CREATE It is used to create a new table in the database.

Syntax:

1. CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

Example:

1. CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email VARCHAR2(100), DOB DATE);

b. DROP: It is used to delete both the structure and record stored in the table.

Syntax

1. DROP TABLE table_name;

Example

1. DROP TABLE EMPLOYEE;

c. ALTER: It is used to alter the structure of the database. This change could be either to modify the
characteristics of an existing attribute or probably to add a new attribute.

Syntax:

To add a new column in the table

1. ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify existing column in the table:

1. ALTER TABLE table_name MODIFY(column_definitions....);

EXAMPLE
1. ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));
2. ALTER TABLE STU_DETAILS MODIFY (NAME VARCHAR2(20));

d. TRUNCATE: It is used to delete all the rows from the table and free the space containing the table.

Syntax:

1. TRUNCATE TABLE table_name;

Example:

1. TRUNCATE TABLE EMPLOYEE;

2. Data Manipulation Language

o DML commands are used to modify the database. It is responsible for all form of changes in the
database.
o The command of DML is not auto-committed that means it can't permanently save all the
changes in the database. They can be rollback.

Here are some commands that come under DML:

o INSERT
o UPDATE
o DELETE

a. INSERT: The INSERT statement is a SQL query. It is used to insert data into the row of a table.

Syntax:

1. INSERT INTO TABLE_NAME

2. (col1, col2, col3,.... col N)
3. VALUES (value1, value2, value3, .... valueN);

Or

INSERT INTO TABLE_NAME

1. VALUES (value1, value2, value3, .... valueN);

 For example:

1. INSERT INTO javatpoint (Author, Subject) VALUES ("Sonoo", "DBMS");

b. UPDATE: This command is used to update or modify the value of a column in the table.

Syntax:

1. UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

For example:

1. UPDATE students
2. SET User_Name = 'Sonoo'
3. WHERE Student_Id = '3'

c. DELETE: It is used to remove one or more row from a table.

Syntax:

1. DELETE FROM table_name [WHERE condition];

For example:

1. DELETE FROM javatpoint

2. WHERE Author="Sonoo";

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.

Here are some commands that come under DCL:

o Grant
o Revoke

a. Grant: It is used to give user access privileges to a database.

Example

1. GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

 b. Revoke: It is used to take back permissions from the user.

Example

1. REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4. Transaction Control Language

TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

These operations are automatically committed in the database that's why they cannot be used while
creating tables or dropping them.

Here are some commands that come under TCL:

o COMMIT
o ROLLBACK
o SAVEPOINT

a. Commit: Commit command is used to save all the transactions to the database.

Syntax:

1. COMMIT;

Example:

1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;
3. COMMIT;

b. Rollback: Rollback command is used to undo transactions that have not already been saved to the
database.

Syntax:

1. ROLLBACK;

Example:

1. DELETE FROM CUSTOMERS

2. WHERE AGE = 25;
3. ROLLBACK;

c. SAVEPOINT: It is used to roll the transaction back to a certain point without rolling back the entire
transaction.

Syntax:

1. SAVEPOINT SAVEPOINT_NAME;

5. Data Query Language

DQL is used to fetch the data from the database.

It uses only one command:

o SELECT

a. SELECT: This is the same as the projection operation of relational algebra. It is used to select the
attribute based on the condition described by WHERE clause.

Syntax:

1. SELECT expressions
2. FROM TABLES
3. WHERE conditions;

For example:

1. SELECT emp_name
2. FROM employee
3. WHERE age > 20;

SQL Operator
There are various types of SQL operator:
SQL Arithmetic Operators
Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

Operator Description Example

+ It adds the value of both operands. a+b will give

30

- It is used to subtract the right-hand operand from the left-hand operand. a-b will give
10

* It is used to multiply the value of both operands. a*b will give

200

/ It is used to divide the left-hand operand by the right-hand operand. a/b will give 2

% It is used to divide the left-hand operand by the right-hand operand and a%b will give 0
returns reminder.

SQL Comparison Operators:

Let's assume 'variable a' and 'variable b'. Here, 'a' contains 20 and 'b' contains 10.

Operator Description Example

= It checks if two operands values are equal or not, if the values are queal (a=b) is not
 then condition becomes true. true

!= It checks if two operands values are equal or not, if values are not equal, (a!=b) is true
then condition becomes true.

<> It checks if two operands values are equal or not, if values are not equal (a<>b) is true
then condition becomes true.

> It checks if the left operand value is greater than right operand value, if (a>b) is not
yes then condition becomes true. true

< It checks if the left operand value is less than right operand value, if yes (a<b) is true
then condition becomes true.

>= It checks if the left operand value is greater than or equal to the right (a>=b) is not
operand value, if yes then condition becomes true. true

<= It checks if the left operand value is less than or equal to the right (a<=b) is true
operand value, if yes then condition becomes true.

!< It checks if the left operand value is not less than the right operand value, (a!=b) is not
if yes then condition becomes true. true

!> It checks if the left operand value is not greater than the right operand (a!>b) is true
value, if yes then condition becomes true.

SQL Logical Operators

There is the list of logical operator used in SQL:

Operator Description

ALL It compares a value to all values in another value set.

AND It allows the existence of multiple conditions in an SQL statement.

ANY It compares the values in the list according to the condition.

BETWEEN It is used to search for values that are within a set of values.

IN It compares a value to that specified list value.

NOT It reverses the meaning of any logical operator.

OR It combines multiple conditions in SQL statements.

EXISTS It is used to search for the presence of a row in a specified table.

LIKE It compares a value to similar values using wildcard operator.

SQL Table
o SQL Table is a collection of data which is organized in terms of rows and columns. In DBMS, the
table is known as relation and row as a tuple.
o Table is a simple form of data storage. A table is also considered as a convenient representation
of relations.

Let's see an example of the EMPLOYEE table:

EMP_ID EMP_NAME CITY PHONE_NO

1 Kristen Washington 7289201223

2 Anna Franklin 9378282882

3 Jackson Bristol 9264783838

4 Kellan California 7254728346

5 Ashley Hawaii 9638482678

 In the above table, "EMPLOYEE" is the table name, "EMP_ID", "EMP_NAME", "CITY", "PHONE_NO" are the
column names. The combination of data of multiple columns forms a row, e.g., 1, "Kristen", "Washington"
and 7289201223 are the data of one row.

Operation on Table
1. Create table
2. Drop table
3. Delete table
4. Rename table

SQL Create Table

SQL create table is used to create a table in the database. To define the table, you should define the name
of the table and also define its columns and column's data type.

Syntax

Apple RELEASED More!

1. create table "table_name"

2. ("column1" "data type",
3. "column2" "data type",
4. "column3" "data type",
5. ...
6. "columnN" "data type");

Example

1. SQL> CREATE TABLE EMPLOYEE (

2. EMP_ID INT NOT NULL,
3. EMP_NAME VARCHAR (25) NOT NULL,
4. PHONE_NO INT NOT NULL,
5. ADDRESS CHAR (30),
6. PRIMARY KEY (ID)
7. );

If you create the table successfully, you can verify the table by looking at the message by the SQL server.
Else you can use DESC command as follows:

SQL> DESC EMPLOYEE;

 Field Type Null Key Default Extra

EMP_ID int(11) NO PRI NULL

EMP_NAME varchar(25) NO NULL

PHONE_NO NO int(11) NULL

ADDRESS YES NULL char(30)

o 4 rows in set (0.35 sec)

Now you have an EMPLOYEE table in the database, and you can use the stored information related to the
employees.

Drop table

A SQL drop table is used to delete a table definition and all the data from a table. When this command is
executed, all the information available in the table is lost forever, so you have to very careful while using
this command.

Syntax

1. DROP TABLE "table_name";

Firstly, you need to verify the EMPLOYEE table using the following command:

1. SQL> DESC EMPLOYEE;

Field Type Null Key Default Extra

EMP_ID int(11) NO PRI NULL

EMP_NAME varchar(25) NO NULL

PHONE_NO NO int(11) NULL

 ADDRESS YES NULL char(30)

o 4 rows in set (0.35 sec)

This table shows that EMPLOYEE table is available in the database, so we can drop it as follows:

volume is

1. SQL>DROP TABLE EMPLOYEE;

Now, we can check whether the table exists or not using the following command:

1. Query OK, 0 rows affected (0.01 sec)

As this shows that the table is dropped, so it doesn't display it.

SQL DELETE table

In SQL, DELETE statement is used to delete rows from a table. We can use WHERE condition to delete a
specific row from a table. If you want to delete all the records from the table, then you don't need to use
the WHERE clause.

Syntax

1. DELETE FROM table_name WHERE condition;

Example

Suppose, the EMPLOYEE table having the following records:

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Denzel Boston 7353662627 100000

 4 Angelina Denver 9232673822 600000

5 Robert Washington 9367238263 350000

6 Christian Los angels 7253847382 260000

The following query will DELETE an employee whose ID is 2.

1. SQL> DELETE FROM EMPLOYEE

2. WHERE EMP_ID = 3;

Now, the EMPLOYEE table would have the following records.

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

4 Angelina Denver 9232673822 600000

5 Robert Washington 9367238263 350000

6 Christian Los angels 7253847382 260000

If you don't specify the WHERE condition, it will remove all the rows from the table.

1. DELETE FROM EMPLOYEE;

Now, the EMPLOYEE table would not have any records.

SQL SELECT Statement

In SQL, the SELECT statement is used to query or retrieve data from a table in the database. The returns
data is stored in a table, and the result table is known as result-set.

Syntax
1. SELECT column1, column2, ...
2. FROM table_name;

Here, the expression is the field name of the table that you want to select data from.

Use the following syntax to select all the fields available in the table:

1. SELECT * FROM table_name;

Example:

EMPLOYEE

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

4 Robert Washington 9367238263 350000

5 Christian Los angels 7253847382 260000

To fetch the EMP_ID of all the employees, use the following query:

1. SELECT EMP_ID FROM EMPLOYEE;

Output

EMP_ID

2
 3

To fetch the EMP_NAME and SALARY, use the following query:

1. SELECT EMP_NAME, SALARY FROM EMPLOYEE;

EMP_NAME SALARY

Kristen 150000

Russell 200000

Angelina 600000

Robert 350000

Christian 260000

To fetch all the fields from the EMPLOYEE table, use the following query:

1. SELECT * FROM EMPLOYEE

Output

EMP_ID EMP_NAME CITY PHONE_NO SALARY

1 Kristen Chicago 9737287378 150000

2 Russell Austin 9262738271 200000

3 Angelina Denver 9232673822 600000

 4 Robert Washington 9367238263 350000

5 Christian Los angels 7253847382 260000

SQL INSERT Statement

The SQL INSERT statement is used to insert a single or multiple data in a table. In SQL, You can insert the
data in two ways:

1. Without specifying column name

2. By specifying column name

Sample Table

EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

1. Without specifying column name

If you want to specify all column values, you can specify or ignore the column values.

Syntax

1. INSERT INTO TABLE_NAME

2. VALUES (value1, value2, value 3, .... Value N);
 Query

1. INSERT INTO EMPLOYEE VALUES (6, 'Marry', 'Canada', 600000, 48);

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

2. By specifying column name

To insert partial column values, you must have to specify the column names.

Syntax

1. INSERT INTO TABLE_NAME

2. [(col1, col2, col3,.... col N)]
3. VALUES (value1, value2, value 3, .... Value N);

Query

1. INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME, AGE) VALUES (7, 'Jack', 40);

Output: After executing this query, the table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

 1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

7 Jack null null 40

SQL Update Statement

The SQL UPDATE statement is used to modify the data that is already in the database. The condition in
the WHERE clause decides that which row is to be updated.

Syntax

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

Sample Table

EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

 4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Updating single record

Update the column EMP_NAME and set the value to 'Emma' in the row where SALARY is 500000.

Syntax

1. UPDATE table_name
2. SET column_name = value
3. WHERE condition;

Query

1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Emma'
3. WHERE SALARY = 500000;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Emma Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

 Updating multiple records
If you want to update multiple columns, you should separate each field assigned with a comma. In the
EMPLOYEE table, update the column EMP_NAME to 'Kevin' and CITY to 'Boston' where EMP_ID is 5.

Syntax

1. UPDATE table_name
2. SET column_name = value1, column_name2 = value2
3. WHERE condition;

Query

1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Kevin', City = 'Boston'
3. WHERE EMP_ID = 5;

Output

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Kevin Boston 200000 36

6 Marry Canada 600000 48

Without use of WHERE clause

If you want to update all row from a table, then you don't need to use the WHERE clause. In the
EMPLOYEE table, update the column EMP_NAME as 'Harry'.

Syntax
1. UPDATE table_name
2. SET column_name = value1;

Query

1. UPDATE EMPLOYEE
2. SET EMP_NAME = 'Harry';

Output

EMP_ID EMP_NAME CITY SALARY AGE

1 Harry Chicago 200000 30

2 Harry Austin 300000 26

3 Harry Denver 100000 42

4 Harry Washington 500000 29

5 Harry Los angels 200000 36

6 Harry Canada 600000 48

SQL DELETE Statement

The SQL DELETE statement is used to delete rows from a table. Generally, DELETE statement removes one
or more records form a table.

Syntax

1. DELETE FROM table_name WHERE some_condition;

Sample Table

EMPLOYEE
 EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Deleting Single Record

Delete the row from the table EMPLOYEE where EMP_NAME = 'Kristen'. This will delete only the fourth
row.Exception Handling in Java -

Query

1. DELETE FROM EMPLOYEE

2. WHERE EMP_NAME = 'Kristen';

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

5 Russell Los angels 200000 36

 6 Marry Canada 600000 48

Deleting Multiple Record

Delete the row from the EMPLOYEE table where AGE is 30. This will delete two rows(first and third row).

Query

1. DELETE FROM EMPLOYEE WHERE AGE= 30;

Output: After executing this query, the EMPLOYEE table will look like:

EMP_ID EMP_NAME CITY SALARY AGE

2 Robert Austin 300000 26

3 Christian Denver 100000 42

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

Delete all of the records

Delete all the row from the EMPLOYEE table. After this, no records left to display. The EMPLOYEE table will
become empty.

Syntax

1. DELETE * FROM table_name;

2. or
3. DELETE FROM table_name;

Query

1. DELETE FROM EMPLOYEE;

Output: After executing this query, the EMPLOYEE table will look like:
EMP_ID EMP_NAME CITY SALARY AGE

Views in SQL
o Views in SQL are considered as a virtual table. A view also contains rows and columns.
o To create the view, we can select the fields from one or more tables present in the database.
o A view can either have specific rows based on certain condition or all the rows of a table.

Sample table:

Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi

2 Kathrin Noida

3 David Ghaziabad

4 Alina Gurugram

Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19

2 Kathrin 86 21

3 David 74 18

4 Alina 90 20
 5 John 96 18

1. Creating view
A view can be created using the CREATE VIEW statement. We can create a view from a single table or
multiple tables.

Syntax:

1. CREATE VIEW view_name AS

2. SELECT column1, column2.....
3. FROM table_name
4. WHERE condition;

2. Creating View from a single table

In this example, we create a View named DetailsView from the table Student_Detail.

Query:

1. CREATE VIEW DetailsView AS

2. SELECT NAME, ADDRESS
3. FROM Student_Details
4. WHERE STU_ID < 4;

Just like table query, we can query the view to view the data.

1. SELECT * FROM DetailsView;

Output:

NAME ADDRESS

Stephan Delhi

Kathrin Noida

David Ghaziabad
 3. Creating View from multiple tables
View from multiple tables can be created by simply include multiple tables in the SELECT statement.

In the given example, a view is created named MarksView from two tables Student_Detail and
Student_Marks.

Query:

1. CREATE VIEW MarksView AS

2. SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
3. FROM Student_Detail, Student_Mark
4. WHERE Student_Detail.NAME = Student_Marks.NAME;

To display data of View MarksView:

1. SELECT * FROM MarksView;

NAME ADDRESS MARKS

Stephan Delhi 97

Kathrin Noida 86

David Ghaziabad 74

Alina Gurugram 90

4. Deleting View
A view can be deleted using the Drop View statement.

Syntax

1. DROP VIEW view_name;

Example:

If we want to delete the View MarksView, we can do this as:

1. DROP VIEW MarksView;

 SQL Index
o Indexes are special lookup tables. It is used to retrieve data from the database very fast.
o An Index is used to speed up select queries and where clauses. But it shows down the data input
with insert and update statements. Indexes can be created or dropped without affecting the data.
o An index in a database is just like an index in the back of a book.
o For example: When you reference all pages in a book that discusses a certain topic, you first have
to refer to the index, which alphabetically lists all the topics and then referred to one or more
specific page numbers.

1. Create Index statement

It is used to create an index on a table. It allows duplicate value.

Syntax

1. CREATE INDEX index_name

2. ON table_name (column1, column2, ...);

Example

1. CREATE INDEX idx_name

2. ON Persons (LastName, FirstName);

2. Unique Index statement

It is used to create a unique index on a table. It does not allow duplicate value.

Syntax

1. CREATE UNIQUE INDEX index_name

2. ON table_name (column1, column2, ...);

Example

1. CREATE UNIQUE INDEX websites_idx

2. ON websites (site_name);

3. Drop Index Statement

It is used to delete an index in a table.

Syntax
1. DROP INDEX index_name;

Example

1. DROP INDEX websites_idx;

SQL JOIN
As the name shows, JOIN means to combine something. In case of SQL, JOIN means "to combine two or
more tables".

In SQL, JOIN clause is used to combine the records from two or more tables in a database.

Types of SQL JOIN

1. INNER JOIN
2. LEFT JOIN
3. RIGHT JOIN
4. FULL JOIN
5. Cross join

Sample Table

EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30

2 Robert Austin 300000 26

3 Christian Denver 100000 42

4 Kristen Washington 500000 29

5 Russell Los angels 200000 36

6 Marry Canada 600000 48

 PROJECT

66.1M

1.3K

Exception Handling in Java -

PROJECT_NO EMP_ID DEPARTMENT

101 1 Testing

102 2 Development

103 3 Designing

104 4 Development

1. INNER JOIN

In SQL, INNER JOIN selects records that have matching values in both tables as long as the condition is
satisfied. It returns the combination of all rows from both the tables where the condition satisfies.

Syntax

1. SELECT table1.column1, table1.column2, table2.column1,....

2. FROM table1
3. INNER JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

2. FROM EMPLOYEE
3. INNER JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT
 Angelina Testing

Robert Development

Christian Designing

Kristen Development

2. LEFT JOIN

The SQL left join returns all the values from left table and the matching values from the right table. If there
is no matching join value, it will return NULL.

Syntax

1. SELECT table1.column1, table1.column2, table2.column1,....

2. FROM table1
3. LEFT JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

2. FROM EMPLOYEE
3. LEFT JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development
 Russell NULL

Marry NULL

3. RIGHT JOIN

In SQL, RIGHT JOIN returns all the values from the values from the rows of right table and the matched
values from the left table. If there is no matching in both tables, it will return NULL.

Syntax

1. SELECT table1.column1, table1.column2, table2.column1,....

2. FROM table1
3. RIGHT JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

2. FROM EMPLOYEE
3. RIGHT JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

4. FULL JOIN

In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join tables have all the
records from both tables. It puts NULL on the place of matches not found.
 Syntax

1. SELECT table1.column1, table1.column2, table2.column1,....

2. FROM table1
3. FULL JOIN table2
4. ON table1.matching_column = table2.matching_column;

Query

1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT

2. FROM EMPLOYEE
3. FULL JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT

Angelina Testing

Robert Development

Christian Designing

Kristen Development

Russell NULL

Marry NULL

5. SQL Cross Join

 Join operation in SQL is used to combine multiple tables together into a single table.
 If we use the cross join to combine two different tables, then we will get the Cartesian product of the sets of rows from the joined
table. When each row of the first table is combined with each row from the second table, it is known as Cartesian join or cross join.
 After performing the cross join operation, the total number of rows present in the final table will be equal to the product of the
number of rows present in table 1 and the number of rows present in table 2.
 For example:
If there are two records in table 1 and three records in table 2, then after performing cross join operation, we will get six records in
the final table.
 Let us take a look at the syntax of writing a query to perform the cross join operation in SQL.
 1. SELECT TableName1.columnName1, TableName2.columnName2 FROM TableName1 CROSS JOIN TableName2 ON TableName1.Colu
mnName = TableName2.ColumnName;

Now let us see take a deeper dive into the cross join in SQL with the help of examples. All the queries in the examples will be written using the
MySQL database.

Consider we have the following tables with the given data:

Table 1: MatchScore

Player Department_id Goals

Franklin 1 2

Alan 1 3

Priyanka 2 2

Rajesh 3 5

Table 2: Departments

Department_id Department_name

1 IT

2 HR

3 Marketing

Table 3: employee

EmployeeID Employee_Name Employee_Salary

1 Arun Tiwari 50000

2 Sachin Rathi 64000

3 Harshal Pathak 48000

4 Arjun Kuwar 46000

5 Sarthak Gada 62000

Table 4: department

DepartmentID Department_Name Employee_ID

1 Production 1
2 Sales 3

3 Marketing 4

4 Accounts 5

Table 5: loan

LoanID Branch Amount

1 B1 15000

2 B2 10000

3 B3 20000

4 B4 100000

Table 6: borrower

CustID CustName LoanID

1 Sonakshi Dixit 1

2 Shital Garg 4

3 Swara Joshi 5

4 Isha Deshmukh 2

Table 7: customer

Customer_ID Name Age Salary

1 Aryan Jain 51 56000

2 Arohi Dixit 21 25000

3 Vineet Garg 24 31000

Table 8: orders

Order_ID Order_Date Cutomer_ID Amount

1 2012-01-20 2 3000

2 2012-05-18 2 2000
3 2012-06-28 3 4000

Example 1:

Write a query to perform the cross join operation considering the MatchScore table as the left table and the Departments table as the right table.

Query:

1. SELECT * FROM MatchScore CROSS JOIN Departments;

We have used the SELECT command with the asterisk to retrieve all the columns present in the MatchScore and Departments table. Then we
have used the CROSS JOIN keyword to perform the cross join operation on the MatchScore and Departments table. Since there are 4 records in
the MatchScore and 3 records in the Departments table, after performing the cross join operation, we will get 12 rows.

After executing this query, you will find the following result:

Player Department_id Goals Depatment_id Department_name

Franklin 1 2 1 IT

Alan 1 3 1 IT

Priyanka 2 2 1 IT

Rajesh 3 5 1 IT

Franklin 1 2 2 HR

Alan 1 3 2 HR

Priyanka 2 2 2 HR

Rajesh 3 5 2 HR

Franklin 1 2 3 Marketing

Alan 1 3 3 Marketing

Priyanka 2 2 3 Marketing

Rajesh 3 5 3 Marketing

Each row from the MatchScore table is combined with each row of the Departments table. Since there are four records in the MatchScore and
three records in the Departments table, we have got 12 rows in the final table after performing the cross join operation.

SQL PRIMARY KEY

A column or columns is called primary key (PK) that uniquely identifies each row in the table.

If you want to create a primary key, you should define a PRIMARY KEY constraint when you create or modify a table.

When multiple columns are used as a primary key, it is known as composite primary key.
In designing the composite primary key, you should use as few columns as possible. It is good for storage and performance both, the more
columns you use for primary key the more storage space you require.

Inn terms of performance, less data means the database can process faster.

Points to remember for primary key:

 Primary key enforces the entity integrity of the table.

 Primary key always has unique data.
 A primary key length cannot be exceeded than 900 bytes.
 A primary key cannot have null value.
 There can be no duplicate value for a primary key.
 A table can contain only one primary key constraint.

When we specify a primary key constraint for a table, database engine automatically creates a unique index for the primary key column.

Main advantage of primary key:

The main advantage of this uniqueness is that we get fast access.

In oracle, it is not allowed for a primary key to contain more than 32 columns.

SQL primary key for one column:

The following SQL command creates a PRIMARY KEY on the "S_Id" column when the "students" table is created.

MySQL:

1. CREATE TABLE students

2. (
3. S_Id int NOT NULL,
4. LastName varchar (255) NOT NULL,
5. FirstName varchar (255),
6. Address varchar (255),
7. City varchar (255),
8. PRIMARY KEY (S_Id)
9. )

SQL Server, Oracle, MS Access:

1. CREATE TABLE students

2. (
3. S_Id int NOT NULL PRIMARY KEY,
4. LastName varchar (255) NOT NULL,
5. FirstName varchar (255),
6. Address varchar (255),
7. City varchar (255),
8. )

SQL primary key for multiple columns:

MySQL, SQL Server, Oracle, MS Access:

1. CREATE TABLE students

2. (
3. S_Id int NOT NULL,
4. LastName varchar (255) NOT NULL,
5. FirstName varchar (255),
6. Address varchar (255),
7. City varchar (255),
8. CONSTRAINT pk_StudentID PRIMARY KEY (S_Id, LastName)
9. )

Note:you should note that in the above example there is only one PRIMARY KEY (pk_StudentID). However it is made up of two columns (S_Id
and LastName).

SQL primary key on ALTER TABLE

When table is already created and you want to create a PRIMARY KEY constraint on the "S_Id" column you should use the following SQL:

Primary key on one column:

1. ALTER TABLE students

2. ADD PRIMARY KEY (S_Id)

Primary key on multiple column:

1. ALTER TABLE students

2. ADD CONSTRAINT pk_StudentID PRIMARY KEY (S_Id,LastName)

When you use ALTER TABLE statement to add a primary key, the primary key columns must not contain NULL values (when the table was first
created).

How to DROP a PRIMARY KEY constraint?

If you want to DROP (remove) a primary key constraint, you should use following syntax:

MySQL:

1. ALTER TABLE students

2. DROP PRIMARY KEY

SQL Server / Oracle / MS Access:

1. ALTER TABLE students

2. DROP CONSTRAINT pk_StudentID

SQL FOREIGN KEY

In the relational databases, a foreign key is a field or a column that is used to establish a link between two tables.

In simple words you can say that, a foreign key in one table used to point primary key in another table.

Let us take an example to explain it:

Here are two tables first one is students table and second is orders table.

Here orders are given by students.

First table:

S_Id LastName FirstName CITY

1 MAURYA AJEET ALLAHABAD
2 JAISWAL RATAN GHAZIABAD
3 ARORA SAUMYA MODINAGAR

Second table:

O_Id OrderNo S_Id

1 99586465 2
2 78466588 2
3 22354846 3
4 57698656 1

Here you see that "S_Id" column in the "Orders" table points to the "S_Id" column in "Students" table.

 The "S_Id" column in the "Students" table is the PRIMARY KEY in the "Students" table.
 The "S_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The foreign key constraint is generally prevents action that destroy links between tables.

It also prevents invalid data to enter in foreign key column.

SQL FOREIGN KEY constraint ON CREATE TABLE:

(Defining a foreign key constraint on single column)

To create a foreign key on the "S_Id" column when the "Orders" table is created:

MySQL:

1. CREATE TABLE orders

2. (
3. O_Id int NOT NULL,
4. Order_No int NOT NULL,
5. S_Id int,
6. PRIMAY KEY (O_Id),
7. FOREIGN KEY (S_Id) REFERENCES Persons (S_Id)
8. )

SQL Server /Oracle / MS Access:

1. CREATE TABLE Orders

2. (
3. O_Id int NOT NULL PRIMAY KEY,
4. Order_No int NOT NULL,
5. S_Id int FOREIGN KEY REFERENCES persons (S_Id)
6. )

SQL FOREIGN KEY constraint for ALTER TABLE:

If the Order table is already created and you want to create a FOREIGN KEY constraint on the "S_Id" column, you should write the following
syntax:

Defining a foreign key constraint on single column:

MySQL / SQL Server / Oracle / MS Access:

1. ALTER TABLE Orders

2. ADD CONSTRAINT fk_PerOrders
3. FOREIGN KEY(S_Id)
4. REFERENCES Students (S_Id)

DROP SYNTAX for FOREIGN KEY COSTRAINT:

If you want to drop a FOREIGN KEY constraint, use the following syntax:

MySQL:

1. ALTER TABLE Orders

2. ROP FOREIGN KEY fk_PerOrders

SQL Server / Oracle / MS Access:

1. ALTER TABLE Orders

2. DROP CONSTRAINT fk_PerOrders

Difference between primary key and foreign key in SQL:

These are some important difference between primary key and foreign key in SQL-

Primary key cannot be null on the other hand foreign key can be null.

Primary key is always unique while foreign key can be duplicated.

Primary key uniquely identify a record in a table while foreign key is a field in a table that is primary key in another table.

There is only one primary key in the table on the other hand we can have more than one foreign key in the table.

By default primary key adds a clustered index on the other hand foreign key does not automatically create an index, clustered or non-clustered.
You must manually create an index for foreign key.

SQL Composite Key

A composite key is a combination of two or more columns in a table that can be used to uniquely identify each row in the table when the columns
are combined uniqueness is guaranteed, but when it taken individually it does not guarantee uniqueness.

Sometimes more than one attributes are needed to uniquely identify an entity. A primary key that is made by the combination of more than one
attribute is known as a composite key.

In other words we can say that:

Composite key is a key which is the combination of more than one field or column of a given table. It may be a candidate key or primary key.

Columns that make up the composite key can be of different data types.

SQL Syntax to specify composite key:

 1. CREATE TABLE TABLE_NAME
2. (COLUMN_1, DATA_TYPE_1,
3. COLUMN_2, DATA_TYPE_2,
4. ???
5. PRIMARY KEY (COLUMN_1, COLUMN_2, ...));

In all cases composite key created consist of COLUMN1 and COLUMN2.

MySQL:

1. CREATE TABLE SAMPLE_TABLE

2. (COL1 integer,
3. COL2 varchar(30),
4. COL3 varchar(50),
5. PRIMARY KEY (COL1, COL2));

MySQL:

1. CREATE TABLE SAMPLE_TABLE

2. (COL1 integer,
3. COL2 varchar(30),
4. COL3 varchar(50),
5. PRIMARY KEY (COL1, COL2));

Oracle:

1. CREATE TABLE SAMPLE_TABLE

2. CREATE TABLE SAMPLE_TABLE
3. (COL1 integer,
4. COL2 varchar(30),
5. COL3 varchar(50),
6. PRIMARY KEY (COL1, COL2));

SQL Server:

Let's see the Syntax for the select top statement:

1. CREATE TABLE SAMPLE_TABLE

2. (COL1 integer,
3. COL2 nvarchar(30),
4. COL3 nvarchar(50),
5. PRIMARY KEY (COL1, COL2));

Unique Key in SQL

A unique key is a set of one or more than one fields/columns of a table that uniquely identify a record in a database table.

You can say that it is little like primary key but it can accept only one null value and it cannot have duplicate values.

The unique key and primary key both provide a guarantee for uniqueness for a column or a set of columns.

There is an automatically defined unique key constraint within a primary key constraint.

There may be many unique key constraints for one table, but only one PRIMARY KEY constraint for one table.

SQL UNIQUE KEY constraint on CREATE TABLE:

If you want to create a UNIQUE constraint on the "S_Id" column when the "students" table is created, use the following SQL syntax:

SQL Server / Oracle / MS Access:

(Defining a unique key constraint on single column):

1. CREATE TABLE students

2. (
3. S_Id int NOT NULL UNIQUE,
4. LastName varchar (255) NOT NULL,
5. FirstName varchar (255),
6. City varchar (255)
7. )

MySQL:

1. CREATE TABLE students

2. CREATE TABLE students
3. (
4. S_Id int NOT NULL,
5. LastName varchar (255) NOT NULL,
6. FirstName varchar (255),
7. City varchar (255),
8. UNIQUE (S_Id)
9. )

(Defining a unique key constraint on multiple columns):

MySQL / SQL Server / Oracle / MS Access:

1. CREATE TABLE students

2. (
3. S_Id int NOT NULL,
4. LastName varchar (255) NOT NULL,
5. FirstName varchar (255),
6. City varchar (255),
7. CONSTRAINT uc_studentId UNIQUE (S_Id, LastName)
8. )

SQL UNIQUE KEY constraint on ALTER TABLE:

If you want to create a unique constraint on "S_Id" column when the table is already created, you should use the following SQL syntax:

(Defining a unique key constraint on single column):

MySQL / SQL Server / Oracle / MS Access:

1. ALTER TABLE students

2. ADD UNIQUE (S_Id)

(Defining a unique key constraint on multiple columns):

MySQL / SQL Server / Oracle / MS Access:

1. ALTER TABLE students

2. ADD CONSTRAINT uc_StudentId UNIQUE (S_Id, LastName)

DROP SYNTAX FOR A FOREIGN KEY constraint:

If you want to drop a UNIQUE constraint, use the following SQL syntax:

MySQL:

1. ALTER TABLE students

2. DROP INDEX uc_studentID
SQL Server / Oracle / MS Access:

1. ALTER TABLE students

2. DROP CONSTRAINT uc_studentID

Alternate Key in SQL

Alternate key is a secondary key it can be simple to understand by an example:

Let's take an example of student it can contain NAME, ROLL NO., ID and CLASS.

Here ROLL NO. is primary key and rest of all columns like NAME, ID and CLASS are alternate keys.

If a table has more than one candidate key, one of them will become the primary key and rest of all are called alternate keys.

In simple words, you can say that any of the candidate key which is not part of primary key is called an alternate key. So when we talk about
alternate key, the column may not be primary key but still it is a unique key in the column.

SQL vs. NoSQL

There are a lot of databases used today in the industry. Some are SQL databases, some are NoSQL databases. The conventional database is SQL
database system that uses tabular relational model to represent data and their relationship. The NoSQL database is the newer one database that
provides a mechanism for storage and retrieval of data other than tabular relations model used in relational databases.

Following is a list of differences between SQL and NoSQL database:

Index SQL NoSQL

Databases are categorized as Relational Database NoSQL databases are categorized as Non-relational or distributed database
1)
Management System (RDBMS). system.
SQL databases have fixed or static or predefined
2) NoSQL databases have dynamic schema.
schema.
SQL databases display data in form of tables so it is NoSQL databases display data as collection of key-value pair, documents, graph
3)
known as table-based database. databases or wide-column stores.
4) SQL databases are vertically scalable. NoSQL databases are horizontally scalable.
SQL databases use a powerful language "Structured In NoSQL databases, collection of documents are used to query the data. It is
5)
Query Language" to define and manipulate the data. also called unstructured query language. It varies from database to database.
NoSQL databases are not so good for complex queries because these are not as
6) SQL databases are best suited for complex queries.
powerful as SQL queries.
SQL databases are not best suited for hierarchical data
7) NoSQL databases are best suited for hierarchical data storage.
storage.
MySQL, Oracle, Sqlite, PostgreSQL and MS-SQL etc. MongoDB, BigTable, Redis, RavenDB, Cassandra, Hbase, Neo4j, CouchDB
8)
are the example of SQL database. etc. are the example of nosql database

Relational Algebra
Relational algebra is a procedural query language. It gives a step by step process to obtain the result of the query. It uses operators to perform
queries.

Types of Relational operation

1. Select Operation:

 The select operation selects tuples that satisfy a given predicate.

 It is denoted by sigma (σ).

1. Notation: σ p(r)

Where:

σ is used for selection prediction

r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR and NOT. These relational can use as relational operators like
=, ≠, ≥, <, >, ≤.

For example: LOAN Relation

BRANCH_NAME LOAN_NO AMOUNT

Downtown L-17 1000

Redwood L-23 2000

Perryride L-15 1500

Downtown L-14 1500

Mianus L-13 500

Roundhill L-11 900

Perryride L-16 1300

Input:

1. σ BRANCH_NAME="perryride" (LOAN)
Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500

Perryride L-16 1300

2. Project Operation:

 This operation shows the list of those attributes that we wish to appear in the result. Rest of the attributes are eliminated from the
table.
 It is denoted by ∏.

1. Notation: ∏ A1, A2, An (r)

Where

A1, A2, A3 is used as an attribute name of relation r.

Example: CUSTOMER RELATION

NAME STREET CITY

Jones Main Harrison

Smith North Rye

Hays Main Harrison

Curry North Rye

Johnson Alma Brooklyn

Brooks Senator Brooklyn

Input:

1. ∏ NAME, CITY (CUSTOMER)

Output:

NAME CITY

Jones Harrison

Smith Rye

Hays Harrison
Curry Rye

Johnson Brooklyn

Brooks Brooklyn

3. Union Operation:

 Suppose there are two tuples R and S. The union operation contains all the tuples that are either in R or S or both in R & S.
 It eliminates the duplicate tuples. It is denoted by ∪.

1. Notation: R ∪ S

A union operation must hold the following condition:

 R and S must have the attribute of the same number.

 Duplicate tuples are eliminated automatically.

Example:

DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO

Johnson A-101

Smith A-121

Mayes A-321

Turner A-176

Johnson A-273

Jones A-472

Lindsay A-284

BORROW RELATION

CUSTOMER_NAME LOAN_NO

Jones L-17

Smith L-23

Hayes L-15

Jackson L-14
Curry L-93

Smith L-11

Williams L-17

Input:

1. ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Johnson

Smith

Hayes

Turner

Jones

Lindsay

Jackson

Curry

Williams

Mayes

4. Set Intersection:

 Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in both R & S.
 It is denoted by intersection ∩.

1. Notation: R ∩ S

Example: Using the above DEPOSITOR table and BORROW table

Input:

1. ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)

Output:
CUSTOMER_NAME

Smith

Jones

5. Set Difference:

 Suppose there are two tuples R and S. The set intersection operation contains all tuples that are in R but not in S.
 It is denoted by intersection minus (-).

1. Notation: R - S

Example: Using the above DEPOSITOR table and BORROW table

Input:

1. ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Jackson

Hayes

Willians

Curry

6. Cartesian product

 The Cartesian product is used to combine each row in one table with each row in the other table. It is also known as a cross product.
 It is denoted by X.

1. Notation: E X D

Example:

EMPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A

2 Harry C

3 John B
DEPARTMENT

DEPT_NO DEPT_NAME

A Marketing

B Sales

C Legal

Input:

1. EMPLOYEE X DEPARTMENT

Output:

EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME

1 Smith A A Marketing

1 Smith A B Sales

1 Smith A C Legal

2 Harry C A Marketing

2 Harry C B Sales

2 Harry C C Legal

3 John B A Marketing

3 John B B Sales

3 John B C Legal

7. Rename Operation:
The rename operation is used to rename the output relation. It is denoted by rho (ρ).

Example: We can use the rename operator to rename STUDENT relation to STUDENT1.

1. ρ(STUDENT1, STUDENT)

Relational Calculus
There is an alternate way of formulating queries known as Relational Calculus. Relational calculus is a non-procedural query language. In the
non-procedural query language, the user is concerned with the details of how to obtain the end results. The relational calculus tells what to do but
never explains how to do. Most commercial relational languages are based on aspects of relational calculus including SQL-QBE and QUEL.
Why it is called Relational Calculus?
It is based on Predicate calculus, a name derived from branch of symbolic language. A predicate is a truth-valued function with arguments. On
substituting values for the arguments, the function result in an expression called a proposition. It can be either true or false. It is a tailored version
of a subset of the Predicate Calculus to communicate with the relational database.

Many of the calculus expressions involves the use of Quantifiers. There are two types of quantifiers:

 Universal Quantifiers: The universal quantifier denoted by ∀ is read as for all which means that in a given set of tuples exactly all
tuples satisfy a given condition.
 Existential Quantifiers: The existential quantifier denoted by ∃ is read as for all which means that in a given set of tuples there is at
least one occurrences whose value satisfy a given condition.

Before using the concept of quantifiers in formulas, we need to know the concept of Free and Bound Variables.

A tuple variable t is bound if it is quantified which means that if it appears in any occurrences a variable that is not bound is said to be free.

Free and bound variables may be compared with global and local variable of programming languages.

Types of Relational calculus:

1. Tuple Relational Calculus (TRC)

It is a non-procedural query language which is based on finding a number of tuple variables also known as range variable for which predicate
holds true. It describes the desired information without giving a specific procedure for obtaining that information. The tuple relational calculus is
specified to select the tuples in a relation. In TRC, filtering variable uses the tuples of a relation. The result of the relation can have one or more
tuples.

Notation:

A Query in the tuple relational calculus is expressed as following notation

1. {T | P (T)} or {T | Condition (T)}

Where

T is the resulting tuples

P(T) is the condition used to fetch T.

For example:

1. { T.name | Author(T) AND T.article = 'database' }

Output: This query selects the tuples from the AUTHOR relation. It returns a tuple with 'name' from Author who has written an article on
'database'.

TRC (tuple relation calculus) can be quantified. In TRC, we can use Existential (∃) and Universal Quantifiers (∀).

For example:

1. { R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)}

Output: This query will yield the same result as the previous one.

2. Domain Relational Calculus (DRC)

The second form of relation is known as Domain relational calculus. In domain relational calculus, filtering variable uses the domain of attributes.
Domain relational calculus uses the same operators as tuple calculus. It uses logical connectives ∧ (and), ∨ (or) and ┓ (not). It uses Existential
(∃) and Universal Quantifiers (∀) to bind the variable. The QBE or Query by example is a query language related to domain relational calculus.

Notation:

1. { a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}

Where

a1, a2 are attributes

P stands for formula built by inner attributes

For example:

1. {< article, page, subject > | ∈ javatpoint ∧ subject = 'database'}

Output: This query will yield the article, page, and subject from the relational javatpoint, where the subject is a database.

Constraints in SQL
Constraints in SQL mean we are applying certain conditions or restrictions on the database. This further means that before inserting data into the
database, we are checking for some conditions. If the condition we have applied to the database holds true for the data which is to be inserted,
then only the data will be inserted into the database tables.

Constraints in SQL can be categorized into two types:

1. Column Level Constraint:

Column Level Constraint is used to apply a constraint on a single column.
2. Table Level Constraint:
Table Level Constraint is used to apply a constraint on multiple columns.

Some of the real-life examples of constraints are as follows:

1. Every person has a unique email id. This is because while creating an email account for any user, the email providing services such as
Gmail, Yahoo or any other email providing service will always check for the availability of the email id that the user wants for
himself. If some other user already takes the email id that the user wants, then that id cannot be assigned to another user. This
simply means that no two users can have the same email ids on the same email providing service. So, here the email id is the
constraint on the database of email providing services.
2. Whenever we set a password for any system, there are certain constraints that are to be followed. These constraints may include
the following:
 o There must be one uppercase character in the password.
o Password must be of at least eight characters in length.
o Password must contain at least one special symbol.

Constraints available in SQL are:

1. NOT NULL
2. UNIQUE
3. PRIMARY KEY
4. FOREIGN KEY
5. CHECK
6. DEFAULT
7. CREATE INDEX

Now let us try to understand the different constraints available in SQL in more detail with the help of examples. We will use MySQL database for
writing all the queries.

1. NOT NULL

 NULL means empty, i.e., the value is not available.

 Whenever a table's column is declared as NOT NULL, then the value for that column cannot be empty for any of the table's records.
 There must exist a value in the column to which the NOT NULL constraint is applied.

NOTE: NULL does not mean zero. NULL means empty column, not even zero.

Syntax to apply the NOT NULL constraint during table creation:

1. CREATE TABLE TableName (ColumnName1 datatype NOT NULL, ColumnName2 datatype,…., ColumnNameN datatype);

Example:

Create a student table and apply a NOT NULL constraint on one of the table's column while creating a table.

1. CREATE TABLE student(StudentID INT NOT NULL, Student_FirstName VARCHAR(20), Student_LastName VARCHAR(20), Student_Pho
neNumber VARCHAR(20), Student_Email_ID VARCHAR(40));

To verify that the not null constraint is applied to the table's column and the student table is created successfully, we will execute the following
query:

1. mysql> DESC student;

Syntax to apply the NOT NULL constraint on an existing table's column:

 1. ALTER TABLE TableName CHANGE Old_ColumnName New_ColumnName Datatype NOT NULL;

Example:

Consider we have an existing table student, without any constraints applied to it. Later, we decided to apply a NOT NULL constraint to one of the
table's column. Then we will execute the following query:

1. mysql> ALTER TABLE student CHANGE StudentID StudentID INT NOT NULL;

To verify that the not null constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC student;

2. UNIQUE

 Duplicate values are not allowed in the columns to which the UNIQUE constraint is applied.
 The column with the unique constraint will always contain a unique value.
 This constraint can be applied to one or more than one column of a table, which means more than one unique constraint can exist
on a single table.
 Using the UNIQUE constraint, you can also modify the already created tables.

Syntax to apply the UNIQUE constraint on a single column:

1. CREATE TABLE TableName (ColumnName1 datatype UNIQUE, ColumnName2 datatype,…., ColumnNameN datatype);

Example:

Create a student table and apply a UNIQUE constraint on one of the table's column while creating a table.

1. mysql> CREATE TABLE student(StudentID INT UNIQUE, Student_FirstName VARCHAR(20), Student_LastName VARCHAR(20), Student
_PhoneNumber VARCHAR(20), Student_Email_ID VARCHAR(40));

To verify that the unique constraint is applied to the table's column and the student table is created successfully, we will execute the following
query:

1. mysql> DESC student;

Syntax to apply the UNIQUE constraint on more than one column:

1. CREATE TABLE TableName (ColumnName1 datatype, ColumnName2 datatype,…., ColumnNameN datatype, UNIQUE (ColumnName1,
ColumnName 2));

Example:

Create a student table and apply a UNIQUE constraint on more than one table's column while creating a table.

1. mysql> CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Student_LastName VARCHAR(20), Student_PhoneN
umber VARCHAR(20), Student_Email_ID VARCHAR(40), UNIQUE(StudentID, Student_PhoneNumber));

To verify that the unique constraint is applied to more than one table's column and the student table is created successfully, we will execute the
following query:

1. mysql> DESC student;

Syntax to apply the UNIQUE constraint on an existing table's column:

1. ALTER TABLE TableName ADD UNIQUE (ColumnName);

Example:

Consider we have an existing table student, without any constraints applied to it. Later, we decided to apply a UNIQUE constraint to one of the
table's column. Then we will execute the following query:

1. mysql> ALTER TABLE student ADD UNIQUE (StudentID);

To verify that the unique constraint is applied to the table's column and the student table is created successfully, we will execute the following
query:

1. mysql> DESC student;

3. PRIMARY KEY

 PRIMARY KEY Constraint is a combination of NOT NULL and Unique constraints.

 NOT NULL constraint and a UNIQUE constraint together forms a PRIMARY constraint.
 The column to which we have applied the primary constraint will always contain a unique value and will not allow null values.

Syntax of primary key constraint during table creation:

1. CREATE TABLE TableName (ColumnName1 datatype PRIMARY KEY, ColumnName2 datatype,…., ColumnNameN datatype);

Example:

Create a student table and apply the PRIMARY KEY constraint while creating a table.

1. mysql> CREATE TABLE student(StudentID INT PRIMARY KEY, Student_FirstName VARCHAR(20), Student_LastName VARCHAR(20), St
udent_PhoneNumber VARCHAR(20), Student_Email_ID VARCHAR(40));

To verify that the primary key constraint is applied to the table's column and the student table is created successfully, we will execute the
following query:

1. mysql> DESC student;

Syntax to apply the primary key constraint on an existing table's column:

1. ALTER TABLE TableName ADD PRIMARY KEY (ColumnName);

Example:

Consider we have an existing table student, without any constraints applied to it. Later, we decided to apply the PRIMARY KEY constraint to the
table's column. Then we will execute the following query:
 1. mysql> ALTER TABLE student ADD PRIMARY KEY (StudentID);

To verify that the primary key constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC student;

4. FOREIGN KEY

 A foreign key is used for referential integrity.

 When we have two tables, and one table takes reference from another table, i.e., the same column is present in both the tables and
that column acts as a primary key in one table. That particular column will act as a foreign key in another table.

Syntax to apply a foreign key constraint during table creation:

1. CREATE TABLE tablename(ColumnName1 Datatype(SIZE) PRIMARY KEY, ColumnNameN Datatype(SIZE), FOREIGN KEY( ColumnName
) REFERENCES PARENT_TABLE_NAME(Primary_Key_ColumnName));

Example:

Create an employee table and apply the FOREIGN KEY constraint while creating a table.

To create a foreign key on any table, first, we need to create a primary key on a table.

1. mysql> CREATE TABLE employee (Emp_ID INT NOT NULL PRIMARY KEY, Emp_Name VARCHAR (40), Emp_Salary VARCHAR (40));

To verify that the primary key constraint is applied to the employee table's column, we will execute the following query:

1. mysql> DESC employee;

Now, we will write a query to apply a foreign key on the department table referring to the primary key of the employee table, i.e., Emp_ID.
 1. mysql> CREATE TABLE department(Dept_ID INT NOT NULL PRIMARY KEY, Dept_Name VARCHAR(40), Emp_ID INT NOT NULL, FOREIG
N KEY(Emp_ID) REFERENCES employee(Emp_ID));

To verify that the foreign key constraint is applied to the department table's column, we will execute the following query:

1. mysql> DESC department;

Syntax to apply the foreign key constraint with constraint name:

1. CREATE TABLE tablename(ColumnName1 Datatype PRIMARY KEY, ColumnNameN Datatype(SIZE), CONSTRAINT ConstraintName FOR
EIGN KEY( ColumnName ) REFERENCES PARENT_TABLE_NAME(Primary_Key_ColumnName));

Example:

Create an employee table and apply the FOREIGN KEY constraint with a constraint name while creating a table.

To create a foreign key on any table, first, we need to create a primary key on a table.

1. mysql> CREATE TABLE employee (Emp_ID INT NOT NULL PRIMARY KEY, Emp_Name VARCHAR (40), Emp_Salary VARCHAR (40));

To verify that the primary key constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC employee;

Now, we will write a query to apply a foreign key with a constraint name on the department table referring to the primary key of the employee
table, i.e., Emp_ID.

1. mysql> CREATE TABLE department(Dept_ID INT NOT NULL PRIMARY KEY, Dept_Name VARCHAR(40), Emp_ID INT NOT NULL, CONST
RAINT emp_id_fk FOREIGN KEY(Emp_ID) REFERENCES employee(Emp_ID));
To verify that the foreign key constraint is applied to the department table's column, we will execute the following query:

1. mysql> DESC department;

Syntax to apply the foreign key constraint on an existing table's column:

1. ALTER TABLE Parent_TableName ADD FOREIGN KEY (ColumnName) REFERENCES Child_TableName (ColumnName);

Example:

Consider we have an existing table employee and department. Later, we decided to apply a FOREIGN KEY constraint to the department table's
column. Then we will execute the following query:

1. mysql> DESC employee;

1. mysql> ALTER TABLE department ADD FOREIGN KEY (Emp_ID) REFERENCES employee (Emp_ID);

To verify that the foreign key constraint is applied to the department table's column, we will execute the following query:

1. mysql> DESC department;

5. CHECK

 Whenever a check constraint is applied to the table's column, and the user wants to insert the value in it, then the value will first be
checked for certain conditions before inserting the value into that column.
 For example: if we have an age column in a table, then the user will insert any value of his choice. The user will also enter even a
negative value or any other invalid value. But, if the user has applied check constraint on the age column with the condition age
greater than 18. Then in such cases, even if a user tries to insert an invalid value such as zero or any other value less than 18, then
the age column will not accept that value and will not allow the user to insert it due to the application of check constraint on the age
column.
Syntax to apply check constraint on a single column:

1. CREATE TABLE TableName (ColumnName1 datatype CHECK (ColumnName1 Condition), ColumnName2 datatype,…., ColumnNameN
datatype);

Example:

Create a student table and apply CHECK constraint to check for the age less than or equal to 15 while creating a table.

1. mysql> CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Student_LastName VARCHAR(20), Student_PhoneN
umber VARCHAR(20), Student_Email_ID VARCHAR(40), Age INT CHECK( Age <= 15));

To verify that the check constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC student;

Syntax to apply check constraint on multiple columns:

1. CREATE TABLE TableName (ColumnName1 datatype, ColumnName2 datatype CHECK (ColumnName1 Condition AND ColumnName2
Condition),…., ColumnNameN datatype);

Example:

Create a student table and apply CHECK constraint to check for the age less than or equal to 15 and a percentage greater than 85 while creating a
table.

1. mysql> CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Student_LastName VARCHAR(20), Student_PhoneN
umber VARCHAR(20), Student_Email_ID VARCHAR(40), Age INT, Percentage INT, CHECK( Age <= 15 AND Percentage > 85));

To verify that the check constraint is applied to the age and percentage column, we will execute the following query:

1. mysql> DESC student;

Syntax to apply check constraint on an existing table's column:

1. ALTER TABLE TableName ADD CHECK (ColumnName Condition);

Example:

Consider we have an existing table student. Later, we decided to apply the CHECK constraint on the student table's column. Then we will execute
the following query:

1. mysql> ALTER TABLE student ADD CHECK ( Age <=15 );

To verify that the check constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC student;

6. DEFAULT

Whenever a default constraint is applied to the table's column, and the user has not specified the value to be inserted in it, then the default value
which was specified while applying the default constraint will be inserted into that particular column.

Syntax to apply default constraint during table creation:

1. CREATE TABLE TableName (ColumnName1 datatype DEFAULT Value, ColumnName2 datatype,…., ColumnNameN datatype);

Example:

Create a student table and apply the default constraint while creating a table.

1. mysql> CREATE TABLE student(StudentID INT, Student_FirstName VARCHAR(20), Student_LastName VARCHAR(20), Student_PhoneN
umber VARCHAR(20), Student_Email_ID VARCHAR(40) DEFAULT "[email protected]");
To verify that the default constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC student;

Syntax to apply default constraint on an existing table's column:

1. ALTER TABLE TableName ALTER ColumnName SET DEFAULT Value;

Example:

Consider we have an existing table student. Later, we decided to apply the DEFAULT constraint on the student table's column. Then we will
execute the following query:

1. mysql> ALTER TABLE student ALTER Student_Email_ID SET DEFAULT "[email protected]";

To verify that the default constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC student;

7. CREATE INDEX

CREATE INDEX constraint is used to create an index on the table. Indexes are not visible to the user, but they help the user to speed up the
searching speed or retrieval of data from the database.

Syntax to create an index on single column:

1. CREATE INDEX IndexName ON TableName (ColumnName 1);

Example:

Create an index on the student table and apply the default constraint while creating a table.
 1. mysql> CREATE INDEX idx_StudentID ON student (StudentID);

To verify that the create index constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC student;

Syntax to create an index on multiple columns:

1. CREATE INDEX IndexName ON TableName (ColumnName 1, ColumnName 2, ColumnName N);

Example:

1. mysql> CREATE INDEX idx_Student ON student (StudentID, Student_PhoneNumber);

To verify that the create index constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC student;

Syntax to create an index on an existing table:

1. ALTER TABLE TableName ADD INDEX (ColumnName);

Consider we have an existing table student. Later, we decided to apply the DEFAULT constraint on the student table's column. Then we will
execute the following query:

1. mysql> ALTER TABLE student ADD INDEX (StudentID);

To verify that the create index constraint is applied to the student table's column, we will execute the following query:

1. mysql> DESC student;

SQL | DIVISION
Division is typically required when you want to find out entities that are interacting with all
entities of a set of different type entities.
The division operator is used when we have to evaluate queries which contain the keyword ‘all’.
Some instances where division operator is used are:
 Which person has account in all the banks of a particular city?
 Which students have taken all the courses required to graduate?
In all these queries, the description after the keyword ‘all’ defines a set which contains some
elements and the final result contains those units who satisfy these requirements.
Important: Division is not supported by SQL implementations. However, it can be
represented using other operations.(like cross join, Except, In )
SQL Implementation of Division
Given two relations(tables): R(x,y) , S(y).
R and S : tables
x and y : column of R
y : column of S
R(x,y) div S(y) means gives all distinct values of x from R that are associated with all values of y
in S.
Computation of Division : R(x,y) div S(y)
Steps:
 Find out all possible combinations of S(y) with R(x) by computing R(x) x(cross join) S(y), say r1
 Subtract actual R(x,y) from r1, say r2
 x in r2 are those that are not associated with every value in S(y); therefore R(x)-r2(x) gives us x
that are associated with all values in S
Queries
1. Implementation 1:
2. SELECT * FROM R
3. WHERE x not in ( SELECT x FROM (
4. (SELECT x , y FROM (select y from S ) as p cross join
5. (select distinct x from R) as sp)
6. EXCEPT
7. (SELECT x , y FROM R) ) AS r );
8.
9. Implementation 2 : Using correlated subquery
10. SELECT * FROM R as sx
11. WHERE NOT EXISTS (
12. (SELECT p.y FROM S as p )
13. EXCEPT
14. (SELECT sp.y FROM R as sp WHERE sp.x = sx.x ) );
15.
Relational algebra
Using steps which is mention above:
All possible combinations
r1 ← πx(R) x S
x values with “incomplete combinations”,
r2x ← πx(r1-R)
and
result ← πx(R)-r2x

R div S = πx(R)- πx((πx(R) x S) – R)

Examples

Supply Schema

Here sid means supplierID and pid means partsID.

Tables: suppliers(sid,pid) , parts(pid)
1. Find suppliers that supply all parts.
Ans 1 : Using implementation 1
SELECT * FROM suppliers
WHERE sid not in ( SELECT sid FROM ( (SELECT sid, pid FROM (select
pid from parts) as p
cross join
(select distinct sid from supplies) as sp)
EXCEPT
(SELECT sid, pid FROM supplies)) AS r );
Ans 2: Using implementation 2
SELECT * FROM suppliers as s
WHERE NOT EXISTS (( SELECT p.pid FROM parts as p )
EXCEPT
(SELECT sp.pid FROM supplies sp WHERE sp.sid = s.sid ) );
Company schema

2. List employees who work on all projects controlled by dno=4.

Ans 1. Using implementation 1
SELECT * FROM employee AS e
WHERE ssn NOT IN (
SELECT essn FROM (
(SELECT essn, pno FROM (select pno from project where dno=4)
as p cross join (select distinct essn from works_on) as w)
EXCEPT (SELECT essn, pno FROM works_on)) AS r );
Ans 2. Using implementation 2
SELECT * FROM employee AS e
WHERE NOT EXISTS (
(SELECT pno FROM project WHERE dno = 4)
EXCEPT
(SELECT pno FROM works_on WHERE essn = e.ssn) );