Navigation
Teaches students how to retrieve specific information from a database using SELECT and filtering clauses.
Every time you search for a song on Spotify or a product on Amazon, you are triggering a complex SQL query. How does a computer sift through billions of records in milliseconds to find exactly what you want?
SQL (Structured Query Language) is the standard language for interacting with databases. The most fundamental operation is retrieving data using the SELECT and FROM clauses. Think of a database table like a massive spreadsheet; SELECT identifies the columns (vertical slices) you want to see, while FROM identifies the specific table. If you want to retrieve every single column in a table, you use the wildcard symbol: ``. For example, `SELECT ` is like saying 'show me everything.'
Suppose we have a table named `Students` with columns `FirstName`, `LastName`, and `GPA`.
1. To see only the names of all students: `SELECT FirstName, LastName FROM Students;` 2. To see all information for every student: `SELECT * FROM Students;`
Quick Check
If you have a table named 'Inventory', what query would you write to see only the 'ItemName' and 'Price' columns?
Answer
SELECT ItemName, Price FROM Inventory;
To find specific records, we use the WHERE clause. This acts as a filter that only allows rows meeting a certain condition to pass through. We use Logical Operators to create complex filters: AND (both conditions must be true), OR (at least one condition must be true), and NOT (excludes records). For numerical data, we use standard comparison operators like or . For example, finding students with a and requires the AND operator.
Imagine a `Products` table with `Category`, `Price`, and `Stock`.
1. Find electronics that cost more than Stock = 0$) or in the 'Clearance' category: `SELECT * FROM Products WHERE Stock = 0 OR Category = 'Clearance';`
Quick Check
Which logical operator would you use if you wanted to find employees who work in 'Sales' but are NOT in the 'Junior' level?
Answer
The AND and NOT operators (e.g., WHERE Department = 'Sales' AND NOT Level = 'Junior')
Raw database results are often returned in an unpredictable order. To organize your output, use the ORDER BY clause at the very end of your query. You can sort by any column in Ascending (ASC) order, which is the default (A-Z, 1-10), or Descending (DESC) order (Z-A, 10-1). Sorting is crucial for identifying extremes, such as the highest sales or the most recent dates.
Let's combine everything. We want a list of all 'Laptop' names and their prices from the `Store` table, but only if they cost less than $1000. Finally, we want the cheapest ones at the top.
1. Identify columns: `SELECT ItemName, Price` 2. Identify table: `FROM Store` 3. Apply filter: `WHERE Category = 'Laptop' AND Price < 1000` 4. Apply sort: `ORDER BY Price ASC;`
Full Query: `SELECT ItemName, Price FROM Store WHERE Category = 'Laptop' AND Price < 1000 ORDER BY Price ASC;`
Which SQL keyword is used to retrieve only unique rows (no duplicates)?
In a query, where must the ORDER BY clause be placed?
The query 'SELECT * FROM Users WHERE Age > 18 OR Age < 30' will return all users because every age is either greater than 18 or less than 30.
Review Tomorrow
In 24 hours, try to recall the three main clauses of a SQL query and the specific order they must appear in.
Practice Activity
Try this on your own: Write a query for a 'Movies' table that selects the Title and Rating for all 'Drama' movies with a rating higher than 8.0, sorted by the Rating from highest to lowest.