SQL Fundamentals

In this course, trainees learn SQL (Structured Query Language), a mainstream language used to create, manage, and query RDBMSs (Relational Database Management Systems). At the end of this course, trainees will be prepared to work with SQL database servers.

Note: This course is only available as part of NCLab’s Data Analyst Career Training program.

Recommended Background

To be successful in this course, trainees 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. Trainees will learn how to work with SQL commands within this course.

Student Learning Outcomes (SLO)

  • Use units of data size to determine data storage requirements.
  • Explain the basic principles of relational (SQL) and non-relational (No-SQL) databases.
  • Name the most widely used SQL and No-SQL databases.
  • Compare the most common flavors of SQL databases.
  • Define referential integrity, ACID, and cascade operations.
  • Explain the role of columns and rows in tables.
  • Formulate basic SQL queries using the SELECT statement.
  • Filter the results of queries.
  • Limit the number of results.
  • Use built-in functions to aggregate data..
  • Create, remove, and alter schemata and tables.
  • Define constraints and specify default values.
  • Explain how the inner join works.
  • Use the inner join to connect two or more tables.

Equipment Requirements

Computer, laptop or tablet with Internet access, email, and one of the following browsers:

  • Google Chrome
  • Mozilla Firefox
  • Microsoft Internet Explorer (9.0 or above)
  • Safari

Course Structure and Length

The course is self-paced, and trainees practice each skill or concept as they 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.  Trainees 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, trainees can practice queries and create portfolio artifacts with NCLab’s SQL apps. They can use a project idea from NCLab or create their own.  NCLab provides several practice databases that can be queried with the SQL app.

Trainees will need approximately 80 hours to complete the course. Since the course is self-paced, the amount of time required to complete the course will vary from trainee to trainee.  Trainees 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 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.