Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

ECS519U - Database Systems > Week 2 - SQL Intro > Flashcards

Week 2 - SQL Intro Flashcards

1
Q

What are the two sublanguages?

A

Data Definition Language (DDL)
Data Manipulation Language (DML)

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

Data Manipulation Language (DML)

A
  • Queries can be written intuitively
  • Works with the content of the database (rows/tuples).
  • Used to insert, update, delete, and query data within tables.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Data Definition Language (DDL)

A
  • Deals with the schema or “shape” of the database.
  • Used to define and modify the structure of tables and their relationships.
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

What is the most widely used relational query language?

A

SQL

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

What two aspects are languages defined by?

A

Syntax (formal rules of the language, grammar)
Semantics (meaning of words, sentences, expressions)

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

Information in a Relational Database is held using what data structure

A

Relations

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

What are relations?

A

A mathematical concept representing a subset of a Cartesian product of sets.

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

What properties does a table have?

A

Name
Unchanging set of columns (attributes - named and typed)
Time varying set of rows (these are the data entries)

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

State table vocab and its corresponding relation vocab

A

TABLE ==> RELATION

Table Name ==> Relation Name
Column Name ==> Set Name
Column Datatype ==> Set
Row ==> n-tuple
All rows ==> The relation

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

Describe tables in terms of relations

A

A table is essentially a concrete implementation of a relation in a relational database.

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

Examples of DML commands

A

INSERT, DELETE, UPDATE, SELECT.

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

Examples of DDL commands

A

CREATE, ALTER, DROP.

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

What is the key difference between DDL and DML in terms of separation of concerns?

A

DDL focuses on the meta-information about the database (e.g., column names and types), while DML manipulates the actual data stored in tables.

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

Practical Advice for creating tables

A

Think carefully about what you need now, and what you might need in the future.
Discuss your schema with end-user.

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

What are the semantics of this SQL?

CREATE TABLE student(
name VARCHAR(255),
id INTEGER,
exam1 INTEGER,
exam2 INTEGER);

A

[[student]] = [[VARCHAR(255)]] x [[INTEGER]] x [[INTEGER]] x [[INTEGER]]

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

List numeric datatypes for SQL

A

Integer: INT
Real: DECIMAL, NUMERIC, FLOAT, DOUBLE

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

List string datatypes for SQL:

A

Fixed length: CHAR(n)
Varying length: VARCHAR(n)

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

List time datatypes for SQL:

A

DATE, DATETIME

(Side note: Format depends on your DBMS. For example, MySQL uses YYYY-MM-DD whereas Oracle uses DD-MMM-YYYY)

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

Give examples of a relation between smaller sets

A

Prices must be positive
NULL can cause crashes

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

Give an example of a constraint between columns

A

AskPrice > BidPrice

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

Give an example of a constraint within columns

A

Primary key must be unique

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

Give an example of a constraint between tables

A

Foreign & Primary keys are related

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

What are the two types of constraints that can be defined in SQL?

A

Column constraints (within columns)
Table constraints (between columns or tables)

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

Provide SQL examples of column constraints

A

AskPrice DOUBLE CHECK (AskPrice >= 0)

Ticker VARCHAR(255) NOT NULL

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

Provide SQL examples of table constraints

A

CHECK (AskPrice >= BidPrice)

PRIMARY KEY(Ticker)

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

What is a function? (in terms of mathematics)

A

A relation where every input maps to exactly one output.

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

Describe how primary keys behave like a mathematical function

A

Each primary key value corresponds to exactly one row.

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

What is a composite primary key?

A

A primary key consisting of two or more columns. The combination of values in these columns must be unique for each row in the table.

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

What do foreign keys do?

A

Establish a relation between tables.

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

What is a foreign key?

A

A set of attributes in table A that refers to a primary key in table B.

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

How can referential integrity constraints be violated?

A

Insertion or deletion of tuples
Foreign key value modified

32
Q

What is referential integrity?

A

A concept in relational databases that ensures the consistency and accuracy of relationships between tables, by guaranteeing that foreign key values in one table always reference valid rows in another table.

33
Q

Why use referential integrity?

A

To prevent rows where the foreign key references a row in another table that doesn’t exist from existing.

34
Q

How can you enforce referential integrity?

A

By defining foreign key constraints in the database schema.
Alternatively, your DBMS may ensure any changes don’t violate the relationship.

35
Q

What are referential triggered actions?

A

Mechanisms to automatically handle scenarios where actions that could potentially break referential integrity occur (e.g. deleting a row from a table)

36
Q

What are the referential triggered action clauses?

A

ON UPDATE
ON DELETE

SET NULL
CASCADE
RESTRICT
NULL

37
Q

Explain what the ON UPDATE referential triggered action clause does?

A

It specifies what should happen to rows in one table when the referenced row in another table is UPDATED.

38
Q

What does the referential triggered action clause SET NULL do?

A

Sets the column value to null when parent row is changed or deleted

39
Q

What does the referential triggered action clause CASCADE do?

A

It propagates changes or deletions to the children tables when the parent table row is modified or deleted

40
Q

What does the referential triggered action clause RESTRICT do?

A

Any changes/deletions to children tables are rejected by the DBMS

41
Q

What does the referential triggered action clause NO ACTION do?

A

Similar to restrict. Prevents changes/deletions in a deferred manner, leading to an error if referential integrity is violated.

42
Q

Explain what the ON DELETE referential triggered action clause does?

A

It specifies what should happen to rows in one table when the referenced row in another table is DELETED.

43
Q

Give an example of a foreign key constraint in SQL

A

FOREIGN KEY (Ticker) REFERENCES stock(Ticker) ON DELETE CASCADE

44
Q

Give an example of a composite primary key in SQL

A

PRIMARY KEY(Ticker, Date)

45
Q

SQL for creating a table

A

CREATE TABLE nameOfTable (
…
);

46
Q

What happens when using default values without specifying what to default to?

A

The value is NULL unless otherwise stated.

47
Q

How do default values work in SQL?

A

Default values are a type of column constraint. The value specified is used if no explicit value was assigned to the attribute.

48
Q

How can you specify default values?

A

You can specify them using DEFAULT<value></value>

CREATE TABLE stock_price(
…
Date DATETIME DEFAULT ‘0000-01-01’
…
);

49
Q

How do you create generic column constraints without doing it in the same row as the attribute in SQL?

A

Using the CONSTRAINT keyword

CREATE TABLE distributors (
did DECIMAL(3) PRIMARY KEY,
name VARCHAR(40),
CONSTRAINT con1 CHECK (did > 100 AND name <> ‘ ‘)
);

50
Q

How do you delete a table in SQL?

A

DROP TABLE stock_price;

51
Q

How do you add a column in SQL?

A

ALTER TABLE stock_price;
ADD ImpliedVol DOUBLE CHECK (ImpliedVol >= 0);

52
Q

How do you remove a column in SQL?

A

ALTER TABLE stock_price
DROP COLUMN Mid60RollAvg;

53
Q

How do you modify a column’s datatype?

A

ALTER TABLE stock_price
MODIFY COLUMN Mid60RollAvg FLOAT;

(note: anything that can be specified can be modified)

54
Q

How do you alter a table to add a new constraint?

A

ALTER TABLE tableA
ADD CONSTRAINT nameOfConstraint
FOREIGN KEY attribute REFERENCES tableB(attribute2) ON DELETE NO ACTION;

55
Q

How do you add a single row (i.e. a tuple) to a table in SQL?

A

– without specifying columns
INSERT INTO tableName
VALUES (v1, v2, …, vN)

  • specifying specific columns to fill. must correspond.
    INSERT INTO tableName (c1, c2, …, cN)
    VALUES (v1, v2, …, vN)
56
Q

How do you update a record (i.e. a tuple) in SQL?

A

UPDATE table
SET column1 = v1, … , columnN = valueN
WHERE condition;

57
Q

How do you delete ALL rows in SQL?

A

DELETE FROM table

58
Q

How do you delete a specific record/tuple in SQL?

A

DELETE FROM table
WHERE condition;

59
Q

SELECT * FROM TABLE1, TABLE2

How does this statement combine rows from table 1 and table 2?

A

Uses the cartesian product / cross product of the two tables, meaning all possible combinations of rows.

60
Q

What does the DISTINCT keyword do?

A

It’s an optional keyword used immediately after the SELECT keyword to indicate the answer should not contain duplicates, since duplicates aren’t eliminated by default.

61
Q

What is target-list, column-list and source-list in this case?

SELECT [DISTINCT] target-list
FROM source-list
WHERE condition
ORDER BY column-list ASC|DESC;

A

target-list / column-list :
List of (1 or more) attributes/functions referring to relations in source-list

source-list :
List of (1 or more) relations, possibly with an alias after each name

62
Q

What is condition in this case?

SELECT [DISTINCT] target-list
FROM source-list
WHERE condition
ORDER BY column-list ASC|DESC;

A

Comparisons that follow the shape:
Attr op const | Attr1 op Attr2

Where op is one of:
=|≠|<|>|≤|≥

And comparisons can be combined using:
AND | OR | NOT

63
Q

Tuples

A

Ordered lists of elements (allows repetitions)
1,2,3) ≠ (2,1,3)
(1,2,3) ≠ (2,3,1)

64
Q

Multisets

A

Un-ordered list of elements (allows repetitions)
(1,2,3) = (2,1,3)
(1,1,2) ≠ (1,2)

65
Q

Sets

A

Un-ordered lists without repetitions.
{1,1,2} = {1,2}
{1,2,3} = {2,3,1}

66
Q

SELECT

Returns: multisets/sets/tuples?

A

multisets

67
Q

SELECT DISTINCT

Returns: multisets/sets/tuples?

A

sets

68
Q

SELECT … ORDER BY

Returns: multisets/sets/tuples?

A

tuples

69
Q

SQL for selecting a column from one table and selecting a column from another table? (Use aliasing)

A

SELECT tb1.column1, tb2.column2 FROM
Table1 AS tb1, Table2 AS tb2

70
Q

What does a projection do?

A

Keeps relevant columns

71
Q

What is the image of the projection?

A

The resulting set of values after applying the projection.

72
Q

Projections in SQL are multisets/sets/tuples?

A

They are often multisets, unless you explicitly use DISTINCT.

73
Q

What is a predicate?

A

A map from a set to {true, false}. In the case of relations, it defines the subset of rows/tuples sent to true.

74
Q

Example of the OR predicate

A

WHERE Grade = ‘A’ OR Grade = ‘B’;

75
Q

Example of the LIKE predicate

A

WHERE Name LIKE ‘J%’;

76
Q
A

ECS519U - Database Systems (12 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions