faqts : Computers : Databases : Microsoft SQL Server

+ Search
Add Entry AlertManage Folder Edit Entry Add page to http://del.icio.us/
Did You Find This Entry Useful?

1 of 1 people (100%) answered Yes
Recently 1 of 1 people (100%) answered Yes

Entry

Database:Create: Type: Key:Primary+Foreign: How create person email one to many database, using SQL?

Dec 22nd, 2003 13:44
Knud van Eeden,


----------------------------------------------------------------------
--- Knud van Eeden --- 22 December 2003 - 10:41 pm -------------------
Database:Create: Type: Key:Primary+Foreign: How create person email 
one to many database, using SQL?
---
Steps: Overview:
 1. -Open Microsoft SQL server Query Analyzer
 2. -To create a database, tables, fill these tables,
     and show these tables,
     type or paste the following text:
--- cut here ---------------------------------------------------------
      --
      -- create your database
      --
      CREATE DATABASE Emaillist ON (
       NAME = 'Emaillist',
       FILENAME = 'c:\program files\microsoft sql 
server\mssql\data\emaillist.mdf',
       SIZE = 10,
       MAXSIZE = 50,
       FILEGROWTH = 5
      )
      LOG ON (
       NAME = 'sa',
       FILENAME = 'c:\program files\microsoft sql 
server\mssql\data\emaillist.ldf',
       SIZE = 5MB,
       MAXSIZE = 25MB,
       FILEGROWTH = 5MB
      )
      GO
      --
      -- use your database
      --
      USE Emaillist
      --
      -- create table 1
      --
      CREATE TABLE Person (
       Name VARCHAR( 50 ) NOT NULL,
       PersonKey INT NOT NULL,
       PRIMARY KEY ( PersonKey )
      );
      --
      -- create table 2
      --
      CREATE TABLE Email (
       emailaddress VARCHAR( 50 ) NOT NULL,
       PersonKey INT NOT NULL,
       FOREIGN KEY ( PersonKey ) REFERENCES Person ( PersonKey )
      );
      --
      -- insert in table 1
      --
      INSERT INTO Person ( Name, PersonKey ) VALUES ( 'John Doe', 1 );
      INSERT INTO Person ( Name, PersonKey ) VALUES ( 'Vanessa Beau', 
2 );
      --
      -- insert in table 2
      --
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 1 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 1 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 1 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 2 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 2 );
      INSERT INTO Email ( Emailaddress, PersonKey ) VALUES 
( '[email protected]', 2 );
      --
      -- view the tables
      --
      --
      -- view table 1
      --
      SELECT * FROM Person;
      --
      -- view table 2
      --
      SELECT * FROM Email;
      --
      -- View tables 1 and 2 connected via their primary and foreign 
key:
      --
      SELECT * FROM Person, Email WHERE Person.Personkey = 
Email.Personkey;
--- cut here ---------------------------------------------------------
---
---
Internet: see also:
---
Database: Microsoft SQL server: SQL: Operation: Overview:Can you give 
an overview of SQL operations?
http://www.faqts.com/knowledge_base/view.phtml/aid/27560/fid/147
----------------------------------------------------------------------