Advertisement
Guest User

Companhia Taxis

a guest
Oct 21st, 2019
95
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. drop table motorista cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  2. create table motorista (
  3.     Nome varchar(20),
  4.     NCartaCond varchar(20),
  5.     DataNasc text,
  6.     Nbi char(4) primary key
  7. );
  8.  
  9. insert into motorista values ('Manuel Duarte','L-123',to_date('140176','DDMMYY'),'1234');
  10. insert into motorista values ('Fernando Nobre','L-124',to_date('140177','DDMMYY'),'1235');
  11. insert into motorista values ('Anibal Silva','L-125',to_date('140178','DDMMYY'),'1236');
  12. insert into motorista values ('Francisco Lopes','L-126',to_date('140179','DDMMYY'),'1237');
  13.  
  14.  
  15.  
  16.  
  17.  
  18. drop table telefone cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  19. create table telefone (
  20.     Nbi char(4),
  21.     Telefone char(10),
  22.     primary key (Nbi, Telefone),
  23.     foreign key (Nbi) references motorista on delete restrict
  24. );
  25.  
  26. --Manuel Duarte
  27. insert into telefone values ('1234', unnest(array['266 262626', '939393939']));  -- 'unnest(array[1,...,n])' coloca vários valores de 'Telefone', distribuídos por linhas, para um mesmo valor em 'Nbi'
  28.  
  29. --Fernando Nobre
  30. insert into telefone values ('1235', unnest(array['266 262627', '939393940']));
  31.  
  32. --Anibal Silva
  33. insert into telefone values ('1236', unnest(array['266 262628', '939393941']));
  34.  
  35. --Francisco Lopes
  36. insert into telefone values ('1237','266 262629');
  37.  
  38.  
  39.  
  40.  
  41.  
  42. drop table modelo cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  43. create table modelo (
  44.     Marca varchar(10),
  45.     Modelo varchar(22),
  46.     Nlugares Integer,
  47.     Consumo varchar(4),
  48.     primary key (Marca, Modelo)
  49. );
  50.  
  51. insert into modelo values ('Renault','Espace','7','7l');
  52. insert into modelo values ('Mercedes','CLK','7','9l');
  53. insert into modelo values ('Honda','Civic','5','5l');
  54. insert into modelo values ('Mercedes','classe S','5','6.5l');
  55.  
  56.  
  57.  
  58.  
  59.  
  60. drop table taxi cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  61. create table taxi (
  62.     Modelo varchar(22),
  63.     Ano char(4),
  64.     Kms Integer,
  65.     Matricula char(8) primary key
  66. );
  67.  
  68. insert into taxi values ('Espace', '2015', '123098', '22-AA-22');
  69. insert into taxi values ('CLK', '2014', '234554', '21-AA-22');
  70. insert into taxi values ('Civic', '2012', '89764', '20-AA-22');
  71. insert into taxi values ('classe S', '2015', '79744', '19-AA-22');
  72.  
  73.  
  74.  
  75.  
  76.  
  77. drop table servico cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  78. create table servico (
  79.     DataInicio text,
  80.     DataFim text,
  81.     Kms Integer,
  82.     Valor varchar(7),
  83.     Matricula char(8),
  84.     CoordGPSInic real,
  85.     CoordGPSfin real,
  86.     primary key (Matricula, CoordGPSInic, CoordGPSfin),
  87.     foreign key (Matricula) references taxi on delete restrict
  88. );
  89.  
  90. insert into servico values (to_timestamp('02-01-2016 08:12:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('02-01-2016 08:32:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'12','5.25€','19-AA-22','0.75','0.76');
  91. insert into servico values (to_timestamp('02-01-2016 08:43:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('02-01-2016 08:52:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'7','3.25€','19-AA-22','0.77','0.78');
  92. insert into servico values (to_timestamp('02-01-2016 08:53:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('02-01-2016 09:59:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'98','53.25€','19-AA-22','0.78','0.84');
  93. insert into servico values (to_timestamp('02-01-2016 10:13:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('02-01-2016 10:29:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'18','19.25€','19-AA-22','0.84','0.85');
  94. insert into servico values (to_timestamp('02-01-2016 11:10:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('02-01-2016 11:39:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'23','22.25€','19-AA-22','0.86','0.88');
  95. insert into servico values (to_timestamp('02-01-2016 12:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('02-01-2016 13:39:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'21','42.25€','19-AA-22','0.88','0.90');
  96. insert into servico values (to_timestamp('02-01-2016 15:20:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('02-01-2016 15:39:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'9','12.25€','19-AA-22','0.93','0.94');
  97. --
  98. insert into servico values (to_timestamp('02-01-2016 08:20:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('02-01-2016 15:33:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'202','127.95€','20-AA-22','0.76','0.98');
  99. ----------------------------------------------------
  100. insert into servico values (to_timestamp('03-01-2016 08:02:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 8:33:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'13','5.10€','21-AA-22','0.76','0.77');
  101. insert into servico values (to_timestamp('03-01-2016 08:35:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 8:43:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'5','3.00€','21-AA-22','0.77','0.78');
  102. insert into servico values (to_timestamp('03-01-2016 08:59:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 9:18:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'12','5.15€','21-AA-22','0.78','0.79');
  103. insert into servico values (to_timestamp('03-01-2016 09:50:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 10:31:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'28','29.10€','21-AA-22','0.79','0.82');
  104. --
  105. insert into servico values (to_timestamp('04-01-2016 08:22:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('04-01-2016 08:43:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'14','6.35€','20-AA-22','0.74','0.76');
  106. insert into servico values (to_timestamp('04-01-2016 08:58:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('04-01-2016 09:42:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'35','18.05€','20-AA-22','0.77','0.80');
  107. insert into servico values (to_timestamp('04-01-2016 10:30:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('04-01-2016 11:39:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'102','54.00€','20-AA-22','0.80','0.88');
  108. insert into servico values (to_timestamp('04-01-2016 12:45:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('04-01-2016 13:04:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'12','6.00€','20-AA-22','0.88','0.90');
  109. insert into servico values (to_timestamp('04-01-2016 15:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('04-01-2016 15:39:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'20','12.25€','20-AA-22','0.90','0.92');
  110. insert into servico values (to_timestamp('04-01-2016 15:50:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('04-01-2016 16:12:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'24','13.85€','20-AA-22','0.92','0.94');
  111. --
  112. insert into servico values (to_timestamp('05-01-2016 08:20:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('05-01-2016 08:43:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'16','7.35€','21-AA-22','0.75','0.76');
  113. insert into servico values (to_timestamp('05-01-2016 08:58:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('05-01-2016 09:47:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'33','15.05€','21-AA-22','0.76','0.80');
  114. insert into servico values (to_timestamp('05-01-2016 10:33:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('05-01-2016 11:30:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'90','49.50€','21-AA-22','0.80','0.86');
  115. insert into servico values (to_timestamp('05-01-2016 12:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('05-01-2016 12:18:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'10','6.00€','21-AA-22','0.86','0.87');
  116. insert into servico values (to_timestamp('05-01-2016 16:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('05-01-2016 16:41:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'21','12.95€','21-AA-22','0.89','0.90');
  117. insert into servico values (to_timestamp('05-01-2016 16:42:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('05-01-2016 16:59:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'28','14.05€','21-AA-22','0.91','0.92');
  118. --
  119. insert into servico values (to_timestamp('06-01-2016 08:21:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('06-01-2016 08:45:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'15','6.55€','22-AA-22','0.74','0.76');
  120. insert into servico values (to_timestamp('06-01-2016 08:59:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('06-01-2016 09:43:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'33','17.75€','22-AA-22','0.77','0.80');
  121. insert into servico values (to_timestamp('06-01-2016 10:32:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('06-01-2016 11:38:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'99','53.70€','22-AA-22','0.80','0.88');
  122. insert into servico values (to_timestamp('06-01-2016 12:44:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('06-01-2016 13:02:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'11','6.10€','22-AA-22','0.88','0.90');
  123. insert into servico values (to_timestamp('06-01-2016 15:03:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('06-01-2016 15:44:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'23','10.05€','22-AA-22','0.90','0.92');
  124.  
  125.  
  126.  
  127.  
  128.  
  129. drop table turno cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  130. create table turno (
  131.     DataInicio text,
  132.     DataFim text,
  133.     KmInicio Integer,
  134.     KmFim Integer,
  135.     Matricula char(8),
  136.     Nbi char(4),
  137.     primary key (Matricula, Nbi),
  138.     foreign key (Matricula) references taxi on delete restrict,
  139.     foreign key (Nbi) references motorista on delete restrict
  140. );
  141.  
  142. insert into turno values (to_timestamp('02-01-2016 08:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('02-01-2016 17:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '79744', '79944', '19-AA-22', '1234');
  143. insert into turno values (to_timestamp('02-01-2016 08:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('02-01-2016 17:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '89764', '89964', '20-AA-22', '1235');
  144. insert into turno values (to_timestamp('03-01-2016 08:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('03-01-2016 17:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '234554', '234954', '21-AA-22', '1236');
  145. insert into turno values (to_timestamp('03-01-2016 08:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('03-01-2016 17:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '123098', '123498', '22-AA-22', '1237');
  146. --
  147. insert into turno values (to_timestamp('04-01-2016 08:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('04-01-2016 17:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '89964','90212', '20-AA-22', '1234');
  148. insert into turno values (to_timestamp('05-01-2016 08:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('05-01-2016 17:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '234954', '235177', '21-AA-22', '1234');
  149. insert into turno values (to_timestamp('06-01-2016 08:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('06-01-2016 17:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '123498', '123703', '22-AA-22', '1234');
  150.  
  151.  
  152.  
  153.  
  154. drop table cliente cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  155. create table cliente (
  156.     Nome varchar(20),
  157.     Morada text,
  158.     CodigoPostal text,
  159.     Nif char(12),
  160.     primary key (Nif)
  161. );
  162.  
  163.  
  164. insert into cliente values ('José Silva','Rua Antonio Silva 23','7100-434 Évora','600700800900');
  165. insert into cliente values ('Francisco Passos', 'Rua Manuel Passos 12','7000-131 Évora','600700800901');
  166. insert into cliente values ('Pedro Sousa','Rua Joaquim Sousa 21','7500-313 Évora','600700800902');
  167.  
  168.  
  169.  
  170.  
  171.  
  172. drop table pedido cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  173. create table pedido (
  174.     Nif char(12),
  175.     MoradaInicio varchar(20),
  176.     CodigoPostalInicio text,
  177.     DataPedido text,
  178.     Matricula char(8),
  179.     DataInicio text,
  180.     primary key (Nif, Matricula),
  181.     foreign key (Nif) references cliente on delete restrict,
  182.     foreign key (Matricula) references taxi on delete restrict
  183. );
  184.  
  185. insert into pedido values ('600700800900', 'Rua Silva Pais 33', '7120-212 Évora', to_timestamp('02-01-2016 09:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '19-AA-22', to_timestamp('02-01-2016 08:43:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone);
  186.  
  187.  
  188. --a)
  189. select Matricula
  190. from modelo natural inner join taxi
  191. where Marca like 'Mercedes' and taxi.Modelo=modelo.Modelo
  192.  
  193. --b)
  194. select Nome
  195. from modelo natural inner join turno natural inner join motorista natural inner join taxi
  196. where Marca like 'Mercedes' and modelo.Modelo=taxi.Modelo and taxi.Matricula=turno.Matricula and turno.Nbi=motorista.Nbi
  197.  
  198. --c)
  199. select distinct Telefone
  200. from pedido, turno, telefone, servico
  201. where Nif like '600700800900' and pedido.Matricula=servico.Matricula and servico.Matricula=turno.Matricula and turno.Nbi=telefone.Nbi
  202.  
  203.  
  204. --d)
  205. select Marca, Modelo
  206. from motorista natural inner join turno natural inner join taxi natural inner join modelo
  207. where Nome like 'Anibal Silva' and motorista.Nbi=turno.Nbi and turno.Matricula=taxi.Matricula and taxi.Modelo=modelo.Modelo
  208.  
  209. --e)
  210. select Nome
  211. from motorista
  212. except
  213. select motorista.Nome
  214. from cliente, motorista, pedido, turno
  215. where cliente.Nome like 'José Silva' and cliente.Nif=pedido.Nif and pedido.Matricula=turno.Matricula and turno.Nbi=motorista.Nbi
  216.  
  217. --f)
  218. select Nome
  219. from motorista
  220. except
  221. select Nome
  222. from modelo natural inner join turno natural inner join motorista natural inner join taxi
  223. where Marca like 'Mercedes' and modelo.Modelo=taxi.Modelo and taxi.Matricula=turno.Matricula and turno.Nbi=motorista.Nbi
  224.  
  225.  
  226. --g)
  227. select Nome
  228. from motorista
  229. where not exists ((select matricula from taxi)
  230. except
  231. (select matricula from turno where motorista.Nbi=turno.Nbi))
  232.  
  233.  
  234. --h)
  235. select Nbi, count(servico.Matricula) as NoServicos
  236. from servico, turno natural inner join motorista
  237. where date(servico.DataInicio)=date(turno.DataInicio) and servico.Matricula=turno.Matricula and turno.Nbi=motorista.Nbi
  238. group by Nbi
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement