SQL Fundamentals Syllabus

In this course, you learn how to work with data, specifically with relational databases and SQL. The course is practice-based, meaning that you learn while working on practical tasks.

What You Will Learn

This course will train you in SQL (Structured Query Language), a mainstream language used to create, manage, and query RDBMSs (Relational Database Management Systems). At the end of this course, you will be prepared to “ask the right questions” and work with SQL database servers.

Recommended Background

For this course, you should be comfortable with basic math and algebra operations, number systems, and data functions such as average and sum.

A background in coding is helpful but not required. You will learn how to work with SQL commands within this course.

Course Structure and Length

The course is self-paced, and you will practice each skill or concept as you go.  Automatic feedback is built into the course for both practices and quizzes.

The course is divided into four Units, and each Unit is composed of five Sections.  Each Section consists of 7 instructional/practice levels, a quiz, and a master (proficiency) level.  You can return to any level or quiz for review.

This table illustrates the course structure as units, sections, and levels.

While learning skills in the SQL Fundamentals course, you can practice queries and create portfolio artifacts with NCLab’s SQL apps. Use a project idea from NCLab or create your own.  NCLab provides several practice databases that can be queried with the SQL app.

You will need approximately 80-120 hours to complete the course. Since the course is self-paced, the amount of time required to complete the course will vary from student to student.  You are responsible for learning both the tutorial content and the skills acquired through practice.

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 your own 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 you can store your 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.