• Categories
• SQL
• Tabla (Base de datos)
• Bases de datos
• Microsoft Sql Server
• Datos

Citation preview

Universidad Dominicana O&M Base de Datos Prof. Junior A. Mendez, MCP, MCSA, LIC Practica Final Numero Descripción

Forma de entrega En Papel

1

Crear las tablas necesarias para la practica Ver. Anexos Realizar lo solicitado en el Anexo.

2

Le sugiero que utilicen Microsoft SQL Express, se baja Base de datos gratis desde Microsoft, traer la plataforma completa, (electrónico) Traer la Base de datos o la Laptop preparada para CD presentar. En Flash Memory es recomendable si no tiene laptop (esto es independiente a la entrega)

3

Insertar información a la base de datos suficiente para realizar pruebas. Debe de presentar las prueba que utilizo el comando INSERT de SQL. Hacer 2 Insert representativas para cada Tabla, pero debe llenar las tablas según como están. Realizar 45 sentencias SQL. Ver Anexos

4

Base de datos (Electrónico) CD

En Papel y en la Base de datos (electrónico) CD Crear 10 sentencias SQL que pueden ser utilizados en su En Papel y en base de datos, y que presenten un grado de complejidad la Base de datos la cual permita desplegar lo ya aprendido (electrónico) CD

5

Nota: Todas las sentencias deben de estar tanto en papel como electrónica para su entrega

Punto 1. Crear las tablas necesarias para la práctica. 1.

Crear la tabla empleado y definir su clave principal en la misma instrucción de creación.

2. Crear la tabla oficina con su clave principal y su clave foránea ( la columna dir contiene el código de empleado del director de la oficina

luego es un campo que hace referencia a un empleado luego es clave foránea y hace referencia a la tabla empleados). 3. Crear la tabla producto con su clave principal. 4. Crear la tabla cliente también con todas sus claves y sin la columna limitecredito. 5. Crear la tabla pedido sin clave principal, con la clave foránea que hace referencia a los productos, la que hace referencia a clientes y la que indica el representante (empleado) que ha realizado el pedido. 6. Crear tabla de Fabricante 7. Añadir a la definición de clientes la columna limitecredito. 8. Añadir a la tabla empleado las claves foráneas que le faltan. (Si no tienes claro cuales son te lo decimos ahora: la columna oficina indica la oficina donde trabaja el empleado y la columna director indica quién dirige al empleado, su jefe inmediato). 9. Hacer que no puedan haber dos empleado con el mismo nombre. 10. Añadir a la tabla de pedido la definición de clave principal. 11. Definir un índice sobre la columna región de la tabla de oficina.

Tema 4. Realizar 45 sentencias SQL 1. Obtener la lista de Fabricante con nombre y cantidad de productos. 2. Obtener una lista de todos los productos indicando para cada uno su idfab, idproducto, descripción, precio y precio con ITBS. incluido (es el precio anterior aumentado en un 16%). 3. De cada pedido queremos saber su número de pedido, fab, producto, cantidad, precio unitario e importe. 4. Listar de cada empleado su nombre, nº de días que lleva trabajando en la empresa y su año de nacimiento (suponiendo que este año ya ha cumplido años). 5. Obtener cantidad de Fabricante por Zona 6. Obtener la lista de los clientes agrupados por código de representante asignado, visualizar todas las columnas de la tabla. 7. Obtener las oficinas ordenadas por orden alfabético de región y dentro de cada región por ciudad, si hay más de una oficina en la misma ciudad, aparecerá primero la que tenga el número de oficina mayor.

8. Obtener los pedidos ordenados por fecha de pedido. 9. Listar las cuatro líneas de pedido más caras (las de mayor importe). 10. Listar el valor total de los productos, distribuido por fabricante (Nombre), o sea cantidad de productos por cantidad de existencia. 11. Obtener las mismas columnas que en el punto 2 pero sacando únicamente las 5 líneas de pedido de menor precio unitario. 12. Listar toda la información de los pedidos de marzo. 13. Listar los números de los empleados que tienen una oficina asignada. 14. Listar los números de las oficinas que no tienen director. 15. Listar los datos de las oficinas de las regiones del norte y del este (tienen que aparecer primero las del norte y después las del este). 16. Listar los empleados de nombre Julia. 17. Listar los productos cuyo idproducto acabe en x. 18. Listar las oficinas del este indicando para cada una de ellas su número, ciudad, números y nombres de sus empleados. Hacer una versión en la que aparecen sólo las que tienen empleados, y hacer otra en las que aparezcan las oficinas del este que no tienen empleados. 19. Listar los pedidos mostrando su número, importe, nombre del cliente, y el límite de crédito del cliente correspondiente (todos los pedidos tienen cliente y representante). 20. Listar los datos de cada uno de los empleados, la ciudad y región en donde trabaja. 21. Listar las oficinas con objetivo superior a 600.000 indicando para cada una de ellas el nombre de su director. 22. Listar los pedidos superiores a 25.000, incluyendo el nombre del empleado que tomó el pedido y el nombre del cliente que lo solicitó. 23. Hallar los empleados que realizaron su primer pedido el mismo día en que fueron contratados. 24. Listar los empleados con una cuota superior a la de su jefe; para cada empleado sacar sus datos y el número, nombre y cuota de su jefe. 25. Listar los códigos de los empleados que tienen una línea de pedido superior a 10.000 o que tengan una cuota inferior a 10.000. 26. Cuál es la cuota media y las ventas medias de todos los empleados 27. Hallar el importe medio de pedidos, el importe total de pedidos y el precio medio de venta (el precio de venta es el precio unitario en cada pedido). 28. Hallar el precio medio de los productos del fabricante ACI. 29. ¿Cuál es el importe total de los pedidos realizados por el empleado Vicente Pantalla? 30. Hallar en qué fecha se realizó el primer pedido (suponiendo que en la tabla de pedidos tenemos todos los pedidos realizados hasta la fecha). 31. Hallar cuántos pedidos hay de más de 25000 32. Listar cuántos empleados están asignados a cada oficina, indicar el número de oficina y cuántos hay asignados. 33. Para cada empleado, obtener su número, nombre, e importe vendido por ese empleado a cada cliente indicando el número de cliente.

34. Para cada empleado cuyos pedidos suman más de 30.000, hallar su importe medio de pedidos. En el resultado indicar el número de empleado y su importe medio de pedidos. 35. Listar de cada producto, su descripción, precio y cantidad total pedida, incluyendo sólo los productos cuya cantidad total pedida sea superior al 75% del stock; y ordenado por cantidad total pedida. 36. Saber cuántas oficinas tienen empleados con ventas superiores a su cuota, no queremos saber cuales sino cuántas hay. 37. Subir un 5% el precio de todos los productos del fabricante ACI. 38. Añadir una nueva oficina para la ciudad de Madrid, con el número de oficina 30, con un objetivo de 100000 y región Centro. 39. Cambiar los empleados de la oficina 21 a la oficina 30. 40. Eliminar los pedidos del empleado 105. 41. Eliminar las oficinas que no tengan empleados. 42. A los empleados de la oficina 30 asignarles la oficina 21. 43. Cual de los Empleados cumplieron con las metas de ventas. 44. Cuál es el fabricante con mayor cantidad de productos en el inventario. 45. Cuál es el producto que más se vende. Anexos

La base de datos se llamara “VentaDB”.

Tablas para la Práctica Final. nume mp

eda d

oficin a

102

nombre Antonio Viguer Alvaro Jaumes

103

Juan Rovira

29

12

104

José Gonzalez Vicente Pantalla

33

12

37

13

52

11

49

22

108

Luis Antonio Jorge Gutiérrez Ana Oustamante

62

21

109

Maria Sunta

31

11

110

Juan Victor

41

101

105 106 107

45

12

48

21

dir ventas representa nte

contra to 20/10/ 86 10/12/ 86 01/03/ 97 19/05/ 87 12/10/ 88 14/06/ 88 14/11/ 88 12/10/ 89 12/10/ 99

representa

13/01/

titulo representa nte representa nte representa nte dir ventas representa nte dir general representa nte

jefe 104 108 104 106 104 108

106 106 104

cuota 300,0 00 350,0 00 275,0 00 200,0 00 350,0 00 275,0 00 300,0 00 350,0 00 300,0 00

venta s 305,0 00 474,0 00 286,0 00 143,0 00 368,0 00 299,0 00 186,0 00 361,0 00 392,0 00 76,00

nte

90

0

Tabla empleado con los siguientes campos: numemp: número del empleado nombre : nombre y apellidos del empleado edad : edad del empleado oficina : número de la oficina donde trabaja el empleado, p.ej. Antonio Viguer trabaja en la oficina 12 de Alicante titulo : el cargo que desempeña el empleado contrato : fecha en que se contrató al empleado jefe : número de su jefe inmediato, p.ej. El jefe de Antonio Viguer es José González. Observar que Luis Antonio no tiene jefe, es el director general. cuota : cuota del empleado, sería el importe mínimo de ventas que debe alcanzar el empleado en el año ventas : importe de ventas realizadas durante este año oficina

ciudad

region

dir

objetivo

ventas

11

Higuey

este

106

575,000.00

693,000.00

12

Romana

este

104

800,000.00

735,000.00

13

San Pedro

este

105

350,000.00

368,000.00

21

Azua

Sur

108

725,000.00

836,000.00

22

Barahona

Sur

108

300,000.00

186,000.00

23

Santiago

Norte

108

24

Puerto Plata

Norte

108

26 28

Santiago

Norte

Higuey

este

250,000.00 900,000.00

150,000.00 -

Tabla oficina con los siguientes campos: oficina: código de la oficina ciudad: ciudad donde está ubicada region : región a la que pertenece dir : director de la oficina (su número de empledo) por ejemplo la oficina 12 tiene como director el empleado104 José González. objetivo : objetivo de ventas que debe alcanzar la oficina ventas: ventas de la oficina numclie

nombre

repclie

limitecredito

2101

Luis Garcia Anton

106

65,000

2102

Alvaro Rodriguez

101

65,000

2103

Jaime Llorens

105

50,000

2105

Antonio Canales

101

45,000

2106

Juan Suárez

102

65,000

2107

Julian LOpez

110

35,000

2103

Julia Antequera

109

55,000

2109

Alberto Juanes

103

25,000

2111

CristObal Garcia

103

50,000

2112

Maria Silva

103

50,000

2113

Luisa Maron

104

20,000

2114

Cristina Bulini

102

20,000

2115

Vicente Martinez

101

20,000

2117

CarlosTena

106

35,000

2113

Junipero Alvarez

108

60,000

2119

Salomon Bueno

109

25,000

2120

Juan Malo

102

50,000

2121

Vicente Rios

103

45,000

2122

José Marchante

105

30,000

2123

José Libros

102

40,000

2124

Juan Bolto

107

40,000

Tabla cliente con los siguientes campos: numclie: número de cliente nombre : nombre y apellidos del cliente repclie : nº del representante asignado al cliente. Cada cliente tiene un representante asignado (el que figura en repclie) que será el que generalmente le atienda. limitecredito : límite de crédito del cliente

idfab

idproducto

descripcion

precio

existencias

aci

41001

arandela

58

277

aci

41002

bisagra

80

167

aci

41003

artt3

112

207

aci

41004

artt4

123

139

aci

4100x

junta

26

37

aci

4100y

extractor

2,888

25

aci

4100z

mont

2,625

28

bic

41003

manivela

652

3

bic

41009

rodamiento

225

70

bic

41672

plato

180

0

fea

112

cubo

140

115

fea

114

cubo

243

15

imm

773c

reostato

975

20

imm

775c

reostato 2

1,425

5

imm

779c

reostato 3

1875

0

imm

887h

caja Clavos

54

223

imm

887p

perno

25

24

imm

887x

manivela

475

32

qsa

xk47

red

355

30

qsa

xk48

red

134

203

qsa

xk48a

red

117

37

rei

2a44g

pas

350

14

rei

2a44I

bomba I

4,500

12

rei

2a44r

bomba r

4,500

12

rei

2a45c

junta

79

210

Tabla producto con los siguientes campos: idfab: identificativo del fabricante del producto idproducto : código que utiliza el fabricante para codificar el producto. Observar que aparecen varias líneas con el mismo idproducto (41003), por lo que la clave principal de la tabla deberá ser idfab+idproducto descripcion: nombre del producto precio: precio del producto existencias: nº de unidades del producto que tenemos en almacén.

idfab

NombreFab

Direccion

Zona

Telefono

aci

Acero Inc

Av. Estrella Sadala

Norte

533-3333

bic

BIC del Caribe

Av. JPD

Sur

586-5326

fea

112

Av. 27 de Feb

Norte

685-9556

imm

Imm Dominicana

Av. 27 de Feb

DN

540-5323

qsa

QSA de Santo Domingo

Av. Luperon

DN

598-8784

rei

Rei & Cia

Av. Independencia

Este

789-9563

Tabla Fabricante con los siguientes campos idfab: identificativo del fabricante del producto NombreFab: nombre del frabicante Direccion: Dirección Zona: Zona del País

codigo

numpedido

fechapedido

clie

rep

fab

producto

cant

importe

1

110036

2/1/97

2107

110

aci

4100z

9

22,500

2

110036

2/1/97

2117

106

rei

2a44I

7

31,500

3

112963

10/5/97

2103

105

aci

41004

28

3,276

4

112960

11/1/90

2102

101

aci

41004

34

3,970

5

112975

11/2/97

2111

103

rei

2a44g

6

2,100

6

112979

12/10/89

2114

108

aci

4100z

6

15,000

7

112903

10/5/97

2103

105

aci

41004

6

702

8

112907

1/1/97

2103

105

aci

4lOOy

11

27,500

9

112989

10/12/97

2101

106

fea

114

6

1,458

10

112992

10/11/90

2110

105

aci

41002

10

760

11

112993

10/3/97

2106

102

rei

2a45c

24

1,096

12

112997

4/4/97

2124

107

bic

41003

1

652

13

113003

5/2/97

2100

109

imm

779c

3

5,625

14

113007

1/1/97

2112

100

imm

773c

3

2,925

15

113012

5/5/97

2111

105

aci

41003

35

3,745

16

113013

6/1/97

2110

100

bic

41003

1

17

113024

4/7/97

2114

100

qsa

xk47

20

7,100

10

113027

5/2/97

2103

105

aci

41002

54

4,104

19

113034

5/11/97

2107

110

rei

2a45c

0

632

20

113042

1/1/97

2113

101

rei

2a44r

5

22,500

21

113045

2/7/97

2112

100

rel

2a44r

10

45,000

22

113040

2/2/97

2120

102

imm

779c

2

3,750

23

113049

4/4/97

2110

100

qsa

xk47

2

776

24

113051

6/7/97

2110

100

qsa

xk47

4

1,420

25

113055

1/14/99

2100

101

aci

4100x

6

150

26

113057

1/11/97

2111

103

aci

4100x

24

600

27

113050

4/7/89

2100

109

fea

112

10

1,400

20

113062

4/7/97

2124

107

bic

41003

10

2,430

29

113065

3/6/97

2106

102

qsa

xk47

6

2,130

30

113069

1/1/97

2109

107

imm

773c

22

31,350

652

Tabla pedido: codigo : nº secuencial que sirve de clave principal numpedido: nº de pedido. Observar que un pedido puede tener varias líneas. fechapedido : fecha del pedido clie : cliente que efectua el pedido rep : representante que tramita el pedido fab: fabricante del producto que se pide

producto : idproducto del producto que se pide. cant : cantidad que se pide del producto importe