Name : Pranav Vijay Pisal
Subject : DBMSL
Class : TE COMP
Batch : T2
Roll no : 0031
Date Of Submission :
Remarks :
ASSIGNMENT 3
• Title : SQL QUERIES – all types of Join,Sub-Query and View
• Date Of Completion :
• Problem Statement :
Write at least 10 SQL queries for suitable database application using
SQL DML statements. e. f. Note: Instructor will design the queries
which demonstrate the use of concepts like all types of Join, Sub-Query
and View
• Theory :
1. Joins in SQL
SQL Joins are a fundamental part of relational databases that
allow for the combination of data from multiple tables based on
logical relationships between them. By joining tables, you can
retrieve and analyze data that is distributed across different tables
in a relational database.
• Types of joins and Queries
1 . INNER JOIN 2 . LEFT JOIN
3 . RIGHT JOIN
4 . FULL JOIN
5 . CROSS JOIN
6 . CROSS JOIN
1 . INNER JOIN : An INNER JOIN returns only the rows where there
is a match
between the columns in the joined tables. This type of join is ideal
when you need data that exists in both tables based on a common
key, ensuring that only relevant and matching data is included in
the result set. Syntax : SELECT columns FROM table1 INNER JOIN
table2 ON table1.column = table2.column;
2 . LEFT JOIN
A LEFT JOIN (or LEFT OUTER JOIN) retrieves all rows from the left
table and the matched rows from the right table. When there is no
match, NULL values are returned for columns from the right table.
This join is particularly useful when you want to include all records
from the left table, even if there are no corresponding entries in
the right table.
Syntax :
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
3.RIGHT JOIN RIGHT JOIN (or RIGHT OUTER JOIN): Provides all
rows from the
right table and the matched rows from the left table. If no match
exists, NULL values are returned for columns from the left table.
This join ensures that all records from the right table are included,
even if there are no corresponding entries in the left table.
Syntax :
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
4.FULL JOIN
FULL JOIN (or FULL OUTER JOIN): Returns all rows when there is a
match in either the left or right table. It includes rows from both
tables that do not have matching rows, with NULLs in place where
no match is found. This join is beneficial for retrieving a complete
dataset from both tables.
Syntax :
SELECT columns
FROM table1
FULL JOIN table2
ON table1.column = table2.column;
5.CROSS JOIN
A CROSS JOIN produces a Cartesian product of the two tables,
where each row in the first table is combined with every row in
the second table. This join does not require a condition and is
useful for generating all possible combinations of rows from both
tables, though it can result in large result sets.
Syntax :
SELECT employees.employee_id, departments.department_name
FROM employees
CROSS JOIN departments;
6.SELF JOIN
SELF JOIN is a join where a table is joined with itself. This type of
join is used for comparing rows within the same table or finding
hierarchical relationships, such as identifying employees who
manage other employees within the same table.
Syntax :
SELECT a.columns, b.columns
FROM table_name a
INNER JOIN table_name b
ON a.column = b.column;
2.Sub-Query
A subquery, also known as a nested query or inner query, is a query embedded
within another SQL query. Subqueries are used to perform operations that
require multiple steps, such as filtering results based on aggregate functions or
comparing results from different tables. Subqueries can be used in various
clauses such as SELECT, WHERE, and FROM.
Syntax : SELECT column1, column2, (SELECT subquery_column FROM
subquery_table
WHERE condition) AS alias_name
FROM main_table
WHERE condition;
3.View
A view in SQL is essentially a virtual table that represents a stored query. Unlike
a regular table, a view does not store data itself but provides a way to simplify
complex queries, encapsulate complex joins, or present data in a specific
format. Views are used to present data in a structured and readable manner,
often hiding the complexities of the underlying tables and queries
Syntax :
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
• Queries
1 . Inner Join
SELECT e.name, e.salary, d.department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;
2 .Left Join
SELECT e.name, p.project_name
FROM employees e
LEFT JOIN employee_projects ep
ON e.employee_id = ep.employee_id
LEFT JOIN projects p
ON ep.project_id = p.project_id;
3.Right Join
SELECT p.project_name, e.name
FROM projects p
RIGHT JOIN employee_projects ep
ON p.project_id = ep.project_id
RIGHT JOIN employees e
ON ep.employee_id = e.employee_id;
4.Full Join
SELECT e.name, p.project_name
FROM employees e
FULL JOIN employee_projects ep
ON e.employee_id = ep.employee_id
FULL JOIN projects p
ON ep.project_id = p.project_id;
5.Cross Join
SELECT e.name, p.project_name
FROM employees e
CROSS JOIN projects p;
6.Self Join
SELECT e1.name AS Employee1, e2.name AS Employee2
FROM employees e1
INNER JOIN employees e2
ON e1.department_id = e2.department_id
AND e1.employee_id <> e2.employee_id;
7.Subquery in Where Clause
SELECT name, salary, department_id
FROM employees e1
WHERE salary > (SELECT AVG(salary)
FROM employees e2
WHERE e1.department_id = e2.department_id);
8. Subquery in From Clause
SELECT department_id, AVG(salary) AS average_salary
FROM (SELECT department_id, salary
FROM employees
WHERE department_id IN (SELECT department_id
FROM departments
WHERE employee_count > 10)) AS dept_salaries
GROUP BY department_id;
9.Subquery in Select Clause
SELECT name,
(SELECT p.project_name
FROM projects p
JOIN employee_projects ep ON p.project_id = ep.project_id
WHERE ep.employee_id = e.employee_id) AS project_name
FROM employees e;
10. View Query
SELECT employee_id, name, department_name
FROM EmployeeDepartment
WHERE hire_date >= DATEADD(year, -1, GETDATE());
• Conclusion
In this exercise, we have explored the use of SQL Data Manipulation
Language (DML) statements through various queries designed to
demonstrate key SQL concepts such as joins, subqueries, and view
