Skip to the content
Start

CREATE TABLE

SQL · Unit 12: Designing tables

Back in unit 2 the Cable and the Speaker ran out, and "the owner is going to call the supplier." At what number? The store keeps customers, products, and orders, and none of its three tables has room for that phone number. Someone built those three before you got here. The fourth is yours to build.

CREATE TABLE gives the table's name and, in parentheses, its columns, separated by commas. Each column gets its name and, after it, the type of value it holds. TEXT is text and INTEGER is an integer.

A CREATE TABLE returns no rows, same as an INSERT. The table starts out empty, so the code below adds its first row and the SELECT that shows it.

CREATE TABLE suppliers (
  name TEXT,
  phone TEXT,
  days INTEGER
);
INSERT INTO suppliers
  (name, phone, days)
VALUES ('Summit', '6175550142', 3);
SELECT name, phone, days
FROM suppliers;

Returns

namephonedays
Summit61755501423

The rest of the explanation is in the lesson, which is part of the full plan.

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

  1. 1. Predict the output

    The store sets up another table, to write down what it needs to restock. It creates the table and queries it without putting anything in. What does the SELECT return?

  2. 2. Put the lines in order

    Another supplier, Techline, takes 10 days. Put the three statements in order. The first creates the table, the second records Techline, and the third shows its name and its days. Two lines don't belong.

  3. 3. Find the bug

    The store wants a separate list of whoever buys from it by the box, each with a discount. Here SQL answers table customers already exists. Which line has the problem?

  4. 4. Find the bug

    The owner wants the supplier that delivers soonest to come first. Summit takes 3 days and Techline takes 10. The query runs without an error and puts Techline first. Which line has the problem?

  5. 5. Complete the code

    Summit ships from ZIP code 02134, with that zero in front. Fill in the column's type so it's stored the way it's written.

  6. 6. Predict the output

    The owner wants to know how many days Techline and Breeze take between the two of them. Unit 1 said that here, text inside a calculation counts as 0. What does the SELECT return?

  7. 7. Multiple choice

    Someone writes the types from memory, the way another language names them. What does this statement do here?

  8. 8. Find the bug

    The owner wants to try out a report with orders empty, and counts the rows that remain to check that it worked. Here SQL answers no such table: orders. Which line has the problem?

  9. 9. Write the code

    The store is going to open more shops and wants a table for them. Create the shops table with three columns, in this order. city holds text. sqft holds an integer. rent holds a number with decimals. Write the types the way this lesson does (TEXT, INTEGER, and REAL), and don't put any rows in it.

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →