multiple tables
Exercise 1 feedback 2 a)
SELECT Prod_desc FROM product WHERE Prod_no IN(SELECT item.Prod_no FROM item WHERE item.Order_no IN(SELECT Order_no FROM porder WHERE porder.Cust_no IN(SELECT Cust_no FROM customer
WHERE Cust_name = ‘Asda’)));
b) SELECT Cust_name FROM customer WHERE Cust_no IN(SELECT porder.Cust_no FROM porder
WHERE Order_date > TO_DATE(‘31-DEC-2011’,’DD-MON-YYYY’) AND Order_date < TO_DATE(‘01-JAN-2013’,’DD-MON-YYYY’)); c) SELECT Cust_name FROM customer WHERE customer.Cust_no IN(SELECT porder.Cust_no FROM porder WHERE porder.Order_no IN(SELECT item.Order_no FROM item WHERE item.Prod_no IN(SELECT Prod_no FROM product
WHERE Prod_desc = ‘Chocolate’)));
Group functions
A group function operates on a group of rows and returns a single result. Suppose you wanted to find out which cd cost the most, or what the total value of all your cds is, these queries can be answered using the group functions.
Function Description
SUM(column_name) Calculates the total value of a column, null values are ignored. AVG(column_name) Finds the average of all the values in a column, null values are ignored. MAX(column_name | expression) Finds the maximum value in a column or expression, null values are ignored. MIN(column_name | expression) Finds the minimum value in a column or expression, null values are ignored. COUNT(* | column_name | expression) * Counts the number of rows, including nulls
If a column or expression is used counts non null values.
A practical introduction using Oracle SQL Subqueries and group functions
Here is an example showing the average, maximum, minimum and total for the cd prices: SELECT AVG(cd_price), MAX(cd_price), MIN(cd_price), SUM(cd_price) FROM cd;
Sometimes it is necessary to apply group functions to separate groups of rows rather than all the rows. The GROUP BY clause can be used to group the rows. The format is as follows:
SELECT column_name, group function (column_name) FROM table_name
[WHERE condition(s)]
[GROUP BY column_name | expression]
[ORDER BY column_name | expression [ASC | DESC]];
When using the GROUP BY clause the columns in SELECT must also appear in the GROUP BY. The WHERE clause cannot be used to restrict groups, but it can be used to restrict the data before the grouping. By default GROUP BY when used with a column will output results in ascending order.
A practical introduction using Oracle SQL
127
Subqueries and group functions
Here is an example of a GROUP BY which will tell you how many tracks you have for each music category: SELECT track_cat_ID, COUNT(*) “no of tracks”
FROM track
GROUP BY track_cat_ID;
In order to restrict groups, the HAVING clause can be used, so suppose you only wanted to see the number of tracks for music categories that had more than 16 tracks, the following could be used:
SELECT track_cat_ID, COUNT(*) “no of tracks” FROM track
GROUP BY track_cat_ID HAVING COUNT(*) > 16;
Exercise 2
Write SELECT statements to answer the following: a) Count the total number of tracks.
b) Show the maximum and minimum track lengths.
c) Display the average cd price by company for any companies with an average price < 10.
Exercise 2 feedback
a) Count the total number of tracks. SELECT COUNT(*)
FROM track;
b) Show the maximum and minimum track lengths. SELECT MAX(track_length), MIN(track_length) FROM track;
c) Display the average cd price by company for any companies with an average price < 10. SELECT AVG(cd_price)
FROM cd
GROUP BY cd_company_ID HAVING AVG(cd_price) < 10;
You have now been introduced to the main ORACLE SQL commands and some of the built-in functions, so you should now be able to search for and try some of the other SQL commands and functions.
A practical introduction using Oracle SQL Subqueries and group functions
There are many SQL resources available on the web which can assist you to develop your skills including:- An Oracle database SQL language reference can be found here:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/toc.htm
For more help with Oracle APEX try here:
https://community.oracle.com/community/database/developer-tools/application_express
EXPERIENCE THE POWER OF
FULL ENGAGEMENT…
RUN FASTER.
RUN LONGER..
RUN EASIER…
READ MORE & PRE-ORDER TODAY WWW.GAITEYE.COMA practical introduction using Oracle SQL
129
Creating pages & reports
11 Creating pages & reports
On completion of this chapter you should be able to: • create a simple master detail form page • create a simple report.
Introduction
Many users of databases do not directly access them using SQL but instead use applications consisting of easy to use web Pages (sometimes called Forms) and reports, which allow controlled access to the data. The following is a basic introduction to using APEX for building a simple page and report. You will be using the company and cd tables to build a master (company) detail (cd) page which will represent the 1:M relationship. This can also be referred to as a parent (one) child (many) relationship between company and cd. The page will show all the cds produced for a selected company.
A Master Detail Page
A practical introduction using Oracle SQL Creating pages & reports
Before you can build the master detail page you need to ensure that you have set up sequences for the tables being used. Database sequences are database objects that act like counters and are used to automatically generate integer values. The sequence value is used to automatically populate the primary key column when a row is added to the table.
In order to create the company and cd tables with sequences for their primary keys you are going to drop the existing company and cd tables and create new versions, this time using the SQL Workshop object browser.
1. In the SQL Commands tool drop the company and cd tables using cascade constraints: DROP TABLE company CASCADE CONSTRAINTS;
DROP TABLE cd CASCADE CONSTRAINTS;
You will need to set up the company table before the cd table as there is a foreign key cd_company_ID in the cd table that references the company table company_ID.
2. In the SQL Workshop click on the Object Browser icon.
SQL Workshop
3. Choose the Tables objects.
A practical introduction using Oracle SQL
131
Creating pages & reports
4. Click on the + to add a Table.
Add table
Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more Click on the ad to read more
PDF components for PHP developers
www.setasign.com
SETASIGN
This e-book
is made with
A practical introduction using Oracle SQL Creating pages & reports
5. Enter the table name company and the column names, data types and sizes (use scale) as per the specification in Appendix B. To select Not Null for a column just tick the Not Null check box. Then click the Next > button.
A practical introduction using Oracle SQL
133
Creating pages & reports
6. Click on the Populated from a new sequence button then click inside the Primary Key box and select the COMPANY_ID(NUMBER) column from the list of columns. The sequence will automatically be given a constraint name and a sequence name. Click Next >.
Primary Key
7. When the Foreign Key page appears click Next > as there are no foreign keys in this table. 8. When the Constraints page appears click Next> as you are not adding any further constraints
to the table.
A practical introduction using Oracle SQL Creating pages & reports
10. The confirmation page will appear in the Object Browser when the table has been created.
Company Table