Lab2
In this session we will practice some SQL queries on a schema of a restaurant chain. The schema has eight tables and one view. Copy thequeries from the file.
The table schema and data is given below.
CREATE TABLE items (
itemid CHAR(5) PRIMARY KEY,
name VARCHAR(30),
price NUMERIC(5,2),
dateadded DATE DEFAULT CURRENT_DATE
);
CREATE TABLE vendors (
vendorid CHAR(5) NOT NULL,
companyname VARCHAR(30) DEFAULT 'SECRET' NOT NULL,
repfname VARCHAR(20) DEFAULT 'Mr. or Ms.',
replname VARCHAR(20),
referredby CHAR(5) NULL,
UNIQUE (repfname, replname),
PRIMARY KEY(vendorid),
FOREIGN KEY(referredby) REFERENCES vendors(vendorid) INITIALLY DEFERRED
);
CREATE TABLE ingredients (
ingredientid CHAR(5) PRIMARY KEY,
name VARCHAR(30) NOT NULL,
unit CHAR(10),
unitprice NUMERIC(5,2),
foodgroup CHAR(15) CHECK (foodgroup IN ('Milk', 'Meat', 'Bread',
'Fruit', 'Vegetable')),
inventory INTEGER DEFAULT 0,
vendorid CHAR(5),
CHECK (unitprice * inventory <= 4000),
FOREIGN KEY(vendorid) REFERENCES vendors(vendorid) INITIALLY DEFERRED
);
CREATE TABLE madewith (
itemid CHAR(5) REFERENCES items(itemid) INITIALLY DEFERRED,
ingredientid CHAR(5) REFERENCES ingredients INITIALLY DEFERRED,
quantity INTEGER DEFAULT 0 NOT NULL,
PRIMARY KEY(itemid, ingredientid)
);
CREATE TABLE meals (
mealid CHAR(5) NOT NULL,
name CHAR(20) UNIQUE,
PRIMARY KEY(mealid)
);
CREATE TABLE partof (
mealid CHAR(5),
itemid CHAR(5),
quantity INTEGER,
discount DECIMAL (2, 2) DEFAULT 0.00,
PRIMARY KEY(mealid, itemid),
FOREIGN KEY(mealid) REFERENCES meals(mealid),
FOREIGN KEY(itemid) REFERENCES items(itemid) INITIALLY DEFERRED
);
CREATE TABLE ads (
slogan VARCHAR(50)
);
CREATE VIEW menuitems AS
(SELECT m.mealid AS menuitemid, m.name, CAST(SUM(price * (1 - discount)) AS NUMERIC(5,2)) AS price
FROM meals m LEFT OUTER JOIN partof p ON m.mealid = p.mealid
LEFT OUTER JOIN items i ON p.itemid = i.itemid
GROUP BY m.mealid, m.name)
UNION
(SELECT itemid, name, price
FROM items);
CREATE TABLE stores (
storeid CHAR(5) NOT NULL,
address VARCHAR(30),
city VARCHAR(20),
state CHAR(2),
zip CHAR(10),
manager VARCHAR(30),
PRIMARY KEY(storeid)
);
CREATE TABLE orders (
ordernumber INTEGER NOT NULL,
linenumber INTEGER NOT NULL,
storeid CHAR(5) NOT NULL,
menuitemid CHAR(5),
price NUMERIC(5,2),
time TIMESTAMP,
PRIMARY KEY(storeid, ordernumber, linenumber),
FOREIGN KEY(storeid) REFERENCES stores(storeid)
);
INSERT INTO items VALUES ('CHKSD', 'Chicken Salad', 2.85, '5-JUN-2013');
INSERT INTO items VALUES ('FRTSD', 'Fruit Salad', 3.45, '6-JUN-2013');
INSERT INTO items VALUES ('GDNSD', 'Garden Salad', 0.99, '3-FEB-2014');
INSERT INTO items VALUES ('MILSD', 'Millennium Salad', NULL, '16-AUG-2014');
INSERT INTO items VALUES ('SODA', 'Soda', 0.99, '2-FEB-2014');
INSERT INTO items VALUES ('WATER', 'Water', 0, '20-FEB-2013');
INSERT INTO items VALUES ('FRPLT', 'Fruit Plate', 3.99, '22-NOV-2013');
INSERT INTO vendors VALUES ('VGRUS', 'Veggies_R_Us', 'Candy', 'Corn', NULL);
INSERT INTO vendors VALUES ('DNDRY', 'Don''s Dairy', 'Marla', 'Milker', 'VGRUS');
INSERT INTO vendors VALUES ('FLVCR', 'Flavorful Creams', 'Sherman', 'Sherbert', 'VGRUS');
INSERT INTO vendors VALUES ('FRTFR', '"Fruit Eating" Friends', 'Gilbert', 'Grape', 'FLVCR');
INSERT INTO vendors VALUES ('EDDRS', 'Ed''s Dressings', 'Sam', 'Sauce', 'FRTFR');
INSERT INTO vendors VALUES ('SPWTR', 'Spring Water Supply', 'Gus', 'Hing', 'EDDRS');
INSERT INTO ingredients VALUES ('CHESE', 'Cheese', 'scoop', 0.03, 'Milk', 150, 'DNDRY');
INSERT INTO ingredients VALUES ('CHIKN', 'Chicken', 'strip', 0.45, 'Meat', 120, 'DNDRY');
INSERT INTO ingredients VALUES ('CRUTN', 'Crouton', 'piece', 0.01, 'Bread', 400, 'EDDRS');
INSERT INTO ingredients VALUES ('GRAPE', 'Grape', 'piece', 0.01, 'Fruit', 300, 'FRTFR');
INSERT INTO ingredients VALUES ('LETUS', 'Lettuce', 'bowl', 0.01, 'Vegetable', 200, 'VGRUS');
INSERT INTO ingredients VALUES ('PICKL', 'Pickle', 'slice', 0.04, 'Vegetable', 800, 'VGRUS');
INSERT INTO ingredients VALUES ('SCTDR', 'Secret Dressing', 'ounce', 0.03, NULL, 120, NULL);
INSERT INTO ingredients VALUES ('TOMTO', 'Tomato', 'slice', 0.03, 'Fruit', 15, 'VGRUS');
INSERT INTO ingredients VALUES ('WATER', 'Water', 'glass', 0.06, NULL, NULL, 'SPWTR');
INSERT INTO ingredients VALUES ('SODA', 'Soda', 'glass', 0.69, NULL, 5000, 'SPWTR');
INSERT INTO ingredients VALUES ('WTRML', 'Watermelon', 'piece', 0.02, 'Fruit', NULL, 'FRTFR');
INSERT INTO ingredients VALUES ('ORNG', 'Orange', 'slice', 0.05, 'Fruit', 10, 'FRTFR');
INSERT INTO madewith VALUES ('CHKSD', 'CHESE', 2);
INSERT INTO madewith VALUES ('CHKSD', 'CHIKN', 4);
INSERT INTO madewith VALUES ('CHKSD', 'LETUS', 1);
INSERT INTO madewith VALUES ('CHKSD', 'SCTDR', 1);
INSERT INTO madewith VALUES ('FRTSD', 'GRAPE', 10);
INSERT INTO madewith VALUES ('FRTSD', 'WTRML', 5);
INSERT INTO madewith VALUES ('GDNSD', 'LETUS', 4);
INSERT INTO madewith VALUES ('GDNSD', 'TOMTO', 8);
INSERT INTO madewith VALUES ('FRPLT', 'WTRML', 10);
INSERT INTO madewith VALUES ('FRPLT', 'GRAPE', 10);
INSERT INTO madewith VALUES ('FRPLT', 'CHESE', 10);
INSERT INTO madewith VALUES ('FRPLT', 'CRUTN', 10);
INSERT INTO madewith VALUES ('FRPLT', 'TOMTO', 8);
INSERT INTO madewith VALUES ('WATER', 'WATER', 1);
INSERT INTO madewith VALUES ('SODA', 'SODA', 1);
INSERT INTO madewith VALUES ('FRPLT', 'ORNG', 10);
INSERT INTO meals VALUES ('CKSDS', 'Chicken N Suds');
INSERT INTO meals VALUES ('VGNET', 'Vegan Eatin''');
INSERT INTO partof VALUES ('CKSDS', 'CHKSD', 1, 0.02);
INSERT INTO partof VALUES ('CKSDS', 'SODA', 1, 0.10);
INSERT INTO partof VALUES ('VGNET', 'GDNSD', 1, 0.03);
INSERT INTO partof VALUES ('VGNET', 'FRTSD', 1, 0.01);
INSERT INTO partof VALUES ('VGNET', 'WATER', 1, 0.00);
INSERT INTO ads VALUES ('Grazing in style');
INSERT INTO ads VALUES (NULL);
INSERT INTO ads VALUES ('Bovine friendly and heart smart');
INSERT INTO ads VALUES ('Where the grazin''s good');
INSERT INTO ads VALUES ('The grass is greener here');
INSERT INTO ads VALUES ('Welcome to the "other side"');
INSERT INTO stores VALUES ('FIRST','1111 Main St.','Waco','TX','76798','Jeff Donahoo');
INSERT INTO stores VALUES ('#2STR','2222 2nd Ave.','Waco','TX','76798-7356','Greg Speegle');
INSERT INTO stores VALUES ('NDSTR','3333 3rd St.','Fargo','ND','58106','Jeff Speegle');
INSERT INTO stores VALUES ('CASTR','4444 4th Blvd','San Francsico','CA','94101-4150','Greg Donahoo');
INSERT INTO stores VALUES ('NWSTR',null,null,'TX',null,'Man Ager');
INSERT INTO orders VALUES (1,1,'FIRST','FRTSD',3.45,'26-APR-2014 1:46:04.188');
INSERT INTO orders VALUES (1,2,'FIRST','WATER',0.0,'26-APR-2014 1:46:19.188');
INSERT INTO orders VALUES (1,3,'FIRST','WATER',0.0,'26-APR-2014 1:46:34.188');
INSERT INTO orders VALUES (2,1,'FIRST','CHKSD',2.85,'26-APR-2014 1:47:49.188');
INSERT INTO orders VALUES (3,1,'FIRST','SODA ',0.99,'26-APR-2014 1:49:04.188');
INSERT INTO orders VALUES (3,2,'FIRST','FRPLT',3.99,'26-APR-2014 1:49:19.188');
INSERT INTO orders VALUES (3,3,'FIRST','VGNET',4.38,'26-APR-2014 1:49:34.188');
INSERT INTO orders VALUES (1,1,'#2STR','CKSDS',3.68,'26-APR-2014 2:02:04.188');
INSERT INTO orders VALUES (1,2,'#2STR','CHKSD',2.85,'26-APR-2014 2:02:19.188');
INSERT INTO orders VALUES (1,3,'#2STR','SODA ',0.99,'26-APR-2014 2:02:34.188');
INSERT INTO orders VALUES (1,4,'#2STR','GDNSD',0.99,'26-APR-2014 2:02:49.188');
INSERT INTO orders VALUES (2,1,'#2STR','CHKSD',2.85,'26-APR-2014 2:04:04.188');
INSERT INTO orders VALUES (2,2,'#2STR','SODA ',0.99,'26-APR-2014 2:04:19.188');
INSERT INTO orders VALUES (3,1,'#2STR','CHKSD',2.85,'26-APR-2014 2:05:34.188');
INSERT INTO orders VALUES (3,2,'#2STR','FRPLT',3.99,'26-APR-2014 2:05:49.188');
INSERT INTO orders VALUES (3,3,'#2STR','GDNSD',0.99,'26-APR-2014 2:06:04.188');
INSERT INTO orders VALUES (1,1,'NDSTR','WATER',0.0,'26-APR-2014 2:1:04.188');
INSERT INTO orders VALUES (1,2,'NDSTR','FRPLT',3.99,'26-APR-2014 2:1:19.188');
INSERT INTO orders VALUES (2,1,'NDSTR','GDNSD',0.99,'26-APR-2014 2:15:34.188');
INSERT INTO orders VALUES (3,1,'NDSTR','VGNET',4.38,'26-APR-2014 2:16:49.188');
INSERT INTO orders VALUES (3,2,'NDSTR','FRPLT',3.99,'26-APR-2014 2:17:04.188');
INSERT INTO orders VALUES (3,3,'NDSTR','FRTSD',3.45,'26-APR-2014 2:17:19.188');
INSERT INTO orders VALUES (3,4,'NDSTR','SODA ',0.99,'26-APR-2014 2:17:34.188');
INSERT INTO orders VALUES (1,1,'CASTR','CHKSD',2.85,'26-APR-2014 2:22:04.188');
INSERT INTO orders VALUES (1,2,'CASTR','GDNSD',0.99,'26-APR-2014 2:22:19.188');
INSERT INTO orders VALUES (2,1,'CASTR','SODA ',0.99,'26-APR-2014 2:23:34.188');
INSERT INTO orders VALUES (2,2,'CASTR','FRTSD',3.45,'26-APR-2014 2:23:49.188');
INSERT INTO orders VALUES (2,3,'CASTR','SODA ',0.99,'26-APR-2014 2:24:04.188');
INSERT INTO orders VALUES (2,4,'CASTR','VGNET',4.38,'26-APR-2014 2:24:19.188');
INSERT INTO orders VALUES (3,1,'CASTR','VGNET',4.38,'26-APR-2014 2:25:34.188');
INSERT INTO orders VALUES (3,2,'CASTR','FRPLT',3.99,'26-APR-2014 2:25:49.188');
INSERT INTO orders VALUES (3,3,'CASTR','FRTSD',3.45,'26-APR-2014 2:26:04.188');
INSERT INTO orders VALUES (3,4,'CASTR','WATER',0.0,'26-APR-2014 2:26:19.188');
INSERT INTO orders VALUES (3,5,'CASTR','CHKSD',2.85,'26-APR-2014 2:26:34.188');
Lab sheet 2 Query.txt
Open wi
SELECT, FROM, WHERE clauses:
The SELECT clause is used to select data from a database. The SELECT clause is a query expression that begins with the SELECT keywordand includes a number of elements that form the expression. WHERE clause is used to specify the Search Conditions. The operatorscommonly used for comparison are =, > , < , >=, <= , <>.
Select FROM items
WHERE PRICE <= 0.99
The following operators are also used in WHERE clause.
Find the food items added after 2013 SQL>SELECT *
FROM items
WHERE dateadded > ‘2013-12-31’;
Find all items with a name less than or equal to ‘garden’ SQL>SELECT name
FROM items
WHERE name <= ‘garden’;
Find the list of vendor representative first names that begin with ‘s’ SQL>SELECT repfname
FROM vendors
WHERE repfname LIKE ‘s%’;
Find all vendor names containing an ‘_’. SQL>SELECT companyname
FROM vendors
WHERE companyname LIKE ‘%#_%’ ESCAPE ‘#’;
Using Logical operators-AND, OR, NOT
Find the name of all of the food items other than salads. SQL> SELECT name
FROM items
WHERE NOT name LIKE ‘%Salad’;
Find all of the ingredients from the fruit food group with an inventory greater than 100
WHERE foodgroup = ‘Fruit’ AND inventory >100;
NOTE: The precedence order is NOT, AND, OR from highest to lowest.
Find the food items that have a name beginning with either F or S that cost less than $3.50
SELECT name, price from items where name like ‘F%’ or name like ‘S%’ and price < 3.50
See the result of the above query. Is it correct? Modify the query to get the correct result.
BETWEEN (inclusive)
FROM items
WHERE price BETWEEN 2.50 AND 3.50;
Selecting a set of values using IN, NOT IN
Find the ingredient ID, name, and unit of items not sold in pieces or strips. SQL> SELECT ingredientid,name,unit
FROM ingredients
WHERE unit NOT IN (‘piece’,’stripe’);
IS NULL
SQL interprets NULL as unknown. So comparing any value with NULL returns unknown result.
Find the details of all vendors not referred by anyone.
SQL>SELECT * FROM vendors
WHERE referredby = NULL;
Check the output and try the following. SQL>SELECT *
FROM vendors
WHERE referredby IS NULL;
Do you see the difference? Comparing NULL with any attribute gives an unknown result.
Check the output of the following query SQL>SELECT *
FROM items
WHERE PRICE <= 0.99
Reshaping Results
As we know that every query result is a relation. SQL allows us to specify how this relation should be shown in a desired way. The resulting relation columns can be renamed, duplicates can be eliminated, derived attributes can be added, the rows can be sorted by some criteria etc.
Result table columns have the same name as attributes in the original table; however you can change the result table column using a column alias using ‘AS’ Select companyname as “Company”, repfname as “First Name” from vendors; DISTINCT keyword when used in SELECT clause removes the duplicates. It can be used only once in the SELECT clause. It treats NULL as a distinct value.
Find the distinct list of food groups provided by each vendor Select distinct foodgroup, vendorid from ingredients;
ALL is used to specify that the result table should include duplicates also. Since by default duplicate elimination is not done, it is unnecessary.
Data in the original table can be somewhat raw. SQL provides the ability to create derived attributes in our result table that are derived using operations and functions over existing attributes and literals.
Find the value of your pickle inventory if you double your stock of pickles. SELECT ingredientid, inventory * 2 * unitprice as “Inventory Value” from ingredients where name=’Pickle’
It is also possible to concatenate two or more columns in original table as one single column in result table.
Create a mailing label for each store
Select manager, sysdate() as “As on”, address || ‘ ‘ || city || ‘ ‘ || state || ‘ ‘|| zip || ‘ USA’ as mail from stores;
Note that system date can be printed using the function sysdate().
ORDER BY: The ORDER BY keyword is used to sort the result-set by a specified column in ASCending or DESCending order.
· The ORDER BY clause takes the output from the SELECT clause and orders the query results according to the specifications within the ORDER BY clause
· The ORDER BY keyword sort the records in ascending order by default.
Find all items from most to least expensive SQL>SELECT name,price
FROM items
ORDER BY price ASC;
Find the name and inventory value of all ingredients ordered by inventory value SQL>SELECT name,inventory*unitprice AS value
FROM ingredients ORDER BY value DESC;
SQL Aggregate Functions: SQL aggregate functions return a single value, calculated from values in a column. The aggregate function when used in select clause, computation applies to set of all rows selected. They are commonly used in combination with GROUP BY and HAVINGclauses.
AVG() - Returns the average value COUNT() - Returns the number of rows FIRST() - Returns the first value LAST() - Returns the last value
MAX() - Returns the largest value MIN() - Returns the smallest value SUM() - Returns the sum
Find the average and total price for all items SQL>SELECT AVG(price), SUM(price)
FROM items;
Find the total number of ingredient units in inventory SQL>SELECT SUM(inventory) AS totalinventoy
FROM ingredients;
Find the date on which last item was added
SQL>SELECT MAX (dateadded) AS lastmenuitem FROM items;
Find the strore id and total sales from all stores. SQL>SELECT storeid,SUM(price)
FROM orders;
Any error ? Can you find the reason for the error?
GROUP BY Clause : The GROUP BY clause is used to group together types of information in order to summarize related data.The GROUP BY clause can be included in a SELECT statement whether or not the WHERE clause is used..Correct the above query.
Find the total for each order.
SQL>SELECT storeid,ordernumber,SUM(price) FROM orders
GROUP BY storeid,ordernumber;
Find the distinct list of food groups provided by each vendor. ???
Find stores and store sales sorted by number of items sold. SQL>SELECT storeid,SUM(price)
FROM orders GROUP BY storeid
ORDER BY COUNT(*);
HAVING: The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
· If a GROUP BY clause is specified, the HAVING clause is applied to the groups created by the GROUP BY clause.
· If a WHERE clause is specified and no GROUP BY clause is specified, the HAVING clause is applied to the output of the WHERE clause and that output is treated as one group.
· If no WHERE clause and no GROUP BY clause are specified, the HAVING clause is applied to the output of the FROM clause and that output is treated as one group.
Find the maximum number of items in an order for each store with total sales of more than $20.
SQL>SELECT storeid, MAX(linenumber) AS “Items Sold” FROM orders
GROUP BY storeid HAVING SUM(price)>20;
Find total sales at FIRST store SELECT SUM(price) AS sales FROM orders
GROUP BY storeid HAVING storeid=’FIRST’;
EXERCISE
1,Find the ID of the vendor who supplies grape
2, Find all of the ingredients from the fruit food group with an inventory greater than 100
3, Display all the food groups from ingredients, in which ‘grape’ is not a member. 4, Find the ingredients, unit price supplied by‘VGRUS’(vendor ID) order by unit price(asc)
5, Find the date on which the last item was added.
6. Find the number of vendors each vendor referred, and only report the vendors reffering more than one.
JOIN
Combining tables to derive a new table is called a join.
For each ingredient, find its name and the name and ID of the vendor that supplies it
SELECT vendors.vendorid, name, companyname FROM ingredients, vendors
WHERE ingredients.vendorid = vendors.vendorid;
Find the names of the ingredients supplied to us by Veggies_R_Us SQL>SELECT name
FROM ingredients, vendors
WHERE ingredients.vendorid = vendors.vendorid AND companyname = 'Veggies_R_Us';
SELF JOIN:
Allowing two copies of the same table to appear in the FROM clause. The only requirement is that each copy of the table must be given adistinct alias to distinguish between the copies of the table
Find all of the vendors referred by Veggies_R_Us SELECT v2.companyname
FROM vendors v1, vendors v2 /*Note the table alias*/
WHERE v1.vendorid = v2.referredby AND v1.companyname = 'Veggies_R_Us';
THETA JOIN:
A join where the join predicate uses any of the comparison operators is called a theta join
Find all of the items and ingredients where we do not have enough of the ingredient to make three items.
SQL>SELECT items.name, ing.name
FROM items, madewith mw, ingredients ing
WHERE items.itemid = mw.itemid AND mw.ingredientid = ing.ingredientid AND 3 * mw.quantity > ing.inventory;
Find the name of all items that cost more than the garden salad SQL>SELECT a.name
FROM items a, items q
WHERE a.price > q.price AND q.name = 'Garden Salad';
JOIN Operators:
Joining tables together is so common that SQL provides a JOIN operator for use in the FROM clause. There are several variants of the JOIN,which we explore here.
INNER JOIN:
INNER JOIN takes two tables and a join specification describing how the two tables should be joined. The join specification may be specified as a condition. The condition follows the keyword ON
Find the names of the ingredients supplied to us by Veggies_R_Us
SELECT name
FROM ingredients i INNER JOIN vendors v ON i.vendorid = v.vendorid WHERE v.companyname = 'Veggies_R_Us';
Find the name of all items that cost more than the garden salad SELECT i1.name
FROM items i1 INNER JOIN items i2 ON i1.price > i2.price WHERE i2.name = 'Garden Salad';
It is common to join tables over attributes with the same name. Thus, SQL provides a shorthand for this type of join. The USING clause liststhose attributes common to both tables that must have the same value to be in the result. Here USING is identical to the equijoin.
Find the names of the ingredients supplied to us by Veggies_R_Us SELECT companyname, name, vendorid
FROM ingredients INNER JOIN vendors v USING (vendorid) WHERE v.companyname = 'Veggies_R_Us';
Find the names of items that are made from ingredients supplied by the company Veggies_R_Us
SQL>SELECT DISTINCT(i.name)
FROM vendors INNER JOIN ingredients USING (vendorid) JOIN madewith USING(ingredientid) JOIN items i USING (itemid) WHERE companyname = 'Veggies_R_Us';
OUTER JOIN:
Outer joins are useful where we want not only the rows that satisfy the join predicate, but also the rows that do not satisfy it. There are three types of OUTER JOIN: FULL, LEFT, and RIGHT. FULL OUTER JOIN includes three kinds of rows:
1. All rows that satisfy the join predicate (same as INNER JOIN)
2. All rows from the first table that don’t satisfy the join predicate for any row in the
second table
3. All rows from the second table that don’t satisfy the join predicate for any row in the
first table.
For each ingredient, find its name and the name and ID of the vendor that supplies them. Include vendors who supply no ingredientsand ingredients supplied by no vendors
SELECT companyname, i.vendorid, i.name
FROM vendors v FULL JOIN ingredients i ON v.vendorid = i.vendorid;
Find the vendors who do not provide us with any ingredients SELECT companyname
FROM vendors v LEFT JOIN ingredients i USING(vendorid) WHERE ingredientid IS NULL;
NATURAL JOIN:
By now you may have noticed we often use equality predicates where both tables contain attribute(s) using the same name. This is acommon join query. In fact, it is so common that it is often called the natural join. Again, to simplify the syntax, SQL has a NATURALmodifier for both the inner and outer joins. With this modifier present, we don’t need a join specification, so the ON or USING clauses are notneeded. Note that if the tables contain no matching attribute names, the NATURAL JOIN performs a Cartesian product.
Find the names of the ingredients supplied to us by Veggies_R_Us SELECT name
FROM ingredients NATURAL JOIN vendors WHERE companyname = 'Veggies_R_Us';
Find the vendors who do not provide us with any ingredients SELECT companyname
FROM vendors v NATURAL LEFT JOIN ingredients i WHERE ingredientid IS NULL;
Find the names of items that are made from ingredients supplied by Veggies_R_Us
SELECT name
FROM vendors NATURAL JOIN ingredients NATURAL JOIN madewith NATURAL JOIN items
WHERE companyname = 'Veggies_R_Us'; What is wrong with above query?
UNION, INTERSECTION and MINUS
The set-difference operator in Oracle is called MINUS rather than EXCEPT as in SQL Union:
UNION combines two query results. The syntax for UNION is as follows:
<left SELECT> UNION [{ALL | DISTINCT}] <right SELECT>.
The <left SELECT> and <right SELECT> can be almost any SQL query, provided that the result sets from the left and right SELECT arecompatible. Two result sets are compatible if they have the same number of attributes and each corresponding attribute iscompatible. Two attributes are compatible if SQL can implicitly cast them to the same type.
Find the list of item prices and ingredient unit prices SELECT price
FROM items UNION
SELECT unitprice FROM ingredients;
Find the names and prices of meals and items SELECT name, price
FROM items UNION
SELECT m.name, SUM(quantity * price * (1.0 - discount)) FROM meals m, partof p, items i
WHERE m.mealid = p.mealid AND p.itemid = i.itemid
GROUP BY m.mealid, m.name;
Intersection
The intersection of two sets is all the elements that are common to both sets. In SQL, the INTERSECT operator returns all rows that are thesame in both results. The syntax for INTERSECT is as follows:
<left SELECT> INTERSECT [{ALL |DISTINCT}] <right SELECT>
The <left SELECT> and <right SELECT> must be compatible, as with UNION . By default, INTERSECT eliminates duplicates.
Find all item IDs with ingredients in both the Fruit and Vegetable food groups SELECT itemid
FROM madewith mw, ingredients ing
WHERE mw.ingredientid = ing.ingredientid and foodgroup = 'Vegetable' INTERSECT
SELECT itemid
FROM madewith mw, ingredients ing
WHERE mw.ingredientid = ing.ingredientid and foodgroup = 'Fruit';
Difference
Another common set operation is set difference. If R and S are sets, then R - S contains all of the elements in R that are not in S. TheEXCEPT (MINUS in Oracle 9i) operator in SQL is similar, in that it returns the rows in the first result that are not in the second one. The syntax for EXCEPT is as follows:
<left SELECT> EXCEPT [ALL | DISTINCT] <right SELECT>
The <left SELECT> and <right SELECT> must be compatible, as with UNION and INTERSECT. Like UNION and INTERSECT, EXCEPTeliminates duplicates. To keep duplicates, use EXCEPT ALL. If the left and right tables have l and r duplicates of a value, v; the number ofduplicate values resulting from an EXCEPT ALL is the minimum of l-r and 0.
Find all item IDs of items not made with Cheese SELECT itemid
FROM items MINUS SELECT itemid
FROM madewith mw, ingredients ing
WHERE mw.ingredientid = ing.ingredientid AND ing.name = 'Cheese';
Find all item IDs of items not made with Cheese SELECT DISTINCT(itemid)
FROM madewith mw, ingredients ing
WHERE mw.ingredientid = ing.ingredientid AND ing.name != 'Cheese';
Did you get the result? What is wrong with above query?
List all the food groups provided by some ingredient that is in the Fruit Plate but not the Fruit Salad?
SELECT foodgroup
FROM madewith m, ingredients i
WHERE m.ingredientid = i.ingredientid AND m.itemid = 'FRPLT' MINUS ALL
SELECT foodgroup
FROM madewith m, ingredients i
WHERE m.ingredientid = i.ingredientid AND m.itemid = 'FRTSD';
FROM vendors v LEFT JOIN ingredients i USING(vendorid) WHERE ingredientid IS NULL;
· Rewrite the above query to use MINUS instead of an OUTER JOIN
· From set theory, R∩S = R-(R-S). Rewrite the following query using MINUS instead of INTERSECT.
SELECT itemid
FROM madewith m, ingredients i
WHERE m.ingredientid = i.ingredientid AND foodgroup='Milk' INTERSECT
SELECT itemid
FROM madewith m, ingredients i
WHERE m.ingredientid = i.ingredientid AND foodgroup='Fruit';