SQL: Structured query Language

- Structured query language

- It is used to manage database

Database
- It is organized collection of information.
- Stored in tabular format

Flavors OF SQL
- In SQL there are different flavors

1. Oracle SQL: mostly used in Asian countries like India, Japan, China
2. Microsoft SQL: Mostly used in US, UK
3. Post gray SQL
4. NO SQL
5. My SQL
6. Mongo DB
7. Sybase

RDBMS: Relational Database Management System

- Advanced Version of DBMS stores data in tabular format
- Relational Database System Is the type of database that stores and provides access to
data points are related to one another
Example:
Table: EMP
ID Name Office Address
1 A Mumbai
2 B PUNE
3 C Satara

Table: EMP data

ID Home Location
1 Karad
2 Satara
3 PUNE
Subsets of SQL (Statements):
- There are 5 types of statements.
1. DDL
2. DML
3. DCL
4. DQL
5. TCL

1. DDL: Data Definition Language

a. Create statement
b. Alter (add, Modify, drop, rename). Used only for column operation.
c. Rename: Used table rename
d. Drop: Table operation.
e. Truncate

2. DML: Data Manipulation Language.

a. Insert
b. Update
c. Delete

3. DCL: Data Control Language

a. Grant
b. Revoke

4. DQL: Data Query Language

a. Select

5. TCL: Transaction Control Language

a. Commit
b. Rollback
c. Save

1. DDL: Data Definition Language.

DDL consists of SQL commands that it used to define database schema.
(Table, View etc.)
It deals with the description of database schema and used to create and modify the
structure of database objects in the system.
a. Create
It is used to create database or database objects. (Table, View etc.)
Syntax: create table table_name (column1 data_type, coloumn2 data_type (size));
Ex:
create table Sangli (Id int, Name varchar (10));

b. Alter
It is used to add, modify, drop and rename the column.
That means alter statement is used to alter the structure of table

Syntax:
Alter table table_name
[add/drop/rename/modify] column column_name;
Ex:
alter table karad
add percentage int;

Ex:
alter table karad
drop column percentage

Ex:
alter table karad
rename column name to std_name  oracle
exec sp_rename 'tasgaon.unique_no','unique','column';  mssql

Ex:
alter table karad
modify name varchar (50);  oracle

alter table karad

alter column kname varchar (20)

c. Rename:
It is used to rename the table
Syntax:
Rename [table name] to [new name]
Ex: rename karad to stara
 d. Drop
It used to delete the table with its table structure.
Syntax:
Drop table [tablename]
Ex:
drop table karad

e. Truncate
It is used to delete all information from the table without affecting its table structure
Syntax: truncate table [tablename]
Ex: truncate table karad

2. DML: Data Manipulation language

Data manipulation language is used to manage the data within the schema
It deals with the operation such as insert, update, delete.

1. Insert:
It is used to insert the rows into table

Syntax:
insert into [table name] values (column 1, column2, column3);
Ex:
insert into karad values (2,'shrinand');

Ex:
insert into karad values (1,'gaurav mali','gaurav@gmail.com'), (2,'shrinand
mali','shrinand@gmail.com');  to insert multiple rows at a time.

Ex:
insert into karad (no, name) values (4,'shree mali');  when you do not have a
specific column value

2. Update
It is used to change the values for particular column of particular row.
Syntax:
update [update tablename]
set [column name] =’value’
where [unique column name] =value of that column
 Ex:
update karad
set email='shreemali@gmail.com'
where no=4;

3. Delete
It is used to delete particular row
Syntax:
delete from [tablename]
where [unique column name]=value

Ex:
delete from karad
where no=2;

3. DCL: Data control language

It includes commands such as grant, revoke.
Which are mainly deals with the rights, permissions and other control on the database
system?

a. Grant: used to give access privileges on the database

Syntax:
grant [permission] on [table name] to [
username]
Ex:
Grant update, delete on student to user1
grant update on karad to ram
grant update, delete on karad to ram

b. Revoke: It is used to withdraw a user access privileges given by grant command

Syntax:
Revoke [permissions] on [table name] from [username]
Ex:
Revoke update, delete on karad from ram
revoke update, delete on karad from ram
4. DQL: Data Query Language
It is used to retrieve data by using select statement

1. Select: It is used for data retrieval

Syntax: select * from tablename
It will display whole data from the table.

Ex: select * from karad

2. Syntax:
select * from [tablename]
Where [unique column] = ’value’
It will show all information of given value

Ex:
select * from karad
where name='gaurav mali'

5. TCL: Transaction Control Language

It is used to deal with the transaction within database

1. Commit
It is used to save the changes permanently
Syntax: commit

Ex:
begin tran
update karad
set email='shreemali@gmail.com'
where no=4
commit

2. Rollback(undo)
Rollback transaction in case of table error occurs
It is used to go back to previous stage.
Syntax:
Begin tran
Update [table name]
 Set [column name]=value
Where [unique column name]=value
Rollback tran

It will undo the recent change.

Ex:
begin tran
update karad
set email='shri123@gmail.com'
where no=1
rollback tran

3. SAVE Point.
It is used to set a transaction so that, next time you can use for the reference.
Save point used in oracle sql
Set tran used in ms sql
Ex:
begin tran
update karad
set name='gaurav'
where no=1
set tran;

 Difference between DDL & DML

DDL: data definition language DML: data manipulation language

1. Create, alter, rename, drop, truncate. Insert, update, delete.

2. In DDL, where clause can’t be used. We can use where clause with DML
3. DDL works on whole table. DML works on row by row.

4. In DDL we can’t be rollback. Because We can be rollback.

DDL statements are auto committed
5. DDL is used to define a database DML used to managing the data within
structure or schema, deals with a the schema, it deals with the storing
creation modification of database. modifying, delete data in a database.
 Difference Between Drop and Truncate

Drop Truncate

1. It will delete the date from the table It will delete only data from the table
with its table structure without affecting table structure.
2. Table space is freed from memory Does not free the table space.

3. View of table does not exists View of table exists.

 Difference between Delete and Truncate

Delete Truncate

1. Delete is a DML statement Truncate is a DDL statement

2. Delete is used to delete a particular Truncate used to delete whole data

row from the table from the table
3. We can use where clause with delete We can’t use where clause with
truncate

4. Rollback is possible Rollback is not possible

5. It is slower It is Faster
Data types:
An SQL decides what type of data column will be stored.

1. Character of fixed length datatype (char)

- We can hold a-z letters and 0 to 9 numbers.
- 2000 characters maximum it can store at a time.
- Default one character needed.
- It will consume fixed size for that particular column.

Ex:
Create table demo (id int, name char (10));
Insert into demo values (1
,'Ram');
(It will consume size of 10 characters for each row of name column)

2. Varchar: variable length datatype

- We can hold a-z letters and 0 to 9 numbers
- String data of specified with maximum width is 4000.
- It will consume size of that particular character size.

Ex:
Create table demo (id int, name varchar (10));
Insert into demo values (1,'Ram');
(It will consume size of 3 particular characters for that row of name column)
(For “Ram” it will consumes that the size of 3 characters)

3. Date:
- Date datatype used to store the values in format of dates
- Date with default format
- In MSSQL: YYYY-MM-DD
Ex:
alter table demo
add dob date;
update demo
set dob='1999-05-28'
where name='Ram'
 - In Oracle SQL
Ex:
insert into sangli values (1,'gaurav','mali', 26, TO_date('1995-07-07','yyyy-mm-
dd'),'45000.90','gaurav@gmail.com');

Syntax: select (GETDATE): It is used to show current date and time

select sys date: We can use this in oracle to get current date & time.

4. Integer : (int)
- It allows only numeric values.
Ex:
create table demo (id int, name char (10));

5. Number:
- It is same like integer.
- Used in oracle

6. NCHAR:
 The nchar datatype is used when to create the table with an nchar column.
 We can define length in characters
 N specifies the Unicode
 Unicode means no English characters. (Eg. @, #, $ etc.)
 Same like char datatype
Ex:
create table emplo (id int, name varchar (20), email nchar(20));
insert into emplo values (2,'gaurav', 'gaurav@gmail.com');

7. NVARCHAR:
 The nvarchar datatype is used when to create the table with a nvarchar column.
 We can define length in characters
 N specifies the Unicode
 Unicode means on English characters. (Eg. @, #, $ etc.)
 Same like varchar datatype
Ex:
create table emplo (id int, name varchar (20), email nvarnchar(20));
insert into emplo values (2,'gaurav', 'gaurav@gmail.com');
8. Decimal
 Used to store data in decimal format
Ex:
alter table emplo
add salary decimal (6, 3);

update emplo
set salary='25000.12'
where id=2
CONSTRAINTS in SQL:
 Constraints are the conditions specified on the column to maintain data integrity
within database.

1. Domain Integrity constraint:

Not null constraint.
Check

2. Entity integrity constraint:

Unique
Primary

3. Referential integrity constraint:

Foreign key.

1. Not Null
It does not allow null values in that particular column.
EX: create table demo (id int not null);

2. Null
It is opposite of not null constraint
All values in that column should be null
Eg. create table demo (id int null);

3. Unique
The values should be unique in that particular column on which unique
constraint has applied.
It allows only not null value
It does not allows null values
Eg. create table student (id int not null, name varchar (10) unique (name));

Ex:
alter table student
add constraint uc_student unique (std_id);
4. Default
When we cannot specify the value for that particular column on which default
constraint has been applied, then by default value will assigned for that
particular column that we have set at the time of table creation.
We can add both numbers as well as characters in default constraint.
EX: create table emp (id int, name varchar (20), salary decimal (10, 2) default
(50000));
Ex:
alter table emp
add emp_work_address varchar (10)

alter table emp

add constraint df_emp default 'pune' for emp_work_address

5. Primary Key
It is a combination of unique and not null constraint
Primary key is a column that set of columns in a table that uniquely identifies
the rows/topples

We can specify only one primary key for one table.

EX: create table student1 (id int primary key, name varchar (10));
EX: create table student2 (id int, name varchar (10), primary key (id));

6. Foreign key constraint

To maintain data integrity between two tables
A foreign key is a key used to link two tables together
Used to restrict the user from inserting those values are present in reference
table.
A foreign key is a column whose values matches primary key of another table.

Eg. create table student3 (id_R int name varchar (10), address varchar (10),
foreign key (id_R) references student2 (id));

7. Check
It is used to check the restricted condition.
Eg: create table student4 (id int, name varchar (10), age int, check (age>=18));
Clauses in SQL:
 Clauses are used to apply on data retrieval or data updating, deleting statement.

1. Where Clause:
It is used to retrieve the data by using particulars from table.
The where clause will apply exact condition in the update delete or select
statements.
Ex: 1
select * from student
where no =1

Ex: 2
select * from student
where no in (3, 5)
To apply more than one condition.

2. Order by
Order by clause in sql is used for sorting the records of the data by ascending
or descending order.
By default, order by clause sort data in ascending order

Ex:
select * from student
order by name;
Ex:
select * from student
order by name desc;
It is used to show data by descending order.

3. Group By
To group the result set of the rows that have the same values in the result
from the database tables.
Ex:
select sum (std_fee), std_subject from students
group by std_subject
select eadd, count (*) as count from emp
group by eadd
4. Having clause
Having clause can be used in group by clause.
It is used to specify a search condition for group in the group in the database
table
Ex:
select sum (std_fee), std_subject from students
group by std_subject
having std_subject='chem

5. Top clause:
This clause is used when the database has many records.
It is used to specify the total no of starting records to be fetched or returned
Ex:
select top 3 prof_name from dept
select top 2 * from dept

6. Limit clause:
It is as same as top clause, used in oracle sql.
Ex:
select * from dept
limit 3

7. And clause:
And clause is used when multiple conditions specified in a query and returns
a dataset when all the conditions given in the AND clause meets the
requirement
Ex:
select * from dept
where deptname='chem' and dept_id=101 and prof_name='patil'

8. Or clause.
The or clause is used when multiple conditions are specified in query and
returns a dataset when of those conditions gets satisfied
Ex:
select * from dept
where deptname='phy' or prof_name='patil'
Operators in SQL
 It is used to apply the conditions with particulars in the query
 We can use this with select, update and delete statements

1. Like operator
It is used to pattern matching.

% represents any value any length

_ represents one unknown characters
_ _ represents two unknown characters
_ _ _ respectively.

Ex:
select * from address
where name like '%k%'
It will display all name having ‘k’ letter in the names

Ex:
select * f
rom address
where address like 'k%'
It will display all the address names starts with ‘k’

Ex:
select * from address
where address like '__k%'
It will display address having two unknown characters before ‘k’

2. In operator
It is used to apply more than one condition at the place of equal to
Ex:
select * from address
where name in ('vishal','megha');

3. Not in
It is used to apply the conditions and avoid that particular.
 Ex:
select * from address
where name not in ('vishal','megha');
It will display the all-other data having vishal and megha name

4. Equal to: =
It is used to match particular condition in the clauses.
Ex:
select * from address
where name = 'vishal'

5. Not Equal to: != or <>

It is used to avoid the particulars from query
Ex:
select * from address
where address != 'karad'
Ex:
select * from address
where address <> 'karad'
These both will display all the address rows in the table expecting karad
address.

6. Greater than:
It is used to find value more than that we have mentioned.
Ex:
select * from student
where std_age > 16

7. Greater than equal to: (>=)

Ex:
select * from student
where std_age >= 16

8. Less than:
it is used to apply condition having values less than that we have mentioned
Ex:
select * from student
 where std_age < 16
9. Less than equal to: <=
Ex:
select * from student
where std_age <= 16

10.Plus, minus, multiplication and division: (+,=,*,/)

Used to add, subtract, multiply, divide that type of mathematical operations.
Ex:
update student
set std_fees = (std_fees + std_fees*5/100)
where std_name='gaurav'
It will increase the std_fees of gaurav with 5 percent

11.Between
Using between operator we can select, update or delete records which fall in
specifies range.
Ex:
select * from student
where std_age between 16 and 20;
It will show the std_age between 16 and 20. (including 16 and 20)

Ex:
select * from student
where std_age > 16 and std_age < 20
It will show the std_age between 10 and 20 (excluding 16 and 20)

Using query under query

select * from student
where std_name like 's%' or std_age > 17 or std_fees between 3000 and
4000
Aggregate functions:
 Which having the specific mathematical functionality to perform an action.

1. Max
It is used to find maximum value from the particular field.
Ex:
select max (age) from empl

Ex: using sub query

select * from empl
where age= (select max (age) from empl)
It will display all columns having maximum age.

2. Min:
It is used to find minimum value from the particular field.
Ex:
select min (age) from empl

Ex: using sub query

select * from empl
where age= (select min (age) from empl)
It will display all columns having minimum age.

3. Count
It is used to count the total no. of values in a table or it count the rows
returns by query
Ex:
select count (age) from empl
where age=39
Ex:
select age, count (*) as count from empl
group by age

4. Average:
It is used to find the average value from particular column.
 Ex: select avg (age) from empl
5. Sum:
It is used to sum the values
It returns total from column
Ex:
select sum (age) from empl
where ename = 'gaurav'
 Example

product sale
mouse 200
keyboard 300
cabinet 900
mouse 1200
keyboard 800
cabinet 500
mouse 500
keyboard 1100
mouse 800

Syntax:
select * from orders
where sale = (select max (sale) from orders)
It will display maximum sale of product

select * from orders

where sale = (select min (sale) from orders)
It will display minimum sale of product

select avg (sale) from orders

It will display total average sale

select product, COUNT (sale) from orders

group by product
having product = 'keyboard'
It will display total count of keyboard sale

SELECT product, COUNT (*) AS COUNT FROM orders

group by product.
It will display all product count

select product, sum (sale) from orders

 group by product
It will display total sale of each product

Subquery.
 Subquery means query under query
 It execute from innermost query to outermost query
Example

Table: dept

Dept_id dept_name
9001 chem
9002 phy
9005 Botany
Table: empls

Emp_id dept_id
1023 9001
1024 9002
2046 9003

Query to find emp_id working in chem dept.

select emp_id from empls

where dept_id = (select dept_id from dept
where dept_name='chem')
Joins:
 Joins are used to retrieve data from two or more tables based on relative column
between them.
 There are 7 types of joins used in sql
1. Inner join
2. Self-join
3. Natural join
4. Right outer join
5. Left outer join
6. Full join
7. Cross join

1. INNER JOIN:

It returns all the records that have matching values both the tables.

Syntax:
Select tablename.columnname, tablename.columnname
From tablename1
Inner join
Tablename2
ON
Tablename1.columnname=tablename2.columnname

Table b1
id home_address dept
1 karad IT
2 belepur SQL
3 pune IT

Table b2:
id emp_name salary
1 meera 10000.00
2 ram 20000.00
3 prathmesh 30000.00
4 Neha 30000.00

Table b3
emp_name age
neha 30
Ram 20
prathmesh 25
Ex: two table inner join

select b1.id, b1.home_address, b2.emp_name, b2.salary

from b1
inner join
b2
ON
b1.id=b2.id

Ex: three tables inner joins

select b1.id, b1.home_address, b1.dept, b2.salary, b3.age

from b1
inner join
b2
ON b1.id=b2.id
inner join b3
ON
b2.emp_name=b3.emp_name

Ex: using condition

select b1.id, b1.home_address, b1.dept, b2.salary, b3.age

from b1
inner join
b2
ON b1.id=b2.id
inner join b3
ON
b2.emp_name=b3.emp_name
where emp_name='prathmesh')

Ex: query to find out max salary

select b1.id, b1.home_address, b2.emp_name, b2.salary

from b1
inner join
b2
ON
b1.id=b2.id
where b2.salary= (select max (salary) from b2)
2. Self-Join
 When table is joined or multiplied with itself then we can said it to be self-joint

EX
id course_id year
1001 C01 2022
1001 C02 2020
1002 C01 2020

Que: find the students id who is enrolled in at least 2 courses

select T1.id, T1.course_id, T1.year

from course AS T1, course as T2
where T1.id=T2.id
and
T1.course_id! = T2.course_id

Output
id course_id year
1001 C01 2022
1001 C02 2020

select T1.id, T1.course_id, T1.year

from course AS T1, course as T2
where T1.id=T2.id

output
Main Table output
Id co_id year id course_id year
1001 C01 2022 1001 C01 2022
1001 C02 2020
1001 C02 2020 1001 C01 2022
1001 C02 2020
1002 C01 2020 1002 C01 2020
3. Left Join
 This join returns all rows of the table on left side of the join and matches rows
for the table on right side of the join.
 For the rows which there is no matching rows on the right side, the result set will
contains ‘null’
 Left join also known as left outer join

Ex:
table1: student table2: st_course
id name address age id c_id
1 vishal karad 20 5 C1
2 Mira pune 22 1 C2
3 Ram Nagpur 23 2 C2
4 Kirti Jalgaon 20
5 Mahesh Satara 18

select * from student

left join
st_course
ON
student.id=st_course.id

Output
id name address age id c_id
1 vishal karad 20 1 C2
2 mira pune 22 2 C2
3 ram nagpur 23 NULL NULL
4 kirti jalgaon 20 NULL NULL
5 mahesh satara 18 5 C1
4. Right joint

 Right join is similar to left join

 This join returns all the rows of the table on right side of the join and matching
rows for the table on the left side of join
 For the rows for which there is no matching rows on the left table, the result set
will contains ‘null’

Ex:

table1: student table2: st_course

id name address age id c_id
1 vishal karad 20 5 C1
2 Mira pune 22 1 C2
3 Ram Nagpur 23 2 C2
4 Kirti Jalgaon 20 6 C3
5 Mahesh Satara 18 8 C2

select * from student

right join

st_course

ON

student.id=st_course.id

Output
id name address age id c_id
5 mahesh satara 18 5 C1
1 vishal karad 20 1 C2
2 mira pune 22 2 C2
NULL NULL NULL NULL 6 C3
NULL NULL NULL NULL 8 C2
5. Full join
 It is combination of left join and right join
 Full join crates the result set by combining results of both left join and right join
 The result set will contain all the rows from both tables .
 For the rows which there is no matching, the result set will contain ‘null’ values

Ex:

table1: student table2: st_course

id name address age id c_id
1 vishal karad 20 5 C1
2 Mira pune 22 1 C2
3 Ram Nagpur 23 2 C2
4 Kirti Jalgaon 20 6 C3
5 Mahesh Satara 18 8 C2

select * from student

FULL JOIN

st_course

ON

student.id=st_course.id

Output

id name address age id c_id

1 vishal karad 20 1 C2
2 mira pune 22 2 C2
3 ram nagpur 23 NULL NULL
4 kirti jalgaon 20 NULL NULL
5 mahesh satara 18 5 C1
NULL NULL NULL NULL 6 C3
NULL NULL NULL NULL 8 C2
6. Natural Join
 Natural join can join tables based on the common columns in the tables being
joined
 A Natural returns all rows by matching values in common columns
 Syntax
Select columns from table1 t1, table2 t2
Where matching column condition

Ex:
select t1.id, t1.name, t2.c_id from student t1, st_course t2
where t1.id=t2.id

Ex:
select t1.id, t1.name, t2.c_id from student t1, st_course t1
where t1.id=t2.id
and
age <= 22

Output
Id name c_id
5 mahesh C1
1 vishal C2
2 mira C2

7. Cross Join
In this join all rows will joined to each row in next table

Ex:
select t1.id, t1.name, t2.c_id from student t1, st_course t2

or

select id, name, c_id from student

cross join
st_course
SET OPERATORS:
- It is used to join the 2 or more select statement.
- Which combines the results of two or more select quires

1. Union:
It combines the result sets into single result set, but it removes duplicate values.
In case of union, no of columns and data type must same in both tables.

Syntax:
Select id, name from table 1
Union
Select id, name from table 2
Union
Select id, name from table3
EX:
table p1 table p2
id name age id name age
1 mira 18 10 ajay 18
2 ram 19 24 rahul 19
3 mahesh 20 3 mahesh 20

select id, name from p1

union
select id, name from p2

Output
id name
1 mira
2 ram
3 mahesh
10 ajay
24 rahul

2. UNION ALL:
It combines two or more result sets in single result set without removing
duplication.
 EX:
select id, name from p1
union all
select id, name from p2
OUTPUT
id name
1 mira
2 ram
3 mahesh
10 ajay
24 rahul
3 mahesh

3. INTERSECT
It displays common rows from the two or more tables with no duplicates.
EX:
select id, name from p1
intersect
select id, name from p2
OUTPUT
id name
3 mahesh

4. MINUS/EXCEPT
It displays the rows are present in 1st query but absent in 2nd query with no
duplicates.
EX:
select id, name from p1 select id, name from p1
except minus
select id, name from p2 select id, name from p2

OUTPUT OUTPUT
id name id name
1 mira 1 mira
2 Ram 2 ram
10 ajay
24 rahul
ALIAS in SQL:
- Sql alias are used to give a table name or a column in a table.
- It is a temporary name
- An alias only exists for the duration of that query.
- An alias is created with AS keyword
Syntax:
Select column name AS alias_name
From table name
1. Alia for column
EX:
Table p1
id name age
1 mira 18
2 ram 19
3 mahesh 20

select name as N, * from p1

OUTPUT
N id name age
mira 1 mira 18
ram 2 ram 19
mahesh 3 mahesh 20

EX:
select p1.name as N, p2.name as n from p1
inner join
p2
on
p1.N=p2.n

2. Alia For TABLE

- Syntax:
Select column from column name
from table name AS alias_name

EX: select * from p1 as P

To create duplicate table

 For MS SQL

- Query:
Select * into newtable
From oldtable

EX:
select * into demo5
from p1

 For Oracle SQL

- Query:
Create table1 as select * from table 2
EX:
Create table demo as select * from p1
How to define comments in SQL:
1. For single line comment
Syntax: --comment
Ex: -- select * from student

2. For multiple line comment

Syntax: /* comment
Syntax: comment */
Ex:
/* select * from student
where id = 1 */

id name dept salary

1 ram chem 20000
2 megha chem 25000
3 ajay math’s 25000
4 kavya math’s 30000

Example: Query to find dept-wise highest salary

Syntax:
select dept, max (salary) from emp10
group by dept
Example2: Query to find dept-wise highest salary with name

Syntax:
select * from emp10
where salary in (select max (salary) from emp10
group by dept);
Normalization:
- It is a systematic approach to decomposing the table to eliminate data
redundancy or duplicates
- I it multi step process that puts data into tabular format by removing duplicate
from its relational table
- There are four types of normalization forms
- 1NF,2NF,3NF,3.5NF(boyce cott NF)

1. First Normal Form(1NF):

Rules of 1st normal form

a. Single cell (row) cannot hold multiple values.
b. Remove repeating groups from the table.
c. Create a separate table for each set of related data.
d. Identify each set of related data with primary key.

Ex:

Id Mob_no
1 1234567890,0987654321
After applying 1NF

Id Mob_no
1 1234567890
1 0987654321

2. Second Normal Form(2NF)

Rules of second normal form (2NF)

a. Table is said to be 2NF when it is fulfills following conditions
It has to be in 1NF.
Table should not contain any partial dependency.
Emp_id Dept_id Ofc_loc
C001 D005 PUNE
C002 DOO2 MUMBAI
COO3 D003 SATARA
 After applying 2nd NF
Emp_id Dept_id
C001 D005
C002 DOO2
COO3 D003

Dept_id Ofc_loc
D005 PUNE
DOO2 MUMBAI
D003 SATARA

3. Third Normal Form. (3NF):

Rules of 3rd Normal Form

a. It is normal form that used to normalizing a table to reduce a duplication
of data and ensures referential integrity.
b. A table is said to be 3 NF when it has to be in 2NF
c. There should be no transitive dependency for non-prime undesirable
data
d. Make the data model more informative

Ex:

Std_id Std_name Sub_id Sub address

After applying 3NF

Table1

Std_id Std_name Sub_id address

Table 2

Sub_id sub

4. Boyce Cott Normal form (3.5NF):

Rules of 3.5 normal forms
a. It has to be in 3NF
b. Every functional dependency A  B then a has to be the super key of the
table.
 (super key is a group of single or multiple keys that identify rows in a
table)
c. Removing non-prime attribute’s function dependency

(higher version of NF was developed by Raymond F. Boyce & Edger F.

Cott)

SQL Loader:
It is an oracle utility which would help you to upload external data into table

The text file is called as flat file , It can be in CSV format.

SQL loader contains following components

1. Control file:
This will define the configuration parameters which tells us to load the data into the
database.

2. Input file or Infile

This is the file from which data should be loaded.

3. Log file:
It will record action of sql loader and user for reference.

4. Bad File:
Records which are failed to load into table due to any reason will be stored in this file
The extension of bad file is .bad

5. Discard file:
Records which does not satisfies the conditions will be placed here.

Syntax:
LOAD DATA
INFILE ‘sample.csv’
INSERT INTO TABLE student
Fields terminated by ‘,’ optionally enclosed by “”””
Trailing Null calls
(rollNo, name);
Rownum and Rowid:
1. Rowid:
a. It is a pseudo column in the table with store & return row address in HEXADECIMAL
format with database in oracle sql.
b. Row-id is the permeant unique identifiers for each row in the table
c. It consist 18-character string

Syntax: select rowid,column1,column2,column3 from tablename.

Ex: select rowid, Ename, Elast, Eage, Eadd from empl

2. Rownum
a. For each row return by a query, the rownum pseudo column returns a number
which indicates the order in which a row was selected from a table
b. 1st row gets 1 number,
2nd row gets 2 number and so on

Ex.
select * from tablename
Where rownum < 10;

Difference between rowid & Rownum

Rowid Rownum
Rowid is a pseudo column Rownum is an analytical functionality
Represents the physical address of rows Rownum assigns a numbers to each row
accepting to its ordering
Rowid is permeant Rownum is temporary.
Rowid is 18-character string Rownum is a function that returns numeric
value
Rowid gives the address of rows or records Rownum gives the rank of records.

To remove duplicate values using rowid

Ex:
Delete from empl
Where rowid not in (select max(rowid) from empl
Group by name);
QUERYS
Table: std
Rowid Rownum Name Address
108 8 Meera Nagpur
109 9 Preeti Mumbai
105 5 Kirti Mumbai
106 6 Mahesh Satara
107 7 Ram Nagpur

Q.
Query to fetch a data from std, more than 1 student from that particular location.

A.
Select address, count(*) from std
Group by address
Having count(*) > 1
Q.
Query to fetch a data from std, more than 1 student from that particular location with
name

A.
Select name from std
Where address in (Select address, from std
Group by address
Having count(*) > 1)

Examples:
Table Empsal
Empid Project Salary Variable
121 P1 8000 500
321 P2 10000 1000
421 P1 12000 0

Empdet
Empid Name Manid DOJ City
121 Meera 321 2019-02-19 Pune
321 Ram 986 2020-03-28 Mumbai
421 ajay 786 2021-12-15 Satara
Examples:

1. Query to fetch Empid and name of all employees working under manager id 986
select Empid, name from empdet
where manid in 986

select Empid, name from empdet

where manid in (select manid from empdet
where manid = 986)

2. Query to fetch different projects available in empsal table

select distinct(project) from empsal

3. Query to fetch count of employees working in project p1

select project, count(*) from empsal

group by project
having project = 'p1'

select count(*) from empsal

where project = ‘p1’

4. Query to display total salary of each employee adding salary with variable value

select empid, salary + variable as totalsalary from empsal;

select empid, sum(salary + variable) as totalsalary from empsal

group by empid

5. Query to fetch a record having emp name having 2 unknown characters at starting ang
3rd character is ‘m’

select * from empdet

where name like '_ _m%'
6. query to fetch common records from two tables.

select * from empdet

where empid in (select empid from empsal);

select * from empdet

inner join empsal
on empdet.empid=empsal.empid

7. Query to fetch empid that are present in empdet and but not in empsal

select * from empdet

where empid not in (select empid from empsal);

8. Retrieve records which are not working in any project

select * from empsal

where project = (‘p1’,’p2’)
9. How to check current date and time.
select getdate ();  ms sql

select now ();  mysql

select sysdate from dual;  oracle

10. Query to fetch emp name having salary greater than 5000 and less than 10000

select name from empdet

where empid= (select empid from empsal
where salary > 5000 and salary < 10000)

11. Query to fetch all employees’ details who joined in year 2020
Ms sql
select * from empdet
where doj between '2020-01-01' and '2020-12-31'
 Oracle
select * from empdet
where doj between to_date('2020-01-01',’yyyy-mm-dd’) and to_date('2020-12-
31',’yyyy-mm-dd’);

12. Query to fetch project-wise count of employees sorted by project’s count in

descending order

select project, count(*) from empsal

group by project
order by count(*) desc

13. Query to fetch odd and even records:

Odd
select * from student
where mod(id,2)!=0;

Even
select * from student
where mod(id,2)=0;

14. How to display Nth highest salary

Select * from emp.e1

Where N-1 = (select count(distinct(salary)) from emp.e2
Where e2.salary>e2.salary

3rd Highest Salary

select * from empsal e1
where 3-1 = (select count(distinct(salary)) from empsal e2
where e2.salary>e1.salary)

15. Query to display both the empId and manager id together in one column

select concat (empid,manid) from empdet

16. How to find last record from table

Select * from (select * from tablename

Order by rownum desc)
Where rownum =1

17. How to fetch last two rows from table

select * from stNew

where id > (select count(*)-2 from stNew)

18. how to fetch 1st and last record from table

select * from stNew

where rownum = 1 or id = (select count(*) from stNew)

19. Query to fetch product-wise count

Select product, count (*) from orders

Group by product

20. Query to find product wise total no of sale

Select product, sum(sale) from orders

Group by product

21. Query to fetch employee names and salary records and display the employee details
even id the salary record is not present in the table

select empdet.name,empdet.manid,empdet.doj,empdet.city , empsal.salary from

empdet
left join empsal
on empdet.empid=empsal.empid

select empdet.name,empsal.salary from empdet

left join empsal
on empdet.empid=empsal.empid
22. Query to retrieve data from 4 tables

select * from empdet inner join empsal

on empdet.empid=empsal.empid inner join empdata
on empdet.name=empdata.empname inner join empinfo
on empdet.manid=empinfo.manid

23. How to find duplicate records from the table

select empid, count(*)from empsal

group by empid
having count(*) > 1

24. How to find duplicate records with all columns from the table

select * from empsal

where empid in (select empid from empsal
group by empid
having count (*) > 1)