3. SQL queries Create materialized view with stores name of manufacturer, count of all repairs of all cars of current ma

[answerhappygod]

3. SQL queries
Create materialized view with stores name of manufacturer, count
of all repairs of all cars of current manufacturer, count of all
car rentals for current manufacturer.
If manufacturer doesn't have rentals or repairs this anyway
should be included in report.
Using materialized view created in Query1. Create query that
extract all data from materialized view and adds category of
manufacturer (additional attribute). If manufacturer maintenances
are more than rentals then manufacturer is bad, in other case –
good.
You need to create dimension with the following levels: Country,
Manufacturer Name, Car Model; and calculate summary of rental cost
for every level of dimension including grand total from all
levels.
Sort results by county, then by manufacturer name, then by car
model.
Calculate average values of rentals cost in every month, every
year and average across all periods, in one query.
Sort results by year, then by month.
Create OLAP cube with the following dimensions: year of rental,
customer address, car class. Summary of rental cost is fact
attribute.
OLAP cube should include data only from 2010 and 2011 years and
only for customers who address has letter 't' in customer's
address.
Sort data by year, address, class.
With OLAP cube made in Q5:
- modify to see only:
- totals for each
year, each address and each class.
- grand total from
all dimensions.
- add grouping columns for every attribute to
see if NULL is generated by DBMS;
- show grouping level;
- check if there is duplicated data;
Create materialized view which stores countries, name of
manufacturer, rental year from rental date, summarize rentals costs
for particular record.
Using materialized view created in Q7:
Create forecast of planned income from rental costs for
manufacturers Capital Motors and Milan Motors (result for every
manufacturer should appear):
Add attribute that calculates difference between current year
and previous yea.
Sort data by manufacturer, year.
Using materialized view created in Q7:
Before extracting data, launch script below. Script creates
hierarchy (unary relation) in table customer and adds data.
Alter table Customers ADD RefCust NUMBER(6);
Alter table Customers
add constraint CustRefHier
Foreign Key
(RefCust)
References Customers(CUSTID);
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Zhang') WHERE CUSTID= (Select custid FROM Customers where
CUSTNUM='133819');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Zhang') WHERE CUSTID= (Select custid FROM Customers where
CUSTNUM='182194');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Martin') WHERE CUSTID= (Select custid FROM Customers
where CUSTNUM='246754');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Martin') WHERE CUSTID= (Select custid FROM Customers
where CUSTNUM='285028');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Martin') WHERE CUSTID= (Select custid FROM Customers
where CUSTNUM='294827');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Chen') WHERE CUSTID= (Select custid FROM Customers where
CUSTNUM='381074');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Chen') WHERE CUSTID= (Select custid FROM Customers where
CUSTNUM='429292');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Perez') WHERE CUSTID= (Select custid FROM Customers where
CUSTNUM='482910');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Perez') WHERE CUSTID= (Select custid FROM Customers where
CUSTNUM='539118');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Jackson') WHERE CUSTID= (Select custid FROM Customers
where CUSTNUM='592937');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Jackson') WHERE CUSTID= (Select custid FROM Customers
where CUSTNUM='730282');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Jefferson') WHERE CUSTID= (Select custid FROM Customers
where CUSTNUM='876586');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Jefferson') WHERE CUSTID= (Select custid FROM Customers
where CUSTNUM='925820');
UPDATE Customers SET RefCust=(Select custid FROM Customers where
CUSTNAME='Stewart') WHERE CUSTID= (Select custid FROM Customers
where CUSTNUM='956732');
Extract from customers: