38a7Ad5t

1# TP Cassendra
2
3## Exercice 1:
4
5
6##### Création de la base `resto_Fez` :
7
8```sql
9CREATE KEYSPACE IF NOT EXISTS resto_Fez 
10WITH REPLICATION = { 'class' : 'SimpleStrategy', 'replication_factor': 1};
11```
12
13```sql
14# pour les requetes qui suivents 
15use resto_Fez;
16```
17
18
19##### Création des Column Family:
20
21```sql
22
23# Création de la table restaurant, et les tables necessaires 
24# pour modéliser les relations necessaires
25
26CREATE TABLE Restaurant (
27   id INT PRIMARY KEY,
28   name TEXT,
29   street TEXT,
30   building_number INT,
31   phone TEXT,
32   cuisine_type text
33 );
34
35# Création de la table Inspection, et les tables necessaires 
36
37CREATE TABLE Inspection (
38   restaurant_id INT,
39   inspection_date DATE, 
40   violation_code TEXT,
41   violation_description TEXT,
42   score INT,
43   grade TEXT,
44   PRIMARY KEY ( restaurant_id, inspection_date )
45 );
46
47```
48## Exercice 2:
49
501)
51
52```sql
53SELECT * FROM Restaurant;
54```
55
562)
57
58```sql
59SELECT name FROM Restaurant;
60```
61
623)
63
64```sql
65SELECT name, street FROM Restaurant WHERE id=412;
66```
67
684)
69
70```sql
71SELECT inspection_date, grade from Inspection WHERE restaurant_id=412;
72```
73
745)
75
76```sql
77SELECT name FROM Restaurant where cuisine_type=‘mexicaine’ ALLOW FILTERING;
78```
79
806)
81
82```sql
83SELECT name FROM Restaurant where street=‘Florence’ ALLOW FILTERING;
84```
85
867)
87
88
89```sql
90SELECT grade, score FROM Inspection WHERE restaurant_id=412
91AND score >= 10 ALLOW FILTERING;
92```
93
948)
95
96```sql
97SELECT grade FROM Inspection WHERE score > 30 AND grade > '' ALLOW FILTERING;
98```
99
1009)
101
102
103```sql
104SELECT count(*) FROM Inspection WHERE score > 30 AND grade > '' ALLOW FILTERING;
105```
106
10710) 
108
109```sql
110SELECT name FROM Restaurant WHERE token(id) > 400 LIMIT 3;
111```
11211)
113
11412)
115
116```sql
117CREATE INDEX retaurant_name ON restaurant (name);
118```
119
12013)
12114)
122
12315)
124
125```sql
126SELECT name FROM restaurant
127WHERE id IN (SELECT restaurant_id FROM Inspection WHERE Grade >= ‘A’);
128```