SQL Overview and Commands

Flashcards based on lecture notes about SQL, covering its history, purpose, and usage.

SQL

Structured Query Language, the standard language for Relational Database Management Systems (RDBMS).

RDBMS

A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables.

1970

E.F. Codd developed the relational database concept.

1974-79

IBM Research Lab created System R with Sequel (later SQL).

1979

Oracle marketed the first relational database with SQL.

1981

SQL/DS, the first available RDBMS system on DOS/VSE, became available.

1986

ANSI SQL standard released.

Benefits of a Standardized Relational Language

Reduced training costs, productivity, application portability, application longevity, reduced dependence on a single vendor, cross-system communication.

Catalog

A set of schemas that constitute the description of a database.

Schema

The structure that contains descriptions of objects created by a user (base tables, views, constraints).

Data Definition Language (DDL)

Commands that define a database, including creating, altering, and dropping tables and establishing constraints.

Data Manipulation Language (DML)

Commands that maintain and query a database.

Data Control Language (DCL)

Commands that control a database, including administering privileges and committing data.

Strings SQL Data Type

CHARACTER (n), VARYING CHARACTER (n).

Binary SQL Data Type

Binary Large Object (BLOB).

Number SQL Data Type

Numeric (precision, scale), Decimal (p, s), Integer.

Temporal SQL Data Type

Timestamp, Timestamp with local time zone.

Boolean SQL Data Type

True or False values.

CREATE SCHEMA

Defines a portion of the database owned by a particular user.

CREATE TABLE

Defines a new table and its columns.

CREATE VIEW

Defines a logical table from one or more tables or views.

Referential integrity

ensures that foreign key values of a table must match primary key values of a related table in relationships

ALTER TABLE statement

Allows you to change column specifications (add, delete, modify columns).

DROP TABLE statement

Allows you to remove tables from your schema.

INSERT Statement

Adds one or more rows to a table.

DELETE Statement

Removes rows from a table.

UPDATE Statement

Modifies data in existing rows.

MERGE Statement

Allows combination of Insert and Update in one statement.

Creating indexes

Speed up random/sequential access to base table data

SELECT clause

List the columns (and expressions) to be returned from the query

FROM clause

Indicate the table(s) or view(s) from which data will be obtained

WHERE clause

Indicate the conditions under which a row will be included in the result.

GROUP BY clause

Indicate categorization of results.

HAVING clause

Indicate the conditions under which a category (group) will be included.

ORDER BY clause

Sorts the result according to specified criteria.

Boolean Operators

AND, OR, and NOT Operators for customizing conditions in WHERE clause