sql server demo
1 SQL (structured query language)
2 T-SQL (transact-sql)
Database :-
=> a db is a organized colllection 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,accounts,trans and loans etc.
Types of Databases :-
1 OLTP DB (online transaction processing)
2 OLAP DB (online analytical processing)
=> organizations uses OLTP for storing day-to-day transactions and OLAP for
analysis.
=> OLTP is for running business and OLAP is for 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 manage database.
=> It is an interface between user and database.
USER
Evolution of DBMS :-
1960 FMS (File Mgmt System)
1970 HDBMS (Hierarchical DBMS)
NDBMS (Network DBMS)
1980 RDBMS (Relational DBMS)
1990 ORDBMS (Object Relational DBMS)
RDBMS :-
=> RDBMS is based on relational model (structure of the data)
=> relational model introduced by E.F.CODD
=> E.F.CODD introduced 12 rules called codd rules
=> a db that supports all 12 rules called perfect rdbms
Information rule :-
=> according to information rule data must be organized in tables i.e. rows and cols
CUST
CUSTID NAME ADDR
10 SACHIN MUM
11 VIJAY HYD
12 RAHUL DEL
Database = collection of tables
Table = collection of rows and cols
Row = collection of field values
Column = collection of values of one field
=> every table must contain primary key to uniquely identify the records.
ex :- accno,empid,aadharno,panno,voterid
RDBMS features :-
1 easy to access and manipulate data.
2 less redundency (duplication of data).
3 more security
4 gurantees data quality or data integrity
5 supports data sharing
6 supports transactions
RDBMS softwares :-
SQL Databases :-
sql server from microsoft
oracle from oracle corp
mysql from oracle corp
db2 from ibm
postgresql from postgresql
rds from amazon
NoSQL Databases :-
MongoDB
cassandra
ORDBMS :-
=> object relational dbms
=> It is the combination of rdbms & oops
ORDBMS = RDBMS + OOPS (reusability)
=> rdbms doesn't support reusability but ordbms supports reusability
ex :-
sql server
oracle
postgresql
what is sql server ?
=> sql server is basically rdbms software from microsoft and also supports
features of ordbms and used to create and to manage database.
DB Development Life Cycle :-
----------------------------------------
Analyze
Design
Develop
Test
Deploy
Maintain
Design :-
------------
=> Designing db means designing tables
=> DB is designed by DB Designers / Architects
=> DB is designed by using
1 ER Model (Entity Relationship)
2 Normalization
Development :-
---------------------
=> DB is developed by Developers & DBAs by using any rdbms tools like
sql server.
Developer DBA (DB Admin)
creating tables installation of sql server
creating views creating database
creating synonyms creating logins
creating sequences db backup & restore
creating indexes db export & import
creating procedures db upgradation & migration
creating functions performance tuning
creating triggers
writing queries
sql server 2014 -----------> sql server 2022 upgradation
mysql --------------------------> sql server migration
04-JUL-24
Testing :-
------------
=> DB is tested by QA team (Quality Assurance)
=> DB is tested by using
1 manual
2 automation by using tools like selenium
Deployment :-
------------------
=> moving db from dev environment to prod enviroment is called deployment.
=> once db is deployed then end user can use the db for day-to-day operations.
summary :-
---------------
what is db ?
what is dbms ?
what is rdbms ?
what is ordbms ?
what is db development life cycle ?
==============================================================
SQL SERVER
===========
=> sql server is a rdbms product from microsoft and als supports ordbms features
and used to create and to manage database.
=> sql server is used by both developers & DBAs
versions :-
---------------
sql server versions :-
----------------------------
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 2022 2022
client / server architecture :-
---------------------------------------
1 server
2 client
server :-
------------
=> server is a system where sql server is installed and running
=> inside the server sql server manages
1 DB
2 INSTANCE
=> DB is created in hard disk and acts as permanent storage.
=> INSTANCE is created in ram and acts as temporary storage.
CLIENT :-
---------------
=> client is also a system from where users can
1 connects to server
2 submit requests to server
3 receive response from server
client tool :-
----------------
SSMS (SQL SERVER MGMT STUDIO)
SQL :-
---------
=> SQL stands for structured query language
=> It is a language used to communicate with sql server
=> user communicates with sql server by sending commands called queries
=> a query is a command or instruction submitted to sql server to
perform some operation over db.
=> sql is originally introduced by IBM and initial name of this language was
"SEQUEL" and later it is renamed to SQL.
=> SQL is common to all relational database softwares
user----ssms----------------sql------------------------sql server------------db
user----sqlplus--------------sql-------------------------oracle---------------db
user----mysqlworkbench---------sql-----------------mysql--------------db
=> based on operations over db sql is categorized into following sublanguages
DDL (Data Definition Lang)
DML (Data Manipulation Lang)
DQL (Data Query Lang)
TCL (Transaction Control Lang)
DCL (Data Control Lang)
5-jul-24
SQL
DDL DML DQL TCL DCL
create insert select commit grant
alter update rollback revoke
drop delete save transaction
truncate merge
DATA & DATA DEFINITION :-
----------------------------------------
EMPID ENAME SAL => DATA DEFINITION / METADATA
100 A 5000 => DATA
How to connect to sql server :-
-----------------------------------------
=> to connect to sql server open ssms and enter following details
server type :- DB Engine
server name :- DESKTOP-G2DM7GI
authentication :- sql server / windows
login :- SA (system admin)
password :- 123
=> click connect
How to create Database :-
-----------------------------------
=> In object explorer select Databases => New Database
Enter Database Name :- DB730
=> click OK
=> a new 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 (C,U,D,I----)
NAME TYPE SIZE AUTOGROWTH PATH
DB730 DATA 8MB 64MB C:\-------
DB730_LOG LOG 8MB 64MB C:\-------
Command to create new Database :-
---------------------------------------------------
=> to create new database open master database and execute the following command
CREATE DATABASE BANKDB
6-jul-24
Download & Install :-
----------------------------
1 sql server
2 ssms
download sql server :-
------------------------------
https://www.microsoft.com/en-in/sql-server/sql-server-downloads
installation :-
------------------
https://www.mssqltips.com/sqlservertip/7313/install-sql-server-2022/
download ssms :-
-----------------------
https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-ver16
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 numeric(p,s) money time varbinary
char nchar int datetime varbinary(max)
varchar nvarchar bigint
varchar(max) nvarchar(max) numeric(p)
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 columns and char is recommended for fixed length
columns
ex :- GENDER CHAR(1)
M
F
STATE_CODE CHAR(2)
AP
TG
COUNTRY_CODE CHAR(3)
IND
USA
varchar(size) :-
---------------------
=> allows character data upto 8000 chars
=> recommended for variable length fileds
ex :- NAME VARCHAR(10)
SACHIN - - - -
released
RAVI - - - - - -
released
=> char / varchar allows ascii chars (256 chars) that includes a-z,A-Z,0-9
and special chars.
ex :- PANNO CHAR(10)
VEHNO CHAR(10)
EMAILID VARCHAR(20)
varchar(max) :-
----------------------
=> allows character data upto 2GB.
ex :- REVIEW VARCHAR(MAX)
08-JUL-24
nchar / nvarchar / nvarchar(max) :- ( n => national)
---------------------------------------------
=> allows unicode chars (65536 chars) that includes all ascii chars and
chars belongs to different languages.
ex :- text nvarchar(100)
=> a unicode char occupies 2 bytes
integer types :-
-------------------
=> integer types allows numbers without decimal
type bytes range
tinyint 1 0 to 255
smallint 2 -32768 to 32767
int 4 -2^31 to 2^31-1 (-2147483647 to 2147483647)
bigint 8 -2^63 to 2^63-1 (-9,223,372,036,854,775,807 to 9,223,372,036,854,775,807)
ex :- age tinyint
cid smallint
numeric(p) :-
-----------------
=> allows numbers upto 38 digits
=> allows numbers without decimal
ex :- empid numeric(4)
10
100
1000
10000 => not allowed
aadharno numeric(12)
mobile numeric(10)
accno numeric(12)
decimal(p,s) / numeric(p,s) :-
--------------------------------------
=> allows numbers with decimal.
p => precision => total no of digits allowed => it can be upto 38
s => scale => no of digits allowed after decimal => it can be between 0 to p
ex :- salary decimal(7,2)
5000
5000.55
50000.55
500000.55 => invalid
5000.5678 => allowed => 5000.57
5000.5638 => allowed => 5000.56
balance decimal(11,4)
avg decimal(5,2)
currency types :-
----------------------
=> currency types are used for fields related to money
type bytes range
smallmoney 4 -214748.3648 to 214748.3647
money 8 -922,337,203,685,477.5808
to
922,337,203,685,477.5807
ex :- salary smallmoney
balance money
date & time :-
-------------------
DATE => allows only date
TIME => allows only time
DATETIME => allows date & time (jan 1 0001 to dec 31 9999)
=> default date format in sql server is yyyy-mm-dd
=> default time format is hh:mm:ss
ex :- DOB DATE
1995-10-15
LOGIN TIME
9:30:00
TXNDT DATETIME
2024-07-07 10:00:00
Binary types :-
------------------
=> binary types are used for storing binary data that includes audio,video,images
binary => allows fixed length binary data upto 8000 bytes
varbinary => allows variable length binary data upto 8000 bytes
varbinary(max) => allows variable length binary data upto 2GB
ex :- photo binary(1000)
photo varbinary(1000)
selfie varbinary(max)
==============================================================
CREATING TABLE IN SQL SERVER DB :-
--------------------------------------------------------
CREATE TABLE <TABNAME>
(
COLNAME DATATYPE(SIZE) ,
COLNAME DATATYPE(SIZE),
-----------------------
)
Rules :-
1 name should start with alphabet
2 name should not contain spaces & special chars but allows _ $ #
3 name can be upto 128 chars
4 table can have 1024 cols
5 no of rows unlimited
ex :- 123cust invalid
cust 123 invalid
cust*123 invalid
cust_123 valid
9-jul-24
=> create table with following strcture ?
EMP
EMPID ENAME JOB SAL HIREDATE DEPT
CREATE TABLE EMP
(
EMPID TINYINT ,
ENAME VARCHAR(10),
JOB VARCHAR(10),
SAL SMALLMONEY,
HIREDATE DATE ,
DEPT VARCHAR(10)
)
=> above command creates table structure / definition / metadata
that includes columns,datatype and size.
sp_help :- (sp => stored procedure)
---------------
=> command to see the structure of the table
sp_help <tabname>
ex :-
sp_help emp
EMPID tinyint 1
ENAME varchar 10
JOB varchar 10
SAL smallmoney 4
HIREDATE date 3
DEPT varchar 10
inserting data into table :-
--------------------------------
=> "insert" command is used to insert data into table.
=> insert command creates a row
=> using insert command we can insert
1 single row
2 multiple rows
inserting single row :-
---------------------------
INSERT INTO <tabname> VALUES(v1,v2,v3,------)
ex :-
INSERT INTO EMP VALUES(100,'sachin','clerk',4000,'2024-07-09','hr')
INSERT INTO EMP VALUES(101,'arvind','manager',8000,getdate(),'it')
inserting multiple rows :-
----------------------------------
INSERT INTO EMP VALUES(102,'david','analyst',6000,'2020-10-15','sales') ,
(103,'rahul','clerk',5000,'2019-05-10','it')
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,'kumar',null,null,'2018-09-05','hr')
method 2 :-
-----------------
INSERT INTO EMP(EMPID,ENAME,HIREDATE,DEPT)
VALUES(105,'vijay','2019-02-10','sales')
remaining two fields job,sal filled with nulls
Operators in sql server :-
----------------------------------
Arithmetic Operators => + - * / %
Relational Operators => > >= < <= = <> !=
Logical Operators => AND OR NOT
Special Operators => BETWEEN
IN
LIKE
IS
ANY
ALL
EXISTS
PIVOT
Set Operators => UNION
UNION ALL
INTERSECT
EXCEPT
Displaying Data :-
-------------------------
=> "SELECT" command is used to display data from table
=> we can display all rows or specific rows
=> we can display all cols or specific cols
SELECT columns / * FROM tabname
SQL = ENGLISH
QUERIES = SENTENCES
CLAUSES = WORDS
* => all columns
=> display employee names and salaries ?
SELECT ENAME,SAL FROM EMP
=> display names,jobs and hiredates ?
SELECT ENAME,JOB,HIREDATE FROM EMP
=> display all the data from emp table ?
SELECT * FROM EMP
10-jul-24
WHERE clause :-
-----------------------
=> where clause is used to apply filter conditions
=> where clause is used to get specific rows from table
SELECT columns / *
FROM tabname
WHERE condition
condition :-
-----------------
COLNAME OP VALUE
=> OP must be any relational operator like > >= < <= = <> !=
=> if cond = true row is selected , if false row is not selected
examples :-
=> display employee details whose id = 103 ?
SELECT * FROM EMP WHERE EMPID = 103
=> display employee details whose name = kumar ?
SELECT * FROM EMP WHERE ENAME = 'kumar'
=> display employee details earning more than 5000 ?
SELECT * FROM EMP WHERE SAL > 5000
=> employees joined after 2020 ?
SELECT * FROM EMP WHERE HIREDATE > 2020 => ERROR
SELECT * FROM EMP WHERE HIREDATE > '2020-12-31'
=> employees joined before 2020 ?
SELECT * FROM EMP WHERE HIREDATE < '2020-01-01'
=> employees not belongs to hr dept ?
SELECT * FROM EMP WHERE DEPT <> 'hr'
compound condition :-
--------------------------------
=> multiple conditions combined with AND / OR operators is called compound
condition.
WHERE COND1 AND COND2 RESULT
T T T
T F F
F T F
F F F
WHERE COND1 OR COND2 RESULT
T T T
T F T
F T T
F F F
=> employees working as clerk,manager ?
SELECT * FROM EMP WHERE JOB='clerk' OR JOB='manager'
=> employees whose id = 100,103,105 ?
SELECT * FROM EMP WHERE EMPID=100 OR EMPID = 103 OR EMPID=105
=> employees working for hr dept and working as clerk ?
SELECT * FROM EMP WHERE DEPT='hr' AND JOB='clerk'
=> employees earning more than 5000 and less than 10000 ?
SELECT * FROM EMP WHERE SAL>5000 AND SAL<10000
=> employees joined in 2020 year ?
SELECT *
FROM EMP
WHERE HIREDATE >= '2020-01-01'
AND
HIREDATE <= '2020-12-31'
=> employees working as clerk,manager and earning more than 5000 ?
SELECT *
FROM EMP
WHERE JOB='clerk'
OR
JOB='manager'
AND
SAL > 5000
=> above query returns clerk records earning less than 5000 because
sal > 5000 is applied only to manager but not to clerk because
operator AND has got more priority then operator OR , to control this
use () .
SELECT *
FROM EMP
WHERE (
JOB='clerk'
OR
JOB='manager'
)
AND
SAL > 5000
=>
STUDENT
SID SNAME S1 S2 S3
1 A 80 90 70
2 B 30 60 50
=> list of students who are passed ?
SELECT * FROM STUDENT WHERE S1>=35 AND S2>=35 AND S3>=35
=> list of students who are failed ?
SELECT * FROM STUDENT WHERE S1<35 OR S2<35 OR S3<35
IN operator :-
-------------------
=> use IN operator for list comparision
=> use IN operator for "=" comprision with multiple values
WHERE COLNAME = V1 , V2 , V3 ---- => INVALID
WHERE COLNAME IN (V1,V2,V3,---)
=> employees whose id = 100,103,105 ?
SELECT * FROM EMP WHERE EMPID IN (100,103,105)
=> employees working as clerk,manager ?
SELECT * FROM EMP WHERE JOB IN ('clerk','manager')
=> employees not working for dept it,sales ?
SELECT * FROM EMP WHERE DEPT NOT IN ('it','sales')
11-jul-24
BETWEEN operator :-
------------------------------
=> use between opertor for range comparision.
WHERE COLNAME BETWEEN V1 AND V2 (COL>=V1 AND COL<=V2)
=> employees earning between 5000 and 10000 ?
SELECT *
FROM EMP
WHERE SAL BETWEEN 5000 AND 10000
=> employees joined in 2020 year ?
SELECT *
FROM EMP
WHERE HIREDATE BETWEEN '2020-01-01' AND '2020-12-31'
=> employees not joined in 2020 ?
SELECT *
FROM EMP
WHERE HIREDATE NOT BETWEEN '2020-01-01' AND '2020-12-31'
=> employees working as clerk,manager and earning between 5000 and 10000
and not joined in 2020 and not working for dept hr,sales ?
SELECT *
FROM EMP
WHERE JOB IN ('clerk','manager')
AND
SAL BETWEEN 5000 AND 10000
AND
HIREDATE NOT BETWEEN '2020-01-01' AND '2020-12-31'
AND
DEPT NOT IN ('hr','sales')
=> list of samsung,redmi,realme mobile phones price between 10000 and 20000 ?
products
prodid pname price category brand
SELECT *
FROM PRODUCTS
WHERE BRAND IN ('samsung','redmi','realme')
AND
CATEGORY='mobiles'
AND
PRICE BETWEEN 10000 AND 20000
=> list of male customers staying in hyd,mum,blr age between 20 and 40 ?
CUST
CID NAME GENDER AGE CITY STATE
SELECT *
FROM CUST
WHERE GENDER= 'M'
AND
CITY IN ('hyd','mum','blr')
AND
AGE BETWEEN 20 AND 40
LIKE operator :-
----------------------
=> use LIKE operator for pattern comparision
ex :- name starts with 's'
name ends with 'd'
name contains 'a'
WHERE COLNAME LIKE 'PATTERN'
=> pattern may contain alphabets,digits,special chars and wildcard chars
wildcard chars :-
----------------------
% => 0 or many chars
_ => exactly 1 char
=> employees name starts with 's' ?
SELECT * FROM EMP WHERE ENAME LIKE 's%'
=> employees name ends with 'd' ?
SELECT * FROM EMP WHERE ENAME LIKE '%d'
=> employees name contains 'a' ?
SELECT * FROM EMP WHERE ENAME LIKE '%a%'
=> where 'a' is the 4th char in their name ?
SELECT * FROM EMP WHERE ENAME LIKE '___a%'
=> where 'a' is the 4th char from last ?
SELECT * FROM EMP WHERE ENAME LIKE '%a___'
=> employees joined in oct month ? YYYY-MM-DD
SELECT * FROM EMP WHERE HIREDATE LIKE '_____10___'
=> employees joined in 2020 year ?
SELECT * FROM EMP WHERE HIREDATE LIKE '2020%'
=> employees joined in 1st 9 days of any month in any year ?
SELECT * FROM EMP WHERE HIREDATE LIKE '%0_'
=>
SELECT *
FROM EMP
WHERE JOB IN ('clerk','man%')
A ERROR
B RETURNS clerk,manager
C RETURNS only clerk
D NONE
ANS :- C
WHERE JOB='clerk' OR JOB LIKE 'man%'
ANS :- B
=>
SELECT *
FROM EMP
WHERE SAL BETWEEN 10000 AND 5000
A ERROR
B RETURNS ROWS
C RETURNS NO ROWS
D NONE
ANS :- C
12-jul-24
=> employees name starts with 'a' , 'k', 'v' ?
SELECT * FROM EMP WHERE ENAME LIKE 'a%'
OR
ENAME LIKE 'k%'
OR
ENAME LIKE 'v%'
SELECT * FROM EMP WHERE ENAME LIKE '[akv]%'
=> employees name starts between 'a' and 'p' ?
SELECT * FROM EMP WHERE ENAME LIKE '[a-p]%'
=>
CUST
CID NAME
10 sachin_tendulkar
11 virat%kohli
12 mahendra_singh_dhoni
=> list of customers name contains "_" ?
SELECT * FROM CUST WHERE CNAME LIKE '%_%'
=> above query returns all customer records because "_" is treated as
wildcard char but not treated as search char , to overcome this
problem use ESCAPE char.
SELECT * FROM CUST WHERE CNAME LIKE '%\_%' ESCAPE '\'
=> name contains "%" ?
SELECT * FROM CUST WHERE CNAME LIKE '%\%%' ESCAPE '\'
=> name contains 2 "_" ?
SELECT * FROM CUST WHERE CNAME LIKE '%\_%\_%' ESCAPE '\'
IS operator :-
-----------------
=> use IS operator for NULL comparision
WHERE COLNAME IS NULL
WHERE COLNAME IS NOT NULL
=> employees not earning sal ?
SELECT * FROM EMP WHERE SAL IS NULL
=> employees earning salary ?
SELECT * FROM EMP WHERE SAL IS NOT NULL
summary :-
WHERE COLNAME IN (V1,V2,V3,---)
WHERE COLNAME BETWEEN V1 AND V2
WHERE COLNAME LIKE 'PATTERN'
WHERE COLNAME IS NULL
ALIAS :-
-----------
=> alias means another name or alternative name
=> used to change column heading
COLNAME / EXPR [AS] ALIAS
Ex :-
=> display ENAME ANNUAL SAL ?
SELECT ENAME,SAL*12 AS ANNSAL FROM EMP
SELECT ENAME,SAL*12 AS [ANNUAL SAL] FROM EMP
=> display ENAME SAL HRA DA TAX TOTSAL ?
HRA = house rent allowance = 20% on sal
DA = dearness allowance = 30% on sal
TAX = 10% on sal
TOTSAL = SAL + HRA + DA - TAX
SELECT ENAME,SAL,
SAL*0.2 AS HRA,
SAL*0.3 AS DA,
SAL*0.1 AS TAX,
SAL+(SAL*0.2) + (SAL*0.3) - (SAL*0.1) AS TOTSAL
FROM EMP
sachin 4000 800 1200 400 5600
=> display SNO TOTAL AVG ?
STUDENT
SNO SNAME S1 S2 S3
1 A 80 90 70
2 B 30 60 50
SELECT SNO,
S1+S2+S3 AS TOTAL,
(S1+S2+S3)/3 AS AVG
FROM STUDENT
13-jul-24
ORDER BY clause :-
-----------------------------
=> order by clause is used to sort table data.
=> order by is based on one or more columns
=> we can sort either in ascending or in descending
=> default order is ascending
SELECT columns
FROM tabname
[WHERE cond]
[ORDER BY col1 ASC/DESC , col2 ASC/DESC,----]
=> arrange employee list name wise asc ?
SELECT *
FROM EMP
ORDER BY ENAME ASC
=> arrange employee list hiredate wise asc ?
SELECT *
FROM EMP
ORDER BY HIREDATE ASC
=> arrange employee list dept wise asc and with in dept sal wise desc ?
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
ORDER BY DEPTNO ASC , SAL DESC
1 A 5000 20 4 D 5000 10
2 B 3000 10 2 B 3000 10
3 C 4000 30 =============> 5 E 6000 20
4 D 5000 10 1 A 5000 20
5 E 6000 20 3 C 4000 30
=> arrange employee list dept wise asc and with in dept hiredate wise asc ?
SELECT EMPNO,ENAME,HIREDATE,DEPTNO
FROM EMP
ORDER BY DEPTNO ASC , HIREDATE ASC
=> arrange student list avg wise desc ,m desc,p desc ?
student
sid sname m p c
1 A 80 90 70
2 B 60 70 50
3 C 90 70 80
4 D 90 80 70
SELECT * , (M+P+C)/3 AS AVG
FROM STUDENT
ORDER BY (M+P+C)/3 DESC , M DESC , P DESC
4 D 90 80 70 80
3 C 90 70 80 80
1 A 80 90 70 80
2 B 60 70 50 60
CREATE TABLE STUDENT
( SID INT,SNAME VARCHAR(10),M TINYINT,P TINYINT,C TINYINT)
INSERT INTO STUDENT
VALUES(1,'A',80,90,70),
(2,'B',60,70,50),
(3,'C',90,70,80),
(4,'D',90,80,70)
=> display employee list working as clerk,manager and arrange output
sal wise desc order ?
SELECT *
FROM EMP
WHERE JOB IN ('CLERK','MANAGER')
ORDER BY SAL DESC
NOTE :-
=> In order by we can use column name or column number
=> column number is not based on table and it is based on select list
ex 1 :-
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
ORDER BY 3 DESC => sorts based on 3rd column in select list i.e. sal
ORDER BY 2 ASC => sorts based on 2nd column in select list i.e. ename
ORDER BY 5 ASC => ERROR
DISTINCT clause :-
--------------------------
=> distinct clause eliminates duplicates
SELECT DISTINCT COLNAME
SELECT DISTINCT COL1,COL2
SELECT DISTINCT *
Ex :-
SELECT DISTINCT JOB FROM EMP
ANALYST
CLERK
MANAGER
PRESIDENT
SALESMAN
SELECT DISTINCT DEPTNO FROM EMP
10
20
30
TOP clause :-
--------------------
=> returns top N rows from table
SELECT TOP <n> columns / *
FROM tabname
[WHERE cond]
[ORDER BY ---]
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 employees based on experience ?
SELECT TOP 5 *
FROM EMP
ORDER BY HIREDATE ASC
=> display top 3 max salaries ?
SELECT DISTINCT TOP 3 SAL
FROM EMP
ORDER BY SAL DESC
summary :-
WHERE => to select specific rows from table
ORDER BY => to sort table data
DISTINCT => to eliminate duplicates
TOP => to select top n rows
15-JUL-24
DML commands :- (Data Manipulation Lang)
-------------------------
INSERT
UPDATE
DELETE
MERGE
=> all DML commands acts on table data.
=> all DML operations are auto committed (saved).
=> to stop auto commit execute the following command
SET IMPLICIT_TRANSACTIONS ON
=> to save the operation execute COMMIT.
=> to cancel the operation execute ROLLBACK.
UPDATE command :-
-----------------------------
=> command used to modify table data.
=> we can update all rows or specific rows
=> we can update single column or multiple columns
syn :- UPDATE tabname
SET colname = value , colname = value ,-------------
[WHERE cond]
=> update all employees comm with 500 ?
UPDATE EMP SET COMM = 500
=> update employees comm with 500 whose comm = null ?
UPDATE EMP SET COMM = 500 WHERE COMM = NULL
=> update employees comm with null whose comm <> null ?
UPDATE EMP SET COMM = NULL WHERE COMM IS NOT NULL
NULL assignment =
NULL comparision IS
=> update 7369 employee sal with 1000 and comm with 500 ?
UPDATE EMP
SET SAL = 1000 , COMM = 500
WHERE EMPNO = 7369
=> increment sal by 10% and comm by 20% those working as salesman
and joined in 1981 year ?
UPDATE EMP
SET SAL = SAL + (SAL*0.1) , COMM = COMM + (COMM*0.2)
WHERE JOB='SALESMAN'
AND
HIREDATE LIKE '1981%'
=> transfer employees from 10th dept to 20th dept ?
UPDATE EMP
SET DEPTNO = 20
WHERE DEPTNO = 10
DELETE command :-
-----------------------------
=> command used to delete row/rows from table
=> we can delete all rows or specific rows
syn :- DELETE FROM <tabname> [WHERE cond]
ex :-
=> delete all rows from emp table ?
DELETE FROM EMP
=> delete employees joined in 2nd quarter of 1981 year ?
DELETE
FROM EMP
WHERE HIREDATE BETWEEN '1981-04-01' AND '1981-06-30'
DDL commands :- (Data Definition Lang)
-----------------------
CREATE
ALTER
DROP
TRUNCATE
=> DDL commands acts on table structure
=> DDL commands are auto committed.
=> to stop auto commit execute the following command
SET IMPLICIT_TRANSACTIONS ON
16-jul-24
ALTER command :-
----------------------------
=> command used to modify table structure
=> using alter command we can
1 add columns
2 drop columns
3 modify column
changing datatype
changing size
Adding columns :-
-------------------------
ALTER TABLE tabname
ADD colname datatype(size) , colname datatype(size) , -----------
ex :-
=> add column gender to emp table ?
ALTER TABLE EMP
ADD GENDER CHAR(1)
after adding by default the new column is filled with nulls , so to insert data into the new
column use update command.
UPDATE EMP SET GENDER='M' WHERE EMPNO = 7369
Droping columns :-
--------------------------
ALTER TABLE tabname
DROP COLUMN col1,col2,--------
ex :-
=>drop column gender from emp ?
ALTER TABLE EMP
DROP COLUMN GENDER
Modifying a column :-
------------------------------
ALTER TABLE tabname
ALTER COLUMN colname datatype(size)
1 changing datatype
2 changing size
=> change the datatype of empno to int ?
ALTER TABLE EMP
ALTER COLUMN EMPNO INT
=> change datatype of comm to smallmoney ?
ALTER TABLE EMP
ALTER COLUMN COMM SMALLMONEY
=> increase the size of ename to 20 ?
ALTER TABLE EMP
ALTER COLUMN ENAME VARCHAR(20)
ALTER TABLE EMP
ALTER COLUMN ENAME VARCHAR(5) => ERROR => some names contains more than 5 chars
DROP command :-
-------------------------
=> command used drop the table from db.
=> drops table structure along with data.
DROP TABLE tabname
ex :-
DROP TABLE EMP
TRUNCATE command :-
---------------------------------
=> deletes all the data from table but keeps structure
=> will empty the table
=> releases the memory allocated for table
TRUNCATE TABLE tabname
ex :-
TRUNCATE TABLE STUDENT
DELETE VS TRUNCATE :-
------------------------------------
DELETE TRUNCATE
1 DML DDL
2 can delete all rows and can delete only all rows
also specific rows but cannot delete specific rows
3 where cond can be where cond cannot be
used with delete used with truncate
4 deletes row-by-row deletes all rows at a time
5 slower faster
6 will not release memory releases memory
7 will not reset identity will reset identity
SP_RENAME :-
--------------------
=> command used to change table name and column name
SP_RENAME 'oldname', 'newname'
ex :-
=> rename table student to stud ?
SP_RENAME 'STUDENT','STUD'
=> rename column M to MATHS in student table ?
SP_RENAME 'STUD.M','MATHS'
Built-in functions in SQL SERVER :-
-------------------------------------------------
=> functions are used for data processing / calculations / analysis
Types of functions :-
---------------------------
1 DATE
2 STRING
3 NUMERIC
4 CONVERSION
5 SPECIAL
6 ANALYTICAL
7 AGGREGATE
DATE functions :-
--------------------------
GETDATE() :-
---------------------
=> returns current date , time and milliseconds
SELECT GETDATE() => 2024-07-16 08:48:30.100
-------------- ----------- -----
DATE TIME MS
DATEPART() :-
--------------------
=> used to extract part of the date
DATEPART(INTERVAL,DATE)
EX :-
SELECT DATEPART(YY,GETDATE()) => 2024
MM => 7
DD => 16 (out of 30/31)
DW => 4 (out of 7)
DY => 199 (out of 365)
QQ => 3 (1-4)
HH => hour
MI => minutes
SS => seconds
JAN-MAR 1
APR-JUN 2
JUL-SEP 3
OCT-DEC 4
=> employees joined in 1980,1983,1985 ?
SELECT *
FROM EMP
WHERE DATEPART(YY,HIREDATE) IN (1980,1983,1985)
=> display employees joined in leap year ?
SELECT *
FROM EMP
WHERE DATEPART(YY,HIREDATE)%4 = 0
=> display employees joined in jan,apr,dec months ?
SELECT *
FROM EMP
WHERE DATEPART(MM,HIREDATE) IN (1,4,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(QQ,HIREDATE) = 2
AND
DATEPART(YY,HIREDATE) = 1981
DATENAME() :-
---------------------
=> similar to datepart used to extract part of the date
DW MM
DATEPART 4 7
DATENAME Wednesday July
=> display ENAME DAY ?
SELECT ENAME,DATENAME(DW,HIREDATE) AS DAY
FROM EMP
=> write a query to display on which day india got independence ?
SELECT DATENAME(DW,'1947-08-15')
YEAR / MONTH / DAY :-
---------------------------------
YEAR() => returns year part => YEAR(GETDATE()) => 2024
MONTH() => returns month part => MONTH(GETDATE()) => 7
DAY() => returns day part => DAY(GETDATE()) => 17
FORMAT() :-
----------------
=> used to display date in different formats
FORMAT(date , 'format')
ex :-
SELECT FORMAT(GETDATE(),'dd-MM-yyyy') => 17-07-2024
SELECT FORMAT(GETDATE(),'dd-MMMM-yyyy hh:mm:ss dddd') => 17-July-2024 8:30:20 Wednesday
=> display ENAME HIREDATE ?
mm/dd/yyyy format ?
SELECT ENAME,FORMAT(HIREDATE,'MM/dd/yyyy') AS HIREDATE FROM EMP
scenario :-
INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,HIREDATE)
VALUES(9999,'ABC','CLERK',4000,GETDATE())
=> display list of employees joined today ?
SELECT *
FROM EMP
WHERE HIREDATE = GETDATE()
2024-07-17 = 2024-07-17 08:38:30.983 => FALSE
SELECT *
FROM EMP
WHERE HIREDATE = FORMAT(GETDATE(),'yyyy-MM-dd')
2024-07-17 = 2024-07-17
18-JUL-24
DATEDIFF() :-
-------------------
=> used to calculate difference between two dates
=> difference can be calculated in days/months/years
DATEDIFF(interval,start date,end date)
ex :-
SELECT DATEDIFF(yy,'2023-07-17',getdate()) => 1
mm => 12
dd => 366
hh => 8792
=> display ENAME EXPERIENCE in years ?
SELECT ENAME,DATEDIFF(YY,HIREDATE,GETDATE()) AS EXPR FROM EMP
=> display ENAME EXPERIENCE ?
M YEARS N MONTHS
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 days/months/years to/from a date
DATEADD(interval, int , date)
ex :-
SELECT DATEADD(DD,10,GETDATE()) => 2024-07-28
SELECT DATEADD(MM,-2,GETDATE()) => 2024-05-18
=>
GOLD_RATES
DATEID RATE
2020-01-01 ?
2020-01-02 ?
2024-07-18 ?
=> display today's gold rate ?
SELECT *
FROM GOLD_RATES
WHERE DATEID = FORMAT(GETDATE(),'yyyy-MM-dd')
=> display yesterday's gold rate ?
WHERE DATEID = FORMAT(DATEADD(DD,-1,GETDATE()),'yyyy-MM-dd')
=> display last month same day gold rate ?
WHERE DATEID = FORMAT(DATEADD(MM,-1,GETDATE()),'yyyy-MM-dd')
=> display last year same day gold rate ?
WHERE DATEID = FORMAT(DATEADD(YY,-1,GETDATE()),'yyyy-MM-dd')
=> display last 1 month gold rates ?
WHERE DATEID >= FORMAT(DATEADD(MM,-1,GETDATE()),'yyyy-MM-dd')
EOMONTH() :-
------------------
=> returns end of the month
EOMONTH(DATE,INT)
Ex :-
SELECT EOMONTH(GETDATE(),0) => 2024-07-31
SELECT EOMONTH(GETDATE(),-1) => 2024-06-30
SELECT EOMONTH(GETDATE(),1) => 2024-08-31
=> display current month 1st day ?
=> display next month 1st day ?
=> display next year first day ?
=> display current year first day ?
CHARACTER / STRING functions :-
-------------------------------------------------
UPPER() :-
------------
=> converts string to uppercase
UPPER(string / column)
Ex :-
SELECT UPPER('hello') => HELLO
LOWER() :-
-----------------
=> converts string to lowercase
LOWER(string/column)
Ex :-
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)
LEN() :-
----------
=> returns string length i.e. no of chars
LEN(string/colname)
Ex :-
SELECT LEN('hello welcome') => 13
=> display employees name contains 4 chars ?
WHERE ENAME LIKE '____'
SELECT * FROM EMP WHERE LEN(ENAME) = 4
LEFT & RIGHT :-
-----------------------
LEFT(string/colname, INT) => returns characters starting from left
RIGHT(string/colname,INT) => returns characters starting from right
Ex :-
SELECT LEFT('hello welcome',5) => hello
SELECT RIGHT('hello welcome',7) => welcome
=> employees name starts with 's' ?
SELECT * FROM EMP WHERE LEFT(ENAME,1) = 's'
=> employees name ends with 's' ?
SELECT * FROM EMP WHERE RIGHT(ENAME,1) = 's'
=> employees name starts and ends with same char ?
WHERE ENAME LIKE 'a%a'
OR
ENAME LIKE 'b%b'
OR
SELECT * FROM EMP WHERE LEFT(ENAME,1) = RIGHT(ENAME,1)
19-jul-24
=> generate emailids for employees ?
empno ename emailid
7369 smith smi736@tcs.com
7499 allen all749@tcs.com
SELECT EMPNO,ENAME,
LEFT(ENAME,3) + LEFT(EMPNO,3) + '@tcs.com' AS EMAILID
FROM EMP
=> store emailids in db ?
step 1 :- add emailid column to emp table
ALTER TABLE EMP
ADD EMAILID VARCHAR(20)
step 2 :- update the column with emailids
UPDATE EMP
SET EMAILID = LEFT(ENAME,3) + LEFT(EMPNO,3) + '@tcs.com'
SUBSTRING() :-
----------------------
=> used to extract characters starting from specific position
SUBSTRING(string,start,no of chars)
ex :-
SELECT SUBSTRING('HELLO WELCOME',1,5) => HELLO
SELECT SUBSTRING('HELLO WELCOME',7,4) => WELC
SELECT SUBSTRING('HELLO WELCOME',10,3) => COM
CHARINDEX() :-
-----------------------
=> returns position of the character in a string.
CHARINDEX(char,string,[start])
ex :-
SELECT CHARINDEX('O','HELLO WELCOME') => 5
SELECT CHARINDEX('K','HELLO WELCOME') => 0
SELECT CHARINDEX('O','HELLO WELCOME',7) => 11
SELECT CHARINDEX('E','HELLO WELCOME',10) => 13
Ex :-
=> employees name contains 'a' ?
WHERE ENAME LIKE '%a%'
SELECT * FROM EMP WHERE CHARINDEX('a',ENAME) <> 0
=>
CUST
CID CNAME
10 SACHIN TENDULKAR
11 VIRAT KOHLI
display CID FNAME LNAME ?
FNAME = SUBSTRING(CNAME,1,CHARINDEX(' ',CNAME)-1)
LNAME = SUBSTRING(CNAME,CHARINDEX(' ',CNAME)+1 , LEN(CNAME))
SELECT CID,
SUBSTRING(CNAME,1,CHARINDEX(' ',CNAME)-1) AS FNAME,
SUBSTRING(CNAME,CHARINDEX(' ',CNAME)+1,LEN(CNAME)) AS LNAME
FROM CUST
=> display FNAME MNAME LNAME ?
CUST
CID CNAME
10 SACHIN RAMESH TENDULKAR
11 MAHENDRA SINGH DHONI
REPLICATE() :-
---------------------
=> repeats given char for given no of times
REPLICATE(char,length)
ex :-
SELECT REPLICATE('*',10) => **********
=> display ENAME SAL ?
******
SELECT ENAME,REPLICATE('*',LEN(SAL)-3) AS SAL FROM EMP
=>
ACCOUNTS
ACCNO BAL
12345678936 10000
your a/c no XXXX8936 debited -------- ?
REPLICATE('X',4) + RIGHT(ACCNO,4)
=> panno :- ABCDE1234K
AB***K
LEFT(PANNO,2) + REPLICATE('*',3) + RIGHT(PANNO,1)
REPLACE() :-
------------------
=> used to replace one string with another string
REPLACE(string1,string2,string3)
ex :-
SELECT REPLACE('HELLO','ELL','ABC') => HABCO
SELECT REPLACE('HELLO','L','ABC') => HEABCABCO
SELECT REPLACE('HELLO','ELO','ABC') => HELLO
SELECT REPLACE('HELLO','ELL','') => HO
=> In table replace 1981 with 2021 in hiredate column ?
UPDATE EMP SET HIREDATE = REPLACE(HIREDATE,'1981','2021')
20-JUL-24
TRANSLATE() :-
----------------------
=> used to translate one char to another char
TRANSLATE(str1,str2,str3)
ex :-
SELECT TRANSLATE('HELLO','ELO','ABC') => HABBC
E => A
L => B
O => C
=> translate function can be used to encrypt data i.e. converting plain text to cipher text
display ENAME SAL ? encrypt salaries ?
SELECT ENAME,
TRANSLATE(SAL , '0123456789.' , '$bT*!&%@
FROM EMP
jones 2975.00 T^@&m$$
=> remove special chars from '@@@he@@ll@@o@@' ?
SELECT
REPLACE( '@@@he@@ll@@o@@','@','')
=> remove all special chars from '@#HE$%LL^&O*!' ?
SELECT
REPLACE(TRANSLATE( '@#HE$%LL^&O*!', '@#$%^&*!','********'),'*','')
**HE**LL**O**
Numeric Functions :-
1 abs() :- returns absolute value
ABS(number) => ABS(-10) => 10
2 power() :- returns power
POWER(num1,num2) => POWER(3,2) => 9
3 sqrt() :- returns square root
SQRT(number) => SQRT(16) => 4
4 square() :- returns square
SQUARE(number) => SQUARE(5) => 25
5 SIGN() :- returns number is positive or negative
SIGN(number) => SIGN(10) => 1
SIGN(-10) => -1
SIGN(0) => 0
6 rounding numbers :-
ROUND
CEILING
FLOOR
ROUND :-
=> used to round number to integer or to decimal places based on avg.
ROUND(number,decimal places,[1/0]) 0 => rounds based on avg (default)
1 => always rounds to lowest
ex :-
SELECT ROUND(46.666666,0) => 47
46
SELECT ROUND(46.466666,0) => 46
number >= avg => rounded to highest
number < avg => rounded to lowest
SELECT ROUND(46.666666,2) => 46.67
46.66
SELECT ROUND(46.664666,2) => 46.66
SELECT ROUND(46.6784,3) => 46.678
SELECT ROUND(46.6784,0,1) => 46
46
SELECT ROUND(46.6784,2,1) => 46.67
SELECT ROUND(386,-2) => 400
300
SELECT ROUND(386,-1) => 390
380
SELECT ROUND(386,-3) => 0
0
SELECT ROUND(386,-2,1) => 300
300
22-jul-24
=> SELECT ROUND(4567,-1),ROUND(4567,-2),ROUND(4567,-3) ,
ROUND(4567,-2,1) ?
O/P :- 4570 4600 5000 4500
=> round all the employee salaries to hundreds in emp table ?
UPDATE EMP SET SAL = ROUND(SAL,-2)
CEILING() :-
=> always rounds number to highest
CEILING(number)
ex :-
SELECT CEILING(3.1) => 4
FLOOR() :-
=> always rounds number to lowest
FLOOR(number)
ex :-
SELECT FLOOR(3.9) => 3
conversion functions :-
1 CAST
2 CONVERT
CAST :-
CAST(expr as target-type)
ex :-
SELECT CAST(10.5 AS INT) => 10
=> display smith earns 800 ?
SELECT ENAME + ' earns ' + CAST(SAL AS VARCHAR) FROM EMP
=> display smith joined on 2021-12-17 as clerk ?
SELECT ENAME + ' joined on ' + CAST(HIREDATE AS VARCHAR) + ' as ' + job
FROM EMP
CONVERT() :-
CONVERT(TARGET-TYPE , EXPR)
Ex :-
SELECT CONVERT(INT , 10.5) => 10
=> difference between cast & convert ?
1 using convert function we can display dates in different formats that is not
possible using cast function.
2 using convert function we can display money in different styles but that is
is not possible using cast function.
Date Styles :-
=> to display dates in different formats , first convert date to char type
CONVERT(VARCHAR,DATE,STYLE-NUMBER)
EX :-
SELECT CONVERT(VARCHAR,GETDATE(),101) => 07/22/2024
103 => 22/07/2024
112 => 20240722
114 => 08:23:16:407
=> display ENAME HIREDATE ? display hiredate in dd.mm.yyyy format ?
SELECT ENAME,CONVERT(VARCHAR,HIREDATE,104) AS HIREDATE
FROM EMP
Money styles :-
CONVERT(VARCHAR,MONEY,STYLE-NUMBER)
0 => number displayed with two decimal places
1 => displayed with thousand seperator
2 => displayed with four decimal places
=> display ENAME SAL ?
display salaries with thousand seperator ?
SELECT ENAME,CONVERT(VARCHAR,SAL,1) AS SAL FROM EMP
=> display EMPNO ENAME SAL ?
SELECT CONVERT(VARCHAR,CAST(EMPNO AS MONEY),1) AS EMPNO ,
ENAME,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
ex :-
SELECT ISNULL(100,200) => 100
SELECT ISNULL(NULL,200) => 200
=> display ENAME SAL COMM TOTSAL ?
TOTSAL = SAL + COMM
SELECT ENAME,SAL,COMM , SAL+ISNULL(COMM,0) AS TOTSAL FROM EMP
smith 800 null 800
allen 1600 300 1900
23-jul-24
=> display ENAME SAL COMM ?
if comm = NULL display 'NO COMM' ?
SELECT ENAME,SAL,ISNULL(CAST(COMM AS VARCHAR),'NO COMM') AS COMM
FROM EMP
Analytical Functions / OLAP functions / Window functions :-
RANK & DENSE_RANK :-
=> both functions are used to find ranks
=> ranking is based on one or more fields
=> for rank functions data must be sorted
RANK() OVER (ORDER BY COLNAME ASC/DESC)
DENSE_RANK() OVER (ORDER BY COLNAME ASC/DESC)
Ex :-
=> display ranks of the employees based on sal and highest paid employee
should get 1st rank ?
SELECT ENAME,SAL,
RANK() OVER (ORDER BY SAL DESC) AS RNK
FROM EMP
SELECT ENAME,SAL,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK
FROM EMP
difference between rank & dense_rank ?
1 rank function generates gaps but dense_rank will not generate gaps.
2 in rank function ranks may not be in sequence but in dense_rank
ranks are always in sequence.
SAL RANK DENSE_RANK
5000 1 1
4000 2 2
3000 3 3
3000 3 3
3000 3 3
2000 6 4
2000 6 4
1000 8 5
=> ranks of the employees based on sal , if salaries are same then ranking should be
based on hiredate ?
SELECT ENAME,HIREDATE,SAL,
DENSE_RANK() OVER (ORDER BY SAL DESC,HIREDATE ASC) AS RNK
FROM EMP
PARTITION BY clause :-
=> used to divide the table based on one or more fields
=> using partition by clause we can find ranks with in group
=> for ex to find ranks with in dept first we need to divide the table dept wise using
partition by clause then apply rank/dense_rank functions on each partition.
SELECT ENAME,SAL,DEPTNO,
DENSE_RANK() OVER (PARTITION BY DEPTNO
ORDER BY SAL DESC) AS RNK
FROM EMP
ROW_NUMBER() :-
=> returns record number
=> it is also based on one or more columns
=> data must be sorted
SELECT ROW_NUMBER() OVER (ORDER BY EMPNO ASC) AS RNO,ENAME,SAL
FROM EMP
SAL RNK DRNK RNO
5000 1 1 1
4000 2 2 2
3000 3 3 3
3000 3 3 4
3000 3 3 5
2000 6 4 6
2000 6 4 7
1000 8 5 8
Aggregate Functions :-
=> these functions process group of rows and returns one value
MAX
MIN
SUM
AVG
COUNT
COUNT(*)
MAX() :- returns maximum value
MAX(arg)
SEELCT MAX(SAL) FROM EMP => 5000
SELECT MAX(HIREDATE) FROM EMP => 2024-07-17
SELECT MAX(ENAME) FROM EMP => ward
24-jul-24
MIN() :-
=> returns minimum value
MIN(arg)
ex :-
SELECT MIN(SAL) FROM EMP => 800
SELECT MIN(HIREDATE) FROM EMP => 1983-
SELECT MIN(ENAME) FROM EMP => adams
SUM() :-
=> returns total
SUM(arg)
ex :-
SELECT SUM(SAL) FROM EMP ; => 29300.00
=> round total sal to thousands ?
SELECT ROUND(SUM(SAL),-3) FROM EMP => 29000
29000
=> after rounding display total sal with thousand seperator ?
SELECT CONVERT(VARCHAR,ROUND(SUM(SAL),-3),1) FROM EMP => 29,000
=> calculate total sal including comm ?
SELECT SUM(SAL+ISNULL(COMM,0)) AS TOTSAL FROM EMP => 31500
AVG() :-
=> returns average value
AVG(arg)
ex :-
SELECT AVG(SAL) FROM EMP => 2092.8571
ex 2 :-
GOLD_RATES
DATEID RATE
=> average gold rate in jun month in the year 2024 ?
SELECT AVG(RATE)
FROM GOLD_RATES
WHERE DATEID BETWEEN '2024-06-01' AND '2024-06-30'
WHERE DATEID LIKE '2024-06%'
NOTE :-
=> SUM,AVG functions cannot be applied on char,date columns.
COUNT() :-
=> returns no of values present in a column
COUNT(arg)
ex :-
SELECT COUNT(EMPNO) FROM EMP => 14
SELECT COUNT(COMM) FROM EMP => 4 => nulls are not counted
COUNT(*) :-
=> returns no of rows in a table
SELECT COUNT(*) FROM EMP => 13
ex :-
T1
F1
10
NULL
20
NULL
30
COUNT(F1) => 3 => nulls are not counted
COUNT(*) => 5 => nulls are counted
=> display no of employees joined in year 1981 ?
SELECT COUNT(*)
FROM EMP
WHERE DATEPART(YY,HIREDATE) = 1981
=> display no of employees joined on sunday ?
SELECT COUNT(*)
FROM EMP
WHERE DATEPART(DW,HIREDATE) = 1
=> display no of employees joined in 2nd quarter of 1981 year ?
SELECT COUNT(*)
FROM EMP
WHERE DATEPART(YY,HIREDATE) = 1981
AND
DATEPART(QQ,HIREDATE) = 2
NOTE :-
=> aggregate functions are not allowed in where clause and they are allowed only in
select,having clauses.
SELECT ENAME
FROM EMP
WHERE SAL = MAX(SAL) => ERROR
SELECT DEPTNO
FROM EMP
WHERE COUNT(*) > 3 => ERROR
=> no of values return by expr1 = no of vaues return by expr2
SELECT EXPR1, EXPR2 FROM TABNAME
1 SELECT ENAME,MAX(SAL) FROM EMP => ERROR
14 1
2 SELECT MIN(SAL),MAX(SAL) FROM EMP => EXECUTED
1 1
3 SELECT ENAME,ROUND(SAL,-2) AS SAL FROM EMP => EXECUTED
14 14
4 SELECT ROUND(SAL,-2) , MAX(SAL) FROM EMP => ERROR
14 1
SUMMARY :-
DATE :- getdate(),datepart,datename,dateadd,datediff,format,eomonth
CHAR :- upper,lower,len,left,right,substring,charindex,replicate,replace,translate
NUMERIC :- abs,power,sqrt,square,sign,round,ceiling,floor
CONVERSION :- cast, convert
ANALYTICAL :- rank,dense_rank,row_number
AGGREGATE :- max,min,sum,avg,count,count(*)
===================================================================
25-jul-24
CASE statement :-
=> used to implement if-then-else
=> similar to switch case
=> using case stmt we can return values based on conditions
=> case statements are 2 types
1 simple case
2 searched case
simple case :-
=> use simple case when conditions based on "=" operator
CASE colname/expr
WHEN value1 THEN return expr1
WHEN value2 THEN return expr2
ELSE return expr
END
=> display EMPNO ENAME DNAME ?
if deptno = 10 display ACCOUNTS
20 RESEARCH
30 SALES
others UNKNOWN
SELECT EMPNO,ENAME,
CASE DEPTNO
WHEN 10 THEN 'ACCOUNTS'
WHEN 20 THEN 'RESEARCH'
WHEN 30 THEN 'SALES'
ELSE 'UNKNOWN'
END AS DNAME
FROM EMP
=> increment employee salaries as follows ?
if job=CLERK incr sal by 10%
SALESMAN 15%
MANAGER 20%
others 5%
UPDATE EMP
SET SAL = CASE JOB
WHEN 'CLERK' THEN SAL*1.1
WHEN 'SALESMAN' THEN SAL*1.15
WHEN 'MANAGER' THEN SAL*1.2
ELSE SAL*1.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 LOSAL
SAL=3000 AVGSAL
SELECT ENAME,SAL,
CASE
WHEN SAL>3000 THEN 'HISAL'
WHEN SAL<3000 THEN 'LOSAL'
ELSE 'AVGSAL'
END AS SALRANGE
FROM EMP
=> display SNO TOTAL AVG RESULT ?
STUDENT
SNO SNAME S1 S2 S3
1 A 80 90 70
2 B 30 60 50
SELECT SNO,
S1+S2+S3 AS TOTAL,
(S1+S2+S3)/3 AS AVG,
CASE
WHEN S1>=35 AND S2>=35 AND S3>=35 THEN 'PASS'
ELSE 'FAIL'
END AS RESULT
FROM STUDENT
===========================================================================
26-jul-24
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.for ex to calculate dept wise total sal
first we need to group rows based on dept then apply sum function on each dept
EMP
EMPNO ENAME SAL DEPTNO
1 A 3000 10
2 B 5000 20 group by 10 8000
3 C 4000 30 ======================> 20 11000
4 D 6000 20 30 4000
5 E 5000 10
detailed data summarized data
=> GROUP BY clause converts detailed data to summarized data which is useful for
analysis.
syntax :-
SELECT columns
FROM tabname
[WHERE cond]
GROUP BY col1,col2,
[HAVING cond]
[ORDER BY col ASC/DESC]
Execution :-
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
Examples :-
=> display dept wise total sal ?
SELECT DEPTNO,SUM(SAL) AS TOTSAL
FROM EMP
GROUP BY DEPTNO ;
FROM EMP :-
EMPNO ENAME SAL DEPTNO
1 A 3000 10
2 B 5000 20
3 C 4000 30
4 D 6000 20
5 E 5000 10
GROUP BY DEPTNO :-
10 1 A 3000
5 E 5000
20 2 B 5000
4 D 6000
30 3 C 4000
SELECT DEPTNO,SUM(SAL) AS TOTSAL :-
10 8000
20 11000
30 4000
=> display job wise no of employees ?
SELECT JOB,COUNT(*) AS CNT
FROM EMP
GROUP BY JOB
ANALYST 2
CLERK 4
MANAGER 3
PRESIDENT 1
SALESMAN 4
=> display year wise no of employees joined ?
SELECT DATEPART(YY,HIREDATE) AS YEAR,COUNT(*) AS CNT
FROM EMP
GROUP BY DATEPART(YY,HIREDATE)
GROUP BY YEAR => INVALID
ORDER BY YEAR => VALID
NOTE :-
=> column alias cannot be used in group by clause because group by clause is executed
before select.
=> column alias can be used in order by clause because order by clause is executed after select
=> display day wise no of employees joined ?
SELECT DATENAME(DW,HIREDATE) AS DAY,COUNT(*) AS CNT
FROM EMP
GROUP BY DATENAME(DW,HIREDATE)
=> display month wise no of employees joined in the year 2021 ?
SELECT DATENAME(MM,HIREDATE) AS MONTH,COUNT(*) AS CNT
FROM EMP
WHERE DATEPART(YY,HIREDATE)=2021
GROUP BY DATENAME(MM,HIREDATE)
=> find the departments having more than 3 employees ?
SELECT DEPTNO,COUNT(*)
FROM EMP
WHERE COUNT(*) > 3
GROUP BY DEPTNO => ERROR
sql server cannot calculate dept wise count before group by and it can calculate only
after group by , so apply the condition COUNT(*) > 3 after group by using HAVING clause.
SELECT DEPTNO,COUNT(*)
FROM EMP
GROUP BY DEPTNO
HAVING COUNT(*) > 3
WHERE VS HAVING :-
WHERE HAVING
1 selects specific rows selects specific groups
2 conditions applied before group by conditions applied after group by
3 use where clause if cond use having clause if cond
doesn't contain aggregate contains aggregate function
function
27-jul-24
=> find southern states having more than 5cr population ?
PERSONS
AADHARNO NAME GENDER DOB ADDR CITY STATE
SELECT STATE,COUNT(*)
FROM PERSONS
WHERE STATE IN ('AP','TG','TN','KL','KA')
GROUP BY STATE
HAVING COUNT(*) > 50000000
=> display job wise no of employees where job = clerk,manager and no of emps > 3 ?
SELECT JOB,COUNT(*)
FROM EMP
WHERE JOB IN ('CLERK','MANAGER')
GROUP BY JOB
HAVING COUNT(*) > 3
Grouping based on multiple columns :-
----------------------------------------------------
=> display dept wise and with in dept job wise total sal ?
SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP
GROUP BY DEPTNO,JOB
ORDER BY DEPTNO ASC ;
=> display year wise and with in year quarter wise no of employees joined ?
SELECT DATEPART(YY,HIREDATE) AS YEAR,
DATEPART(QQ,HIREDATE) AS QRT,
COUNT(*) AS CNT
FROM EMP
GROUP BY DATEPART(YY,HIREDATE),DATEPART(QQ,HIREDATE)
ORDER BY YEAR ASC
2021 1 ?
2 ?
3 ?
4 ?
2022 1 ?
2 ?
3 ?
4 ?
ROLLUP & CUBE :-
----------------------------
=> both functions are used to display subtotals and grand total
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)
FROM EMP
GROUP BY ROLLUP(DEPTNO,JOB)
ORDER BY DEPTNO ASC ;
NULL NULL 32805.00 => grand total
10 CLERK 1430.00
10 MANAGER 3000.00
10 PRESIDENT 5250.00
10 NULL 9680.00 => dept subtotal
20 ANALYST 6300.00
20 CLERK 2090.00
20 MANAGER 3600.00
20 NULL 11990.00 => dept subtotal
30 CLERK 1100.00
30 MANAGER 3480.00
30 SALESMAN 6555.00
30 NULL 11135.00 => dept subtotal
CUBE :-
-----------
=> cube displays subtotals for each group by column (deptno,job) and also displays grand total
SELECT DEPTNO,JOB,SUM(SAL)
FROM EMP
GROUP BY CUBE(DEPTNO,JOB)
ORDER BY DEPTNO ASC , JOB ASC
NULL NULL 32805.00 => grand total
NULL ANALYST 6300.00 => job subtotal
NULL CLERK 4620.00
NULL MANAGER 10080.00
NULL PRESIDENT 5250.00
NULL SALESMAN 6555.00
10 NULL 9680.00 => dept subtotal
10 CLERK 1430.00
10 MANAGER 3000.00
10 PRESIDENT 5250.00
20 NULL 11990.00 => dept subtotal
20 ANALYST 6300.00
20 CLERK 2090.00
20 MANAGER 3600.00
30 NULL 11135.00 => dept subtotal
30 CLERK 1100.00
30 MANAGER 3480.00
30 SALESMAN 6555.00
=> display state wise and with in state gender wise population and display state wise and gender
wise subtotals ?
PERSONS
AADHARNO NAME GENDER DOB ADDR CITY STATE
SELECT STATE,GENDER,COUNT(*)
FROM PERSONS
GROUP BY CUBE(STATE,GENDER)
ORDER BY STATE ASC ,GENDER ASC
NULL NULL 147---
NULL FEMALE ?
NULL MALE ?
AP NULL ?
FEMALE ?
MALE ?
AR NULL ?
FEMALE ?
MALE ?
=> display year wise and with in year quarter wise total amount ? display year wise subtotals ?
SALES
DATEID PRODID CUSTID QTY AMT
2024-07-27 100 10 1 2000
null null ?
2020 null ?
1 ?
2 ?
3 ?
4 ?
SELECT DATEPART(YY,DATEID) AS YEAR,
DATEPART(QQ,DATEID) AS QRT ,
SUM(AMT) AS TOTAL
FROM SALES
GROUP BY ROLLUP( DATEPART(YY,DATEID) , DATEPART(QQ,DATEID))
ORDER BY YEAR ASC,QRT ASC
29-jul-24
GROUPING_ID() :-
---------------------------
=> grouping_id functions accepts group by columns and returns subtotal belongs to which
group by column
Ex :- GROUPING_ID(DEPTNO,JOB)
1 => subtotal belongs to 1st group by column i.e. deptno
2 => subtotal belongs to 2nd group by column i.e. job
3 => grand total
SELECT DEPTNO,JOB,SUM(SAL) AS TOTSAL,
CASE GROUPING_ID(DEPTNO,JOB)
WHEN 1 THEN 'DEPT SUBTOTAL'
WHEN 2 THEN 'JOB SUBTOTAL'
WHEN 3 THEN 'GRAND TOTAL'
END AS SUBTOTALS
FROM EMP
GROUP BY CUBE(DEPTNO,JOB)
ORDER BY DEPTNO ASC,JOB ASC
summary :-
importance of group by
writing queries using group by
where vs having
rollup vs cube
grouping_id
===========================================================================
INTEGRITY CONSTRAINTS
======================
=> integrity constraints are rules to maintain data integrity i.e. data quality or data consistency.
=> used to prevent users from entering invalid data.
=> used to enforce rules like min bal must be 1000.
Types of Integrity Constraints :-
-------------------------------------------
1 NOT NULL
2 UNIQUE
3 PRIMARY KEY
4 CHECK
5 FOREIGN KEY
6 DEFAULT
=> above constraints can be declared in two ways
1 column level
2 table level
column level :-
--------------------
=> if constraints are declared immediately after declaring column then it is called column level
CREATE TABLE <tabname>
(
colname datatype(size) constraint ,
colname datatype(size) constraint,
---------------------------------
)
NOT NULL :-
-----------------
=> NOT NULL constraint doesn't accept null values.
=> a column declared with NOT NULL is called mandatory column.
ex :-
CREATE TABLE EMP11
(
EMPNO INT,
ENAME VARCHAR(10) NOT NULL
)
INSERT INTO EMP11 VALUES(100,NULL) => ERROR
INSERT INTO EMP11 VALUES(101,'A') => 1 ROW AFFECTED
UNIQUE :-
=> unique constraint doesn't accept duplicates
ex :-
CREATE TABLE cust
(
custid INT,
cname VARCHAR(10) NOT NULL,
emailid VARCHAR(20) UNIQUE
)
INSERT INTO CUST VALUES(100,'A','abc@gmail.com')
INSERT INTO CUST VALUES(101,'B','abc@gmail.com') => ERROR
INSERT INTO CUST VALUES(102,'C',NULL)
INSERT INTO CUST VALUES(103,'D',NULL) => ERROR
NOTE :- unique allows one null
PRIMARY KEY :-
-----------------------
=> primary key doesn't allow duplicates & nulls.
=> primary key is the combination of unique & not null.
=> In tables one column must be there to uniquely identify the records and into that
column duplicates and nulls are not allowed , so declare that column with
primary key.
ex :-
CREATE TABLE emp12
(
empid INT PRIMARY KEY ,
ename VARCHAR(10) NOT NULL
)
INSERT INTO EMP12 VALUES(100,'A')
INSERT INTO EMP12 VALUES(100,'B') => ERROR
INSERT INTO EMP12 VALUES(NULL,'A') => ERROR
NOTE :-
=> a table can have only one primary key , if we want multiple primary keys then
one column with primary key and other columns with unique not null.
ex ;-
CREATE TABLE CUST
(
CUSTID INT PRIMARY KEY,
NAME VARCHAR(10) NOT NULL,
AADHARNO NUMERIC(12) UNIQUE NOT NULL ,
PANNO CHAR(10) UNIQUE NOT NULL
)
=> difference between unique & primary key ?
unique primary key
1 allows one null doesn't allow nulls
2 a table can have a table can have only one primary key
multiple unique
constraints
3 sql server creates sql server creates clustered
non clustered index index on primary key column
on unique column
30-jul-24
CHECK constraint :-
---------------------------
=> use check constraint when rule based on condition.
CHECK(condition)
ex 1 :- sal must be min 3000
CREATE TABLE emp13
(
empid INT PRIMARY KEY,
ename VARCHAR(10) NOT NULL,
sal MONEY CHECK(sal>=3000)
)
INSERT INTO EMP13 VALUES(100,'A',1000) => ERROR
INSERT INTO EMP13 VALUES(101,'B',5000)
INSERT INTO EMP13 VALUES(102,'C',NULL)
NOTE :- check constraint allows nulls
ex 2 :- gender must be 'm','f' ?
GENDER CHAR(1) CHECK(GENDER IN ('M','F'))
ex 3 :- amount must be multiple of 100
AMOUNT MONEY CHECK(AMOUNT%100=0)
ex 4 :- pwd must be min 6 chars
PWD VARCHAR(12) CHECK(LEN(PWD) >= 6)
ex 5 :- emailid must contain '@'
must end with '.com' or '.co' or '.in'
EMAILID VARCHAR(30) CHECK(EMAILID LIKE '%@%'
AND
(
EMAILID LIKE '%.com'
OR
EMAILID LIKE '%.co'
OR
EMAILID LIKE '%.in'
))
FOREIGN KEY :-
----------------------
=> foreign key is used to establish relationship between two tables.
=> to establish relationship take primary key of one table and add it to another table
as foreign key and declare with references constraint.
ex :-
PROJECTS
PROJID PNAME DURATION COST CLIENT
100 AAA 5 500 TATA MOTORS
101 BBB 4 300 DBS BANK
EMP
EMPID ENAME SAL PROJID REFERENCES PROJECTS (PROJID)
1 A 5000 100
2 B 6000 101
3 C 7000 999 => NOT ACCEPTED
4 D 4000 100
5 E 3000 NULL
=> values entered in fk column should match with values entered in pk column
=> fk allows duplicates & nulls.
=> after declaring fk a relationship is established between two tables called parent/child
relationship.
=> pk table is parent and fk table is child.
Ex :-
CREATE TABLE projects
(
projid INT PRIMARY KEY,
pname VARCHAR(10) NOT NULL,
client VARCHAR(20) NOT NULL
)
INSERT INTO projects VALUES(100,'A','TATA'),(101,'B','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,'K',4000,100)
INSERT INTO emp_proj VALUES(2,'B',5000,999) => ERROR
INSERT INTO emp_proj VALUES(3,'C',4000,100)
INSERT INTO emp_proj VALUES(4,'D',3000,NULL)
31-jul-24
Relationship Types :-
----------------------------
1 one to one (1:1)
2 one to many (1:m) => DEFAULT
3 many to one (m:1)
4 many to many (m:n)
=> by default sql server creates one to many relationship between two tables
1:1 relationship :-
-----------------------
=> to establish 1:1 relationship between two tables declare foreign key with unique constraint
ex :-
DEPT
DNO DNAME
10 HR
20 IT
30 SALES
MGR
MGRNO MNAME DNO REFERENCES DEPT(DNO) UNIQUE
1 A 10
2 B 20
3 C 30
=> In the above example one dept managed by one manager and one manager manages
exactly one dept , so relationship between dept and mgr is one to one.
m;n relationship :-
------------------------
=> if relationship is many to many then create 3rd table and in 3rd table take primary keys
of both tables as foreign keys.
ex :-
CUST PRODUCTS
CID NAME ADDR PRODID NAME PRICE CATEGORY BRAND
10 100
11 101
12 102
SALES
DATEID PRODID CID QTY AMOUNT
30/ 100 10 1 2000
30/ 101 10 1 1000
30/ 100 11 1 2000
NOTE :-
=> if relationship is one to one then we can add fk to any table
=> if relationship is one to many then fk must be added to many side table
=> if relationship is many to many then create 3rd and add pks of both tables as fks
ER MODEL =====================> RELATIONAL MODEL
used in design phase used in development phase
ENTITYIES TABLES
ATTRIBUTES FIELDS
RELATIONSHIP FOREIGN KEY
RELATIONAL MODEL :-
---------------------------------
BANK
CODE NAME ADDR
-----------
BRANCH
BRANCH_ID NAME ADDRES CODE (FK)
-----------------
LOANS
LOAN_ID TYPE AMOUNT BRANCH_ID (FK) CUSTID(FK)
--------------
CUSTOMER
CUSTID NAME PHONE ADDR
------------
ACCOUNTS
ACCNO ACTYPE BAL BRANCH_ID (FK) CUSTID(FK)
----------
DEFAULT :-
---------------
=> a column can be declared with default value as follows
hiredate date default getdate()
=> while inserting data if we skip hiredate then sql server inserts default value
ex :-
create table emp33
(
empid int primary key,
ename varchar(10) not null,
hiredate date default getdate()
)
INSERT INTO EMP33(empid,ename) VALUES(100,'A')
INSERT INTO EMP33 VALUES(101,'B','2024-01-01')
INSERT INTO EMP33 VALUES(102,'C',NULL)
SELECT * FROM EMP33
EMPID ENAME HIREDATE
100 A 2024-07-31
101 B 2024-01-01
102 C 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 should not be duplicate & null
2 ttype must be 'W' OR 'D'
3 tdate must be always current date
4 tamt must be multiple of 100
5 accno should match with accounts table accno.
TABLE LEVEL :-
----------------------
=> table level constraints are declared after declaring all columns
=> use table level to declare constraints for multiple or combination of columns
Declaring check constraint at table level :-
---------------------------------------------------------
PRODUCTS
prodid pname mfd_dt exp_dt
100 A 2024-07-01 2024-01-01 => INVALID
Rule :- exp_dt > mfd_dt
CREATE TABLE PRODUCTS
(
PRODID INT PRIMARY KEY,
PNAME VARCHAR(10) NOT NULL,
MFD_DT DATE ,
EXP_DT DATE ,
CHECK(EXP_DT > MFD_DT)
)
01-aug-24
composite primary key :-
---------------------------------
=> If combination of columns declared primary key then it is called composite primary key.
=> In some tables we may not be able to uniquely identify by using single column
and we need combination of columns to uniquely identify and that combination
should be declared primary key at table level.
=> In composite primary key combination should not be duplicate.
ex :-
STUDENT COURSE
SID SNAME CID CNAME
------ ------
1 A 10 .NET
2 B 11 SQL SERVER
REGISTRATIONS
SID CID DOR FEE
------------------
1 10 ? ?
1 11 ? ?
2 10 ? ?
=> In the above table sid,cid combination uniquely identifies the records , so declare this
combination as primary key.
CREATE TABLE STUDENT
(
SID INT PRIMARY KEY,
SNAME VARCHAR(10) NOT NULL
)
INSERT INTO STUDENT VALUES(1,'A'),(2,'B')
CREATE TABLE COURSE
(
CID INT PRIMARY KEY,
CNAME VARCHAR(10) NOT NULL
)
INSERT INTO COURSE VALUES(10,'.NET') , (11,'SQL SERVER')
CREATE TABLE REGISTRATIONS
(
SID INT REFERENCES STUDENT(SID) ,
CID INT REFERENCES COURSE(CID) ,
DOR DATE,
FEE MONEY,
PRIMARY KEY(SID,CID)
)
INSERT INTO REGISTRATIONS VALUES(1,10,GETDATE(),5000)
INSERT INTO REGISTRATIONS VALUES(1,11,GETDATE(),5000)
INSERT INTO REGISTRATIONS VALUES(2,10,GETDATE(),5000)
INSERT INTO REGISTRATIONS VALUES(1,10,GETDATE(),5000) => ERROR
composite foreign key :-
---------------------------------
=> if foreign key declared for combination then it is called composite foreign key
=> a composite foreign key refers composite primary key.
ex :-
REGISTRATIONS
SID CID DOR FEE
------------------
1 10 ? ?
1 11 ? ?
2 10 ? ?
CERTIFICATES
CERTNO DOI SID CID
1000 ? 1 10
1001 ? 1 11
1002 ? 2 11 => invalid
=> in the above example sid,cid combination should match with registrations table sid,cid combination
so declare this combination as foreign key at table level.
CREATE TABLE CERTIFICATES
(
CERTNO INT PRIMARY KEY,
DOI DATE,
SID INT ,
CID INT ,
FOREIGN KEY(SID,CID) REFERENCES REGISTRATIONS(SID,CID)
)
INSERT INTO CERTIFICATES VALUES(1000,GETDATE(),2,11) => ERROR
which of the following constraint cannot be declared at table level ?
A UNIQUE
B CHECK
C NOT NULL
D PRIMARY KEY
E FOREIGN KEY
ANS :- C
Which statements are true regarding constraints ?
A a foreign key cannot contain NULL value F
B a column with UNIQUE constraint can contain NULL value T
C a constraint is enforced only for the INSERT operation on a table F
D all constraints can be defined at column level and table level. F
Which CREATE TABLE statement is valid?
A. CREATE TABLE ord_details
(ord_no INT PRIMARY KEY,
item_no INT PRIMARY KEY,
ord_date DATE NOT NULL);
B. CREATE TABLE ord_details
(ord_no INT UNIQUE, NOT NULL,
item_no INT,
ord_date DATE DEFAULT GETDATE() NOT NULL);
C. CREATE TABLE ord_details
(ord_no INT ,
item_no INT,
ord_date DATE DEFAULT NOT NULL,
UNIQUE (ord_no),
PRIMARY KEY (ord_no));
D. CREATE TABLE ord_details
(ord_no INT,
item_no INT,
ord_date DATE DEFAULT GETDATE() NOT NULL,
PRIMARY KEY (ord_no, item_no));
Adding constraints to existing table ;-
-------------------------------------------------
=> "alter" command is used to add constraints to existing table.
CREATE TABLE emp55
(
empid INT ,
ename VARCHAR(10),
sal MONEY,
emailid VARCHAR(20),
dno INT
)
Adding primary key :-
----------------------------
=> primary key cannot be added to nullable column
=> to add primary key first change the column to not null
add primary key to empno ?
STEP1 :-
ALTER TABLE EMP66
ALTER COLUMN EMPNO INT NOT NULL
STEP2 :-
ALTER TABLE EMP66
ADD PRIMARY KEY(EMPNO)
Adding check :-
----------------------
=> add check constraint with cond sal >= 3000 ?
ALTER TABLE EMP66
ADD CHECK(SAL>=3000)
ALTER TABLE EMP
ADD CHECK(SAL>=3000) => ERROR
in emp table some of the employee salaries are less than 3000 , so while
adding constraint sql server also validates existing data. if existing data
satisfies the cond then constraint is added.
WITH NOCHECK :-
--------------------------
=> if check constraint added with " WITH NOCHECK " then sql server will
not validate existing data and it validates only new data.
ALTER TABLE EMP
WITH NOCHECK ADD CHECK(SAL>=3000)
Adding foreign key :-
-------------------------------
=> add fk to dno that references dept55 table pk i.e. dno ?
ALTER TABLE EMP66
ADD FOREIGN KEY(DNO) REFERENCES DEPT55(DNO)
Changing from NULL to NOT NULL :-
---------------------------------------------------
ALTER TABLE EMP66
ALTER COLUMN ENAME VARCHAR(10) NOT NULL
Droping constraints :-
-----------------------------
ALTER TABLE <TABNAME>
DROP CONSTRAINT <NAME>
Ex :-
=> drop check constraint in emp66 table ?
ALTER TABLE EMP66
DROP CONSTRAINT CK__EMP66__SAL__6E01572D
=> drop primary key in dept55 table ?
ALTER TABLE DEPT55
DROP CONSTRAINT PK__DEPT55__C035B8C2F28DA7A6 => ERROR
DROP TABLE DEPT55 => ERROR
TRUNCATE TABLE DEPT55 => ERROR
NOTE :-
=> pk constraint cannot be dropped if referenced by some fk
=> pk table cannot be dropped if referenced by some fk
=> pk table cannot be truncated if referenced by some fk
DELETE RULES :-
------------------------
ON DELETE NO ACTION (DEFAULT)
ON DELETE CASCADE
ON DELETE SET NULL
ON DELETE SET DEFAULT
=> the above rules are declared with foreign key
=> delete rules specifies how child rows are affected if parent row is deleted
ON DELETE NO ACTION :-
--------------------------------------
=> parent row cannot be deleted if associated with child rows
create table dept77
(
dno int primary key,
dname varchar(10)
)
insert into dept77 values(10,'hr'),(20,'it')
create table emp77
(
eno 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 BAL
100 10000
101 20000
LOANS
ID TYPE AMT ACCNO
1 H 30 100
2 C 10 100
ON DELETE CASCADE :-
-----------------------------------
=> parent row 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
(
eno 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 DELETED
SELECT * FROM EMP77 => NO ROWS
scenario :-
ACCOUNTS
ACCNO BAL
100 10000
101 20000
TRANSACTIONS
TRID TTYPE TDATE TAMT ACCNO
1 W 1000 100
2 D 2000 100
ON DELETE SET NULL :-
------------------------------------
=> parent row is deleted without deleting child rows 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
(
eno 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 deleted
SELECT * FROM EMP77
ENO ENAME DNO
1 A NULL
2 B NULL
scenario :-
PROJECTS
PROJID NAME DURATION
100
101
EMP
EMPID ENAME PROJID
1 100
2 101
=> if project is completed (deleted) then set the employee projid to null
ON DELETE SET DEFAULT :-
--------------------------------------------
=> parent row is deleted without deleting child rows 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
(
eno 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 DELETED
SELECT * FROM EMP77
ENO ENAME DNO
1 A 20
2 B 20
summary :-
importance of constraints
types of constraints
declaring constraints
column level
table level
adding constraints to table
droping constraints
delete rules
============================================================================
03-aug-24
JOINS
=====
=> join is an operation performed to display data from two or more tables.
=> In DB related data stored in multiple tables , so to combine or to gather data
stored in multple tables we need to join those tables.
ex :-
orders customers
ordid orddt deldt amount cid cid name addr phone
1000 ? ? ? 10 10 A HYD
1001 11 11 B HYD
1002 12 12 C HYD
report :-
ordid orddt deldt cname addr phone
1000 ? ? A HYD ?
1001 B HYD
Types of joins :-
---------------------
1 Inner Join
equi join
non equi join
2 Outer Join
left join
right join
full join
3 Self join
4 Cross / Cartesian join
Equi 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 specifies which record of table1 shoulbe be joined with which record of table2.
table1.commonfield = table2.commonfield
Ex :-
EMP DEPT
EMPNO ENAME SAL DEPTNO DEPTNO DNAME LOC
1 A 3000 10 10 ACCOUNTS NEW YORK
2 B 4000 20 20 RESEARCH
3 C 5000 30 30 SALES
4 D 3000 20 40 OPERATIONS
5 E 2000 NULL
=> display ENAME SAL DNAME LOC ?
---------------------- ---------------------------
SELECT ENAME,SAL,DNAME,LOC
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO ;
ON EMP.DEPTNO = DEPT.DEPTNO :-
----------------------------------------------------
1 A 3000 10 10 ACCOUNTS NEW YORK
2 B 4000 20 20 RESEARCH
3 C 5000 30 30 SALES
4 D 3000 20 20 RESEARCH
SELECT ENAME,SAL,DNAME,LOC :-
----------------------------------------------------
A 3000 ACCOUNTS NEW YORK
B 4000 RESEARCH ??
C 5000 SALES ?
D 3000 RESEARCH ?
note :-
=> In join queries declare table alias and prefix column names with table alias for two reasons
1 to avoid ambiguity
2 for faste execution
SELECT E.ENAME,E.SAL,D.DEPTNO,D.DNAME,D.LOC
FROM EMP AS E INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
05-aug-24
=> display employee details dept details working at NEW YORK loc ?
SELECT E.ENAME,E.SAL,D.DEPTNO,D.DNAME,D.LOC
FROM EMP AS E INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO /* join cond */
WHERE D.LOC = 'NEW YORK' /* filter cond */
=> display order details with cust details ?
SELECT o.* , c.*
FROM orders as o INNER JOIN cust as c
ON o.cid = c.cid
=> display order details with cust details to be delivered today ?
SELECT o.* , c.*
FROM orders as o INNER JOIN cust as c
ON o.cid = c.cid
WHERE o.deldt = FORMAT(getdate() , 'yyyy-MM-dd')
joining more than 2 tables :-
---------------------------------------
=> if no of tables increases no of join conditions also increases
=> to join N tables N-1 join conditions required
ex :-
emp dept locations countries
empno deptno locid country_id
ename dname city country_name
sal locid state
deptno country_id
=> display ENAME DNAME CITY STATE COUNTRY ?
---------- ----------- ------------------ ----------------
EMP DEPT LOCATIONS COUNTRIES
SELECT e.ename,
d.dname,
l.city,l.state,
c.country_name AS country
FROM emp as e inner join dept as d
ON e.deptno = d.deptno
inner join locations as l
ON d.locid = l.locid
inner join countries as c
ON l.country_id = c.country_id
Non Equi Join :-
-----------------------
=> non equi join is performed between the tables not sharing a common field
=> here join condition is not based on "=" operator and it is based on
> < between operaots.
SELECT columns
FROM tab1 INNER JOIN tab2
ON join condition
ex :-
EMP SALGRADE
EMPNO ENAME SAL GRADE LOSAL HISAL
1 A 3000 1 700 1000
2 B 1000 2 1001 2000
3 C 5000 3 2001 3000
4 D 2000 4 3001 4000
5 E 1500 5 4001 9999
=> display ENAME SAL GRADE ?
------------------ -----------
EMP SALGRADE
SELECT e.ename,e.sal,s.grade
FROM emp as e INNER JOIN salgrade as s
ON e.sal BETWEEN s.losal AND s.hisal
A 3000 3
B 1000 1
C 5000 5
D 2000 2
E 1500 2
=> display ENAME DNAME GRADE ?
SELECT E.ENAME,D.DNAME,S.GRADE
FROM EMP AS E INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
INNER JOIN SALGRADE AS S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
06-aug-24
Outer join :-
----------------
=> inner join returns only matching records but will not return unmatched records , to display
unmatched records perform outer join.
EMP DEPT
EMPNO ENAME SAL DEPTNO DEPTNO DNAME LOC
1 A 3000 10 10 ACCOUNTS NEW YORK
2 B 4000 20 20 RESEARCH
3 C 5000 30 30 SALES
4 D 3000 20 40 OPERATIONS => unmatched
5 E 2000 NULL => unmatched
=> outer join is 3 types
1 LEFT JOIN
2 RIGHT JOIN
3 FULL JOIN
LEFT JOIN :-
------------------
=> 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 AS E LEFT JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
A ACCOUNTS
B RESEARCH
C SALES
D RESEARCH
E NULL => unmatched from emp
RIGHT JOIN :-
--------------------
=> returns all rows from right side table and matching rows from left side table.
SELECT E.ENAME,D.DNAME
FROM EMP AS E RIGHT JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
A ACCOUNTS
B RESEARCH
C SALES
D RESEARCH
NULL OPERATIONS => unmatched from dept
FULL JOIN :-
--------------------
=> returns all rows from both tables
SELECT E.ENAME,D.DNAME
FROM EMP AS E FULL JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
A ACCOUNTS
B RESEARCH
C SALES
D RESEARCH
E NULL => unmatched from emp
NULL OPERATIONS => unmatched from dept
Displaying only unmatched records :-
----------------------------------------------------
left side table :-
----------------------
SELECT E.ENAME,D.DNAME
FROM EMP AS E LEFT JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE D.DNAME IS NULL
E NULL
right side table :-
------------------------
SELECT E.ENAME,D.DNAME
FROM EMP AS E RIGHT JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE E.ENAME IS NULL
NULL OPERATIONS
both tables :-
-----------------
SELECT E.ENAME,D.DNAME
FROM EMP AS E FULL JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE D.DNAME IS NULL
OR
E.ENAME IS NULL
E NULL
NULL OPERATIONS
=>
EMP PROJECTS
EMPID ENAME SAL PROJID PROJID NAME CLIENT
1 100 100
2 101 101
3 100 102
4 NULL
1 display employee details with project details ?
2 display employee details with project details and also display employees not assigned to any project ?
3 display only the projects where no employee assigned ?
SELF JOIN :-
------------------
=> joining a table to itself is called self join.
=> In self join a record in one table joined with another record of same table.
=> to perform self join the same table must be declared two times with different alias
FROM EMP AS X JOIN EMP AS Y
ex :-
EMP X EMP Y
EMPNO ENAME MGR EMPNO ENAME MGR
7369 smith 7902 7369 smith 7902
7499 allen 7698 7499 allen 7698
7566 jones 7839 7566 jones 7839
7698 blake 7839 7698 blake 7839
7839 king null 7839 king null
7902 ford 7566 7902 ford 7566
=> display ENAME MGRNAME ?
SELECT X.ENAME AS EMPLOYEE, Y.ENAME AS MANAGER
FROM EMP AS X INNER JOIN EMP AS Y
ON X.MGR = Y.EMPNO
smith ford
allen blake
jones king
blake king
ford jones
=> display employees reporting to blake ?
SELECT X.ENAME AS EMPLOYEE, Y.ENAME AS MANAGER
FROM EMP AS X INNER JOIN EMP AS Y
ON X.MGR = Y.EMPNO
WHERE Y.ENAME='blake'
=> display blake's manager name ?
SELECT Y.ENAME AS MANAGER
FROM EMP AS X INNER JOIN EMP AS Y
ON X.MGR = Y.EMPNO
WHERE X.ENAME='blake'
=> display employees earning more than their manager ?
SELECT X.ENAME AS EMPLOYEE,X.SAL AS EMPSAL,
Y.ENAME AS MANAGER,Y.SAL AS MGRSAL
FROM EMP AS X INNER JOIN EMP AS Y
ON X.MGR = Y.EMPNO
WHERE X.SAL > Y.SAL
=> display employees who are joined befor their manager ?
SELECT X.ENAME AS EMPLOYEE,X.HIREDATE AS EMPHIRE,
Y.ENAME AS MANAGER,Y.HIREDATE AS MGRHIRE
FROM EMP AS X INNER JOIN EMP AS Y
ON X.MGR = Y.EMPNO
WHERE X.HIREDATE < Y.HIREDATE
=> display ENAME DNAME GRADE MNAME ?
SELECT E.ENAME,
D.DNAME,
S.GRADE,
M.ENAME AS MANAGER
FROM EMP AS E INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
INNER JOIN SALGRADE AS S
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
INNER JOIN EMP AS M
ON E.MGR = M.EMPNO
CROSS JOIN / CARTESIAN JOIN :-
=> cross join returns cross product or cartesian product of two tables
A = 1,2
B = 3,4
AXB = (1,3) (1,4) (2,3) (2,4)
=> if cross join performed between two tables then all records of 1st table joined with all
records of 2nd table
=> to perform cross join run the join query without join condition.
Ex :-
SELECT E.ENAME,D.DNAME
FROM EMP AS E CROSS JOIN DEPT AS D
ex :-
T1 T2
ID NAME PRICE ID NAME PRICE
1 BURGER 100 1 THUMSUP 40
2 PIZZA 160 2 JUICE 60
SELECT T1.NAME + '+' + T2.NAME , T1.PRICE + T2.PRICE AS TOTAL
FROM T1 CROSS JOIN T2
BURGER + THUMSUP 140
BURGER + JUICE 160
PIZZA + THUMSUP 200
PIZZA + JUICE 220
===========================================================================
08-aug-24
SET OPERATORS :-
UNION
UNION ALL
INTERSECT
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 performed between the records return by two queries
SELECT STATEMENT 1
UNION / UNION ALL / INTERSECT / EXCEPT
SELECT STATEMENT 2
Rules :-
1 no of columns return by both queries must be same
2 corresponding columns datatype must be same
UNION :-
=> combines rows return by two queries
=> eliminates duplicates
=> sorts result
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
SELECT JOB FROM EMP WHERE DEPTNO = 20
UNION
SELECT JOB FROM EMP WHERE DEPTNO = 30
ANALYST
CLERK
MANAGER
SALESMAN
UNION VS JOIN :-
union join
1 horizontal merge vertical merge
2 combines rows combines columns
3 performed between can be performed between
two similar structures two dissimilar structures
scenario :-
EMP_US
ENO ENAME SAL DNO
DEPT
EMP_IND DNO DNAME LOC
ENO ENAME SAL DNO
=> display total employees ?
SELECT * FROM EMP_US
UNION
SELECT * FROM EMP_IND
=> employees working at US loc with dept details ?
SELECT E.*,D.*
FROM EMP_US AS E INNER JOIN DEPT AS D
ON E.DNO = D.DNO
=> total employees list with dept details ?
SELECT E.*,D.*
FROM EMP_US AS E INNER JOIN DEPT AS D
ON E.DNO = D.DNO
UNION
SELECT E.*,D.*
FROM EMP_IND AS E INNER JOIN DEPT AS D
ON E.DNO = D.DNO
SELECT *
FROM (SELECT * FROM EMP_US
UNION
SELECT * FROM EMP_IND) AS E INNER JOIN DEPT AS D
ON E.DNO = D.DNO
UNION ALL :-
=> combines rows return by two queries
=> duplicates are not eliminated
=> result is not sorted
SELECT JOB FROM EMP WHERE DEPTNO = 20
UNION ALL
SELECT JOB FROM EMP WHERE DEPTNO = 30
CLERK
MANAGER
ANALYST
CLERK
ANALYST
SALESMAN
SALESMAN
SALESMAN
MANAGER
SALESMAN
CLERK
=> difference between union & union all ?
UNION UNION ALL
1 eliminates duplicates doesn't eliminate duplicates
2 sorts result doesn't sort result
3 slower faster
INTERSECT :-
=> returns common values from the output of two select statements
SELECT JOB FROM EMP WHERE DEPTNO = 20
INTERSECT
SELECT JOB FROM EMP WHERE DEPTNO = 30
CLERK
MANAGER
EXCEPT :-
=> returns values present in 1st query output and not present in 2nd query output
SELECT JOB FROM EMP WHERE DEPTNO = 20
EXCEPT
SELECT JOB FROM EMP WHERE DEPTNO = 30
ANALYST
SELECT JOB FROM EMP WHERE DEPTNO = 30
EXCEPT
SELECT JOB FROM EMP WHERE DEPTNO = 20
SALESMAN
=>
T1 T2
F1 C1
1 1
2 2
3 3
10 40
20 50
30 60
write the output for the following operations ?
1 INNER JOIN
2 LEFT JOIN
3 RIGHT JOIN
4 FULL JOIN
5 UNION
6 UNION ALL
7 INTERSECT
8 EXCEPT
SUB-QUERIES / NESTED QUERIES :-
=> a query in another query is called sub-query or nested query
=> one query is called inner / child / sub-query
=> other query is called outer / parent / main query
=> first sql server executes inner query then it executes outer query
=> output of inner query is input to outer query
=> use sub-queries when where cond based on unknown value
Types of sub-queries :-
1 single row sub-queries
2 multi row sub-queries
3 co-related sub-queries
4 derived tables / CTEs
5 scalar sub-query
single row sub-queries :-
=> if inner query returns one value then it is called single row sub-query
SELECT columns
FROM tabname
WHERE colname OP (SELECT statement)
=> OP must any relational operator like > >= < <= = <>
=> sub-query output acts like a value for outer query
Ex :-
=> employees earning more than blake ?
SELECT *
FROM EMP
WHERE SAL > (SELECT SAL FROM EMP WHERE ENAME='BLAKE')
=> employees who are senior to king ?
SELECT *
FROM EMP
WHERE HIREDATE < ( SELECT HIREDATE FROM EMP WHERE ENAME='KING' )
=> name of the employee earning max salary ?
SELECT ENAME,MAX(SAL) FROM EMP => ERROR
14 1
SELECT ENAME
FROM EMP
WHERE SAL = MAX(SAL) => ERROR => aggregates are not allowed in where clause
SELECT ENAME
FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP)
=> name of the employee having max experience ?
SELECT ENAME
FROM EMP
WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP)
=> display 2nd max salary ?
SELECT MAX(SAL)
FROM EMP
WHERE SAL <> (SELECT MAX(SAL) FROM EMP)
=> name of the employee earning 2nd max sal ?
SELECT ENAME
FROM EMP
WHERE SAL = (SELECT MAX(SAL)
FROM EMP
WHERE SAL <> (SELECT MAX(SAL) FROM EMP))
=> delete the employee having max experience ?
DELETE
FROM EMP
WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP)
=> increment sal of employee by 10% having max experience ?
UPDATE EMP
SET SAL = SAL+(SAL*0.1)
WHERE HIREDATE = (SELECT MIN(HIREDATE) FROM EMP)
=> swap employee salaries whose empno = 7369,7499 ?
UPDATE EMP
SET SAL = CASE EMPNO
WHEN 7369 THEN (SELECT SAL FROM EMP WHERE EMPNO=7499)
WHEN 7499 THEN (SELECT SAL FROM EMP WHERE EMPNO=7369)
END
WHERE EMPNO IN (7369,7499)
multi-row sub-queries :-
=> if sub-query returns more than one value then it is called multi-row sub-query
SELECT columns
FROM tabname
WHERE colname OP (SELECT statement)
=> OP must be IN,NOT IN,ANY,ALL
single multi
= IN
<> NOT IN
> >ANY >ALL
< <ANY <ALL
=> display employees working at NEW YORK,CHICAGO locations ?
subquery :-
SELECT ENAME
FROM EMP
WHERE DEPTNO IN (SELECT DEPTNO
FROM DEPT
WHERE LOC IN ('NEW YORK','CHICAGO'))
10
30
join :-
SELECT E.ENAME
FROM EMP AS E INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE D.LOC IN ('NEW YORK','CHICAGO')
=> display ENAME DNAME working at NEW YORK,CHICAGO locations ?
join :-
SELECT E.ENAME,D.DNAME
FROM EMP AS E INNER JOIN DEPT AS D
ON E.DEPTNO = D.DEPTNO
WHERE D.LOC IN ('NEW YORK','CHICAGO')
sub-query :-
not possible
=> Difference between sub-query and join ?
1 to display data from one table and condition based on another table then we can use sub-query or join
2 to display data from two tables then use join
10-aug-24
ANY & ALL operators :-
=> use ANY & ALL operators for > < comparision with multiple values
WHERE X > ANY(1000,2000,3000) WHERE X < ANY(1000,2000,3000)
IF X = 800 FALSE IF X = 800 TRUE
1500 TRUE 1500 TRUE
4500 TRUE 4500 FALSE
WHERE X > ALL(1000,2000,3000) WHERE X < ALL(1000,2000,3000)
IF X = 800 FALSE IF X = 800 TRUE
1500 FALSE 1500 FALSE
4500 TRUE 4500 FALSE
=> employees earning more than all managers ?
SELECT *
FROM EMP
WHERE SAL > ALL(SELECT SAL FROM EMP WHERE JOB='MANAGER')
2975
2850
2450
=> employees earning more than atleast one manager ?
SELECT *
FROM EMP
WHERE SAL > ANY(SELECT SAL FROM EMP WHERE JOB='MANAGER')
co-related sub-queries :-
=> if inner query references values of outer query then it is called co-related sub-query.
=> here execution starts from outer query and inner query is executed no of times
depends on no of rows return by outer query.
=> use co-related sub-query to execute sub-query for each row return by outer query
ex :-
EMP
EMPNO ENAME SAL DEPTNO
1 A 5000 10
2 B 3000 20
3 C 4000 30
4 D 6000 20
5 E 3000 10
=> employees earning more than avg sal of the organization ?
SELECT *
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP)
4200
=> employees earning more than avg sal of their dept ?
SELECT *
FROM EMP AS X
WHERE SAL > (SELECT AVG(SAL) FROM EMP WHERE DEPTNO = X.DEPTNO )
1 A 5000 10 5000 > (4000) TRUE
2 B 3000 20 3000 > (4500) FALSE
3 C 4000 30 4000 > (4000) FALSE
4 D 6000 20 6000 > (4500) TRUE
5 E 3000 10 3000 > (4000) FALSE
=> employees earning max sal in each dept ?
SELECT *
FROM EMP AS X
WHERE SAL = (SELECT MAX(SAL)
FROM EMP
WHERE DEPTNO = X.DEPTNO )
1 A 5000 10 5000 = (5000) TRUE
2 B 3000 20 3000 = (6000) FALSE
3 C 4000 30 4000 = (4000) TRUE
4 D 6000 20 6000 = (6000) TRUE
5 E 3000 10 3000 = (5000) FALSE
=> display top 3 max salaries ?
SELECT DISTINCT A.SAL
FROM EMP AS A
WHERE 2 = (SELECT COUNT(DISTINCT B.SAL)
FROM EMP AS B
WHERE A.SAL < B.SAL)
ORDER BY SAL DESC
EMP A EMP B
5000 5000 3 > ( 0) TRUE
3000 3000 3 > (1) TRUE
3000 3000 3 > (1) TRUE
2000 2000 3 > (3) FALSE
2975 2975 3 > (2) TRUE
=> display 3rd max salary ?
SELECT DISTINCT A.SAL
FROM EMP AS A
WHERE (3-1) = (SELECT COUNT(DISTINCT B.SAL)
FROM EMP AS B
WHERE A.SAL < B.SAL)
ORDER BY SAL DESC
12-aug-24
Derived Tables :-
=> sub-queries in FROM clause are called derived tables
SELECT columns
FROM (SELECT STATEMENT) AS E
WHERE COND
=> sub-query output acts like a table for outer query
=> derived tables are used in following scenarios
1 to control order of execution of clauses
2 to join two query outputs
3 to use result of one operation in another operation
controlling order of execution :-
=> by default sql server executes the clauses in the following order
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
=> to control this order of execution use derived table
Ex :- controlling where & order by execution
SELECT SELECT
FROM ======================> FROM (SELECT
WHERE FROM
ORDER BY ORDER BY ) AS E
WHERE
=> display ranks of the employees based on sal and highest paid 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 used 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) AS E
WHERE RNK <= 5
=> display top 3 max salaries ?
SELECT DISTINCT SAL
FROM (SELECT SAL,
DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK
FROM EMP) AS E
WHERE RNK <= 3
ORDER BY SAL DESC
=> display 3rd max sal ?
WHERE RNK = 3
=> display first 5 rows from emp table ?
SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY EMPNO ASC) AS RNO,
EMPNO,ENAME,SAL
FROM EMP) AS E
WHERE RNO<= 5
WHERE RNO IN (5,10,14)
WHERE RNO BETWEEN 5 AND 10
WHERE RNO%2 = 0
=> display last 3 rows from emp ?
SELECT *
FROM (SELECT ROW_NUMBER() OVER (ORDER BY EMPNO ASC) AS RNO,EMPNO,ENAME,SAL
FROM EMP) AS E
WHERE RNO >= (SELECT COUNT(*)-2 FROM EMP)
=> delete first 3 rows from emp ?
DELETE
FROM (SELECT ROW_NUMBER() OVER (ORDER BY EMPNO ASC) AS RNO,
EMPNO,ENAME,SAL
FROM EMP) AS E
WHERE RNO<= 3 => ERROR
NOTE :- in derived tables outer query cannot be DML and it must be always select.To
overcome this problem use CTE
14-aug-24
CTE :-
=> CTE stands for common table expression.
=> using CTE we can give name to the query output and we can use that name is another
query like SELECT/INSERT/UPDATE/DELETE.
=> In derived table outer query must be always select but in CTEs outer query can be
SELECT / INSERT / UPDATE / DELETE
=> using CTEs we can simplify the complex queries.
syntax :-
WITH <CTE-NAME1>
AS
(SELECT STATEMENT)
<CTE-NAME2)
AS
(SELECT STATEMENT)
SELECT / INSERT / UPDATE / DELETE
Example 1 :- delete first 3 rows from emp table
WITH E
AS
(SELECT ROW_NUMBER() OVER (ORDER BY EMPNO ASC) AS RNO,
EMPNO,ENAME,SAL
FROM EMP)
DELETE FROM E WHERE RNO <= 3
Example 2 :- write a query to delete duplicate rows ?
EMP44
ENO ENAME SAL
1 A 5000
2 B 6000
3 C 7000
1 A 5000
2 B 6000
STEP 1 :- generfate row numbers with in group of eno,ename,sal
SELECT ENO,ENAME,SAL,
ROW_NUMBER() OVER (PARTITION BY ENO,ENAME,SAL ORDER BY ENO ASC)
AS RNO
FROM EMP44
1 A 5000 1
1 A 5000 2
2 B 6000 1
2 B 6000 2
3 C 7000 1
STEP 2 :- delete the records whose rno > 1
WITH E
AS
(SELECT ENO,ENAME,SAL,
ROW_NUMBER() OVER (PARTITION BY ENO,ENAME,SAL ORDER BY ENO ASC)
AS RNO
FROM EMP44)
DELETE FROM E WHERE RNO > 1
Example 3 :-
T1 T2
F1 C1
10 A
20 B
30 C
OUTPUT :-
10 A
20 B
30 C
SELECT ROW_NUMBER() OVER (ORDER BY F1 ASC) AS RNO ,F1
FROM T1
1 10
2 20
3 30
SELECT ROW_NUMBER() OVER (ORDER BY C1 ASC) AS RNO ,C1
FROM T2
1 A
2 B
3 C
WITH A
AS
(SELECT ROW_NUMBER() OVER (ORDER BY F1 ASC) AS RNO ,F1
FROM T1) ,
B
AS
(
SELECT ROW_NUMBER() OVER (ORDER BY C1 ASC) AS RNO ,C1
FROM T2
)
SELECT A.F1,B.C1
FROM A INNER JOIN B
ON A.RNO = B.RNO
Example 4 :-
T1
AMT
100
-500
2000
-300
5000
-1000
OUTPUT :-
POS NEG
100 -500
2000 -300
5000 -1000
scalar sub-queries :-
=> sub-queries in select clause are called scalar sub-queries.
SELECT (sub-query1) , (sub-query2),
FROM tabname
WHERE cond
=> sub-query output acts like a column for outer query
=> use scalar sub-query to show query output in seperate column
Example 1 :-
SELECT (SELECT COUNT(*) FROM EMP) AS EMP ,
(SELECT COUNT(*) FROM DEPT) AS DEPT
OUTPUT :-
EMP DEPT
15 4
Example 2 :-
=> display dept wise total salary ?
SELECT DEPTNO,SUM(SAL) AS TOTSAL
FROM EMP
GROUP BY DEPTNO
10 8750.00
20 10875.00
30 9400.00
=> display DEPTNO DEPT_TOTSAL TOTSAL ?
SELECT DEPTNO,SUM(SAL) AS DEPT_TOTSAL ,
(SELECT SUM(SAL) FROM EMP) AS TOTSAL
FROM EMP
GROUP BY DEPTNO
10 8750.00 29025
20 10875.00 29025
30 9400.00 29025
=> display DEPTNO DEPT_TOTSAL TOTSAL PCT ?
PCT = ( DEPT_TOTSAL / TOTSAL ) * 100
SELECT DEPTNO,SUM(SAL) AS DEPT_TOTSAL ,
(SELECT SUM(SAL) FROM EMP) AS TOTSAL ,
(SUM(SAL) / (SELECT SUM(SAL) FROM EMP))*100 AS PCT
FROM EMP
GROUP BY DEPTNO
simple select
where
order by
distinct
top
functions
group by
joins
set operators
sub-queries
=========================================================================
16-aug-24
PIVOT :-
=> operator used to convert rows into columns
=> used to display data in matrix form
=> used for cross-tabulation
Syntax :-
SELECT *
FROM (SELECT required data) AS <ALIAS>
PIVOT
(
AGGR-EXPR FOR COLNAME IN (V1,V2,V3,
) AS <PIVOT-TBL-NAME>
ORDER BY COL ASC/DESC
Ex :-
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
Ex 2 :-
1 2 3 4
1980 ? ? ? ?
1981 ? ? ? ?
1982 ? ? ? ?
1983 ? ? ? ?
SELECT *
FROM (SELECT DATEPART(YY,HIREDATE) AS YEAR,
DATEPART(QQ,HIREDATE) AS QRT,
EMPNO
FROM EMP) AS E
PIVOT
(
COUNT(EMPNO) FOR QRT IN ([1],[2],[3],[4])
) AS PIVOT_TBL
ORDER BY YEAR ASC
Ex 3 :-
STUDENT
SNO SNAME SUBJECT MARKS
1 A MAT 80
1 A PHY 60
1 A CHE 50
2 B MAT 60
2 B PHY 70
2 B CHE 50
OUTPUT :-
SNO SNAME MAT PHY CHE
1 A 80 60 50
2 B 60 70 50
CREATING NEW TABLE FROM EXISTING TABLE (replica) :-
SELECT COLUMNS INTO <NEW-TABNAME>
FROM <OLD-TABNAME>
[WHERE COND]
Ex 1 :- copying all rows and all columns
SELECT * INTO EMP10
FROM EMP
=> a new table is created with name EMP10 and whatever columns and rows in emp table
are copied to emp10
Ex 2 :- copying specific rows & cols
SELECT EMPNO,ENAME,JOB,SAL INTO EMP11
FROM EMP
WHERE JOB IN ('CLERK','MANAGER')
Ex 3 :- copy only structure ( columns) but not data (rows)
SELECT * INTO EMP13
FROM EMP
WHERE 1=2
EX 4 :- copy table from one db to another db
copy EMP table from DB730 db TO HR db ?
SELECT * INTO HR.DBO.EMP
FROM DB730.DBO.EMP
copying data from one table to another table :-
INSERT INTO <target-table>
SELECT columns FROM <source-table> [WHERE cond]
Ex :-
=> copy data from emp to emp13 ?
INSERT INTO EMP13
SELECT * FROM EMP
17-aug-24
MERGE command :-
=> merge is the combination of insert,update and delete.
=> command used to manage replicas.
=> using merge we can apply changes made to one table to another table.
syntax :-
MERGE INTO <target-table> AS <alias>
USING <source-table> AS <alias>
ON (condition)
WHEN MATCHED THEN
UPDATE
WHEN NOT MATCHED THEN
INSERT
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
Ex :-
step 1 :- create source table
CREATE TABLE CUSTS
(
CID INT,
CNAME VARCHAR(10),
ADDR CHAR(3)
)
INSERT INTO CUSTS VALUES(10,'A','HYD'),(11,'B','MUM')
step 2 :- create target table (replica)
SELECT * INTO CUSTT FROM CUSTS
step 3 :- modify source table table
INSERT INTO CUSTS VALUES(12,'C','DEL')
UPDATE CUSTS SET ADDR='BLR' WHERE CID=10
step 4 :- apply changes made to custs to custt
MERGE INTO CUSTT AS T
USING CUSTS AS S
ON (S.CID = T.CID)
WHEN MATCHED THEN
UPDATE SET T.ADDR = S.ADDR
WHEN NOT MATCHED THEN
INSERT VALUES(S.CID,S.CNAME,S.ADDR)
WHEN NOT MATCHED BY SOURCE THEN
DELETE ;
==========================================================================
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
update1 update2
(bal = bal - 1000) (bal = bal + 1000)
successful failed invalid
failed successful invalid
successful successful valid
failed failed valid
=> every db transaction must gurantee a property called atomocity i.e. all or none
if transaction contains multiple operations if all are successful then it must be
saved , if one of the operation fails then entire transaction must be cancelled.
=> the following commands provided by sql server to handle transactions called
TCL commands.
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 command and ends implicitly with commit
user :- sql server :-
begin transaction
update update
commit
update1 begin transaction
update2 update1
commit
begin transaction
update2
commit
=> a user can also start transaction by executing "begin transaction" command
and ends transaction either with commit / rollback command
example 1 :-
create table a(a int) => implicitly committed
begin transaction => txn begins T1
insert into a values(10)
insert into a values(20)
insert into a values(30)
insert into a values(40)
rollback => txn ends
=> if txn ends with rollback then it is called aborted transaction and operations are
cancelled
create table => saved
inserts => cancelled
example 2 :-
create table a(a int) => implicitly committed
begin transaction => txn begins T1
insert into a values(10)
insert into a values(20)
commit => txn ends
insert into a values(30) => implicitly committed
insert into a values(40) => implicitly committed
rollback => trying to end txn without starting
select * from a
10
20
30
40
21-aug-24
DB Objects :-
TABLES
VIEWS
SYNONYMS
SEQUENCES
INDEXES
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
=> views are 2 types
1 simple views
2 complex views
simple views :-
=> a view said to be simple view if based on single table
syn :-
create view <name>
as
select statement
ex :-
create view v1
as
select empno,ename,job,hiredate,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 it executes the query as follows
select * from (select empno,ename,job,hiredate,deptno from emp)
Granting permissions on view to user :-
DBO :-
GRANT SELECT,INSERT,UPDATE,DELETE ON V1 TO VIJAY
VIJAY :-
1 SELECT * FROM V1
2 UPDATE V1 SET JOB='MANAGER' WHERE EMPNO = 7369
3 UPDATE V1 SET SAL = 3000 WHERE EMPNO = 7369 => ERROR
ROW LEVEL SECURITY :-
DBO :-
CREATE VIEW V2
AS
SELECT EMPNO,ENAME,JOB,HIREDATE,DEPTNO
FROM EMP
WHERE DEPTNO = 20
GRANT SELECT,INSERT,UPDATE,DELETE ON V2 TO VIJAY
complex views :-
=> a view said to be complex view
1 if based on multiple tables
2 if query contains group by clause
having clause
aggregate functions
set operators
sub-queries
=> view reduces complexity i.e. with the help of views complex queries
can be converted into simple queries.
ex 1 :-
create view cv1
as
select e.empno,e.ename,e.sal,
d.deptno,d.dname,d.loc
from emp as e inner join dept as d
on e.deptno = d.deptno
=> after creating view whenever we want data from emp & dept tables then
instead of writing complex join query write the simple query as follows
select * from cv1
ex 2 :-
create view cv2
as
select d.dname,
min(e.sal) as minsal,
max(e.sal) as maxsal,
sum(e.sal) as totsal,
count(*) as emps
from emp as e inner join dept as d
on e.deptno = d.deptno
group by d.dname
=> after creating view whenever we want dept wise summary then execute the
following query
select * from cv2
=> difference between simple and complex views ?
simple complex
1 based on single table based on multiple tables
2 query performs simple query performs complex
operations operations like joins,group by etc
3 simple views are updatable not updatable
i.e. allows dmls i.e. doesn't allow dmls
=> list of tables & views ?
SELECT * FROM INFORMATION_SCHEMA.TABLES
=> list of views ?
SELECT * FROM INFORMATION_SCHEMA.VIEWS
Droping view :-
---------------------
DROP VIEW V1
22-aug-24
synonyms :-
----------------
=> a synonym is another name or alternative name for a table or view
=> if tablename is lengthy then we can give a simple and short name to the table
called synonym and instead of using tablename we can use synonym name
in select / insert / update / delete queries.
syn :- create synonym <name> for <tabname>
ex :- create synonym e for emp
=> after creating synonym use synonym name in select / insert / update / delete queries
1 select * from e
2 update e set sal = 2000 where empno = 7369
accessing tablenames without schema name :-
---------------------------------------------------------------
CREATE SCHEMA SALES
CREATE TABLE SALES.PRODUCTS
(
PRODID INT,
PNAME VARCHAR(10)
)
SELECT * FROM SALES.PRODUCTS
=> everytime prefixing tablename with schema name is difficult , so create synonym for table
CREATE SYNONYM SPROD FOR SALES.PRODUCTS
=> after creating syonym we can access table as follows
SELECT * FROM SPROD
=> list of synonyms created by user ?
SELECT name,base_object_name FROM SYS.SYNONYMS
Droping synonym :-
--------------------------
DROP SYNONYM E
SEQUENCES :-
---------------------
=> a sequence is a db object created to generate sequence numbers
=> used to auto increment column values
syn :-
CREATE SEQUENCE <NAME>
[START WITH <VALUE>]
[INCREMENT BY <VALUE>]
[MAXVALUE <VALUE>]
[MINVALUE <VALUE>]
[CYCLE]
creating sequence :-
--------------------------------
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
SELECT * FROM STUDENT
SID SNAME
1 A
2 B
3 C
4 D
5 E
Example 2 :- calling sequence in update command
CREATE SEQUENCE S2
START WITH 100
INCREMENT BY 1
MAXVALUE 999
=> use above sequence to update empno ?
UPDATE EMP SET EMPNO = NEXT VALUE FOR S2
How to reset sequence :-
----------------------------------
1 using alter command
2 using cycle option
using alter command :-
--------------------------------
ALTER SEQUENCE S1 RESTART WITH 1
using cycle option :-
----------------------------
=> by default sequence created with NOCYCLE i.e. after reaching max then it stops
=> if sequence created with CYCLE then after reaching max then it will be reset to min
CREATE SEQUENCE S3
START WITH 1
INCREMENT BY 1
MAXVALUE 5
MINVALUE 1
CYCLE
list of sequences ?
SELECT * FROM INFORMATION_SCHEMA.SEQUENCES
Droping sequence :-
----------------------------
DROP SEQUENCE S1
IDENTITY :-
-----------------
=> used to generate sequence numbers
=> used to auto increment column values
IDENTITY(SEED,INCR)
Ex :-
CREATE TABLE CUST
(
CUSTID INT IDENTITY(100,1) ,
CNAME VARCHAR(10)
)
INSERT INTO CUST(CNAME) VALUES ('A')
INSERT INTO CUST(CNAME) VALUES ('B')
INSERT INTO CUST(CNAME) VALUES ('C')
SELECT * FROM CUST
CID CNAME
100 A
101 B
102 C
=> difference between sequence & identity ?
identity sequence
1 bind to specific column not bind any column
in a table
2 not declared with maxvalue declared with maxvalue
3 can't access identity next value can access sequence next value
4 can't be reset can be reset
what is db ?
a db is a organized colllection of interrelated data
A data base is a palce where you store your business data
Logicaly data base is a collection of table
fhysically database is a collection of files
what is dbms ?
=> DBMS stands for Database Management System.
=> It is a software used to manage database.
=> It is an interface between user and database.
USER
what is rdbms ?
=> RDBMS is based on relational model (structure of the data)
=> relational model introduced by E.F.CODD
=> E.F.CODD introduced 12 rules called codd rules
=> a db that supports all 12 rules called perfect rdbm
what is ordbms ?
Object relational database manegment sysytem
It is the combination of rdbms & oops
ORDBMS = RDBMS + OOPS (reusability)
what is db development life cycle ?
Analyze
Design
Develop
Test
Deploy
Maintain
