SQL SERVER

------------
30-NOV-21

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

=> a Database is a organized collection of interrelated data , for example

a univ db stores data related to students,courses,faculty etc and a bank
db stores data related to customers,transactions and loans etc.

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

1 OLTP DB (online transaction processing)

2 OLAP DB (online analytical processing)

=> OLTP db are used for storing day-to-day transactions and OLAP db are
used for analysis.

=> OLTP db are used to run business and OLAP db are used to analyze business

=> day-to-day operations on db includes

C create
R read
U update
D delete

DBMS :-
--------

=> DBMS stands for database management system , it is a software used to

create database and used to store,read,update and delete data.

=> DBMS is an interface between user and database.

Evolution of DBMS :-
--------------------

1960 fms (file management system)

1970 hdbms (hierarchical dbms)

ndbms (network dbms)

1980 rdbms (relational dbms)

1990 ordbms (object relational dbms)

01-dec-21

RDBMS :- (Relational Database Management System)

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

=> RDBMS concepts are introduced by E.F.CODD

=> E.F.CODD introduced 12 rules called codd rules
=> a db software that supports all 12 rules is called perfect rdbms software
=> accroding to E.F.CODD in db data must be organized in tables i.e. rows & cols
 CUSTOMERS
CUSTID NAME CITY AGE => columns/fields/attributes
100 SACHIN MUM 40
101 VIJAY HYD 30
102 RAHUL DEL 25 => row/record/tuple

DATABASE = COLLECTION OF TABLES

TABLE = COLLECTION OF ROWS & COLS
ROW = COLLECTION OF FIELD VALUES
COLUMN = COLLECTION OF VALUES ASSIGNED TO ONE FIELD

=> every table must contain primary key to uniquely identify the records

EX :- ACCNO,EMPID,CUSTID,AADHARNO,PANNO

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

1 easy to access and manipulate data

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

RDBMS softwares :-
------------------

SQL SERVER from microsoft

ORACLE from oracle corp
DB2 from IBM
MYSQL from oracle corp (open source db)
POSTGRESQL from oracle corp (open source db)

ORDBMS :- (Object Relational DBMS)

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

=> ORDBMS is the combination of RDBMS & OOPS

ORDBMS = RDBMS + OOPS (reusability)

=> RDBMS doesn't support reusability but ORDBMS supports reusability

ORDBMS softwares :-
--------------------

oracle
sql server
postgresql

summary :-

what is db ?
what is dbms ?
what is rdbms ?
what is ordbms ?

-------------------------------------------------------------------------------
02-DEC-21 SQL SERVER
-----------

=> SQL SERVER is a rdbms product from microsoft and used to

create and to manage database and used for db development and administration

Development Administration

creating tables installation of sql server

creating views creating database
creating sequences creating logins
creating synonyms db backup & restore
creating indexes db export & import
creating procedures db upgradation & migration
creating functions db mirroring & replication
creating triggers performance tuning
writing queries

versions of sql server :-

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

version year

SQL SERVER 1.1 1991

SQL SERVER 4.2 1993
SQL SERVER 6.0 1995
SQL SERVER 6.5 1996
SQL SERVER 7.0 1998
SQL SERVER 2000 2000
SQL SERVER 2005 2005
SQL SERVER 2008 2008
SQL SERVER 2012 2012
SQL SERVER 2014 2014
SQL SERVER 2016 2016
SQL SERVER 2017 2017
SQL SERVER 2019 2019

sql server 2016 :-

1 polybase
2 json
3 temporal table to save data changes.
4 dynamic data masking and row level security

sql server 2017 :-

1 identity cache
2 New String functions
3 Automatic Tuning

sql server 2019 :-

1 Read, write, and process big data from Transact-SQL

2 Easily combine and analyze high-value relational data with high-volume big data.
3 Query external data sources.
4 Store big data in HDFS managed by SQL Server.
5 Query data from multiple external data sources through the cluster.
CLIENT/SERVER ARCHITECTURE :-
-----------------------------

1 SERVER
2 CLIENT

SERVER :-
---------

=> server is a system where sql server software is installed and running.
=> inside the server sql server manages database.
=> inside the server sql server recieves requests from client and
process the requensts and sends response to client.

CLIENT :-
---------

=> using client system users

1 connects to server
2 submit the requests to server
3 recieves response from server

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

SSMS => SQL SERVER MANAGEMENT STUDIO

LINKS :-
---------

SQL SERVER 2019 :-

download (DEVELOPER EDITION)

https://www.microsoft.com/en-in/sql-server/sql-server-downloads

step by step installation

https://computingforgeeks.com/install-sql-server-developer-edition-on-windows-
server/

SSMS :-

download :-

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-
ssms?view=sql-server-ver15

How to connect to sql server :-

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

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

SERVER TYPE :- DATABASE ENGINE

SERVER NAME :- LAPTOP-ODHIIKLC
AUTHENTICATION :- SQL SERVER AUTHENTICATION
LOGIN :- SA (SYSTEM ADMIN)
PASSWORD :- 123
06-dec-21

CREATING DATABASE IN SQL SERVER :-

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

=> In object Explorer select Databases => New Database

Enter Database name :- DB6PM

=> click OK

=> a DB is created with following two files

1 DATA FILE (.MDF) (master data file)

2 LOG FILE (.LDF) (log data file)

=> Data File stores data and log file stores operations

NAME TYPE SIZE AUTO GROWTH PATH

DB6PM DATA 8MB 64MB C:\Program Files\Microsoft SQL Server\
MSSQL15.MSSQLSERVER\MSSQL\DATA\
DB6PM_LOG LOG 8MB 64MB C:\Program Files\Microsoft SQL Server\
MSSQL15.MSSQLSERVER\MSSQL\DATA\

command to create new database :-

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

=> open master database and execute the following command

system database
master => New Query

CREATE DATABASE DB10

Datatypes in SQL SERVER :-

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

=> a datatype specifies

1 type of the data allowed in a column

2 amount of memory allocated for column

DATATYPES

CHAR INTEGER FLOAT CURRENCY DATE

BINARY

ASCII UNICODE tinyint decimal(p,s) smallmoney date

binary
smallint money` time
varbinary
char nchar int datetime varbinary(max)
varchar nvarchar bigint
varchar(max) nvarchar(max)

char(size) :-
-------------
=> allows character data upto 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 recommended
for variable length fields and char is recommended for fixed length
fields

ex :- STATE_CODE CHAR(2)

AP
TS
MH

COUNTRY_CODE CHAR(3)

IND
USA

07-dec-21

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

=> allows character data upto 8000 chars

=> it is recommended for variable length char fields
=> in VARCHAR extra bytes are released

ex :- NAME VARCHAR(10)

SACHIN----
released

RAVI------
released

VARCHAR(max) :-
---------------

=> allows character data upto 2GB

ex :- TEXT VARCHAR(MAX)

NOTE :- char/varchar/varchar(max) allows ascii characters (256 chars)

that includes a-z,A-Z,0-9 & special chars.

ex :- PANNO CHAR(10)
VEHNO VARCHAR(10)
EMAIL VARCHAR(30)
PWD VARCHAR(10)

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

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

=> allows unicode characters (65536 chars) that includes all ASCII chars
and characters belongs to different languages.

Integer Types :-
-----------------

=> allows whole numbers i.e. numbers without decimal point.

TINYINT 1 BYTE 0 TO 255

SMALLINT 2 BYTES -32768 TO 32767
INT 4 BYTES -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)
BIGINT 8 BYTES -2^63 (-9,223,372,036,854,775,808) to 2^63-1
(9,223,372,036,854,775,807)

EX :- AGE TINYINT
EMPID SMALLINT
ACCNO INT
AADHARNO BIGINT

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

=> allows real numbers i.e. numbers with decimal part

p => precision => total no of digits allowed

s => scale => no of digits allowed after decimal

ex :- SALARY DECIMAL(7,2)

5000
5000.50
50000.50
500000.50 => NOT ACCEPTED

CURRENCY types :-
------------------

=> currency types are used for fields related to money

SMALLMONEY 4 BYTES −214478.3648 TO +214478.3647

MONEY 8 BYTES -922,337, 203, 685,477.5808 TO +922,337, 203,
685,477.5807

EX : SAL SMALLMONEY
BAL MONEY

DATE & TIME :-

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

=> sql server supports the following Date datatypes

DATE => allows only dates

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

=> default date format in sql server is yyyy-mm-dd

 => default time format is hh:mi:ss

ex :- DOB DATE

1998-10-05

LOGIN TIME

10:00:00

TXN_DT DATETIME

2021-12-07 11:00:00

Binary Types :-
---------------

=> binary types allows binary data that includes audio,video,images

BINARY
VARBINARY
VARBINARY(MAX)

binary :-
---------

=> allows binary data upto 8000 bytes

=> extra bytes are wasted

ex :- photo binary(1000)

varbinary :-
-------------

=> allows binary data upto 8000 bytes

=> extra bytes are released

ex :- photo varbinary(1000)

varbinary(max) :-
-----------------

=> allows binary data upto 2GB

ex :- photo varbinary(max)

08-dec-21

CREATING TABLE IN SQL SERVER :-

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

CREATE TABLE <tabname>

(
COLNAME DATATYPE(SIZE),
COLNAME DATATYPE(SIZE),
------------------------
)

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

1 tabname should start with alphabet

2 tabname should not contain spaces & special chars but allows _,#,$
3 tabname can be upto 128 chars
4 table can have upto 1024 columns
5 table can have unlimited rows

Example :-

create table with following structure ?

EMP
empid ename job sal age hiredate

CREATE TABLE emp

(
empid TINYINT,
ename VARCHAR(10),
job VARCHAR(10),
sal SMALLMONEY,
age TINYINT,
hiredate DATE
)

=> above command created table structure/definition/metadata that includes

columns,datatype and size.

SP_HELP :-
---------

=> command to see the structure of the data

SYN :- SP_HELP <tabname> (SP => stored procedure)

EX :- SP_HELP emp

column datatype size

empid tinyint 1
ename varchar 10
job varchar 10
sal smallmoney 4
age tinyint 1
hiredate date 3

INSERTNG DATA INTO TABLE :-

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

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

=> using INSERT command we can insert single row or multiple rows

inserting single row :-

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

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

ex :- INSERT INTO emp VALUES(100,'sachin','clerk',5000,35,'2021-12-01')

INSERT INTO emp VALUES(101,'vijay','analyst',8000,30,getdate())
inserting multiple rows :-
----------------------------

INSERT INTO emp VALUES(102,'bharat','manager',9000,28,'2020-05-15'),

(103,'sindhu','clerk',4000,20,getdate())

09-dec-21

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

=> a null means blank or empty

=> it is not equal to 0 or space
=> nulls can be inserted in two ways

method 1 :-

INSERT INTO emp VALUES(104,'ajay',NULL,NULL,30,GETDATE())

method 2 :-

INSERT INTO emp(empid,ename,age,hiredate) VALUES(105,'phani',28,'2019-10-05')

=> remaining two fields job,sal filled with nulls

Operators in SQL SERVER :-

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

1 Arithmetic Operators => + - * / %

2 Relational Operators => > >= < <= = <>
3 Logical Operators => AND OR NOT
4 Special Operators => BETWEEN,IN,LIKE,IS,ANY,ALL,EXISTS,PIVOT
6 Set Operators => UNION,UNION ALL,INTERSECT,EXCEPT

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

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

=> using SELECT command we can display all columns or specific columns
=> using SELECT command we can display all rows or specific rows

syn :- SELECT columns/* FROM tabname

* => all columns

=> display all the data from emp table ?

SELECT * FROM emp

=> display employee name and salaries ?

SELECT ename,sal FROM emp

=> display employee names and age ?

SELECT ename,age FROM emp

WHERE clause :-
 ---------------

=> used to get specific row/rows from table based on a condition

SELECT columns
FROM tabname
WHERE condition

condition :-
-----------

COLNAME OP VALUE

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

=> display employee details whose empid=103 ?

SELECT * FROM emp WHERE empid=103

=> display employee details whose name = vijay ?

SELECT * FROM emp WHERE ename='vijay'

=> display employees earning more than 5000 ?

SELECT * FROM emp WHERE sal>5000

=> display employees joined after 2020 ?

SELECT * FROM emp WHERE hiredate > 2020 => ERROR

SELECT * FROM emp WHERE hiredate > '2020-12-31'

=> display employees joined before 2020 ?

SELECT * FROM emp WHERE hiredate < '2020-01-01'

10-dec-21

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

=> mutliple 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
 => display employees working as clerk,manager ?

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

=> display employees working as clerk and earning more than 4000 ?

SELECT * FROM emp WHERE job='clerk' AND sal>4000

=> display employees whose empid=100,103,105 ?

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

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

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

=> display employees joined in 2020 year ?

SELECT * FROM emp

WHERE hiredate >= '2020-01-01' AND hiredate <= '2020-12-31'

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

=> use IN operator for list comparision

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

WHERE COLNAME IN (V1,V2,V3,--) (COL=V1 OR COL=V2 OR COL=V3)

=> display working as clerk,manager ?

SELECT * FROM emp WHERE job IN ('clerk','manager')

=> display employees whose empid=100,103,105 ?

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

=> display employees not working as clerk,manager ?

SELECT * FROM emp WHERE job NOT IN ('clerk','manager')

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

=> use BETWEEN operator for range comparision

WHERE COLNAME BETWEEN V1 AND V2 (COL>=V1 AND COL<=V2)

=> display employees earning between 5000 and 10000 ?

SELECT * FROM emp WHERE sal BETWEEN 5000 AND 10000

=> display employees joined in 2020 year ?

SELECT * FROM emp WHERE hiredate BETWEEN '2020-01-01' AND '2020-12-31'

=> display employees not joined in 2020 year ?

SELECT * FROM emp WHERE hiredate NOT BETWEEN '2020-01-01' AND '2020-12-31'
 scenario :-
-----------

PRODUCTS
prodid pname price category brand

=> display list of mobiles phones price between 10000 and 20000
and brand = redmi,realme,samsung ?

SELECT *
FROM products
WHERE category='mobiles'
AND
price BETWEEN 10000 AND 20000
AND
brand IN ('redmi','realme','samsung')

13-DEC-21

=> display employees working as clerk,manager and earning between 5000 and
10000 and joined in 2021 year ?

SELECT * FROM emp

WHERE job IN ('CLERK','MANAGER')
AND
sal BETWEEN 5000 AND 10000
AND
hiredate BETWEEN '2021-01-01' AND '2021-12-31'

scenario :-

STUDENT
sno sname s1 s2 s3
1 A 80 90 70
2 B 30 70 60

=> display list of students who are passed ?

SELECT *
FROM student
WHERE s1>=35 AND s2>=35 AND s3>=35

=> displayt list of students who are failed ?

SELECT *
FROM student
WHERE s1<35 OR s2<35 OR s3<35

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

=> use LIKE operator for pattern comparision

WHERE COLNAME LIKE 'Pattern'

=> pattern consists of alphabets,digits,wildcard chars

 wildcard chars :-
-----------------

% => 0 or many chars

_ => exactly 1 char

=> display employees name starts with 's' ?

SELECT * FROM emp WHERE ename LIKE 's%'

=> display employees name ends with 's' ?

SELECT * FROM emp WHERE ename LIKE '%s'

=> display employees name contains 's' ?

SELECT * FROM emp WHERE ename LIKE '%s%'

=> display employee where 'a' is 3rd char in their name ?

SELECT * FROM emp WHERE ename LIKE '__a%'

=> display employees where 'a' is the 3rd char from last ?

SELECT * FROM emp WHERE ename LIKE '%a__'

=> display employees joined in jan month ? (yyyy-mm-dd)

SELECT * FROM emp WHERE hiredate LIKE '%01%'

=> display employees joined 2020 year ?

SELECT * FROM emp WHERE hiredate LIKE '2020%'

=> display employees name start with 'a','j','s' ?

SELECT * FROM emp WHERE ename LIKE 'a%'

OR
ename LIKE 'j%'
OR
ename LIKE 's%'

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

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

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

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

=> use IS operator for NULL comparision

WHERE COLNAME IS NULL

WHERE COLNAME IS NOT NULL

=> display employees not earning commission ?

 SELECT * FROM emp WHERE comm = NULL

=> display employees earning commission ?

SELECT * FROM emp WHERE comm IS NOT NULL

summary :-
-----------

WHERE COLNAME IN (V1,V2,V3,--)

WHERE COLNAME BETWEEN V1 AND V2
WHERE COLNAME LIKE 'PATTERN'
WHERE COLNAME IS NULL

14-dec-21

ORDER BY clause :-
-----------------

=> ORDER BY clause is used to sort data based on one or more columns
either in ascending or in descending order.

SELECT columns
FROM tabname
[WHERE cond]
ORDER BY <col> [ASC/DESC]

=> default order is ASC

=> arrange employee list name wise asc order ?

SELECT *
FROM emp
ORDER BY ename ASC

=> arrange employee list sal wise desc order ?

SELECT *
FROM emp
ORDER BY sal DESC

=> arrange employee list hiredate wise asc order ?

SELECT *
FROM emp
ORDER BY hiredate ASC

=> arrange employee list dept wise asc and with in dept sal wise desc ?

SELECT *
FROM emp
ORDER BY deptno ASC,sal DESC

1 A 3000 20 4 D 5000 10
2 B 4000 10 2 B 4000 10
3 C 3000 30 ------> 5 E 6000 20
4 D 5000 10 1 A 3000 20
5 E 6000 20 6 F 4000 30
6 F 4000 30 3 C 3000 30
scenario :-
-----------

STUDENT
sno sname mat phy che
1 A 80 90 70
2 B 60 70 50
3 C 90 80 70
4 D 90 70 80

=> arrange student list avg wise desc ,mat desc,phy desc ?

SELECT *
FROM student
ORDER BY (mat+phy+che)/3 DESC,mat DESC,phy DESC

3 C 90 80 70
4 D 90 70 80
1 A 80 90 70
2 B 60 70 50

=> to display avg in the output

SELECT sno,sname,mat,phy,che,(mat+phy+che)/3 as avg

FROM student
ORDER BY (mat+phy+che)/3 DESC,mat DESC,phy DESC

SELECT sno,sname,mat,phy,che,(mat+phy+che)/3 as avg

FROM students
ORDER BY avg DESC,mat DESC,phy DESC

=> display employees working as clerk,manager and

arrange list sal wise desc order ?

SELECT empno,ename,job,sal
FROM emp
WHERE job IN ('CLERK','MANAGER')
ORDER BY sal DESC

15-DEC-21

DISTINCT clause :-
-------------------

=> used to eliminate duplicates from the select statement output

SELECT DISTINCT col1,col2,----

FROM tabname
WHERE cond

Ex :- SELECT DISTINCT job FROM emp

ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
 SELECT DISTINCT deptno FROM emp

10
20
30

TOP clause :-
--------------

=> used select Top N rows from table.

Ex :- display first 5 rows from emp table ?

SELECT TOP 5 * FROM emp

display top 5 highest paid employees ?

SELECT TOP 5 *
FROM emp
ORDER BY sal DESC

display top 5 max salaries ?

SELECT DISTINCT TOP 5 sal

FROM emp
ORDER BY sal DESC

DML commands :- (Data Manipulation Languague)

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

INSERT
UPDATE
DELETE

=> DML commands acts on table data.

=> in SQL SERVER by default all operations are auto committed (saved).
=> to stop auto commit execute the following command

SET IMPLICIT_TRANSACTIONS ON

=> to save operation then execute COMMIT and to cancel operation execute ROLLBACK

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

=> command used to modify the table data

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

syn :- UPDATE <tabname>

SET colname = value, colname = value,------
[WHERE condition]

ex :- update all employees comm with 500 ?

UPDATE emp SET comm=500

update employee comm with 500 whose empno=7369 ?

 UPDATE emp SET comm=500 WHERE empno=7369

update employee comm with 500 whose comm = null ?

UPDATE emp SET comm=500 WHERE comm IS NULL

update employee comm with null whose empno=7369 ?

UPDATE emp SET comm = NULL WHERE empno=7369

NULL assignment =
NULL comparision IS

increment sal by 20% and comm by 10% those working as salesman

and joined in 1981 year ?

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

16-DEC-21

DELETE command :-
------------------

=> command used to delete row/rows from table

=> we can delete all rows or specific rows

syn :- DELETE FROM tabname [WHERE cond]

=> delete all rows from emp table ?

DELETE FROM emp

=> delete employee row whose empno=7844 ?

DELETE FROM emp WHERE empno=7844

=> delete employees joined in 2020 year ?

DELETE FROM emp WHERE hiredate LIKE '2020%'

DDL commands :- (Data Definition Language)

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

CREATE
ALTER
DROP
TRUNCATE

=> all DDL commands acts on table structure

ALTER command :-
-----------------

=> command used to modify the table structure

=> using ALTER command we can

1 add columns
2 drop columns
3 modify a column

Adding column :-
-----------------

ALTER TABLE <tabname>

ADD COLNAME DATATYPE(SIZE),COLNAME DATATYPE(SIZE),--------

=> add column dob to emp table ?

ALTER TABLE emp

ADD dob DATE

after adding by default the column is filled with NULL , to insert data
into the new column use UPDATE command.

UPDATE emp SET dob = '2000-05-10' WHERE empno=7369

Droping Column :-
------------------

ALTER TABLE <tabname>

DROP COLUMN colname

=> drop column dob from emp table ?

ALTER TABLE emp

DROP COLUMN dob

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

1 changing size
2 changing datatype

ALTER TABLE <tabname>

ALTER COLUMN colname DATATYPE(size)

=> increase size of ename to 20 ?

ALTER TABLE emp

ALTER COLUMN ename VARCHAR(20)

=> change the datatype of sal to money ?

ALTER TABLE emp

ALTER COLUMN sal MONEY

ALTER TABLE emp

ALTER COLUMN empno TINYINT => ERROR

DROP command :-
----------------

=> used to drop table from database.

 => drops table structure with data.

syn :- DROP TABLE <tabname>

ex :- DROP TABLE emp

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

=> deletes all the data from table but keeps structure
=> will empty the table.
=> releases memory allocated for table.

syn :- TRUNCATE TABLE <tabname>

ex :- TRUNCATE TABLE emp

=> when truncate command is executed then sql server goes to memory and
releases all the pages allocated for table and when pages are released
then data stored in the pages are also deleted.

DROP VS DELETE VS TRUNCATE :-

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

DROP DELETE TRUNCATE

1 DDL command DML command DDL command

2 drops structure deletes only data deletes data

with data but not structure but not structure

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

DELETE TRUNCATE

1 DML command DDL command

2 can delete all rows can delete only all rows

or specific rows but cannot delete specific row

3 where cond can be where cond cannot be used with truncate

used with delete

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 change tablename or column name

SP_RENAME 'OLD-TABNAME','NEW-TABNAME'
 => rename table emp to employees ?

SP_RENAME 'EMP','EMPLOYEES'

=> rename column COMM to BONUS in employees table ?

SP_RENAME 'EMPLOYEES.COMM','BONUS'

IDENTITY :-
----------

=> identity is used to generate sequence numbers

=> used to auto increment column values
=> used for integer columns

syntax :- IDENTITY(SEED,INCR)

SEED => start

optional
default 1

INCR => increment

optional
default 1

Example :-

CREATE TABLE cust

(
cid 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')
INSERT INTO cust(cname) VALUES('E')

SELECT * FROM cust

CID CNAME
100 A
101 B
102 C
103 D
104 E

DELETE VS TRUNCATE :-

DELETE TRUNCATE
-------- ----------

SELECT * FROM cust SELECT * FROM cust

CID CNAME CID CNAME

100 A 100 A
101 B 101 B
102 C 102 C
 103 D 103 D
104 E 104 E
DELETE FROM cust TRUNCATE TABLE cust
105 K 100 X

how to reset identity manually :-

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

DBCC CHECKIDENT(tablename,reseed,value)

DBCC => Database Consistency Check

DBCC CHECKIDENT('CUST',RESEED,99)

18-dec-21

Built-in Functions in SQL SERVER :-

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

=> a function accepts some input performs some calculation and

returns one value.

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

1 date
2 string
3 mathematical
4 conversion
5 special
6 analytical
7 aggregate

Date functions :-
-----------------

1 GETDATE() :- returns current date & time

SELECT GETDATE() => 2021-04-24 08:15:27.853

2 DATEPART() :- used to extract part of the date

syn :- DATEPART(interval,date)

DATEPART(yy,GETDATE()) => 2021

DATEPART(mm,GETDATE()) => 04
DATEPART(dd,GETDATE()) => 24
DATEPART(dw,GETDATE()) => 07 (dw => day of the week)
01 SUNDAY
02 MONDAY

07 SATURDAY

DATEPART(q,GETDATE()) => 02 (Q => Quarter)

01 JAN-MAR
02 APR-JUN
03 JUL-SEP
04 OCT-DEC
 DATEPART(hh,GETDATE()) => 8
DATEPART(mi,GETDATE()) => 22
DATEPART(ss,GETDATE()) => 20

=> display employees joined in 1980,1983,1985 ?

SELECT * FROM emp

WHERE DATEPART(yy,hiredate) IN (1980,1983,1985)

=> display employees joined in jan,apr,dec months ?

SELECT * FROM emp

WHERE DATEPART(mm,hiredate) IN (01,04,12)

=> display employees joined on sunday ?

SELECT * FROM emp

WHERE DATEPART(dw,hiredate)=1

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

SELECT * FROM emp

WHERE DATEPART(yy,hiredate)=1981
AND
DATEPART(q,hiredate)=2

DATENAME() :-

=> similar to datepart used to extract part of the date

MM DW

DATEPART 04 07

DATENAME APRIL SATURDAY

=> write a query to display on which day india got independence ?

SELECT DATENAME(dw,'1947-08-15')

=> write a query to print smith joined on wednesday

allen joined on friday ?

SELECT ename + ' joined on ' + datename(dw,hiredate)

FROM emp

21-dec-21

DATEDIFF() :-

=> used to find difference between two dates

DATEDIFF(interval,start date,end date)

SELECT DATEDIFF(yy,'2020-12-21',GETDATE()) => 1

SELECT DATEDIFF(mm,'2020-12-21',GETDATE()) => 12
SELECT DATEDIFF(dd,'2020-12-21',GETDATE()) => 365
 => display ENAME EXPERIENCE ?

SELECT ENAME,DATEDIFF(yy,hiredate,GETDATE()) as experience

FROM emp

=> display ENAME EXPERIENCE ?

M years N months

example experience = 40 months = 3 years 4 months

years = months/12 = 40/12 = 3

months = months%12 = 40%12 = 4

SELECT ename,
DATEDIFF(mm,hiredate,GETDATE())/12 as years,
DATEDIFF(mm,hiredate,GETDATE())%12 as months
FROM emp

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

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

DATEADD(interval,int,DATE)

SELECT DATEADD(yy,1,GETDATE()) => 2022-12-21

SELECT DATEADD(mm,1,GETDATE()) => 2022-01-21
SELECT DATEADD(dd,10,GETDATE()) => 2021-12-31
SELECT DATEADD(dd,-10,GETDATE()) => 2021-12-11

scenario :-

GOLD_RATES
DATEID RATE
2015-01-01
2015-01-02

2021-12-21 ?

=> display todays gold rate ?

SELECT RATE FROM GOLD_RATES WHERE DATEID = GETDATE()

=> display yesterday's gold rate ?

SELECT RATE FROM GOLD_RATES WHERE DATEID = DATEADD(DD,-1,GETDATE())

=> display last month same day gold rate ?

SELECT RATE FROM GOLD_RATES WHERE DATEID = DATEADD(MM,-1,GETDATE())

=> display last year same day gold rate ?

SELECT RATE FROM GOLD_RATES WHERE DATEID = DATEADD(YY,-1,GETDATE())

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

=> returns last day of the month

 EOMONTH(date,int)

SELECT EOMONTH(GETDATE(),0) => 2021-12-31

SELECT EOMONTH(GETDATE(),1) => 2022-01-31
SELECT EOMONTH(GETDATE(),-1) => 2021-11-30

Questions :-

1 display next month 1st day ?

2 display current month 1st day ?
3 display 1st day of the next year ?
4 display 1st day of the current year ?

STRING functions :-
--------------------

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

=> converts string to uppercase

UPPER(string/colname)

SELECT UPPER('hello') => HELLO

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

=> converts string to lowercase

LOWER(string/colname)

SELECT LOWER('HELLO') => hello

=> display EMPNO ENAME SAL ? display names in lowercase ?

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

=> convert names to lowercase in table ?

UPDATE emp SET ename=LOWER(ename)

22-dec-21

LEN() :- returns string length i.e. no of chars

--------
LEN(string)

SELECT LEN('HELLO') => 5

SELECT LEN('HELLO WELCOME') => 13

=> display employees name contains 5 chars ?

SELECT * FROM emp WHERE ename LIKE '_____'

SELECT * FROM emp WHERE LEN(ename)=5

LEFT() :- used to extract characters from left side

-----------
LEFT(string,len)

SELECT LEFT('hello welcome',5) => hello

=> display employees name starts with 's' ?

SELECT * FROM emp WHERE ename LIKE 's%'

SELECT * FROM emp WHERE LEFT(ename,1)='s'

RIGHT() :- used to extract characters from right side

-----------
RIGHT(string,len)

SELECT RIGHT('hello welcome',7) => welcome

=> display employees name ends with 's' ?

SELECT * FROM emp WHERE ename LIKE '%s'

SELECT * FROM emp WHERE RIGHT(ename,1)='s'

=> display employees name starts and ends with same char ?

SELECT * FROM emp WHERE ename LIKE 'a%a'

OR
ename LIKE 'b%b'
OR
ename LIKE 'c%c'

SELECT * FROM emp WHERE LEFT(ename,1) = RIGHT(ename,1)

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

=> generate emailids for employees ?

empno ename emailid

7369 smith smi736@microsoft.com
7499 allen all749@microsoft.com

SELECT empno,ename,
as emailid
FROM emp

=> above query displays emailids but to store emailids in db then

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)+'@microsoft.com'

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

=> used to repeat string for given no of times

REPLICATE(str,no of times)

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

=> display ENAME,SAL ?

****

SELECT ename,REPLICATE('*',LEN(sal)) as sal FROM emp

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

ACCOUNTS
ACCNO ACTYPE BAL
12345678936 S 10000

your a/c no XXXX8936 debited ------

REPLICATE('X',4) + RIGHT(ACCNO,4)

1234 5678 9123 4567

12XXXX4567

23-dec-21

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

=> used to replace one string with another string.

REPLACE(str1,str2,str3)

SELECT REPLACE('hello','ell','abc') => habco

SELECT REPLACE('hello','l','abc') => heabcabco
SELECT REPLACE('hello','ell','') => ho
SELECT REPLACE('hello','elo','abc') => hello

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

=> used to translate one char to another char.

TRANSLATE(str1,str2,str3)

SELECT TRANSLATE('hello','elo','abc') => habbc

e => a
l => b
o => c

=> TRANSLATE function can be used to encrypt data i.e. changing plain text
to cipher text.
 SELECT empno,ename,
TRANSLATE(sal,'0123456789.','$Kp*H@b&#%^') as sal
FROM emp

jones 2975.00 p%&@^$$

Assignment :-
------------

remove all special characters from '%&he*#ll%o@$' ?

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

=> used to extract part of the string starting from specific position.

SUBSTRING(string,start,length)

SELECT SUBSTRING('hello welcome',7,4) => welc

SELECT SUBSTRING('hello welcome',10,4) => come

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

=> returns position of char in a string

CHARINDEX(char,string,[start])

SELECT CHARINDEX('o','hello welcome') => 5

SELECT CHARINDEX('k','hello welcome') => 0
SELECT CHARINDEX('o','hello welcome',6) => 11

Assignment :-

CUST
CID NAME
10 sachin tendulkar
11 virat kohli

display CID FNAME LNAME ?

Mathematical Functions :-
-------------------------

1 ABS() :- returns absolute value

ABS(number)

SELECT ABS(-10) => 10

2 POWER() :- returns power

POWER(num1,num2)

SELECT POWER(3,2) => 9

3 SQRT() :- returns square root

 SQRT(number)

SELECT SQRT(16) => 4

4 SQUARE() :- returns square of a number

SQUARE(number)

SELECT SQUARE(5) => 25

5 ROUND() :- used to round number to integer or to decimal places based on avg

ROUND(number,decimal places)

SELECT ROUND(38.4785,0) => 38

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

number < avg => rounded to lowest

number >= avg => rounded to highest

SELECT ROUND(38.4785,2) => 38.48

SELECT ROUND(38.4735,2) => 38.47

SELECT ROUND(386,-2) => 400

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

SELECT ROUND(386,-1) => 390

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

SELECT ROUND(386,-3) => 0

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

24-DEC-21

CEILING() :- rounds number always to highest

SELECT CEILING(3.1) => 4

FLOOR() :- rounds number always to lowest

SELECT FLOOR(3.9) => 3

conversion functions :-
-----------------------

=> these functions are used to convert one type to another type

1 CAST
2 CONVERT

CAST :-
--------

CAST(source-expr AS target-type)
 SELECT CAST(10.5 AS INT) => 10

=> display smith earns 800

allen earns 1600 ?

'a' + 'b' => ab

SELECT ename + ' earns ' + sal FROM emp => ERROR

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

=> display smith joined on 2020-12-17 as clerk ?

SELECT ename + ' joined on ' + CAST(hiredate as VARCHAR) + ' as '+ job
FROM emp

CONVERT() :-
-------------

CONVERT(target-type,source-expr)

SELECT CONVERT(INT,10.5) => 10

=> diff b/w CAST & CONVERT functions ?

1 using CONVERT function we can display dates in different formats

which is not possible using cast function

2 using CONVERT function we can display money in different formats

which is not possible using cast function

Displying dates in different formats :-

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

CONVERT(varchar,date,style-number)

Without century With century (yyyy) Standard Input/Output (3)

1 101 U.S. 1 = mm/dd/yy
101 = mm/dd/yyyy
2 102 ANSI 2 = yy.mm.dd
102 = yyyy.mm.dd
3 103 British/French 3 = dd/mm/yy
103 = dd/mm/yyyy
4 104 German 4 = dd.mm.yy
104 = dd.mm.yyyy
5 105 Italian 5 = dd-mm-yy
105 = dd-mm-yyyy
6 106 - 6 = dd mon yy
106 = dd mon yyyy
7 107 7 = Mon dd, yy
107 = Mon dd, yyyy
8 108 - hh:mi:ss
9 109 Default + milliseconds mon dd yyyy
hh:mi:ss:mmmAM (or PM)
10 110 USA 10 = mm-dd-yy
110 = mm-dd-yyyy
11 111 JAPAN 11 = yy/mm/dd
111 = yyyy/mm/dd
12 112 ISO 12 = yymmdd
 112 = yyyymmdd
13 113 Europe default + milliseconds dd mon
yyyy hh:mi:ss:mmm (24h)
14 114 hh:mi:ss:mmm (24h)
20 120 ODBC canonical yyyy-mm-dd hh:mi:ss (24h)
21 121 ODBC canonical default for time, date, datetime2,
and datetimeoffset yyyy-mm-dd hh:mi:ss.mmm (24h)
22 U.S. mm/dd/yy hh:mi:ss AM (or PM)
23 ISO8601 yyyy-mm-dd
126 ISO8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces)

127 ISO8601 yyyy-MM-ddThh:mm:ss.fffZ (no spaces)

130 Hijri dd mon yyyy hh:mi:ss:mmmAM

131 Hijri dd/mm/yyyy hh:mi:ss:mmmAM

display ENAME HIREDATE ? display hiredate in dd.mm.yyyy ?

SELECT ename,
CONVERT(varchar,hiredate,104) as hiredate
FROM emp

MONEY AND SMALLMONEY STYLES :-

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

CONVERT(varchar,expr,style-number)

0 No commas every three digits to the left of the decimal point, and two
digits to the right of the decimal point
1 Commas every three digits to the left of the decimal point, and two digits
to the right of the decimal point
2 No commas every three digits to the left of the decimal point, and four
digits to the right of the decimal point

display EMPNO ENAME SAL ? display salaries with thousand seperator ?

SELECT empno,ename,CONVERT(varchar,sal,1) as sal FROM emp

special functions :-
--------------------

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

=> used to convert null values

ISNULL(arg1,arg2)

if arg1=null returns arg2

if arg1<>null returns arg1 only

SELECT ISNULL(100,200) => 100

SELECT ISNULL(NULL,200) => 200
 => display ENAME SAL COMM TOTSAL ?

TOTSAL = SAL + COMM

SELECT ename,sal,comm,sal+comm as totsal FROM emp

smith 800.00 NULL NULL

allen 1600.00 300.00 1900.00

SELECT ename,sal,comm,sal+ISNULL(comm,0) as totsal FROM emp

smith 800.00 NULL 800.00

allen 1600.00 300.00 1900.00

27-dec-21

Analytical Functions :-
-----------------------

RANK & DENSE_RANK :-

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

=> both functions are used to calculate ranks

=> ranking is based on some expression/column
=> for rank functions input must be sorted

syn :- RANK() OVER (ORDER BY colname ASC/DESC)

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

=> 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

function ranks will be always in sequence.

SAL RANK DENSE_RNK

5000 1 1
4000 2 2
3000 3 3
3000 3 3
3000 3 3
2000 6 4
2000 6 4
1000 8 5

=> display ranks of the employees based on sal ? if salaries are same then
 ranking should be based on experience ?

SELECT empno,ename,hiredate,sal,
DENSE_RANK() OVER (ORDER BY sal DESC,hiredate ASC) as rnk
FROM emp

7839 king 2020-11-17 5000.00 1

7902 ford 2020-12-03 3000.00 2
7788 scott 2020-12-09 3000.00 3
7566 jones 2021-04-02 2975.00 4
7698 blake 2021-05-01 2850.00 5

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

=> used to find ranks with in group for example to find ranks with in dept
first we need to divide the table dept wise using PARTITION BY clause
and apply dense_rank function on each partition instead of applying it on
whole table.

SELECT empno,ename,sal,deptno,
DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal DESC) as rnk
FROM emp

7839 king 5000.00 10 1

7782 clark 2450.00 10 2
7934 miller 1300.00 10 3

7902 ford 3000.00 20 1

7788 scott 3000.00 20 1
7566 jones 2975.00 20 2
7876 adams 1100.00 20 3
7369 smith 800.00 20 4

7698 blake 2850.00 30 1

7499 allen 1600.00 30 2
7844 turner 1500.00 30 3
7521 ward 1250.00 30 4
7654 martin 1250.00 30 4
7900 james 950.00 30 5

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

=> row_number returns record numbers

=> row_number is also based on some column
=> for row_number also input must be sorted

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

7369 smith 800.00 1

7499 allen 1600.00 2
7521 ward 1250.00 3
7566 jones 2975.00 4
7654 martin 1250.00 5
7698 blake 2850.00 6
7782 clark 2450.00 7
 7788 scott 3000.00 8
7839 king 5000.00 9
7844 turner 1500.00 10
7876 adams 1100.00 11
7900 james 950.00 12
7902 ford 3000.00 13
7934 miller 1300.00 14

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

=> all these functions process group of rows and returns one value

MAX() :- returns maximum value

--------

MAX(arg)

SELECT MAX(sal) FROM emp => 5000

SELECT MAX(hiredate) FROM emp => 2021-09-28

MIN() :- returns minimum value

--------

MIN(arg)

SELECT MIN(sal) FROM emp => 800

SUM() :- returns total

--------

SELECT SUM(sal) FROM emp => 29025

=> round total sal to hundreds and display with thousand seperator ?

SELECT CONVERT(VARCHAR,ROUND(SUM(sal),-2),1)
FROM emp => 29,000

29000---------29050-----------29100

28-dec-21

AVG() :- returns average value

AVG(arg)

SELECT AVG(sal) FROM emp => 2073.2142

round avg(sal) to highest ?

SELECT CEILING(AVG(sal)) FROM emp => 2074

NOTE :- SUM,AVG functions cannot be applied on date & char columns

COUNT() :- returns no of values present in a column

COUNT(expr)
 SELECT COUNT(empno) FROM emp => 14

SELECT COUNT(comm) FROM emp => 4 (nulls are not counted)

SELECT COUNT(deptno) FROM emp => 14

SELECT COUNT(DISTINCT deptno) FROM emp => 3

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

=> returns no of rows in a table

SELECT COUNT(*) FROM emp => 14

Difference between COUNT & COUNT(*) ?

COUNT function ignores nulls but COUNT(*) includes nulls

T1
F1
10
NULL
20
NULL
30

COUNT(F1) = 3
COUNT(*) = 5

=> count no employees joined in 2021 year ?

SELECT COUNT(*) FROM emp WHERE DATEPART(yy,hiredate)=2021

=> count no of employees joined on sunday ?

SELECT COUNT(*) FROM emp WHERE DATENAME(dw,hiredate)='sunday'

summary :-

date :- getdate,datepart,datename,datediff,dateadd,eomonth

string :- upper,lower,len,left,right,substring,charindex,replicate,
replate,translate

math :- abs,power,sqrt,square,round,ceiling,floor

convesion :- cast,convert

special :- ISNULL

analytical :- RANK,DENSE_RANK,ROW_NUMBER

aggregate :- MAX,MIN,SUM,AVG,COUNT,COUNT(*)

-----------------------------------------------------------------------------
 GROUP BY clause :-
------------------

=> GROUP BY clause is used to group rows based on one or more columns to
calculate min,max,sum,avg,count for each group.

EMP
empno ename sal deptno
1 A 5000 10
2 B 4000 20 10 8000
3 C 6000 30-------GROUP BY-------> 20 6000
4 D 2000 20 30 10000
5 E 3000 10
6 F 4000 30

detailed data summarized data

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

syntax :-

SELECT columns
FROM tabname
[WHERE condition]
GROUP BY colname
[HAVING condition]
[ORDER BY colname ASC/DESC]

Execution :-
------------

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

=> display dept wise total salaries ?

SELECT deptno,SUM(sal) as totsal

FROM emp
GROUP BY deptno

FROM emp :-

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

GROUP BY deptno :-

10
 1 A 5000
5 E 3000

20
2 B 4000
4 D 2000

30
3 C 6000
6 F 4000

SELECT deptno,SUM(sal) as totsal :-

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

10 8000
20 6000
30 10000

29-dec-21

=> display job wise no of employees ?

SELECT job,COUNT(*) as cnt

FROM emp
GROUP BY job

=> display year wise no of employees joined ?

SELECT DATEPART(yy,hiredate) as year,COUNT(*) as cnt

FROM emp
GROUP BY DATEPART(yy,hiredate)

=> display day of the week wise no of employees joined ?

SELECT DATENAME(dw,hiredate) as day,COUNT(*) as cnt

FROM emp
GROUP BY DATENAME(dw,hiredate)

=> display the dept in which more than 3 employees working ?

SELECT deptno,COUNT(*) as cnt

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

NOTE :- 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 by using HAVING clause.

SELECT deptno,COUNT(*) as cnt

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

=> display job wise no of employees where job=clerk,manager ?

SELECT job,COUNT(*)
FROM emp
WHERE job IN ('CLERK','MANAGER')
 GROUP BY job

WHERE VS HAVING :-
------------------

WHERE HAVING

1 selects specific rows selects specific groups

2 conditions applied before conditions applied after

group by group by

3 grouping is not required to grouping is required to

apply where conditions apply having conditions

4 use where clause if cond doesn't use having clause if

contain aggregate function condition contains aggregate
function

=> display dept wise and with in dept job wise total salary ?

SELECT deptno,job,SUM(sal) as totsal

FROM emp
GROUP BY deptno,job
ORDER BY deptno ASC

10 CLERK 1300
MANAGER 2450
PRESIDENT 5000

20 ANALYST 6000
CLERK 1900
MANAGER 2975

30 CLERK 950
MANAGER 2860
SALESMAN 5600

ROLLUP & CUBE :-

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

=> both functions are used to calculate subtotals and grand total

syn :- GROUP BY ROLLUP(COL1,COL2,---)

GROUP BY CUBE(COL1,COL2,----)

ROLLUP :-
--------

=> rollup displays subtotals for each group and also displays grand total

SELECT deptno,job,SUM(sal) as totsal

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

NULL NULL 29025.00 => grand total

10 CLERK 1300.00
 10 MANAGER 2450.00
10 PRESIDENT 5000.00
10 NULL 8750.00 => dept subtotal
20 ANALYST 6000.00
20 CLERK 1900.00
20 MANAGER 2975.00
20 NULL 10875.00 => dept subtotal
30 CLERK 950.00
30 MANAGER 2850.00
30 SALESMAN 5600.00
30 NULL 9400.00 => dept subtotal

30-dec-21

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,job ASC

NULL NULL 29025.00 => GRAND TOTAL

NULL ANALYST 6000.00 => JOB SUBTOTAL
NULL CLERK 4150.00 => JOB SUBTOTAL
NULL MANAGER 8275.00 => JOB SUBTOTAL
NULL PRESIDENT 5000.00 => JOB SUBTOTAL
NULL SALESMAN 5600.00 => JOB SUBTOTAL
10 NULL 8750.00 => DEPT SUBTOTAL
10 CLERK 1300.00
10 MANAGER 2450.00
10 PRESIDENT 5000.00
20 NULL 10875.00 => DEPT SUBTOTAL
20 ANALYST 6000.00
20 CLERK 1900.00
20 MANAGER 2975.00
30 NULL 9400.00 => DEPT SUBTOTAL
30 CLERK 950.00
30 MANAGER 2850.00
30 SALESMAN 5600.00

Assignment :-
-------------

PERSONS
AADHARNO NAME GENDER AGE ADDR CITY STATE

1 display gender wise population ?

2 display state wise population ?
3 display state wise and with state gender wise population ?
4 display age group wise population ?
5 display state wise and with in state gender wise population and also
display state wise and gender wise subtotals ?

SALES
DATEID PRODID CUSTID QTY AMOUNT
2021-12-30 100 10 1 1000

=> display year wise and with in year quarter wise total sales amount and
display year wise subtotals ?

summary :-
----------

1 importance of group by
2 writing queries using group by clause
3 where vs having
4 rollup & cube
------------------------------------------------------------------------------

CASE statement :-
------------------

=> used to implement if-then-else

=> similar to switch case
=> using case statement we can return values based on the conditions
=> case statements are 2 types

1 simple case
2 searched case

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

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

CASE expr/colname
WHEN value1 THEN return expr1
WHEN value2 THEN return expr2
------------------
ELSE return expr
END

=> display ENAME JOB ?

if job=CLERK display WORKER

job=MANAGER display BOSS
job=PRESIDENT display BIG BOSS
else EMPLOYEE

SELECT empno,ename,
CASE job
WHEN 'CLERK' THEN 'WORKER'
WHEN 'MANAGER' THEN 'BOSS'
WHEN 'PRESIDENT' THEN 'BIG BOSS'
ELSE 'EMPLOYEE'
END as job
FROM emp

=> increment employee salaries as follows ?

if deptno=10 incr sal by 10%

20 15%
30 20%
 others 5%

UPDATE emp
SET sal = CASE deptno
WHEN 10 THEN sal+(sal*0.1)
WHEN 20 THEN sal+(sal*0.15)
WHEN 30 THEN sal+(sal*0.2)
ELSE sal+(sal*0.05)
END

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

=> display ENAME SAL SALRANGE ?

if sal>3000 display Hisal

sal<3000 display Losal
sal=3000 display Avgsal

SELECT ename,sal,
CASE
WHEN sal>3000 THEN 'Hisal'
WHEN sal<3000 THEN 'Losal'
ELSE 'Avgsal'
END as salrange
FROM emp

31-dec-21

Integrity Constraints :-
-------------------------

=> Integrity Constraints are rules to maintain Data Quality

=> Integrity Constraints are used to prevent users from entering invalid data
=> Integrity Constraints are used to enforce rules like min bal must be 1000
=> Different Integrity Constraints in SQL SERVER

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

=> 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 level.

CREATE TABLE <tabname>

(
colname datatype(size) CONSTRAINT,
colname datatype(size),
-------------------,
-------------------
)

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

=> NOT NULL constraint doesn't accept null values

=> a column declared with NOT NULL is called mandatory column

example :-

CREATE TABLE emp11

(
empno int,
ename varchar(10) NOT NULL
)

INSERT INTO emp11 VALUES(100,'A')

INSERT INTO emp11 VALUES(101,NULL) => ERROR

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

=> unique constraint doesn't accept duplicates

example :-

CREATE TABLE cust

(
custid int,
cname varchar(10),
emailid varchar(30) UNIQUE
)

insert into cust values(100,'A','abc@gmail.com')

insert into cust values(101,'B','abc@gmail.com') => ERROR
insert into cust values(102,'C',NULL)
insert into cust values(103,'D',NULL) => ERROR

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

=> PRIMARY KEY constraint doesn't allow duplicates and nulls

=> PRIMARY KEY is the combination of unique & not null

PRIMARY KEY = UNIQUE + NOT NULL

=> in rdbms in tables one column must be there to uniquely identify the
records and that column must be declared with primary key.
 Example :-

CREATE TABLE emp12

(
empid int PRIMARY KEY,
ename varchar(10)
)

INSERT INTO emp12 VALUES(100,'A')

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

=> only one primary key is allowed per table , if we want two primary keys
then declare one column with primary key and another column with
unique & not null.

CREATE TABLE cust

(
custid int PRIMARY KEY,
cname varchar(10),
caddr varchar(50),
aadharno bigint UNIQUE NOT NULL,
panno char(10) UNIQUE NOT NULL
)

03-jan-22

CHECK constraint :-
-------------------

=> use check constraint when rule based on condition

syn :- CHECK(condition)

example 1 :- sal must be min 3000

CREATE TABLE emp13

(
empno int ,
ename varchar(10),
sal money CHECK(sal>=3000)
)

INSERT INTO emp13 VALUES(100,'A',1000) => ERROR

INSERT INTO emp13 VALUES(101,'B',NULL) => 1 ROW AFFECTED

=> CHECK constraint allows null values

example 2 :- gender must be 'm','f' ?

gender char(1) check(gender in ('m','f'))

example 3 :- amt must be multiple of 100 ?

amt money check(amt%100=0)

example 4 :- pwd must be min 8 chars ?

 pwd varchar(20) check(len(pwd) >= 8)

example 5 :- email must end with '.com' or '.co' or '.in'

email varchar(3) check(email like '%.com'

or
email like '%.co'
or
email like '%.in'
)

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

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

=> to establish relationship between two tables take primary key of one
table and add it to another table as foreign key and declare with
references constraint.

PROJECTS
projid name duration cost client
100 A 5 YEARS 200 TATA MOTORS
101 B 3 YEARS 150 DBS
102 C 4 YEARS 250 L&T

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

=> values entered in foreign key column should match with values entered in
primary key column.

=> foreign key allows duplicates & nulls

=> after declaring foreign key a relationship is created between two tables
called parent/child relationship.

=> primary key table is parent and foreign key table is child.

CREATE TABLE projects

(
projid int PRIMARY KEY,
pname varchar(10) NOT NULL,
duration varchar(10),
cost money,
client varchar(20)
)

INSERT INTO projects VALUES(100,'A','5 YEARS',200,'TATA MOTORS')

INSERT INTO projects VALUES(101,'B','3 YEARS',150,'DBS')

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',5000,100)

INSERT INTO emp_proj VALUES(2,'B',3000,101)
INSERT INTO emp_proj VALUES(3,'C',4000,999) => ERROR
INSERT INTO emp_proj VALUES(4,'D',3000,100)
INSERT INTO emp_proj VALUES(5,'E',4000,NULL)

Assignment :-
---------------

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 must be automatically generated

2 ttype must be 'w' or 'd'
3 tamt must be multiple of 100
4 accno should match with accounts table accno

DEFAULT :-
---------

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

hiredate date default getdate()

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

CREATE TABLE emp14

(
empno int,
hiredate date default getdate()
)

INSERT INTO emp14(empno) VALUES(100)

INSERT INTO emp14 VALUES(101,'2021-12-31')
INSERT INTO emp14 VALUES(102,NULL)

SELECT * FROM emp14

empno hiredate
100 2022-01-03
101 2021-12-31
 102 null

04-jan-22

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

CREATE TABLE <tabname>

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

declaring check constraint at table level :-

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

PRODUCTS
prodid pname mfd_dt exp_dt
100 ABC 2022-01-01 2021-01-01 invalid

rule :- exp_dt > mfd_dt

CREATE TABLE products

(
prodid int,
pname varchar(10),
mfd_dt date,
exp_dt date,
CHECK(exp_dt > mfd_dt)
)

INSERT INTO products VALUES(100,'A',GETDATE(),'2021-01-01') => ERROR

INSERT INTO products VALUES(101,'B','2021-01-01',GETDATE())

composite primary key :-

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

=> in some tables we may not uniquely identify the records by using single
column and we need combination of columns to uniquely identify the records.
if combination of columns uniquely identify records then declare that
combination
as primary key at table level.

=> if primary key declared for combination of columns then it is called

composite primary key.

=> in composite primary key combination should not be duplicate

example :-

ORDERS PRODUCTS
ordid ord_dt del_dt prodid pname price
1000 2022-01-03 2022-01-10 100 A 1000
1001 2022-01-04 2022-01-10 101 B 2000

ORDER_DETAILS
ordid prodid qty
1000 100 1
1000 101 2
1001 100 2
1001 101 1

=> in the above example ordid & prodid combination uniquely identifies the
records so declare this combination as primary key at table level.

CREATE TABLE orders

(
ordid int PRIMARY KEY,
ord_dt date,
del_dt date ,
CHECK(del_dt > ord_dt)
)

INSERT INTO orders VALUES(1000,'2022-01-03','2022-01-10')

INSERT INTO orders VALUES(1001,'2022-01-04','2022-01-10')

CREATE TABLE products

(
prodid int PRIMARY KEY,
pname varchar(10),
price money
)

INSERT INTO products VALUES(100,'A',1000)

INSERT INTO products VALUES(101,'B',2000)

CREATE TABLE order_details

(
ordid int REFERENCES orders(ordid),
prodid int REFERENCES products(prodid),
qty int,
PRIMARY KEY(ordid,prodid)
)

INSERT INTO order_details VALUES(1000,100,1)

INSERT INTO order_details VALUES(1000,101,1)
INSERT INTO order_details VALUES(1001,100,1)
INSERT INTO order_details VALUES(1000,100,1) => ERROR

Assignment :-
-------------

PRODUCTS
prodid pname price
100 A 1000
101 B 2000

CUSTOMERS
custid name addr
1 A HYD
2 B HYD
 SALES
DATEID PRODID CUSTID QTY AMOUNT
2022-01-03 100 1 1 1000
2022-01-03 101 1 1 2000
2022-01-03 100 2 1 1000
2022-01-04 100 1 1 1000

=> identify PRIMARY KEY & FOREIGN KEY in above examples and write create
table script ?

05-jan-21

Adding constraints to existing table :-

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

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

CREATE TABLE emp66

(
empno int,
ename varchar(10),
sal money,
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

=> add primary key to column empno ?

STEP 1 :- changing the column to NOT NULL

ALTER TABLE emp66

ALTER COLUMN empno INT NOT NULL

STEP 2 :- add primary key

ALTER TABLE emp66

ADD PRIMARY KEY(empno)

Adding check constraint :-

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

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

ALTER TABLE emp66

ADD CHECK(sal>=3000)

ALTER TABLE emp

ADD CHECK(sal>=3000) => ERROR

=> above command fails because in emp table some of the employee salaries
are less than 3000. while adding constraint sql server also validates
 existing data.

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

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

ALTER TABLE emp

WITH NOCHECK ADD CHECK(sal>=3000)

Adding foreign key :-

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

=> add fk to column dno that refers dept table primary key ?

ALTER TABLE emp66

ADD FOREIGN KEY(dno) REFERENCES DEPT(deptno)

changing from NULL to NOT NULL :-

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

=> modify the column ename to not null ?

ALTER TABLE emp66

ALTER COLUMN ename VARCHAR(10) NOT NULL

Adding UNIQUE :-
------------------

=> add unique constraint to dno ?

ALTER TABLE emp66

ADD UNIQUE(dno)

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

ALTER TABLE <TABNAME>

DROP CONSTRAINT <NAME>

example :-

drop check constraint in emp66 table ?

ALTER TABLE emp66

DROP CONSTRAINT CK__emp66__sal__534D60F1

drop primary key in dept table ?

ALTER TALBE dept

DROP CONSTRAINT PK__DEPT__E0EB08D72C198F7F => ERROR

DROP TABLE DEPT => ERROR

TRUNCATE TABLE DEPT => ERROR

NOTE :- primary key constraint cannot be dropped if referenced by some fk

primary key table cannot be dropped if referenced by some fk
 primary key table cannot be truncated if referenced by some fk

06-jan-21 :-
------------

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

1 on delete no action (DEFAULT)

2 on delete cascade
3 on delete set null
4 on delete set default

=> DELETE 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 it associated with child rows.

CREATE TABLE dept77

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

INSERT INTO dept77 VALUES(10,'HR'),(20,'IT')

CREATE TABLE emp77

(
empno int primary key,
ename varchar(10),
dno int REFERENCES dept77(dno)
)

INSERT INTO emp77 VALUES(1,'A',10),(2,'B',10)

DELETE FROM dept77 WHERE dno=10 => ERROR

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

ACCOUNTS
ACCNO ACTYPE BAL
100 S 10000
101 S 20000

LOANS
ID TYPE AMT ACCNO
1 H 30 100
2 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 dept77
(
dno int primary key,
dname varchar(10)
)

INSERT INTO dept77 VALUES(10,'HR'),(20,'IT')

CREATE TABLE emp77

(
empno int primary key,
ename varchar(10),
dno int REFERENCES dept77(dno)
ON DELETE CASCADE
)

INSERT INTO emp77 VALUES(1,'A',10),(2,'B',10)

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

SELECT * FROM emp77 => no rows

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

ACCOUNTS
ACCNO ACTYPE BAL
100 S 10000
101 S 20000

TRANSACTIONS
TRID TTYPE TDATE TAMT ACCNO
1 W /// 2000 100
2 D /// 5000 100

RULE :- if account is closed along with account delete transactions also

ON DELETE SET NULL :-

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

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

CREATE TABLE dept77

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

INSERT INTO dept77 VALUES(10,'HR'),(20,'IT')

CREATE TABLE emp77

(
empno int primary key,
ename varchar(10),
dno int REFERENCES dept77(dno)
ON DELETE SET NULL
 )

INSERT INTO emp77 VALUES(1,'A',10),(2,'B',10)

DELETE FROM DEPT77 WHERE DNO=10 => 1 ROW AFFECTED

SELECT * FROM EMP77

1 A NULL
2 B NULL

ON DELETE SET DEFAULT :-

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

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

CREATE TABLE dept77

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

INSERT INTO dept77 VALUES(10,'HR'),(20,'IT')

CREATE TABLE emp77

(
empno int primary key,
ename varchar(10),
dno int DEFAULT 20
REFERENCES dept77(dno)
ON DELETE SET DEFAULT
)

INSERT INTO emp77 VALUES(1,'A',10),(2,'B',10)

DELETE FROM DEPT77 WHERE DNO=10 => 1 ROW AFFECTED

SELECT * FROM emp77

1 A 20
2 B 20

UPDATE rules :-
----------------

1 ON UPDATE NO ACTION
2 ON UPDATE CASCADE
3 ON UPDATE SET NULL
4 ON UPDATE SET DEFAULT

CREATE TABLE dept77

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

INSERT INTO dept77 VALUES(10,'HR'),(20,'IT')

 CREATE TABLE emp77
(
empno int primary key,
ename varchar(10),
dno int REFERENCES dept77(dno)
ON DELETE SET NULL
ON UPDATE CASCADE
)

INSERT INTO emp77 VALUES(1,'A',10),(2,'B',10)

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

07-jan-21 JOINS
------

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

=> to fetch data from two tables we need to join those two tables.

=> in db tables are normalized i.e. related data stored in multiple tables
to gather or combine data stored in multiple tables we need to join those
tables.

example :-

ORDERS CUSTOMERS
ordid ord_dt del_dt cid CID NAME ADDR
1000 05/ 8/ 10 10 A HYD
1001 06/ 9/ 11 11 B MUM
1002 07/ 9/ 12 12 C DEL

report :-

ordid ord_dt del_dt cname caddr

1000 05/ 08/ A HYD

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

1 equi / inner join

2 outer join
left join
right join
full join
3 non equi join
4 self join
5 cross / cartesian join

Equi / Inner Join :-

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

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

=> Equi join is performed based on the common field with same datatype.
 SELECT columns
FROM tab1 INNER JOIN tab2
ON join-condition ;

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

=> based on the given join condition sql server joins the records of two tables
=> join condition decides which record of 1st table joined with which record of
2nd table

table1.commonfield = table2.commonfield

Example :-

EMP DEPT
EMPNO ENAME SAL DEPTNO DEPTNO DNAME LOC
1 A 3000 10 10 ACCOUNTS ?
2 B 5000 20 20 RESEARCH ?
3 C 4000 30 30 SALES ?
4 D 2000 10 40 OPERATIONS ?
5 E 3000 NULL

=> display EMPNO ENAME SAL DNAME LOC ?

----------------- -------------
EMP DEPT

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

1 A 3000 ACCOUNTS ???

2 B 5000 RESEARCH ???
3 C 4000 SALES ???
4 D 2000 ACCOUNTS ???

=> display EMPNO ENAME SAL DEPTNO DNAME LOC ?

------------------- --------------------
emp dept

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

=> 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 e INNER JOIN dept d
ON e.deptno = d.deptno

08-jan-22

=> Display employees working at NEW YORK loc ?

 SELECT e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno
WHERE d.loc='NEW YORK'

joining more than 2 tables :-

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

SELECT columns
FROM tab1 INNER JOIN tab2
ON cond1
INNER JOIN tab3
ON cond2
INNER JOIN tab4
ON cond3

example :-

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 e INNER JOIN dept d
ON e.deptno = d.deptno
INNER JOIN locations l
ON d.locid = l.locid
INNER JOIN countries c
ON l.country_id = c.country_id

Assignment :-
-------------

ORDERS CUSTOMERS
ordid ord_dt del_dt cid CID NAME ADDR
1000 05/ 8/ 10 10 A HYD
1001 06/ 9/ 11 11 B MUM
1002 07/ 9/ 12 12 C DEL

=> display order details with customer details ?

=> display order details with customers details ordered today ?
=> display order details with customers details delivered today ?

OUTER JOIN :-
------------

=> inner join returns only matching records but cannot return unmatched
records but to get unmatched records also perform outer join.

=> outer join is 3 types

 1 LEFT JOIN
2 RIGHT JOIN
3 FULL JOIN

EMP DEPT
EMPNO ENAME SAL DEPTNO DEPTNO DNAME LOC
1 A 3000 10 10 ACCOUNTS ?
2 B 5000 20 20 RESEARCH ?
3 C 4000 30 30 SALES ?
4 D 2000 10 40 OPERATIONS ? => unmatched record
5 E 3000 NULL => unmatched record

LEFT JOIN :-
------------

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

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

=> above query returns all rows from emp and matching rows from dept

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

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

=> returns all rows from right side table and matching rows from left side table

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

=> returns all rows from dept table and matching rows from emp table.

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

FULL JOIN :-
-------------

=> returns all rows from both tables

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

=> above query returns all rows from emp & dept
 A ACCOUNTS
B RESEARCH
C SALES
D ACCOUNTS
E NULL => unmatched from emp
NULL OPERATIONS => unmatched from dept

10-jan-22

Assignment :-
-------------

PROJECTS
projid pname duration cost client
100
101
102

EMP
empid ename sal projid
1 100
2 101
3 100
4 null

=> display employee details with project details and also display employees
not assigned to any project ?

=> display employee details with project details and also display projects
where no employee assigned to it ?

NON EQUI JOIN :-

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

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

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

Example :-

EMP SALGRADE
EMPNO ENAME SAL GRADE LOSAL HISAL
1 A 5000 1 700 1000
2 B 2500 2 1001 2000
3 C 1000 3 2001 3000
4 D 3000 4 3001 4000
5 E 1500 5 4001 9999

=> Display EMPNO ENAME SAL GRADE ?

----------------- ------
EMP SALGRADE

SELECT e.empno,e.ename,e.sal,
s.grade
FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal and s.hisal
1 A 5000 5
2 B 2500 3
3 C 1000 1
4 D 3000 3
5 E 1500 2

=> display grade 3 employees ?

SELECT e.empno,e.ename,e.sal,
s.grade
FROM emp e JOIN salgrade s
ON e.sal BETWEEN s.losal and s.hisal
WHERE s.grade = 3

=> display ENAME DNAME GRADE ?

------ ------ ------
EMP DEPT SALGRADE

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

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

=> joning a table to itself is called self join

=> a record in one table joined with another record of same table

example :-

EMP
EMPNO ENAME MGR
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7839 KING NULL
7902 FORD 7566

=> above table contains manager number but to display manager name
perform self join.

=> to perform self join the same table must be declared two times with
different alias in from clause.

FROM emp x JOIN emp y

EMP X EMP Y
EMPNO ENAME MGR EMPNO ENAME MGR
7369 SMITH 7902 7369 SMITH 7902
7499 ALLEN 7698 7499 ALLEN 7698
7521 WARD 7698 7521 WARD 7698
7566 JONES 7839 7566 JONES 7839
7654 MARTIN 7698 7654 MARTIN 7698
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 mgrname

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

SMITH FORD
ALLEN BLAKE
WARD BLAKE

17-jan-22 :-

=> display employee names reporting to blake ?

SELECT x.ename,y.ename
FROM emp x JOIN emp y
ON x.mgr = y.empno
WHERE y.ename='blake'

=> display blake's manager name ?

SELECT x.ename,y.ename
FROM emp x JOIN emp y
ON x.mgr = y.empno
WHERE x.ename='blake'

=> display employees earning more than their managers ?

SELECT x.ename,y.ename
FROM emp x JOIN emp y
ON x.mgr = y.empno
WHERE x.sal > y.sal

=> employees joined before their manager ?

SELECT x.ename,y.ename
FROM emp x JOIN emp y
ON x.mgr = y.empno
WHERE x.hiredate < y.hiredate

Assignment :-
-------------

TEAMS
ID COUNTRY
1 IND
2 AUS
3 ENG

OUTPUT :-

IND VS AUS
IND VS ENG
AUS VS ENG
Display ENAME DNAME GRADE MNAME ?
----- ----- ------- -------
EMP DEPT SALGRADE EMP

CROSS JOIN OR 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 each record of 1st table
joined with each and every record of 2nd table.

SELECT e.ename,d.dname
FROM emp e CROSS JOIN dept d

GROUP BY & JOIN :-

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

=> display dept wise no of employees ?

SELECT deptno,COUNT(empno) as cnt

FROM emp
GROUP BY deptno

10 3
20 5
30 6

above query display departments numbers , instead of deptno to display

dept names join is required

SELECT d.dname,COUNT(e.empno) as cnt

FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.dname

Assignment :-
-------------

SALES
DATEID PRODID CUSTID QTY AMOUNT
2022-01-17 100 10 1 1000

PRODUCTS
PRODID PNAME PRICE CATEGORY BRAND
100 AAA 1000 ELECTRONICS SAMSUNG

CUSTOMERS
CUSTID NAME ADDR COUNTRY
10 XYZ HYD IND

=> display year wise total amount ?

=> display quarter wise total amount in the year 2021 ?
=> display category wise total amount ?
 => display brand wise total amount ?
=> display country wise total amount ?
=> display year wise ,country wise,category wise total amount ?

18-jan-22

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 set operations are performed between set of rows return
by two select statements.

SELECT STATEMENTS 1
UNION/UNION ALL/INTERSECT/EXCEPT
SELECT STATEMENT 2

Rules :-

1 both queries must return same no of columns

2 corresponding columns datatype must be same

SELECT job FROM emp WHERE deptno=20

CLERK
MANAGER
ANALYST
CLERK
ANALYST

SELECT job FROM emp WHERE deptno=30

SALESMAN
SALESMAN
SALESMAN
MANAGER
SALESMAN
CLERK

UNION :-
---------

=> combines rows return by two select statements

=> eliminates duplicates
=> sorts result
 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 FROM emp WHERE deptno=30 => ERROR (no of columns not matching)

SELECT job,sal FROM emp WHERE deptno=20

UNION
SELECT sal,job FROM emp WHERE deptno=30 => ERROR (corresponding columns
datatype is not same)

SELECT job,sal FROM emp WHERE deptno=20

UNION
SELECT job,sal FROM emp WHERE deptno=30

ANALYST 3450.00
ANALYST 3950.00
CLERK 920.00
CLERK 1140.00
CLERK 1265.00
MANAGER 3420.00
MANAGER 4421.25
SALESMAN 1500.00
SALESMAN 1920.00
SALESMAN 4160.00

=> What is the difference between union and join ?

UNION JOIN

1 horizontal merge vertical merge

2 combines rows combines columns

3 performed between performed between

two similar structure two dissimilar structures

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

EMP_US
ENO ENAME SAL DNO
DEPT
EMP_IND DNO DNAME LOC
ENO ENAME SAL DNO

1 display total employees list ?

SELECT * FROM emp_us

UNION
SELECT * FROM emp_ind
2 display employees working at US loc with dept details ?

SELECT e.*,d.*
FROM emp_us e INNER JOIN dept d
ON e.dno = d.dno

3 display total employees with dept details ?

SELECT e.*,d.*
FROM emp_us e INNER JOIN dept d
ON e.dno = d.dno
UNION
SELECT e.*,d.*
FROM emp_ind e INNER JOIN dept d
ON e.dno = d.dno

UNION ALL :-
------------

=> combines rows return by two select statements

=> 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 result is sorted result is not sorted

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 from the 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

SELECT job FROM emp WHERE deptno=30

EXCEPT
SELECT job FROM emp WHERE deptno=20

SALESMAN

19-jan-22 :-

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

=> write the output for following operations ?

1 inner join
2 left join
3 right join
4 full join
5 union
6 union all
7 intersect
8 except

SUBQUERIES OR NESTED QUERIES :-

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

=> a query in another query is called subquery 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 then sql server executes outer query
=> output of inner query is input to outer query
=> use subqueries when where condition based on unknown value

Types of subqueries :-
----------------------

1 single row subqueries

2 multi row subqueries
3 co-related subqueries
4 derived tables
5 scalar subqueries
 single row subqueries :-
--------------------------

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

SELECT columns
FROM tabname
WHERE colname OP (SELECT STATEMENT)

=> OP can be any relational operator like > >= < <= = <>

Examples :-

=> display employees earning more than blake ?

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

=> display employees who are senior to king ?

SELECT *
FROM emp
WHERE hiredate < (SELECT hiredate FROM emp WHERE ename='king')

=> display employee name earning max salary ?

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

aggregate functions are not allowed in where clause and they are allowed
only in select,having clauses.

SELECT ename
FROM emp
WHERE sal = (SELECT MAX(sal) FROM emp)

=> display employee name having max experience ?

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

NOTE :- outer query can be SELECT/INSERT/UPDATE/DELETE but inner query

must be always SELECT.

=> delete employee having max experience ?

DELETE FROM emp

WHERE hiredate = (SELECT MIN(hiredate) FROM emp)

=> update employee sal to max(sal) of 30th dept whose empno = 7499 ?

UPDATE emp
SET sal = (SELECT MAX(sal) FROM emp WHERE deptno=30)
WHERE empno=7499
20-jan-22

multirow subqueries :-
-----------------------

=> if inner query returns more than one value then it is called multirow
subquery

SELECT columns
FROM tabname
WHERE colname OP (SELECT STATEMENT)

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

=> display employees whose job = job of smith,blake ?

SELECT *
FROM emp
WHERE job IN (SELECT job FROM emp WHERE ename IN ('smith','blake'))

ANY operator :-
---------------

=> use ANY operator for > < comparision with multiple values

WHERE X > ANY(1000,2000,3000)

IF X=800 FALSE
X=1500 TRUE
X=4500 TRUE

WHERE X < ANY(1000,2000,3000)

IF X=800 TRUE
1500 TRUE
4500 FALSE

ALL operator :-
---------------

=> use ALL operator for > < comparision with multiple values

WHERE X > ALL(1000,2000,3000)

IF X=800 FALSE
1500 FALSE
4500 TRUE

WHERE X < ALL(1000,2000,3000)

IF X=800 TRUE
1500 FALSE
4500 FALSE

SINGLE MULTI

= IN
 > >ANY >ALL

< <ANY <ALL

=> display employees earning more than all managers ?

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

=> display employees earning more than atleast one manager ?

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

CO-RELATED SUBQUERIES :-
------------------------

=> if inner query references values of outer query then it is called

co-related subquery.

=> 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 subquery to execute subquery for each row return by outer query

Example 1 :-

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

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

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

1 A 5000 10 5000 > (SELECT AVG(sal) FROM emp WHERE deptno=10)

4000 TRUE

2 B 3000 20 3000 > (SELECT AVG(sal) FROM emp WHERE deptno=20)

4500 FALSE

3 C 4000 30 4000 > (SELECT AVG(sal) FROM emp WHERE deptno=30)

4000 FALSE

4 D 6000 20 6000 > (SELECT AVG(sal) FROM emp WHERE deptno=20)

4500 TRUE

5 E 3000 10 3000 > ( SELECT AVG(sal) FROM emp WHERE deptno=10)

4000 FALSE

Example 2 :-
 => display employees earning max sal in their dept ?

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

31-jan-22

EXISTS & NOT EXISTS operators :-

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

=> EXISTS & NOT EXISTS are used to check whether record exists in the table or not

SELECT *
FROM tabname
WHERE EXISTS (SELECT STATEMENT)

=> EXISTS returns TRUE OR FALSE

TRUE => if subquery returns atleast one row

FALSE => if subquery returns 0 rows

SELECT *
FROM tabname
WHERE NOT EXISTS (SELECT STATEMENT)

=> NOT EXISTS returns TRUE OR FALSE

TRUE => if subquery returns 0 rows

FALSE => if subquery returns atleast one row

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

CUSTS CUSTT
CID NAME ADDR CID NAME ADDR
10 A HYD 10 A HYD
11 B MUM 11 B MUM
12 C DEL

=> display records from custs table which are there in custt table ?

METHOD 1 :-

SELECT *
FROM custs s
WHERE EXISTS (SELECT * FROM custt WHERE cid=s.cid)

10 A HYD
11 B MUM

METHOD 2 :-

SELECT *
FROM CUSTS
WHERE CID IN (SELECT CID FROM CUSTT)

10 A HYD
 11 B MUM

=> SQL SERVER recommends EXISTS operator than IN operator because EXISTS gives
good performance than IN operator.

=> display records from custs table which are not there in custt table ?

method 1 :-

SELECT *
FROM custs s
WHERE NOT EXISTS (SELECT * FROM custt WHERE cid=s.cid)

12 C DEL

method 2 :-

SELECT *
FROM custs
WHERE cid NOT IN (SELECT cid FROM custt)

12 C DEL

=> sql server recommends NOT EXISTS operator than NOT IN because NOT EXISTS
gives good performance than NOT IN operator.

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

=> subqueries appears in FROM clause are called derived tables

SELECT columns
FROM (SELECT statement) <alias>
WHERE condition

=> subquery 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 use result of one operation in another operation
3 to join table with query output

=> by default sql server executes the clauses in the following order

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

=> use derived tables to control this order of execution

Example 1 :-

=> display ranks of all 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 displays 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 referenced in WHERE clause because WHERE clause is

executed before SELECT , to overcome this problem use derived table.

SELECT *
FROM (SELECT empno,ename,sal,
DENSE_RANK() OVER (ORDER BY sal DESC) as rnk
FROM emp) E
WHERE rnk<=5

Example 2 :-

display top 5 max salaries ?

SELECT DISTINCT sal

FROM (SELECT empno,ename,sal,
DENSE_RANK() OVER (ORDER BY sal DESC) as rnk
FROM emp) E
WHERE rnk<=5
ORDER BY sal DESC

01-FEB-22

Example 3 :-

=> display first 5 rows from emp table ?

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

=> display 5th record to 10th record ?

SELECT *
FROM ( SELECT empno,ename,sal,
ROW_NUMBER() OVER (ORDER BY empno ASC) as rno
FROM emp ) E
WHERE rno BETWEEN 5 AND 10

=> display even no rows ?

SELECT *
FROM ( SELECT empno,ename,sal,
ROW_NUMBER() OVER (ORDER BY empno ASC) as rno
FROM emp ) E
WHERE rno%2=0
=> delete first 5 rows from emp table ?

DELETE
FROM ( SELECT empno,ename,sal,
ROW_NUMBER() OVER (ORDER BY empno ASC) as rno
FROM emp ) E
WHERE rno<=5 => ERROR

NOTE :- in derived tables outer query cannot be DML command it must be

SELECT statement. To overcome this problem use CTEs

CTE :-
--------

=> Common Table Expression, also called as CTE in short form,

is a temporary named result set that you can reference within a SELECT,
INSERT, UPDATE, or DELETE statement.

=> in derived tables outer query must be SELECT but in CTEs outer query
can be SELECT/INSERT/UPDATE/DELETE.

syntax :-

WITH expression_name [ ( column_name [,...n] ) ]

AS
( CTE_query_definition )

Select [Column1,Column2,Column3 …..] from expression_name

Example 1 :-

=> delete first 5 rows from emp table ?

WITH E
AS
(SELECT empno,ename,sal,
ROW_NUMBER() OVER (ORDER BY empno ASC) as rno
FROM emp)
DELETE FROM E WHERE rno<=5

Example 2 :-

=> delete duplicate records ?

EMP44
ENO ENAME SAL
1 A 5000
2 B 6000
1 A 5000 => duplicate record
2 B 6000 => duplicate record
3 C 7000

step 1 :-

SELECT eno,ename,sal,
ROW_NUMBER() over (partition by eno,ename,sal ORDER BY eno ASC) as rno
FROM emp44
eno ename sal rno
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 record 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

scalar subqueries :-
---------------------

=> subqueries in SELECT clause are called scalar subqueries

SELECT (subquery1),(subquery2),----------
FROM tabname
WHERE cond

=> subquery output acts like a column

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

Example 1 :-

SELECT (SELECT COUNT(*) FROM emp) as emp,(SELECT COUNT(*) FROM dept) as dept

emp dept
14 4

using where
order by
functions
group by
joins
set operators
subqueries

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 gurantee a property called atomocity

i.e. all or none. if transaction contains multiple dmls
if all operations are successful then it must be saved , if one of
operation fails then entire transaction must be cancelled.

=> the following commands provided by sql server to handle transactions

called TCL commands (Transaction Control Language).

1 COMMIT => to save transaction

2 ROLLBACK => to cancel transaction
3 SAVE TRANSACTION => to cancel part of the transaction

=> every transaction has a begin point and an end point

=> in sql server a txn begins implicitly with DML/DDL command and ends
implicitly with COMMIT.

=> user can also start transaction explicitly with "BEGIN TRANSACTION"
command and ends explicitly with COMMIT/ROLLBACK command.

Example 1 :-

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)
commit

=> if txn ends with commit then it is called successful transaction and
operations are saved.

Example 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

=> if txn ends with rollback then it is called aborted transaction

and operations are cancelled.

Example 3 :-

create table a(a int) => saved

begin transaction => txn begins T1
insert into a values(10)
 insert into a values(20)
commit => txn ends
insert into a values(30) => implicitly saved
insert into a values(40) => implicitly saved
rollback

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

example 1 :-

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

example 2 :-

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 st2

select * from a

10
20
30
40

03-feb-22

Database Security :-
--------------------

1 logins => provides security at server level

2 users => provides security at db level
3 privileges => provides security at table level
4 views => provides security at row & col level

SERVER (login)
DATABASE (users)
TABLES (privileges)
ROWS & COLS (views)

04-feb-22

Database Objects :-
--------------------

TABLES
VIEWS
SYNONYMS
SEQUENCES
INDEXES

PROCEDURES
FUNCTIONS
TRIGGERS

VIEWS :-
---------

=> a view is a subset of a 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 are created

1 to provide security
2 to reduce complexity

=> view provides another level of security called row & column level i.e.
with the help of views we can grant specific rows and columns to users

=> views are 2 types

1 simple views
2 complex views

simple views :-
----------------

=> if view created on single table then it is called simple view

CREATE VIEW <NAME>

AS
SELECT STATEMENT

Ex :-

CREATE VIEW V1
 AS
SELECT empno,ename,job,deptno FROM emp

=> sql server creates view v1 and stores query but not query output

SELECT * FROM V1

=> when above query submitted to sql server then sql server executes
the query as follows

SELECT * FROM (SELECT empno,ename,job,deptno FROM emp)

Granting permissions on view to user :-

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

GRANT SELECT,INSERT,UPDATE,DELETE ON V1 TO VIJAY

VIJAY :-
--------

1 SELECT * FROM emp

2 INSERT INTO v1 VALUES(444,'ABC','CLERK',20)

3 UPDATE V1 SET JOB='MANAGER' WHERE EMPNO=444

4 UPDATE V1 SET SAL=5000 WHERE EMPNO=444 => ERROR

ROW LEVEL SECURITY :-

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

CREATE VIEW V2
AS
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno=20

GRANT SELECT,INSERT,UPDATE,DELETE ON V2 TO VIJAY

VIJAY :-

INSERT INTO V2 VALUES(555,'XYZ','CLERK',30) => 1 ROW AFFECTED

WITH CHECK OPTION :-

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

=> if view created with "WITH CHECK OPTION" then any DML command
through violates where condition that DML is not accepted.

CREATE VIEW V3
AS
SELECT empno,ename,job,deptno
FROM emp
WHERE deptno=20
WITH CHECK OPTION

GRANT SELECT,INSERT,UPDATE,DELETE ON V2 TO VIJAY

VIJAY :-
INSERT INTO V3 VALUES(777,'KLM','CLERK',30) => ERROR

05-feb-22

complex views :-
------------------

=> a view said to be complex view

1 if query performs join operation

2 if query performs group by
distinct
aggregate
set operations
subqueries

=> with the help of views complex queries can be converted into simple queries

Example 1 :-

CREATE VIEW CV1

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

=> after creating view whenever we want data from emp & dept tables
instead of writing join query write the simple query as follows

SELECT * FROM cv1

Example 2 :-

CREATE VIEW CV2

AS
SELECT d.dname,MIN(e.sal) as minsal,
MAX(e.sal) as maxsal,
SUM(e.sal) as totsal,
COUNT(e.empno) as cnt
FROM emp e INNER JOIN dept d
ON e.deptno = d.deptno
GROUP BY d.dname

=> after creating view whenever we want dept wise summary then write the
simple query as follows

SELECT * FROM CV2

=> Difference between simple & complex views ?

simple complex

1 based on single table based on multiple tables

2 query performs simple query performs complex operations

operations like joins,group by etc.
3 always updatable i.e. not updatable i.e. doesn't allow dmls
allows dmls

=> display list of views created by user ?

SELECT * FROM INFORMATION_SCHEMA.views

Droping view :-
--------------

DROP VIEW V1

=> if we drop table what about views created on table ?

ans :- views are not dropped but cannot be accessed

WITH SCHEMABINDING :-
---------------------

=> if view created with schemabinding then sql server will not allow
the user to drop table if any view exists on the table. To drop table
first we need to drop view.

Rules :-

1 "*" is not allowed select

2 tablename should be prefixed with schema name (dbo)

CREATE VIEW V10

WITH SCHEMABINDING
AS
SELECT deptno,dname,loc FROM dbo.dept

DROP TABLE DEPT => ERROR

Synonyms :-
----------

=> a synonym is another name or alternative name for a table or view

=> if tablename is lengthy then developer can give a simple and short name
to the table called synonym and where you want to use tablename instead of
using tablename we can use synonym name

syn :- CREATE SYNONYM <NAME> FOR <TABNAME>

Ex :- CREATE SYNONYM E FOR EMP

=> after creating synonym instead of using tablename we can use synonym
name in SELECT/INSERT/UPDATE/DELETE queries

1 SELECT * FROM E

2 UPDATE E SET COMM=500 WHERE EMPNO=7369

07-feb-22

=> Difference between alias and synonym ?

 alias synonym

1 not permanent permanent

2 not stored in db stored in db

3 scope of the alias scope of the synonym is upto the schema

is upto the query

SEQUENCES :-
------------

=> sequences are created to generate sequence numbers.

=> using sequence we can auto increment column values

syn :- CREATE SEQUENCE <NAME>

[START WITH <VALUE>]
[INCREMENT BY <VALUE>]
[MAXVALUE <VALUE>]
[MINVALUE <VALUE>]
[CYCLE/NOCYCLE]

Ex :- CREATE SEQUENCE S1
START WITH 1
INCREMENT BY 1
MAXVALUE 5

using sequence :-
-----------------

CREATE TABLE student

(
sid int,
sname varchar(10)
)

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

calling sequence in update command :-

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

CREATE SEQUENCE S2
START WITH 100
INCREMENT BY 1
MAXVALUE 999

UPDATE EMP SET EMPNO = NEXT VALUE FOR S2

calling sequence in expression :-

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

CREAT SEQUENCE S3
START WITH 1
INCREMENT BY 1
MAXVALUE 9999

CREATE TABLE INVOICE

(
INVNO VARCHAR(20),
INVDT DATETIME
)

=> use above sequence to generate invno as follows ?

INVOICE
INVNO INVDT
KLM/0222/1 ?
KLM/0222/2 ?
KLM/0222/3 ?

INSERT INTO INVOICE VALUES('KLM/' +

FORMAT(GETDATE(),'MMyy') + '/' +
CAST(NEXT VALUE FOR S3 AS VARCHAR),GETDATE())

How to reset sequence ?

1 using ALTER command

2 using CYCLE option

using ALTER command :-

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

CREATE SEQUENCE S4
START WITH 100
INCREMENT BY 1
MAXVALUE 999

=> after generating 150 reset the sequence to 100 ?

ALTER SEQUENCE S4 RESTART WITH 100

CYCLE/NOCYCLE option :-
----------------------

=> default is NOCYCLE

=> if sequence created with NOCYCLE then it starts from start with and
generates upto MAX and after reaching MAX then it stops.

=> if sequence created with CYCLE then it starts from start with and
generates upto MAX and after reaching MAX then it will be reset to MIN.

CREATE SEQUENCE S5
START WITH 1
INCREMENT BY 1
MAXVALUE 5
MINVALUE 1
CYCLE

how to see the list of sequences ?

SELECT * FROM INFORMATION_SCHEMA.SEQUENCES

08-feb-22

Difference between sequence & identity ?

1 The Identity Property of a column is available from SQL Server 2000

whereas the Sequence object is available from SQL Server 2012.

3 identity is bind to a particular column and particular table but

whereas sequence is not bind to any column and any table.

2 Identity cannot be controlled by application code whereas

Sequence can be controlled by application code.

3 If any column is marked as Identity then we cannot insert data into

this column directly. In that case, we must first turn off the
Identity of the column whereas a Sequence object does not depend
on the table so we can insert any value in the column.

4 We cannot get the value of an Identity column before inserting a record

whereas we can get the value of the next Sequence number for a
Sequence Object before inserting a record.

5 We can reseed an Identity property but we cannot change the step size
whereas we can alter the Seed as well as the Step size of a Sequence object
at any time.

INDEXES :-
-----------

=> index is also a db object created to improve the performance of

data accessing.

=> index in db is similar to index in textbook , in textbook using index

a particular topic can be located fastly. In db using index a particular
record can be located fastly.

=> indexes are created on columns and that column is called index key

=> indexes created on columns

1 that are frequently accessed in where conditions

2 that are used in join operation

=> whenever user submits a query to sql server then it uses following
methods to locate the record

1 table scan
2 index scan

=> in table scan sql server scans complex table.

=> in index scan sql server scans only half the table so index scan
is much faster than table scan.

Types of Indexes :-
-------------------
 1 Non Clustered Indexes
simplex index
composite index
unique index
2 Clustered Indexes

simple non clustered index :-

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

=> if index created on single column then it is called simple index.

syn :- CREATE INDEX <NAME> ON <TABNAME>(COLNAME)

Ex :- CREATE INDEX I1 ON EMP(SAL)

EMP 3000 ROOT

SAL
5000 2000 4000 INTERMEDIATE
1000
3000 1000 * 2500 * 4000 * 5000 * LEAF
2000 1500 * 3000 *,*
4000 2000 *
1500
3000
2500

SELECT * FROM emp WHERE sal=3000 (index scan)

SELECT * FROM emp WHERE sal>=3000 (index scan)
SELECT * FROM emp WHERE sal<=3000 (index scan)

09-feb-22

composite index :-
------------------

=> if index created on multiple columns then index is called composite index

ex :- CREATE INDEX I2 ON EMP(DEPTNO,JOB)

EMP
deptno job
10 CLERK
20 MANAGER
30 SALESMAN
20 CLERK
30 CLERK
10 MANAGER
20 CLERK
30 SALESMAN

20

10 40

10 CLERK * 20 CLERK *.* 30 CLERK *

10 MANAGER * 20 MANAGER * 30 SALESMAN *,*
=> SELECT * FROM emp WHERE deptno=20 ; (index scan)
SELECT * FROM emp WHERE deptno=20 AND job='CLERK' (index scan)
SELECT * FROM emp WHERE job='CLERK' (table scan)

note :- SQL SERVER uses above index when where condition based on
leading column of the index.

UNIQUE index :-
--------------

=> UNIQUE index doesn't allow duplicate values into the column on which
index is created

ex :- CREATE UNIQUE INDEX I3 ON EMP(ENAME)

G Q

ADAMS * JAMES * MARTIN * SCOTT *

ALLEN * JONES * MILLER * SMITH *
BLAKE * KING *

SELECT * FROM emp WHERE ename='BLAKE' ;

INSERT INTO emp(empno,ename,sal) VALUES(333,'BLAKE',4000) => ERROR

=> primary key/unique columns are automatically indexed by sql server

and a unique index is created on primary key/unique columns and
unique index doesn't allow duplicates so primary key/unique also doesn't
allow duplicates .

CLUSTERED INDEX :-
------------------

=> a non clustered index stores pointers to actual records but whereas
clustered index stores actual records.

=> in non clustered indexes order of the records in table and order of
the records in index will not be same but in clustered index this order
will be same.

Example :-

CREATE TABLE cust

(
cid int,
cname varchar(10)
)

CREATE CLUSTERED INDEX I5 ON cust(cid)

INSERT INTO cust VALUES(10,'A')

INSERT INTO cust VALUES(70,'B')
INSERT INTO cust VALUES(40,'C')
INSERT INTO cust VALUES(90,'D')
INSERT INTO cust VALUES(30,'E')
 50

30 80

10 A 40 C 70 B 90 D
30 E

SELECT * FROM cust

10 A
30 E
40 C
70 B
90 D

10-feb-22

=> only one clustered index is allowed per table.

=> by default sql server creates clustered index on primary key column.

What type of index is created on primary key columns ?

ans :- clustered unique index

difference between non clustered and clustered indexes ?

non clustered clustered

1 stores pointers to actual records stores actual records

2 order of the records in table order of the records in table

and order of the records in and order of the records in index
index will not be same is same

3 needs extra storage doesn't need extra storage

4 requires two lookups requires one lookup to find

to find the records record

5 sql server allows 999 sql server allows only one

non clustered indexes clustered index per table
per table

6 created explicitly created implicitly on primary key columns

=> maximum no of indexes created on table ?

ans :- 1000 ( 999 non clustered + 1 clustered )

Droping Index :-
----------------

DROP INDEX EMP.I1

=> if we drop table what about indexes created on table ?

 ans :- indexes are also dropped

DB6PM
TABLES
ROWS & COLS
CONSTRAINTS
INDEXES
TRIGGERS
VIEWS
SYNONYMS
SEQUENCES

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

=> used for cross tabulation or matrix report

=> used to convert rows into columns

syntax :-

SELECT *
FROM (SELECT statement) AS <ALIAS>
PIVOT
(
AGGR-EXPR FOR COLNAME IN (V1,V2,V3,--)
) AS <PIVOT-TABNAME>
ORDER BY <COLNAME> ASC/DESC

Example 1 for cross tabulation or matrix report :-

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

10 20 30

analyst ??? ??? ???

clerk ??? ??? ???

manager ??? ??? ???

salesman ??? ??? ???

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

job 10 20 30
ANALYST NULL 6000.00 NULL
CLERK 1300.00 1900.00 950.00
MANAGER 2450.00 2975.00 2850.00
PRESIDENT 5000.00 NULL NULL
SALESMAN NULL NULL 5600.00
 Example 2 :-
-----------

1 2 3 4

2018 ? ? ? ?

2019 ? ? ? ?

2020 ? ? ? ?

2021 ? ? ? ?

SELECT *
FROM (SELECT DATEPART(yy,hiredate) AS year,
DATEPART(qq,hiredate) AS qrt,
empno
FROM emp) E
PIVOT
(
COUNT(empno) FOR qrt IN ([1],[2],[3],[4])
) AS PIVOT_TABLE
ORDER BY year ASC

YEAR 1 2 3 4
2018 1 0 0 0
2019 1 0 0 1
2020 0 0 0 4
2021 2 3 2 0

11-feb-22

Example 3 :- converting rows into columns

STUDENT
SID SNAME SUBJECT MARKS
1 A MAT 80
1 A PHY 90
1 A CHE 70
2 B MAT 60
2 B PHY 70
2 B CHE 50

OUTPUT :-

SID SNAME MAT PHY CHE

1 A 80 90 70
2 B 60 70 50

SELECT *
FROM student
PIVOT
(
SUM(marks) FOR subject IN ([MAT],[PHY],[CHE])
) AS PIVOT_TABLE
ORDER BY SID ASC

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

=> used to merge data into a table.

=> merge is the combination of insert,update and delete
=> used to manage replicas i.e. duplicate copy

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

10/2/22

CUSTS
CID NAME ADDR
10 A HYD
11 B MUM
12 C DEL

create replica for CUSTS ?

SELECT * INTO CUSTT FROM CUSTS

CUSTT
CID NAME ADDR
10 A HYD
11 B MUM
12 C DEL

11/2/22

CUSTS
CID NAME ADDR
10 A BLR => UPDATED
11 B MUM
12 C DEL
13 D KOL => INSERTED

=> use MERGE command to apply changes made to CUSTS to CUSTT

Syn :-

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

Example :-

MERGE INTO CUSTT AS T

USING CUSTS AS S
ON (S.CID = T.CID)
WHEN MATCHED THEN
UPDATE SET T.ADDR = S.ADDR
WHEN NOT MATCHED THEN
INSERT VALUES(S.CID,S.NAME,S.ADDR) ;
step 1 :-

CREATE TABLE CUSTS

(
CID INT,NAME VARCHAR(10),ADDR VARCHAR(10)
)

INSERT INTO CUSTS VALUES(10,'A','HYD'),(11,'B','MUM'),(12,'C','DEL')

SELECT * FROM CUSTS

CID NAME ADDR

10 A HYD
11 B MUM
12 C DEL

step 2 :- create replica

SELECT * INTO CUSTT FROM CUSTS

SELECT * FROM CUSTT

CID NAME ADDR

10 A HYD
11 B MUM
12 C DEL

step 3 :- modify source table

INSERT INTO CUSTS VALUES(13,'D','KOL')

UPDATE CUSTS SET ADDR = 'BLR' WHERE CID=10

SELECT * FROM CUSTS

CID NAME ADDR

10 A BLR => UPDATED
11 B MUM
12 C DEL
13 D KOL => INSERTED

step 4 :- apply the above changes to target table

MERGE INTO CUSTT AS T

USING CUSTS AS S
ON (S.CID = T.CID)
WHEN MATCHED THEN
UPDATE SET T.ADDR = S.ADDR
WHEN NOT MATCHED THEN
INSERT VALUES(S.CID,S.NAME,S.ADDR) ;

SELECT * FROM CUSTT

10 A BLR
11 B MUM
12 C DEL
13 D KOL
Example 2 :-

SELECT * FROM CUSTS

CID NAME ADDR

10 A BLR
11 B MUM
12 C DEL
13 D KOL => DELETED

SELECT * FROM CUSTT

10 A BLR
11 B MUM
12 C DEL
13 D KOL

MERGE INTO CUSTT AS T

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

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

12-feb-22

TSQL(Transact SQL) programming :-

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

Features :-
-----------

1 improves performance :-
-------------------------

=> in TSQL , sql commands can be grouped into one block and we submit that
block to sql server. so TSQL programming no of requests and response
between user and sql server are reduced and performance is improved.

2 supports conditional statements :-

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

=> TSQL supports conditional statements like IF-THEN-ELSE , here we can

execute commands based on conditions.

3 supports loops :-
-------------------

=> loops are used to execute statements repeatedly multiple times

TSQL supports looping statements like while.
4 supports error handling :-
-----------------------------

=> in TSQL programming if any statement causes error then we can handle
that error and we can display our own simple and user friendly message.

5 supports resuability :-
-------------------------

=> TSQL programs can be stored in db and applications which are connected
to db can reuse these programs.

6 supports security :-
-----------------------

=> TSQL programs are secured because only authorized users can execute these
programs.

=> TSQL blocks are 2 types

1 anonymous blocks
2 named blocks
stored procedures
stored functions
triggers

Anonymous blocks :-
---------------------

=> a TSQL block without name is called anonymous block

=> the following statements are used to TSQL programming

1 DECLARE
2 SET
3 PRINT

DECLARE statement :-
--------------------

=> used to declare variable

syn :- DECLARE @varname datatype(size)

ex :- DECLARE @x int
DECLARE @s varchar(10)
DECLARE @d date

DECLARE @x int,@s varchar(10),@d date

SET statement :-
------------------

=> used to assign value to variable

syn :- SET @varname = value

ex :- SET @x = 100

SET @s = 'abc'
 SET @d = GETDATE()

PRINT statement :-
-------------------

=> used to print messages or values

PRINT 'HELLO'

PRINT @x

=> write a prog to add two numbers ?

DECLARE @a int,@b int,@c int

SET @a=100
SET @b=200
SET @c=@a+@b
PRINT @c

=> write a prog to input date and print day of the week ?

DECLARE @d DATE
SET @d = '1947-08-15'
PRINT DATENAME(DW,@d)

15-feb-22

DB programming with TSQL :-

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

=> to perform operations over db execute SQL commands from tsql program
=> the following commands are executed from tsql program

1 DML (insert,update,delete,merge)
2 DRL (select)
3 TCL (commit,rollback,save transaction)

SELECT stmt syntax :-

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

SELECT @var1=col1,
@var2=col2,
---------
FROM tabname
WHERE condition

example :-

SELECT @x=sal
FROM emp
WHERE empno=110

SELECT @a=ename,@b=sal
FROM emp
WHERE empno=110

=> write a prog to input empno and print name & salary ?
 DECLARE @eno int,@name varchar(10),@sal money
SET @eno=112
SELECT @name=ename,@sal=sal FROM emp WHERE empno=@eno
PRINT @name + ' ' + CAST(@sal AS VARCHAR)

=> write a prog to input empno and print employee experience ?

DECLARE @eno int,@doj date,@expr int

SET @eno=100
SELECT @doj = hiredate FROM emp WHERE empno=@eno
SET @expr = DATEDIFF(yy,@doj,getdate())
PRINT 'Experience = ' + cast(@expr as varchar) + ' years'

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

=> write a prog to input empno and increment employee sal by specific amount
and after increment if sal exceeds 5000 then cancel that increment ?

DECLARE @eno int,@amt money,@sal money

SET @eno = 107
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

16-feb-22

=> write a prog to input empno and increment employee sal as follows

if deptno=10 incr sal by 10%

20 15%
30 20%
others 5%

DECLARE @eno int,@dno int,@pct int

SET @eno = 110
SELECT @dno = deptno FROM emp WHERE empno = @eno
IF @dno=10
SET @pct=10
ELSE IF @dno=20
SET @pct=15
ELSE IF @dno=30
SET @pct=20
ELSE
SET @pct=5
UPDATE emp SET sal = sal + (sal*@pct/100) WHERE empno=@eno

=> write a prog to process bank transaction (w/d) ?

ACCOUNTS
ACCNO ACTYPE BAL
100 S 10000
101 C 20000

DECLARE @acno int,@type char(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
UPDATE accounts SET bal=bal-@amt WHERE accno=@acno
END
ELSE IF @type='d'
UPDATE accounts SET bal=bal+@amt WHERE accno=@acno
ELSE
PRINT 'invalid transaction'

=> write a prog to process money transfer ?

17-feb-22

WHILE loop :-
-------------

=> LOOPS are used to execute statements repeatedly multiple times

WHILE(condition)
begin
statements
end

if cond = true loop continues

if cond = false loop terminates

=> write a prog to print nos from 1 to 20 ?

DECLARE @a int = 1
WHILE(@a<=20)
BEGIN
PRINT @a
SET @a = @a+1
END

=> write a prog to print 2022 calendar ?

2022-01-01 ?
2022-01-02 ?

2022-12-31 ?

DECLARE @d1 date,@d2 date

SET @d1 = '2022-01-01'
SET @d2 = '2022-12-31'
WHILE(@d1<=@d2)
BEGIN
PRINT CAST(@d1 AS VARCHAR) + ' ' + DATENAME(dw,@d1)
SET @d1 = DATEADD(dd,1,@d1)
END

=> write a prog to input string and print it in following pattern ?

input :- NARESH
 output :-

N
A
R
E
S
H

DECLARE @s VARCHAR(20),@x int = 1

SET @s='NARESH'
WHILE(@x<=LEN(@s))
BEGIN
PRINT SUBSTRING(@s,@x,1)
SET @x = @x + 1
END

=> write a prog to input string and print it in following pattern ?

input :- NARESH

output :-

N
NA
NAR
NARE
NARES
NARESH

DECLARE @s VARCHAR(20),@x int = 1

SET @s='NARESH'
WHILE(@x<=LEN(@s))
BEGIN
PRINT SUBSTRING(@s,1,@x)
SET @x = @x + 1
END

=> write a prog to print the following pattern ?

*
**
***
****
*****

DECLARE @x int=1
WHILE(@x<=5)
BEGIN
PRINT REPLICATE('*',@x)
SET @x = @x + 1
END

18-feb-22

CURSOR :-
---------
 => cursors are used to access row-by-row into tsql program.

=> from tsql program if we submit a query to sql server it goes to db

and gets the data from table and copies that data into temporary
memory and using cursor we can give name to that memory and access
row-by-row into tsql program and process the row.

=> follow below steps to use cursor

1 declare cursor
2 open cursor
3 fetch records from cursor
4 close cursor
5 deallocate cursor

Declaring cursor :-
--------------------

syn :- DECLARE <NAME> CURSOR FOR SELECT statement

ex :- DECLARE C1 CURSOR FOR SELECT * FROM emp

Opening cursor :-
-------------------

syn :- OPEN <cursor-name>

ex :- OPEN C1

1 SELECT statement submitted to sql server

2 data returned by select stmt is copied to temporary memory
3 cursor c1 points to temporary memory

fetching records from cursor :-

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

=> "FETCH" statement is used to fetch records from cursor

syn :- FETCH NEXT FROM <CURSOR-NAME> INTO <VARIABLES>

ex :- FETCH NEXT FROM C1 INTO @a,@b,@c

=> FETCH statement fetches one row at a time but to process multiple rows
fetch statement should be executed multiple times , so fetch statement
should be inside a loop

Closing cursor :-
--------------------

CLOSE <cursor-name>
CLOSE c1

Deallocating cursor :-
----------------------

DEALLOCATE <cursor-name>
DEALLOCATE c1

@@FETCH_STATUS :-
-------------------
=> it is a system variable the returns status of fetch.
=> using this we can find whether fetch stmt is successful or failed
=> @@FETCH_STATUS returns

0 => fetch successful

-1 => fetch unsuccessful

=> write a prog 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

=> write a prog to increment employee salaries based on pct in raise_salary

table ?

raise_salary
empno pct
100 15
101 20
102 12
103 15

DECLARE C1 CURSOR FOR SELECT empno,pct FROM raise_salary

DECLARE @eno int,@pct int
OPEN C1
FETCH NEXT FROM C1 INTO @eno,@pct
WHILE(@@FETCH_STATUS=0)
BEGIN
UPDATE emp SET sal = sal + (sal*@pct/100) WHERE empno=@eno
FETCH NEXT FROM C1 INTO @eno,@pct
END
CLOSE C1
DEALLOCATE C1

21-feb-22

STUDENT
sno sname s1 s2 s3
1 A 80 90 70
2 B 30 60 50

RESULT
sno stot savg sres

=> write a prog to calculate all the students total,avg,result and

insert into result table ?

DECLARE C1 CURSOR FOR SELECT sno,s1,s2,s3 FROM student

DECLARE @sno int,@s1 int,@s2 int,@s3 int
DECLARE @total int,@avg decimal(5,2),@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 called forward only cursor and it supports 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.

=> forward only cursor supports only FETCH NEXT statement but scrollable
cursor supports the following fetch statements

FETCH FIRST => fetches first record

FETCH NEXT => fetches next record
FETCH PRIOR => fetches previous record
FETCH LAST => fetches last record
FETCH ABSOLUTE N => fetches Nth record from 1st record
FETCH RELATIVE N => fetches Nth record from current record

DECLARE C1 CURSOR SCROLL FOR SELECT ename FROM emp

DECLARE @name VARCHAR(10)
OPEN C1
FETCH FIRST FROM C1 INTO @name
PRINT @name
FETCH ABSOLUTE 5 FROM C1 INTO @name
PRINT @name
FETCH RELATIVE 5 FROM C1 INTO @name
PRINT @name
FETCH LAST FROM C1 INTO @name
PRINT @name
FETCH PRIOR FROM C1 INTO @name
PRINT @name
CLOSE C1
DEALLOCATE C1

=>write a prog to print every 5th record ?

DECLARE C1 CURSOR SCROLL FOR SELECT ename FROM emp

DECLARE @name VARCHAR(10)
OPEN C1
 FETCH RELATIVE 5 FROM C1 INTO @name
WHILE(@@FETCH_STATUS=0)
BEGIN
PRINT @name
FETCH RELATIVE 5 FROM C1 INTO @name
END
CLOSE C1
DEALLOCATE C1

Assignment :-
--------------

=> write to prog to print names from last to first ?

=> write a prog to calculate total sal without using sum function ?
=> write a prog to calculate max sal without using max function ?
=> write a prog to calculate min sal without using min function ?

22-feb-22

ERROR HANDLING / EXCEPTION HANDLING :-

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

=> in TSQL program if any statement causes runtime then sql server displays
error message , to replace system generated message with our own
simple and user friendly message then we need to handle that runtime
error.

=> to handle runtime error we need to include a block called TRY---CATCH block

BEGIN TRY
statement 1
statement 2
statement 3 => causes exception
statement 4
----------
END TRY
BEGIN CATCH
statements => handles exception
END CATCH

=> in try block if statement causes runtime error then control is transferred
to catch block and executes the statements in catch block

Example 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'
END CATCH

Example 2 :-

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'
END CATCH

Example 3 :-

CREATE TABLE emp66

(
empno int PRIMARY KEY,
ename VARCHAR(10) NOT NULL,
sal MONEY CHECK(sal>=3000)
)

=> write a prog to insert data into emp66 table ?

DECLARE @eno int,@name varchar(10),@sal money

BEGIN TRY
SET @eno=101
SET @name='B'
SET @sal=1000
INSERT INTO emp66 VALUES(@eno,@name,@sal)
END TRY
BEGIN CATCH
IF ERROR_NUMBER()=2627
PRINT 'eno 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 error :-

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

=> errors raised by user are called user defined errors.

=> user defined errors raised by user by using

RAISERROR(error msg,severity level,state)

severity level => 1 to 25

state => 1 to 255

=> write a prog to input empno and increment sal by specific amount but
sunday updates are not allowed ?

DECLARE @eno int,@amt money

SET @eno=110
SET @amt=1000
IF DATENAME(dw,GETDATE())='sunday'
 RAISERROR('sunday not allowed',15,1)
ELSE
UPDATE emp SET sal=sal+@amt WHERE empno=@eno

message 0 to 10
errors 11 to 20
fatal errors 21 to 25

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

23-feb-22

Named TSQL blocks :-

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

1 stored procedures
2 stored functions
3 triggers

SUB-PROGRAMS :-
-----------------

1 stored procedures
2 stored functions

Advantages :-
-------------

1 modular programming :-
-------------------------

=> with the the help of procedures & functions a big tsql program can be
divided into small modules

2 reusability :-
----------------

=> procedures & functions can be stored in db and applications which are
connected to db can reuse these programs.

3 security :-
-------------

=> because these programs are stored in db so only authorized users can
execute these programs.

4 called from front-end applications :-

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

=> procedures & functions can be called from front-end applications like
java,.net .

5 improves performance :-
-------------------------

=> TSQL programs improves performance because they are precompiled

i.e. compiled already and ready for execution. when we create a
procedure program is compiled and stored in db and whenever we
call procedure only execution is repeated but not compilation.so
 this improves performance.

STORED PROCEDURES :-
---------------------

=> a stored procedure is a named tsql block that accepts some input
performs some action on db and may or may not returns a value.

=> procedures are created to perform dml operations like insert,update,delete

=> these programs are called stored procedure because they are stored in db

CREATE OR ALTER PROCEDURE <name>

parameters if any
AS
STATEMENTS

parameters :-
--------------

=> parameters are used to recieve and send values.

=> parameters are 2 types

1 INPUT
2 OUTPUT

INPUT :-
--------

=> always recieves value

=> default

OUTPUT :-
-----------

=> always sends value

example 1 :-
-------------

create 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 ssms
2 tsql program
3 front-end applications

executing from ssms :-

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

EXECUTE raise_salary 100,1000

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 100,1000,@s OUTPUT
PRINT @s

24-feb-22

=> create a procedure for money withdrawl ?

ACCOUNTS
ACCNO ACTYPE BAL
100 S 10000
101 S 20000

CREATE OR ALTER PROCEDURE debit

@acno int,
@amt money,
@newbal money OUTPUT
AS
DECLARE @bal money,@errmsg varchar(100)
BEGIN TRY
IF NOT EXISTS (SELECT * FROM accounts WHERE accno=@acno)
RAISERROR('account does not exists',15,1)
SELECT @bal=bal FROM accounts WHERE accno=@acno
IF @amt > @bal
RAISERROR('insufficient balance',15,1)
UPDATE accounts SET bal=bal-@amt WHERE accno=@acno
SELECT @newbal=bal FROM accounts WHERE accno=@acno
END TRY
BEGIN CATCH
SET @errmsg = ERROR_MESSAGE()
RAISERROR(@errmsg,15,1)
END CATCH

execution :-

DECLARE @b money
EXECUTE debit 100,1000,@b output
print @b

Assignment :-
 create a procedure for money deposit ?

=> create a procedure for money transfer

CREATE OR ALTER PROCEDURE transfer

@sacno int,
@tacno int,
@amt money
AS
DECLARE @bal money,@msg varchar(100)
BEGIN TRY
IF NOT EXISTS (SELECT * FROM accounts WHERE accno=@sacno)
RAISERROR('source account does not exists',15,1)
IF NOT EXISTS (SELECT * FROM accounts WHERE accno=@tacno)
RAISERROR('target account does not exists',15,1)
SELECT @bal=bal FROM accounts WHERE accno=@sacno
IF @amt > @bal
RAISERROR('insufficient balance',15,1)
UPDATE accounts SET bal=bal-@amt WHERE accno=@sacno
UPDATE accounts SET bal=bal+@amt WHERE accno=@tacno
END TRY
BEGIN CATCH
SET @msg = ERROR_MESSAGE()
RAISERROR(@msg,15,1)
END CATCH

25-feb-22

USER DEFINE FUNCTIONS :-

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

=> functions created by user are called user defined 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 functions (SVF)

2 table valued functions (TVF)

scalar valued functions :-

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

=> a function is also a named TSQL block that accepts some input performs
some calculation and must return a value.

=> functions are created

1 for calculations
2 to fetch value from db

syn :-

CREATE OR ALTER
FUNCTION <NAME>(parameters if any) RETURNS <type>
AS
BEGIN
 statements
RETURN <expr>
END

example 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 tsql program
3 front-end applications

executing from sql commands :-

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

SELECT DBO.CALC(10,20,'*') => 200

Example 2 :-

PRODUCTS
prodid pname price
100 A 2000
101 B 1000
102 C 1500

ORDERS
ordid prodid qty
1000 100 2
1000 101 1
1000 102 2
1001 100 2

=> create a function to calculate order amount of particular order ?

input :- ordid = 1000

output :- amount = 8000

CREATE OR ALTER FUNCTION getOrdAmt(@d int) RETURNS money

AS
BEGIN
DECLARE C1 CURSOR FOR SELECT o.prodid,o.qty,p.price
FROM orders o INNER JOIN products p
 ON o.prodid = p.prodid
WHERE o.ordid = @d
DECLARE @pid int,@qty int,@price money,@value money,@total money=0
OPEN C1
FETCH NEXT FROM C1 INTO @pid,@qty,@price
WHILE(@@FETCH_STATUS=0)
BEGIN
SET @value = @qty * @price
SET @total = @total + @value
FETCH NEXT FROM C1 INTO @pid,@qty,@price
END
CLOSE C1
DEALLOCATE C1
RETURN @total
END

26-feb-22

TABLE VALUED FUNCTIONS :-

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

=> these functions returns records

=> return type of these functions must be table
=> TVF allows only one statement and that must be return statement
=> return statement must be select statement.
=> TVF are invoked in FROM clause

CREATE OR ALTER FUNCTION <NAME>(parameters if any) RETURNS TABLE

AS
RETURN (SELECT STATEMENT)

Example 1 :-

=> create a function that accepts deptno and returns list of employees
working for the dept ?

CREATE OR ALTER FUNCTION getEmpList(@d int) RETURNS TABLE

AS
RETURN (SELECT * FROM emp WHERE deptno=@d)

execution :-
--------------

select * from dbo.getEmpList(30)

example 2 :-

=> create a function to return top N employees based on sal ?

CREATE OR ALTER FUNCTION getTopNEmpList(@n int) RETURNS TABLE

AS
RETURN (select *
from ( select empno,ename,job,deptno,sal,
dense_rank() over (order by sal desc) as rnk
from emp ) as e
where rnk<= @n)

execution :-
------------
SELECT * FROM DBO.getTopNEmpList(5)

Assignment :-
--------------

CUSTOMERS
CUSTID NAME ADDR PHONE AADHARNO PANNO

ACCOUNTS
ACCNO ACTYPE BAL CUSTID

TRANSACTIONS
TRID TTYPE TDATE TAMT ACCNO

=> create following procedures & functions to implement various bank transactions

1 account opening (procedure)

2 account closing (procedure)
3 money deposit (procedure)
4 money withdrawl (procedure)
5 money transfer (procedure)
6 balance enquiry (scalar valued function)
7 statement of particular customer between two given dates (table valued function)
8 latest N transactions of particular customers (table valued function)

=> 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,date

3 return expr is a scalar variable return expr is select statement

4 called in select clause called in from clause

=> difference between procedures and functions ?

procedures functions

1 may or may not returns a value must return a value

2 can return multiple values returns one value

3 returns values using OUTPUT returns value using return

statement
parameter

4 we can execute dml commands we can't execute dml commands

5 cannot be called from select stmt can be called from select stmt

6 created to perform one or more created to perform calculations

actions like insert,update,delete
on db
7 create procedure to update balance create function to get balance

Droping :-
-----------

DROP PROCEDURE raise_salary

DROP FUNCTION DBO.CALC

28-feb-22

TRIGGERS :-
-----------

=> a trigger is also a named TSQL block like procedure but executed implicitly
by sql server whenever user submits DML/DDL commands.

=> triggers are created

1 to control dmls/ddls
2 to enforce complex rules & validations
3 to audit tables
4 to generate values for primary key columns
5 to manage replicas

syntax :-

CREATE OR ALTER TRIGGER <NAME>

ON <TABNAME>
AFTER/INSTEAD OF INSERT,UPDATE,DELETE
AS
STATEMENTS

AFTER trigger :-
----------------

=> 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.

example 1 :- 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

example 2 :- create trigger to not to allow dmls on emp table as follows

 mon - fri <10am and >4pm
sat <10am and >2pm
sun ---------------

CREATE OR ALTER TRIGGER T2

ON EMP
AFTER INSERT,UPDATE,DELETE
AS
IF DATEPART(dw,GETDATE()) BETWEEN 2 AND 6
AND
DATEPART(hh,GETDATE()) NOT BETWEEN 10 AND 15
BEGIN
ROLLBACK
RAISERROR('only between 10am and 4pm',15,1)
END
ELSE IF DATEPART(dw,GETDATE())=7
AND
DATEPART(hh,GETDATE()) NOT BETWEEN 10 AND 13
BEGIN
ROLLBACK
RAISERROR('only between 10am and 2pm',15,1)
END
ELSE IF DATEPART(dw,GETDATE())=1
BEGIN
ROLLBACK
RAISERROR('sunday not allowed',15,1)
END

example3 :- create trigger to not to allow to update empno ?

CREATE OR ALTER TRIGGER T3

ON EMP
AFTER UPDATE
AS
IF UPDATE(empno)
BEGIN
ROLLBACK
RAISERROR('empno cannot be updated',15,1)
END

02-mar-22

Magic Tables :-
-----------------

1 INSERTED
2 DELETED

=> by using these two tables we can access the data in triggers affected
by dmls.

=> record user is trying to insert is copied to INSERTED table.

=> record user is trying to delete is copied to DELETED table.

=> record user is trying to update is copied to both INSERTED & DELETED tables

INSERT INTO emp(empno,ename,sal) VALUES(100,'A',5000) => INSERTED

empno ename sal
 100 A 5000

DELETE FROM EMP WHERE EMPNO=110 => DELETED

EMPNO ENAME SAL
110 KING 5000

UPDATE EMP SET SAL=4000 WHERE EMPNO=100 => INSERTED

EMPNO ENAME SAL
100 A 4000

DELETED
EMPNO ENAME SAL
100 A 5000

=> create a trigger to not to allow to decrement salary ?

CREATE OR ALTER TRIGGER T4

ON EMP
AFTER UPDATE
AS
DECLARE @NEWSAL MONEY,@OLDSAL MONEY
SELECT @NEWSAL=SAL FROM INSERTED
SELECT @OLDSAL=SAL FROM DELETED
IF @NEWSAL < @OLDSAL
BEGIN
ROLLBACK
RAISERROR('sal cannot be decremented',15,1)
END

UPDATE EMP SET SAL=2000 WHERE EMPNO = 107 => ERROR

=> create trigger to insert details into emp_resign whenever employee

resigns from organization ?

EMP_RESIGN
EMPNO ENAME HIREDATE DOR

CREATE TABLE emp_resign

(
empno int,
ename varchar(10),
hiredate date,
dor date
)

CREATE OR ALTER TRIGGER T5

ON EMP
AFTER DELETE
AS
DECLARE @eno int,@name varchar(10),@hire date
SELECT @eno=empno,@name=ename,@hire=hiredate FROM DELETED
INSERT INTO emp_resign VALUES(@eno,@name,@hire,GETDATE())

Testing :-

DELETE FROM EMP WHERE EMPNO=110

INSTEAD OF trigger :-
---------------------
EMP99
ENO ENAME SAL BONUS

create table emp99

(
eno int,
ename varchar(10),
sal money,
bonus money
)

=> create trigger to generate eno and bonus for new employee ?

bonus = 10% on sal

INSERT INTO emp99(ename,sal) VALUES('A',5000)

CREATE OR ALTER TRIGGER T6

ON EMP99
INSTEAD OF INSERT
AS
DECLARE @ename VARCHAR(10),@sal MONEY,@eno int,@bonus money
SELECT @ename=ename,@sal=sal FROM INSERTED
SET @bonus = @sal*0.1
SELECT @eno=ISNULL(MAX(eno),99)+1 FROM emp99
INSERT INTO emp99 VALUES(@eno,@ename,@sal,@bonus)