Greetings, and welcome to our second unit in the "Getting Started with SQL for Online Shopping" course!

You have already journeyed through what databases are, explored the world of SQL, and understood the usage of MySQL. Remember how we wrote our first SHOW TABLES SQL command to list all the tables in our Online Shop database? That was your first step into SQL syntax!

SQL, unlike many programming languages, doesn't deal with logic or flow control; instead, it understands, manipulates, and retrieves data stored in databases in a structured manner.

In this lesson, we will cover the SELECT keyword. The syntax is straightforward.

General Syntax:

SQL
Copy to clipboard
1SELECT column1, column2, ..., columnN FROM table_name

In this syntax, you mention the column names that you want to retrieve, separated by commas. If you want to retrieve all columns, replace the column names with an asterisk (*).

Let's see how it works!

Let's pull all the data from the Products table. We'll use the asterisk (*) symbol to do this.

SQL
Copy to clipboard
1SELECT * FROM Products;

This statement fetches all columns, along with their data, from the table Products. You would see product_id, product_name, product_price, category_id, and more, all displaying data related to the products available in the Online Shop.

Note: When using SELECT *, the database fetches every column and every row from the specified table. While this is useful for exploring the table’s structure or inspecting all data at once, it can be inefficient, especially for large tables. For instance, if the Products table has thousands of rows and includes columns like product_id, product_name, product_price, and category_id, SELECT * retrieves everything — which might include data you don’t need.

Best Practice: Use SELECT * sparingly. For precise and efficient queries, always specify the required columns.

Now, what if we want only specific information, such as the product name and product price? It's simple. We replace the asterisk with the required column names. Here's how:

SQL
Copy to clipboard
1SELECT product_name, product_price FROM Products;

In this example, we have fetched only the product_name and product_price columns from our Products table. Isn't fine-tuning our query results exciting?

Can we rename column names in the output for better understanding? Yes, we can! Thanks to SQL aliasing features. The AS keyword is used to rename a column or table with an alias.

SQL
Copy to clipboard
1SELECT product_name AS "Product Name", product_price AS Price FROM Products;

In this statement, AS is used to rename product_name to "Product Name" and product_price to Price in our output. Notice that we use double quotes around "Product Name" because the new name contains spaces. This way, our result set has more comprehensible column names.

Rules for Using Aliases:

• Use double quotes (") around the alias if it contains spaces or special characters.
• Aliases exist only for the duration of the query and do not alter the database schema.

The output of this query would be:

Let's recap what we've covered:

• SELECT * FROM Products will fetch all columns.
• SELECT product_name, product_price FROM Products will fetch specific columns.
• SELECT product_name AS "Product Name", product_price AS Price FROM Products will fetch specific columns and rename them in the output.

Practice these commands with different columns and aliases to gain a firm understanding of the SELECT statement. Don't worry if you make mistakes — they are stepping stones to mastering any new skill. Let's get to practice.

That wraps up our second unit! You now understand the SQL syntax, the SELECT command, and the usage of the AS keyword to rename items in the output of your query.

The practice exercises that follow this lesson will further reinforce these concepts. Remember, the best way to learn is by doing. So, try writing and running your own SQL commands before you move on to the practice exercises.

Great job so far, and keep practicing!