Sql Server
- SSMS - IDE
- DDL : CREATE, ALTER, TRUNCATE, DROP
- DML : INSERT, SELECT, UPDATE, DELETE
- Auto-generate Values
- Batches
- Exception Handling
- Functions
Built-in
Math, String, Conversion, Date Time, Ranking
User defined
Scalar, Table (inline, multi-stmt)
- Procedures
- Misc.
temporary table, clauses , flags
-------------------------------------------------
SSMS Connection
Server Type : Database Engine
Server Name : VBIMYS-29
Authentication : Windows
[ITLINFOSYS]\username
Database Name : Classroom_Practice // not master
SQL Server:
create <object> <name>
create table StudentDetails
create database Student
User Defined
TraineeMgmt
- TraineeDetails
(EmpNo, Name, Batch, DateOfJoining, BatchOwner)
1 John
- EducatorDetails
(EmpNo, Name)
- BatchDetils - batchid
(BatchId, BatchName, SubBatchName, BatchStartDate, BatchEndDate)
- Exams
(ExamId, TraineeID, FocusArea, MarksHandsOn, MarksObjective)
- Courses
SELECT * FROM TraineeDetails WHERE Batch = 'Nov20'
SELECT * FROM TraineeDetails WHERE Batch = (SELECT BatcName FROm BatchDet where
Start ='')
1. For each trainee, display their total marks --> 400
SELECT TraineeID, Name, SUM(HandsOn + Objective) as TotalMarks
FROM Exams E JOIN TraineeDetails T ON E.EmpNo = T.EmpNo
GROUP BY TraineeID
ORDER BY TotalMarks DESC
Trainee TotalMarks
2. For each FA, avg marks of Hands-on, avg of
FA1 50 35
FA2 25 15
.....
SELECT TraineeID, AVG(HandsOn), AVG(Objective) as TotalMarks FROM Exams
GROUP BY FocusArea
Aggregate: MIN,MAX, COUNT, SUM, AVG
System Defined
master : starting
model
resource
msdb
temp
Datatypes
EmpNo : INT
BIT, TINYINT, SMALLINT, INT, DECIMAL
MONEY, SMALLMONEY
FLOAT
Name : VARCHAR(size)
CHAR(size), VARCHAR(size)
Batch : CHAR(20)
DOJ : Date
Date, time, datetime
TimeOfEnteringSession : time
BATCHES
Syntax:
BEGIN
DECLARE @num INT
SET @num =10
SELECT @num
END
Program Construct
IF <cond>
BEGIN
<stmts>
END
SELECT @@identity : global
SELECT @num : local
Exception Handling
BEGIN TRY
DECLARE @num INT
SET @num =10
SELECT @num
END TRY
BEGIN CATCH
SELECT 'Error'
END CATCH
ERRORLINE, ERRORMESSAGE, ERRORCODE....
Msg 8134, Level 16, State 1, Line 8
Divide by zero error encountered.
-------------------------------------------------------
Sparsh --> Webapps --> Email --> India Oulook
ITLINFOSYS\username
domain
Classroom_Practice --> database in VBIMYS-29
@@ERROR -->
ERROR_MESSAGE(), ERROR_LEVEL(), ERROR_LINE()
THROW --> raise an exception, causing the control to move to CATCH block
-----------------------------------------------------------
1. To update a record in CardDetails
DECLARE @CardNumber NUMERIC(16)=''
DECLARE @CardType
DECLARE @Amount
i. Visa Card - V - Add 250 Rs. Service Charge
Master Card - M - Add 10% of the amount as service charge
ii. Calculate final amount
iii. Update Balance - final amount after checking balance
iv. Update Successful/NOT updated
CardNum = 1209611246778470, Type='V' and Amount=3600
1224920265219560,'M',,1000
BEGIN TRY
DECLARE @CardNumber NUMERIC(16)=1209611246778470
DECLARE @CardType CHAR='V'
DECLARE @Amount MONEY=3600, @BalAmt MONEY
IF @CardType = 'V'
BEGIN
SET @Amount = @Amount + 250
END
ELSE IF @CardType = 'M'
SET @Amount *= 1.1
SELECT @BalAmt=Balance FROM CardDetails WHERE CardNumber=@CardNumber
IF @BalAmt >= @Amount
SELECT 'Yes'
ELSE
SELECT 'No'
END TRY
BEGIN CATCH
SELECT ERROR_MESSAGE(), ERROR_LINE(), ERROR_NUMBER()
END CATCH
2. For a given Productid, display the maximum priced product of the given pdt's
category.
'P126' from category id 4 --> display, name, category name, price of the most
priced from category 4
BEGIN
DECLARE @CatId INT, @Price MONEY, @PID CHAR(4)='P126', @PdtName VARCHAR(50)
SELECT @CatId =CategoryId FROM Products WHERE ProductId=@PID
SELECT @Price=MAX(Price) FROM Products WHERE CategoryId = @CatId --- Price =
9056
SELECT @PdtName = ProductName FROM Products WHERE Price = @Price and
CategoryId = @CatId
SELECT @PdtName
END
---------------------------------------------------------------
SQL Built-in Functions
ROUND(), CEILING(),
SUBSTRING()
SELECT CONCAT(firstname, " ", lastname ) FROM table
SELECT firstname lastname FROM table
USer defined functions
Function Name:
checkAmount
Parameters:
@CardNumber NUMERIC(16), @CardType CHAR, @Amount MONEY
Logic:
any logic concerned with data storage
Function cannot accomodate TRY, CATCH...
Function cannot modify database object state, No INSERTS...
The last statement included within a function must be a return statement
Scalar Valued Function:
Returns a single
CREATE FUNCTION <name>(param Datatype, .....)
RETRUNS <return datatype>
BEGIN
logic
RETURN
END
Schema: [ITLINFOSYS\username]
Execution:
SELECT [schema].<function name>(parameter1,parameter2)
DECLARE @result INT
EXEC @result = [schema].<function name> parameter1 parameter2
SELECT @result
------------------
1. Offers in all categories AvailDiscount()
PArameter: Productid
Logic:
- Category of the product
- how much offer % can we apply
1,2,3 5%
4 10%
5,6 12%
7 20%
- final amount after offer applied
ALTER FUNCTION AvailDiscount(@ProductId CHAR(5))
RETURNS MONEY
BEGIN
DECLARE @amt MONEY,@price MONEY, @cat INT
SELECT @amt=Price, @cat=CategoryId FROM Products WHERE ProductId=@ProductId
IF @cat IN (1,2,3)
SET @amt*= 0.95
ELSE IF @cat =4
SET @amt*= 0.9
ELSE IF @cat =5 OR @cat = 6
SET @amt*= 0.88
ELSE
SET @amt*=0.8
RETURN @amt
END
--Execution
SELECT [schema].AvailDiscount('P110')
DECLARE @output MONEY
EXEC @output = [schema].AvailDiscount 'P110'
SELECT @output
-----------------------
Products Categories
CardDetails PurchaseDetails
USers Roles
2. Given a ProductID, display count of customers who have purchased that product
3. Given a ProductID, display details of customers who have purchased that product
Name, Quatity, Price, Email....
-- Inline Function
CREATE FUNCTION [schema].<func name> (parameter list)
RETURNS TABLE AS
RETURN (single select query - joins, subquery,.....)
SELECT * FROM <table>
SELECT * FROM [schema].<func name>(param) // return a table as output
ALTER FUNCTION [schema].DisplayCustomers(@ProdID CHAR(4))
RETURNS TABLE
RETURN (SELECT * FROM PurchaseDetails WHERE ProductId=@ProdID)
1. a Find out how many people have made purchase worth Rs.5000 or more
b Display the details - Address, EmailID- of the people have made purchase
worth Rs.5000 or more
2. Check if there are any customers who are registered, but have not made any
purchase so far. Display their details.
3. Create a function named ChristmasBonus() which will give a customer a bonus
discount based on few criteria:
- Receive : EmailID of customer
- For Customers who have made purchases, based on the count of purchases made
'Valuable Buyer' - more than 5 purchase - "10% off on all
products"
'Frequent Buyer' - 1 to 5 purchases - "8% off on all
products"
'Potential Buyer' - 0 purchases
- For Potential Buyers, give bonus discount as:
Male - "Rs.250 off on next 3 purchases"
Female - "Rs.500 off on next 2 purchases"
Output:
EmailID BuyerType OfferMessage
VARCHAr(100) VARCHAr(50) VARCHAr(100)
-------------------------------------------------------------------
Functions
SQL Built-in User Defined
1. Scalar 2. Table
valued func
valued func
returns single
value
2a) inline
2b) mulit-stmt
single query
multiple stmts
/stmt
MultiStatement Table Valued Funtion
- Returns a TABLE - with a Schema defined in func sign
- Multiple Statements in the logic
CREATE Function <func Name>(parameter list)
RETURNS @res TABLE (col datatype,....)
AS
BEGIN
stmts.... DECLARE, IF, SET, SELECT....
INSERT INTO @res TABLE VALUES ()
RETURN
END
RETURN - sends control back
RETURNS - defining Function datatype/TABLE
multiple record return using SELECT keyword:
CREATE FUNCTION FetchDetails12()
RETURNS @temp TABLE(Email VARCHAR(50), Address VARCHAR(100))
AS
BEGIN
INSERT @temp
SELECT EmailID, Address FROM Users
RETURN
END
--------------------------------------------------
Ranking Functions
- ROW_NUMBER : serial number after ordering in given arrangement
Field - PRice
100 1
100 2
90 3
- RANK : same rank to the same values in the column
Field - PRice
100 1
100 1
90 3
- DENSE_RANK : No skipping of ranks- continuous
Field - PRice
100 1
100 1
90 2
- NTILE(n) : n groups formed based on the column ordering
SELECT ProductId, ProductName, QuantityAvailable, Price,
ROW_NUMBER() OVER (ORDER BY Price DESC) AS RowNum,
RANK() OVER (ORDER BY Price DESC) AS RankCol,
DENSE_RANK() OVER (ORDER BY Price DESC) AS RankDense
FROM Products
--------------------------------------
Procedure
- Change values - UPDATE, INSERT, DELETE...
- Error Handling
- Batch - Declare, Set, WHILE, IF ... SELECT, INSERT...
Creation:
ALTER PROCEDURE usp_AddStock(@varname INT OUTPUT,@ProdId CHAR(4),@Qty INT)
AS
BEGIN
-----
END
Execution:
DECLARE @returnvalue INT
EXEC @returnvalue= usp_AddStock 'P103',19999999 -- Execution
SELECT @returnvalue
Return
1- Invalid Product
2- Invalid Qty
3- Successful
Use of OUTPUT var - return any value from procedure
DECLARE @returnvalue INT, @outputvar INT
EXEC @returnvalue= usp_AddStock @outputvar OUT, 'P103',19999999 -- Execution
SELECT @returnvalue
1. a.Create a procedure named usp_NewPurchase
b.Receive parameters:
Emailid - If not present in Users table - Return -1
ProductId - If not present in Products - Return -2
QuatityPurchased - Should be more than 0 and less than 10 - Return -3
c. Autogenerate next PurchaseId
d. Consider DateofPurchase - today's date
e. insert record, If successfully inserted, return 1
f. Implement Error handling
2. a.Create a procedure named usp_NewPurchase2
b.Receive parameters:
Emailid - If not present in Users table - Return -1
ProductId - If not present in Products - Return -2
QuatityPurchased - Should be more than 0 and less than 10 - Return -3
- If enough qty is not available - Return
-4
PurchaseId OUTPUT parameter
c. Autogenerate next PurchaseId
d. Consider DateofPurchase - today's date
e. insert record, If successfully inserted, then
- reduce the qty for the product from Products table
- If update successful, also set output parameter - PurchaseId
return 1
f. Implement Error handling
---------------------------------------------------------------
Temporary Tables
- scope of a session
#localtemptable - current user
##globaltemp - all users
------------------------------------------------------------
Program Constructs:
1. WHILE
WHILE is the only iterative construct supported in SQL Server.
BEGIN
DECLARE @Price NUMERIC(8) =200, @QuantityPurchased TINYINT=2, @TotalAmount
NUMERIC(8)=0, @Counter INT =0
WHILE @Counter < @QuantityPurchased
BEGIN
SET @TotalAmount=@TotalAmount + @price
SET @Counter = @Counter + 1
END
PRINT @TotalAmount
END
2. CASE
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;
