UNIVERSIDAD DE MANIZALES

DIPLOMADO DE DESARROLLO DE SOFTWAREMATERIA: BASES DE DATOSPRESENTADO POR: ALEXANDRA SANTA MUOZ

PROFESOR: LUIS BERTEL

MANIZALES, ABRIL DE 2014TALLER DE CONSULTAS

Resuelva las siguientes preguntas con lgebra relacional y su respectiva consulta en SQL. El estudiante debe dar evidencia de los resultados de las consultas.

1. Liste las oficinas de ventas mostrando la ciudad en donde cada una est localizada y sus ventas anuales hasta la fecha.

SELECT ciudad, ventas, FROM oficinas 2. Liste los nombres y las ventas anuales hasta la fecha para cada vendedor de la base de datos. Tambin muestre la cuota (objetivo de venta) y el nmero de oficina en donde cada persona trabaja.

Select repventas.nombre, repventas.ventas * 12 AS ventas_anuales, repventas.cuota, oficinas.objetivo, oficinas.oficinafrom repventas, oficinas

3. Calcule el valor en la cual cada vendedor est por encima o por debajo de su cuota.

4. Indique los vendedores cuyas ventas son inferiores a sus cuotas.Select num_empl, nombre, ventas, cuotafrom repventaswhere ventasobjetivo;

14. Cules son los objetivos y ventas promedio para las oficinas de la regin ESTE?

Select regin, objetivo, avg(ventas)From oficinasWhere regin='ESTE'Group by oficinas. regin, oficinas.objetivo;

15. Liste los nombres, oficinas y fechas de contrato de todos los vendedores.

select nombre, oficina_rep, contratofrom repventas

16. Cul es el nombre, cuota y ventas del empleado nmero 107?SELECT nombre, cuota, ventasFROM repventasWHERE num_empl = 107

17. Cules son las ventas promedio de nuestros vendedores?SELECT avg (ventas)From repventas

18. Liste el nombre y la fecha de contrato de cualquier vendedor cuyas ventas sean superiores a $500.000.

Select nombre, contrato, ventasFrom repventas Where ventas > 500000;

19. Lista de los vendedores, sus cuotas y sus directores.SELECT nombre, cuota, directorFROM repventas

20. Lista de la ciudad, regin y ventas de cada oficina.

SELECT ciudad, regin, ventasFrom oficinas

21. Liste la ciudad, la regin y el importe por encima o por debajo del objetivo para cada oficina.

Select oficina, ciudad, regin, objetivoFrom oficinasWhere ventasobjetivo;

22. Muestre el valor del inventario para cada producto.

SELECT id_producto, (existencias * precio)FROM productos;

23. Muestre qu suceder si se eleva la cuota de cada vendedor un 3% de sus ventas anuales hasta la fecha correspondiente.

24. Liste el nombre, el mes y el ao de contrato para cada vendedor.

SELECT nombre, extract(month from contrato), extract(year from contrato)FROM repventas

25. Liste las ventas para cada ciudad.SELECT ventas, ciudadFROM oficinas

26. Muestre todos los datos de la tabla oficinas.SELECT *from oficinas

27. Liste los nmeros de empleados de todos los directores de las oficinas de ventas.SELECT num_empl, director,oficina_repfrom repventas

28. Liste los nmeros de empleado de todos los directores de oficina de ventas.

SELECT num_empl, director,oficina_repfrom repventas

29. Muestre las oficinas en donde las ventas exceden al objetivo.

SELECT oficinafrom oficinaswhere ventas> objetivo;

30. Muestre el nombre, las ventas y la cuota del empleado nmero 105.

SELECT nombre, ventas, cuotaFROM repventasWHERE num_empl = 105

31. Muestre los empleados dirigidos por Bob Smith (empleado 104).

SELECT num_empl, nombreFROM repventasWHERE director = 104;

32. Halle los vendedores contratados antes de 1988.

SELECT *FROM repventasWHERE extract(year from contrato)cuota;

37. Liste los vendedores que estn por debajo o en su cuota.select num_empl, nombre, ventas, cuotafrom repventaswhere(ventas250000);

64. Lista los pedidos superiores a $25.000, mostrando el nombre del cliente que remiti el pedido y el nombre del vendedor asignado a ese cliente.

Select repventas.nombre, clientes.empresa, pedidos.num_pedido, pedidos.importe From repventas Inner join pedidos On (repventas.num_empl=pedidos.rep) Inner join clientes On (clientes.num_clie=pedidos.clie) Where (pedidos.importe>25000);

65. Liste los pedidos superiores a $25.000, mostrando el nombre del cliente que lo orden, el vendedor asociado al cliente y la oficina en donde el vendedor trabaja.

Select clientes.empresa, repventas.nombre, repventas.oficina_rep, pedidos.num_pedido, pedidos.importe From repventas Inner join pedidos On (repventas.num_empl=pedidos.rep) Inner join clientes On (clientes.num_clie=pedidos.clie) Where (pedidos.importe>25000);

66. Halle todos los pedidos recibidos en los das en que un nuevo vendedor fue contratado.

67. Liste todas las combinaciones de vendedores y oficinas en donde la cuota del vendedor es superior al objetivo de la oficina.

Select repventas.num_empl, repventas.nombre, repventas.oficina_rep, oficinas.objetivo, repventas.cuotaFrom oficinas, repventasWhere cuota>objetivo

68. Muestre las ciudades en donde las ventas superan al objetivo.

select ciudad, ventas, objetivofrom oficinaswhere ventas>objetivo

69. Muestre todos los vendedores con ventas superiores a $350.000.

Select repventas.num_empl, repventas.nombre, repventas.ventasFrom repventasWhere ventas > 350000;

70. Muestre el nombre, las ventas y la oficina de cada vendedor.

Select nombre, ventas, oficina_repFrom repventas

71. Liste los nombres de los vendedores y sus directores.

Select nombre, directorFrom repventas

72. Lista los vendedores con una cuota superior a la de su director.

select empleado.num_empl, empleado.nombre from repventas As empleado inner join repventas on (empleado.num_empl=repventas.director)where empleado.cuota>repventas.cuota;

73. Liste los vendedores que trabajan en diferentes oficinas que sus directores, mostrando el nombre y la oficina en donde trabaja cada uno.

74. Liste los nombres, cuota y cumpleaos de los vendedores.

select nombre,cuota, edadfrom repventas

75. Muestre todas las combinaciones posibles de vendedor y ciudades.select oficinas.ciudad, repventas.num_empl from repventas, oficinas

76. Muestre todos los vendedores y las ciudades en donde trabajan.

select repventas.num_empl,repventas.nombre, oficinas.ciudadfrom repventas, oficinas

77. Liste el nombre y la empresa y todos los pedidos para el nmero de cliente 2103.

select clientes.num_clie, clientes.empresa, sum(pedidos.cant)from clientes, pedidoswhere clientes.num_clie=2103group by clientes.num_clie;

78. Liste los vendedores y las oficinas en que trabajan.

Select num_empl, nombre, oficina_repFrom repventas

79. Liste los vendedores y las ciudades en que trabajan.

select repventas.num_empl, oficinas.oficina, oficinas.ciudadfrom repventas, oficinas

80. Cul es la cuota promedio y las ventas promedio de los vendedores?

Select nombre, avg (cuota), avg (ventas)From repventasGroup by (num_empl)

81. Cul es el rendimiento de cuota promedio de los vendedores?

select avg(cuota), num_emplfrom repventasgroup by num_empl

82. Cules son las cuotas y ventas totales para todos los vendedores?

Select sum (cuota), sum(ventas)From repventas

83. Cul es el total de los pedidos aceptados por Bill Adams?

select repventas.nombre, sum(pedidos.cant)from pedidos, repventaswhere repventas.nombre='BILL ADAMS'group by repventas.nombre;

84. Calcule el precio medio de los productos del fabricante ACI.

select id_fab,avg(precio)from productoswhere id_fab='ACI'group by id_fab;

85. Calcule el tamao medio de un pedido ordenado por Acme Mfg.

Select avg(pedidos.cant),clientes.empresaFrom pedidos, clientesWhere clientes.empresa='ACME MFG'Group by clientes.empresa;

86. Cules son las cuotas asignadas mnima y mxima?

Select MIN(cuota), MAX(cuota)From repventas

87. Cul es la fecha de pedido ms antigua en la base de datos?select MIN(fecha_pedido)FROM pedidos

88. Cul es el mejor rendimiento de ventas de todos los vendedores?

select Max(ventas),sum(repventas.num_empl)FROM repventasgroup by repventas.num_empl

89. Cuntos clientes hay?

SELECT num_clie, COUNT(*)FROM clientesGROUP BY num_clie;

90. Cuntos vendedores superan su cuota?

select nombre,count(num_empl),cuota,ventasfrom repventaswhere ventas>cuotagroup by repventas.nombre,repventas.cuota,repventas.ventas

91. Cuntos pedidos de ms de $25.000 hay en la base de datos?

SELECT importe, COUNT(*)FROM pedidosGROUP BY importeHAVING importe > 25000;

92. Halle el importe medio de pedidos, el importe total de pedidos, el importe medio de pedido como porcentaje del lmite de crdito del cliente y el importe medio de pedido como porcentaje de la cuota del vendedor.

93. Cuntos ttulos diferentes tienen los vendedores?

SELECT DISTINCT tituloFROM repventas

94. Cuntas oficinas de ventas tienen vendedores que superan a sus cuotas?

select count(oficina_rep)from repventaswhere ventas>cuota

95. Cul es el tamao medio de pedidos?

SELECT AVG(importe)FROM pedidos

96. Cul es el tamao medio de pedidos para cada vendedor?

SELECT AVG(importe)FROM pedidosGroup by rep;

97. Cul es el rango de cuotas asignadas en cada oficina?

98. Cuntos vendedores estn asignados a cada oficina?

SELECT oficina_rep, COUNT(*)FROM repventasGROUP BY oficina_rep;

99. Cuntos clientes diferentes son atendidos por cada vendedor?

SELECT count(num_clie), rep_clie FROM clientesgroup by clientes.rep_clie

100. Calcule los pedidos totales para cada cliente de cada vendedor, ordenados por cliente y dentro de cada cliente por vendedor.

select sum(cant),clie,repfrom pedidosgroup by pedidos.clie,pedidos.reporder by clie,rep

101. Calcula los pedidos totales para cada cliente de cada vendedor, ordenados por vendedor, y dentro de cada vendedor por cliente.

select sum(cant),rep,cliefrom pedidosgroup by pedidos.clie,pedidos.reporder by rep,clie

102. Calcula los pedidos totales por cada vendedor.

Select rep, cant, num_pedidoFrom pedidos

103. Cul es el tamao de pedidos promedio para cada vendedor cuyos pedidos totalizan ms de $30.000?

select rep, avg (cant),importeFrom pedidosGROUP BY REP, importeHAVING importe>30000

104. Por cada oficina con dos o ms persona, calcular la cuota total y las ventas totales para todos los vendedores que trabajan en la oficina.

SELECT ciudad, SUM(cuota), SUM(repventas.ventas)FROM oficinasINNER JOIN repventasON (oficinas.oficina = repventas.oficina_rep)GROUP BY ciudadHAVING COUNT(*) >2;

105. Muestre el precio, la existencia y la cantidad total de los pedidos de cada producto para los cuales la cantidad total pedida es superior al 75%.

106. Liste las oficinas en donde el objetivo de ventas de la oficina excede a la suma de las cuotas de los vendedores individuales.

select oficinas.objetivo,oficinas.oficina,sum(repventas.cuota),repventas.num_emplfrom oficinas,repventaswhere objetivo> (select sum(repventas.cuota) from repventas)group by oficinas.objetivo,oficinas.oficina, repventas.num_empl

107. Liste los vendedores cuyas cuotas son iguales o superiores al objetivo de la oficina de ventas de Atlanta.

select repventas.num_empl, repventas.cuota, oficinas.oficina, oficinas.ciudad,oficinas.objetivofrom repventas,oficinaswhere cuota>=objetivo and oficina=13 and ciudad='ATLANTA';

108. Liste todos los clientes atendidos por Bill Adams(105).

SELECT num_clie, rep_clie From clientes Where rep_clie=105;

107. Liste todos los productos del fabricante ACI para los cuales las existencias superan a las existencias del producto ACI-41004.

select id_producto,id_fab,existenciasfrom productoswhere id_fab='ACI' and existencias>'41004';

108. Liste los vendedores que trabajan en oficinas que superan su objetivo.

select repventas.num_empl, repventas.nombre, oficinas.oficina,oficinas.objetivo,oficinas.ventasfrom oficinas, repventaswhere oficinas.ventas>oficinas.objetivo

109. Liste los vendedores que no trabajan en oficinas dirigidas por Larry Fith.

select repventas.num_empl,repventas.nombre, oficinas.dirfrom repventas,oficinaswhere 'LARRY FITCH' is null;

110. Liste todos los clientes que han remitido pedidos de ACI (Fabricante ACI, nmeros de productos que comienzan con 41000) entre enero y junio de 1990.

select productos.id_fab, productos.id_producto,pedidos.clie, extract(month from pedidos.fecha_pedido)from pedidos,productoswhere id_fab='ACI' and id_producto= '41000' and pedidos.fecha_pedido='1990-01-01' AND '1990-06-30';

111. Liste los productos para los cuales se ha recibido un pedido de $25.000, o ms.

select productos.id_producto, pedidos.importefrom productos, pedidoswhere pedidos.importe>25000

112. Lista los clientes asignados a Sue Smith que no han remitido en pedido superior a $3.000.

select clientes.num_clie,clientes.empresa,repventas.nombre,pedidos.importefrom pedidos,clientes, repventaswhere pedidos.importe>3000 and repventas.nombre='SUE SMITH';

113. Lista las oficinas en donde haya un vendedor cuya cuota represente ms del 55% del objetivo de las oficinas.

114. Liste los vendedores que han aceptado un pedido que represente ms del 10% de su cuota.

115. Lista las oficinas y sus objetivos en donde todos los vendedores tienen ventas que superan al 50% del objetivo de la oficina.

116. Lista los nombres y edades de los vendedores que trabajan en oficinas de la regin OESTE.

Select nombre, edad, oficina_repFrom repventasWhere oficina_rep between 21 and 22;

117. Lista los nombres y edades de los vendedores que trabajan en oficinas de la regin OESTE.

Select nombre, edad, oficina_repFrom repventasWhere oficina_rep between 21 and 22;

118. Lista los nombres y edades de los vendedores que tienen por encima del promedio.

select nombre,edad,avg(ventas)from repventasgroup by repventas.nombre,repventas.edad

119. Liste los clientes cuyos vendedores estn asignados a oficinas en la regin de ventas ESTE.

select clientes.num_clie,clientes.empresa, repventas.num_empl,oficinas.regionfrom clientes,repventas,oficinaswhere region='ESTE';

120. Liste las oficinas de ventas cuyas ventas estn por debajo del objetivo medio.

select oficina,ventas,avg(objetivo)from oficinas where ventas(select sum(repventas.cuota) from repventas)group by oficinas.oficina

122. Liste los vendedores que tienen ms de 40 aos y dirigen a un vendedor por encima de la cuota.

select empleado.edad, empleado.cuota, empleado.ventas from repventas as empleadoinner join repventas on (empleado.edad=repventas.director)where empleado.edad>40 and empleado.ventas>empleado.cuota;

123. Liste los directores mayores de 40 aos y que dirigen a un vendedor cuyas ventas superan a la cuota y que no trabaja en la misma oficina de ventas que el director.

select empleado.edad, empleado.cuota, empleado.ventas, oficinas.dir from repventas,oficinas as empleadoinner join repventas on (empleado.edad=repventas.director)where empleado.edad>40 and empleado.ventas>empleado.cuota and oficina_rep in (select DISTINCT dir from oficinas);

124. Liste los vendedores cuyo tamao de pedido medio para productos fabricados por ACI es superior al tamao de pedido medio global.

125. Liste los vendedores cuyo tamao de pedido medio para productos fabricados por ACI es al menos tan grande como el tamao de pedido medio global de ese vendedor.Marcar infraccin de copyright