Microsoft SQL Server Practical Guide
CREATE DATABASE
Creates a new database.
Syntax / Example
CREATE DATABASE SalesDB;
CREATE TABLE
Creates a table.
Syntax / Example
CREATE TABLE Employees(
EmployeeID INT PRIMARY KEY,
Name VARCHAR(100),
Salary DECIMAL(10,2)
);
INSERT
Adds rows.
Syntax / Example
INSERT INTO Employees VALUES (1,'Prem',50000);
SELECT
Retrieves data.
Syntax / Example
SELECT * FROM Employees;
SELECT Name,Salary FROM Employees WHERE Salary>40000;
UPDATE
Modifies rows.
Syntax / Example
UPDATE Employees SET Salary=55000 WHERE EmployeeID=1;
DELETE
Removes rows.
Syntax / Example
DELETE FROM Employees WHERE EmployeeID=1;
JOINS
Combine tables.
Syntax / Example
SELECT [Link],[Link]
FROM Employees e
INNER JOIN Departments d ON [Link]=[Link];
GROUP BY
Aggregate.
Syntax / Example
SELECT DeptID,AVG(Salary) AvgSalary FROM Employees GROUP BY DeptID;
HAVING
Filter groups.
Syntax / Example
SELECT DeptID,COUNT(*) Cnt FROM Employees GROUP BY DeptID HAVING COUNT(*)>5;
VIEWS
Virtual table.
Syntax / Example
CREATE VIEW vwEmployees AS SELECT Name,Salary FROM Employees;
STORED PROCEDURE
Reusable SQL.
Syntax / Example
CREATE PROC GetEmp @Id INT AS BEGIN SELECT * FROM Employees WHERE EmployeeID=@Id END;
FUNCTION
Returns value.
Syntax / Example
CREATE FUNCTION [Link](@Salary DECIMAL(10,2)) RETURNS DECIMAL(10,2) AS BEGIN RETURN @Salary*0.1
INDEX
Improves performance.
Syntax / Example
CREATE INDEX IX_Name ON Employees(Name);
TRANSACTION
Maintain consistency.
Syntax / Example
BEGIN TRANSACTION;
UPDATE Accounts SET Balance=Balance-100 WHERE Id=1;
UPDATE Accounts SET Balance=Balance+100 WHERE Id=2;
COMMIT;
CTE
Readable queries.
Syntax / Example
WITH EmpCTE AS (SELECT * FROM Employees WHERE Salary>50000)
SELECT * FROM EmpCTE;
WINDOW FUNCTION
Ranking.
Syntax / Example
SELECT Name,Salary,DENSE_RANK() OVER(ORDER BY Salary DESC) RankNo FROM Employees;