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 implementation of SQL but with added functionality.

SELECT

<aside> 💡 It is used for retrieving data from the table.

</aside>

Example:

• Selecting one columns

SELECT description
FROM grid;

The above query selects the description column from the grid table.

• Selecting more than one column

SELECT artist_id, artist_name
FROM artist;

The above query selects the artist_id and artist_name columns from the artist table.

SELECT TOP(N)

<aside> 💡 It return the top n rows from the table.

</aside>

SELECT TOP(5) artist
FROM artists;

The above query selects the top 5 artist in the artist column from the artists table.

SELECT TOP(N) PERCENT

<aside> 💡 It selects the top n percent of rows.

</aside>

SELECT TOP(5) PERCENT artist
FROM artists;

The above query selects the top 5 percent of artist in the artist column from the artists table.

SELECT DISTINCT

<aside> 💡 It returns unique rows.

</aside>

SELECT DISTINCT artist
FROM album;

The above query returns all the unique artist from the album table.

SELECT *

<aside> 💡 It returns all rows.

</aside>

SELECT * 
FROM grid;

The above query returns all rows from the grid table.

AS

<aside> 💡 It it used for aliasing column names.

</aside>

SELECT fname AS first_name
FROM profile;

The above query aliases the column fname as first_name from the profile table.

ORDER BY

<aside> 💡 It is used for sorting the rows by the values of some column.

</aside>

Example:

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

In the above example, first the rows are ordered by the values of the column year_intro and then by the column product_id , both in the ascending order.

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

In the above example, first the rows are ordered by the values of the column year_intro in descending order and then by the column product_id in the ascending order.

• WHERE

  <aside> 💡 The where clause is use to filter those rows that meets the certain criteria.

  </aside>

  Example:

  SELECT customer_id, total
  FROM invoice
  WHERE total > 15;
  

  The above query returns all those where the value of the column total is greater than 15.

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

  The above query returns all those where the value of the column total is not equal to 10.

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

  The above query returns all the rows where all the specified conditions are satisfied.

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

  The above query returns all the rows where at least one of the given condition is satisfied.

BETWEEN

<aside> 💡 It is used to check if the values lies between the specified range inclusive.

</aside>

Example:

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

The above query returns those rows where the value of the total column is in between the range of 20 and 30 inclusive.

Example:

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

The above query returns those rows where the value of the total column is not in between the range of 20 and 30 inclusive.

NULL

<aside> 💡 It indicates that there is no value for that record. It helps to highlight gaps in our data.

</aside>

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

The above query returns those rows where the value of the column billing_state is NULL.

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

The above query returns those rows where the value of the column billing_state is not NULL.

IN

<aside> 💡 It is used to check if the values is in the specified list followed by the IN keyword.

</aside>

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

The above query returns those rows where the value of the column release_year is in the specified list.

LIKE

<aside> 💡 It is used to check if the values match the pattern specified after the LIKE keyword.

</aside>

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

The above query returns those rows where the values of the artist column begins with an f.

Aggregating Data

SUM

<aside> 💡 It is used to calculate the total amount of a column.

</aside>

SELECT 
    SUM(affected_customers) AS total_affected
FROM grid;

The above query returns the total of all the values in the affected_customers column and displayed the result in the new column total_affected.

SELECT 
    SUM(affected_customers) AS total_affected,
    SUM(demand_loss_mw) AS total_loss
FROM grid;

The above query returns the total of all values in the affected_customers column and demand_loss_mw column and displayed the result in the new columns total_affected and total_loss column.

COUNT

<aside> 💡 It returns the total count of the rows in table returned by the query.

</aside>

SELECT
    COUNT(affected_customers) AS count_affected
FROM grid;

The above query returns the count of all the rows in the table grid.

COUNT DISTINCT

<aside> 💡 It returns the total count of the distinct records.

</aside>

SELECT
    COUNT(DISTINCT affected_customers) AS unique_count_affected
FROM grid;

The above query returns the total count of distinct rows in the grid table.

MIN

<aside> 💡 It returns the minimum value of a column.

</aside>

Example:

SELECT
    MIN(affected_customers) AS min_affected_customers
FROM grid
WHERE affected_customers > 0;

The above query returns the minimum value

It returns the minimum value of the affected_customer column from all the records where affected_customer> 0.

MAX

<aside> 💡 It returns the maximum value of a column.

</aside>

SELECT
    MAX(affected_customers) AS max_affected_customers
FROM grid
WHERE affected_customers > 0;

It returns the maximum value of the affected_customercolumn from all the records where affected_customer > 0.

AVG

<aside> 💡 It returns the average value of a column.

</aside>

SELECT
    AVG(affected_customers) AS avg_affected_customers
FROM grid;

It returns the average value of the affected_customercolumn.

LEN

<aside> 💡 It returns the length of the string.

</aside>

SELECT
    description,
    LEN(description) AS description_length
FROM grid;

The above query returns the description column and the total number of characters in the description column as description_length from the grid table.

LEFT

<aside> 💡 It returns the specified no of characters from the string from the left side.

</aside>

SELECT
    description,
    LEFT(description, 20) AS first_20_left
FROM grid;

The above query returns the description column and the first 20 characters from the left from the description column as first_20_left from the grid table.