This homework focuses on SQL, both written answers and executing SQL on an SQL-based database system, e.g., MySQL or MS SQL Server. Suggestions about how to get a DB server running and creating the CAP database are given at the end of this page.
The following two parts of Assignment #2 can be done in either order or intermixed. They both require generating answers or results that you will then hand in.
In this part, you simply do these exercises:
3.1 b, d, h, t
3.2 b
3.4 b
In this part, you work with SQL commands that are described in chapter 3, entering them on the command line (pasting them in), getting results, and then editing them into a file that describes each command and the results it returned, plus a sentence by you for each item, about the connection between the SQL command and your result.
/* example 3.3.1, pg. 87 */
select aid, aname from agents where city = 'New York';
/* example 3.3.2, pg. 88 */
select * from customers;
/* example 3.3.3, pg. 88 */
select pid from orders;
select distinct pid from orders;
select distinct aid, pid from orders;
select all pid from orders;
/* example 3.3.4, pg. 89 */
select distinct customers.cname, agents.aname
from customers, orders, agents
where customers.cid = orders.cid and orders.aid = agents.aid;
/* Some inspection utilities - from Learning MySQL */
show columns from customers;
show create table customers;
/* example 3.3.5, pg. 90 */
select ordno, x.cid, x.aid, x.pid,
0.40*(x.qty*p.price) - 0.01*(c.discnt + a.percent) * (x.qty*p.price)
from orders as x, customers as c, agents as a, products as p
where c.cid = x.cid and a.aid = x.aid and p.pid = x.pid;
/* adding 'order by' to the above to get the result on page 91 */
select ordno, x.cid, x.aid, x.pid,
0.40*(x.qty*p.price) - 0.01*(c.discnt + a.percent) * (x.qty*p.price)
from orders as x, customers as c, agents as a, products as p
where c.cid = x.cid and a.aid = x.aid and p.pid = x.pid
order by ordno;
/* using 'as profit' as on page 91 gives a more compact table */
select ordno, x.cid, x.aid, x.pid,
0.40*(x.qty*p.price) - 0.01*(c.discnt + a.percent) * (x.qty*p.price) as profit
from orders as x, customers as c, agents as a, products as p
where c.cid = x.cid and a.aid = x.aid and p.pid = x.pid
order by ordno;
/* example 3.3.6, pg. 92 */
select c1.cid, c2.cid
from customers c1, customers c2
where c1.city = c2.city and c1.cid < c2.cid;
/* example 3.3.7, pg. 94 */
select x1.pid
from orders x1, orders x2
where x1.pid = x2.pid and x1.cid < x2.cid;
/* example 3.3.8, pg. 95 */
/* first, consider these: */
select x.pid from orders x where x.aid = 'a06';
/* or we could do: */
select distinct x.pid from orders x where x.aid = 'a06';
/* continuing with example 3.3.8, pg. 95 */
select distinct y.cid
from orders x, orders y
where y.pid = x.pid and x.aid = 'a06';
/* example 3.4.1, pg. 97 */
select aid from agents
where city = 'Duluth' or city = 'Dallas';
/* example 3.4.1, pg. 97 */
select distinct cid from orders
where aid in (select aid from agents
where city = 'Duluth' or city = 'Dallas');
/* example 3.4.2, pg. 97 */
select * from agents
where city in ('Duluth', 'Dallas');
/* the same result are obtained with an equivalent form, pg. 98 */
select * from agents
where city = 'Duluth' or city = 'Dallas';
/* example 3.4.3, pg. 98 */
select cname, discnt from customers
where cid in (select cid from orders where aid in
(select aid from agents where city in ('Duluth', 'Dallas')));
/* example 3.4.4, pg. 98 */
select distinct cname from customers, orders
where customers.cid = orders.cid and orders.pid = 'p05';
/* However, can use this approach - example 3.4.4, pg. 98 */
select distinct cname from customers where 'p05' in
(select pid from orders where cid = customers.cid);
/* example 3.4.6, pg. 100 using compact,
up-to-date syntax - works fine in MySQL */
select ordno from orders
where (cid, aid) in
(select cid, aid from customers c, agents a
where c.city = 'Duluth' and a.city = 'New York');
/* example 3.4.7, pg. 101 */
select aid from agents where percent <= all (select percent from agents);
/* example 3.4.8, pg. 101 */
select cid, cname from customers
where discnt = some (select discnt from customers
where city = 'Dallas' or city = 'Boston');
/* example 3.4.9, pg. 102 */
select cid from customers
where discnt < all (select discnt from customers
where city = 'Duluth');
/* example 3.4.10, pg. 103 */
select distinct c.cname from customers c
where exists (select * from orders x
where c.cid = x.cid and x.aid = 'a05');
/* example 3.4.10, pg. 103 */
select distinct c.cname from customers c, orders x
where c.cid = x.cid and x.aid = 'a05';
/* Example 3.4.11 pg. 104 */
select distinct cid from orders x
where pid = 'p01' and exists (select * from orders
where cid = x.cid and pid = 'p07');
select distinct x.cid from orders x, orders y
where x.pid = 'p01' and x.cid = y.cid and y.pid = 'p07';
/* example 3.4.12 pg. 104 */
select distinct c.cname from customers c
where not exists (select * from orders x
where c.cid = x.cid and x.aid = 'a05');
/* example 3.4.13 pg. 105 */
select distinct c.cname from customers c
where c.cid not in
(select cid from orders where aid = 'a05');
select distinct c.cname from customers c
where c.cid <>all
(select cid from orders where aid = 'a05');
/* example 3.4.14 pg. 105 */
select distinct cid from orders x
where not exists (select * from orders
where cid = x.cid and aid = 'a03');
select cid from customers c
where not exists ( select * from orders
where cid = c.cid and aid = 'a03');
/* example 3.4.15 pg. 106 */
select distinct city from customers where cid in
(select cid from orders where pid = 'p01');
select distinct city from customers where cid =any
(select cid from orders where pid = 'p01');
select distinct city from customers c where exists
(select * from orders where cid = c.cid and pid = 'p01');
select distinct city from customers c, orders x
where x.cid = c.cid and x.pid = 'p01';
select distinct city from customers c where 'p01' in
(select pid from orders where cid = c.cid);
Get a DB system/server working, typically MySQL, but you may use another of your choice. Below is a tiny SQL script to get you going. You will need to add to it to load the entire CAP database, since the assignment will involve queries on this database. (If you find that entering the data into .csv files is a bit tedious, remember that your instructor (Prof. Futrelle) entered the data for his CAP database manually - so you can do it too ;-).
Note that there is a collection of (overlapping) useful instructions about using MySQL on this page in the Projects section of the class website.
I used this first .sql file to define the CUSTOMERS table (change 'futrelle'). You can put the .sql file and the .csv files in your working directory. I loaded my .sql file from within MySQL by simply executing :
source futrelle1.sql
Here's what's in my futrelle1.sql file:
use futrelle; drop table if exists customers; create table if not exists customers (cid char(4) not null, cname char(13), city char(20), discnt real, primary key (cid)); show fields from customers; load data local infile 'custs.csv' into table customers fields terminated by ','; select * from customers; /* shows table contents */
I typed in this custs.csv file, a comma-separated file, that contains the data itself:
c001,Tiptop,Duluth,10.00 c002,Basics,Dallas,12.00 c003,Allied,Dallas,8.00 c004,ACME,Duluth,8.00 c006,ACME,Kyoto,0.00