SQL and Database Manipulation

Introduction

Structured Query Language (SQL) is the principal language for interacting with relational database management systems (RDBMSs). It enables users and applications to define database structures, insert and modify data, retrieve information, enforce integrity, and present derived views of data. Although SQL is often introduced as a query language, it is more accurately understood as a family of declarative languages covering data definition, manipulation, control, and transaction management.

SQL is declarative: the user specifies what result is required rather than the exact algorithm for obtaining it. The database optimizer determines an execution plan using indexes, statistics, join strategies, and access methods. This separation between logical intention and physical execution is a major reason for the durability and portability of relational systems.

This chapter develops SQL from foundational statements to advanced querying techniques, including joins, subqueries, set operations, aggregation, views, common table expressions, window functions, constraints, and contemporary capabilities. Examples use a university database with the following conceptual relations:

• `Student(student_id, student_name, programme, year_level)`
• `Course(course_id, title, credits, department_id)`
• `Department(department_id, department_name)`
• `Enrollment(student_id, course_id, semester, mark)`
• `Instructor(instructor_id, instructor_name, department_id)`

SQL syntax varies among PostgreSQL, MySQL, SQL Server, Oracle, and other systems. The examples use broadly standard SQL; vendor-specific features should be checked against the relevant documentation.

Learning Objectives

By the end of this chapter, a student should be able to:

1. Explain SQL’s declarative nature and major language categories.

2. Create tables and enforce integrity using constraints.

3. Retrieve data using filtering, projection, sorting, joins, subqueries, and set operations.

4. Modify data safely using `INSERT`, `UPDATE`, `DELETE`, and transaction controls.

5. Use aggregation, grouping, views, common table expressions, and window functions.

6. Analyse null values, duplicate rows, and three-valued logic.

7. Evaluate SQL solutions for correctness, maintainability, security, and performance.

SQL Language Categories

SQL statements are commonly grouped as follows:

• Data Definition Language (DDL): `CREATE`, `ALTER`, and `DROP` define schemas and database objects.
• Data Manipulation Language (DML): `INSERT`, `UPDATE`, `DELETE`, and `MERGE` change stored data.
• Data Query Language (DQL): `SELECT` retrieves data. Some classifications treat `SELECT` as part of DML.
• Data Control Language (DCL): `GRANT` and `REVOKE` manage privileges.
• Transaction Control Language (TCL): `COMMIT`, `ROLLBACK`, and savepoints control transaction boundaries.

A practical SQL workflow separates schema design, controlled data modification, and read-only reporting. Production applications should also use parameterized statements, least-privilege accounts, and explicit transaction boundaries.

Defining Tables and Constraints

A table definition specifies attributes, data types, and integrity rules. For example:

```sql

CREATE TABLE Department (

department_id INTEGER PRIMARY KEY,

department_name VARCHAR(100) NOT NULL UNIQUE

);

CREATE TABLE Student (

student_id INTEGER PRIMARY KEY,

student_name VARCHAR(120) NOT NULL,

programme VARCHAR(100) NOT NULL,

year_level INTEGER NOT NULL CHECK (year_level BETWEEN 1 AND 8)

);

CREATE TABLE Course (

course_id VARCHAR(12) PRIMARY KEY,

title VARCHAR(150) NOT NULL,

credits INTEGER NOT NULL CHECK (credits > 0),

department_id INTEGER NOT NULL,

FOREIGN KEY (department_id)

REFERENCES Department(department_id)

);

CREATE TABLE Enrollment (

student_id INTEGER NOT NULL,

course_id VARCHAR(12) NOT NULL,

semester VARCHAR(20) NOT NULL,

mark DECIMAL(5,2),

PRIMARY KEY (student_id, course_id, semester),

FOREIGN KEY (student_id) REFERENCES Student(student_id),

FOREIGN KEY (course_id) REFERENCES Course(course_id),

CHECK (mark IS NULL OR mark BETWEEN 0 AND 100)

);

```

A domain is the set of permissible values for an attribute. SQL data types implement approximate domains such as integers, character strings, dates, timestamps, decimals, Boolean values, JSON documents, and binary data. Choosing an appropriate type prevents invalid representation and can improve storage and performance. Monetary values, for example, should generally use exact decimal types rather than floating-point types.

A primary key uniquely identifies each row and cannot contain `NULL`. A foreign key requires a value to match a candidate key in a referenced table, unless the foreign-key column is nullable. A unique constraint prevents duplicate non-null values; treatment of multiple nulls varies across database products. `NOT NULL` prevents missing values, while `CHECK` expresses row-level validity conditions.

Referential actions determine what happens when a referenced row is updated or deleted. Options include `RESTRICT`, `NO ACTION`, `CASCADE`, `SET NULL`, and `SET DEFAULT`, subject to DBMS support. Cascading deletion may be appropriate for dependent records, but it can also cause unexpectedly large deletions. Referential actions should therefore reflect business rules rather than convenience.

Constraints are preferable to relying solely on application code because all clients—applications, scripts, and administrators—are then subject to the same integrity rules. However, a `CHECK` constraint usually evaluates one row at a time; cross-row or cross-table rules may require triggers, assertions where supported, transactions, or application-level workflows.

Retrieving Data with SELECT

The basic form is:

```sql

SELECT column_list

FROM table_name

WHERE condition

ORDER BY expression;

```

For example:

```sql

SELECT student_id, student_name

FROM Student

WHERE programme = 'Artificial Intelligence'

ORDER BY student_name;

```

`SELECT *` returns all columns, but explicit column lists are usually safer because they document intent and avoid unexpected results when a table changes. Column aliases improve readability:

```sql

SELECT student_name AS name, year_level AS level

FROM Student;

```

SQL uses `DISTINCT` to eliminate duplicate result rows:

```sql

SELECT DISTINCT programme

FROM Student;

```

Duplicate elimination can require sorting or hashing, so it should not be used merely to conceal an incorrectly constructed join.

Logical Query Processing

Although SQL is written beginning with `SELECT`, its conceptual processing order is generally:

1. `FROM` and joins

2. `WHERE`

3. `GROUP BY`

4. `HAVING`

5. `SELECT`

6. `DISTINCT`

7. `ORDER BY`

8. `FETCH` or `LIMIT`

This explains why a `SELECT` alias often cannot be referenced in `WHERE`, and why aggregate conditions belong in `HAVING` rather than `WHERE`. Optimizers may physically execute operations in another order while preserving the specified result.

Filtering operators include comparison operators, `AND`, `OR`, `NOT`, `BETWEEN`, `IN`, `LIKE`, and `IS NULL`:

```sql

SELECT course_id, title

FROM Course

WHERE credits IN (3, 4)

AND title LIKE '%Data%';

```

`LIKE` commonly uses `%` for any sequence of characters and `_` for one character. Pattern syntax and case sensitivity can vary by DBMS and collation.

Null Values and Three-Valued Logic

`NULL` represents an absent, unknown, or inapplicable value; it is not the same as zero, an empty string, or false. Comparisons involving `NULL` generally evaluate to `UNKNOWN`. SQL’s three-valued logic therefore has `TRUE`, `FALSE`, and `UNKNOWN`. The `WHERE` clause returns only rows for which its condition is `TRUE`.

Incorrect:

```sql

WHERE mark = NULL

```

Correct:

```sql

WHERE mark IS NULL

```

`COALESCE(mark, 0)` returns the first non-null expression, but replacing an unknown mark by zero changes its meaning and should be done only when justified. `COUNT(*)` counts rows, whereas `COUNT(mark)` counts only non-null marks. This distinction is important in reporting.

Joins

A join combines rows from relations according to a matching condition. An inner join returns only matching rows:

```sql

SELECT s.student_name, e.course_id, e.mark

FROM Student AS s

JOIN Enrollment AS e

ON s.student_id = e.student_id;

```

A multi-table join can add course information:

```sql

SELECT s.student_name, c.title, e.semester, e.mark

FROM Student AS s

JOIN Enrollment AS e ON e.student_id = s.student_id

JOIN Course AS c ON c.course_id = e.course_id;

```

A left outer join preserves every row from the left table, supplying nulls when no match exists:

```sql

SELECT s.student_id, s.student_name, e.course_id

FROM Student AS s

LEFT JOIN Enrollment AS e

ON e.student_id = s.student_id;

```

This is useful for finding students with no enrollment:

```sql

SELECT s.student_id, s.student_name

FROM Student AS s

LEFT JOIN Enrollment AS e

ON e.student_id = s.student_id

WHERE e.student_id IS NULL;

```

Moving a condition from `ON` to `WHERE` can change an outer join into an effective inner join. To retain students while restricting enrollments to a semester, place the restriction in `ON`:

```sql

SELECT s.student_name, e.course_id

FROM Student AS s

LEFT JOIN Enrollment AS e

ON e.student_id = s.student_id

AND e.semester = '2025-S1';

```

A self-join joins a table to itself, such as comparing students in the same programme. A cross join produces every combination of rows and should be used deliberately because its size is the product of the input row counts. A natural join relies on same-named columns and is generally avoided in production because schema changes can alter its meaning.

Subqueries and Set Operations

A subquery is a query nested inside another statement. A scalar subquery returns one value; a row or table subquery returns one or more rows. For example:

```sql

SELECT student_name

FROM Student

WHERE student_id IN (

SELECT student_id

FROM Enrollment

WHERE course_id = 'AI201'

);

```

`EXISTS` tests whether at least one related row exists and is often clearer for existence questions:

```sql

SELECT s.student_name

FROM Student AS s

WHERE EXISTS (

SELECT 1

FROM Enrollment AS e

WHERE e.student_id = s.student_id

);

```

The inner query may refer to the outer query, producing a correlated subquery. Conceptually, it is evaluated in relation to each outer row, although an optimizer may transform it into a join or semi-join.

Set operations combine compatible query results. `UNION` removes duplicates, while `UNION ALL` preserves them and is usually more efficient. `INTERSECT` returns rows appearing in both results, and `EXCEPT` returns rows in the first result but not the second, subject to DBMS support.

```sql

SELECT student_id FROM Enrollment WHERE course_id = 'AI201'

INTERSECT

SELECT student_id FROM Enrollment WHERE course_id = 'DB301';

```

Corresponding queries must have the same number of columns, with compatible data types. Column names in the final result generally come from the first query.

Aggregation and Grouping

Aggregate functions summarize multiple rows. Common functions include `COUNT`, `SUM`, `AVG`, `MIN`, and `MAX`:

```sql

SELECT course_id,

COUNT(*) AS enrollment_count,

AVG(mark) AS average_mark

FROM Enrollment

WHERE mark IS NOT NULL

GROUP BY course_id;

```

Every selected expression must ordinarily be aggregated or functionally determined by the `GROUP BY` columns. `HAVING` filters groups after aggregation:

```sql

SELECT course_id, COUNT(*) AS enrollment_count

FROM Enrollment

GROUP BY course_id

HAVING COUNT(*) >= 30;

```

`WHERE` filters rows before grouping; `HAVING` filters groups after grouping. A useful conditional aggregation pattern is:

```sql

SELECT course_id,

COUNT(*) AS attempts,

SUM(CASE WHEN mark >= 50 THEN 1 ELSE 0 END) AS passes

FROM Enrollment

GROUP BY course_id;

```

The pass rate, when attempts is nonzero, can be computed as `passes / attempts`. In SQL, integer division may truncate, so multiplying by a decimal value or casting is often necessary:

```sql

100.0 * SUM(CASE WHEN mark >= 50 THEN 1 ELSE 0 END)

/ NULLIF(COUNT(*), 0) AS pass_rate

```

`NULLIF` prevents division by zero. The expression assumes that a passing mark is at least 50 and that each row represents one attempt; those are business assumptions, not universal SQL rules.

Data Modification and Transactions

Rows can be inserted individually or from a query:

```sql

INSERT INTO Student (student_id, student_name, programme, year_level)

VALUES (1001, 'Amina Rahman', 'Artificial Intelligence', 2);

```

Updates must include a carefully tested `WHERE` clause:

```sql

UPDATE Student

SET year_level = year_level + 1

WHERE student_id = 1001;

```

Without `WHERE`, every row is updated. Likewise, an unqualified `DELETE` removes all rows from a table:

```sql

DELETE FROM Enrollment

WHERE student_id = 1001

AND course_id = 'AI201';

```

A transaction groups related operations into an atomic unit:

```sql

BEGIN;

UPDATE Enrollment

SET mark = 78

WHERE student_id = 1001 AND course_id = 'AI201' AND semester = '2025-S1';

-- inspect affected rows

COMMIT;

```

If validation fails, use `ROLLBACK`. Transactions support the ACID properties: atomicity, consistency, isolation, and durability. Isolation levels differ among systems and balance concurrency against phenomena such as dirty reads, non-repeatable reads, and phantom reads. A transaction should be short enough to reduce lock contention but complete enough to preserve the business operation.

Modern systems may support `MERGE` or vendor-specific upsert syntax for insert-or-update behavior. Such statements require careful handling of duplicate matches, concurrency, and database-specific semantics.

Views and Common Table Expressions

A view is a stored query presented as a virtual table:

```sql

CREATE VIEW StudentResults AS

SELECT s.student_id, s.student_name, c.course_id, c.title,

e.semester, e.mark

FROM Student AS s

JOIN Enrollment AS e ON e.student_id = s.student_id

JOIN Course AS c ON c.course_id = e.course_id;

```

Views can simplify recurring queries, provide column-level abstraction, and restrict access to sensitive attributes. An ordinary view stores the definition, not necessarily the result. A materialized view stores results and must be refreshed; it can accelerate reporting but introduces freshness and maintenance concerns. Whether a view is updatable depends on its structure and DBMS rules.

A common table expression (CTE) names a query within one statement:

```sql

WITH CourseAverages AS (

SELECT course_id, AVG(mark) AS average_mark

FROM Enrollment

WHERE mark IS NOT NULL

GROUP BY course_id

)

SELECT c.title, a.average_mark

FROM CourseAverages AS a

JOIN Course AS c ON c.course_id = a.course_id

WHERE a.average_mark >= 70;

```

CTEs improve readability and can decompose complex logic. A recursive CTE can traverse hierarchical data, such as an organizational structure or prerequisite graph, using an anchor query and a recursive query. Recursive queries require termination conditions and protection against cycles. A CTE is not automatically a performance optimization; some systems inline it, while others materialize it.

Window Functions

A window function computes across related rows while retaining one output row per input row. Its general form is:

```sql

function(...) OVER (

PARTITION BY grouping_columns

ORDER BY ordering_columns

frame_specification

)

```

For example, rank students by mark within each course:

```sql

SELECT course_id, student_id, mark,

RANK() OVER (

PARTITION BY course_id

ORDER BY mark DESC

) AS course_rank

FROM Enrollment

WHERE mark IS NOT NULL;

```

`RANK` assigns equal ranks to ties and leaves gaps after ties. `DENSE_RANK` does not leave gaps, while `ROW_NUMBER` assigns a distinct sequential number even when values tie. A deterministic secondary ordering should be supplied when reproducibility matters:

```sql

ROW_NUMBER() OVER (

PARTITION BY course_id

ORDER BY mark DESC, student_id

)

```

A running total can be calculated as:

```sql

SELECT student_id, semester, mark,

SUM(mark) OVER (

PARTITION BY student_id

ORDER BY semester

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

) AS cumulative_mark

FROM Enrollment;

```

The frame is important. `ROWS` counts physical rows, whereas `RANGE` groups rows with equal ordering values in ways that can produce different results. Window functions are evaluated after grouping and aggregation in the logical processing model, and they generally cannot be used directly in `WHERE`; a CTE or derived table can filter their result.

A Detailed Illustrative Case: University Progress Dashboard

Background

A university wants a dashboard showing course participation, average marks, pass rates, and the highest-performing students. Faculty members need course-level summaries, while advisers need to identify students with no enrollment or consistently weak results.

Problem

A first attempt used several separate application queries. It produced inconsistent definitions of “pass,” counted null marks incorrectly, and displayed duplicate students when multiple enrollments existed.

Analysis and Approach

The data team defined a pass as a non-null mark of at least 50. Because an enrollment row represents one student-course-semester attempt, course summaries can use conditional aggregation. A CTE makes the summary reusable:

```sql

WITH CourseSummary AS (

SELECT course_id,

COUNT(*) AS attempts,

COUNT(mark) AS marked_attempts,

SUM(CASE WHEN mark >= 50 THEN 1 ELSE 0 END) AS passes,

AVG(mark) AS average_mark

FROM Enrollment

GROUP BY course_id

)

SELECT c.course_id,

c.title,

cs.attempts,

cs.marked_attempts,

cs.passes,

cs.average_mark,

100.0 * cs.passes / NULLIF(cs.marked_attempts, 0) AS pass_rate

FROM CourseSummary AS cs

JOIN Course AS c ON c.course_id = cs.course_id;

```

The dashboard also uses a window function to identify the top three marked attempts in each course:

```sql

WITH RankedMarks AS (

SELECT course_id, student_id, mark,

ROW_NUMBER() OVER (

PARTITION BY course_id

ORDER BY mark DESC, student_id

) AS position

FROM Enrollment

WHERE mark IS NOT NULL

)

SELECT course_id, student_id, mark

FROM RankedMarks

WHERE position <= 3;

```

Outcome

The resulting reports distinguish enrolled students from students with recorded marks, use a documented pass definition, and avoid duplicate output caused by careless joins. Access can be provided through views that expose student identifiers but conceal unnecessary personal information.

Learning Points

The case demonstrates that SQL correctness depends on understanding data semantics, not merely syntax. Null handling, row grain, tie-breaking, and business definitions must be made explicit. A dashboard query should also be tested against empty courses, duplicate attempts, ties, missing marks, and students without enrollments.

Performance, Security, and Professional Practice

SQL correctness comes before optimization, but performance matters at scale. Indexes can accelerate joins, searches, and ordering, especially on primary keys, foreign keys, and frequently filtered columns. They also consume storage and slow writes. Composite indexes should reflect common predicates and column order. The actual benefit should be assessed with execution plans and representative data.

Avoid retrieving unnecessary columns or rows. Filter early when semantically valid, but do not sacrifice correctness by moving predicates across outer joins. Avoid functions on indexed columns when they prevent index use, unless a functional index is available. Use `EXPLAIN` or the DBMS’s plan-analysis tools rather than assuming a query is efficient.

SQL injection occurs when untrusted input is concatenated into SQL text. Parameterized queries or prepared statements treat user input as data:

```text

SELECT ... WHERE student_id = ?

```

The application binds a value to `?`; it does not construct SQL by string concatenation. Least privilege, role-based access, auditing, encryption, secure backups, and controlled administrative access are complementary safeguards. Views can reduce exposure, but they are not a substitute for comprehensive authorization design.

Personal, academic, and biometric data may be regulated by privacy laws and institutional policy. Data minimization, purpose limitation, retention controls, access logging, correction procedures, and appropriate de-identification are important governance practices. In AI and ML systems, SQL pipelines influence training data; unnoticed duplicates, leakage from future records, or biased filtering can produce invalid or unfair models. Database provenance and reproducible transformations are therefore part of responsible AI practice.

Contemporary SQL Capabilities and Limitations

Modern relational systems increasingly support JSON, arrays, spatial data, full-text search, temporal querying, generated columns, recursive queries, lateral joins, and analytical functions. These capabilities can reduce the need for separate processing systems, but portability and data modeling concerns remain. JSON is useful for genuinely variable or semi-structured attributes; placing all data in JSON can weaken constraints, indexing, and relational clarity.

SQL is powerful but not universally ideal. Highly connected graph traversal, massive unstructured documents, or ultra-low-latency key-value access may be better served by specialized systems. Polyglot architectures introduce synchronization, governance, and operational complexity. The appropriate design depends on data relationships, consistency requirements, workload, scale, and organizational capability.

Best Practices

1. Define keys, domains, and integrity constraints explicitly.

2. Use clear aliases and explicit column lists.

3. Treat null semantics and duplicate behavior as design decisions.

4. Distinguish row filtering (`WHERE`) from group filtering (`HAVING`).

5. Test outer joins carefully, especially predicate placement.

6. Use transactions for logically atomic changes.

7. Parameterize all externally supplied values.

8. Document business assumptions such as pass thresholds and row grain.

9. Inspect execution plans for important queries.

10. Test boundary cases: empty sets, nulls, ties, duplicates, and missing relationships.

11. Apply least privilege and protect personal information.

12. Prefer maintainable SQL over opaque, prematurely optimized SQL.

Chapter Summary

SQL is a declarative language for defining, querying, modifying, and controlling relational data. DDL creates tables and constraints; DML changes rows; `SELECT` retrieves and transforms data; transactions provide controlled atomic changes. Primary keys, foreign keys, unique constraints, `NOT NULL`, and `CHECK` constraints protect integrity.

Joins combine related relations, while outer joins preserve unmatched rows. Subqueries and set operations express membership and relational comparison. Aggregation summarizes groups, with `WHERE` filtering rows and `HAVING` filtering groups. Views provide abstraction and controlled access, CTEs organize complex or recursive queries, and window functions perform rankings and running calculations without collapsing rows.

Professional SQL practice requires careful treatment of nulls, duplicates, transactions, performance, security, privacy, and business semantics. Contemporary SQL supports analytical, semi-structured, temporal, and recursive workloads, but selection of technology should follow workload and governance requirements.

Key Terms

SQL: Declarative language for managing relational data.

DDL: SQL statements that define database objects.

DML: SQL statements that insert, update, or delete data.

Primary key: Attribute or attribute set that uniquely identifies a row.

Foreign key: Attribute referencing a key in another table.

Constraint: Rule enforced by the DBMS to protect data validity.

Null: Marker representing missing, unknown, or inapplicable information.

Join: Operation combining rows from related tables.

Outer join: Join that preserves unmatched rows from one or both inputs.

Subquery: Query nested within another SQL statement.

Set operation: Operation such as `UNION`, `INTERSECT`, or `EXCEPT` combining query results.

Aggregate function: Function that summarizes multiple rows.

View: Named query presented as a virtual table.

CTE: Common table expression that names an intermediate query result.

Window function: Function computing across related rows while retaining row detail.

Transaction: Logical unit of database work.

ACID: Atomicity, consistency, isolation, and durability properties.

SQL injection: Attack caused by treating untrusted input as executable SQL.

Query optimizer: DBMS component that selects an execution strategy.

Referential integrity: Requirement that foreign-key references remain valid.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?

2. Why must `IS NULL` be used instead of `= NULL`?

3. Distinguish `WHERE` from `HAVING`.

4. What does a left outer join preserve?

5. State one difference between `UNION` and `UNION ALL`.

Long Answer Questions

1. Explain SQL’s logical query processing order and its significance for writing correct queries.

2. Discuss the major integrity constraints and illustrate how they apply to a university database.

3. Compare ordinary views, materialized views, and common table expressions.

4. Explain transactions and the ACID properties, including why transaction boundaries matter.

5. Describe window functions and compare `ROW_NUMBER`, `RANK`, and `DENSE_RANK`.

Analytical / Application Questions

1. Design a query that lists every student and the number of courses they have taken, including students with zero enrollments. Explain why your join and aggregation are correct.

2. A report shows a pass rate of 100% for a course with many null marks. Diagnose the likely SQL error and propose a corrected calculation.

3. An application builds SQL by concatenating a user-entered student identifier. Analyse the security risk and recommend database and application controls.

Multiple Choice Questions with Answers

Question 1

Which constraint uniquely identifies each row in a table?

A. Foreign key

B. Primary key

C. Check constraint

D. View

Correct Answer: B. Primary key

Question 2

Which clause filters groups after aggregation?

A. WHERE

B. ORDER BY

C. HAVING

D. FROM

Correct Answer: C. HAVING

Question 3

What does a left outer join preserve?

A. Only matching rows from both tables

B. Every row from the left table

C. Every row from the right table only

D. Only rows containing no nulls

Correct Answer: B. Every row from the left table

Question 4

Which expression correctly tests whether mark is missing?

A. mark = NULL

B. mark <> NULL

C. mark IS NULL

D. mark == NULL

Correct Answer: C. mark IS NULL

Question 5

Which set operation preserves duplicate rows?

A. UNION

B. INTERSECT

C. EXCEPT

D. UNION ALL

Correct Answer: D. UNION ALL

Question 6

What is the main characteristic of a window function?

A. It always deletes duplicate rows

B. It retains individual rows while calculating across related rows

C. It can only be used in table definitions

D. It replaces all joins

Correct Answer: B. It retains individual rows while calculating across related rows

Question 7

Which statement permanently saves the successful changes of a transaction?

A. ROLLBACK

B. CHECKPOINT

C. COMMIT

D. REVOKE

Correct Answer: C. COMMIT

Question 8

Which function counts rows including rows whose mark is NULL?

A. COUNT(mark)

B. COUNT(*)

C. AVG(mark)

D. SUM(mark)

Correct Answer: B. COUNT(*)

Question 9

What is the safest standard approach for incorporating user input into SQL?

A. String concatenation

B. Parameterized queries

C. Removing all spaces

D. Using SELECT *

Correct Answer: B. Parameterized queries

Question 10

Which object provides a named, reusable query definition that is generally virtual?

A. View

B. Primary key

C. Index scan

D. Savepoint

Correct Answer: A. View