Test Your MySQL CREATE TABLE Knowledge: Interactive Quiz [2024]

Progress

Single Choice

What is the primary keyword used to create a new table in MySQL?

ADD TABLE CREATE TABLE NEW TABLEMAKE TABLE

The CREATE TABLE statement is used to create a new table in MySQL.

Single Choice

Which data type would you use for a column storing integer values in MySQL?

VARCHAR TEXT INTDATE

The INT data type is used to store integer values in MySQL

Single Choice

What is the correct syntax to create a table named 'employees' with columns 'id' (integer) and 'name' (varchar)?

CREATE TABLE employees (id INT, name VARCHAR); CREATE TABLE employees (id INT, name VARCHAR(255)); CREATE TABLE employees (id INTEGER, name VARCHAR(255));CREATE employees (id INT, name VARCHAR);

This is the correct syntax in MySQL. The VARCHAR datatype requires a length specification.

Single Choice

Which statement is correct when creating a table with a primary key in MySQL?

CREATE TABLE users (user_id INT PRIMARY); CREATE TABLE users (user_id INT PRIMARY KEY); CREATE users (user_id INT PRIMARY KEY);CREATE TABLE users (user_id INT PRIMARY KEY, PRIMARY KEY (user_id));

This syntax correctly defines user_id as the primary key of the table.

Single Choice

How do you specify a default value for a column in a MySQL CREATE TABLE statement?

CREATE TABLE employees (id INT, status VARCHAR(20) DEFAULT 'active'); CREATE TABLE employees (id INT, status VARCHAR DEFAULT 'active'); CREATE TABLE employees (id INT, status VARCHAR(20) SET DEFAULT 'active');CREATE TABLE employees (id INT, status VARCHAR(20) WITH DEFAULT 'active');

The DEFAULT keyword is used to set a default value for a column.

Single Choice

How can you ensure a column cannot have NULL values in MySQL?

CREATE TABLE products (product_id INT, name VARCHAR(100) NOT NULL); CREATE TABLE products (product_id INT, name VARCHAR(100) NON NULL); CREATE TABLE products (product_id INT, name VARCHAR(100) NO NULL);CREATE TABLE products (product_id INT, name VARCHAR(100) ZERO NULL);

The NOT NULL constraint ensures that a column cannot have NULL values.

Single Choice

What is the purpose of the AUTO_INCREMENT attribute in MySQL?

Automatically updates column values Automatically generates a unique number for a column Automatically creates an index for the columnAutomatically sets a column to NULL

The AUTO_INCREMENT attribute is used to generate a unique identifier for new rows in a table.

Single Choice

How would you create a table with a composite primary key in MySQL?

CREATE TABLE orders (order_id INT, product_id INT, PRIMARY KEY (order_id, product_id)); CREATE TABLE orders (order_id INT, product_id INT, PRIMARY (order_id, product_id)); CREATE TABLE orders (order_id INT, product_id INT, COMPOSITE KEY (order_id, product_id));CREATE TABLE orders (order_id INT, product_id INT, PRIMARY (order_id, product_id));

This syntax correctly defines a composite primary key consisting of order_id and product_id.

Single Choice

Which of the following is a correct statement to create a foreign key constraint in MySQL?

CREATE TABLE orders (order_id INT, customer_id INT, FOREIGN KEY (customer_id) REFERENCES customers(id)); CREATE TABLE orders (order_id INT, customer_id INT, FOREIGN (customer_id) REFERENCES customers(id)); CREATE TABLE orders (order_id INT, customer_id INT, FOREIGN KEY customer_id REFERENCES customers(id));CREATE TABLE orders (order_id INT, customer_id INT, REFERENCE KEY (customer_id) REFERENCES customers(id));

This syntax correctly defines a foreign key constraint on customer_id.

Q10

Single Choice

In MySQL, how would you create a table that stores text data efficiently for large amounts of text?

CREATE TABLE articles (id INT, content VARCHAR(10000)); CREATE TABLE articles (id INT, content TEXT); CREATE TABLE articles (id INT, content STRING);CREATE TABLE articles (id INT, content LONGVARCHAR);

The TEXT datatype is designed for storing large amounts of text data efficiently in MySQL.

Q11

True / False

In MySQL, the CREATE TABLE statement is used to create a new database.

True False

The CREATE TABLE statement is used to create a new table, not a database. The CREATE DATABASE statement is used to create a new database.

Q12

True / False

The VARCHAR datatype in MySQL requires a length specification when creating a table.

True False

In MySQL, the VARCHAR datatype requires a length to be specified to determine the maximum number of characters that can be stored.

Q13

True / False

You can use the CREATE TABLE statement in MySQL to create a table without specifying any columns.

True False

When creating a table in MySQL, you must specify at least one column.

Q14

True / False

In MySQL, the PRIMARY KEY constraint can be applied to more than one column in a table.

True False

MySQL allows the PRIMARY KEY constraint to be applied to multiple columns, creating a composite primary key.

Q15

True / False

The DEFAULT keyword in MySQL is used to set a default value for a column when creating a table.

True False

The DEFAULT keyword sets a default value for a column that will be used if no value is specified during insertion.

Q16

True / False

In MySQL, columns defined with the NOT NULL constraint can accept NULL values.

True False

The NOT NULL constraint ensures that a column cannot accept NULL values.

Q17

True / False

In MySQL, the AUTO_INCREMENT attribute can be used on any column type.

True False

The AUTO_INCREMENT attribute is typically used on integer columns to automatically generate unique values.

Q18

True / False

In MySQL, you can create a table with a FOREIGN KEY constraint that references a column in the same table.

True False

The AUTO_INCREMENT attribute is typically used on integer columns to automatically generate unique values.

Q19

True / False

The ENGINE keyword in MySQL is used in the CREATE TABLE statement to specify the storage engine for the table.

True False

The ENGINE keyword specifies the storage engine (such as InnoDB or MyISAM) for the table.

Q20

True / False

In MySQL, creating a table with a TEXT column is more efficient than using a VARCHAR column for large text data.

True False

The TEXT datatype is designed for storing large amounts of text data efficiently, while VARCHAR is suitable for shorter strings and requires a length specification.

Q21

Multiple Choice

Identify the correct CREATE TABLE statement for defining a table with a primary key and a unique constraint.

SQL Code

CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 email VARCHAR(100) UNIQUE
);

CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) UNIQUE ); CREATE TABLE employees ( employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) ); CREATE TABLE employees ( employee_id INT, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) PRIMARY KEY );CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), email VARCHAR(100) NOT NULL );

The primary key ensures each employee has a unique ID, and the unique constraint on the email column prevents duplicate email addresses.

Q22

Multiple Choice

Select the correct CREATE TABLE statement that includes a foreign key constraint.

SQL Code

CREATE TABLE orders (
 order_id INT PRIMARY KEY,
 customer_id INT,
 order_date DATE,
 FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE ); CREATE TABLE orders ( order_id INT, customer_id INT, order_date DATE, FOREIGN KEY (order_date) REFERENCES customers(order_date) );CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT, order_date DATE, CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

The foreign key constraint on the customer_id column establishes a relationship between the orders and customers tables.

Q23

Multiple Choice

Choose the correct CREATE TABLE statement that sets a default value for a column.

SQL Code

CREATE TABLE products (
 product_id INT PRIMARY KEY,
 product_name VARCHAR(100),
 category VARCHAR(50) DEFAULT 'General',
 price DECIMAL(10, 2)
);

CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50) DEFAULT 'General', price DECIMAL(10, 2) ); CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100) DEFAULT 'General', category VARCHAR(50), price DECIMAL(10, 2) ); CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50), price DECIMAL(10, 2) DEFAULT 0.00 );CREATE TABLE products ( product_id INT PRIMARY KEY, product_name VARCHAR(100), category VARCHAR(50) DEFAULT 'General', price DECIMAL(10, 2) DEFAULT 10.00 );

The category column has a default value of 'General', which is used if no other value is provided during the insert operation.

Q24

Multiple Choice

Which CREATE TABLE statement correctly defines a table with a composite primary key?

SQL Code

CREATE TABLE order_items (
 order_id INT,
 product_id INT,
 quantity INT,
 PRIMARY KEY (order_id, product_id)
);

CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id, product_id) ); CREATE TABLE order_items ( order_id INT PRIMARY KEY, product_id INT, quantity INT ); CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT, PRIMARY KEY (order_id) );CREATE TABLE order_items ( order_id INT, product_id INT PRIMARY KEY, quantity INT, PRIMARY KEY (order_id) );

A composite primary key is defined using multiple columns, in this case, order_id and product_id together form the primary key.

Q25

Multiple Choice

Determine the correct CREATE TABLE statement that includes a check constraint.

SQL Code

CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 salary DECIMAL(10, 2),
 CHECK (salary > 0)
);

CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), salary DECIMAL(10, 2), CHECK (salary > 0) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), salary DECIMAL(10, 2) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), salary DECIMAL(10, 2), CHECK (salary <= 0) );CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), salary DECIMAL(10, 2) CHECK (salary > 0) );

The check constraint ensures that the salary value is always greater than 0, preventing invalid data entry.

Q26

Multiple Choice

Select the correct CREATE TABLE statement that defines a table with an auto-incrementing primary key.

SQL Code

CREATE TABLE invoices (
 invoice_id INT AUTO_INCREMENT PRIMARY KEY,
 customer_id INT,
 invoice_date DATE,
 amount DECIMAL(10, 2)
);

CREATE TABLE invoices ( invoice_id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT, invoice_date DATE, amount DECIMAL(10, 2) ); CREATE TABLE invoices ( invoice_id INT PRIMARY KEY, customer_id INT, invoice_date DATE, amount DECIMAL(10, 2) ); CREATE TABLE invoices ( invoice_id INT AUTO_INCREMENT, customer_id INT, invoice_date DATE, amount DECIMAL(10, 2) );CREATE TABLE invoices ( invoice_id INT, customer_id INT, invoice_date DATE, amount DECIMAL(10, 2) );

The AUTO_INCREMENT keyword ensures that each new invoice has a unique, automatically incremented ID.

Q27

Multiple Choice

Which CREATE TABLE statement correctly specifies a table with multiple foreign keys?

SQL Code

CREATE TABLE student_courses (
 student_id INT,
 course_id INT,
 enrollment_date DATE,
 PRIMARY KEY (student_id, course_id),
 FOREIGN KEY (student_id) REFERENCES students(student_id),
 FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

CREATE TABLE student_courses ( student_id INT, course_id INT, enrollment_date DATE, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students(student_id), FOREIGN KEY (course_id) REFERENCES courses(course_id) ); CREATE TABLE student_courses ( student_id INT, course_id INT, enrollment_date DATE, FOREIGN KEY (student_id, course_id) REFERENCES students_courses(student_id, course_id) ); CREATE TABLE student_courses ( student_id INT, course_id INT PRIMARY KEY, enrollment_date DATE, FOREIGN KEY (student_id) REFERENCES students(student_id) );CREATE TABLE student_courses ( student_id INT PRIMARY KEY, course_id INT, enrollment_date DATE, FOREIGN KEY (course_id) REFERENCES courses(course_id) );

This table links students to courses with a composite primary key and two foreign keys referencing other tables.

Q28

Multiple Choice

Identify the CREATE TABLE statement that includes a column with a NOT NULL constraint.

SQL Code

CREATE TABLE departments (
 department_id INT PRIMARY KEY,
 department_name VARCHAR(100) NOT NULL,
 manager_id INT
);

CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(100) NOT NULL, manager_id INT ); CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(100), manager_id INT ); CREATE TABLE departments ( department_id INT, department_name VARCHAR(100) PRIMARY KEY, manager_id INT );CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(100) NOT NULL, manager_id INT UNIQUE );

The NOT NULL constraint ensures that the department_name cannot be null, making it a required field.

Q29

Multiple Choice

Choose the CREATE TABLE statement that defines a table with a column using the ENUM data type.

SQL Code

CREATE TABLE employees (
 employee_id INT PRIMARY KEY,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 employment_status ENUM('Active', 'Inactive', 'On Leave')
);

CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), employment_status ENUM('Active', 'Inactive', 'On Leave') ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), employment_status VARCHAR(10) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), employment_status SET('Active', 'Inactive', 'On Leave') );CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), employment_status VARCHAR(50) );

The ENUM data type restricts the values of the employment_status column to 'Active', 'Inactive', or 'On Leave'.

Q30

Multiple Choice

Determine the CREATE TABLE statement that correctly uses a column alias within the statement.

SQL Code

CREATE TABLE inventory (
 product_id INT PRIMARY KEY,
 product_name VARCHAR(100),
 quantity_in_stock INT AS qty_stock,
 price DECIMAL(10, 2)
);

CREATE TABLE inventory ( product_id INT PRIMARY KEY, product_name VARCHAR(100), quantity_in_stock INT AS qty_stock, price DECIMAL(10, 2) ); CREATE TABLE inventory ( product_id INT PRIMARY KEY, product_name VARCHAR(100), qty_stock INT, price DECIMAL(10, 2) ); CREATE TABLE inventory ( product_id INT PRIMARY KEY, product_name VARCHAR(100), quantity_in_stock INT, price DECIMAL(10, 2) );CREATE TABLE inventory ( product_id INT PRIMARY KEY, product_name VARCHAR(100) AS pname, quantity_in_stock INT, price DECIMAL(10, 2) );

Column aliases are used to give a column a temporary name. However, in MySQL, column aliases are typically used in SELECT statements, not in table definitions.

MySQL Database Quiz Questions