Unit 1:  Introduction to Data, Databases, and SQL

Section 1

  • Define and explain the purpose of data and the most widely used data types.
  • Understand the hierarchy of units used to calculate data size.
  • Calculate the size of data.
  • Define and explain the purpose of databases.
  • Understand relational vs. non-relational databases.
  • Use design and ethics principles to guide database use, including referential integrity and ACID.
  • Learn about the Structured Query Language (SQL) and its history.
  • Understand the main differences between various SQL flavors: PostgreSQL, MySQL, and SQLite.
  • Learn further details about PostgreSQL, which will be used in this course.

Section 2

  • Examine the structure of a SQL database.
  • Use the SELECT statement to display all columns of a table.
  • Understand that SQL is case-insensitive.
  • Understand the purpose of the keyword NULL and SQL’s three-valued logic.
  • Order query results using ORDER BY.
  • Limit the number of results using the keyword LIMIT.

Section 3

  • Use the SELECT statement to only display one column of interest.
  • Use the SELECT statement to display two or more columns.
  • Change the order of the displayed columns if needed.
  • Skip some rows in the output using the keyword OFFSET.
  • Order multiple columns at the same time.

Section 4

  • Filter the results of queries using the keyword WHERE.
  • Access a table in a schema by typing schema.table.
  • Remove duplicate results with SELECT DISTINCT.

Section 5

  • Use aggregate functions:
    • Function COUNT() to count results.
    • Functions MIN(), MAX() to find minimum and maximum values.
    • Functions SUM(), AVG() to calculate the sum and average of values, respectively.
  • Narrow down queries using WHERE … LIKE … and WHERE … BETWEEN …
  • Combine multiple conditions in the search using the keywords AND, OR, NOT.

Unit 2:  SQL Queries

Section 6

  • Practice basic SQL queries by exploring the Northwind database and in particular the table Employees.

Section 7

  • Practice basic SQL queries by exploring the table Products of the Northwind database.

Section 8

  • Practice basic SQL queries by exploring the table Products of the Northwind database.
  • Use SQL for calculations
  • Use the WHERE … IN … clause.

Section 9

  • Practice basic SQL queries by exploring the table Order Details of the Northwind database.
  • Create new columns and name them using the keyword AS
  • Group results for better readability using the GROUP BY … and GROUP BY … HAVING … clauses.

Section 10

  • Practice basic SQL queries by exploring the Customers table of the Northwind database.

Unit 3:  Creating and Managing Tables

Section 11

  • Explain the different data types in the SQL standard and in PostgreSQL.
  • Create and drop schemata.
  • Learn about the two schemata in NCLab where trainees can store their own data.
  • Create tables.

Section 12

  • Insert complete rows (without using column names).
  • Insert incomplete rows (without using column names).
  • Insert incomplete rows (using the names of columns).

Section 13

  • Insert multiple rows at once.
  • Create a new table by copying an existing table.
  • Create a new empty table which has the same structure as an existing table.
  • Create a new table by copying selected rows from an existing table.

Section 14

  • Insert selected rows from a table into an existing table.
  • Use the powerful statement ALTER TABLE to modify tables, such as:
    • rename tables,
    • add / rename / drop (= delete) columns,
    • change data types of columns, etc.
  • Delete all rows from a table, selected rows, or entire tables.
  • Delete selected rows from a table.
  • Delete entire tables.
  • Understand that the result of the VALUES clause and of the SELECT statement is a TABLE.

Section 15

  • Define constraints and specify default values.

Unit 4:  Joining Tables

Section 16

  • Combine data from two different tables using the inner join operation.
  • Initially, the tables are matched based on a shared column of the same name.
  • Write inner joins in three simple steps:
    • Basic SELECT query followed by the columns one wants to display,
    • Add INNER JOIN followed by the second table name,
    • Add USING followed by the name of the shared column in parentheses.
  • Join three and more tables, and that joining multiple tables is equally simple as joining just two.

Section 17

  • Review the structure of the tables in the schema World.
  • Perform an inner join of tables based on columns of different names, using the keyword ON.
  • Abbreviated table names using the keyword AS.
  • Understand that some keywords (for example, AS) can be omitted.
  • Write inner joins using an alternative (implicit) syntax without the keywords INNER JOIN and USING/ON.

Section 18

  • Practice inner joins by solving practical tasks related to the schema World.

Section 19

  • Review the schema Northwind, and then perform calculations which require combining data from various tables in this schema.
  • Combine inner joins with filtering, grouping and other basic SQL techniques.

Section 20

  • Practice inner joins performing calculations which combine data from various tables in the schema Northwind.
  • Combine inner joins with filtering, grouping and other basic SQL techniques.