Database exercises

Create a view where you can display the Product Category and the number of orders made by
  employees living in the USA


create view CategoryOrdersUSA as (
SELECT Categories.CategoryName, COUNT(Orders.OrderID) AS NR
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID
INNER JOIN
Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE (Employees.Country = N’USA’)
GROUP BY Categories.CategoryName)

Create a view where you can display the list of names of employees who have placed orders with

  product named ‘Chai’

create view EmployeeProduct as
(SELECT distinct Products.ProductName, Employees.FirstName + ‘ ‘ +
Employees.LastName as Emri
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
INNER JOIN
Products ON [Order Details].ProductID = Products.ProductID
INNER JOIN
Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE (Products.ProductName = N’CHAI’))

Create a view where you can display products with the 5 largest quantities that have been ordered during
  1998 by suppliers


create view TopFiveProducts as (
SELECT TOP 5 a.ProductID,
ProductName,
CompanyName AS Supplier,
SUM(quantity) AS quantity_ordered
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)= 1998
GROUP BY a.productid, productname, CompanyName
order by SUM(quantity) desc)

Modify the view by adding the criterion so that the sum of the quantities of products is greater
  se 450.


alter view TopFiveProducts as(
SELECT TOP 5 a.ProductID,
ProductName,

CompanyName AS Supplier,
SUM(quantity) AS quantity_ordered
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)= 1998
GROUP BY a.productid, productname, CompanyName
having SUM(quantity) >450
order by SUM(quantity) desc)
select * from TopFiveProducts

Create a view where you can display the names of employees, years of employment for those who are
  employed for more than 27 years.


create view TopEmployee as
(select EmployeeID, FirstName, LastName, HireDate , DATEDIFF(year, HireDate ,
GETDATE()) as Years
from Employees where DATEDIFF(year, HireDate , GETDATE()) > 27)
select * from TopEmployee

Create a view where you display all the products, the price and the region of the supplier
  have no furniture from the USA region.


create view SuppliersNotUSA as (SELECT productname, unitprice, suppliers.Region
from products inner join
suppliers on products.supplierID=suppliers.supplierID where suppliers.region
<>’USA’)
select * from SuppliersNotUSA

Delete some of the views you have created
drop view TenProd
drop view SuppliersNotUSA
drop view TopEmployee
drop view EmployeeProduct

index clustered
CREATE TABLE parts(
part_id INT NOT NULL,
part_name VARCHAR(100)
);
INSERT INTO
parts(part_id, part_name)
VALUES
(1,’Frame’),
(2,’Head Tube’),

(3,’Handlebar Grip’),
(4,’Shock Absorber’),
(5,’Fork’);
SELECT part_id, part_name
FROM parts
WHERE part_id = 5;
CREATE CLUSTERED INDEX ix_parts_id
ON parts (part_id);

index unclustered
CREATE TABLE Users (
users_id INT IDENTITY (1, 1) PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255) NOT NULL,
phone VARCHAR (25),
email VARCHAR (255) NOT NULL,
street VARCHAR (255),
city VARCHAR (50),
state VARCHAR (25),
zip_code VARCHAR (5)
);
INSERT INTO
Users (first_name,last_name,email)
VALUES
(‘Andrea’,’Torres’,’[email protected]’),
(‘Anita’,’Taras’,’[email protected]’),
(‘John’,’Muko’,’[email protected]’),
(‘Micky’,’Liro’,’[email protected]’),
(‘Albert’,’Trini’,’[email protected]’)
select * from Users
SELECT
users_id,
first_name,
last_name
FROM
users
WHERE
last_name = ‘Torres’ AND
first_name = ‘Andrea’;
CREATE INDEX ix_usera_name
ON users(last_name , first_name);

index unik
SELECT users_id,email
FROM users
WHERE email = ‘[email protected]’;

SELECT email, COUNT(email) as Nr
FROM users
GROUP BY email
HAVING COUNT(email) > 1;
CREATE UNIQUE INDEX ix_user_email
ON users(email);

index te filtruar
update users
set phone = ‘(281) 363-3305’
where Users.users_id = 1;
update users
set phone = ‘(281) 363-3309’
where Users.users_id = 2;
update users
set phone = ‘(281) 363-3310’
where Users.users_id = 3;
update users
set phone = ‘(281) 363-3311’
where Users.users_id = 4;
CREATE INDEX ix_user_phone
ON users(phone)
WHERE phone IS NOT NULL;
SELECT first_name, last_name, phone
FROM users
WHERE phone = ‘(281) 363-3308’;

Delete some of the indexes you created


Drop index users.ix_user_phone
Drop index users.ix_user_email

Posted in C++

admin

Leave a Reply

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

Next Post

Router

Mon Apr 20 , 2020
At the conceptual level, we can think of the global internet as a network in the form of a black box that connects millions together /billions of computers worldwide. We will only address how a message from the application layer on one computer reaches the application layer on another computer. […]

You May Like