Unit-3
Basic SQL
SQL : Structured query language or Database language to perform operations on existing database to create DB.
DML : Data Manipulation language to access data and database
Command Description Syntax
INSERT INTO table_name (column1,
Insert data into a table
INSERT column2,…)VALUES(value1,value2,….);
Update existing data UPDATE table name SET column1 =
UPDATE within a table. value1,column2, value2 where condition;
Delete records from
DELETE from table name where condition;
DELETE database table
Nested Queries:
To perform complex queries by embedding one query within another.
Outer query can apply same condition on the result of the inner query.
Types;
Independent nested queries ,Co-related nested queries, Tables for understanding nested queries:
Student, course, student_course.
STUDENT:
S_ID S.Name S_Address S_Phone S_Age
S1 RAVI ONGOLE 6404091088 41
S2 RANI NELLORE 8801904046 31
S3 RANSOM HYDERABAD 9949989630 24
COURSE:
C_ID C_Name
C1 SOM
C2 EM
C3 ED
STUDENT_COURSE:
S_ID C_ID
s1 c1
s2 c3
s3 c2
Independent Nested queries:
1. Query execution starts from innermost query to outermost query.
2. Execution of inner query is independent of outer query.
3. Result of inner query used is outer query.
4. Operation line IN, NOT IN , ANY, ALL etc., used in independent nested query.
Aggregate Functions:
The functions which are grouped values of multiple rows to form a single value result.
Used to summarize the data.
Employee table
ID Name Salary Various aggregate functions.
1 A 802
2 B 403 COUNT():
3 C 604
Count(*): Total no. of records
4 D 705
5 E 636 Count(Salary): No.of non null values
6 F nill
Count(distinct salary): distinct non null values of salary
SUM(): Sum(salary): Sum all non null values of salary
Sum(Distinct salary): distinct non null values of salary
Avg():Avg(salary) = sum(salary)/count(salary)
Avg(distinct salary)= sum(salary)/count(distinct salary)
Min():Min(salary):
Minimum value in the salary
Max():Max(salary):
Maximum value in the salary
Create SQL queries to select the following information from the employee table:
Emp(id,name,dept,designation,sal,gender)
1) How many employees are there in each department
2) What is the maximum salary in each department
3) Which department has large number of female employees
4) What is the total salary of each department
5) Who are managers of each department
SQL editor:
Step 1: create table
create table
emp(id int primary key, name varchar(50), dept varchar(50),designation varchar(50),sal int,gender varchar(10));
Step 2: insert values
insert into
emp(id,name,dept,designation,sal,gender)values(101,'ravi','civil','professor',50000,'m'),(102,'raju','cse','assistant
professor',30000,'m'),(103,'rani','eee','professor',80000,'f'),(104,'rama','cse','technician',20000,'f'),(105,'rekha','civil','
technician',15000,'f'),(106,'srinu','cse','data operator',10000,'m');
d name dept designation sal gender
101 ravi civil professor 50000 m
assistant
102 raju cse 30000 m
professor
103 rani eee professor 80000 f
104 rama cse technician 20000 f
105 rekha civil technician 15000 f
106 srinu cse data operator 10000 m
Step 3: select required data
select dept,count(*) as employee_count from emp group by dept;
Output
dept employee_count
civil 2
cse 3
eee 1
select dept, max(sal) as max_salary from emp group by dept;
Output
dept max_salary
civil 50000
cse 30000
eee 80000
select dept from emp where gender='f' group by dept order by count(*) desc limit 1;
Output
dept
eee
select dept, sum(sal) as total_slary from emp group by dept;
Output
dept total_slary
civil 65000
cse 60000
eee 80000
select dept,name from emp where designation ='professor';
Output
dept name
civil ravi
eee rani
JOINS:
Joins in SQL are used to combine rows from two or more tables based on a related column between them. Here’s a
breakdown of the different types of joins:
### 1. **Inner Join**
An **inner join** returns only the rows that have matching values in both tables. It excludes rows where there is no
match.
**Syntax:**
```sql
SELECT columns
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
```
**Example:**
Suppose we have two tables:
- `Employees` (with `EmployeeID`, `Name`, `DepartmentID`)
- `Departments` (with `DepartmentID`, `DepartmentName`)
To find the names of employees along with their department names, we can use an inner join:
```sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
```
### 2. **Outer Join**
An **outer join** returns all rows from one or both of the tables, even if there is no match. There are three types of
outer joins:
- **Left Outer Join (Left Join):** Returns all rows from the left table and the matched rows from the right table. If
there is no match, NULL values are returned for columns from the right table.
**Syntax:**
```sql
SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
```
**Example:**
```sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
```
This will return all employees, even if they don't belong to any department.
- **Right Outer Join (Right Join):** Returns all rows from the right table and the matched rows from the left table. If
there is no match, NULL values are returned for columns from the left table.
**Syntax:**
```sql
SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
```
**Example:**
```sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
```
This will return all departments, even if no employees are assigned to them.
- **Full Outer Join (Full Join):** Returns all rows when there is a match in either table. If there is no match, it returns
NULLs for missing matches from either side.
**Syntax:**
```sql
SELECT columns
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
```
**Example:**
```sql
SELECT Employees.Name, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;
```
This will return all employees and all departments, matching when possible and filling with NULLs when not.
### 3. **Self Join**
A **self join** is a join in which a table is joined with itself. It is useful for comparing rows within the same table.
**Syntax:**
```sql
SELECT a.column_name, b.column_name
FROM table_name a, table_name b
WHERE a.common_column = b.common_column;
```
**Example:**
Suppose we have a `Employees` table with `EmployeeID`, `Name`, and `ManagerID`, where `ManagerID` references
`EmployeeID` in the same table. To find the names of employees along with their manager names, we can use a self
join:
```sql
SELECT e1.Name AS EmployeeName, e2.Name AS ManagerName
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;
```
### Summary
- **Inner Join:** Returns only matching rows.
- **Left/Right Outer Join:** Returns all rows from the left/right table, and matching rows from the other table.
- **Full Outer Join:** Returns all rows from both tables, with matching where possible.
- **Self Join:** Joins a table to itself to compare rows within the same table.
Each of these joins is useful in different scenarios depending on the data relationships you want to analyze or
retrieve.
