Module 1: Basic SQL Operations: Select & Filters

Objective:

🔍 Learn how to retrieve data from a database and filter it according to specific conditions.

Let’s first learn what is a SQL Query -

A SQL query is a structured command used to retrieve, modify, or manage data in a database.

📖 Continuing from our previous example of tables, below is the most basic SQL query -

SELECT: This is like highlighting a particular column or multiple columns in our spreadsheet. In this case we are retrieving all the data from the column ‘title’. We will only see the values from ‘title’ column (aka field) -

WHERE: Helps us filter out data. It's like using the filter option on our spreadsheet to only show movies released after 2000.

Alright! Now that you've got a hang of how SELECT and WHERE play out, let's crank things up a notch. Ready?

Introduction: 💼 Imagine you're a data analyst at a popular electronics store. You need to pull out relevant data to answer business questions and make informed decisions. Using SQL, you can fetch and filter the data you need!

1️⃣ Exploring the Database

• Task: 📜 Use the SELECT statement to view all columns of the products table.

  Sample Question: "📋 List all the products and their attributes available in our store."

  Solution: SELECT * FROM products;

• Sample Output:

  product_id	product_name	category	brand	price	stock_quantity	discount
  1	iPhone 13	Smartphone	Apple	599	30	5
  2	Galaxy S21	Smartphone	Samsung	420	50	10
  3	Pixel 5a	Smartphone	Google	160	25	0
  4	MacBook Pro 13	Laptop	Apple	1299	15	10
  5	XPS 13	Laptop	Dell	899	20	5

• <aside> 💡 Note: We will keep showing you sample results enough to help you visualize the final output for the rest of the course.

  </aside>

2️⃣ Select Specific Columns

Sample Question:"🔎 List the name and price of all products."

Solution:

3️⃣ Filtering with WHERE

• Task: 🕵️‍♂️ Learn to filter results using the WHERE clause.

  Sample Question: "📖 List name of all books in our product inventory."

  Solution:SELECT product_name FROM products WHERE category='Book';

  

  Sample Question: 📋 "Retrieve the names, prices, and release dates of products that were released between January 1, 2020, and December 31, 2021. Display their names, prices, and release dates."

  SELECT product_name, price, release_date 
  FROM products 
  WHERE release_date BETWEEN '2020-01-01' AND '2021-12-31';
  

  Sample Question: "📱 Find name & price of all smartphones from our product inventory that are priced above $750."

  Solution:SELECT product_name, price FROM products WHERE category='Smartphone' AND price > 750;

  

  <aside> 💡 Numbers in filters don’t require any ’ quotes, whereas, dates and string do!

  </aside>

4️⃣ Multiple Conditions using AND & OR

• Task: 🧠 Use multiple conditions in the WHERE clause.

  Sample Question: "🎧 List name, brand and discount of the headphone category that are either from Sony or have a discount greater than 10% from product inventory"

  Solution:SELECT product_name, brand, discount FROM products WHERE category='Headphone' AND (brand='Sony' OR discount > 10);

  

  <aside> 💡 Noticed brackets in where condition? Think of the brackets around brand='Sony' OR discount > 10 like a special filter: they filter 'Sony' brand or products with more than 10% discounts while making sure that the category is 'Headphone' 🎧🔍

  </aside>

Module Sherlock Query 🕵️

Sample Question: 📋 "Retrieve the names and prices of products in the 'Smartphone' category that are either priced above $500 but below $800, or have a discount greater than 5%. Display their names, prices, and discount."

SELECT product_name, price, discount 
FROM products 
WHERE category = 'Smartphone' 
AND (price between 500 and 800 OR discount > 5);

<aside> 💡 🕵️‍♂️ Introducing the Sherlock Query! 🎉

Here's the deal: If you can unravel the Sherlock Query, you've mastered this module. It's the culmination of everything you've learned. But, a word to the wise? If it stumps you, take a moment. Dive back into the concepts, or do a little detective work online. After all, even Sherlock sometimes needed a second look 😉

A word from Sherlock: "My dear Watson, before we rush into another mystery, ensure you've truly grasped this one. It would be most unwise to venture forth without a clear understanding of the puzzle at hand."

</aside>

🚀 Module Practice Problems

1. Question: "👖 List name and price of the products from Jeans category that are priced below $50."

Solution:SELECT product_name, price FROM products WHERE category='Jeans' AND price < 50;

1. Question: "📱 How many smartphones are in stock?"

Solution:SELECT COUNT(product_name) FROM products WHERE category='Smartphone' AND stock_quantity > 0;

1. Question: "🎧 Which headphones have a discount of exactly 15%?"

Solution:SELECT product_name FROM products WHERE category='Headphone' AND discount = 15;

1. Question: "🖥 Are there any laptops from the brand 'Dell' available?"

Solution:SELECT product_name FROM products WHERE category='Laptop' AND brand='Dell';

1. Question: "🍔 List all food items that are vegetarian."

Solution:SELECT product_name FROM products WHERE category='Food' AND attributes='Vegetarian';

1. Question: "💻 Find the names and prices of laptops which are in stock and have a discount of less than 10%, sorted in ascending order of price."

Solution:SELECT product_name, price FROM products WHERE category='Laptop' AND stock_quantity > 0 AND discount < 10 ORDER BY price ASC;

Key Takeaways:

• SELECT is your gateway to accessing data columns in a table.
• With WHERE, you can filter data to meet specific conditions.
  • It can be combined with operators like >, <, = for more refined filtering.
• AND & OR allow for combining multiple conditions in your filters.
• There are more advanced filters such as LIKE, IN, BETWEEN that we will learn in later sections