Brainscape's Knowledge GenomeTM

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


See full index

DBMS > SQL > Flashcards

SQL Flashcards

1
Q

Why SQL?

A

High-level language that specifies “what” rather than “how” avoiding data manipulation details; lets DBMS optimize and execute “best way”

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

What is the Basic SQL Query?

A

SELECT [Distinct] target-list FROM relation-list WHERE qualification

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

What does FROM specify?

A

A list of relation names possibly with a range-variable

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

What does the SELECT specify?

A

The list of attributes of relations in relation-list

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

What does the WHERE specify?

A

The qualification or comparisons (Attribute op const/attribute) combined using AND, OR, NOT

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

What is DISTINCT and where is it placed?

A

Optional keyword indicating that the answer should not contain duplicates; its placed between SELECT and the target-list

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

What is the conceptual evaluation strategy of the basic SQL query?

A
  1. Compute the cross-product of the relation-list
  2. Discard resulting tuples if they fail qualifications
  3. Delete attributes not in the target-list
  4. If distinct is specified, eliminate duplicate rows
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a Range variable and when are they Variables needed?

A

Ex: From Relations R;

needed only if the same relation appears twice in the FROM caluse

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

What is LIKE keyword used for and where is it used?

A

Used for string matching in the WHERE clause.

Ex: WHERE s.name LIKE ‘B-%B’

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

In conjunction with LIKE, what does _ mean

A

Any one character

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

In conjunction with LIKE, what does % mean

A

0 or more characters

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

What are two ways to name fields in a result?

A

Using AS and =

Ex: SELECT age1=S.age-5, 2*S.age AS age2

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

Does SELECT-FROM-WHERE use bag or set semantics? Why?

A

Bag because it is easier to avoid eliminating duplicates.

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

Does union, intersection, and difference use bag or set semantics? Why?

A

Set because it’s already sorted so it’s easy to eliminate duplicates.

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

How do you force the result to be a set?

A

SELECT DISTINCT

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

How do you force the result to be a bag?

A

ALL Keyword (Ex: UNION ALL)

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

What is a nested Query?

A

A feature where WHERE, FROM, or HAVING clauses can contain an SQL query?

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

What are the 3 query clauses that can use a nested query?

A

WHERE, FROM, HAVING

19
Q

How do you find tuples that are not in a query?

A

Use NOT IN

20
Q

What are SQL Join Expressions?

A

NATURAL JOIN, CROSS JOIN, OUTER JOIN can be used in place of relations in a FROM clause

21
Q

What is the SQL Theta Join Semantics?

A

R JOIN S on

Ex: Sailors S JOIN Reserves R ON S.sid=R.sid;

22
Q

What are the optional OUTER JOIN keywords?

A

1-NATURAL in front of OUTER
2- Optional ON after JOIN
3- LEFT, RIGHT, or FULL before OUTER

23
Q

What are the 6 SQL aggregate operations?

A 
1- COUNT(*)
2- COUNT ([DISTINCT] A)
3- SUM([DISTINCT] A)
4- AVG[DISTINCT ] A)
5- MAX (A)
6- MIN (A)
where A is a single column
24
Q

Where are the aggregate operators placed?

A

In the SELECT clause (which can be in the where clause for nested query)

25
Q

What use grouping?

A

To apply aggregate operators on groups instead of individual tuples.

26
Q

What follows the GROUP BY clause?

A

A grouping-list?

27
Q

What follows the HAVING clause?

A

A group-qualification

28
Q

When using grouping, what may be in the SELECT target-list?

A

(i) attribute names that is a subset of the grouping list and (ii) terms with aggregate operations like MIN(S.age)
- each answer tuple corresponds to a group, and these attributes must have a single value per group

29
Q

If any aggregation is used, what are the 2 restrictions on each element of the SELECT list?

A

1- elements must be aggregated, or

2- an attribute on the group by list

30
Q

What may be in the HAVING clause?

A

Relations or attributes in the FROM clause, that are either
1- grouping attribute
2- aggregated

31
Q

What is the conceptual evaluation for queries with grouping?

A
  1. Compute cross-product of relation-list
  2. Discard tuples that fail qualification
  3. Delete unnecessary fields
  4. Partition remaining tuples into groups by the values of the attributes int he grouping list (group by)
  5. Apply group qualifications to eliminate some groups. Expressions must have a single value per group. One answer tuple is generated per qualifying group.
32
Q

What’s the difference between EXISTS and IN?

A

EXISTS evaluates to True or False while IN is used to compare against a subquery column

33
Q

Can aggregate operations be nested?

A

No, the below example is wrong:

Ex: Where.Sage = (SELECT MIN(AVG(S2.age)) FROM Sailors S2)…

34
Q

When are NULL values used?

A

When field values are unknown or inapplicable

35
Q

What is a constraint?

A

A relationship among data elements that the DBMS is required for enforce
ex: key, foreign key

36
Q

What is a trigger?

A

An event, condition, or action that is more flexible and general than a constraint

37
Q

What are 5 kinds of constraints?

A

1- Keys
2- Foreign key (referential integrity)
3- Value-based (attributes contained to a value)
4- Tuple-based (relations among components)
5- assertions: any SQL boolearn expression

38
Q

What are 2 possible violations to foreign keys?

A

1- insert/update on referencing relation

2- delete/update on referenced relation

39
Q

What are 4 possible actions on foreign key violations?

A

1- reject
2- cascade
3- set NULL
4- set value

40
Q

What are attributed-based checks and how are they implemented?

A
  • constraints on the value of a particular attribute
    Add CHECK to declaration of attribute;
    EX: beer CHAR(2) (beer IN (SELECT name FROM beers))
    Ex: price REAL CHECK (price
41
Q

When are attribute checks done?

A

When attribute value is inserted or updated?

42
Q

What is a tuple-based check?

A

A check added as a relation-scheme element. The Check may refer to any attribute of the relation
Ex: CHECK (bar = ‘Joes’ OR price

43
Q

What are assertions and how are they created?

A

Database-scheme elements like relation or views
CREATE ASSERTION
CHECK
where condition may refer to any relation or attribute in a database scheme

44
Q

When are assertion checks?

A

On every modification to every relation of the database (drawback), but a clever system could do better

DBMS (8 decks)

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