Foros del Web » Programación para mayores de 30 ;) » Bases de Datos General » Oracle »

Consultas SQL de ORACLE express

Estas en el tema de Consultas SQL de ORACLE express en el foro de Oracle en Foros del Web. Me plantearon unas consultas en un examen y no se como realizarlas, me ayudaría mucho para estudiar si alguien me ayudara a hacerlas o a ...
  #1 (permalink)  
Antiguo 14/05/2015, 09:30
 
Fecha de Ingreso: abril-2015
Mensajes: 8
Antigüedad: 4 años, 2 meses
Puntos: 0
Pregunta Consultas SQL de ORACLE express

Me plantearon unas consultas en un examen y no se como realizarlas, me ayudaría mucho para estudiar si alguien me ayudara a hacerlas o a darme cuenta como se hacen para tenerlas de ejemplo.


Aquí los enunciados:

A. Es necesario mostrar el numero de tickets que hay en cada estado (ticket_status_code)
juntamente con el código de ese estado, siempre y cuando el ticket este expedido por un citroen (manufacturer_name) de categoría económica (vehicle_type_description). Si algún estado no tiene ningún ticket no se debe mostrar por pantalla.


B. Se ha descubierto que el sistema ha sacado algunos tickets por error debido al hecho que un vez solicitado el pago se ha generado un evento 'Crear Pago' (Make Payment) pero la fecha del ticket se ha grabado en el campo de fecha del campo cancelado (date_ticket_cancelled) i la barrera se a levantado como si el pago se hubiese realizado correctamente. Se quiere el nombre i el apellido del cliente, así como el telefono de su casa i el identificador del ticket (pcn_ticket_id). Es necesario tener en cuenta que los campos de fecha que no pertocan para un ticket determinado se formatean con un NULL.


C. En el momento en el que el sistema detecta que alguien quiere hacer el pago del ticket de parking genera un evento, y por tanto un proceso llamado ‘Crear pago’ (Make Payment). Dar por pantalla el numero de procesos de este estilo iniciado por el sistema por población (‘town_city’) conjuntamente con el nombre de esta población.


D. Se requiere mostrar por pantalla el nombre y apellidos de los clientes que no son ‘Cleveland’ i que se les ha generado un ticket correcto (‘ticket_status_description’) una vez ha querido pagarlo, es decir, que no se le ha cancelado ni a salido erróneo, Es necesario tener en cuenta que hace falta comprobar no solo el estado, si no también las fechas de la tabla PCN_Parking_Tickets (si son nulas o no, no deberían serlo, en ese caso no ha de aparecer en pantalla), se valorara hacerlo con MINUS.


E. Queremos saber todos los datos de clientes (relaciones ‘Customers’, ‘Addresses’, y
‘Officers’) de los clientes por los cuales se ha generado algún tique erróneo y que han cancelado alguna vez una operación a partir de la implantación de las nuevas tarifas el 1 de enero de 2015.



Aqui las tablas:




Código SQL:
Ver original
  1. CREATE TABLE Ref_Vehicle_Manufactures
  2. (
  3. manufacturer_code CHAR(6),
  4. manufacturer_name Varchar2(30),
  5. PRIMARY KEY (manufacturer_code)
  6. );
  7.  
  8.  
  9. CREATE TABLE Ref_Vehicle_Types
  10. (
  11. vehicle_type_code CHAR(6),
  12. vehicle_type_description Varchar2(30),
  13. PRIMARY KEY (vehicle_type_code)
  14. );
  15.  
  16.  
  17. CREATE TABLE Ref_PCN_Ticket_Types
  18. (
  19. pcn_type_code CHAR(6),
  20. pcn_type_description Varchar2(30),
  21. PRIMARY KEY (pcn_type_code)
  22. );
  23.  
  24.  
  25. CREATE TABLE Ref_Event_Types
  26. (
  27. event_type_code CHAR(6),
  28. event_type_description Varchar2(30),
  29. PRIMARY KEY (event_type_code)
  30. );
  31.  
  32.  
  33. CREATE TABLE Ref_PCN_Ticket_Status
  34. (
  35. ticket_status_code CHAR(6),
  36. ticket_status_description Varchar2(30),
  37. PRIMARY KEY (ticket_status_code)
  38. );
  39.  
  40.  
  41. CREATE TABLE Addresses
  42. (
  43. address_id CHAR(6),
  44. line_1_number_building CHAR(6),
  45. line_2_number_street CHAR(6),
  46. line_3_number_area_l_s Varchar2(6),
  47. line_4_other CHAR(6),
  48. town_city Varchar2(30),
  49. zip_postcode CHAR(5),
  50. state_province_country Varchar2(30),
  51. country Varchar2(30),
  52. other_address_details Varchar2(30),
  53. PRIMARY KEY (address_id)
  54. );
  55.  
  56.  
  57. CREATE TABLE Officers
  58. (
  59. officer_badge_number CHAR(6),
  60. officer_address_id CHAR(6),
  61. gender Varchar2(10),
  62. date_of_birth DATE,
  63. officer_first_name Varchar2(30),
  64. officer_middle_name Varchar2(30),
  65. officer_last_name Varchar2(30),
  66. officer_other_details Varchar2(30),
  67. PRIMARY KEY (officer_badge_number),
  68. FOREIGN KEY (officer_address_id)
  69. REFERENCES Addresses (address_id)
  70. );
  71.  
  72.  
  73. CREATE TABLE Customers
  74. (
  75. customer_id CHAR(6),
  76. address_id CHAR(6),
  77. first_name Varchar2(30),
  78. middle_name Varchar2(30),
  79. last_name Varchar2(30),
  80. home_phone CHAR(9),
  81. email_address Varchar2(60),
  82. cell_mobile_phone CHAR(9),
  83. other_details Varchar2(30),
  84. PRIMARY KEY (customer_id),
  85. FOREIGN KEY (address_id)
  86. REFERENCES Addresses (address_id)
  87. );
  88.  
  89.  
  90. CREATE TABLE Vehicles
  91. (
  92. vehicle_license_number CHAR(6),
  93. manufacturer_code CHAR(6),
  94. vehicle_type_code CHAR(6),
  95. year_of_manufacture CHAR(4),
  96. vehicle_details Varchar2(30),
  97. model Varchar2(30),
  98. PRIMARY KEY (vehicle_license_number),
  99. FOREIGN KEY (manufacturer_code)
  100. REFERENCES Ref_Vehicle_Manufactures (manufacturer_code),
  101. FOREIGN KEY (vehicle_type_code)
  102. REFERENCES Ref_Vehicle_Types (vehicle_type_code)
  103. );
  104.  
  105.  
  106. CREATE TABLE PCN_Parking_Tickets
  107. (
  108. pcn_ticket_id CHAR(6),
  109. customer_id CHAR(6) UNIQUE,
  110. issued_by_officer_badge_number CHAR(6),
  111. tiket_status_code CHAR(6),
  112. tiket_type_code CHAR(6),
  113. vehicle_licence_number CHAR(6),
  114. date_ticket_issued DATE,
  115. date_ticket_cancelled DATE,
  116. date_ticket_paid DATE,
  117. other_details Varchar2(30),
  118. PRIMARY KEY (pcn_ticket_id),
  119. FOREIGN KEY(customer_id)
  120. REFERENCES Customers (customer_id),
  121. FOREIGN KEY(issued_by_officer_badge_number)
  122. REFERENCES Officers (officer_badge_number),
  123. FOREIGN KEY(tiket_status_code)
  124. REFERENCES Ref_PCN_Ticket_Status (ticket_status_code),
  125. FOREIGN KEY(tiket_type_code)
  126. REFERENCES Ref_PCN_Ticket_Types (pcn_type_code),
  127. FOREIGN KEY(vehicle_licence_number)
  128. REFERENCES Vehicles
  129. );
  130.  
  131.  
  132. CREATE TABLE Events
  133. (
  134. event_id CHAR(6),
  135. event_type_code CHAR(6),
  136. pcn_ticket_id CHAR(6),
  137. date_of_event DATE,
  138. other_details Varchar2(30),
  139. PRIMARY KEY (event_id),
  140. FOREIGN KEY (event_type_code)
  141. REFERENCES Ref_Event_Types (event_type_code),
  142. FOREIGN KEY (pcn_ticket_id)
  143. REFERENCES PCN_Parking_Tickets (pcn_ticket_id)
  144. );

Cualquier ayuda por pequeña que sea me sera muy útil! gracias por vuestro tiempo.

Última edición por gnzsoloyo; 14/05/2015 a las 09:56 Razón: Añadir imagen
  #2 (permalink)  
Antiguo 15/05/2015, 09:59
 
Fecha de Ingreso: abril-2015
Mensajes: 8
Antigüedad: 4 años, 2 meses
Puntos: 0
Respuesta: Consultas SQL de ORACLE express

Bueno pues he estado probando y esto es lo que he sacado

A.
Código SQL:
Ver original
  1. SELECT COUNT (ticket_status_code), ticket_status_code
  2. FROM PCN_Parking_Tickets
  3. WHERE vehicle_licence_number IN (SELECT vehicle_licence_number
  4. FROM Vehicles
  5. WHERE manufacturer_code IN (SELECT manufacturer_code
  6. FROM Ref_vehicle_Manufacturers
  7. WHERE manufacturer_name = 'Citroen'))
  8. AND WHERE vehicle_licence_number IN (SELECT vehicle_licence_number
  9. FROM vehicles
  10. WHERE vehicle_type_code IN (SELECT vehiclet_type_code
  11. FROM ref_vehicle_tipes
  12. WHERE vehicle_type_description = 'economica'));

B. (No esta completa, pero, como mínimo se ejecuta)
Código SQL:
Ver original
  1. SELECT c.first_name, c.middle_name, c.last_name, c.home_phone, p.pcn_ticket_id
  2. FROM Customers c LEFT JOIN PCN_Parking_Tickets p
  3. ON c.customer_id = p.customer_id;

C. (Ni idea de como hacerla)


D. (Me falta la parte que comprueba ‘ticket_status_description’ pero no tengo mucha idea de como hacerlo)
Código SQL:
Ver original
  1. SELECT c.first_name, c.middle_name, c.last_name,
  2. FROM Customers c, Addresses a, PCN_Parking_Tickets p
  3. WHERE a.address_id = c.address_id AND c.customer_id = p.customer_id
  4. MINUS
  5. SELECT c.first_name, c.middle_name, c.last_name,
  6. FROM Customers c, Addresses a, PCN_Parking_Tickets p
  7. WHERE a.address_id = c.address_id
  8. AND c.customer_id = p.customer_id
  9. AND p.date_ticket_issued = NULL
  10. AND a.town_city = 'Cleveland';

E. (esta, sin el and se ejecuta bien en realidad debería comprobar ‘ticket_status_description’ y no ticket_status_code pero tampoco tengo mucha idea de como hacerlo)
Código SQL:
Ver original
  1. SELECT c.*
  2. FROM Customers c LEFT JOIN PCN_Parking_Tickets p
  3. ON c.customer_id = p.customer_id
  4. WHERE p.date_ticket_cancelled > '01-JAN-2015' AND p.ticket_status_code = 'erroneo';

Muchas gracias a todos los que le han echado un vistazo al tema. Si alguien me echa una mano se lo agradeceré mucho!
  #3 (permalink)  
Antiguo 19/05/2015, 13:53
 
Fecha de Ingreso: febrero-2015
Mensajes: 89
Antigüedad: 4 años, 5 meses
Puntos: 1
Respuesta: Consultas SQL de ORACLE express

Lo qe veo esque no usar inner join para algunas cosas que se requieren.
  #4 (permalink)  
Antiguo 20/05/2015, 07:57
 
Fecha de Ingreso: abril-2015
Mensajes: 8
Antigüedad: 4 años, 2 meses
Puntos: 0
Respuesta: Consultas SQL de ORACLE express

Cita:
Iniciado por jmendezg Ver Mensaje
Lo qe veo esque no usar inner join para algunas cosas que se requieren.
No termino de entender lo que quieres decir, me puedes dar algún ejemplo o explicármelo mas detalladamente?

Por cierto, muchísimas gracias por responder.
  #5 (permalink)  
Antiguo 21/05/2015, 09:12
 
Fecha de Ingreso: febrero-2015
Mensajes: 89
Antigüedad: 4 años, 5 meses
Puntos: 1
Respuesta: Consultas SQL de ORACLE express

Cita:
Iniciado por seloal Ver Mensaje
No termino de entender lo que quieres decir, me puedes dar algún ejemplo o explicármelo mas detalladamente?

Por cierto, muchísimas gracias por responder.
La primera consulta que hiciste esta muy mal hecha.....
bueno mira la primera consulta es así:

Código SQL:
Ver original
  1. SELECT COUNT (tiket_status_code), tiket_status_code
  2. FROM PCN_Parking_Tickets a INNER JOIN  Vehicles b
  3. ON a.vehicle_licence_number = b.vehicle_license_number
  4. INNER JOIN  Ref_Vehicle_Manufactures c  ON  b.manufacturer_code = c.manufacturer_code
  5. INNER JOIN  Ref_Vehicle_Types d ON  d.vehicle_type_code  = b.vehicle_type_code
  6. WHERE c.manufacturer_name = 'Citroen'  AND d.vehicle_type_description = 'economica'
  7. GROUP BY tiket_status_code

Te la pongo de ejemplo para que te hagas una idea de como funciona los inner joins.
Por otro lado es necesario que al menos des un ejemplo de los datos q van en cada tabla.

En fin, la 3era pregunta. Por lo que entiendo se debe "ejecutar un proceso" cada vez que realizan un pago. Quizás te esten pidiendo dar un eso de un trigger o un procedure.

y las otras son consultas como la primera. solo un poco de logica y ya :D
  #6 (permalink)  
Antiguo 22/05/2015, 08:52
 
Fecha de Ingreso: abril-2015
Mensajes: 8
Antigüedad: 4 años, 2 meses
Puntos: 0
Respuesta: Consultas SQL de ORACLE express

Cita:
Iniciado por jmendezg Ver Mensaje
La primera consulta que hiciste esta muy mal hecha.....

...

Te la pongo de ejemplo para que te hagas una idea de como funciona los inner joins.
Por otro lado es necesario que al menos des un ejemplo de los datos q van en cada tabla.

En fin, la 3era pregunta. Por lo que entiendo se debe "ejecutar un proceso" cada vez que realizan un pago. Quizás te esten pidiendo dar un eso de un trigger o un procedure.

y las otras son consultas como la primera. solo un poco de logica y ya :D

Gracias por el ejemplo, me es muy útil. Si, la primera era un desastre XD

Los datos de las tablas me los han dejado a libre interpretación siguiendo los nombres de las tablas, como no era lo que se me requería no le he prestado atención.

Tengo un par de dudas mas, en el caso (D) se pueden poner los ultimos AND como los he puesto después del WHERE?
En el caso (D) y (E) como puedo implementar "ticket_status_description" de la tabla "Ref_PCN_Ticket_status" en las consultas? (ticket_status_description determina si el ticket es erroneo o correcto para criterio de las consultas) no consigo que me funcione.

Siento si son dudas muy básicas, de momento tengo pocas nociones y no se me da especialmente bien.
  #7 (permalink)  
Antiguo 22/05/2015, 09:49
 
Fecha de Ingreso: febrero-2015
Mensajes: 89
Antigüedad: 4 años, 5 meses
Puntos: 1
Respuesta: Consultas SQL de ORACLE express

Cita:
Iniciado por seloal Ver Mensaje
Gracias por el ejemplo, me es muy útil. Si, la primera era un desastre XD

Los datos de las tablas me los han dejado a libre interpretación siguiendo los nombres de las tablas, como no era lo que se me requería no le he prestado atención.

Tengo un par de dudas mas, en el caso (D) se pueden poner los ultimos AND como los he puesto después del WHERE?
En el caso (D) y (E) como puedo implementar "ticket_status_description" de la tabla "Ref_PCN_Ticket_status" en las consultas? (ticket_status_description determina si el ticket es erroneo o correcto para criterio de las consultas) no consigo que me funcione.

Siento si son dudas muy básicas, de momento tengo pocas nociones y no se me da especialmente bien.
1- Si puedes poner muchos And despues del where.

2- La forma en la que puedes llegar a ese campo es relacionandolo La tabla PCN ticket con Ref mediando el id_ticket_code.

En fin te dejo.. como deberia ser la consulta... Lo puedes hacer con minus con algo de logica solo es cambiar un par de cositas. Eso ya es tu chamba.


Código SQL:
Ver original
  1. SELECT a.first_name, a.middle_name, a.last_name
  2. FROM Customers a
  3. INNER JOIN PCN_Parking_Tickets b  ON a.customer_id = b.customer_id
  4. INNER JOIN Addresses c ON c.address_id = a.address_id
  5. INNER JOIN Ref_PCN_Ticket_Status d  ON d.ticket_status_code = b.tiket_status_code
  6. WHERE c.address_id = 'Cleveland'  AND d.ticket_status_description NOT IN ('Cancelado','Erroneo')
  7. AND b.date_ticket_cancelled IS NOT NULL AND b.date_ticket_issued IS NOT NULL AND b.date_ticket_paid IS NOT NULL
  #8 (permalink)  
Antiguo 28/05/2015, 07:02
 
Fecha de Ingreso: abril-2015
Mensajes: 8
Antigüedad: 4 años, 2 meses
Puntos: 0
Respuesta: Consultas SQL de ORACLE express

Muchas gracias por tu ayuda me esta resultando muy útil.
  #9 (permalink)  
Antiguo 10/06/2015, 04:40
 
Fecha de Ingreso: abril-2015
Mensajes: 8
Antigüedad: 4 años, 2 meses
Puntos: 0
Respuesta: Consultas SQL de ORACLE express

Hola de nuevo, me han surgido un par de dudas mas (disculpas adelantadas por mi torpeza, este temario se me da realmente mal y voy muy atareado estos días)

La primera duda es con la primera consulta, por lo visto me han comentado que no es un SELECT COUNT y me han resaltado que a de mostrar el numero de tickets que hay en cada estado, creo que se debería hacer con un SELECT SUM pero ni estoy muy seguro ni se como se usan.

La segunda duda es respecto a la ultima pregunta que he modificado para acceder a ticket_status_description pero me da error ("B"."TIKET_STATUS_CODE": invalid identifier) entiendo el tipo de error, parece muy sencillo de solucionar pero no consigo arreglarlo.

Código SQL:
Ver original
  1. SELECT c.*
  2. FROM Customers c
  3. INNER JOIN PCN_Parking_Tickets p ON c.customer_id = p.customer_id
  4. INNER JOIN Ref_PCN_Ticket_Status d ON d.ticket_status_code = b.tiket_status_code
  5. WHERE p.date_ticket_cancelled > '01-JAN-2015'
  6. AND d.ticket_status_description = 'Erroneo';


Por ultimo la tercera pregunta me tiene absolutamente perdido, parece sencilla pero como tampoco he tratado con procesos me esta haciendo dar vueltas intentando hacerla.

Etiquetas: consulta+sql
Atención: Estás leyendo un tema que no tiene actividad desde hace más de 6 MESES, te recomendamos abrir un Nuevo tema en lugar de responder al actual.
Respuesta




La zona horaria es GMT -6. Ahora son las 09:31.