Chapter 22
22.1
Fill in the blanks in each of the following statements:
a) The international standard database language is SQL.
b) A table in a database consists of rows and columns.
c) The primary key uniquely identifies each row in a table.
d) SQL keyword WHERE is followed by the selection criteria that specify the rows to select in a query.
e) SQL keywords ORDER BY specify the order in which rows are sorted in a query.
f) Merging rows from multiple database tables is called joining the tables.
g) A(n) database is an organized collection of data.
h) A(n) foreign key is a set of columns whose values match the primary key values of another table.
22.2
Define the following terms:
a) Qualified name.
The fully qualified name of an object consists of four identifiers, the server name, database name, schema name, and object name. Typically, a qualified name simply means that the table name and column or row name is specified when using a clause for query.
b) Rule of Referential Integrity.
The rule of referential integrity requires that every foreign-key value must appear as another table's primary-key value.
c) Rule of Entity Integrity.
The rule of entity integrity requires that the table's primary-key must uniquely identify each row in the table.
d) System.Data.
System.Data is the root namespace fo the ADO.NET API.
e) selection criteria.
When running a query, it is necessary to select data that meets a certain selection criteria. This can be done with a "WHERE" clause that contains certain operators for determining value ranges.
22.3
State the purpose of the following SQL keywords:
a) ASC
This keyword will sort data returned from a query in an ascending order.
b) FROM
This keyword specifies the tables involved in the query.
c) DESC
DESC will sort data returned from a query in descending order.
d) INSERT
This command will insert a row or rows into a specified table.
e) LIKE
This command will return results that match a given pattern.
f) UPDATE
This command will update rows in a given table.
g) SET
This keyword will set the values of given columns in a row within an UPDATE command.
h) VALUES
The VALUES keyword is used in conjunction with "INSERT INTO" for creating a new row of values in a table. The values are separated by commas and will be inserted into the column names that were specified by the INSERT INTO command.
i) ON
The ON clause specifies a condition that determines which rows are combined.
22.4
Write SQL queries for the books database (discussion in Section 22.3) that perform each of the following tasks:
a) Select all authors from the Authors table with the columns in the order lastName, firstName and authorID.
SELECT lastName, firstName, authorID
FROM authors
b) Select a specific author and list all books for that author. Include the title, year and ISBN number. Order the information alphabetically by title.
SELECT firstName, lastName, title, year, ISBN
FROM authors, authorISBN, titles
WHERE authorID = 1
ORDER BY title ASC
c) Add a new author to the Author table.
INSERT INTO authors ( authorID, lastName, firstName )
VALUES ( 5, 'Talbott', 'Josh' )
d) Add a new title for an author (remember that the book must have an entry in the AuthorISBN table).
INSERT INTO authorISBN, titles( authorISBN.authorID, authorISBN.ISBN, titles.ISBN, titles.title, titles.editionNumber, titles.copyright )
VALUES ( 5, XX-XXXXX, XX-XXXXX, 'How to Be Awesome', 1, 2011 )
22.5
Fill in the blanks in each of the following statments:
a) The the rule of entity integrity states that every column in a primary key must have a value, and the value of the primary key must be unique.
b) The rule of referential integrity states that every foreign-keyvalue must appear as another table's primary-key value.
c) A(n) percent character in a pattern indicates that a string matching the pattern can have zero or more characters at the percent character's location in the pattern.
d) Java DB is the Sun branded version of MySQL.
e) A(n) underscore in a LIKE pattern string indicates a single character at that position in the pattern.
f) There is a(n) one to many relationship between a primary key and its corresponding foreign key.
g) SQL uses single quote as the delimiter for strings.
h) Microsoft's ADO object model provides an API for accessing database systems programmatically.
22.6
Correct each of the following SQL statements that refer to the books database.
a) SELECT firstName FROM author WHERE authorID = 3
SELECT firstName FROM authors WHERE authorID = 3
b) SELECT isbn, title FROM Titles ORDER WITH title DESC
SELECT isbn, title FROM titles ORDER by title DESC
c) INSERT INTO Authors ( authorID, firstName, lastName )
VALUES ( "2", "Jane", "Doe" )
INSERT INTO authors ( authorID, firstName, lastName ) VALUES ( 2, 'Jane', 'Doe' )