Data Storag
Practical Lecture
I. Theor
1. A data warehouse can be modeled by either a star schema or a snow ake schema. Brie y
describe the similarities and the differences of the two models, and then analyze their advantages
and disadvantages with regard to one another
2. Suppose that a data warehouse consists of the three dimensions time, doctor, and patient, and
the two measures count and charge, where charge is the fee that a doctor charges a patient for a
visit
(a) Enumerate three classes of schemas that are popularly used for modeling data warehouses
(b) Draw a schema diagram for the above data warehouse using one of the schema classes listed in
(a)
(c) Starting with the base cuboid [day; doctor; patient], what speci c OLAP operations should be
performed in order to list the total fee collected by each doctor in 2017
(d) To obtain the same list, write an SQL query assuming the data is stored in a relational database
with the schema fee (day, month, year, doctor, hospital, patient, count, charge)
II. Practic
1. Create the table cli (Clients) as follows
drop table cli
create table cli (codecli varchar2(10), nomcli varchar2(10), catcli number(1), vilcli varchar2(10),
paycli varchar2(10), cacli number(10,2), constraint pk_cli primary key (codecli))
2. Insert the following information in the table
insert into cli values ('C1','LECLEMENT',1, 'Paris', 'France', 10000)
insert into cli values ('C2','SEUL', 1, 'Paris', 'France', 2000);
insert into cli values ('C3','UNIQUE', 2, 'Nice', 'France', 1500);
insert into cli values ('C4','CORDOUX', 1, 'Madrid','Espagne',3000);
insert into cli values ('C5','PUISSANT', 1, 'Lyon', 'France', 2000)
insert into cli values ('C6','PARIS', 3, 'Paris', 'France', 4000);
insert into cli values ('C7','ADAM', 3, 'Tunis', 'Tunisie',10000);
insert into cli values ('C8','TRAIFORT', 1, 'Sousse','Tunisie',20000);
3. Show (select) all the Client
4. Decoding of the category using decode
5. Create a table com (pour commendes) with
numcom varchar2(10), codecli varchar2(10), datecom date
constraint pk_com primary key (numcom)
6. Insert the following information into values:
insert into com values ('N1', 'C1', '17-SEP-2009')
insert into com values ('N2', 'C1', '18-SEP-2009')
.
fi
?
fl
.
fl
insert into com values ('N3', 'C7', '17-SEP-2009')
7. Show all commands and use a title
8. Make a joint between the both tables by selecting all commande
9. Make a full outer join as follows:
ttitle 'full outer join
select * from cli full outer joincom on cli.codecli = com.codecli
10. Make a left join with cli.codecli = com.codecli using the select quer
Explain the reasonin
11. Make a right outer join using a select quer
12. Make a joint between CODECLI and DATECOM using two different queries
13. Show/select the number of client
14. Show/select the number of countrie
15. Show/select the number of clients by categor
16. Select the number of clients by category and by city (2 possibilities
17. Select the number of clients by category and by city using CUBE
18. Select the number of clients by category and by city using ROLLUP
19. Sort the clients in descending order by CACL
20. Sort the clients in descending order by CACLI and give the Ran
21. Sort the clients in descending order by CACLI and give the Ran
'
