history Databases Part Three (years) 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.
11 .- Get a list of items, including for each data item name, price and name of the manufacturer.
SQL statement: SELECT
art. NAME, art.
PRICE,
fab. NAME
Nombre_Fabricante ARTICLES
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)
SQL statement: select to . name, to . price
f . NAME as items from manufacturer to
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
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;
(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 .
. 'Bot is short for the word robot. Are small programs that are put on computers with the intention of allowing cyber criminals to take control of them. Botnets, or 'botnets', are groups of computers infected by bots together to act jointly. The phisher instructs those computers, for example, downloading a new threat on your computer, display advertising to the user, launch denial service or, above all, sending spam. 
after their stay at the hotels of that company throughout the year. 
Indian hacker managed 
from fabricantes 
subqueries in the WHERE and entangled enough but the best is to LIMIT 
September 
(