CREATE DATABASE at1;
USE at1;

CREATE TABLE PECA (
 CodPeca INTEGER CONSTRAINT pk_CodPeca PRIMARY KEY,
 NomePeca VARCHAR2(20) NOT NULL,
 PesoPeca NUMBER(5,2) NOT NULL,
 CorPeca VARCHAR2(20) NOT NULL,
 CidadePeca VARCHAR2(20) NOT NULL
);

CREATE TABLE EMBARQ (
 CodPeca INTEGER CONSTRAINT fk_CodPeca FOREINGN KEY (CodPeca) REFERENCES PECA (CodPeca),
 CodFornec INTEGER CONSTRAINT pk_Embarq PRIMARY KEY,
 QtdEmbarq NUMBER(10) NOT NULL
);

CREATE TABLE FORNEC (
 CodFornec NUMBER CONSTRAINT fk_CodFornec FOREINGN KEY (CodFornec) REFERENCES EMBARQ (CodFornec),
 NomeFornec VARCHAR2(40) NOT NULL,
 StatusFornec INTEGER NOT NULL,
 CidadeFornec VARCHAR2(20) NOT NULL
);

SELECT * FROM PECA;
SELECT * FROM EMBARQ;
SELECT * FROM FORNEC;

--Exercício1
SELECT COUNT(NomeFornec) FROM Fornec;

--Exercício2
SELECT COUNT(CidadeFornec) FROM Fornec WHERE CodFornec IS NOT NULL;

--Exercício3
SELECT COUNT(CodFornec) FROM Fornec WHERE CidadeFornec IS NOT NULL;

--Exercício4
SELECT MAX(QtdeEmbarc);

--Exercício5
SELECT SUM(QtdeEmbarc) FROM Fornec WHERE CodFornec=F1;
SELECT SUM(QtdeEmbarc) FROM Fornec WHERE CodFornec=F2;
SELECT SUM(QtdeEmbarc) FROM Fornec WHERE CodFornec=F3;
SELECT SUM(QtdeEmbarc) FROM Fornec WHERE CodFornec=F4;

--Exercício6
SELECT QtdeEmbarc FROM Fornec WHERE QtdeEmbarc > 300 ORDER BY CodFornec

--Exercício7
SELECT CodFornec,NomeFornec FROM Fornec WHERE EXISTS (
		SELECT SUM(CorPeca) FROM Peca WHERE CorPeca='Cinza')ORDER BY [..] DESC;

--Exercício8
SELECT CodFornec FROM Fornec WHERE QtdeEmbarc>500 AND EXISTS (
  SELECT SUM(CorPeca) FROM Peca WHERE CorPeca='Cinza');