Banner

 

10 - SQL

Answers to exercises

1.
Why is it important that SQL was adopted as a standard by ANSI and ISO?

Since ISO and ANSI are two of the major international standards setting institutions, a standard that is adopted by both of them will be widely used by software manufacturers and thus, end users.

3.
Is SQL a complete programming language? What are the implications of your answer?

SQL is not a complete programming language since it cannot be used to write computer programs and procedures. However, SQL can be embedded in standard programming languages such as COBOL and C.

5.
What is the difference between a base table and a view?

A base table is an autonomous, named table. It is autonomous because it exists in its own right. Views are derived from one or more base tables and actually do not exist independently of the base table. A view is also called a virtual table.

7.
What is the purpose of an index?

An index is an ordered set of pointers to rows of a base table. It stores data about the position of the data in a certain column of a base table. Indexes are used to accelerate data access and ensure uniqueness.

9.
Specify the data type (e.g., DECIMAL(6,2)) you would use for the following columns:

(To answer these questions we assume US data. In other countries the data format might vary slightly)

9a.
The selling price of a house.
DECIMAL(10,2)
9c.
Hourly temperatures in Antarctica.
DECIMAL(6,2)
9e.
A credit card number.
CHAR(16)
9g.
A sentence using Chinese characters.
VARGRAPHIC
9i.
The text of an advertisement in the classified section of a newspaper.
LONG VARCHAR
9k.
The title of a CD.
VARCHAR(50)
9q.
The time of arrival of an e-mail message.
TIMESTAMP
11.
Give some reasons for creating a view.

A view can be created for a variety of reasons:

13.
Write SQL codes to create a unique index on firm name for the shr table defined in Chapter 3. Would it make sense to create a unique index for PE ratio in the same table?
CREATE UNIQUE INDEX firmname ON shr (shrfirm); 

a. No, it would not make sense to create a unique index for PE since two companies might have the same PE.

15.
What is the difference between an equijoin and a natural join?

An equijoin table contains two identical columns. If one of these two columns is dropped, the remaining table is called a natural join.

17.
What function would you use for the following situations?
17a.
Computing the total value of a column.
SUM
17c.
Counting the number of customers in the customer table.
COUNT
17e.
Reporting the month part of a date.
MONTH
17g.
Retrieving the first five characters of a city's name.
SUBSTR(cityname,1,5)
18.
Write SQL statements for the following:
18a.
Let Hui-Tze query and add to the nation table.
GRANT SELECT, INSERT ON nation TO hui-tze; 
18c.
Remove all of William's privileges.
REVOKE ALL ON tablename FROM william; 
18e.
Find the names of all tables that include the word sale.
SELECT TABLE_NAME FROM TABLES WHERE TABLE_NAME REGEXP 'Sale';
18g.
What is the maximum length of the column city in the ClassicModels database? Why do you get multiple rows in the response?
SELECT CHARACTER_MAXIMUM_LENGTH FROM COLUMNS WHERE COLUMN_NAME ='City' AND TABLE_SCHEMA = 'ClassicModels';
19.
What are the two modes in which you can use SQL?

SQL can be used as an interactive query language or embedded in an application program.

21.
How do procedural programming languages and SQL differ in the way they process data? How is this difference handled in an application program? What is embedded SQL?

SQL processes one table at a time while COBOL processes one record at a time. Thus, COBOL uses a loop to process each record of a table.

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 02-Dec-2022