CREATE TABLE syntax in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of this CREATE TABLE statement?

Consider the following SQL statement:

CREATE TABLE Employees (ID INT PRIMARY KEY, Name VARCHAR(50), Age INT);

What will happen when this statement is executed in a standard SQL database?

SQL

CREATE TABLE Employees (ID INT PRIMARY KEY, Name VARCHAR(50), Age INT);

A A new table named Employees is created with three columns: ID, Name, and Age. ID is the primary key.

B An error occurs because VARCHAR requires a length greater than 50.

C The statement creates a table named Employees but the Age column is not created due to missing datatype.

D The table is created but without a primary key constraint.

💡 Hint

Think about what PRIMARY KEY means and if the syntax for VARCHAR is correct.

The statement correctly creates a table named Employees with three columns. ID is set as the primary key, which means it uniquely identifies each row. VARCHAR(50) is valid syntax for a string up to 50 characters.

📝 Syntax

intermediate

Which CREATE TABLE statement has a syntax error?

Identify which of the following CREATE TABLE statements will cause a syntax error in standard SQL.

A CREATE TABLE Products (ProductID INT, ProductName VARCHAR(100), Price DECIMAL(10,2));

B CREATE TABLE Orders (OrderID INT PRIMARY KEY, OrderDate DATE, CustomerID INT);

C CREATE TABLE Customers (CustomerID INT, Name VARCHAR(50), Email VARCHAR(50) UNIQUE);

D CREATE TABLE Sales (SaleID INT, SaleDate DATETIME, Amount MONEY(10,2));

💡 Hint

Check the datatype MONEY and its parameters.

The MONEY datatype does not accept precision and scale parameters like DECIMAL does. Using MONEY(10,2) causes a syntax error.

🧠 Conceptual

advanced

What is the purpose of the PRIMARY KEY constraint in CREATE TABLE?

Why do we use PRIMARY KEY in a CREATE TABLE statement? Choose the best explanation.

A It ensures that the column values are unique and not NULL, uniquely identifying each row.

B It allows duplicate values but speeds up queries on that column.

C It automatically creates an index but does not enforce uniqueness.

D It marks the column as optional and allows NULL values.

💡 Hint

Think about what makes each row in a table unique.

PRIMARY KEY enforces uniqueness and disallows NULLs, ensuring each row can be uniquely identified by that column or set of columns.

🔧 Debug

advanced

Why does this CREATE TABLE statement fail?

Examine this SQL statement:

CREATE TABLE Inventory (ItemID INT, Quantity INT DEFAULT 'ten');

Why will this statement cause an error?

SQL

CREATE TABLE Inventory (ItemID INT, Quantity INT DEFAULT 'ten');

A The table name Inventory is a reserved keyword and cannot be used.

B The column Quantity is missing a NOT NULL constraint.

C The DEFAULT value 'ten' is a string and cannot be assigned to an INT column.

D The statement is valid and will create the table successfully.

💡 Hint

Check the data type of the DEFAULT value compared to the column type.

The DEFAULT value must match the column's datatype. 'ten' is a string and cannot be used as a default for an INT column.

❓ optimization

expert

Which CREATE TABLE design is best for fast lookups on a large dataset?

You need to create a table to store millions of user records and want fast lookups by user email. Which CREATE TABLE statement is best optimized for this?

A CREATE TABLE Users (UserID INT PRIMARY KEY, Email TEXT, Name VARCHAR(100));

B CREATE TABLE Users (UserID INT PRIMARY KEY, Email VARCHAR(255) UNIQUE, Name VARCHAR(100));

C CREATE TABLE Users (UserID INT, Email VARCHAR(255), Name VARCHAR(100));

D CREATE TABLE Users (UserID INT PRIMARY KEY, Email VARCHAR(255), Name VARCHAR(100));

💡 Hint

Consider constraints that help with indexing and uniqueness.

Adding UNIQUE constraint on Email creates an index that speeds up lookups and ensures no duplicate emails exist. VARCHAR(255) is appropriate for email length.