INFORMATION MANAGEMENT

The ______ Statement is used to create a database.

CREATE DATABASE

The ________ Statement is used to create tables to store data.

CREATE TABLE

The SQL ________ command is used to modify the definition (structure) of a table by modifying the definition of its columns

ALTER TABLE

The SQL _____ command is used to change the name of the table or a database object.

RENAME

This command can delete an index, table or

view.

DROP

Using the ______ command, all the records in a database are deleted, but the database structure is maintained.

TRUNCATE

A subset of SQL used to manipulate and manage data within database tables.

Data Manipulation Language (DML)

A command used to insert new records (rows) into a table.

INSERT Statement

Used to modify existing records in a table.

UPDATE Statement

Removes existing rows from a table based on a condition.

DELETE Statement

the process of improving the efficiency of database queries to ensure they run faster and use fewer system resources.

Query Optimization

A query approach where the user specifies how the database should retrieve the data, not just what data is needed.

Procedural Query

A query approach where the user specifies what data is needed, without describing how to get it (e.g., SQL SELECT).

Declarative Query

What is the difference between Procedural and Declarative Queries

• Declarative Query: Focuses on what data is needed (e.g., SQL SELECT).

• Procedural Query: Focuses on how to retrieve data step by step (e.g., Relational Algebra).

A procedural query language used in relational databases. It defines a set of operations that take relations (tables) as input and return a new relation as output.

Relational Algebra

An operation to select rows (tuples) that satisfy a given condition.

Select/Selection (σ) sigma

An operation to display specific columns from a relation.

Projection (π) pi

A virtual table in SQL that displays data from one or more tables based on a query. They do not store data; they show results dynamically when queried.

View

Types of SQL Joins

• INNER JOIN

• LEFT JOIN (LEFT OUTER JOIN)

• RIGHT JOIN (RIGHT OUTER JOIN)

• FULL JOIN (FULL OUTER JOIN)

Ensures that relationships between tables remain consistent, typically enforced using foreign keys.

Referential Integrity

When a referenced record is deleted, all dependent records are automatically deleted.

ON DELETE CASCADE

When a referenced record is updated, all dependent records are automatically updated.

ON UPDATE CASCADE

When a referenced record is deleted, foreign key values are set to NULL.

SET NULL

Prevents deletion or update of a referenced record if dependent records exist.

RESTRICT

Same as RESTRICT; no change is allowed if it would break referential integrity.

NO ACTION