SQL Guide

For each task, replace or update the existing SQL code in the top pane (main.sql) with your answer to the task and run it to see the results in the bottom pane (SQL Viewer).

Step 1: Using UNION and UNION ALL

To review the concepts covered in this step, please refer to the course Subqueries and Sets module of the Introduction to SQL Course.

In this step, you will explore two essential SQL operators—UNION and UNION ALL. These operators allow you to combine the results of two or more SELECT queries into a single result set. However, the key difference between the two lies in how they handle duplicates.

When you use UNION, the database engine automatically removes any duplicate records from the combined result set, ensuring that only unique rows are returned. This is helpful when you're working with datasets where overlapping records are expected, and you only want to see unique values.

On the other hand, UNION ALL is less restrictive—it returns all rows from the combined queries, including any duplicates. This is particularly useful when you need to include all records, even if some are repeated.

Understanding these concepts is important in database management when you need to aggregate data from multiple sources. Whether you’re working with different tables or subsets of data from the same table, UNION and UNION ALL offer flexibility in how you present your results.

You’ll be working with the Customers, ArchivedCustomers, Employees, and Products tables to practice these operators. By the end of this step, you’ll be able to effectively combine query results using both UNION and UNION ALL, understanding when and why to use each.

Step 2: INSERT Operations

Now that you’ve combined results using UNION and UNION ALL, let’s dive into one of the most fundamental SQL operations: INSERT.

The INSERT statement is your gateway to adding new data to a database. This operation is part of what we call CRUD (Create, Read, Update, Delete), representing the "Create" aspect. As a database administrator or developer, you’ll often find yourself using INSERT to add new records—whether you're onboarding new employees, adding products to inventory, or logging customer orders.

The INSERT operation involves specifying the table into which you want to add data and providing values for each column (or only the required columns if others have default values). It’s essential to structure your INSERT queries carefully to ensure that the data you’re adding fits within the constraints and rules defined by the table’s schema (such as NOT NULL constraints or data types).

In this step, you’ll practice adding new employees, products, and orders to the CompanyDB database. You’ll work with multiple rows in some cases, using the efficient syntax to insert multiple records at once.

Step 3: UPDATE and DELETE Operations

So far, you’ve learned how to insert new data into tables, but what happens when data needs to be modified or removed? That’s where the UPDATE and DELETE statements come into play, and these are the focus of this step.

The UPDATE statement allows you to modify existing data in a table. For example, you might need to update an employee’s salary, adjust the stock of a product, or change a customer’s email address. When using UPDATE, it’s important to be precise in specifying which rows should be changed. This is typically done with a WHERE clause, ensuring you don’t unintentionally modify other rows.

The DELETE statement, on the other hand, allows you to remove data from a table. This operation is part of the CRUD cycle as well, representing the "Delete" aspect. Similar to UPDATE, the DELETE statement requires careful usage, especially when working in relational databases with foreign key relationships. It’s important to ensure that you are deleting only the intended rows, as this action is irreversible without a proper backup.