Brainscape's Knowledge GenomeTM

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


See full index

Week3 > SQL > Flashcards

SQL Flashcards

1
Q

Explain what SQL is. What are some SQL databases?

A

SQL stands for Structured Query Language. It is used to create, manage, and access relational databases.
SQL implementations include PostgreSQL, MS SQL Server, MySQL, and OracleSQL.

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

How is data structured in a SQL database?

A

Data is placed into tables. Every column in the table represents an attribute or field. Each row, called a record, represents an individual item of data.

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

What is an ERD? How is it useful?

A

ERD stands for Entity Relationship Diagram. ERD is a visual representation of the different tables in a database and models the relationships between different tables.
ERD can be a useful tool when designing a database, and can be used as helpful documentation when accessing a database.

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

What are the different multiplicity relationships? How would you create these relations?

A

Relationships in a RDBMS can be one-to-one, one-to-many/many-to-one, or many-to-many.

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

What kind of relationship would exist between Students and Classes?

A

Students and Classes have a many-to-many relation. A Student may take many Classes and a Class may have many Students.

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

Explain the concept of referential integrity

A

Referential integrity means that every entry in a foreign key column has a matchning record in the associated table.
We don’t want orphaned records, or foreign keys that don’t have a matching primary key, in our database.

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

What is a cascade delete?

A

With a cascade delete, if a record referenced by records in other tables is deleted, then all records referencing it will also be deleted.

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

Explain Domain constraints.

A

Defines the valid set of values for an attribute. You can also specify that a value should be unique or not null.

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

Define the word “schema”

A

A database schema is a collection of tables, views, triggers, and functions.

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

What is a candidate key? What about a surrogate key?

A

A candiadate key is any attribute or set of attributes that are unique for all records in a table, qualifying them to be used as a primary key.

A surrogate key is a key which does not have any contextual or business meaning . It is manufactured “artificially” and only for the purposes of data analysis.

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

How would you create a one-to-one relationship?

A

A one-to-one relationship is implemented by refencing a primary key in another table. To ensure that the relationship remains one-to-one and one row references exactly one other row, the foreign key should also be either a primary key or have a unique constraint.

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

How would you create a many-to-one relationship?

A

A many-to-one relationship is a relationship in which many rows may reference one row. A table must simply reference another table in order to implement this type of relation. Note that the table with the foreign key represents the ‘many’ side of this relation.

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

How would you create a many-to-many relationship?

A

A many-to-many relationship occurs when many rows may reference many other rows in another table. In SQL, this is done by creating a separate junction table which references both tables in the many-to-many relation.

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

Explain Entity integrity constraint.

A

Prevents the primary key of a table from being null. We cannot use the primary key to identify a record if the primary key is null.

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

Explain Referential integrity constraints.

A

Referential integrity constraints require foreign keys to be null or match existing records in the referenced table.

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

Explain Key constraints.

A

Key constraints identify the attribute(s) that will be used to uniquely identify all records in the table.

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

What is a surrogate key?

A

Surrogate keys are primary keys used in a database to uniquely identify a record, but is not related or derived from the data itself. A surrogate key is artificially generated.

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

What kind of relationship exists between Students and Textbooks?

A

Students and Textbooks have a one-to-many relation. A Student may have many Textbooks, but a Textbook only belongs to one Student.

19
Q

What is the DDL sublanguage of SQL? Can you list some commands?

A

Data Definition Language (DDL) is used to create, edit, and delete data entities, such as databases, schemas, users, tables, and properties.
CREATE, ALTER, DROP, and TRUNCATE

20
Q

What is the DCL sublanguage of SQL? Can you list some commands?

A

Data Control Language (DCL) is used to manage user permissions within a database.
GRANT and REVOKE are DCL commands.

21
Q

What is the DML sublanguage of SQL? Can you list some commands?

A

Data Manipulation Language (DML) is used to interact with records within tables.
INSERT, UPDATE, and DELETE are DML commands.

22
Q

What is the DQL sublanguage of SQL? Can you list some commands?

A

Data Query Language (DQL) is used to retrieve information from tables.
SELECT is the main operation used to query data. WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT are all DQL commands used to filter data.

23
Q

What is the TCL sublanguage of SQL? Can you list some commands?

A

Transaction Control Language (TCL) is used to create and manage transactions. Transactions combine multiple operations into a unit of work. BEGIN, SAVEPOINT, ROLLBACK, and COMMIT are TCL commands.

24
Q

What is the difference between DELETE, DROP, and TRUNCATE commands?

A

DELETE removes records from a TABLE, can use a WHERE clause to select which records to remove.
DROP deletes an entire table and all of its records.
TRUNCATE removes all the records from a table (but not the table itself).

25
Q

What are some SQL clauses you can use with SELECT statements?

A

WHERE, GROUP BY, HAVING, ORDER BY, and LIMIT are all DQL commands that can be used with SELECT statements.

26
Q

What is the difference between joins and set operators?

A

JOINS combine rows from different tables based on a logical relationship, typically a foreign key relationship.
Set operators combine queries together. Set operations are used on queries on the same table. UNION, UNION ALL, INTERSECT, and EXCEPT are all set operations.

27
Q

Explain the difference between UNION, UNION ALL, and INTERSECT

A

UNION combines the query sets and removes duplicate records
UNION ALL combines the query sets, but does not remove duplicate records
INTERSECT returns the common records from both data sets

28
Q

What are the types of joins?

A 
(INNER) JOIN
LEFT (OUTER) JOIN
RIGHT (OUTER) JOIN
FULL (OUTER) JOIN
SELF JOIN
29
Q

Explain (INNER) JOIN

A

returns all records that have matching values in both tables.

30
Q

Explain LEFT (OUTER) JOIN

A

returns all records in the left table and matching records in the right table.

31
Q

Explain RIGHT (OUTER) JOIN

A

returns all records in the right table and mathcing records in the left table.

32
Q

Explain FULL (OUTER) JOIN

A

returns all records in both tables

33
Q

Explain SELF JOIN

A

joins a table to itself to combine related data on the same table.

34
Q

What are the properties a transaction must follow?

A

Atomicity,
Consistency,
Isolation,
Durability.

35
Q

Explain Atomicity:

A

A transaction should either occur in its entirety or not at all.

36
Q

Explain Consistency:

A

A database should be in a valid (according to database constraints and business logic) state before and after every transaction.

37
Q

Explain Isolation:

A

A transaction should not interfere with another transaction. Concurrent transactions should behave the same as sequential transactions.

38
Q

Explain Durability:

A

Changes made during a transaction should be persisted to the database.

39
Q

What are the four transaction isolation levels?

A

Read uncommited
Read committed
Repeatable
Serializable

40
Q

Explain Read uncommitted transaction level.

A

allows data to be read from an uncommitted transaction. This allows all read phenomena to be possible.

41
Q

Explain Read committed transaction level.

A

only allows data to be read from committed transactions. This isolation level prevents the dirty read phenomena.

42
Q

Explain Repeatable read transaction level

A

only allows data to be seen that was committed before the transaction began. This isolation level prevents the nonrepeatable read phenomena from occuring.

43
Q

Explain Serializable transaction level

A

is the strictest level of isolation and forces transactions to occur sequentially. This prevents the phantom read phenomena from occuring.

Week3 (13 decks)

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