Continuing with the tutorial database data (see Part 1
,
see Part 2) in this third part I will focus only building exercises with their results so they can serve as an example.
SQL statement: SELECT
art. NAME, art.
PRICE, fab. NAME
Nombre_Fabricante ARTICLES
from art, manufacturers 
WHERE fab art. CODFRABICANTE = fab. CODE ;
Result query
12 .- Get the average price of the products of each manufacturer showing the manufacturer's code. SQL statement: select CODFRABICANTE, AVG (PRICE) as average from GROUP ARTICLES by CODFRABICANTE;
NOTE:
In this statement we have included a new concept by
GROUP
serves to group information, it is this case was grouped by manufacturers which are differentiated by whether ID or manufacturer code.
Query Result
13 .- Get the average price of the products of each manufacturer, showing the name of the manufacturer. SQL statement: select FABRICANTES.NOMBRE, AVG (PRICE) as average from GOODS, MANUFACTURERS WHERE ARTICULOS.CODFRABICANTE GROUP = FABRICANTES.CODIGO by FABRICANTES.NOMBRE; Query Result
14 .- Get the name of the manufacturers who offer products whose average price is equal to $ 5445. 
Note: I selected the amount of $ 5445 porque la consulta anterior me devolvió los valores promedio y en consultas anteriores se que insumos nacionales posee mas de un articulo. De lo contrario no se reflejarÃa la sentencia si un fabricante posee un solo articulo puesto que su promedio seria el mismo valor del articulo
Sentencia SQL:
select fab. NOMBRE
as EMPRESA
from fabricantes fab inner JOIN articulos
art
on
fab. CODIGO = art. CODFRABICANTE
GROUP BY EMPRESA HAVING AVG (precio) = 5445;
Extended Description: (Here I have included new commands) * A Manufacturers Table I have assigned an alias fab (not write fabrica.nombre) * A table articles I have assigned an alias
art
(not write articulos.codfrabicante)
* To Field Factory. NAME fab.NOMBRE or an alias assigned him COMPANY. *
INNER JOIN returns all rows from both tables where there is a common Keys. (In previous consultations have written all of two tables componetes example
Select articles. Name articles. Codfrabicante, articles. Price manufacturers. Code,
manufacturers. name from articles, manufacturers WHERE ........ * BAY GROUP groups the result by a field name. *
WHERE HAVING similar to but is treated as a function. * AVG returns the average.
Query Result 14 .- Get the name and price of the product cheaper. SQL statement:
select name, price from items order by price limit 1;
Query Result
Note: We could have done
subqueries in the WHERE and entangled enough but the best is to LIMIT
use the default order is ascending is why we deliver the lowest price ... if descending "order by name desc" we deliver the product more expensive.
16 .-
Get a list of names and prices of more expensive items from each supplier (including the provider's name)
inner join manufacturers f on to . codfrabicante = code group by f . name
order by price desc;
Query Result Note: Here again use the limit in descending order and also group by manufacturers to showcase their products more expensive .. the inner join to help me bring the records in the related table (manufacturers) the manufacturers as it is only to give names to the column. 17 .- Add a new product from manufacturer 2 USB lamp $ 7990.
SQL statement:
insert into items (codigoart, name, price, codfrabicante) values \u200b\u200b(114, 'USB Lamp', 7990, 2);
Query Result
(The select is very complete but only reference to show inserted record)
18 .- Rename the article 108 to Laser Printer.
select * from articles WHERE codigoart = 108;
SQL statement: UPDATE Articles in September Name = 'Laser Printer' where codigoart = 8;
Query Result
19 .- Apply 10% discount on all products.
SQL statement: update articles
September price = price * 0.9;
Note: Self-explanatory
all products were reduced by 10% .
20 .- Apply a discount of $ 1777, which price is greater than or equal to $ 20000. SQL statement:
Update articles September price = price - 1777 WHERE price> = 20000;
That's all ... Soon second set of 20 exercises more .
* To Field Factory. NAME fab.NOMBRE or an alias assigned him COMPANY. *
INNER JOIN returns all rows from both tables where there is a common Keys. (In previous consultations have written all of two tables componetes example
Select articles. Name articles. Codfrabicante, articles. Price manufacturers. Code,
manufacturers. name from articles, manufacturers WHERE ........ * BAY GROUP groups the result by a field name. *
WHERE HAVING similar to but is treated as a function. * AVG returns the average.
Query Result 14 .- Get the name and price of the product cheaper. SQL statement:
select name, price from items order by price limit 1;
Query Result
Note: We could have done
subqueries in the WHERE and entangled enough but the best is to LIMIT use the default order is ascending is why we deliver the lowest price ... if descending "order by name desc" we deliver the product more expensive.
16 .-
Get a list of names and prices of more expensive items from each supplier (including the provider's name)
SQL statement: select to . name, to . price
f . NAME as items from manufacturer to
f . NAME as items from manufacturer to
inner join manufacturers f
order by price desc;
Query Result Note: Here again use the limit in descending order and also group by manufacturers to showcase their products more expensive .. the inner join to help me bring the records in the related table (manufacturers) the manufacturers as it is only to give names to the column. 17 .- Add a new product from manufacturer 2 USB lamp $ 7990.
SQL statement:
insert into items (codigoart, name, price, codfrabicante) values \u200b\u200b(114, 'USB Lamp', 7990, 2);
Query Result
To see the result now arem a select manufacturer 2 with its products select f.codigo, f.nombre, a.name, a.precio from manufacturers
f inner join on items to f.codigo = WHERE codfrabicante f.codigo = 2;
f inner join on items to f.codigo = WHERE codfrabicante f.codigo = 2;
(The select is very complete but only reference to show inserted record)
18 .- Rename the article 108 to Laser Printer.
select * from articles WHERE codigoart = 108;
SQL statement: UPDATE Articles in September Name = 'Laser Printer' where codigoart = 8;
Query Result
19 .- Apply 10% discount on all products. SQL statement: update articles
September price = price * 0.9; Note: Self-explanatory
all products were reduced by 10% .
20 .- Apply a discount of $ 1777, which price is greater than or equal to $ 20000. SQL statement:
Update articles September price = price - 1777 WHERE price> = 20000;
That's all ... Soon second set of 20 exercises more .
0 comments:
Post a Comment