SQL AND PL/SQL DBMS 2019 PATTERN SPPU UNIT NO 2

1
DBMS V . M. KANAVADE AISSMS COE PUNE
UNIT 2
SQL & PL/SQL

3
SQL is a database computer language designed for the retrieval and management of data in
a relational databases like MySQL, MS Access, SQL Server, Oracle, Sybase, Informix.
SQL is not a database management system, but it is a query language which is used to store
and retrieve the data from a database or in simple words SQL is a language that
communicates with databases.
SQL stands for Structured Query Language. SQL was developed in the 1970s by IBM
Computer Scientists.

SQL HISTORY
1970 − Dr. Edgar F. "Ted" Codd of IBM is known as the father of relational databases. He described a relational
model for databases.
1974 − Structured Query Language appeared.
1978 − IBM worked to develop Codd's ideas and released a product named System/R.
1986 − IBM developed the first prototype of relational database and standardized by ANSI. The first relational
database was released by Relational Software which later came to be known as Oracle.

5
Characteristics of SQL
• SQL is an ANSI and ISO standard computer language for creating and manipulating databases.
• SQL allows the user to create, update, delete, and retrieve data from a database.
• SQL is very simple and easy to learn.
• SQL works with database programs like DB2, Oracle, MS Access, Sybase, MS SQL Sever etc.

6
ADVANTAGES OF SQL
1. High speed: SQL is designed to retrieve and manipulate large amounts of data quickly and efficiently,
thanks to optimized operations and indexing techniques. This means tasks like searching, updating,
or deleting vast datasets are performed in a very short time.
2. No coding needed: You don't need traditional programming logic or lengthy code to get data from
databases using SQL. The commands are simple and English-like (for example: SELECT, INSERT,
UPDATE, DELETE). This makes it easy for non-programmers and beginners to use and understand.
3. Well defined standards: SQL is controlled by international standards (ANSI, ISO), ensuring that the
language works similarly across all major database systems (like MySQL, SQL Server, Oracle,
PostgreSQL). This consistency prevents confusion and allows for wide portability of SQL skills and
queries.
4. Portability : SQL can run on various computer systems, platforms, and devices—from PCs to servers.
It's also used across different database products with minimal adjustments, making your skills and
queries portable.

7
SQL DATATYPES
SQL DATATYPES
Numeric Data
Types
Character and
String Data
Types
Date and Time
Data Types
Binary Data
Types
Boolean Data
Types
Special Data
Types
Numeric data types are fundamental to database design and are used to store numbers, whether
they are integers, decimals, or floating-point numbers. These data types allow for mathematical
operations like addition, subtraction, multiplication, and division, which makes them essential for
managing financial, scientific, and analytical data. For example Int, FLOAT

8
SQL DATATYPES
SQL DATATYPES
Numeric Data
Types
Character and
String Data
Types
Date and Time
Data Types
Binary Data
Types
Boolean Data
Types
Special Data
Types
Character data types are used to store text or character-based data. The choice between fixed-length
and variable-length data types depends on the nature of your data.
For Example Char varchar

9
SQL DATATYPES
SQL DATATYPES
Numeric Data
Types
Character and
String Data
Types
Date and Time
Data Types
Binary Data
Types
Boolean Data
Types
Special Data
Types
SQL provides several data types for storing date and time information. They are essential for
managing timestamps, events, and time-based queries. DATE, TIME, DATETIME, TIMESTAMP

10
SQL DATATYPES
SQL DATATYPES
Numeric Data
Types
Character and
String Data
Types
Date and Time
Data Types
Binary Data
Types
Boolean Data
Types
Special Data
Types
For storing binary data—like images, files, or any data that isn’t just text or numbers.
Examples: BINARY, VARBINARY, BLOB

11
SQL DATATYPES
SQL DATATYPES
Numeric Data
Types
Character and
String Data
Types
Date and Time
Data Types
Binary Data
Types
Boolean Data
Types
Special Data
Types
Used for logical TRUE/FALSE values.
TRUE=1 , FALSE=0

12
SQL DATATYPES
SQL DATATYPES
Numeric Data
Types
Character and
String Data
Types
Date and Time
Data Types
Binary Data
Types
Boolean Data
Types
Special Data
Types
For specialized use-cases, beyond the standard numbers/text/dates. For Example: JSON, XML,

13
Categories Data Types
Numeric Data Type INT, FLOAT(m,d), DOUBLE(m,d),
DECIMAL(m,d)
Date and Time Data Type: DATE, DATETIME,TIME,
TIMESTAMP,YEAR
String Data Types: CHAR,VARCHAR,TEXT,
LONGTEXT,BINARY
Large Object Data Types (LOB) Data
Types:
TINYBLOB, BLOB, MEDIUMBLOB,
LONGTEXT

14
How to start MySQL
First install mysql then write following command on command prompt
mysql -u root –p
Then Enter Password, it will show you MySQL prompt : -u -username -p –password

15
SQL Commands
Data Definition Language (DDL)
DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
Data Manipulation Language
DML commands are used to modify the database. It is responsible for all form of changes in the
database.
Data Control Language
DCL commands are used to grant and take back authority from any database user.
Transaction Control Language
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.

16
SQL Commands
Data Definition Language (DDL)
DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc. or used to
define and modify the database structure of your tables or schema.
Some commands of DDL are:
CREATE – to create table (objects) in the database
ALTER – alters the structure of the database
DROP – delete table from the database
TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
COMMENT – add comments to the data dictionary
RENAME – rename a table

17
SQL Commands
DDLCommands for Table:
Create table
Syntax
Create table table_name (Col_name1 data_type(Size) , Col_name2 data_type(Size),…))
Example
Create table Stud(rno number(4),name varchar2(20), DOB date));

18
SQL Commands
DDLCommands for Table:
Describe table / Display Table :
Syntax
desc table_name;
Example
desc Stud;

19
SQL Commands
Show tables
This will list the tables in the current database:
Syntax
show tables;
Example
mysql> show tables;
customers orders
2 rows in set (0.00 sec)

20
SQL Commands
Alter table (add or modify)
Syntax:
alter table table_name add/modify/drop
column_name data_type(size)
Example
Alter table stud add age int(3);
Alter table stud modify name varchar2(30);
Alter table stud modify name char(20);
Alter table stud drop column age;

21
SQL Commands
Truncate Table
used to delete complete data from an existing table.
Syntax
TRUNCATE TABLE table_name;
Example
mysql> TRUNCATE TABLE stud;

22
SQL Commands
Rename Table
used to rename table.
Syntax
RENAME TABLE tbl_name TO new_tbl_name
Example
mysql> RENAME TABLE stud TO student ;

23
SQL Constraints
Constraints are the set of rules defined on tables to ensure data
integrity.
Primary key
Not null
Default
Unique
Check
Foreign key/reference key

24
SQL Constraints
Why Define Constraints?
• PRIMARY KEY: Uniqueness of rows.
• NOT NULL: Ensures mandatory data.
• UNIQUE: No duplicate values allowed.
• DEFAULT: Provides a default value if none is specified.

25
SQL Constraints
The UNIQUE constraint in MySQL does not allow to insert a duplicate value
in a column.
Syntax
CREATE TABLE table_name (col_name data_type (size) Unique);
Example
CREATE TABLE Stud (Rno number(4) Unique);

26
SQL Constraints
A NOT NULL constraint means that a data row must have a value for the
column specified as NOT NULL.
Syntax
CREATE TABLE table_name
(Col_name Data_type(size)not null, ... );
Example
Create table stud
(rollno number(4) ,name varchar2(20)not null);

27
SQL Constraints : Primary Key
Each table must normally contain a column or set of columns that uniquely
identifies rows of data that are stored in the table. This column or set of
columns is referred to as the primary key.
A table can have only one primary key.
Multiple columns can be clubbed under a composite primary key.
Primary key columns is combination of NOT NULL and UNIQUE.
Syntax
CREATE TABLE table_name ( Col_name Data_type(size)CONSTRAINT
constraint_name PRIMARY KEY, ... );
Example
Create table stud (rollno number(4)constraint pk1 primary key,name…)
Create table stud (rollno number(4) primary key, name ….)

28
SQL Constraints : DEFAULT
In a MySQL table, while inserting data into a table, if no value is supplied to a column,
then the column gets the value set as DEFAULT.
Syntax
CREATE TABLE table_name (col_name
data_type(size) DEFAULT ‘default_value’ );
Example
CREATE TABLE Stud (rno number(4) ,name varchar2(20), addr varchar(30) DEFAULT
‘Nashik ’ );

29
SQL Constraints : CHECK
In a MySQL table, A CHECK constraint controls the values in the associated column.
The CHECK constraint determines whether the value is valid or not.
Syntax
CREATE TABLE table_name (col_name data_type(size) Check
(condition) );
Example
CREATE TABLE Stud (rno number(4) CHECK (rollno BETWEEN 1
AND 60));
CREATE TABLE Stud (age number(4) CHECK (age>18));

30
SQL Constraints : FOREIGN KEY
A FOREIGN KEY in MySQL creates a link between two tables by one specific column of
both tables. The specified column in one table must be a PRIMARY KEY and referred by
the column of another table known as FOREIGN KEY.
Syntax
Create table table_name(col_name data_type(size)references table_name(col_name));
Example
Createtable stud1 (rollno number(4) references stud(rno));

31
DDL Commands for Table
Drop table
Syntax
Drop table table_name;
Example
Drop table stud;

32
Table Management
• A table is a collection of rows and columns used to store related data in a structured way.
• Columns represent fields (attributes), and rows represent records (data entries).
Creating Tables: CREATE TABLE statement, define columns and data types.
Modifying Tables: ALTER TABLE to add/modify/delete columns.
Deleting Tables: DROP TABLE statement.
Updating Data: UPDATE statement to modify existing records.

33
Table Management
CREATE TABLE Statement
• This SQL command builds a brand-new table in your database.
• You define the table’s name, and specify each column with a name and data type.
• Optionally, you define constraints like PRIMARY KEY, NOT NULL, UNIQUE to maintain data integrity.
Key Components While Creating a Table:
• Table Name: Identifier for the table.
• Columns: Each column has:
o Name (e.g., roll_no)
o Data Type (e.g., INT, VARCHAR(50), DATE)
o Constraints (e.g., PRIMARY KEY, NOT NULL)

34
Table Management
CREATE TABLE Statement
Table Name: Identifier for the table.
Columns:
Each column has:
Name (e.g., roll_no)
Data Type (e.g., INT, VARCHAR(50), DATE)
Constraints (e.g., PRIMARY KEY, NOT NULL)

35
Table Management
1. CREATE TABLE Statement
roll_no is an integer and serves as the primary key, so it uniquely identifies each student.
name is text limited to 50 characters and cannot be left empty.
dob stores date values.
marks stores integer values.

36
Table Management
2. Modifying Tables
Sometimes the table's requirements change — new fields to add, fields to remove, or properties to modify.
ALTER TABLE Statement: Used to change the structure of an existing table.
Common Modifications:
• Adding columns: New data attributes.
• Modifying columns: Change data type, size or constraints.
• Dropping columns: Remove unnecessary attributes.

37
Table Management
2. Modifying Tables
Syntax and Examples:
1. Add a new column: This command adds a new column called email that can
store up to 100 characters.
ALTER TABLE students ADD email VARCHAR(100);
2. Modify an existing column: Changes marks from INT to FLOAT to allow
decimal values.
ALTER TABLE students MODIFY marks FLOAT;

38
3. Drop a column:
ALTER TABLE students DROP COLUMN email;
Removes the email column and all associated data.

39
3. Deleting Tables
DROP TABLE Statement:
• Permanently deletes the entire table and all its data.
• Use with caution; this is a destructive operation.
Example:
DROP TABLE students;
• After execution, the students table no longer exists in the database.
• Any dependent objects like indexes, triggers, or views associated with the table may also be dropped or
invalidated.

40
4. Updating Data
UPDATE Statement:
Used to modify existing records in a table.
You specify which rows to change using the WHERE clause; if omitted, all rows will be updated
Basic Structure:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

41
Examples:
Update a specific student’s marks:
UPDATE students
SET marks = 95
WHERE roll_no = 3;
Updates the marks to 95 for the student with roll number 3.
Increase marks for all students by 5:
UPDATE students
SET marks = marks + 5;
Adds 5 to marks for every student (no WHERE clause).

43
SELECT Query and Clauses:
Retrieve specific data using SELECT, WHERE, GROUP BY, HAVING, ORDER BY, DISTINCT.
SELECT
Retrieves one or more columns from one or more tables.
Syntax:
SELECT column1, column2 FROM table_name;
You can use * to select all columns:
SELECT * FROM students;

44
Syntax:
SELECT attribute_list
FROM table_list
<WHERE condition>;
The attributes are those you want to see in the result,
the tables are those required for the query,
the condition is a boolean expression that specifies which tuples are to be retrieved by the query.
Example:
Select * from Stud;
Select * from Stud where rno=1;
Select Name, Address from Stud;
Select Name, Address from Stud where rno=1
Select Name from Stud where address=‘Nashik’;

45
Retrieve specific data using SELECT, WHERE , GROUP BY, HAVING, ORDER BY, DISTINCT.
WHERE
Used to filter rows before they are returned.
Only rows that make the condition true appear in the results.
You can use operators (=, <, >, LIKE, IN, BETWEEN, AND, OR, NOT) for flexible filtering.
Example:
SELECT name FROM students WHERE marks >= 80;
Only students with marks 80 or more are shown.

46
Multiple conditions:
SELECT name FROM students WHERE marks >= 80 AND class = '12A';

47
GROUP BY & Having Clause
Aggregates (collects together) rows that have the same value(s) in specified column(s).
Used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), MIN().
Lets you perform calculations for groups rather than the whole table or individual rows.
Example:
SELECT class, COUNT(*) AS num_students FROM students GROUP BY class;
Counts how many students are in each class.
Grouping by multiple columns:
SELECT class, city, AVG(marks) FROM students GROUP BY class, city;

48
GROUP BY & Having Clause
Having is similar to where, to give condition, but it can only work with group by
Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition ;

50
Retrieve specific data using ORDER BY
The ORDER BY keyword is used to sort the result-set by a specified column.
The ORDER BY keyword sort the records in ascending order by default.
If you want to sort the records in a descending order, you can use the DESC keyword.
Syntax
FROM table_name
ORDER BY column_name(s) ASC|DESC
Example
1. Select * from Stud order by rno desc;
2. Select * from Stud order by rno ;
3. Select * from Stud order by Name;

51
The DISTINCT keyword can be used to return only distinct (different) values.
Syntax
SELECT DISTINCT column_name(s) FROM table_name
Example : The "Persons" table:
SELECT distinct(Address) from Person
The result-set will look like this

52
LIKE Operator- search for a specified pattern in a column
The LIKE operator is used in a WHERE clause to search for a specified
pattern in a column
Syntax:
FROM table_name
WHERE column_name LIKE pattern
.

53
SQL Wildcards
SQL wildcards can substitute for one or more characters when
searching for data in a database.
SQL wildcards must be used with the LIKE operator.
.
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character

LIKE OPERATOR - Example
P_Id LastName Address
1 Heena Pune
2 Savita Pune
3 Sarika Bombay
SELECT LastName from Person where LastName like ‘S%’ ;
LastName
Savita
Sarika
"Persons" table
We use the following SELECT statement:
The result-set will look like this:

IN Operator- The IN operator allows you to specify multiple values in a WHERE clause.
Syntax
SELECT column_name(s) FROM table_name WHERE column_name IN
(value1,value2,...)
Example
SELECT * FROM Persons WHERE Address IN ('Pune','Bombay') The
result-set will look like this
P_Id Name Address
1 Harry Pune
2 Silverstone Nashik
3 Pitterson Bombay
The "Persons" table:
P_Id Name Address
1 Hansen Pune
3 Pitterson Bombay

Aggregate Functions
Aggregate /Group functions are built-in SQL functions that operate on groups of rows and return one value
for the entire group.
The Functions are as below:
MIN returns the smallest value in a given column
MAX returns the largest value in a given column
SUM returns the sum of the numeric values in a given column
AVG returns the average value of a given column
COUNT returns the total number of values in a given column
COUNT(*) returns the number of rows in a table

Count()- Syntax
COUNT(column_name)-The COUNT(column_name) function returns the number of
values (NULL values will not be counted) of the specified column:
Syntax
SELECT COUNT(column_name) FROM table_name
COUNT(*) = The COUNT(*) function returns the number of records in a table:
Syntax
SELECT COUNT(*) FROM table_name

Count()- Example
Consider an employee_tbl table as shown below :
SELECT COUNT(*) FROM employee_tbl ;
SELECT COUNT(*) FROM employee_tbl WHERE name="Zara";
Count(*)
7
Count(*)
2

Avg(),Min(),Max(),Sum()
Syntax
SELECT AVG(column_name) FROM table_name
Example
SELECT AVG(Marks) FROM Stud
Syntax
SELECT Min(column_name) FROM table_name
Example
SELECT Min(Marks) FROM Stud
Syntax of all other functions are similar.

60
Set Operators
UNION:
Combines results from all given SELECT queries.
Removes duplicate rows from the final output.
By default, sorts the results in ascending order.
Keeps all NULL values in the result.
UNION ALL :
Combines results from all queries.
Does NOT remove duplicates — if the same row appears in multiple queries, it appears multiple times in the
output.
Does not sort the results by default (sorting must be explicit if required).

Set Operator- Example Union and Union all
ENo Ename Addr
101 Jenny Mumbai
102 Akash Pune
103 Sona Pune
104 Minal Nashik
105 Raksh Jalgoan
PrNo Addr
10 Mumbai
20 Pune
30 Pune
SELECT City FROM Emp
UNION
SELECT City FROM Proj
ORDER BY City;
UNION All
ORDER BY City;
Emp Table
Proj Table
Addr
Jalgoan
Mumbai
Nashik
Pune
Addr
Jalgoan
Mumbai
Mumbai
Nashik
Pune
Pune
Pune
Pune
O/P O/P

62
Set Operators
Queries:
UNION:
UNION
ORDER BY City;
OUTPUT:
Jalgoan
Mumbai
Nashik
Pune
(Only unique cities, each shown once and sorted.)
Using UNION ALL:
UNION ALL
ORDER BY City;
OUTPUT:
Jalgoan
Mumbai
Mumbai
Nashik
Pune
Pune
Pune
Pune
(All cities shown, including duplicates. Sorting
not applied unless explicitly stated.)

Solve
Eid EName Address Salary Commision
1 Amit Pune 35000 5000
2 Sneha Pune 25000
3 Savita Nasik 28000 2000
4 Pooja Mumbai 19000
5 Sagar Mumbai 25000 3000
1. Find different locations from where employees belong to?
2. What is maximum and minimum salary?
3. Display the content of employee table according to the ascending order of salary amount.
4. Find the name of employee who lived in Nasik or Pune city.
5. Find the name of employees who does not get commission.
6. Change the city of Amit to Nashik.
7. Find the information of employees whose name starts with ‘A’.
8. Find the count of staff from Mumbai.
9. Find the count of staff from each city
10.Find the address from where employees are belonging as well as where projects are going on.
11.Find city wise minimum salary.
12.Find city wise maximum salary having maximum salary greater than 26000
13.Delete the employee who is having salary greater than 30,000.
Create Employee table, Project table and add rows shown below
PrNo Addr
10 Mumbai
20 Pune
30 Jalgoan

64
Many queries reference only a small proportion of the records in a table.
It is inefficient for the system to read every record to find a record with particular value
An index on an attribute of a relation is a data structure that allows the database system to find those
tuples in the relation that have a specified value for that attribute efficiently, without scanning through
all the tuples of the relation.
Syntax
1> Create Index index_name on table_name(column_name)
2> Alter table table_name add index index_name (column_name)
Example
1> Create Index n1 on Stud(Name)
2> Alter table Stud add Index n1 (name)
DDL Commands for Index

• Show Index
• Syntax
Show Index from table_name
• Example
Show Index from Stud;

• Drop Index
• Syntax
Alter table table_name drop Index
index_name
• Example
• Alter table Stud drop Index n1;

67
1. Create View
A View is like a “virtual table.” It’s not a real table but acts like one. It is created by saving a SQL SELECT
query with a name.
To simplify complex queries, show only specific columns/rows, or add security by restricting which data
users see.
Syntax:
CREATE VIEW view_name AS
SELECT column1, column2 FROM table_name WHERE condition;
Example:
CREATE VIEW HighScorers AS
SELECT name, marks FROM students WHERE marks > 80;
Now, you can use the view just like a table:
SELECT * FROM HighScorers;
DDL Commands for View

68
2. Show View
This means listing or describing existing views in your database.
The command to "show views" depends on your database system
SHOW FULL TABLES WHERE Table_type = 'VIEW’;
Or
SHOW CREATE VIEW view_name;
SQL Server:
SELECT * FROM INFORMATION_SCHEMA.VIEWS;

69
3. Drop View
This command deletes a view from the database. The underlying tables are NOT affected—only the
view is removed.
Syntax:
DROP VIEW view_name;
Example:
DROP VIEW HighScorers;

71
SQL JOINS
Join operations take two relations and return as a result another relation.
A join operation is a Cartesian product which requires that tuples in the two relations match (under some
condition).
It also specifies the attributes that are present in the result of the join .
The join operations are typically used as subquery expressions in the from clause.
Three types of joins:
• Natural join
• Inner join
•Outer join

72
SQL JOINS
SQL joins are powerful tools that allow you to retrieve data from multiple related tables in a single query.
Because relational databases store information in separate tables to reduce redundancy, joins are essential
for analyzing relationships.
1ST
NATURAL JOINS :
Natural join matches tuples with the same values for all common attributes, and retains only one copy of
each common column.
List the names of instructors along with the course ID of the courses that they taught
• select name, course_id
from students, takes
where student.ID = takes.ID;
Same query in SQL with “natural join” construct
• select name, course_id
from student natural join takes;

73
SQL JOINS
The from clause in can have multiple relations combined using natural join:
select A1, A2, … An from r1 natural join r2 natural join .. natural join rn where P ;
Example of
natural join
Database System Concepts - 7th Edition ©Silberschatz, Korth and Sudarshan

76
SQL JOINS
Beware of unrelated attributes with same name which get equated incorrectly
Example -- List the names of student's instructors along with the titles of courses that they have taken
• Correct version
select name, title
from student natural join takes, course
where takes.course_id = course.course_id;
• Incorrect version
select name, title
from student natural join takes natural join course;
This query omits all (student name, course title) pairs where
the student takes a course in a department other than the
student's own department.
The correct version (above), correctly outputs such pairs.

77
SQL JOINS
Outer Join
An Outer Join is a type of join that returns rows not only where there is a match between two tables based
on a join condition, but also rows from one or both tables that have no matching rows in the other table.
Rows without matches include NULL values for the columns of the other table.
This contrasts with an Inner Join which only returns rows where matches exist in both tables.
An extension of the join operation that avoids loss of information.
Computes the join and then adds tuples form one relation that does not match tuples in the other relation to
the result of the join. Uses null values.
Three forms of outer join:
• left outer join
• right outer join
• full outer join

MySQL Left Outer Join
• The LEFT OUTER JOIN returns all rows from the left hand table specified in the ON condition and
only those rows from the other table where the join condition is fulfilled.
• Syntax:
SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

Left Outer Join Example
RN
o
Name Address
1 Abhay Nashik
2 Sarika Pune
3 Riya Nashik
4 Sachin Manmad
Stud_Info Table
RNo Dbms Toc
1 50 45
2 67 65
3 76 55
5 70 50
Stud_Marks Table
SELECT Stud_Info.Rno,Name,Dbms,Toc FROM
Stud_Info LEFT JOIN Stud_Marks ON
Stud_Info.RNo= Stud_Marks.RNo
RNo Name Dbms Toc
1 Abhay 50 45
2 Sarika 67 65
3 Riya 76 55
4 Sachin 0 0
O/P

Right Outer Join
The MySQL Right Outer Join returns all rows from the RIGHT- hand table specified in the ON condition and only
those rows from the other table where he join condition is fulfilled.
OR
A RIGHT OUTER JOIN (commonly called RIGHT JOIN) in SQL returns all rows from the right table and the matching
rows from the left table based on the join condition. If a row in the right table does not have a matching row in the
left table, the result will include the right table’s row with NULLs for columns of the left table.
Returns every row from the right table.
Includes matching rows from the left table.
If no match in the left table, returns NULLs for its columns.
Useful when you want to keep all records of the right table and optionally combine matching records
from the left.

81
Syntax:
SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

Right Outer Join Example
RN
o
Name Address
1 Abhay Nashik
2 Sarika Pune
3 Riya Nashik
4 Sachin Manmad
Stud_Info Table
RNo Dbms Toc
1 50 45
2 67 65
3 76 55
5 70 50
Stud_Marks Table
SELECT Stud_Info.Rno,Name,Dbms,Toc FROM
Stud_Info RIGHT JOIN Stud_Marks ON
Stud_Info.RNo= Stud_Marks.RNo
RNo Name Dbms Toc
1 Abhay 50 45
2 Sarika 67 65
3 Riya 76 55
NULL NULL 70 50
O/P

Right Outer Join Example
RN
o
Name Address
1 Abhay Nashik
2 Sarika Pune
3 Riya Nashik
4 Sachin Manmad
Stud_Info Table
RNo Dbms Toc
1 50 45
2 67 65
3 76 55
5 70 50
Stud_Marks Table
SELECT Stud_Marks.Rno,Name,Dbms,Toc
FROM Stud_Info RIGHT JOIN Stud_Marks
ON Stud_Info.RNo= Stud_Marks.RNo
RNo Name Dbms Toc
1 Abhay 50 45
2 Sarika 67 65
3 Riya 76 55
5 NULL 70 50
O/P

Full Outer Join Example
RN
o
Name Address
1 Abhay Nashik
2 Sarika Pune
3 Riya Nashik
4 Sachin Manmad
Stud_Info Table
RNo Dbms Toc
1 50 45
2 67 65
3 76 55
5 70 50
Stud_Marks Table
RNo Name Dbms Toc
1 Abhay 50 45
2 Sarika 67 65
3 Riya 76 55
4 Sachin 0 0
5 NULL 70 50
O/P After Full Outer Join

MySQL CROSS JOIN
• A CROSS JOIN is such a join which specifies the complete cross product of two tables.
• For each record in the first table, all the records in the second table are joined, creating a potentially
huge result set.
• This command has the same effect as leaving off the join condition, and its result set is also known as a
Cartesian product.
• Syntax
SELECT Attr_list FROM table_A
CROSS JOIN table_B;

87
SQL JOINS
Reference link : https://pin.it/34WbgsOKI

SQL AND PL/SQL DBMS 2019 PATTERN SPPU UNIT NO 2

More Related Content

Similar to SQL AND PL/SQL DBMS 2019 PATTERN SPPU UNIT NO 2

Recently uploaded

SQL AND PL/SQL DBMS 2019 PATTERN SPPU UNIT NO 2