SQL Create and Drop Statements Quiz

1. Which SQL statement is used to create a new table in a database?

CREATE TABLE

INSERT TABLE

MAKE TABLE

NEW TABLE

The SQL statement "CREATE TABLE" is specifically designed to define and create a new table within a database. It allows users to specify the table's structure, including column names and data types, making it essential for database schema design and organization. Other options do not serve this purpose in SQL.

Explanation

The SQL statement "CREATE TABLE" is specifically designed to define and create a new table within a database. It allows users to specify the table's structure, including column names and data types, making it essential for database schema design and organization. Other options do not serve this purpose in SQL.

2. What does the DROP statement do?

Removes a table and all its data from the database

Deletes only the data but keeps the table structure

Temporarily disables a table

Moves a table to another database

The DROP statement is a SQL command used to permanently remove a table from a database, including all associated data and structure. Once executed, the table and its contents cannot be recovered, making it essential to use this command with caution.

Explanation

The DROP statement is a SQL command used to permanently remove a table from a database, including all associated data and structure. Once executed, the table and its contents cannot be recovered, making it essential to use this command with caution.

3. In a CREATE TABLE statement, what does PRIMARY KEY do?

Uniquely identifies each row and prevents NULL values

Sorts the table automatically

Creates a backup of the table

Encrypts sensitive data

A PRIMARY KEY in a CREATE TABLE statement ensures that each row in the table is unique, allowing for reliable identification of records. It also enforces the rule that no NULL values can be present in the primary key column, maintaining data integrity and consistency within the database.

Explanation

A PRIMARY KEY in a CREATE TABLE statement ensures that each row in the table is unique, allowing for reliable identification of records. It also enforces the rule that no NULL values can be present in the primary key column, maintaining data integrity and consistency within the database.

4. Which constraint ensures that all values in a column are unique?

UNIQUE

DISTINCT

PRIMARY

EXCLUSIVE

The UNIQUE constraint in a database ensures that all values in a specified column are distinct from one another. This means no two rows can have the same value in that column, thereby maintaining data integrity and preventing duplicate entries. It is commonly used to enforce uniqueness in fields like email addresses or usernames.

Explanation

The UNIQUE constraint in a database ensures that all values in a specified column are distinct from one another. This means no two rows can have the same value in that column, thereby maintaining data integrity and preventing duplicate entries. It is commonly used to enforce uniqueness in fields like email addresses or usernames.

5. What is the purpose of a FOREIGN KEY constraint?

Links a column to the primary key of another table

Prevents duplicate entries in a table

Automatically generates unique IDs

Encrypts data between tables

A FOREIGN KEY constraint establishes a relationship between two tables by linking a column in one table to the primary key of another. This ensures referential integrity, meaning that the values in the foreign key column must match existing values in the primary key column, thereby maintaining consistent and valid data across related tables.

Explanation

A FOREIGN KEY constraint establishes a relationship between two tables by linking a column in one table to the primary key of another. This ensures referential integrity, meaning that the values in the foreign key column must match existing values in the primary key column, thereby maintaining consistent and valid data across related tables.

6. Which DDL command modifies the structure of an existing table?

ALTER TABLE

UPDATE TABLE

CHANGE TABLE

MODIFY DATABASE

The ALTER TABLE command is used in SQL to modify the structure of an existing table. This includes adding or deleting columns, changing data types, and setting constraints. It allows for flexible adjustments to a table's schema without needing to recreate it, making it essential for database management and design.

Explanation

The ALTER TABLE command is used in SQL to modify the structure of an existing table. This includes adding or deleting columns, changing data types, and setting constraints. It allows for flexible adjustments to a table's schema without needing to recreate it, making it essential for database management and design.

7. What does NOT NULL constraint guarantee?

Every row must have a value in that column

Values must be numeric

Values cannot be duplicated

Values are automatically encrypted

The NOT NULL constraint ensures that a specified column in a database table cannot have a NULL value. This means that every row in the table must contain a valid entry for that column, thereby enforcing data integrity and ensuring that essential information is always present.

Explanation

The NOT NULL constraint ensures that a specified column in a database table cannot have a NULL value. This means that every row in the table must contain a valid entry for that column, thereby enforcing data integrity and ensuring that essential information is always present.

8. In SQL, DROP TABLE removes the table structure and its data. True or False?

True

False

DROP TABLE in SQL is a command used to delete an entire table from the database, including its structure and all the data it contains. Once executed, the table and its contents cannot be recovered, making it a permanent action. Therefore, the statement is true.

Explanation

DROP TABLE in SQL is a command used to delete an entire table from the database, including its structure and all the data it contains. Once executed, the table and its contents cannot be recovered, making it a permanent action. Therefore, the statement is true.

9. Which keyword is used to set a default value for a column?

DEFAULT

INITIAL

PRESET

STANDARD

The keyword "DEFAULT" is used in SQL to specify a default value for a column when no value is provided during record insertion. This ensures that the column has a predefined value, enhancing data integrity and simplifying data entry by avoiding null entries.

Explanation

The keyword "DEFAULT" is used in SQL to specify a default value for a column when no value is provided during record insertion. This ensures that the column has a predefined value, enhancing data integrity and simplifying data entry by avoiding null entries.

10. What is the difference between DROP and DELETE in SQL?

DROP removes the table structure; DELETE removes only data

DELETE removes the table structure; DROP removes only data

They perform the same function

DROP is for databases; DELETE is for tables

DROP and DELETE serve different purposes in SQL. DROP permanently removes the entire table structure and its data from the database, whereas DELETE only removes the data within the table while keeping the structure intact. This distinction is crucial for data management and database design.

Explanation

DROP and DELETE serve different purposes in SQL. DROP permanently removes the entire table structure and its data from the database, whereas DELETE only removes the data within the table while keeping the structure intact. This distinction is crucial for data management and database design.

11. The CREATE INDEX statement creates an index to speed up data ____.

The CREATE INDEX statement enhances the performance of database queries by allowing faster access to rows in a table. By creating an index on one or more columns, the database can quickly locate the data without scanning the entire table, thereby improving the efficiency of data retrieval operations.

Explanation

The CREATE INDEX statement enhances the performance of database queries by allowing faster access to rows in a table. By creating an index on one or more columns, the database can quickly locate the data without scanning the entire table, thereby improving the efficiency of data retrieval operations.

Submit

12. A table can have multiple PRIMARY KEY constraints. True or False?

True

False

A table can only have one PRIMARY KEY constraint, which uniquely identifies each record. This constraint ensures that no two rows can have the same key value, maintaining data integrity. While a table can have multiple UNIQUE constraints, only one PRIMARY KEY can be defined to enforce this uniqueness.

Explanation

A table can only have one PRIMARY KEY constraint, which uniquely identifies each record. This constraint ensures that no two rows can have the same key value, maintaining data integrity. While a table can have multiple UNIQUE constraints, only one PRIMARY KEY can be defined to enforce this uniqueness.