SQL chapter 5

As you do the exercises, unless it is stated otherwise, you will be using the tables from our standard Student- Course database. Also, as you do the exercises, it will be a good idea to copy/paste your query as well as your query result into a word processor.

a. Repeat the CREATE VIEW part of the exercise in 5-2c with a column name in the CREATE OR REPLACE VIEW statement like this:

using the Stutab table you created in exercise 5-2. Display the view. What is the effect of the (Na, Ma)?

b. Repeat exercise 5-3a, but this time use column aliases in the view definition. Are there any differences noted between explicitly defining the view-columns in the heading part of the statement versus using a column alias?

Perform an experiment to determine the precedence in a query with three conditions linked by AND and OR. Find out whether AND, OR, or left-to-right take precedence? Use statements like this (actual attribute names may be found using DESC Student):

Run this query:

SELECT FROM WHERE

* Student stno < 100 AND major = COSC OR major = ACCT;

Then run the following two queries and answer whether the non-parenthesized statement gives you:

SELECT FROM WHERE

or:

SELECT FROM WHERE

* Student (stno < 100 AND major = COSC) OR major = ACCT;

* Student stno < 100 AND (major = COSC OR major = ACCT);

What happens if you put the OR first instead of the AND and run the query without parentheses? 5-5. Develop a query to find the instructor name and course name for computer science courses

(use the Section table). Order the table by instructor name.

a. Convert your query into a view.

b. Remove the ORDER BY clause and convert the query into an inline view with column aliases and test it.

c. Put the ORDER BY clause outside of the inline view in the main query and run your query again.