Step 3. Create the queries listed below: NOTE: Before you begin to run Queries 1

Question

Step 3.

Create the queries listed below:

NOTE: Before you begin to run Queries 1 - 19, Enter the following two commands at the SQL Prompt:

Set Linesize 120
Set Pagesize 50

This will allow you to see all of your data without it wrapping. If your data still wraps, then you may have to adjust those settings.  Also, I would encourage you to do Natural Joins for any multiple table queries as indicated on page 151 of the 8th edition of your SQL Book.

Step 4.

Create 1 formatted Report following the examples given in the PDF version of Chapter 7 7th Edition of the SQL Book. The report should be similar to the following in format:

Create the queries listed below: NOTE: Before you begin to run Queries 1 - 19, Enter the following two commands at the SQL Prompt: Set Linesize 120 Set Pagesize 50 This will allow you to see all of your data without it wrapping. If your data still wraps, then you may have to adjust those settings. Also, I would encourage you to do Natural Joins for any multiple table queries as indicated on page 151 of the 8th edition of your SQL Book. List all records from each table. List all of your constraints in the database one table at a time. List all of your table names in the database. List the structure of each table. Create three new tables that all have the same structure as your Trainee Table above. Name each of the tables as follows: Chrysler_Trainee_TBL, Ford_Trainee_TBL, and GM_Trainee_TBL. (Do not relate these tables to other tables) Copy the respective Trainees from the Trainee Table into Chrysler_Trainee_TBL, Ford_Trainee_TBL, or GM_Trainee_TBL based on which company the Trainees are associated with. Use one command for each insertion of records into each table. Add a new CompanyName field to each of the three Trainee tables that you just created in Q5. Add the actual Company Name to each Trainee record that they are associated with in each of the three Trainee tables that you just created in Q5. Make the CompanyName field in the three Trainee Tables that you created in Q5 all required fields. Delete any records from the three Trainee Tables that you created in Q5 that do not have permanent phone numbers listed. List all data from each of the three Trainee Tables that you created in Q5. Create three indexes named Chrysler_Index, Ford_Index, and GM_Index on their Company Names respectively from each of the three Trainee Tables that were created in Q5. The three Trainee Tables that you created in Q5 are no longer needed, so you may delete all thee of them at this point. List the Apartment Building name and the number of apartments in each building that are available to be rented. Create an address listing of all Employers and all Apartment Buildings. The new headings to be displayed are: (ID, Name, Street, City, State, Zipcode, Phone) List the Building Name, Apartment Number, Apartment Type and Floor for any Apartments that have never been rented out. Sort the information by Building Name and then by Apartment Number both ascending. List all of the Trainee's Name, City, State and Phone Number from the Trainee table for any pair of Trainees who live in the same city and state and worked for the same companies. Note: Only list the city and state once. List the Building Name listed as "Building", AptNumber listed as "Apartment", the AptFloor the apartment is on listed as "Floor", theApartmentType listed as "Apartment Style" and the PricePerNight listed as "Nightly Charge". List the Trainee's First and Last name as "Full Name", the BuildingName, AptNumber, ApartmentType, PricePerNight, CheckInDate, CheckOutDate, "Number of Days Stayed" (Calculated Field), along with the total charge amount for their stay (calculated field, which includes the 8% tax and the ContractDiscount which should be discounted first) listed as "Total Charge". Create 1 formatted Report following the examples given in the PDF version of Chapter 7 7th Edition of the SQL Book. The report should be similar to the following in format: Create the view to support the Report named ABHC_Report Populate your view with data. Format the view to correspond to the report below. I am looking for each Apartment Building and the Apartments and their type, along with the total price for each apartment per night with tax included. Then for each Apartment you will show the Total for all their apartments. After listing all of the Apartments by their associated Apartment Buildings, then you will list the Total for both Apartment Buildings. NOTE: Your values may be slightly different than what I have listed above, but you should adhere to the formatting listed above.

Explanation / Answer

1. List all records from each table.

SELECT * FROM APARTMENT_BUILDING_TBL;

SELECT * FROM EMPLOYER_TBL;

SELECT * FROM APARTMENT_TYPE_TBL;

SELECT * FROM APARTMENT_TBL;

SELECT * FROM TRAINEE_TBL;

SELECT * FROM APARTMENT_RENTAL_TBL;

2. List all of your constraints in the database one table at a time.

SELECT CONSTRAINT_NAME,TABLE_NAME FROM ALL_CONSTRAINTS

WHERE CONSTRAINT_NAME IN

('ABTBL_BUILDID_PK',

'EMPTBL_EMPID_PK',

'APMNTTYPETBL_APMNTTYPEID_PK',

'APMNTTBL_APMNTID_PK',

'ABTBL_BUILDID_FK',

'APMNTTYPETBL_APMNTTYPEID_FK',

'TRAINTBL_TRAINID_PK',

'EMPTBL_EMPID_FK',

'RENTALTBL_RENTID_PK',

'APMNTTBL_APMNTID_FK',

'TRAINTBL_TRAINID_FK');

3. List all of your table names in the database.

select table_name from all_all_tables where

table_name in ('APARTMENT_BUILDING_TBL','EMPLOYER_TBL','APARTMENT_TYPE_TBL','APARTMENT_TBL','TRAINEE_TBL','APARTMENT_RENTAL_TBL');

4. List the structure of each table.

desc APARTMENT_BUILDING_TBL;

desc EMPLOYER_TBL;

desc APARTMENT_TYPE_TBL;

desc APARTMENT_TBL;

desc TRAINEE_TBL;

desc APARTMENT_RENTAL_TBL;

5. Create three new tables that all have the same structure as your Trainee Table above.

Name each of the tables as follows: Chrysler_Trainee_TBL, Ford_Trainee_TBL, and GM_Trainee_TBL. (Do not relate these tables to other tables)

create table Chrysler_Trainee_TBL as select * from TRAINEE_TBL where 1 = 2;

create table Ford_Trainee_TBL as select * from TRAINEE_TBL where 1 = 2;

create table GM_Trainee_TBL as select * from TRAINEE_TBL where 1 = 2;

6. Copy the respective Trainees from the Trainee Table into Chrysler_Trainee_TBL, Ford_Trainee_TBL, or GM_Trainee_TBL

based on which company the Trainees are associated with. Use one command for each insertion of records into each table.

insert into Chrysler_Trainee_TBL select * from TRAINEE_TBL where EmployerID = 1;

insert into Ford_Trainee_TBL select * from TRAINEE_TBL where EmployerID = 2;

insert into GM_Trainee_TBL select * from TRAINEE_TBL where EmployerID = 3;

commit;

7. Add a new CompanyName field to each of the three Trainee tables that you just created in Q5.

alter table Chrysler_Trainee_TBL add CompanyName varchar2(50);

alter table Ford_Trainee_TBL add CompanyName varchar2(50);

alter table GM_Trainee_TBL add CompanyName varchar2(50);

8. Add the actual Company Name to each Trainee record that they are associated with in each of the three Trainee tables that you just created in Q5.

update Chrysler_Trainee_TBL set CompanyName = (select CompanyName from Employer_TBL where EmployerID = 1);

update Ford_Trainee_TBL set CompanyName = (select CompanyName from Employer_TBL where EmployerID = 2);

update GM_Trainee_TBL set CompanyName = (select CompanyName from Employer_TBL where EmployerID = 3);

commit;

9. Make the CompanyName field in the three Trainee Tables that you created in Q5 all required fields.

ALTER TABLE Chrysler_Trainee_TBL MODIFY (CompanyName varchar2(50) NOT NULL);

ALTER TABLE Ford_Trainee_TBL MODIFY (CompanyName varchar2(50) NOT NULL);

ALTER TABLE GM_Trainee_TBL MODIFY (CompanyName varchar2(50) NOT NULL);

10. Delete any records from the three Trainee Tables that you created in Q5 that do not have permanent phone numbers listed.

delete from Chrysler_Trainee_TBL where PermPhone is null;

delete from Ford_Trainee_TBL where PermPhone is null;

delete from GM_Trainee_TBL where PermPhone is null;

commit;

11. List all data from each of the three Trainee Tables that you created in Q5.

select * from Chrysler_Trainee_TBL;

select * from Ford_Trainee_TBL;

select * from GM_Trainee_TBL;

12. Create three indexes named Chrysler_Index, Ford_Index, and GM_Index on

their Company Names respectively from each of the three Trainee Tables that were created in Q5.

CREATE INDEX Chrysler_Index ON Chrysler_Trainee_TBL (CompanyName);

CREATE INDEX Ford_Index ON Ford_Trainee_TBL (CompanyName);

CREATE INDEX GM_Index ON GM_Trainee_TBL (CompanyName);

13. The three Trainee Tables that you created in Q5 are no longer needed, so you may delete all three of them at this point.

drop table Chrysler_Trainee_TBL;

drop table Ford_Trainee_TBL;

drop table GM_Trainee_TBL;

14. List the Apartment Building name and the number of apartments in each building that are available to be rented.

select BuildingName, count(*) num_of_apartments from Apartment_Building_TBL, Apartment_TBL

where Apartment_Building_TBL.BuildingID = Apartment_TBL.BuildingID

and AptAvailable = 'Yes'

group by BuildingName;

15. Create an address listing of all Employers and all Apartment Buildings.

The new headings to be displayed are: (ID, Name, Street, City, State, Zipcode, Phone)

select BuildingID ID, BuildingName Name,Street,City,State,Zip Zipcode,Phone from Apartment_Building_TBL;

select EmployerID ID, CompanyName Name,Street,City,State,Zip Zipcode,Phone from Employer_TBL;

16. List the Building Name, Apartment Number, Apartment Type and Floor for any Apartments that have never been rented out.

Sort the information by Building Name and then by Apartment Number both ascending.

select Apartment_Building_TBL.BuildingName,

Apartment_TBL.AptNumber,

Apartment_Type_TBL.ApartmentType,

Apartment_TBL.AptFloor

from Apartment_Building_TBL,Apartment_Type_TBL,Apartment_TBL

where Apartment_Building_TBL.BuildingID = Apartment_TBL.BuildingID

and Apartment_Type_TBL.ApartmentTypeID = Apartment_TBL.ApartmentTypeID

and AptAvailable = 'Yes'

order by Apartment_Building_TBL.BuildingName,Apartment_TBL.AptNumber asc;

17. List all of the Trainee's Name, City, State and Phone Number from the Trainee table

for any pair of Trainees who live in the same city and state and worked for the same companies.

Note: Only list the city and state once.

select FirstName||', '||LastName Trainee_Name, City, State ,Phone

from Trainee_TBL, Employer_TBL

where Trainee_TBL.EmployerID = Employer_TBL.EmployerID;

18. List the Building Name listed as "Building", AptNumber listed as "Apartment", the AptFloor the apartment is on listed as "Floor",

the ApartmentType listed as "Apartment Style" and the PricePerNight listed as "Nightly Charge".

select BuildingName Building, AptNumber Apartment,AptFloor Floor, ApartmentType "Apartment Style"

from Apartment_Building_TBL,Apartment_TBL,Apartment_Type_TBL

where Apartment_Building_TBL.BuildingID = Apartment_TBL.BuildingID

and Apartment_Type_TBL.ApartmentTypeID = Apartment_TBL.ApartmentTypeID;

19. List the Trainee's First and Last name as "Full Name",

the BuildingName, AptNumber, ApartmentType, PricePerNight, CheckInDate, CheckOutDate,

"Number of Days Stayed" (Calculated Field), along with the total charge amount for their stay

(calculated field, which includes the 8% tax and the ContractDiscount which should be discounted first) listed as "Total Charge".

select FirstName||', '||LastName "Full Name",

BuildingName,

AptNumber ,

ApartmentType,

PricePerNight,

CheckInDate,

CheckOutDate,

(CheckOutDate - CheckInDate) "Number of Days Stayed",

((PricePerNight) * (CheckOutDate - CheckInDate)) - (ContractDiscount) + ((PricePerNight) * (CheckOutDate - CheckInDate) * (0.08)) "Total Charge"

from Trainee_TBL,

Apartment_Building_TBL,

Apartment_TBL,

Apartment_Type_TBL,

Apartment_Rental_TBL,

Employer_TBL

where Apartment_Building_TBL.BuildingID = Apartment_TBL.BuildingID

Trainee_TBL.EmployerID = Employer_TBL.EmployerID

Apartment_Type_TBL.ApartmentTypeID = Apartment_TBL.ApartmentTypeID

Trainee_TBL.TraineeID = Apartment_Rental_TBL.TraineeID

and Apartment_TBL.ApartmentID = Apartment_Rental_TBL.ApartmentID;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.