Term
|
Definition
| The Data Manipulation Language is a subset of SQL that allows users to pose queries and to insert, delete, and modify rows. |
|
|
Term
|
Definition
| The Data Definition Language is a subset of SQL that supports the creation, deletion, and modification of definitions for tables and views and the creation and deletion of indexes, and the creation of integrity constraints. |
|
|
Term
|
Definition
| They are actions executed by the DBMS whenever changes to the database meet conditions specified in the trigger. |
|
|
Term
| (5.1) What is embedded SQL? |
|
Definition
| It is SQL code to be called from a host language, such as C or Cobol. |
|
|
Term
| (5.1) What is Dynamic SQL? |
|
Definition
| Dynamic SQL allows a query to be constructed and executed at run-time. |
|
|
Term
| (5.1) Can SQL be executed in a client-server environment or over a network? |
|
Definition
| Yes, there are certain SQL commands that control how a client application program can connect to an SQL database server, or access data from a database over a network. |
|
|
Term
| (5.1) What is transaction management? |
|
Definition
| There are various SQL commands that allow a user to explicitly control aspects of how a transaction is to be executed. |
|
|
Term
| (5.1) Does SQL provide data security? |
|
Definition
| Yes, SQL provides mechanisms to control users' access to data objects such as tables and views. |
|
|
Term
| (5.2) What are the parts of a basic SQL query? |
|
Definition
| Every query must have a SELECT clause with a select-list, which specifies columns to be retained in the result, and a FROM clause with a from-list which specifies a cross-product of tables. A WHERE clause is optional and its qualification specifies selection conditions on the tables in the from-list. The keyword DISTINCT is optional and it indicates that the table computed as an answer to this query should not contain duplicates. |
|
|
Term
| (5.2) How does the DBMS know what fields to return when an * is used for the select-list? |
|
Definition
| The DBMS must refer to the schema of the table(s) in the from-list, in order to determine the fields to return. |
|
|
Term
| (5.2) What is the difference between a set and a multi-set? |
|
Definition
| A multi-set has duplicate rows in the result relation, whereas a set does not have duplicates. |
|
|
Term
| (5.2) How can you obtain a set of tuples as the result of a query? |
|
Definition
| The DISTINCT keyword eliminates duplicates in the return relation, thereby making it a set instead of a multi-set. |
|
|
Term
| (5.2) Describe an SQL statement evaluation strategy. |
|
Definition
1) Compute the cross-product of the tables in the from-list.
2) Delete rows in the cross-product that fail the qualification conditions.
3) Delete all columns that do not appear in the select-list.
4) If DISTINCT is specified, eliminate duplicate rows. |
|
|
Term
| (5.2.1) What are range variables in SQL? |
|
Definition
| Range variables are optional and they serve to improve the readability of your queries by assigning a variable to the tables in the from-list that is used to qualify the table attributes in the select-list and WHERE clause qualification. |
|
|
Term
| (5.2.1) Write a query to find the id numbers of sailors who have reserved a red boat. |
|
Definition
SELECT R.sid
FROM Boats B, Reserves R
WHERE B.bid = R.bid AND B.color = 'red' |
|
|
Term
| (5.2.2) How can you give names to output columns in a query that are defined by arithmetic or string expressions? |
|
Definition
| By specifying "as" and a label after the column reference expression in the select-list. |
|
|
Term
| (5.2.2) What support does SQL offer for string pattern matching? |
|
Definition
| SQL provides support for pattern matching through the LIKE operator, along with the use of the wild-card symbols & and _. The percent symbol stands for zero or more arbitrary characters and the underscore symbol stands for exactly one arbitrary character. |
|
|
Term
| (5.3) What operations does SQL provide over (multi-)sets of tuples? |
|
Definition
| The UNION, INTERSECT, and EXCEPT operations. |
|
|
Term
| (5.3) When would you use the UNION operation in a query? |
|
Definition
| If you wanted to display the results from two different queries, you can use the UNION operation. example: SELECT...FROM ... WHERE ... UNION SELECT ... FROM ... WHERE ... Note: duplicate result rows are automatically eliminated, a DISTINCT operator is not necessary. |
|
|
Term
| (5.3) When would you use the INTERSECT operation in a query? |
|
Definition
| When you would like to see the common rows between two separate queries. It works similar to the relational calculus and operation. example: SELECT...FROM ... WHERE ... INTERSECT SELECT ... FROM ... WHERE ... |
|
|
Term
| 5.3) When would you use the INTERSECT operation in a query? |
|
Definition
| When you want to display the rows that are true for the first select statement and false for the second select statement. example: SELECT...FROM ... WHERE ... EXCEPT SELECT ... FROM ... WHERE ... |
|
|
Term
| (5.3) What does the INTERSECT ALL operation do? |
|
Definition
| It is the same as the INTERSECT operation except that duplicate rows are retained. |
|
|
Term
| (5.3) What does the UNION ALL operation do? |
|
Definition
| It is the same as the UNION operation except that duplicate rows are retained. |
|
|
Term
| (5.4) What are nested queries? |
|
Definition
| It is a query that has another query embedded within it; the embedded query is called a subquery. |
|
|
Term
| (5.4) Where can subqueries be placed within an SQL statement? |
|
Definition
| There are usually placed in the WHERE clause, although they can also appear in the FROM or HAVING clause. |
|
|
Term
| (5.4.2) What is correlation in nested queries? |
|
Definition
It is when the subquery is evaluated according to the current row of the outer query. Note the condition R.sid = S.sid in the following example is checking the current S.sid in Sailors against the R.sid in Reserves.
Example: SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid = 103
AND R.sid = S.sid) |
|
|
Term
| (5.4.1) How and when would you use the IN operator? |
|
Definition
You use the IN operator to test whether a value is in a given set of elements and a subquery is used to generate this set of elements. This operator also has a negated version, NOT IN.
Example: SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid = 103) |
|
|
Term
| (5.4.2) How and when would you use the EXISTS operator? |
|
Definition
You use the EXISTS operator to test whether a given set of elements is non-empty and a subquery is used to generate this set of elements. This operator also has a negated version, NOT EXISTS.
Example: SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid = 103
AND R.sid = S.sid) |
|
|
Term
| (5.4.2) When is it considered good programming style to use the * in the SELECT clause? |
|
Definition
There are 2 cases. One is when using the EXISTS operator since you don't really want to retrieve any data, you just want to know if it exists. The second is when you want to use the aggregate operator COUNT, and the same reasoning applies. |
|
|
Term
| (5.4.2) How and when would you use the UNIQUE operator? |
|
Definition
You use the UNIQUE operator to test whether a row appears twice in the answer to the subquery. When UNIQUE is applied to a subquery, the resulting condition returns true if there are no duplicates in the subquery results. Note that it will return true if the answer is an empty set. This operator also has a negated version, NOT UNIQUE.
Example:
SELECT S.sname
FROM Sailors S
WHERE UNIQUE (SELECT *
FROM Reserves R
WHERE R.bid = 103
AND R.sid = S.sid) |
|
|
Term
| (5.4.3) How and when would you use the ANY operator? |
|
Definition
The ANY operator is used in conjuction with any of the comparison operators (<, <=, =, <>, >=, >). The SOME operator is a synonym for ANY. The subquery must return some tuple in order for the outer query to find any row. If subquery evaluates as null, then the outer query will return a null set.
Say you want to find all sailors whose rating is better than a sailor named HORATIO.
SELECT S.sid
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname = 'Horatio') |
|
|
Term
| (5.4.3) How and when would you use the ALL operator? |
|
Definition
The ALL operator is used in conjuction with any of the comparison operators (<, <=, =, <>, >=, >). If the subquery returns a null set, then the outer query would evaluate all rows to true. If subquery evaluates as null, then the outer query will return all rows. Example: You want to find the sailors with the highest rating.
SELECT S.sid
FROM Sailors S
WHERE S.rating >= ALL (SELECT S2.rating
FROM Sailors S2) |
|
|
Term
| (5.4.3) What operator is equivalent to =ANY operation? |
|
Definition
|
|
Term
| (5.4.3) What operator is equivalent to <>ALL operation? |
|
Definition
|
|
Term
(5.4.4) How would you rewrite this query if you did not have the INTERSECT operator?
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
INTERSECT
SELE |
|
Definition
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
AND S.sid IN (SELECT S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid
AND B2.color = 'green') |
|
|
Term
| (5.5) What aggregate operators does SQL support? |
|
Definition
|
|
Term
| (5.5) Write a query to find the average age of all sailors. |
|
Definition
SELECT AVG (S.age)
FROM Sailors S |
|
|
Term
| (5.5) Write a query to find the average age of sailors with a rating of 10. |
|
Definition
SELECT AVG (S.age)
FROM Sailors S
WHERE S.rating = 10 |
|
|
Term
| (5.5) Write a query to find the name and age of the oldest sailor. |
|
Definition
SELECT S.sname, S.age
FROM Sailors S
WHERE S.age = (SELECT MAX (S2.age)
FROM Sailors S2) |
|
|
Term
| (5.5) Write a query to count the number of sailors. |
|
Definition
SELECT COUNT (*)
FROM Sailors S |
|
|
Term
| (5.5) Write a query to count the number of different sailor names. |
|
Definition
SELECT COUNT (DISTINCT S.sname)
FROM Sailors S |
|
|
Term
| (5.5) Write a query to find the names of sailors who are older than the oldest sailor with a rating of 10. |
|
Definition
SELECT S.sanme
FROM Sailors S
WHERE S.age > (SELECT MAX (S2.age)
FROM Sailors S2
WHERE S2.rating = 10) |
|
|
Term
| (5.5.1) What is grouping? |
|
Definition
| It is when we want to apply aggregate operations to each of a number of groups of rows in a relation, where the number of groups depends on the relation instance and is not known in advance. |
|
|
Term
| (5.5.1) How do you specify grouping in a query? |
|
Definition
| with the GROUP BY clause. |
|
|
Term
| (5.5.1) What purpose does the HAVING clause serve? |
|
Definition
| It serves to provide a qualification criterion for the group defined with the GROUP BY clause. |
|
|
Term
| (5.5.1) Write a query to find the age of the youngest sailor for each rating level. |
|
Definition
SELECT S.rating, MIN (S.age)
FROM Sailors S
Group BY S.rating |
|
|
Term
| (5.2.1) Write a query to find the names of sailors who have reserved a red boat. |
|
Definition
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red' |
|
|
Term
| (5.2.1) Write a query to find the colors of boats reserved by Lubber. |
|
Definition
SELECT B.color
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND S.sname = 'Lubber' |
|
|
Term
| (5.2.1) Write a query to find the names of sailors who have reserved at least one boat. |
|
Definition
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid = R.sid |
|
|
Term
| (5.2.2) Write a query to increment the ratings of persons who have sailed two different boats on the same day. |
|
Definition
SELECT S.sname, S.rating+1 AS rating
FROM Sailors S, Reserves R1, Reserves R2
WHERE S.sid = R1.sid AND S.sid = R2.sid
AND R1.day = R2.day AND R1.bid <> R2.bid |
|
|
Term
| (5.2.2) Write a query to find the ages of sailors whose name begins and ends with B and has at least three characters. |
|
Definition
SELECT S.age
FROM Sailors S
WHERE S.sname LIKE 'B_%B' |
|
|
Term
| (5.3) Write a query to find the names of sailors who have reserved a red or a green boat. |
|
Definition
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid
AND (B.color = 'red' OR B.color = 'green')
-or-
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
UNION
SELECT S2.sname
FROM Sailors S2, Reserves R2, Boats B2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green' |
|
|
Term
| (5.3) Write a query to find the names of sailors who have reserved both a red and a green boat. |
|
Definition
SELECT S.sname
FROM Sailors S, Reserves R, Boats B, Reserves R2, Boats B2
WHERE S.sid = R.sid AND R.bid = B.bid
AND S.sid = R2.sid AND R2.bid = B2.bid
AND B.color = 'red' AND B2.color = 'green'
-or-
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
INTERSECT
SELECT S2.sname
FROM Sailors S2, Reserves R2, Boats B2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid AND B2.color = 'green' |
|
|
Term
| (5.3) Write a query to find the id numbers of all sailors who have reserved red boats but not green boats. |
|
Definition
SELECT R.sid
FROM Boats B, Reserves R
WHERE R.bid = B.bid AND B.color = 'red'
EXCEPT
SELECT R2.sid
FROM Boats B2, Reserves R2
WHERE R2.bid = B2.bid AND B2.color = 'green' |
|
|
Term
| (5.3) Write a query to find all the id numbers of sailors who have a rating of 10 or reserved boat 104. |
|
Definition
SELECT S.sid
FROM Sailors S
WHERE S.rating = 10
UNION
SELECT R.sid
FROM Reserves R
WHERE R.bid = 104 |
|
|
Term
| (5.4.1) Write an efficient query to find the names of sailors who have reserved boat 103. |
|
Definition
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid = 103) |
|
|
Term
| (5.4.1) Write an efficient query to find the names of sailors who have reserved a red boat. |
|
Definition
SELECT S.sname
FROM Sailors S
WHERE S.sid IN (SELECT R.sid
FROM Reserves R
WHERE R.bid IN (SELECT B.bid
FROM Boats B
WHERE B.color = 'red')) |
|
|
Term
| (5.4.1) Write an efficient query to find the names of sailors who have not reserved a red boat. |
|
Definition
SELECT S.sname
FROM Sailors S
WHERE S.sid NOT IN (SELECT R.sid
FROM Reserves R
WHERE R.bid IN (SELECT B.bid
FROM Boats B
WHERE B.color = 'red')) |
|
|
Term
| (5.4.2) Write a correlated query to find the names of sailors who have reserved boat number 103. |
|
Definition
SELECT S.sname
FROM Sailors S
WHERE EXISTS (SELECT *
FROM Reserves R
WHERE R.bid = 103
AND R.sid = S.sid) |
|
|
Term
| (5.4.3) Using a set-comparison operator, write a query to find sailors whose rating is better than some sailor named Horatio. |
|
Definition
SELECT S.sid
FROM Sailors S
WHERE S.rating > ANY (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname = 'Horatio') |
|
|
Term
| (5.4.3) Using a set-comparison operator, write a query to find sailors whose rating is better than every sailor named Horatio. |
|
Definition
SELECT S.sid
FROM Sailors S
WHERE S.rating > ALL (SELECT S2.rating
FROM Sailors S2
WHERE S2.sname = 'Horatio') |
|
|
Term
| (5.4.3) Using a set-comparison operator, write a query to find the sailors with the highest rating. |
|
Definition
SELECT S.sid
FROM Sailors S
WHERE S.rating >= ALL (SELECT S2.rating
FROM Sailors S2) |
|
|
Term
| (5.4.4) Write a query (without using the INTERSECT operator) to find the names of sailors who have reserved both a red and a green boat. |
|
Definition
SELECT S.sname
FROM Sailors S, Reserves R, Boats B
WHERE S.sid = R.sid AND R.bid = B.bid AND B.color = 'red'
AND S.sid IN (SELECT S2.sid
FROM Sailors S2, Boats B2, Reserves R2
WHERE S2.sid = R2.sid AND R2.bid = B2.bid
AND B2.color = 'green') |
|
|
Term
| (5.4.4) Write a query to express the relational algebra division operation that will find the names of sailors who have reserved all boats. |
|
Definition
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS ((SELECT B.bid
FROM Boats B)
EXCEPT
(SELECT R.bid
FROM Reserves R
WHERE R.sid = S.sid))
-or-
SELECT S.sname
FROM Sailors S
WHERE NOT EXISTS (SELECT B.bid
FROM Boats B
WHERE NOT EXISTS (SELECT R.bid
FROM Reserves R
WHERE R.bid = B.bid
AND R.sid = S.sid)) |
|
|
