1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
/******************************************
* Syntax Highlighting Test File for SQL *
* Multi-Line Comment Block *
* Oracle 9i SQL *
*******************************************/
--- Single Line Comments are like this
--- Drop all tables, in case they were previously created ---
DROP TABLE shipment;
DROP TABLE customer;
DROP TABLE truck;
DROP TABLE city;
--- Create the customer table ---
CREATE TABLE customer
(
CUS_ID Char(4) CONSTRAINT cus_id_pk PRIMARY KEY,
CUS_LNAME Varchar2(20),
CUS_FNAME Varchar2(20),
ANN_REVENUE Number(12,2),
CUS_TYPE Char(1)
);
--- Create the truck table ---
CREATE TABLE truck
(
TRUCK_ID Char(4) CONSTRAINT truck_id_pk PRIMARY KEY,
DRIVER_NAME Varchar2(40)
);
--- Create the city table ---
CREATE TABLE city
(
CITY_ID Varchar2(4) CONSTRAINT city_id_pk PRIMARY KEY,
CITY_NAME Varchar2(30),
CITY_STATE Char(2),
POPULATION Number(10)
);
--- Create the shipment table ---
CREATE TABLE shipment
(
SHIPMENT_ID Char(4) CONSTRAINT ship_id_pk PRIMARY KEY,
CUS_ID Char(4) CONSTRAINT cust_id_fk REFERENCES customer(cus_id),
WEIGHT Number(12,2),
TRUCK_ID Char(4) CONSTRAINT truck_id_fk REFERENCES truck(truck_id),
CITY_ID Varchar2(4) CONSTRAINT city_id_fk REFERENCES city(city_id),
SHIP_DATE DATE
);
--- Insert records into customer table ---
INSERT INTO customer VALUES
('C101','Smith','Joe',3000000.3,'P');
INSERT INTO customer VALUES
('C102','Sneider','Jenny',7000000.5,'P');
INSERT INTO customer VALUES
('C103','Robinson','Dan',1000000.8,'C');
COMMIT;
--- Insert records into truck table ---
INSERT INTO truck VALUES
('T101','Dan Brun');
INSERT INTO truck VALUES
('T102','Bob Lee');
INSERT INTO truck VALUES
('T104','Jerry Carlson');
INSERT INTO truck VALUES
('T103','Frank Hong');
COMMIT;
--- Insert records into city table ---
INSERT INTO city VALUES
('101','Dekalb','IL',50000);
INSERT INTO city VALUES
('201','Lincoln','NE',160000);
INSERT INTO city VALUES
('301','Houston','TX',800000);
INSERT INTO city VALUES
('401','Laredo','TX',260000);
COMMIT;
--- Insert records into shipment table ---
INSERT INTO shipment VALUES
('2001','C101',2500.2,'T101','101','12-Apr-2002');
INSERT INTO shipment VALUES
('2002','C102',7500.7,'T101','201','20-Apr-2002');
INSERT INTO shipment VALUES
('2003','C103',800000.8,'T103','201','25-May-2002');
INSERT INTO shipment VALUES
('2004','C102',95.00,'T102','301','02-May-2003');
INSERT INTO shipment VALUES
('2005','C101',85.00,'T102','401','02-May-2003');
COMMIT;
--- Queries 1 - 10 ---
--- How many shipments between 1/1/02 & 5/1/03?
--- Version 1 shows all records between the given dates
SELECT *
FROM shipment
WHERE SHIP_DATE >= '01-Jan-2002'
AND SHIP_DATE <= '01-May-2003';
--- Version 2 returns simply a count of all the given dates
SELECT COUNT(*)
FROM shipment
WHERE SHIP_DATE >= '01-Jan-2002'
AND SHIP_DATE <= '01-May-2003';
--- What is destination city name of shipment id# 2004
SELECT CITY_NAME
FROM shipment,city
WHERE SHIPMENT_ID = '2004'
AND shipment.CITY_ID = city.CITY_ID;
--- What are the truck ids of trucks that have carried
--- shipments over 100 lbs?
SELECT DISTINCT TRUCK_ID
FROM shipment
WHERE WEIGHT >= 100;
--- Give the Names of customers who have sent shipments to cities
--- starting with 'L'?
SELECT CUS_LNAME, CUS_FNAME
FROM customer,shipment,city
WHERE customer.CUS_ID = shipment.CUS_ID
AND shipment.CITY_ID = city.CITY_ID
AND city.CITY_NAME LIKE 'L%';
--- What are the names of customers who have sent packages to
--- Lincoln, NE?
SELECT CUS_LNAME, CUS_FNAME
FROM customer,shipment,city
WHERE customer.CUS_ID = shipment.CUS_ID
AND shipment.CITY_ID = city.CITY_ID
AND city.CITY_NAME = 'Lincoln';
--- Who are the customers having over 5 million in revenue and
--- have sent less than 100lbs?
SELECT DISTINCT CUS_FNAME, CUS_LNAME
FROM customer, shipment
WHERE customer.ANN_REVENUE > 5000000
AND shipment.WEIGHT < 100;
--- For each customer what is the average weight of a package,
--- show name and avg weight?
SELECT CUS_FNAME, CUS_LNAME, AVG(WEIGHT)
FROM customer,shipment
WHERE customer.CUS_ID = shipment.CUS_ID
GROUP BY CUS_FNAME, CUS_LNAME;
--- For each city with a population over 100,000 what is the
--- minimum weight of a package sent there?
SELECT CITY_NAME, MIN(WEIGHT)
FROM city,shipment
WHERE city.POPULATION >= 100000
AND city.CITY_ID = shipment.CITY_ID
GROUP BY CITY_NAME;
--- For each city that has recieved at least 2 packages, what is the
--- average weight of a package sent to that city?
SELECT CITY_NAME, COUNT(SHIPMENT_ID), AVG(WEIGHT)
FROM city,shipment
WHERE shipment.CITY_ID = city.CITY_ID
GROUP BY CITY_NAME
HAVING COUNT(shipment.CITY_ID) >= 2;
|
