SQL Notes

SQL TRAINING WORKBOOK
SKKP ENTERPRISE CORP

KATY, TX 77494.
Contents
1. INTRODUCTION ......................................................................................................................................... 3
2. Installation ................................................................................................................................................ 6
3. SQL BASICS [INTRODUCTION] ................................................................................................................. 20
BASIC TYPES OF SQL STATEMENTS: ........................................................................................................ 23
SQL Comparison Operators..................................................................................................................... 24
SQL Logical Operators ............................................................................................................................. 25
SQL Operator Precedence....................................................................................................................... 26
SQL Data types ........................................................................................................................................ 26
MS SQL Server string DATA TYPES .......................................................................................................... 27
MS SQL Server numeric DATA TYPES ...................................................................................................... 27
MS SQL Server date & time DATA TYPES ................................................................................................ 28
DDL and DML EXERCISE .......................................................................................................................... 28
SCHEMA AND TABLES ............................................................................................................................. 34
SCHEMA’S EXERCISE................................................................................................................................ 34
TEMP TABLES EXERCISE .......................................................................................................................... 39
SQL Constraints ....................................................................................................................................... 40
Primary Key Constraint ........................................................................................................................... 41
Foreign Key Constraint ............................................................................................................................ 41
Not Null Constraint ................................................................................................................................. 42
Unique Key Constraint ............................................................................................................................ 42
Default Value Constraint ......................................................................................................................... 43
Check Value Constraint ........................................................................................................................... 43
Index Constraint ...................................................................................................................................... 44
pg. 1
CONSTRAINTS EXERCISE.......................................................................................................................... 44
SQL JOINS ................................................................................................................................................ 48
SQL INNER JOIN....................................................................................................................................... 49
SQL LEFT JOIN.......................................................................................................................................... 49
SQL RIGHT JOIN ....................................................................................................................................... 50
SQL FULL OUTER ..................................................................................................................................... 50
SQL SELF JOIN.......................................................................................................................................... 51
JOIN EXERCISE ......................................................................................................................................... 52
SQL VIEWS ............................................................................................................................................... 60
SQL VIEWS Exercise................................................................................................................................. 61
SQL STORED PROCEDURES...................................................................................................................... 65
SQL STORED PROCEDURES EXERCISE...................................................................................................... 67
SQL USER DEFINED FUNCTIONS .............................................................................................................. 68
SQL USER DEFINED FUNCTIONS EXERCISE .............................................................................................. 68
pg. 2
1. INTRODUCTION
1. WHAT IS DATABASE?
A PLATFORM TO STORE ANY TYPE OF DATA. ANY AMOUNT OF DATA.
2. TYPES OF DATABASES?
Type of Database Purpose Examples

OLTP : Online Transaction Processing Database LIVE, Real-time SQL Dev LIVE Temperature
OLAP : Online Analytical Processing Database Analysis, Forecasts Weather Forecasts
DWH : Data Warehouse Database Old, Historical Data BI Past Temperature Data
HTAP: Hybrid Transactional Analytical Database All Above in one DB Google Map
3. WHAT IS RDBMS ? Relational DATABASE MANAGEMENT SYSTEM

A SOFTWARE USED TO STORE AND MANAGE DATABASES. EXAMPLES FOR DBMS: SQL SERVER, DB2,
ORACLE, SYBASE, INGRES........
MS OFFICE (SOFTWARE) > EXCEL FILE > SHEETS

SQL SERVER (SOFTWARE) > DATABASE > TABLES & CUBES
4. WHAT IS SQL SERVER?

A DATABASE MANAGEMENT SYSTEM (DBMS SOFTWARE) TO STORE DATABASES OF ANY TYPE, ANY SIZE.
UNLIMITTED CONNECTIONS. CHEAPER (14500 USD), EASY USAGE, FREE SUPPORT, FREE UPDATES,
FREE BI TOOLS [FOR BIG DATA ANALYTICS, FORECASTS, REPORTING]
5. WHAT IS CLOUD?
pg. 3
A PLATFORM USED TO STORE ANY TYPE OF DATA REMOTELY EX: GOOGLE DOC EX: MOBILE CONTACTS
6. WHAT IS AZURE?
A MICROSOFT CLOUD PLATFORM TO STORE BIG DATA AND PERFORM DATA ANALYTICS. CHEAPER, EASY
TO USE, CODELESS ARCHITECTURE.
7. VERSIONS OF SQL SERVER?

SQL SERVER 2019, SQL SERVER 2017, SQL SERVER 2016, 2014, 2012.....
Windows 7, 8, 8.1, 10 ......
8. EDITIONS OF SQL SERVER?

DATA CENTER, ENTERPRISE EDITION, EVALUATION EDITION (6 M),
Wind Ent, Prof, Home, Ulti.. STANDARD EDITION, DEVELOPER EDITION (FOR LIFE), WEB EDITION....
9. WHAT IS SQL? STRUCTURED QUERY LANGUAGE. USED TO COMMUNICATE, PERFORM DATA

OS STORAGE, RETREIVAL OPERATIONS ON ANY DATABASE. FOR DEV, TEST, ADMIN.
10. WHAT IS T-SQL? TRANSACT SQL. USED TO COMMUNICATE WITH DATABASES STORED IN
WIN MICROSOFT SQL SERVER. FOR DEV, TEST, ADMIN.
pg. 4
11. CAREER OPTIONS?
1. SQL DEVELOPERS INVOLVED IN DESIGN OF OLTP DATABASES. 100% CODING.

ONLINE TRANSACTION PROCESSING DATABASES. LIVE, REAL-TIME
2. MSBI DEVELOPERS INVOLVED IN DESIGN OF OLAP & DWH DATABASES + REPORTS

MICROSOFT BUSINESS INTELLIGENCE.
3. DATA ANALYSTS DESIGN OF REPORTS, DASHBOARDS WITH POWER BI CLOUD

Microsoft Certification: DA 100
4. DATA ENGINEERS DESIGN & MANAGE DWH + ETL WITH AZURE DATA ENGINEER
Microsoft Certification: DP 203
5. AZURE BI OLTP + OLAP + DWH IN ON-PREMISE [NON CLOUD] + AZURE

SQL SERVER + MSBI + POWER BI + AZURE DATA ENGINEER.
pg. 5
2. Installation
STEP 1: SQL Server Installation
https://sqlschool.com/sql-server-downloads/
Download and install sql server 2019
pg. 6
pg. 7
pg. 8
pg. 9
pg. 10
pg. 11
CLICK “ADD Current User” as administrator
pg. 12
pg. 13
STEP 2: SQL Server Installation [FOR 2ND SERVER]
From your downloaded SQL Server > Double Click the Setup file once again and repeat above
Installation Process. Give a different name for your installation [Instance Name]
Optional: Select DEVELOPER Edition during the Installation.
Instance: A successful installation of SQL Server.

We can install up to 50 times per OS. Each instance has its own name.
Types of Instances: DEFAULT INSTANCE NAMED INSTANCE

------------------------------------------------------------------------------------
Instance Name MSSQLSERVER Defined during Installation
Server Name Computer Name ComputerName\InstanceName
Communication Port 1433 Auto defined by Operating System
Databases per Instance 32767 Databases 32767 Databases
Number of Installations Up to 1 per OS Up to 50 Instances per OS
Real-time Usage Production Development & Testing
SERVICE: A communication path between Operating System and SQL Server.

Service is responsible to allocate Processor & Memory to perform
the day to day Database Operations.
SERVICE ACCOUNT: A valid User from your Operating System to manage the service.
During installation, one service account is auto created.
Authentication: Communication Path between SQL Server and Users (Customers).
Authentication Types:
Windows Authentication : Only Windows Users can connect to SQL Server
SQL Authentication : Any user can connect to SQL Server. For Linux, Mac..
Authentication Modes:
Windows Authentication Mode : Only Windows Users are supported to connect
Mixed Mode *** : Windows Users and SQL Server Users supported
To work with above Authentications, we need "LOGINS".

We need "LOGINS" to Authenticate / Connect to SQL Server.
pg. 14
pg. 15
pg. 16
pg. 17
STEP 3: INSTALL SSMS TOOL [SQL SERVER MANAGEMENT STUDIO]
Download from https://sqlschool.com/downloads/
Double click SSMS Setup File > Install.
Restart your Computer.
STEP 4: HOW TO TEST THE INSTALLATIONS ?

WINDOWS APPS > SEARCH > TYPE "SSMS" > CLICK > PROMPT FOR SERVER NAME > BROWSE FOR MORE
> EXPAND DATABASE ENGINE > SELECT ABOVE INSTALLED SERVER (1) > OK > CONNECT.
FOR 2ND SERVER : LEFT: CONNECT > DATABASE ENGINE > DROP DOWN : BROWSE FOR MORE >
EXPAND DATABASE ENGINE > SELECT ABOVE INSTALLED SERVER (2) > OK > CONNECT.
pg. 18
Component Purpose
1 Database Engine Supports Real-time, Rapid Processing (OLTP) Databases. We use SQL.
2 Analysis Services Supports Analytical Databases for Big Data Analysis, Forecasts
(ONLINE ANALYTICAL PROCESSING DATABASE : OLAP)
pg. 19
3 Integration Services For Design of Databases for Old, Historical Data (DATA WAREHOUSE : DWH)
4 Reporting Services For end to end implementation of Report Design, Storage, Access.
5 Machine Learning Python and R. Used to implement Machine Learning for Data Scientists
3. SQL BASICS [INTRODUCTION]
1. WHAT IS DATABASE?
2. WHAT IS DBMS?
3. WHAT IS SQL SERVER?
4. ADVANTAGES OF SQL SERVER?
5. VERSIONS OF SQL SERVER?

6. EDITIONS OF SQL SERVER?
7. HOW TO INSTALL SQL SERVER? TO STORE DATA

8. HOW TO INSTALL SSMS TOOL? TO OPERATE ON THE SERVER [DDL, DML, SELECT....]
9. HOW TO TEST THE CONNECTIONS TO SERVER FROM SSMS TOOL?
10. HOW TO CREATE DATABASES?

11. HOW TO CREATE TABLES?
12. HOW TO INSERT DATA INTO TABLES?
13. HOW TO REPORT (VERIFY) DATA FROM TABLES?
pg. 20
SQL : STRUCTURED QUERY LANGUAGE. A PLATFORM USED TO COMMUNICATE WITH ANY
OS DATABASE. FOR DEVELOPMENT, TESTING AND ADMINISTRATION (MANAGEMENT).
T-SQL : TRANSACT SQL. A TYPE OF SQL EXCLUSIVELY USED FOR COMMUNICATING WITH
WIN DATABASES STORED IN MICROSOFT SQL SERVER (MS SQL SERVER).
BASIC PRACTICE EXAMPLES USING GUI [USER INTERFACE]
ITEM #1: HOW TO CREATE NEW DATABASE IN YOUR LOCAL SQL SERVER?
WINDOWS APPS SEARCH > TYPE "SSMS" > LAUNCH SSMS > THIS PROMPTS FOR SERVER
NAME > IN THE DROPDOWN > BROWSE FOR MORE > EXPAND DATABASE ENGINE >
SELECT REQUIRED SERVER > OK > CONNECT.
RIGHT CLICK DATABASES FOLDER > NEW DATABASE > SPECIFY A DATABASE NAME > OK.
THEN TWO FILES ARE AUTO CREATED IN YOUR COMPUTER INSTALLATION LOCATION
1. DATA FILE = ROWS DATA FILE. USED TO STORE TABLE DATA
FILE TYPE : MDF = PRIMARY DATA FILE
NDF
2. LOG FILE = TRANSACTION LOG FILE. TO MONITOR THE DATABASE.
FILE TYPE : LDF = LOG DATA FILE
ITEM #2: HOW TO CREATE NEW TABLE INSIDE ABOVE DATABASE?

FROM SSMS TOOL: EXPAND ABOVE CREATED DATABASE > TABLES FOLDER : RIGHT
CLICK > NEW TABLE > THEN WE SEE ONE FORM. SPECIFY BELOW COLUMN NAMES:
PRODUCT_ID > HIT ENTER
PRODUCTNAME > HIT ENTER
PRODUCT PRICE > HIT ENTER
Product_quantity →
pg. 21
RIGHT CLICK TOP YELLOW RIBBON > SAVE > SET TABLE NAME > OK.
Save the table as “product_info”
ITEM #3: HOW TO VERIFY ABOVE TABLE IN THE DATABASE?

FROM SSMS > SERVER > DATABASE > TABLES FOLDER > RIGHT CLICK > REFRESH.
ITEM #4: HOW TO INSERT (STORE) DATA INTO ABOVE TABLE?

RIGHT CLICK TABLE > EDIT TOP 200 ROWS > FILL THE COLUMN VALUES (LIKE EXCEL).
EACH ROW IS AUTO SAVED ONCE WE MOVE THE MOUSE (CURSOR) OUT OF THE ROW.
DEFAULT VALUE FOR EVERY CELL IS "NULL". NULL MEANS AN UNDEFINED VALUE. OR
UKNOWN VALUE. NOTE: NULL IS DIFFERENT FROM ZERO AND SPACE.
ITEM #5: HOW TO REPORT DATA FROM ABOVE TABLE?

RIGHT CLICK TABLE > SELECT TOP 1000 ROWS.
ITEM #6: LIMITATIONS WITH GUI [GRAPHICAL USER INTERFACE] IN SSMS TOOL?
1. ONLY 200 ROWS CAN BE EDITTED AT A TIME
2. ONLY 1000 ROWS CAN BE REPORTED A TIME
TO ADDRESS ALL ABOVE LIMITATIONS, WE USE SQL.
----------
ITEM #7: HOW TO CREATE DATABASE USING SQL?
FROM SSMS TOOL: RIGHT CLICK SERVER > NEW QUERY > THEN YOU SEE A "SESSION".
TYPE BELOW COMMAND. SELECT THE COMMAND AND CLICK @ EXECUTE.
CREATE DATABASE ProductDB
ITEM #8: HOW TO CONNECT TO ABOVE DATABASE USING SQL?

USE ProductDB
ITEM #9: HOW TO CREATE TABLE INSIDE ABOVE DATABASE USING SQL?
SYNTAX:
CREATE TABLE <<TABLENAME>>
(
<<Column1Name>> <<datatype>>,
....... max: upto 1023 columns
)
DataType specifies the type of value. Example: Digits, Alphabets, Date, Time, etc..
SELECT ALL BELOW FIVE LINES TOGETHER >>> THEN EXECUTE
pg. 22
CREATE TABLE tblStore
(
ProductID INTEGER, -- THIS COLUMN STORES DIGITS [0, -9, 99..]
ProductName CHAR(30) -- THIS COLUMN STORES UPTO 30 DIGITS, ALPHA, SYMBOLS
)
ITEM #10: HOW TO STORE DATA INTO THE TABLE ?

RULE: SINGLE QUOTES MANDATORY FOR ALPHABETS, SYMBOLS.
SINGLE QUOTES ARE OPTIONAL FOR DIGITS.
INSERT INTO tblStore VALUES (1001, 'CAPS');
INSERT tblStore VALUES (1002, 'CHAINS')

insert tblStore VALUES (1003, 555);
ITEM #11: HOW TO REPORT DATA FROM THE TABLE USING SQL ?
SELECT * FROM tblStore; -- * means to report all columns
ITEM #12: HOW TO SAVE OR STORE THE ABOVE SQL QUERY IN YOUR PC FOR FUTURE USE?
TOP : FILE MENU > SAVE OR SAVE AS > SPECIFY YOUR DISK LOCATION.
BASIC TYPES OF SQL STATEMENTS:
DATA DEFINITION LANGUAGE [DDL] : STRUCTURE (CONTAINER)
CREATE To define new Databases and Tables
ALTER To modify structure of existing Databases and Tables
DROP To remove existing Databases and Tables
DATA MANUPULATION LANGUAGE [DML] : DATA (CONTENT)
INSERT To store new data into tables
UPDATE To modify or change existing data in the tables
DELETE To remove existing data from the tables
SELECT Used to Report data from Table +++ Create Tables +++ Insert Data
In later classes we have other SQL Commands to practice: TCL, DCL, etc...
During Installation of SQL Server, a set of FIVE System Databases are auto created for each instance :
pg. 23
1. master : Used to manage the connections to SQL Server and other databases
2. model : Used as a template for new databases we create on the server
3. msdb : Used by SQL DBAs for Jobs, Alerts, Monitoring, Errors and Repairs
4. tempdb : Used by Developers for Temporary Data Storage and Faster Data Access
5. resource : Hidden, internally used by master db to control SQL Server Files in OS.
Resource Database controls the Installation Files.
SQL Comparison Operators
SKK E C R C M
pg. 24
SQL Logical Operators
SKK E C R C M
pg. 25
SQL Operator Precedence
SKK E C R C M
SQL Data types
SKK E C R C M
pg. 26
MS SQL Server string DATA TYPES
Data t pe Descrip on
It holds the character strin ith the ed idth Ma i u si e of this data t pe
char n is characters
It holds the character strin ith the variable idth Ma i u si e of this data
varchar n t pe is also characters
It holds the character strin ith the variable idth Ma i u si e of this data
varchar a t pe is characters
It holds the character strin ith the variable idth his data t pe can store up to
te t a i u of te t data
It holds the Unicode strin ith the ed idth Ma i u si e of this data t pe is
nchar also characters
It holds the Unicode strin ith the variable idth Ma i u si e of this data
nvarchar t pe is also characters
It holds the Unicode strin ith the variable idth his data t pe can store up to
nte t a i u of te t data
binar n It holds the binar strin ith the ed idth
It holds the binar strin ith variable idth Ma i u si e of this data t pe is
varbinar also b tes
It holds the binar strin of a len th of variable idth Ma i u si e of this
varbinar a data t pe is
It holds the variable len th of the data t pe that can store binar data Ma i u
i a e si e of this data t pe is
It holds the Unicode strin of a len th of variable idth Ma i u si e of this
varchar a data t pe is characters
SKK E C R C M
MS SQL Server numeric DATA TYPES
bit It holds the inte er that can be or ULL
n int It allo to holds the hole nu ber fro to
s allint It allo to holds the nu ber bet een and
int It allo to holds the hole nu ber bet een and
It allo to holds the hole nu ber bet een and
bi int
It is ed precision and scale nu bers hat allo nu bers fro to
he p para eter indicates the a i u total nu ber of di its that can be stored on both
sides of the deci al point le and ri ht It ust have a value fro to default it is
deci al p s
he s para eter indicates the a i u nu ber of the di it to the ri ht of the deci al
point S ust be a value fro to p he value is set to b default
It is ed precision and scale nu bers hat allo nu bers fro to
he p para eter indicates the a i u total nu ber of di its that can be stored on both
sides of the deci al point le and ri ht It ust have a value fro to default it is
nu eric p s
he s para eter indicates the a i u nu ber of the di it to the ri ht of the deci al
point S ust be a value fro to p he value is set to b default
s all one It holds the onetar data fro to
It holds the onetar data fro to
Mone
It holds or store the oa n precession nu ber data fro E to E
loat n he n para eter indicates hether the eld should hold or b tes loat contains a
b te eld hile loat contains an b te eld he default value of n is
real It holds the oa n precision nu ber data fro E to E
SKK E C R C M
pg. 27
MS SQL Server date & time DATA TYPES
It stores date and e both fro anuar to Dece ber ith an accurac of
illiseconds
It stores date and e both fro anuar to Dece ber ith an accurac of
date e
nanoseconds
It stores date and e both fro anuar to une ith an accurac of
s alldate e
inute
It stores date onl fro anuar to Dece ber
It store e onl to an accurac of nanoseconds
date eo set It is the sa e of the date e ith the addi on of the e one o set
It stores the uni ue nu ber that ets updated ever e a ro ets created or odi ed It
esta p does not correspond to real e and is based on internal e Each table a have onl one
esta p variable
SKK E C R C M
DDL and DML EXERCISE
-- QUERY 1: HOW TO CREATE NEW DATABASE?
CREATE DATABASE Employee_Database
-- QUERY 2: HOW TO CONNECT TO ABOVE DATABASE?
USE Employee_Database
-- QUERY 3: HOW TO CREATE TABLE IN ABOVE DATABASE?
-- EVERY TABLE NEEDS MINIMUM 1 COLUMN. MAXIMUM UPTO 1024 COLUMNS.
-- EVERY COLUMN NEEDS A NAME, DATA TYPE.
CREATE TABLE EMPLOYEE_INFO
EMP_ID INT, -- THIS COLUMN STORES DIGITS [0, -9, 99, ETC...]
pg. 28
EMP_NAME CHAR(30), -- THIS COLUMN STORES UPTO 30 CHARACTERS [DIGITS, SYMBOLS,
ALPHABETS]. FIXED DATA. USER NAME. GENDER
EMP_COUNTRY VARCHAR(40),-- THIS COLUMN STORES UPTO 40 CHARACTERS [DIGITS, SYMBOLS,

ALPHABETS]. VARYING DATA. PASSWORD. LOCATION
EMP_SAL INTEGER -- THIS COLUMN STORES DIGITS [0, -9, 99, ETC...]
-- QUERY 4: HOW TO STORE OR INSERT DATA INTO ABOVE TABLE?
-- A COLLECTION OF SQL STATEMENTS SUBMITTED FOR EXECUTION IS CALLED A "BATCH".
INSERT INTO EMPLOYEE_INFO VALUES (1001, 'SAI', 'CANADA', 999999)
INSERT INTO EMPLOYEE_INFO VALUES (1002, 'SAM', 'CANADA', 888888)
INSERT INTO EMPLOYEE_INFO VALUES (1003, 'JON', 'CANADA', 999999);
INSERT INTO EMPLOYEE_INFO VALUES (1004, 'AMI', 'CANADA', 777777);
INSERT INTO EMPLOYEE_INFO VALUES (1005, 'JOE', 'USA', 999999), (1006, 'ANI', 'USA', 999999);
GO
INSERT INTO EMPLOYEE_INFO VALUES (1007, 'AMI', 'USA', 777777), (1008, 'AMIN', 'INDIA', 999999);
INSERT INTO EMPLOYEE_INFO VALUES (1009, 'KIR', 'INDIA', 777777)
INSERT INTO EMPLOYEE_INFO VALUES (1010, 'SAI', 'CANADA', 9999999)
-- STATEMENTS ABOVE "GO" FORMS ONE BATCH. STATEMENTS BELOW "GO" FORMS ANOTHER BATCH.
-- FROM ABOVE: WE HAVE TWO BATCHES. WE USE BATCHES IN REAL-WORLD TO "GROUP" 100s & 1000s
OF LINES TOGTHER.
-- ADVANTAGE OF BATCH: WHEN 1ST BATCH IS UNDER EXECUTION, 2ND BATCH GETS "READY" FOR
EXECUTION. LOOKING FOR QUERY RESOURCES: PROCESSOR, MEMORY
-- QUERY 5: HOW TO REPORT DATA FROM ABOVE TABLE?
SELECT * FROM EMPLOYEE_INFO -- *

MEANS TO REPORT ALL COLUMNS
pg. 29
-- QUERY 6: HOW TO REPORT LIST OF EMPLOYEES FROM CANADA?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_COUNTRY = 'CANADA' -- "WHERE" IS A KEYWORD

(PREDEFINED) TO SPECIFY CONDITIONS TO REPORT DATA
-- QUERY 7: HOW TO REPORT LIST OF EMPLOYEES FROM USA?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_COUNTRY = 'USA'
-- QUERY 8: HOW TO REPORT LIST OF EMPLOYEES EITHER FROM CANADA OR FROM USA?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_COUNTRY = 'CANADA' OR EMP_COUNTRY = 'USA'
SELECT * FROM EMPLOYEE_INFO WHERE EMP_COUNTRY IN ('CANADA', 'USA')
-- QUERY 9: HOW TO REPORT LIST OF EMPLOYEES NEITHER FROM CANADA NOR FROM USA?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_COUNTRY != 'CANADA' AND EMP_COUNTRY != 'USA'
SELECT * FROM EMPLOYEE_INFO WHERE EMP_COUNTRY NOT IN ('CANADA', 'USA')
-- QUERY 10:HOW TO REPORT LIST OF EMPLOYEES WHOSE SALARY VALUE IS BETWEEN 777777 AND
999999?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL BETWEEN 777777 AND 999999
-- QUERY 11:HOW TO REPORT LIST OF EMPLOYEES WHOSE SALARY VALUE IS NOT BETWEEN 777777
AND 999999?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL NOT BETWEEN 777777 AND 999999
-- QUERY 12: HOW TO REPORT EMPLOYEES WHOSE NAME IS 'SAI'?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_NAME = 'SAI'
-- QUERY 13: HOW TO REPORT EMPLOYEES WHOSE NAME STARTS WITH LETTER 'S'?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_NAME LIKE 'S%' -- %

MEANS TO SEARCH FOR ANY CHARACTER
-- QUERY 14: HOW TO REPORT EMPLOYEES WHOSE NAME DOES NOT STARTS WITH LETTER 'S'?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_NAME NOT LIKE 'S%' -- %

MEANS TO SEARCH FOR ANY CHARACTER
-- QUERY 15: HOW TO REPORT EMPLOYEES WHOSE NAME STARTS WITH LETTERS 'SA'?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_NAME LIKE 'SA%'
pg. 30
-- QUERY 16: HOW TO REPORT EMPLOYEES WHOSE NAME CONTAINS LETTER 'A'?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_NAME LIKE '%A%'
-- QUERY 17: HOW TO REPORT EMPLOYEES WHOSE NAME ENDS WITH LETTER 'N'?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_NAME LIKE '%N'
-- QUERY 18: HOW TO REPORT EMPLOYEES WITH SALARIES IN ASCENDING ORDER? MEANS, LOW TO
HIGH
SELECT * FROM EMPLOYEE_INFO ORDER BY EMP_SAL ASC
-- QUERY 19: HOW TO REPORT EMPLOYEES WITH SALARIES IN DESCENDING ORDER? MEANS, HIGH TO
LOW
SELECT * FROM EMPLOYEE_INFO ORDER BY EMP_SAL DESC
-- QUERY 20: HOW TO REPORT TOP 2 EMPLOYEES BASED ON THEIR IDs. IN ASCENDING ORDER.
MEANS, LOW TO HIGH
SELECT * FROM EMPLOYEE_INFO ORDER BY EMP_ID
SELECT TOP 2 * FROM EMPLOYEE_INFO
-- QUERY 21: HOW TO REPORT ALL EMPLOYEES BASED ON THEIR IDs. IN ASCENDING ORDER EXCEPT
TOP 2?
SELECT * FROM EMPLOYEE_INFO ORDER BY EMP_ID OFFSET 2 ROWS
-- QUERY 22: HOW TO REPORT LIST OF ALL EMPLOYEES SALARIES?
SELECT * FROM EMPLOYEE_INFO
SELECT EMP_SAL FROM EMPLOYEE_INFO
-- QUERY 23: HOW TO REPORT HIGHEST SALARY VALUE OUT OF OF ALL EMPLOYEES?
-- AS: ALIAS. A TEMPORARY NAME GIVEN TO A COLUMN OR TABLE
SELECT MAX(EMP_SAL) FROM EMPLOYEE_INFO
SELECT MAX(EMP_SAL) AS MAXIMUM_SALARY FROM EMPLOYEE_INFO
SELECT MAX(EMP_SAL) AS MAXSALARY FROM EMPLOYEE_INFO
pg. 31
SELECT EMP_ID,EMP_NAME,EMP_COUNTRY,MAX(EMP_SAL) AS MAXIMUM_SALARY FROM
EMPLOYEE_INFO
GROUP BY EMP_ID,EMP_NAME,EMP_COUNTRY,EMP_SAL
ORDER BY EMP_SAL DESC
SELECT EMP_ID,EMP_NAME,EMP_COUNTRY,MIN(EMP_SAL) AS MINIMUM_SALARY FROM

EMPLOYEE_INFO
GROUP BY EMP_ID,EMP_NAME,EMP_COUNTRY,EMP_SAL
ORDER BY EMP_SAL ASC
-- QUERY 24: HOW TO REPORT LIST OF EMPLOYEES WITH HIGHEST (MAXIMUM) SALARY?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL = 9999999
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL = (SELECT MAX(EMP_SAL) FROM EMPLOYEE_INFO)
-- QUERY 25: HOW TO REPORT LIST OF EMPLOYEES WITH LOWEST (MINIMUM) SALARY?
SELECT * FROM EMPLOYEE_INFO WHERE EMP_SAL = (SELECT MIN(EMP_SAL) FROM EMPLOYEE_INFO)
-- SUB QUERY: TO DEFINE ONE QUERY INSIDE ANOTHER. THE QUERY INCLUDED IN ( ) WILL BE
EXECUTED FIRST.
SELECT * FROM EMPLOYEE_INFO WHERE EMP_COUNTRY='CANADA' AND EMP_ID=1004
UPDATE EMPLOYEE_INFO SET EMP_SAL=1000000 WHERE EMP_COUNTRY='CANADA' AND

EMP_ID=1004
-- QUERY 26: HOW TO MODIFY THE DATA IN EXISTING TABLE?
UPDATE EMPLOYEE_INFO SET EMP_SAL = 100000 WHERE EMP_COUNTRY = 'CANADA'
-- QUERY 27: HOW TO REMOVE THE DATA IN EXISTING TABLE? -- Delete is monitored in the log
file. Supports Conditions. Safe.
DELETE FROM EMPLOYEE_INFO WHERE EMP_COUNTRY = 'MEXICO'
-- QUERY 28: HOW TO REMOVE THE DATA IN EXISTING TABLE? -- Truncate is NOT monitored in
the log file. Does not support conditions. Faster.
TRUNCATE TABLE EMPLOYEE_INFO
pg. 32
-- WE WILL SEE EMPTY TABLE.
-- QUERY 29: HOW TO ADD NEW COLUMN TO EXISTING TABLE?
ALTER TABLE EMPLOYEE_INFO ADD "EMPLOYEE_STATUS" VARCHAR(8000)
-- 8000 is the maximum limit.
-- QUERY 30: HOW TO MODIFY EXISTING COLUMN IN EXISTING TABLE?
ALTER TABLE EMPLOYEE_INFO ALTER COLUMN [EMPLOYEE_STATUS] INT
-- QUERY 31: HOW TO REMOVE EXISTING COLUMN FROM THE TABLE?
ALTER TABLE EMPLOYEE_INFO DROP COLUMN "EMPLOYEE_STATUS"
-- QUERY 32: HOW TO REMOVE EXISTING TABLE?
DROP TABLE EMPLOYEE_INFO

-- OPPOSITE OF CREATE

-- error. reason: no table
pg. 33
SCHEMA AND TABLES
SKK E C R C M
SCHEMA’S EXERCISE
-- QUERY 1: HOW TO CREATE DATABASE?
CREATE DATABASE DBTEST
USE DBTEST
-- QUERY 3: HOW TO CREATE NEW TABLE?
CREATE TABLE TABLE0
pg. 34
COL1 INT,
COL2 CHAR
INSERT INTO TABLE0 VALUES (1, 'ABC') -- ERROR. REASON: WE CAN INSERT MAX 1
CHARACTER IN 2ND COLUMN
INSERT INTO TABLE0 VALUES (1, 'A') -- NO ERROR. REASON: WE CAN INSERT
1 CHARACTER IN 2ND COLUMN
ALTER TABLE TABLE0 ALTER COLUMN COL2 CHAR(10)
INSERT INTO TABLE0 VALUES (1, 'ABC')
-- QUERY 5: HOW TO REPORT DATA FROM ABOVE TABLE?
SELECT * FROM TABLE0
SELECT * FROM dbo.TABLE0
--- QUERY 6: IN REAL-TIME, EVERY DATABASE HAS HUGE NUMBER OF TABLES. SO WE NEED TO GROUP
THOSE TABLES INTO SCHEMAS
-- HOW TO CREATE NEW SCHEMA IN THE DATABASE?
CREATE SCHEMA SCHEMA1

-- LIKE A FOLDER
pg. 35
-- QUERY 7: HOW TO USE ABOVE SCHEMA AND CREATE TABLES?
CREATE TABLE SCHEMA1.TABLE1

-- LIKE FILES INSIDE THE FOLDER
COL1 INT,
COL2 INT

COL1 INT,
COL2 INT

COL1 INT,
COL2 INT
pg. 36
-- QUERY 8: HOW TO CREATE ONE MORE SCHEMA IN THE DATABASE? WE CAN CREATE ANY
NUMBER OF SCHEMAS INSIDE THE DATABASE
CREATE SCHEMA SCHEMA2
-- QUERY 9: HOW TO USE ABOVE SCHEMA TO CREATE TABLES?
COL1 INT,
COL2 INT
COL1 CHAR,
COL2 INT
COL1 INT,
COL2 CHAR,
COL3 FLOAT
pg. 37
INSERT INTO SCHEMA2.TABLE1 VALUES (1, 1)
INSERT INTO SCHEMA2.TABLE1 VALUES (2, 2), (3, 3), (4, 4)
-- QUERY 11: HOW TO REPORT OR VERIFY DATA?
SELECT * FROM SCHEMA2.TABLE1
-- QUERY 12: HOW TO MOVE OR MIGRATE TABLE FROM ONE SCHEMA TO ANOTHER?
ALTER SCHEMA SCHEMA2 TRANSFER dbo.TABLE0
-- QUERY 13: HOW TO VERIFY ABOVE MIGRATION OF TABLE?
SELECT * FROM SCHEMA2.TABLE0
pg. 38
TEMP TABLES EXERCISE
CREATE TABLE #TABLE1 -- THIS IS A LOCAL TEMPORARY TABLE. you create, you
use it
COL1 INT,
COL2 INT
INSERT INTO #TABLE1 VALUES (1,1), (2,2), (3,3)
SELECT * FROM #TABLE1
CREATE TABLE ##TABLE2 -- THIS IS A GLOBAL TEMPORARY TABLE. you create, we

use it as long as you are online.
COL1 INT,
COL2 INT
INSERT INTO ##TABLE2 VALUES (100,100), (200,200), (300,300)
SELECT * FROM ##TABLE2
-- HOW TO TEST ABOVE TABLES?
-- FROM SSMS TOOL: GO TO FILE > NEW > QUERY WITH CURRENT CONNECTION. OR RIGHT CLICK
SERVER > NEW QUERY. RUN BELOW STATEMENTS:
SELECT * FROM #TABLE1 -- THIS STATEMENT WILL NOT WORK. REASON: ITS LOCAL TO
CURRENT SESSION (THIS QUERY WINDOW).
SELECT * FROM ##TABLE2 -- THIS STATEMENT WILL WORK. REASON: ITS GLOBAL TO
ENTIRE SERVER.
-- HOW TO COPY DATA FROM ONE TABLE TO ANOTHER TABLE? -- COPY PASTE
SELECT * INTO NEW_TABLE FROM ##TABLE2 -- TABLE

CREATED, DATA LOADED
SELECT * FROM NEW_TABLE
pg. 39
-- ABOVE TABLE "NEW_TABLE" IS AUTO CREATED IN THE CURRENTLY CONNECTED DATABASE.
-- PERMENANT TABLE.
SQL Constraints
Constraints are the rules enforced on data colu ns on a table hese are used to li it the t pe of data that can o into a ta ble his ensures the
accurac and reliabilit of the data in the database
Constraints can either be colu n level or table level Colu n level constraints are applied onl to one colu n hereas table level constraints are
applied to the en re table
ollo in are so e of the ost co onl used constraints available in S L
ULL Constraint Ensures that a colu n cannot have a ULL value

DE AUL Constraint rovides a default value for a colu n hen none is speci ed
U I UE Ke Ensures that all the values in a colu n are di erent
ri ar Ke Uni uel iden es each ro record in a database table
REI Ke Uni uel iden es a ro record in an another database table
CHECK Constraint Ensures that all values in a colu n sa sf certain condi ons
I DEX Constraint Used to create and retrieve data fro the database ver uic l
SKK E C R C M
pg. 40
Primary Key Constraint
SKK E C R C M
Foreign Key Constraint
SKK E C R C M
pg. 41
Not Null Constraint
SKK E C R C M
Unique Key Constraint
SKK E C R C M
pg. 42
Default Value Constraint
SKK E C R C M
Check Value Constraint
SKK E C R C M
pg. 43
Index Constraint
SKK E C R C M
CONSTRAINTS EXERCISE
CREA E DA A ASE U IV_DA A ASE
USE U IV_DA A ASE
CREA E A LE tblCourses
CourseID int U I UE HIS C LUM D ES ALL W DU LICA ES
Course a e varchar ULL HIS C LUM D ES ALL W ULL VALUES
CourseDur int CHECK CourseDur = R CourseDur =
pg. 44
I SER I tblCourses VALUES 'C M U ERS'
I SER I tblCourses VALUES 'CIVIL'
I SER I tblCourses VALUES 'R ICS'
I SER I tblCourses VALUES 'MECHA ICS' ERR R
I SER I tblCourses VALUES ULL ERR R
I SER I tblCourses VALUES 'MECHA ICS' ERR R
SELEC * R M tblCourses
RE : H W CREA E S UDE S A LE E SURE VALID C URSE RE IS RA I S R EVERY

S UDE ?
CREA E A LE tblStudents
StdID int RIMARY KEY HIS C LUM D ES ALL W DU LICA ES
Std a e varchar ULL HIS C LUM D ES ALL W ULL VALUES
StdA e int CHECK StdA e >=
StdCourseID int RE ERE CES tblCourses CourseID EVERY S UDE SH ULD E E R LLED I
VALID EXIS I C URSE LY
I SER I tblStudents VALUES 'SAI' MR SAI IS EI E R LLED

I C M U ERS C URSE
I SER I tblStudents VALUES 'SAM' MR SAM IS EI E R LLED

I CIVIL C URSE
I SER I tblStudents VALUES 'RAM' MR RAM IS EI E R LLED

I CIVIL C URSE
pg. 45
I SER I tblStudents VALUES 'RAMI' ERR R
I SER I tblStudents VALUES ' ' MR IS EI E R LLED

I C M U ERS C URSE
I SIDE A A CH HE SUCCESS R AILURE E S A EME WILL S HE EXECU I

REMAI I S A EME S
SELEC * R M tblStudents
I SER I tblStudents VALUES ' Y' ERR R REAS : I VALID

C URSE ID
RE : H W CREA E S A A LE E SURE VALID C URSE RECRUI ME R EVERY S A ?
CREA E A LE tblSta
StfID int IDE I Y E ERA E SE UE CE VALUES S AR

VALUE IS CALLED "SEED"
Stf a e varchar ULL HIS C LUM D ES ALL W ULL

VALUES
StfA e int CHECK StfA e >= DE AUL
StfCourseID int
I SER I tblSta VALUES 'AMI ' I VALID C URSE ID VALUE

S ILL ERR R
RU CA E A LE tblSta
H W ADD RIMARY KEY EXIS I A LE?
pg. 46
AL ER A LE tblSta ADD C S RAI K_S ID RIMARY KEY StfID
H W ADD REI KEY EXIS I A LE?
AL ER A LE tblSta ADD C S RAI K_S CRSID REI KEY StfCourseID RE ERE CES
tblCourses CourseID
I SER I tblSta VALUES 'AMI ' HIS S A MEM ER IS EI RECRUI ED

RC M UERS C URSE
I SER I tblSta VALUES ' H ' HIS S A MEM ER IS EI RECRUI ED

R CIVIL C URSE
I SER I tblSta VALUES 'SAMI' HIS S A MEM ER IS EI RECRUI ED

R CIVIL C URSE
SELEC * R M tblSta
I SER I tblSta Stf a e StfCourseID VALUES 'AMI I' MISSI C LUM S

WILL AKE DE AUL VALUES EX: StfA e
SELEC * R M tblSta
I SER I tblSta VALUES 'MU U DHA '
Inde
SELEC * R M LS A WHERE S _ID >=
CREA E CLUS ERED I DEX I D LS A S _ID
SELEC * R M LS A WHERE S _ E DER = 'M'
CREA E CLUS ERED I DEX I D LS A S _ E DER
pg. 47
SQL JOINS
SKK E C R C M
SKK E C R C M
pg. 48
SQL INNER JOIN
SKK E C R C M
SQL LEFT JOIN
pg. 49
SQL RIGHT JOIN
SKK E C R C M
SQL FULL OUTER
SKK E C R C M
pg. 50
SQL SELF JOIN
SKK E C R C M
pg. 51
JOIN EXERCISE
CREATE DATABASE UNIVDATABASE4
USE UNIVDATABASE4
CREATE TABLE tblCourses
CourseID int UNIQUE, -- THIS COLUMN DOES NOT ALLOW DUPLICATES
CourseName varchar(30) not null, -- THIS COLUMN DOES NOT ALLOW NULL VALUES. MEANS,
MANDATORY COLUMNS
CourseDur int CHECK (CourseDur = 120 OR CourseDur = 180)
INSERT INTO tblCourses VALUES (1001, 'COMPUTERS', 120), (1002, 'ROBOTICS', 180), (1003, 'CIVIL', 180)
SELECT * FROM tblCourses
CREATE TABLE tblStudents
StdID int PRIMARY KEY, -- THIS COLUMN DOES NOT ALLOW DUPLICATES, DOES
NOT ALLOW NULL VALUES
StdName varchar(30) not null, -- THIS COLUMN DOES NOT ALLOW NULL VALUES. MEANS,
MANDATORY COLUMNS
StdAge int CHECK (StdAge >= 18),
pg. 52
StdCourseID int REFERENCES tblCourses(CourseID) -- EVERY STUDENT SHOULD REGISTER
FOR A VALID COURSE ONLY
INSERT INTO tblStudents VALUES (10001, 'SAI', 30, 1001) -- Mr. Sai is registering
for computers course
INSERT INTO tblStudents VALUES (10002, 'Ami', 30, 1002)
INSERT INTO tblStudents VALUES (10003, 'Sami', 30, 1002)
INSERT INTO tblStudents VALUES (10004, 'SAI2', 30, 1001) -- Mr. Sai2 is registering for
computers course
INSERT INTO tblStudents VALUES (10005, 'Ami2', 30, 1002)
INSERT INTO tblStudents VALUES (10006, 'Sami2', 30, 1002)
SELECT * FROM tblStudents
-- REQ 1: HOW TO REPORT LIST OF COURSES AND RESPECTIVE STUDENTS?

MATCHING DATA
-- REQ 2: HOW TO REPORT LIST OF COURSES WITH AND WITHOUT STUDENTS?

MATCHING DATA & MISSING DATA
-- REQ 3: HOW TO REPORT LIST OF COURSES WITHOUT ANY STUDENT?

MISSING DATA
-- REQ 1: HOW TO REPORT LIST OF COURSES AND RESPECTIVE STUDENTS?

MATCHING DATA
SELECT * FROM
tblCourses -- LEFT TABLE
pg. 53
INNER JOIN
tblStudents -- RIGHT TABLE
ON
tblStudents.StdCourseID = tblCourses.CourseID
ORDER BY tblCourses.CourseID ASC
-- REQ 2: HOW TO REPORT LIST OF COURSES WITH STUDENTS & COURSES WITHOUT STUDENTS?
SELECT * FROM
LEFT OUTER JOIN -- LEFT OUTER JOIN : All Left Table + Matching Right Table Data.
Non match RIGHT data is null
ON
-- REQ 3: HOW TO REPORT LIST OF COURSES WITH STUDENTS & COURSES WITHOUT STUDENTS?
SELECT * FROM
tblStudents -- LEFT TABLE
RIGHT OUTER JOIN -- RIGHT OUTER JOIN : All RIGHT Table + Matching LEFT Table Data. Non
match LEFT data is null
tblCourses -- RIGHT TABLE
ON
pg. 54
-- REQ 4: HOW TO REPORT LIST OF COURSES WITHOUT STUDENTS?
MISSING DATA
SELECT * FROM
LEFT OUTER JOIN -- LEFT OUTER JOIN : All Left Table + Matching Right Table Data.
Non match RIGHT data is null
ON
WHERE tblStudents.StdCourseID IS NULL
------ Exercise 2
CREATE DATABASE RESERVATIONDB
USE RESERVATIONDB
CREATE TABLE FLIGHT
CRAFT_CODE VARCHAR(30),
SOURCE VARCHAR(30) NOT NULL,
DESTINATION VARCHAR(30)
pg. 55
)
INSERT INTO FLIGHT VALUES ('EMI101', 'HYB', 'NYC')
INSERT INTO FLIGHT VALUES ('EMI102', 'HYB', 'LSA')
SELECT * FROM FLIGHT
create table Reservation
CRAFT_CODE varchar(30),
No_of_Seats int ,
Class_Code varchar(10)
INSERT INTO Reservation VALUES ('EMI101', 1, 'ECO')
INSERT INTO Reservation VALUES ('EMI102', 4, 'BIZ')
pg. 56
SELECT * FROM Reservation
-- REQ 1: HOW TO REPORT LIST OF FLIGHTS AND RESPECTIVE RESERVATIONS?

MATCHING DATA. INNER JOIN
INNER JOIN Reservation
ON
FLIGHT.CRAFT_CODE = Reservation.CRAFT_CODE
-- REQ 2: HOW TO REPORT LIST OF FLIGHTS WITH & WITHOUT RESERVATIONS?

MATCHING & MISSING DATA. OUTER JOIN
LEFT OUTER JOIN
Reservation
ON
SELECT * FROM Reservation
RIGHT OUTER JOIN
pg. 57
FLIGHT
ON
RIGHT OUTER JOIN
RESERVATION
ON FLIGHT.CRAFT_CODE = Reservation.CRAFT_CODE
-- REQ 3: HOW TO REPORT LIST OF FLIGHTS WITHOUT RESERVATIONS?

MISSING DATA. OUTER JOIN
LEFT OUTER JOIN
Reservation
ON
WHERE Reservation.CRAFT_CODE IS NULL
-- REQ 4: HOW TO REPORT LIST OF FLIGHTS WITH & WITHOUT RESERVATIONS, RESERVATIONS
WITH & WITHOUT FLIGHTS? FULL OUTER JOIN
FULL OUTER JOIN
Reservation
ON
pg. 58
-- REQ 5: HOW TO REPORT LIST OF FLIGHTS & RESERVATIONS. REPORT ALL POSSIBLE
COMBINATIONS? CROSS JOIN
SELECT * FROM FLIGHT CROSS JOIN Reservation
SELECT * FROM FLIGHT CROSS APPLY Reservation
SELECT * FROM FLIGHT , Reservation
/*
INNER JOIN : TO REPORT MATCHING DATA
OUTER JOINS : TO REPORT MATCHING & MISSING DATA
LEFT : REPORT ALL LEFT, MATCHING RIGHT. NON MATCH RIGHT IS NULL
RIGHT : REPORT ALL RIGHT, MATCHING LEFT. NON MATCH LEFT IS NULL
FULL : REPORT LEFT & RIGHT TABLES. COMPARED ON BOTH SIDES.
CROSS JOIN : TO REPORT ALL POSSIBLE COMBINATIONS
*/
CREATE CLUSTERED INDEX INDX1 ON Reservation(Class_Code) --

GOOGLE MAP PK
CREATE NONCLUSTERED INDEX INDX2 ON Reservation(CRAFT_CODE) --

ARROW MARK UQ
pg. 59
SQL VIEWS
SKK E C R C M
SKK E C R C M
pg. 60
SKK E C R C M
SQL VIEWS Exercise
-- EXAMPLE 1:
-- QUERY 1: HOW TO CREATE NEW DATABASE?
CREATE DATABASE DBOBJECT1
USE DBOBJECT1
-- QUERY 3: HOW TO DEFINE COURSES TABLE AND STORE DATA?
CREATE TABLE tblCourses
COURSE_ID INT UNIQUE,
COURSE_NAME VARCHAR(30) NOT NULL,
pg. 61
COURSE_DUR INT CHECK (COURSE_DUR = 120 OR COURSE_DUR = 180)
INSERT INTO tblCourses VALUES (101, 'COMPUTERS', 120), (102, 'ROBOTICS', 120), (103, 'CIVIL', 180)
-- QUERY 4: HOW TO DEFINE STUDENTS TABLE AND STORE DATA?
CREATE TABLE tblStudent
StdID INT PRIMARY KEY,
StdName VARCHAR(30) NOT NULL,
StdAge int CHECK (StdAge >= 18),
StdCourse INT REFERENCES tblCourses(COURSE_ID)
INSERT INTO tblStudent VALUES (1001, 'SAI', 39, 101), (1002, 'AMI', 39, 101), (1003, 'AMIN', 39, 101)
INSERT INTO tblStudent VALUES (1004, 'JOHN', 39, 102),(1005, 'JEFFERY', 39, 102)
-- QUERY 5: VERIFY DATA FROM ABOVE TABLES?
SELECT * FROM tblCourses;
SELECT * FROM tblStudent;
-- QUERY 6: HOW TO REPORT LIST OF STUDENTS FROM ALL COURSES?
JOIN -- JOIN MEANS INNER JOIN
pg. 62
tblStudent
ON
tblCourses.COURSE_ID = tblStudent.StdCourse
-- QUERY 7: HOW TO REPORT LIST OF STUDENTS FROM COMPUTERS COURSES?
tblStudent
ON
WHERE
tblCourses.COURSE_NAME = 'COMPUTERS'
INSERT INTO tblStudent VALUES (1006, 'ARUN', 35, 103), (1007, 'DAVID', 39, 101)
-- QUERY 8: HOW TO STORE ABOVE QUERY IN THE DATABASE FOR EASY ACCESS?
CREATE VIEW VWCOMPUTERS
AS
tblStudent
ON
WHERE
pg. 63
tblCourses.COURSE_NAME = 'COMPUTERS'
-- QUERY 9: HOW TO REPORT FROM ABOVE VIEW?
SELECT * FROM VWCOMPUTERS
-- QUERY 10: HOW TO REPORT LIST OF STUDENTS FROM ROBOTICS COURSE? USING VIEW?
CREATE VIEW VWROBOTICS
AS
tblStudent
ON
WHERE
tblCourses.COURSE_NAME = 'ROBOTICS'
-- QUERY 11: HOW TO REPORT FROM ABOVE VIEW?
SELECT * FROM VWROBOTICS
pg. 64
SQL STORED PROCEDURES
SKK E C R C M
pg. 65
pg. 66
SQL STORED PROCEDURES EXERCISE
-- QUERY 12: HOW TO REPORT LIST OF STUDENTS FROM A GIVEN COURSE?
CREATE PROCEDURE spReportStudents (@CourseName varchar(30)) -- Unknown value should be

included in Parenthisis = Parameter
AS
tblStudent
ON
WHERE
tblCourses.COURSE_NAME = @CourseName
-- QUERY 13: HOW TO EXECUTE ABOVE OBJECT?
EXECUTE spReportStudents 'COMPUTERS'
EXEC spReportStudents 'ROBOTICS'
pg. 67
SQL USER DEFINED FUNCTIONS
SKK E C R C M
SQL USER DEFINED FUNCTIONS EXERCISE
-- QUERY 13: HOW TO REPORT LIST OF STUDENTS FROM A GIVEN COURSE?
CREATE FUNCTION udfReportStudents (@CourseName varchar(30)) -- Unknown value should be

included in Parenthisis = Parameter
RETURNS table -- table IS A DATA TYPE. TO REPRESENT A COLLECTION OF VALUES. (EXAMPLE:

COLLECTION OF STUDENTS)
AS
RETURN
tblStudent
ON
pg. 68
WHERE
tblCourses.COURSE_NAME = @CourseName
-- QUERY 14: HOW TO EXECUTE ABOVE OBJECT?
SELECT * FROM udfReportStudents ('COMPUTERS')
SELECT COURSE_ID, COURSE_NAME, COURSE_DUR,STDID FROM udfReportStudents ('COMPUTERS')

WHERE STDID > 1001
pg. 69

SQL Notes

Uploaded by

Copyright:

Available Formats

SQL Notes

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

SQL Notes

Uploaded by

Copyright:

Available Formats

SQL TRAINING WORKBOOK

SKKP ENTERPRISE CORP

Type of Database Purpose Examples

3. WHAT IS RDBMS ? Relational DATABASE MANAGEMENT SYSTEM

MS OFFICE (SOFTWARE) > EXCEL FILE > SHEETS

4. WHAT IS SQL SERVER?

7. VERSIONS OF SQL SERVER?

8. EDITIONS OF SQL SERVER?

9. WHAT IS SQL? STRUCTURED QUERY LANGUAGE. USED TO COMMUNICATE, PERFORM DATA

1. SQL DEVELOPERS INVOLVED IN DESIGN OF OLTP DATABASES. 100% CODING.

2. MSBI DEVELOPERS INVOLVED IN DESIGN OF OLAP & DWH DATABASES + REPORTS

3. DATA ANALYSTS DESIGN OF REPORTS, DASHBOARDS WITH POWER BI CLOUD

5. AZURE BI OLTP + OLAP + DWH IN ON-PREMISE [NON CLOUD] + AZURE

Download and install sql server 2019

Instance: A successful installation of SQL Server.

Types of Instances: DEFAULT INSTANCE NAMED INSTANCE

SERVICE: A communication path between Operating System and SQL Server.

Authentication: Communication Path between SQL Server and Users (Customers).

To work with above Authentications, we need "LOGINS".

STEP 4: HOW TO TEST THE INSTALLATIONS ?

(ONLINE ANALYTICAL PROCESSING DATABASE : OLAP)

3. SQL BASICS [INTRODUCTION]

5. VERSIONS OF SQL SERVER?

7. HOW TO INSTALL SQL SERVER? TO STORE DATA

10. HOW TO CREATE DATABASES?

BASIC PRACTICE EXAMPLES USING GUI [USER INTERFACE]

ITEM #2: HOW TO CREATE NEW TABLE INSIDE ABOVE DATABASE?

Save the table as “product_info”

ITEM #3: HOW TO VERIFY ABOVE TABLE IN THE DATABASE?

ITEM #4: HOW TO INSERT (STORE) DATA INTO ABOVE TABLE?

ITEM #5: HOW TO REPORT DATA FROM ABOVE TABLE?

ITEM #8: HOW TO CONNECT TO ABOVE DATABASE USING SQL?

SELECT ALL BELOW FIVE LINES TOGETHER >>> THEN EXECUTE

ITEM #10: HOW TO STORE DATA INTO THE TABLE ?

INSERT INTO tblStore VALUES (1001, 'CAPS');

INSERT tblStore VALUES (1002, 'CHAINS')

BASIC TYPES OF SQL STATEMENTS:

DATA DEFINITION LANGUAGE [DDL] : STRUCTURE (CONTAINER)

CREATE To define new Databases and Tables

ALTER To modify structure of existing Databases and Tables

DROP To remove existing Databases and Tables

DATA MANUPULATION LANGUAGE [DML] : DATA (CONTENT)

INSERT To store new data into tables

UPDATE To modify or change existing data in the tables

DELETE To remove existing data from the tables

2. model : Used as a template for new databases we create on the server

Resource Database controls the Installation Files.

SQL Comparison Operators

SQL Data types

MS SQL Server numeric DATA TYPES

DDL and DML EXERCISE

-- QUERY 1: HOW TO CREATE NEW DATABASE?

CREATE DATABASE Employee_Database

-- QUERY 2: HOW TO CONNECT TO ABOVE DATABASE?

-- QUERY 3: HOW TO CREATE TABLE IN ABOVE DATABASE?

-- EVERY TABLE NEEDS MINIMUM 1 COLUMN. MAXIMUM UPTO 1024 COLUMNS.

-- EVERY COLUMN NEEDS A NAME, DATA TYPE.

CREATE TABLE EMPLOYEE_INFO

EMP_COUNTRY VARCHAR(40),-- THIS COLUMN STORES UPTO 40 CHARACTERS [DIGITS, SYMBOLS,