Chapter 15:MS SQL SERVER 2005 / 2008Advance SQL Code
MS SQL SERVER 2005 / 2008
Multiple Address Case Study
1.INTRODUCTION
This guide introduces the advance coding concepts for SQL … the code provided is considered as top end distinctive examples.
Consider a situation when we need to handle multiple addresses for customers or clients. For example:
- Suppliers deliver on behalf of their clients to different warehouses, retails outlets or customer addresses.
- Taxis may have a variety of pickup and dropoff addresses.
- Driving Instructors may have a pickup/dropoff from the work address rather than home address
2.DRIVING SCHOOL CASE STUDY
The following is a partial ERD from a Driving School Case Study byJamie Saunderson …
A lesson can have a pickup and dropoff address for a pupil which may or may not be from the pupils home address. So our database needs to record a pupils home adddress and a lessons pickup and dropoff addresses.
Q. Can we generate a VIEW of a lesson pickup and dropoff using the auto CREATE VIEW.
A. We can generate a pickup view or generate a dropoff view BUT not both. We are unable to access both the pickup and dropoff addresses … it is one or the other …
Solution: Write SQL Script code to provide the information in the format we require.
In order to explore these solutions the create and insert scripts have been provided below.
3.CREATE SQL SCRIPT
The SQL Script to implement the above model is provided below. Load and execute this in SQL.
CREATETABLE [dbo].[Addresses](
[Address#] [int] IDENTITY(1,1)NOTNULL,
[Address] [varchar](50)COLLATE Latin1_General_CI_AS NOTNULL,
[Town] [varchar](20)COLLATE Latin1_General_CI_AS NOTNULL,
[PostCode] [nchar](10)COLLATE Latin1_General_CI_AS NULL,
CONSTRAINT [PK_Addresses] PRIMARYKEYCLUSTERED
([Address#] ASC
)WITH(IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
CREATETABLE [dbo].[Pupils](
[Pupil#] [int] NOTNULL,
[Title] [varchar](4)COLLATE Latin1_General_CI_AS NOTNULL,
[FirstName] [varchar](20)COLLATE Latin1_General_CI_AS NOTNULL,
[MiddleInitials] [varchar](3)COLLATE Latin1_General_CI_AS NULL,
[LastName] [varchar](20)COLLATE Latin1_General_CI_AS NOTNULL,
[PhoneNumber] [nchar](12)COLLATE Latin1_General_CI_AS NOTNULL,
[DOB] [datetime] NOTNULL,
[Address#] [int] NOTNULL,
[Instructer#] [int] NULL,
[Active] [varchar](3)COLLATE Latin1_General_CI_AS NOTNULL,
CONSTRAINT [PK_Pupils] PRIMARYKEYCLUSTERED
(
[Pupil#] ASC
)WITH(IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
CREATETABLE [dbo].[Lessons](
[Lesson#] [int] IDENTITY(1,1)NOTNULL,
[Pupil#] [int] NOTNULL,
[Instructor#] [int] NOTNULL,
[LessonDateTime] [datetime] NOTNULL,
[PickUp] [int] NOTNULL,
[DropOff] [int] NULL,
CONSTRAINT [PK_Lessons] PRIMARYKEYCLUSTERED
(
[Lesson#] ASC
)WITH(IGNORE_DUP_KEY =OFF)ON [PRIMARY]
)ON [PRIMARY]
ALTERTABLE [dbo].[Lessons] WITHCHECKADD CONSTRAINT [FK_Lessons_Addresses] FOREIGNKEY([PickUp])
REFERENCES [dbo].[Addresses] ([Address#])
GO
ALTERTABLE [dbo].[Lessons] WITHCHECKADD CONSTRAINT [FK_Lessons_Addresses1] FOREIGNKEY([DropOff])
REFERENCES [dbo].[Addresses] ([Address#])
4.SQL CREATE SCRIPT
The SQL Insert Script to populate the tables with sample data.
SETIDENTITY_INSERT addresses ON
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(1,'1 js street','hartlepool','ts24 0js')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(2,'2 zr street','hartlepool','ts24 0zr')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(3,'3 gw street','hartlepool','ts24 0gw')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(4,'4 cm street','hartlepool','ts24 0cm')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(5,'5 ls street','hartlepool','ts24 0ls')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(6,'6 dd street','hartlepool','ts24 0dd')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(7,'7 pupil street','hartlepool','ts24 0pp')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(8,'8 pupil street','hartlepool','ts24 0pa')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(9,'9 pupil street','hartlepool','ts24 0pb')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(10,'10 pupil street','hartlepool','ts24 0pc')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(11,'11 pupil street','hartlepool','ts24 0pd')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(12,'12 pupil street','hartlepool','ts24 0pe')
INSERTINTO [addresses] ([Address#],[Address],[Town],[PostCode])VALUES(13,'13 pupil street','hartlepool','ts24 0pf')
INSERTINTO [pupils] ([Pupil#],[Title],[FirstName],[MiddleInitials],[LastName],[PhoneNumber],[DOB],[Address#],[Instructer#],[Active])VALUES(8,'mr','pupil','','eight','01429111111','Jan 1 1980 12:00:00:000AM',7,1,'y')
INSERTINTO [pupils] ([Pupil#],[Title],[FirstName],[MiddleInitials],[LastName],[PhoneNumber],[DOB],[Address#],[Instructer#],[Active])VALUES(9,'miss','pupil',NULL,'nine','01429222222','Jan 2 1980 12:00:00:000AM',8,2,'y')
INSERTINTO [pupils] ([Pupil#],[Title],[FirstName],[MiddleInitials],[LastName],[PhoneNumber],[DOB],[Address#],[Instructer#],[Active])VALUES(10,'mrs','pupil','a','ten','01429333333','Jan 3 1980 12:00:00:000AM',9,1,'y')
INSERTINTO [pupils] ([Pupil#],[Title],[FirstName],[MiddleInitials],[LastName],[PhoneNumber],[DOB],[Address#],[Instructer#],[Active])VALUES(11,'mrs','pupil',NULL,'eleven','01429444444','Jan 4 1980 12:00:00:000AM',10,1,'y')
INSERTINTO [pupils] ([Pupil#],[Title],[FirstName],[MiddleInitials],[LastName],[PhoneNumber],[DOB],[Address#],[Instructer#],[Active])VALUES(12,'mr','pupil','b','twelve','01429555555','Jan 5 1980 12:00:00:000AM',11,2,'y')
INSERTINTO [pupils] ([Pupil#],[Title],[FirstName],[MiddleInitials],[LastName],[PhoneNumber],[DOB],[Address#],[Instructer#],[Active])VALUES(13,'mr','pupil',NULL,'thirteen','01429666666','Jan 6 1980 12:00:00:000AM',12,2,'y')
INSERTINTO [pupils] ([Pupil#],[Title],[FirstName],[MiddleInitials],[LastName],[PhoneNumber],[DOB],[Address#],[Instructer#],[Active])VALUES(14,'miss','pupil','c','seven','01429777777','Jan 7 1980 12:00:00:000AM',13,2,'y')
SETIDENTITY_INSERT addresses OFF
SETIDENTITY_INSERT lessons ON
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(3,8,1,'Dec 1 2008 9:30:00:000AM',1,2)
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(5,9,2,'Dec 1 2008 10:00:00:000AM',8,8)
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(6,10,1,'Dec 2 2008 10:00:00:000AM',12,11)
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(7,8,1,'Dec 18 2008 9:00:00:000AM',7,8)
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(8,11,2,'Dec 19 2008 2:00:00:000PM',13,13)
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(9,8,1,'Jan 1 2009 2:00:00:000PM',7,7)
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(10,8,1,'Jan 8 2009 10:00:00:000AM',7,7)
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(11,10,1,'Jan 9 2009 11:30:00:000AM',11,11)
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(12,10,1,'Jan 10 2009 11:30:00:000AM',11,12)
INSERTINTO [lessons] ([Lesson#],[Pupil#],[Instructor#],[LessonDateTime],[PickUp],[DropOff])VALUES(13,8,1,'Jan 11 2009 12:00:00:000AM',7,7)
5.SQL TABLES WITH DATA
SELECT *
FROM addresses
SELECT *
FROM pupils
SELECT *
FROM lessons
6.SQL QUERIES for PICKUP ADDRESS
The following code will give you the pick up address for alllessons
select lesson#,pupils.pupil#, pupils.lastname, lessons.pickup, addresses.address#, addresses.address
from lessons, addresses, pupils
where lessons.pupil#=pupils.pupil# and pickup = addresses.address#
7.SQL QUERIES for PICKUP ADDRESS
The following code will give you the pickup and dropoff address for lesson 3
declare @addresses TABLE(address# int, address varchar(50), town varchar(50), postcode nchar(10))
select lesson#,pupils.pupil#, pupils.lastname,
lessons.pickup,pickuppoint.address#,pickuppoint.address,
lessons.dropoff,dropoffpoint.address#,dropoffpoint.address
from lessons, addresses aspickuppoint, addresses asdropoffpoint, pupils
where lessons.pupil#=pupils.pupil#
and pickup =pickuppoint.address#
and dropoff =dropoffpoint.address#
8.CREATING VIEWS OF PICKUP and DROPOFF
CREATEVIEW InstructorSchedule(lesson#,pupil#,lastname,
pickup, pickupaddress#, pickupaddress,
dropoff, dropoffaddress#, dropoffaddress)
AS
select lesson#,pupils.pupil#, pupils.lastname,
lessons.pickup, pickuppoint.address#, pickuppoint.address,
lessons.dropoff, dropoffpoint.address#, dropoffpoint.address
from lessons, addresses as pickuppoint, addresses as dropoffpoint, pupils
where lessons.pupil#=pupils.pupil#
and pickup = pickuppoint.address#
and dropoff = dropoffpoint.address#
9.IMPROVED ERD SOLUTION
REDESIGN THE TABLES ... Lessons - remove pickup and dropoff
Create new table - LESSON-PICKUP with fields lesson# and address#
Create new table - LESSON-DROPOFF with fields lesson# and address#
10.OTHER ADVANCE SQL CODE EXAMPLES
Football League example by Darren
1
Mansha NawazSQL & Web Image ManipulationChapter 15: