• Author / Uploaded
• anavlucenag

• Categories
• SQL
• Tabla (base de datos)
• Bases de datos
• Recuperación de información
• Datos

Citation preview

Guía de Ejercicios SQL 1. Teniendo como base las siguientes tablas: MECANICOS aci caracter 9 anombre caracter 30 adir caracter 40 atlf caracter 12 afnac date dd/mm/yyyy afing date dd/mm/yyyy asueldo numérico aestatus caracter 1

CLIENTES pci pnombre pdir ptlf pestatus

CARROS/CLIENTES

REPARACIONES MECANICO mci caracter 9 mplaca caracter 5 mdesc_repara caracter 6 mfecha_repara numérico monto_cobrado numércio mrestatus caracter 1

ci placa estatus

caracter 9 caracter 6 caracter 1

caracter 9 caracter 30 caracter 40 caracter 12 caracter 1

CARROS cplaca cmodelo color cserial motor caño_carro cestatus

caracter 6 caracter 20 caracter 30 caracter 15 numèrico caracter 1

Se pide que Ud. construya las instrucciones de SQL que permitan resolver cada una de las siguientes preguntas: 1. Listado donde se observe la información de los carros reparados por ‘Juan Gonzalez’ en el mes de Agosto del año 2007. Este listado debe ser presentado en forma ordenada por fecha de reparación y los datos a reportar son: la placa, año carro, ci dueño, nombre dueño, fecha reparación y monto Cobrado 2. Se necesita guardar en una nueva tabla la información del Nombre Cliente, Cédula Cliente y cantidad de carros que posee. Una vez que tenga esta tabla indique cual es el cliente que tiene mayor cantidad de carros 3. El taller ha decidido que se premiará con un bono del 10% del sueldo a los mecánicos que hayan producido ingresos superiores a un millón de bolívares entre los meses de enero a marzo del año 2007. Elabore un listado que indique cuales mecánicos recibirán el premio y cual es el monto que el taller le deberá pagar por el premio. 4. Se necesita saber los datos de los clientes que tienen carros de algunos de los siguientes años: 1980, 1970, 1990 que sean de color negro

2. Teniendo como base las siguientes tablas:

ALUMNOS aci caracter 9 anombre caracter 30 adir caracter 40 atlf caracter 12 afnac date dd/mm/yyyy afing date dd/mm/yyyy acarrera caracter 1 ‘I’,’M’,’A’ aestatus caracter 1 ASIG-CURSADAS ccial caracter 9 ccodasig caracter 5 clapso caracter 6 cseccion numérico cnotafinal numérico ccondicion caracter 1 cestatus caracter 1

PROFESORES pci caracter 9 pnombre caracter 30 pdir caracter 40 ptlf caracter 12 pfnac date dd/mm/yyyy pfing date dd/mm/yyyy pcoddpto numérico (1al 5) psueldo Numérico pestatus caracter 1 ASIG-PROF prci caracter 9 prcodasig caracter 5 prlapso caracter 6 prseccion Numérico prestatus caracter 1

ASIGNATURAS scodasig caracter 5 snomasig caracter 20 screditos numérico sestatus caracter 1

DEPARTAMENTOS dcod numérico (1 al 5) dnombre caracter 30 dcijefe caracter 9 destatus caracter 1

NOTA: El código de la asignatura esta formado por 5 caracteres donde el primero será una letra (‘I’,’M’,’A’) que representa la carrera, el segundo un número que identifica a que semestre pertenece la asignatura y los 3 restantes son números para complementar el código. Así por ejemplo: Código ‘I3154’ es una asignatura que pertenece al tercer semestre de la carrera de Informática, Código ‘M3155’ es una asignatura que pertenece al tercer semestre de la carrera de Matemática, Código ‘M1024’ es una asignatura que pertenece al primer semestre de la carrera de Matemática y así sucesivamente. Se pide que Ud. construya las instrucciones de SQL que permitan resolver cada una de las siguientes preguntas: 1. Mostrar los datos de los profesores que han sido jefes de departamento. En el listado deben aparecer los datos del código y nombre del departamento así como la Cédula y el nombre del profesor. SELECT dcod, dnombre, dcijefe, pnombre FROM departamento, profesores WHERE dcijefe = pci AND pestauts = 'A' AND destatus = 'A'; 2. Mostrar la información de todas las asignaturas que se dictan en la carrera de informática. SELECT scodasig, snomasig, screditos FROM asignaturas WHERE sestatus = 'A' and upper(substr(scodasig,1,1)) = 'I'; otra alternativa SELECT scodasig, snomasig, screditos FROM asignaturas

WHERE sestatus = 'A' and upper(scodasig) like 'I%'; 3. Eliminar el departamento 3. Todos sus profesores deberán ser transferidos a un archivo temporal y eliminados físicamente del archivo de profesores. UPDATE departamento SET destatus = ‘E’ WHERE dcod = 3 CREATE TABLE temporal ( tci VARCHAR(9), tnom VARCHAR(30), tdir VARCHAR(40), ttlf VARCHAR(12), tfnac DATE, tfing DATE, tcodpto NUMBER, tsueldo NUMBER, testatus VARCHAR(1)); INSERT INTO temporal (tci,tnom,tdir,ttlf,tfnac,tfing,tcodpto,tsueldo,testatus) SELECT pci,pnombre,pdir,ptlf,pfnac,pfing,pcoddpto,psueldo,pestatus FROM profesores WHERE pcoddpto = 3 and pestatus = 'A' DELETE FROM profesores WHERE pcoddpto = 3 4. Mostrar las cédulas y los nombres de todos los estudiantes que han cursado alguna asignatura con la profesora MARGARITA PEREIRA. En este listado no deben repetirse la información de los estudiantes. SELECT DISTINCT aci, anombre FROM alumnos, profesores, asigcursadas, asigprof WHERE prci = (SELECT pci FROM profesores WHERE pnombre = 'MARGARITA PEREIRA' AND pestatus = 'a') and aci = ccial and ccodasig = prcodasig and cseccion = prseccion and prlapso = clapso and aestatus = 'a' and pestatus = 'a' and cestatus = 'a' and pestatus = 'a'; 5. Mostrar por cada profesor el total de alumnos y el promedio de notas SELECT pci, pnombre, count(*) as 'total alumnos', avg(cnotafinal) as 'promedio' FROM asigprof, asigcursadas, profesores WHERE pci = prci and prcodasig = ccodasig and prseccion = cseccion

and prlapso = clapso and prestatus = 'A' and cestatus = 'A' and pestatus = 'A' GROUP BY pci, pnombre 6. La universidad ha decidido pagar un bono del 5% del sueldo a todos los profesores. Para realizar este cambio Ud. debe crear y adicionar los datos a un nuevo archivo llamado “PROF_BONO” que tendrá la misma estructura del archivo “PROFESORES” con un campo adicional llamado BONO de tipo numérico. CREATE TABLE profbono ( bci VARCHAR(9), bnom VARCHAR(30), bdir VARCHAR(40), btlf VARCHAR(12), bfnac DATE, bfing DATE, bcodpto NUMBER, bsueldo NUMBER, bono NUMBER, bestatus VARCHAR(1)); INSERT INTO profbono ( bci, bnom, bdir, btlf, bfnac, bfing, bcodpto, bsueldo,bono, bestatus ) SELECT pci, pnombre, pdir, ptlf, pfnac, pfing, pcoddpto, psueldo, (psueldo*0.05),pestatus FROM profesores WHERE pestatus='A'; 7. Mostrar la información de todos los profesores que tienen más de 15 años trabajando en la Universidad, este listado debe presentarse en forma ordenada por carrera. Las columnas que deberán observarse en el listado son: Carrera, CI profesor, Nombre Prof, Fecha ingreso, Total años de Servicio SELECT pcoddpto, pci, pnombre, pfing AS 'Fecha de Ingreso', ROUND((pfing-TO_CHAR(SYSDATE,'DD/MM/YYYY')/365) AS 'Total años de servicio' FROM profesores WHERE pestatus='A' ORDER BY pcoddpto; 8. Presentar un listado donde se observe la cedula y el nombre de todos los alumnos que han repetido alguna asignatura. SELECT DISTINCT ccial, anombre FROM alumnos, asigcursadas WHERE (ccial=aci) AND estatus='A' AND cestatus='A' AND (ccondicion In ('1','2','3'));

3. Con las siguiente Base de Datos: Identificación Empleados Aci Anombre Adirec Atlf Afing Afnac Asueldo Acod_dpto Aestatus

Identificación de Departamentos Pcódigo Pnombre Pci_jefe

Identificación de Proyectos Rcódigo_proy Rnombre_proy Rcódigo_dpto Rnro_personas

Empleados/Proyecto Eci_empleado Ecódigo_proyecto Efecha_inicio Efecha_culminación

El Código de departamento es un char. Por ejemplo ‘C’ corresponde dpto de Contabilidad, ‘I’ dpto de Informática, ‘R’ recursos humanos, etc…. El estatus en el archivo de Identificación de Empleados es un char cuyos valores son ‘A’ activo y ‘S’ Suspendido temporalmente de la empresa. Por política de la Empresa el Departamento de Recursos humanos ha decidido que todos los empleados que estén suspendidos deben ser despedidos de la empresa y además a todos los empleados que pertenezcan al departamento de Informática se le dará un aumento del 10% de su sueldo. Se pide que Ud. Elabore las instrucciones SQL que permitan aplicar esta medida. Su eliminación debe ser física y todos los registros eliminados deben ser guardados en un archivo histórico. Su programa debe mantener la información de los archivos coherente y consistente.