Scribd
Upload
44 views

DataStorage Lab2

This document provides instructions for practical exercises on data storage and modeling using SQL. It includes: 1) Questions about star schemas, snowflake schemas, and modeling a data warehouse with dimensions of time, doctor, and patient and measures of count and charge. 2) Commands to create tables for clients and orders, and insert data. 3) Questions involving joins, aggregations, sorting on the client and order tables.

Uploaded by

Nistor Grozavu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
44 views

DataStorage Lab2

This document provides instructions for practical exercises on data storage and modeling using SQL. It includes: 1) Questions about star schemas, snowflake schemas, and modeling a data warehouse with dimensions of time, doctor, and patient and measures of count and charge. 2) Commands to create tables for clients and orders, and insert data. 3) Questions involving joins, aggregations, sorting on the client and order tables.

Uploaded by

Nistor Grozavu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

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
'

You might also like