Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement