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';