SQL Journey - Lesson 1 & 2

Lesson 1

Database
A container (usually a file or set of files) to store organized data.

Table
A structured list of data of a specific type.

Schema
Information about database and table layout and properties.

Column
A single field in a table. All tables are made up of one or more columns.

Tip: Breaking Up Data
It's important to break data into multiple columns correctly (e.g., city, state, ZIP). If combined, sorting/filtering becomes hard. Choose granularity based on needs. For example, house number and street name are usually kept together unless you need to sort by street name.

Datatype
The kind of data allowed in a column, restricting or permitting specific values.

Row
A record in a table. Though “record” and “row” are interchangeable, “row” is more technically correct.

Primary key
A column (or set of columns) that uniquely identifies every row in a table.

Tip: Always Define Primary Keys
Primary keys are optional but recommended for reliable data manipulation and maintenance.

• No duplicate primary key values.
• Primary key columns cannot be NULL.
• Values shouldn’t be changed or reused.

Primary keys can consist of multiple columns. Together, their values must be unique.

What is SQL?
SQL (Structured Query Language) is used to communicate with databases. It is simple but powerful, supported by most DBMS, and uses a small number of keywords.

Lesson 2

This lesson introduces the SELECT statement to retrieve one or more columns of data from a table.

Input

SELECT prod_name
  FROM Products;

Output

prod_name
  Fish bean bag toy
  Bird bean bag toy
  Rabbit bean bag toy
  8 inch teddy bear
  12 inch teddy bear
  18 inch teddy bear
  Raggedy Ann
  King doll
  Queen doll

Tip: Terminating Statements
Multiple SQL statements should be separated by semicolons. Some DBMS require it; others don't. Adding it won't hurt.

Tip: SQL Case Sensitivity
SQL keywords are case-insensitive. Best practice: use uppercase for SQL and lowercase for names.

Tip: Use of White Space
White space is ignored. You can write SQL on one line or many. Example:

SELECT prod_name FROM Products;
  SELECT
  prod_name
  FROM
  Products;

To retrieve multiple columns:

Input

SELECT prod_id, prod_name, prod_price
  FROM Products;

Tip: Take Care with Commas
Use commas between column names, but not after the last column name.

Note: Presentation of Data
SQL returns raw, unformatted data. Formatting is handled by the application layer, not SQL.

To retrieve all columns:

Input

SELECT *
  FROM Products;

Caution: Using Wildcards
Using * to select all columns may reduce performance.

To get distinct rows:

Input

SELECT vend_id
  FROM Products;

Output

vend_id
  BRS01
  BRS01
  BRS01
  DLL01
  DLL01
  DLL01
  DLL01
  FNG01
  FNG01

To eliminate duplicates:

Input

SELECT DISTINCT vend_id
  FROM Products;

Output

vend_id
  BRS01
  DLL01
  FNG01

Caution: Can’t Be Partially DISTINCT
DISTINCT applies to all selected columns, not just the first one.

To limit rows:

Input

SELECT prod_name
  FROM Products
  LIMIT 5;

To skip rows and limit count:

Input

SELECT prod_name
  FROM Products
  LIMIT 5 OFFSET 5;

Output

prod_name
  Rabbit bean bag toy
  Raggedy Ann
  King doll
  Queen doll

Analysis
LIMIT 5 OFFSET 5 skips the first 5 rows and returns the next 5. Only 4 rows are returned here because the table had 9 products.

Caution: Row 0
The first row is row 0, not row 1. So OFFSET 1 gets the second row.

Tip: MySQL & MariaDB Shortcut
You can write LIMIT 4 OFFSET 3 as LIMIT 3,4 in these databases.

Lesson 3: Sorting Retrieved Data

In this lesson, you will learn how to use the SELECT statement’s ORDER BY clause to sort retrieved data as needed.

By default, SQL does not guarantee any specific order of returned rows. To control order, you must use ORDER BY.

Input

SELECT prod_name
      FROM Products
      ORDER BY prod_name;

Analysis
This statement sorts results by the prod_name column. Without ORDER BY, the order of results is unpredictable.

Caution: Position of ORDER BY Clause
ORDER BY must be the last clause in a SELECT statement. Placing it elsewhere will cause errors.

Tip: Sorting by Nonselected Columns
You can sort by columns that are not included in the SELECT list.

Sorting by Multiple Columns

You can sort by more than one column. For example, by price and then by name.

Input

SELECT prod_id, prod_price, prod_name
      FROM Products
      ORDER BY prod_price, prod_name;

Output

prod_id     prod_price     prod_name
      BNBG02      3.49           Bird bean bag toy
      BNBG01      3.49           Fish bean bag toy
      BNBG03      3.49           Rabbit bean bag toy
      RGAN01      4.99           Raggedy Ann
      BR01        5.99           8 inch teddy bear
      BR02        8.99           12 inch teddy bear
      RYL01       9.49           King doll
      RYL02       9.49           Queen doll
      BR03        11.99          18 inch teddy bear

Sorting by Column Position

You can also sort using the position of columns in the SELECT list instead of column names.

Input

SELECT prod_id, prod_price, prod_name
      FROM Products
      ORDER BY 2, 3;

Output

(Same as previous output)

Analysis
ORDER BY 2 sorts by the second column (prod_price), and ORDER BY 2, 3 sorts by price then name. This can be risky if column positions change.

⚠️ Downsides of using column positions:

• Higher risk of mistakes if columns are rearranged
• Not usable if the column isn’t in SELECT

Specifying Sort Direction

By default, sorting is ascending (A–Z). To sort in descending order (Z–A), use the DESC keyword.

Input

SELECT prod_id, prod_price, prod_name
      FROM Products
      ORDER BY prod_price DESC;

Output

prod_id     prod_price     prod_name
      BR03        11.99          18 inch teddy bear
      RYL01       9.49           King doll
      RYL02       9.49           Queen doll
      BR02        8.99           12 inch teddy bear
      BR01        5.99           8 inch teddy bear
      RGAN01      4.99           Raggedy Ann

Sorting by multiple columns in descending order:

Input

SELECT prod_id, prod_price, prod_name
      FROM Products
      ORDER BY prod_price DESC, prod_name;

Note: DESC is short for DESCENDING. ASC (or ASCENDING) is rarely needed because it's the default.

Tip: Case-Sensitivity and Sort Orders
When sorting text, whether A = a depends on database collation. Most DBMSs treat A and a the same by default, but this can be configured. If you need a special sort order (e.g., for non-English characters), ORDER BY might not be enough, and you'll need DBA help.

Lesson 4: Filtering Data

In this lesson, you will learn how to use the SELECT statement’s WHERE clause to specify search conditions.

Using the WHERE Clause

Database tables usually contain large amounts of data, but you seldom need to retrieve all the rows. More often, you'll want a subset of the data for a specific task. You define this using the WHERE clause, placed after the FROM clause.

Input

SELECT prod_name, prod_price
          FROM Products
          WHERE prod_price = 3.49;

Analysis
This query returns only the rows where prod_price is 3.49.

Output

prod_name              prod_price
          Fish bean bag toy      3.49
          Bird bean bag toy      3.49
          Rabbit bean bag toy    3.49

Tip: SQL vs Application Filtering
Although filtering can be done in the application layer, it's best done in SQL. Filtering in code causes performance issues and network overhead. Let the database do the filtering.

Caution: WHERE Clause Position
If you're using both WHERE and ORDER BY, make sure WHERE comes first.

Checking for Nonmatches

Input

SELECT vend_id, prod_name
          FROM Products
          WHERE vend_id <> 'DLL01';

Tip: When to Use Quotes
Use single quotes around string values (like vendor IDs). Do not quote numeric values.

Checking for a Range of Values

Input

SELECT prod_name, prod_price
          FROM Products
          WHERE prod_price BETWEEN 5 AND 10;

Output

prod_name              prod_price
          8 inch teddy bear      5.99
          12 inch teddy bear     8.99
          King doll              9.49
          Queen doll             9.49

Analysis
BETWEEN requires two values and includes both ends of the range.

Checking for NULL Values

Input

SELECT prod_name
          FROM Products
          WHERE prod_price IS NULL;

Caution: NULL and Non-matches
NULLs do not behave like regular values. A condition like "WHERE column <> value" will not include rows where the column is NULL. Always check NULL handling carefully.

Summary

In this lesson, you learned how to use the WHERE clause to filter data by equality, inequality, ranges, and NULL values.

Lesson 5: Advanced Data Filtering

In this lesson, you’ll learn how to combine WHERE clauses to create powerful and sophisticated search conditions. You’ll also learn how to use the NOT and IN operators.

Combining WHERE Clauses

Until now, you’ve filtered using a single condition. SQL allows combining multiple conditions using AND or OR operators.

Operator: A special keyword used to join or alter clauses in a WHERE clause (e.g., AND, OR).

Using the AND Operator

Input

SELECT prod_id, prod_price, prod_name
              FROM Products
              WHERE vend_id = 'DLL01' AND prod_price <= 4;

Analysis
This returns products from vendor DLL01 with a price less than or equal to $4. Both conditions must be true for a row to be included.

AND: Returns rows matching all specified conditions.

Using the OR Operator

Input

SELECT prod_name, prod_price
              FROM Products
              WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

Analysis
This returns products from either DLL01 or BRS01. Only one condition needs to match for inclusion.

Output

prod_name              prod_price
              Fish bean bag toy      3.49
              Bird bean bag toy      3.49
              Rabbit bean bag toy    3.49
              8 inch teddy bear      5.99
              12 inch teddy bear     8.99
              18 inch teddy bear     11.99
              Raggedy Ann            4.99

OR: Returns rows matching any of the specified conditions.

Understanding Order of Evaluation

SQL evaluates AND before OR. Misplaced conditions can produce unexpected results. Use parentheses to control logic.

Incorrect Input

SELECT prod_name, prod_price
              FROM Products
              WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'
              AND prod_price >= 10;

Output

prod_name              prod_price
              Fish bean bag toy      3.49
              Bird bean bag toy      3.49
              Rabbit bean bag toy    3.49
              18 inch teddy bear     11.99
              Raggedy Ann            4.99

Analysis
The AND takes precedence, so this returns all DLL01 products (any price) and BRS01 products with price ≥ 10. This is likely not what was intended.

Corrected Input

SELECT prod_name, prod_price
              FROM Products
              WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01')
              AND prod_price >= 10;

Output

prod_name              prod_price
              18 inch teddy bear     11.99

Tip: Using Parentheses in WHERE Clauses
Always use parentheses when mixing AND and OR conditions. It avoids ambiguity and logical mistakes.

Using the IN Operator

Input

SELECT prod_name, prod_price
              FROM Products
              WHERE vend_id IN ('DLL01', 'BRS01')
              ORDER BY prod_name;

Output

prod_name              prod_price
              12 inch teddy bear     8.99
              18 inch teddy bear     11.99
              8 inch teddy bear      5.99
              Bird bean bag toy      3.49
              Fish bean bag toy      3.49
              Rabbit bean bag toy    3.49
              Raggedy Ann            4.99

Analysis
IN is a cleaner, more efficient alternative to multiple OR statements.

Why use IN?

• Cleaner syntax with long lists
• Better control when combining with AND/OR
• Typically faster than multiple ORs
• Can be combined with subqueries

Using the NOT Operator

Input

SELECT prod_name
              FROM Products
              WHERE NOT vend_id = 'DLL01'
              ORDER BY prod_name;

Analysis
NOT negates the condition following it. Here, it returns all rows where vend_id is not DLL01.

Why use NOT?
While basic use cases don’t benefit much, NOT is powerful when combined with IN, BETWEEN, or EXISTS.

Note: NOT in MariaDB
MariaDB supports NOT with IN, BETWEEN, and EXISTS. Some DBMSs may limit this to just a few clause types.

Summary

This lesson taught how to combine WHERE clauses using AND, OR, and NOT, and how to control logic with parentheses. You also learned the use and advantages of the IN operator.

Lesson 6: Using Wildcard Filtering

In this lesson, you’ll learn what wildcards are, how they are used, and how to perform wildcard searches using the LIKE operator for more flexible filtering.

Using the LIKE Operator

Previous filtering methods used known values — matching exact values or ranges. But sometimes, you want to find partial matches, such as all products containing the phrase "bean bag". This is where wildcard searching helps.

Wildcards: Special characters used to match parts of a value.

Search pattern: A search condition using literal text, wildcard characters, or both.

To use wildcards, you must use the LIKE operator. LIKE tells the database to perform a pattern match, not just a direct comparison.

The Percent Sign (%) Wildcard

The percent sign (%) matches any number of characters. For example, to find all products starting with the word “Fish”:

Input

SELECT prod_id, prod_name
                  FROM Products
                  WHERE prod_name LIKE 'Fish%';

Analysis:
This retrieves any value that starts with “Fish”. The percent sign (%) matches any trailing characters.

The Underscore (_) Wildcard

The underscore (_) matches exactly one character. For example, to match "12 inch teddy bear" or "18 inch teddy bear":

Input

SELECT prod_id, prod_name
                  FROM Products
                  WHERE prod_name LIKE '__ inch teddy bear';

This matches any product name where the first two characters can be anything, followed by " inch teddy bear".

The Brackets ([]) Wildcard

The brackets wildcard allows you to specify a list or range of characters to match at a specific position in the string. Example syntax:

WHERE prod_name LIKE '[FBR]%'

This matches any product name starting with F, B, or R.

Tips for Using Wildcards

• ✅ Use wildcards only when necessary; they are slower than exact match filters.
• ⚠️ Avoid starting patterns with a wildcard unless needed — these are the slowest to process.
• 🎯 Be precise — misplaced wildcards can lead to incorrect results.

Used wisely, wildcards are a powerful part of your SQL toolkit.

Summary

In this lesson, you learned how to use SQL wildcards — %, _, and [] — in WHERE clauses with the LIKE operator. While powerful, wildcards should be used with care for performance and accuracy.

Lesson 7: Creating Calculated Fields

Understanding Calculated Fields

Data in a database is not always in the exact format your application needs. Common examples include:

• Combining company name and location from separate fields
• Formatting address lines by joining city, state, and ZIP
• Changing case (e.g., all uppercase)
• Computing total prices from quantity × item_price
• Performing totals, averages, or calculations directly in queries

Rather than reformat data in the application, you can retrieve calculated or reformatted values directly from SQL queries using calculated fields.

Calculated fields don’t exist in the table—they are created on-the-fly using expressions inside the SELECT statement.

Concatenate: Joining values together (appending them) into one combined value.

To concatenate fields in SQL, you use:

• + or || in many databases
• CONCAT() in MySQL or MariaDB

The RTRIM() function can be used to trim trailing spaces for cleaner output.

Using Aliases

Calculated fields do not have a name by default. To make them usable in client applications, assign them a name using the AS keyword.

Tip: Other Uses for Aliases
Aliases are useful for renaming columns with special characters, making column names more readable, or resolving ambiguity in joins.

Performing Mathematical Calculations

Input

SELECT prod_id,
                             quantity,
                             item_price,
                             quantity * item_price AS expanded_price
                      FROM OrderItems
                      WHERE order_num = 20008;

Output

prod_id   quantity   item_price   expanded_price
                      RGAN01    5          4.9900       24.9500
                      BR03      5          11.9900      59.9500
                      BNBG01    10         3.4900       34.9000
                      BNBG02    10         3.4900       34.9000

Analysis
The expanded_price column is calculated using quantity * item_price. It behaves like any regular column in output and can be used in reports, exports, or applications.

Summary

In this lesson, you learned how to create calculated fields in SQL for string concatenation and math. You also saw how to use aliases to name these fields for easier use in applications.

Lesson 8: Using Data Manipulation Functions

Understanding Functions

In this lesson, you’ll learn what functions are, the types of functions supported by SQL DBMSs, and the advantages and challenges of using them effectively.

Functions are built-in operations provided by SQL databases to transform or analyze data values within queries.

Types of SQL Functions

• Text functions: Manipulate string values (e.g., trimming, padding, case conversion)
• Numeric functions: Perform math operations on numeric data (e.g., absolute value, rounding)
• Date and time functions: Handle date/time values, extract parts, or calculate differences
• System functions: Return info about the DBMS or session (e.g., current user, server time)

Text Manipulation Functions

In Lesson 7, we used RTRIM() to remove trailing white space. Here's another example using UPPER() to convert text to uppercase:

Input

SELECT prod_name, UPPER(prod_name) AS uppercase_name
                          FROM Products;

This query returns the product name in its original form and in uppercase.

Date and Time Manipulation Functions

Dates and times are stored in special formats depending on the DBMS. SQL provides powerful functions to work with these values, such as extracting year, calculating differences, and formatting output.

However, date and time functions are the least standardized — each DBMS handles them differently.

Here's an example that retrieves all orders made in the year 2012 using SQL Server:

Input

SELECT order_num, order_date
                          FROM Orders
                          WHERE YEAR(order_date) = 2012;

This query extracts the year from each order_date and filters those equal to 2012.

Summary

In this lesson, you learned how to use SQL functions to manipulate data:

• Text functions like UPPER() and RTRIM()
• Date and time functions like YEAR()
• SQL functions are powerful but can vary across DBMSs — always test on your specific platform

Lesson 9. Summarizing Data

It is often necessary to summarize data without actually retrieving it all, and SQL provides special functions for this purpose. Using these functions, SQL queries are often used to retrieve data for analysis and reporting purposes.

Examples of this type of retrieval are:

• Determining the number of rows in a table (or the number of rows that meet some condition or contain a specific value).
• Obtaining the sum of a set of rows in a table.
• Finding the highest, lowest, and average values in a table column (either for all rows or for specific rows).

In each of these examples, you want a summary of the data in a table, not the actual data itself. Therefore, returning the actual table data would be a waste of time and processing resources (not to mention bandwidth). To repeat, all you really want is the summary information.

To facilitate this type of retrieval, SQL features a set of five aggregate functions:

The AVG() Function

AVG() is used to return the average value of a specific column by counting both the number of rows in the table and the sum of their values. AVG() can be used to return the average value of all columns or of specific columns or rows.

Individual Columns Only

AVG() may only be used to determine the average of a specific numeric column, and that column name must be specified as the function parameter. To obtain the average value of multiple columns, multiple AVG() functions must be used.

The COUNT() Function

COUNT() does just that: It counts. Using COUNT(), you can determine the number of rows in a table or the number of rows that match a specific criterion.

COUNT() can be used two ways:

• Use COUNT(*) to count the number of rows in a table, whether columns contain values or NULL values.
• Use COUNT(column) to count the number of rows that have values in a specific column, ignoring NULL values.

NOTE: NULL Values

Column rows with NULL values in them are ignored by the COUNT() function if a column name is specified, but not if the asterisk (*) is used.

The MAX() Function

MAX() returns the highest value in a specified column. MAX() requires that the column name be specified.

TIP: Using MAX() with Non-Numeric Data

Although MAX() is usually used to find the highest numeric or date values, many (but not all) DBMSs allow it to be used to return the highest value in any columns including textual columns. When used with textual data, MAX() returns the row that would be the last if the data were sorted by that column.

Note: NULL Values

Column rows with NULL values in them are ignored by the MAX() function.

The MIN() Function

MIN() does the exact opposite of MAX(); it returns the lowest value in a specified column. Like MAX(), MIN() requires that the column name be specified.

The SUM() Function

SUM() is used to return the sum (total) of the values in a specific column.

Here is an example to demonstrate this. The OrderItems table contains the actual items in an order, and each item has an associated quantity. The total number of items ordered (the sum of all the quantity values) can be retrieved:

Input

SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num = 20005;

Aggregates on Distinct Values

The five aggregate functions can all be used in two ways:

• To perform calculations on all rows, specify the ALL argument or specify no argument at all (because ALL is the default behavior).
• To only include unique values, specify the DISTINCT argument.

Caution: No DISTINCT With COUNT(*)

DISTINCT may only be used with COUNT() if a column name is specified. DISTINCT may not be used with COUNT(*). Similarly, DISTINCT must be used with a column name and not with a calculation or expression.

Tip: Using DISTINCT with MIN() and MAX()

Although DISTINCT can technically be used with MIN() and MAX(), there is actually no value in doing so. The minimum and maximum values in a column will be the same whether or not only distinct values are included.

Combining Aggregate Functions

All the examples of aggregate function used thus far have involved a single function. But actually, SELECT statements may contain as few or as many aggregate functions as needed. Look at this example:

Input

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM Products;

Here a single SELECT statement performs four aggregate calculations in one step and returns four values (the number of items in the Products table, and the highest, lowest, and average product prices).

Caution: Naming Aliases

When specifying alias names to contain the results of an aggregate function, try to not use the name of an actual column in the table. Although there is nothing actually illegal about doing so, many SQL implementations do not support this and will generate obscure error messages if you do so.

Summary

Aggregate functions are used to summarize data. SQL supports five aggregate functions, all of which can be used in multiple ways to return just the results you need. These functions are designed to be highly efficient, and they usually return results far more quickly than you could calculate them yourself within your own client application.

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
  

vend_id   | num_prods
---------------------
BRS01     | 3
DLL01     | 4
FNG01     | 2
  

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
  

vend_id   | num_prods
---------------------
BRS01     | 3
FNG01     | 2
  

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*) >= 3
ORDER BY items, order_num;
  

order_num | items
-----------------
20006     | 3
20009     | 3
20007     | 5
20008     | 5
  

   customer_id |   cust_name    |    city    | grade | salesman_id 
  -------------+----------------+------------+-------+-------------
          3002 | Nick Rimando   | New York   |   100 |        5001
          3007 | Brad Davis     | New York   |   200 |        5001
          3005 | Graham Zusi    | California |   200 |        5002
          3008 | Julian Green   | London     |   300 |        5002
          3004 | Fabian Johnson | Paris      |   300 |        5006
          3009 | Geoff Cameron  | Berlin     |   100 |        5003
          3003 | Jozy Altidor   | Moscow     |   200 |        5007
          3001 | Brad Guzan     | London     |       |        5005
  
  

   salesman_id |    name    |   city   | commission 
  -------------+------------+----------+------------
          5001 | James Hoog | New York |       0.15
          5002 | Nail Knite | Paris    |       0.13
          5005 | Pit Alex   | London   |       0.11
          5006 | Mc Lyon    | Paris    |       0.14
          5007 | Paul Adam  | Rome     |       0.13
          5003 | Lauson Hen | San Jose |       0.12
  
  

  ord_no      purch_amt   ord_date    customer_id  salesman_id
  ----------  ----------  ----------  -----------  -----------
  70001       150.5       2012-10-05  3005         5002
  70009       270.65      2012-09-10  3001         5005
  70002       65.26       2012-10-05  3002         5001
  70004       110.5       2012-08-17  3009         5003
  70007       948.5       2012-09-10  3005         5002
  70005       2400.6      2012-07-27  3007         5001
  70008       5760        2012-09-10  3002         5001
  70010       1983.43     2012-10-10  3004         5006
  
  

  
  -- Selecting specific columns and renaming one column for clarity
  SELECT a.cust_name, a.city, a.grade, 
         b.name AS "Salesman", 
         c.ord_no, c.ord_date, c.purch_amt 
  -- Specifying the tables to retrieve data from ('customer' as 'a', 'salesman' as 'b', and 'orders' as 'c')
  FROM customer a 
  -- Performing a right outer join based on the salesman_id, including unmatched rows from 'salesman'
  RIGHT OUTER JOIN salesman b 
  ON b.salesman_id = a.salesman_id 
  -- Performing another right outer join with the result of the previous join and the 'orders' table based on customer_id
  RIGHT OUTER JOIN orders c 
  ON c.customer_id = a.customer_id;
  
  

   customer_id |   cust_name    |    city    | grade | salesman_id 
  -------------+----------------+------------+-------+-------------
          3002 | Nick Rimando   | New York   |   100 |        5001
          3007 | Brad Davis     | New York   |   200 |        5001
          3005 | Graham Zusi    | California |   200 |        5002
          3008 | Julian Green   | London     |   300 |        5002
          3004 | Fabian Johnson | Paris      |   300 |        5006
          3009 | Geoff Cameron  | Berlin     |   100 |        5003
          3003 | Jozy Altidor   | Moscow     |   200 |        5007
          3001 | Brad Guzan     | London     |       |        5005
  

   salesman_id |    name    |   city   | commission 
  -------------+------------+----------+------------
          5001 | James Hoog | New York |       0.15
          5002 | Nail Knite | Paris    |       0.13
          5005 | Pit Alex   | London   |       0.11
          5006 | Mc Lyon    | Paris    |       0.14
          5007 | Paul Adam  | Rome     |       0.13
          5003 | Lauson Hen | San Jose |       0.12
  

  -- Selecting specific columns and renaming them for clarity
  SELECT a.cust_name, a.city, a.grade, 
         b.name AS "Salesman", b.city 
  -- Specifying the tables to retrieve data from ('customer' as 'a' and 'salesman' as 'b')
  FROM customer a 
  -- Performing a right outer join based on the salesman_id, including unmatched rows from 'salesman'
  RIGHT OUTER JOIN salesman b 
  ON b.salesman_id = a.salesman_id 
  -- Sorting the result set by salesman_id in ascending order
  ORDER BY b.salesman_id; 
  

  cust_name	city		grade	Salesman	city
  Brad Davis	New York	200	James Hoog	New York
  Nick Rimando	New York	100	James Hoog	New York
  Graham Zusi	California	200	Nail Knite	Paris
  Julian Green	London		300	Nail Knite	Paris
  Geoff Cameron	Berlin		100	Lauson Hen	San Jose
  Brad Guzan	London			Pit Alex	London
  Fabian Johnson	Paris		300	Mc Lyon		Paris
  Jozy Altidor	Moscow		200	Paul Adam	Rome
  

   customer_id |   cust_name    |    city    | grade | salesman_id 
  -------------+----------------+------------+-------+-------------
          3002 | Nick Rimando   | New York   |   100 |        5001
          3007 | Brad Davis     | New York   |   200 |        5001
          3005 | Graham Zusi    | California |   200 |        5002
          3008 | Julian Green   | London     |   300 |        5002
          3004 | Fabian Johnson | Paris      |   300 |        5006
          3009 | Geoff Cameron  | Berlin     |   100 |        5003
          3003 | Jozy Altidor   | Moscow     |   200 |        5007
          3001 | Brad Guzan     | London     |       |        5005
  

  ord_no      purch_amt   ord_date    customer_id  salesman_id
  ----------  ----------  ----------  -----------  -----------
  70001       150.5       2012-10-05  3005         5002
  70009       270.65      2012-09-10  3001         5005
  70002       65.26       2012-10-05  3002         5001
  70004       110.5       2012-08-17  3009         5003
  70007       948.5       2012-09-10  3005         5002
  70005       2400.6      2012-07-27  3007         5001
  70008       5760        2012-09-10  3002         5001
  70010       1983.43     2012-10-10  3004         5006
  70003       2480.4      2012-10-10  3009         5003
  70012       250.45      2012-06-27  3008         5002
  70011       75.29       2012-08-17  3003         5007
  70013       3045.6      2012-04-25  3002         5001
  

   salesman_id |    name    |   city   | commission 
  -------------+------------+----------+------------
          5001 | James Hoog | New York |       0.15
          5002 | Nail Knite | Paris    |       0.13
          5005 | Pit Alex   | London   |       0.11
          5006 | Mc Lyon    | Paris    |       0.14
          5007 | Paul Adam  | Rome     |       0.13
          5003 | Lauson Hen | San Jose |       0.12
  

  -- Selecting specific columns and renaming one column for clarity
  SELECT a.cust_name, a.city, b.ord_no,
         b.ord_date, b.purch_amt AS "Order Amount", 
         c.name, c.commission 
  -- Specifying the tables to retrieve data from ('customer' as 'a', 'orders' as 'b', and 'salesman' as 'c')
  FROM customer a 
  -- Performing a left outer join based on the customer_id, including unmatched rows from 'customer'
  LEFT OUTER JOIN orders b 
  ON a.customer_id = b.customer_id 
  -- Performing another left outer join with the result of the previous join and the 'salesman' table based on salesman_id
  LEFT OUTER JOIN salesman c 
  ON c.salesman_id = b.salesman_id;
  

  cust_name	city		ord_no	ord_date	Order Amount	name		commission
  Brad Guzan	London		70009	2012-09-10	270.65		Pit Alex	0.11
  Nick Rimando	New York	70002	2012-10-05	65.26		James Hoog	0.15
  Geoff Cameron	Berlin		70004	2012-08-17	110.50		Lauson Hen	0.12
  Brad Davis	New York	70005	2012-07-27	2400.60		James Hoog	0.15
  Nick Rimando	New York	70008	2012-09-10	5760.00		James Hoog	0.15
  Fabian Johnson	Paris		70010	2012-10-10	1983.43		Mc Lyon		0.14
  Geoff Cameron	Berlin		70003	2012-10-10	2480.40		Lauson Hen	0.12
  Jozy Altidor	Moscow		70011	2012-08-17	75.29		Paul Adam	0.13
  Nick Rimando	New York	70013	2012-04-25	3045.60		James Hoog	0.15
  Graham Zusi	California	70001	2012-10-05	150.50		Nail Knite	0.13
  Graham Zusi	California	70007	2012-09-10	948.50		Nail Knite	0.13
  Julian Green	London		70012	2012-06-27	250.45		Nail Knite	0.13
  

  DPT_CODE DPT_NAME        DPT_ALLOTMENT
  -------- --------------- -------------
        57 IT                      65000
        63 Finance                 15000
        47 HR                     240000
        27 RD                      55000
        89 QC                      75000
  

   EMP_IDNO EMP_FNAME       EMP_LNAME         EMP_DEPT
  --------- --------------- --------------- ----------
     127323 Michale         Robbin                  57
     526689 Carlos          Snares                  63
     843795 Enric           Dosio                   57
     328717 Jhon            Snares                  63
     444527 Joseph          Dosni                   47
     659831 Zanifer         Emily                   47
     847674 Kuleswar        Sitaraman               57
     748681 Henrey          Gabriel                 47
     555935 Alex            Manuel                  57
     539569 George          Mardy                   27
     733843 Mario           Saule                   63
     631548 Alan            Snappy                  27
     839139 Maria           Foster                  57
  

  -- Selecting specific columns and renaming them for clarity
  SELECT emp_details.emp_fname AS "First Name", emp_lname AS "Last Name", 
         emp_department.dpt_name AS "Department", 
         dpt_allotment AS "Amount Allotted"
  -- Specifying the tables to retrieve data from ('emp_details' and 'emp_department')
  FROM emp_details 
  -- Performing an inner join based on the equality of 'emp_dept' in 'emp_details' and 'dpt_code' in 'emp_department'
  INNER JOIN emp_department
  ON emp_details.emp_dept = emp_department.dpt_code;
  

  First Name	Last Name	Department	Amount Allotted
  Alan		Snappy		RD		55000
  Maria		Foster		IT		65000
  Michale		Robbin		IT		65000
  Carlos		Snares		Finance		15000
  Enric		Dosio		IT		65000
  Jhon		Snares		Finance		15000
  Joseph		Dosni		HR		240000
  Zanifer		Emily		HR		240000
  Kuleswar	Sitaraman	IT		65000
  Henrey		Gabriel		HR		240000
  Alex		Manuel		IT		65000
  George		Mardy		RD		55000
  Mario		Saule		Finance		15000
  

COMBINING QUERIES

Most SQL queries contain a single SELECT statement that returns data from one or more tables. SQL also enables you to perform multiple queries (multiple SELECT statements) and return the results as a single query result set. These combined queries are usually known as unions or compound queries.

SQL queries are combined using the UNION operator. Using UNION, multiple SELECT statements can be specified, and their results can be combined into a single result set.

UNION Rules

As you can see, unions are very easy to use. But there are a few rules governing exactly which can be combined:

• A UNION must be composed of two or more SELECT statements, each separated by the keyword UNION (so, if combining four SELECT statements there would be three UNION keywords used).
• Each query in a UNION must contain the same columns, expressions, or aggregate functions (and some DBMSs even require that columns be listed in the same order).
• Column datatypes must be compatible: They need not be the exact same type, but they must be of a type that the DBMS can implicitly convert (for example, different numeric types or different date types).

Including or Eliminating Duplicate Rows

The UNION automatically removes any duplicate rows from the query result. This is the default behavior of UNION, but you can change this if you so desire. If you would, in fact, want all occurrences of all matches returned, you can use UNION ALL instead of UNION.

Sorting Combined Query Results

SELECT statement output is sorted using the ORDER BY clause. When combining queries with a UNION only one ORDER BY clause may be used, and it must occur after the final SELECT statement. There is very little point in sorting part of a result set one way and part another way, and so multiple ORDER BY clauses are not allowed.

Summary

In this lesson, you learned how to combine SELECT statements with the UNION operator. Using UNION, you can return the results of multiple queries as one combined query, either including or excluding duplicates. The use of UNION can greatly simplify complex WHERE clauses and retrieving data from multiple tables.

INSERTING DATA

Understanding Data Insertion

SELECT is undoubtedly the most frequently used SQL statement (which is why the last 14 lessons were dedicated to it). But there are three other frequently used SQL statements that you should learn. The first one is INSERT. (You’ll get to the other two in the next lesson.)

As its name suggests, INSERT is used to insert (add) rows to a database table. INSERT can be used in several ways:

• Inserting a single complete row
• Inserting a single partial row
• Inserting the results of a query

Inserting Complete Rows

The simplest way to insert data into a table is to use the basic INSERT syntax, which requires that you specify the table name and the values to be inserted into the new row. Here is an example of this:

Input

INSERT INTO Customers
VALUES('1000000006',
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);
  

Analysis

This example inserts a new customer into the Customers table. The data to be stored in each table column is specified in the VALUES clause, and a value must be provided for every column. If a column has no value, use NULL (assuming the table allows it). The columns must be populated in the order they appear in the table definition.

Although simple, this syntax is not safe because it's highly dependent on the column order. Writing SQL that assumes a specific column order is dangerous and should be avoided.

The safer way is:

Input

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
VALUES('1000000006',
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);
  

Analysis

This example does the same as the previous one, but explicitly lists the column names. The DBMS matches the values to the columns based on position, not name.

Inserting Partial Rows

When using INSERT, it's recommended to specify column names explicitly. This allows you to omit columns you don't want to provide values for.

Inserting Retrieved Data

INSERT is often used with fixed values, but you can also insert data retrieved from another table using INSERT SELECT. For example:

Input

INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM CustNew;
  

Tip: Column Names in INSERT SELECT

The column names in INSERT and SELECT don't need to match. The DBMS matches values by position, not name.

Analysis

This inserts all rows from CustNew into Customers. If CustNew has rows, they are inserted; if it’s empty, nothing happens.

Tip: Inserting Multiple Rows

INSERT normally adds one row at a time, unless used with INSERT SELECT, which can insert multiple rows in one go.

Copying from One Table to Another

Another method of data insertion uses SELECT INTO to copy data into a new table:

Input

SELECT *
INTO CustCopy
FROM Customers;
  

Analysis

This creates a new table CustCopy and copies all data from Customers. To copy only specific columns, list them instead of using *.

In MariaDB, MySQL, Oracle, PostgreSQL, and SQLite:

Input

CREATE TABLE CustCopy AS
SELECT * FROM Customers;
  

Notes on SELECT INTO:

• All SELECT options like WHERE, GROUP BY can be used.
• You can join multiple tables.
• Only one table can be created with SELECT INTO.

Summary

You learned how to INSERT rows into a table, the benefits of specifying columns explicitly, and how to use INSERT SELECT and SELECT INTO to copy data. The next lesson covers UPDATE and DELETE for modifying data.

Lesson 16: Updating and Deleting Data

UPDATE Statement

The UPDATE statement is used to modify existing data in a table.

Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

Updating Multiple Columns

UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

Setting a Column to NULL

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

Important: Always use a WHERE clause to avoid updating all rows unintentionally.

DELETE Statement

The DELETE statement is used to remove rows from a table.

Syntax

DELETE FROM table_name
WHERE condition;

Example

DELETE FROM Customers
WHERE cust_id = '1000000006';

Be careful: Omitting the WHERE clause deletes all rows from the table.

TRUNCATE TABLE

• Deletes all rows in a table.
• Faster than DELETE because it doesn't log individual row deletions.
• Does not delete the table itself.

TRUNCATE TABLE Customers;

Guidelines and Best Practices

• Never execute UPDATE or DELETE without a WHERE clause unless you're sure.
• Ensure every table has a primary key and use it in your WHERE clause.
• Test your WHERE clause with SELECT first to confirm correct filtering.
• Use referential integrity to prevent deleting rows that are referenced in other tables.
• Consider DBMS-level restrictions to prevent unsafe UPDATE or DELETE commands.

Lesson 17: Creating and Manipulating Tables

Creating Tables

There are generally two ways to create database tables:

• Using a database administration tool provided by most DBMSs.
• Using SQL statements directly, such as CREATE TABLE.

Basic CREATE TABLE Syntax

You must specify:

• Table name after CREATE TABLE.
• Column names and definitions separated by commas.
• Some DBMSs may require the table location.

Example

CREATE TABLE Products (
  prod_id     CHAR(10),
  vend_id     CHAR(10),
  prod_name   CHAR(50),
  prod_price  DECIMAL(8,2),
  prod_desc   TEXT
);

Specifying Default Values

Use the DEFAULT keyword in a column definition to set a default value when none is provided during INSERT.

Example

CREATE TABLE OrderItems (
  order_num    INTEGER       NOT NULL,
  order_item   INTEGER       NOT NULL,
  prod_id      CHAR(10)      NOT NULL,
  quantity     INTEGER       NOT NULL DEFAULT 1,
  item_price   DECIMAL(8,2)  NOT NULL
);

Updating Tables

Use ALTER TABLE to update a table definition. Support for specific alterations varies by DBMS.

Considerations

• Ideally, avoid altering tables that already contain data.
• Most DBMSs support adding columns, but may restrict the datatypes or default values.
• Many do not allow removing or changing columns.
• Renaming columns is supported by most DBMSs.
• Fewer restrictions apply to unpopulated columns.

Example: Adding a Column

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

Deleting Tables

To remove an entire table, use DROP TABLE.

Example

DROP TABLE CustCopy;

Summary

This lesson covered how to:

• Use CREATE TABLE to define new tables.
• Use ALTER TABLE to modify table definitions.
• Use DROP TABLE to delete tables.

Always consult your specific DBMS documentation, as syntax and supported features may vary.

Understanding Views

Views are virtual tables that dynamically retrieve data using stored queries.

Why Use Views

• Reuse SQL statements
• Simplify complex queries
• Restrict access to table subsets
• Secure data
• Change data formatting

View Rules and Restrictions

• Must have unique names
• Unlimited number can be created
• Require appropriate security access
• Can be nested (check DBMS limits)
• ORDER BY often not allowed in views
• Aliases may be required for calculated fields
• Cannot have indexes, triggers, or defaults
• Often read-only (depends on DBMS)

Creating Views

Example: Simplify Complex Joins

CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;

SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

Example: Reformat Retrieved Data

CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) || ' (' || RTRIM(vend_country) || ')'
AS vend_title
FROM Vendors;

SELECT * FROM VendorLocations;

DELIMITER $$

CREATE PROCEDURE GetCustomerEmailList()
BEGIN
  SELECT cust_name, cust_email
  FROM Customers
  WHERE cust_email IS NOT NULL;
END$$

DELIMITER ;

CALL GetCustomerEmailList();

CREATE PROCEDURE GetOrdersByCustomer(IN cust_id_param CHAR(10))
BEGIN
  SELECT order_num, order_date
  FROM Orders
  WHERE cust_id = cust_id_param;
END;

CALL GetOrdersByCustomer('1001');

DELIMITER $$

CREATE PROCEDURE ListProductNames()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE prod_name_val VARCHAR(100);
  DECLARE prod_cursor CURSOR FOR SELECT prod_name FROM Products;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN prod_cursor;

  read_loop: LOOP
    FETCH prod_cursor INTO prod_name_val;
    IF done THEN
      LEAVE read_loop;
    END IF;
    SELECT prod_name_val;
  END LOOP;

  CLOSE prod_cursor;
END$$

DELIMITER ;