Database Reference
In-Depth Information
3. A compound condition is formed by connecting two or more simple conditions using one or both
of the following operators: AND and OR. You can also precede a single condition with the NOT
operator to negate a condition. When you connect simple conditions using the AND operator, all
the simple conditions must be true for the compound condition to be true. When you connect
simple conditions using the OR operator, the compound condition will be true whenever any of
the simple conditions are true.
5. To use the LIKE or IN operators in an SQL query, include them in the WHERE clause. A charac-
ter string containing one or more wildcards is included after the word LIKE. The word IN is fol-
lowed by a list of values.
7. Use an SQL built-in function (COUNT, SUM, AVG, MAX, and MIN) by including it in the SELECT
clause followed by the name of the field to which it applies.
9. To group data in SQL, include the words GROUP BY followed by the field or fields on which the
data is to be grouped in the query results. If you group data, you only can include the fields on
which you are grouping or statistics in the SELECT clause.
11. To qualify the name of a field in an SQL query, precede the field with the name of the table to
which it belongs, followed by a period. It is necessary to qualify a field if the field name occurs in
more than one of the tables listed in the FROM clause.
13. The update commands in SQL are INSERT, which inserts new rows in a table; UPDATE, which
changes all the rows in a table that satisfy some condition; and DELETE, which deletes all the
rows in a table that satisfy some condition.
361
CHAPTER 4
THE RELATIONAL MODEL 3: ADVANCED TOPICS
—
1. A view is an individual user
s picture of the database. It is defined using a defining query. The
data in the view never actually exists in the form described in the view. Rather, when a user
accesses the view, his or her query is merged with the defining query of the view to form a query
that pertains to the whole database.
3. a.
CREATE VIEW PartOrder AS
SELECT Part.PartNum, Description, Price, OrderNum, OrderDate, NumOrdered, QuotedPrice
FROM Part, OrderLine, Orders
WHERE Part.PartNum
¼
OrderLine.PartNum
AND Orders.OrderNum
¼
OrderLine.OrderNum
;
b.
SELECT PartNum, Description, OrderNum, QuotedPrice
FROM PartOrder
WHERE QuotedPrice
>
100
;
c.
SELECT Part.PartNum, Description, OrderNum, QuotedPrice
FROM Part, OrderLine
WHERE Part.PartNum
¼
OrderLine.PartNum
AND QuotedPrice
>
100
;
5. The GRANT statement is used to assign privileges to users of a database. It relates to security
because a user who does not have the privilege of accessing a certain portion of a database cannot
access that portion of the database. The privileges that can be assigned include the privilege of
selecting rows from a table, inserting new rows, and updating existing rows. The REVOKE com-
mand is used to revoke privileges.
'
