Portafolio virtual khaleda: julio 2014

Ejercicio en clase 1:ejemplo BD

script

create database Ejemplo

use Ejemplo

create table carreras

(

cod varchar (10) not null primary key,

nombre varchar (30) not null,

duracion char (4) not null

)

create table Estudiantes

(

id varchar(15) not null primary key,

nombre varchar(30) not null,

Apellidos varchar (30) not null,

Direccion char (15) not null,

edad varchar (3) not null,

sexo varchar (15) not null,

tel varchar (7) not null,

ciudad varchar (20)not null,

cod_carrera varchar (10) not null,

foreign key (cod_carrera) references carreras (cod)

)

--consultas de insercion

INSERT INTO carreras (cod,nombre,duracion)

VALUES (0102,'administracion de empresas',5)

INSERT INTO carreras (cod,nombre,duracion)

VALUES (0103,'derecho',8)

INSERT INTO carreras (cod,nombre,duracion)

VALUES (0104,'psicologia',5)

INSERT INTO carreras (cod,nombre,duracion)

VALUES (0105,'arquitectura',5)

INSERT INTO carreras (cod,nombre,duracion)

VALUES (0106,'ingenieria mecanica',5)

-- consultas de estudiantes

INSERT INTO estudiantes (id,nombre,apellidos,direccion,edad,sexo,tel,ciudad,cod_carrera)

VALUES (99020412794,'luisa','zapata','cll 76d#82f17',15,'femenino',4225698,'medellin',0102)

INSERT INTO estudiantes (id,nombre,apellidos,direccion,edad,sexo,tel,ciudad,cod_carrera)

VALUES (97110912971,'pedro','alvarez','cll 71d#81a15',16,'masculino',5830234,'medellin',0103)

INSERT INTO estudiantes (id,nombre,apellidos,direccion,edad,sexo,tel,ciudad,cod_carrera)

VALUES (98021398475,'martha','zuluaga','cll 12k#80s15',16,'femenino',5833402,'medellin',0104)

INSERT INTO estudiantes (id,nombre,apellidos,direccion,edad,sexo,tel,ciudad,cod_carrera)

VALUES (98020678496,'miguel','rincon','cll 50h#45e13',16,'masculino',4218546,'medellin',0105)

INSERT INTO estudiantes (id,nombre,apellidos,direccion,edad,sexo,tel,ciudad,cod_carrera)

VALUES (99030789546,'fernanda','ospina','cll 20u#45r20',15,'femenino',4225558,'medellin',0106)

--consultas de seleccion basica

SELECT *

FROM Estudiantes

SELECT *

FROM carreras

SELECT id, nombre

FROM estudiantes

-- consultas de seleccion con condicion

Mostrar id,nombre,edad de los estudiantes que tengan menos de 18 años

SELECT id,nombre,edad,tel

FROM estudiantes

WHERE edad < 18 and ciudad = 'medellin'

mostrar nombre de las carreras que duren mas de 4 años

SELECT nombre, duracion

FROM carreras

WHERE duracion > 4

ORDER BY nombre asc

mostrar identificacion de los estudiantes que estudian arquitectura

SELECT Estudiantes.id, Estudiantes.nombre ,carreras.nombre

FROM Estudiantes,carreras

WHERE cod_carrera = 0105 and cod=0105

ORDER BY estudiantes.nombre desc

Diagrama

Ejercicio de aplicación consultas de seleccion

Script

create database usuarios

use usuarios

create table usuarios

(

id varchar(15) not null primary key,

alias varchar(15) not null,

apellido varchar(30) not null,

nombre varchar(30) not null,

direccion char(15) not null,

edad varchar(3) not null,

ciudad varchar(15) not null,

tel_fijo varchar(7) not null,

celular varchar(10) not null,

e_mail varchar(30) not null,

sexo_f_m varchar(1) not null,

fecha_nacimiento smalldatetime not null,

estado_civil varchar(15) not null,

hijos bit not null,

desempleado bit not null,

profesion varchar(30) not null,

nivel_academico varchar(30) not null,

idioma varchar(15) not null,

ingresos_mensuales int not null,

ahorros int not null,

peso_kg smallint not null,

altura smallint not null,

color_cabello varchar(15) not null,

color_ojos varchar(15) not null,

fumador bit not null,

hobbies varchar(30) not null,

fobias varchar(30) not null,

sexualidad varchar(30) not null,

disponible bit not null

)

INSERT INTO usuarios (id,alias,apellido,nombre,direccion,edad,ciudad,tel_fijo,celular,e_mail,sexo_f_m,fecha_nacimiento ,estado_civil ,

hijos ,desempleado ,profesion ,nivel_academico , idioma , ingresos_mensuales ,ahorros ,peso_kg , altura ,color_cabello , color_ojos ,

fumador ,hobbies , fobias , sexualidad , disponible )

VALUES (35586146,'checho','ospina','sergio','cll 76D#82f17',40,'cali',4223526,3207327474,'checho@hotmail.com','m', 03/05/1965,

'casado','true','false','Ginecologo','Profesional','español',2000000,500000,65,178,'negro','cafes oscuros','false','voleibol',

'claustrofobia','heterosexual',

'false')

INSERT INTO usuarios (id,alias,apellido,nombre,direccion,edad,ciudad,tel_fijo,celular,e_mail,sexo_f_m,fecha_nacimiento ,estado_civil ,

hijos ,desempleado ,profesion ,nivel_academico , idioma , ingresos_mensuales ,ahorros ,peso_kg , altura ,color_cabello , color_ojos ,

fumador ,hobbies , fobias , sexualidad , disponible)

VALUES (34988937,'majo','arango','maria jose','cll 77C#83g17',35,'cartagena',5833402,3114376965,'majolamejor@hotmail.com','f',

25/11/1970,'soltera','false','false','cantante','bachillerato completo','ingles',600000,1000000,54,154,'rojo','azules','true',

'tocar bateria y cantar','aracnofobia','homosexual','true')

INSERT INTO usuarios (id,alias,apellido,nombre,direccion,edad,ciudad,tel_fijo,celular,e_mail,sexo_f_m,fecha_nacimiento ,estado_civil ,

hijos ,desempleado ,profesion ,nivel_academico , idioma , ingresos_mensuales ,ahorros ,peso_kg , altura ,color_cabello , color_ojos ,

fumador ,hobbies , fobias , sexualidad , disponible)

VALUES (1036647610,'el mono','arrieta','alejandro','cra 65 Nº34-98',22,'bogota',2345587,3218481801,'elmonorico@gmail.com','m',

01/12/1992,'soltero','true', 'true','mecanico','bachillerato completo','español',700.000,100.000,60,160,'castaño claro','verdes',

'true','jugar futbol','acarofobia','heterosexual','true')

INSERT INTO usuarios (id,alias,apellido,nombre,direccion,edad,ciudad,tel_fijo,celular,e_mail,sexo_f_m,fecha_nacimiento, estado_civil,

hijos,desempleado,profesion,nivel_academico, idioma, ingresos_mensuales,ahorros,peso_kg, altura,color_cabello, color_ojos,

fumador,hobbies,fobias, sexualidad, disponible)

VALUES (35689417,'naty','hernandez','natalia','cll 23ANº56C-76',34,'Medellin',2865974,3104002820,'Natyhc89@hotmail.com','f',23/06/1975,

'casada','true','false','licenciada','profesional','portugues',1400000,3000000,60,164,'negro','cafe claro','true','jugar tennis',

'agliofobia','heterosexual','false')

INSERT INTO usuarios (id,alias,apellido,nombre,direccion,edad,ciudad,tel_fijo,celular,e_mail,sexo_f_m,fecha_nacimiento ,estado_civil ,

hijos ,desempleado ,profesion ,nivel_academico , idioma , ingresos_mensuales ,ahorros ,peso_kg , altura ,color_cabello , color_ojos ,

fumador ,hobbies , fobias , sexualidad , disponible)

VALUES (102569866,'pipe','arroyo','felipe','crra32BNº74-56',36,'cali',5674321,3138905342,'pipebueno@gmail.com','m',02/02/1991,

'soltero','true','true','licenciado','profesional completo','español',600.000,100.000,76,183,'rubio','azules','false','leer',

'altofobia','homosexual','true')

--consultas de seleccion con condicion

mostrar id, nombre, tel_fijo y direccion de los hombres desempleados mayores de 25 años.

select id, nombre, tel_fijo, direccion

from usuarios

where sexo_f_m='m' and desempleado='true' and edad>25

mostrar nombre y la edad de las mujeres mayores de 30 años fumadoras residente de medellin.

select nombre, edad

from usuarios

where edad>30 and fumador='true' and ciudad='medellin'

mostrar id, estatura,ingresos_mensuales de los rubios ojos azules y mas de 1.80 de estatura

select id, altura, ingresos_mensuales

from usuarios

where color_cabello='rubio' and altura>180

order by ingresos_mensuales desc

mostrar nombre,alias, y nivel de ingresos de los caleños homosexuales, nofumadores, con hijos,

ordenados ascendentemente por nombre.

select nombre, alias, ingresos_mensuales

from usuarios

where ciudad='cali' and sexualidad='homosexual' and fumador='false' and hijos='true'

order by nombre asc

Nombre, apellidos y teléfono, de las personas que nacieron después de 01-enero-

1990, ordenados descendentemente por apellido.

select nombre,apellido,tel_fijo

from usuarios

where fecha_nacimiento > 01/01/1990

order by apellido desc

--cosulta ordenador or,con ordenamiento

mostrar el nombre,ciudad y apellido de las personas con ciudad medellin o bogota ordenados ascendentemente por apellido

select nombre, apellido,ciudad

from usuarios

where ciudad='medellin' or ciudad='bogota'

order by apellido asc

mostrar nombre,edad de las personas mayores de 30 y menores de 1.80 de estatura y que su idioma sea español y ordenarlos por

edades ascendentes.

select nombre,edad,altura,idioma

from usuarios

where idioma='español' or edad>30 or altura<180

order by edad asc

mostrar id,nombre,edad de las personas que sean diferentes de 30 años y sean hombres ordenados por edad de manera

descendente

select id,nombre,edad,sexo_f_m

from usuarios

where edad<>30 and sexo_f_m='m'

order by edad desc

mostrar nombre,hijos,telefono y apellido de las personas con fobias igual a o sinhijos y edades mayores a 30

select nombre,apellido,tel_fijo,edad,hijos

from usuarios

where fobias='altofobias' or hijos='false' and edad>30

mostrar el nombre,ciudad y apellido de las personas con ciudad medellin o bogota, y edad diferente de 30,

estatura mayor 1.50 y peso menor a 60 y con hijos

select nombre,apellido,ciudad,edad,altura,peso_kg,hijos

from usuarios

where edad<>30 and altura>150 and peso_kg<60 and ciudad='medellin' or ciudad='bogota'

diagrama

Pantallazos

ejercicio 2: exposiciones

BASE DE DATOS

Diagrama

create database supermercado

use supermercado

create table proveedores

(

nit varchar(15) not null primary key,

nombre varchar (30) not null,

dir varchar (15) not null,

tel varchar (7) not null,

)

create table productos

(

cod varchar (15) not null primary key,

nombre varchar(30) not null,

tipo varchar (20) not null,

precio_compra int not null,

stock int not null,

nit_provee varchar (15) not null,

foreign key (nit_provee) references proveedores (nit)

)

create table ventas

(

id_vta varchar(15) not null primary key,

fecha smalldatetime not null,

cliente varchar (30)not null,

id_producto varchar(15) not null,

valor int not null,

cantidad smallint not null,

total int not null,

foreign key (id_producto) references productos (cod)

)

insert into proveedores (nit,nombre,dir,tel)

values (3569,'alberto','cll 76d#82f17',4223526)

insert into proveedores (nit,nombre,dir,tel)

values (3670,'alejandra','cll 80h#62s14',4218918)

insert into proveedores (nit,nombre,dir,tel)

values (3771,'luis','cll 23t#56j12',2343334)

insert into proveedores (nit,nombre,dir,tel)

values (3872,'ana maria','cll 54g#79s40',4225689)

insert into proveedores (nit,nombre,dir,tel)

values (3973,'roberto','cll 74d#81f10',4215896)

insert into productos (cod,nombre,tipo,precio_compra,stock,nit_provee )

values (123,'alpinito','lacteo',800,1000,3569)

insert into productos (cod,nombre,tipo,precio_compra,stock,nit_provee )

values (124,'lentejas','granos',1200,10000,3670)

insert into productos (cod,nombre,tipo,precio_compra,stock,nit_provee )

values (125,'arroz','granos',1100,10000,3771)

insert into productos (cod,nombre,tipo,precio_compra,stock,nit_provee )

values (126,'alpinette','lacteo',2000,700,3872)

insert into productos (cod,nombre,tipo,precio_compra,stock,nit_provee )

values (127,'papitas','mecato',800,100,3973)

insert into ventas (id_vta,fecha,cliente,id_producto,valor,cantidad,total )

values (2021,12/05/2014,'pedro',123,800,7,5600)

insert into ventas (id_vta,fecha,cliente,id_producto,valor,cantidad,total )

values (2022,04/02/2014,'luisa maria',124,1200,3,3600)

insert into ventas (id_vta,fecha,cliente,id_producto,valor,cantidad,total )

values (2023,01/01/2014,'doris',125,1100,2,2200)

insert into ventas (id_vta,fecha,cliente,id_producto,valor,cantidad,total )

values (2024,03/04/2014,'luisa',126,2000,7,14000)

insert into ventas (id_vta,fecha,cliente,id_producto,valor,cantidad,total )

values (2025,07/05/2014,'valentina',127,800,2,1600)

-- consulta grupo 2: actualizaciones de registros y sentencia update

UPDATE ventas

SET valor = 2000

WHERE Fecha > 01/05/1996

--ejecicio 1

en la tabla de proveedores la direccion y el telefono utilizando como condicion uno delos nit de su bd

UPDATE proveedores

SET dir = 'cr56#34',

tel = 4444444

WHERE nit = 3771

--ejercicio 2

modificar en la tabla ventas el valor, de modo que quede igual a 0

y cantidad igual a 888 utilizando como condicion la del total

UPDATE ventas

SET valor=0,

cantidad=888

WHERE total=1600

--ejercicio 3

modificar el precio de venta del producto llamado arroz por 2000

UPDATE productos

SET precio_compra = 2000

WHERE nombre='arroz'

--consultas de elimnacion: grupo 1

delete from ventas

where cliente='pedro'

truncate table "ventas"

drop table ventas

--ejercicio 1

eliminar todos las ventas menores a 30000

delete from ventas

where total < 30000

--ejercicio 2

eliminar los datos de la tabla venta

truncate table "ventas"

--ejercicio 3

eliminar la tabla ventas

drop table ventas

-- sentencia alter: grupo 3

alter table ventas

add iva decimal null

alter table productos

drop column tipo

alter table proveedores

alter column nombre varchar (60) not null

--ejercicio 1

agregar en la tabla proveedores una columna llamada sexo

alter table proveedores

add sexo varchar(15) null

--ejercicio 2

alterar la longitud en la tabla de ventas en la columna cantidad = varchar(60)

alter table ventas

alter column cantidad varchar(60) not null

--ejercicio 3

eliminar la columna direccion

alter table proveedores

drop column dir

--consultas agregadas grupo 4

--ejercicio 1

realiza el promedio del total de ventas

select avg(total)

from ventas

--ejercicio 2

cuenta cuantos clientes hay en la tabla de ventas

select count (cliente)

from ventas

--ejercicio3

saca el maximo y minimo valor del precio de la compra

select max(precio_compra)

from productos

select min(precio_compra)

from productos

--ejercicio 4

suma la cantidad de ventas realizadas

select sum(cantidad*valor)

from ventas

--consultas de agregadas grupo 5

--ejemplo 1

Select Cliente, count(*) as cantidad

from ventas

where total>5000

group by cliente

--ejemplo 2

select cliente,sum(valor)

from ventas

group by cliente

having sum(valor)>1500;

--ejercicio 1

agrupas los productos que valgan menor de 2000

select stock,count(*) as precio_compra

from productos

where precio_compra<2000

group by stock

--ejercicio 2

sumar las cantidades de la tabla productos que valgan menor de 3000 usando having

select cantidad,sum(valor)

from ventas

group by cantidad

having sum(valor)<3000;

--sentencia distinct y all : grupo 6

--ejemplo 1

select distinct "cliente"

from "ventas"

--ejemplo 2

select total from ventas

union all

select nit_provee from productos;

--ejercicio 1

eliminar nombres repetidos de la tabla ventas

select distinct "cliente"

from "ventas"

--ejercicio 2

mostrar los nombres de los productos y nombres de los clientes que los compraron

select nombre from productos

union all

select cliente from ventas;

--palabras claves o reservadas: grupo 7

--ejemplo 1

select *

from productos

where nombre in ('alpinito','lentejas');

--ejemplo 2

select *

from productos

where nombre like 'a%'

--'a%' empieza por la a,'%a' finalice,'%a%' en el medio

select *

from productos

where nombre like '%s'

select *

from productos

where nombre like '%pi%'

select *

from ventas

where cliente like '%is%'

select *

from productos

where nombre not like '%te%'

select *

from ventas

where cliente like 'l%a'

--columnas calculadas :grupo 8

--ejemplo 1 operadores aritmeticos

select "valor" * "cantidad" as "total"

from "ventas"

--ejemplo 2 concatenacion

select 'bienvenidos: ' + cliente

from ventas

--ejemplo 3 alias

select "prod"."nombre" "nom"

from "productos" "prod";

--ejercicio 1: escriba el alias del registro tipo en productos y a su vez el alias de productos

select "prod"."tipo" "tip"

from "productos" "prod"

--ejercicio 2: concatenar el tipo del producto

select 'el tipo de procucto es: ' + tipo

from productos

--ejercicio 3: sumar a la cantidad 10 unidades

select "cantidad" + 10 as "total"

from "ventas"

--ejercicio 4:dividir precio_compra entre 2

select "precio_compra" / 2 as "total"

from "productos"

--ejercicio 5:restar cantidad el 7% y llamar al resultado porcentaje

select "total" * 0.07 as "porcentaje"

from "ventas"

--union,excpt e intersect: grupo 9

--ejemplo 1

select cod

from productos

union

select id_vta

from ventas

--ejemplo 2

select nombre

from productos

except

select cliente

from ventas

--ejemplo 3

select nit_provee

from productos

intersect

select nit

from proveedores

--ejercicio 1 : usar el comando union, para unir el codigo de los productos con el codigo productos en la tabla ventas

select cod

from productos

union

select id_producto

from ventas

--ejercicio 2 : usar el comando intersect para mostrar a cantidad de ventas y el stock de la tabla productos

select cantidad

from ventas

intersect

select stock

from productos

--ejercicio 3 : usar l comando except para mostrar en nit de la tabla de proveedores y el nit de la tabla productos

select nit_provee

from productos

intersect

select nit

from proveedores

KATHY VANESSA ANAYA CONTRERAS

ADRIANA ASPRILLA MURILLO

11º4

ejercicio 3: taller consultas de selección

Diagrama BD

create database tallerconsultas

use tallerconsultas

create table articulos

(

cod_articulo varchar(10) not null primary key,

nombre_art varchar(20) not null,

precio bigint not null

)

create table zonas

(

zona_vtas varchar(10) not null primary key,

nom_zona varchar(20) not null

)

create table clientes

(

cod varchar(10) not null primary key,

nombre varchar(20) not null,

direccion char(15) not null,

telefono varchar(10) not null,

ciudad varchar(20) not null,

fax varchar(10) not null,

descuento real not null,

zona_vtas varchar(10) not null,

foreign key (zona_vtas) references zonas(zona_vtas)

)

create table pedidos

(

numero varchar(10) not null primary key,

cod_cliente varchar(10) not null,

cod_articulo varchar(10) not null,

unidades varchar(10) not null,

fecha smalldatetime not null,

foreign key (cod_cliente) references clientes(cod),

foreign key (cod_articulo) references articulos(cod_articulo)

)

insert into zonas(zona_vtas,nom_zona)

values ('z01','oriente')

insert into zonas(zona_vtas,nom_zona)

values ('z02','occidente')

insert into zonas(zona_vtas,nom_zona)

values ('z03','sur')

insert into zonas(zona_vtas,nom_zona)

values ('z04','norte')

insert into zonas(zona_vtas,nom_zona)

values ('z05','noroccidente')

insert into clientes (cod,nombre,direccion,telefono,ciudad,fax,descuento,zona_vtas)

values ('C01','daniel','cll76D#82f17',4223526,'Medellin',4325689,10,'z05')

insert into clientes (cod,nombre,direccion,telefono,ciudad,fax,descuento,zona_vtas)

values ('C02','Ana Maria','cr85a#7527',5234233,'Cali',5833402,5,'z02')

insert into clientes (cod,nombre,direccion,telefono,ciudad,fax,descuento,zona_vtas)

values ('C03','Roberto','cll34#1637',2345205,'Bogotá',4218918,10,'z03')

insert into clientes (cod,nombre,direccion,telefono,ciudad,fax,descuento,zona_vtas)

values ('C04','Isabella','cr87#77cc27',2343334,'Medellin',2356981,5,'z03')

insert into clientes (cod,nombre,direccion,telefono,ciudad,fax,descuento,zona_vtas)

values ('C05','Mario','cll87n#65a14',2824233,'Cucuta',5268947,20,'z05')

insert into articulos(cod_articulo,nombre_art,precio)

values ('A01','sala',2500000)

insert into articulos(cod_articulo,nombre_art,precio)

values ('A02','comedor',1700000)

insert into articulos(cod_articulo,nombre_art,precio)

values ('A03','alcoba',4000000)

insert into articulos(cod_articulo,nombre_art,precio)

values ('A04','silla',700000)

insert into articulos(cod_articulo,nombre_art,precio)

values ('A05','espejo',500000)

insert into pedidos(numero,cod_cliente,cod_articulo,unidades,fecha)

values ('P01','C05','A01',100,02/05/1999)

insert into pedidos(numero,cod_cliente,cod_articulo,unidades,fecha)

values ('P02','C02','A01',30,07/07/2000)

insert into pedidos(numero,cod_cliente,cod_articulo,unidades,fecha)

values ('P03','C04','A03',40,09/09/2001)

insert into pedidos(numero,cod_cliente,cod_articulo,unidades,fecha)

values ('P04','C04','A02',20,01/10/2007)

insert into pedidos(numero,cod_cliente,cod_articulo,unidades,fecha)

values ('P05','C05','A05',200,01/01/2010)

-- consulta de seleccion--

--1--

select *

from clientes

order by cod desc

--2--

select nombre,direccion,descuento

from clientes

where descuento>5

order by nombre desc

--3—

select cod, nombre,cod_articulo

from clientes,pedidos

where unidades<50 or (cod_articulo= 'A03' or cod_articulo='A04')

order by cod_articulo asc

--consultas de actualizacion--

--4--

update articulos

set precio=650000

where nombre_art='silla'

select *

from articulos

--5--

update clientes

set direccion='cll89j#65s89',telefono=4589874

where cod='C03'

select *

from clientes

--sentencia alter--

--6--

alter table zonas

alter column nom_zona varchar(100) not null

--7--

alter table zonas

add ciudad varchar(30) null,barrio varchar(30) null

select *

from zonas

--8--

alter table zonas

drop column ciudad

select *

from zonas

--like-in-not-join--

--9--

select nombre,direccion

from clientes

where nombre like 'a%'

--10--

select cod,nombre,nom_zona

from clientes join zonas on clientes.zona_vtas=zonas.zona_vtas

--11—

select nombre_art

from articulos

where precio in (500000,700000)

--12--

select ciudad,nombre,descuento

from clientes

where ciudad not like 'Medellin' and descuento=5

--CONSULTAS AGREGADAS (AVG-COUNT-MAX-MIN-SUM)--

--13--

Select ciudad, count(*) as clientes

from clientes

where ciudad='Medellin'

group by ciudad

--14--

select min (precio)

from articulos

select max (precio)

from articulos

--15--

select sum (precio) as total

from articulos

--CONSULTAS AGREGADAS (Clausula GROUP BY - Clausula HAVING)--

--16--

select cod_cliente, count(*) as unidades

from pedidos

having unidades = 20

group by cod_cliente

--SENTENCIA REPLACE--

--17--

select precio * 0.05 as porcentaje

from articulos

--CONSULTAS DE ELIMINACION--

--18--

Select *

From clientes

Truncate table clientes

--19--

Select nom_zona

From zonas

Truncate table "noroccidente"

--20--

alter table zonas

drop column barrio

select *

from zonas

Adriana asprilla murillo

Kathy anaya contreras 11º4

ejercicio 4: ejemplo BD

Ejercicio en clase