Base De Datos

Crear Vistas

1.- Crear vista nombre de la Compañia con productos mayores a 10

Create view ca

as

Select Customers.CompanyName, [Order Details].quantity

from Customers inner join Orders

on Customers.CustomerID=Orders.CustomerID

inner join [Order Details] on [Order Details].OrderID=Orders.OrderID

where [Order Details].quantity > 10

go

Select * from ca

2.- Contar el total de compañias

Create view coni

as

Select count (companyname) as [total de compañias] from dbo.Customers

go

select * from coni

3.- Mostrar el primer nombre del empleado que realiza embarques a Francia

Create view caf

as

Select Employees.FirstName from Employees

inner join Orders on Employees.EmployeeID=Orders.EmployeeID

where ShipCountry= 'France'

go

select* from caf

Inner Join

Select Products.productname, Products.unitprice, Employees.firstname

from Orders inner join Employees

on Employees.EmployeeID=Orders.EmployeeID

inner join [Order Details]on Orders.Orderid= [Order Details].Orderid

inner join Products on Products.ProductID= [Order Details].ProductID

where Employees.firstname = 'Janet'

order by Products.productname asc

go

Procedimiento Almacenado.

1.- Donde se ingrese el “País” y se muestren el nombre de los empleados que generan ordenes de embarque a ese “país”

Create procedure p_almacenado

@nombrePais varchar(10)

as

Select Employees.FirstName from Employees

inner join Orders on Employees.EmployeeID=Orders.EmployeeID

where Orders.ShipCountry=@nombrePais

go

Exec p_almacenado '_____'

2.-

create procedure procedimiento_f

@nombreEmpleado varchar(10)

as

Select Products.productname, Products.unitprice, Employees.firstname

from Orders inner join Employees

on Employees.EmployeeID=Orders.EmployeeID

inner join [Order Details]on Orders.Orderid= [Order Details].Orderid

inner join Products on Products.ProductID= [Order Details].ProductID

where Employees.firstname = @nombreEmpleado

order by Products.productname asc

go

Exec procedimiento_f 'Janet

...