: SQL




Cursor Management

July 1st, 2008 admin Posted in SQL Comments Off

DECLARE @Person char(50)
DECLARE @FirstName VARCHAR(50)

DECLARE c1 CURSOR FOR
select Person, FirstName from Person

OPEN c1
FETCH NEXT FROM c1
INTO @Person,@FirstName

WHILE @@FETCH_STATUS = 0
BEGIN
	PRINT @Person +  @FirstName
	FETCH NEXT FROM c1
	INTO @Person, @FirstName
END

CLOSE c1
DEALLOCATE c1
AddThis Social Bookmark Button

Example Database Schema : PetStore

July 1st, 2008 admin Posted in SQL Comments Off

CREATE DATABASE mypetstore;

USE mypetstore;

CREATE TABLE IF NOT EXISTS supplier (
   suppid int NOT NULL,
    name varchar(80) null,
    status varchar(2) not null,
    addr1 varchar(80) null,
    addr2 varchar(80) null,
    city varchar(80) null,
    state varchar(80) null,
    zip varchar(5) null,
    phone varchar(80) null,
PRIMARY KEY (suppid));

CREATE TABLE IF NOT EXISTS signon (
    username varchar(25) not null,
    password varchar(25)  not null,
PRIMARY KEY (username));

CREATE TABLE IF NOT EXISTS account (
    userid varchar(80) not null,
    email varchar(80) not null,
    firstname varchar(80) not null,
    lastname varchar(80) not null,
    status varchar(2)  null,
    addr1 varchar(80) not null,
    addr2 varchar(40) null,
    city varchar(80) not  null,
    state varchar(80) not null,
    zip varchar(20) not null,
    country varchar(20) not null,
    phone varchar(80) not null,
    creditcard varchar(80) not null,
    exprdate varchar(7) not null,
    cardtype varchar(80) not null,
PRIMARY KEY (userid));

CREATE TABLE IF NOT EXISTS profile (
    userid varchar(80) not null,
    langpref varchar(80) not null,
    favcategory varchar(30),
    mylistopt bool,
    banneropt bool,
PRIMARY KEY (userid));

CREATE TABLE IF NOT EXISTS bannerdata (
    favcategory varchar(80) not null,
    bannername varchar(255)  null,
PRIMARY KEY (favcategory));

CREATE TABLE IF NOT EXISTS orders (
      orderid int not null,
      userid varchar(80) not null,
      orderdate date not null,
      shipaddr1 varchar(80) not null,
      shipaddr2 varchar(80) null,
      shipcity varchar(80) not null,
      shipstate varchar(80) not null,
      shipzip varchar(20) not null,
      shipcountry varchar(20) not null,
      billaddr1 varchar(80) not null,
      billaddr2 varchar(80)  null,
      billcity varchar(80) not null,
      billstate varchar(80) not null,
      billzip varchar(20) not null,
      billcountry varchar(20) not null,
      courier varchar(80) not null,
      totalprice decimal(10,2) not null,
      billtofirstname varchar(80) not null,
      billtolastname varchar(80) not null,
      shiptofirstname varchar(80) not null,
      shiptolastname varchar(80) not null,
      creditcard varchar(80) not null,
      exprdate varchar(7) not null,
      cardtype varchar(80) not null,
      locale varchar(20) not null,
PRIMARY KEY (orderid));

CREATE TABLE IF NOT EXISTS orderstatus (
      orderid int not null,
      linenum int not null,
      timestamp date not null,
      status varchar(2) not null,
PRIMARY KEY (orderid, linenum));

CREATE TABLE IF NOT EXISTS lineitem (
      orderid int not null,
      linenum int not null,
      itemid varchar(10) not null,
      quantity int not null,
      unitprice decimal(10,2) not null,
PRIMARY KEY (orderid, linenum));

ALTER TABLE lineitem ADD FOREIGN KEY (orderid)
         REFERENCES orders(orderid)
         ON DELETE RESTRICT
         ON UPDATE RESTRICT;

CREATE TABLE IF NOT EXISTS category (
      catid varchar(10) not null,
      name varchar(80) null,
      descn varchar(255) null,
PRIMARY KEY (catid));

CREATE TABLE IF NOT EXISTS product (
      productid varchar(10) not null,
      category varchar(10) not null,
      name varchar(80) null,
      descn varchar(255) null,
PRIMARY KEY (productid));

ALTER TABLE product
	ADD INDEX productCat(category);

ALTER TABLE product
	ADD INDEX productName(name);

ALTER TABLE category
	ADD INDEX ixCategoryProduct(catid);

ALTER TABLE product  ADD FOREIGN KEY (category)
         REFERENCES category(catid)
         ON DELETE RESTRICT
         ON UPDATE RESTRICT;

CREATE TABLE IF NOT EXISTS item (
      itemid varchar(10) not null,
      productid varchar(10) not null,
      listprice decimal(10,2) null,
      unitcost decimal(10,2) null,
      supplier int null,
      status varchar(2) null,
      attr1 varchar(80) null,
      attr2 varchar(80) null,
      attr3 varchar(80) null,
      attr4 varchar(80) null,
      attr5 varchar(80) null,
PRIMARY KEY (itemid));

ALTER TABLE item
	ADD INDEX itemProd(productid);

ALTER TABLE item ADD FOREIGN KEY (productid)
         REFERENCES product(productid)
         ON DELETE RESTRICT
         ON UPDATE RESTRICT;

ALTER TABLE item ADD FOREIGN KEY (supplier)
         REFERENCES supplier(suppid)
         ON DELETE RESTRICT
         ON UPDATE RESTRICT;

CREATE TABLE IF NOT EXISTS inventory (
      itemid varchar(10) not null,
      qty int not null,
PRIMARY KEY (itemid));

CREATE TABLE IF NOT EXISTS sequence (
      name varchar(20) not null,
      seqnum int not null,
PRIMARY KEY (name));

INSERT INTO signon VALUES('j2ee','j2ee');

INSERT INTO account VALUES('j2ee','yourname@yourdomain.com','ABC', 'XYX', 'OK', '901 San Antonio Road', 'MS UCUP02-206', 'Palo Alto', 'CA', '94303', 'USA',  '555-555-5555', '123456789', '04/04', 'Meow Card');

INSERT INTO profile VALUES('j2ee','english','DOGS','1','1');

INSERT INTO bannerdata VALUES ('FISH','<image src="images/banner_fish.gif">');
INSERT INTO bannerdata VALUES ('CATS','<image src="images/banner_cats.gif">');
INSERT INTO bannerdata VALUES ('DOGS','<image src="images/banner_dogs.gif">');
INSERT INTO bannerdata VALUES ('REPTILES','<image src="images/banner_reptiles.gif">');
INSERT INTO bannerdata VALUES ('BIRDS','<image src="images/banner_birds.gif">');

INSERT INTO category VALUES ('FISH','Fish','<image src="images/fish_icon.gif"><font size="5" color="blue"> Fish</font>');
INSERT INTO category VALUES ('DOGS','Dogs','<image src="images/dogs_icon.gif"><font size="5" color="blue"> Dogs</font>');
INSERT INTO category VALUES ('REPTILES','Reptiles','<image src="images/reptiles_icon.gif"><font size="5" color="blue"> Reptiles</font>');
INSERT INTO category VALUES ('CATS','Cats','<image src="images/cats_icon.gif"><font size="5" color="blue"> Cats</font>');
INSERT INTO category VALUES ('BIRDS','Birds','<image src="images/birds_icon.gif"><font size="5" color="blue"> Birds</font>');

INSERT INTO product VALUES ('FI-SW-01','FISH','Angelfish','<image src="images/fish1.jpg">Salt Water fish from Australia');
INSERT INTO product VALUES ('FI-SW-02','FISH','Tiger Shark','<image src="images/fish4.gif">Salt Water fish from Australia');
INSERT INTO product VALUES ('FI-FW-01','FISH', 'Koi','<image src="images/fish3.gif">Fresh Water fish from Japan');
INSERT INTO product VALUES ('FI-FW-02','FISH', 'Goldfish','<image src="images/fish2.gif">Fresh Water fish from China');
INSERT INTO product VALUES ('K9-BD-01','DOGS','Bulldog','<image src="images/dog2.gif">Friendly dog from England');
INSERT INTO product VALUES ('K9-PO-02','DOGS','Poodle','<image src="images/dog6.gif">Cute dog from France');
INSERT INTO product VALUES ('K9-DL-01','DOGS', 'Dalmation','<image src="images/dog5.gif">Great dog for a Fire Station');
INSERT INTO product VALUES ('K9-RT-01','DOGS', 'Golden Retriever','<image src="images/dog1.gif">Great family dog');
INSERT INTO product VALUES ('K9-RT-02','DOGS', 'Labrador Retriever','<image src="images/dog5.gif">Great hunting dog');
INSERT INTO product VALUES ('K9-CW-01','DOGS', 'Chihuahua','<image src="images/dog4.gif">Great companion dog');
INSERT INTO product VALUES ('RP-SN-01','REPTILES','Rattlesnake','<image src="images/lizard3.gif">Doubles as a watch dog');
INSERT INTO product VALUES ('RP-LI-02','REPTILES','Iguana','<image src="images/lizard2.gif">Friendly green friend');
INSERT INTO product VALUES ('FL-DSH-01','CATS','Manx','<image src="images/cat3.gif">Great for reducing mouse populations');
INSERT INTO product VALUES ('FL-DLH-02','CATS','Persian','<image src="images/cat1.gif">Friendly house cat, doubles as a princess');
INSERT INTO product VALUES ('AV-CB-01','BIRDS','Amazon Parrot','<image src="images/bird4.gif">Great companion for up to 75 years');
INSERT INTO product VALUES ('AV-SB-02','BIRDS','Finch','<image src="images/bird1.gif">Great stress reliever');

INSERT INTO supplier VALUES (1,'XYZ Pets','AC','600 Avon Way','','Los Angeles','CA','94024','212-947-0797');
INSERT INTO supplier VALUES (2,'ABC Pets','AC','700 Abalone Way','','San Francisco ','CA','94024','415-947-0797');

INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-1','FI-SW-01',16.50,10.00,1,'P','Large');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-2','FI-SW-01',16.50,10.00,1,'P','Small');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-3','FI-SW-02',18.50,12.00,1,'P','Toothless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-4','FI-FW-01',18.50,12.00,1,'P','Spotted');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-5','FI-FW-01',18.50,12.00,1,'P','Spotless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-6','K9-BD-01',18.50,12.00,1,'P','Male Adult');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-7','K9-BD-01',18.50,12.00,1,'P','Female Puppy');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-8','K9-PO-02',18.50,12.00,1,'P','Male Puppy');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-9','K9-DL-01',18.50,12.00,1,'P','Spotless Male Puppy');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-10','K9-DL-01',18.50,12.00,1,'P','Spotted Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-11','RP-SN-01',18.50,12.00,1,'P','Venomless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-12','RP-SN-01',18.50,12.00,1,'P','Rattleless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-13','RP-LI-02',18.50,12.00,1,'P','Green Adult');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-14','FL-DSH-01',58.50,12.00,1,'P','Tailless');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-15','FL-DSH-01',23.50,12.00,1,'P','With tail');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-16','FL-DLH-02',93.50,12.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-17','FL-DLH-02',93.50,12.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-18','AV-CB-01',193.50,92.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-19','AV-SB-02',15.50, 2.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-20','FI-FW-02',5.50, 2.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-21','FI-FW-02',5.29, 1.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-22','K9-RT-02',135.50, 100.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-23','K9-RT-02',145.49, 100.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-24','K9-RT-02',255.50, 92.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-25','K9-RT-02',325.29, 90.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-26','K9-CW-01',125.50, 92.00,1,'P','Adult Male');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-27','K9-CW-01',155.29, 90.00,1,'P','Adult Female');
INSERT INTO  item (itemid, productid, listprice, unitcost, supplier, status, attr1) VALUES('EST-28','K9-RT-01',155.29, 90.00,1,'P','Adult Female');

INSERT INTO inventory (itemid, qty ) VALUES ('EST-1',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-2',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-3',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-4',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-5',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-6',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-7',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-8',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-9',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-10',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-11',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-12',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-13',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-14',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-15',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-16',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-17',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-18',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-19',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-20',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-21',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-22',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-23',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-24',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-25',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-26',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-27',10000);
INSERT INTO inventory (itemid, qty ) VALUES ('EST-28',10000);

INSERT INTO sequence VALUES('orderseq', 1000);
AddThis Social Bookmark Button

Microsoft SQL Server Settings

July 1st, 2008 admin Posted in SQL Comments Off

Collation		SQL_Latin_General_CP1_C1_AS
Recovery model	Simple
Compatibility level	SQL Server 2005 (90)

Automatic
---------
Auto Close		False
Auto Create Stats		True
Auto Shrink		False
Auto Update Stats		True
Auto Update Stats Asynch	False

Cursor
-------
Close Cursor on Commit Enabled	False
Default Cursor			GLOBAL

Misc
------
ANSI NULL Default		False
ANSI			False
Parameterization		Simple
......			False
......			False

Recovery
-------
Page Verify			CHECKSUM

State
-----
Database Read-Only		False
Restrict Access			MULTI_USER
AddThis Social Bookmark Button

Example Database Schema: Typical WebSite

July 1st, 2008 admin Posted in SQL Comments Off

CREATE TABLE [dbo].[Users](
	[UserId] [int] IDENTITY(100,1) NOT NULL,
	[ContactId] [int] NOT NULL,
	[RoleId] [int] NOT NULL,
	[LoginId] [char](32)  NOT NULL,
	[Password] [char](64)
);

CREATE TABLE [dbo].[Roles](
	[RoleId] [int] IDENTITY(100,1) NOT NULL,
	[Name] [char](32) NOT NULL,
	[Description] [char](256) NOT NULL,
);

CREATE TABLE [dbo].[Contacts](
	[ContactId] [int] IDENTITY(1001,1) NOT NULL,
	[LoginId] [char](32)  NOT NULL,
	[FirstName] [char](30) NULL,
	[LastName] [char](30) NULL,
	[StreetAddress] [char](60) NULL,
	[ApptNo] [char](10) NULL,
	[City] [char](30) NULL,
	[State] [char](2) NULL,
	[Zip] [char](10) NULL,
	[Country] [char](2) NULL,
	[PhoneHome] [char](12) NULL,
	[PhoneWork] [char](12) NULL,
	[PhoneCell] [char](12) NULL,
	[Email1] [char](256) NULL,
	[Email2] [char](256) NULL
);

ALTER TABLE [dbo].[Users] ADD PRIMARY KEY (UserId);
ALTER TABLE [dbo].[Roles] ADD PRIMARY KEY (RoleId);
ALTER TABLE [dbo].[Contacts] ADD PRIMARY KEY (ContactId);

ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_Users_Contacts] FOREIGN KEY([ContactId]) REFERENCES [dbo].[Contacts] ([ContactId]);
ALTER TABLE [dbo].[Users] ADD CONSTRAINT [FK_Users_Roles] FOREIGN KEY([RoleId]) REFERENCES [dbo].[Roles] ([RoleId]);
AddThis Social Bookmark Button

Similar Posts