📗 Table Creation

Welcome to the Selecting Data module! This foundational learning path is designed to help you master the basics of querying data, particularly focusing on how to retrieve specific information from databases effectively.

📘 Creating SQL Table

In SQL, when creating a table, each column must have a data type that defines what kind of data it can hold. Common data types include:

INTEGER – whole numbers REAL – decimal (floating-point) numbers TEXT – strings of characters

Other types like BLOB and NULL exist but are not covered in this tutorial.

🛠️ Example: Creating a Table

Lesson Overview

Let’s create a simple table named Directory, which holds the floor number and company name.

This CREATE TABLE statement defines a new table called Directory with two columns:

floor — stores the floor number as an INTEGER

company — stores the company name as TEXT

info

SQL Code

Creating SQL Tables & db.

CREATE TABLE Directory (
floor INTEGER,
company TEXT
);

Output

Query OK, table created successfully.

tip

✅ Tip: SQL keywords like CREATE TABLE, INTEGER, and TEXT are not case-sensitive, but using uppercase for SQL keywords improves readability.

🧾 Inserting Data into a Table

info

Once a table structure is created using CREATE TABLE, the next step is to insert data into it. You can insert as many rows as you want, at any time.

We’ll continue with the Directory table from earlier. Let’s insert a couple of company records.

SQL Code

Creating SQL Tables.

CREATE TABLE Directory (
floor INTEGER,
company TEXT
);

INSERT INTO Directory (floor, company)
VALUES (1, 'Acme Inc.');

INSERT INTO Directory (floor, company)
VALUES (2, 'Homeflix');

Output

floor	company
1	Acme Inc.
2	Homeflix

tip

✅ Tip: Always match the order of columns in your INSERT INTO statement with the order of values inside VALUES().

🚫 Avoiding Duplicate Table Creation When running a CREATE TABLE command, trying to create a table that already exists will usually result in an error. To avoid this, we can use the IF NOT EXISTS clause.

CREATE TABLE IF NOT EXISTS Directory (
floor INTEGER,
company TEXT
);

✅ What You Have Learned

In this module, you learned the fundamentals of creating tables in SQL, including:

• Defining Table Structure
  How to use the CREATE TABLE statement to define a new table and specify columns with appropriate data types.

• Common Data Types
  The purpose of data types like INTEGER for numbers and TEXT for strings.

• Inserting Data
  How to add rows to a table using the INSERT INTO statement.

• Preventing Duplicate Tables
  Using IF NOT EXISTS with CREATE TABLE to avoid errors if the table already exists.

• Best Practices
  Writing SQL keywords in uppercase for readability and matching column order in INSERT INTO statements.

---

📝 Quiz: Test Your Knowledge

1. How can you ensure a table is only created if it doesn't already exist, to avoid errors?

Answer

• By adding IF NOT EXISTS to the CREATE TABLE statement:

CREATE TABLE IF NOT EXISTS Directory (
  floor INTEGER,
  company TEXT
);

2. Complete the column definition for the Tickets table creation syntax with the appropriate column data types.

CREATE TABLE Tickets (
  qty _____,
  email _____
);

Answer

• Specify qty as INTEGER and email as TEXT:

CREATE TABLE Tickets (
  qty INTEGER,
  email TEXT
);

3. Which of the following code can delete all the values from the given table?

• DELETE FROM toys WHERE price < 10
• DELETE FROM toys

Answer

• DELETE FROM toys will delete all rows from the toys table.

DELETE FROM toys;

---

4. What happens if you call UPDATE with a WHERE clause that does not match any existing row?

• SQLite will update the next closest row
• Nothing will change
• SQLite will automatically create a new row with the provided data

Answer

• Nothing will change — If no rows match the WHERE clause, no rows are updated.

5. Write an SQL statement to update the streams count for the song "Paradise" by "The Jets" to 130.

songname	artist	streams	certifications
Paradise	The Jets	120
My Way	Stevie J	100
Purple Sunrise	Harry	90
California Highway	Joan & Jerry	85
My Sister Meg	Couches	70

Answer

• Use the UPDATE statement with a WHERE clause to target the correct row:

UPDATE Songs
SET streams = 130
WHERE songname = 'Paradise';

6. Which of the following is not a requirement for defining a SQL table’s columns?

• Column name
• Column constraints
• Column data type

Answer

• Column constraints — This is not a requirement for defining a column in a SQL table.

7. Which of the following is not a valid SQL column data type?

• REAL
• BOOLEAN
• TEXT

Answer

• BOOLEAN — Standard SQL does not define BOOLEAN as a column data type. Some databases support it as an alias, but it is not part of the SQL standard.