Ch 5. Structured Query Language

What does it mean to 'manipulate data' in database terminology?

Adding new data, changing the values of existing data, reorganizing the data, and retrieving specific data.

What organization established SQL as an official standard?

The American National Standards Institute (ANSI).

What is a conditional expression in a WHERE clause?

The specific question you want to ask to filter the data.

What do the comparison operators = and <> represent?

= means Equal to; <> means Not equal to.

What are the evaluation rules for the AND, OR, and NOT logical operators?

AND: Displays a record if both conditions are true. OR: Displays a record if either condition is true. NOT: Displays a record if the condition is false.

Why must every SQL statement strictly end with a semicolon ;?

It is required because it explicitly tells the database engine to process everything written before it.

In a relational database layout, how are files, records, and fields organized?

Data is composed of files (tables), files are composed of records (rows), and records are composed of fields (columns).

What four explicit pieces of information must a programmer know to successfully construct a SQL statement?

1. Where the desired data are stored. 2. Specifically what data is desired. 3. What search criteria will return the desired results. 4. Where to store the results.

What is a subquery, and what is its alternative name?

A query nested within a main SQL query used to further refine a result. It is also called a nested query or an INNER SELECT.

What is an INNER JOIN, and what visual term describes its dataset?

The most common simple join; it retrieves rows where a specified condition matches on both tables. Visually, it returns records where the tables intersect (where data is equal).

What unique structural advantage does a FULL OUTER JOIN provide when combining rows?

It returns all rows from both tables, and the combined table will deliberately include rows with null data so absolutely nothing is left out.

Where must the GROUP BY clause be placed when used in a statement?

It must be paired with a SELECT statement and it always precedes the ORDER BY clause (though ORDER BY is not strictly required).

What does a GROUP BY clause structurally require to function?

An Aggregate function (such as count, sum, average, minimum, or maximum).

What are the definitions/examples of DDL, DML, and DCL?

DDL (Data Definition): Handles structure creation (CREATE, ALTER, DROP). DML (Data Manipulation): Handles data updates (INSERT, UPDATE, DELETE). DCL (Data Control): Configures access rights (GRANT, REVOKE).

What are the definitions/examples of Data Administration and Transaction Control commands?

Data Administration: Handles audits and operations (START AUDIT, STOP AUDIT). Transaction Control: Manipulates database transactions (COMMIT, ROLLBACK, SAVEPOINT).

What is the mechanical difference between a SQL query and a SQL script?

A SQL query is a single statement or entity. A SQL script is a sequential list or collection of two or more SQL commands grouped together in a text file to perform a repetitive task.

What are the four major operational benefits of using SQL scripts?

Ease of use, consistent operation (if it works once, it works every time), decrease in human errors, and scheduled operations.

What are the four main practical uses of SQL scripts in business?

Backups (for easy restoration), Reports (summaries for business operations), Periodic updates, and Synchronization (across diverse servers).