21-JAN-25

Database :-
---------------

=> a DB is a organized collection of interrelated data .

For ex a bank DB stores data related to

customers ,accounts ,transactions etc and
a university DB stores data related to students ,courses and
faculty etc.

Types of Databases :-
-----------------------------

1 OLTP DB (online transaction processing)

2 OLAP DB (online analytical processing)

=> organizations uses OLTP for storing day-to-day

transactions and OLAP is for analysis.

=> OLTP is for running business and OLAP is for to analyse business.

=> day-to-day operations on DB includes

C create
R read
U update
D delete

DBMS :-
--------------

=> Database Management System

=> It is a software used to create and to manage database.
=> DBMS is an interface between user and database.
 USER-----------------DBMS------------------------DB

RDBMS :-
--------------

=> a DBMS that supports all 12 rules called perfect RDBMS

Information Rule :-
------------------------

=> according to information rule data must be organized in tables i.e.

rows and cols

CUSTOMER
CID NAME ADDR
100 sachin mum
101 vijay hyd
102 rahul del

DB => collection of tables

TABLE => collection of rows & cols
ROW => collection of field values
COLUMN => collection of values of one field

=> every table must contain primary key to uniquely identify

=> one table is related to another table using foreign key

ex
:-

RDBMS features :-
---------------------------

1 easy to access and manipulate data

2 lessredundency (duplication of data).
3 more security
4 guarantes data integrity i.e. data quality
5 supports data sharing
6 supports transactions

RDBMS Softwares :- (SQL Databases)

-----------------------------

1 SQL server from Microsoft

2 oracle from oracle corp
3 mysql from oracle corp
4 postgresql from postgressql
5 rds from amazon

NoSQL Databases :-
---------------------------

1 MongoDB
2 Cassandra

23-jan-25

ORDBMS :-
==========
=> object relational DBMS
=> It is the combination of RDBMS & oops

ORDBMS = RDBMS + oops (reusability)

=> RDBMS doesn't support reusability but ORDBMS

supports reusability

=> ORDMS supports reusability by using UDT (user

define type).
ORDMS Softwares :-
------------------------

SQL SERVER
ORACLE
POSTGRESQL

DB Development Life Cycle :-

=======================

Analysis
Design
Development
Testing
Deploy
Maintenance

Design :-
------------

=> Designing DB means designing tables

=> DB is designed by DB Designer / Architects
=> DB is designed by using

1 ER model (Entity Relationship Model)

2 Normalization

Development :-
-------------------

=> DB is developed by Developers & DBAs

=> DB is developed by using any RDBMS Soft wares like SQL server ,
oracle etc.

Developer DBA
creating tables installation of SQL server
creating views creating database
creating synonyms creating logins
creating sequences DB backup & restore
creating indexes DB export & import
creating procedures performance tuning
creating functions creating triggers
writing queries

Testing :-
------------

=> DB is tested by QA team ( Quality Assurance) by using

1 manual testing
2 automation testing

Deployment :-
-----------------

=> moving DB from Dev server to PROD server is called deployment

=> after moving DB to prod server end users can use DB for day-to-day
operations

================================================================
SQL SERVER

=> SQL server is RDBMS software from Microsoft and also

supports features of ORDBMS and used to create and to manage
database.

=> SQL server can be used for DB development and administration.

CLIENT / SERVER Architecture :-

===========================

1 server
 2 client

Server :-
-----------
=> Server is a system where SQL server is installed and running.
=> inside the server SQL server manages

1 DB
2 INSTANCE

=> DB is created in Hard disk and acts as permanent storage.

=> INSTANCE is created in ram and acts as temporary storage.

Client
-----------

=> Client is also a system from where users can

1 connects to server
2 submit requests to server
3 receive response from server

client tool :-
---------------

SSMS (SQL server MGMT studio)

USER---SSMS-------------------------------------SQL SERVER---------DB

USER---SQLPLUS--------------------------------ORACLE---------------DB

USER-----MYSQLWORKGENCH---------------MYSQL---------------DB

24-JAN-25

SQL :-
----------
 => SQL stands for structured query language.

=> It is a language used to communicate with SQL server.

=> user communicates with SQL server by sending

commands called queries.

=> a query is a command/instruction/question submitted to SQL

server to perform operations on DB.

=> SQL is introduced by IBM and initial name of this

language was sequel and later it is renamed to SQL.

=> SQL is common to all relational database Soft wares

user-------SSMS-------------------SQL---------------------- SQL Server------------DB

user-------SQL plus------------------SQL---------------------Oracle---------------DB

user-------MySQL Bench-------SQL--------------------MYSQL-------------------DB

=> based on operations over Db SQL is categorized into following

sublanguages

DDL (DATA DEFINITION LANG)

DML (DATA MANIPULATION LANG)

DQL (DATA QUERY LANG)

TCL (TRANSACTION CONTROL LANG)

DCL (DATA CONTRO LANG)

Data & Data Definition :-

--------------------------------

Empid ename sal => data definition / metadata

1 A 5000 => data

21-nov-24

How to connect to SQL server :-

------------------------------------------

=> to connect to SQL server open SSMS and enter following details

server type :- DB engine

server name :- DESKTOP-G2DM7GI
authentication :- windows / SQL
server login :- SA (system admin)
password :- 123

server
database
table
data

Creating Database :-
----------------------------
=> In object explorer select Databases => New Database

Enter Database Name :- BATCH11AM

=> click OK

=> a new DB is created with name BATCH11AM

Command to create new database :-
---------------------------------------------------

=> after installation the following Db s are created by default

called system databases

1 master
2 model
3 Ms Db
4 Temp Db

=> use master DB for server level operations like creating new database
=> open master DB and execute following command

=> In query window execute the following command

CREATE DATABASE DB11AM

Download & Install :-

------------------------------

1 SQL server
2 SSMS

SQL server :-
----------------

download :- https://www.microsoft.com/en-in/sql-server/sql-server-
downloads install :-
https://www.mssqltips.com/sqlservertip/7313/install-sql-server-2022

SSMS :-
----------
download :- https://learn.microsoft.com/en-us/sql/ssms/download-sql-
server-management-studio-ssms?view =sql-server-ver16

25-JAN-25

Datatypes in SQL SERVER :-

-------------------------------------

=> Datatype specifies

1 type of the data in a column

2 amount of memory allocated for column

DATA TYPES

Integer (int, small int, tiny Int, big int, numeric(p))

Char (char(), varchar(size),varchar(max))

Date (date, datetime ,time … etc)
Float (decimal(p,s) )
Unicode (nchar(), nvarchar(size),nvarchar(max))
Currency (Money, SmallMoney)

char(size) :-
-----------------

=> allows character data up to 8000 chars

=> recommended for fixed length char columns

ex :- NAME CHAR(10)
 SACHIN - - - -
wasted

RAVI - - - - - -
wasted

=> In char datatype extra bytes are wasted , so char is not recommend for
variable length fields and char is recommended for fixed length fields .

ex :- GENDER CHAR(1)

M
F

STATE_CODE CHAR(2)

AP
TG

COUNTRY_CODE CHAR(3)

IND
USA

VARCHAR(size) :-
----------------------------

=> allows character data up to 8000.

=> recommended for variable length fields

ex :- NAME VARCHAR(10)

SACHIN - - - -
released
 => char / varchar allows ascii chars (256 chars) that includes A-Z,a-
z,0-9,special chars so char / varchar allows alphanumeric data.

ex :-

PANNO CHAR(10)
VEHNO CHAR(10)
EMAILID VARCHAR(30)
VARCHAR(MAX) :-
---------------------------

allows characters up to 2GB.

ex :- review VARCHAR(MAX)

NCHAR/NVARCHAR/NVARCHAR(MAX) :- ( N => National)

------------------------------------------------------------

=> allows Unicode chars (65536) that includes all ascii chars and chars
belongs to different languages.

INTEGER types :-
-------------------------

=> allows numbers without decimal

TYPE MEMORY RANGE

tinyint 1 0 to 255
smallint 2 -2^15 to 2^15-1 (-32768 to 32767)
int 4 -2^31 to 2^31-1
bigint 8 -2^63 to 2^63-1

ex :- age tinyint
empid smallint

NUMERIC(p) :-
-----------------------

=> allows numbers without decimal up to 38 digits

ex :- empid NUMERIC(4)

10
100

=>
 1000
10000 => ERROR

aadhaarno NUMERIC(12)

phone NUMERIC(10)

accno NUMERIC(12)

NUMERIC(p,s) / DECIMAL(p,s) :-
---------------------------------------------------

=> allows numbers with decimal (float)

p => precision => total no of digits

allowed

s => scale => no of digits allowed

after decimal

ex :- SAL NUMERIC(7,2)

5000
5000.55
50000.55
500000.55 => ERROR

5000.5278 => ALLOWED => 5000.53

5000.5638 => ALLOWED => 5000.56

SAVG NUMERIC(5,2)

CURRENCY types :-
---------------------------
 => currency types are used for fields related to money

TYPE MEMORY RANGE

smallmoney 4 -2,14,748.3648 to 2,14,748.3647
money 8 - 922337203685477.5808 to 922337203685477.5807

ex :- SAL SMALLMONEY
BAL MONEY

DATE & TIME :-

-----------------------

DATE => allows only date

TIME => allows only time
DATETIME => allows date & time

=> default date format in SQL server

YYYY-MM-DD

default time format in SQL server

HH:MI:SS

Ex :- DOB DATE

2003-10-05

LOGIN TIME

9:05:10

TXNDT DATETIME

2025-01-25 10:00:00

CREATING TABLES IN SQL SERVER DB :-

-------------------------------------------------------------

=>
CREATE TABLE <tabname>
(
colname datatype(size) ,
colname datatype(size),
----------------------------
)

Rules :-
------------

1 name should start with alphabet

2 name should not contain spaces & special chars but allows ,_, $, #
3 name can be up to 128 chars
4 table can have 1024 columns
5 no of rows unlimited

ex :-

123emp INVALID
emp123 INVALID
emp*123 INVALID
emp_123 VALID

ex :-

=> create table with following structure ?

EMP is table name

EMPID ENAME JOB SAL HIREDATE DEPT

CREATE TABLE EMP

 (
EMPID TINYINT,
ENAME VARCHAR(10),
JOB VARCHAR(10),
SAL SMALLMONEY,
HIREDATE DATE,
DEPT VARCHAR(10)
)

=> above command created table structure that includes columns ,datatype
and size

SP_HELP :- (SP => stored procedure)

==========

=> command used to see the structure of the table

SP_HELP <tabname>

Ex :-

SP_HELP EMP

INSERTING DATA INTO TABLE :-

---------------------------------------
---------
=> "INSERT" command is used to insert data into the table.

=> insert command creates a new row

using insert command we can insert

1 single row

=>
 2 multiple rows

inserting single row :-

--------------------------

INSERT INTO <tabname> VALUES(v1,v2,v3,-----------)

ex :-

INSERT INTO EMP VALUES(100,'sachin','clerk',5000,'2025-01-27','hr')

INSERT INTO EMP VALUES(101,'arvind','manager',8000,getdate(),'it')

inserting multiple rows :-

-------------------------------

INSERT INTO EMP

VALUES(102,'rahul','analyst',9000,'2020-05-10','sales') ,
(103,'kumar','clerk',4000,'2019-02-05','it')

inserting nulls :-
----------------------

=> a null means blank of empty

=> we insert nulls when values are not present (missing)
=> nulls can be inserted in two ways

method 1 :-

INSERT INTO EMP VALUES(104,'satish',NULL,NULL,'2021-10-

26','sales')

method 2 :-
 INSERT INTO EMP(EMPID,ENAME,HIREDATE,DEPT)
VALUES(105,'vijay','2017-04-20','hr')

=> remaining two fields are filled with NULLs

Operators in SQL SERVER :-

=======================

Arithmetic Operators => + - * / %

Relational Operators => > >= < <= = <> !=

Logical Operators ` => AND OR NOT

Special Operators => BETWEEN

IN
LIKE
IS
ANY
ALL
EXISTS ,Pivot

Set Operators => UNION

UNION ALL
INTERSECT
EXCEPT

28-JAN-25

Displaying Data :-
==============

=> "SELECT" command is used to display data from table

=> we can display all rows or specific rows
=> we can display all columns or specific columns

SELECT columns / *
FROM tabname

=>
 [WHERE cond]

SQL = ENGLISH
QUERIES = SENTENCES
CLAUSES = WORDS

=> display all the data from emp table ?

SELECT * FROM EMP

* => all columns

display employee names and salaries ?

SELECT ename, sal FROM emp

WHERE clause :-

=> where clause is used to display specific row/rows from table

WHERE COLNAME OP VALUE

=> where condition returns true / false

=> op must be any relational operator like > >= < <= = <>
=> if Condition = true row is selected
=> if Condition = false row is not selected

ex :-

=> display employee details whose id = 103 ?

SELECT * FROM emp WHERE empid = 103

=> display employee details whose name = rahul ?

SELECT * FROM emp WHERE ename = 'rahul'

=> employees not working for hr dept ?

SELECT * FROM emp WHERE dept <> 'hr'

compound condition :-
-----------------------------

=> multiple conditions combined with AND / OR operators is

called compound condition.

WHERE cond1 AND cond2 result

T T T
T F F
F T F
F F F

WHERE cond1 OR cond2 result

T T T
T F T
F T T
F F F

=> employees working as clerk and manager ?

SELECT * FROM emp WHERE job='clerk' OR job='manager'

=> employees whose id = 100,103,105 ?

SELECT *
FROM emp
WHERE empid = 100 OR empid = 103 OR empid = 105

=> display hr dept clerk details and earning more than 4000 ?
 SELECT *
FROM emp
WHERE dept='hr' AND job='clerk' AND sal>4000

=> employees earning more than 5000 and less than 10000 ?

SELECT *
FROM emp
WHERE sal > 5000 AND sal < 10000

create table student

( sno int,
sname varchar(10),
s1 tinyint,
s2 tinyint,
s3 tinyint
)

INSERT INTO student

VALUES(1,'A',80,90,70) , (2,'B',30,60,50)

=> display employees working as clerk,manager and earning more than

5000 ?

SELECT *
FROM emp
WHERE job='clerk' OR job='manager' AND sal>5000
-------------- -------------------------------------------

above query returns clerks earning less than 5000 because

operator sal > 5000 is applied only to manager but not to clerk
because operator
AND has got more priority than operator OR , to overcome this problem use
( )

SELECT *
FROM emp
WHERE (job='clerk' OR job='manager') AND sal>5000
29-JAN-25

IN operator :-
===========

=> use IN operator for list comparison

=> use IN operator for "=" comparison with multiple values

WHERE COLNAME = V1,V2,V3 => invalid

WHERE COLNAME IN (V1,V2,V3,---) => valid

=> employees whose id = 100,103,105 ?

SELECT *
FROM emp
WHERE empid IN (100,103,105)

BETWEEN operator :-
==================

=> use between operator for range comparison

WHERE COLNAME BETWEEN V1 AND V2

Ex :-

=> employees earning between 5000 and 10000 ?

SELECT *
FROM emp
WHERE sal BETWEEN 5000 AND 10000 ( sal>=5000 and sal<=10000)

=> employees working as clerk, manager and earning between

5000 and 10000 and not joined in 2020 and not working for it,
sales dept?
 SELECT *
FROM emp
WHERE job IN ('clerk', 'manager')
AND
sal BETWEEN 5000 AND 10000
AND
hiredate NOT BETWEEN '2020-01-01' AND '2020-12-31'
AND
dept NOT IN ('it','sales')

LIKE operator:-
============

=> use LIKE operator for pattern comparison

WHERE COLNAME LIKE 'pattern'

=> pattern may contain alphabets, digits, special chars and wildcard chars

Wildcard chars :-
=============

% => 0 or many chars

_ => exactly 1 chars

=> employees name starts with 's' ?

SELECT * FROM emp WHERE ename LIKE 's%'

=> employees name ends with 'd' ?

SELECT * FROM emp WHERE ename LIKE '%d'

=> 'a' is the 4th char from last ?

SELECT * FROM emp WHERE ename LIKE '%a___'

=> employees joined in oct month ? YYYY-MM-DD

SELECT * FROM emp WHERE hiredate LIKE '_____10___'

=> employees name starts with vowels ?

SELECT *
FROM emp
WHERE ename LIKE 'a%'

OR

ename LIKE
'e%'

SELECT * FROM emp WHERE ename LIKE '[aeiou] %'

=> employees name starts between 'a' and 'p' ?

SELECT * FROM emp WHERE ename LIKE '[a-p]%'

30-jan-25

cust list name contains "_" ?

SELECT * FROM CUST WHERE CNAME LIKE '%\_%' ESCAPE '\'

=> list of customers name contains "%" ?

SELECT * FROM CUST WHERE CNAME LIKE '%\%%' ESCAPE '\'

=> name contains 2 "_" ?

SELECT * FROM CUST WHERE CNAME LIKE '%\_%\_%' ESCAPE '\'

=>

SELECT *
FROM EMP
WHERE JOB IN ('CLERK','MAN%')

A ERROR
B RETURNS CLERKs & MANAGERs
C RETURNS ONLY CLERKs
D NONE

ANS :- C

SELECT *
FROM EMP
WHERE JOB = 'CLERK' OR JOB LIKE 'MAN%'

ANS :- B

IS operator :-
===========

=> use IS operator for NULL comparison

WHERE COLNAME IS NULL

WHERE COLNAME IS NOT NULL

Ex :-

=> employees not earning salary ?

SELECT * FROM emp WHERE sal IS NULL

=> employees earning salary ?

SELECT * FROM emp WHERE sal IS NOT NULL

ALIAS:-
=>alias means another name or alternative name
=>used to change column heading

COLNAME /EXPR [AS] ALIAS

Ex :-

=> display ENAME ANNUAL SAL ?

SELECT ENAME,SAL*12 AS ANNSAL

FROM EMP

ORDER BY clause:-

=> order by clause is used to sort table data.

=> using order by we can sort data based on one or more fields
=> using order by we can sort data either in ascending or in descending
order

SELECT columns
FROM tabname
[WHERE cond]
ORDER BY colname ASC/DESC , ------------

=> default order is ASC

Ex :-

=> arrange employee list name wise Asc order ?

SELECT *
FROM emp
ORDER BY ename ASC

=> arrange employee list sal wise desc ?

SELECT *
FROM emp
ORDER BY sal DESC
 NOTE :-
=> in order by clause we can use column name or column number
=> column number is not based on table and it is based on select list

31-JAN-25
=> employees joined in 1981 year and arrange list name wise asc ?

SELECT empno,ename,hiredate
FROM emp
WHERE hiredate LIKE '1981%'
ORDER BY ename ASC

DISTINCT clause:-

=> Distinct clause eliminates duplicates from select Statement output

SELECT DISTINCT column name

SELECT DISTINCT col1,col2
SELECT DISTINCT *

SELECT DISTINCT DEPTNO FROM EMP

10
20
30

TOP clause :-

=> TOP clause is used to select top n rows

SELECT TOP <n> * / columns

FROM tabname
[WHERE cond]
[ORDER BY colname ASC/DESC]

Ex :-
 => display first 5 rows from emp ?

SELECT TOP 5 *
FROM emp

DML commands:-(Data Manipulation Lang)

---------------------------

INSERT
UPDATE
DELETE
MERGE

=> all DML commands acts on table data.

=> all DML commands are implicitly committed (saved).
=> to stop auto commit execute the following command

SET IMPLICIT_TRANSACTIONS ON

=> to save the operation execute commit.

=> to cancel the operation execute rollback.

UPDATE command :-
-------------------------------

=> command used to modify table data.

=> we can update all rows or specific rows
=> we can update single column or multiple columns

UPDATE tabname
SET colname = value , colname = value ,
[WHERE cond]

=> update all employees comm with 500 ?

UPDATE emp SET comm = 500

=> increment sal by 20% and comm by 10% those joined
in 1981 year as salesman ?

UPDATE emp
SET sal = sal + (sal*0.2) , comm = comm + (comm*0.1)
WHERE hiredate LIKE '1981%'
AND
job='SALESMAN'

=> transfer employees from 10th dept to 20th dept ?

UPDATE emp SET deptno = 20 WHERE deptno = 10

1-feb-25

DELETE command:-

=> command used to delete row/rows from table.

DELETE FROM tabname [WHERE cond]

Ex :-

=> delete all rows from emp table ?

DELETE FROM emp

=> delete employees joined in 1980 year ?

DELETE FROM emp WHERE hiredate LIKE '1980%'

NOTE :-

=> rollback command cancels all uncommitted operations

DDL commands :- (Data Definition Lang)
----------------------

CREATE
ALTER
DROP
TRUNCATE

TABLE = STRUCTURE + DATA

(COLS) (ROWS)

DDL DML

=> all DDL commands acts on table structure (columns,

datatype and size) => all DDL commands are auto
committed.

=> to stop auto commit execute the following command

SET IMPLICIT_TRANSACTIONS ON

=> to save DDL execute commit.

=> to cancel DDL execute rollback

ALTER command :-

=> command used to modify table structure

=> using ALTER command we can

1 add columns
2 drop columns
3 modify a column
changing datatype , changing size

Adding columns :-
------------------------
 ALTER TABLE tabname

ADD colname datatype(size) , colname datatype(size) ,--------

ex
:-

=> add columns gender,dob to emp table ?

ALTER TABLE emp

ADD gender char(1), dob date

after adding by default the new columns are filled with NULLs,
to insert data into the new columns use update command.

1 UPDATE emp SET gender = 'M' WHERE empno = 7566

Drop column :-
------------------------

ALTER TABLE tabname

DROP COLUMN col1,col2,---------

Ex :-

=> drop columns gender, dob from emp?

ALTER TABLE emp
DROP COLUMN gender, dob

Modifying a column :-
----------------------------

ALTER TABLE tabname

ALTER COLUMN colname datatype(size)

Ex :-
 => modify column empno datatype to int ?

ALTER TABLE emp

ALTER COLUMN empno INT

=> modify column comm datatype to smallmoney ?

ALTER TABLE emp

ALTER COLUMN comm SMALLMONEY

=> increase the size of ename to 20 ?

ALTER TABLE emp

ALTER COLUMN ename VARCHAR(20)

Drop command :-
-----------------------

=> command used to drop table from db.

=> drops table structure along with data.

DROP TABLE <tabname>

Ex :-

=> drop table emp from db ?

DROP TABLE emp

TRUNCATE command :-
----------------------------------

=> command deletes all rows from table but

keeps structure
=> will empty the table.
=> releases memory allocated for table
 TRUNCATE TABLE <tabname>

Ex :-

TRUNCATE TABLE emp

=> when above command is executed SQL server goes to

memory and releases all pages allocated for table, when
pages are released then data stored in pages are also
deleted.

DELETE VS TRUNCATE :-
-----------------------------------

DELETE TRUNCATE

1 DML DDL

2 can delete all rows can delete only all rows

and specific rows but cannot delete specific rows

3 where condition can be where condition cannot be

used with delete used with truncate

4 deletes row-by-row deletes all rows at a time

5 slower faster

6 will not release memory releases memory

7 will not reset identity will reset identity

SP_RENAME :- (SP => stored procedure)

------------------------

=> used to rename table and column

 SP_RENAME 'oldname', 'newname'

Ex :-

=> rename table emp to employees ?

SP_RENAME 'EMP','EMPLOYEES'

=> rename column comm to bonus ?

SP_RENAME 'EMPLOYEES.COMM','BONUS'

3-feb-24

Built-in
Functions :-

=> a function accepts some input and perform some calculation and
returns one value.

Types of functions :-
================

1 Date
2 Character
3 Numeric
4 Conversion
5 Special
6 Analytical
7 Aggregate

Date Functions :-
------------------------

1 GETDATE() :-
--------------------

=> returns current date & time

SELECT GETDATE() => 2025-02-03 11:37:07.320

2 DATEPART() :-
--------------------

=> returns part of the date

DATEPART(INTERVAL , DATE)

Ex :-

SELECT DATEPART(YY, GETDATE()) => 2025

MM => 02
DD => 03
DW => 02 (Day of the week / 7)
DY => 34 (Day of the year / 365)
HH => 11
MI => 44
SS => 20
QQ => 1 (1-4)

JAN-MAR 1
APR-JUN 2
 JUL-SEP 3
OCT-DEC 4

=> display ENAME YEAR_OF_JOIN ?

SELECT ENAME,DATEPART(YY,HIREDATE) AS YEAR_OF_JOIN

FROM EMP

=> display employee details joined in 1980,1983,1985 ?

SELECT *
FROM EMP
WHERE DATEPART(YY,HIREDATE) IN (1980,1983,1985)

=> display employee details joined in jan,apr,dec months ?

SELECT *
FROM EMP
WHERE DATEPART(MM,HIREDATE) IN (1,4,12)

=> employees joined in leap year ?

SELECT *
FROM EMP
WHERE DATEPART(YY,HIREDATE)%4=0

=> employees joined on sunday ?

SELECT *
FROM EMP
WHERE DATEPART(DW,HIREDATE) = 1

=> employees joined in 2nd quarter of 1981 year ?

SELECT *
 FROM EMP
WHERE DATEPART(YY,HIREDATE) = 1981
AND
DATEPART(QQ,HIREDATE) = 2

DATENAME() :-
-----------------------

=> returns part of the date

MM DW

DATEPART 2 2

DATENAME February Monday

Ex :-

=> display ENAME DAY ?

SELECT ename, DATENAME(DW,hiredate) as day

FROM emp

=> waq to display on which day india got independence ?

SELECT DATENAME(DW,'1947-08-15') => Friday

FORMAT() :-
-----------------

=> function used to display date in different formats.

FORMAT(DATE , 'format')

Ex :-

SELEC FORMAT(GETDATE(),'dd-MM-yyyy') => 03-02-2025

'dd-MM-yyyy hh:mm:ss') => 03-02-2025 12:17:14

 'dd-MMMM-yyyy') => 03-February-2025

'dd-MM-yyyy dddd') => 03-02-2025 Monday

display hiredate in mm/dd/yyyy format and also display day of the

week ?

"=" comparision with GETDATE() always fails because sql server not
only compares dates but it also compares date & time , To overcome
this problme use FORMAT function

SELECT *
FROM EMP
WHERE HIREDATE = FORMAT(GETDATE(),'yyyy-MM-dd')
2025-02-03 = 2025-02-03

DATEADD() :-
--------------------

=> used to add / subtract days, months, years to/from a date

DATEADD(INTERVAL , INT , DATE)

Ex :-

SELECT DATEADD(YY,1,GETDATE()) => 2026-02-03 12:39:20.857

SELECT DATEADD(MM,2,GETDATE()) => 2025-04-03 12:30:20.857

SELECT DATEADD(DD,10,GETDATE()) => 2025-02-13

SELECT DATEADD(MM,-2,GETDATE()) => 2024-12-03

2025-02-04 ?

=> display today's gold rate ?

SELECT *
FROM GOLD_RATES
WHERE DATEID = FORMAT(GETDATE(),'yyyy-MM-dd')

4-FEB-25

DATEDIFF() :-
-------------------

=> returns difference between two dates

=> differences can be calculated in days, months, years

DATEDIFF(INTERVAL,START DATE,END DATE)

Ex :-

SELECT DATEDIFF(YY,'2024-02- => 1

04',GETDATE())

SELECT DATEDIFF(MM,'2024-02- => 12

04',GETDATE()

SELECT DATEDIFF(DD,'2024-02- => 366

04',GETDATE()

=> display ENAME EXPERIENCE in years ?

SELECT ENAME ,
DATEDIFF(YY,HIREDATE,GETDATE()) AS EXPERIENCE
FROM EMP

EOMONTH() :-
-------------------------

=> returns end of the month i.e. last day of the month

EOMONTH(DATE,INT)

Ex :-

SELECT => 2025-02-

EOMONTH(GETDATE(),0) 28

SELECT => 2025-03-

EOMONTH(GETDATE(),1) 31

SELECT => 2025-01-

EOMONTH(GETDATE(),-1) 31

Character functions :-
--------------------------------

UPPER() :-
------------------

=> converts string to uppercase

UPPER(string/colname)

Ex :-
SELECT UPPER('welcome') => WELCOME

LOWER() :-
-----------------

=> converts string to lowercase

LOWER(string)
Ex :-

display EMPNO ENAME SAL ? display names in lowercase ?

SELECT empno , LOWER(ename) as ename,sal FROM emp

LEN() :-
-------------

=> returns string length i.e. no of chars

LEN(string / colname)

Ex :-

SELECT LEN('HELLO WELCOME') => 13

=> display employee details name contains more than 5 chars ?

SELECT *
FROM emp
WHERE LEN(ename) > 5

LEFT() & RIGHT() :-

----------------------------

LEFT(string,no of chars) => returns chars starting from left side

RIGHT(string,no of chars) => returns chars starting from right side

Ex :-

SELECT LEFT('HELLO => HELLO

WELCOME',5)
 SELECT RIGHT('HELLO =>
WELCOME',7) WELCOME

SELECT RIGHT('HELLO => COME

WELCOME',4)

String concatenation => +

'a' + 'b' => ab

SELECT empno,
ename,
LEFT(ename,3) +
LEFT(empno,3) +
'@tcs.com' as emailid
FROM emp

=> store emailids in db ?

STEP 1 :- add emailid column to emp table

ALTER TABLE emp

ADD emailid VARCHAR(30)

STEP 2 :- update the column with emailids

UPDATE emp
SET emailid = LEFT(ename,3) + LEFT(empno,3) + '@tcs.com'

5-feb-25

SUBSTRING() :-
-------------------------

=> returns part of the string starting from specific position

 SUBSTRING(string,start,no of chars)

Ex :-

SELECT SUBSTRING('HELLO WELCOME',10,3) => COM

SELECT SUBSTRING('HELLO WELCOME',7,7) => WELCOME

CHARINDEX() :-
------------------------

=> returns position of a character in a

string

CHARINDEX(char,string,
[start])

Ex :-

SELECT CHARINDEX('O','HELLO WELCOME') => 5

SELECT CHARINDEX('K','HELLO WELCOME') => 0

SELECT CHARINDEX('O','HELLO WELCOME',7) => 11

SELECT CHARINDEX('E','HELLO WELCOME',10) => 13

scenario :-
-------------

CUST

CID CNAME DOB

10 sachin tendulkar 1984-04-20
11 virat kohli 1992-10-05

=> display CID FNAME LNAME AGE ?

 first name = SUBSTRING(CNAME,1,CHARINDEX(' ',CNAME)-1)

last name = SUBSTRING(CNAME,CHARINDEX(' ',CNAME)+1 , LEN(CNAME))

SELECT CID,

SUBSTRING(CNAME,1,CHARINDEX(' ',CNAME)-1) AS FNAME,

SUBSTRING(CNAME,CHARINDEX(' ',CNAME)+1 , LEN(CNAME)) AS LNAME ,

DATEDIFF(YY,DOB,GETDATE()) AS AGE
FROM CUST

REPLICATE() :-
---------------------

=> repeats characters for given no of times

REPLICATE(char , length)

Ex :-

SELECT REPLICATE('*',10) => ***********

REPLACE() :-
-------------------

=> used to replace one string with another string

REPLACE(str1,str2,str3)

Ex :-

SELECT REPLACE('HELLO','ELL','ABC') => HABCO

SELECT REPLACE('HELLO','L','ABC') => HEABCABCO

SELECT REPLACE('HELLO','ELO','ABC') => HELLO

 SELECT REPLACE('@@HE@@LL@@O@@','@','') => HELLO

=> replace 1981 with 2021 in hiredate ?

UPDATE EMP
SET HIREDATE = REPLACE(HIREDATE,'1981','2021')

TRANSLATE() :-
---------------------

=> function used to translate one char to another char

TRANSLATE(str1,str2,str3)

Ex :-

SELECT TRANSLATE('HELLO','ELO','ABC') => HABBC

E => A
L => B
O => C

SELECT TRANSLATE('HELLO','ELO','') => ERROR

NOTE :-

=> translate function can be used to encrypt data i.e. converting plain text
to cipher text

Ex :-

SELECT ENAME ,
TRANSLATE(SAL,'0123456789.' , '$bT*r@&#%^!') AS SAL
FROM EMP

JONES 2975.00 T^#@!$$

=> remove all special chars from @!HE#$LL%^O&* ?

 SELECT
REPLACE(TRANSLATE( '@!HE#$LL%^O&*','@!#$%^&*','********'),'*','')
---------------------------------------------------------------------------
**HE**LL**O**

6-FEB-25

Numeric functions :-
================

ROUND
CEILING
FLOOR

ROUND() :-
----------------

=> function used to round number to integer or to decimal places

ROUND(number, decimal places,[option])

Ex :-

SELECT ROUND(38.5678,0) => 39

38-------------------------------------38.5--------------------------------------39

number >= avg => rounded to highest

number < avg => rounded to lowest

SELECT ROUND(38.4678,0) => 38

SELECT ROUND(38.5678,2) => 38.57

SELECT ROUND(386,-2) => 400

300-----------------------------350----------------------------------400

SELECT ROUND(386,-1) => 390

380-------------------------------385----------------------------------390

SELECT ROUND(386,-3) => 0

0-----------------------------------500----------------------------------1000

ROUND(number ,decimal places ,option) :-

-------------------------------------------------------

if option = 1 => number is truncated i.e. always rounded to lowest

ROUND(38.5678,0,1) => 38
ROUND(38.5678,2,1) => 38.56
ROUND(38.5678,3,1) => 38.567

ROUND(386,-2,1) => 300

ROUND(386,-1,1) => 380
ROUND(999,-3,1) => 0

CEILING() :-
------------------

=> rounds number always to highest

CEILING(number)

Ex :-

SELECT CIELING(3.1) => 4

 FLOOR() :-
---------------

=> rounds number always to lowest

FLOOR(number)

Ex :-

SELECT FLOOR(3.9) => 3

Conversion functions :-
--------------------------------

=> functions used to convert one datatype to another datatype

1 CAST
2 CONVERT

CAST :-
-----------

CAST(expression AS datatype)

Ex :-

SELECT CAST(10.5 AS INT) => 10

=> display allen earns 1600 ?

SELECT ename + ' earns ' + CAST(sal AS VARCHAR) FROM emp

CONVERT() :-
-----------------
 CONVERT(TARGET-TYPE , SOURCE-VALUE)

Ex :-
SELECT CONVERT(INT,10.5) => 10

difference between CAST & CONVERT ?

1 using convert function we can display dates in different formats

but not possible using cast

2 using convert we can display money in different styles but not

possible using cast

Displaying Dates in different styles :-

---------------------------------------------------

=> to display dates in different formats first convert date to char type

CONVERT(VARCHAR , DATE , STYLE-NUMBER)

Ex :-

SELECT CONVERT(VARCHAR, GETDATE(), 101) => 02/06/2025

=> display ENAME HIREDATE ?

display hiredate in DD/MM/YYYY

format ?

SELECT ENAME,CONVERT(VARCHAR,HIREDATE,103) AS HIREDATE FROM EMP

=> converting date string to date ?

SELECT CONVERT(DATE , '06-02-2025',105) => 2025-02-06

 SELECT CONVERT(DATE , '02/06/2025',101) => 2025-02-06

07-feb-25

Displaying money in different styles:-

CONVERT(VARCHAR,MONEY,1)

Ex :-

SELECT ENAME,CONVERT(VARCHAR,SAL,1) AS SAL FROM EMP

allen 1,600.00
ward 1,250.00

Special functions :-
--------------------------

ISNULL() :-
---------------

=> function used to convert null values

ISNULL(arg1,arg2)

if arg1 = null returns arg2

if arg1 <> null returns arg1 only

Ex :-

SELECT ISNULL(100,200) => 100

SELECT ISNULL(NULL,200) => 200

SELECT ENAME, SAL, COMM, SAL+ ISNULL (COMM ,0) AS TOTSAL FROM EMP

ALLEN 1600 300 1900

JONES 3000 NULL 3000
 SELECT ENAME,SAL,ISNULL(CAST(COMM AS VARCHAR),'N/A') AS COMM FROM EMP

Analytical / Window / OLAP functions :-

-----------------------------------------------------

RANK & DENSE_RANK :-

-------------------------------------

=> these functions are used to find ranks

=> ranking is based on some column
=> for rank functions data must be sorted

RANK() OVER (ORDER BY COLNAME ASC/DESC ,---)

DENSE_RANK() OVER (ORDER BY COLNAME ASC/DESC ,----)

Ex :-

=> display ranks of the employees based on sal and highest paid
employee should get 1st rank ?

SELECT EMPNO,ENAME,SAL,
RANK() OVER (ORDER BY SAL DESC) AS RNK
FROM EMP

SELECT EMPNO,ENAME,SAL,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK
FROM EMP

difference between rank & dense_rank ?

1 rank function generates gaps but dense_rank will not generate gaps.

2 in rank function ranks may not be in sequence but in dense_rank ranks

are always in sequence.
 SAL RANK DENSE_RANK
5000 1 1
3000 2 2
3000 2 2
3000 2 2
2000 5 3
2000 5 3
1000 7 4

PARTITION BY clause :-
-----------------------------------

=> partition by clause is used to divide the table based on one or more
columns

=> used to find ranks with in group , for ex to find ranks with in dept
first divide the table dept wise and apply rank/ dense_rank functions
on each dept instead of applying on whole table.

Ex :-

=> the following query finds ranks of the employees with in dept based on
sal ?

SELECT EMPNO, ENAME, SAL, DEPTNO,

DENSE_RANK()OVER (PARTITION BY DEPTNO ORDER BY
SAL DESC) AS RNK
FROM EMP

ROW_NUMBER() :-
----------------------------

=> returns record number

=> row_number is also based on some column
=> for row_number also data must be sorted
 ROW_NUMBER() OVER (ORDER BY COL ASC/DESC)

Ex :-

SELECT EMPNO,ENAME,SAL,
ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RNO
FROM EMP

7839 king 5000.00 1

7566 jones 3000.00 2
7902 ford 3000.00 3
7788 scott 3000.00 4
7698 blake 2900.00 5

8-FEB-25

LAG & LEAD :-

----------------------

LAG(colname,int) OVER (ORDER BY col ASC/DESC) => returns

previous value
LEAD(colname,int) OVER (ORDER BY col ASC/DESC) => returns next
value

Ex 1 :-

SELECT ename,sal,
LAG(sal,1) OVER (ORDER BY empno ASC) as prev_sal
FROM emp

Ex 2 :-

create table population

( year
int ,
population numeric
)
INSERT INTO population VALUES(2015,1328024498) ,
(2020,1402617695),
(2022,1425423212),
(2023,1438069596),
(2024,1450935791)

=> display YEAR POPULATION GROWTH ?

SELECT year , population,

population - lag(population,1) over (order by year asc) as
growth
FROM population

=> display YEAR POPULATION GROWTH(%) ?

Aggregate Functions :-
-----------------------------------

=> these functions process multiple rows and returns one value

MAX
MIN
SUM
AVG
COUNT
COUNT(*)

MAX() :-
------------

=> returns maximum value

 MAX(arg)
Ex :-

SELECT MAX(sal) FROM emp => 5000

MIN() :-
----------

=> returns minimum value

MIN(arg)

Ex :-

SELECT MIN(Sal) FROM emp => 1000.00

SUM() :-
--------------

=> returns total

SUM(arg)

Ex :-

SELECT SUM(sal) FROM emp => 28500.00

AVG() :-
-----------

=> returns average value

AVG(arg)
Ex :-

SELECT AVG(SAL) FROM EMP => 2192.3076

NOTE :-

sum, Avg functions cannot be applied on char, date columns

COUNT() :-
---------------

=> returns no of values present in a column

COUNT(arg)

Ex :-

SELECT COUNT(EMPNO) FROM EMP => 13

COUNT(*) :-
-------------------

=> returns no of rows in a table

SELECT COUNT(*) FROM EMP => 13

COUNT(F1) => 3 => nulls are not counted

COUNT(*) => 5 => nulls are counted

NOTE :-

=> aggregate function are not allowed in where clause and allowed only in
select, having clauses

SELECT deptno
 FROM emp
WHERE COUNT(*) > 3 => ERROR

========================================================================
==

How to implement IF-ELSE in SQL :-

------------------------------
=> to implement IF-ELSE, SQL server provided case statement.
=> using case statement we can return values based on conditions.
=> case statements are 2 types

1 simplecase
2 searched case

simple case :-
------------------

=> use simple case when conditions based on "=" operator

CASE COLNAME
WHEN VALUE1 THEN RETURN EXPR1
WHEN VALUE2 THEN RETURN EXPR2
WHEN VALUE3 THEN RETURN EXPR3
------
ELSE RETURN EXPR
END

Ex :-

=> display ENAME SAL DEPT ?

IF DEPTNO = 10 DISPLAY HR
20 IT
30 SALES
OTHERS UNKNOWN
SELECT ENAME,SAL,
 CASE DEPTNO
WHEN 10 THEN 'HR'
WHEN 20 THEN 'IT'
WHEN 30 THEN 'SALES'
ELSE 'UNKNOWN'
END AS DEPT
FROM EMP

Searched case :-
------------------------

=> use searched case when conditions not based on "=" operator

CASE
WHEN COND1 THEN RETURN EXPR1
WHEN COND2 THEN RETURN EXPR2
---------------------------
ELSE RETURN EXPR
END

Ex :-

=> display ENAME SAL SALRANGE ?

IF SAL > 3000 DISPLAY HISAL

SAL < 3000 DISPLAY LOSAL OTHERWISE
AVGSAL

SELECT ENAME,SAL,
CASE
WHEN SAL > 3000 THEN 'HISAL'
WHEN SAL<3000 THEN 'LOSAL'
ELSE 'AVGSAL'
END AS SALRANGE
FROM EMP

========================================================================
==

11-FEB-25
 GROUP BY clause :-
================

=> GROUP BY clause is used group rows based on one or more columns to
calculate min, max, sum, avg, count for each group. For ex to calculate dept
wise total sal then first group the records based on deptno and apply sum
function on each dept.

1 A 3000 20
2 B 5000 30 group by 10 7000
3 C 4000 10 =========> 20 9000
4 D 6000 20 30 5000 5E 3000 10

detailed data summarized data

=> GROUP BY clause can be used to convert detailed data into summarized data
which is useful for analysis.

SELECT columns
FROM tabname
[WHERE cond]
GROUP BY col1,col2,----
[HAVING cond]
[ORDER BY col1 ASC/DESC,------]

Execution:-

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Ex :-

=> display dept wise total salary ?

 SELECT deptno,SUM(sal) as totsal
FROM emp
GROUP BY deptno FROM emp

NOTE :-

=> column alias cannot be used in GROUP BY clause because GROUP

BY clause is executed before SELECT,

=> column alias can be used in ORDER BY clause because ORDER

BY clause is executed after SELECT.
=> display day wise no of employees joined ?

SELECT DATENAME(dw,hiredate) as day,

COUNT(*) as cnt
FROM emp
GROUP BY DATENAME(dw,hiredate)

SQL server cannot calculate dept wise count before group by

and it can calculate only after group by, so apply the condition
count(*)>3 after group by using HAVING clause.

SELECT deptno ,count(*) as cnt

FROM emp
GROUP BY deptno
HAVING COUNT(*) > 3

20 5
30 6

WHERE VS HAVING:-

WHERE HAVING

1 selects specific rows selects specific groups

2 conditions applied conditions applied after group by
before group by

3 use where clause if use having clause

cond doesn't contain if cond contains aggregate
aggregate function functions

=> find southern states having more than 5cr total no of persons ?

PERSONS
AADHARNO NAME GENDER ADDR CITY STATE

SELECT STATE
FROM PERSONS
WHERE STATE IN ('AP','TG','KA','KL','TN')
GROUP BY STATE
HAVING COUNT(*) > 50000000

ROLLUP & CUBE :-

=> both functions displays subtotals and grand total.

GROUP BY ROLLUP(col1,col2,--)
GROUP BY CUBE(col1,col2,-----)

ROLLUP :-
----------------
=> rollup displays subtotals for each group (deptno) and also displays
grand total.

SELECT deptno,job,SUM(sal) as totsal

FROM emp
GROUP BY ROLLUP(deptno,job)
ORDER BY deptno ASC

CUBE :-
--------------
 => cube displays subtotals for each group by column (deptno,job) and also displays
grand total

SELECT deptno,job,SUM(sal) as totsal

FROM emp
GROUP BY CUBE(deptno,job)
ORDER BY deptno ASC

GROUPING_ID() :-
--------------------------

=> this function accepts group by columns and returns subtotal belongs to which
group by column

Ex :- GROUPING_ID(deptno,job)

1 => subtotal belongs to 1st group by column i.e. deptno

2 => subtotal belongs to 2nd group by column i.e. job
3 => grand total

SELECT deptno,job,SUM(sal) as totsal,

CASE GROUPING_ID(deptno,job)
WHEN 1 THEN 'Dept Subtotal'
WHEN 2 THEN 'Job Subtotal'
WHEN 3 THEN 'Grand Total'
END as subtotals
FROM emp
GROUP BY CUBE(deptno,job)
ORDER BY deptno ASC ,job ASC

13-feb-25
INTEGRITY CONSTRAINTS
=>Integrity Constraints are rules to maintain data quality or data consistency or data
integrity
=> used to prevent users from entering invalid data.
=> used to enforce rules like min bal must be 1000

Types of constraints :-

1 NOT NULL
2 UNIQUE
3 PRIMARY KEY
4 CHECK
5 FOREIGN KEY
6 DEFAULT

=> above constraints can be declared in two ways

1 column level
2 table level

column level :-
-------------------

=> if constraints are declared immediately after declaring column

then it is called column

CREATE TABLE <tabname>

(
colname datatype(size)
constraint ,
colname datatype(size)
constraint ,
---------------------------
)

NOT NULL :-
-----------------

=> NOT NULL constraint doesn't accept null values.

 => if column declared with NOT NULL is called mandatory column.

Ex :-

CREATE TABLE emp15

(
empno INT ,
ename VARCHAR(10) NOT
NULL
)

INSERT INTO emp15 VALUES(100,NULL) => ERROR

UNIQUE :-
---------------

=> unique constraint doesn't accept duplicates

ex :-

CREATE TABLE cust

( custid INT
,
cname VARCHAR(10) NOT NULL,
emailid VARCHAR(20) UNIQUE
)

INSERT INTO cust VALUES(100,'A','abc@gmail.com')

INSERT INTO cust VALUES(101,'B','abc@gmail.com') => ERROR

How to allow multiple nulls :-

--------------------------------------

step 1 :- create table

CREATE TABLE cust

(custid INT,
cname VARCHAR(10) NOT NULL,
emailid VARCHAR(20)
 )

+ step 2 :- create unique index On

emailid

CREATE UNIQUE INDEX ind10 ON CUST(emailid) WHERE emailid IS NOT NULL

step 3 :- insert data cust table

INSERT INTO cust VALUES(1,'A','abc@gmail.com')

INSERT INTO cust VALUES(2,'B','abc@gmail.com') => ERROR
INSERT INTO cust VALUES(3,'C',NULL) => 1 row affected
INSERT INTO cust VALUES(4,'D',NULL) => 1 row affected

PRIMARY KEY :-
-----------------------

=> primary key doesn't accept duplicates and nulls.

=> primary key is the combination of unique and not null.

primary key = unique + not null

=> In tables one column must be there to uniquely identify the

records and into that column duplicates and nulls are not allowed, so
declare that column with primary key.

ex :-

CREATE TABLE emp20

(
empid INT PRIMARY KEY,
ename VARCHAR(10) NOT NULL
)

INSERT INTO emp20 VALUES(100,'A')

INSERT INTO emp20 VALUES(100,'B') => ERROR
INSERT INTO emp20 VALUES(NULL,'C') => ERROR

NOTE :-
 => a table can have only one primary key, if we want multiple primary
keys then declare one column with primary key and other columns with
unique not null.

difference between unique & primary key

UNIQUE PRIMARY KEY

1 allows one null doesn't allow nulls

2 a table can have table can have only one primary key
multiple unique constraints
3 SQL server creates SQL server creates
a non clustered index a clustered index on primary key column
on unique column

candidate key :-
---------------------

=> a field eligible for primary key is called candidate key .

ex :- vehicle vehno vname model

cost chassisno

candidate keys :- vehno, chassisno

primary key :- vehno
secondary key :- chassisno
or
alternate key

=> while creating table secondary keys are declared with unique not null.

14-feb-25

check constraint :-
-------------------------

=> use check constraint when rule based on condition

check(condition)

Ex 1 :- sal must be min 3000

 CREATE TABLE emp16
(
empno INT PRIMARY KEY,
ename VARCHAR(10) NOT NULL,
sal MONEY CHECK(sal>=3000)
)

NOTE :- check constraint allows nulls

EX 5 :- emailid must contain '@'

must end with '.com' or '.co' or '.in'

EMAILID VARCHAR(20) CHECK(EMAILID LIKE '%@%'

AND
(
EMAILID LIKE '%.com'
OR
EMAILID LIKE '%.in'
OR
EMAILID LIKE '%.co'
))

FOREIGN KEY :-
-------------------------

=> foreign key is used to establish relationship between two or more tables.

Ex :-

EMP
empid ename sal projid pname duration cost client
1 A 5000 100 TATA 5 800 TATA MOTORS
2 B 4000 100 TATA 5 800 TATA MOTORS
3 C 3000 100 TATA 5 800 TATA MOTORS

=> above table contains repeation of data called redundency , so to

reduce this redundency divide the table into two tables and establish
relationship.
 => To establish relationship take pk of one table and add it to another
table as fk and declare with references constraint.

PROJECTS
Projid pname duration cost client
100 TATA 5 800 TATA MOTORS
101 L&T 4 600 L&T

EMP
empid ename sal projid REFERENCES projects(projid)
1 A 5000 100
2 B 4000 101
3 C 3000 999 => invalid
4 D 4000 100
5 E 3000 NULL

=> values entered in fk column should match with values entered in pk

column
=> fk allows duplicates & nulls.

=> after declaring fk a relationship is created between two tables called

parent / child relationship.

=> pk table is parent and fk table is child.

create table projects

(
projid int primary key,
pname varchar(20) not null ,
client varchar(20) not null
)

insert into projects values(100,'TATA','TATA MOTORS'),

(101,'L&T','L&T')

create table emp_proj

(
empid int primary key, ename
varchar(10) not null, sal money
check(sal>=3000), projid int
references projects(projid)
 )
insert into emp_proj values(1,'A',4000,100)
insert into emp_proj values(2,'B',5000,999) =>
error insert into emp_proj
values(3,'C',3000,100) insert into emp_proj
values(4,'D',3000,NULL)

Relationship Types :-
---------------------------

1 one to one (1:1)

2 one to many (1:m)
3 many to one (m:1)
4 many to mnay(m:n)

=> by default SQL server creates one to many relationship between two tables

How to establish one to one :-

----------------------------------------
=> To establish one to one relationship then declare fk with unique constraint.

Ex :-

DEPT
dno dname
10 HR
20 IT
30 SALES

MGR mgrno mname start_date end_date dno REFERENCES

dept(dno)
1 A /// NULL 10 UNIQUE
2 B ? NULL 20
3 C ? NULL 30

How to establish many to many relationship :-

------------------------------------------------------------
=> To establish many to many relationship , create 3rd table
and add primary keys of both tables as foreign keys.

ex :-
STUDENT COURSE
sid sname cid cname
1 A 10 .NET
2 B 11 SQL SERVER

REGISTRATIONS
sid cid dor fee
1 10 ? ?
1 11 ? ?
2 10 ? ?

NOTE :-

=> if relationship is one to one then fk can be added to any table.

=> if relationship is one to many fk must be added to many side table.

=> if relationship is many to many then create 3rd table and add pks of both tables as
foreign keys

ER Model Relational Model

1 used in design phase used in development phase

2 entities tables

3 attributes fields

4 relationship foreign key

Relational model :-
---------------------------

BANK
 code name addr
-------

BRANCH branch_id name addr

code(fk)
-------------

ACCOUNT
accno actype bal branch_id (fk) custid(fk)
---------

LOAN loan_id loan_type amount branch_id(fk) custid(fk)

-----------

CUSTOMER custid name

addr phone
---------

15-feb-25

DEFAULT:-

=> a column can be declared with default value as follows

ex :- hiredate date default getdate()

=> while inserting if we skip hiredate then SQL server inserts default value.

CREATE TABLE emp77

(
empno INT PRIMARY KEY,
ename VARCHAR(10) NOT NULL,
hiredate DATE DEFAULT GETDATE()
)

INSERT INTO emp77(empno,ename)VALUES(100,'A')

Question :-

=> create tables with given rules ?

ACCOUNTS
 ACCNO ACTYPE BAL

Rules :-

1 accno should not be duplicate & null

2 actype must be 'S' OR 'C'
3 bal must be min 1000

TRANSACTIONS
TRID TTYPE TDATE TAMT ACCNO

Rules :-

1 trid should not be duplicate & null

2 ttype must be 'W' OR 'D'
3 tdate always equal to sysdate
4 accno should match with accounts table accno

TABLE LEVEL :-
------------------------

=> if constraints are declared after declaring all columns then it is called table level
=> use table level to declare constraints for multiple or combination of columns

Declaring check constraint at table level:-

RULE :- exp_dt > mfd_dt

CREATE TABLE products

(
prodid INT PRIMARY KEY,
pname VARCHAR(10) NOT NULL,
mfd_dt DATE , exp_dt DATE ,
CHECK(exp_dt > mfd_dt)
)

Composite primary key :-

 => if combination of columns declared primary key then it is called composite
primary key.
=> In some tables we may not be able to uniquely identify by using
single column and we need combination of columns to uniquely
identify and that combination should be declared primary key at table
level.

=> In composite primary key combination should not be duplicate.

create table registrations

(
sid int references student(sid) ,
cid int references course(cid),
dor date, fee money,
primary key(sid,cid)
)

Primary key(sid,cid) these two primary keys combined as one composite Primary key

Question :-

SALES
DATEID PRODID CUSTID QTY AMT
2025-02-14 100 10 1 2000
2025-02-14 100 11 1 2000
2025-02-14 101 10 1 1000
2025-02-15 100 10 1 2000

=> identity primary key and write create table script ?

17-feb-25

composite foreign key :-

=> if combination of columns declared foreign key then it is called composite foreign
key.
=> a composite foreign key refers composite primary key.

ex :-
 STUDENT COURSE
sid sname c
cname
1 A 10 .NET
2 B 11 SQL SERVER

REGISTRATIONS sid
cid dor fee
------------------
1 10 ? ?
1 11 ? ?
2 10 ? ?

CERTIFICATES
certno doi sid cid
1000 10/ 1 10
1001 11/ 1 11
1002 12/ 2 11

=> In the above example sid,cid combination should match with registrations table
sid,cid combination, so declare this combination as foreign key that references
registrations table primary key (sid,cid).

CREATE TABLE CERTIFICATE

(
certno INT PRIMARY KEY,
doi DATE, sid INT,
cid INT ,
FOREIGN KEY(sid,cid) REFERENCES REGISTRATIONS(sid,cid)
)

INSERT INTO CERTIFICATE VALUES(1000,getdate(),1,10)

INSERT INTO CERTIFICATE VALUES(1001,getdate(),2,11) => ERROR

Which of the following constraint cannot be declared at table level ?

A UNIQUE
B CHECK
C NOT NULL
D PRIMARY KEY
E FOREIGN KEY
 Which statements are true regarding constraints ?

A a foreign key cannot contain NULL value F

B a column with UNIQUE constraint can contain NULL value T
C a constraint is enforced only for the INSERT operation on a table F
D all constraints can be defined at column level and table level. F
Which CREATE TABLE statement is valid ?

A. CREATE TABLE ord_details

(ord_no NUMERIC(2) PRIMARY KEY,
item_no NUMERIC(3) PRIMARY KEY,
ord_date DATE NOT NULL);

B. CREATE TABLE ord_details

(ord_no NUMERIC(2) UNIQUE , NOT
NULL, item_no NUMERIC(3),
ord_date DATE DEFAULT GETDATE()
NOT NULL);

C. CREATE TABLE ord_details

(ord_no NUMERIC(2) , item_no
NUMERIC(3), ord_date DATE
DEFAULT NOT NULL,
UNIQUE (ord_no),
PRIMARY KEY (ord_no));

D. CREATE TABLE ord_details

(ord_no NUMERIC(2), item_no
NUMERIC(3),
ord_date DATE DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (ord_no, item_no));

Adding constraints to existing table :-

=> "ALTER" command is used to add constraints to existing table.

CREATE TABLE emp77

(
empno INT , ename
VARCHAR(10), sal
MONEY, emailid
VARCHAR(20), dno INT
)
Adding primary key :-
-----------------------------

=> primary key cannot be added to nullable column

=> to add primary key

1 change the column to not null

2 add primary key

ex :-

=> add primary key to empno ?

STEP 1 :- change the column to not null

ALTER TABLE emp77

ALTER COLUMN empno INT NOT NULL

STEP 2 :- add primary key

ALTER TABLE emp77

ADD PRIMARY KEY(empno)

Adding check constraint :-

---------------------------------------

=> add check constraint with cond sal>=3000 ?

ALTER TABLE emp77

ADD CHECK(sal>=3000)

ALTER TABLE emp

ADD CHECK(sal>=3000) => ERROR => some of the employee salaries are
less than 3000.

WITH NOCHECK :-
-------------------------

=> if constraint is added with "WITH NOCHECK" then sql server will not validate
existing data and it validates only new data.

ALTER TABLE emp

 WITH NOCHECK ADD CHECK(sal>=3000)

Adding foreign key :-

------------------------------
=> add fk to dno that should match with dept table primary key table i.e. deptno ?

ALTER TABLE emp77

ADD FOREIGN KEY(dno) REFERENCES dept(deptno)

Adding unique :-
------------------------

=> add unique to emailid ?

ALTER TABLE emp77

ADD UNIQUE (emailid)

Changing from NULL to NOT NULL :-

----------------------------------------------------
=> modify the column ename to not null ?

ALTER TABLE emp77

ALTER COLUMN ename VARCHAR(10) NOT NULL

Droping constraints :-
-----------------------------

ALTER TABLE <tabname>

DROP CONSTRAINT <name>

Ex :-

=> drop check constraint in emp77 table ?

ALTER TABLE emp77

DROP CONSTRAINT CK__emp77__sal__1332DBDC

=> drop primary key in emp77 table ?

ALTER TABLE emp77

DROP CONSTRAINT PK__emp77__AF4C318AB13E72B1

=> drop primary key in dept table ?

 ALTER TABLE dept
DROP CONSTRAINT PK__DEPT__E0EB08D7D7E4A33C => ERROR DROP
TABLE dept => ERROR

TRUNCATE TABLE dept => ERROR

NOTE :-

1 pk cannot be dropped if referenced by some fk

2 pk table cannot be cannot be dropped if referenced by some fk 3 pk table
cannot be truncated if referenced by some fk.

18-feb-25

DELETE rules :-
----------------------

1 ON DELETE NO ACTION (default)

2 ON DELETE CASCADE
3 ON DELETE SET NULL
4 ON DELETE SET DEFAULT

=> these rules are declared with foreign key.

=> delete rules specifies how child rows are affected if parent row is deleted

on delete no action :-
-----------------------------

=> parent row cannot be deleted if associated with child rows.

ex :-

create table dept99

(
dno int primary key,
dname varchar(10)
)

insert into dept99 values(10,'hr') , (20,'it')

create table emp99

(
 empno int primary key,
ename varchar(10) not null,
dno int references
dept99(dno)
)

insert into emp99 values(1,'A',10) , (2,'B',10)

DELETE FROM dept99 WHERE dno = 10 => ERROR

DELETE FROM dept99 WHERE dno = 20 => 1 row affected

scenario :-
---------------

ACCOUNTS
accno actype bal
100 s 10000
101 s 20000

LOANS
id type amt accno references accounts(accno)
1 H 50 100
1 C 10 100

rule : - account closing is not possible if associated with loans

ON DELETE CASCADE :-
----------------------------------

=> if parent row is deleted then it is deleted along with child rows.

create table dept99

(
dno int primary key,
dname varchar(10)
)

insert into dept99 values(10,'hr') , (20,'it')

create table emp99

(
 empno int primary key, ename varchar(10) not null,
dno int references dept99(dno) ON DELETE
CASCADE )

insert into emp99 values(1,'A',10) , (2,'B',10)

DELETE FROM dept99 WHERE dno = 10 => 1 row affected

SELECT * FROM emp99 => no rows

scenario :-
-------------

ACCOUNTS
accno actype bal
100 s 10000
101 s 20000

TRANSACTIONS trid ttype tdate tamt accno

references accounts(accno)
1 W ? 2000 100 on delete cascade
2 D ? 1000 100

Rule :- if account is closed along with account delete transactions.

ON DELETE SET NULL :-

--------------------------------

=> if parent row is deleted then it is deleted but child rows are not deleted but
fk will be set to null.

create table dept99

(
dno int primary key,
dname varchar(10)
)

insert into dept99 values(10,'hr') , (20,'it')

create table emp99

(
empno int primary key, ename varchar(10) not null,
dno int references dept99(dno) ON DELETE SET NULL
)

insert into emp99 values(1,'A',10) , (2,'B',10)

DELETE FROM dept99 WHERE dno = 10 => 1 row affected

SELECT * FROM emp99

1 A NULL
2 B NULL

scenario ;-
-------------

projects projid
pname duration
1000
1001

emp empid ename projid references

projects(projid)
1 1000 on delete set null
2 1001

rule :- if project is completed (deleted) set the employee projid to null

ON DELETE SET DEFAULT :-

---------------------------------------

=> if parent row is deleted then it is deleted but child rows are not deleted but
fk will be set to default value.

create table dept99

(
dno int primary key,
dname varchar(10)
)

insert into dept99 values(10,'hr') , (20,'it')

create table emp99

(
 empno int primary key,
ename varchar(10) not null,
dno int default 20
references dept99(dno) ON
DELETE SET DEFAULT
)

insert into emp99 values(1,'A',10) , (2,'B',10)

DELETE FROM DEPT99 WHERE DNO = 10 => 1 row affected

SELECT * FROM EMP99

1 A 20
2 B 20

summary :-

importance of constraints
declaring constraints column
level table level adding
constraints to existing table
droping constraints delete rules

================================================================

JOINS
=====

=> join is an operation performed to display data from two or more tables.

=> In DB related data stored in multiple tables , to gather or to combine

data stored in multiple tables we need to join those tables.

ex :-

orders cust
ordid orddt deldt cid cid cname addr 1000 10-
20- 10 10 A HYD
1001 15- 20- 11 11 B HYD

OUTPUT :-
 ordid orddt deldt cname addr
1000 10- 20- A HYD

Types of joins :-
---------------------

1 Inner join
equi join non
equi join self
join 2 Outer join
left join
right join
date
full join
3 Cartisean / Cross join

Equi join :-
--------------

=> To perform equi join between the two tables there must be common field
and name of the common field need not to be same and pk-fk relationship
is not compulsory.

=> Equi join is performed on the common field with same datatype.

SELECT columns
FROM tab1 INNER JOIN tab2
ON join condition

join condition :-
---------------------

=> join condition specifies which record of 1st table should be joined with
which record of 2nd table.

=> based on the given join condition sql server joins the records of two tables.

table1.commonfield = table2.commonfield

=> this join is called equi join because here join condtion is based on "=" operator

Ex :-
 EMP DEPT
empno ename sal deptno deptno dname loc
1 A 3000 10 10 ACCOUNTS NEW YORK
2 B 5000 20 20 RESEARCH
3 C 4000 30 30 SALES
4 D 3000 20 40 OPERATIONS
5 E 2000 NULL

=> display employee details with dept details ?

SELECT empno,ename,sal,dname,loc
FROM emp INNER JOIN dept
ON emp.deptno = dept.deptno

1 A 3000 ACCOUNTS NEW YORK

2 B 5000 RESEARCH ?
3 C 4000 SALES ? 4 D 3000 RESEARCH ?

=> In join queries declare table alias and prefix column names with table alias
for two reasons

1 to avoid ambiguity
2 for faster execution

SELECT e.empno,e.ename,e.sal,
d.deptno,d.dname,d.loc
FROM emp as e INNER JOIN dept as d
ON e.deptno = d.deptno

19-feb-25

=> display employee details with dept details working at NEW YORK loc ?

SELECT e.ename,d.dname,d.loc
FROM emp as e INNER JOIN dept as d
ON e.deptno = d.deptno /* join cond */
WHERE d.loc = 'NEW YORK' /* filter cond */

joining more than 2 tables :-

---------------------------------------
 => if no of tables increases no of join conditions also increases.
=> to join N tables N-1 join conditions required.

SELECT columns
FROM tab1 INNER JOIN tab2
ON join cond
INNER JOIN tab3
ON join cond
INNER JOIN tab4
ON join cond

Ex :-

EMP DEPT LOCATIONS COUNTRIES

empno deptno locid country_id
ename dname city country_name
sal locid state
deptno country_id

=> display ENAME DNAME CITY STATE COUNTRY_NAME ?

----------- ----------- ------------------ ---------------------------
emp dept locations countries
SELECT e.ename,
d.dname,
l.city,l.state,
c.country_name
FROM emp as e INNER JOIN dept as d
ON e.deptno = d.deptno
INNER JOIN locations as l
ON d.locid = l.locid
INNER JOIN countries as c
ON l.country_id = c.country_id

Outer join :-
----------------

=> Inner join returns only matching records but to display unmatched records
perform outer join.

EMP DEPT
empno ename sal deptno deptno dname loc
1 A 3000 10 10 ACCOUNTS NEW YORK
2 B 5000 20 20 RESEARCH
3 C 4000 30 30 SALES
4 D 3000 20 40 OPERATIONS => unmatched
5 E 2000 NULL => unmatched

=> outer join is 3 types

1 left join
2 right join
3 full join
LEFT JOIN :-
----------------

=> returns all (matched + unmatched) records from left side table and
matching records from right side table.

SELECT e.ename,d.dname
FROM emp as e LEFT JOIN dept as d
ON e.deptno = d.deptno

A ACCOUNTS
B RESEARCH C SALES
D RESEARCH
E NULL => unmatched from emp

RIGHT JOIN :-
----------------------

=> returns all (matched + unmatched) records from right side table and matching
records from left side table.

SELECT e.ename,d.dname
FROM emp as e RIGHT JOIN dept as d
ON e.deptno = d.deptno

A ACCOUNTS
B RESEARCH C SALES
D RESEARCH
NULL OPERATIONS => unmatched from dept

FULL JOIN :-
----------------
 => returns all rows from both tables

SELECT e.ename,d.dname
FROM emp as e FULL JOIN dept as d
ON e.deptno = d.deptno

A ACCOUNTS
B RESEARCH C SALES
D RESEARCH
E NULL => unmatched from emp
NULL OPERATIONS => unmatched from dept

Displying only unmatched records :-

----------------------------------------------

left side table :-

---------------------

SELECT e.ename,d.dname
FROM emp as e LEFT JOIN dept as d
ON e.deptno = d.deptno
WHERE d.dname IS NULL

E NULL

right side table :-

-----------------------

SELECT e.ename,d.dname
FROM emp as e RIGHT JOIN dept as d
ON e.deptno = d.deptno
WHERE e.ename IS NULL

NULL OPERATIONS

both tables :-
-----------------

SELECT e.ename,d.dname
FROM emp as e full JOIN dept as d
ON e.deptno = d.deptno
 WHERE d.dname IS NULL
OR
e.ename IS NULL

E NULL
NULL OPERATIONS

Question 1 :-

T1 T2
F1 F1
1 1
2 2
1 1
2 2
NULL NULL
NULL NULL

=> no of rows return by following operations ?

INNER JOIN => 8

LEFT JOIN => 10
RIGHT JOIN => 10
FULL JOIN => 12

Question 2 :-

emp projects
empno ename sal projid projid pname duration client
100 A 50000 1000 1000
101 B 40000 1001 1001
102 C 30000 NULL 1002

1 display employee details with project details ?

2 employee details with project details and also display employee not assigned to any
project ?
3 display only the projects where no employee assigned to it ?
NON EQUI JOIN :-
--------------------------

=> non equi join is performed between the tables not sharing a common field.

=> here join conditions are not based on "=" operator and it is based on
> < between operators.

Ex :-

emp salgrade
empno ename sal grade losal hisal
1 A 2500 1 700 1000
2 B 5000 2 1001 2000
3 C 1000 3 2001 3000
4 D 3000 4 3001 4000
5 E 1500 5 4001 9999

=> display ENAME SAL GRADE ?

SELECT e.ename,e.sal,s.grade
FROM emp as e INNER JOIN salgrade as s
ON e.sal BETWEEN s.losal AND s.hisal

=> display grade 4 employee list ?

SELECT e.ename,e.sal,s.grade
FROM emp as e INNER JOIN salgrade as s
ON e.sal BETWEEN s.losal AND s.hisal
WHERE s.grade = 4

=> display ENAME DNAME GRADE ?

SELECT e.ename,d.dname,s.grade
FROM emp as e INNER JOIN dept as d
ON e.deptno = d.deptno
INNER JOIN salgrade as s
ON e.sal BETWEEN s.losal AND s.hisal ;

ON e.deptno = d.deptno :-
--------------------------------------
 EMP DEPT
empno ename sal deptno deptno dname loc
1 A 3000 10 10 ACCOUNTS NEW YORK
2 B 5000 20 20 RESEARCH
3 C 4000 30 30 SALES
4 D 3000 20 40 OPERATIONS
5 E 2000 10
result
1 :-
---------- SALGRADE
GRADE LOSAL HISAL
1 A 3000 ACCOUNTS 1 700 1000
2 B 5000 RESEARCH 2 1001 2000
3 C 4000 SALES 3 2001 3000
4 D 3000 RESERCH 4 3001 4000
5 E 2000 ACCOUNTS 5 4001 9999

ON e.sal BETWEEN s.losal AND s.hisal :-

-------------------------------------------------------------
result
2 :-

1 A 3000 ACCOUNTS 3
2 B 5000 RESEARCH 5
3 C 4000 SALES 4
4 D 3000 RESEARCH 3
5 E 2000 ACCOUNTS 2

SELECT e.ename,d.dname,s.grade :-
-------------------------------------------------------

output :-

A ACCOUNTS 3
B RESEARCH 5
C SALES 4
D RESEARCH 3
E ACCOUNTS 2
 Question :-
----------------

CUSTOMER PRODUCTS
cid name addr prodid pname price category brand
10 A 100 ABC 2000
11 101 XYZ 1000

ORDERS ordid orddate deldate

cid
1000 ? ? 10

ORDER_DETAILS discounts ordid

prodid qty pct lamt hamt
1000 100 2 5 500 1000
1000 101 3 10 1001 5000
20 5000 99999

output :-

ORDID ORDDT DELDT CNAME ADDR PNAME PRICE QTY DIS_PCT

AMOUNT
1000 ? ? A HYD ABC 2000 2 10 3600
1000 ? ? A HYD XYZ 1000 3 10 2700

SELF JOIN :-
--------------------

=> joining a table to itself is called self join.

=> In self join a record in one table joined with another record of same table.

=> To perform self join the same table must be specified two times with
different alias in FROM clause.

FROM emp as x INNER JOIN emp as y

Ex :-

emp x emp y
empno ename mgr empno ename mgr
7369 SMITH 7902 7369 SMITH 7902
 7499 ALLEN 7698 7499 ALLEN 7698
7566 JONES 7839 7566 JONES 7839
7698 BLAKE 7839 7698 BLAKE 7839
7839 KING NULL 7839 KING NULL
7902 FORD 7566 7902 FORD 7566

=> display ENAME MGRNAME ?

SELECT x.ename,y.ename as manager

FROM emp as x INNER JOIN emp as y
ON x.mgr = y.empno

SMITH FORD
ALLEN BLAKE
JONES KING
BLAKE KING
FORD JONES

=> display employees reporting to blake ?

SELECT x.ename,y.ename as manager

FROM emp as x INNER JOIN emp as y
ON x.mgr = y.empno
WHERE y.ename='BLAKE'

=> display blake's manager name ?

SELECT x.ename,y.ename as manager

FROM emp as x INNER JOIN emp as y
ON x.mgr = y.empno
WHERE x.ename='BLAKE'

BLAKE KING

=> employees earning more than their managers ?

SELECT x.ename,x.sal,
y.ename as manager,y.SAL as mgrsal
FROM emp as x INNER JOIN emp as y
ON x.mgr = y.empno
 WHERE x.sal > y.sal

=> employees joined before their managers ?

SELECT x.ename,x.hiredate,
y.ename as manager,y.HIREDATE as mgrhire
FROM emp as x INNER JOIN emp as y
ON x.mgr = y.empno
WHERE x.hiredate < y.hiredate
Question :-

TEAMS
ID COUNTRY
1 IND
2 AUS
3 NZ

=> waq to display following output ?

IND VS AUS
IND VS NZ
AUS VS NZ

TEAMS A TEAMS B
ID COUNTRY ID COUNTRY
1 IND 1 IND
2 AUS 2 AUS
3 NZ 3 NZ

21-FEB-25

CROSS / CARTESIAN JOIN :-

-----------------------------------------

=> cross join returns cross product or cartesian product of two tables

A = 1,2
B =3,4

AXB = (1,3 )(1,4) (2,3)(2,4)

=> if cross join performed between two tables then all the records of 1st table joined with
all the records of 2nd table.

=> to perform cross join join condition is not required.

Ex 1 :-

SELECT e.ename,d.dname
FROM emp as e CROSS JOIN dept as d
=> above query joins all 14 employees records with all 4 records of dept table and
query returns 56 rows.

Ex 2 :-

T1 T2
ITEM COST ITEM COST
BURGER 100 COOL DRINK 40
PIZZA 150 JUICE 60

output :-

BURGER + COOLDRINK 140

BURGER + JUICE 160
PIZZA + COOLDRINK 190
PIZZA + JUICE 210

SELECT T1.ITEM + ' + ' + T2.ITEM , T1.COST + T2.COST AS TOTAL

FROM T1 CROSS JOIN T2

========================================================================
=====

SET operators :-
-----------------------

1 UNION
2 UNION ALL
3 INTERSECT
4 EXCEPT
A = 1,2,3,4
B = 1,2,5,6

A UNION B = 1,2,3,4,5,6
A UNION ALL B = 1,2,3,4,1,2,5,6
A INTERSECT B = 1,2
A EXCEPT B = 3,4
B EXCEPT A = 5,6
=> In SQL SERVER these operations are performed between the records return by two
queries

SELECT statement 1
UNION / UNION ALL / INTERSECT / EXCEPT
SELECT statement 2

Rules :-
------------

1 no of columns return by both queries must be same

2 corresponding columns datatype must be same

Query 1 :-

SELECT job FROM emp WHERE deptno = 20

CLERK
MANAGER
ANALYST
CLERK
ANALYST

Query 2 :-

SELECT job FROM emp WHERE deptno = 30

SALESMAN
SALESMAN
SALESMAN
MANAGER
SALESMAN
CLERK
UNION :-
--------------

=> combines rows return by two queries

=> duplicates are eliminated
=> result is sorted

SELECT job FROM emp WHERE deptno = 20

UNION
SELECT job FROM emp WHERE deptno = 30

ANALYST
CLERK
MANAGER
SALESMAN

SELECT job,sal FROM emp WHERE deptno = 20

UNION
SELECT JOB,sal FROM emp WHERE deptno = 30

ANALYST 3000.00
CLERK 800.00
CLERK 950.00
CLERK 1100.00
MANAGER 2850.00
MANAGER 2975.00
SALESMAN 1250.00
SALESMAN 1500.00
SALESMAN 1600.00

=> difference between UNION & JOIN ?

UNION JOIN

1 combines rows combines columns

2 horizontal merge vertical merge

3 performed between performed between two tables two query

outputs
Ex 1 :-

T1 T2
F1 C1
1 10
2 20
3 30

UNION :- JOIN :-

1 1 10 2
2 20
3 3 30
10
20
30

Ex 2 :-

EMP_US eno
ename sal dno
100 A 4000 10
101 B 5000 20
DEPT
EMP_IND dno dname loc eno ename sal dno 10
IT 200 K 6000 10 20 HR
202 B 7000 30 30 SALES

=> total employees ?

SELECT * FROM EMP_US

UNION
SELECT * FROM EMP_IND

=> employees working at US loc with dept details ?

SELECT E.* , D.*

FROM EMP_US AS E INNER JOIN DEPT AS D
ON E.DNO = D.DNO

=> total employees with dept details ?

 SELECT E.* , D.*
FROM EMP_US AS E INNER JOIN DEPT AS D
ON E.DNO = D.DNO
UNION
SELECT E.* , D.*
FROM EMP_IND AS E INNER JOIN DEPT AS D
ON E.DNO = D.DNO

UNION ALL :-
-------------------
=> combines rows return by two queries
=> duplicates are not eliminated
=> result is not sorted

SELECT job FROM emp WHERE deptno = 20

UNION ALL
SELECT job FROM emp WHERE deptno = 30

CLERK
MANAGER
ANALYST
CLERK
ANALYST
SALESMAN
SALESMAN
SALESMAN
MANAGER
SALESMAN
CLERK

=> difference between UNION & UNION ALL ?

UNION UNION ALL

1 eliminates duplicates doesn't eliminate duplicates

2 sorts result doesn't sort result

3 slower faster
INTERSECT :-
-------------------

=> returns common values from the output of two select statements

SELECT job FROM emp WHERE deptno = 20

INTERSECT
SELECT job FROM emp WHERE deptno = 30

CLERK
MANAGER

EXCEPT :-
----------------

=> returns values present in 1st query output and not present in 2nd query output

SELECT job FROM emp WHERE deptno = 20

EXCEPT
SELECT job FROM emp WHERE deptno = 30

ANALYST

Ex :-

CUSTS CUSTT
CID NAME ADDR CID NAME ADDR
1 A HYD 1 A HYD
2 B BLR 2 B BLR
3 C MUM
display new customers
data ?

method 1 :-

SELECT * FROM CUSTS

EXCEPT
SELECT * FROM CUSTT

method 2 :-
 SELECT S.*
FROM CUSTS AS S LEFT JOIN CUSTT AS T
ON S.CID = T.CID
WHERE T.CID IS NULL

copying data from one table to another table :-

----------------------------------------------------------------

INSERT INTO <target-table>

SELECT columns FROM <source-table> [WHERE cond]

ex :-
=> copy new customers data into custt ?

INSERT INTO CUSTT

SELECT * FROM CUSTS
EXCEPT
SELECT * FROM CUSTT

Question :-

T1 T2
F1 F1
1 1
2 2
3 3
10 40
20 50
30 60

=> write outputs for the following operations ?

1 EQUI JOIN
2 LEFT JOIN
3 RIGH TJOIN
4 FULL JOIN
5 UNION
6 UNION ALL
7 INTERSECT
8 EXCEPT

24-FEB-25

SUB-QUERIES OR NESTED QUERIES :-

-----------------------------------------------------------

=> a query in another query is called sub-query or nested query.

=> one query is called inner / child / sub-query.
=> other query is called outer / parent / main query.
=> first sql server executes inner query next it executes outer query.
=> output of inner query is input to outer query.
=> use sub-query when where cond based on uknown value.

Types of sub-queries :-
-----------------------------

1 single row sub-queries

2 multi row sub-queries
3 co-related sub-query
4 derived tables and CTEs
5 scalar sub-queries

single row sub-queries :-

---------------------------------

=> if inner query returns one value then it is called single row sub-query

SELECT columns
FROM tabname
WHERE colname OP (SELECT statement)

=> OP must be = <> > <

Ex :-

=> employees earning more than blake ?

SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename='blake')
-----------------------------------------------------------------
 2850

=> employees who are senior to king ?

SELECT *
FROM emp
WHERE hiredate < (SELECT hiredate FROM emp WHERE ename='king')
---------------------------------------------------------------------
1981-11-17

=> name of the employee earning max salary ?

SELECT ename
FROM emp
WHERE sal = MAX(sal) => ERROR
SELECT ename,MAX(sal) FROM emp => ERROR
14 1

SELECT ename
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp) => KING
-------------------------------------------
5000

=> name of the employee having max experience ?

SELECT ename
FROM emp
WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP)
--------------------------------------------------
1980-12-17

=> employees having min,max experience ?

SELECT ename
FROM emp
WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP)
OR
HIREDATE = (SELECT MAX(HIREDATE) FROM EMP)

SELECT ename
FROM emp
 WHERE hiredate = (SELECT MIN(hiredate) FROM emp)
UNION
SELECT ename
FROM emp
WHERE hiredate = (SELECT MAX(hiredate) FROM emp)

=> display 2nd max salary ?

SAL
5000
1000
3000
2000
2975

SELECT MAX(sal)
FROM emp
WHERE sal <> (SELECT MAX(sal) FROM emp)

=> name of the employee earning 2nd max salary ?

SELECT ename,sal
FROM emp
WHERE sal = ( SELECT MAX(sal)
FROM emp
WHERE sal <> (SELECT MAX(sal) FROM emp))

=> employees working at NEW YORK loc ?

sub-query :-
-----------------
SELECT *
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO
FROM DEPT
WHERE LOC='NEW YORK')

join :-
----------

SELECT E.*
FROM EMP AS E INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
 WHERE D.LOC = 'NEW YORK'

=> display ENAME DNAME working at NEW YORK loc ?

join :-
---------

SELECT E.ENAME,D.DNAME
FROM EMP AS E INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE D.LOC = 'NEW YORK'

sub-query :-
-----------------
no t possible

=> difference between join and sub-query ?

1 to display data from one table and condition based on another table then we can
use join or sub-query.

2 to display data from two tables then use join operation.

=> list of employees working at USA ?

EMP DEPT LOCATIONS COUNTRIES

empno deptno locid country_id
ename dname city country_name
sal locid state deptno
country_id

SELECT ename
FROM emp
WHERE deptno = (SELECT deptno
FROM dept
WHERE locid = (SELECT locid
FROM locations
WHERE country_id = (SELECT country_id
FROM countries
WHERE country_name='USA')))
NOTE :-
 => outer query can be select / insert / update / delete but inner query must be
always select.

=> increment sal by 10% of the employee having max experience ?

UPDATE emp
SET sal = sal + (sal*0.1)
WHERE hiredate = (SELECT MIN(hiredate) FROM emp)

25-FEB-25

=> swap employee salaries whose empno = 7369,7499 ?

before swap after swap

7369 880 7369 1600

7499 1600 7499 880

UPDATE emp
SET sal = CASE empno
WHEN 7369 THEN (SELECT sal FROM emp WHERE empno =7499)
WHEN 7499 THEN (SELECT sal FROM emp WHERE empno=7369)
END
WHERE empno IN (7369,7499)

UPDATE emp
SET sal = CASE empno
WHEN 7369 THEN 1600
WHEN 7499 THEN 880
END
WHERE empno IN (7369,7499)

Multi-row sub-queries :-
-------------------------------

=> if sub-query returns more than one row then it is called multi-row sub-query

SELECT columns
FROM tabname
WHERE colname OP (SELECT statement)

=> OP mustr be IN , NOT IN,ANY,ALL

 single multi

= IN

<> NOT IN

> >ANY >ALL

< <ANY <ALL

Ex :-
=> employees working at NEW YORK,CHICAGO locations ?

SELECT *
FROM emp
WHERE deptno IN (SELECT deptno
FROM dept
WHERE loc IN ('NEW YORK','CHICAGO'))

ANY & ALL operators :-

---------------------------------

=> operators used for > < comparision with multiple values

WHERE sal > ANY(1000,2000,3000) WHERE sal < ANY(1000,2000,3000)

if sal = 800 false if sal = 800 true

1500 true 1500 true
4500 true 4500 false

WHERE sal > ALL(1000,2000,3000) WHERE sal < ALL(1000,2000,3000)

if sal = 800 FALSE if sal = 800 TRUE

1500 FALSE 1500 FALSE
4500 TRUE 4500 FALSE

=> employees earning more than atleast one manager ?

SELECT *
FROM emp
 WHERE sal > ANY (SELECT sal FROM emp WHERE job='MANAGER')

2975.00
2850.00
2450.00

=> employees earning more than all managers ?

SELECT *
FROM emp
WHERE sal > ALL (SELECT sal FROM emp WHERE job='MANAGER')

co-related sub-queries :-
---------------------------------

=> if inner query references values of outer query then it is called co-related sub-query.

=> execution starts from outer query and inner query is executed no of times depends
on no of rows return by outer query.

=> use co-related sub-query to execute sub-query for each row return by outer query.

Ex :-

EMP
empno ename sal deptno
1 A 5000 10
2 B 3000 20
3 C 4000 30
4 D 6000 20
5 E 3000 10

=> employees earning more than avg sal of the organization ?

SELECT *
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp)
---------------------------------------------
4200

=> employees earning more than avg sal of their dept ?

 SELECT *
FROM emp as x
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = x.deptno)

EMP
empno ename sal deptno
1 A 5000 10 5000 > (where deptno = 10) 4000 TRUE
2 B 3000 20 3000 > (where deptno = 20) 4500 FALSE
3 C 4000 30 4000 > (where deptno = 30) 4000 FALSE 4 D 6000 20
6000 > ( where deptno = 20) 4500 TRUE
5 E 3000 10 3000 > ( where deptno = 10) 4000 FALSE

=> employees earning max sal in their dept ?

SELECT *
FROM emp as x
WHERE sal = (SELECT MAX(sal) FROM emp WHERE deptno = x.deptno)

1 A 5000 10 5000 = (5000) TRUE

2 B 3000 20 3000 = (6000) FALSE
3 C 4000 30 4000 = (4000) TRUE
4 D 6000 20 6000 = (6000) TRUE
5 E 3000 10 3000 = (5000) FALSE

=> display top 3 max salaries ?

EMP
SAL
5000
1000
3000
2000
2975

SELECT DISTINCT A.SAL

FROM EMP AS A
WHERE 3 > (SELECT COUNT(DISTINCT B.SAL)
FROM EMP AS B
WHERE A.SAL < B.SAL)
ORDER BY SAL DESC

EMP A EMP B
 SAL SAL
5000 5000 3 > (0) TRUE
1000 1000 3 > (4) FALSE
3000 3000 3 > (1) TRUE
2000 2000 3 > (3) FALSE
2975 2975 3 > (2) TRUE

26-FEB-25

=> display 3rd max salary ?

SELECT DISTINCT A.SAL

FROM EMP AS A
WHERE (3-1) = (SELECT COUNT(DISTINCT B.SAL)
FROM EMP AS B
WHERE A.SAL < B.SAL)
ORDER BY SAL DESC

Derived tables :-
------------------------

=> sub-queries in FROM clause are called derived tables.

SELECT columns
FROM (SELECT statement) AS <alias>
WHERE cond ;

=> sub-query output acts like a table for outer query

=> derived tables are used in following scenarios

1 to control order of execution of clauses

2 to join two query outputs
3 to use result of one operation in another operation

controlling order of execution of clauses :-

-------------------------------------------------------

default order :-
--------------------

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

=> to control this order of execution use derived tables , for ex to control
execution order of where & order by.

SELECT SELECT
FROM =============================> FROM (SELECT
WHERE FROM
ORDER BY ORDER BY )
WHERE

1 WHERE 1 ORDER BY
2 ORDER BY 2 WHERE

Ex 1 :-

=> display ranks of the employees based on sal and highest paid employee
should get 1st rank ?

SELECT empno,ename,sal,
dense_rank() over (order by sal desc) as rnk
FROM emp

above query returns ranks of all the employees but to display top 5 employees

SELECT empno,ename,sal,
dense_rank() over (order by sal desc) as rnk
FROM emp
WHERE rnk <= 5 => ERROR

column alias cannot be used in where clause because where clause

is executed before select. To overcome this problem use derived table

SELECT empno,ename,sal
FROM ( SELECT empno,ename,sal,
dense_rank() over (order by sal desc) as rnk
FROM emp ) AS E
WHERE rnk <= 5
 => display top 3 max salaries ?

SELECT DISTINCT sal

FROM ( SELECT sal,
dense_rank() over (order by sal desc) as rnk
FROM emp ) AS E
WHERE rnk <= 3
ORDER BY sal DESC

method 1 :- (using top clause)

=> top 3 max salaries

SELECT DISTINCT TOP 3 SAL

FROM EMP
ORDER BY SAL DESC

=> 3rd max salary ?

SELECT TOP 1 *
FROM (SELECT DISTINCT TOP 3 SAL
FROM EMP
ORDER BY SAL DESC) AS E
ORDER BY SAL ASC

method 2 :- (co-related subquery)

=> display top 3 max salaries ?

SELECT DISTINCT A.SAL

FROM EMP AS A
WHERE 3 > (SELECT COUNT(DISTINCT B.SAL)
FROM EMP AS B
WHERE A.SAL < B.SAL)

=> display 3rd max salary ?

SELECT DISTINCT A.SAL

FROM EMP AS A
WHERE (3-1) = (SELECT COUNT(DISTINCT B.SAL)
FROM EMP AS B
WHERE A.SAL < B.SAL)
method 3 :- (dense_rank & derived table)

=> display top 3 max salaries

SELECT DISTINCT sal

FROM ( SELECT sal,
dense_rank() over (order by sal desc) as rnk
FROM emp ) AS E
WHERE rnk <= 3
ORDER BY sal DESC

=> display 3rd max salary ?

SELECT DISTINCT sal FROM ( SELECT sal,

dense_rank() over (order by sal desc) as rnk
FROM emp ) AS E
WHERE rnk = 3
ORDER BY sal DESC

=> display top 3 max salaries in each dept ?

SELECT DEPTNO,SAL,rnk
FROM (SELECT sal,deptno,
DENSE_RANK() over (partition by deptno
order by sal desc) as rnk
FROM emp) AS E
WHERE rnk <= 3

Ex 2 :-

=> display first 5 rows from emp table ?

SELECT *
FROM (SELECT empno,ename,sal ,
ROW_NUMBER() OVER (ORDER BY empno asc) as rno
FROM EMP) AS E
WHERE rno <= 5

WHERE rno IN (5,10,15)

WHERE rno BETWEEN 5 AND 10

 WHERE rno%2 = 0

=> display last 3 rows from emp table ?

SELECT *
FROM (SELECT empno,ename,sal ,
ROW_NUMBER() OVER (ORDER BY empno asc) as rno
FROM EMP) AS E
WHERE rno >= (SELECT COUNT(*)-2 FROM EMP)

27-FEB-25

=> delete first 5 rows from emp table ?

DELETE
FROM (SELECT empno,ename,sal,
ROW_NUMBER() over (order by empno asc) as rno
FROM EMP) AS E
WHERE rno <= 5 => ERROR

in derived tables outer query cannot be DML and outer query must be
always SELECT. To overcome this use CTE

CTEs -
----------

=> CTE stands for common table expression.

=> using CTE we can give name to the query output and we can refer
that name in another query like INSERT/UPDATE/DELETE/SELECT.

=> using CTE we can use result of one operation in another operation.

=> complex queries can be simplied by using CTEs.

WITH <CTE-NAME1>
AS
(SELECT STATEMENT ) ,
<CTE-NAME2)
AS
(SELECT STATEMENT)

SELECT / INSERT / UPDATE / DELETE

Ex 1 :-

=> delete first 5 rows from emp ?

WITH E
AS
(SELECT empno,ename,sal,
ROW_NUMBER() OVER (ORDER BY empno ASC) as rno
FROM emp)

DELETE FROM E WHERE rno <= 5

Ex 2 :-

EMP44
ENO ENAME SAL
1 A 5000
2 B 6000
3 C 7000
1 A 5000
2 B 6000

=> delete duplicate rows ?

step 1 :- generate row numbers with in the group of eno,ename,sal

select eno,ename,sal,
ROW_NUMBER() over (partition by eno,ename,sal
order by eno asc) as rno from emp44

1 A 5000.00 1
1 A 5000.00 2
2 B 6000.00 1
2 B 6000.00 2
3 C 7000.00 1

step 2 :- delete the records whose rno > 1

WITH E
AS
( select eno,ename,sal,
 ROW_NUMBER() over (partition by eno,ename,sal
order by eno asc) as rno from emp44)

DELETE FROM E WHERE rno > 1

Ex 3 :-

CRICKET
TEAMA TEAMB WINNER
ENGLAND NEW ZEALAND NEW ZEALAND

output :-

COUNTRY PLAYED WON LOST WITH P

AS
(SELECT COUNTRY,COUNT(*) AS PLAYED
FROM (SELECT TEAMA AS COUNTRY FROM CRICKET
UNION ALL
SELECT TEAMB AS COUNTRY FROM CRICKET) AS E
GROUP BY COUNTRY) ,

W
AS
(SELECT WINNER AS COUNTRY,COUNT(*) AS WON
FROM CRICKET
GROUP BY WINNER)

SELECT P.COUNTRY , P.PLAYED,W.WON,P.PLAYED-W.WON AS LOST

FROM P INNER JOIN W
ON P.COUNTRY = W.COUNTRY

Question 1 :-

SALES
ID SDATE AMT
1 2025-02-01 2000
2 2025-02-02 1000
3 2025-02-06 5000
4 2025-02-07 6000
5 2025-02-12 5000

=> find the dates where no sales in consecutive 3 days ?

Question 2 :-

points
custid date points
1 2025-02-10 40
2 ? 50
1 ? 30
1 ? 50
2 ? 80

=> find the date on which customer reached 100 points ?

28-feb-25

scalar sub-queries :-
---------------------------

=> sub-queries in select clause are called scalar sub-queries

SELECT (select stmt1) , (select stmt2) , (select stmt3) ,-------

FROM tabname
WHERE cond

=> sub-query output acts like a column for outer query

=> use scalar-subquery to show the query output in seperate column

Ex 1 :-

SELECT (SELECT COUNT(*) FROM EMP) AS EMP,

(SELECT COUNT(*) FROM DEPT) AS DEPT

EMP DEPT
14 4

Ex 2 :-

=> display dept wise total sal ?

SELECT DEPTNO,SUM(SAL) AS DEPT_TOTSAL

FROM EMP
 GROUP BY DEPTNO

10 8750.00
20 7100.00
30 5300.00

=> display DEPTNO DEPT_TOTSAL TOTSAL ?

SELECT DEPTNO,SUM(SAL) AS DEPT_TOTSAL ,

(SELECT SUM(SAL) FROM EMP) AS TOTSAL
FROM EMP
GROUP BY DEPTNO

10 8750.00 21150.00
20 7100.00 21150.00
30 5300.00 21150.00

=> display DEPTNO DEPT_TOTSAL TOTSAL PCT ?

PCT = (DEPT_TOTSAL/TOTSAL)*100

SELECT DEPTNO,SUM(SAL) AS DEPT_TOTSAL ,

(SELECT SUM(SAL) FROM EMP) AS TOTSAL ,
(SUM(SAL) / (SELECT SUM(SAL) FROM EMP))*100 AS PCT
FROM EMP
GROUP BY DEPTNO

10 8750.00 21150.00 41.37

20 7100.00 21150.00 33.56
30 5300.00 21150.00 25.05

=> display sno sname total avg ?

student
sno sname marks
1 A 70,90,80,50,40
2 B 60,70,50,40,80
3 C 30,50,90,50,40

STRING_SPLIT() :-
--------------------------
 => It is a table valued function and invoked in from clause.
=> used to split the string into rows based on some seperator.

STRING_SPLIT(string,seperator)

Ex :-

1 SELECT * FROM STRING_SPLIT('a,b,c,d',',')

o/p :-

a
b
c
d

2 SELECT * FROM STRING_SPLIT('sachin ramesh tendulkar',' ')

sachin
ramesh
tendulkar

SELECT sno,sname,marks,
(select sum(cast(value as int))
from string_split(marks,',')) as total,
(select avg(cast(value as int))
from string_split(marks,',')) as avg
FROM student

Question 1 :-

T1
AMT
1000
-200
3000
-500
4000
-600

output :-
 POS NEG
1000 -200
3000 -500
4000 -600

Question 2 :-

T1 T2
F1 C1
1 A
2 B
3 C

output :-
1 A
2 B
3 C

WHERE
ORDER BY
DISTINCT
TOP
FUNCTIONS
GROUP BY
JOINS
SET OPERATORS
SUB-QUERIES

==============================================================

PIVOT operator :-
-------------------------

=> used to convert rows into columns.

=> used for cross tabulation.
=> used to display data in matrix form.

SELECT columns
FROM (SELECT required data) AS <ALIAS>
PIVOT
 (
AGGR-EXPR FOR COLNAME IN (V1,V2,V3,---)
) AS <PIVOT-TAB-NAME>
ORDER BY COL ASC/DESC

Ex 1 :-

10 20 30

ANALYST ? 6000 ?

CLERK ? ? ?

MANAGER

SALESMAN 5600

SELECT *
FROM (SELECT DEPTNO,JOB,SAL FROM EMP) AS E
PIVOT
(
SUM(SAL) FOR DEPTNO IN ("10","20","30")
) AS PIVOT_TBL
ORDER BY JOB ASC

Ex 2 :-

1 2 3 4

1980 ? ? ? ?

1981 ? ? ? ?

1982

1983

SELECT *
FROM (SELECT DATEPART(YY,HIREDATE) AS YEAR,
DATEPART(QQ,HIREDATE) AS QRT,
EMPNO
 FROM EMP) AS E
PIVOT
(
COUNT(EMPNO) FOR QRT IN ("1","2","3","4")
) AS PIVOT_TBL
ORDER BY YEAR ASC

Ex 3 :-

STUDENT sno sname

subject marks
1 A MAT 80
1 A PHY 60
1 A CHE 70
2 A MAT 50
2 A PHY 60
2 A CHE 40
output :-

sno sname mat phy che

1 A 80 60 70
2 B 50 60 40

========================================================================
==

1-mar-25

CREATING NEW TABLE FROM EXISTING TABLE (CREATING REPLICA) :-

------------------------------------------------------------------------------------------------------
SELECT COLUMNS / * INTO <NEW-TABNAME>
FROM <OLD-TABNAME>
[WHERE COND]

Ex 1 :- copying complete table

SELECT * INTO EMP10

FROM EMP

=> a new table is created with no of rows and columns return by query.
Ex 2 :- copying specific rows & cols

SELECT EMPNO,ENAME,JOB,SAL INTO EMP11

FROM EMP
WHERE JOB IN ('CLERK','MANAGER')

Ex 3 :- copy only structure (cols) but not data (rows)

SELECT * INTO EMP12

FROM EMP
WHERE 1=2

Ex 4 :- copying table from one db to another db

copy student table from batch11am to db9am ?

SELECT * INTO DB9AM.DBO.STUDENT

FROM STUDENT

copying data from one table to another table :-

--------------------------------------------------------------

INSERT INTO <target-table>

SELECT columns FROM <source-table> [WHERE cond]

Ex :-

=> copy data from emp to emp12 ?

INSERT INTO emp12

SELECT * FROM emp

MERGE command :-
----------------------------

=> merge is the combination of insert, update & delete.

=> command used to manage replicas.
=> using merge command we can apply changes made to source table to replica.

MERGE INTO <TARGET-TABLE> AS <ALIAS>

USING <SOURCE-TABLE> AS <ALIAS>
ON (CONDITION)
WHEN MATCHED THEN
 UPDATE
WHEN NOT MATCHED THEN
INSERT
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;

Ex 1 :-

step 1 :- create source table

CREATE TABLE CUSTS

(
CID INT ,
CNAME VARCHAR(10),
CITY VARCHAR(10)
)

INSERT INTO CUSTS VALUES(1,'A','HYD') , (2,'B','BLR')

step 2 :- create replica

SELECT * INTO CUSTT FROM CUSTS

step 3 :- modify source table

INSERT INTO CUSTS VALUES(3,'C','DEL')

UPDATE CUSTS SET CITY='MUM' WHERE CID=1

step 4 :- use merge command to apply changes made to custs to custt

MERGE INTO CUSTT AS T

USING CUSTS AS S
ON (S.CID = T.CID)
WHEN MATCHED THEN
UPDATE SET T.CITY = S.CITY
WHEN NOT MATCHED THEN
INSERT VALUES(S.CID,S.CNAME,S.CITY)
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;

before merge :-
----------------------
 CUSTS CUSTT
CID CNAME CITY CID CNAME CITY
1 A MUM => UPDATED 1 A HYD
2 B BLR => DELETED 2 B BLR
3 C DEL => INSERTED

after merge :-
-------------------

CUSTS CUSTT
CID CNAME CITY CID CNAME CITY
1 A MUM 1 A MUM
3 C DEL 3 C DEL

DATABASE TRANSACTIONS:-

=> a transaction is a unit of work that contains one or more dmls and
must be saved as a whole or must be cancelled as a whole.

ex :- money transfer

acct1------------------1000----------------------------acct2

update1 update2
(bal=bal-1000) (bal=bal+1000)

successful failed INVALID

failed successful INVALID

successful successful VALID

failed failed VALID

=> every transaction must guarantee a property called atomocity i.e. all
or none
if transaction contains multiple operations then if all are successful
then it must be saved, if one of the operation fails then entire transaction
must be cancelled.
 => the following commands provided by sql server to handle
transactions called TCL commands.

COMMIT =>to save txn

ROLLBACK => to cancel txn
SAVE TRANSACTION => to cancel part of the txn

=> every txn has a begin point and an end point.

=> a txn begins implicitly when user submits dml/ddl and ends implicitly with commit.

UPDATE1 ==========> a txn begins

UPDATE2 update1
commit a
txn begins
update2
commit
=> a user can also start txn by executing "BEGIN TRANSACTION" command
and ends with COMMIT / ROLLBACK.

Ex 1 :-

create table a(a int) insert into

a values(10) insert into a
values(20) insert into a
values(30) insert into a
values(40) rollback
=> error

all operations are committed implicitly

Ex 2 :-

create table a(a int)

BEGIN TRANSACTION
insert into a values(10)
insert into a values(20) insert
into a values(30) insert into a
values(40)
ROLLBACK

Ex 3 :-
 create table a(a int) => implicitly committed
BEGIN TRANSACTION => txn begins T1
insert into a values(10) insert into a
values(20)
COMMIT => txn ends insert
into a values(30) => implicitly committed insert
into a values(40) => implicitly committed
ROLLBACK => error => trying to end transaction without starting

=> if txn ends with COMMIT then it is called successful txn and operations are saved.
=> if txn ends with ROLLBACK then it is called aborted txn and operations are cancelled.

3-mar-25

SAVE TRANSACTION :-
-------------------------------------

=> we can declare save transaction and we can rollback upto the save transaction.
=> using save transaction we can cancel part of the transaction.

Ex :-

create table a(a int)

begin transaction insert
into a values(10) insert
into a values(20) save
transaction st1 insert
into a values(30) insert
into a values(40) save
transaction st2 insert
into a values(50) insert
into a values(60)
rollback transaction st1

SELECT * FROM a

10
20

========================================================================
=

DB SECURITY :-
 =============

LOGINS => provides security at server level

USERS => provides security at db level
PRIVILEGES => provides security at table level
VIEWS => provides security at row & col level

SERVER (logins)
DATABASE (users)
TABLE (privileges)
ROWS & COLS (views)

Creating logins :-
-----------------------

=> In object explorer expand security

logins => New Login
Enter Login Name :- NARESH

select authentication :- sql server

enter password :- 123

confirm password :- 123

=> click OK

command to create login :-

-------------------------------------

=> open master database and execute the following command

CREATE LOGIN NARESH WITH PASSWORD='abc'

NOTE :-

=> a new login is created but cannot access databases , so to access database login
must be associated with a user in database.

creating user in db :-
---------------------------
=> In object explorer expand Databases
DB11AM
Users => New User
Enter Username :- VIJAY

Enter Login Name :- NARESH

=> click OK

command to create user :-

-----------------------------------

=> open the database in which you want to create user for ex DB9AM
and execute the following command

CREATE USER VIJAY FOR LOGIN NARESH

NOTE :-

=> user is created but cannot access tables in database , to access

table kumar needs permissions

PRIVILEGES :-
--------------------

=> privileges means permissions

=> permissions granted to user by using grant command

GRANT <privileges> ON <tabname> TO <usernames>

Ex 1 :- granting all permissions

DBO :-
----------

GRANT SELECT,INSERT,UPDATE,DELETE ON EMP TO VIJAY

KUMAR :-
-------------

1 SELECT * FROM EMP

2 UPDATE EMP SET SAL=2000 WHERE EMPNO = 7369

3 DELETE FROM EMP WHERE EMPNO = 7369

Ex 2 :- granting read only permission

DBO :-
-----------

GRANT SELECT ON DEPT TO VIJAY

KUMAR :-
------------
1 SELECT * FROM DEPT

2 UPDATE DEPT SET LOC='HYD' WHERE DEPTNO = 10 => ERROR

REVOKE command :-
-----------------------------

=> command used to take back the permissions from user

REVOKE <privileges> ON <tabname> FROM <usernames>

Ex :-

DBO :-
-------------

REVOKE SELECT,INSERT,UPDATE,DELETE ON EMP FROM VIJAY

5-mar-25

DB objects :-
===========

1 TABLES
2 VIEWS
3 SYNONYMS
4 SEQUENCES
5 INDEXES
VIEWS :-
--------------

=> a view is a subset of a table i.e. part of the table.

=> a view is a virtual table because it doesn't store data and doesn't occupy memory
and it always derives data from base table.

=>a view is a representation of a query.

=> views created for

1 to provide security
2 to reduce complexity

=> views are 2 types

1 simple views
2 complex views

simple views :- -----------------------

=> a view said to be simple view if it is created on single table

CREATE VIEW <NAME>

AS
SELECT STATEMENT

Ex 1 :-

CREATE VIEW V1
AS
SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP

=> sql server creates view "V1" and stores query but not query output i.e. data

SELECT * FROM V1

=> sql server executes the above query as follows

SELECT * FROM (SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP)

Granting permissions on view to user :-

 ---------------------------------------------------

DBO :-
-----------

GRANT SELECT,INSERT,UPDATE,DELETE ON V1 TO VIJAY

VIJAY :-
------------

1 SELECT * FROM V1

2 UPDATE V1 SET JOB='CLERK' WHERE EMPNO = 7698

3 DELETE FROM V1 WHERE EMPNO = 7698

4 UPDATE V1 SET SAL=3000 WHERE EMPNO = 7844 => ERROR

ROW LEVEL SECURITY :-

-------------------------------------

EX 2 :-

CREATE VIEW V2
AS
SELECT EMPNO,ENAME,JOB,DEPTNO
FROM EMP
WHERE DEPTNO = 20

GRANT SELECT,INSERT,UPDATE,DELETE ON V2 TO VIJAY

Complex views :-
------------------------

=> a view said to be complex view

1 if based on multiple tables (joins)

2 if query contains group by clause
distinct clause
aggregate functions
set operators sub-
queries
 => with the help of views complex queries can be converted into simple queries

Ex 1 :-

EMP DEPT LOCATIONS COUNTRIES

empno deptno locid country_id
ename dname city country_name
sal locid state
deptno country_id

CREATE VIEW CV1

AS
SELECT e.empno,e.ename,e.sal,
d.dname,
l.city,l.state,
c.country_name as country
FROM emp as e INNER JOIN dept as d
ON e.deptno = d.deptno
INNER JOIN locations as l
ON d.locid = l.locid
INNER JOIN countries as c
ON l.country_id = c.country_id

=> after creating view whenever user wants data from 4 tables then execute
the following simple query

SELECT * FROM CV1

Ex 2 :-

CREATE VIEW CV2

AS
SELECT D.DNAME,
MIN(E.SAL) AS MINSAL,
MAX(E.SAL) AS MAXSAL,
SUM(E.SAL) AS TOTSAL,
AVG(E.SAL) AS AVGSAL,
COUNT(*) AS CNT
FROM EMP AS E INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME
 after creating view whenever we want dept wise summary then execute the
following simple query

SELECT * FROM CV2

difference between simple and complex views ?

simple complex

1 based on single table based on multiple tables

2 query performs simple query performs complex operations

operations

3 allows dmls doesn't allows

(updatable) (not updatable)

=> list of tables created by user ?

SELECT * FROM INFORMATION_SCHEMA.TABLES

------------------------------------ ------------
SCHEMA TABLE
=> list of views created by user ?

SELECT * FROM INFORMATION_SCHEMA.VIEWS

--------------------------------- -----------
schema table

Droping view :-
--------------------

DROP VIEW V1

if we drop table what about views created on table ?

ANS :- views are not dropped but cannot be queried

6-mar-25

SYNONYMS :-
===========
=> a synonym is another name or alternative name for a table or view

=> if tablename is lengthy then we can give a simple and short name to the
table called synonym and instead of using tablename we can use
synonym name in SELECT/INSERT/UPDATE/DELETE queries.

CREATE SYNONYM <name> FOR <tabname>

Ex :-

CREATE SYNONYM E FOR EMP

=> after creating synonym instead of using tablename use synonym name
in SELECT / INSERT / UPDATE / DELETE queries.

SELECT * FROM E

Question :-

1 CREATE SYNONYM E FOR EMP

2 SELECT * FROM EMP AS E

3 SP_RENAME 'EMP','E' => changes tablename from EMP to E

difference between synonym and alias ?

synonym alias

1 permanent temporary

2 stored in db not stored in db

3 scope of the synonym scope of the alias is upto the db is upto the query

=> list of synonyms created by user ?

SELECT name,base_object_name FROM sys.synonyms

E EMP

Droping synonyms :-
--------------------------
 DROP SYNONYM E

=> if we drop table what about synonyms created on table ?

ans :- synonyms are not dropped but becomes invalid

Question :-
--------------

SQL> DROP TABLE products;

What is the implication of this command? (Choose all that apply.)

A. All data along with the table structure is deleted. T

B. All indexes on the table will remain but they are invalidated. F
C. All views and synonyms will remain but they are invalidated. T
D. All data in the table are deleted but the table structure will remain. F

SEQUENCES :-
----------------------
=> a sequence is also db object created to generate sequence numbers.
=> sequences are mostly used for primary key columns.

CREATE SEQUENCE <name>

[START WITH <value>]
[INCREMENT BY <value>]
[MAXVALUE <value>]
[MINVALUE <value>]
[CYCLE/NOCYCLE]
[CACHE <size>]

Ex 1 :-

CREATE SEQUENCE S1
START WITH 1
INCREMENT BY 1
MAXVALUE 5

using sequence :-
------------------------

CREATE TABLE student

(
sid INT PRIMARY KEY,
sname VARCHAR(10)
)

use sequence s1 to generate sid for every new student

INSERT INTO student VALUES(NEXT VALUE FOR S1,'A')

INSERT INTO student VALUES(NEXT VALUE FOR S1,'B')
INSERT INTO student VALUES(NEXT VALUE FOR S1,'C')
INSERT INTO student VALUES(NEXT VALUE FOR S1,'D')
INSERT INTO student VALUES(NEXT VALUE FOR S1,'E')
INSERT INTO student VALUES(NEXT VALUE FOR S1,'F') => ERROR

Ex 2 :-

CREATE SEQUENCE S2
START WITH 100
INCREMENT BY 1
MAXVALUE 9999

use above sequence to update empno ?

UPDATE EMP SET EMPNO = NEXT VALUE FOR S2

Ex 3 :-

BILL
BILLNO BDATE BAMT
DM/060325/1 2025-03-25 2000
DM/060325/2

CREATE TABLE BILL

(
BILLNO VARCHAR(20) PRIMARY KEY,
BDATE DATETIME,
BAMT MONEY
)

CREATE SEQUENCE S3
START WITH 1
INCREMENT BY 1
 MAXVALUE 999999

use above sequence to generate billno for every new bill ?

INSERT INTO BILL

VALUES('DM/' + FORMAT(dated,'ddMMyy') + '/' +
CAST(NEXT VALUE FOR S3 AS VARCHAR) ,GETDATE(),2000)

CYCLE option :-
-----------------------
=> by default sequence is created with NOCYCLE i.e. it starts from start with
and generates upto max and after reaching max then it stops.

=> if sequence created with CYCLE then after reaching max then it will be
reset to min.

=> use CYCLE option to reuse sequence.

CREATE SEQUENCE S5
START WITH 1
INCREMENT BY 1
MAXVALUE 5
MINVALUE 1
CYCLE

list of sequences created by user ?

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES

Droping sequences :-
-----------------------------

DROP SEQUENCE S1

7-MAR-25

INDEXES :-
----------------
=> index is also a db object created to improve the performace of data accessing.

=> index makes data accessing faster.

=> index in db is similar to index in textbook , In textbook using index a particular topic
can be located fastly and In db using index a particular row can be located fastly.

=> indexes are created on columns and it is called index key.

=> indexes created on columns

1 that are frequently used in where clause

2 which are used in join operation
Types of Indexes :-
--------------------------

1 Non Clustered Index simple composite

unique
2 Clustered

simplex non clustered index :-

------------------------------------------

=> If index created on single column then it is called simple index

CREATE INDEX <NAME> ON <TABNAME>(COLNAME)

Ex :-

CREATE INDEX I1 ON EMP(SAL)

=> after creating index sql server creates a structure called BTREE (balanced binary tree)

EMP 3000
SAL
5000
1000
3000 2000 4000
2000
4000 1000 * 2500 * 4000 * 5000 *
3000 1500 * 3000 *,*
2500 2000 *
1500

SELECT * FROM EMP WHERE SAL = 3000; (INDEX)

SELECT * FROM EMP WHERE SAL >= 3000; (INDEX)
SELECT * FROM EMP WHERE SAL<=3000; (INDEX)
 SELECT * FROM EMP WHERE ENAME='BLAKE' (TABLE)
SELECT * FROM EMP (TABLE)

Composite index :-
----------------------------
=> if index created on multiple columns then it is called composite index

CREATE INDEX I2 ON EMP(DEPTNO,JOB)

20

10 30

10 CLERK * 20 ANALYST *,* 30 CLERK *

10 MGR * 20 CLERK *,* 30 MGR *
20 MGR * 30 SALESMAN *,*,*,*

=> SQL SERVER uses above index when where cond based on leading column
of the index i.e. deptno

SELECT * FROM EMP WHERE DEPTNO = 20 (INDEX)

SELECT * FROM EMP WHERE DEPTNO=20 AND JOB='CLERK' (INDEX)
SELECT * FROM EMP WHERE JOB='CLERK' (TABLE)

UNIQUE index :-
-----------------------

=> unique index doesn't allow duplicates into the column on which index is created

CREATE UNIQUE INDEX I3 ON EMP(ENAME)

G Q
ADAMS * JAMES * MARTIN * SCOTT *
ALLEN * JONES * MILLER * SMITH *
BLAKE *

1 SELECT * FROM EMP WHERE ENAME='BLAKE'

2 INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES(9999,'BLAKE',3000) => ERROR

How many methods are there to enforce uniqueness ?

1 primary key / unique

2 create unique index

NOTE :-

=> primary key / unique columns are implicitly indexed by sql server.

=> sql server creates unique index on primary key / unique columns and
unique index doesn't allow duplicates so primary key / unique also doesn't allow
duplicates.

8-mar-25

clustered index :-
-----------------------

=> a non clustered index stores pointers to actual records but where as
clustered index stores actual records.

=> In non clustered , index and table are two seperate objects but where as
clustered index and table are one object.

Ex :-

CREATE TABLE CUST

(
CID INT ,
CNAME VARCHAR(10)
)

CREATE CLUSTERED INDEX I10 ON CUST(CID)

INSERT INTO CUST VALUES(10,'A')

INSERT INTO CUST VALUES(60,'B')
INSERT INTO CUST VALUES(40,'C')
INSERT INTO CUST VALUES(80,'D')
 50

30 70

10 A 40 C 60 B 80 D

1 SELECT * FROM CUST WHERE CID=40

for the above query sql server goes to index and find the entry cid=40
and returns row from index

2 SELECT * FROM CUST

for the above query sql server goes to index and access all leaf nodes
from left to right.

NOTE :-

=> only one clustered index allowed per table.

=> by default sql server creates clustered index on primary key column.

difference between non clustered and clustered indexes ?

non clustered clustered

1 stores pointers to actual records stores actual records

2 index and table are two seperate index and table are
objects not two seperate objects
they are one object

3 needs extra storage doesn't need extra storage

4 required two lookups to requires one lookup

find the required row to find the required row

5 sql server allows sql server allows only one

999 non clustered clustered index per table
indexes per table

6 by default it is created by default it is created on

 on unique columns primary key column

=> list of indexes created on table ?

SP_HELPINDEX CUST

Droping index :-
-----------------------

DROP INDEX EMP.I1

if we drop table what about indexes created on table ?

ANS :- indexes are also dropped

SERVER
DATABASE
TABLE
ROWS & COLS
CONSTRAINTS
INDEXES
TRIGGERS
VIEW
SYNONYM
SEQUENCE

IDENTITY :-
----------------

=> used to generate sequence numbers

=> used to auto increment column values

IDENTITY(SEED,INCR)

Ex :-

CREATE TABLE CUST

(
CUSTID INT IDENTITY(100,1) ,
CNAME VARCHAR(10)
)

INSERT INTO cust(CNAME) VALUES('A')

 INSERT INTO cust(CNAME) VALUES('B')
INSERT INTO cust(CNAME) VALUES('C')
INSERT INTO cust(CNAME) VALUES('D')

SELECT * FROM CUST

CID CNAME
100 A
101 B
102 C
103 D
difference between IDENTITY &
SEQUENCE ?

IDENTITY SEQUENCE

1 identity bind to specific not bind to any table and column

table and specific column

2 value of the identity cannot value of the sequence can be

referred from application referred from applicaton programs
programs

3 identity cannot be reset sequence can be reset

SQL

COMMANDS CLAUSES OPERATIONS OBJECTS

DDL WHERE FILTERING TABLES

DML ORDER BY SORTING VEWS
DQL DISTINCT ELIMINATING DUPLICATES SYNONYMS
TCL TOP TOP N ROWS SEQUENCES
DCL GROUP BY GROUPING INDEXES
HAVING FILTERING GROUPS SCHEMAS
ON JOIN
WITH SUB-QUERIES

=================================================================
10-MAR-25

T-SQL PROGRAMMING :-
-----------------------------------

1 Basic programming
2 conditional statements
3 loops
4 cursors
5 error handling
6 stored procedures
7 functions
8 triggers
9 dynamic sql

Features :-
==========

1 improves performance :-
---------------------------------

=> In T-SQL , sql commands can be grouped into one block and we submit
that block to sql server , so in T-SQL no of requests and response between
user and sql server are reduced and performane is improved.

2 supports conditional statements :-

--------------------------------------------

=> T-SQL supports conditional statements like IF-ELSE , so in T-SQL

we can execute sql commands based on conditions.

3 supports loops :-
----------------------

=> T-SQL supports looping statements like while and with the help of
loops we can execute sql commands repeatedly multiple times.

4 supports error handling :-

----------------------------------

=> In T-SQL , if any statement causes error then we can handle that error
and we can replace system generated message with our own simple
and user friendly message.
5 supports reusability :-
------------------------------

=> T-SQL programs can be stored in db and front-end applications which

are connected to db can reuse t-sql programs.

=> T-SQL blocks are 2 types

1 Anonymous Blocks
2 Named Blocks
STORED PROCEDURES
FUNCTIONS
TRIGGERS

Anonymous Blocks :-
-----------------------------

=> a T-SQL block without name is called anonymous block.

=> the following statements are used in T-SQL programming

1 DECLARE
2 SET
3 PRINT

DECLARE :-
-----------------

=> statement used to declare variables

DECLARE @varname datatype(size)

Ex :-

DECLARE @x INT
DECLARE @s VARCHAR(10)
DECLARE @d DATE

OR

DECLARE @x INT,@s VARCHAR(10),@d DATE

SET :-
----------

=> statement used to assign value to variable.

SET @varname = value

Ex :-
SET @x = 100
SET @s = 'hello'
SET @d = getdate()

PRINT :-
-------------

=> statement used to print messages or variable values

PRINT message

Ex :- PRINT 'hello'
PRINT @x

Ex :-

=> wap to add two numbers ?

DECLARE @a INT,@b INT,@c INT

SET @a=100
SET @b=200
SET @c = @a+@b
PRINT @c

=> wap to input date and print day of the week ?

DECLARE @d DATE
SET @d = getdate()
PRINT DATENAME(DW,@d)

DB Programming with T-SQL :-

------------------------------------------

=> To work with db execute SQL commands from T-SQL program

=> following commands can be executed from T-SQL program
1 DML (insert,update,delete,merge)
2 DQL (select)
3 TCL (commit,rollback,save transaction)

SELECT stmt syntax :-

------------------------------
SELECT @var1 = col1 , @var2 = col2, -------------
FROM tabname
[WHERE cond]

Ex 1 :-

=> wap to input empno and print name & sal ?

DECLARE @eno INT,@name VARCHAR(10),@sal MONEY

SET @eno = 7844
SELECT @name = ename , @sal = sal
FROM emp
WHERE empno = @eno
PRINT @name + ' ' + CAST(@sal AS VARCHAR)

Ex 2 :-

=> wap to input empno and calculate and print experience ?

DECLARE @eno INT,@hire DATE,@expr INT

SET @eno = 7566
SELECT @hire = hiredate FROM emp WHERE empno = @eno
SET @expr = DATEDIFF(YY,@hire,GETDATE())
PRINT 'Experience = ' + CAST(@expr AS VARCHAR) + ' years'

o/p :-

Experience = 44 years

11-mar-25

=> wap to input empno and calculate total sal ?

total sal = sal + comm

DECLARE @eno INT,@sal MONEY,@comm MONEY,@totsal MONEY

SET @eno = 7499
 SELECT @sal=sal , @comm=comm FROM emp WHERE empno = @eno
SET @totsal = @sal + ISNULL(@comm,0)
PRINT @totsal

Conditional statements :-
----------------------------------

1 IF-ELSE
2 MULTI IF
3 NESTED IF

IF-ELSE :-
---------------

IF COND BEGIN
statements
END
ELSE BEGIN
statements
END

MULTI IF :-
---------------
IF COND1
BEGIN
statements
END
ELSE IF COND2 BEGIN
statements
END
ELSE IF COND3 BEGIN
statements
END
ELSE BEGIN
statements
END

NESTED IF :-
-------------------

IF COND
BEGIN
IF COND BEGIN statements
 END
ELSE
BEGIN
statements
END
END
ELSE
BEGIN
statements
END

Ex :-

=> wap to input empno and increment salary by specific amount and after increment
if salary exceeds 5000 then cancel that increment ?

DECLARE @eno INT,@amt MONEY,@sal MONEY

SET @eno = 7788
SET @amt=2500
BEGIN TRANSACTION
UPDATE emp SET sal = sal + @amt WHERE empno = @eno
SELECT @sal=sal FROM emp WHERE empno = @eno
IF @sal > 5000
ROLLBACK
ELSE
COMMIT

DECLARE @eno INT,@pct MONEY,@sal MONEY

SET @eno = 7788
SET @pct=20
BEGIN TRANSACTION
UPDATE emp SET sal = sal + (sal*@pct/100) WHERE empno = @eno
SELECT @sal=sal FROM emp WHERE empno = @eno
IF @sal > 5000
ROLLBACK
ELSE
COMMIT

=> wap to input empno and increment sal as follows ?

if job=CLERK incr sal by 10%

 SALESMAN 15%
MANAGER 20% others
5%

DECLARE @eno INT,@job VARCHAR(10),@pct INT

SET @eno = 7844
SELECT @job=job FROM emp WHERE empno = @eno
IF @job='CLERK'
SET @pct=10
ELSE IF @job='SALESMAN'
SET @pct=15
ELSE IF @job='MANAGER'
SET @pct=20
ELSE
SET @pct=5
UPDATE emp SET sal = sal + (sal*@pct/100) WHERE empno = @eno

=> wap to process bank transaction ( W / D ) ?

ACCOUNTS
ACCNO ACTYPE BAL
100 S 10000
101 S 20000

TRANSACTIONS
TRID TTYPE TDATE TAMT ACCNO

CREATE TABLE ACCOUNTS

(
ACCNO INT PRIMARY KEY,
ACTYPE VARCHAR(1),
BAL MONEY
)

INSERT INTO ACCOUNTS VALUES(100,'S',10000),(101,'S',20000)

CREATE TABLE TRANSACTIONS

(
TRID INT ,
TTYPE VARCHAR(1),
TDATE DATETIME,
TAMT MONEY,
ACCNO INT REFERENCES ACCOUNTS(ACCNO)
 )

CREATE SEQUENCE S10

START WITH 1
INCREMENT BY 1
MAXVALUE 999999

DECLARE @acno INT,@type VARCHAR(1),@amt MONEY,@bal MONEY

SET @acno=100
SET @type='W'
SET @amt=1000
IF @type='W'
BEGIN
SELECT @bal = bal FROM accounts WHERE accno = @acno
IF @amt > @bal
PRINT 'insufficient balance'
ELSE
BEGIN
UPDATE accounts SET bal = bal - @amt WHERE accno = @acno
INSERT INTO transactions VALUES(next value for S10,'W',getdate(),@amt,@acno)
END
END
ELSE IF @type='D'
BEGIN
UPDATE accounts SET bal = bal + @amt WHERE accno = @acno
INSERT INTO transactions VALUES(next value for S10,'D',getdate(),@amt,@acno)
END
ELSE
PRINT 'invalid transaction type'

12-MAR-25

=> wap to process money transfer ?

DECLARE @sacno INT,@tacno INT,@amt MONEY,@bal MONEY

SET @sacno = 100
SET @tacno = 101
SET @amt=1000
SELECT @bal = bal FROM accounts WHERE accno = @sacno
IF @amt > @bal
PRINT 'insufficient balance'
ELSE
BEGIN
 BEGIN TRANSACTION
UPDATE accounts SET bal = bal - @amt WHERE accno = @sacno
UPDATE accounts SET bal = bal + @amt WHERE accno = @tacno
INSERT INTO transactions VALUES(NEXT VALUE FOR
S10,'W',GETDATE(),@amt,@sacno)
INSERT INTO transactions VALUES(NEXT VALUE FOR
S10,'D',GETDATE(),@amt,@tacno)
COMMIT
END

=> wap to input sno and calculate total,avg,result and insert into result table ?

STUDENT
SNO SNAME S1 S2 S3
1 A 80 90 70
2 B 30 60 50

RESULT
SNO TOTAL AVG RESULT

DECLARE @sno INT,@s1 INT,@s2 INT,@s3 INT

DECLARE @total INT,@avg DECIMAL(5,2),@res CHAR(4)
SET @sno=1
SELECT @s1 = s1 , @s2 = s2 , @s3 = s3 FROM student WHERE sno = @sno
SET @total = @s1 + @s2 + @s3
SET @avg = @total/3.0
IF @s1>=35 AND @s2>=35 AND @s3>=35
SET @res='pass'
ELSE
SET @res='fail'
INSERT INTO result VALUES(@sno,@total,@avg,@res)

WHILE loop :-
--------------------

WHILE(cond)
BEGIN
statements
END

if cond = true loop continues

if cond = false loop terminates
=> wap to print numbers from
1 to 20 ?

DECLARE @x INT = 1
WHILE(@x <= 20)
BEGIN
PRINT @x
SET @x = @x + 1
END

=> wap to print 2025 calendar ?

2025-01-01 ?
2025-01-02 ?

2025-12-31 ?

DECLARE @d1 DATE,@d2 DATE

SET @d1 = '2025-01-01'
SET @d2 = '2025-12-31'
WHILE(@d1<=@d2)
BEGIN
PRINT CAST(@d1 AS VARCHAR) + ' ' + DATENAME(DW,@d1)
SET @d1 = DATEADD(DD,1,@d1)
END

=> wap to print sundays in 2025 year ?

DECLARE @d1 DATE,@d2 DATE

SET @d1 = '2025-01-01'
SET @d2 = '2025-12-31'
WHILE(@d1<=@d2)
BEGIN
IF DATENAME(DW,@d1) = 'sunday'
PRINT CAST(@d1 AS VARCHAR) + ' ' + DATENAME(DW,@d1)
SET @d1 = DATEADD(DD,1,@d1)
END

DECLARE @d1 DATE,@d2 DATE

SET @d1 = '2025-01-01'
SET @d2 = '2025-12-31'
/* to find first sunday */
 WHILE(DATENAME(DW,@d1) <> 'SUNDAY')
BEGIN
SET @d1 = DATEADD(DD,1,@d1)
END
/* to print sundays */
WHILE(@d1<=@d2)
BEGIN
PRINT CAST(@d1 AS VARCHAR) + ' ' + DATENAME(DW,@d1)
SET @d1 = DATEADD(DD,7,@d1)
END

=> wap to print EMI dates ?

2025-04-05

2035-04-05

=> wap to input string and print following pattern ?

INPUT :- WELCOME

output :-

W
E
L
C
O
M
E

DECLARE @s1 VARCHAR(20) , @x INT = 1

SET @s1 = 'WELCOME'
WHILE(@x <= LEN(@s1))
BEGIN
PRINT SUBSTRING(@s1 , @x , 1 )
SET @x = @x + 1
END

=> wap to input string and print following pattern ?

INPUT :- WELCOME
output :-
W
WE
WEL
WELC
WELCO
WELCOM
WELCOME

DECLARE @s1 VARCHAR(20) , @x INT = 1

SET @s1 = 'WELCOME'
WHILE(@x <= LEN(@s1))
BEGIN
PRINT SUBSTRING(@s1,1,@x)
SET @x = @x + 1
END

=> wap to input string and print reverse of that string

INPUT :- WELCOME

OUTPUT :- EMOCLEW

13-mar-25

CURSORS :-
==========

=> cursors are used to process multiple rows in T-SQL program.

=> using cursor we can access row-by-row in T-SQl program.
=> follow below steps to use cursor

1 declare cursor
2 open cursor
3 fetch record from cursor
4 close cursor
5 deallocate cursor

Declaring cursor :-
---------------------------

DECLARE <name> CURSOR FOR SELECT statement Ex :-

 DECLARE C1 CURSOR FOR SELECT ename,sal FROM emp

Opening :-
-------------

OPEN <cursor-name>

Ex :- OPEN C1

1 select stmt submitted to sql server

2 sql server executes the query
3 data returned by query is copied to cursor
4 c1 points to cursor

Fetching records from cursor :-

----------------------------------------

=> "FETCH" statemet used to fetch record from cursor

FETCH NEXT FROM <cursor-name> INTO <variables>

Ex :-

FETCH NEXT FROM C1 INTO @name,@sal

=> a fetch stmt fetches one row at a time but to process multiple rows
fetch stmt should be executed multiple times , so fetch stmt should be in a
loop.

Closing cursor :-
--------------------

CLOSE C1 ;

Deallocate cursor :-
---------------------------

DEALLOCATE C1 ;

@@FETCH_STATUS :-
-------------------------------
=> It is a system variable that returns fetch status i.e. fetch is successful or not
 => if fetch is successful then returns 0 otherwise returns -1
=> used for loop condition

Ex :-

=> wap to print all employee names and salaries ?

DECLARE C1 CURSOR FOR SELECT ename,sal FROM emp

DECLARE @name VARCHAR(10),@sal MONEY
OPEN C1
FETCH NEXT FROM C1 INTO @name,@sal
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT @name + ' ' + CAST(@sal as varchar)
FETCH NEXT FROM C1 INTO @name,@sal
END
CLOSE C1
DEALLOCATE C1

=> wap to calculate total sal without using sum function ?

DECLARE C1 CURSOR FOR SELECT sal FROM emp

DECLARE @sal MONEY,@t MONEY = 0
OPEN C1
FETCH NEXT FROM C1 INTO @sal
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @t = @t + @sal
FETCH NEXT FROM C1 INTO @sal
END
PRINT @t
CLOSE C1
DEALLOCATE C1

=> wap to print all employee names as follows ?

smith,allen,ward,jones,---------

DECLARE C1 CURSOR FOR SELECT ename FROM emp

DECLARE @name VARCHAR(10),@s VARCHAR(1000)=''
OPEN C1
FETCH NEXT FROM C1 INTO @name
WHILE(@@FETCH_STATUS=0)
 BEGIN
SET @s = @s + @name + ','
FETCH NEXT FROM C1 INTO @name
END
PRINT STUFF(@s,LEN(@s),1,'.')
CLOSE C1
DEALLOCATE C1

=> wap to find max sal without using max function ?

=> wap to find min sal without using min function ?

STRING_AGG() :-
--------------------------

=> function used to concatenate column values

STRING_AGG(colname,seperator)

Ex :-

SELECT STRING_AGG(ename,',') FROM emp

o/p :- SMITH,ALLEN,WARD,----------------------

14-mar-25

=> wap to find max sal without using max function ?

DECLARE C1 CURSOR FOR SELECT sal FROM EMP

DECLARE @sal MONEY,@max MONEY
OPEN C1
FETCH NEXT FROM C1 INTO @max
WHILE(@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM C1 INTO @sal
IF @sal > @max
SET @max = @sal
END
PRINT @max
CLOSE C1
DEALLOCATE C1
DECLARE C1 CURSOR FOR SELECT sal FROM EMP ORDER BY sal DESC
DECLARE @max MONEY
OPEN C1
FETCH NEXT FROM C1 INTO @max
PRINT @max
CLOSE C1
DEALLOCATE C1

=> wap to find min sal without using max function ?

DECLARE C1 CURSOR FOR SELECT sal FROM EMP

DECLARE @sal MONEY,@min MONEY
OPEN C1
FETCH NEXT FROM C1 INTO @min
WHILE(@@FETCH_STATUS=0)
BEGIN
FETCH NEXT FROM C1 INTO @sal
IF @sal < @min
SET @min = @sal
END
PRINT @min
CLOSE C1
DEALLOCATE C1

DECLARE C1 CURSOR FOR SELECT sal FROM EMP ORDER BY sal ASC
DECLARE @max MONEY
OPEN C1
FETCH NEXT FROM C1 INTO @max
PRINT @max
CLOSE C1
DEALLOCATE C1

=> wap to calculate all students total,avg,result and insert into result table ?

STUDENT
SNO SNAME S1 S2 S3
1 A 80 90 70
2 B 30 60 50

RESULT
SNO TOTAL AVG RESULT
DECLARE C1 CURSOR FOR SELECT sno,s1,s2,s3 FROM student
DECLARE @sno INT,@s1 INT,@s2 INT,@s3 INT,@total INT,@avg DECIMAL(5,2)
DECLARE @res CHAR(4)
OPEN C1
FETCH NEXT FROM C1 INTO @sno,@s1,@s2,@s3
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @total = @s1 + @s2 + @s3
SET @avg = @total/3
IF @s1>=35 AND @s2>=35 AND @s3>=35
SET @res='PASS'
ELSE
SET @res='FAIL'
INSERT INTO result VALUES(@sno,@total,@avg,@res)
FETCH NEXT FROM C1 INTO @sno,@s1,@s2,@s3
END
CLOSE C1
DEALLOCATE C1

SCROLLABLE CURSOR :-
---------------------------------------

=> by default cursor is forward only cursor and supports only forward
navigation but doesn't support backward navigation.

=> if cursor declared with "SCROLL" then it is called scrollable cursor

and it supports both forward and backward navigation.

=> a forward only cursor supports only FETCH NEXT statement but
scrollable cursor supports the following fetch statements

FETCH FIRST => fetches first row

FETCH NEXT => fetches next row
FETCH LAST => fetches last row
FETCH PRIOR => fetches previous row
FETCH ABSOLUTE N => fetches Nth row from first row
FETCH RELATIVE N => fetches Nth row from current row

=> wap to print employee names from last to first ?

DECLARE C1 CURSOR SCROLL FOR SELECT ename FROM EMP

DECLARE @name VARCHAR(20)
OPEN C1
FETCH LAST FROM C1 INTO @name
WHILE(@@FETCH_STATUS=0)
BEGIN
 PRINT @name
FETCH PRIOR FROM C1 INTO @name
END
CLOSE C1
DEALLOCATE C1

=> wap to print every 5th row ?

DECLARE C1 CURSOR SCROLL FOR SELECT ename FROM EMP

DECLARE @name VARCHAR(20)
OPEN C1
FETCH ABSOLUTE 5 FROM C1 INTO @name
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT @name
FETCH RELATIVE 5 FROM C1 INTO @name
END
CLOSE C1
DEALLOCATE C1

==================================================================

15-MAR-25

ERROR HANDLING / EXCEPTION HANDLING :-

=======================================

1 syntax errors
2 logical errors
3 runtime errors (exceptions)

=> errors that are raised during program execution are called runtime errors

ex :- declare @x tinyint
set @x = 1000 => runtim error
print @x

=> if any statement causes runtime error then sql server displays error
message and continues program execution.
=> To replace system generated message with our own simple and user
friendly message then we need to handle that runtime error

=> To handle runtime errors include a block called TRY-----CATCH----- block

 BEGIN TRY
statements => stmts causes exception
END TRY
BEGIN CATCH
statements => stmts handles exception
END CATCH

=> if any statement in try block causes runtime error then control is transferred
to catch block and executes the statements in catch block.

Ex 1 :-

DECLARE @a tinyint,@b tinyint,@c tinyint

BEGIN TRY
SET @a=100
SET @b=0
SET @c = @a/@b
PRINT @c
END TRY
BEGIN CATCH
PRINT 'error try again----'
END CATCH

Error handling functions :-

-----------------------------------

1 ERROR_NUMBER() => returns error code

2 ERROR_MESSAGE() => returns error message

DECLARE @a tinyint,@b tinyint,@c tinyint

BEGIN TRY
SET @a=100
SET @b=0
SET @c = @a/@b
PRINT @c
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 220
PRINT 'value exceeding limit'
ELSE IF ERROR_NUMBER() = 8134
PRINT 'divisor cannot be zero'
ELSE
 PRINT 'unknown error'
END CATCH

=> wap to insert data into emp44 table ?

create table emp44

(
empid int primary key,
ename varchar(10) not null,
sal money check(sal>=3000)
)

DECLARE @eno INT,@name VARCHAR(10),@sal MONEY

BEGIN TRY
SET @eno=2
SET @name='B'
SET @sal=1000
INSERT INTO emp44 VALUES(@eno,@name,@sal)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 2627
PRINT 'empno should not be duplicate'
ELSE IF ERROR_NUMBER() = 515
PRINT 'name should not be null'
ELSE IF ERROR_NUMBER() = 547
PRINT 'sal >= 3000'
END CATCH

USER DEFINED ERRORS :-

-----------------------------------------

=> errors raised by user are called user defined errors

=> user raises error to stop program execution based on some condition
=> the following procedure is used to raise user define error

RAISERROR(error msg,severity level,state)

serverity level => 0 to 25

0 - 10 informational message
11-18 error
19-25 fatal error (connect to server is terminated)
 state => 0 to 255 => used when same error raised
at multiple locations by using this state
we can identity.

Ex :-

=> wap to input empno and increment sal but sunday updates are not allowed ?

DECLARE @eno INT,@amt MONEY

SET @eno=7788
SET @amt=1000
IF DATENAME(DW,GETDATE())='SUNDAY'
RAISERROR('sunday not allowed',15,1)
ELSE
UPDATE emp SET sal = sal + @amt WHERE empno = @eno

=> wap to process money transfer ?

ACCOUNTS
ACCNO ACTYPE BAL
100 S 10000
101 S 20000

TRANSACTIONS
TRID TTYPE TDATE TAMT ACCNO

CREATE SEQUENCE S10

START WITH 1
INCREMENT BY 1
MAXVALUE 9999999

DECLARE @sacno INT,@tacno INT,@amt MONEY,@bal MONEY

DECLARE @cnt1 INT,@cnt2 INT,@msg VARCHAR(1000)
BEGIN TRY
SET @sacno = 100
SET @tacno = 101
SET @amt=1000
SELECT @cnt1 = COUNT(*) FROM accounts WHERE accno = @sacno
IF @cnt1=0
RAISERROR('source account does not exists',16,1)
SELECT @cnt2 = COUNT(*) FROM accounts WHERE accno = @tacno
IF @cnt2=0
 RAISERROR('target account does not exists',16,1)
SELECT @bal = bal FROM accounts WHERE accno = @sacno
IF @amt > @bal
RAISERROR('insufficient balance',15,1)
BEGIN TRANSACTION
UPDATE accounts SET bal = bal - @amt WHERE accno = @sacno
UPDATE accounts SET bal = bal + @amt WHERE accno = @tacno
INSERT INTO transactions VALUES(NEXT VALUE FOR
S10,'W',GETDATE(),@amt,@sacno)
INSERT INTO transactions VALUES(NEXT VALUE FOR
S10,'D',GETDATE(),@amt,@tacno)
COMMIT
END TRY
BEGIN CATCH
SET @msg = ERROR_MESSAGE()
RAISERROR(@msg,16,1)
END CATCH

17-mar-25

Named T-SQL blocks :-

---------------------------------

1 stored procedures
2 functions
3 triggers

sub-programs :-
----------------------

1 stored procedures
2 functions

Advantages :-
---------------------

1 modular programming :-
----------------------------------

=> with the help of procedures & functions a big t-sql program can be
divided into small modules.

2 reusability :- ------------------
 => proc & func are created with name , so proc & func can be called
from another program.

3 invoked from front-end applications :-

-------------------------------------------------

=> proc & func are stored in db , so applications like asp.net,python,java

connected to sql server can call procedures & functions.

4 improves performance :-
-----------------------------------

=> proc & func improves performance because of one time compilation i.e.
when we create a procedure program is compiled and stored in db and
whenever we call only execution is repeated but not compilation , so this
improves performance.

STORED PROCEDURES :-
======================

=> a procedure is a named T-SQL block that takes some input

performs some action on db tables and may or may not returns a value.

=> procedures are created to perform one to more dml operations on table.

CREATE OR ALTER PROCEDURE <name>

parameters if any
AS statements

parameters :-
-------------------

1 INPUT (DEFAULT)
2 OUTPUT

=> input parameter always receives value

=> output parameter always sends value

ASP.NET PROCEDURE

A ============> X (INPUT)
 B <============= Y (OUTPUT)

Ex 1 :-

=> create procedure to increment all the employee salaries by 1000 ?

CREATE OR ALTER PROCEDURE raise_salary

AS
UPDATE emp SET sal = sal + 1000

procedure created ( compiled + stored in db)

Execution :-
---------------

1 from ssms
2 another t-sql program 3 front-end applications

from ssms :-
----------------

EXECUTE raise_salary

Ex 2 :- INPUT parameter example

=> create a procedure to increment specific employee sal by specific amount ?

CREATE OR ALTER PROCEDURE raise_salary

@eno INT,
@amt MONEY
AS
UPDATE emp SET sal = sal + @amt WHERE empno = @eno

Execution :-
----------------

1 EXECUTE raise_salary 7369,1000 positional association

2 EXECUTE raise_salary @eno=7369 , @amt = 1000 named association

3 EXECUTE raise_salary @amt = 1000 ,@eno=7369

Ex 3 :- OUTPUT parameter example

create a procedure to increment specific employee sal by specific amount

and after increment send the updated sal to calling program ?

CREATE OR ALTER PROCEDURE raise_salary

@eno INT ,
@amt MONEY,
@newsal MONEY OUTPUT
AS
UPDATE emp SET sal = sal + @amt WHERE empno = @eno
SELECT @newsal = sal FROM emp WHERE empno = @eno

Execution :-

declare @s money execute raise_salary

7369,1000,@s output print @s

18-mar-25

Declaring parameter with default value :-

-------------------------------------------------------

=> a parameter can be declared with default value as follows

@amt MONEY = 500

=> while executing procedure if we don't pass value to @amt then

sql server assigns default value.

CREATE OR ALTER PROCEDURE raise_salary

@eno INT ,
@amt MONEY = 500,
@newsal MONEY OUTPUT
AS
UPDATE emp SET sal = sal + @amt WHERE empno = @eno
SELECT @newsal = sal FROM emp WHERE empno = @eno
Execution :-

declare @s money execute raise_salary

7369,DEFAULT,@s output print @s

declare @s money execute raise_salary

@eno=7369,@newsal=@s output print @s

Ex :-

=> create a procedure to implement money withdrawl ?

ACCOUNTS
ACCNO ACTYPE BAL
100 S 10000
101 S 20000

TRANSACTIONS
TRID TTYPE TDATE TAMT ACCNO

CREATE SEQUENCE S10

START WITH 1
INCREMENT BY 1
MAXVALUE 99999

CREATE OR ALTER PROCEDURE DEBIT

@acno INT,
@amt MONEY,
@newbal MONEY OUTPUT
AS
DECLARE @bal MONEY
SELECT @bal = bal FROM accounts WHERE accno = @acno IF @amt > @bal
RAISERROR('insufficient balance',15,1)
ELSE
BEGIN
BEGIN TRANSACTION
UPDATE accounts SET bal = bal - @amt WHERE accno = @acno
INSERT INTO transactions VALUES(next value for S10,'W',getdate(),@amt,@acno)
COMMIT
END

USER DEFINE FUNCTIONS :-

-------------------------------------------

=> functions created by user are called user define functions.

=> when predefine functions not meeting our requirements then we create
our own functions called user define functions.

=> user define functions are 2 types

1 SCALAR VALUED (SVF)

2 TABLE VALUED (TVF)

SCALAR VALUED FUNCTIONS :-

----------------------------------------------

=> a function is also a named T-SQL block that accepts some input
performs some calculation and must return a value.

=> return type must be scalar types like int,varchar,money etc.

=> return expr must be a scalar variable.

CREATE OR ALTER FUNCTION <name>(parameters) RETURNS <type>

AS
BEGIN
statements
RETURN <expr>
END

Ex 1 :-

CREATE OR ALTER FUNCTION CALC

(
@a INT,@b INT,@op CHAR(1)
) RETURNS INT
AS
BEGIN
DECLARE @c INT
IF @op='+'
SET @c = @a + @b
ELSE IF @op='-'
SET @c = @a - @b
ELSE IF @op='*'
 SET @c = @a*@b
ELSE
SET @c = @a/@b
RETURN @c
END

Execution :-
----------------

1 SQL commands
2 another program
3 front-end applications

Executing from sql commands :-

--------------------------------------------

SELECT DBO.CALC(10,20,'*') => 200

Ex 2 :-

=> create a function to calculate total amount of particular order ?

orders products
ordid pid qty pid pname price
1000 100 2 100 A 2000
1000 101 3 101 B 1000
1000 102 1 102 C 500
1001 100 3

input :- ordid = 1000

output :- amount = 7500

CREATE OR ALTER FUNCTION getOrdAmt

(
@d INT
) RETURNS MONEY
AS
BEGIN
DECLARE C1 CURSOR FOR SELECT o.pid,o.qty,p.price
FROM orders o INNER JOIN products p
 ON o.pid = p.pid
WHERE o.ordid = @d
DECLARE @pid INT,@qty INT,@price MONEY
DECLARE @amt MONEY= 0
OPEN C1
FETCH NEXT FROM C1 INTO @pid,@qty,@price
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @amt = @amt + (@qty * @price)
FETCH NEXT FROM C1 INTO @pid,@qty,@price
END
CLOSE C1
DEALLOCATE C1
RETURN @amt
END

C1
100 2 2000
101 3 1000
102 1 500

Execution :-
------------------

SELECT DBO.GETORDAMT(1000) => 7500

SELECT DBO.GETORDAMT(1001) => 6000

TABLE VALUED FUNCTIONS :-

--------------------------------------------

=> TVF returns records

=> return type must be TABLE
=> return expression must be SELECT stmt
=> TVF functions allows only one stmt and it must be return stmt

CREATE OR ALTER FUNCTION <name>

(
parameters
) RETURNS TABLE
AS
RETURN (select stmt)
Ex 1 :-

=> create a function that accepts deptno and returns employee list working
for that dept ?

CREATE OR ALTER FUNCTION getEmpList

(
@d INT
) RETURNS TABLE
AS
RETURN (SELECT * FROM EMP WHERE DEPTNO = @d)

Execution :-

SELECT * FROM dbo.getEmpList(20)

Ex 2 :-

=> create a function that returns top N employee list based on sal ?

CREATE OR ALTER FUNCTION getTopNEmpList

(
@n INT
) RETURNS TABLE
AS
RETURN ( SELECT *
FROM (SELECT EMPNO,ENAME,SAL,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK
FROM EMP) AS E
WHERE RNK<=@n)

Execution :-

SELECT * FROM DBO.getTopNEmpList(3)

procedures returning records :-

-------------------------------------------

=> To return records from procedure take output parameter of type cursor

Ex :-
 => create a procedure that accepts deptno and returns employee list working
for dept ?

CREATE OR ALTER PROCEDURE getDeptEmpList

@dno INT,
@empcur CURSOR VARYING OUTPUT
AS
SET @empcur = CURSOR FOR SELECT empno,ename,sal
FROM emp
WHERE DEPTNO = @dno
OPEN @empcur

Execution :-

declare @c1 cursor declare @eno int,@name

varchar(10),@sal money execute getDeptEmpList
20,@c1 output fetch next from @c1 into
@eno,@name,@sal
while(@@FETCH_STATUS=0)
begin print @name + ' ' + cast(@sal as
varchar) fetch next from @c1 into
@eno,@name,@sal end close @c1
deallocate @c1

=> create procedures & functions to implement various bank transactions ?

ACCOUNTS
ACCNO ACTYPE BAL

TRANSACTIONS
TRID TTYPE TDATE TAMT ACCNO

CREATE SEQUENCE S10

START WITH 1
INCREMENT BY 1
MAXVALUE 99999

1 account opening (proc)

2 account closing (proc)
3 balance enquiry (svf)
4 money deposit (proc)
5 money withdrawl (proc)
6 money transfer (proc)
7 stmt between two given dates (tvf)
8 latest N transactions of particular customer (tvf)

difference between scalar and table valued functions ?

scalar table

1 returns one value returns records

2 return type must be return type must be table

scalar types like int,
varchar

3 return expr is a scalar return expr is a select stmt variable

4 called in select clause called in from clause

=> difference between procedures & functions ?

procedures functions

1 may or may not returns a value must return a value

2 can return multiple values always returns one value

3 returns values using output returns values using parameter

return stmt

4 allows dmls doesn't allow dmls

5 cannot be called from sql commands can be called from sql commands

6 created to perform one or more created for calculation or actions

like insert,update,delete to fetch value table on table

7 create procedure to update balance create function to get balance

=> list of procedures & functions created by user ?

SELECT * FROM INFORMATION_SCHEMA.ROUTINES

 Droping :-
---------------

DROP PROCEDURE raise_salary

DROP FUNCTION CALC

========================================================================
=

TRIGGERS :-
===========

=> a trigger is also a named T-SQL block like procedure but executed implicitly by sql server.
=> triggers are executed automatically when user submits DML commands to sql server.
=> triggers are created

1 to control DMLs
2 to enforce complex rules and validations
3 to audit day-to-day operations on tables

CREATE OR ALTER TRIGGER <NAME>

ON <TABNAME>
AFTER / INSTEAD OF INSERT,UPDATE,DELETE
AS statements

AFTER triggers :-
-------------------------

=> if trigger is after then sql server executes the trigger after executing dml

INSTEAD OF triggers :-
-----------------------------------

=> if trigger is instead of then sql server executes the trigger instead of executing dml

Ex :-

=> create trigger to not to allow dmls on emp table on sunday ?

CREATE OR ALTER TRIGGER T1

ON EMP
AFTER INSERT,UPDATE,DELETE
 AS
IF DATENAME(DW,GETDATE())='SUNDAY'
BEGIN
ROLLBACK
RAISERROR('sunday not allowed',15,1)
END

Testing :-

UPDATE EMP SET SAL=2000 WHERE EMPNO = 7369 => ERROR

=> create trigger to not to allow dmls on emp table as follows ?

MON - FRI <10AM AND >4PM NOT ALLOWED

SAT <10AM AND >2PM NOT ALLOWED
SUN ---------------------------- NOT ALLOWED

CREATE OR ALTER TRIGGER T2

ON EMP
AFTER INSERT,UPDATE,DELETE
AS
IF DATEPART(DW,GETDATE()) BETWEEN 2 AND 6
BEGIN
IF DATEPART(HH,GETDATE()) < 10 OR DATEPART(HH,GETDATE()) >= 16
BEGIN
ROLLBACK
RAISERROR('only between 10am and 4pm',16,1)
END
END
ELSE IF DATEPART(DW,GETDATE()) = 7
BEGIN
IF DATEPART(HH,GETDATE()) < 10 OR DATEPART(HH,GETDATE()) >= 14
BEGIN
ROLLBACK
RAISERROR('only between 10am and 2pm',16,1)
END
END
ELSE
BEGIN
ROLLBACK
RAISERROR('sunday not allowed',16,1)
 END

Testing :-

update EMP set SAL=1000 where EMPNO = 7369

=> create trigger to not to allow user to update empno ,hiredate ?

create or alter trigger t3

on emp after update
as
IF UPDATE(empno) OR UPDATE(hiredate)
BEGIN
ROLLBACK
RAISERROR('cannot update empno and hiredate',16,1)
END

Testing :-

UPDATE EMP SET EMPNO = 9999 WHERE EMPNO = 7369 => ERROR

Magic tables :-
--------------------

1 INSERTED
2 DELETED

=> these tables are called magic tables because they are created and destroyed automatically.
=> these tables exists upto the trigger execution , once trigger execution is completed then
these tables are deleted.

=> with the help of these two tables in triggers we can access data affected by dmls

INSERT INTO EMP VALUES(100,'A','CLERK',4000,--20) =======> INSERTED

DELETE FROM EMP WHERE EMPNO = 7369 ========> DELETED

UPDATE EMP SET SAL=5000 WHERE EMPNO = 100 ==========> INSERTED

EMPNO SAL
100 5000
 DELETED
EMPNO SAL
100 4000

Ex :-

=> create trigger to not to allow to decrement salary ?

CREATE OR ALTER TRIGGER T4

ON EMP
AFTER UPDATE
AS
DECLARE @OLDSAL MONEY,@NEWSAL MONEY
SELECT @OLDSAL = SAL FROM DELETED
SELECT @NEWSAL = SAL FROM INSERTED
IF @NEWSAL < @OLDSAL
BEGIN
ROLLBACK
RAISERROR('sal cannot be decremented',16,1)
END

Testing :-

UPDATE EMP SET SAL=1000 WHERE EMPNO = 7369 => ERROR

/* 1 row is copied to inserted,deleted tables

2 executes update command
3 executes trigger
*/

21-mar-25

=> create a trigger to insert details into emp_resign table when employee resigns ?

EMP_RESIGN
EMPNO ENAME JOB SAL HIREDATE DOR

CREATE TABLE EMP_RESIGN

(
EMPNO INT ,
ENAME VARCHAR(10),
 JOB VARCHAR(10),
SAL MONEY,
HIREDATE DATE,
DOR DATE
)

CREATE OR ALTER TRIGGER T5

ON EMP
AFTER DELETE
AS
DECLARE @ENO INT,@NAME VARCHAR(10),@JOB VARCHAR(10)
DECLARE @SAL MONEY,@HIRE DATE
SELECT @ENO = EMPNO , @NAME=ENAME,@JOB=JOB,@SAL=SAL,
@HIRE = HIREDATE
FROM DELETED
INSERT INTO EMP_RESIGN
VALUES(@ENO,@NAME,@JOB,@SAL,@HIRE,GETDATE())

OR

INSERT INTO EMP_RESIGN

SELECT EMPNO,ENAME,JOB,SAL,HIREDATE,GETDATE() FROM DELETED

Auditing :-
---------------

=> Auditing means monitoring day-to-day activities on tables like insert,update,delete

EMP_AUDIT
UNAME OPERATION OPTIME NEW_ENO NEW_ENAME NEW_SAL OLD_ENO
OLD_ENAME OLD_SAL
dbo insert ??? 100 ABC 5000 NULL NULL NULL
dbo update ??? 100 ABC 6000 100 ABC

5000
dbo delete ??? NULL NULL NULL 100 ABC
6000

CREATE TABLE EMP_AUDIT

(
UNAME VARCHAR(10),
OPERATION VARCHAR(10),
 OPTIME DATETIME,
NEW_ENO INT,
NEW_ENAME VARCHAR(10),
NEW_SAL MONEY,
OLD_ENO INT,
OLD_ENAME VARCHAR(10),
OLD_SAL MONEY
)

CREATE OR ALTER TRIGGER T6

ON EMP
AFTER INSERT,UPDATE,DELETE
AS
DECLARE @NEWENO INT,@NEWENAME VARCHAR(10),@NEWSAL MONEY
DECLARE @OLDENO INT,@OLDENAME VARCHAR(10),@OLDSAL MONEY
DECLARE @CNT1 INT,@CNT2 INT,@OP VARCHAR(10)
SELECT @CNT1 = COUNT(*) FROM INSERTED
SELECT @CNT2 = COUNT(*) FROM DELETED
SELECT @NEWENO=EMPNO,@NEWENAME=ENAME,@NEWSAL=SAL FROM
INSERTED
SELECT @OLDENO = EMPNO,@OLDENAME=ENAME,@OLDSAL=SAL FROM
DELETED
IF @CNT1 = 1 AND @CNT2 = 0
SET @OP='INSERT'
ELSE IF @CNT1=0 AND @CNT2=1
SET @OP='DELETE'
ELSE
SET @OP='UPDATE'
INSERT INTO EMP_AUDIT
VALUES(user_name(),@OP,getdate(),@NEWENO,@NEWENAME,@NEWSAL,
@OLDENO,@OLDENAME,@OLDSAL)

Testing :-

1 INSERT INTO EMP(EMPNO,ENAME,SAL) VALUES(100,'ABC',5000)

2 UPDATE EMP SET SAL=6000 WHERE EMPNO=100
3 DELETE FROM EMP WHERE EMPNO = 100

SELECT * FROM EMP_AUDIT

INSTEAD OF triggers :-
---------------------------------
 => if trigger is instead of then sql server executes the trigger instead of executing DML
i.e. DML is replaced with trigger.

Ex :-

EMP44
ENO ENAME DNO
1 A 10
2 B 10
3 C 10
4 D 10
5 E 10 => not alllowed

CREATE OR ALTER TRIGGER T7

ON EMP44
INSTEAD OF INSERT
AS
DECLARE @ENO INT,@NAME VARCHAR(10),@DNO INT , @CNT INT
SELECT @ENO = ENO ,@NAME=ENAME,@DNO = DNO FROM INSERTED
SELECT @CNT = COUNT(*) FROM EMP44 WHERE DNO = @DNO
IF @CNT >= 4
RAISERROR('max 4 emps per dept',16,1)
ELSE
INSERT INTO EMP44 VALUES(@ENO,@NAME,@DNO)

Testing :-
------------

insert into emp44 values(1,'A',10) insert into emp44 values(2,'B',10)

insert into emp44 values(3,'c',10) insert into emp44 values(4,'D',10)
insert into emp44 values(5,'E',10) => ERROR => max 4 emps per dept

AFTER :- INSTEAD OF :-
--------------- ---------------------
IF COND IF COND
BEGIN RAISERROR
ROLLBACK ELSE
RAISERROR DML
END
=> list of triggers created by user ?

TRIGGER NAME TABLE NAME

SYS.TRIGGERS SYS.TABLES
NAME PARENT_ID NAME OBJECT_ID
T5 814625945 EMP 814625945

SELECT TR.NAME AS TRGNAME,TB.NAME AS TABNAME

FROM SYS.TRIGGERS AS TR INNER JOIN SYS.TABLES AS TB
ON TR.PARENT_ID = TB.OBJECT_ID

Droping :-
--------------

DROP TRIGGER T5

========================================================================

22-MAR-25

Dynamic SQL :-
----------------------

=> sql commands generated at runtime are called dyanmic sql commands

1 DROP TABLE EMP (static sql)

2 DECLARE @tname VARCHAR(20)

SET @tname = 'EMP'
DROP TABLE @tname (dynamic sql command)

=> Dynamic sql is useful when we don't know tablenames and column names until runtime.
=> Dynamic sql commands are executed by using EXEC procedure

EXEC ('Dynamic sql command')

Ex :-

=> create a procedure to drop table from db ?

 CREATE OR ALTER PROCEDURE DROP_TABLE
@tname VARCHAR(30)
AS
EXEC ('DROP TABLE ' + @tname)

execution :-

EXECUTE DROP_TABLE 'EMP_RESIGN'

=> create a procedure to drop all tables ?

CREATE OR ALTER PROCEDURE DROP_ALL_TABLES

AS
DECLARE C1 CURSOR FOR SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
DECLARE @TNAME VARCHAR(30)
OPEN C1
FETCH NEXT FROM C1 INTO @TNAME
WHILE(@@FETCH_STATUS=0)
BEGIN
EXEC ('DROP TABLE ' + @TNAME)
FETCH NEXT FROM C1 INTO @TNAME
END
CLOSE C1
DEALLOCATE C1
========================================================================
====

24-mar-25

BACKUP & RESTORE :-

====================

=> backup is the way to protect db from different failures

=> backup means copying db (.mdf , .ldf) to backup file (.bak)
=> backup is useful when original db is damaged
=> recovering db from backup is called restore

command to take backup :-

------------------------------------
 BACKUP DATABASE <NAME> TO DISK = FILENAME

Ex :-

BACKUP DATABASE DB9AM TO DISK = 'D:\NARESH\DB9AM.BAK'

=> create a procedure to take backup of all databases ?

CREATE OR ALTER PROCEDURE backup_all_dbs

AS
DECLARE C1 CURSOR FOR SELECT name
FROM sys.databases
WHERE database_id > 4
DECLARE @dbname VARCHAR(20)
DECLARE @fname VARCHAR(100)
OPEN C1
FETCH NEXT FROM C1 INTO @dbname
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @fname = 'D:\NARESH\' + @dbname + '.bak'
BACKUP DATABASE @dbname TO DISK = @fname
FETCH NEXT FROM C1 INTO @dbname
END
CLOSE C1
DEALLOCATE C1

Execution :-

EXECUTE backup_all_dbs

RESTORE :- ===========

RESTORE DATABASE DB9AM FROM DISK = 'D:\NARESH\DB9AM.BAK'

===============================================================

Normalization :-
=============

=> Normalization is the process of decomposing tables with rudundency into

number of well structured tables.
=> Normalization process is set of rules and each rule is called one normal form.

=> There are six normal forms

1NF
2NF
3NF
BCNF (boyce-codd NF)
4NF
5NF

Example :-

BILL
BILLNO BDATE CCODE CNAME ADDR ICODE NAME QTY RATE VALUE
TBILL
1000 24/ 100 A HYD 1 ABC 2 100 200
200
2 XYZ
3
20

=> above table contains redundency , to reduce this redundency apply normalization
process

1NF :-
---------

=> a table said to be in 1NF if there are no multivalued attributes in it or all the
attributes in table are atomic (single).

BILL
BILLNO BDATE CCODE CNAME ADDR ICODE NAME QTY RATE VALUE
TBILL
S S S S S M M M M M S

=> In above table some fields are single valued and some fields are multi valueds,
so the table is not according to 1NF then decompose the table as follows

TABLE 1 :-

BILL
 BILLNO BDATE CCODE CNAME ADDR TBILL
-------------
1000
1001

TABLE 2 :-

BILL_ITEMS
BILLNO ICODE NAME QTY RATE VALUE
----------------------
1000 1 ABC 3 100
1000 2
1000 3
1001 1 ABC 2 100
1001 2

=> In the above table if ICODE is repeated then NAME,RATE are also repeated
so to reduce this redundency apply 2NF.

2NF :-
------------

=> a table said to be in 2NF

1 if it is in 1NF
2 if there are no partial dependencies in it

partial dependency :-
-----------------------------

R(A,B,C,D) A => pk

A ======> B,C,D (full dependency)

R(A,B,C,D) A,B => pk

A,B =====> C (full dependency)

B ======> D (partial dependency)

TABLE 1 :-

BILL
BILLNO BDATE CCODE CNAME ADDR TBILL
 -------------
1000
1001

=> above table saitisfies 2NF because no partial dependencies exists in the table

TABLE 2 :-

BILL_ITEMS
BILLNO ICODE NAME QTY RATE VALUE
----------------------

BILLNO,ICODE ======> QTY ,VALUE (full dependency)

ICODE =======> NAME,RATE (partial dependency)

=> above table contains partial dependency , so the table is not according to 2NF
then decompose the table as follows

TABLE 2 :-
-------------

ITEMS
ICODE NAME RATE
---------

TABLE 3 :-
--------------

BILL_ITEMS
BILLNO ICODE QTY VALUE
----------------------

3NF :-
----------

=> a table said to be in 3NF

1 if it is in 2NF
2 if there are no transitive dependencies in it

Transitive dependency :-
----------------------------------
 R(A,B,C,D) A => pk

A =====> B,C (full dependency)

C ======> D (transitive dependency)

TABLE 1 :-
-------------

BILL
BILLNO BDATE CCODE CNAME ADDR TBILL
-------------

BILLNO =======> BDATE , TBILL (full dependency)

CCODE =======> NAME,ADDR (transitive dependency)

=> above table contains transitive dependency , so the table is not according to
3NF then decompose the table as follows.

CUST
CCODE NAME ADDR
-----------

BILL
BILLNO BDATE TBILL CCODE( FK)
----------

TABLE 2 :-
-------------

ITEMS
ICODE NAME RATE
---------

ICODE =======> NAME,RATE (full dependency)

TABLE 3 :-
--------------
 BILL_ITEMS
BILLNO ICODE QTY VALUE
----------------------

BILLNO ,ICODE =========> QTY (full dependency)

QTY ==========> VALUE (transitive dependency)

note :-

=> derived attributes can be permanently removed from tables

for ex :- value,tbill

AFTER 3NF :-
-----------------

CUST
CCODE NAME ADDR
-----------

BILL
BILLNO BDATE CCODE( FK)
----------

ITEMS
ICODE NAME RATE
---------

BILL_ITEMS
BILLNO ICODE QTY
----------------------

===============================================================

what are temporary tables ?

=> Temporary tables stores data temporarly.

=> these tables exists upto the session and once session closes these tables
are automtically deleted.

=> used for storing intermediate results

Ex :-

method 1 :-

CREATE TABLE #TEMP

(
empno INT, ename
VARCHAR(10), sal
MONEY, rnk INT
)

inserting data into temporary table :-

-----------------------------------------------

INSERT INTO #TEMP

SELECT EMPNO,ENAME,SAL,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK
FROM EMP

Display temporary table :-

---------------------------------
SELECT * FROM #TEMP

Method 2 :-

SELECT EMPNO,ENAME,SAL,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK INTO #TEMP
FROM EMP

display top 5 employees ?

SELECT * FROM #TEMP WHERE RNK <= 5

Different ways for storing data temporarly ?

1 CTE
2 Temporary tables
3 cursor

=> difference between CTE & Temporary tables ?

=> scope of the CTE is upto the query and cannot be referenced in another query =>
scope the Temp table is upto the session
===============================================================