4. SQL Basics (DDL and DML) Flashcards

(11 cards)

1
Q

What is SQL?

A

The standard language for Relational DBMSs, though not strictly “pure” relational.
It uses terminology like tables, columns, and rows

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is DDL?

A

Used to define database structure (e.g., CREATE TABLE, DROP TABLE, ALTER TABLE)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What does CREATE TABLE do?

A

Defines table structure including column names, data types, and constraints

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What are the common Data types?

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Common Constraints?

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What does DROP TABLE/ALTER TABLE do?

A

Used to remove or modify table definitions. CASCADE option removes dependent objects

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

DML:

A

Data Manipulation Language, used to query and modify data (e.g., SELECT, INSERT, UPDATE, DELETE).

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

INSERT:

A

Adds new rows. Values must match column order/names and satisfy constraints. Can insert results of a query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

DELETE:

A

Removes rows. The WHERE clause is crucial to specify which rows; omitting it deletes all rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

UPDATE:

A

Modifies column values in existing rows. The WHERE clause specifies which rows.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

SELECT (Basic Form): SELECT [ALL | DISTINCT] select_list FROM table_list [WHERE condition] [GROUP BY …] [HAVING …] [ORDER BY …].

A

◦ 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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly