Database Design-Construction

Question 1

What are the SQL commands to create and delete a database?

Answer 1

CREATE DATABASE database_name; and DROP DATABASE database_name;

Question 2

Write the basic syntax for creating a table with primary and foreign key constraints.

Answer 2

CREATE TABLE table_name (column1 datatype constraint, column2 datatype constraint, CONSTRAINT pk_table_name PRIMARY KEY (column1), CONSTRAINT fk_column2 FOREIGN KEY (column2) REFERENCES parent_table(parent_column));

Question 3

What are the 4 main categories of SQL data types and give 2 examples of each?

Answer 3

1. Numerical: Int, Float, Double 2. String: Varchar(n), Text 3. Date-Time: Date, Time, Datetime, Timestamp 4. Booleans: Boolean

Question 4

When would you use VARCHAR(n) vs TEXT for string data?

Answer 4

VARCHAR(n) for fixed-length strings with known maximum length (more efficient, allows indexing). TEXT for variable-length content with unknown size (emails, descriptions, articles)

Question 5

What does the PRIMARY KEY constraint enforce and what are its two key requirements?

Answer 5

Ensures the column can only hold Unique and non-null values. Requirements: 1. Values must be unique across all rows, 2. Cannot contain NULL values

Question 6

What does a FOREIGN KEY constraint do and how does it maintain data integrity?

Answer 6

Links tables together and ensures the column can only contain values that exist in the referenced column of the linked table, preventing orphaned records and maintaining referential integrity

Question 7

Compare these constraints: NOT NULL, UNIQUE, DEFAULT, CHECK. What does each enforce?

Answer 7

NOT NULL: No empty values. UNIQUE: No duplicate values (but allows NULL). DEFAULT: Sets automatic value if none provided. CHECK: Enforces custom rules/conditions on column values

Question 8

Write a CREATE TABLE statement with a CHECK constraint that ensures age is between 18 and 65.

Answer 8

CREATE TABLE IF NOT EXISTS employees (age INT CHECK (age >= 18 AND age <= 65), name VARCHAR(50));

Question 9

What command shows the structure of an existing table?

Answer 9

DESC table_name

Question 10

Write the SQL to add an email column (VARCHAR 100, NOT NULL) to an existing ‘users’ table after the ‘name’ column.

Answer 10

ALTER TABLE users ADD COLUMN email VARCHAR(100) NOT NULL AFTER name;

Question 11

What’s the advantage of combining multiple ALTER operations? Write an example.

Answer 11

More efficient than separate commands. Example: ALTER TABLE Employees DROP COLUMN MiddleName, ADD COLUMN Email VARCHAR(100) NOT NULL, MODIFY COLUMN Department VARCHAR(50) DEFAULT ‘General’;

Question 12

What are the 4 main ALTER TABLE operations and their basic syntax?

Answer 12

1. ADD: ADD COLUMN name datatype 2. DROP: DROP COLUMN name 3. MODIFY: MODIFY COLUMN name new_datatype 4. RENAME: RENAME COLUMN old_name TO new_name

Question 13

You’re creating a ‘products’ table with: product_id, name, price, category. Which constraints would you apply and why?

Answer 13

product_id: PRIMARY KEY (unique identifier), name: NOT NULL + UNIQUE (must exist, no duplicates), price: NOT NULL + CHECK (price > 0), category: DEFAULT ‘General’ (fallback value)

Question 14

Design tables for a library system: books and authors (many-to-many relationship). Show the table structures with appropriate constraints.

Answer 14

CREATE TABLE authors (author_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL); CREATE TABLE books (book_id INT PRIMARY KEY, title VARCHAR(200) NOT NULL); CREATE TABLE book_authors (book_id INT, author_id INT, FOREIGN KEY (book_id) REFERENCES books(book_id), FOREIGN KEY (author_id) REFERENCES authors(author_id));