SQL column data types and sizes
Name:
Date:
Class:
A
Quick Questions
Shade in the A or B circle for each question.
1. Which column type is best for a student's first name?
2. Which column type is best for a whole-number count such as stock?
3. What does the (8,2) mean in DECIMAL(8,2)?
4. Which is the correct way to declare a price column that stores values like 12.50?
5. Which column is declared with a sensible type for an order date?
B
Match the Pairs
Draw a line connecting each item on the left to its match on the right.
| Left column | Right column | |
|---|---|---|
| 1firstName VARCHAR(50) | AVARCHAR has no size. A maximum number of characters is needed, for example VARCHAR(50). | |
| 2price DECIMAL(8,2) | BA number column with up to 8 digits in total, 2 of them after the decimal point | |
| 3A column that stores whole numbers only, such as a student's age or points | CA text column that can hold up to 50 characters | |
| 4A column that stores a calendar date such as a birth date or order date | DINT stores whole numbers, so it is the wrong type for a true/false value. BOOLEAN should be used. | |
| 5lastName VARCHAR | EorderDate DATE | |
| 6inStock INT | Fpoints INT |
C
Definitions
VARCHAR(50)
D
Fill in the Blanks
Complete each line of code by filling in the missing parts.
1.
CREATE TABLE students (
firstName _______(50)
);
2.
CREATE TABLE products (
stock ___
);
3.
CREATE TABLE products (
price DECIMAL(8,_)
);
4.
CREATE TABLE orders (
orderDate ____
);
5.
CREATE TABLE students (
isPrefect _______
);
E
Spot the Error
Shade in the letter of the line that contains an error.
Question 1
A
CREATE TABLE Student (
B
firstName VARCHAR,
C
yearLevel INT,
D
house VARCHAR(10)
E
);
Question 2
A
CREATE TABLE Product (
B
productId INT,
C
name VARCHAR(40),
D
price DECIMAL(6.2),
E
stock INT
F
);
Question 3
A
CREATE Orders TABLE (
B
orderId INT,
C
orderDate DATE
D
);
Question 4
A
CREATE TABLE Event
B
eventName VARCHAR(30),
C
eventDate DATE,
D
isPublic BOOLEAN
E
);
Question 5
A
CREATE TABLE Staff (
B
staffId INT,
C
hireDate DATE(10),
D
salary DECIMAL(8,2)
E
);
Answer Key
SQL column data types and sizes
Section A: Quick Questions
Q1
B — firstName VARCHAR(50)
Q2
A — stock INT
Q3
B — Up to 8 digits in total, with 2 after the decimal point
Q4
A — price DECIMAL(8,2)
Q5
A — orderDate DATE
Section B: Match the Pairs
Q1
firstName VARCHAR(50) → A text column that can hold up to 50 characters
Q2
price DECIMAL(8,2) → A number column with up to 8 digits in total, 2 of them after the decimal point
Q3
A column that stores whole numbers only, such as a student's age or points → points INT
Q4
A column that stores a calendar date such as a birth date or order date → orderDate DATE
Q5
lastName VARCHAR → VARCHAR has no size. A maximum number of characters is needed, for example VARCHAR(50).
Q6
inStock INT → INT stores whole numbers, so it is the wrong type for a true/false value. BOOLEAN should be used.
Section C: Definitions
Q1
VARCHAR(50) — A column's data type and size set in CREATE TABLE. VARCHAR(50) holds text up to 50 characters. Other types: INT, DECIMAL(8,2), DATE, BOOLEAN. They control what a column stores.Section D: Fill in the Blanks
Q1
CREATE TABLE students (
firstName VARCHAR(50)
);Q2
CREATE TABLE products (
stock INT
);Q3
CREATE TABLE products (
price DECIMAL(8,2)
);Q4
CREATE TABLE orders (
orderDate DATE
);Q5
CREATE TABLE students (
isPrefect BOOLEAN
);Section E: Spot the Error
Q1
B — VARCHAR must have a size in brackets to say the maximum number of characters. It should be VARCHAR(50).
Q2
D — DECIMAL takes two numbers separated by a comma: precision and scale. It should be DECIMAL(6,2), not DECIMAL(6.2).
Q3
A — The SQL keywords are in the wrong order. It should be CREATE TABLE Orders ( with the table name after TABLE.
Q4
A — The column definitions must start inside an opening bracket. The line should end with ( so it reads CREATE TABLE Event (
Q5
C — The DATE type has a fixed format and does not take a size. It should be just DATE.