Querying a Single Table

In this chapter, you will learn how to query a single table. This includes learning how to use the SQL SELECT statement and the FROM, WHERE, and ORDER BY clauses. You will also learn how to tell which index your query is using and if you may need additional indexes. By the end of this chapter, you will be able to understand how to query data using the SELECT statement and the FROM clause. You will also learn how to limit the results with a WHERE clause, how to use an ORDER BY clause to return results in a specified order, and how to see information about what indexes are being used or may be needed.

In this chapter, we will cover the following topics:

• Using the SELECT statement and FROM clause
• Using the WHERE clause
• Using the ORDER BY clause
• Using indexes with your queries

Let's get started!

Technical requirements

The code files for this chapter can be found at the following GitHub link: https://github.com/PacktPublishing/learn-sql-database-programming/tree/master/chapter-6.

Using the SELECT statement and FROM clause

To extract data from a table, you need to use a SQL SELECT query. The SELECT query allows you to specify what data you want from a table using a simple query structure.

Understanding the SELECT statement and the FROM clause

At a minimum, every SELECT statement on a table needs the SELECT and FROM keywords. If you want to select all the rows and columns from the appearances table in lahmansbaseballdb, you should execute the following query:

SELECT * FROM lahmansbaseballdb.appearances;

Make sure to add a semicolon (;) to the end of your SQL statements. The semicolon marks the end of a query. The query may execute without it, but it is good practice to ensure that the SQL code will...

Using the WHERE clause

The WHERE clause helps limit the results of your queries. For example, if you only wanted to see players with more than 40 appearances in games, you can create a WHERE clause to include only those players that meet the criteria.

Understanding how and when to use the WHERE clause to limit query results

The WHERE clause is placed after the FROM clause in a SELECT query. Using the example of players with more than 40 appearances in games, you can execute the following query:

USE lahmansbaseballdb;
SELECT playerid, g_all, g_batting, g_defense FROM appearances
WHERE g_all > 40;

The criterion we are setting in the WHERE clause (for example, g_all > 40) is called an expression. There are different expression...

Using the ORDER BY clause

The ORDER BY clause helps you sort your results. You can sort your results in a few different ways, all of which will be covered in the following sections.

Learning how to use the ORDER BY clause to order query results

The ORDER BY clause is placed after FROM, as well as after WHERE, if you have a WHERE clause. You can order columns by ascending or descending order. Ascending is the default sort order, so you don't need to specify ascending explicitly.

Do not depend on the order of the rows in a result set, unless you have specified an ORDER BY clause. The order in which rows are returned may or may not be the same without an ORDER BY explicitly defined in your query.

To sort the columns...

Using indexes with your queries

To make sure your queries have been optimized and can run as quickly as they can, you want to ensure they are using the indexes on the table you are querying properly. As you learned in Chapter 4, Designing and Creating a Database, locking, blocking, and deadlocking play a role in your queries. If you use indexes properly with your queries, you will have less blocking and deadlocking to deal with because the index will allow you to query data faster.

Learning how to see what indexes your query is using

There is a simple way to see how MySQL will run your query, and that is to append EXPLAIN to the front of your query. For example, you can see EXPLAIN being used in the following query:

USE...

Summary

In this chapter, you learned how to query a single table. This included learning how to use the SQL SELECT statement and the FROM, WHERE, and ORDER BY clauses.

With the SELECT and FROM clauses, you learned how to select distinct records with the DISTINCT keyword, how to limit results with the LIMIT keyword, how to save a SQL query to a file, and how to add proper comments to SQL code.

With the WHERE clause, you learned how and when to use the WHERE clause, along with AND, OR, NOT, IN, and BETWEEN, and how to use with the percent sign (%) and underscore (_) wildcards.

With the ORDER BY clause, you learned how to order query results by one or more columns.

Finally, you learned how to tell which index your query is using and if you need additional indexes by using EXPLAIN or the visual explain tool in MySQL Workbench. You also learned how to add an index to make the query...

Questions

1. What character is required at the end of all SQL statements?
2. What two clauses are required to select data from a single table?
3. Why should you avoid using SELECT * in a query?
4. What does the WHERE clause do to your query results?
5. What two wildcard operators can you use with LIKE?
6. What does an ORDER BY clause do to your query results?
7. What options do you have for sorting results in your ORDER BY clause?
8. What SQL clause can you use to see the explanation of your query, and what indexes will it use?
9. How do you add an index to an existing table?
10. How do you see the explanation of a query plan using MySQL Workbench?

Further reading

For more information:

• Refer to this link for more information on Oracle execution plans: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/generating-and-displaying-execution-plans.html#GUID-60E30B1C-342B-4D71-B154-C26623D6A3B1
• Refer to this link for more information on PostgreSQL execution plans: https://www.postgresql.org/docs/11/sql-explain.html
• Refer to this link for more details about SQL Server execution plans: https://docs.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver15
• Refer to this link for more details about MySQL partitioning: https://dev.mysql.com/doc/refman/8.0/en/partitioning.html