Skip to content

Instantly share code, notes, and snippets.

@jonosue

jonosue/Scalar_Function_Returns_XML.sql

Created December 23, 2015 03:57
Show Gist options
  • Save jonosue/e941d307339ad9693675 to your computer and use it in GitHub Desktop.
Save jonosue/e941d307339ad9693675 to your computer and use it in GitHub Desktop.
Download ZIP
I'm not sure if there's a practical use case for this, but I think it's pretty cool nonetheless. In this example, I've designed a function that accepts an input parameter for Author ID and returns the Author's name and residence in XML format from the "authors" table in the pubs database. The script to create the pubs database is included as well.
Raw
Scalar_Function_Returns_XML.sql
USE pubs
GO
IF OBJECT_ID('dbo.AuthorIDXML', 'FN') IS NOT NULL
DROP FUNCTION dbo.AuthorIDXML
GO
CREATE FUNCTION dbo.AuthorIDXML
(
@AuID varchar(13)
)
RETURNS XML
AS
BEGIN
DECLARE @ResultSet XML
SET @ResultSet =
(
SELECT
au_id AS '@AuthorID',
au_lname + ', ' + au_fname AS 'AuthorName',
city + ',' + state AS 'Location'
FROM
authors
WHERE
au_id = @AuID
FOR XML PATH('Author'), TYPE
)
RETURN @ResultSet
END
GO
/* Test with au_id "172-32-1176" */
SELECT dbo.AuthorIDXML('172-32-1176')
Raw
Script_pubs_database.sql
USE [master]
GO
/****** Object: Database [pubs] Script Date: 12/15/2013 6:09:51 PM ******/
CREATE DATABASE [pubs]
GO
ALTER DATABASE [pubs] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [pubs].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [pubs] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [pubs] SET ANSI_NULLS OFF
GO
ALTER DATABASE [pubs] SET ANSI_PADDING OFF
GO
ALTER DATABASE [pubs] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [pubs] SET ARITHABORT OFF
GO
ALTER DATABASE [pubs] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [pubs] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [pubs] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [pubs] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [pubs] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [pubs] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [pubs] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [pubs] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [pubs] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [pubs] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [pubs] SET ENABLE_BROKER
GO
ALTER DATABASE [pubs] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [pubs] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [pubs] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [pubs] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [pubs] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [pubs] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [pubs] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [pubs] SET RECOVERY FULL
GO
ALTER DATABASE [pubs] SET MULTI_USER
GO
ALTER DATABASE [pubs] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [pubs] SET DB_CHAINING OFF
GO
ALTER DATABASE [pubs] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [pubs] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
EXEC sys.sp_db_vardecimal_storage_format N'pubs', N'ON'
GO
USE [pubs]
GO
/****** Object: UserDefinedDataType [dbo].[empid] Script Date: 12/15/2013 6:09:51 PM ******/
CREATE TYPE [dbo].[empid] FROM [char](9) NOT NULL
GO
/****** Object: UserDefinedDataType [dbo].[id] Script Date: 12/15/2013 6:09:51 PM ******/
CREATE TYPE [dbo].[id] FROM [varchar](11) NOT NULL
GO
/****** Object: UserDefinedDataType [dbo].[tid] Script Date: 12/15/2013 6:09:51 PM ******/
CREATE TYPE [dbo].[tid] FROM [varchar](6) NOT NULL
GO
/****** Object: StoredProcedure [dbo].[byroyalty] Script Date: 12/15/2013 6:09:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[byroyalty] @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
GO
/****** Object: Table [dbo].[authors] Script Date: 12/15/2013 6:09:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[authors](
[au_id] [dbo].[id] NOT NULL,
[au_lname] [varchar](40) NOT NULL,
[au_fname] [varchar](20) NOT NULL,
[phone] [char](12) NOT NULL,
[address] [varchar](40) NULL,
[city] [varchar](20) NULL,
[state] [char](2) NULL,
[zip] [char](5) NULL,
[contract] [bit] NOT NULL,
CONSTRAINT [UPKCL_auidind] PRIMARY KEY CLUSTERED
(
[au_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[discounts] Script Date: 12/15/2013 6:09:51 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[discounts](
[discounttype] [varchar](40) NOT NULL,
[stor_id] [char](4) NULL,
[lowqty] [smallint] NULL,
[highqty] [smallint] NULL,
[discount] [decimal](4, 2) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[employee] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[employee](
[emp_id] [dbo].[empid] NOT NULL,
[fname] [varchar](20) NOT NULL,
[minit] [char](1) NULL,
[lname] [varchar](30) NOT NULL,
[job_id] [smallint] NOT NULL,
[job_lvl] [tinyint] NULL,
[pub_id] [char](4) NOT NULL,
[hire_date] [datetime] NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[jobs] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[jobs](
[job_id] [smallint] IDENTITY(1,1) NOT NULL,
[job_desc] [varchar](50) NOT NULL,
[min_lvl] [tinyint] NOT NULL,
[max_lvl] [tinyint] NOT NULL,
PRIMARY KEY CLUSTERED
(
[job_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[pub_info] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pub_info](
[pub_id] [char](4) NOT NULL,
[logo] [image] NULL,
[pr_info] [text] NULL,
CONSTRAINT [UPKCL_pubinfo] PRIMARY KEY CLUSTERED
(
[pub_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[publishers] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[publishers](
[pub_id] [char](4) NOT NULL,
[pub_name] [varchar](40) NULL,
[city] [varchar](20) NULL,
[state] [char](2) NULL,
[country] [varchar](30) NULL,
CONSTRAINT [UPKCL_pubind] PRIMARY KEY CLUSTERED
(
[pub_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[roysched] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[roysched](
[title_id] [dbo].[tid] NOT NULL,
[lorange] [int] NULL,
[hirange] [int] NULL,
[royalty] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[sales] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[sales](
[stor_id] [char](4) NOT NULL,
[ord_num] [varchar](20) NOT NULL,
[ord_date] [datetime] NOT NULL,
[qty] [smallint] NOT NULL,
[payterms] [varchar](12) NOT NULL,
[title_id] [dbo].[tid] NOT NULL,
CONSTRAINT [UPKCL_sales] PRIMARY KEY CLUSTERED
(
[stor_id] ASC,
[ord_num] ASC,
[title_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[stores] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[stores](
[stor_id] [char](4) NOT NULL,
[stor_name] [varchar](40) NULL,
[stor_address] [varchar](40) NULL,
[city] [varchar](20) NULL,
[state] [char](2) NULL,
[zip] [char](5) NULL,
CONSTRAINT [UPK_storeid] PRIMARY KEY CLUSTERED
(
[stor_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[titleauthor] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[titleauthor](
[au_id] [dbo].[id] NOT NULL,
[title_id] [dbo].[tid] NOT NULL,
[au_ord] [tinyint] NULL,
[royaltyper] [int] NULL,
CONSTRAINT [UPKCL_taind] PRIMARY KEY CLUSTERED
(
[au_id] ASC,
[title_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[titles] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[titles](
[title_id] [dbo].[tid] NOT NULL,
[title] [varchar](80) NOT NULL,
[type] [char](12) NOT NULL,
[pub_id] [char](4) NULL,
[price] [money] NULL,
[advance] [money] NULL,
[royalty] [int] NULL,
[ytd_sales] [int] NULL,
[notes] [varchar](200) NULL,
[pubdate] [datetime] NOT NULL,
CONSTRAINT [UPKCL_titleidind] PRIMARY KEY CLUSTERED
(
[title_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: View [dbo].[titleview] Script Date: 12/15/2013 6:09:52 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[titleview]
AS
select title, au_ord, au_lname, price, ytd_sales, pub_id
from authors, titles, titleauthor
where authors.au_id = titleauthor.au_id
AND titles.title_id = titleauthor.title_id
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [employee_ind] Script Date: 12/15/2013 6:09:52 PM ******/
CREATE CLUSTERED INDEX [employee_ind] ON [dbo].[employee]
(
[lname] ASC,
[fname] ASC,
[minit] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'172-32-1176', N'White', N'Johnson', N'408 496-7223', N'10932 Bigge Rd.', N'Menlo Park', N'CA', N'94025', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'213-46-8915', N'Green', N'Marjorie', N'415 986-7020', N'309 63rd St. #411', N'Oakland', N'CA', N'94618', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'238-95-7766', N'Carson', N'Cheryl', N'415 548-7723', N'589 Darwin Ln.', N'Berkeley', N'CA', N'94705', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'267-41-2394', N'O''Leary', N'Michael', N'408 286-2428', N'22 Cleveland Av. #14', N'San Jose', N'CA', N'95128', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'274-80-9391', N'Straight', N'Dean', N'415 834-2919', N'5420 College Av.', N'Oakland', N'CA', N'94609', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'341-22-1782', N'Smith', N'Meander', N'913 843-0462', N'10 Mississippi Dr.', N'Lawrence', N'KS', N'66044', 0)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'409-56-7008', N'Bennet', N'Abraham', N'415 658-9932', N'6223 Bateman St.', N'Berkeley', N'CA', N'94705', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'427-17-2319', N'Dull', N'Ann', N'415 836-7128', N'3410 Blonde St.', N'Palo Alto', N'CA', N'94301', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'472-27-2349', N'Gringlesby', N'Burt', N'707 938-6445', N'PO Box 792', N'Covelo', N'CA', N'95428', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'486-29-1786', N'Locksley', N'Charlene', N'415 585-4620', N'18 Broadway Av.', N'San Francisco', N'CA', N'94130', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'527-72-3246', N'Greene', N'Morningstar', N'615 297-2723', N'22 Graybar House Rd.', N'Nashville', N'TN', N'37215', 0)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'648-92-1872', N'Blotchet-Halls', N'Reginald', N'503 745-6402', N'55 Hillsdale Bl.', N'Corvallis', N'OR', N'97330', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'672-71-3249', N'Yokomoto', N'Akiko', N'415 935-4228', N'3 Silver Ct.', N'Walnut Creek', N'CA', N'94595', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'712-45-1867', N'del Castillo', N'Innes', N'615 996-8275', N'2286 Cram Pl. #86', N'Ann Arbor', N'MI', N'48105', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'722-51-5454', N'DeFrance', N'Michel', N'219 547-9982', N'3 Balding Pl.', N'Gary', N'IN', N'46403', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'724-08-9931', N'Stringer', N'Dirk', N'415 843-2991', N'5420 Telegraph Av.', N'Oakland', N'CA', N'94609', 0)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'724-80-9391', N'MacFeather', N'Stearns', N'415 354-7128', N'44 Upland Hts.', N'Oakland', N'CA', N'94612', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'756-30-7391', N'Karsen', N'Livia', N'415 534-9219', N'5720 McAuley St.', N'Oakland', N'CA', N'94609', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'807-91-6654', N'Panteley', N'Sylvia', N'301 946-8853', N'1956 Arlington Pl.', N'Rockville', N'MD', N'20853', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'846-92-7186', N'Hunter', N'Sheryl', N'415 836-7128', N'3410 Blonde St.', N'Palo Alto', N'CA', N'94301', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'893-72-1158', N'McBadden', N'Heather', N'707 448-4982', N'301 Putnam', N'Vacaville', N'CA', N'95688', 0)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'899-46-2035', N'Ringer', N'Anne', N'801 826-0752', N'67 Seventh Av.', N'Salt Lake City', N'UT', N'84152', 1)
INSERT [dbo].[authors] ([au_id], [au_lname], [au_fname], [phone], [address], [city], [state], [zip], [contract]) VALUES (N'998-72-3567', N'Ringer', N'Albert', N'801 826-0752', N'67 Seventh Av.', N'Salt Lake City', N'UT', N'84152', 1)
INSERT [dbo].[discounts] ([discounttype], [stor_id], [lowqty], [highqty], [discount]) VALUES (N'Initial Customer', NULL, NULL, NULL, CAST(10.50 AS Decimal(4, 2)))
INSERT [dbo].[discounts] ([discounttype], [stor_id], [lowqty], [highqty], [discount]) VALUES (N'Volume Discount', NULL, 100, 1000, CAST(6.70 AS Decimal(4, 2)))
INSERT [dbo].[discounts] ([discounttype], [stor_id], [lowqty], [highqty], [discount]) VALUES (N'Customer Discount', N'8042', NULL, NULL, CAST(5.00 AS Decimal(4, 2)))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'PMA42628M', N'Paolo', N'M', N'Accorti', 13, 35, N'0877', CAST(0x0000843100000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'PSA89086M', N'Pedro', N'S', N'Afonso', 14, 89, N'1389', CAST(0x000081CD00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'VPA30890F', N'Victoria', N'P', N'Ashworth', 6, 140, N'0877', CAST(0x0000816700000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'H-B39728F', N'Helen', N' ', N'Bennett', 12, 35, N'0877', CAST(0x0000800200000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'L-B31947F', N'Lesley', N' ', N'Brown', 7, 120, N'0877', CAST(0x0000820000000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'F-C16315M', N'Francisco', N' ', N'Chang', 4, 227, N'9952', CAST(0x0000819A00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'PTC11962M', N'Philip', N'T', N'Cramer', 2, 215, N'9952', CAST(0x0000803500000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'A-C71970F', N'Aria', N' ', N'Cruz', 10, 87, N'1389', CAST(0x000082FF00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'AMD15433F', N'Ann', N'M', N'Devon', 3, 200, N'9952', CAST(0x0000829900000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'ARD36773F', N'Anabela', N'R', N'Domingues', 8, 100, N'0877', CAST(0x000084CA00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'PHF38899M', N'Peter', N'H', N'Franken', 10, 75, N'0877', CAST(0x000083CB00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'PXH22250M', N'Paul', N'X', N'Henriot', 5, 159, N'0877', CAST(0x0000859600000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'CFH28514M', N'Carlos', N'F', N'Hernadez', 5, 211, N'9999', CAST(0x00007F6900000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'PDI47470M', N'Palle', N'D', N'Ibsen', 7, 195, N'0736', CAST(0x0000853000000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'KJJ92907F', N'Karla', N'J', N'Jablonski', 9, 170, N'9999', CAST(0x0000866200000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'KFJ64308F', N'Karin', N'F', N'Josephs', 14, 100, N'0736', CAST(0x0000846400000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'MGK44605M', N'Matti', N'G', N'Karttunen', 6, 220, N'0736', CAST(0x0000869500000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'POK93028M', N'Pirkko', N'O', N'Koskitalo', 10, 80, N'9999', CAST(0x000085FC00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'JYL26161F', N'Janine', N'Y', N'Labrune', 5, 172, N'9901', CAST(0x0000826600000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'M-L67958F', N'Maria', N' ', N'Larsson', 7, 135, N'1389', CAST(0x0000839800000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'Y-L77953M', N'Yoshi', N' ', N'Latimer', 12, 32, N'1389', CAST(0x00007F9C00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'LAL21447M', N'Laurence', N'A', N'Lebihan', 5, 175, N'0736', CAST(0x0000810100000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'ENL44273F', N'Elizabeth', N'N', N'Lincoln', 14, 35, N'0877', CAST(0x0000813400000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'PCM98509F', N'Patricia', N'C', N'McKenna', 11, 150, N'9999', CAST(0x00007FCF00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'R-M53550M', N'Roland', N' ', N'Mendel', 11, 150, N'0736', CAST(0x000082CC00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'RBM23061F', N'Rita', N'B', N'Muller', 5, 198, N'1622', CAST(0x000085C900000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'HAN90777M', N'Helvetius', N'A', N'Nagy', 7, 120, N'9999', CAST(0x000084FD00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'TPO55093M', N'Timothy', N'P', N'O''Rourke', 13, 100, N'0736', CAST(0x00007E3700000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'SKO22412M', N'Sven', N'K', N'Ottlieb', 5, 150, N'1389', CAST(0x0000823300000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'MAP77183M', N'Miguel', N'A', N'Paolino', 11, 112, N'1389', CAST(0x0000849700000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'PSP68661F', N'Paula', N'S', N'Parente', 8, 125, N'1389', CAST(0x0000862F00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'M-P91209M', N'Manuel', N' ', N'Pereira', 8, 101, N'9999', CAST(0x00007F0300000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'MJP25939M', N'Maria', N'J', N'Pontes', 5, 246, N'1756', CAST(0x00007F3600000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'M-R38834F', N'Martine', N' ', N'Rance', 9, 75, N'0877', CAST(0x0000836500000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'DWR65030M', N'Diego', N'W', N'Roel', 6, 192, N'1389', CAST(0x0000833200000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'A-R89858F', N'Annette', N' ', N'Roulet', 6, 152, N'9999', CAST(0x0000809B00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'MMS49649F', N'Mary', N'M', N'Saveley', 8, 175, N'0736', CAST(0x0000856300000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'CGS88322F', N'Carine', N'G', N'Schmitt', 13, 64, N'1389', CAST(0x000083FE00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'MAS70474F', N'Margaret', N'A', N'Smith', 9, 78, N'1389', CAST(0x00007E9D00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'HAS54740M', N'Howard', N'A', N'Snyder', 12, 100, N'0736', CAST(0x00007ED000000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'MFS52347M', N'Martin', N'F', N'Sommer', 10, 165, N'0736', CAST(0x000080CE00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'GHT50241M', N'Gary', N'H', N'Thomas', 9, 170, N'0736', CAST(0x00007E6A00000000 AS DateTime))
INSERT [dbo].[employee] ([emp_id], [fname], [minit], [lname], [job_id], [job_lvl], [pub_id], [hire_date]) VALUES (N'DBT39435M', N'Daniel', N'B', N'Tonini', 11, 75, N'0877', CAST(0x0000806800000000 AS DateTime))
SET IDENTITY_INSERT [dbo].[jobs] ON
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (1, N'New Hire - Job not specified', 10, 10)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (2, N'Chief Executive Officer', 200, 250)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (3, N'Business Operations Manager', 175, 225)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (4, N'Chief Financial Officier', 175, 250)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (5, N'Publisher', 150, 250)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (6, N'Managing Editor', 140, 225)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (7, N'Marketing Manager', 120, 200)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (8, N'Public Relations Manager', 100, 175)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (9, N'Acquisitions Manager', 75, 175)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (10, N'Productions Manager', 75, 165)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (11, N'Operations Manager', 75, 150)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (12, N'Editor', 25, 100)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (13, N'Sales Representative', 25, 100)
INSERT [dbo].[jobs] ([job_id], [job_desc], [min_lvl], [max_lvl]) VALUES (14, N'Designer', 25, 100)
SET IDENTITY_INSERT [dbo].[jobs] OFF
INSERT [dbo].[pub_info] ([pub_id], [logo], [pr_info]) VALUES (N'0736', 0xFFFFFFFF, N'None yet')
INSERT [dbo].[pub_info] ([pub_id], [logo], [pr_info]) VALUES (N'0877', 0xFFFFFFFF, N'None yet')
INSERT [dbo].[pub_info] ([pub_id], [logo], [pr_info]) VALUES (N'1389', 0xFFFFFFFF, N'None yet')
INSERT [dbo].[pub_info] ([pub_id], [logo], [pr_info]) VALUES (N'1622', 0xFFFFFFFF, N'None yet')
INSERT [dbo].[pub_info] ([pub_id], [logo], [pr_info]) VALUES (N'1756', 0xFFFFFFFF, N'None yet')
INSERT [dbo].[pub_info] ([pub_id], [logo], [pr_info]) VALUES (N'9901', 0xFFFFFFFF, N'None yet')
INSERT [dbo].[pub_info] ([pub_id], [logo], [pr_info]) VALUES (N'9952', 0xFFFFFFFF, N'None yet')
INSERT [dbo].[pub_info] ([pub_id], [logo], [pr_info]) VALUES (N'9999', 0xFFFFFFFF, N'None yet')
INSERT [dbo].[publishers] ([pub_id], [pub_name], [city], [state], [country]) VALUES (N'0736', N'New Moon Books', N'Boston', N'MA', N'USA')
INSERT [dbo].[publishers] ([pub_id], [pub_name], [city], [state], [country]) VALUES (N'0877', N'Binnet & Hardley', N'Washington', N'DC', N'USA')
INSERT [dbo].[publishers] ([pub_id], [pub_name], [city], [state], [country]) VALUES (N'1389', N'Algodata Infosystems', N'Berkeley', N'CA', N'USA')
INSERT [dbo].[publishers] ([pub_id], [pub_name], [city], [state], [country]) VALUES (N'1622', N'Five Lakes Publishing', N'Chicago', N'IL', N'USA')
INSERT [dbo].[publishers] ([pub_id], [pub_name], [city], [state], [country]) VALUES (N'1756', N'Ramona Publishers', N'Dallas', N'TX', N'USA')
INSERT [dbo].[publishers] ([pub_id], [pub_name], [city], [state], [country]) VALUES (N'9901', N'GGG&G', N'M?nchen', NULL, N'Germany')
INSERT [dbo].[publishers] ([pub_id], [pub_name], [city], [state], [country]) VALUES (N'9952', N'Scootney Books', N'New York', N'NY', N'USA')
INSERT [dbo].[publishers] ([pub_id], [pub_name], [city], [state], [country]) VALUES (N'9999', N'Lucerne Publishing', N'Paris', NULL, N'France')
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1032', 0, 5000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1032', 5001, 50000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PC1035', 0, 2000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PC1035', 2001, 3000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PC1035', 3001, 4000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PC1035', 4001, 10000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PC1035', 10001, 50000, 18)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU2075', 0, 1000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU2075', 1001, 3000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU2075', 3001, 5000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU2075', 5001, 7000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU2075', 7001, 10000, 18)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU2075', 10001, 12000, 20)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU2075', 12001, 14000, 22)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU2075', 14001, 50000, 24)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS2091', 0, 1000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS2091', 1001, 5000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS2091', 5001, 10000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS2091', 10001, 50000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS2106', 0, 2000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS2106', 2001, 5000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS2106', 5001, 10000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS2106', 10001, 50000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC3021', 0, 1000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC3021', 1001, 2000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC3021', 2001, 4000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC3021', 4001, 6000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC3021', 6001, 8000, 18)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC3021', 8001, 10000, 20)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC3021', 10001, 12000, 22)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC3021', 12001, 50000, 24)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC3218', 0, 2000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC3218', 2001, 4000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC3218', 4001, 6000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC3218', 6001, 8000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC3218', 8001, 10000, 18)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC3218', 10001, 12000, 20)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC3218', 12001, 14000, 22)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC3218', 14001, 50000, 24)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PC8888', 0, 5000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PC8888', 5001, 10000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PC8888', 10001, 15000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PC8888', 15001, 50000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS7777', 0, 5000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS7777', 5001, 50000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS3333', 0, 5000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS3333', 5001, 10000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS3333', 10001, 15000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS3333', 15001, 50000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1111', 0, 4000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1111', 4001, 8000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1111', 8001, 10000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1111', 12001, 16000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1111', 16001, 20000, 18)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1111', 20001, 24000, 20)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1111', 24001, 28000, 22)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU1111', 28001, 50000, 24)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC2222', 0, 2000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC2222', 2001, 4000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC2222', 4001, 8000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC2222', 8001, 12000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC2222', 12001, 20000, 18)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'MC2222', 20001, 50000, 20)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC7777', 0, 5000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC7777', 5001, 15000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC7777', 15001, 50000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC4203', 0, 2000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC4203', 2001, 8000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC4203', 8001, 16000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC4203', 16001, 24000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC4203', 24001, 32000, 18)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC4203', 32001, 40000, 20)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'TC4203', 40001, 50000, 22)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU7832', 0, 5000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU7832', 5001, 10000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU7832', 10001, 15000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU7832', 15001, 20000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU7832', 20001, 25000, 18)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU7832', 25001, 30000, 20)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU7832', 30001, 35000, 22)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'BU7832', 35001, 50000, 24)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS1372', 0, 10000, 10)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS1372', 10001, 20000, 12)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS1372', 20001, 30000, 14)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS1372', 30001, 40000, 16)
INSERT [dbo].[roysched] ([title_id], [lorange], [hirange], [royalty]) VALUES (N'PS1372', 40001, 50000, 18)
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'6380', N'6871', CAST(0x0000871D00000000 AS DateTime), 5, N'Net 60', N'BU1032')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'6380', N'722a', CAST(0x0000871C00000000 AS DateTime), 3, N'Net 60', N'PS2091')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7066', N'A2976', CAST(0x0000853F00000000 AS DateTime), 50, N'Net 30', N'PC8888')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7066', N'QA7442.3', CAST(0x0000871C00000000 AS DateTime), 75, N'ON invoice', N'PS2091')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7067', N'D4482', CAST(0x0000871D00000000 AS DateTime), 10, N'Net 60', N'PS2091')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7067', N'P2121', CAST(0x000083E800000000 AS DateTime), 40, N'Net 30', N'TC3218')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7067', N'P2121', CAST(0x000083E800000000 AS DateTime), 20, N'Net 30', N'TC4203')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7067', N'P2121', CAST(0x000083E800000000 AS DateTime), 20, N'Net 30', N'TC7777')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'N914008', CAST(0x0000871D00000000 AS DateTime), 20, N'Net 30', N'PS2091')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'N914014', CAST(0x0000871D00000000 AS DateTime), 25, N'Net 30', N'MC3021')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'P3087a', CAST(0x0000854400000000 AS DateTime), 20, N'Net 60', N'PS1372')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'P3087a', CAST(0x0000854400000000 AS DateTime), 25, N'Net 60', N'PS2106')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'P3087a', CAST(0x0000854400000000 AS DateTime), 15, N'Net 60', N'PS3333')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7131', N'P3087a', CAST(0x0000854400000000 AS DateTime), 25, N'Net 60', N'PS7777')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7896', N'QQ2299', CAST(0x000085DC00000000 AS DateTime), 15, N'Net 60', N'BU7832')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7896', N'TQ456', CAST(0x0000860900000000 AS DateTime), 10, N'Net 60', N'MC2222')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'7896', N'X999', CAST(0x000084E300000000 AS DateTime), 35, N'ON invoice', N'BU2075')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'8042', N'423LL922', CAST(0x0000871D00000000 AS DateTime), 15, N'ON invoice', N'MC3021')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'8042', N'423LL930', CAST(0x0000871D00000000 AS DateTime), 10, N'ON invoice', N'BU1032')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'8042', N'P723', CAST(0x000084F500000000 AS DateTime), 25, N'Net 30', N'BU1111')
INSERT [dbo].[sales] ([stor_id], [ord_num], [ord_date], [qty], [payterms], [title_id]) VALUES (N'8042', N'QA879.1', CAST(0x0000853D00000000 AS DateTime), 30, N'Net 30', N'PC1035')
INSERT [dbo].[stores] ([stor_id], [stor_name], [stor_address], [city], [state], [zip]) VALUES (N'6380', N'Eric the Read Books', N'788 Catamaugus Ave.', N'Seattle', N'WA', N'98056')
INSERT [dbo].[stores] ([stor_id], [stor_name], [stor_address], [city], [state], [zip]) VALUES (N'7066', N'Barnum''s', N'567 Pasadena Ave.', N'Tustin', N'CA', N'92789')
INSERT [dbo].[stores] ([stor_id], [stor_name], [stor_address], [city], [state], [zip]) VALUES (N'7067', N'News & Brews', N'577 First St.', N'Los Gatos', N'CA', N'96745')
INSERT [dbo].[stores] ([stor_id], [stor_name], [stor_address], [city], [state], [zip]) VALUES (N'7131', N'Doc-U-Mat: Quality Laundry and Books', N'24-A Avogadro Way', N'Remulade', N'WA', N'98014')
INSERT [dbo].[stores] ([stor_id], [stor_name], [stor_address], [city], [state], [zip]) VALUES (N'7896', N'Fricative Bookshop', N'89 Madison St.', N'Fremont', N'CA', N'90019')
INSERT [dbo].[stores] ([stor_id], [stor_name], [stor_address], [city], [state], [zip]) VALUES (N'8042', N'Bookbeat', N'679 Carson St.', N'Portland', N'OR', N'89076')
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'172-32-1176', N'PS3333', 1, 100)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'213-46-8915', N'BU1032', 2, 40)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'213-46-8915', N'BU2075', 1, 100)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'238-95-7766', N'PC1035', 1, 100)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'267-41-2394', N'BU1111', 2, 40)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'267-41-2394', N'TC7777', 2, 30)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'274-80-9391', N'BU7832', 1, 100)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'409-56-7008', N'BU1032', 1, 60)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'427-17-2319', N'PC8888', 1, 50)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'472-27-2349', N'TC7777', 3, 30)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'486-29-1786', N'PC9999', 1, 100)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'486-29-1786', N'PS7777', 1, 100)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'648-92-1872', N'TC4203', 1, 100)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'672-71-3249', N'TC7777', 1, 40)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'712-45-1867', N'MC2222', 1, 100)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'722-51-5454', N'MC3021', 1, 75)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'724-80-9391', N'BU1111', 1, 60)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'724-80-9391', N'PS1372', 2, 25)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'756-30-7391', N'PS1372', 1, 75)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'807-91-6654', N'TC3218', 1, 100)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'846-92-7186', N'PC8888', 2, 50)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'899-46-2035', N'MC3021', 2, 25)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'899-46-2035', N'PS2091', 2, 50)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'998-72-3567', N'PS2091', 1, 50)
INSERT [dbo].[titleauthor] ([au_id], [title_id], [au_ord], [royaltyper]) VALUES (N'998-72-3567', N'PS2106', 1, 100)
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'BU1032', N'The Busy Executive''s Database Guide', N'business ', N'1389', 19.9900, 5000.0000, 10, 4095, N'An overview of available database systems with emphasis on common business applications. Illustrated.', CAST(0x0000827700000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'BU1111', N'Cooking with Computers: Surreptitious Balance Sheets', N'business ', N'1389', 11.9500, 5000.0000, 10, 3876, N'Helpful hints on how to use your electronic resources to the best advantage.', CAST(0x0000827400000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'BU2075', N'You Can Combat Computer Stress!', N'business ', N'0736', 2.9900, 10125.0000, 24, 18722, N'The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations.', CAST(0x0000828900000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'BU7832', N'Straight Talk About Computers', N'business ', N'1389', 19.9900, 5000.0000, 10, 4095, N'Annotated analysis of what computers can do for you: a no-hype guide for the critical user.', CAST(0x0000828100000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'MC2222', N'Silicon Valley Gastronomic Treats', N'mod_cook ', N'0877', 19.9900, 0.0000, 12, 2032, N'Favorite recipes for quick, easy, and elegant meals.', CAST(0x0000827400000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'MC3021', N'The Gourmet Microwave', N'mod_cook ', N'0877', 2.9900, 15000.0000, 24, 22246, N'Traditional French gourmet recipes adapted for modern microwave cooking.', CAST(0x0000827D00000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'MC3026', N'The Psychology of Computer Cooking', N'UNDECIDED ', N'0877', NULL, NULL, NULL, NULL, NULL, CAST(0x0000A295012B0C1B AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'PC1035', N'But Is It User Friendly?', N'popular_comp', N'1389', 22.9500, 7000.0000, 16, 8780, N'A survey of software for the naive user, focusing on the ''friendliness'' of each.', CAST(0x0000828900000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'PC8888', N'Secrets of Silicon Valley', N'popular_comp', N'1389', 20.0000, 8000.0000, 10, 4095, N'Muckraking reporting on the world''s largest computer hardware and software manufacturers.', CAST(0x000086BF00000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'PC9999', N'Net Etiquette', N'popular_comp', N'1389', NULL, NULL, NULL, NULL, N'A must-read for computer conferencing.', CAST(0x0000A295012B0C1B AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'PS1372', N'Computer Phobic AND Non-Phobic Individuals: Behavior Variations', N'psychology ', N'0877', 21.5900, 7000.0000, 10, 375, N'A must for the specialist, this book examines the difference between those who hate and fear computers and those who don''t.', CAST(0x000082FA00000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'PS2091', N'Is Anger the Enemy?', N'psychology ', N'0736', 10.9500, 2275.0000, 12, 2045, N'Carefully researched study of the effects of strong emotions on the body. Metabolic charts included.', CAST(0x0000827A00000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'PS2106', N'Life Without Fear', N'psychology ', N'0736', 7.0000, 6000.0000, 10, 111, N'New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately.', CAST(0x000082EA00000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'PS3333', N'Prolonged Data Deprivation: Four Case Studies', N'psychology ', N'0736', 19.9900, 2000.0000, 10, 4072, N'What happens when the data runs dry? Searching evaluations of information-shortage effects.', CAST(0x0000827700000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'PS7777', N'Emotional Security: A New Algorithm', N'psychology ', N'0736', 7.9900, 4000.0000, 10, 3336, N'Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized.', CAST(0x0000827700000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'TC3218', N'Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean', N'trad_cook ', N'0877', 20.9500, 7000.0000, 10, 375, N'Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend.', CAST(0x000082FA00000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'TC4203', N'Fifty Years in Buckingham Palace Kitchens', N'trad_cook ', N'0877', 11.9500, 4000.0000, 14, 15096, N'More anecdotes from the Queen''s favorite cook describing life among English royalty. Recipes, techniques, tender vignettes.', CAST(0x0000827700000000 AS DateTime))
INSERT [dbo].[titles] ([title_id], [title], [type], [pub_id], [price], [advance], [royalty], [ytd_sales], [notes], [pubdate]) VALUES (N'TC7777', N'Sushi, Anyone?', N'trad_cook ', N'0877', 14.9900, 8000.0000, 10, 4095, N'Detailed instructions on how to make authentic Japanese sushi in your spare time.', CAST(0x0000827700000000 AS DateTime))
SET ANSI_PADDING ON
GO
/****** Object: Index [aunmind] Script Date: 12/15/2013 6:09:52 PM ******/
CREATE NONCLUSTERED INDEX [aunmind] ON [dbo].[authors]
(
[au_lname] ASC,
[au_fname] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [PK_emp_id] Script Date: 12/15/2013 6:09:52 PM ******/
ALTER TABLE [dbo].[employee] ADD CONSTRAINT [PK_emp_id] PRIMARY KEY NONCLUSTERED
(
[emp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [titleidind] Script Date: 12/15/2013 6:09:52 PM ******/
CREATE NONCLUSTERED INDEX [titleidind] ON [dbo].[roysched]
(
[title_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [titleidind] Script Date: 12/15/2013 6:09:52 PM ******/
CREATE NONCLUSTERED INDEX [titleidind] ON [dbo].[sales]
(
[title_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [auidind] Script Date: 12/15/2013 6:09:52 PM ******/
CREATE NONCLUSTERED INDEX [auidind] ON [dbo].[titleauthor]
(
[au_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [titleidind] Script Date: 12/15/2013 6:09:52 PM ******/
CREATE NONCLUSTERED INDEX [titleidind] ON [dbo].[titleauthor]
(
[title_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
SET ANSI_PADDING ON
GO
/****** Object: Index [titleind] Script Date: 12/15/2013 6:09:52 PM ******/
CREATE NONCLUSTERED INDEX [titleind] ON [dbo].[titles]
(
[title] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[authors] ADD DEFAULT ('UNKNOWN') FOR [phone]
GO
ALTER TABLE [dbo].[employee] ADD DEFAULT ((1)) FOR [job_id]
GO
ALTER TABLE [dbo].[employee] ADD DEFAULT ((10)) FOR [job_lvl]
GO
ALTER TABLE [dbo].[employee] ADD DEFAULT ('9952') FOR [pub_id]
GO
ALTER TABLE [dbo].[employee] ADD DEFAULT (getdate()) FOR [hire_date]
GO
ALTER TABLE [dbo].[jobs] ADD DEFAULT ('New Position - title not formalized yet') FOR [job_desc]
GO
ALTER TABLE [dbo].[publishers] ADD DEFAULT ('USA') FOR [country]
GO
ALTER TABLE [dbo].[titles] ADD DEFAULT ('UNDECIDED') FOR [type]
GO
ALTER TABLE [dbo].[titles] ADD DEFAULT (getdate()) FOR [pubdate]
GO
ALTER TABLE [dbo].[discounts] WITH CHECK ADD FOREIGN KEY([stor_id])
REFERENCES [dbo].[stores] ([stor_id])
GO
ALTER TABLE [dbo].[employee] WITH CHECK ADD FOREIGN KEY([job_id])
REFERENCES [dbo].[jobs] ([job_id])
GO
ALTER TABLE [dbo].[employee] WITH CHECK ADD FOREIGN KEY([pub_id])
REFERENCES [dbo].[publishers] ([pub_id])
GO
ALTER TABLE [dbo].[pub_info] WITH CHECK ADD FOREIGN KEY([pub_id])
REFERENCES [dbo].[publishers] ([pub_id])
GO
ALTER TABLE [dbo].[roysched] WITH CHECK ADD FOREIGN KEY([title_id])
REFERENCES [dbo].[titles] ([title_id])
GO
ALTER TABLE [dbo].[sales] WITH CHECK ADD FOREIGN KEY([stor_id])
REFERENCES [dbo].[stores] ([stor_id])
GO
ALTER TABLE [dbo].[sales] WITH CHECK ADD FOREIGN KEY([title_id])
REFERENCES [dbo].[titles] ([title_id])
GO
ALTER TABLE [dbo].[titleauthor] WITH CHECK ADD FOREIGN KEY([au_id])
REFERENCES [dbo].[authors] ([au_id])
GO
ALTER TABLE [dbo].[titleauthor] WITH CHECK ADD FOREIGN KEY([title_id])
REFERENCES [dbo].[titles] ([title_id])
GO
ALTER TABLE [dbo].[titles] WITH CHECK ADD FOREIGN KEY([pub_id])
REFERENCES [dbo].[publishers] ([pub_id])
GO
ALTER TABLE [dbo].[authors] WITH CHECK ADD CHECK (([au_id] like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]'))
GO
ALTER TABLE [dbo].[authors] WITH CHECK ADD CHECK (([zip] like '[0-9][0-9][0-9][0-9][0-9]'))
GO
ALTER TABLE [dbo].[employee] WITH CHECK ADD CONSTRAINT [CK_emp_id] CHECK (([emp_id] like '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' OR [emp_id] like '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'))
GO
ALTER TABLE [dbo].[employee] CHECK CONSTRAINT [CK_emp_id]
GO
ALTER TABLE [dbo].[jobs] WITH CHECK ADD CHECK (([max_lvl]<=(250)))
GO
ALTER TABLE [dbo].[jobs] WITH CHECK ADD CHECK (([min_lvl]>=(10)))
GO
ALTER TABLE [dbo].[publishers] WITH CHECK ADD CHECK (([pub_id]='1756' OR [pub_id]='1622' OR [pub_id]='0877' OR [pub_id]='0736' OR [pub_id]='1389' OR [pub_id] like '99[0-9][0-9]'))
GO
USE [master]
GO
ALTER DATABASE [pubs] SET READ_WRITE
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment