Engineer bro!
Thu Jun 23 2022 (1 year ago)
Engineer by mistake!
I am a software engineer by passion
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
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.
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);
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.
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.
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.
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.
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.
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.
© 2021 dsabyte. All rights reserved