1. Writing queries
It's time to level up on our SQL queries by learning a few more commonly used keywords. Let's dive in.
2. Aliasing
Sometimes, it can be helpful to rename columns in our result set, whether for clarity or brevity. We can do this using aliasing.
For instance, let's consider an employees table that includes a field for each employee's name and their hire date.
Because the name field only contains each employee's first name, we can use the AS keyword to alias the name field as first_name.
This changes the field name to first_name in the result set, while the actual field name in the table remains as name.
3. Selecting distinct records
Some SQL questions require a way to return a list of unique values.
Let's imagine we want to create a list of years when we hired our employees. Selecting the year_hired field from the employees table shows some years multiple times, which is not what we want.
To get a list of years with no repeat values, we can add the DISTINCT keyword before the year_hired field name in the SELECT statement.
Now, we can see that all of our employees were hired in just four different years.
4. DISTINCT with multiple fields
It's possible to return the unique combinations of multiple field values by listing multiple fields after the DISTINCT keyword. Take a look at the employees table. Perhaps we'd like to know the years that different departments hired employees.
We could use this SQL query to look at this information, selecting the dept_id and year_hired from the employees table.
Looking at the results, we see that department three hired two employees in 2021.
5. DISTINCT with multiple fields
To avoid repeating this information, we could add the DISTINCT keyword before the fields to select. Notice that the department id and year_hired fields still have repeat values individually, but none of the records are the same: they are all unique combinations of the two fields.
6. Views
Finally, let's explore saving SQL result sets. A view is a saved SQL query that acts like a virtual table. Views don't store data; they store the query, ensuring the results are always up-to-date with the latest database changes.
To create a view, use CREATE VIEW, followed by the view name, and AS to define the query. For example, employee_hire_years can be created by saving a query that selects specific fields from the employees table. Note that creating a view doesn't produce a result set, it only saves the query for reuse.
7. Using views
Once a view is created, we can query it just as we would a normal table by selecting FROM the view.
8. Let's practice!
Time to practice refining and saving queries with these new keywords!