SHARE
TWEET

Untitled

a guest Feb 21st, 2020 71 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE DATABASE at1;
  2. USE at1;
  3.  
  4. CREATE TABLE PECA (
  5.  CodPeca INTEGER CONSTRAINT pk_CodPeca PRIMARY KEY,
  6.  NomePeca VARCHAR2(20) NOT NULL,
  7.  PesoPeca NUMBER(5,2) NOT NULL,
  8.  CorPeca VARCHAR2(20) NOT NULL,
  9.  CidadePeca VARCHAR2(20) NOT NULL
  10. );
  11.  
  12. CREATE TABLE EMBARQ (
  13.  CodPeca INTEGER CONSTRAINT fk_CodPeca FOREINGN KEY (CodPeca) REFERENCES PECA (CodPeca),
  14.  CodFornec INTEGER CONSTRAINT pk_Embarq PRIMARY KEY,
  15.  QtdEmbarq NUMBER(10) NOT NULL
  16. );
  17.  
  18. CREATE TABLE FORNEC (
  19.  CodFornec NUMBER CONSTRAINT fk_CodFornec FOREINGN KEY (CodFornec) REFERENCES EMBARQ (CodFornec),
  20.  NomeFornec VARCHAR2(40) NOT NULL,
  21.  StatusFornec INTEGER NOT NULL,
  22.  CidadeFornec VARCHAR2(20) NOT NULL
  23. );
  24.  
  25. SELECT * FROM PECA;
  26. SELECT * FROM EMBARQ;
  27. SELECT * FROM FORNEC;
  28.  
  29. --Exercício1
  30. SELECT COUNT(NomeFornec) FROM Fornec;
  31.  
  32. --Exercício2
  33. SELECT COUNT(CidadeFornec) FROM Fornec WHERE CodFornec IS NOT NULL;
  34.  
  35. --Exercício3
  36. SELECT COUNT(CodFornec) FROM Fornec WHERE CidadeFornec IS NOT NULL;
  37.  
  38. --Exercício4
  39. SELECT MAX(QtdeEmbarc);
  40.  
  41. --Exercício5
  42. SELECT SUM(QtdeEmbarc) FROM Fornec WHERE CodFornec=F1;
  43. SELECT SUM(QtdeEmbarc) FROM Fornec WHERE CodFornec=F2;
  44. SELECT SUM(QtdeEmbarc) FROM Fornec WHERE CodFornec=F3;
  45. SELECT SUM(QtdeEmbarc) FROM Fornec WHERE CodFornec=F4;
  46.  
  47. --Exercício6
  48. SELECT QtdeEmbarc FROM Fornec WHERE QtdeEmbarc > 300 ORDER BY CodFornec
  49.  
  50. --Exercício7  
  51. SELECT CodFornec,NomeFornec FROM Fornec WHERE EXISTS (
  52.         SELECT SUM(CorPeca) FROM Peca WHERE CorPeca='Cinza')ORDER BY [..] DESC;
  53.  
  54. --Exercício8
  55. SELECT CodFornec FROM Fornec WHERE QtdeEmbarc>500 AND EXISTS (
  56.   SELECT SUM(CorPeca) FROM Peca WHERE CorPeca='Cinza');
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top