Learning how to work with complex queries, views, and synonyms

SQL provides powerful ways to summarize data by counting rows, computing averages, sums and so on. Aggregate functions include statistical functions like min, max, sddev and so on. They are used to calculate totals over entire columns or groups of rows. For example, let us say that the Book Store Owner wants to know the total number of books sold that cost over $100. The following query would give him this information. The AS keyword provides an alternate name or ALIAS for the calculated value. Regular columns may also be renamed temporarily using the AS keyword. This query returns '12'

SELECT SUM(quantity) AS TotalBooks from orderdetails WHERE unitprice >= 100;

To calculate the average price of all books sold that cost over $100, the owner may use the following query (this returns 113.5). The round function is used to round the returned average to two decimal places:

SELECT round(avg(unitprice),2) AS AveragePrice from orderdetails WHERE unitprice >= 100;

The GROUP BY clause offers a way to group date in relevant columns to calculate sums, count, averages etc. by group. For example, the following command returns a subjectwise average book price:

SELECT subjectid,round(avg(price),2) AS AveragePrice from book GROUP BY subjectid order by subjectid;

Following is a table that lists out common SQL aggregate functions. Try out the examples in sql *plus to see the results

Function	Example	Explanation (Result)
AVG(column)	SELECT AVG(price) from book where pubdate >='1-Jan-2000';	The average price of books published in or after 2000
COUNT(column)	SELECT Count(bookid) from book where subjectid = 1;	The total number of books on subject 1.
MAX(column)	SELECT MAX(quantity) from orderdetails;	The largest order for a single book
MIN(column)	SELECT MIN(price) from book;	The lowest priced book
SUM(column)	SELECT SUM(unitprice*quantity) from orderdetails group by orderid	The value of each order before application of discount

Note: Null values are ignored by all functions other than count.

SQL allows nested queries in which the result of one query is obtained via a sub-query.

• The following query returns all books that belong to subjects that come under the 'Fiction' category with ID 1.

  select title, price from book where subjectid in (select subjectid from subject where categoryid = 1)

• The following query returns details of all orders made during the current year.

  select * from orderdetails where orderid in (select orderid from custorder where orderdate >= '1-Jan-2006')

The any and all keywords are used in subqueries to evaluate query results. For example, the following subquery returns the title and price of books that cost more than all the books with subject = 1.

select title, price from book where price > all (select price from book where subjectid = 1)

Let us consider the following query: Give me the authors and subjects of all the books under category fiction

How do we get this information?

• The book table contains book titles, the author and subject ids but no information about categories
• The category table simply contains category description and id.
• The author table contains author information alone.
• The subject table contains information about categories

To get this information using SQL, we JOIN the four concerned tables together. JOIN can be confusing because the actual JOIN clause is not really necessary while writing JOIN queries. However, once we have the fundamentals of combining tables straight, using JOIN becomes easy and intuitive. First, let us look at the query that will answer the above question. Partial results follow the query; 64 rows are returned in all:

SELECT title, subjectdesc,
lastname||', '||firstname AS AUTHOR 
FROM book, author, subject, category
WHERE category.categorydesc like 'Fiction' 
AND book.authorid = author.authorid
AND book.subjectid = subject.subjectid
AND subject.categoryid = category.categoryid;

TITLE                               SUBJECTDESC     AUTHOR
----------------------------------- --------------- -------------------
Adventures of Huckleberry Finn, The Children and Yo Twain, Mark
                                    ung Adult

Adventures of Tom Sawyer, The       Children and Yo Twain, Mark
                                    ung Adult

Alice's Adventures in Wonderland    Children and Yo Carroll, Lewis
                                    ung Adult

All Quiet on the Western Front      Historical      Remarque, Erich Mar
                                                    ia

What was the problem we had? The table that we needed information from contained only IDs and not names. The filter provided was a string (Fiction) and the output sought was also string data and descriptions. The author ID to name mapping is in the author table while the subjectid to name mapping is in the subject table. A good understanding of the concept of foreign keys is essential to writing JOIN queries in SQL as most common JOINs are performed using primary key-foreign key pairs. When a primary key of a table appears as a column in any other table, it is called a foreign key. In the book table, we have two foreign keys: subjectid and authorid. To hook up tables, all we have to do is hookup the foreign key in one table with the corresponding primary key in the other table after the WHERE clause. This is exactly what the following parts of the command did

AND book.authorid = author.authorid
AND book.subjectid = subject.subjectid
AND subject.categoryid = category.categoryid; 

In each case, we used the syntax Table Name.Column Name in order to not confuse the database engine - the names of the columns are the same, so this is the only way to distinguish between them.

Also, we listed all concerned tables (book, author, subject, and category) after the FROM clause with a comma between each table's name. The hooking up of the foreign keys with primary keys and the listing of all the tables’ names constitute the bulk of the join. What is left over is simply setting the input and getting the output.

SELECT title, subjectdesc,  lastname||', '||firstname AS AUTHOR

The above statement gets us the output we want. We combine the lastname of the author with the firstname and a comma in between through the '||' concatenation operator and assign the alias 'AUTHOR; to this field. Interestingly, specifying the table name before the column name is unnecessary here because there is only one column called 'title' and one column called 'lastname' in all three tables. The following line indicates our input condition:

WHERE category.categorydesc LIKE 'Fiction' 

Here are a few more joins that you can try out at the sql*plus prompt.

• SELECT title, subjectdesc,
  lastname||', '||firstname AS AUTHOR 
  FROM book, author, subject, category
  WHERE category.categorydesc like 'Fiction' 
  AND book.authorid = author.authorid
  AND book.subjectid = subject.subjectid
  AND subject.categoryid = category.categoryid
  ORDER BY title;

  All this command does is perform the same JOIN we looked at earlier. Additionally, it sorts the result by title.
  
  

• SELECT custorder.orderid, lastname, firstname,
      sum(unitprice*quantity) 
  	from customer, custorder,orderdetails
  	where customer.customerid = custorder.customerid
  	and custorder.orderid = orderdetails.orderid
  	group by custorder.orderid, customer.lastname, customer.firstname
  	order by custorder.orderid;

  
  
  The above query returns the value of each customer's total order with the customer name.
  
  

• SELECT lastname, firstname, orderdate, orderid from 
      custorder, customer
  	where custorder.customerid = customer.customerid
  	order by orderdate;

  
  
  Retrieves orders with dates for each customer
  
  

When the JOIN keyword is left out, SQL performs an INNER JOIN by default. That is, for each hooked up column, it brings up only the rows of information that match conditions in both tables. A LEFT JOIN brings up all the rows of the table on the left hand side of the LEFT JOIN keyword and only rows that where both fields are equal from the table on the right, while a RIGHT JOIN brings up all the rows of the table on the right hand side of the RIGHT JOIN keyword and only rows where both fields are equal from the table on the left. Let us use an example outside our database to understand the differences between the three types of joins.

Table Authors:

AuthorID	Name
01	Sample Author 1
02	Sample Author 2
03	Sample Author 3
04	Sample Author 4

Table SubjectAuthors:

SubjectID	SubjectName	AuthorID
1	Horror	04
2	Children	04
3	Western	02
4	Adventure

Join Types

INNER

SELECT Authors.Name, SubjectAuthors.SubjectName
FROM Authors, SubjectAuthors
WHERE Authors.AuthorID = SubjectAuthors.AuthorID

Output:

Name	SubjectName
Sample Author 4	Horror
Sample Author 4	Children
Sample Author 2	Western

LEFT

SELECT Authors.Name, SubjectAuthors.SubjectName
FROM Authors LEFT JOIN SubjectAuthors
WHERE Authors.AuthorID = SubjectAuthors.AuthorID

Output:

Name	SubjectName
Sample Author 1
Sample Author 2	Western
Sample Author 3
Sample Author 4	Horror
Sample Author 4	Children

RIGHT

SELECT Authors.Name, SubjectAuthors.SubjectName
FROM Authors RIGHT JOIN SubjectAuthors
WHERE Authors.AuthorID = SubjectAuthors.AuthorID

Output:

Name	Product
Sample Author 2	Western
Sample Author 4	Horror
Sample Author 4	Children
	Adventure

The SQL command 'CREATE VIEW' is used to create an Oracle view or virtual table. The optional REPLACE clause overwrites an existing view with the same name. The view displays data in a unique or useful way and may be treated just like a table. Rows may be retrieved through select statements and may even be modified. However, the rows of an oracle view are not stored physically; the select statement that defines the view and executed when the view is invoked and its results are provided as a virtual table. Note that insert, update, or delete operations may not be performed on views that contain joins, aggregate function results, group by, or distinct clauses as part of their definition. The delete command may be used to delete an existing view. The following view shows books with authors and subjects. Note that we use the '||' operator to concatenate the lastname and firstname columns in the author table with a comma in between. Partial results of running 'select * from bookinfo' are shown below the view creation command.

CREATE OR REPLACE VIEW bookinfo AS
SELECT title, lastname || ', ' || firstname AS AUTHOR, subjectdesc
FROM book, author, subject 
WHERE book.authorid = author.authorid
AND book.subjectid = subject.subjectid;

TITLE                                AUTHOR                  SUBJECTDESC
------------------------------------ -------------------     ---------------
1000 Years, 1000 People: Ranking the Gottlieb, Agnes Hooper   Autobiography 
Men and Women Who Shaped the                                  and Biography
Millennium

A Brief History of Time             Hawking, Stephen          Science
Adventures of Huckleberry Finn, The Twain, Mark               Children and 
                                                              Young Adult

Adventures of the Rat Family        Verne, Jules              Original 
                                                              Creation

Sometimes, a user may require a large set of data from a remote server. In these cases, it may take a lot of time for him to perform read operations or view the data by issuing individual select statements. He may create a local 'snapshot' or materialized view of the data that updates once every few hours or days (as specified by him). The results of the query that define a materialized view are actually stored at time of creation. The user may use this copy to view and analyze the remote data. Following is a command that creates a materialized view of the orderdetails table for a manager on a remote server who needs to analyze this data. The data in the snapshot refreshes every two days.

CREATE MATERIALIZED VIEW ord_details_mv
REFRESH FAST 
START WITH SYSDATE 
NEXT  SYSDATE + 2
WITH PRIMARY KEY 
AS SELECT * FROM orderdetails@remote_booksdb;

An Oracle synonym is an alternative name or alias for a database object. The CREATE OR REPLACE SYNONYM command creates a new synonym or replaces an existing synonym. Synonyms are used to refer to frequently used tables and other objects in other schemas or remote databases. The short synonym, in these cases, may be used instead of the long object reference of the form <schema_name>.<object_name> or <schema_name>.<object_name>@<remote_dblink>. A public clause makes the synonym available to all users who have access to the underlying object. The following synonym allows all users who have access to the book table to refer to the table directly rather than having to prefix the schema name.

create public synonym book
for booksdb.book;