Lesson 2: SQL Data Definition Language

What is the main purpose of Data Definition Language (DDL) in MySQL?

To create, delete, and modify database structures.

What are the three main DDL statements discussed in the notes?

CREATE, DROP, and ALTER.

Which statement would you use to create a new database?

CREATE DATABASE.

Which statement permanently deletes an existing database?

DROP DATABASE.

What does CREATE TABLE do?

Creates a new table and defines its columns and data types.

What happens when you use DROP TABLE?

The table and all its data are deleted.

When would you use ALTER TABLE?

When you need to modify the structure of an existing table.

What three common structural changes can ALTER TABLE perform?

ADD a column, MODIFY a column, or DROP a column.

Which statement would you use to add a new column to an existing table?

ALTER TABLE … ADD.

Which statement would you use to change a column's data type?

ALTER TABLE … MODIFY.

Which statement would you use to remove a column from a table?

ALTER TABLE … DROP.

What is the purpose of constraints in a database table?

To maintain data accuracy and integrity.

What does the NOT NULL constraint prevent?

It prevents a column from containing NULL values.

What does the UNIQUE constraint ensure?

It prevents duplicate values in a column or group of columns.

What is a PRIMARY KEY used for?

To uniquely identify each record in a table.

What two properties does a PRIMARY KEY combine?

NOT NULL and UNIQUE.

What is the purpose of a FOREIGN KEY?

To link tables and enforce referential integrity.

If tblReceipt.itemId refers to tblItems.itemId, what type of key is tblReceipt.itemId?

A FOREIGN KEY.

What does AUTO_INCREMENT do?

Automatically generates sequential numbers, commonly for primary keys.

Which constraint would you use if every item must have a name?

NOT NULL.

Which constraint would you use to prevent two records from having the same value?

UNIQUE.

Which constraint would you use to give every record a unique identifier?

PRIMARY KEY.

Which constraint would you use to establish a relationship between two tables?

FOREIGN KEY.

Which feature would you use to automatically assign sequential IDs to new records?

AUTO_INCREMENT.

What is the difference between UNIQUE and PRIMARY KEY?

UNIQUE prevents duplicates; PRIMARY KEY uniquely identifies records and is both NOT NULL and UNIQUE.

What is the difference between a PRIMARY KEY and a FOREIGN KEY?

A primary key identifies records in its own table; a foreign key links to a key in another table.

What is the difference between DROP and ALTER?

DROP removes a database, table, or column, while ALTER changes an existing table's structure.

In the example, why is itemId in tblReceipt a foreign key?

It references itemId in tblItems, creating a relationship between the tables.

Why is itemId in tblItems a good candidate for PRIMARY KEY AUTO_INCREMENT?

It uniquely identifies each item and can be automatically numbered.