MISCELLANEOUS TOPICS Database Management System

MISCELLANEOUS TOPICS Database Management System

MISCELLANEOUS TOPICS

Indexes

MISCELLANEOUS TOPICS Database Management System : Indexes allow a DBMS to access data quicker (please note: this feature is nonstandard/not available on all Systems). The System creates this internal data structure (the index) which causes selection of rows, when the Selection is based on indexed Columns, to occur faster. This index tells the DBMS where a certain row is in the table given an indexed-Column value, much like a book index tells you what page a given word appears. Let’s create an index for the OwnerID in the AntiqueOwners table :

CREATE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID) ;

Now on the names :

CREATE INDEX NAME_IDX ON ANTIQUEOWNERS (OWNERLASTNAME, OWNERFIRSTNAME) ;

To get rid of an index, drop it :

DROP INDEX OID_IDX ;

By the way, you can also “drop” a table, as well (careful!-that means that your table is deleted). In the Second example, the index is kept on the two Columns, aggregated together-Strange behavior might occur in this situation…check the manual before performing Such an Operation.

Some DBMS’s do not enforce primary keys, in other words, the uniqueness of a column is not enforced automatically. What that means is, if, for example, if we try to insert another row into the AntiqueOwners table with an OwnerID of O2, some Systems will allow me to do that, even though we do not, as that Column is supposed to be unique to that table (every row value is supposed to be different). One way to get around that is to Create a unique index on the Column that we want to be a primary key, to force the System to enforce prohibition of duplicates :

CREATE UNIQUE INDEX OID_IDX ON ANTIQUEOWNERS (OWNERID) ;

GROUP BY & HAVING

One Special use of GROUP BY is to associate an aggregate function (especially COUNT, Counting the number of rows in each group) with groups of rows. First, assume that the Antique stable has the Price Column, and each row has a value for that column. We want to see the price of the most expensive item bought by each owner. We have to tell SOL to group each owner’s purchases, and tell us the maximum purchase price :

SELECT BUYERID, MAX (PRICE)

FROM ANTIQUES

GROUP BY BUYERID ;

Now, say we only want to see the maximum purchase price if the purchase is over $1000, So we use the HAVING Clause :

SELECT BUYERID, MAX (PRICE)

FROM ANTIQUES

GROUP BY BUYERID ;

ΗAVING PRICE > 1000 ;

More Subqueries

Another Common usage of Subdueries involves the use of operators to allow a Where condition to include the Select output of a subquery. First, list the buyers who purchased an expensive item (the Price of the item is $100 greater than the average price of all items purchased):

SELECT BUYERID

FROM ANTIQUES

WHERE PRICE >

(SELECT AVG (PRICE) + 100

FROM ANTIQUES) ;

The subquery calculates the average Price, plus S100, and using that figure, an OwnerID is printed for every item Costing Over that figure. One could use DISTINCTBUYERID, to eliminate duplicates.

List the Last Names of those in the AntiqueOwners table, ONLY if they have bought an item :

SELECT OWNERLASTNAME

FROM ANTIQUEOWNERS

WHERE OWNERID IN

(SELECT DISTINCT BUYERID

FROM ANTIQUES) ;

The subquery returns a list of buyers, and the LastName is printed for an Antique Owner if and only if the Owner’s D appears in the subquery list (sometimes called a candidate list). Note: on Some DBMS’s, equals can be used instead of IN, but for clarity’s sake, since a set is returned from the subquery, IN is the better choice.

For an Update example, we know that the gentleman who bought the bookcase has the wrong First Name in the database…it should be John :

UPDATE ANTIQUEOWNERS

SET OWNERFIRSTNAME = ‘John’

WHERE OWNERID =

(SELECT BUYERID

FROM ANTIQUES

WHERE ITEM = ‘Bookcase’) ;

First, the subquery finds the BuyerID for the person(s) who bought the Bookcase, then the outer query updates his First Name.

Remember this rule about subqueries : When you have a subquery as part of a WHERE condition, the Select clause in the subquery must have columns that match in number and type to those in the Where clause of the outer query. In other words, if you have “WHERE Column Name = (SELECT…);”, the Select must have only one column in it, to match the Column Name in the outer Where clause, and they must match in type (both being integers, both being character strings, etc.).

EXISTS & ALL

EXISTS uses a subquery as a condition, where the condition is True if the Subduery returns any rows, and False if the Subduery does not return any rows; this is anonintuitive feature with few unique uses. However, if a prospective customer wanted to see the list of Owners only if the shop dealt in Chairs, try:

SELECT OWNERFIRSTNAME, OWNERLASTNAME

FROM ANTIQUEOWNERS

WHERE EXISTS

(SELECT *

FROM ANTIQUES

WHERE ITEM = Chair’) ;

If there are any Chairs in the Antiques column, the subquery would return a row or rows, making the EXISTS clause true, causing SOL to list the Antique Owners. If there had been no Chairs, no rows would have been returned by the outside query.

ALL is another unusual feature, as ALL queries can usually be done with different, and possibly simpler methods, let’s take a look at an example query :

SELECT BUYERID, ITEM

FROM ANTIQUES

WHERE PRICE >= ALL

(SELECT PRICE

FROM ANTIQUES) ;

This will return the largest priced item (or more than one item if there is a tie), and its buyer. The subquery returns a list of all Prices in the Antiquestable, and the Outer query goes through each row of the Antique stable, and if its Price is greater than or equal to every (or ALL) Prices in the list, it is listed, giving the highest priced item. The reason “=” must be used is that the highest priced item will be equal to the highest price on the list, because this item is in the Price list.

UNION & Outer Joins (briefly explained)     

There are occasions where you might want to see the results of multiple queries together, Combining their output; use UNION. To merge the output of the following two queries, displaying the ID’s of all Buyers, plus all those who have an Order placed:

SELECT BUYERID

FROM ANTIQUES

UNION

SELECT ONNER ID

FROM ORDERS ;

Notice that SQL requires that the Select list of Columns) must match, Column-by-column, in data type. In this case Buyer D and OwnerID are of the same data type (integer). Also notice that SOL does automatic duplicate elimination when using UNION (as if they were two “sets”); in single queries, you have to use DISTINCT.

The Outer join is used when a join query is “united” with the rows not included in the join, and are especially useful if constant text “flags” are included. First, look at the query :

SELECT OWNERID,  ‘is in both Orders & Antiques’

FROM ORDERS, ANTIQUES

WHERE OWNERID = BUYERID

UNION

SELECT BUYERID, ‘is in Antiques only’

FROM ANTIQUES

WHERE BUYERTID NOT IN

(SELECT OWNERID

FROM ORDERS) ;

The first query does a join to list any owners who are in both tables, and putting a tag line after the ID repeating the quote. The UNION merges this list with the next list. The second list is generated by first listing those ID’s not in the Orders table, thus generating a list of ID’s excluded from the join query. Then, each row in the Antiques table is scanned, and if the BuyerID is not in this exclusion list, it is listed with its quoted tag. There might be an easier way to make this list, but it’s difficult to generate the informational quoted Strings of text.

This concept is useful in situations where a primary key is related to a foreign key, but the foreign key value for some primary keys is NULL. For example, in one table, the primary key is a salesperson, and in another table is customers, with their salesperson listed in the same row. However, if a salesperson has no Customers, that person’s name won’t appear in the customer table. The outer join is used if the listing of all Salespersons is to be printed, listed with their customers, whether the salesperson has a customer or not-that is, no customer is printed (a logical NULL value) if the salesperson has no customers, but is in the Salespersons table. Otherwise, the Salesperson will be listed with each customer.

Another important related point about Nulls having to do with joins: the order of tables listed in the From clause is very important. The rule states that SOL “adds” the second table to the first; the first table listed has any rows where there is a null on the join column displayed; if the Second table has a row with a null on the join Column, that row from the table listed Second does not get joined, and thus included with the first table’s row data. This is another occasion (should you wish that data included in the result) where an Outer join is commonly used. The concept of nulls is important, and it may be worth your time to investigate them further.

Relational Operators and SQL       

Relational operators each have implementations in SQL.

MISCELLANEOUS TOPICS Database Management System

Oueries

Using the example tables in the tutorial, write a SQL statement to :

  1. Show each Antiques order and the last and first names of the person who ordered the item.
  2. Show each column in the Employeestatistics Table in alphabetical order by Position, then by EmployeelDNo.
  3. Show the annual budget for Benefits from the EmployeeStatisticsTable.
  4. Using the IN Operator, show the names of the owners of Chairs.
  5. Show the names of all Antiques Owners who have do not have an order placed.
  6. Show the names of those who have placed Antique Orders, with no duplicates (Hint: Consider the order of tables in the From clause).
  7. Delete all of Bob Smith’s Antique orders (Hint: Bob’s ID Number is 02).
  8. Create an Antique order for a Rocking Chair for Jane Akins (Hint: Jane’s ID Number is 21).
  9. Create a table called Employees, with Columns Employee DNo (don’t worry about trailing zeroes), FirstName, and LastName.
  10. (Challenger) Show the annual budget for Salary by each position from the EmployeeStatisticsTable (Hint: Try GROUP BY).

Databases

  1. What is the relationship between the AntiqueOwners table and the Owners table?
  2. If you do not have a primary key in a table, the addition of what type of column is preferred to give the table a primary key?
  3. Which function will allow you to substitute a given value for any Null values arising from a Select Statement?
  4. What term is used to describe the event of a database system automatically updating the values of foreign keys in other tables, when the value of a primary key is updated?
  5. What database object provides fast access to the data in the rows of a table?
  6. What type of SQL statement is used to change the attributes of a column?
  7. In a Create Table statement, when a Column is designated as NOT NULL, what does this mean?
  8. If you wish to write a query that is based on other queries, rather than tables, what do these other queries need to be created as?

Answers (Queries may have more than one correct answer):

 

  1. SELECT AntiqueOwners. OwnerLastName, AntiqueOwners . OwnerFirstName, Orders. ItemDesired FROM AntiqueOwners, Orders

WHERE Antique Owners. OwnerID = Orders. OwnerID;

or

SELECT AntiqueOwners. OwnerLastName,

AntiqueOwners. OwnerFirstName, Orders. ItemDesired

FROM Anti queOwners RIGHT JOIN Orders ON AntiqueOwners .OwnerID = Orders . OwnerID ;

  1. SELECT *

FROM EmρΙοyeeStatisticsTable

ORDER BY Position, Employee IDNo ;

  1. SELECT Sum (Benefits)

FROM EmployeeStatisticsTable;

  1. SELECT OwnerLastName , OwnerFirstName

FROM AntiqueOwners, Antiques

WHERE Item In (‘Chair’)

AND Antiqueowners. OwnerID = Antiques. BuyerID ;

  1. SELECT OwnerLastName, OwnerFirstName

FROM AntiqueOwners

WHERE OwnerID ΝΟΤ IN

(SELECT OwnerID

FROM Orders ) ;

  1. SELECT DISTINCT OwnerLastName, OwnerFirstName

FROM Orders , AntigueOwners

WHERE Antiqueowners. OwnerID = Orders. OwnerID;

or to use JOIN notation :

SELECT DISTINCT AntiqueOwners . OwnerLastName,

AntiqueOwners.OwnerFirstName

FROM AntiqueOwners RIGHT JOIN Orders ON AntiqueOwners . OwnerID =

Orders . Owner ID);

  1. DEIETE FROM ORDERS

WHERE OWNERID = O2;

  1. INSERT INTO ORDERS VALUES (21, Rocking Chair’ );
  2. CREATE TABLE EMPLOYEES

(EmployeeIDNo INTEGER ΝΟΤ NULL,

FirstName CHAR (40) NOT NULL,

LastName CHAR(40) NOT NULL);

  1. SELECT Position, Sum (Salary)

FROM EmployeeStatisticsTable

GROUP BY Position;

  1. One-to-Many.
  2. An integer identification number; an auto-increment ID is preferred.
  3. A Cursor.
  4. Third Normal Form.
  5. Cascading update.
  6. An Index.
  7. ALTER TABLE.
  8. A Value is required in this column for every row in the table.
  9. Views