Computer Store SQL Server Case Study

Scenario :

In this “Computer Store” SQL Server Case Study.The owner of a computer shop would like to keep track of their products with Prices.

Relational Schema :

relational schema

 

 

 

 

Table creation code :

CREATE TABLE Manufacturers (
        Code int PRIMARY KEY NOT NULL,
        Name varchar(50) NOT NULL 
);

CREATE TABLE Products (
        Code int PRIMARY KEY NOT NULL,
        Name varchar(50) NOT NULL ,
        Price int NOT NULL ,
        Manufacturer int NOT NULL 
                CONSTRAINT fk_Manufacturers_Code REFERENCES MANUFACTURERS(Code)
);

Sample dataset :

INSERT INTO Manufacturers(Code,Name) VALUES(1,'Sony');
INSERT INTO Manufacturers(Code,Name) VALUES(2,'Creative Labs');
INSERT INTO Manufacturers(Code,Name) VALUES(3,'Hewlett-Packard');
INSERT INTO Manufacturers(Code,Name) VALUES(4,'Iomega');
INSERT INTO Manufacturers(Code,Name) VALUES(5,'Fujitsu');
INSERT INTO Manufacturers(Code,Name) VALUES(6,'Winchester');

INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(1,'Hard drive',240,5);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(2,'Memory',120,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(3,'ZIP drive',150,4);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(4,'Floppy disk',5,6);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(5,'Monitor',240,1);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(6,'DVD drive',180,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(7,'CD drive',90,2);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(8,'Printer',270,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(9,'Toner cartridge',66,3);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(10,'DVD burner',180,2);

Exercises:

–1.Select the names of all the products in the store.

select Name from Products

–2. Select the names and the prices of all the products in the store.

select name,price from Products

–3. Select the name of the products with a price less than or equal to $200.

select name from Products
where price <=200

–4. Select all the products with a price between $60 and $120.

select * from Products
where price between 60 and 120

–5. Select the name and price in cents (i.e., the price must be multiplied by 100).

select name,price*100 [Price In cents]from products

–6. Compute the average price of all the products.

select Name,avg(Price) [Average Price] from Products
group by Name

–7. Compute the average price of all products with manufacturer code equal to 2

select name,avg(price) from products
where code=2
group by name

–8. Compute the number of products with a price larger than or equal to $180

select count(*) from Products
where Price >= 180

–9. Select the name and price of all products with a price larger than or equal to $180, and sort first by price (in descending order), and then by name (in ascending order).

select name,price from Products
where price >=180
order by Price desc,Name

–10. Select all the data from the products, including all the data for each product’s manufacturer.

select * from Products
inner join Manufacturers
on Products.Manufacturer=Manufacturers.Code

–11. Select the product name, price, and manufacturer name of all the products.

select Products.Name,Price,Manufacturers.Name from Products
inner join Manufacturers
on Products.Manufacturer=Manufacturers.code

–12. Select the average price of each manufacturer’s products, showing only the manufacturer’s code.

select avg(Price),Manufacturer from Products
group by Manufacturer

–13. Select the average price of each manufacturer’s products, showing the manufacturer’s name.

select avg(price),Manufacturers.Name from Products
inner join Manufacturers
on Products.Manufacturer=Manufacturers.Code
group by Manufacturers.Name

–14. Select the names of manufacturer whose products have an average price larger than or equal to $150.

select avg(price) as [Average Price],Manufacturers.name from Products
inner join Manufacturers
on Products.Manufacturer=Manufacturers.Code
group by Manufacturers.name
having avg(price) >=150

–15. Select the name and price of the cheapest product.

select Name,price from Products
where price=(select min(price) from Products)

–16. Select the name of each manufacturer along with the name and price of its most expensive product.

SELECT P.Name, P.Price, M.Name
FROM Products P INNER JOIN Manufacturers M
ON P.Manufacturer = M.Code
AND P.Price =
(
SELECT MAX(P.Price)
FROM Products P
WHERE P.Manufacturer = M.Code
)

–17. Add a new product: Loudspeakers, $70, manufacturer 2

insert into Products(code,Name,Price,Manufacturer)values(11,’Loudspeakers’,70,2)

–18. Update the name of product 8 to “Laser Printer”.

select * from Products

update Products
set Name=’Laser Priner’
where Code=8

–19. Apply a 10% discount to all products.

UPDATE Products
SET Price = Price * 0.9

–20. Apply a 10% discount to all products with a price larger than or equal to $120.

UPDATE Products
SET Price = Price * 0.9
WHERE Price >= 120

Downloads :

Computer Store SQL Dataset and Commands

I hope this tutorial will surely help you. If you have any questions or problems please let me know.

Happy Hadooping with Patrick..

Leave a Reply

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