1.
What is the difference between DDL and DML?
A DDL (Data Definition Language) is used to define the database structure, i.e., to create new tables or indexes or to add or delete fields to existing tables. A DML (Data Manipulation Language) is used to manipulate the data within an existing database, i.e., to run queries (select statements in SQL) and to insert or delete records.
2.
What is a composite key?
A composite key is a key consisting of more than one attribute.
3.
Why must the primary key of a table be unique?
To be able to uniquely identify each instance of an entity.
4.
Consider an electronic phone book with the following structure. For which fields would an index be useful?
An index would be useful for the fields lastname, street, city, zipcode, and telephone number.
A search on telephone number might be considered an invasion of privacy. A recently published German electronic telephone book, which includes this feature, has led to widespread criticism.
5.
Below is a CREATE TABLE command. Explain the function of each part of the command.
CREATE TABLE STOCK
(STKCODE CHAR(3) NOT NULL,
STKFIRM CHAR(20),
STKPRICES DECIMAL(6,2),
STKQTY DECIMAL(8),
STKDIV DECIMAL(5,2),
STKPE DECIMAL(5),
NATCODE CHAR(3),
PRIMARY KEY (STKCODE),
FOREIGN KEY FKNATION(NATCODREFERENCES NATION
ON DELETE RESTRICT)
CREATE TABLE STOCK
(STKCODE CHAR(3) NOT NULL,
STKFIRM CHAR(20),
STKPRICES DECIMAL(6,2), STKQTY DECIMAL(8), STKDIV DECIMAL(5,2), STKPE DECIMAL(5), NATCODE CHAR(3), PRIMARY KEY (STKCODE),
FOREIGN KEY FKNATION(NATCODREFERENCES NATION
ON DELETE RESTRICT)
6.
Explain the difference between a product and a join of two tables.
A product of a table combines every row of one table with every row of the other table. A join is used to link two tables only where the values of two fields are the same
7.
What is the function of the LIKE clause in SQL?
It supports pattern matching in SQL queries.
8.
What is the difference between zero and null and why is it important to make a clear distinction?
Null does not present a value whereas zero represents the value zero, e.g., nobody bought the item. Null might have several meanings, it could stand for "no value supplied" or for "value undefined", i.e., the value is not known or there is no value applicable. The user has to infer which meaning is appropriate.
9.
How do you give and take user privileges using SQL?
Using the GRANT and the REVOKE commands.
10.
What will be the major changes SQL 3 will bring?
SQL 3 will support object oriented databases and user defined abstract data types (ADT).
11.
Specify the data type (e.g. DECIMAL (6,2)) you would use for the following columns:
11a. Weight of a person
11b. Day of marriage
11c. Water usage of a city in gallons
11d. Title of a person
11e. X-ray of a person
12.
Explain the following SQL functions:
12a. CHAR
Converts a date, time, or timestamp to a character representation
12b. HOUR
Retrieves the hour portion of a time or timestamp.
12c. DAYS
Converts a date or timestamp to a number of days. Useful for calculating the number of days between two dates.
12d. SUBSTR
Extracts a substring from a string. SUBSTR (NATNAME, 1,5) gives the first five characters of a nation's name.
12e. FLOAT
Converts a number to floating point representation.
12f. DAY
Retrieves the day portion of a date or timestamp.
12g. DECIMAL
DECIMAL (n,p,d) reports a number n with specified precision p and d decimal places.
| 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 |