Consultas SQL de ORACLE express

[seloal]

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:

CREATE TABLE Ref_Vehicle_Manufactures
(
manufacturer_code CHAR(6),
manufacturer_name Varchar2(30),
PRIMARY KEY (manufacturer_code)
);
 
 
CREATE TABLE Ref_Vehicle_Types
(
vehicle_type_code CHAR(6),
vehicle_type_description Varchar2(30),
PRIMARY KEY (vehicle_type_code)
);
 
 
CREATE TABLE Ref_PCN_Ticket_Types
(
pcn_type_code CHAR(6),
pcn_type_description Varchar2(30),
PRIMARY KEY (pcn_type_code)
);
 
 
CREATE TABLE Ref_Event_Types
(
event_type_code CHAR(6),
event_type_description Varchar2(30),
PRIMARY KEY (event_type_code)
);
 
 
CREATE TABLE Ref_PCN_Ticket_Status
(
ticket_status_code CHAR(6),
ticket_status_description Varchar2(30),
PRIMARY KEY (ticket_status_code)
);
 
 
CREATE TABLE Addresses
(
address_id CHAR(6),
line_1_number_building CHAR(6),
line_2_number_street CHAR(6),
line_3_number_area_l_s Varchar2(6),
line_4_other CHAR(6),
town_city Varchar2(30),
zip_postcode CHAR(5),
state_province_country Varchar2(30),
country Varchar2(30),
other_address_details Varchar2(30),
PRIMARY KEY (address_id)
);
 
 
CREATE TABLE Officers
(
officer_badge_number CHAR(6),
officer_address_id CHAR(6),
gender Varchar2(10),
date_of_birth DATE,
officer_first_name Varchar2(30),
officer_middle_name Varchar2(30),
officer_last_name Varchar2(30),
officer_other_details Varchar2(30),
PRIMARY KEY (officer_badge_number),
FOREIGN KEY (officer_address_id)
REFERENCES Addresses (address_id)
);
 
 
CREATE TABLE Customers
(
customer_id CHAR(6),
address_id CHAR(6),
first_name Varchar2(30),
middle_name Varchar2(30),
last_name Varchar2(30),
home_phone CHAR(9),
email_address Varchar2(60),
cell_mobile_phone CHAR(9),
other_details Varchar2(30),
PRIMARY KEY (customer_id),
FOREIGN KEY (address_id)
REFERENCES Addresses (address_id)
);
 
 
CREATE TABLE Vehicles
(
vehicle_license_number CHAR(6),
manufacturer_code CHAR(6),
vehicle_type_code CHAR(6),
year_of_manufacture CHAR(4),
vehicle_details Varchar2(30),
model Varchar2(30),
PRIMARY KEY (vehicle_license_number),
FOREIGN KEY (manufacturer_code)
REFERENCES Ref_Vehicle_Manufactures (manufacturer_code),
FOREIGN KEY (vehicle_type_code)
REFERENCES Ref_Vehicle_Types (vehicle_type_code)
);
 
 
CREATE TABLE PCN_Parking_Tickets
(
pcn_ticket_id CHAR(6),
customer_id CHAR(6) UNIQUE,
issued_by_officer_badge_number CHAR(6),
tiket_status_code CHAR(6),
tiket_type_code CHAR(6),
vehicle_licence_number CHAR(6),
date_ticket_issued DATE,
date_ticket_cancelled DATE,
date_ticket_paid DATE,
other_details Varchar2(30),
PRIMARY KEY (pcn_ticket_id),
FOREIGN KEY(customer_id)
REFERENCES Customers (customer_id),
FOREIGN KEY(issued_by_officer_badge_number)
REFERENCES Officers (officer_badge_number),
FOREIGN KEY(tiket_status_code)
REFERENCES Ref_PCN_Ticket_Status (ticket_status_code),
FOREIGN KEY(tiket_type_code)
REFERENCES Ref_PCN_Ticket_Types (pcn_type_code),
FOREIGN KEY(vehicle_licence_number)
REFERENCES Vehicles 
);
 
 
CREATE TABLE Events
(
event_id CHAR(6),
event_type_code CHAR(6),
pcn_ticket_id CHAR(6),
date_of_event DATE,
other_details Varchar2(30),
PRIMARY KEY (event_id),
FOREIGN KEY (event_type_code)
REFERENCES Ref_Event_Types (event_type_code),
FOREIGN KEY (pcn_ticket_id)
REFERENCES PCN_Parking_Tickets (pcn_ticket_id)
);
 

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

[seloal]

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

A.

Código SQL:

SELECT COUNT (ticket_status_code), ticket_status_code 
FROM PCN_Parking_Tickets
WHERE vehicle_licence_number IN (SELECT vehicle_licence_number
FROM Vehicles
WHERE manufacturer_code IN (SELECT manufacturer_code
FROM Ref_vehicle_Manufacturers
WHERE manufacturer_name = 'Citroen'))
AND WHERE vehicle_licence_number IN (SELECT vehicle_licence_number
FROM vehicles
WHERE vehicle_type_code IN (SELECT vehiclet_type_code 
FROM ref_vehicle_tipes
WHERE vehicle_type_description = 'economica'));
 

B. (No esta completa, pero, como mínimo se ejecuta)

Código SQL:

SELECT c.first_name, c.middle_name, c.last_name, c.home_phone, p.pcn_ticket_id
FROM Customers c LEFT JOIN PCN_Parking_Tickets p
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:

SELECT c.first_name, c.middle_name, c.last_name,
FROM Customers c, Addresses a, PCN_Parking_Tickets p 
WHERE a.address_id = c.address_id AND c.customer_id = p.customer_id
MINUS
SELECT c.first_name, c.middle_name, c.last_name,
FROM Customers c, Addresses a, PCN_Parking_Tickets p 
WHERE a.address_id = c.address_id 
AND c.customer_id = p.customer_id
AND p.date_ticket_issued = NULL
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:

SELECT c.*
FROM Customers c LEFT JOIN PCN_Parking_Tickets p
ON c.customer_id = p.customer_id
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!

[jmendezg]

Lo qe veo esque no usar inner join para algunas cosas que se requieren.

[seloal]

Iniciado por jmendezg

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.

[jmendezg]

Iniciado por seloal

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:

SELECT COUNT (tiket_status_code), tiket_status_code 
FROM PCN_Parking_Tickets a INNER JOIN  Vehicles b
ON a.vehicle_licence_number = b.vehicle_license_number
INNER JOIN  Ref_Vehicle_Manufactures c  ON  b.manufacturer_code = c.manufacturer_code
INNER JOIN  Ref_Vehicle_Types d ON  d.vehicle_type_code  = b.vehicle_type_code
WHERE c.manufacturer_name = 'Citroen'  AND d.vehicle_type_description = 'economica'
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

[seloal]

Iniciado por jmendezg

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.

[jmendezg]

Iniciado por seloal

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:

SELECT a.first_name, a.middle_name, a.last_name
FROM Customers a 
INNER JOIN PCN_Parking_Tickets b  ON a.customer_id = b.customer_id
INNER JOIN Addresses c ON c.address_id = a.address_id
INNER JOIN Ref_PCN_Ticket_Status d  ON d.ticket_status_code = b.tiket_status_code
WHERE c.address_id = 'Cleveland'  AND d.ticket_status_description NOT IN ('Cancelado','Erroneo')
AND b.date_ticket_cancelled IS NOT NULL AND b.date_ticket_issued IS NOT NULL AND b.date_ticket_paid IS NOT NULL
 

[seloal]

Muchas gracias por tu ayuda me esta resultando muy útil.

[seloal]

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:

SELECT c.*
FROM Customers c 
INNER JOIN PCN_Parking_Tickets p ON c.customer_id = p.customer_id
INNER JOIN Ref_PCN_Ticket_Status d ON d.ticket_status_code = b.tiket_status_code
WHERE p.date_ticket_cancelled > '01-JAN-2015' 
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.