CREATE TABLE and INSERT INTO

Name:

Date:

Class:

Quick Questions

Shade in the A or B circle for each question.

1. Which command builds a brand new table in a SQL database?

A CREATE TABLE

B INSERT INTO

2. Which statement adds one new record to a table called Students?

A INSERT INTO Students VALUES (1, 'Mia', 15);

B CREATE TABLE Students VALUES (1, 'Mia', 15);

3. Which line correctly defines a field in a CREATE TABLE statement?

A TEXT Name,

B Name TEXT,

4. What does PRIMARY KEY do when it is added to the StudentID field?

A Makes every record's StudentID unique so each record can be identified

B Sorts the table so the biggest StudentID is always first

5. True or false: you can INSERT INTO a table before it has been created with CREATE TABLE.

A True

B False

Match the Pairs

Draw a line connecting each item on the left to its match on the right.

Left column		Right column
1CREATE TABLE Students (ID INT, Name TEXT);		AINSERT INTO Pets VALUES (2, 'Rex');
2INSERT INTO Students VALUES (1, 'Mia');		BAdds one new record to the Students table with ID 1 and Name Mia.
3A PRIMARY KEY field gives every record a unique ID, and it is set when the table is created.		CCREATE TABLE Pets (PetID INT PRIMARY KEY, Name TEXT);
4Text values are put in quotes, and numbers are not, when adding a record.		DThe word TABLE is missing. It should be CREATE TABLE Students (...).
5CREATE Students (ID INT, Name TEXT);		EBuilds a new table called Students with two fields: ID that holds whole numbers and Name that holds text.
6INSERT INTO Students VALUES (3, Zoe);		FThe text value Zoe needs quotes. It should be 'Zoe'.

Definitions

CREATE TABLE

Fill in the Blanks

Complete each line of code by filling in the missing parts.

____________ Students ( StudentID INT PRIMARY KEY, Name TEXT);

CREATE TABLE Books ( BookID INT ___________, Title TEXT);

___________ Students (StudentID, Name) VALUES (1, 'Mia');

INSERT INTO Books (BookID, Title) ______ (1, 'Holes');

CREATE TABLE Pets ( PetID ___ PRIMARY KEY, Name TEXT);

Spot the Error

Shade in the letter of the line that contains an error.

Question 1

A CREATE TABLE Student (

B ID INTEGER PRIMARY KEY,

C Name TEXT

D Age INTEGER

E );

Question 2

A CREATE TABLE Pet (

B ID INTEGER PRIMARY,

C Name TEXT

D );

Question 3

A MAKE TABLE Song (

B SongID INTEGER PRIMARY KEY,

C Title TEXT

D );

Question 4

A INSERT Song (ID, Title)

B VALUES (1, 'Thunder');

C SELECT * FROM Song;

Question 5

A CREATE TABLE Game (

B ID INTEGER PRIMARY KEY,

C Title TEXT);

D INSERT INTO Game (ID Title)

E VALUES (1, 'Chess');

Answer Key

CREATE TABLE and INSERT INTO

Section A: Quick Questions

A — CREATE TABLE

A — INSERT INTO Students VALUES (1, 'Mia', 15);

B — Name TEXT,

A — Makes every record's StudentID unique so each record can be identified

B — False

Section B: Match the Pairs

CREATE TABLE Students (ID INT, Name TEXT); → Builds a new table called Students with two fields: ID that holds whole numbers and Name that holds text.

INSERT INTO Students VALUES (1, 'Mia'); → Adds one new record to the Students table with ID 1 and Name Mia.

A PRIMARY KEY field gives every record a unique ID, and it is set when the table is created. → CREATE TABLE Pets (PetID INT PRIMARY KEY, Name TEXT);

Text values are put in quotes, and numbers are not, when adding a record. → INSERT INTO Pets VALUES (2, 'Rex');

CREATE Students (ID INT, Name TEXT); → The word TABLE is missing. It should be CREATE TABLE Students (...).

INSERT INTO Students VALUES (3, Zoe); → The text value Zoe needs quotes. It should be 'Zoe'.

Section C: Definitions

CREATE TABLE — SQL command that builds a new table by naming its fields and their data types, such as a PRIMARY KEY. INSERT INTO ... VALUES then adds one record to that table.

Section D: Fill in the Blanks

CREATE TABLE Students (
  StudentID INT PRIMARY KEY,
  Name TEXT);

CREATE TABLE Books (
  BookID INT PRIMARY KEY,
  Title TEXT);

INSERT INTO Students (StudentID, Name)
VALUES (1, 'Mia');

INSERT INTO Books (BookID, Title)
VALUES (1, 'Holes');

CREATE TABLE Pets (
  PetID INT PRIMARY KEY,
  Name TEXT);

Section E: Spot the Error

C — Each field definition must be separated from the next by a comma. The Name line should end with a comma: Name TEXT,

B — The keyword is PRIMARY KEY, two words. The line should be: ID INTEGER PRIMARY KEY,

A — SQL does not have a MAKE TABLE command. A new table is built with CREATE TABLE Song (

A — The command needs the keyword INTO before the table name. It should be: INSERT INTO Song (ID, Title)

D — The field names in the brackets must be separated by a comma. It should be: INSERT INTO Game (ID, Title)