- 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
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:
- to restrict access to certain rows or columns
- to handle derived data (data conversion)
- to avoid writing common SQL queries
- 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
