Advertisement
Guest User

Untitled

a guest
Feb 23rd, 2020
88
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 0.81 KB | None | 0 0
  1.  
  2. --9.1
  3. CREATE TABLE user1_c AS SELECT * FROM Clients
  4. GROUP BY CNUM,CNAME,CITY,RATING,SNUM HAVING COUNT(*) = 1;
  5.  
  6. --9.2
  7. CREATE TABLE user1_s AS SELECT * FROM Sales
  8. GROUP BY SNUM,SNAME,CITY,COMM HAVING COUNT(*) = 1;
  9.  
  10. --9.3
  11. DELETE FROM user1_s o_s WHERE o_s.SNUM IN (SELECT SNUM FROM user1_s s GROUP BY SNUM HAVING COUNT(*) > 1);
  12. DELETE FROM USER1_S s WHERE s.SNUM IS NULL;
  13.  
  14. ALTER TABLE user1_s ADD PRIMARY KEY (SNUM);
  15. --ALTER TABLE user1_s ADD CONSTRAINT PK_USER1_S PRIMARY KEY (SNUM);
  16. DESC user1_s;
  17. --9.4
  18. DELETE FROM USER1_C c WHERE c.SNUM IS NULL;
  19. DELETE FROM USER1_C WHERE SNUM NOT IN (SELECT SNUM FROM user1_s)
  20. ALTER TABLE user1_c ADD FOREIGN KEY (SNUM) REFERENCES USER1_S(SNUM);
  21.  
  22. --9.4.5
  23. SELECT * FROM USER1_S s RIGHT JOIN USER1_C c ON s.SNUM = c.SNUM;
  24. -- 9.5
  25. DROP TABLE USER1_C;
  26. DROP TABLE USER1_S;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement