DEV Community

Cover image for Free AWS BootCamp WEEK 4: SQL Databases
Msaghu
Msaghu

Posted on

Free AWS BootCamp WEEK 4: SQL Databases

Hi guys, welcome back!!!
This is week 4 of the Andrew Brown AWS Free code camp that you can also follow on Youtube and LinkedIn.

This week we will be handling:

  1. SQL
  2. PostgreSQL

What are Databases?

  • A database is a server where we can store, manipulate and retrieve data.
  • A relational database defines relationships between tables of data inside the database.
  • Therefore PostgreSQL is the database engine and SQL is the Structured Query Language/the programming language.

What are the advantages of databases?

  • They have more storage capabilities than spreadsheets e.g Google spreadsheets or Excel sheets.
  • Are more secure as data is encrypted.
  • Multiple users can write queries to gather insight from the data at the same time.

What is SQL?

  • SQL stands for Structured Query Language.
  • This is the most widely used programming language for databases.

What are the basics of SQL?

  • SQL is not processed in the order in which it is written i.e. it starts from FROM ==> SELECT ==> LIMIT

What are tables in SQL?

  • These are the main building blocks of Databases.
  • Databases are organized into tables which hold related data about a particular subject.
  • Tables can have both columns and rows , rows are referred to as records and columns are referred to as fields.
  • A tables fields are limited to the number created during database creation.
  • While there is no limit to the number of records.
  • Table names should be lowercase, should not include spaces(use underscores instead) and can be plural.
  • Field names should be lowercase, should not include spaces(use underscores instead), should be singular, each field should have different names and should not share names with the table name.
  • Unique identifiers/keys help identify records in a table, are unique and are often numbers.

Data types in SQL

  • Data types are chosen depending on the type of data the field will hold i.e numbers, text, dates.
  • Data types are necessary as they are stored differently and they take up different amounts of space.
  • A string refers to a sequence of characters such as letters or punctuation.
  • VARCHAR is a flexible and popular string data type in SQL.
  • INT is a flexible and popular integer data type in SQL.
  • NUMERIC is a flexible and popular float data type in SQL.

What are Schemas?

  • Are blueprints of databases.
  • Schemas show database design i.e tables included in the database and the relationships between the tables.
  • Schemas also inform the data type each field can hold.
  • Information in a database table is stored in a hard disk of a server.

Basic SQL queries

  • This is the basic structure of an SQL query
SELECT name  ------------------> chooses the field name
FROM patrons; -----------------> chooses the table in which the fields are listed
Enter fullscreen mode Exit fullscreen mode
  • Selecting multiple fields
SELECT card_num, name
FROM patrons;
Enter fullscreen mode Exit fullscreen mode
  • Selecting all fields
SELECT *
FROM patrons;
Enter fullscreen mode Exit fullscreen mode
  • Renaming columns using aliasing(temporarily)
SELECT name AS first_name, year_hired
FROM employees;
Enter fullscreen mode Exit fullscreen mode
  • To show a value i.e year_hired only once using DISTINCT.
SELECT DISTINCT year_hired
FROM employees;
Enter fullscreen mode Exit fullscreen mode
  • To show multiple fields i.e year_hired .
SELECT dept_id, year_hired
FROM employees;
Enter fullscreen mode Exit fullscreen mode
  • To show multiple fields i.e year_hired using DISTINCT.
SELECT DISTINCT dept_id, year_hired
FROM employees;
Enter fullscreen mode Exit fullscreen mode
  • A view is a virtual table that is a query code stored for future use.
  • To save a SQL query as a view;
CREATE VIEW employee_hire_years AS
SELECT id, name, year_hired
FROM employees;
Enter fullscreen mode Exit fullscreen mode

Querying databases

  • COUNT() - returns number of records with a value in a field
SELECT COUNT(birthdate) AS count_birthdates 
FROM people;

-- to count multiple fields from a table
SELECT COUNT(name) AS count_names, COUNT(birthdate) AS count_birthdates 
FROM people;

-- to count the number of records in a table
SELECT COUNT(*) AS total_records
FROM people;
Enter fullscreen mode Exit fullscreen mode
  • To find distinct values in table
SELECT COUNT(DISTINCT birthdate) AS count_distinct_birthdates
FROM people;
Enter fullscreen mode Exit fullscreen mode

Filtering data

  • Using WHERE -n Comparison operators >, <, =, <=, >=, <>(not equal to)
WHERE with comparison operators

SELECT title
FROM films
WHERE release_year > 1960;
Enter fullscreen mode Exit fullscreen mode
  • To use a comparison operator from a films table to find is a film language could be English
-- Count the Spanish-language films
SELECT COUNT(language) AS count_spanish
FROM films
WHERE language = 'Spanish';
Enter fullscreen mode Exit fullscreen mode
  • To use multiple criteria with : OR, AND, BETWEEN
-- Using OR operator
SELECT *
FROM films
WHERE language = 'Spanish' OR length = 120;

-- Using AND operator
SELECT *
FROM films
WHERE language = 'Spanish' AND length = 120;

-- Using AND, OR
SELECT title
FROM films
WHERE (release_year = 1994 OR release_year = 1995) AND (certification = "PG" OR certification = "R");

-- Using BETWEEN operator(shorthand for the AND operator)
SELECT title
FROM films
WHERE length BETWEEN 120 AND 180;

-- Using BETWEEN operator(shorthand for the AND operator)
SELECT title
FROM films
WHERE length BETWEEN 120 AND 180 AND country='Kenya';

Enter fullscreen mode Exit fullscreen mode

Filtering text

  • Its usually recommended to filter with a pattern rather than specific text.
  • We will then use: LIKE, NOT LIKE, IN.

  • Use Like for records that match the specified pattern

- LIKE use like with % to match zero, one or many characters 

SELECT name
FROM people
WHERE name LIKE 'Ade%';

- LIKE use like with _ to match a single character
SELECT name
FROM people
WHERE name LIKE 'Ev_';
Enter fullscreen mode Exit fullscreen mode
  • Use NOT LIKE for records that do not match the specified pattern
SELECT name
FROM people
WHERE name NOT LIKE 'A.%';
Enter fullscreen mode Exit fullscreen mode
  • To filter across many conditions or a range of numbers with WHERE, OR or WHERE, IN
SELECT title
FROM films
WHERE release_year = 1920
OR release_year = 1930
OR release_year = 1940;

SELECT title
FROM films
WHERE release_year IN (1920, 1930, 1940);
Enter fullscreen mode Exit fullscreen mode

Filtering Data with NULL values

  • Use IS NULL incase of missing data
--Gives the titles where release_year was not provided 
SELECT title
FROM films
WHERE release_year IS NULL;

--Gives the total number of titles where release_year was not provided 
SELECT COUNT(*) AS not_released
FROM films
WHERE release_year IS NULL;

--Gives the total number of titles where release_year was provided
SELECT COUNT(title) AS not_released
FROM films
WHERE release_year IS NOT NULL;
Enter fullscreen mode Exit fullscreen mode

Summarizing Data

  • For numerical data AVG(), SUM()
SELECT AVG(budget)
FROM films;
Enter fullscreen mode Exit fullscreen mode
  • For non-numerical data MIN(), MAX(), COUNT()

Summarizing subsets

SELECT AVG(budget) AS avg_budget
FROM films
WHERE release_year >= 2010;
Enter fullscreen mode Exit fullscreen mode
  • ROUND()
--number_to_round, decimal_places

SELECT ROUND(AVG(budget), 2) AS avg_budget
FROM films
WHERE release_year >= 2010;

--to round to a whole number
SELECT ROUND(AVG(budget)) AS avg_budget
FROM films
WHERE release_year >= 2010;

--to round using a negative number
SELECT ROUND(AVG(budget), -5) AS avg_budget
FROM films
WHERE release_year >= 2010;

Enter fullscreen mode Exit fullscreen mode

Basic Arithmetic

  • Basic arithmetic is +, -, * and /
SELECT (4 + 3);

SELECT (4 / 3);  --------------> 1

SELECT (4.0 / 3.0); -----------> 1.3333
Enter fullscreen mode Exit fullscreen mode

Sorting results

  • Using ORDER BY(sorts in ascending order)
-- Using ORDER BY
SELECT title, budget
FROM films
ORDER BY budget;

-- Using ORDER BY and ASCending order
SELECT title, budget
FROM films
ORDER BY budget ASC;

-- Using ORDER BY and DESCending order
SELECT title, budget
FROM films
WHERE budget IS NOT NULL
ORDER BY budget DESC;

Enter fullscreen mode Exit fullscreen mode
  • ORDER BY multiple fields
-- Using ORDER BY and DESCending order
SELECT title, wins, imdb_score
FROM best_movies
ORDER BY wins DESC, imdb_score DESC;
Enter fullscreen mode Exit fullscreen mode

Grouping data

  • GROUP BY single fields,
-- Grouping by certification, then counting
SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification;
Enter fullscreen mode Exit fullscreen mode
  • GROUP BY multiple fields,
-- Grouping by certification, then counting
SELECT certification, language COUNT(title) AS title_count
FROM films
GROUP BY certification, language;

-- Modify the query to also list the average budget and average gross
SELECT release_year, AVG(budget) AS avg_budget, AVG(gross) AS avg_gross
FROM films
WHERE release_year > 1990
GROUP BY release_year;
Enter fullscreen mode Exit fullscreen mode
  • Using Grouping BY with ORDER BY
SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification
ORDER BY title_count DESC;
Enter fullscreen mode Exit fullscreen mode

Filtering Grouped Data

  • Since we cant filter aggregate functions with WHERE
-- This will not work
SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification
WHERE COUNT(title) > 10;

--This WILL work
SELECT certification, COUNT(title) AS title_count
FROM films
GROUP BY certification
HAVING COUNT(title) > 10;

--This WILL work
SELECT release_year
FROM films
GROUP BY release_year
HAVING AVG(duration) > 10;
Enter fullscreen mode Exit fullscreen mode

RESOURCES

  1. DataCamp Beginner SQL & Intermediate Courses

  2. FreeCodecamp - Learn PostgreSQL Tutorial

Top comments (0)