Database Design/Construction Commands

Question 1

What is the goal of SQL design-construction commands?

Answer 1

To define and modify the database schema.

Question 2

What SQL command creates a new database?

Answer 2

CREATE DATABASE database_name;

Question 3

What SQL command deletes a database?

Answer 3

DROP DATABASE database_name;

Question 4

What is the basic syntax to create a table?

Answer 4

CREATE TABLE table_name (column1 datatype constraint, …);

Question 5

What is the purpose of constraints in table creation?

Answer 5

To enforce data validity rules like uniqueness, non-null values, or references.

Question 6

How do you define a primary key in CREATE TABLE?

Answer 6

CONSTRAINT pk_name PRIMARY KEY (column_name)

Question 7

How do you define a foreign key in CREATE TABLE?

Answer 7

CONSTRAINT fk_name FOREIGN KEY (column_name) REFERENCES parent_table(parent_column)

Question 8

List four major categories of SQL data types.

Answer 8

Numerical, String, Date-Time, Boolean.

Question 9

Give examples of SQL string data types.

Answer 9

VARCHAR(n), TEXT

Question 10

What is the BOOLEAN type used for?

Answer 10

To store TRUE or FALSE values.

Question 11

What does the PRIMARY KEY constraint enforce?

Answer 11

That column values are unique and not null.

Question 12

What does the FOREIGN KEY constraint do?

Answer 12

It ensures values in the column exist in the referenced table’s primary key.

Question 13

What does NOT NULL constraint enforce?

Answer 13

That a column cannot have NULL values.

Question 14

What does the UNIQUE constraint enforce?

Answer 14

That no duplicate values exist in the column.

Question 15

What does the DEFAULT constraint do?

Answer 15

Assigns a default value if no value is provided.

Question 16

What does the CHECK constraint do?

Answer 16

Ensures a column value satisfies a given condition.

Question 17

Give a syntax example using DEFAULT.

Answer 17

column_name datatype DEFAULT value

Question 18

Give a syntax example using CHECK.

Answer 18

column_name datatype CHECK (condition)

Question 19

What command shows the structure of a table?

Answer 19

DESC table_name;

Question 20

What command adds a column to a table?

Answer 20

ALTER TABLE table_name ADD column_name datatype constraint;

Question 21

How do you add a column at a specific position?

Answer 21

Use AFTER keyword in ADD COLUMN: … ADD COLUMN name AFTER existing_column;

Question 22

What command deletes a column?

Answer 22

ALTER TABLE table_name DROP column_name;

Question 23

What command modifies a column’s type or constraint?

Answer 23

ALTER TABLE table_name MODIFY COLUMN column_name new_type [constraint];

Question 24

What command renames a column?

Answer 24

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

Question 25

Can multiple ALTER operations be combined?

Answer 25

Yes, they can be chained in one ALTER TABLE statement.

Question 26

Give an example of multiple ALTER operations.

Answer 26

ALTER TABLE Employees DROP COLUMN MiddleName, ADD Email VARCHAR(100), MODIFY Department VARCHAR(50);

Question 27

Spot the mistake: 'CREATE TABLE employees (id INT PRIMARY, name VARCHAR(100));'

Answer 27

PRIMARY should be PRIMARY KEY.

Question 28

Spot the mistake: 'ALTER TABLE employees MODIFY name VARCHAR(100);'

Answer 28

Use MODIFY COLUMN instead: ALTER TABLE employees MODIFY COLUMN name VARCHAR(100);

Question 29

If a column must never be empty, what constraint is needed?

Answer 29

NOT NULL

Question 30

If a column must be unique but also optional, what combination of constraints works?

Answer 30

UNIQUE without NOT NULL

Question 31

Explain why constraints are vital in database design.

Answer 31

They prevent bad data from being entered and protect integrity.

Question 32

If you forget the primary key when designing a table, what problems may arise?

Answer 32

Duplicates, difficulty linking records, poor integrity.