Create SELECT Statement to return City

Here's how we would 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.

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.

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