MSSQLSERVER 

This page is about the SQL Server commands. All of the contents are based on the code in w3schools - SQL with the sample database, Northwind. To make myself easier to look up the commands I want in the future, I picked out the ones I have been used a lot in my work and add some notes on it. I also borrowed some materials from the course LinkdedIn Learning - Microsoft SQL Server 2016: Query Data. The lecturer of this course, Gerry O’Brien, did a great job on teaching this course and provided me a terrific starting point on learning Microsoft SQL Server. If you are interested in the complete commands taught in the course. Please check out, Exercise Folder of the course. All the example codes can be executed on w3schools - SQL Try it Editor or test the code on the SQL Fiddle by creating new tables by ourselves.

Details

  • References
    1. LinkdedIn Learning - Microsoft SQL Server 2016: Query Data
    2. Exercise Folder of the course
    3. Transact-SQL Reference (Database Engine)
    4. w3schools - SQL
    5. SQL Fiddle
    6. zentut - SQL Tutorial
  • Example Database : Northwind
  • Common SQL Server Commands

    • Data Overview (SELECT, TOP, DISTINCT, ORDER BY, GROUP BY, COUNT(), AVG())

      SELECT TOP 3 *
FROM Customers
      SELECT DISTINCT Country
FROM Customers
      SELECT COUNT(*) AS Total
FROM Customers
      SELECT * FROM Customers
ORDER BY country DESC, city
      SELECT country, city, count(*) AS Total_by_country_city
FROM Customers
GROUP BY country, city
      SELECT ProductID, AVG(Quantity) AS AVG_Qty
FROM OrderDetails
GROUP BY ProductID

    • Subset Data with conditions (WHERE, IN, <>, IS NULL, …)

      SELECT * FROM Customers
WHERE Country IN ('UK', 'USA')
    AND Country IS NOT NULL
      SELECT * FROM OrderDetails
WHERE Quantity > 20

    • Subquery (SELECT in WHERE)

      SELECT * FROM OrderDetails
WHERE OrderID =
(
    SELECT OrderID FROM Orders
    WHERE OrderDate > '1996-07-04'
)
      -- Correlated subquery, have to use table alias
SELECT * FROM OrderDetails o1
WHERE Quantity =
(
    SELECT MAX(Quantity) FROM OrderDetails o2
    WHERE o1.ProductID = o2.ProductID
)

    • Common Table Expression (CTE) (WITH)
      It is a temporary table saved only in memory and can be referred in the later query.

      -- Need to run whole code in one time
WITH temp_tab AS
(
   SELECT * FROM OrderDetails o1
   WHERE Quantity =
   (
       SELECT MAX(Quantity) FROM OrderDetails o2
       WHERE o1.ProductID = o2.ProductID
   )
 )
 SELECT * FROM temp_tab
 ORDER BY Quantity

    • Create New Columns from Other Columns (CASE)
      It is how if and then logic works in SQL Server. 

      SELECT ShipperID, Phone,
CASE ShipperName
   WHEN 'Speedy Express' THEN 'SE'
   WHEN 'United Package' THEN 'UP'
   WHEN 'Federal Shipping' THEN 'FS'
END AS shippername_short
FROM Shippers
      SELECT *,
CASE
     WHEN BirthDate < '1960-01-01' THEN 'Senior'
     ELSE 'Not Senior'
END AS age_category
FROM Employees

    • Join Multiple tables through Columns (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, …)

      SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName
FROM ((Orders
    INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
    INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID)
      SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
    AND A.City = B.City
ORDER BY A.City

      The above images are from here and here.

    • Combine Rows from Multiple Tables (UNION, INTERSECT, EXCEPT)

      -- only return distinct values
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City
      -- return duplicated values
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City

      The above image is from here.

    • Table Transformation. From Long to Wide or from Wide to Long (PIVOT, UNPIVOT)
      Example code in SQL Fiddle - PIVOT
      Example code in SQL Fiddle - UNPIVOT

      CREATE TABLE un_tab
([Name] varchar(7), [Subject] varchar(7), [Grade] numeric(10,5))

INSERT INTO un_tab
    ([Name], [Subject], [Grade])
VALUES
('Andy', 'Math', 60),
('Andy', 'Science', 70),
('Andy', 'English', 80),
('Judy', 'Math', 20),
('Judy', 'Science', 80),
('Judy', 'English', 100),
('Joe', 'Math', 90),
('Joe', 'Science', 70),
('Joe', 'English', 90)

SELECT *
FROM un_tab
PIVOT
(
    min(Grade)
    FOR Subject IN ([Math], [Science], [English])
) AS tab
      CREATE TABLE tab
     ([Name] varchar(7), [Math] numeric(10,5), [Science] numeric(10,5), [English] numeric(10,5))

INSERT INTO tab
     ([Name], [Math], [Science], [English])
VALUES
     ('Andy', 60, 70, 80),
     ('Judy', 20, 80, 100),
     ('Joe', 90, 70, 90)

SELECT Name, Subject, Grade
FROM tab
UNPIVOT
(
    Grade
    FOR Subject IN (Math, Science, English)
) AS un_tab

    • Add new rows to a table using IF to avoid duplication (EXIST, IF, BEGIN, END) Example code in SQL Fiddle - EXISTS

      CREATE TABLE tab
    ([Name] varchar(7), [Math] numeric(10,5), [Science] numeric(10,5), [English] numeric(10,5))

INSERT INTO tab
    ([Name], [Math], [Science], [English])
VALUES
    ('Andy', 60, 70, 80),
    ('Judy', 20, 80, 100),
    ('Joe', 90, 70, 90)

IF NOT EXISTS
(
    SELECT 1
    FROM tab
    WHERE Name = 'Andy'
)
BEGIN
    INSERT INTO tab
    ( Name, Math, Science, English)
VALUES ('Andy', 60, 70, 80)
END

    • Stored Procedures (’ @ ’)
      It is like define a function for future uses.
      Example code in SQL Fiddle - @

      CREATE TABLE tab
    ([Name] varchar(7), [Math] numeric(10,5), [Science] numeric(10,5), [English] numeric(10,5))

INSERT INTO tab
    ([Name], [Math], [Science], [English])
VALUES
    ('Andy', 60, 70, 80),
    ('Judy', 20, 80, 100),
    ('Joe', 90, 70, 90)

CREATE PROCEDURE pp @Name nvarchar(50)
AS
BEGIN
    SELECT * FROM tab
    WHERE Name = @Name
END;

EXEC pp @Name = 'Andy';