Ask Rick

Given a relation with a set of attributes and a set of tuples is it possible to have an empty set of attributes or tuples?

You can have a relation (table) without any tuples (rows). An SQL query that returns no rows is an example of such a relation.

You cannot have a relation without any attributes (columns). Since every relation must have a unique primary key, you must have at least one column to define the primay key.

A sentence in chapter 3 states, "For example, the value 1066 stored in a character field of four bytes would be stored as '1066' and the value 45 would be stored as '45 '."

Am I wrong in thinking that you mean four bits as opposed to four bytes?

The data type character means that each character requires one byte of storage, irrespective of whether the character is numeric, alphabetic, or special character. That is, '1' , 'A', and '@' all occupy one byte.

If you know a column always contains numeric data, then choose a numeric data type as this is a more compact form of storage and also avoids confusion with sort order, which is the topic of the mentioned paragraph.

In the product table in Chapter 6, the proddesc with prodid 105 is Photographer's vest.

Since ' is a reserved symbol in SQL, how do I insert Photographer's vest?

In most versions of SQL you can insert a single quote (') by entering two consecutive single quotes.

Thus, you would write

INSERT INTO product VALUES (105,'Photographer''s vest',25,40)

Another approach is

INSERT INTO product VALUES (105,"'Photographer's vest",25,40)

I have a question regarding creating a table

CREATE TABLE nation
(natcode CHAR(3) NOT NULL,
natname CHAR(20),
exchrate DECIMAL(9,5),
PRIMARY KEY(natcode))

Is there any SQL syntax so that I can make natcode in ascending/descending order?

There is no implied ordering in a relational database, and you can not enforce an ordering with the CREATE statement. However, you can use the ORDER BY statement to sort the results of a query on particular columns. Thus, you might write
SELECT * FROM nation ORDER BY natcode

I was going through your SQL playbook and had questions with respect to the exercises towards the end. Is there any possibility of getting the query solutions to the exercise since they are so hard or at least if that's not a possibility, get the answer for question 14.

I publish every second answer to encourage students to attempt the problems. I fear that if I publish every answer, some people may not get enough SQL practice. :)

(I supplied the answer)

Could you please give me a brief idea about semantic data models?

A semantic data model captures the meaning of the data to be stored in a database. It is a database design tool. Semantic modeling is known by many names: data modeling, entity-relationship (E-R) modeling, entity modeling, and object modeling, and there are multiple approaches to capturing meaning.

Is this query keeping with the intent of 61 in Reference 2?

"Is it true that all the departments that sell items of type C are located on the third floor?"

The result of the following query be a Boolean 1 or 0, meaning yes or no.

SELECT count( qitem.itemtype )
FROM qitem
WHERE qitem.itemtype = 'C'
AND 1 <= (
SELECT count( * )
FROM qitem, qsale, qdept
WHERE qitem.itemtype = 'C'
AND qitem.itemname = qsale.itemname
AND qdept.deptname = qsale.deptname
AND qdept.deptfloor =3)
AND 0 = 
(SELECT count( * )
FROM qitem, qsale, qdept
WHERE qitem.itemtype = 'C'
AND qitem.itemname = qsale.itemname
AND qdept.deptname = qsale.deptname
AND qdept.deptfloor <>3) 

This is a clever piece of coding and while it guarantees that the resulting output is 0 or 1, this is not the same as returning true or false. Furthermore, it returns an integer and not a Boolean. As a result, if this this query were a subquery in a larger query, the output could not be tested to see if it were true or false.

However, if this code were embedded in a programming language. You could use the value returned (i.e., 0 or 1) to in effect test for true or false.

The book has STOCK PRICE X STOCK QTY X EXCHANGE RATE. I think it should be STOCK PRICE X STOCK QTY DIVIDED BY EXCHANGE RATE.

The exchange rates in the example are based on the UK pounds (GBP), which is why it is 1.0

Thus, to compute the value of a country's stock in UK pounds, you use STOCK PRICE * STOCK QTY * EXCHANGE RATE.

Consider Narembeen Emu, an Australian stock. The value in AUD is 12.34*45619 and since 1 AUD = 0.46 GBP, the value in GBP is 12.34*45619*0.46

Exchange rates can be expressed either as multipliers or divisors. In this case, I used the multiplier approach.

How do I run SQL queries in MS Access?

1. Click on the Queries tab
2. Click on New
3. Click on Design View
4. Click on Close
5. Click on SQL (top left of menu bar)
6. Click on the red exclamation mark (!) to execute an SQL command (middle of menu bar)

When using MS Access, CREATE TABLE does not work when written as it is in the text, and nor does the CREATE VIEW command on p. 104. Any suggestions?

In the text, I describe standard SQL, which includes CREATE TABLE and CREATE VIEW commands. MS ACCESS does not have the these commands, but offers the same facility.

You implicitly use CREATE TABLE when you create a new table using MS Access.

Whenever you save a query in MS Access, you create a view. Say you create and save a query called Q1. Then you can query the query using SQL (e.g., SELECT * FROM Q1). In other words, a saved query is a view.