API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Oct 20th, 2019
111
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.33 KB | None | 0 0
raw download clone embed print report
  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. --foreign key (Modelo) references modelo on delete restrict
  67. );
  68.  
  69. insert into taxi values ('Espace', '2015', '123098', '22-AA-22');
  70. insert into taxi values ('CLK', '2014', '234554', '21-AA-22');
  71. insert into taxi values ('Civic', '2012', '89764', '20-AA-22');
  72. insert into taxi values ('classe S', '2015', '79744', '19-AA-22');
  73.  
  74.  
  75.  
  76.  
  77.  
  78. drop table servico cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  79. create table servico (
  80. DataInicio text,
  81. DataFim text,
  82. Kms Integer,
  83. Valor varchar(7),
  84. Matricula char(8),
  85. CoordGPSInic real,
  86. CoordGPSfin real,
  87. primary key (Matricula, CoordGPSInic, CoordGPSfin),
  88. foreign key (Matricula) references taxi on delete restrict
  89. );
  90.  
  91. --insert into servico values ('NULL','NULL','NULL','NULL','NULL','NULL','NULL');
  92. 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');
  93. 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.76','0.77');
  94. 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.77','0.78');
  95. 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.78','0.79');
  96. 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.79','0.80');
  97. 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.80','0.81');
  98. 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.81','0.82');
  99. --
  100. insert into servico values (to_timestamp('03-01-2016 08:12:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 08:32:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'15','9.25€','20-AA-22','0.75','0.76');
  101. insert into servico values (to_timestamp('03-01-2016 08:43:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 08:52:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'22','13.25€','21-AA-22','0.76','0.77');
  102. insert into servico values (to_timestamp('03-01-2016 08:53:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 09:59:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'66','55.25€','22-AA-22','0.77','0.78');
  103. insert into servico values (to_timestamp('03-01-2016 10:13:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 10:29:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'108','110.25€','19-AA-22','0.80','0.83');
  104. insert into servico values (to_timestamp('03-01-2016 11:10:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 11:39:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'21','20.25€','20-AA-22','0.79','0.80');
  105. insert into servico values (to_timestamp('03-01-2016 12:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 13:39:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'16','15.25€','21-AA-22','0.80','0.81');
  106. insert into servico values (to_timestamp('03-01-2016 15:20:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,to_timestamp('03-01-2016 15:39:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone,'19','18.25€','22-AA-22','0.81','0.82');
  107.  
  108. -- VERIFICAR VALORES NAS TABELAS *
  109.  
  110.  
  111.  
  112.  
  113.  
  114. drop table turno cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  115. create table turno (
  116. DataInicio text,
  117. DataFim text,
  118. KmInicio Integer,
  119. KmFim Integer,
  120. Matricula char(8),
  121. Nbi char(4),
  122. primary key (Matricula, Nbi),
  123. foreign key (Matricula) references taxi on delete restrict,
  124. foreign key (Nbi) references motorista on delete restrict
  125. );
  126.  
  127. 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');
  128. 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');
  129. 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');
  130. 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');
  131. --
  132. 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 11:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '79744', '79944', '20-AA-22', '1234');
  133. insert into turno values (to_timestamp('04-01-2016 12:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('04-01-2016 13:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '79944', '80000', '20-AA-22', '1236');
  134. insert into turno values (to_timestamp('05-01-2016 18:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('05-01-2016 20:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '80000', '80090', '21-AA-22', '1234');
  135. insert into turno values (to_timestamp('05-01-2016 22:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, to_timestamp('05-01-2016 23:00:00','DD-MM-YYYY HH24:MI:SS')::timestamp without time zone, '80090', '80150', '22-AA-22', '1234');
  136.  
  137.  
  138.  
  139.  
  140. drop table cliente cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  141. create table cliente (
  142. Nome varchar(20),
  143. Morada text,
  144. CodigoPostal text,
  145. Nif char(12),
  146. primary key (Nif)
  147. );
  148.  
  149.  
  150. insert into cliente values ('José Silva','Rua Antonio Silva 23','7100-434 Évora','600700800900');
  151. insert into cliente values ('Francisco Passos', 'Rua Manuel Passos 12','7000-131 Évora','600700800901');
  152. insert into cliente values ('Pedro Sousa','Rua Joaquim Sousa 21','7500-313 Évora','600700800902');
  153.  
  154.  
  155.  
  156.  
  157.  
  158. drop table pedido cascade; -- Apaga a tabela, caso esta já esteja criada no pgAdmin4
  159. create table pedido (
  160. Nif char(12),
  161. MoradaInicio varchar(20),
  162. CodigoPostalInicio text,
  163. DataPedido text,
  164. Matricula char(8),
  165. DataInicio text,
  166. primary key (Nif, Matricula),
  167. foreign key (Nif) references cliente on delete restrict,
  168. foreign key (Matricula) references taxi on delete restrict
  169. );
  170.  
  171. 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);
  172.  
  173.  
  174. --a)
  175. --select Matricula
  176. --from modelo natural inner join taxi
  177. --where Marca like 'Mercedes' and taxi.Modelo=modelo.Modelo
  178.  
  179. --b)
  180. --select Nome
  181. --from modelo natural inner join turno natural inner join motorista natural inner join taxi
  182. --where Marca like 'Mercedes' and modelo.Modelo=taxi.Modelo and taxi.Matricula=turno.Matricula and turno.Nbi=motorista.Nbi
  183.  
  184. --c)
  185. --select distinct Telefone
  186. --from pedido, turno, telefone, servico
  187. --where Nif like '600700800900' and pedido.Matricula=servico.Matricula and servico.Matricula=turno.Matricula and turno.Nbi=telefone.Nbi
  188.  
  189.  
  190. --d)
  191. --select Marca, Modelo
  192. --from motorista natural inner join turno natural inner join taxi natural inner join modelo
  193. --where Nome like 'Anibal Silva' and motorista.Nbi=turno.Nbi and turno.Matricula=taxi.Matricula and taxi.Modelo=modelo.Modelo
  194.  
  195. --e)
  196. --select Nome
  197. --from motorista
  198. --except
  199. --select motorista.Nome
  200. --from cliente, motorista, pedido, turno
  201. --where cliente.Nome like 'José Silva' and cliente.Nif=pedido.Nif and pedido.Matricula=turno.Matricula and turno.Nbi=motorista.Nbi
  202.  
  203. --f)
  204. --select Nome
  205. --from motorista
  206. --except
  207. --select Nome
  208. --from modelo natural inner join turno natural inner join motorista natural inner join taxi
  209. --where Marca like 'Mercedes' and modelo.Modelo=taxi.Modelo and taxi.Matricula=turno.Matricula and turno.Nbi=motorista.Nbi
  210.  
  211.  
  212. --g)
  213. --select max(Nome)
  214. --from taxi natural inner join motorista natural inner join turno
  215. --where motorista.Nbi=turno.Nbi and turno.Matricula=taxi.Matricula
  216.  
  217.  
  218. --h)
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!