Introduction to Relational Databases and Basic SQL

Introduction to Relational Databases and Basic SQL

What are they?

A language designed to interact with relational databases. All relational database management systems (RDBMS) use SQL, although each RDBMS might have slight syntax variations. However, the majority of SQL syntax is consistent across different systems.

A type of database that organizes data into one or more tables where each table has rows and columns. The key property is that we can show relationship between tables via common fields known as keys.

A software that allows users to create, modify, and query databases. Different DBMSs are tailored to different type of databases. Relational DBMS (RDBMS) is used in relational database. Some popular RDBMS examples include MySQL, PostgreSQL, Oracle.

A sturctured container that stores any digital data (e.g. texts, images, and audios), and can be interacted with any DBMS.

Structure Query Language (SQL)

Database Management System (DBMS)

Relational Database

Database

Other DBMSs

A table example

Roadmap to create a table

Define a table schema

A table schema is a blueprint that defines a table's structures. It can then be converted to an SQL script to create the table. This step is usually done with external diagramming tools (e.g. Lucidchart and Draw.io) A table schema defines:

Data types

Common column data types include Text, Varchar, Bool, Int, Date, and Enum.

Table, column names

Constraints

Constraints are rule for data, ensuring accuracy and reliability.

Apply your schema in SQL

You need a database before you can apply your schema to a database. To create it, you can follow the SQL syntax: CREATE DATABASE database_name; Since there can be many databases exist, you need to specify which database you want to use:USE database_name; Now, you're ready to apply your table schemas. The general syntax for table creation is: CREATE TABLE table_name ( column1 datatype [optional constraint(s)], column2 datatype [optional constraint(s)], ... [optional constraint(s)]);

Ready to try it out?

Insert content to tables

Now, the database contains only empty tables. We need to populate the tables with SQL. The general syntax is: INSERT INTO table_name (column1, column2, column3, ...)VALUES (value1, value2, value3, ...) [optionally more rows]; * Note that the inserted values must follow the same orders as you specify in the column list If you insert row(s) with all columns present, you can leave out the column list: INSERT INTO table_name VALUES (value1, value2, value3, ...) [optionally more rows]; * The inserted values are assumed to follow the column order as defined in the table

Of course, you can also insert only the values you want: INSERT INTO table_name (column1, column3, ...) VALUES (value1, value3, ...) [optionally more rows]; Inserting values that reference other tables, those values must exist in other tables first; Otherwise, it will throw an error.

Ready to try it out?

Pop Quiz

Start

Question 1/5

Question 2/5

Question 3/5

Question 4/5

Question 5/5

Next: Fetch data, Filtering, Aggregates, Group by, and Join

Step 4

We will learn how to fetch data across two or more tables at one query

04

Join tables

We will learn the common aggregate functions (e.g. summation, average, max, and min, etc) to boost data analysis

Step 3

03

Aggregate function

Step 2

02

We will learn how to apply filter when fetching data and sort the result

Where clause and sort

Step 1

We will learn the general SQL syntax to fetch one or more data from a table.

01

Fetch data

Fetch Data

The general syntax to fetch data is:SELECT column1, column2, ... FROM table_name; If you want to select across all columns, a shortcut is to use the wildcard '*': SELECT * FROM table_name;

Employees table:

What do you want to select?

• All columns

• Name, salary

• Name

Employees table:

Oftentimes, you don't want to fetch all data from a table. To filter the results, you can use the WHERE clause. SELECT column1, column2, ... FROM table_name WHERE column1 = ... ; You can also include multiple conditions in the WHERE clause by using AND, and OR. SELECT column1, column2, ... FROM table_name WHERE column1 = ... AND column2 = ...; If you want to sort the results, you can use the ORDER BY clause on specified columns. Note that ORDER BY must come after WHERE clause, if it is used. There are different operators you can use within the WHERE clause other than =.

FILTER FETCH RESULT

What do you want to filter?

• Employees in HR

• Earn >= 5,500, sort

• Not Employee Alice

Aggregates and Group by

Employees table:

Aggregates are functions that do computations on a set of columns. They are useful for data summarization and analysis. Examples include: COUNT(), SUM(), MIN(), MAX(), and AVG(). * COUNT() counts the number of rows. SUM() sums up the values in the column. They are often used in conjunction with the GROUP BY clause, which groups the fetched results by the specified columns and applies aggregates to them. SELECT column1, column2, AGGREGATE_FUNCTION(column3) FROM table_name WHERE condition GROUP BY column1, column2 HAVING condition ORDER BY column1, column2; The HAVING clause is used to filter the result after applying the GROUP BY. Notice the order among WHERE, HAVING, and ORDER BY must be enforced.

What do you want to select?

• # of employees in HR

• Total earn in HR

• Average earn

Search Across Multiple Tables

Employees table:

JOIN in SQL is used to combine rows from two or more tables based on a related column between them. It allows you to retrieve data spread across multiple tables using a common field. There are different types of JOIN. The most commonly used one is the INNER/Default JOIN.

Departments table:

Aliases

Aliases allow us to temporarily rename a column or a table in a SQL script. To use them, you can directly provide the temporary name after the column or table name. Optionally , you can also add the term AS before renaming. Aliases serve two main purposes: 1. Rename the output columns in a SELECT statement to make the result more readable. SELECT first_name AS name, salary AS monthly_wage FROM Employees; orSELECT first_name name, salary AS monthly_wage FROM Employees; 2. Shorten the table name to make complex queries more readable and resolve column ambiguaties.SELECT id FROM Employees JOIN Departments ON department_id = id;(This query will throw an error because the DBMS doesn't know which id you refer to since both tables have the id field)We need aliases to fix the error:SELECT e.id FROM Employees AS e JOIN Departments AS d ON e.departmet_id = d.id;

Pop Quiz 2

Start

Question 1/10

Question 2/10

Question 3/10

Question 4/10

Question 5/10

Question 6/10

Question 7/10

Question 8/10

Question 9/10

Question 10/10

Case Chanllenge

Now, you have learned almost all the fundamental SQL concepts. It's time to test your overall knowledge. In the following, you are given a real-world scenario and different parts to complete the task. You will need to use the following online editor to complete your task. Lastly, Good luck and have fun with this task. You’ve been hired by a small online store that sells products in various categories. Your job is to set up and query the database. The system must keep track of:

• Categories: Each category has an ID, a name, and a short description.
• Products: Each product has an ID, a name, a price, and is associated with exactly one category.

Your tasks: 1. Create two tables: Categories and Products. 2. Insert sample data into these tables. 3. Write queries that demonstrate various SQL features.

PART A: Table Creation

1. Create the Categories Table

• Must have a primary key.
• Must have a unique name for each category.
• Columns should include:
• CategoryID (integer, primary key)
• CategoryName (string, cannot be NULL, must be unique)
• Description (string, cannot be NULL)

2. Create the Products Table

• Must have a primary key.
• Must have a foreign key that references Categories.
• Columns should include:
• ProductID (integer, primary key)
• ProductName (string, cannot be NULL)
• Price (numeric type with two decimals)
• CategoryID (integer, references Categories(CategoryID))

Answer

PART B: Insert Sample Data

1. Insert Data into CategoriesUse the following data:

2. Insert Data into ProductsUse the following data:

Answer

PART C: Queries

1. List All Products
1. Retrieve all columns from the Products table.
2. Filter by Price
1. Show the ProductName and Price of products that cost more than 100.
2. Use a WHERE clause.
3. Join Products with Their Category Names
1. Display each ProductName along with its CategoryName.
2. Use an INNER JOIN.
4. Find Products in “Clothing”
1. List the ProductName of all products belonging to the “Clothing” category.
2. Hint: Filter on CategoryName.

Answer

PART C: Queries

5. Sort Products by Price

1. Show the ProductName and Price, ordered from highest to lowest.

6. Average Price per Category

1. List the CategoryName and the average price of products in that category (AvgPrice).
2. Use GROUP BY.

7. Filter Groups with HAVING

1. Extend the previous query to show only categories whose average product price is above 100.
2. Use a HAVING clause on the aggregated result.

8. Count Products per Category

1. Show the CategoryName and product count (as ProductCount) in each category.
2. Use GROUP BY to group by category.

9. Show All Categories, Including Those Without Products

1. Display all CategoryName values along with any corresponding ProductName.
2. Use a LEFT JOIN of Categories to Products.
3. If a category has no products, ProductName should be NULL.

Answer

The END

Complete the following questions in the MySQL editor using tables created in previous step

INSERT INTO courses VALUES ('3', 'Computer Networking'); INSERT INTO Students VALUES ('1', 'Alex', 'Wang', 'CS', '3', false); * Must add the new course first because the student depends on the course

Which and Why of the following code will throw an error:INSERT INTO Courses VALUES ('4', 'Intro to Python'); INSERT INTO Courses VALUES ('5', 'Intro to Python'); INSERT INTO Courses VALUES ('5', 'Intro to Java');

A CS student Alex Wang has signed for the Computer Networking course. Let's add Alex to our database. (Be careful the order to add the new rows)

There are two new courses are offering. One is Intro to Database. Another is Intro to AI. Add these two new courses to the Courses table

The last one because the primary key id already existed. The second one can have duplicate course name because the 'name' field is not unique.

INSERT INTO Courses VALUES ('1', 'Intro to AI');INSERT INTO Courses VALUES ('2', 'Intro to Database');

Table Schema

Table Content

Common Constraint Examples

• PRIMARY KEY - A unique row identifier (not required, but highly recommended). Equivalent to UNIQUE and NOT NULL.
• NOT NULL - Must have a value
• UNIQUE - The same value can not exist more than once. Similar to PRIMARY KEY, but it can have a NULL value.
• DEFAULT - Specify the default value if no value is given.
• FOREIGN KEY - Used to reference a row in another table (key to defining the relationship between tables). When designing database relationships, need to consider the different types. For more details, refer to this

resource

More in here

CREATE TABLE Categories ( CategoryID INT PRIMARY KEY, CategoryName VARCHAR(50) NOT NULL UNIQUE, Description VARCHAR(100) NOT NULL ); CREATE TABLE Products ( ProductID INT PRIMARY KEY, ProductName VARCHAR(100) NOT NULL, Price DECIMAL(10, 2), CategoryID INT, CONSTRAINT FK_Category FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID) );

SELECT * FROM Employees; or SELECT id, name, department, salary FROM Employees;

SELECT Department, COUNT(*) FROM EmployeesWHERE Department = 'HR GROUP BY Department; or SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING Department = 'HR'; Both produce the same result. The first one first filter out all rows that are not 'HR', then apply the GROUP BY. The second one applies the GROUP BY first, then filter out those that are not in 'HR'.

5. SELECT ProductName, Price FROM Products ORDER BY Price DESC; 6. SELECT c.CategoryName, AVG(p.Price) AS AvgPrice FROM Products p INNER JOIN Categories c ON p.CategoryID = c.CategoryID GROUP BY c.CategoryName; 7. SELECT c.CategoryName, AVG(p.Price) AS AvgPrice FROM Products p INNER JOIN Categories c ON p.CategoryID = c.CategoryID GROUP BY c.CategoryName HAVING AVG(p.Price) > 100;

8. SELECT c.CategoryName, COUNT(p.ProductID) AS ProductCount FROM Categories c LEFT JOIN Products p ON c.CategoryID = p.CategoryID GROUP BY c.CategoryName; 9. SELECT c.CategoryName, p.ProductName FROM Categories c LEFT JOIN Products p ON c.CategoryID = p.CategoryID;

CROSS JOIN

Returns cartesian product (all possible combinations of rows). SELECT Employees.Name, Departments.DepartmentName FROM Employees CROSS JOIN Departments;

SELECT name, salary FROM EmployeesWHERE name != 'Bob';

Create the following tables in the MySQL editor

CREATE TABLE Courses ( id varchar(100) primary key, name text not null ); CREATE TABLE Students ( id varchar(100) primary key, first_name text not null, last_name text not null, major text not null, course varchar(100), is_grad bool default true, FOREIGN KEY (course) REFERENCES Courses(id) );

Modfiy the previous students table to include a 'course' field, which references which courses a student is taking. The courses table should have an id and a name

CREATE TABLE Students ( id varchar(100) primary key, first_name text not null, last_name text not null, major text not null, is_grad bool default true );

CREATE TABLE Students ( id text, first_name text, last_name text, major text, is_grad bool );

Create the above table

Create the above table

SELECT SUM(salary) FROM EmployeesWHERE department = 'HR';

SELECT name FROM Employees;

SELECT * FROM EmployeesWHERE Department = 'HR';

SELECT AVG(salary) FROM Employees;

1. SELECT * FROM Products; 2. SELECT ProductName, Price FROM Products WHERE Price > 100; 3. SELECT p.ProductName, c.CategoryName FROM Products p INNER JOIN Categories c ON p.CategoryID = c.CategoryID; 4. SELECT p.ProductName FROM Products p INNER JOIN Categories c ON p.CategoryID = c.CategoryID WHERE c.CategoryName = 'Clothing';

LEFT JOIN

Returns all rows from the left table (Employees) and matching rows from the right table (Departments). Rows without matches get NULLs. SELECT Employees.Name, Departments.DepartmentName FROM Employees LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; It returns all employees and their departments. If they don't work at any department, return NULL.

Congragulation !!!

You have reached the end of this learning module. I hope you enjoyed learning SQL, and I firmly believe you are now comfortable to complete any basic SQL tasks. If you want to learn more about advance SQL concepts such as Index, Functions, Materialized Views, feel free to check out the following links. Please also considering completing a short feedback form with the link below. As always, thank you for taking this learning modules.

• Index

• Function

• Materialized View

INSERT INTO Categories (CategoryID, CategoryName, Description) VALUES (201, 'Electronics', 'Phones, tablets, and gadgets'), (202, 'Clothing', 'Apparel, accessories'), (203, 'Home & Kitchen', 'Appliances and home supplies'); INSERT INTO Products (ProductID, ProductName, Price, CategoryID) VALUES (1, 'Smartphone', 699.99, 201), (2, 'Laptop', 999.50, 201), (3, 'T-Shirt', 19.99, 202), (4, 'Jeans', 49.99, 202), (5, 'Coffee Maker', 35.50, 203), (6, 'Blender', 45.00, 203), (7, 'Microwave Oven', 120.00, 203), (8, 'Wireless Headphones', 129.99, 201);

Congragulation !!!

You have reached the end of this learning module. I hope you enjoyed learning SQL, and I firmly believe you are now comfortable to complete any basic SQL tasks. If you want to learn more about advance SQL concepts such as Index, Functions, Materialized Views, feel free to check out the following links. Please consider completing the feedback form with the link below. As always, thank you for taking this learning modules.

• Index

• Function

• Materialized View

SELECT name, salary FROM EmployeesWHERE salary >= 5,500 ORDER BY salary; ORDER BY sorts ascendingly by default. If want to sort in descending order, append DESC after the sorting column name.

RIGHT JOIN

Returns all rows from the right table (Departments) and matching rows from the left table (Employees). Rows without matches get NULLs. SELECT Employees.Name, Departments.DepartmentName FROM Employees RIGHT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

Inner Join / Default Join

Returns only the matching rows. SELECT Employees.Name, Departments.DepartmentName FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID; It only returns employees who work at the same department.

SELECT name, salary FROM Employees;

FULL JOIN

Returns all rows when there is a match in one of the tables. Rows without matches get NULLs. SELECT Employees.Name, Departments.DepartmentName FROM Employees FULL JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;