Table Querying   «Prev  Next»

Lesson 8 Use the SELECT statement
Objective Create a SELECT statement to return the city for each of the rows in the customer table.

Create SELECT Statement

Absolutely! Here's how we'd accomplish this:
  1. Table Creation (SQL)** 
    CREATE TABLE customer (
    CustomerID INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    DOB DATE,
    City VARCHAR(50) 
);
  2. Fictional Data Insertion (SQL)** 
    INSERT INTO customer (CustomerID, first_name, last_name, DOB, City) 
VALUES 
    (1, 'Sarah', 'Johnson', '1985-05-12', 'New York'),
    (2, 'John', 'Miller', '1972-01-30', 'Los Angeles'),
    (3, 'Emily', 'Williams', '1990-11-07', 'Chicago'),
    (4, 'David', 'Brown', '1980-08-21', 'Miami'),
    (5, 'Olivia', 'Davis', '1995-03-16', 'Seattle');
  3. SELECT Statement (SQL): 
    SELECT City FROM customer;
Explanation
  • The `CREATE TABLE` statement builds our `customer` table with the specified columns and appropriate datatypes (adjust VARCHAR lengths as needed for your fictional data).
  • The `INSERT INTO` statement populates the table with 5 rows of fictional customer information.
  • The `SELECT` statement fetches only the `City` column from every row in the `customer` table.

Output Running the `SELECT City FROM customer;` query would give you: 
City          
-------------
New York
Los Angeles
Chicago
Miami
Seattle
The SELECT statement has the following overall basic syntax: 
SELECT what FROM where

You can indicate that individual columns or all the columns should be returned. To indicate all the columns, use the keyword ALL, or the asterisk, as we've done before. The following is an example of a query that will return only the name fields from Customer Table: 
SELECT lastname, firstname from Customer

This gives you just these two columns from the table, as shown below.
LastName FirstName
Sasser Sheila
Golden Debbie
Schwartz Kathy

There is still more to learn about the SELECT statement, and we will examine advanced uses in the next module. Then we will investigate how to narrow the results returned by the SELECT statement, and how you can sort the results.


FROM Clause

The FROM clause lists the tables from which the database should pull data. Normally if the query pulls data from more than one table, the query either uses a JOIN or a WHERE clause to indicate how the records in the tables are related. For example, the following statement selects information from the Orders and OrderItems tables. It matches records from the two using a WHERE clause. That clause tells the database to associate Orders records with OrderItems records that have the same OrderId value. 
SELECT * FROM Orders, OrderItems
WHERE Orders.OrderId = OrderItems.OrderId

Several different kinds of JOIN clauses perform roughly the same function as the previous WHERE clause. They differ in how the database handles records in one table that have no corresponding records in the second table.

Select Statement - Exercise

Click the Exercise link below to use the SELECT statement on the customer table.
Select Statement - Exercise

SEMrush Software