PRACTICA ORACLE SQL

Vista previa del material en texto

PRACTICA DE ORACLE Y SQL
1.-Se va crear la base de datos Universidad, con sus diferentes tablas relacionadas, se llenara las tablas, y ademas de creara un nuevo usuario llamado "UsrUniversidad" con rol de administrador y password 123,
Nota.- RECUERDA el usuario UsrUniversidad NO DEBE EXISTIR, tampoco la Base de Datos Universidad, si existe la base de datos borralo con drop database Universidad si exisitiera el usuario borralo con sp_droplogin 'UsrUniversidad'
-- Creamos la Base de Datos
CREATE DATABASE Universidad
ON
PRIMARY
(
 NAME = Universidad_data,
	 FILENAME = 'D:\SQLTaller\Universidad_data.mdf',
	 SIZE = 20MB,
	 MAXSIZE = UNLIMITED,
	 FILEGROWTH = 20%
)
LOG ON
(
	 NAME = Luniversidad,
	 FILENAME = 'D:\SQLTaller\Luniversidad.ldf',
	 SIZE = 5MB,
	 MAXSIZE = UNLIMITED,
	 FILEGROWTH = 20MB
)
GO
-- Creamos un usuario y le damos rol de administrador
use Universidad
go
exec sp_addlogin 'UsrUniversidad', '123'
go
exec sp_adduser 'UsrUniversidad'
go
exec sp_addrolemember 'db_owner', 'UsrUniversidad'
GO
-- Creamos las tablas de la Base de Datos
create table Persona
(
 persDNI varchar(8) PRIMARY KEY,
 persNombre varchar(25) NOT NULL,
 persApellido varchar(50) NOT NULL,
 persCiudad varchar(25),
 persDireccionCalle varchar(50) NOT NULL,
 persDireccionNum varchar(3) NOT NULL,
 persTelefono varchar(9),
 persFecNac smalldatetime,
 persSexo varchar(1) NOT NULL constraint CK_perssex check(persSexo = 'M' or persSexo = 'F'),
 persEdad Tinyint NOT NULL
)
GO
create table Alumno
(
 alumCodigo varchar(7) PRIMARY KEY,
 persDNI varchar(8) NOT NULL FOREIGN KEY (persDNI) REFERENCES Persona(persDNI)
)
GO
create table Profesor
(
 profCodigo varchar(4) PRIMARY KEY,
 persDNI varchar(8) NOT NULL FOREIGN KEY (persDNI) REFERENCES Persona(persDNI)
)
GO
create table Titulacion
(
 tituCodigo varchar(6) PRIMARY KEY,
 TituDescripcion varchar(20) NOT NULL unique
)
GO
create table Asignatura
(
 asigCodigo varchar(6) PRIMARY KEY,
	asigNombre varchar(50) NOT NULL,
	asigCreditos int NOT NULL constraint CK_asig check(asigCreditos = 4 or asigCreditos = 6 or asigCreditos = 8 or asigCreditos = 10), asigCuatrimestre varchar(1) NOT NULL constraint CK_cuatrimestre check(asigCuatrimestre = 1 or asigCuatrimestre = 2),
asigCostoBasico numeric NOT NULL constraint CK_CostoBasico check(asigCostoBasico < 99.99),
	profCodigo varchar(4) NOT NULL FOREIGN KEY (profCodigo) REFERENCES Profesor(profCodigo),
	tituCodigo varchar(6) NOT NULL FOREIGN KEY (tituCodigo) REFERENCES Titulacion(tituCodigo)
)
GO
create table Alumno_Asignatura
(
 alumCodigo varchar(7) NOT NULL FOREIGN KEY (alumCodigo) REFERENCES Alumno(alumCodigo),
 asigCodigo varchar(6) NOT NULL FOREIGN KEY (asigCodigo) REFERENCES Asignatura(asigCodigo),
 alasNumMatricula Tinyint NOT NULL constraint CS_NumMatriX check(alasNumMatricula >= 1 AND alasNumMatricula <= 6)
)
GO
-- INGRESAR VALORES A LAS TABLAS
-- TABLA Persona
INSERT INTO Persona VALUES ('16161616','Luis' ,'Ramirez','Haro','Pez','34','941111111','01/01/1969','M'
,18)
INSERT INTO Persona VALUES ('17171717','Laura' ,'Beltrán','Madrid','Gran Via','23','912121212',
'08/08/1974','F',18)
INSERT INTO Persona VALUES ('18181818','Pepe' ,'Perez','Madrid','Percebe','13','913131313','02/02/1980',
'M',18)
INSERT INTO Persona VALUES ('19191919','Juan' ,'Sanchez','Bilbao','Melancolia','7','944141414',
'03/03/1966','M',18)
INSERT INTO Persona VALUES ('20202020','Luis' ,'Jimenes','Nájera','Cigueña','15','941151515','03/03/1979'
,'M',18)
INSERT INTO Persona VALUES ('21212121','Rosa' ,'Garcia','Haro','Alegria','16','941161616','04/04/1978',
'F',18)
INSERT INTO Persona VALUES ('23232323','Jorge' ,'Sáenz','Logroño','Luis Ulloa','17','941171717',
'09/09/1978','M',18)
INSERT INTO Persona VALUES ('24242424','Maria' ,'Gutierrez','Logroño','Avda. de la Paz','18','941181818',
'10/10/1964','F',18)
INSERT INTO Persona VALUES ('25252525','Rosario','Díaz','Logroño','Percebe','19','941191919','11/11/1971',
'F',18)
INSERT INTO Persona VALUES ('26262626','Elena' ,'Gonzales','Logroño','Percebe','20','941202020',
'05/05/1975','F',18)
GO
-- TABLA Alumno
INSERT INTO Alumno VALUES ('A121212','16161616')
INSERT INTO Alumno VALUES ('A131313','17171717')
INSERT INTO Alumno VALUES ('A020202','18181818')
INSERT INTO Alumno VALUES ('A030303','20202020')
INSERT INTO Alumno VALUES ('A010101','21212121')
INSERT INTO Alumno VALUES ('A040404','26262626')
GO
-- TABLA Profesor
INSERT INTO Profesor VALUES ('P101','19191919')
INSERT INTO Profesor VALUES ('P203','23232323')
INSERT INTO Profesor VALUES ('P304','24242424')
INSERT INTO Profesor VALUES ('P117','25252525')
INSERT INTO Profesor VALUES ('P204','26262626')
GO
-- TABLA Titulacion
INSERT INTO Titulacion VALUES ('160000','Empresariales')
INSERT INTO Titulacion VALUES ('130110','Matemáticas')
INSERT INTO Titulacion VALUES ('150210','Química')
GO
-- TABLA Asignatura
-- En los datos sumnistrados originalmente con exel
-- existen algunos problemas tales como que los creditos no
-- concuerdan con la restriccion.
-- o que se trata de ingresar un valor nulo en un campo que es requerido
-- los valores originales se han cambiado para que sean aceptados
INSERT INTO Asignatura VALUES ('000115','Seguridad Vial',4,'1',30.00,'P204','160000')
INSERT INTO Asignatura VALUES ('130113','Programacion I',8,'1',60.00,'P101','130110')
INSERT INTO Asignatura VALUES ('130122','Analisis II' ,8,'2',60.00,'P203','130110')
INSERT INTO Asignatura VALUES ('150212','Quimica Física',8,'2',70.00,'P101','150210')
INSERT INTO Asignatura VALUES ('160002','Contabilidad' ,6,'1',70.00,'P117','160000')
GO
-- TABLA Alumno_Asignatura 
INSERT INTO Alumno_Asignatura VALUES ('A010101','150212',1)
INSERT INTO Alumno_Asignatura VALUES ('A020202','130113',1)
INSERT INTO Alumno_Asignatura VALUES ('A020202','150212',2)
INSERT INTO Alumno_Asignatura VALUES ('A030303','130113',2)
INSERT INTO Alumno_Asignatura VALUES ('A030303','150212',1)
INSERT INTO Alumno_Asignatura VALUES ('A030303','130122',2)
INSERT INTO Alumno_Asignatura VALUES ('A040404','130122',1)
INSERT INTO Alumno_Asignatura VALUES ('A121212','000115',1)
INSERT INTO Alumno_Asignatura VALUES ('A131313','160002',4)
PREGUNTAS DE STOREPROCEDURE
1.- Implementar un stored procedure "alumSPObtNroAsig" cuyo propietario
-- sea el "UsrUniversidad". Este procedure deberá de recibir como
-- parámetro el nombre y apellido de un alumno y mostrar como resultado
-- la cantidad de asignaturas en las cuales se encuentra matriculado
-- el alumno.
2.- Crear un StoreProcedure para que reciba como parámetro adicional
-- la variable "@xintNroAsignaturas". En la lógica del procedure,
-- hacer que el resultado de la cantidad de asignaturas sea asignado
-- a la variable "@xintNroAsignaturas", por lo que el stored ya
-- no retornara una fila o conjunto de fila, SOLO asignara el resultado a
-- la variable.
PREGUNTAS DE VISTAS
3.- Crear una vista denominada "viewObtDatosProfesores" que liste
-- el código, nombre y apellido de los profesores.
create view viewObtDatosProfesores
as
select Profesor.profCodigo,Persona.persNombre,Persona.persApellido 
from Profesor inner join Persona on Profesor.persDNI = Persona.persDNI;
select * from viewObtDatosProfesores
4.- Crear una vista para que liste, aparte del código,
-- nombre y apellido, el número de cursos asignado.
create view viewObtDatosProfesoress
as
select Profesor.profCodigo,Persona.persNombre,Persona.persApellido,Asignatura.asigNombre
from Profesor inner join Persona on Profesor.persDNI = Persona.persDNI inner join Asignatura on Profesor.profCodigo= Asignatura.profCodigo;
select * from viewObtDatosProfesoress
5.- En esta ultima vista usaremos la Base de Datos Northwind
PREGUNTAS DE CURSORES
6.- Para este ejemplo deseamos que se genereun correlativo pero por grupo, como puede ser el nombre.
7.- Crear un cursor que muestre todas las tablas y cuantos registros posee
PREGUNTAS DE FUNCIONES
8.-Implementar una función que reciba como parámetro una
-- fecha(SmallDatfechaeTime), un formato y un separador de fecha.
-- La función deberá de retornar la misma fecha como STRING,
-- armado en base al formato y al SEPARADOR.
9.- FUNCTION 2. En la Base de Datos Universidad implementar una función que
-- reciba como parámetro el DNI de la persona de modo que se pueda
-- identificar si la persona es un Alumno o un Profesor. Esta
-- función deberá de ser utilizada al hacer un SELECT a la tabla
-- "Persona" añadiéndole una columna en el query que haga referencia
-- a si es un Alumno ó un Profesor.
10.- FUNCTION 3. Implementar un función de Valores de Tabla en línea que reciba
-- como parámetro el Nombre y Apellido de la persona y devuelva como
-- un conjunto de Registros (rowset) los datos de las asignaturas
-- asociadas al nombre y apellido del profesor enviado como parámetro.
PREGUNTAS DE TRIGERS
11.- Crea un desencadenador, que es una clase especial de procedimiento almacenado que se ejecuta automáticamente cuando un usuario intenta la instrucción especificada de modificación de datos en la tabla indicada. Microsoft® SQL Server™ permite crear varios desencadenadores para cualquier instrucción INSERT, UPDATE o DELETE.
12.-ELIMINAR UN TRIGUER