Bases de datos. Consultas SQL (III)

Son los ejercicios de la hoja seis de la unidad 3, que corresponden a la creación y modificación de tablas.

EJERCICIOS: Creación y manipulación de tablas

Definición de las tablas

1. Creación de las tablas con las restricciones.

a. Crear la tabla COMPRADORES con la columna de columna cif_comprador como clave primaria con nombre PK_COMPRADORES_CIF, y la columna nombre_social única con nombre UQ_COMPRADORES_NOMBRE_SOCIAL. La columna telefono debe ser obligatoria.

create table compradores (
     CIF_comprador varchar(11),
     Nombre_Social varchar(30),
     Domicilio_Social varchar(30),
     Localidad varchar(30),
     C_Postal varchar(5),
     Telefono varchar(9) NOT NULL,

     CONSTRAINT PK_COMPRADORES_CIF PRIMARY KEY (CIF_comprador),
     CONSTRAINT UQ_COMPRADORES_NOMBRE_SOCIAL UNIQUE (Nombre_Social)
);

b. Crear la tabla ARTICULOS, con referencia_articulo como PRIMARY KEY con el nombre PK_ARTICULOS, la columna IVA con valores entre 5 y 25 inclusive y la columna existencias_actuales con valor por defecto 0.

create table articulos (
     Referencia_Articulo varchar(12),
     Descripcion_articulo varchar(30),
     Precio_Unidad numeric (6,2),
     IVA numeric (2) check (IVA BETWEEN 5 AND 25),
     Existencias_Actuales numeric(5) default 0,
     CONSTRAINT PK_ARTICULOS PRIMARY KEY (Referencia_Articulo)
);

c. Crear la tabla FACTURAS con la columna factura_no como clave primaria con el nombre PK_FACTURAS, y la columna fecha_factura tendrá como valor por defecto la fecha 1 de enero de 2005.

create table facturas (
     Factura_no numeric(6),
     Fecha_factura date default '2005-01-01',
     CIF_Cliente varchar(11),
     CONSTRAINT PK_FACTURAS PRIMARY KEY (Factura_no)
);

d. Crear la tabla LINEAS_FACTURAS con las columnas factura_no y referencia_articulo como PRIMARY KEY con nombre PK_LINEAS_FACTURA, la columna factura_no como FOREIGN KEY con nombre FK_LINEAS_FACTURAS referenciando la columna factura_no de la tabla facturas con borrado en cascada y la columna referencia_articulo como FOREIGN KEY con nombre FK_LINEAS_ARTICULOS referenciando la columna referencia_articulo de la tabla articulos

create table lineas_facturas (
     factura_no numeric(6),
     Referencia_articulo varchar(12),
     Unidades numeric(3),
     CONSTRAINT PK_LINEAS_FACTURA primary key (Referencia_articulo,factura_no),
     CONSTRAINT FK_LINEAS_FACTURAS foreign key (factura_no) REFERENCES facturas(factura_no)
     ON DELETE CASCADE,
     CONSTRAINT FK_LINEAS_ARTICULOS foreign key (Referencia_articulo) REFERENCES articulos(referencia_articulo)
);

2. Añadir a la tabla FACTURAS la columna cod_oficina de tipo numérico de 4 posiciones, con el número de oficina

alter table facturas add (cod_oficina numeric(4));

3. Añadir en la tabla FACTURAS la columna cif_cliente como FORIEGN KEY con nombre 

FK_FACTURA_COMPRADORES referenciando a la columna cif_comprador de la tabla compradores

alter table facturas add CONSTRAINT FK_FACTURA_COMPRADORES FOREIGN KEY (CIF_Cliente) REFERENCES clientes(CIF_Cliente);

4. Cambiar en la tabla COMPRADORES el nombre de la columna c_postal por texto_codigo_postal

ALTER TABLE compradores change c_postal texto_codigo_postal varchar(5);

5. Añadir a la columna cod_oficina de la tabla FACTURAS la comprobación de estar entre 1 y 1000.

alter table facturas

add constraint cod_oficina check (cod_oficina between 1 and 1000);

Definición de las tablas

1. Creación de tablas sin restricciones.

           a. Crear una tabla COMPRADORES_1 sin ninguna restricción.
           b. Crear una tabla ARTÍCULOS_1 sin ninguna restricción.
           c. Crear una tabla FACTURAS_1 sin ninguna restricción.
           d. Crear la tabla LINEAS_FACTURA_1 sin ninguna restricción

drop table if exists compradores_1;

drop table if exists articulos_1;

drop table if exists facturas_1;

drop table if exists lineas_facturas_1;

create table compradores_1(

     CIF_comprador varchar(11),

     Nombre_social varchar(30),

     Domicilio_social varchar(30),

     Localidad varchar(30),

     C_postal varchar(5),

     Teléfono varchar(9)

);

create table articulos_1(

     Referencia_articulo varchar (12),

     Descripcion_articulo varchar(30),

     Precio_unidad decimal(6,2),

     IVA numeric(2),

     Existencias_actuales numeric(5)

);

create table facturas_1(

     Factura_no numeric(6),

     Fecha_factura date,

     CIF_cliente varchar(11)

);

create table lineas_factura_1(

     Factura_no numeric(6),

     Referencia_articulo varchar(12),

     Unidades numeric(3)

)

2. Creación de tablas con restricciones a nivel de columna

           a. Crear la tabla COMPRADORES_2 con la columna cif_comprador como clave primaria, la columna telefono obligatoria y la columna nombre_social única
           b. Crear la tabla ARTICULOS_2, con referencia_articulo como clave primaria, la columna IVA con valores entre 5 y 25 inclusive y la columna existencias_atuales con valor por defecto 0.
           c. Crear una tabla FACTURAS_2 con la columna factura_no como clave primaria y la columna cif_cliente como clave ajena referenciando a la columna cif_comprador de la tabla compradores_2.

drop table if exists compradores_2;
drop table if exists articulos_2;
drop table if exists facturas_2;

create table compradores_2(
     CIF_comprador varchar(11) PRIMARY KEY,
     Nombre_social varchar(30) UNIQUE KEY,
     Domicilio_social varchar(30),
     Localidad varchar(30),
     C_postal varchar(5),
     Teléfono varchar(9) not null
);

create table articulos_2(
     Referencia_articulo varchar (12) PRIMARY KEY,
     Descripcion_articulo varchar(30),
     Precio_unidad decimal(6,2),
     IVA numeric(2) check (IVA between 5 and 25),
     Existencias_actuales numeric(5) default 0
);

create table facturas_2(
     Factura_no numeric(6) PRIMARY KEY,
     Fecha_factura date,
     CIF_cliente varchar(11) REFERENCES compradores2(CIF_comprador)
);

3. Creación de tablas con restricciones a nivel de tabla y con nombre de constaint

           a. Crear la tabla COMPRADORES_3 con la columna de columna cif_comprador como clave primaria con nombre PK_COMPRADORES_3, y la columna nombre_social con la restricción de ser única, con nombre UQ_COMPRADORES_NOMBRE_SOCIAL_3.
           b. Crear la tabla FACTURAS_3 con la columna factura_no como clve primariacon el nombre PK_FACTURAS_3, y la columna cif_cliente como clave ajena con nombre FK_FACTURA_COMPRADORES_3 referenciando a la columna cif_comprador de la tabla COMPRADORES_3.

drop table if exists compradores_3;
drop table if exists facturas_3;

create table compradores_3(
     CIF_comprador varchar(11),
     Nombre_social varchar(30),
     Domicilio_social varchar(30),
     Localidad varchar(30),
     C_postal varchar(5),
     Teléfono varchar(9),
     CONSTRAINT PK_COMPRADORES_3 PRIMARY KEY (CIF_comprador),
     CONSTRAINT UQ_COMPRADORES_NOMBRE_SOCIAL_3 UNIQUE (Nombre_social)
);

create table facturas_3(
     Factura_no numeric(6),
     Fecha_factura date,
     CIF_cliente varchar(11),
     CONSTRAINT PK_FACTURA_3 PRIMARY KEY (Factura_no),
     CONSTRAINT FK_FACTURA_3 FOREIGN KEY (CIF_cliente) REFERENCES compradores_3(CIF_comprador)
);

4. Añadir en la tabla FACTURAS_1 la restricción de clave primaria con nombre pk_factura_no_1 para la columna factura_no

alter table facturas_1

add constraint pk_factura_no_1 primary key (factura_no);

5. Añadir en la tabla LÍNEAS_FACTURAS_1 la restricción de nombre fk_lineas_factura-1 como clave ajena para la columna factura_no referenciando a la columna factura_no de la tabla facturas_1

alter table lineas_factura_1

add constraint fk_lineas_facturas_1 foreign key(factura_no) references facturas1(factura_no);

6. Añadir en la tabla ARTICULOS_1 la restricción de nombre uq_articulos_descripcion_1 para poner la restricción de ser única a la columna descripcion_articulo.

alter table articulos_1

add constraint uq_articulos_descripcion_1 unique (descripcion_articulo);

7. Cambiar en la tabla COMPRADORES_1 la definición del tipo de la columna teléfono por numérico entero.

alter table compradores_1

modify Teléfono int;