- create table Customer(CustomerID number(10) primary key,Customer_Name varchar2(10),Street varchar2(10),City varchar2(10),Zip_Code number(5),Telephone number(11));
- create table Location(Location_ID number(5) primary key, Customer_ID number(10),Street varchar2(10),City varchar2(10),Zip_Code number(5),Location_type varchar2(10),Time_of_day timestamp, Foreign key(Customer_ID) references Customer(CustomerID));
- create table Rate(Rate_Class char(2) primary key,location_ID number(5),RatePerKWH number(3));
- To insert data to the tables use the insert query as below
Inserting data to the customer table.
- Insert into customer values(12345,’James’,’Street1′,’Toronto’,34567,19234556778);
- Insert into customer values(23456,’John’,’Street2′,’Ohio’,45678,14556778983);
- Insert into customer values(34567,’stephen’,’Street3′,’Mishigan’,56789,1567785678);
Inserting data to Location table
- Insert into Location values(5432,12345,lstreet,’Mishigan’,2345,’Business’,’19:10:30′);
- Insert into Location values(3254,34521,lstreet2,’Ohio’,4512,’Housing’,’14:30:30′);
- Insert into Location values(3452,23451,lstreet3,’Mishigan’,4532,’Industrial’,’24:11:20′);
Inserting data to Rate table
- Insert into Rate values(‘A’,5432,30);
- Insert into Rate values(‘C’,3254,10);
- Insert into Rate values(‘B’,3452,15);
Scenario 2:-
- A query (or queries) that lists all of the records in all of the tables.
Select * from customer;
Select * from location;
Select * from Rate;
- A query that lists each customer by name with location addresses.
Select Customer_ID,Customer_Name,l.Street,l.City,l.Zip_Code from Location l,customer where Customer_ID=CustomerID;
- A query that lists the location type and the rates by time of day.
Select Location_Type,Time_Of_Day,RatePerKWH from Location l,Rate where l.Location_ID=Rate.Location_ID;
