Sign in or start a free trial to avail of this feature.
1. Course Overview
In this lesson, we introduce the core material covered in the course.
Database summary (00:13)
In this course, we’ll continue with the bicycle store database, however, we’ll be adding a new table called customers. This will contain basic information such as name and address for customers who registered with our site.
For a full summary of the database, an overview can be found in the resources section of the SQL learning plan.
Course summary (04:46)
Throughout this course we’ll cover two main components of SQL queries, SELECT statements and WHERE clauses.
With SELECT statements, we’ll recap the basics from the first course and demonstrate other functionality such as SELECT DISTINCT.
With WHERE clauses, we’ll learn how these can be used to filter the data we receive in the output of our query.
In the previous course, we learned how SQL databases are constructed, and we also touched on the basics of how to write a query using SQL code.
Throughout this course, we'll be using the bicycle store database from the first SQL course.
This database is considerably simpler than any database you'll find yourself working with.
It has a small number of tables, and a small number of rows in each table.
This makes it easier to learn the basics of SQL.
However, we've added a little complexity to the database by adding one more table called customers.
This table tracks customers who've created an account on our store's website.
Including this table will enable us to build more complex SQL queries.
Getting acquainted with your database is essential if you want to be able to make insightful and inefficient queries.
This database was first introduced in course one, but we'll do a brief recap before continuing as this will be our first time viewing the database in its entirety.
We'll also be using this database in its current form for the remaining SQL courses.
To start, we have the sales table.
This records each sales transaction and in addition to the primary key field, it takes note of the date of the sale, whether or not a student discount was applied, and the customer information associated with that transaction.
We also added a new field here for points which represents the number of customer loyalty points awarded to our existing customers.
Note that not all transactions were made by customers who held an account with our store. When this occurs, the customer ID field will contain no value.
This is called a null value.
Similarly, since only customers with a registered account can receive points, the points field will be null for any transactions not associated with an account holding customer.
The customer ID field is a foreign key which is used to connect to the customers table.
Let's take a look at this table now.
Here we have the primary ID field which connects to the foreign key in the sales table.
We also have the first name and surname of the customer, as well as their address which is used for shipping and billing.
The points field keeps track of how many loyalty points each customer has accumulated and the referral ID field references the ID of another customer who referred them to our site. Note that not all customers were referred by another customer.
Next, we have the products table.
Along with the primary key field, it records the product name, price, and manufacturer ID.
This field is a foreign key used to connect to the manufacturer's table.
The manufacturer's table records the manufacturer name, origin, and a foreign key that connects it to the representatives table.
The representatives table contains information about the representatives at each manufacturer who we contact in order to make purchases.
Finally, we have the line items table. This is a junction table used to resolve the many to many relationship between sales and products. It breaks down each sales transaction into multiple rows, one for each product purchased during the transaction. It also lists the quantity of each product that was sold.
Returning to our relationship diagram, we can see that the line items table resolves the many to many relationship by establishing a many to one relationship with each of its parent tables.
Moving on, the sales table connects to the customers table with a one to many relationship because each sale can have just one associated customer account. Whereas each customer who holds an account may have many associated sales.
Similarly, the products table connects to the manufacturers table with a one to many relationship because each product can have just one manufacturer but each manufacturer likely makes many products.
Meanwhile, we have just one sales representative at each manufacturer, and each representative works for just one manufacturer. So these tables have a one-to-one relationship.
Now that we've been fully acquainted with our database, let's take a quick look at the structure of this course.
Our goal in this course is to focus on the fundamental components of selecting and filtering data in SQL.
The combination of these concepts will empower you to write queries matching a wide variety of parameters that can even be used on complex databases.
We'll start by covering select statements in more detail. This will include different ways we can modify the columns we select, and how to select only unique values.
We'll then shift our focus to filtering the results of our queries using where clauses.
We'll discover the different types of conditions that we can apply to our results and the various operators such as and, or, in, between, like and not.
These operators will enable us to add more control on the information that gets passed into the query output. Let's stop the lesson here.
In the next lesson, we'll start using more complex select statements.