Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
as
DROP TABLE IF EXISTS ENVIO_NACIONAL; DROP TABLE IF EXISTS NACIONAL; DROP TABLE IF EXISTS INTERNACIONAL; DROP TABLE IF EXISTS PAQUETE; DROP TABLE IF EXISTS CONDUCTOR_CAMION; DROP TABLE IF EXISTS CONDUCTOR; DROP TABLE IF EXISTS CAMION; DROP TABLE IF EXISTS RUTA; DROP TABLE IF EXISTS CIUDAD; DROP TABLE IF EXISTS AEROLINEA; CREATE TABLE AEROLINEA( id_Aerolinea integer, Nombre_aerolinea varchar(50) NOT NULL, CONSTRAINT id_Aerolinea_PK PRIMARY KEY (id_Aerolinea) ); CREATE TABLE CIUDAD( id_Ciudad integer, Nombre_Ciudad varchar(50) NOT NULL, CONSTRAINT id_Ciudad_PK PRIMARY KEY (id_Ciudad) ); CREATE TABLE RUTA( id_Ruta integer, Nombre_Ruta varchar(50) NOT NULL, CONSTRAINT id_Ruta_PK PRIMARY KEY (id_Ruta) ); CREATE TABLE CAMION ( Placa varchar(8) NOT NULL, Ciudad_Resguardo varchar(50) NOT NULL, Carga_Maxima float NOT NULL, Carga_Minima float NOT NULL, CONSTRAINT Placa_PK PRIMARY KEY (Placa) ); CREATE TABLE CONDUCTOR ( id_Conductor integer, RFC varchar(15) NOT NULL, id_Ruta integer NOT NULL, Direccion varchar(50) NOT NULL, Nombre_Conductor varchar(50) NOT NULL, CONSTRAINT idConductor_PK PRIMARY KEY (id_Conductor) ); CREATE TABLE CONDUCTOR_CAMION ( Placa varchar(8) NOT NULL, id_Conductor integer NOT NULL, Fecha DATE NOT NULL, CONSTRAINT UK_Placa_idConductor_Fecha UNIQUE(Placa,id_Conductor,Fecha) ); CREATE TABLE PAQUETE ( id_Paquete integer, Direccion varchar(100) NOT NULL, Peso_kg float NOT NULL, Destinatorio varchar(50) NOT NULL, Tipo_paquete varchar(1) NOT NULL, CONSTRAINT CHK_Tipo_paquete CHECK (Tipo_paquete LIKE ('I') OR Tipo_paquete LIKE ('N')), CONSTRAINT id_Paquete_PK PRIMARY KEY (id_Paquete) ); CREATE TABLE INTERNACIONAL ( id_Paquete_Internacional varchar(5) NOT NULL, id_Paquete integer NOT NULL, id_Ciudad integer NOT NULL, id_Aerolinea integer NOT NULL, Fecha_Entrega DATE NOT NULL, CONSTRAINT CHK_id_Paquete_Internacional CHECK (id_Paquete_Internacional LIKE '_____'), CONSTRAINT id_Paquete_Internacional_PK PRIMARY KEY (id_Paquete_Internacional) ); CREATE TABLE NACIONAL ( id_Paquete_Nacional varchar(5) NOT NULL, id_Paquete integer NOT NULL, id_Conductor integer, RFC varchar(15), id_Ruta integer, Ciudad_Destino varchar(50) NOT NULL, CONSTRAINT CHK_id_Paquete_Nacional CHECK (id_Paquete_Nacional LIKE '_____'), CONSTRAINT id_Paquete_Nacional_PK PRIMARY KEY (id_Paquete_Nacional) ); CREATE TABLE ENVIO_NACIONAL ( id_Paquete_Nacional varchar(5) NOT NULL, Ciudad_Destino varchar(50) NOT NULL, Num_Paquetes integer NULL DEFAULT 0, CONSTRAINT id_Paquete_E_PK PRIMARY KEY (id_Paquete_Nacional) ); -- Llaves Foraneas para la tabla Conductor Camión ALTER TABLE CONDUCTOR_CAMION ADD CONSTRAINT Placa_FK FOREIGN KEY (Placa) REFERENCES CAMION ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE CONDUCTOR_CAMION ADD CONSTRAINT idConductor_C_FK FOREIGN KEY (id_Conductor) REFERENCES CONDUCTOR ON UPDATE CASCADE ON DELETE CASCADE; -- Llaves Forenaeas para la tabla Internacional ALTER TABLE INTERNACIONAL ADD CONSTRAINT id_Paquete_I_FK FOREIGN KEY (id_Paquete) REFERENCES PAQUETE ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE INTERNACIONAL ADD CONSTRAINT id_Ciudad_FK FOREIGN KEY (id_Ciudad) REFERENCES CIUDAD ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE INTERNACIONAL ADD CONSTRAINT id_Aerolinea_FK FOREIGN KEY (id_Aerolinea) REFERENCES AEROLINEA ON UPDATE CASCADE ON DELETE CASCADE; -- Llaves Foreneas para la tabla Nacional ALTER TABLE NACIONAL ADD CONSTRAINT id_Paquete_N_FK FOREIGN KEY (id_Paquete) REFERENCES PAQUETE ON UPDATE CASCADE ON DELETE CASCADE; ALTER TABLE NACIONAL ADD CONSTRAINT id_Conductor_N_FK FOREIGN KEY (id_Conductor) REFERENCES CONDUCTOR ON UPDATE CASCADE ON DELETE CASCADE; -- Llaves Foreneas para envío Nacional /*ALTER TABLE ENVIO_NACIONAL ADD CONSTRAINT id_Paquete_Nacional_E_FK FOREIGN KEY (id_Paquete_Nacional) REFERENCES NACIONAL ON UPDATE CASCADE ON DELETE CASCADE;*/ -- Llaves Foraneas para la tabla Conductor ALTER TABLE CONDUCTOR ADD CONSTRAINT id_Ruta_C_FK FOREIGN KEY (id_Ruta) REFERENCES RUTA; SELECT * FROM CONDUCTOR_CAMION
run
|
edit
|
history
|
help
0
Descriptor test
1225. Report Contiguous Dates
Laidata Practice
select distinct
post
SQL Lab
prueb final
Ankit
test_tags_count
test 16721723