Test Your PostgreSQL CREATE TABLE Skills with This Quiz

Progress

True / False

In PostgreSQL, the CREATE TABLE command is used to create a new table in the current database.

True False

The CREATE TABLE command in PostgreSQL is used to create a new table in the current database.

True / False

PostgreSQL does not allow specifying a default value for a column when creating a table.

True False

PostgreSQL allows specifying a default value for a column using the DEFAULT keyword when creating a table.

True / False

The SERIAL keyword in PostgreSQL is used to create an auto-incrementing integer column.

True False

In PostgreSQL, the SERIAL keyword is used to create an auto-incrementing integer column.

True / False

In PostgreSQL, you cannot create a table without specifying a primary key.

True False

While it is a good practice to have a primary key, PostgreSQL does not require specifying a primary key when creating a table.

True / False

PostgreSQL allows the use of foreign key constraints when creating a table.

True False

PostgreSQL supports foreign key constraints to enforce referential integrity between tables.

True / False

The CREATE TABLE command in PostgreSQL does not support the CHECK constraint.

True False

PostgreSQL supports the CHECK constraint, which allows specifying a condition that must be met for each row in the table.

True / False

In PostgreSQL, the INHERITS keyword allows a table to inherit columns from one or more parent tables

True False

The INHERITS keyword in PostgreSQL allows a table to inherit columns and constraints from one or more parent tables.

True / False

PostgreSQL supports partitioning tables using the PARTITION BY clause in the CREATE TABLE statement.

True False

PostgreSQL supports table partitioning using the PARTITION BY clause, which allows dividing a large table into smaller, more manageable pieces.

True / False

The CREATE TABLE AS command in PostgreSQL creates a new table and populates it with the result of a query.

True False

The CREATE TABLE AS command in PostgreSQL creates a new table based on the result of a query and populates it with the query result.

Q10

True / False

In PostgreSQL, you can use the CREATE TABLE command to create a table with an index on a column by specifying the index directly within the CREATE TABLE statement.

True False

In PostgreSQL, you cannot specify an index directly within the CREATE TABLE statement. Instead, you need to create the index separately using the CREATE INDEX statement after the table has been created.

Q11

Single Choice

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

CREATE employees (id INT, name VARCHAR(50)); CREATE TABLE employees (id INT, name VARCHAR(50)) CREATE TABLE employees id INT, name VARCHAR(50);CREATE employees TABLE (id INT, name VARCHAR(50));

The correct syntax for creating a table in MySQL requires the use of the CREATE TABLE statement followed by the table name and columns.

Q12

Single Choice

What is the default storage engine in MySQL 8.0 when creating a table without specifying the engine?

InnoDB MyISAM MEMORYCSV

In MySQL 8.0, the default storage engine is InnoDB, which supports transactions, foreign keys, and row-level locking.

Q13

Single Choice

Which MySQL statement is used to create a table named customers with a primary key on the customer_id column?

CREATE TABLE customers (customer_id INT PRIMARY); CREATE customers TABLE (customer_id INT PRIMARY KEY); CREATE TABLE customers (customer_id INT PRIMARY KEY);CREATE customers (customer_id INT PRIMARY KEY);

The CREATE TABLE statement correctly places the PRIMARY KEY constraint on the customer_id column in option

Q14

Single Choice

What does the following MySQL statement do?

SQL Code

CREATE TABLE orders (
 order_id INT AUTO_INCREMENT,
 order_date DATE,
 PRIMARY KEY (order_id)
);

Creates a table with two columns and a primary key on order_id. Creates a table with an automatically generated order_id but no primary key. Creates a table with two columns but no primary keyCreates a table with order_id as a foreign key

The AUTO_INCREMENT keyword ensures that order_id is automatically incremented, and the PRIMARY KEY constraint is applied to this column.

Q15

Single Choice

How would you define a foreign key in the following MySQL CREATE TABLE statement?

SQL Code

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

REFERENCES customers(customer_id) CONSTRAINT fk_customer PRIMARY KEY (order_id)FOREIGN KEY (customer_id)

The REFERENCES keyword is used to link the customer_id in the orders table to the customer_id in the customers table.

Q16

Single Choice

In MySQL, which of the following correctly adds a unique constraint while creating a table?

CREATE TABLE employees (emp_id INT UNIQUE); CREATE TABLE employees (emp_id INT, UNIQUE emp_id); CREATE employees (emp_id INT, UNIQUE(emp_id));CREATE TABLE employees emp_id INT UNIQUE;

The UNIQUE keyword is correctly placed after the column type in option A, ensuring that all values in emp_id are unique.

Q17

Single Choice

Consider the following CREATE TABLE statement in MySQL:

SQL Code

CREATE TABLE inventory (
 item_id INT,
 item_name VARCHAR(50),
 item_quantity INT CHECK (item_quantity >= 0)
);
What does the CHECK constraint do in this context?

Ensures that item_quantity is always greater than 0. Ensures that item_quantity is always non-negative. Restricts item_quantity to be an integer only.No effect since MySQL does not support CHECK constraints

The CHECK constraint ensures that item_quantity is a non-negative integer. Note that while MySQL ignored CHECK constraints before version 8.0.16, this feature is now supported.

Q18

Single Choice

Which of the following CREATE TABLE statements defines a composite primary key?

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

Option B correctly defines a composite primary key, which includes both order_id and product_id as part of the primary key.

Q19

Single Choice

How does MySQL handle a table creation if a table with the same name already exists, and the CREATE TABLE statement does not include IF NOT EXISTS?

MySQL will overwrite the existing table with the new one MySQL will create a new table with a different name. MySQL will throw an error.MySQL will merge the new table definition with the existing table.

If a table with the same name already exists and IF NOT EXISTS is not specified, MySQL will throw an error, preventing the table from being created.

Q20

Single Choice

What happens when you run the following MySQL CREATE TABLE statement if the database is set to strict SQL mode?

SQL Code

CREATE TABLE transactions (
 transaction_id INT NOT NULL,
 transaction_date DATE,
 amount DECIMAL(10,2) DEFAULT 'abc',
 PRIMARY KEY (transaction_id)
);

The table is created, and the amount column's default value is set to abc. The table is created, but the amount column's default value is set to NULL. The table creation fails with an error.The table is created without setting a default value for amount.

In strict SQL mode, MySQL will throw an error because 'abc' is not a valid decimal value for the amount column. Without strict mode, the table would be created with 0.00 as the default value for amount.

Q21

Multiple Choice

Which SQL command correctly creates a basic table in PostgreSQL with a primary key?

SQL Code

CREATE TABLE employees (
 employee_id SERIAL PRIMARY KEY,
 first_name VARCHAR(50) NOT NULL,
 last_name VARCHAR(50) NOT NULL,
 hire_date DATE NOT NULL
);

CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(50) NOT NULL, hire_date DATE NOT NULL ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, first_name TEXT, last_name TEXT, hire_date DATE ); CREATE TABLE employees ( employee_id SERIAL PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100) );CREATE TABLE employees ( employee_id SERIAL, first_name VARCHAR(50), last_name VARCHAR(50), hire_date DATE );

This command creates an 'employees' table with a primary key on 'employee_id' and includes several columns with specific data types.

Q22

Multiple Choice

Which SQL command is used to create a table with a foreign key constraint in PostgreSQL?

SQL Code

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

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

This command creates an 'orders' table where the 'customer_id' is a foreign key that references the 'customer_id' column in the 'customers' table.

Q23

Multiple Choice

Which SQL command creates a table with a composite primary key in PostgreSQL?

SQL Code

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

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

This command creates an 'order_items' table with a composite primary key composed of 'order_id' and 'product_id'.

Q24

Multiple Choice

Which SQL command correctly creates a table with a UNIQUE constraint on one of its columns?

SQL Code

CREATE TABLE users (
 user_id SERIAL PRIMARY KEY,
 username VARCHAR(50) UNIQUE NOT NULL,
 password VARCHAR(100) NOT NULL
);

CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, password VARCHAR(100) NOT NULL ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, password VARCHAR(100) UNIQUE NOT NULL ); CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, password VARCHAR(100) NOT NULL, UNIQUE (username) );CREATE TABLE users ( user_id SERIAL PRIMARY KEY, username VARCHAR(50), password VARCHAR(100) );

This command creates a 'users' table where the 'username' column must have unique values.

Q25

Multiple Choice

Which SQL command creates a table in PostgreSQL with a CHECK constraint to ensure a column's value is within a specific range?

SQL Code

CREATE TABLE products (
 product_id SERIAL PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,
 price NUMERIC(10, 2) CHECK (price > 0 AND price < 10000)
);

CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price NUMERIC(10, 2) CHECK (price > 0 AND price < 10000) ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(100), price NUMERIC(10, 2) ); CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price NUMERIC(10, 2) NOT NULL CHECK (price > 0) );CREATE TABLE products ( product_id SERIAL PRIMARY KEY, product_name VARCHAR(100) NOT NULL, price NUMERIC(10, 2) NOT NULL );

This command creates a 'products' table with a CHECK constraint to ensure that the 'price' column's value is greater than 0 and less than 10,000.

Q26

Multiple Choice

Which SQL command creates a table with a column that has a default value in PostgreSQL?

SQL Code

CREATE TABLE accounts (
 account_id SERIAL PRIMARY KEY,
 username VARCHAR(50) NOT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, created_at TIMESTAMP ); CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, username VARCHAR(50) NOT NULL, created_at TIMESTAMP DEFAULT NOW() );CREATE TABLE accounts ( account_id SERIAL PRIMARY KEY, username VARCHAR(50), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

This command creates an 'accounts' table where the 'created_at' column automatically uses the current timestamp if no other value is provided.

Q27

Multiple Choice

Which SQL command is used to create a table that automatically deletes rows older than a certain age in PostgreSQL?

SQL Code

CREATE TABLE session_logs (
 session_id SERIAL PRIMARY KEY,
 user_id INT NOT NULL,
 login_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
 CONSTRAINT chk_login_time CHECK (login_time > CURRENT_TIMESTAMP - INTERVAL '1 year')
);

CREATE TABLE session_logs ( session_id SERIAL PRIMARY KEY, user_id INT NOT NULL, login_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, CONSTRAINT chk_login_time CHECK (login_time > CURRENT_TIMESTAMP - INTERVAL '1 year') ); CREATE TABLE session_logs ( session_id SERIAL PRIMARY KEY, user_id INT NOT NULL, login_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE session_logs ( session_id SERIAL PRIMARY KEY, user_id INT NOT NULL, login_time TIMESTAMPTZ );CREATE TABLE session_logs ( session_id SERIAL PRIMARY KEY, user_id INT NOT NULL, login_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, CHECK (login_time > CURRENT_TIMESTAMP - INTERVAL '1 year') );

This command creates a 'session_logs' table with a CHECK constraint ensuring that 'login_time' is within the last year, effectively limiting row age.

Q28

Multiple Choice

Which SQL command creates a partitioned table in PostgreSQL by range?

SQL Code

CREATE TABLE sales (
 sale_id SERIAL PRIMARY KEY,
 sale_date DATE NOT NULL,
 amount NUMERIC(10, 2) NOT NULL
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales ( sale_id SERIAL PRIMARY KEY, sale_date DATE NOT NULL, amount NUMERIC(10, 2) NOT NULL ) PARTITION BY RANGE (sale_date); CREATE TABLE sales ( sale_id SERIAL PRIMARY KEY, sale_date DATE NOT NULL, amount NUMERIC(10, 2) ); CREATE TABLE sales ( sale_id SERIAL PRIMARY KEY, sale_date DATE NOT NULL, amount NUMERIC(10, 2) NOT NULL ) PARTITION BY RANGE (amount);CREATE TABLE sales ( sale_id SERIAL PRIMARY KEY, sale_date DATE, amount NUMERIC(10, 2) NOT NULL );

This command creates a 'sales' table partitioned by range based on the 'sale_date' column, which allows for more efficient querying and management of time-based data.

Q29

Multiple Choice

Which SQL command is used to create a table with a JSONB column in PostgreSQL?

SQL Code

CREATE TABLE events (
 event_id SERIAL PRIMARY KEY,
 event_data JSONB NOT NULL,
 event_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE events ( event_id SERIAL PRIMARY KEY, event_data JSONB NOT NULL, event_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE events ( event_id SERIAL PRIMARY KEY, event_data JSON NOT NULL, event_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE events ( event_id SERIAL PRIMARY KEY, event_data TEXT NOT NULL, event_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP );CREATE TABLE events ( event_id SERIAL PRIMARY KEY, event_data JSONB, event_time TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP );

This command creates an 'events' table where 'event_data' is stored as JSONB, allowing for efficient storage and querying of JSON data.

Q30

Multiple Choice

Which SQL command creates a table with a generated column in PostgreSQL?

SQL Code

CREATE TABLE invoices (
 invoice_id SERIAL PRIMARY KEY,
 amount NUMERIC(10, 2) NOT NULL,
 tax NUMERIC(10, 2) GENERATED ALWAYS AS (amount * 0.15) STORED
);

CREATE TABLE invoices ( invoice_id SERIAL PRIMARY KEY, amount NUMERIC(10, 2) NOT NULL, tax NUMERIC(10, 2) GENERATED ALWAYS AS (amount * 0.15) STORED ); CREATE TABLE invoices ( invoice_id SERIAL PRIMARY KEY, amount NUMERIC(10, 2) NOT NULL, tax NUMERIC(10, 2) AS (amount * 0.15) STORED ); CREATE TABLE invoices ( invoice_id SERIAL PRIMARY KEY, amount NUMERIC(10, 2) NOT NULL, tax NUMERIC(10, 2) GENERATED AS (amount * 0.15) );CREATE TABLE invoices ( invoice_id SERIAL PRIMARY KEY, amount NUMERIC(10, 2) NOT NULL, tax NUMERIC(10, 2) GENERATED ALWAYS AS (amount * 0.15) );

This command creates an 'invoices' table where the 'tax' column is a generated column that calculates 15% of the 'amount' automatically.

PostgreSQL Database Quiz Questions