Base de datos: tienda
CREATE TABLE Manufacturers (
Code INTEGER PRIMARY KEY NOT NULL,
Name CHAR(50) NOT NULL
);
CREATE TABLE Products (
Code INTEGER PRIMARY KEY NOT NULL,
Name CHAR(50) NOT NULL ,
Price REAL NOT NULL ,
Manufacturer INTEGER NOT NULL,
CONSTRAINT fk_Manufacturers_Code FOREIGN KEY (Manufacturer) REFERENCES
Manufacturers(Code)
);
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 Manufacturers(Code,Name) VALUES(7,'Bose');
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);
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES(11,'CD',5,2);
1. Seleccione los nombres de todos los productos de la tienda.
SELECT Name FROM Products;
2. Seleccione los nombres y los precios de todos los productos de la tienda.
SELECT Name, Price FROM Products;
3. Seleccione el nombre de los productos con un precio menor o igual a $ 200.
SELECT Name FROM Products WHERE Price <= 200;
4. Seleccione todos los productos con un precio entre $ 60 y $ 120.
/* With AND */
SELECT * FROM Products
WHERE Price >= 60 AND Price <= 120;
/* With BETWEEN */
SELECT * FROM Products
WHERE Price BETWEEN 60 AND 120;
5. Seleccione el nombre y el precio en centavos (es decir, el precio debe multiplicarse por 100).
/* Without AS */
SELECT Name, Price * 100 FROM Products;
/* With AS */
SELECT Name, Price * 100 AS PriceCents FROM Products;
6. Calcule el precio promedio de todos los productos.
SELECT AVG(Price) FROM Products;
7. Calcule el precio promedio de todos los productos con código de fabricante igual a 2.
SELECT AVG(Price) FROM Products WHERE Manufacturer=2;
8. Calcule la cantidad de productos con un precio mayor o igual a $ 180.
SELECT COUNT(*) FROM Products WHERE Price >= 180;
9. Seleccione el nombre y precio de todos los productos con un precio mayor o igual a $ 180 y
ordene primero por precio (en orden descendente) y luego por nombre (en orden ascendente).
SELECT Name, Price
FROM Products
WHERE Price >= 180
ORDER BY Price DESC, Name ASC;
10. Seleccione todos los datos de los productos, incluidos todos los datos del fabricante de cada
producto.
/* Without LEFT JOIN */
SELECT * FROM Products, Manufacturers
WHERE [Link] = [Link];
/* With LEFT JOIN */
SELECT *
FROM Products LEFT JOIN Manufacturers
ON [Link] = [Link];
11. Seleccione el nombre del producto, el precio y el nombre del fabricante de todos los
productos.
/* Without INNER JOIN */
SELECT [Link], Price, [Link]
FROM Products, Manufacturers
WHERE [Link] = [Link];
/* With INNER JOIN */
SELECT [Link], Price, [Link]
FROM Products INNER JOIN Manufacturers
ON [Link] = [Link];
12. Seleccione el precio promedio de los productos de cada fabricante, mostrando solo el código
del fabricante.
SELECT AVG(Price), Manufacturer
FROM Products
GROUP BY Manufacturer;
13. Seleccione el precio promedio de los productos de cada fabricante, mostrando el nombre del
fabricante.
/* Without INNER JOIN */
SELECT AVG(Price), [Link]
FROM Products, Manufacturers
WHERE [Link] = [Link]
GROUP BY [Link];
/* With INNER JOIN */
SELECT AVG(Price), [Link]
FROM Products INNER JOIN Manufacturers
ON [Link] = [Link]
GROUP BY [Link];
14. Seleccione los nombres de los fabricantes cuyos productos tienen un precio promedio mayor o
igual a $ 150.
/* Without INNER JOIN */
SELECT AVG(Price), [Link]
FROM Products, Manufacturers
WHERE [Link] = [Link]
GROUP BY [Link]
HAVING AVG(Price) >= 150;
/* With INNER JOIN */
SELECT AVG(Price), [Link]
FROM Products INNER JOIN Manufacturers
ON [Link] = [Link]
GROUP BY [Link]
HAVING AVG(Price) >= 150;
15. Seleccione el nombre y el precio del producto más barato.
SELECT name,price
FROM Products
ORDER BY price ASC
LIMIT 1
/* With a nested SELECT */
/* WARNING: If there is more than one item with the cheapest price it will select them both */
SELECT Name, Price
FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products);
16. Seleccione el nombre de cada fabricante junto con el nombre y precio de su producto más
caro.
/* With a nested SELECT and without INNER JOIN */
SELECT [Link], [Link], [Link]
FROM Products A, Manufacturers F
WHERE [Link] = [Link]
AND [Link] =
(
SELECT MAX([Link])
FROM Products A
WHERE [Link] = [Link]
);
/* With a nested SELECT and an INNER JOIN */
SELECT [Link], [Link], [Link]
FROM Products A INNER JOIN Manufacturers F
ON [Link] = [Link]
AND [Link] =
(
SELECT MAX([Link])
FROM Products A
WHERE [Link] = [Link]
);
17. Seleccione el nombre de cada fabricante que tenga un precio promedio superior a $ 145 y
contenga al menos 2 productos diferentes.
Select [Link], Avg([Link]) as p_price, COUNT([Link]) as m_count
FROM Manufacturers m, Products p
WHERE [Link] = [Link]
GROUP BY [Link]
HAVING p_price >= 150 and m_count >= 2;
18. Agregue un nuevo producto: Loudspeakers, $70, manufacturer 2.
INSERT INTO Products( Code, Name , Price , Manufacturer)
VALUES ( 11, 'Loudspeakers' , 70 , 2 );
19. Actualice el nombre del producto 8 a "Laser Printer".
UPDATE Products
SET Name = 'Laser Printer'
WHERE Code = 8;
20. Aplicar un 10% de descuento a todos los productos.
UPDATE Products
SET Price = Price - (Price * 0.1);
21. Aplique un descuento del 10% a todos los productos con un precio mayor o igual a $ 120.
UPDATE Products
SET Price = Price - (Price * 0.1)
WHERE Price >= 120;
Base de datos de muestra de Salika (alquiler de DVD)
msql -u root -p
SOURCE C:/[Link];
SOURCE C:/[Link];
