When it comes to efficiently handling and managing a large amount of data, using databases can have the same organizational advantage as if you had your own personal office with well-appointed cabinets and folders. Every database management system, for instance, SQL, which stands for Structured Query Language, helps data explorers maneuver through the maze and perform some incredible actions, such as discovering specific items from massive data tables. In this session, we are going to implement a very fantastic experience, by taking for example, filtering out a products table such that only the items with a price above ₹100 will be displayed. By following along with this tutorial, you will gain a clearer understanding of the SQL WHERE statement as well as develop some very important hands-on experience that will involve creating tables, inputting data, and applying filters. The motivation of doing this is beyond learning the theory behind, but rather, giving you the skills of working with databases in an actual work situation in order to help strengthen and develop your employability in an ever-changing conjuncture of our societies.
Step 1: Understanding the Products Table
To make this example work, let’s first imagine our products table. If you’re just starting with SQL, think of a table as a simple spreadsheet. Each row holds a record, and each column represents a type of information, like a price or product name.
Table: products
Here’s a basic table layout we’ll work with:
Column Name | Data Type | Description |
---|---|---|
product_id | INT | Unique ID for each product |
product_name | VARCHAR(255) | Name of the product |
category | VARCHAR(100) | Category (e.g., Electronics, Stationery) |
price | DECIMAL(10, 2) | Product price |
stock | INT | Quantity available |
The product_id is a unique number assigned to each product. It helps us pinpoint one specific item, like an ID number for each row.
Step 2: Creating the Products Table in SQL
To get started, you’ll need to set up this table in your database. Here’s how to create the products table in SQL:
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(255),
category VARCHAR(100),
price DECIMAL(10, 2),
stock INT
This code snippet lays out the structure for our table. Each column has its data type—for instance, VARCHAR(255)
holds text for our product names, while DECIMAL(10, 2)
is for precise prices with up to two decimal places.
Step 3: Adding Some Dummy Data
With the table set up, let’s populate it with some dummy data to make testing easier:
INSERT INTO products (product_id, product_name, category, price, stock) VALUES
(1, 'Laptop', 'Electronics', 45000.00, 10),
(2, 'Smartphone', 'Electronics', 15000.00, 20),
(3, 'Headphones', 'Accessories', 1500.00, 50),
(4, 'Chocolates', 'Food', 99.00, 100),
(5, 'Book', 'Stationery', 200.00, 15),
(6, 'Sunglasses', 'Accessories', 750.00, 30),
(7, 'Notebook', 'Stationery', 50.00, 200),
(8, 'Backpack', 'Accessories', 1200.00, 25);
Now, our products table has a mix of items across different categories and prices. This will help us test our query effectively.
Step 4: Writing the SQL Query to Filter by Price
Our goal is to fetch only those products that cost more than ₹100. We’ll use the WHERE clause to set this condition.
SQL Query to Select Products with Price Above ₹100
SELECT product_id, product_name, category, price
FROM products
WHERE price > 100;
Let’s break down this query:
- SELECT tells SQL which columns we want to retrieve—in this case,
product_id
,product_name
,category
, andprice
. - FROM products specifies that we’re working with the
products
table. - WHERE price > 100 filters the results, including only those rows where the price is greater than ₹100.
Expected Output
With the sample data we’ve added, this query will return a list like this:
product_id | product_name | category | price |
---|---|---|---|
1 | Laptop | Electronics | 45000.00 |
2 | Smartphone | Electronics | 15000.00 |
3 | Headphones | Accessories | 1500.00 |
5 | Book | Stationery | 200.00 |
8 | Backpack | Accessories | 1200.00 |
This output shows only products priced above ₹100, filtering out items like Chocolates and Notebook.
Step 5: Exploring Variations and Expanding Queries
SQL is powerful because of its flexibility. Here are some ideas to take your query to the next level:
Retrieve All Columns
If you’d like to pull all the columns instead of specifying them, you can use the *
wildcard:
SELECT * FROM products WHERE price > 100;
Adding Multiple Conditions
To refine results further, add another condition using AND or OR. For instance, here’s how to filter by both price and category:
SELECT * FROM products WHERE price > 100 AND category = 'Electronics';
This will show only electronics priced above ₹100.
Advanced Tips for Practicing SQL
Here are a few additional SQL tricks for fine-tuning your queries:
Use BETWEEN for Ranges
If you want to find products within a specific price range, say between ₹500 and ₹5000, use the BETWEEN operator:
SELECT * FROM products WHERE price BETWEEN 500 AND 5000;
Sorting Results
Want to see the most expensive items first? Use the ORDER BY clause to sort by price:
SELECT * FROM products WHERE price > 100 ORDER BY price DESC;
Limiting Results
You can also use LIMIT to show only the top few results:
SELECT * FROM products WHERE price > 100 ORDER BY price DESC LIMIT 3;
Final Words
And there you have it! Working with SQL queries, especially using the SQL WHERE clause, is incredibly useful for pulling out specific information from large datasets. Whether you’re building a report, running analysis, or managing an e-commerce database, mastering queries like these will make your work faster and more efficient.
If you’re new to SQL, practice is key. Try using different conditions, playing around with AND/OR combinations, and testing queries on various datasets. Soon enough, you’ll be navigating tables and filtering data with ease!