CST363 Week 3

This week we covered the basics of SQL.

SQL for creation of tables:

set echo on

DROP TABLE truck;
DROP TABLE type;
DROP TABLE base;
CREATE TABLE base (
 base_code char(3) PRIMARY KEY,
 base_city varchar2(15),
 base_state char(2),
 base_area_code char(3),
 base_phone varchar2(10),
 base_manager varchar2(20)
);
INSERT INTO base VALUES('501', 'Mulfreesboro', 'TN', '615', '123-4567', 'Andrea D Gallager');
INSERT INTO base VALUES('502', 'Lexington', 'KY', '568', '234-5678', 'George H. Delarosa');
INSERT INTO base VALUES('503', 'Cape Girardeau', 'MO', '456', '345-6789', 'Maria J. Talindo');
INSERT INTO base VALUES('504', 'Dalton', 'GA', '901', '456-7890', 'Peter F. McAvee');
CREATE TABLE type (
 type_code char(1) PRIMARY KEY,
 type_description varchar2(30)
);
INSERT INTO type VALUES('1', 'Single box, double-axle');
INSERT INTO type VALUES('2', 'Single box, single-axle');
INSERT INTO type VALUES('3', 'Tandem trailer, single-axle');
CREATE TABLE truck (
 truck_num char(4) PRIMARY KEY,
 base_code char(3),
 type_code char(1),
 truck_miles number(7,1),
 truck_buy_date date,
 truck_serial_num varchar2(20),
 CONSTRAINT fk_base_code FOREIGN KEY (base_code) REFERENCES base(base_code),
 CONSTRAINT fk_type_code FOREIGN KEY (type_code) REFERENCES type(type_code)
);
INSERT INTO truck VALUES('1001', '501', '1', 32123.5, '23-Sep-2007', 'AA-322-12212-W11');
INSERT INTO truck VALUES('1002', '502', '1', 76984.3, '05-Feb-2006', 'AC-342-22134-Q23');
INSERT INTO truck VALUES('1003', '501', '2', 12346.6, '11-Nov-2006', 'AC-445-78656-Z99');
INSERT INTO truck VALUES('1004', null, '1', 2894.3, '06-Jan-2007', 'WQ-112-23144-T34');
INSERT INTO truck VALUES('1005', '503', '2', 45673.1, '01-Mar-2006', 'FR-998-32245-W12');
INSERT INTO truck VALUES('1006', '501', '2', 193245.7, '15-Jul-2003', 'AD-456-00845-R45');
INSERT INTO truck VALUES('1007', '502', '3', 32012.3, '17-Oct-2004', 'AA-341-96573-Z84');
INSERT INTO truck VALUES('1008', '502', '3', 44213.6, '07-Aug-2005', 'DR-559-22189-D33');
INSERT INTO truck VALUES('1009', '503', '2', 10932.9, '12-Feb-2008', 'DE-887-98456-E94');