API tools faq
paste
Ethan007

DATABASE 2

Ethan007
May 5th, 2012
33
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 8.81 KB | None | 0 0
raw download clone embed print report
  1.  
  2.  
  3. DROP TABLE player;
  4. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  5. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%                 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  6. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%  CREATE TABLE   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  7. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%                 %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  8. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  9. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  10. CREATE TABLE Player(  PID INT NOT NULL,
  11.                     Username VARCHAR(50) NOT NULL,
  12.                  Firstname VARCHAR(50),
  13.                   Lastname VARCHAR(50),
  14.                   Email    VARCHAR(50) NOT NULL,
  15.                   Password  VARCHAR(50) NOT NULL,
  16.                   Address  VARCHAR(50),
  17.                   Create_date DATE,
  18.                     Paid_until_date DATE,
  19.                     Security_level INT NOT NULL,
  20.                     Lasttimelogin_IP VARCHAR(50),
  21.                     Lasttimelogin_time TIMESTAMP,
  22.                     Locked_status NUMBER(1) DEFAULT 0 NOT NULL,
  23.                     Online_status NUMBER(1) DEFAULT 0 NOT NULL,
  24.                     Coins INT NOT NULL,
  25.                     Thumbs INT NOT NULL,
  26.                     Avatars VARCHAR(50),
  27.                     Homepages VARCHAR(50),
  28.                     CONSTRAINT  pkPID  PRIMARY KEY (PID))
  29.  
  30.  
  31.  
  32. DROP TABLE Friends;-- for Drop table
  33.  
  34. CREATE TABLE FRIENDS ( PID INT NOT NULL,
  35.                    Friend_PID INT ,
  36.                    CONSTRAINT FkPID1 FOREIGN KEY (PID )REFERENCES Player (PID))
  37.  
  38. DROP TABLE List;-- for Drop table
  39.  
  40. CREATE TABLE LIST(  PID INT NOT NULL,
  41.                 List_ID INT NOT NULL,
  42.                 Name VARCHAR(50) NOT NULL,
  43.                 Text_Description VARCHAR(200),
  44.                 Played_flag NUMBER(1) DEFAULT 0 NOT NULL,
  45.                 Image_Address VARCHAR(100),
  46.                 Thumb_Count INT,
  47.                 Tip_Box VARCHAR(100),
  48.                 CONSTRAINT  pkListID PRIMARY KEY (List_ID),
  49.                 CONSTRAINT FkPID2 FOREIGN KEY(PID) REFERENCES PLAYER (PID))
  50.  
  51. DROP TABLE ITEM;-- for Drop table
  52.  
  53. CREATE TABLE ITEM(  Item_ID INT NOT NULL,
  54.                 --PID INT NOT NULL,
  55.                 Name VARCHAR(50) NOT NULL,
  56.                 Text_description VARCHAR(50),
  57.                 Release_date DATE,
  58.                 Publisher VARCHAR(50),
  59.                 Developer VARCHAR(50),
  60.                 Category  VARCHAR(50) NOT NULL,
  61.                 Age_rating INT,
  62.                 Average_user_rating DECIMAL(20,1) DEFAULT 0.0,
  63.                 Overall_rank DECIMAL(20,1) DEFAULT 0.0,
  64.                 Rank_within_category DECIMAL(20,1) DEFAULT 0.0,
  65.                 Pointer_image VARCHAR(100),
  66.                 Website VARCHAR(100),
  67.                 LANGUAGE VARCHAR(50) NOT NULL,
  68.                 CONSTRAINT  pk_IID PRIMARY KEY (Item_ID),
  69.                 --CONSTRAINT fkPID3 FOREIGN KEY (PID) REFERENCES PLAYER (PID))
  70.  
  71.  
  72. DROP TABLE COLLECTION;-- for Drop table
  73.  
  74. CREATE TABLE COLLECTION( PID INT NOT NULL,
  75.                     Item_ID INT NOT NULL,
  76.                     User_comment VARCHAR(50),
  77.                     Owns_flag NUMBER(1) DEFAULT 0 NOT NULL,
  78.                     Wishlist_flag NUMBER(1) DEFAULT 0 NOT NULL,
  79.                     Played_flag NUMBER(1) DEFAULT 0 NOT NULL,
  80.                     CONSTRAINT fkPID4 FOREIGN KEY (PID) REFERENCES PLAYER(PID),
  81.                     CONSTRAINT fk_ItemID FOREIGN KEY(Item_ID) REFERENCES ITEM(Item_ID))
  82.  
  83.  
  84.  
  85.  
  86. DROP TABLE POST;-- for Drop table
  87.  
  88. CREATE TABLE POST(          PID INT NOT NULL,
  89.                         Post_ID INT NOT NULL,
  90.                         Thread_ID INT NOT NULL,
  91.                         The_date_posted DATE,
  92.                         Some_text VARCHAR(50),
  93.                         Thumb_count INT,
  94.                         Tip_box VARCHAR(50),
  95.                         Flag NUMBER(1) DEFAULT 0,
  96.                         Previous_Post_ID INT,
  97.                         Next_Post_ID INT,
  98.                         CONSTRAINT  pk_PID PRIMARY KEY (Post_ID),
  99.                         CONSTRAINT fk_TID FOREIGN KEY (Thread_ID) REFERENCES THREAD (Thread_ID),
  100.                         CONSTRAINT fk_PID5 FOREIGN KEY (PID) REFERENCES PLAYER (PID))
  101.  
  102.  
  103. DROP TABLE THREAD;-- for Drop table
  104.  
  105. CREATE TABLE THREAD(    Thread_ID INT NOT NULL,
  106.                     Title VARCHAR(50) NOT NULL,
  107.                     PID INT NOT NULL,
  108.                     Locked_flag NUMBER(1) DEFAULT 0  NOT NULL,
  109.                     CONSTRAINT  pk_TID PRIMARY KEY (Thread_ID),
  110.                     CONSTRAINT fkPID6 FOREIGN KEY (PID) REFERENCES PLAYER (PID))
  111.  
  112.  
  113.  
  114. DROP TABLE ITEM_LIST ;-- for Drop table
  115. CREATE TABLE ITEM_LIST(     Item_ID  INT NOT NULL,
  116.                         List_ID  INT NOT NULL,
  117.                         Text_comment VARCHAR(100),
  118.                         Pointer_image VARCHAR(100),
  119.                         Thumb_count INT,
  120.                         Tipbox VARCHAR(100),
  121.                         CONSTRAINT  fkItemID  FOREIGN  KEY (Item_ID) REFERENCES ITEM (Item_ID),
  122.                         CONSTRAINT  fkListID  FOREIGN  KEY (List_ID) REFERENCES LIST (List_ID))
  123.  
  124.  
  125. DROP TABLE ITEM_USER;-- for Drop table
  126. CREATE TABLE ITEM_USER(     Item_ID INT NOT NULL,
  127.                         PID INT NOT NULL,
  128.                         CONSTRAINT fkPID7 FOREIGN KEY (PID) REFERENCES PLAYER (PID),
  129.                         CONSTRAINT fkIID FOREIGN KEY (Item_ID) REFERENCES ITEM (Item_ID))
  130.  
  131.  
  132.  
  133.  
  134.  
  135. -----%%%%%%%%%%%%%%%%%%%%%  Delete the table  %%%%%%%%%%%%----------
  136.  
  137. DELETE FROM PLAYER WHERE PID=4;
  138.  
  139. SELECT *FROM PLAYER;
  140.  
  141.  
  142. SELECT PID,Username,Firstname,Lastname,Email,Password,Address, Security_level, Lasttimelogin_IP,Locked_status,Online_status,Coins,Thumbs,Homepages FROM PLAYER;
  143.  
  144.  
  145.  
  146.  
  147. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  148. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%                           %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  149. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%  Insert Player table!!!   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  150. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%                           %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  151. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  152. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  153. --%%%%%%%%%%%%%%%%%%%%%%%%%%    feel free to add information      %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  154.                  -----%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%----------
  155. INSERT INTO PLAYER(PID,Username,Firstname,Lastname,Email,Password,Address,  Create_date,Paid_until_date ,Security_level, Lasttimelogin_IP,Lasttimelogin_time,Locked_status,Online_status,Coins,Thumbs,Homepages )
  156.             VALUES(1,'hw0476','Hui','Wang','[email protected]','nimeide','iQ bristol', to_date('20120505','yyyymmdd'),to_date('20120510','yyyymmdd'),10,'108.121.5.1',CURRENT_TIMESTAMP,1,1,10000000,100000000,'WWW.renren.com/XXXXXX')
  157.  
  158. INSERT INTO PLAYER(PID,Username,Firstname,Lastname,Email,Password,Address,  Create_date,Paid_until_date ,Security_level, Lasttimelogin_IP,Lasttimelogin_time,Locked_status,Online_status,Coins,Thumbs,Homepages )
  159.             VALUES(2,'ld0897','Li','Du','[email protected]','2222222','HighKingsdown', to_date('20120505','yyyymmdd'),to_date('20120510','yyyymmdd'),10,'108.121.5.2',CURRENT_TIMESTAMP,1,1,100000,1000000,'WWW.renren.com/XXXXXX')
  160.  
  161. INSERT INTO PLAYER(PID,Username,Firstname,Lastname,Email,Password,Address,  Create_date,Paid_until_date ,Security_level, Lasttimelogin_IP,Lasttimelogin_time,Locked_status,Online_status,Coins,Thumbs,Homepages )
  162.             VALUES(3,'wz0891','Wenxuan','Zhou','[email protected]','3333333','Flat 1', to_date('20120505','yyyymmdd'),to_date('20120510','yyyymmdd'),10,'108.121.5.3',CURRENT_TIMESTAMP,1,1,100000,1000000,'WWW.renren.com/XXXXXX')
  163.  
  164. INSERT INTO PLAYER(PID,Username,Firstname,Lastname,Email,Password,Address,  Create_date,Paid_until_date ,Security_level, Lasttimelogin_IP,Lasttimelogin_time,Locked_status,Online_status,Coins,Thumbs,Homepages )
  165.             VALUES(4,'hzXXXX','Huaizi','','[email protected]','2222222','Flat 2', to_date('20120505','yyyymmdd'),to_date('20120510','yyyymmdd'),10,'108.121.5.1',CURRENT_TIMESTAMP,1,1,100000,1000000,'WWW.renren.com/XXXXXX')
  166.  
  167.  
  168.  
  169.  
  170.  
  171.  
  172.  
  173.  
  174.  
  175. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  176. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%                           %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  177. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%    Insert Item table!!!   %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  178. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%                           %%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  179. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  180. --%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
  181.  
  182. CREATE TABLE ITEM(  Item_ID INT NOT NULL,
  183.                     PID INT ,
  184.                     Name VARCHAR(50) NOT NULL,
  185.                     Text_description VARCHAR(50),
  186.                     Release_date DATE,
  187.                     Publisher VARCHAR(50),
  188.                     Developer VARCHAR(50),
  189.                     Category  VARCHAR(50) NOT NULL,
  190.                     Age_rating INT,
  191.                     Average_user_rating DECIMAL(20,1) DEFAULT 0.0,
  192.                     Overall_rank DECIMAL(20,1) DEFAULT 0.0,
  193.                     Rank_within_category DECIMAL(20,1) DEFAULT 0.0,
  194.                     Pointer_image VARCHAR(100),
  195.                     Website VARCHAR(100),
  196.                     LANGUAGE VARCHAR(50) NOT NULL,
  197.                     CONSTRAINT  pk_IID PRIMARY KEY (Item_ID),
  198.                     --CONSTRAINT fkPID3 FOREIGN KEY (PID) REFERENCES PLAYER (PID))
  199.  
  200. INSERT INTO ITEM VALUES(1,1,)
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!