Database Exercises

Help get a complete picture of the information on the porosity code, product products and product categories.

create the Product_Category view as (
select P.Emer as EmerProduct, P.ProduktID, K.Emer as EmerKategory
from Product P internal join Category K
in P.CategoryID = K.ID)

Build a complete picture of the information on the products, the product names and the total selling price.

create Product_Sear view as (
select P.Emer, sum (o.Sasia * p.Price) as Sales from
The internal O message joins the Compounds in O.PorosiID = Compounds.PorosiID

internal coupling Product P in P.ProduktID = Consists.ProduktID
group by P.Emer)

Procedure Build a client that takes the Customer’s Input id (is email) as a parameter and displays the number of purchases he has made. Tetons procedure.

create the History_Purchase procedure (@id_klient varchar (75))
like
begin
select the count (porosiid) as no.
where K.EmailKlient = @ id_klient;
end

exec Histori_Blerjes ‘[email protected]

Build a procedure that displays the number of customers who are from Albania.

create the State procedure (@nr_kientesh int outside)
like
begin
select @ nr_kientesh = number (*) from Client join internation dark sh in Client.EmailClient = Sh.EmailClient
where sh.State = ‘Albania’
end
declare @nr int
execute State of the Union ashtray abroad
select @nr

-Build a function that takes as a parameter the id of the category and finds the number of products that are part of it


create the prod_Category function (_id_category int)
turns int as
begin
declare intnr int;
select @ nr = count (*) from Product p interior join Category k in p.CategoryID = k.ID
ku [email protected]_kategori
return .nr
end

select dbo. prod_Category (5)


Build a funx that finds the number of products at a higher price than the one I got as a parameter


create the Cmim_About function (December)
returns int
Firstly
declare intnr int;
select @ nr = count (ProduktID) from the product where the price>. price

return .nr
end

select dbo.Cmim_More (10)

Display the list of products and prices using the WHITE object Product – price

declare
roduproduct_name VARCHAR (MAX),
EClist_price DECIMAL;
DECLARE cursor_product CURSOR
P FORR CHOICE
name,
Price
FROM
product;
OPEN cursor_product;
FET NXT FROM cursor_product INTO
ProduktProduct name,
Ista Price list;
WHILE AND FETCH_STATUS = 0
BEGIN
PRINT @product_name + ‘-‘ + CAST (istlist_price AS varchar);
FET NXT FROM cursor_product INTO
ProduktProduct name,
Ista Price list;
END;
CLOSE cursor_product;
DEALLOCATE cursor_product;

Build a trigger instead of stopping other additions to the vendor table
Trigger instead


create cause stop_support_seller
in the seller
instead of inserted
like
begin
print ‘You cannot add data to the Sell’ table;
end

inserts in Salesman (EmailSaler, Emer, Surname)
values ​​(‘[email protected] ‘oni’ oto ‘);

Build a tiger for the chart seller that will be executed every time we update in this table.


cause the seller
in the seller
after updating
like
begin
print ‘The change was successful’;
end

update Vendor set Emer = ‘Joni’ where EmailShitesi=’[email protected]

clear transaction which tends to make a change in the product table

start trying
start the transaction
update the product
set price = price / 0
where ProduktID = 1;
perform transactions;
end try

start catching
return transaction;
raiserror (‘Change failed’, 14.1);
end capture

Implicit transactions which tends to make a change in the product table

activate implicit_transactions
start trying
update the product
set price = price + 11
where ProduktID = 1;
perform transactions;
end try

start catching
return transaction;
raiserror (‘The change was successful’, 14,1);
end capture

In the table of manufacturing products an index that will be applied to the colonist is displayed and the index availability will be created for Service ID 1 and 3


create the ind_product index
in Product (Emer, Availability)
where ProduktId in (1,3)

Create a function that takes the product name as a parameter and displays the product id as a result.
CREATE FUNCTION [dbo]. [Ufn_GetIdFor_Prod]
(
PemerProd as varchar (200)
)
RETURNS int
BEGIN
RETURN
(
 
SELECT dbo.Products.ProductID
FROM dbo.Products
where dbo.Products.ProductName = @ emerProd
)
END
 

Create a function that takes the year as a parameter and displays as a result the products with the 5 largest quantities that were ordered during 1998 by the suppliers.
CREATE FUNCTION dbo. top_5_best_sellers (INyear INT)
RETURNS TABLE
AS
RETURN
(
SELECT TOP 5
SUM (quantity) AS quantity_ordered,
a.ProductID,
ProductName,
CompanyName AS Supplier
FROM [order details] a INNER JOIN products b
ON a.productid = b.productid
INNER JOIN suppliers c ON c.supplierid = b.supplierid
INNER JOIN orders d ON d.orderid = a.orderid
AND DATEPART (YEAR, OrderDate) = .year

GROUP BY a.productid, productname, CompanyName
ORDER BY 1 DESC
)

Create a function that takes as the state and displays the surname of the employee and the total amount that each employee has made from the state that is set as the ountcountry parameter according to 1996, 1997 and 1998.
CREATE FUNCTION fn_listAnualAmounts (ountcountry NVARCHAR)
RETURNS TABLE
AS
RETURN
(
SELECT e.LastName,
SUM (CASE WHEN YEAR (o.OrderDate) = ‘1996’
THEN (od.UnitPrice – od.Discount) * od.quantity
END) as total_1996,
SUM (CASE WHEN YEAR (o.OrderDate) = ‘1997’
THEN (od.UnitPrice – od.Discount) * od.quantity

END) as total_1997,
SUM (CASE WHEN YEAR (o.OrderDate) = ‘1998’
THEN (od.UnitPrice – od.Discount) * od.quantity
END) as total_1998
FROM [Order Details] from LEFT JOIN
Orders o
ON o.OrderID = od.OrderID LEFT JOIN
Employees e
ON e.EmployeeID = o.EmployeeID
where Country = ountcountry
GROUP BY e.EmployeeId, e.LastName;
)
GO

Create an SP that displays all employees, and the state (ShipCountry) from which they have made the most orders.
Create PROCEDURE Employee_Top
neither
BEGIN
SELECT O1.EmployeeID, O1.ShipCountry, COUNT () AS Orders FROM Orders O1 GROUP BY O1.EmployeeID, O1.ShipCountry HAVING COUNT () = (SELECT TOP 1 COUNT (*) AS Orders
FROM Orders O2
WHERE O2.EmployeeID = O1.EmployeeID
GROUP BY O2.EmployeeID, O2.ShipCountry
ORDER BY Orders DESC
)
ORDER BY O1.EmployeeID
END

Create an SP where it takes the client code as a parameter and displays CategoryName, [Description], ProductName, UnitPrice, UnitsInStock for which this client has placed an order.
CREATE PROCEDURE CustomerProductDetails
(
@p_CustomerID NVARCHAR (10)
)
AS
BEGIN
SELECT CAT.CategoryName, CAT. [Description],
P.ProductName, P.UnitPrice, P.UnitsInStock
FROM Orders O
INNER JOIN [Order Details] OD
ON O.OrderID = OD.OrderID
INNER JOIN Products P
ON OD.ProductID = P.ProductID
INNER JOIN Categories CAT
ON P.CategoryID = CAT.CategoryID
where [email protected]_CustomerID
END

Posted in C++

admin

Leave a Reply

Your email address will not be published. Required fields are marked *

Next Post

Backup Strategy

Mon May 18 , 2020
Exercise 1: Backup Strategy – Design Purpose: Creating a maintenance plan. scenario The NorthWind administrator has asked you to build a recovery strategy for the NorthWind database. The strategy must meet the following conditions: The strategy should use reusable backup devices Log Log files must be copied (back up). Full […]

You May Like