Course Notes: Introduction to SQL Server
  • AI Chat
  • Code
  • Report
    • Beta
    Spinner

    SQL Server

    Course Notes

    • How to connect SQL cells to an integration to run a query.
    • How to connect your own integration.

    Note: For sample integrations you'll have read-only access. It's not possible to make any changes such as adding, deleting, or modifying the data (e.g., creating tables, views, etc.).

    Part 1 - SELECTion Box

    • TOP() Used to limit the number of rows returned. If we include PERCENT clause we are going to retrive the percent number declared on TOP clause
    • DISTINCT Return unique rows
    • ORDER BY By default, this clause is going to take the "ascending order". If you want the "descending order", you have to include DESC clause.
    • WHERE Here are some operatos and keywords commonly used on WHERE clause,
      • <,>,>=,<=,<>
      • NOT BETWEEN, BETWEEN, IS NOT NULL, IS NULL
      • AND, OR, and the parentheses ()
    • ORDER BY going to take the "ascending order". If you want the "descending order", you have to include DESC clause.

    Examples:

    SELECT TOP(5) artist 
FROM artist;
----------------------------------------------------------------------------
SELECT TOP(5) PERCENT artist /*Return top 5% of rows*/
FROM artist;
----------------------------------------------------------------------------
SELECT DISTINCT artist
FROM artist;
----------------------------------------------------------------------------
SELECT TOP(10) product_id, year_intro
FROM products
ORDER BY year_intro DESC, product_id;
----------------------------------------------------------------------------
SELECT song, artist, release_year
FROM songlist
WHERE
artist = 'Green Day'
AND (release_year = 1994 OR release_year > 2000);
----------------------------------------------------------------------------
SELECT song, artist, release_year
FROM songlist
WHERE
(artist = 'Green Day' AND release_year = 1994)
OR (artist = 'Green Day' AND release_year > 2000);
----------------------------------------------------------------------------
SELECT song, artist, release_year
FROM songlist
WHERE
release_year IN (2000, 1994);
----------------------------------------------------------------------------
SELECT song, artist, release_year
FROM songlist
WHERE
song LIKE 'a%';

    Part 2 - Groups, strings, and counting things

    Aggregate functions

    • SUM() Calculate the total amount of a column value
    • COUNT() It's just to count, and can be combined with DISTINCT
    • MIN() Return the minimum value from a column
    • MAX() Obtain the maximum column value.
    • AVG() Return the average value of a column.

    Strings functions

    • LEN() Find the length of a text column (which means the number of characters, including spaces.
    • LEFT() If we want to extract a number of characters from the beginning of a string.
    • RIGHT() This function starts from the right hand side of the string and works back to extract the number of characters we specify.
    • CHARINDEX Helps us find a specific character within a string.
    • SUBSTRING To extract from the middle portion of a string, as opposed to from the LEFT or RIGHT edges.
    • REPLACE Finding and replacing text.

    Grouping and Having

    See the example code below where GROUP BY and HAVING were implemented. You have to use HAVING when you want to filter rows based on Aggregated Functions like SUM().

    --************************************************************************--
### Aggregate functions ###
--************************************************************************--
SELECT 
SUM(affected_customers) AS total_affected
SUM(demand_lost_mw) AS total_loss
FROM grid;
----------------------------------------------------------------------------
SELECT 
COUNT(affected_customers) AS count_affected
FROM grid;
----------------------------------------------------------------------------
SELECT 
COUNT(DISTINCT affected_customers) AS unique_count_affected
FROM grid;
----------------------------------------------------------------------------
SELECT 
MIN(affected_customers) AS min_affected_customers
FROM grid
WHERE affected_customers > 0 ;
----------------------------------------------------------------------------
SELECT 
MAX(affected_customers) AS max_affected_customers
FROM grid;
----------------------------------------------------------------------------
SELECT 
AVG (affected_customers) AS avg_affected_customers
FROM grid;

--************************************************************************--
### Strings functions ###
--************************************************************************--
SELECT 
description,
LEN(description) AS description_length
FROM grid;
----------------------------------------------------------------------------
SELECT 
description,
LEFT(description,20) AS first_20_left
RIGHT(description,20) AS last_20
FROM grid;
----------------------------------------------------------------------------
SELECT 
url,
CHARINDEX ('_',url) AS char_location, 
SUBSTRING (url,12,12) AS char_location,
REPLACE(url, '_', '-') AS replace_with_hyphen
FROM courses;
--************************************************************************--
### Grouping and Having ###
--************************************************************************--
SELECT
SUM(demand_loss_mv) AS lost_demand,
description,
FROM grid
WHERE
description LIKE '%storm'
AND demand_loss_mv IS NOT NULL
GROUP BY description;
----------------------------------------------------------------------------
SELECT
SUM(demand_loss_mv) AS lost_demand,
description,
FROM grid
WHERE
description LIKE '%storm'
AND demand_loss_mv IS NOT NULL
GROUP BY description
HAVING SUM(demand_loss_mv) > 1000;
----------------------------------------------------------------------------

    Part 3 - Joining tables

    • INNER JOIN
    • LEFT JOIN
    • RIGHT JOIN
    • UNION Don´t return duplicated rows
    • UNION ALL It does all the rows, included the one that are duplicated
    --************************************************************************--
### Joining Tables ###
--************************************************************************--
SELECT 
album_id,
title,
album.artist_id,
name AS artist_name
FROM album
INNER JOIN artist ON artist.artist_id = album.artist_id
WHERE album.artist_id = 1;
----------------------------------------------------------------------------
SELECT 
album_id,
title,
artist_id
FROM album
WHERE artist_id IN (1,3)
UNION
SELECT
album_id,
title,
artist_id
FROM album
WHERE artist_id IN (1,4,5)
----------------------------------------------------------------------------
SELECT 
COUNT(DISTINCT affected_customers) AS unique_count_affected
FROM grid;
----------------------------------------------------------------------------
SELECT 
MIN(affected_customers) AS min_affected_customers
FROM grid
WHERE affected_customers > 0 ;
----------------------------------------------------------------------------
SELECT 
MAX(affected_customers) AS max_affected_customers
FROM grid;
----------------------------------------------------------------------------
SELECT 
AVG (affected_customers) AS avg_affected_customers
FROM grid;