USING SQL TO MANIPULATE DATABASES
SQL pronounced "S-Q-L" or "sequel" stands for Standard
Query Language is used to create and query relational databases. It has
been implemented in both mainframe and personal computer systems. Sites
on the web such as http://www.llbean.com/
allow users to browse the catalog . The information about an item, such
as size, color, description or availability is stored on database, and
retrieved using an SQL query, but the user has not issued an SQL command.
Thus users can access database applications without knowing SQL.
An SQL-based relational database application involves a user interface, a set of tables, and a relational database management system (RDBMS) with an SQL capability. Within RDBMS, SQL will be used to create the tables, translate user requests, maintain the data dictionary and system catalog, update and maintain the tables, establish security, and carry out backup and recovery procedures. RDBMS is a data management system that implements a relational data model, where data are stored in a collection of tables, and data relationships are represented by common values, not links.
SQL commands can be classified into three types.
1. Data Definition Language (DDL) commands,
used to create, alter, and drop tables. In a production database, the
ability to use DDL commands may be restricted to administrators to protect the
database structure from unexpected changes. In development or student
databases, more users have DDL privileges. Eight SQL DDL commands
are:
|
CREATE TABLE |
Defines a new table and its columns. |
|
DROP TABLE |
Destroys a table. (Only the table creator can delete the table) |
|
ALTER TABLE |
Adds, deletes, column(s), or redefines column's data type |
|
CREATE INDEX |
Defines an index on a column or a concatenation of columns that enable rapid access to the rows of a table in a sequence or randomly by key value. Index may be sequenced in ascending or descending order |
|
DROP INDEX |
Destroys an index. (Only the table creator can delete an index of that table) |
|
CREATE VIEW |
Defines a logical table from one or more tables or views. |
|
DROP VIEW |
Destroys a view definition. (Only the creator of the view's base table can delete a view) |
|
CREATE SCHEMA |
Used +o define that portion of a database that a particle user owns. |
CREATE TABLE tablename
(column_name datatype [NULL | NOT NULL]
[DEFAULT default_value] [column_constraint_clause] . . .
[, column_name datatype [NULL | NOT NULL]
[DEFAULT default_value] [column_constraint_clause] . . .
(table_constraint_clause)
1. Identify the appropriate data type, including length,
precision, and scale if required, for each attribute.
2. Identify those columns that accept null values.
Columns that cannot be null are established when a table is created and
enforced for every update of the table when data are entered.
3. Identify those columns that need to be unique. Data
in that column must have a different value (no duplicates) for each row.
4. Identify all primary key-foreign key mates. Foreign
keys may be established as the table is created, or later.
5. Determine values to be inserted in any columns for which a
default value (a value that is automatically inserted when no value is inserted
during data entry - example data) is desired.
6. Identify any columns for which domain specifications may
be stated that are more constrained than those established by data type.
Use CHECK as a column constraint.
7. Create the table and any desired indexes using the CREATE
TABLE and CREATE INDEX statements.
An example is shown below.
CREATE TABLE CUSTOMER_T
(CUSTOMER_ID
INTEGER
NOT NULL,
CUSTOMER
NAME
VARCHAR(40) NOT
NULL,
CUSTOMER_ADDRESS VARCHAR(40),
CITY
VARCHAR(25),
STATE
VARCHAR(2),
POSTAL_CODE
VARCHAR(10),
CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID));
CREATE TABLE ORDER_T
(ORDER_ID
INTEGER
NOT NULL,
ORDER_DATE
DATE
DEFAULT SYSDATE
CUSTOMER_ID
INTEGER,
CONSTRAINT ORDER_PK PRIMARY KEY (ORDER_ID),
CONSTRAINT ORDER_FK FOREIGN KEY (CUSTOMER_ID) REFERENCES
CUSTOMER_T (CUSTOMER ID));
CREATE TABLE PRODUCT_T
(PRODUCT_ID
INTEGER
NOT NULL,
PRODUCT_NAME
VARCHAR(40),
PRODUCT_FINISH
VARCHAR(25),
CHECK (PRODUCT_FINISH IN ('Cherry', 'Natural Ash', 'White Ash', 'Red
Oak',
'Natural Oak', 'Walnut)),
UNIT_PRICE
DECIMAL(6.2),
ON_HAND
INTEGER,
PRODUCT_DESCRIPTION VARCHAR(40),
CONSTRAINT PRODUCT_PK PRIMARY KEY (PRODUCT_ID));
CREATE TABLE ORDER_LINE_T
(ORDER_ID
INTEGER
NOT NULL,
PRODUCT_ID
INTEGER
NOT NULL,
QUANTITY
INTEGER,
CONSTRAINT ORDER_LINE_PK PRIMARY KEY (ORDER_ID, PRODUCT_ID),
CONSTRAINT ORDER_LINE_FK1 FOREIGN KEY (ORDER_ID)
REFERENCES ORDER_T (ORDER_ID)
CONSTRAINT ORDER_LINE_FK2 FOREIGN KEY (PRODUCT_ID) REFERENCES
PRODUCT_T (PRODUCT_ID));
To add a customer type column to the CUSTOMER table
ALTER TABLE CUSTOMER_T
ADD (TYPE VARCHAR (2));
The ALTER TABLE commands include ADD and DROP, and allow changing the column's name, datatype, length, and constraints. Its status will be NULL, to deal with data already entered.
Removing Tables
To drop a table from a database schema
DROP TABLE CUSTOMER_T;
will drop the table and save any pending changes. Only the owner or one with privileges may do so. To retain the structure but remove all data, use TRUNCATE TABLE command.
To insert a row of data into a table where a value will be inserted for every attribute.
INSERT INTO CUSTOMER_T VALUES
(001, 'Contemporary Casuals', '1355 S. Himes Blvd.', 'Gainesville', 'FL',
'32601');
To insert a row of data into a table where some attributes will be left null.
INSERT INTO PRODUCT_T (PRODUCT_ID, PRODUCT_NAME, PRODUCT_FINISH,
UNIT_PRICE, ON_HAND)
VALUES (1, 'End Table', 'Cherry', 175, 8);
Populating a table using a subset of another table with the same structure
INSERT INTO CA_CUSTOMER_T
SELECT * FROM CUSTOMER_T
WHERE STATE
= 'CA';
Deleting Database Contents
Deleting rows that meet a certain criterion from the CUSTOMER table.
DELETE FROM CUSTOMER_T
WHERE STATE = 'HI';
Deleting all rows from the CUSTOMER table
DELETE FROM CUSTOMER_T;
Changing DataBase Contents
To modify unit price in the PRODUCT table to 775
UPDATE PRODUCT_T
SET UNIT_PRICE = 775
WHERE
PRODUCT_ID = 7;
To create an alphabetical index on customer name in the CUSTOMER table.
CREATE_INDEX NAME_IDX ON CUSTOMER_T (CUSTOMER_NAME));
To remove the index on the Customer name in the CUSTOMER table.
DROP INDEX NAME_IDX;
2. Data Manipulation Language (DML) commands. These are core commands used to update, insert, modify, and query, used interactively (immediate result) or included within programs written in a 3GL, such as C or COBOL. Embedding SQL commands provide more control over timing of report generation, interface appearance, error handling, and database security. The general syntax of the SQL SELECT command in DML is
SELECT [ALL | DISTINCT] column_list
FROM table_list
[WHERE conditional expression]
[GROUP BY group_by_column_list]
[HAVING conditional_expression]
[ORDER BY order_by_column_list]
PROCESSING SINGLE TABLES
Clauses of the SELECT statement
SQL retrieval statements include three clauses. They are:
1. SELECT Lists
the columns (including expressions involving columns) from base tables or views
to be projected into
the table that will be the result of the command.
2.
FROM Identifies the
table or views from which columns will be chosen to appear in the result table,
and includes
the tables or views needed to join tables to process the query.
3. WHERE Includes
the conditions for row selection within a single table or view, and the
conditions between tables or
views for joining.
Example: Query: For which products are there more than four units on hand?
SELECT PRODUCT_NAME, ON_HAND
FROM PRODUCT_T
WHERE ON_HAND > 4;
Result:
|
PRODUCT_NAME |
ON_HAND |
|
End Table |
8 |
|
Computer Desk |
5 |
|
8-Drawer Desk |
5 |
SELECT DISTINCT will eliminate duplicate rows. SELECT * displays all columns from all tables or views in the FROM clause. If typing the qualifiers is wearisome, or if the column names will not be meaningful, establish aliases for columns or tables that will then be used for the rest of the query.
Query: What s the address of the customer named Home Furnishings? Use an alias, NAME, for customer name
SELECT CUST CUSTOMER_NAME AS NAME, CUST, CUSTOMER_ADDRESS
FROM ownerid.CUSTOMER_T CUST
WHERE NAME ='Home Furnishings';
The columns can be rearranged so they will be ordered differently in the result table then they were n the original table.
Query: List the unit price, product name, and productID for all products in the PRODUCT table.
SELECT UNIT_PRICE, PRODUCT_NAME, PRODUCT_ID
FROM PRODUCT_T
Expressions are mathematical manipulation of data.
Query: What is the total value for each product in inventory?
SELECT PRODUCT_NAME, UNIT_PRICE, ON_HAND,
UNIT_PRICE * ON_HAND AS VALUE
FROM PRODUCT_T
|
Addition |
+ |
|
Subtraction |
- |
|
Multiplication |
* |
|
Division |
/ |
|
Modulo |
% |
Functions such as COUNT, MIN, MAX, SUM, and AVG of specified columns in the column list of a SELECT command used so result contains aggregated data instead of row-level data
Query: How many items were ordered on order number 1004?
SELECT COUNT (*)
FROM ORDER_LINE_T
WHERE ORDER_ID = 1004;
COUNT (*) counts all rows selected by a query regardless of whether any of the rows contain null values. COUNT tallies only those rows that contain a value; it ignores null values. SUM and AVG can only be used with numeric values. COUNT, COUNT(*), MIN, and MAX can be used with any data type. MIN and MAX will use ASCII to find the smallest or largest ASCII value. (Note digits before letters; Upper Case before lower case)
Using Wildcards
Wildcards are used in the WHERE clause where an exact match is not possible. The keyword LIKE is paired with wildcard characters and a string containing the characters that are known to be desired matches. The wildcard character, %, is used to represent any collection of characters. Thus, using LIKE''%Desk' when searching PRODUCT_NAME will find all different types of desks. The underscore, _, is used to represent exactly one character, rather any collection of characters. Thus, using LIKE'_-drawer' when searching PRODUCT_NAME will find any products with specified drawers, such as 3-drawer, 5-drawer, or 8-drawer desks.
|
Operator |
Meaning |
|
= |
Equal to |
|
> |
Greater than |
|
>= |
Greater than or equal to |
|
< |
Less than |
|
<= |
Less than or equal to |
|
<> or != |
Not equal to |
Query: Which orders have been placed since 10/24/98?
SELECT ORDER_ID, ORDER_DATE
FROM ORDER_T
WHERE
ORDER_DATE > '24-OCT-98';
Query: What furniture isn't made of cherry?
SELECT PRODUCT_NAME, PRODUCT_FINISH
FROM PRODUCT_T
WHERE
PRODUCT_FINISH != 'Cherry';
|
AND |
joins two or more conditions and returns results only when all conditions are true |
|
OR |
joins two or more conditions and returns results when any conditions are true |
|
NOT |
negates an expression |
NOT is evaluated first, then AND, and finally OR. Use of parentheses can change the order. The following example shows how parentheses can change the results
Query: List product name, finish, and unit price for all desks and all tables that cost more than $300 in the PRODUCT table.
Without parentheses.
SELECT PRODUCT_NAME, PRODUCT_FINISH, UNIT_PRICE
FROM PRODUCT_T
WHERE
PRODUCT_NAME LIKE '%Desk'
OR PODUCT_NAME LIKE '%Table'
AND UNIT_PRICE > 300
Result
|
PRODUCT_NAME |
PRODUCT_FINISH |
UNIT_PRICE |
|
Computer Desk |
Natural Ash |
375 |
|
Writer's Desk |
Cherry |
325 |
|
8-Drawer Desk |
White Ash |
750 |
|
Dining Table |
Natural Ash |
800 |
|
Computer Desk |
Walnut |
250 |
All the desks are listed, even the computer desk that costs less than $300. Only one table is listed; the less expensive ones that cost less than $300 are not included. AND will be processed first, returning all tables with a unit price greater than $300. Then OR is processed, returning all desks, regardless of cost.
With parentheses.
SELECT PRODUCT_NAME, PRODUCT_FINISH, UNIT_PRICE
FROM PRODUCT_T
WHERE
(PRODUCT_NAME LIKE '%Desk'
OR PODUCT_NAME LIKE '%Table')
AND UNIT_PRICE > 300
Result
|
PRODUCT_NAME |
PRODUCT_FINISH |
UNIT_PRICE |
|
Computer Desk |
Natural Ash |
375 |
|
Writer's Desk |
Cherry |
325 |
|
8-Drawer Desk |
White Ash |
750 |
|
Dining Table |
Natural Ash |
800 |
The comparison operators < and > can establish a range of values.
Query: Which products in the PRODUCT table have a unit price between $200 and $300
SELECT PRODUCT_NAME, UNIT_PRICE
FROM PRODUCT_T
WHERE UNIT_PRICE
> 200 AND UNIT_PRICE < 300
Alternately the key word BETWEEN (or NOT BETWEEN) can be used.
SELECT PRODUCT_NAME, UNIT_PRICE
FROM PRODUCT_T
WHERE UNIT_PRICE
BETWEEN 200 AND 300
Returning rows will contain duplicates.
Query: What are the order numbers in the ORDER_LINE TABLE?
SELECT ORDER_ID
FROM ORDER_LINE_T
will return all orders including duplicates, but
SELECT DISTINCT ORDER_ID
FROM ORDER_LINE_T
will return each order only once (no duplicates)
DISTINCT (and ALL) can only be used once and comes immediately after the keyword SELECT before any columns or expressions. If a SELECT statement projects more than one column, only rows that are identical for every column will be eliminated.
IN and NOT IN Lists
To match a list of values, IN may be used
Query: List all customers who live in warmer states.
SELECT CUSTOMER_NAME, CITY, STATE
FROM CUSTOMER_T
WHERE STATE IN ('FL', 'TX', 'CA',
'HI');
Sorting Results: The ORDER BY Clause
|
ORDER BY |
sorts the results rows in ascending or descending order |
|
GROUP BY |
groups rows in an intermediate results table where the values in these rows are the same for one or more columns |
|
HAVING |
can only be used following a GROUP BY and acts as a secondary WHERE clause, returning only those groups which meet a specified condition. |
Query: List customer, city, and state for all customers in the CUSTOMER table whose address is Florida, Texas, California, or Hawaii. List the customers alphabetically by state, and alphabetically by customer within each state.
SELECT CUSTOMER_NAME, CITY, STATE
FROM CUSTOMER_T
WHERE STATE = ('FL', 'TX', 'CA',
'HI')
ORDER BY
STATE, CUSTOMER_NAME;
Categorizing Results: The GROUP BY Clause
GROUP BY is paired with aggregate functions SUM or COUNT.
Query: Count the number of customers with addresses in each state.
SELECT STATE, COUNT (STATE)
FROM CUSTOMER_T
GROUP BY STATE;
The states will be automatically arranged alphabetically.
Qualifying results by categories: The HAVING Clause
The HAVING clause acts like a WHERE clause, but it identifies groups that meet a criterion, rather than rows, so follows a GROUP BY clause.
Query: Find only states with more than one customer
SELECT STATE, COUNT (STATE)
FROM CUSTOMER_T
GROUP BE STATE
HAVING COUNT
(STATE) > 1;
To include more than one condition in the HAVING clause, use AND, OR, and NOT just as in the WHERE clause.
Query: List the product finish, average unit price for each finish, and number of products on hand for selected finishes where the average unit price is less than 750 and the quantity on hand is more than 2.
SELECT PRODUCT_FINISH, AVG (UNIT_PRICE), SUM (ON_HAND)
FROM PRODUCT_T
GROUP BY PRODUCT_FINISH
HAVING AVG
(UNIT_PRICE) < 750
AND SUM (ON_HAND) > 2
ORDER BY PRODUCT_FINISH;
Note: The six clauses in the statement above must be in the same order given.
3. Data Control Language (DCL) commands help the database administrator to control the database, to grant or revoke privileges, to access the database or particular objects within the database, and to store or remove transactions that would affect the database.
The power of the RDBMS is realized when working with multiple tables. Where relationships exist between tables they can be linked together in queries. Relationships are established by including common column(s) in each table where a relationship is needed. This is accomplished by setting up a primary key-foreign key relationship, where the foreign key in one table references the primary key in another. We can use these columns to establish the link between two tables by finding a common value in the columns. For example, DRIVER NUMBER in Customer table matches DRIVER NUMBER in Driver table. In a relational system, data from related tables are combined into one result table and then displayed or used as input to a form or report definition.
In SQL, the WHERE clause of the SELECT command is used to link related tables. SELECT can include references to any number of tables in the same command.
Join, a relational operation that causes two (or more) tables to be combined into a single table, is the one most frequently used. SQL specifies a join implicitly by referring in a WHERE clause to the matching of common columns over which tables are joined. Two tables may be joined when each one contains a sharing column. The result is a single table, which includes selected columns from all tables. Each row returned contains data from rows in the different input tables where values for the common columns match. There should be one WHERE condition for each pair of tables being joined.. If two tables are to be combined, one condition is necessary, but if three tables (A, B, and C) are to be combined, two conditions are necessary, one for A-B, and one for B-C, and so forth. Three types of joins are equi-joins, natural joins, and outer joins.
Equi-join is a join in which the joining condition is based on equality between values in the common columns. Common columns appear (redundantly) in the result table. If we wan to know the names of customers who have placed orders, that information is kept in two tables, CUSTOMER_T and ORDER_T. It is necessary to match customers with their orders, and then collect the information about name and order number in one table.
Query: What are the names of all customers who have placed orders?
SELECT CUSTOMER_T.CUSTOMER_ID, ORDER_T.CUSTOMER_ID
CUSTOMER_NAME, ORDER_ID
FROM CUSTOMER_T, ORDER_T
WHERE CUSTOMER_T.CUSTOMER_ID =
ORDER_T.CUSTOMER_ID;
will result in one table with CUSTOMER_ID twice, CUSTOMER_NAME, and ORDER_ID. The redundant CUSTOMER_ID columns, one from each table, demonstrate that the customer IDs have been matched. If the WHERE clause is omitted, all combinations of customers and orders will result, that is multiple rows for each customer and order. To avoid redundant columns, we can use natural join.
Natural join, which is the same as equi-join except that one of the duplicate columns is eliminated in the result table, is the most commonly used form of join operation. The SQL command to find customer names and order numbers leaves out one CUSTOMER_ID. CUSTOMER_ID exists in both CUSTOMER_T and ORDER_T, and so it must be specified from which table SQL should pick CUSTOMER_ID.
Query: For each customer who has placed an order, what is the customer's name and order number?
SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID
FROM CUSTOMER_T, ORDER_ID
WHERE CUSTOMER_T.CUSTOMER_ID =
ORDER_T.CUSTOMER_ID;
The order of table names in the FROM clause is immaterial..
Outer join is a join in which rows that do not have matching value in common columns are nevertheless included in the result table. A row in one table does not have a matching row in another table. If a customer has not placed an order for some time, that CUSTOMER_ID number will not appear in the ORDER_T. So equi-join and natural join will not include those customers.
The organization would like o identify these customers to solicit future orders. An outer join will produce this information. Null values will appear in columns where there is no match between tables.
Query: List customer name, identification number, and order number for all customers listed in the CUSTOMER table. Include the customer identification number and name even if there is no order available for that customer.
SELECT USTOMER_T,CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID
FROM CUSTOMER_T, ORDER_T
WHERE CUSTOMER_T.CUSTOMER_ID =
ORDER_T.CUSTOMER.ID RIGHT JOIN;
ORDER_T (which is on the right) is the table that may have no matches with a row in CUSTOMER_T. A match (with an all-null row) is returned for any row in CUSTOMER_T for which there is no match. This join is a right outer join. All customer names will be returned whether or not they had placed an order. In Left outer join, LEFT JOIN will be attached to the table on he left of the equal sign (CUSTOMER_T). Then all orders will be returned even if no customer ordered those items and for those orders, CUSTOMER_ID, and CUSTOMER_NAME will be blank.
Query: List customer name, identification number, and order number for all customers listed in the ORDER table. Include the order number even if there is no customer identification number and nameavailable for that order.
SELECT USTOMER_T,CUSTOMER_ID, CUSTOMER_NAME, ORDER_ID
FROM CUSTOMER_T, ORDER_T
WHERE CUSTOMER_T.CUSTOMER_ID LEFT
JOIN = ORDER_T.CUSTOMER.ID ;
Unless otherwise stated, all joins will be natural joins.
More than two tables can be joined.
Query Assemble all information necessary to create an invoice for order number 1006.
SELECT CUSTOMER_T.CUSTOMER_ID, CUSTOMER_NAME, CUSTOMER_ADDRESS,
CITY, STATE, POSTAL_CODDE, ORDER_T.ORDER_ID, ORDER_DATE,
QUANTITY,
PRODUCT_NAME, UNIT_PRICE, (QUANTITY * UNIT_PRICE)
FROM CUSTOMER_T, ORDER_T,
ORDER_LINE_T, PRODUCT_T
WHERE
CUSTOMER_T.CUSTOMER_ID = ORDER_T.CUSTOMER_ID
AND
ORDER_T.ORDER_ID = ORDER_LINE_T.ORDER_ID
AND
ORDER_LINE_T.PRODUCT_ID = PRODUCT_T.PRODUCT_ID
AND
ORDER_T.ORDER_ID = 1006
As the join involves four tables, there will be three column joins.
|
CUSTOMER_ID |
CUSTOMER_NAME |
CUSTOMER_ADDRESS |
CITY |
ST |
POSTAL_CODE |
|
2 |
Value Furniture |
15145 S.W. 17th St. |
Plano |
TX |
75094 |
|
2 |
Value Furniture |
15145 S.W. 17th St. |
Plano |
TX |
75094 |
|
2 |
Value Furniture |
15145 S.W. 17th St. |
Plano |
TX |
75094 |
|
ORDER_ID |
ORDER-DATE |
QUANTITY |
PRODUCT_NAME |
UNIT_PRICE |
(QUANTITY*UNIT_PRICE) |
|
1006 |
27-OCT-98 |
1 |
Entertainment Center |
650 |
650 |
|
1006 |
27-OCT-98 |
2 |
Writer's Desk |
325 |
650 |
|
1006 |
27-OCT-98 |
2 |
Dining Table |
800 |
1600 |
Subqueries involves placing an inner query (SELECT, FROM, WHERE) within a WHERE or HAVING clause of an outer query. The inner query provides values for the search condition of the outer query. Such queries are referred to as subqueries or nested subqueries and may be nested multiple times. Joining or subquery often achieve the same result. The joining technique is useful when data from several relations are to be retrieved and displayed, and the relationships are not necessarily nested.
The following two queries return the same result, answering the question, What is the name and address of the customer who placed order # 1008? Using a join query,
Query What is the name and address of the customer who placed order # 1008?
SELECT CUSTOMER_NAME, CUSTOMER_ADDRESS, CITY, STATE, POSTAL_CODE
FROM CUSTOMER_T, ORDER_T
WHERE CUSTOMER_T.CUSTOMER_ID =
ORDER_T.CUSTOMER_ID AND
ORDER_ID =
1008;
Using the subquery technique.
Query What is the name and address of the customer who placed order # 1008?
SELECT CUSTOMER_NAME, CUSTOMER_ADDRESS, CITY, STATE, POSTAL_CODE
FROM CUSTOMER_T
WHERE CUSTOMER_T.CUSTOMER_ID =
(SELECT
ORDER_T.CUSTOMER_ID
FROM ORDER_T
WHERE ORDER_ID = 1008);
The subquery is enclosed in parentheses and could stand on its own as an independent query. The subquery approach may be used as we only need to display data from the table in the outer query. It will return only one value, the CUSTOMER_ID associated with ORDER_ID # 1008. The result will be empty if an order with that ID does not exist. A subquery can also return a list of values (with zero, one, or many entries) by using the keyword IN.
Query Which customers have placed orders?
SELECT CUSTOMER_NAME
FROM CUSTOEMR_T
WHERE CUSTOMER_ID IN
(SELECT
DISTINCT CUSTOMER_ID
FROM ORDER_T);
The qualifiers NOT, ANY, and ALL may be used in front of IN or with logical operators such as =, >, and <. NOT and join can also be used in an inner query.
Query Which customers have not placed any orders for computer desks?
SELECT CUSTOMER_NAME
FROM CUSTOMER_T
WHERE CUSTOMER_ID NOT IN
(SELECT
CUSTOMER_ID
FORM ORDER_T, ORDER_LINE_T, PRODUCT_T
WHERE ORDER_T.ORDER_ID =
ORDER_LINE_T.ORDER_ID AND
ORDER_LINE_T.PRODUCT_ID =
PRODUCT_T.PRODUCT_ID
AND PRODUCT_NAME = 'Computer Desk');
The inner query returned a list of all customers who had ordered computer desks. However, the outer query lists the names of those customers who are not in the list returned by the inner query. Two other conditions with subqueries are EXISTS and NOT EXISTS. EXISTS will take a value of true if the subquery returns an intermediate results table which contains one or more rows, and false if no rows are returned. NOT EXISTS is the reverse.
Query What are the order numbers for all orders that have included furniture finished in natural ash?
SELECT DISTINCT ORDER_ID FROM ORDER_LIJNE_T
WHERE EXISTS
(SELECT *
FROM
PRODUCT_T
WHERE PRODUCT_ID = ORDER_LINE_T.PRODUCT_ID
AND PRODUCT_FINISH = 'Natural Ash');
The subquery checks to see if the finish for the product on an order line is natural ash. The main query picks out the order numbers for all orders that have included furniture finished in natural ash.
When EXISTS and NOT EXISTS is used in a subquery, the select list of the subquery will select all columns (SELECT *) as a placeholder as it does not matter which columns are returned. The purpose of the subquery is testing to see if any rows fit the conditions, not the return values from particular columns. The columns that will be returned are determined by the outer query..
Corelated subqueries in which processing the inner query depends on data from the outer query, unlike subqueries where the result of the inner query was used to limit the processing of the outer query. So in correlated subquery, the inner query is somewhat different for each row referenced in the outer query. So the inner query must be computed for each outer row, while usually inner query was computed only once for all rows processed in the outer query.
Processing a non-correlated subquery
SELECT CUSTOMER_NAME
FROM CUSTOMER_T
WHERE CUSTOMER_ID IN
(SELECT DISTINCT CUSTOMER_ID
FROM ORDER_T);
1. The subquery is processed first and an intermediate
results table created.
2. The outer query returns the requested customer information
for each customer included in the intermediate results table.
Processing a correlated subquery
SELECT DISTINCT ORDER_ID FROM ORDER_LINE_T
WHERE EXISTS
(SELECT *
FROM
PRODUCT_T
WHERE PRODUCT_ID = ORDER_LINE_T.PRODUCT_ID
AND PRODUCT_FINISH = 'Natural Ash';
1. The first order ID is selected from ORDER_LINE_T.
2. The subquery is evaluated to see if any product in that
order has a natural ash finish. If so, it is part of the order.
EXISTS is valued ad true and the order ID is added to the results table.
3. The nest order ID is selected from ORDER_LINE_T.
4. The subquery is evaluated to see if the product ordered
has a natural ash finish.
5. Processing continues through each order ID. If any
order does not have a natural ash finish, it is not included.
Query List the details about the product with the highest unit price.
SELECT PRODUCT_NAME, PRODUCT_FINISH, UNIT_PRICE
FROM PRODUCT_T PA
WHERE UNIT_PRICE > ALL
(SELECT
UNIT_PRICE FROM PRODUCT_T PB
WHERE PB.PRODUCT_ID != PA.PRODUCT_ID);
The subquery will be executed once for each product to be sure that no other
product has a higher unit price. The table is compared to itself by
giving the table two aliases, PA and PB. First, PRODUCT_ID 1, will be
considered. When the subquery is executed, it will return the prices of
every product except the one being considered in the outer query. Then
the outer query will check to see if the unit price for the product being
considered is greater than all of the unit prices returned by the
subquery. If it is, it will be returned as the result of the query.
If not, the nest value in the outer query will be considered, and the inner
query will return a list of all the unit prices for the other products.
The list returned by the inner query changes as each product in the outer query
changes, so it is a correlated subquery.
Trigger is a named set of SQL statements that are
considered (triggered) when a data modification (INSERT, UPDATE, DELETE)
occurs. If a condition stated within a trigger is met, then a prescribed
action is taken. Triggers have three parts, the event, the condition,
and the action. An example:
CREATE TRIGGER ORDER_ID_BIR
BEFORE INSERT ON ORDER_T
FOR EACH ROW
BEGIN
SELECT ID_SEQUENCE.NEXTVAL
INTO :NEW.ORDER_ID
FROM DUAL;
END_ORDER_ID_BIR;
This trigger will automatically insert the order number whenever a new order is added. BIR is part of a trigger naming convention and stands for Before Insert Row. Triggers may occur either before or after the statement that aroused the trigger is executed. They may occur on INSERT, UPDATE, or DELETE commands. And they may fire each time a row is affected, or once per statement, regardless of the number of rows affected. Since triggers fire automatically, unless the trigger includes a message to the user, the user will be unaware that a trigger has fired. Also triggers can cascade and cause other triggers to fire. For example, a BEFORE UPDATE trigger could require that a row be inserted in another table. If that table has a BEFORE INSERT trigger, it will also fire, with unintended results. I is even possible to create a loop of triggers.
Query-By-Example (QBE) is the most widely available direct-manipulation database language that uses a graphical approach to query construction. Although SQL is the de facto language used for data manipulation of relational database implementations, and its syntax is more easily understood than other languages, it is still complex enough to deter many end users from writing their own queries using SQL QBE, unlike SQL does not have an international standard.. However, it is simple and easy to learn. It was originally developed by Zloof.
It is useful for end-user. The visual programming environment gives the nonprogramming user a single view of data no matter what database task is performed. Queries are developed interactively on a CRT screen in a format that resembles the desired output. Both queries and results are shown in the same spread-sheet type format. What makes QBE unique is that the visual image of a table is also used for writing queries. The visual image gives the user the sense of directly manipulating the data.
Levels of QBE. At the lowest level are the Objects which allow the creation of tables, queries, forms, and reports without any specific programming knowledge. The use of expressions, or functions, to perform simple processes such as multiplication of fields, validation of data, or enforcement of a specific business rule is possible at the second level. Above that level, there are Macros, where users can take advantage of stored modules of Visual Basic for Applications (VBA) code to automate their application, without explicit knowledge of VBA. At the next level, users can program their own modules of VBA code to custom tailor their own applications. At the top level, Windows API calls to functions or DDLs written in C, Jav, or Visual Basic can be used to write interfaces to other programs and sources of data
. Top Level API
Next Level VBA
Next Level MACROS
Next Level FUNCTIONS/EXPTRESSIONS
Bottom Level OBJECT – TABLES, QUERIES, FORMS, REPORTS
Relationships. Database is a collection of related tables. When the relationship between tables is defined, they are joined using Natural Join (see hand-out). Since the relationship screen shows tables and relationships (not entities and relationships), many-to-many are not allowed.
Single-Table Queries. The handout shows a QBE pane for a single-table query involving the raw materials table that asks for information about the current inventory of natural maple items. The handout also shows SQL view of this query.
Multiple-Table Queries. In QBE links have been established when the relationships were made explicit. (see Handout which also shows SQL).
The next handout is an example of an outer join, in which rows in the table being joined that do not have a match in other tables are also included in the result. A new or unpopular product may not have any entries in the Order_Line_t table. The arrowhead on the end of the relationship line next to Order_Line_t table indicates that an outer join is performed.
Self (or recursive) join is a query that requires a table to be joined with itself. If you want to know what orders have been placed that include any of the same products that were ordered on order #1004. A self join is by putting two copies of the Ordser_Line_t table in the QBE pane, setting a relationship between product Ids, and setting a criteria of 1004 in one table and NOT 1004 in the other table that will return product ID and quantity values.
Basing a query on another query. If a question cannot be answered in a single query, then it is broken into multiple steps. The query generated in a particular step is saved, and the next query is based on the saved previous query rather than on the base table. (Similar to subqueries in SQL)
.