Teach Yourself SQL in 21 Days, Second Edition
 
- Appendix F -
Answers to Quizzes and Exercises
Day 1, "Introduction to SQL"
Quiz Answers
- 1. What makes SQL a nonprocedural language?
SQL determines what should be done, not how it should be done. The database must
implement the SQL request. This feature is a big plus in cross-platform, cross-language
development.
2. How can you tell whether a database is truly relational?
Apply Dr. Codd's 12 (we know there are 13) rules.
3. What can you do with SQL?
SQL enables you to select, insert, modify, and delete the information in a database;
perform system security functions and set user permissions on tables and databases;
handle online transaction processing within an application; create stored procedures
and triggers to reduce application coding; and transfer data between different databases.
4. Name the process that separates data into distinct, unique sets.
Normalization reduces the amount of repetition and complexity of the structure of
the previous level.
Exercise Answer
Determine whether the database you use at work or at home is truly relational.
(On your own.)
Day 2, "Introduction to the Query: The SELECT
Statement"
Quiz Answers
- 1. Do the following statements return the same or different output:
SELECT * FROM CHECKS;
select * from checks;?
- The only difference between the two statements is that one statement is in lowercase
and the other uppercase. Case sensitivity is not normally a factor in the syntax
of SQL. However, be aware of capitalization when dealing with data.
2. None of the following queries work. Why not?
a. Select *
The FROM clause is missing. The two mandatory components of a SELECT statement
are the SELECT and FROM.
b. Select * from checks
The semicolon, which identifies the end of a SQL statement, is missing.
c. Select amount name payee FROM checks;
You need a comma between each column name: Select amount, name, payee FROM checks;
3. Which of the following SQL statements will work?
a. select *
from checks;
b. select * from checks;
c. select * from checks
/
All the above work.
Exercise Answers
- 1. Using the CHECKS table from earlier today, write a query to
return just the check numbers and the remarks.
SELECT CHECK#, REMARKS FROM CHECKS;
- 2. Rewrite the query from exercise 1 so that the remarks will appear as
the first column in your query results.
SELECT REMARKS, CHECK# FROM CHECKS;
- 3. Using the CHECKS table, write a query to return all the unique
remarks.
SELECT DISTINCT REMARKS FROM CHECKS;
Day 3, "Expressions, Conditions, and Operators"
Quiz Answers
Use the FRIENDS table to answer the following questions.
LASTNAME FIRSTNAME AREACODE PHONE ST ZIP
--------------- ---------------- -------- -------- -- ------
BUNDY AL 100 555-1111 IL 22333
MEZA AL 200 555-2222 UK
MERRICK BUD 300 555-6666 CO 80212
MAST JD 381 555-6767 LA 23456
BULHER FERRIS 345 555-3223 IL 23332
PERKINS ALTON 911 555-3116 CA 95633
BOSS SIR 204 555-2345 CT 95633
- 1. Write a query that returns everyone in the database whose last name
begins with M.
SELECT * FROM FRIENDS WHERE LASTNAME LIKE 'M%';
- 2. Write a query that returns everyone who lives in Illinois with a first
name of AL.
SELECT * FROM FRIENDS
WHERE STATE = 'IL'
AND FIRSTNAME = 'AL';
- 3. Given two tables (PART1 and PART2) containing columns
named PARTNO, how would you find out which part numbers are in both tables?
Write the query.
Use the INTERSECT. Remember that INTERSECT returns rows common
to both queries.
SELECT PARTNO FROM PART1
INTERSECT
SELECT PARTNO FROM PART2;
- 4. What shorthand could you use instead of WHERE a >= 10 AND a
<=30?
WHERE a BETWEEN 10 AND 30;
- 5. What will this query return?
SELECT FIRSTNAME
FROM FRIENDS
WHERE FIRSTNAME = 'AL'
AND LASTNAME = 'BULHER';
- Nothing will be returned, as both conditions are not true.
Exercise Answers
- 1. Using the FRIENDS table, write a query that returns the following:
NAME ST
------------------- --
AL FROM IL
INPUT:
SQL> SELECT (FIRSTNAME || 'FROM') NAME, STATE
2 FROM FRIENDS
3 WHERE STATE = 'IL'
4 AND
5 LASTNAME = 'BUNDY';
- 2. Using the FRIENDS table, write a query that returns the following:
NAME PHONE
-------------------------- ------------
MERRICK, BUD 300-555-6666
MAST, JD 381-555-6767
BULHER, FERRIS 345-555-3223
INPUT:
SQL>SELECT LASTNAME || ',' || FIRSTNAME NAME,
2 AREACODE || '-' || PHONE PHONE
3 FROM FRIENDS
4 WHERE AREACODE BETWEEN 300 AND 400;
Day 4, "Functions: Molding the Data You Retrieve"
Quiz Answers
- 1. Which function capitalizes the first letter of a character string and
makes the rest lowercase?
INITCAP
2. Which functions are also known by the name ?
Group functions and aggregate functions are the same thing.
3. Will this query work?
SQL> SELECT COUNT(LASTNAME) FROM CHARACTERS;
- Yes, it will return the total of rows.
4. How about this one?
sql> SELECT SUM(LASTNAME) FROM CHARACTERS
- No, the query won't work because LASTNAME is a character field.
5. Assuming that they are separate columns, which function(s) would splice
together FIRSTNAME and LASTNAME?
The CONCAT function and the || symbol.
6. What does the answer 6 mean from the following SELECT?
INPUT:
SQL> SELECT COUNT(*) FROM TEAMSTATS;
OUTPUT:
COUNT(*)
- 6 is the number of records in the table.
7. Will the following statement work?
SQL> SELECT SUBSTR LASTNAME,1,5 FROM NAME_TBL;
- No, missing () around lastname,1,5. Also, a better plan is
to give the column an alias. The statement should look like this:
SQL> SELECT SUBSTR(LASTNAME,1,5) NAME FROM NAME_TBL;
Exercise Answers
- 1. Using today's TEAMSTATS table, write a query to determine
who is batting under .25. (For the baseball-challenged reader, batting average is
hits/ab.)
INPUT:
SQL> SELECT NAME FROM TEAMSTATS
2 WHERE (HITS/AB) < .25;
OUTPUT:
NAME
--------------
HAMHOCKER
CASEY
- 2. Using today's CHARACTERS table, write a query that will return
the following:
OUTPUT:
INITIALS__________CODE
K.A.P. 32
1 row selected.
INPUT:
SQL> select substr(firstname,1,1)||'.'||
substr(middlename,1,1)||'.'||
substr(lastname,1,1)||'.' INITIALS, code
from characters
where code = 32;
Day 5, "Clauses in SQL"
Quiz Answers
- 1. Which clause works just like LIKE(<exp>%)?
STARTING WITH
2. What is the function of the GROUP BY clause, and what other
clause does it act like?
The GROUP BY clause groups data result sets that have been manipulated by
various functions. The GROUP BY clause acts like the ORDER BY clause
in that it orders the results of the query in the order the columns are listed in
the GROUP BY.
3. Will this SELECT work?
SQL> SELECT NAME, AVG(SALARY), DEPARTMENT
FROM PAY_TBL
WHERE DEPARTMENT = 'ACCOUNTING'
ORDER BY NAME
GROUP BY DEPARTMENT, SALARY;
- No, the syntax is incorrect. The GROUP BY must come before the ORDER BY.
Also, all the selected columns must be listed in the GROUP BY.
4. When using the HAVING clause, do you always have to use a GROUP
BY also?
Yes.
5. Can you use ORDER BY on a column that is not one of the columns
in the SELECT statement?
Yes, it is not necessary to use the SELECT statement on a column that
you put in the ORDER BY clause.
Exercise Answers
- 1. Using the ORGCHART table from the preceding examples, find
out how many people on each team have 30 or more days of sick leave.
Here is your baseline that shows how many folks are on each team.
INPUT:
SELECT TEAM, COUNT(TEAM)
FROM ORGCHART
GROUP BY TEAM;
OUTPUT:
TEAM COUNT
=============== ===========
COLLECTIONS 2
MARKETING 3
PR 1
RESEARCH 2
- Compare it to the query that solves the question:
INPUT:
SELECT TEAM, COUNT(TEAM)
FROM ORGCHART
WHERE SICKLEAVE >=30
GROUP BY TEAM;
OUTPUT:
TEAM COUNT
=============== ===========
COLLECTIONS 1
MARKETING 1
RESEARCH 1
- The output shows the number of people on each team with a SICKLEAVE
balance of 30 days or more.
2. Using the CHECKS table, write a SELECT that will return
the following:
OUTPUT:
CHECK#_____PAYEE_______AMOUNT__
1 MA BELL 150
INPUT:
SQL> SELECT CHECK#, PAYEE, AMOUNT
FROM CHECKS
WHERE CHECK# = 1;
- You can get the same results in several ways. Can you think of some more?
Day 6, "Joining Tables"
Quiz Answers
- 1. How many rows would a two-table join produce if one table had 50,000
rows and the other had 100,000?
5,000,000,000 rows.
2. What type of join appears in the following select statement?
select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where e.employee_id = ep.employee_id;
- The preceding join is an equi-join. You are matching all the employee_ids
in the two tables.
3. Will the following SELECT statements work?
select name, employee_id, salary
from employee_tbl e,
employee_pay_tbl ep
where employee_id = employee_id
and name like '%MITH';
- No. The columns and tables are not properly named. Remember column and table
aliases.
select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where name like '%MITH';
- No. The join command is missing in the where clause.
select e.name, e.employee_id, ep.salary
from employee_tbl e,
employee_pay_tbl ep
where e.employee_id = ep.employee_id
and e.name like '%MITH';
- Yes. The syntax is correct.
4. In the WHERE clause, when joining the tables, should you do
the join first or the conditions?
The joins should go before the conditions.
5. In joining tables are you limited to one-column joins, or can you join
on more than one column?
You can join on more than one column. You may be forced to join on multiple columns
depending on what makes a row of data unique or the specific conditions you want
to place on the data to be retrieved.
Exercise Answers
- 1. In the section on joining tables to themselves, the last example returned
two combinations. Rewrite the query so only one entry comes up for each redundant
part number.
INPUT/OUTPUT:
SELECT F.PARTNUM, F.DESCRIPTION,
S.PARTNUM,S.DESCRIPTION
FROM PART F, PART S
WHERE F.PARTNUM = S.PARTNUM
AND F.DESCRIPTION <> S.DESCRIPTION
AND F.DESCRIPTION > S.DESCRIPTION
PARTNUM DESCRIPTION PARTNUM DESCRIPTION
========== ================ =========== ====================
76 ROAD BIKE 76 CLIPPLESS SHOE
- 2. Rewrite the following query to make it more readable and shorter.
INPUT:
select orders.orderedon, orders.name, part.partnum,
part.price, part.description from orders, part
where orders.partnum = part.partnum and orders.orderedon
between '1-SEP-96' and '30-SEP-96'
order by part.partnum;
Answer:
SQL> select o.orderedon ORDER_DATE, o.name NAME, p.partnum PART#,
p.price PRICE, p.description DESCRIPTION
from orders o,
part p
where o.partnum = p.partnum
and o.orderedon like '%SEP%'
order by ORDER_DATE;
- 3. From the PART table and the ORDERS table, make up
a query that will return the following:
OUTPUT:
ORDEREDON NAME PARTNUM QUANTITY
================== ================== ======= ========
2-SEP-96 TRUE WHEEL 10 1
Answer:
select o.orderedon ORDEREDON, o.name NAME, p.partnum PARTNUM, o.quanity QUANITY
from orders o,
part p
where o.partnum = p.partnum
and o.orderedon like '%SEP%';
- Many other queries will also work.
Day 7, "Subqueries: The Embedded SELECT Statement"
Quiz Answers
- 1. In the section on nested subqueries, the sample subquery returned several
values:
LE SHOPPE
BIKE SPEC
LE SHOPPE
BIKE SPEC
JACKS BIKE
- Some of these are duplicates. Why aren't these duplicates in the final result
set?
The result set has no duplicates because the query that called the subquery
SELECT ALL C.NAME, C.ADDRESS, C.STATE,C.ZIP
FROM CUSTOMER C
WHERE C.NAME IN
- returned only the rows where NAME was in the list examined by the statement
IN. Don't confuse this simple IN statement with the more complex
join.
2. Are the following statements true or false?
The aggregate functions SUM, COUNT, MIN, MAX,
and AVG all return multiple values.
False. They all return a single value.
The maximum number of subqueries that can be nested is two.
False. The limit is a function of your implementation.
Correlated subqueries are completely self-contained.
False. Correlated subqueries enable you to use an outside reference.
3. Will the following subqueries work using the ORDERS table and
the PART table?
INPUT/OUTPUT:
SQL> SELECT *
FROM PART;
PARTNUM DESCRIPTION PRICE
54 PEDALS 54.25
42 SEATS 24.50
46 TIRES 15.25
23 MOUNTAIN BIKE 350.45
76 ROAD BIKE 530.00
10 TANDEM 1200.00
6 rows selected.
INPUT/OUTPUT:
SQL> SELECT *
FROM ORDERS;
ORDEREDON NAME PARTNUM QUANITY REMARKS
15-MAY-96 TRUE WHEEL 23 6 PAID
19-MAY-96 TRUE WHEEL 76 3 PAID
2-SEP-96 TRUE WHEEL 10 1 PAID
30-JUN-96 BIKE SPEC 54 10 PAID
30-MAY-96 BIKE SPEC 10 2 PAID
30-MAY-96 BIKE SPEC 23 8 PAID
17-JAN-96 BIKE SPEC 76 11 PAID
17-JAN-96 LE SHOPPE 76 5 PAID
1-JUN-96 LE SHOPPE 10 3 PAID
1-JUN-96 AAA BIKE 10 1 PAID
1-JUN-96 AAA BIKE 76 4 PAID
1-JUN-96 AAA BIKE 46 14 PAID
11-JUL-96 JACKS BIKE 76 14 PAID
13 rows selected.
a. SQL> SELECT *
FROM ORDERS
WHERE PARTNUM =
SELECT PARTNUM FROM PART
WHERE DESCRIPTION = 'TRUE WHEEL';
- No. Missing the parenthesis around the subquery.
b. SQL> SELECT PARTNUM
FROM ORDERS
WHERE PARTNUM =
(SELECT * FROM PART
WHERE DESCRIPTION = 'LE SHOPPE');
- No. The SQL engine cannot correlate all the columns in the part table
with the operator =.
c. SQL> SELECT NAME,
PARTNUM
FROM ORDERS
WHERE EXISTS
(SELECT * FROM ORDERS
WHERE NAME = 'TRUE WHEEL');
- Yes. This subquery is correct.
Exercise Answer
Write a query using the table ORDERS to return all the NAMEs
and ORDEREDON dates for every store that comes after JACKS BIKE
in the alphabet.
INPUT/OUTPUT:
SELECT NAME, ORDEREDON
FROM ORDERS
WHERE NAME >
(SELECT NAME
FROM ORDERS
WHERE NAME ='JACKS BIKE')
NAME ORDEREDON
========== ===========
TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL 2-SEP-1996
TRUE WHEEL 30-JUN-1996
LE SHOPPE 17-JAN-1996
LE SHOPPE 1-JUN-1996
Day 8, "Manipulating Data"
Quiz Answers
- 1. What is wrong with the following statement?
DELETE COLLECTION;
- If you want to delete all records from the COLLECTION table, you must
use the following syntax:
DELETE FROM COLLECTION;
- Keep in mind that this statement will delete all records. You can qualify which
records you want to delete by using the following syntax:
DELETE FROM COLLECTION
WHERE VALUE = 125
- This statement would delete all records with a value of 125.
2. What is wrong with the following statement?
INSERT INTO COLLECTION SELECT * FROM TABLE_2
- This statement was designed to insert all the records from TABLE_2 into
the COLLECTION table. The main problem here is using the INTO keyword
with the INSERT statement. When copying data from one table into another
table, you must use the following syntax:
INSERT COLLECTION
SELECT * FROM TABLE_2;
- Also, remember that the data types of the fields selected from TABLE_2
must exactly match the data types and order of the fields within the COLLECTION
table.
3. What is wrong with the following statement?
UPDATE COLLECTION ("HONUS WAGNER CARD", 25000, "FOUND IT");
- This statement confuses the UPDATE function with the INSERT
function. To UPDATE values into the COLLECTIONS table, use the
following syntax:
UPDATE COLLECTIONS
SET NAME = "HONUS WAGNER CARD",
VALUE = 25000,
REMARKS = "FOUND IT";
- 4. What would happen if you issued the following statement?
SQL> DELETE * FROM COLLECTION;
- Nothing would be deleted because of incorrect syntax. The * is not required
here.
5. What would happen if you issued the following statement?
SQL> DELETE FROM COLLECTION;
- All rows in the COLLECTION table will be deleted.
6. What would happen if you issued the following statement?
SQL> UPDATE COLLECTION
SET WORTH = 555
SET REMARKS = 'UP FROM 525';
- All values in the COLLECTION table for the worth column are now 555,
and all remarks in the COLLECTION table now say UP FROM 525. Probably
not a good thing!
7. Will the following SQL statement work?
SQL> INSERT INTO COLLECTION
SET VALUES = 900
WHERE ITEM = 'STRING';
- No. The syntax is not correct. The INSERT and the SET do not
go together.
8. Will the following SQL statement work?
SQL> UPDATE COLLECTION
SET VALUES = 900
WHERE ITEM = 'STRING';
- Yes. This syntax is correct.
Exercise Answers
- 1. Try inserting values with incorrect data types into a table. Note the
errors and then insert values with correct data types into the same table.
Regardless of the implementation you are using, the errors that you receive should
indicate that the data you are trying to insert is not compatible with the data type
that has been assigned to the column(s) of the table.
2. Using your database system, try exporting a table (or an entire database)
to some other format. Then import the data back into your database. Familiarize yourself
with this capability. Also, export the tables to another database format if your
DBMS supports this feature. Then use the other system to open these files and examine
them.
See your database documentation for the exact syntax when exporting or importing
data. You may want to delete all rows from your table if you are performing repeated
imports. Always test your export/import utilities before using them on production
data. If your tables have unique constraints on columns and you fail to truncate
the data from those tables before import, then you will be showered by unique constraint
errors.
Day 9, "Creating and Maintaining Tables"
Quiz Answers
- 1. True or False: The ALTER DATABASE statement is often used
to modify an existing table's structure.
False. Most systems do not have an ALTER DATABASE command. The ALTER
TABLE command is used to modify an existing table's structure.
2. True or False: The DROP TABLE command is functionally equivalent
to the DELETE FROM <table_name> command.
False. The DROP TABLE command is not equivalent to the DELETE FROM
<table_name> command. The DROP TABLE command completely deletes
the table along with its structure from the database. The DELETE FROM...
command removes only the records from a table. The table's structure remains in the
database.
3. True or False: To add a new table to a database, use the CREATE
TABLE command.
True.
4. What is wrong with the following statement?
INPUT:
CREATE TABLE new_table (
ID NUMBER,
FIELD1 char(40),
FIELD2 char(80),
ID char(40);
- This statement has two problems. The first problem is that the name ID
is repeated within the table. Even though the data types are different, reusing a
field name within a table is illegal. The second problem is that the closing parentheses
are missing from the end of the statement. It should look like this:
INPUT:
CREATE TABLE new_table (
ID NUMBER,
FIELD1 char(40),
FIELD2 char(80));
- 5. What is wrong with the following statement?
INPUT:
ALTER DATABASE BILLS (
COMPANY char(80));
- The command to modify a field's data type or length is the ALTER TABLE
command, not the ALTER DATABASE command.
6. When a table is created, who is the owner?
The owner of the new table would be whoever created the table. If you signed on
as your ID, then your ID would be the owner. If you signed on as SYSTEM, then SYSTEM
would be the owner.
7. If data in a character column has varying lengths, what is the best
choice for the data type?
VARCHAR2 is the best choice. Here's what happens with the CHAR
data type when the data length varies:
INPUT/OUTPUT:
SQL> SELECT *
2 FROM NAME_TABLE;
LAST_NAME FIRST_NAME
JONES NANCY
SMITH JOHN
2 rows selected.
SQL> SELECT LAST_NAME
2 FROM NAME_TABLE
3 WHERE LAST_NAME LIKE '%MITH';
No rows selected.
ANALYSIS:
- You were looking for SMITH, but SMITH does exist in our table.
The query finds SMITH because the column LAST_NAME is CHAR
and there are spaces after SMITH. The SELECT statement did not
ask for these spaces. Here's the correct statement to find SMITH:
INPUT/OUTPUT:
SQL> SELECT LAST_NAME
2 FROM NAME_TABLE
3 WHERE LAST_NAME LIKE '%MITH%';
LAST_NAME
SMITH
1 row selected.
ANALYSIS:
- By adding the % after MITH, the SELECT statement found
SMITH and the spaces after the name.
TIP: When creating tables, plan your data
types to avoid this type of situation. Be aware of how your data types act. If you
allocate 30 bytes for a column and some values in the column contain fewer than 30
bytes, does the particular data type pad spaces to fill up 30 bytes? If so, consider
how this may affect your select statements. Know your data and its structure.
- 8. Can you have duplicate table names?
Yes. Just as long as the owner or schema is not the same.
Exercise Answers
- 1. Add two tables to the BILLS database named BANK and
ACCOUNT_TYPE using any format you like. The BANK table should contain
information about the BANK field used in the BANK_ACCOUNTS table
in the examples. The ACCOUNT_TYPE table should contain information about
the ACCOUNT_TYPE field in the BANK_ACCOUNTS table also. Try to
reduce the data as much as possible.
You should use the CREATE TABLE command to make the tables. Possible
SQL statements would look like this:
SQL> CREATE TABLE BANK
2 ( ACCOUNT_ID NUMBER(30) NOT NULL,
BANK_NAME VARCHAR2(30) NOT NULL,
ST_ADDRESS VARCHAR2(30) NOT NULL,
CITY VARCHAR2(15) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP NUMBER(5) NOT NULL;
SQL> CREATE TABLE ACCOUNT_TYPE
( ACCOUNT_ID NUMBER(30) NOT NULL,
SAVINGS CHAR(30),
CHECKING CHAR(30);
- 2. With the five tables that you have created--BILLS, BANK_ACCOUNTS,
COMPANY, BANK, and ACCOUNT_TYPE--change the table structure
so that instead of using CHAR fields as keys, you use integer ID
fields as keys.
SQL> ALTER TABLE BILLS DROP PRIMARY KEY;
SQL> ALTER TABLE BILLS ADD (PRIMARY KEY (ACCOUNT_ID));
SQL> ALTER TABLE COMPANY ADD (PRIMARY KEY (ACCOUNT_ID));
- 3. Using your knowledge of SQL joins (see Day 6, "Joining Tables"),
write several queries to join the tables in the BILLS database.
Because we altered the tables in the previous exercise and made the key field
the ACCOUNT_ID column, all the tables can be joined by this column. You
can join the tables in any combination; you can even join all five tables. Don't
forget to qualify your columns and tables.
Day 10, "Creating Views and Indexes"
Quiz Answers
- 1. What will happen if a unique index is created on a nonunique field?
Depending on which database you are using, you will receive some type of error
and no index at all will be created. The constituent fields of a unique index must
form a unique value.
2. Are the following statements true or false?
Both views and indexes take up space in the database and therefore must be factored
in the planning of the database size.
False. Only indexes take up physical space.
If someone updates a table on which a view has been created, the view must have an
identical update performed on it to see the same data.
False. If someone updates a table, then the view will see the updated data.
If you have the disk space and you really want to get your queries smoking, the more
indexes the better.
False. Sometimes too many indexes can actually slow down your queries.
3. Is the following CREATE statement correct?
SQL> create view credit_debts as
(select all from debts
where account_id = 4);
- No. You do not need the parentheses; also the word all should been an
*.
4. Is the following CREATE statement correct?
SQL> create unique view debts as
select * from debts_tbl;
- No. There is no such thing as a unique view.
5. Is the following CREATE statement correct?
SQL> drop * from view debts;
- No. The correct syntax is
drop view debts;
- 6. Is the following CREATE statement correct?
SQL> create index id_index on bills
(account_id);
- Yes. This syntax is correct.
Exercise Answers
- 1. Examine the database system you are using. Does it support views? What
options are you allowed to use when creating a view? Write a simple SQL statement
that will create a view using the appropriate syntax. Perform some traditional operations
such as SELECT or DELETE and then DROP the view.
Check your implementation's data dictionary for the proper tables to query for
information on views.
2. Examine the database system you are using to determine how it supports
indexes. You will undoubtedly have a wide range of options. Try out some of these
options on a table that exists within your database. In particular, determine whether
you are allowed to create UNIQUE or CLUSTERED indexes on a table
within your database.
Microsoft Access allows developers to use graphical tools to add indexes to a
table. These indexes can combine multiple fields, and the sort order can also be
set graphically. Other systems require you to type the CREATE INDEX statement
at a command line.
3. If possible, locate a table that has several thousand records. Use a
stopwatch or clock to time various operations against the database. Add some indexes
and see whether you can notice a performance improvement. Try to follow the tips
given to you today.
Indexes improve performance when the operation returns a small subset of records.
As queries return a larger portion of a table's records, the performance improvement
gained by using indexes becomes negligible. Using indexes can even slow down queries
in some situations.
Day 11, "Controlling Transactions"
Quiz Answers
- 1. When nesting transactions, does issuing a ROLLBACK TRANSACTION
command cancel the current transaction and roll back the batch of statements into
the upper-level transaction? Why or why not?
No. When nesting transactions, any rollback of a transaction cancels all the transactions
currently in progress. The effect of all the transactions will not truly be saved
until the outer transaction has been committed.
2. Can savepoints be used to "save off" portions of a transaction?
Why or why not?
Yes. Savepoints allow the programmer to save off statements within a transaction.
If desired, the transaction can then be rolled back to this savepoint instead of
to the beginning of the transaction.
3. Can a COMMIT command be used by itself or must it be embedded?
A COMMIT command can be issued by itself or in the transaction.
4. If you issue the COMMIT command and then discover a mistake,
can you still use the ROLLBACK command?
Yes and No. You can issue the command, but it will not roll back the changes.
5. Will using a savepoint in the middle of a transaction save all that
happened before it automatically?
No. A savepoint comes into play only if a ROLLBACK command is issued--and
then only the changes made after the savepoint will be rolled back.
Exercise Answers
- 1. Use Personal Oracle7 syntax and correct the syntax (if necessary) for
the following:
SQL> START TRANSACTION
INSERT INTO CUSTOMERS VALUES
('SMITH', 'JOHN')
SQL> COMMIT;
Answer:
SQL> SET TRANSACTION;
INSERT INTO CUSTOMERS VALUES
('SMITH', 'JOHN');
SQL> COMMIT;
- 2. Use Personal Oracle7 syntax and correct the syntax (if necessary) for
the following:
SQL> SET TRANSACTION;
UPDATE BALANCES SET CURR_BAL = 25000;
SQL> COMMIT;
Answer:
SQL> SET TRANSACTION;
UPDATE BALANCES SET CURR_BAL = 25000;
SQL> COMMIT;
- This statement is correct and will work quite well; however, you have just updated
everyone's current balance to $25,000!
3. Use Personal Oracle7 syntax and correct the syntax (if necessary) for
the following:
SQL> SET TRANSACTION;
INSERT INTO BALANCES VALUES
('567.34', '230.00', '8');
SQL> ROLLBACK;
- This statement is correct. Nothing will be inserted.
Day 12, "Database Security"
Quiz Answers
- 1. What is wrong with the following statement?
SQL> GRANT CONNECTION TO DAVID;
- There is no CONNECTION role. The proper syntax is
SQL> GRANT CONNECT TO DAVID;
- 2. True or False (and why): Dropping a user will cause all objects owned
by that user to be dropped as well.
This statement is true only if the DROP USER user name CASCADE
statement is executed. The CASCADE option tells the system to drop all objects
owned by the user as well as that user.
3. What would happen if you created a table and granted select privileges
on the table to public?
Everyone could select from your table, even users you may not want to be able
to view your data.
4. Is the following SQL statement correct?
SQL> create user RON
identified by RON;
- Yes. This syntax creates a user. However, the user will acquire the default settings,
which may not be desirable. Check your implementation for these settings.
5. Is the following SQL statement correct?
SQL> alter RON
identified by RON;
- No. The user is missing. The correct syntax is
SQL> alter user RON
identified by RON;
- 6. Is the following SQL statement correct?
SQL> grant connect, resource to RON;
- Yes. The syntax is correct.
7. If you own a table, who can select from that table?
Only users with the select privilege on your table.
Exercise Answer
Experiment with your database system's security by creating a table and then by
creating a user. Give this user various privileges and then take them away.
(On your own.)
Day 13, "Advanced SQL Topics"
Quiz Answers
- 1. True or False: Microsoft Visual C++ allows programmers to call the
ODBC API directly.
False. Microsoft Visual C++ encapsulates the ODBC library with a set of C++ classes.
These classes provide a higher-level interface to the ODBC functions, which results
in an easier-to-use set of functions. However, the overall functionality is somewhat
limited. If you purchase the ODBC Software Development Kit (SDK) (you can obtain
the SDK by joining the Microsoft Developers Network), you can call the API directly
from within a Visual C++ application.
2. True or False: The ODBC API can be called directly only from a C program.
False. The ODBC API resides within DLLs that can be bound by a number of languages,
including Visual Basic and Borland's Object Pascal.
3. True or False: Dynamic SQL requires the use of a precompiler.
False. Static SQL requires a precomplier. Dynamic SQL is just that: dynamic. The
SQL statements used with Dynamic SQL can be prepared and executed at runtime.
4. What does the # in front of a temporary table signify?
SQL Server uses the # to flag a temporary table.
5. What must be done after closing a cursor to return memory?
You must deallocate the cursor. The syntax is
SQL> deallocate cursor cursor_name;
- 6. Are triggers used with the SELECT statement?
No. They are executed by the use of UPDATE, DELETE, or INSERT.
7. If you have a trigger on a table and the table is dropped, does the
trigger still exist?
No. The trigger is automatically dropped when the table is dropped.
Exercise Answers
- 1. Create a sample database application. (We used a music collection to
illustrate these points today.) Break this application into logical data groupings.
2. List of queries you think will be required to complete this application.
3. List the various rules you want to maintain in the database.
4. Create a database schema for the various groups of data you described
in step 1.
5. Convert the queries in step 2 to stored procedures.
6. Convert the rules in step 3 to triggers.
7. Combine steps 4, 5, and 6 into a large script file that can be used
to build the database and all its associated procedures.
8. Insert some sample data. (This step can also be a part of the script
file in step 7.)
9. Execute the procedures you have created to test their functionality.
(On your own.)
Day 14, "Dynamic Uses of SQL"
Quiz Answers
- 1. In which object does Microsoft Visual C++ place its SQL?
In the CRecordSet object's GetDefaultSQL member. Remember, you
can change the string held here to manipulate your table.
2. In which object does Delphi place its SQL?
In the TQuery object.
3. What is ODBC?
ODBC stands for open database connectivity. This technology enables Windows-based
programs to access a database through a driver.
4. What does Delphi do?
Delphi provides a scalable interface to various databases.
Exercise Answers
- 1. Change the sort order in the C++ example from ascending to descending
on the State field.
Change the return value of GetDefaultSQL as shown in the following code
fragment:
CString CTyssqlSet::GetDefaultSQL()
{
return " SELECT * FROM CUSTOMER ORDER DESC BY STATE ";
}
- 2. Go out, find an application that needs SQL, and use it.
(On your own.)
Day 15, "Streamlining SQL Statements for Improved
Performance"
Quiz Answers
- 1. What does streamline an SQL statement mean?
Streamlining an SQL statement is taking the path with the least resistance by carefully
planning your statement and arranging the elements within your clauses properly.
2. Should tables and their corresponding indexes reside on the same disk?
Absolutely not. If possible, always store tables and indexes separately to avoid
disk contention.
3. Why is the arrangement of conditions in an SQL statement important?
For more efficient data access (the path with the least resistance).
4. What happens during a full-table scan?
A table is read row by row instead of using an index that points to specific rows.
5. How can you avoid a full-table scan?
A full-table scan can be avoided by creating an index or rearranging the conditions
in an SQL statement that are indexed.
6. What are some common hindrances of general performance?
Common performance pitfalls include
- Insufficient shared memory
- Limited number of available disk drives
- Improper usage of available disk drives
- Running large batch loads that are unscheduled
- Failing to commit or rollback transactions
- Improper sizing of tables and indexes
Exercise Answers
- 1. Make the following SQL statement more readable.
SELECT EMPLOYEE.LAST_NAME, EMPLOYEE.FIRST_NAME, EMPLOYEE.MIDDLE_NAME,
EMPLOYEE.ADDRESS, EMPLOYEE.PHONE_NUMBER, PAYROLL.SALARY, PAYROLL.POSITION,
EMPLOYEE.SSN, PAYROLL.START_DATE FROM EMPLOYEE, PAYROLL WHERE
EMPLOYEE.SSN = PAYROLL.SSN AND EMPLOYEE.LAST_NAME LIKE 'S%' AND
PAYROLL.SALARY > 20000;
- You should reformat the SQL statement as follows, depending on the consistent
format of your choice:
SELECT E.LAST_NAME, E.FIRST_NAME, E.MIDDLE_NAME,
E.ADDRESS, E.PHONE_NUMBER, P.SALARY,
P.POSITION, E.SSN, P.START_DATE
FROM EMPLOYEE E,
PAYROLL P
WHERE E.SSN = P.SSN
AND E.LAST_NAME LIKE 'S%'
AND P.SALARY > 20000;
- 2. Rearrange the conditions in the following query to optimize data retrieval
time.Use the following statistics (on the tables in their entirety) to determine
the order of the conditions:
593 individuals have the last name SMITH.
712 individuals live in INDIANAPOLIS.
3,492 individuals are MALE.
1,233 individuals earn a salary >= 30,000.
5,009 individuals are single.
- Individual_id is the primary key for both tables.
SELECT M.INDIVIDUAL_NAME, M.ADDRESS, M.CITY, M.STATE, M.ZIP_CODE,
S.SEX, S.MARITAL_STATUS, S.SALARY
FROM MAILING_TBL M,
INDIVIDUAL_STAT_TBL S
WHERE M.NAME LIKE 'SMITH%'
AND M.CITY = 'INDIANAPOLIS'
AND S.SEX = 'MALE'
AND S.SALARY >= 30000
AND S.MARITAL_STATUS = 'S'
AND M.INDIVIDUAL_ID = S.INDIVIDUAL_ID;
--------------
- Answer:
According to the statistics, your new query should look similar to the following
answer. Name like 'SMITH%' is the most restrictive condition because it
will return the fewest rows:
SELECT M.INDIVIDUAL_NAME, M.ADDRESS, M.CITY, M.STATE, M.ZIP_CODE,
S.SEX, S.MARITAL_STATUS, S.SALARY
FROM MAILING_TBL M,
INDIVIDUAL_STAT_TBL S
WHERE M.INDIVIDUAL_ID = S.INDIVIDUAL_ID
AND S.MARITAL_STATUS = 'S'
AND S.SEX = 'MALE'
AND S.SALARY >= 30000
AND M.CITY = 'INDIANAPOLIS'
AND M.NAME LIKE 'SMITH%';
Day 16, "Using Views to Retrieve Useful Information
from the Data Dictionary"
Quiz Answers
- 1. In Oracle, how can you find out what tables and views you own?
By selecting from USER_CATALOG or CAT. The name of the data
dictionary object will vary by implementation, but all versions have basically the
same information about objects such as tables and views.
2. What types of information are stored in the data dictionary?
Database design, user statistics, processes, objects, growth of objects, performance
statistics, stored SQL code, database security.
3. How can you use performance statistics?
Performance statistics suggest ways to improve database performance by modifying
database parameters and streamlining SQL, which may also include the use of indexes
and an evaluation of their efficiency.
4. What are some database objects?
Tables, indexes, synonyms, clusters, views.
Exercise Answers
Suppose you are managing a small to medium-size database. Your job responsibilities
include developing and managing the database. Another individual is inserting large
amounts of data into a table and receives an error indicating a lack of space. You
must determine the cause of the problem. Does the user's tablespace quota need to
be increased, or do you need to allocate more space to the tablespace? Prepare a
step-by-step list that explains how you will gather the necessary information from
the data dictionary. You do not need to list specific table or view names.
- 1. Look up the error in your database documentation.
2. Query the data dictionary for information on the table, its current
size, tablespace quota on the user, and space allocated in the tablespace (the tablespace
that holds the target table).
3. Determine how much space the user needs to finish inserting the data.
4. What is the real problem? Does the user's tablespace quota need to be
increased, or do you need to allocate more space to the tablespace?
5. If the user does not have a sufficient quota, then increase the quota.
If the current tablespace is filled, you may want to allocate more space or move
the target table to a tablespace with more free space.
6. You may decide not to increase the user's quota or not to allocate more
space to the tablespace. In either case you may have to consider purging old data
or archiving the data off to tape.
These steps are not irrevocable. Your action plan may vary depending upon your
company policy or your individual situation.
Day 17, "Using SQL to Generate SQL Statements"
Quiz Answers
- 1. From which two sources can you generate SQL scripts?
You can generate SQL scripts from database tables and the data dictionary.
2. Will the following SQL statement work? Will the generated output work?
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL CNT.SQL
SQL> SELECT 'COUNT(*) FROM ' || TABLE_NAME || ';'
2 FROM CAT
3 /
- Yes the SQL statement will generate an SQL script, but the generated script will
not work. You need select 'select' in front of count(*):
SELECT 'SELECT COUNT(*) FROM ' || TABLE_NAME || ';'
- Otherwise, your output will look like this:
COUNT(*) FROM TABLE_NAME;
- which is not a valid SQL statement.
3. Will the following SQL statement work? Will the generated output work?
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL GRANT.SQL
SQL> SELECT 'GRANT CONNECT DBA TO ' || USERNAME || ';'
2 FROM SYS.DBA_USERS
3 WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')
4 /
- Once again, yes and no. The statement will generate an SQL script, but the SQL
that it generates will be incomplete. You need to add a comma between the privileges
CONNECT and DBA:
SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'
- 4. Will the following SQL statement work? Will the generated output work?
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SELECT 'GRANT CONNECT, DBA TO ' || USERNAME || ';'
2 FROM SYS.DBA_USERS
3 WHERE USERNAME NOT IN ('SYS','SYSTEM','SCOTT')
4 /
- Yes. The syntax of the main statement is valid, and the SQL that will be generated
will grant CONNECT and DBA to all users selected.
5. True or False: It is best to set feedback on when generating
SQL.
False. You do not care how many rows are being selected, as that will not be part
of the syntax of your generated statements.
6. True or False: When generating SQL from SQL, always spool to a list
or log file for a record of what happened.
False. You should spool to an .sql file, or whatever your naming convention
is for an SQL file. However, you may choose to spool within your generated file.
7. True or False: Before generating SQL to truncate tables, you should
always make sure you have a good backup of the tables.
True. Just to be safe.
8. What is the ed command?
The ed command takes you into a full screen text editor. ed
is very similar to vi on a UNIX system and appears like a Windows Notepad
file.
9. What does the spool off command do?
The spool off command closes an open spool file.
Exercise Answers
- 1. Using the SYS.DBA_USERS view (Personal Oracle7), create an SQL statement
that will generate a series of GRANT statements to five new users: John,
Kevin, Ryan, Ron, and Chris. Use the column called USERNAME. Grant them
Select access to history_tbl.
SQL> SET ECHO OFF
SQL> SET FEEDBACK OFF
SQL> SPOOL GRANTS.SQL
SQL> SELECT 'GRANT SELECT ON HISTORY_TBL TO ' || USERNAME || ';'
2 FROM SYS.DBA_USERS
3 WHERE USERNAME IN ('JOHN','KEVIN','RYAN','RON','CHRIS')
4 /
grant select on history_tbl to JOHN;
grant select on history_tbl to KEVIN;
grant select on history_tbl to RYAN;
grant select on history_tbl to RON;
grant select on history_tbl to CHRIS;
- 2. Using the examples in this chapter as guidelines, create some SQL statements
that will generate SQL that you can use.
There are no wrong answers as long as the syntax is correct in your generated
statements.
WARNING: Until you completely understand
the concepts presented in this chapter, take caution when generating SQL statements
that will modify existing data or database structures.
Day 18, "PL/SQL: An Introduction"
Quiz Answers
- 1. How is a database trigger used?
A database trigger takes a specified action when data in a specified table is
manipulated. For instance, if you make a change to a table, a trigger could insert
a row of data into a history table to audit the change.
2. Can related procedures be stored together?
Related procedures may be stored together in a package.
3. True or False: Data Manipulation Language can be used in a PL/SQL statement.
True.
4. True or False: Data Definition Language can be used in a PL/SQL statement.
False. DDL cannot be used in a PL/SQL statement. It is not a good idea to automate
the process of making structural changes to a database.
5. Is text output directly a part of the PL/SQL syntax?
Text output is not directly a part of the language of PL/SQL; however, text output
is supported by the standard package DBMS_OUTPUT.
6. List the three major parts of a PL/SQL statement.
DECLARE section, PROCEDURE section, EXCEPTION section.
7. List the commands that are associated with cursor control.
DECLARE, OPEN, FETCH, CLOSE.
Exercise Answers
- 1. Declare a variable called HourlyPay in which the maximum accepted
value is 99.99/hour.
DECLARE
HourlyPay number(4,2);
- 2. Define a cursor whose content is all the data in the CUSTOMER_TABLE
where the CITY is INDIANAPOLIS.
DECLARE
cursor c1 is
select * from customer_table
where city = 'INDIANAPOLIS';
- 3. Define an exception called UnknownCode.
DECLARE
UnknownCode EXCEPTION;
- 4. Write a statement that will set the AMT in the AMOUNT_TABLE
to 10 if CODE is A, set the AMT to 20
if CODE is B, and raise an exception called UnknownCode
if CODE is neither A nor B. The table has one row.
IF ( CODE = 'A' ) THEN
update AMOUNT_TABLE
set AMT = 10;
ELSIF ( CODE = 'B' ) THEN
update AMOUNT_TABLE
set AMT = 20;
ELSE
raise UnknownCode;
END IF;
Day 19, "Transact-SQL: An Introduction"
Quiz Answers
- 1. True or False: The use of the word SQL in Oracle's PL/SQL and Microsoft/Sybase's
Transact-SQL implies that these products are fully compliant with the ANSI standard.
False. The word is not protected by copyright. The products mentioned do comply
with much of the ANSI standard, but they do not fully comply with everything in that
standard.
2. True or False: Static SQL is less flexible than Dynamic SQL, although
the performance of static SQL can be better.
True. Static SQL requires the use of a precompiler, and its queries cannot be
prepared at runtime. Therefore, static SQL is less flexible than dynamic SQL, but
because the query is already processed, the performance can be better.
Exercise Answers
- 1. If you are not using Sybase/Microsoft SQL Server, compare your product's
extensions to ANSI SQL to the extensions mentioned today.
Because nearly all of Day 19 deals with Transact-SQL, we did not explore the many
other extensions to ANSI SQL. Most documentation that accompanies database products
makes some effort to point out any SQL extensions provided. Keep in mind that using
these extensions will make porting your queries to other databases more difficult.
2. Write a brief set of statements that will check for the existence of
some condition. If this condition is true, perform some operation. Otherwise, perform
another operation.
This operation requires an IF statement. There are no wrong answers as
long as you follow the syntax for logical statements (IF statements) discussed
today.
Day 20, "SQL*Plus"
Quiz Answers
- 1. Which commands can modify your preferences for an SQL session?
SET commands change the settings available with your SQL session.
2. Can your SQL script prompt a user for a parameter and execute the SQL
statement using the entered parameter?
Yes. Your script can accept parameters from a user and pass them into variables.
3. If you are creating a summarized report on entries in a CUSTOMER
table, how would you group your data for your report?
You would probably break your groups by customer because you are selecting from
the CUSTOMER table.
4. Are there limitations to what you can have in your LOGIN.SQL
file?
The only limitations are that the text in your LOGIN.SQL file must be
valid SQL and SQL*Plus commands.
5. True or False: The DECODE function is the equivalent of a loop
in a procedural programming language.
False. DECODE is like an IF...THEN statement.
6. True or False: If you spool the output of your query to an existing
file, your output will be appended to that file.
False. The new output will overwrite the original file.
Exercise Answers
- 1. Using the PRODUCTS table at the beginning of Day 20, write
a query that will select all data and compute a count of the records returned on
the report without using the SET FEEDBACK ON command.
compute sum of count(*) on report
break on report
select product_id, product_name, unit_cost, count(*)
from products
group by product_id, product_name, unit_cost;
- 2. Suppose today is Monday, May 12, 1998. Write a query that will produce
the following output:
Today is Monday, May 12 1998
Answer:
set heading off
select to_char(sysdate,' "Today is "Day, Month dd yyyy')
from dual;
- 3. Use the following SQL statement for this exercise:
1 select *
2 from orders
3 where customer_id = '001'
4* order by customer_id;
Without retyping the statement in the SQL buffer, change
the table in the FROM clause to the CUSTOMER table:
l2
c/orders/customer
- Now append DESC to the ORDER BY clause:
l4
append DESC
Day 21, "Common SQL Mistakes/Errors and Resolutions"
Quiz Answers
- 1. A user calls and says, "I can't sign on to the database. But everything
was working fine yesterday. The error says invalid user/password. Can you help me?"
What steps should you take?
At first you would think to yourself, yeah sure, you just forgot your password.
But this error can be returned if a front-end application cannot connect to the database.
However, if you know the database is up and functional, just change the password
by using the ALTER USER command and tell the user what the new password
is.
2. Why should tables have storage clauses and a tablespace destination?
In order for tables not to take the default settings for storage, you must include
the storage clause. Otherwise medium to large tables will fill up and take extents,
causing slower performance. They also may run out of space, causing a halt to your
work until the DBA can fix the space problem.
Exercise Answers
- 1. Suppose you are logged on to the database as SYSTEM, and you
wish to drop a table called HISTORY in your schema. Your regular user ID
is JSMITH. What is the correct syntax to drop this table?
Because you are signed on as SYSTEM, be sure to qualify the table by including
the table owner. If you do not specify the table owner, you could accidentally drop
a table called HISTORY in the SYSTEM schema, if it exists.
SQL> DROP TABLE JSMITH.HISTORY;
- 2. Correct the following error:
INPUT:
SQL> select sysdate DATE
2 from dual;
OUTPUT:
select sysdate DATE
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
- DATE is a reserved word in Oracle SQL. If you want to name a column
heading DATE, then you must use double quotation marks: "DATE".
© Copyright, Macmillan Computer Publishing. All
rights reserved.
|
