Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

DBS > Intermediate SQL > Flashcards

Intermediate SQL Flashcards

(79 cards)

Start Studying
1
Q

What is a join operation in SQL?

A

A join operation takes two relations and returns as a result another relation.

A join operation is a Cartesian product which requires that tuples in the two relations match under some condition.

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

What does the using clause do in SQL joins?

A

It specifies that values must match on specified attributes for a natural join.

This is a form of natural join.

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

What is the purpose of the on condition in SQL joins?

A

It allows a general predicate over the relations being joined.

The predicate is written like a where clause predicate except for the use of the keyword on.

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

What is an outer join in SQL?

A

An extension of the join operation that avoids loss of information by preserving tuples that would be lost in a join, creating tuples in the result containing null values.

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

What are the three forms of outer join?

A
  • Left outer join
  • Right outer join
  • Full outer join
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

What does a left outer join do?

A

Preserves tuples only in the relation named before the left outer join operation.

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

What does a right outer join do?

A

Preserves tuples only in the relation named after the right outer join operation.

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

What is the full outer join?

A

It preserves tuples in both relations.

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

True or False: The right outer join is symmetric to the left outer join.

A

True

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

What is the primary purpose of integrity constraints in SQL?

A

To guard against accidental damage to the database by ensuring that authorized changes do not result in a loss of data consistency.

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

How can integrity constraints be added to an existing relation?

A

Using the command alter table table-name add constraint.

The constraint can be any constraint on the relation.

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

What does the not null specification do in SQL?

A

It prohibits the insertion of a null value for the specified attribute.

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

What does the unique constraint do in SQL?

A

It prevents the storage of repetition of data/values in the column but allows one NULL value.

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

What are candidate keys in SQL?

A

Attributes that are permitted to be null and can form a unique constraint.

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

Fill in the blank: An outer join adds tuples from one relation that does not match tuples in the other relation to the result of the join using _______.

A

null values

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

What is the difference between the on condition and where clause in outer joins?

A

The on condition is part of the outer join specification, while a where clause is not.

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

What is the role of the select statement in SQL?

A

To retrieve data from one or more tables in the database.

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

What is a natural join?

A

A type of join that automatically joins tables based on columns with the same name.

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

What do you achieve with a natural left outer join?

A

You retrieve all records from the left table and the matched records from the right table, with nulls for non-matching records.

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

What is the result of a full outer join?

A

It combines results from both left and right outer joins, showing all records from both tables.

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

What is a Cartesian product in SQL?

A

A result of combining all rows from two or more relations.

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

What does a unique constraint allow in a database?

A

Allows ONE NULL value.

More than one NULL value is also considered as repetition.

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

What is a candidate key?

A

Attributes A1, A2,…, Am that are permitted to be null.

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

What is the purpose of the CHECK constraint?

A

Specifies a predicate P that must be satisfied by every tuple in a relation.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
What does referential integrity ensure?
A value in one relation must also appear in another relation for a certain set of attributes.
26
What happens if a foreign key constraint is violated?
Normally, the action causing the violation is rejected and the transaction is rolled back.
27
What is the CASCADE option in referential integrity?
If a referenced row is deleted or updated, the change propagates to the referencing table.
28
What does the SET NULL option do in referential integrity?
Sets the foreign key in the referencing table to NULL if a referenced row is deleted or updated.
29
What is the SET DEFAULT option in referential integrity?
Assigns a default value to the foreign key if a referenced row is deleted or updated.
30
What does the RESTRICT option do in referential integrity?
Prevents deletion or update of a referenced row if any referencing rows exist.
31
What is an assertion in SQL?
A predicate that expresses a condition the database must always satisfy.
32
What is the syntax to create an assertion in SQL?
create assertion check ;
33
What are built-in data types in SQL?
Includes date, time, timestamp, and interval.
34
What is a user-defined type in SQL?
A type created using the create type construct.
35
What is a domain in SQL?
A user-defined type that can have constraints such as not null.
36
What is a blob in SQL?
Binary large object that stores uninterpreted binary data.
37
What is a clob in SQL?
Character large object that stores large collections of character data.
38
What is a view in SQL?
A virtual relation defined by a query that hides certain data from users.
39
How is a view defined in SQL?
Using the create view statement in the form create view v as .
40
True or False: A view is a precomputed and stored relation.
False. ## Footnote A view is computed by executing the query whenever it is used.
41
What is the purpose of the complex check clause?
Allows for arbitrary predicates, including subqueries, to be included in a check constraint.
42
Fill in the blank: The SQL statement 'create table Employees (emp_id INT PRIMARY KEY, email VARCHAR(100) UNIQUE, phone_number VARCHAR(15) UNIQUE);' establishes a table with ______ and ______ constraints.
PRIMARY KEY and UNIQUE
43
What does the 'budget check (budget > 0)' clause ensure in a department table?
Ensures that the budget is greater than zero.
44
What does the 'not exists' clause do in an assertion?
Ensures that a specified condition does not exist in the database.
45
What does the 'create domain' construct do?
Creates user-defined domain types with constraints.
46
What is the purpose of the 'create type' command?
Creates a user-defined type in SQL.
47
What SQL statement creates an assertion to ensure that there is no more than one president among employees?
create assertion At_most_one_president as CHECK ((select count(*) from EMP e where e.job = 'President') <= 1)
48
What is the significance of the 'on delete cascade' option in a foreign key?
It allows for automatic deletion of dependent rows when a referenced row is deleted.
49
What does the 'interval' data type represent in SQL?
A period of time.
50
What is the function of the 'create domain person_name char(20) not null' statement?
Creates a domain for person names with a maximum length of 20 characters and not null constraint.
51
What does the 'create assertion NO_TRAINERS_IN_BOSTON' clause ensure?
Ensures that Boston-based departments do not employ trainers.
52
How does the 'create assertion Emp_sal_chek' function?
Checks that a supervisee's salary is less than their supervisor's salary.
53
What does the 'create table department' statement define?
Defines a table for departments with attributes for name, building, and budget.
54
What is the form used to create a view in SQL?
create view v as ## Footnote This defines a virtual relation generated by the view.
55
What happens once a view is defined?
The view name can be used to refer to the virtual relation that the view generates.
56
Is a view definition the same as creating a new relation?
No, a view definition saves an expression rather than creating a new relation.
57
What is stored in a view definition?
View definitions are stored, but not the result of the query in the view.
58
What is an example of creating a view for instructors without a salary field?
create view faculty_details as select id, name, deptname from instructor;
59
Can view names appear in queries?
Yes, view names may appear in any place where a relation name may appear.
60
What is a recursive view?
A view relation v is said to be recursive if it depends on itself.
61
Create a view that lists the course ID and room number of all Computer Science courses offered in Fall 2015.
create view cse_fall_2015 as select course.coursed, secid, building, roomnumber from course, section where course.courseid = section.courseid and course.deptname = 'Comp. Sci.' and section.semester = 'Fall' and section.year = 2015;
62
What is the result of inserting a tuple into a view?
The insertion must be represented by an insertion into the actual relation from which the view is constructed.
63
What happens if a tuple is added to a faculty view?
It must be represented by an insertion into the instructor relation.
64
What are the two reasonable approaches to deal with inserting a tuple into a view?
* Reject the insertion and return an error message * Insert a tuple with null values for non-specified attributes
65
What conditions must be satisfied for an SQL view to be updatable?
* The from clause has only one database relation * The select clause contains only attribute names * Any attribute not listed can be set to null * The query does not have a group by or having clause
66
What are materialized views?
Views that are stored and updated when the actual relations used in the view definition change.
67
What is the process of keeping a materialized view up-to-date called?
Materialized view maintenance.
68
What must be weighed against the benefits of materializing a view?
Storage costs and added overhead for updates.
69
What does 'build immediate' mean in the context of materialized views?
Materialized views are created immediately.
70
What does 'build deferred' mean?
Materialized views are created after one refresh.
71
What is a complete refresh in materialized views?
Re-execution of the materialized view query to refresh the view.
72
What is a fast refresh in materialized views?
Updates only the rows which have been changed on master tables.
73
What is refresh on commit in materialized views?
Commits the data in materialized views immediately after data is inserted and committed.
74
What is refresh on demand in materialized views?
Allows adding conditions for refreshing data in the materialized view.
75
What constitutes a transaction in SQL?
A sequence of query and/or update statements.
76
How does a transaction begin in SQL?
Implicitly when an SQL statement is executed.
77
What is an atomic transaction?
A transaction that is either fully executed or rolled back.
78
What does 'commit work' do in a transaction?
Makes the updates performed by the transaction permanent in the database.
79
What does 'rollback work' do?
Undoes all updates performed by the SQL statements in the transaction.

DBS (1 decks)

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