SQL Fundamentals: SELECT and WHERE

This lesson introduces the fundamentals of SQL, the language used to interact with databases. You will learn how to retrieve specific data from tables using the `SELECT` statement and how to filter the results based on specific criteria using the `WHERE` clause.

Learning Objectives

  • Understand the basic structure of SQL queries.
  • Write `SELECT` statements to retrieve all columns and specific columns from a table.
  • Use the `WHERE` clause to filter data based on various conditions.
  • Recognize and interpret different data types commonly found in SQL databases.

Text-to-Speech

Listen to the lesson content

Lesson Content

Introduction to SQL and Databases

SQL (Structured Query Language) is the standard language for managing and manipulating data in relational databases. Databases are organized collections of data, typically structured into tables. Think of a table like a spreadsheet, with rows representing individual records and columns representing different attributes or pieces of information. Each column has a data type, such as TEXT (for text), INTEGER (for whole numbers), REAL (for decimal numbers), or DATE (for dates). Understanding these data types is crucial for writing effective queries. For this lesson, we'll imagine a database containing a table called Customers with columns like CustomerID, FirstName, LastName, City, and Country.

The SELECT Statement

The SELECT statement is the most fundamental SQL command. It's used to retrieve data from one or more tables. The basic syntax is:

SELECT column1, column2, ... FROM table_name;
  • SELECT is the keyword to start your query.
  • column1, column2, ... specifies the columns you want to retrieve. Use * to select all columns.
  • FROM specifies the table you want to retrieve data from.

Example 1: Selecting all columns

SELECT * FROM Customers;

This query would retrieve all columns (CustomerID, FirstName, LastName, City, Country, etc.) and all rows from the Customers table.

Example 2: Selecting specific columns

SELECT FirstName, LastName, City FROM Customers;

This query would retrieve only the FirstName, LastName, and City columns for all rows in the Customers table.

The WHERE Clause

The WHERE clause allows you to filter the data based on a condition. This is crucial for retrieving specific subsets of data. The basic syntax is:

SELECT column1, column2, ... FROM table_name WHERE condition;
  • WHERE is the keyword that introduces the filtering condition.
  • condition is a logical expression that evaluates to true or false for each row. Common comparison operators include: = (equal to), <> or != (not equal to), > (greater than), < (less than), >= (greater than or equal to), and <= (less than or equal to).

Example 1: Filtering by City

SELECT * FROM Customers WHERE City = 'London';

This query would retrieve all columns for all customers whose city is 'London'.

Example 2: Filtering by Country and City

SELECT FirstName, LastName FROM Customers WHERE Country = 'USA' AND City = 'New York';

This query would retrieve the FirstName and LastName for all customers who live in 'New York' and are from 'USA'. The AND operator requires both conditions to be true. You can also use the OR operator to check if either condition is true.

Example 3: Using Numeric Comparison

Let's assume there's a table called Orders with a column called OrderTotal. To find all orders over $100:

SELECT * FROM Orders WHERE OrderTotal > 100;
Progress
0%