Introduction to SQL Server

A SQL Server is a relational database system developed by Microsoft. And a Transact-SQL (T-SQL) is a Microsoft’s implemenataion of SQL butwith added functionality.

Queries

• SELECT

  Is used for retreiving data from the table.

  • Example:

    •  SELECT description 
       FROM grid;
      

    • Selecting more than one column

        SELECT artist_id, artist_name
        FROM artist;
      

    • SELECT TOP()

      Return top 5 rows

        SELECT TOP(5) artist
        FROM artists;
      

    • SELECT TOP() PERCENT

      Return top 5% of rows

        SELECT TOP(5) PERCENT artist
        FROM artists;
      

    • SELECT DISTINCT

      Returns unique rows

        SELECT DISTINCT nerc_region
        FROM grid;
      

    • SELECT *

      Returns all rows

        SELECT *
        FROM grid;
      

      NOT suitable for large tables

• AS

  Aliasing column names

  • Example:

    •   SELECT demand_loss_mw AS lost_demand
        FROM grid;
      

    •   SELECT description AS cause_of_courage
        FROM grid;
      

• ORDER BY

  Use ORDER BY if order is important.

  • Example:

    •   SELECT TOP(10) product_id, year_intro
        FROM products
        ORDER BY year_intro, product_id;
      

      In the above example, first it is ordered by year_intro and then it is order by product_id

    •   SELECT TOP(10) product_id, year_intro
        FROM products
        ORDER BY year_intro DESC, product_id;
      

      In the above example, firs it is ordered by year_intro in the descending order and then by product_id

• WHERE

  Use Where to return rows that meet certain criteria.

  • Example:

    •   SELECT customer_id, total
        FROM invoice
        WHERE total > 15;
      

      The above SQL statement returns records where total is greater than 15.

    •   SELECT customer_id, total
        FROM invoice
        WHERE total <> 10;
      

      The above SQL statement returns records where total is not equal to 10.

    •   SELECT *
        FROM songlist
        WHERE
            release_year = 1994
            AND artist = 'Green Day'
            AND song = 'Basket Case';
      

      The above SQL statement returns records where all the given conditions are satisfied.

    •   SELECT 
            song,
            artist,
            release_year
        FROM songlist
        WHERE
            release_year = 1994
            OR release_year >2000;
      

      The above SQL statement returns records where at least one of the given condition is satisfied.

• BETWEEN

  Use Between to return where the values lies between the specified range.

  • Example:

    •   SELECT customer_id, total
        FROM invoice
        WHERE total BETWEEN 20 AND 30;
      

      Returns those records where the value of total is in between the range of 20-30 inclusive.

    •   SELECT customer_id, total
        FROM invoice
        WHERE total NOT BETWEEN 20 AND 30;
      

      Returns those records where the value of total is not in between the range of 20-30 inclusive.

• NULL

  • It indicates that there is no value for that record
  • It helps to highlight gaps in our data.

  • Example:

    •   SELECT 
            TOP(6) total,
            billing_state
        FROM invoice
        WHERE billing_state IS NULL;
      

      It returns records where billing_state is NULL.

    •   SELECT 
            TOP(6) total,
            billing_state
        FROM invoice
        WHERE billing_state IS NOT NULL;
      

      It returns records where billing_state is NOT NULL.

• IN

  It is used to return records where the values are inside the list followed by the IN keyword.

  • Example:

    •   SELECT song, release_year
        FROM songlist
        WHERE
            release_year IN (1985, 1991, 1992);
      

      Returns records where release_year is in the list followed by the IN keyword.

• LIKE

  It is used to return records where the values match the pattern specified after the LIKE keyword

  • Example:

    •   SELECT artist
        FROM songlist
        WHERE artist LIKE 'f%';
      

      Returns those records where values of artist column begins with an f.