Transact-SQL/Prácticas

As prácticas son exercicios cuxos enunciados describen directamente bases de datos e as súas táboas. O obxectivo das prácticas é, seguindo as súas instrucións, crear as bases de datos correspondentes en Transact-SQL.

Práctica 1

1. Queremos crear unha base de datos que resolla información dos empregados da nosa empresa. As características a ter en conta son:
   • O nome da base de datos será EmpregadosEmpresa.
   • O ficheiro de datos principal cumprirá os seguintes requisitos: o nome lóxico será "EmpregadosData", o nome físico será "C:\mssql7\data\EmpregadosDatos.mdf", o tamaño inicial 2 MiB, o tamaño máximo 10 MiB e medrará en intervalos do 25% (creará 0,5 MiB a primeira vez que o necesite).
   • O ficheiro de rexistro de transaccións terá as seguintes características: o nome lóxico será RexistroEmpregados, o nome físico será "C:\mssql7\data\RexistroEmpregados.ldf", o tamaño inicial 1 MiB, o tamaño máximo 5 MiB e o índice de incremento un MiB.
2. Na base de datos anterior crearase a táboa DEPARTAMENTOS, que recollerá a lista dos departamentos nos que traballan os empregados. Os atributos son os seguintes:
   • IdDepaD. Será o campo clave. Na empresa hai sete departamentos, e cada un ten asignado un número do 1 ao 7.
   • NomeD. Nome do departamento. O límite de caracteres será de 20, e o dato non poderá quedar baleiro.
   • ExteD. Cada departamento ten unha extensión telefónica distinta. Constará de 3 caracteres numéricos.
3. Crearase tamén a táboa CATEGORÍAS, que recollerá as categorías profesionais dos traballadores. Os seus campos son:
   • IdCateC. Hai oito categorías profesionais distintas, identificadas cada unha cunha letra do "A" ao "H". Será o campo clave.
   • NomeC. Nome da categoría. O límite de caracteres será de 20. Será obrigatorio asignarlle un valor a este campo.
   • BaseC. Cada categoría profesional ten asignado un soldo base distinto. Este campo recollerá un número enteiro de ata cinco díxitos. Será obrigatorio asignarlle un valor a este campo.
4. Na táboa EMPREGADOS gardaranse os datos persoais dos empregados. Os campos que conterá son:
   • NIFE. Recolle o NIF. É o campo clave da táboa. O tamaño deberá apropiarse ao seu contido.
   • ApeE. Este campo recollerá os apelidos do empregado. Terá un límite de 60 caracteres e será obrigatorio asignarlle un valor.
   • NomeE. Nome do empregado. O límite será de 25 caracteres e será obrigatorio asignarlle un valor.
   • DataNaceE. Data de nacemento do empregado. Será un campo de tipo data (curta).
   • FillosE. Cantidade de fillos do empregado.
   • DireE. Enderezo onde reside o empregado. Terá un límite de 60 caracteres.
   • LocaE. Localidade de residencia do empregado. Terá un límite de 30 caracteres.
   • ProvE. Provincia na que reside o empregado. Terá un límite de 25 caracteres.
   • CodPE. Código postal do empregado. Terá 5 caracteres.
   • TlfE. Teléfono de contacto do empregado. Terá 9 caracteres.
   • DataAltaE. Data na que o empregado comezou traballar na empresa. Será un campo de tipo data (curta).
   • IdDepaE. Identificador que relaciona o empregado co departamento no que traballa. Cada empregado traballa nun único departamento. Debe ter un valor equivalente a un dos da táboa DEPARTAMENTOS (será unha clave externa).
   • IdCateE. Cada empregado ten unha categoría profesional. Do mesmo xeito que o campo anterior, este terá un rexistro relacionado estreitamente coa táboa CATEGORÍAS.

Solución 1.1

CREATE DATABASE EmpregadosEmpresa
ON 
( NAME = 'DatosEmpregados',
  FILENAME = 'c:\mssql7\data\DatosEmpregados.mdf',
  SIZE = 2MB,
  MAXSIZE = 10MB,
  FILEGROWTH = 25% )

LOG ON

( NAME = 'RexistroEmpregados',
  FILENAME = 'c:\mssql7\data\RexistroEmpregados.ldf',
  SIZE = 1MB,
  MAXSIZE = 5MB,
  FILEGROWTH = 1MB )
GO

CREATE TABLE departamentos
(
	IdDepaD TINYINT CONSTRAINT PKIdDepaD PRIMARY KEY,
	NomeD VARCHAR(20) NOT NULL,
	ExteD VARCHAR(3) CONSTRAINT CExteD NOT NULL
)

CREATE TABLE categorias
(
	IdCateC CHAR(1) CONSTRAINT PKIdCateC PRIMARY KEY,
	NomeC VARCHAR(20) NOT NULL,
	BaseC INT CONSTRAINT limcartos CHECK (BaseC BETWEEN 0 AND 99999)
)
CREATE TABLE empregados
(

	NIFE CHAR(9) NOT NULL CONSTRAINT cfEmpregados PRIMARY KEY,
	Ape1E VARCHAR(25) NOT NULL,
	Ape2E VARCHAR(25),
	NomeE VARCHAR(25) NOT NULL,
	DataNaceE SMALLDATETIME,
	FilloE int,
	DireE VARCHAR(50),
	LocaE VARCHAR(30),
	ProvE VARCHAR(25),
	CodPE CHAR(5),
	TlfE CHAR(9),
	DataAltaE SMALLDATETIME,
	IdDepaE TINYINT CONSTRAINT cfIdDepaE FOREIGN KEY REFERENCES departamentos(IdDepaD),
	IdCateE CHAR(1) CONSTRAINT cfIdCatE FOREIGN KEY REFERENCES categorias(idCateC),
 	
)

Práctica 2

Modificación de ficheiros de base de datos e táboas da primeira práctica.

1. Fixe o tamaño máximo do ficheiro de datos en 8 MiB e do ficheiro de rexistro de transaccións en 4 MiB.
2. Na táboa "departamentos" debe variar a restrición dos valores de entrada de IdDepaD para que poida admitir valores entre 1 e 8. Despois comprobe o seu funcionamento introducindo algún dato con valores non válidos.
3. Na táboa "categorias" restrinxa os valores de entrada de BaseC para que só admita valores entre 12000 e 99000 euros con dous díxitos decimais. Despois comprobe o seu funcionamento introducindo algún dato con valores fóra dese rango.
4. Engada na táboa "empregados" un campo que permita rexistrar unha foto do empregado.
5. Elimine a columna creada no apartado anterior.
6. Estableza unha regra de validación para que a data de alta do empregado sexa menor ou igual á data actual do sistema.
7. Estableza outra regra de validación que permita asegurar que os traballadores que entran a traballar na nosa empresa cumprisen os 18 anos de idade.
8. Elimina a restrición creada no apartado anterior.
9. Establece coma valor predeterminado no campo de alta dos traballadores o valor da data do sistema.
10. Na táboa "departamentos" redefine ExteD para que sexa de tipo "autoincremental" partindo de 100 e medrando por centenas.

Solución 2.1

-- Primeiro punto:
ALTER DATABASE EmpregadosEmpresa MODIFY FILE (NAME = DatosEmpregados, MAXSIZE=8MB)
ALTER DATABASE EmpregadosEmpresa MODIFY FILE (NAME = RexistroEmpregados, MAXSIZE=4MB)

-- Segundo punto:
<source lang=sql>
USE EmpregadosEmpresa
  ALTER TABLE departamentos
    DROP CONSTRAINT PKIdDepaD

USE EmpregadosEmpresa
  ALTER TABLE departamentos
    ADD CONSTRAINT CKIdDepaD CHECK (IdDepaD BETWEEN 1 AND 8)

-- Terceiro punto:
USE EmpregadosEmpresa
  ALTER TABLE categorias
    DROP CONSTRAINT PKIdCateC

USE EmpregadosEmpresa
  ALTER TABLE categorias
    ALTER COLUMN BaseC DECIMAL(7,2) NULL

USE EmpregadosEmpresa
  ALTER TABLE categorias
    WITH NOCHECK ADD CONSTRAINT BaseCCE CHECK (BaseC BETWEEN 12000 AND 99000)

-- Cuarto punto:
ALTER TABLE empregados
  ADD COLUMN FotoE IMAGE CONSTRAINT FotoE NULL

-- Quinto punto:
ALTER TABLE empregados
  DROP COLUMN FotoE

-- Sexto punto:
ALTER TABLE empregados
  ADD CONSTRAINT CKDataAltaE CHECK ( DataAltaE <= GETDATE() )

-- Sétimo punto:
ALTER TABLE empregados
  WITH NOCHECK ADD CONSTRAINT CK2DataAltaE CHECK ( DATEDIFF( DAY, DataNaceE, DataAltaE)/365.25 >= 18 )

-- Oitavo punto:
ALTER TABLE empregados
  DROP CONSTRAINT CK2DataAltaE

-- Noveno punto:
ALTER TABLE empregados
  ADD CONSTRAINT DDataAltaE DEFAULT GETDATE() FOR DataAltaE

-- Décimo punto:
ALTER TABLE departamentos
  DROP CONSTRAINT CExteD
ALTER TABLE departamentos
  DROP COLUMN ExteD
ALTER TABLE departamentos
  ADD ExteD SMALLINT IDENTITY(100,100)

Práctica 3

Sobre a base de datos "EmpregadosEmpresa", aplique as instrucións que permitan:

1. Crear unha nova táboa, "HorasExtra", que recolla as horas extraordinarias realizadas polos traballadores. Terá os seguintes campos:
   • NIFH. NIF do traballador. Teña en conta o punto 3, pero sen realizar a restrición.
   • DataH. data na que fixo horas extraordinarias (no formato de data curta). Se non se introduce, o seu valor será a data do sistema.
   • NumeH. Número de horas extraordinarias dunha data. Non admitirá un valor inferior a 30 minutos nin un maior de 8 horas.
2. Un traballador só pode ter un rexistro de horas extraordinarias por cada data na que as traballe. Definir con ALTER a clave primaria acorde con esta restrición.
3. O campo NIFH é o que permite relacionar esta táboa coa de "Empregados". Pode ter duplicados, pois un traballador estará rexistrado tantas veces coma datas nas que faga horas extraordinarias.
4. A empresa estuda contratar prazas nun aparcadoiro próximo e necesita saber que empregados estarían interesados no seu uso. Engadir un campo á táboa ?cun máximo de 8 díxitos? que permita rexistrar para cada traballador a matrícula do coche que aparcaría. O valor predeterminado será "N".
5. Cambiar a definición da columna "BaseC" da táboa "categorías" para que recolla o mesmo rango de valores pero de tipo monetario.
6. Desactiva a restrición da columna BaseC e comproba con novos datos (fóra de rango) que non está activada.
7. Volve activar a restrición anterior e comproba con novos datos que se fai efectiva.

Solución 3.1

-- Primeiro punto:
CREATE TABLE horasextra (
  NIFH CHAR(9) NOT NULL CONSTRAINT NIFHCK CHECK (NIFH LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]'),
  DataH CHAR(8) NOT NULL CONSTRAINT DataHD DEFAULT CONVERT (CHAR(8),getdate(),3),
  NumeH DECIMAL (4,2) CONSTRAINT NumeHCK CHECK (NumeH >= 0.5 AND NumeH <= 8)),
)

-- Segundo punto:
ALTER TABLE horasextra ADD CONSTRAINT HorasExtraCP PRIMARY KEY (NIFH,DataH)

-- Terceiro punto:
ALTER TABLE horasextra ADD CONSTRAINT NIFHCE FOREIGN KEY(NIFH) REFERENCES empregados(NIFE)

-- Cuarto punto:
ALTER TABLE empregados ADD aparcadoiro VARCHAR(9) NOT NULL
  CONSTRAINT apacadoiroD DEFAULT 'N'
  CONSTRAINT aparcadoiroCK CHECK
  (
    aparcadoiro LIKE 'N'
 OR aparcadoiro LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9]'
 OR aparcadoiro LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][A-Z]'
 OR aparcadoiro LIKE '[A-Z][0-9][0-9][0-9][0-9][0-9][A-Z][A-Z]'
 OR aparcadoiro LIKE '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9]'
 OR aparcadoiro LIKE '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][A-Z]'
 OR aparcadoiro LIKE '[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][A-Z][A-Z]'
 OR aparcadoiro LIKE '[0-9][0-9][0-9][0-9][A-Z][A-Z][A-Z]'
  )

-- Quinto punto:
ALTER TABLE categorias DROP CONSTRAINT BaseCCE
ALTER TABLE categorias ALTER COLUMN BaseC SMALLMONEY NOT NULL
ALTER TABLE categorias WITH NOCHECK ADD CONSTRAINT BaseCCE CHECK (BaseC BETWEEN 12000 AND 99000)

-- Sexto punto:
ALTER TABLE categorias NOCHECK CONSTRAINT BaseCCE

-- Sétimo punto:
ALTER TABLE categorias CHECK CONSTRAINT BaseCCE

Práctica 4

Deseñe en Transact-SQL unha base de datos correspondente ao "camiño de Santiago":

• O nome da base de datos será "Santiago".
• O ficheiro de datos principal terá as seguintes características:
  • O nome lóxico será "SantiagoBD".
  • O nome físico será "C:\MSSQL7\Data\SantiagoBD.ldf".
  • O tamaño inicial será de 2 MiB.
  • O tamaño máximo será de 10 MiB.
  • Medrará en intervalos do 10%.
• O ficheiro de rexistro das transaccións terá as seguintes características:
  • O nome lóxico será "SantiagoRT".
  • O nome físico será "C:\MSSQL7\Data\SantiagoRT.ldf".
  • O tamaño inicial será de 1 MiB.
  • O tamaño máximo será de 4 MiB.
  • Medrará en intervalos de 1 MiB.

As táboas e campos relacionados son os seguintes:

albergues

CodigoA. Tres caracteres numéricos. Clave primaria.

DescricionA. Descrición do albergue non superior a unha páxina (uns 6000 caracteres).

peregrinos

NIFP. Nove caracteres alfanuméricos. Clave primaria.

NomeP. 25 caracteres alfabéticos.

ApelidosP. 66 caracteres alfabéticos.

PaisP. 20 caracteres albabeticos.

empregados

NIFE. 9 caracteres alfanuméricos. Clave primaria.

NomeE. 25 caracteres alfabéticos.

ApelidosE. 66 caracteres alfabéticos.

TlfE. 9 caracteres numéricos.

CodigoE. 3 caracteres numéricos. Clave externa de "albergues".

camas

CodigoC. 3 caracteres numéricos. Clave primaria e tamén clave externa de "albergues".

NumeroC. 3 caracteres numéricos. Clave primaria.

AltoC. 3 caracteres numéricos (centímetros).

AnchoC. 3 caracteres numéricos (centímetros).

estancia

CodigoAP. 3 caracteres numéricos. Clave primaria, e tamén clave externa de "albergues".

NIFAP. 9 caracteres alfanuméricos. Clave primaria, e tamén clave externa de "peregrinos".

DataAP. Data de estancia no albergue. Clave primaria. Formato de data curta.

NumeroHorasAP. Número de horas de estancia. 2 caracteres numéricos.

durmir

CodigoCP. 3 caracteres numéricos. Calve primaria, e tamén clave externa de "camas".

NumeroCP. 3 caracteres numéricos. Clave primaria, e tamén clave externa de "camas".

NIFCP. 9 caracteres alfanuméricos. Clave primaria, e tamén clave externa de "peregrinos".

DataCP. Data de estancia no albergue. Clave primaria. 2 caracteres numéricos.

atencion

NIFEEP. 9 caracteres numéricos. Clave primaria, e tamén clave externa de "empregados".

NIFPEP. 9 caracteres numéricos. Clave primaria, e tamén clave externa de "peregrinos".

DataEP. Data de estancia no albergue. Clave primaria. Formato de data curta.

Solución 4.1

USE master
GO
CREATE DATABASE Santiago
ON
(
  NAME = Santiago,
  FILENAME = 'C:\MSSQL7\Data\DatosSantiago.mdf',
  SIZE = 2MB,
  MAXSIZE = 10MB,
  FILEGROWTH = 10% )
LOG ON
(
  NAME = 'RexistroSantiago',
  FILENAME = 'C:\MSSQL7\Data\RexistroSantiago.ldf',
  SIZE = 1MB,
  MAXSIZE = 4MB,
  FILEGROWTH = 1MB)
GO

CREATE TABLE albergues
(
  CodigoA SMALLINT NOT NULL CONSTRAINT CodigoACP PRIMARY KEY CHECK (CodigoA LIKE ('[0-9][0-9][0-9]')),
  Descricion VARCHAR(6000),
)

CREATE TABLE peregrinos
(
  NIFP CHAR(9) NOT NULL CONSTRAINT NIFPCP PRIMARY KEY CHECK (NIFP LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]')),
  NomeP	VARCHAR(25),
  ApelidosP VARCHAR(66),
  PaisP VARCHAR(20),
)

CREATE TABLE empregados
(
  NIFE CHAR(9) NOT NULL CONSTRAINT NIFECP PRIMARY KEY CHECK (NIFE LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][A-Z]')),
  NomeE	VARCHAR(25),
  ApelidosE VARCHAR(66),
  TlfE VARCHAR(9) CHECK (TlfE LIKE ('[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')),
  CodigoE SMALLINT CONSTRAINT CodigoECE FOREIGN KEY(CodigoE) REFERENCES albergues(CodigoA),
)

CREATE TABLE camas
(
  CodigoC SMALLINT NOT NULL CONSTRAINT CodigoCCE FOREIGN KEY(CodigoC) REFERENCES albergues(CodigoA),
  NumeroC SMALLINT NOT NULL,
  AltoC SMALLINT,
  AnchoC SMALLINT,
  CONSTRAINT ClaveC PRIMARY KEY(CodigoC,NumeroC),
)

CREATE TABLE estancia
(
  CodigoAP SMALLINT NOT NULL CONSTRAINT CodigoAPCE FOREIGN KEY(CodigoAP) REFERENCES albergues(CodigoA),
  NIFAP CHAR(9) NOT NULL CONSTRAINT NIFAPCE FOREIGN KEY(NIFAP) REFERENCES peregrinos(NIFP),
  DataAP SMALLDATETIME NOT NULL,
  HorasAP SMALLINT,
  CONSTRAINT ClaveAP PRIMARY KEY(CodigoAP,NIFAP,DataAP),
)

CREATE TABLE durmir
(
  CodigoCP SMALLINT NOT NULL,
  NumeroCP SMALLINT NOT NULL,
  NIFCP CHAR(9) NOT NULL CONSTRAINT NIFCPCE FOREIGN KEY(NIFCP) REFERENCES peregrinos(NIFP),
  DataCP SMALLDATETIME NOT NULL,
  CONSTRAINT ClaveCCPCE FOREIGN KEY(CodigoCP,NumeroCP) REFERENCES camas(CodigoC,NumeroC),
  CONSTRAINT ClaveCP PRIMARY KEY(CodigoCP,NumeroCP,NIFCP,DataCP),
)

CREATE TABLE atencion
(
  NIFEEP CHAR(9) NOT NULL CONSTRAINT NIFEEPCE FOREIGN KEY(NIFEEP) REFERENCES empregados(NIFE),
  NIFPEP CHAR(9) NOT NULL CONSTRAINT NIFPEPCE FOREIGN KEY(NIFPEP) REFERENCES peregrinos(NIFP),
  DataEP SMALLDATETIME NOT NULL,
  CONSTRAINT ClaveEP PRIMARY KEY(NIFEEP,NIFPEP,DataEP),
)

Práctica 5

Sobre a base de datos EmpregadosEmpresa que se creou previamente:

1. Establecer "Pontevedra" coma o valor predeterminado de "ProvE".
2. Renomear os departamentos 1, 2 e 3 como "Administración", "Produción" e "Comercial" respectivamente.
3. Incrementar o soldo base nun 3'5% para as categorias do A ao D.
4. Cambiar o teléfono dun Empregado creado previamente por "987654321".
5. Engadir á táboa de horas extraordinarias unha fila para un dos empregados existentes na táboa de empregados, sendo a data o 5 de outubro do 2010. O campo das horas extraordinarias traballadas non se encherá de momento.
6. Actualizar a táboa de horas extraordinarias, poñendo a dúas as horas traballadas por todos os empregados que fixeron horas extraordinarias o 5 de outubro do 2010.
7. Actualizar as horas extraordinarias feitas o 5 de outubro do 2010 aumentándoas nunha hora (o reloxo estaba mal).
8. Borrar todas as filas da táboa de horas extraordinarias correspondentes ao 5 de outubro de 2010.
9. Eliminar todas as filas da táboa de horas extraordinarias.

Solución 5.1

-- Primeiro punto
ALTER TABLE empregados ADD CONSTRAINT ProvEVP COLUMN ProvE DEFAULT 'Pontevedra'

-- Segundo punto
UPDATE departamentos SET departamentos.NomeD = 'Administración' WHERE IdDepaD = 1
UPDATE departamentos SET departamentos.NomeD = 'Produción' WHERE IdDepaD = 2
UPDATE departamentos SET departamentos.NomeD = 'Comercial' WHERE IdDepaD = 3

-- Terceiro punto
UPDATE categorias SET categorias.BaseC = BaseC * 0.035 WHERE IdCateC LIKE '[A-D]'

-- Cuarto punto
UPDATE empregados SET empregados.TlfE = '987654321' WHERE NIFE = '123456789A'

-- Quinto punto
INSERT INTO horasextra VALUES ('123456789A', '05-10-2010', NULL)

-- Sexto punto
UPDATE horasextra SET horasextra.NumeH = 2 WHERE datediff('dd-mm-yyyy', DataH, '05-10-2010')

-- Sétimo punto
UPDATE horasextra SET horasextra.NumeH = ( NumeH + 1 ) WHERE datediff('dd-mm-yyyy', DataH, '05-10-2010')

-- Oitavo punto
DELETE FROM horasextra WHERE datediff('dd-mm-yyyy', DataH, '05-10-2010')

-- Noveno punto
TRUNCATE horasextra