Databases - T03 SQL DDL Quiz

Databases - T03 SQL Data Definition Language Subset (DDL)

• SQL is more than just a query language; it's used to define data structures, change data, and specify security.
• SQL's DDL subset is used for defining the structure of a database.
• DDL statements include CREATE TABLE, CREATE INDEX, CREATE VIEW, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE statements, which are used to create new database objects such as tables, indexes, views, delete and empty tables, and alter database objects
• CREATE TABLE is used to build new tables, defining column names and data types (e.g., VARCHAR2, NUMBER, DATE).
• DROP TABLE is used to remove a table and all its data.
• Different data types (e.g., VARCHAR2, NVARCHAR2, NUMBER, FLOAT, DATE, TIMESTAMP) exist in SQL databases, each storing different kinds of data. Understanding these data types is essential for designing effective schemas.

SQL-Structured Query Language

• SQL encompasses defining data structure, modifying database contents, and security.
• Different categories (DDL, DML, DCL) exist within SQL, defining distinct functions.
• DDL (Data Definition Language): creating, changing, and removing database objects (tables, views, indices, etc.)
• DML (Data Manipulation Language): working with data in tables (inserting, deleting, selecting, etc. data)
• DCL (Data Control Language): managing access permissions to database objects using statements like GRANT, REVOKE, ADD, and DELETE.

CREATE TABLE Statement

• Used to build new tables in the database.

• Column parameters specify the names of the columns within each table.

• Data types (e.g., VARCHAR, INTEGER, DATE) determine the kind of data a column can hold.

• The CREATE TABLE statement defines a new table with the specified columns.

• Example: CREATE TABLE table_name (column1 datatype, column2 datatype, ...);

• DROP TABLE statement deletes an existing table and all its data.

Some Oracle Data Types

• VARCHAR2(size): A variable-length character string.
• NVARCHAR2(size): A variable-length Unicode character string.
• NUMBER [(p [, s])]: A numeric data type with precision (p) and scale (s).
• FLOAT [(p)]: A floating-point number with precision (p).
• DATE: Stores dates and times.
• TIMESTAMP [(fractional_seconds_precision)]: Stores timestamps with fractional seconds.

CREATE TABLE Constraints

• Integrity constraints help ensure data consistency (e.g., primary key, foreign key).

• Value constraints define rules for column values (e.g., NOT NULL, unique values, specific value ranges - CHECK constraint).

• Constraints can be added to an existing table or defined at table creation.

• Constraints are classified as column-level or table-level.

• Column-level constraints apply to an individual column.

• Table-level constraints apply to more than one column.

DDL Example (GoldFish Problem)

• A possible database schema for a "GoldFish" problem is presented visually with relationships between tables
• This illustrated schema represents a possible database design for the "GoldFish" problem.

CREATE TABLE Example

• Example CREATE TABLE statements for RaceType, Style, Coach, Coaching, Swimmer, and RaceResult tables showcasing the use of constraints and data types

RaceType and Style Table Example Constraints

• RaceType and Style table examples showcase value constraints, including a CHECK constraint for Distance and primary key
• Example usage of ALTER TABLE and FOREIGN KEY for relationships between tables

Coach and Coaching Table Example Constraints

• Example Coach and Coaching table examples demonstrate value constraints and PRIMARY KEY and FOREIGN KEY constraints.

Race Table Integrity Constraints

• Example Race table constraint definition including constraints.

Swimmer and RaceResult Table Integrity Constraints

• Example Swimmer and RaceResult tables demonstrating the use of integrity constraints, data types, and primary keys.

Further Reading

• Begg, C., & Connolly, T. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). Pearson. Recommended for a comprehensive study of database concepts and SQL. Chapter 7 specifically deals with database definition.