SQL Server Video Scripts
1. Intro to SQL Server
SELECT @@VERSION;
2. Create Database in SQL Server
CREATE DATABASE SchoolDB;
3. Rename Database in SQL Server
ALTER DATABASE SchoolDB MODIFY NAME = SchoolDB_New;
4. Create Table in SQL Server
CREATE TABLE Students(
StudentID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Age INT
);
5. Drop Table and Database and Modify DataType column
ALTER TABLE Students
ALTER COLUMN Age SMALLINT;
DROP TABLE Students;
DROP DATABASE SchoolDB_New;
6. Primary Key and Foreign Key in SQL
CREATE TABLE Departments(
DeptID INT PRIMARY KEY,
DeptName NVARCHAR(50)
);
CREATE TABLE Employees(
EmpID INT PRIMARY KEY,
EmpName NVARCHAR(50),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);
7. Insert and Select Data in Table
INSERT INTO Departments(DeptID, DeptName) VALUES (1, 'IT'), (2, 'HR');
SELECT * FROM Departments;
8. Update and Delete and Select Data With Where
UPDATE Departments SET DeptName = 'Finance' WHERE DeptID = 2;
DELETE FROM Departments WHERE DeptID = 1;
SELECT * FROM Departments WHERE DeptName = 'Finance';
9. Distinct and Order By
SELECT DISTINCT DeptName FROM Departments;
SELECT * FROM Departments ORDER BY DeptName DESC;
10. Group By and Count
SELECT DeptID, COUNT(EmpID) AS EmployeeCount FROM Employees GROUP BY DeptID;
11. Avg and Sum with revision
SELECT AVG(Age) AS AvgAge, SUM(Age) AS TotalAge FROM Students;
12. Joins
SELECT [Link], [Link] FROM Employees E INNER JOIN Departments D ON
[Link] = [Link];
13. View
CREATE VIEW ViewEmployees AS
SELECT EmpName, DeptName FROM Employees E JOIN Departments D ON [Link] =
[Link];
SELECT * FROM ViewEmployees;
14. Constraint (Default, Check, Unique)
CREATE TABLE Products(
ProductID INT PRIMARY KEY,
ProductName NVARCHAR(50) UNIQUE,
Price DECIMAL(10,2) DEFAULT 0,
Quantity INT CHECK (Quantity >= 0)
);
15. Add Constraint and Drop Constraint
ALTER TABLE Products ADD CONSTRAINT CK_Price CHECK (Price >= 0);
ALTER TABLE Products DROP CONSTRAINT CK_Price;
16. Constraint on Update Cascade on Delete Cascade
CREATE TABLE Orders(
OrderID INT PRIMARY KEY,
ProductID INT,
FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
17. Top, Concat, Substring, Len, CharIndex, Replace, Lower, Upper,
GetDate…
SELECT TOP 5 EmpName FROM Employees;
SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Students;
SELECT SUBSTRING(EmpName, 1, 3) FROM Employees;
SELECT LEN(EmpName) FROM Employees;
SELECT CHARINDEX('a', EmpName) FROM Employees;
SELECT REPLACE(EmpName, 'a', '@') FROM Employees;
SELECT LOWER(EmpName), UPPER(EmpName) FROM Employees;
SELECT GETDATE() AS CurrentDate;
18. Stored Procedure Normal Type
CREATE PROCEDURE GetAllDepartments AS BEGIN SELECT * FROM Departments; END;
EXEC GetAllDepartments;
19. Stored Procedure Input Parameters
CREATE PROCEDURE GetEmployeeByDept @DeptID INT AS BEGIN SELECT * FROM
Employees WHERE DeptID = @DeptID; END;
EXEC GetEmployeeByDept @DeptID = 2;
20. Stored Procedure Output Parameters
CREATE PROCEDURE GetEmployeeCount @DeptID INT, @EmpCount INT OUTPUT AS
BEGIN SELECT @EmpCount = COUNT(*) FROM Employees WHERE DeptID = @DeptID;
END;
DECLARE @Count INT;
EXEC GetEmployeeCount @DeptID = 2, @EmpCount = @Count OUTPUT;
SELECT @Count AS EmployeeCount;
21. Stored Procedure Return Procedure
CREATE PROCEDURE CheckDepartmentExists @DeptID INT AS BEGIN IF EXISTS(SELECT 1
FROM Departments WHERE DeptID = @DeptID) RETURN 1; ELSE RETURN 0; END;
DECLARE @Result INT;
EXEC @Result = CheckDepartmentExists @DeptID = 2;
SELECT @Result AS ExistsFlag;
22. Cursor
DECLARE EmpCursor CURSOR FOR SELECT EmpName FROM Employees;
DECLARE @EmpName NVARCHAR(50);
OPEN EmpCursor;
FETCH NEXT FROM EmpCursor INTO @EmpName;
WHILE @@FETCH_STATUS = 0 BEGIN PRINT @EmpName; FETCH NEXT FROM EmpCursor
INTO @EmpName; END;
CLOSE EmpCursor;
DEALLOCATE EmpCursor;