Engineer bro!

Thu Jun 23 2022 (1 year ago)

Engineer by mistake!

I am a software engineer by passion

Learning SELECT statement and WHERE clause | SQL

In this article we'll learn SELECT and WHERE of SQL. Select statement is used for selecting rows from a table inside the database, we can also select particular column and rows upon conditions. Where clause is used to specify conditions for selecting rows.

SELECT Syntax

SELECT statement has the following syntax

SELECT column1, column2
FROM table_name;

Here column1 and column2 are columns of table table_name. There is a special keyword in SQL which you can use to select all the columns of the table.

SELECT *
FROM table_name;

The above SQL statement will return all columns from the database.

Creating Data Set

Let's create a table named products which will have product name, product id and price and columns.

CREATE TABLE products(
    product_name VARCHAR(40), 
    pid VARCHAR(5),
    price INT
);

Once the table is created we can insert the data into it. Insert the following data into your table.

INSERT INTO products VALUES("Mobile",'p1',1000);
INSERT INTO products VALUES("MacOs",'p2',10060);
INSERT INTO products VALUES("Tablet",'p3',6000);
INSERT INTO products VALUES("Mouse",'p4',100);
INSERT INTO products VALUES("Slack Recharge",'p5',1900);
INSERT INTO products VALUES("WiFi Fiber",'p6',1100);

Using * for getting all data

Now, let have the problem statement as to get all products along with its name , id and price. We can write the following query.

SELECT * FROM products;

+----------------+------+-------+
| product_name   | pid  | price |
+----------------+------+-------+
| Mobile         | p1   |  1000 |
| MacOs          | p2   | 10060 |
| Tablet         | p3   |  6000 |
| Mouse          | p4   |   100 |
| Slack Recharge | p5   |  1900 |
| WiFi Fiber     | p6   |  1100 |
+----------------+------+-------+
6 rows in set (0.00 sec)

As the above query returned all the data present in the table.

Getting particular columns from table

Now, suppose you just want to display product name and price to the users in UI just like Amazon and Flipkart, so why will you fetch product id. It is viable to fetch only product name and price.

Getting particular columns from table

As you can see in the above image, website is not showing product id. Instead they are just showing product details and their price to the user.

SELECT product_name AS "Product Name", price AS "Price" 
FROM products;

+----------------+-------+
| Product Name   | Price |
+----------------+-------+
| Mobile         |  1000 |
| MacOs          | 10060 |
| Tablet         |  6000 |
| Mouse          |   100 |
| Slack Recharge |  1900 |
| WiFi Fiber     |  1100 |
+----------------+-------+
6 rows in set (0.00 sec)

To fetch only a particular set of columns, you can add them after SELECT statement.

Using WHERE

When you'll see the below image then you'll get that flipkart has added an option to filter the products with minimum and maximum price.

Using WHERE SQL

Now, let'w write down query for fetching all products whose price is greater than 100 and less than 10000.

SELECT product_name AS "Product Name", price AS "Price" 
FROM products 
WHERE price > 100 AND price < 10000;

+----------------+-------+
| Product Name   | Price |
+----------------+-------+
| Mobile         |  1000 |
| Tablet         |  6000 |
| Slack Recharge |  1900 |
| WiFi Fiber     |  1100 |
+----------------+-------+
4 rows in set (0.00 sec)

In the above query, we have just added WHERE price > 100 AND price < 10000. The WHERE clause will help you to fetch only those rows whose selection criteria meets the requirements.

Conclusion

In this article i have shown you how can you use SELECT statement and WHERE clause along with it. We have also practiced it on sample data set. At the end i would like to thank you for reading the article.

Data StructureMySqlDatabaseSoftware Engineering

© 2021 dsabyte. All rights reserved