SQL Commands and Constraints Quiz

SQL Query Language Overview

• SQL is a query language for managing and manipulating databases.
• It was developed as part of the System R project at IBM.
• ANSI and ISO standards define SQL.
• Different SQL versions have been released (e.g., SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003).

Data Definition Language (DDL)

• DDL defines the structure of database relations.
• This includes defining the schema for relations.
• The domains of values for attributes are also defined.
• Integrity constraints are specified.
• Various aspects of security and authorization are included.
• Physical storage and structure on disk is also specified.

Domain Types in SQL

• char(n): Fixed-length character strings with user-specified length.
• varchar(n): Variable-length character strings with user-specified maximum length.
• int: Integer (machine-dependent).
• smallint: Small integer (machine-dependent).
• numeric(p,d): Fixed-point number with precision (p) and decimals (d).
• real, double precision: Floating-point numbers with machine-dependent precision.
• float(n): Floating-point number with user-specified precision.

Create Table

• Defines a relation in SQL.
• Includes attribute names and the data type of values.
• Integrity constraints (e.g., not null, primary key, foreign key) are defined.

Integrity Constraints

• not null: Attributes cannot contain null values.
• primary key: Attributes uniquely identify each tuple.
• foreign key: Attributes link tuples to other relations.

Basic Query Structure

• A typical SQL query includes select, from, and where clauses.
• select: Attributes to retrieve.
• from: Relations to retrieve data from.
• where: Predicates to filter the data.

The Select Clause

• Includes the attributes to be retrieved.

• Using the * in the select clause retrieves all attributes.

• distinct keyword removes duplicate values.

• all keyword does not remove duplicate values.

The Where Clause

• Specifies conditions for the selection of tuples.
• Uses and, or, and not logical connectives to apply multiple conditions.
• Used for comparisons of attribute values.

The From Clause

• Specifies the relations involved in the query.
• Often used with the Cartesian product of the specified relations.

String Operations

• like: Operator for string pattern matching; uses wildcards % and _.
• Case sensitivity exists in matching.

Ordering the Display of Tuples (Ordering)

• Retrieve data based on ascending or descending order.
• order by clause is used.

Where Clause Predicates

• between: Compares values within a range (inclusive).

Set Operations (set operators)

• union: Combines unique values from two or more sets.
• intersect: Returns common values in two or more sets.
• except, minus: Returns values in a set that are not present in another.

Aggregate Functions

• Operations on a group of values (e.g., avg, min, max, sum, count).
• Used with group by clause to aggregate data by groups.

Aggregate Functions (group by and having clause)

• Used with group by clause to group rows with the same values in the specified column(s).
• Used with having clause to filter groups based on aggregate values.

Null Values

• Represented by null, indicating no value or unknown value.
• Special handling in SQL expressions and conditions might be required.

Nested Subqueries (nested queries)

• Subqueries can be used within select, from, and where clauses.
• Used to perform set membership checks, comparisons, or cardinality analysis.

Subqueries in the Where Clause: Set Membership

• Check for values that exist in another result set.

Subqueries in the Where Clause: Set Comparisons

• Compare values using some, all clauses.

Subqueries in the Where Clause: Set Cardinality

• Checks conditions based on the count of rows in the related set.

Subqueries in the From Clause

• Use subqueries to construct complex queries.

Subqueries in the Select Clause (scalar queries):

• A scalar subquery is one that returns a single value.

Modification of the Database

• delete, insert, update statements to alter or manipulate data.

Using with Clause (common table expressions)

• Defines temporary named relations within a query.
• Use CTEs to improve query readability and structure.

Complex Queries Using With Clause

• Query with multiple joins and CTEs.

SQL allows various functions to interact with strings and numeric values.