1. MySQL: SELECT statement & WHERE clause

Before we start, I would like you to install MySQL workbench as we will be running queries, and it will be a quick and easy hands-on for you.

It is okay if you want a quick look at the queries and the examples, but with hands-on experience, you will remember it for a longer time, and you won't be baffled in the interview if your query is not working due to a simple ';'(semi-colon).

If you want to know how to install MySQL workbench, you can use the link mentioned below, and you will be good to go! Thanks to ProgrammingKnowledge for this video!

I would suggest you watch this on 2x speed and MySQL workbench!

https://www.youtube.com/watch?v=WuBcTJnIuzo

In this blog series, we will use the Sakila database, which is present in the MySQL workbench. It has the following tables in it.

Now that everything is ready, we will learn and use the SELECT statement and WHERE clause.

MySQL SELECT:

SELECT is the most important and commonly used statement in SQL, and it is used to retrieve and filter data from a database. The SELECT statement can be used to access single or multiple columns from one or more tables and can also be used to calculate aggregate values such as sum, average, or the count.

• The syntax is : SELECT * FROM table_name;

• Suppose we want to look at all the columns in the actor table; we will use the SELECT statement with a *.

SELECT * FROM actor;

Output:

• If we already know the columns' names, we can use them in our SELECT statement; this will save us time. We won't be able to find the time difference in these databases, but in the production environment, we need to be careful with our queries.

• The syntax now becomes: SELECT column_name_1, column_name_2 from table_name

    SELECT actor_id,first_name, last_name
    FROM actor;
  

  Output:

  

  MySQL WHERE CLAUSE:

  The MySQL WHERE clause is an essential part of the SQL language that filters and restricts the data returned from a query. Using the WHERE clause, you can specify the conditions that must be met before the data is returned. This clause can be used with SELECT, DELETE, and UPDATE statements.

• The syntax is as follows:

  SELECT column_name_1, column_name_2

  FROM table_name

  WHERE condition ;

• Let's have a look at the payment table.

SELECT * FROM payment;

• Output:

  

• Now, we want some customer details where the amount equals 0.99. So we now have a condition that we will introduce to our statement.

    SELECT customer_id, payment_id, rental_id, amount FROM payment 
    Where amount = '0.99' ;
  

  Output:

  

• We can use the WHERE clause with other operators, such as BETWEEN, IN, etc. Here is an example of how it looks, as we will be diving into these operators in future blogs in this series.

SELECT customer_id, payment_id, rental_id, amount FROM payment
WHERE amount BETWEEN 2.99 AND 5.99;

• Output:

  

We'll meet again tomorrow!