4. SQL Basics (DDL and DML) Flashcards
(11 cards)
What is SQL?
The standard language for Relational DBMSs, though not strictly “pure” relational.
It uses terminology like tables, columns, and rows
What is DDL?
Used to define database structure (e.g., CREATE TABLE, DROP TABLE, ALTER TABLE)
What does CREATE TABLE do?
Defines table structure including column names, data types, and constraints
What are the common Data types?
Common types include INTEGER, DECIMAL/NUMERIC, REAL/DOUBLE PRECISION, VARCHAR/TEXT, DATE, TIME, TIMESTAMP. DECIMAL/NUMERIC is most appropriate for monetary values to avoid floating-point issues
Common Constraints?
Can be defined inline (within column definition) or out of line (separately).
▪ NOT NULL: Ensures a column must have a value.
▪ DEFAULT: Specifies a default value for a column if none is provided.
▪ CHECK: Defines a condition that column values must satisfy.
▪ PRIMARY KEY: Defines the PK (implies UNIQUE and NOT NULL). Can be composite.
▪ UNIQUE: Defines a candidate key (ensures uniqueness).
▪ FOREIGN KEY: Defines the FK and references the PK of another table. Includes options for referential integrity violations (ON DELETE/ON UPDATE CASCADE, SET NULL, RESTRICT, NO ACTION). ON UPDATE CASCADE is not supported natively in Oracle but can be implemented with triggers.
What does DROP TABLE/ALTER TABLE do?
Used to remove or modify table definitions. CASCADE option removes dependent objects
DML:
Data Manipulation Language, used to query and modify data (e.g., SELECT, INSERT, UPDATE, DELETE).
INSERT:
Adds new rows. Values must match column order/names and satisfy constraints. Can insert results of a query.
DELETE:
Removes rows. The WHERE clause is crucial to specify which rows; omitting it deletes all rows.
UPDATE:
Modifies column values in existing rows. The WHERE clause specifies which rows.
SELECT (Basic Form): SELECT [ALL | DISTINCT] select_list FROM table_list [WHERE condition] [GROUP BY …] [HAVING …] [ORDER BY …].
◦ select_list: Specifies columns, expressions, or aggregates to retrieve. * selects all columns.
◦ FROM: Specifies source tables. Tables can be joined using INNER JOIN, LEFT OUTER JOIN, etc., with ON or USING clauses. Joining can be written as a Cartesian product followed by a Restrict (in theory).
◦ WHERE: Filters rows based on a condition. Equivalent to Relational Algebra’s SELECT (Restrict). Uses comparison (=, >, <, etc.) and logical (AND, OR, NOT) operators. Special operators: IN, BETWEEN, LIKE, IS NULL, EXISTS, SOME, ALL.
◦ DISTINCT: Eliminates duplicate rows in the result. SQL tables/results can contain duplicates, unlike formal relations.