# SQL
comandos terminam em ";"
show databases;
create database nova;
use nova;
show tables;

create table tabela1 (id int, nome varchar(100));

desc tabela1;
(descreve)

insert into tabela1 (id, nome) values(1, 'Ricardo');

select id,nome
from tabela1;

create table tabela (id int not null primary key auto_increment, nome varchar(100));

update tabela2 set nome='gil' where id=1;
delete from tabela2 where id>2 and more='Gil'

source c:/sql/hr.sql;
#importa comandos
select max(salary) from employees;

select min(salary) from employees;
count (conta quantos há)
sum (soma tudo)
avg (media)
select first_name from employees where salary > (select avg(salary)from employees);

select first name, (select avg(salary)from employees), salary * 2 from employees;

alias= as total
exemplo: select first name, (select avg(salary)from employees) as total, salary * 2 from employees;

select first_name, last_name, department_name from employees, departments where employees.department_id=departments.departments_id

uma maneira diferente de fazer o de cima:

select first_name, last_name, department_name, region_name from employees left join departments on employees.department_id=departments.department_id left join locations on departments.location_id=locations.location_id left join countries on locations.country_id=countries.country_id left join regions on countries.region_id=regions.region_id;

select first_name, last_name, department_name, salary from employees left join departments on employees.department_id=departments.department_id where salary >(select avg(salary) from employees);

select first_name, last_name, department_name, salary from employees where salary >(select avg(salary) from employees where department_name='IT');

https://www.w3resource.com/mysql-exercises/
mostrar quem ganha mais de todos os departamentos

select first_name, last_name from employees

select=escolher informaçao de tabelas
from = indicar qual a tabela
join = buscar info de outras tabelas
where (if) = por condiçoes
order by = ordernar asc / des

left('string',num)=>string
left('Portugal',4)=>Port
right('Portugal',4)=>ugal

length('string')=
length('portugal')=8

reverse (string)->string
reverse ('portugal')= lagutrop

locate(string, string[num])->num
locate('tuga','portugal')->4
locate ('a','portugal')->7
locate ('a', 'espanha',5)=>7

locate ('a',string,locate('a',string)+1)

exercisio:
ficheiro.html
documento.xls
carta.docx
outro.tar.gz
pasta.tx