Difference between revisions of "CSC220 Database Management Systems"

From dftwiki3
Jump to: navigation, search
(Insert Data)
(Insert Data)
Line 35: Line 35:
 
==Insert Data==
 
==Insert Data==
  
 +
* Add 5 users to the database (Marie, John, Manon, Rob, Elaine)
  
 
  INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Marie');
 
  INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Marie');
Line 42: Line 43:
 
  INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Elaine');
 
  INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Elaine');
 
   
 
   
 +
* Add 6 items and their price (pencil, eraser, paper, ink, ruler, markers)
 
   
 
   
 
  INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'pencil','1');
 
  INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'pencil','1');
Line 51: Line 53:
 
  INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'markers','2.5');
 
  INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'markers','2.5');
 
   
 
   
 +
* Create some user orders of items.  e.g. Marie buys a pencil, an eraser, paper, and ink.
 
   
 
   
 
  INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','1');
 
  INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','1');

Revision as of 07:19, 6 October 2010

--D. Thiebaut 12:08, 6 October 2010 (UTC)



Example

  • Users, purchasing items from a store.
  • 3 tables: users, orders, items

Create Tables

CREATE TABLE `users` (
 `userId` INT(1) NOT NULL AUTO_INCREMENT,
 `userName` VARCHAR(100) DEFAULT NULL,
 PRIMARY KEY  (`userId`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;


CREATE TABLE `orders` (
 `orderId` INT(1) NOT NULL AUTO_INCREMENT,
 `userId` INT(1) DEFAULT NULL,
 `itemId` INT(1) DEFAULT NULL,
 PRIMARY KEY  (`orderId`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;


CREATE TABLE `items` (
 `itemId` INT(1) NOT NULL AUTO_INCREMENT,
 `itemName` VARCHAR(100) DEFAULT NULL,
 `price` FLOAT DEFAULT NULL,
 PRIMARY KEY  (`itemId`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8;

Insert Data

  • Add 5 users to the database (Marie, John, Manon, Rob, Elaine)
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Marie');
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'John');
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Manon');
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Rob');
INSERT INTO `users`(`userId`,`userName`) VALUES ( NULL,'Elaine');

  • Add 6 items and their price (pencil, eraser, paper, ink, ruler, markers)
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'pencil','1');
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'erase','0.5');
UPDATE `items` SET `itemId`='2',`itemName`='eraser',`price`='0.5' WHERE `itemId`='2';
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'paper','5');
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'ink','30');
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'ruler','1.5');
INSERT INTO `items`(`itemId`,`itemName`,`price`) VALUES ( NULL,'markers','2.5');

  • Create some user orders of items. e.g. Marie buys a pencil, an eraser, paper, and ink.
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','1');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','2');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','3');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'1','4');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'2','1');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'3','1');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'3','4');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','4');
INSERT INTO `orders`(`orderId`,`userId`,`itemId`) VALUES ( NULL,'5','3');