DATABASE DESIGN AND DEVELOPMENT (IT2140) - LECTURE 02: SQL

A comprehensive set of practice flashcards covering SQL fundamentals from Lecture 02, including DDL, DML, constraints, joins, aggregation, and query clauses.

What was SQL initially called?

SEQUEL (Structured English Query Language).

SQL was developed for which experimental relational database system?

System R.

Which two organizations led to the standard version of SQL in 1986?

ANSI (American National Standards Institute) and ISO (International Standards Organization).

What is SQL-92 commonly called?

SQL2.

Name the main components and facilities of SQL mentioned in the lecture.

Data Definition Language (DDL), Data Manipulation Language (DML), security/authorization, transaction processing, and Embedded SQL.

What does the Data Definition Language (DDL) subset of SQL do?

Supports creation, deletion, and modifications of tables and views (with constraints).

List the constraints that can be defined on a table in DDL.

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK.

In the CREATE TABLE STUDENT example, which constraint defines the primary key?

studentId INTEGER PRIMARY KEY.

Which constraint ensures a column does not contain null values?

NOT NULL.

What does the UNIQUE constraint do?

Ensures all values in a column are unique (no duplicates).

What is the purpose of the DEFAULT constraint?

Defines a default value for a column when no value is provided.

What is a CHECK constraint used for?

Validates data in a column (e.g., CHECK (gpa <= 4.0)).

Which constraint is used to define a foreign key relationship?

FOREIGN KEY.

In the STUDENT table example, what does the FOREIGN KEY (progId) REFERENCES programs(id) specify, including any actions?

progId references id in programs; ON DELETE SET DEFAULT and ON UPDATE CASCADE define behavior on delete/update.

What is ALTER TABLE used for?

To alter the definition of a table (e.g., add a column or a constraint).

How do you add a new column to a table?

ALTER TABLE student ADD age INT.

How do you add a constraint to a column using ALTER TABLE?

ALTER TABLE student ADD CONSTRAINT chk_age CHECK (age > 18).

How do you remove a column from a table?

ALTER TABLE student DROP COLUMN age.

What is the SQL command to drop a table?

DROP TABLE Employee.

What does the Data Manipulation Language (DML) allow you to do?

Insert, delete, modify, and display rows.

What is the syntax to insert a row into a table?

INSERT INTO student VALUES (1000, 'Amal', '123456789V', 3.2, 'BM').

What is the SQL command to delete a row from a table?

DELETE FROM student WHERE studentId = 1000.

What is the SQL command to update a row's data?

UPDATE student SET gpa = 2.8 WHERE studentId = 1001.

What is the basic form of a SELECT statement?

SELECT FROM WHERE .

What is the LIKE operator used for and what are the wildcards?

Pattern matching; % matches any sequence of characters, _ matches any single character.

What does IS NULL / IS NOT NULL check for?

Whether an attribute value is NULL.

What does the DISTINCT operator do in a SELECT statement?

Removes duplicate values from the result set.

What does the BETWEEN operator do?

Checks whether a value lies within a specified range (inclusive).

What is the ORDER BY clause used for?

Sorting results by one or more columns; defaults to ASC unless DESC is specified.

What is an INNER JOIN?

Joins two tables based on a condition and returns matching rows from both tables.

What does a LEFT OUTER JOIN return?

All rows from the left table and the matching rows from the right; non-matching right rows show NULL.

What does a RIGHT OUTER JOIN return?

All rows from the right table and the matching rows from the left; non-matching left rows show NULL.

What are common aggregate functions in SQL?

SUM, COUNT, AVG, MIN, MAX.

What is the purpose of GROUP BY in SQL?

Groups the data by values of specified columns and produces a single summary row per group.

What is the HAVING clause used for?

Applying conditions to groups produced by GROUP BY (group-level filtering).

What is the overall SQL query structure summarized in the lecture?

SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ].

What should you do by next week according to the notes?

Try the self-test questions on the course website and complete the tutorial.