Return a list of unique countries using DISTINCT. Give the results an alias of unique_country.(use AS within queries to rename columns and tables, and to provide an alias for the table results.)
SELECT
distinct country as unique_country
FROM
eurovision
restrict the rows to the first half using 'PERCENT', using the same shortcut as before to return all columns.
-- Return all columns, restricting the percent of rows returned(combine the use of * with TOP to limit the number of rows returned.)
SELECT
top (50) percent *
FROM
eurovision
-- Select the top 20 rows from description, nerc_region and event_date
SELECT
TOP (20) description,
nerc_region,
event_date
FROM
grid
-- Order by nerc_region, affected_customers & event_date
-- Event_date should be in descending order
ORDER BY
nerc_region,
affected_customers,
event_date DESC;
Use between:
-- The affected_customers column should be >= 50000 and <=150000
WHERE
affected_customers BETWEEN 50000
AND 150000
-- Define the order
order by
event_date DESC
Find the NULL value
-- Retrieve all columns
SELECT
*
FROM
grid
-- Return rows where demand_loss_mw is not missing or unknown
WHERE
demand_loss_mw IS NULL;
If we want the value is not null:
-- Return rows where demand_loss_mw is not missing or unknown
WHERE
demand_loss_mw IS NOT NULL;
Extract strings from words:
SELECT
description,
LEFT(description, 20) AS first_20_left
FROM grid
Try to remember that WHERE is applied before GROUP BY and filters on rows. HAVING is applied after GROUP BY, and filters on the groups or aggregates you created within the query
SELECT
nerc_region,
SUM (demand_loss_mw) AS demand_loss
FROM
grid
-- Remove the WHERE clause
WHERE demand_loss_mw IS NOT NULL
GROUP BY
nerc_region
-- Enter a new HAVING clause so that the sum of demand_loss_mw is greater than 10000
HAVING
SUM(demand_loss_mw) > 10000
ORDER BY
demand_loss DESC;
HAVING and WHERE
Notice: having and where, we can not use having demand_loss_mw >10000 because having should be matched with group by.
A WHERE clause wouldn't work because we are applying the filter based on an aggregate value - in this example, the count of entries by each country. That completes this chapter.
LEFT JOIN:
A LEFT JOIN will return ALL rows in the first table, and any matching rows in the right table. If there aren't any matches in the right table for a particular row, then a NULL is returned. This quickly lets you assess the gaps in your data, and how many you have.
UNION ALL:
UNION ALL returns all the rows from both queries, even if they contain duplicates.
CREATE TABLE test_table(
test_date DATE,
test_name VARCHAR(20),
test_int INT
)
INSERT some data and retrieve it.
-- Create the table
CREATE TABLE tracks(
-- Create track column
track VARCHAR(200),
-- Create album column
album VARCHAR(160),
-- Create track_length_mins column
track_length_mins INT
);
-- Complete the statement to enter the data to the table
INSERT INTO tracks
-- Specify the destination columns
(track, album, track_length_mins)
-- Insert the appropriate values for track, album and track length
VALUES
('Basket Case', 'Dookie', 3);
-- Select all columns from the new table
SELECT
*
FROM
tracks;
UPDATE
when updating, you need to SET the change you want to make and pinpoint the exact rows to change using a WHERE clause.
-- Select the album
SELECT
title
FROM
album
WHERE
album_id = 213;
-- UPDATE the title of the album
UPDATE
album
SET
title = 'Pure Cult: The Best Of The Cult'
WHERE
album_id = 213;
-- Run the query again
SELECT
title
FROM
album
WHERE
album_id = 213;
DELTE:
-- Run the query
SELECT
*
FROM
album
-- DELETE the record
DELETE FROM
album
WHERE
album_id = 1
-- Run the query again
SELECT
*
FROM
album;
DECLARE:
Using variables makes it easy to run a query multiple times, with different values, without having to scroll down and amend the WHERE clause each time. You can quickly update the variable at the top of the query instead.
