PROGRAM 1 /* program: program1.sql author: anthony f. ortiz */ /* this program demonstrates an oracle sql create, drop, insert, and */ /* select statements. */ grail 13% sqlplus SQL*Plus: Release 3.3.3.0.0 - Production on Fri Apr 23 19:51:52 1999 Copyright (c) Oracle Corporation 1979, 1996. All rights reserved. Enter user-name: cs466031 Enter password: Connected to: Oracle7 Server Release 7.3.3.0.0 - Production Release PL/SQL Release 2.3.3.0.0 - Production /* create the cap (customers, agents, products) database. */ SQL> start capcre.sql Table dropped. Table dropped. Table dropped. Table dropped. Table created. Table created. Table created. Table created. /* fill in the cap database. */ SQL> start cap.sql 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. 1 row created. /* select all rows from the agents table. */ SQL> select * from agents; AID ANAME CITY PERCENT --- ------------- -------------------- ---------- a01 Smith New York 6 a02 Jones Newark 6 a03 Brown Tokyo 7 a04 Gray New York 6 a05 Otasi Duluth 5 a06 Smith Dallas 5 6 rows selected. /* find aid and aname from agents living in new york. */ SQL> select aid, aname from agents where city = 'New York'; AID ANAME --- ------------- a01 Smith a04 Gray /* find the names of customers who order a product costing 0.50. */ SQL> select distinct cname from customers, products, orders where customers.cid 2 = orders.cid and products.pid = orders.pid and price = .50; CNAME ------------- ACME Tiptop SQL> exit Disconnected from Oracle7 Server Release 7.3.3.0.0 - Production Release PL/SQL Release 2.3.3.0.0 - Production /* data file: capcre.sql */ drop table Customers; drop table Agents; drop table Products; drop table Orders; create table Customers (cid char(4) not null, cname varchar(13), city varchar(20), discnt real); create table Agents (aid char(3) not null, aname varchar(13), city varchar(20), percent number(6)); create table Products (pid char(3) not null, pname varchar(13), city varchar(20), quantity number(10), price real); create table Orders (ordno number(6) not null, month char(3), cid char(4), aid char(3), pid char(3), qty number(6), dollars float); /* data file: cap.sql */ Insert into Customers values ('c001','Tiptop','Duluth',10.00); Insert into Customers values ('c002','Basics','Dallas',12.00); Insert into Customers values ('c003','Allied','Dallas',8.00); Insert into Customers values ('c004','ACME','Duluth',8.00); Insert into Customers values ('c006','ACME','Kyoto',0.00); Insert into Agents values ('a01','Smith','New York',6); Insert into Agents values ('a02','Jones','Newark',6); Insert into Agents values ('a03','Brown','Tokyo',7); Insert into Agents values ('a04','Gray','New York',6); Insert into Agents values ('a05','Otasi','Duluth',5); Insert into Agents values ('a06','Smith','Dallas',5); Insert into Products values ('p01','comb','Dallas',111400,0.50); Insert into Products values ('p02','brush','Newark',203000,0.50); Insert into Products values ('p03','razor','Duluth',150600,1.00); Insert into Products values ('p04','pen','Duluth',125300,1.00); Insert into Products values ('p05','pencil','Dallas',221400,1.00); Insert into Products values ('p06','folder','Dallas',123100,2.00); Insert into Products values ('p07','case','Newark',100500,1.00); Insert into Orders values (1011,'jan','c001','a01','p01',1000,450.00); Insert into Orders values (1012,'jan','c001','a01','p01',1000,450.00); Insert into Orders values (1019,'feb','c001','a02','p02',400,180.00); Insert into Orders values (1017,'feb','c001','a06','p03',600,540.00); Insert into Orders values (1018,'feb','c001','a03','p04',600,540.00); Insert into Orders values (1023,'mar','c001','a04','p05',500,450.00); Insert into Orders values (1022,'mar','c001','a05','p06',400,720.00); Insert into Orders values (1025,'apr','c001','a05','p07',800,720.00); Insert into Orders values (1013,'jan','c002','a03','p03',1000,880.00); Insert into Orders values (1026,'may','c002','a05','p03',800,704.00); Insert into Orders values (1015,'jan','c003','a03','p05',1200,1104.00); Insert into Orders values (1014,'jan','c003','a03','p05',1200,1104.00); Insert into Orders values (1021,'feb','c004','a06','p01',1000,460.00); Insert into Orders values (1016,'jan','c006','a01','p01',1000,500.00); Insert into Orders values (1020,'feb','c006','a03','p07',600,600.00); Insert into Orders values (1024,'mar','c006','a06','p01',800,400.00);
BACK TO CS4660 PAGE.