SQL - Ayuda con practica sql

Ayuda con practica sql

Publicado por Sergio (5 intervenciones) el 14/04/2015 12:00:48

Hola.

Veréis hace un curso suspendí un trabajo de sql y ahora tengo que hacer otro, mi problema es que no me acuerdo de casi nada y estoy hasta arriba de faena con exámenes finales y un proyecto final, cualquier ayuda, guía o consejo sera bien recibido,

Aquí el ejercicio:

1. Las instrucciones necesarias para programar con SQL(DDL), las siguientes relaciones.

Captura_de_2015_04_13_19_43_07

El tamaño i tipo de los campos lo decidís vosotros de manera pertinente

2. En segundo lugar se pide programar las siguientes consultas en SQL(DML):

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.

Valora esta pregunta

Me gusta: Está pregunta es útil y esta clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

Pos: 2

Val: 806

Ayuda con practica sql

Publicado por leonardo_josue (1173 intervenciones) el 14/04/2015 22:15:47

Tareas no hacemos... dinos qué dudas puntuales tienes y qué es lo que intentaste hacer y con gusto te ayudamos a completar lo que falta.

saludos
Leo.

Valora esta respuesta

Me gusta: Está respuesta es útil y esta clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Ayuda con practica sql

Publicado por Sergio (5 intervenciones) el 15/04/2015 10:12:27

Muchas gracias por responder!

Nada mas faltaría, a ver no negare que me facilitaría muchas cosas, pero la intención era recibir como ya digo la ayuda que sea o incluso enlaces a guías que me sean útiles para realizar estos ejercicios en concreto.

Para empezar me podríais revisar que tal esta el A porque voy dando palos de ciego entre que es del curso pasado y que no es una asignatura principal de mi perfil.

Select Count (Ticket_status_code), Ticket_status_code
from PCN_Parking_Tickets
where vehicle_licence number = (Select vehicle_licence_number
from vehicles
where manufacturer_code = (Select manufacturer_code
from Ref_vehicle_Manufacturers
where manufacturer_name="citroen"))
and where vehicle_licence_number = (Select vehicle_licence_number
from vehicles
where vehicle_type_code = (select vehiclet_type_code
from ref_vehicle_tipes
where vehicle_type_description="economica"));

Muchas gracias por la atención.

Valora esta respuesta

Comentar

Pos: 2

Val: 806

Ayuda con practica sql

Publicado por leonardo_josue (1173 intervenciones) el 15/04/2015 18:21:33

Hola de nuevo Sergio:

La consulta puede que esté bien, sin embargo, te recomiendo tratar de minimizar el uso de Subconsultas en el WHERE, ya que estas tienen un pésimo rendimiento. En lugar de eso puedes hacer uso de JOIN's: checa este ejemplo:

Supongamos que tienes estas dos tablas;

mysql> SELECT * FROM marcas;

+----------+--------+

| id_marca | marca  |

+----------+--------+

|        1 | Dodge  |

|        2 | Nissan |

|        3 | Ford   |

+----------+--------+

3 rows in set (0.00 sec)

mysql> SELECT * FROM modelos;

+-----------+----------+---------+

| id_modelo | id_marca | modelo  |

+-----------+----------+---------+

|         1 |        1 | Charger |

|         2 |        1 | Stratus |

|         3 |        2 | Tsuru   |

|         4 |        2 | Platina |

|         5 |        2 | Xtrail  |

|         6 |        3 | Focus   |

+-----------+----------+---------+

6 rows in set (0.00 sec)

Ahora, supongamos que quieres extraer todos los modelos de la marca NISSAN, tal como lo haces tú, podrías hacerlo con una subconsulta, más o menos así:

mysql> SELECT * FROM modelos

    -> WHERE id_marca = ( SELECT id_marca

    ->                    FROM marcas

    ->                    WHERE marca = 'Nissan');

+-----------+----------+---------+

| id_modelo | id_marca | modelo  |

+-----------+----------+---------+

|         3 |        2 | Tsuru   |

|         4 |        2 | Platina |

|         5 |        2 | Xtrail  |

+-----------+----------+---------+

3 rows in set (0.00 sec)

Sin embargo, esta es la manera menos óptima para hacerlo. Podrías hacer lo mismo haciendo uso de JOIN's, así:

mysql> SELECT modelos.*

    -> FROM modelos

    -> INNER JOIN marcas ON marcas.id_marca = modelos.id_marca AND

    ->                      marcas.marca = 'Nissan';

+-----------+----------+---------+

| id_modelo | id_marca | modelo  |

+-----------+----------+---------+

|         3 |        2 | Tsuru   |

|         4 |        2 | Platina |

|         5 |        2 | Xtrail  |

+-----------+----------+---------+

3 rows in set (0.00 sec)

Observa que el resultado es exactamente el mismo, pero la consulta está optimizada. En la consulta que tienes por ejemplo, haces dos subconsultas a la tabla VEHICLES, lo cual es completamente inaceptable. Además, tienes sobconsultas anidadas, es decir, una subconsulta dentro de otra subconsulta, esto también es terrible en cuanto al performance. Haz la prueba cambiando tus consultas, si continuas con problemas, postea algunos datos de ejemplo de cada una de tus tablas y dinos qué es lo que esperas obtener como salida, de esta manera será más factible que podamos ayudarte.

Saludos
Leo.

Valora esta respuesta

Comentar

Ayuda con practica sql

Publicado por Sergio (5 intervenciones) el 16/04/2015 15:47:42

Muchas gracias por el comentario! es una respuesta muy útil.

Otra cosa que no me iría mal es saber si los siguientes comandos para crear todas las tablas son correctos o si deberían seguir otro orden, también cualquier sugerencia de añadidos.

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),
);

create table Addresses
(
address_id Char(6),
line_1_number_building Char(6),
line_2_number_street Char(6),
line_3_number_area_locality_suburb 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) unique,
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)
);

create table Customers
(
customer_id Char(6) unique,
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)
);

create table Vehicles
(
vehicle_license_number Char(6) unique,
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, vehicle_type_code)
);

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, pcn_ticket_id)
);

create table PCN_Parking_Tickets
(
pcn_ticket_id Char(6) unique,
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, issued_by_officer_badge_number, tiket_status_code, tiket_type_code, vehicle_licence_number)
);

Valora esta respuesta

Comentar

Pos: 2

Val: 806

Ayuda con practica sql

Publicado por leonardo_josue (1173 intervenciones) el 16/04/2015 17:01:48

Hola de nuevo Sergio:

No sé qué motor de BD's estás utilizando, pero por la sintaxis que utilizas podría suponer que se trata de ORACLE. Veo un problema con las sentencias CREATE que estás colocando, en la parte reference a las FOREIGN KEY, ya que en ningún momento estás especificando hacia qué tabla está relacionada la tabla... checa esta liga:

http://www.w3schools.com/sql/sql_foreignkey.asp

Observa que en todos los casos se debe especificar el campo que es llave foranea y la tabla hacia la que apunta dicho campo (REFERENCES)... con respecto al orden de ejecución debes de tener cuidado de poner primero aquellas tablas QUE NO TIENEN FK definidas y después aquellas que si la tienen.

Saludos
Leo.

Valora esta respuesta

Comentar

Ayuda con practica sql

Publicado por Sergio (1 intervención) el 23/04/2015 18:41:03

Una vez mas muchas gracias por la ayuda, creo que los create table están colocados en el orden correcto para que las claves foráneas no den problemas.

Me estoy encontrando con dificultades con el resto de preguntas (B, C, D y E)
No se por donde empezar con ninguna de ellas, alguna pista?

Valora esta respuesta

Comentar

Ayuda con practica sql

Publicado por Sergio (5 intervenciones) el 13/05/2015 19:25:07

Bueno pues ahora si que tengo todos los create correctamente realizados son los siguientes.

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

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)

);

Ahora sigo con las consultas que me tienen totalmente despistado. Si alguien se le ocurren pistas de como plantearlas que no dude en comentarme, me será de gran ayuda.
P.D. si estoy trabajando en ORACLE se me olvido comentarlo.

Valora esta respuesta

Comentar

Ayuda con practica sql

Publicado por Sergio (5 intervenciones) el 15/05/2015 18:10:27

Esto es lo que he conseguido hacer, de momento

A.(No me sale con join)
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)
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)
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)

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';

Valora esta respuesta

Comentar

problemas con la select

Mostrar reporte con última fecha de varias tablas