Posted By

CDIDevs on 12/31/09


Tagged

insert commands create common update alter


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

delikassap
Tyster


Common SQL Commands (create table, etc)


 / Published in: SQL
 

URL: http://sqlzoo.net/howto/source/z.dir/tip550066/sqlserver

  1. -- create/drop tables
  2. CREATE TABLE t_test
  3. (a INTEGER PRIMARY KEY
  4. ,b VARCHAR(10)
  5. )
  6.  
  7. -- ...with identity
  8. CREATE TABLE t_test(
  9. id INTEGER IDENTITY
  10. PRIMARY KEY,
  11. name VARCHAR(10)
  12. );
  13. INSERT INTO t_test(name) VALUES ('Andrew');
  14. INSERT INTO t_test(name) VALUES ('Gordon');
  15. SELECT * FROM t_test;
  16.  
  17.  
  18. -- ... with pkey
  19. CREATE TABLE track(
  20. album CHAR(10) NOT NULL,
  21. dsk INTEGER NOT NULL,
  22. posn INTEGER NOT NULL,
  23. song VARCHAR(255),
  24. PRIMARY KEY (album, dsk, posn)
  25. )
  26.  
  27. -- ... with fkey
  28. CREATE TABLE album(
  29. id CHAR(10) NOT NULL PRIMARY KEY,
  30. title VARCHAR(100),
  31. artist VARCHAR(100)
  32. );
  33.  
  34. CREATE TABLE track(
  35. album CHAR(10),
  36. dsk INTEGER,
  37. posn INTEGER,
  38. song VARCHAR(255),
  39. FOREIGN KEY (album) REFERENCES album(id)
  40. );
  41.  
  42.  
  43. DROP TABLE t_test
  44.  
  45. -- add/remove columns
  46. ALTER TABLE a DROP COLUMN y
  47. ALTER TABLE a ADD z INTEGER

Report this snippet  

You need to login to post a comment.