A Brief History of Databases

A little background on the evolution of databases and database theory will help you understand the workings of SQL. Database systems store information in every conceivable business environment. From large tracking databases such as airline reservation systems to a child's baseball card collection, database systems store and distribute the data that we depend on. Until the last few years, large database systems could be run only on large mainframe computers. These machines have traditionally been expensive to design, purchase, and maintain. However, today's generation of powerful, inexpensive workstation computers enables programmers to design software that maintains and distributes data quickly and inexpensively

Dr. Codd's 12 Rules for a Relational Database Model

The most popular data storage model is the relational database, which grew from the seminal paper "A Relational Model of Data for Large Shared Data Banks," written by Dr. E. F. Codd in 1970. SQL evolved to service the concepts of the relational database model. Dr. Codd defined 13 rules, oddly enough referred to as Codd's 12 Rules, for the relational model:

0. A relational DBMS must be able to manage databases entirely through its relational capabilities.

1. Information rule-- All information in a relational database (including table and column names) is represented explicitly as values in tables.

2. Guaranteed access--Every value in a relational database is guaranteed to be accessible by using a combination of the table name, primary key value, and column name.

3. Systematic null value support--The DBMS provides systematic support for the treatment of null values (unknown or inapplicable data), distinct from default values, and independent of any domain.

4. Active, online relational catalog--The description of the database and its contents is represented at the logical level as tables and can therefore be queried using the database language.

5. Comprehensive data sublanguage--At least one supported language must have a well-defined syntax and be comprehensive. It must support data definition, manipulation, integrity rules, authorization, and transactions.

6. View updating rule--All views that are theoretically updatable can be updated through the system.

7. Set-level insertion, update, and deletion--The DBMS supports not only set-level retrievals but also set-level inserts, updates, and deletes.

8. Physical data independence--Application programs and ad hoc programs are logically unaffected when physical access methods or storage structures are altered.

9. Logical data independence--Application programs and ad hoc programs are logically unaffected, to the extent possible, when changes are made to the table structures.

10. Integrity independence--The database language must be capable of defining integrity rules. They must be stored in the online catalog, and they cannot be bypassed.

11. Distribution independence--Application programs and ad hoc requests are logically unaffected when data is first distributed or when it is redistributed.

12. Nonsubversion--It must not be possible to bypass the integrity rules defined through the database language by using lower-level languages.

Introduction to SQL

Answers to Quizzes and Exercises

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.)

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;

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;

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;

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?

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.

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

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.

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.

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.

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.

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".

Send mail to jatoione@hotmail.com with questions or comments about this web site.
Copyright © 2001 Jatoi Solution Providers
Last modified: 11/26/96