Advanced Search

Common SQL Commands (create table, etc)

/ Published in: SQL
Save to your folder(s)

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  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

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

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.