SQL Fundamentals – Lecture Review

Question-and-answer flashcards covering key concepts, commands, and best practices from the SQL lecture notes.

What does the acronym SQL stand for?

Structured Query Language

What is the primary purpose of SQL?

Creating, managing, and querying relational databases.

In a client/server architecture, where are SQL commands executed?

On the database server; only results are returned to the client.

Who introduced the relational model and in what year?

E. F. Codd in 1970.

Which IBM research project (1974-1979) proved the feasibility of the relational model?

IBM System R project.

What was the first commercial DBMS to support SQL and in what year?

Oracle in 1979.

When was the first ANSI/ISO SQL standard approved and what was it called?

1986; SQL/86.

Give two major purposes of the SQL standard.

(1) Define SQL syntax and semantics, (2) Enable portability of database definitions and applications.

List two advantages of having an SQL standard.

Lower training costs and greater application portability.

Name one frequently cited disadvantage of a single SQL standard.

It can stifle innovation or be slow to change.

As of June 2020, which three commercial DBMSs dominated the market?

Oracle, MySQL, and Microsoft SQL Server.

In the SQL environment, what is a catalog?

A collection of metadata describing all database objects under the control of an SQL server.

What are the three broad categories of SQL commands?

DDL (Data Definition Language), DML (Data Manipulation Language), and DCL (Data Control Language).

What does DDL do?

Creates, alters, and drops database objects such as schemas, tables, views, and indexes.

What does DML do?

Inserts, updates, deletes, and queries data stored in tables.

What does DCL do?

Grants or revokes user privileges and manages transactions.

Provide the basic syntax for creating a schema in ANSI SQL.

CREATE SCHEMA schemaname AUTHORIZATION owneruserid;

Which SQL command defines a new table?

CREATE TABLE.

Which command permanently deletes a table and all its data?

DROP TABLE.

Which command modifies the structure of an existing table?

ALTER TABLE.

What is the purpose of CREATE CHARACTER SET?

To define a new character set for storing text, supporting multiple languages.

What constraint forces a column to have unique non-NULL values?

PRIMARY KEY (or UNIQUE for candidate keys that may contain NULLs).

Which constraint disallows NULL values in a column?

NOT NULL.

Which clause is used in a child table to enforce referential integrity?

REFERENCES parent_table(column).

Name two ON UPDATE actions for foreign keys.

ON UPDATE RESTRICT and ON UPDATE CASCADE (also ON UPDATE SET NULL).

Name two ON DELETE actions for foreign keys.

ON DELETE RESTRICT and ON DELETE CASCADE (also ON DELETE SET NULL or SET DEFAULT).

How can you duplicate an existing table’s structure without data?

CREATE TABLE newtable LIKE existingtable; (SQL:2008).

What is the purpose of the DUAL table in Oracle/MySQL?

To select system variables or evaluate expressions without referencing a user table.

Which SQL 2008 feature automatically generates unique primary key values?

Identity columns created with GENERATED ALWAYS (or BY DEFAULT) AS IDENTITY.

Which command removes all rows but keeps the table definition?

TRUNCATE TABLE.

What is the correct clause order in a SELECT statement?

SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY.

Which keyword eliminates duplicate rows from a query result?

DISTINCT.

Which wildcard in a LIKE pattern matches any sequence of characters?

% (percent).

Which wildcard in a LIKE pattern matches exactly one character?

_ (underscore).

Give four common SQL comparison operators.

=, <>, >, >= (others include < and <=).

How do you test for missing (NULL) values in SQL?

column_name IS NULL (or IS NOT NULL).

Which Boolean operator is evaluated first in SQL?

NOT (followed by AND, then OR).

Which clause sorts the final result set?

ORDER BY.

Which clause groups rows so aggregate functions can be applied per group?

GROUP BY.

Which clause filters groups after aggregation?

HAVING.

What is the difference between a scalar and a vector aggregate?

Scalar aggregate returns one value for the entire table; vector aggregate returns one value per group when used with GROUP BY.

Which aggregate function returns the average of numeric values?

AVG().

Which function counts all rows, including those with NULLs?

COUNT(*).

Name one analytical or numerical function added in SQL:2008.

LN, EXP, POWER, SQRT, FLOOR, or CEILING (any one).

What is the main performance benefit of creating an index?

Speeds up row retrieval, joins, and ordering by providing rapid access paths.

Give an example of a statement to create an index on CustomerName in Oracle.

CREATE INDEX NameIDX ON CustomerT (CustomerName);

In an identity column definition, which option sets the first generated value?

START WITH n (e.g., START WITH 1).

Name two common ANSI numeric data types.

INTEGER (or INT) and NUMERIC(p,s) / DECIMAL(p,s).

How does NUMERIC(p,s) differ from INTEGER?

NUMERIC stores exact numbers with specified precision and scale; INTEGER stores whole numbers with zero scale.

Why assign meaningful names to constraints instead of letting the system generate them?

To make them easier to identify, manage, and reference during maintenance.

Define referential integrity in relational databases.

A rule ensuring that every foreign-key value in a child table matches a primary-key value in the parent table or is NULL.

In a 1:M relationship, a foreign-key value must match what in the parent table?

A valid primary-key value (or be NULL if allowed).

Why are parentheses important when combining AND and OR conditions?

They control evaluation order, ensuring the intended logical grouping of predicates.

Which operator selects values within an inclusive range?

BETWEEN (e.g., value BETWEEN 10 AND 20).

When DISTINCT is applied to multiple columns, how are duplicates determined?

Rows are removed only if all selected columns have identical values.

What advantage does the IN operator provide when used with subqueries?

Allows comparison against a set of values returned by another SELECT, supporting powerful set-based logic.

What is the purpose of the LIMIT clause (or OFFSET … FETCH)?

To restrict the number of rows returned, often for paging results.

List the internal execution order of major SQL clauses.

1 FROM 2 WHERE 3 GROUP BY 4 HAVING 5 SELECT 6 ORDER BY

Explain how removing optional clauses helps debug a complex SQL query.

By viewing intermediate results after each clause is added back, you can isolate and correct logic or syntax errors.