Consultas SQL de ORACLE express

[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!