-- YellowPages_MSSQL.sql

if exists (select * from sysobjects where id = object_id(N'categories') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table categories
GO
if exists (select * from sysobjects where id = object_id(N'items') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table items
GO
if exists (select * from sysobjects where id = object_id(N'users') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table users
GO

create table users
(
	user_id integer identity primary key,
	user_login varchar(15) not null,
	user_password varchar(15) not null,
	user_level int not null default 0,
	name varchar(50) not null,
	email varchar(30),
	unique (user_login)
)
GO

insert into users (user_login, user_password, name, user_level) values ('admin','admin','Admin',3)
GO
insert into users (user_login, user_password, name, user_level) values ('guest','guest','Guest',1)
GO


CREATE TABLE categories (
       category_id          int IDENTITY primary key,
       name                 varchar(50) NULL,
       par_category_id      int NULL
)
go

insert into categories (name, par_category_id) values ('Automotive', null)
GO
insert into categories (name, par_category_id) values ('Community', null)
GO
insert into categories (name, par_category_id) values ('Computers and Internet', null)
GO
insert into categories (name, par_category_id) values ('Education and Instruction', null)
GO
insert into categories (name, par_category_id) values ('Entertainment and Arts', null)
GO
insert into categories (name, par_category_id) values ('Food and Dining', null)
GO
insert into categories (name, par_category_id) values ('Health and Medicine', null)
GO
insert into categories (name, par_category_id) values ('Home and Garden', null)
GO
insert into categories (name, par_category_id) values ('Legal and Financial', null)
GO
insert into categories (name, par_category_id) values ('Accessories', 1)
GO
insert into categories (name, par_category_id) values ('Auto Wrecking', 1)
GO
insert into categories (name, par_category_id) values ('Dealers', 1)
GO
insert into categories (name, par_category_id) values ('Motorcycles', 1)
GO
insert into categories (name, par_category_id) values ('Racing', 1)
GO
insert into categories (name, par_category_id) values ('Rental', 1)
GO
insert into categories (name, par_category_id) values ('Tires', 1)
GO
insert into categories (name, par_category_id) values ('Animal and Humane Societies', 2)
GO
insert into categories (name, par_category_id) values ('Children and Youth', 2)
GO
insert into categories (name, par_category_id) values ('Disabilities', 2)
GO
insert into categories (name, par_category_id) values ('Government', 2)
GO
insert into categories (name, par_category_id) values ('Housing', 2)
GO
insert into categories (name, par_category_id) values ('Libraries', 2)
GO
insert into categories (name, par_category_id) values ('Post Offices', 2)
GO
insert into categories (name, par_category_id) values ('Religion', 2)
GO
insert into categories (name, par_category_id) values ('Communications and Networking', 3)
GO
insert into categories (name, par_category_id) values ('Computer Graphics', 3)
GO
insert into categories (name, par_category_id) values ('Computer Rental', 3)
GO
insert into categories (name, par_category_id) values ('Computer Service and Repair', 3)
GO
insert into categories (name, par_category_id) values ('Computer Training', 3)
GO
insert into categories (name, par_category_id) values ('Desktop Publishing', 3)
GO
insert into categories (name, par_category_id) values ('Software', 3)
GO
insert into categories (name, par_category_id) values ('Colleges and Universities', 4)
GO
insert into categories (name, par_category_id) values ('K-12', 4)
GO
insert into categories (name, par_category_id) values ('Language', 4)
GO
insert into categories (name, par_category_id) values ('Preschools', 4)
GO
insert into categories (name, par_category_id) values ('Tutoring', 4)
GO
insert into categories (name, par_category_id) values ('Bars, Pubs, and Clubs', 5)
GO
insert into categories (name, par_category_id) values ('Dance', 5)
GO
insert into categories (name, par_category_id) values ('Entertainers', 5)
GO
insert into categories (name, par_category_id) values ('Movies and Film', 5)
GO
insert into categories (name, par_category_id) values ('Museums and Galleries', 5)
GO
insert into categories (name, par_category_id) values ('Music', 5)
GO
insert into categories (name, par_category_id) values ('Tickets', 5)
GO
insert into categories (name, par_category_id) values ('Video', 5)
GO
insert into categories (name, par_category_id) values ('Beverages', 6)
GO
insert into categories (name, par_category_id) values ('Candy and Sweets', 6)
GO
insert into categories (name, par_category_id) values ('Catering', 6)
GO
insert into categories (name, par_category_id) values ('Culinary Schools and Classes', 6)
GO
insert into categories (name, par_category_id) values ('Grocery Stores', 6)
GO
insert into categories (name, par_category_id) values ('Meat', 6)
GO
insert into categories (name, par_category_id) values ('Natural and Organic', 6)
GO
insert into categories (name, par_category_id) values ('Poultry', 6)
GO
insert into categories (name, par_category_id) values ('Restaurants', 6)
GO
insert into categories (name, par_category_id) values ('Alternative Medicine', 7)
GO
insert into categories (name, par_category_id) values ('Care Providers', 7)
GO
insert into categories (name, par_category_id) values ('Doctors and Clinics', 7)
GO
insert into categories (name, par_category_id) values ('Education', 7)
GO
insert into categories (name, par_category_id) values ('Fitness', 7)
GO
insert into categories (name, par_category_id) values ('Mental Health', 7)
GO
insert into categories (name, par_category_id) values ('Pharmacies', 7)
GO
insert into categories (name, par_category_id) values ('Appliances', 8)
GO
insert into categories (name, par_category_id) values ('Bed and Bath', 8)
GO
insert into categories (name, par_category_id) values ('Carpets and Rugs', 8)
GO
insert into categories (name, par_category_id) values ('Cleaning Services', 8)
GO
insert into categories (name, par_category_id) values ('Furniture', 8)
GO
insert into categories (name, par_category_id) values ('Housewares', 8)
GO
insert into categories (name, par_category_id) values ('Lawn and Garden', 8)
GO
insert into categories (name, par_category_id) values ('Linens', 8)
GO
insert into categories (name, par_category_id) values ('Accounting and Bookkeeping', 9)
GO
insert into categories (name, par_category_id) values ('Arbitration and Mediation', 9)
GO
insert into categories (name, par_category_id) values ('Bail Bonds', 9)
GO
insert into categories (name, par_category_id) values ('Bankruptcy', 9)
GO
insert into categories (name, par_category_id) values ('Banks', 9)
GO
insert into categories (name, par_category_id) values ('Court Reporting Services', 9)
GO
insert into categories (name, par_category_id) values ('Credit Services', 9)
GO
insert into categories (name, par_category_id) values ('Financing', 9)
GO
insert into categories (name, par_category_id) values ('Insurance', 9)
GO
insert into categories (name, par_category_id) values ('Law Firms', 9)
GO
insert into categories (name, par_category_id) values ('Taxes', 9)
GO

CREATE TABLE items (
       item_id              int IDENTITY primary key,
       name                 varchar(100) NULL,
       address              varchar(255) NULL,
       city                 varchar(50) NULL,
       phone                varchar(50) NULL,
       state                varchar(2) NULL,
       zip                  varchar(10) NULL,
       category_id          int NULL,
       notes                text NULL,
       email                varchar(50) NULL,
       url                  varchar(50) NULL
)
go

insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('YesSoftware', '2226 A Westborough Blvd. #405', 'San Francisco', '', 'CA', '94080', 31, 'http://www.yessoftware.com')
GO
insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('IBM', '325 Corey Way # 105', 'San Francisco', '(650) 589-5500', 'CA', '94080', '31', 'http://www.ibm.com')
GO
insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('Oracle', '500 Oracle Pkwy', 'Redwood City', '(650) 506-7000', 'CA', '94065', '31', 'http://www.oracle.com')
GO
insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('Microsoft', '185 Berry St # 6600', 'San Francisco', '(415) 817-0200', 'CA', '94107', '31', 'http://www.microsoft.com')
GO
insert into items (name, address, city, phone, state, zip, category_id, url)
  values ('Sun Microsystems', '100 California St # 800', 'San Francisco', '(415) 781-8140', 'CA', '94111', '31', 'http://www.sun.com')
GO