LAB EXERCISE - 06 Q- Consider the following relational database schema. It is intended to represent who will eat what kinds of sandwiches and the places which serve the various kinds of sandwiches. A sample database instance is also given.
SQL>create table taste1(name varchar2(15) not null,filling varchar2(15) not null,primary key(filling)); Table created. SQL> create table location1(Lname varchar2(15) primary key,phone number(10),address varchar2(50));
Table created. SQL> create table sandwich1(location varchar2(15) references location1, bread varchar2(10),fillings varchar2(15) references taste1(filling),price number(5,2));
Table created. Insert data into taste table
SQL> insert into taste1 values('brown','tukey'); values('brown','tukey'); 1 row created. SQL> insert into taste1 values ('brown','beef'); ('brown','beef'); 1 row created. SQL> insert into taste1 values ('brown','ham'); ('brown','ham'); 1 row created. SQL> insert into taste1 values ('jones','cheese'); 1 row created. SQL> insert into taste1 values ('green','bef'); ('green','bef'); 1 row created. SQL> insert into taste1 values('green','turkey'); 1 row created. SQL> insert into taste1 values('green','chese'); 1 row created. Insert data into location table
SQL> insert into location1 values('lincoln',6834523,'lincoln place'); 1 row created. SQL> insert into location1 values('ONeills',6742134,'Pearse St'); 1 row created. SQL> insert into location1 values ('Old Nag',7678132,'Dame St'); 1 row created. SQL> insert into location1 values ('Buttery',7023421,'College St'); 1 row created. Insert data into sandwich table
SQL> insert into sandwich1 values('lincoln','rye','ham','1.25'); 1 row created. SQL> insert into sandwich1 values('ONeills','white','chese','1.20'); 1 row created. SQL> insert into sandwich1 values('ONeills','whole','ham','1.25'); 1 row created. SQL> insert into sandwich1 values('Old Nag','rye','beef','1.35'); 1 row created. SQL> insert into sandwich1 values('Buttery','white','turkey','1.35'); 1 row created. SQL> insert into sandwich1 values('ONeills','white','chese','1.20'); 1 row created. SQL> insert into sandwich1 values('Buttery','white','ham','1.10'); 1 row created. SQL> insert into sandwich1 values('lincoln','rye','beef','1.35'); 1 row created. SQL> insert into sandwich1 values('lincoln','white','ham','1.30');
1 row created. SQL> insert into sandwich1 values('Old Nag','rye','ham','1.40'); 1 row created. SQL> select * from taste1; NAME
FILLING
--------------- -------------- brown
tukey
brown
beef
brown
ham
jones
cheese
green
bef
green
turkey
green
chese
7 rows selected.
SQL> select * from location1; LNAME
PHONE ADDRESS
--------------- ---------- --------------------------------------------lincoln
6834523 lincoln place
ONeills
6742134 Pearse St
Old Nag
7678132 Dame St
Buttery
7023421 College St
SQL> select * from sandwich1; LOCATION
BREAD
FILLINGS
--------------- ---------- --------------- ----------
PRICE
lincoln
rye
ham
1.25
ONeills
white
chese
1.2
ONeills
whole
ham
1.25
Old Nag
rye
Buttery
white
turkey
1.35
ONeills
white
chese
1.2
Buttery
white
ham
1.1
lincoln
rye
lincoln
white
ham
1.3
rye
ham
1.4
Old Nag
beef
beef
1.35
1.35
10 rows selected. Create the table and populate them with the given data. Write SQL statements to retrieve the following information: (i) places where Jones can eat (using a nested subquery).
SQL> select lname,address from location1 where lname in (select LOCATION from sandwich1); LNAME
ADDRESS
--------------- -------------------------------------------------ONeills
Pearse St
Old Nag
Dame St
Buttery
College St
lincoln
lincoln place
(ii) places where Jones can eat (without using a nested subquery).
SQL> select lname,address from location1; LNAME
ADDRESS
--------------- ------------------------------lincoln
lincoln place
ONeills
Pearse St
Old Nag
Dame St
Buttery
College St
(iii) for each location the number of people who can eat there.
SQL> select lname from location1; LNAME --------------Buttery ONeills Old Nag lincoln 1.Create the Car_Rental Database. Draw the schema diagram by analyzing the create table statements in the Car_Rental database. Write Select statement for each of the following queries using the Car Rental database. Write SELECT statements for each of the following:
1.Retrieve all customers who have booked a car in-group 'B3' and display the customer number, model, registration, rate per day, rate per mile, date reserved and Car’s Status. select cust.cust_no, car.model_name, car.registration, cg.rate_per_day, cg.rate_per_mile, b.date_reserved from customers cust,cars car, cargroups cg, bookings bwhere cust.cust_no = b.cust_no and b.registration=car.registration and car.car_group_name=cg.car_group_name and car.car_group_name='B3';
2. Retrieve the registration, group, model, rate per day, date reserved, date rental start, customer number, customer name and address for all customer bookings taken since October 1991. select b.registration, car.Car_group_name, car.model_name, cg.rate_per_day, b.date_rent_start, b.cust_no, cust.cust_name, cust.address from bookings b, cargroups cg, customers cust, cars car where cust.cust_no = b.cust_no and b.registration=car.registration and car.car_group_name=cg.car_group_name and b.date_reserved>’01-10-91’;
3. Retrieve model, registration, rate per day from the cars table, for those cars whose daily rate is more than the daily rate for model 'BMW750'. select car.model_name, car.registration, cg.rate_per_day from cars car, cargroups cg where car.car_group_name = cg.car_group_name and cg.rate_per_day < (select distinct cg2.rate_per_day from cargroups cg2, cars car2 where cg2.car_group_name=car2.car_group_name and car2.model_name = 'BMW 750');
Write SELECT statements, which incorporate a subquery for each of the following:
4. Select customer number, name, town, from the customers table for customers who have had their bookings taken by 'Eric'. select cust_no, cust_name, Town from customers where cust_no in (select cust_no from bookings where reserved_by='ERIC');
5. Retrieve model, registration, rental start date, customer number and town for all bookings made by customer who live in Workingham, or Castle Combe. select car.model_name, b.registration, b.date_rent_start, b.cust_no, cust.town from cars car, bookings b, customers cust where car.registration=b.registration and b.cust_no=cust.cust_no and cust.cust_no in (select cust_no from customers where town='WORKINGHAM' or town = 'CASTLE COMBE');
6. Write a SELECT statement to find the car, which has the cheapest daily rate. select car.model_name, car.registration, car.car_group_name, cg.rate_per_day from cars car, cargroups cg where car.car_group_name=cg.car_group_name and cg.rate_per_day =(select min(rate_per_day) from cargroups);
7. Write a SELECT statement to return the total number of customers who have rented during 1990. Use the HAVING clause to exclude those customers who did not charge the rental to their account.
select count(*) from bookings where date_rent_start>=’01-01-90’ and date_rent_start<=’31-12-90’ group by amount_due having amount_due = 'y';
8. Write a SELECT statement, which returns just the models, groups and maintenance intervals of the cars in the cars table. The rows should be returned in ascending order of car group and descending order of maintenance interval. select model_name, car_group_name, maint_int from models order by car_group_name asc, maint_int desc;
9. Write a statement which creates a view of the bookings table to include customer number, car group, model, date reserved, reserved by, date rental start, rental period and pay method. create view MyView as select b.cust_no, c.car_group_name, c.model_name, b.date_reserved, b.reserved_by, b.date_rent_start, b.rental_period, b.pay_method from bookings b, cars c where b.registration=c.registration;
10. Select date bought for all cars in-group B3, display the date in a form similar to ' the 6 th day of June, 1990'. select registration, tochar(date_bought, "The DDth day of Month, yyyy") from cars where car_group_name='B3';
