USE [master]
GO
/****** Object:  Database [Week2_Assignment]    Script Date: 10/11/2021 7:59:44 AM ******/
CREATE DATABASE [Week2_Assignment]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Week2_Assignment', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\Week2_Assignment.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Week2_Assignment_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\DATA\Week2_Assignment_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO
ALTER DATABASE [Week2_Assignment] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Week2_Assignment].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Week2_Assignment] SET ANSI_NULL_DEFAULT OFF 
GO
ALTER DATABASE [Week2_Assignment] SET ANSI_NULLS OFF 
GO
ALTER DATABASE [Week2_Assignment] SET ANSI_PADDING OFF 
GO
ALTER DATABASE [Week2_Assignment] SET ANSI_WARNINGS OFF 
GO
ALTER DATABASE [Week2_Assignment] SET ARITHABORT OFF 
GO
ALTER DATABASE [Week2_Assignment] SET AUTO_CLOSE OFF 
GO
ALTER DATABASE [Week2_Assignment] SET AUTO_SHRINK OFF 
GO
ALTER DATABASE [Week2_Assignment] SET AUTO_UPDATE_STATISTICS ON 
GO
ALTER DATABASE [Week2_Assignment] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO
ALTER DATABASE [Week2_Assignment] SET CURSOR_DEFAULT  GLOBAL 
GO
ALTER DATABASE [Week2_Assignment] SET CONCAT_NULL_YIELDS_NULL OFF 
GO
ALTER DATABASE [Week2_Assignment] SET NUMERIC_ROUNDABORT OFF 
GO
ALTER DATABASE [Week2_Assignment] SET QUOTED_IDENTIFIER OFF 
GO
ALTER DATABASE [Week2_Assignment] SET RECURSIVE_TRIGGERS OFF 
GO
ALTER DATABASE [Week2_Assignment] SET  DISABLE_BROKER 
GO
ALTER DATABASE [Week2_Assignment] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO
ALTER DATABASE [Week2_Assignment] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO
ALTER DATABASE [Week2_Assignment] SET TRUSTWORTHY OFF 
GO
ALTER DATABASE [Week2_Assignment] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO
ALTER DATABASE [Week2_Assignment] SET PARAMETERIZATION SIMPLE 
GO
ALTER DATABASE [Week2_Assignment] SET READ_COMMITTED_SNAPSHOT OFF 
GO
ALTER DATABASE [Week2_Assignment] SET HONOR_BROKER_PRIORITY OFF 
GO
ALTER DATABASE [Week2_Assignment] SET RECOVERY SIMPLE 
GO
ALTER DATABASE [Week2_Assignment] SET  MULTI_USER 
GO
ALTER DATABASE [Week2_Assignment] SET PAGE_VERIFY CHECKSUM  
GO
ALTER DATABASE [Week2_Assignment] SET DB_CHAINING OFF 
GO
ALTER DATABASE [Week2_Assignment] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO
ALTER DATABASE [Week2_Assignment] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO
ALTER DATABASE [Week2_Assignment] SET DELAYED_DURABILITY = DISABLED 
GO
ALTER DATABASE [Week2_Assignment] SET QUERY_STORE = OFF
GO
USE [Week2_Assignment]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
USE [Week2_Assignment]
GO
/****** Object:  Table [dbo].[CargoType]    Script Date: 10/11/2021 7:59:44 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CargoType](
	[CargoTypeID] [int] NOT NULL,
	[CargoType] [varchar](50) NULL,
 CONSTRAINT [PK_CargoType] PRIMARY KEY CLUSTERED 
(
	[CargoTypeID] 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].[DriverDemographic]    Script Date: 10/11/2021 7:59:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DriverDemographic](
	[EmployeeNumber] [int] NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[DateOfBirth] [date] NOT NULL,
	[DateOfHire] [date] NULL,
	[CommercialDriverLicense] [varchar](50) NOT NULL,
 CONSTRAINT [PK_DriverDemographic] PRIMARY KEY CLUSTERED 
(
	[EmployeeNumber] 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].[HaulManifest]    Script Date: 10/11/2021 7:59:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HaulManifest](
	[Item] [int] IDENTITY(1,1) NOT NULL,
	[Description] [varchar](50) NULL,
	[WeightPerUnit] [float] NULL,
	[Quantity] [int] NOT NULL,
	[HaulID] [int] NOT NULL,
 CONSTRAINT [PK_HaulManifest] PRIMARY KEY CLUSTERED 
(
	[Item] 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].[HaulRecord]    Script Date: 10/11/2021 7:59:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HaulRecord](
	[HaulID] [int] NOT NULL,
	[Client] [varchar](50) NULL,
	[CargoType] [int] NOT NULL,
	[DateHaulBegan] [date] NOT NULL,
	[DateDelivered] [date] NOT NULL,
	[Mileage] [float] NOT NULL,
	[HaulNotes] [varchar](50) NULL,
	[EmployeeNumber] [int] NOT NULL,
	[TruckID] [int] NOT NULL,
 CONSTRAINT [PK_HaulRecord] PRIMARY KEY CLUSTERED 
(
	[HaulID] 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].[MaintenanceCode]    Script Date: 10/11/2021 7:59:45 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MaintenanceCode](
	[MaintenanceCodeID] [int] NOT NULL,
	[MaintenanceCode] [varchar](50) NULL,
 CONSTRAINT [PK_MaintenanceCode] PRIMARY KEY CLUSTERED 
(
	[MaintenanceCodeID] 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].[MaintenanceType]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MaintenanceType](
	[MaintenanceTypeID] [int] NOT NULL,
	[MaintenanceType] [varchar](50) NULL,
 CONSTRAINT [PK_MaintenanceType] PRIMARY KEY CLUSTERED 
(
	[MaintenanceTypeID] 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].[Trailer]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Trailer](
	[TrailerID] [int] NOT NULL,
	[TrailorType] [int] NOT NULL,
	[Capacity] [float] NOT NULL,
	[Mileage] [float] NOT NULL,
	[Description] [varchar](50) NULL,
 CONSTRAINT [PK_Trailer] PRIMARY KEY CLUSTERED 
(
	[TrailerID] 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].[TrailerType]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrailerType](
	[TrailerTypeID] [int] NOT NULL,
	[TrailerType] [varchar](50) NOT NULL,
 CONSTRAINT [PK_TrailerType] PRIMARY KEY CLUSTERED 
(
	[TrailerTypeID] 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].[Truck]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Truck](
	[TruckID] [int] NOT NULL,
	[Type] [varchar](50) NOT NULL,
	[BodyType] [varchar](50) NOT NULL,
	[LicenseNumber] [varchar](50) NOT NULL,
	[Description] [varchar](50) NOT NULL,
	[EngineType] [varchar](50) NOT NULL,
	[FuelType] [nchar](10) NOT NULL,
	[CurrentMileage] [float] NULL,
	[EmployeeNumber] [int] NOT NULL,
	[TrailerID] [int] NULL,
 CONSTRAINT [PK_Truck] PRIMARY KEY CLUSTERED 
(
	[TruckID] 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].[TruckMaintenance]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TruckMaintenance](
	[TruckMaintenanceID] [int] IDENTITY(1,1) NOT NULL,
	[TruckID] [int] NOT NULL,
	[MaintenanceStartDate] [date] NULL,
	[MaintenanceEndDate] [date] NULL,
	[MaintenanceType] [nchar](10) NULL,
	[MaintenanceCode] [nchar](10) NULL,
 CONSTRAINT [PK_TruckMaintenance] PRIMARY KEY CLUSTERED 
(
	[TruckMaintenanceID] 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
/****** Object:  Table [dbo].[TruckType]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TruckType](
	[TruckTypeID] [int] NOT NULL,
	[TruckType] [varchar](50) NOT NULL,
 CONSTRAINT [PK_TruckType] PRIMARY KEY CLUSTERED 
(
	[TruckTypeID] 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].[YesNo]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[YesNo](
	[YesNoID] [bit] NOT NULL,
	[YesNo] [varchar](50) NOT NULL,
 CONSTRAINT [PK_YesNo] PRIMARY KEY CLUSTERED 
(
	[YesNoID] 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
INSERT [dbo].[CargoType] ([CargoTypeID], [CargoType]) VALUES (1, N'Hazardous')
INSERT [dbo].[CargoType] ([CargoTypeID], [CargoType]) VALUES (2, N'Liquid')
INSERT [dbo].[CargoType] ([CargoTypeID], [CargoType]) VALUES (3, N'Refrigerated')
INSERT [dbo].[CargoType] ([CargoTypeID], [CargoType]) VALUES (4, N'Standard')
INSERT [dbo].[CargoType] ([CargoTypeID], [CargoType]) VALUES (5, N'Other')
INSERT [dbo].[DriverDemographic] ([EmployeeNumber], [Name], [DateOfBirth], [DateOfHire], [CommercialDriverLicense]) VALUES (1, N'FakeEmployee1', CAST(N'1910-01-02' AS Date), CAST(N'1910-01-03' AS Date), N'yes')
INSERT [dbo].[DriverDemographic] ([EmployeeNumber], [Name], [DateOfBirth], [DateOfHire], [CommercialDriverLicense]) VALUES (2, N'FakeEmployee2', CAST(N'1920-01-02' AS Date), CAST(N'1920-01-03' AS Date), N'no')
INSERT [dbo].[DriverDemographic] ([EmployeeNumber], [Name], [DateOfBirth], [DateOfHire], [CommercialDriverLicense]) VALUES (3, N'FakeEmployee3', CAST(N'1930-01-02' AS Date), CAST(N'1930-01-03' AS Date), N'yes')
SET IDENTITY_INSERT [dbo].[HaulManifest] ON 

INSERT [dbo].[HaulManifest] ([Item], [Description], [WeightPerUnit], [Quantity], [HaulID]) VALUES (1, N'testItem1', 46541, 1, 1)
INSERT [dbo].[HaulManifest] ([Item], [Description], [WeightPerUnit], [Quantity], [HaulID]) VALUES (2, N'testItem2', 123542, 7, 2)
INSERT [dbo].[HaulManifest] ([Item], [Description], [WeightPerUnit], [Quantity], [HaulID]) VALUES (3, N'testItem3', 984651, 4, 3)
INSERT [dbo].[HaulManifest] ([Item], [Description], [WeightPerUnit], [Quantity], [HaulID]) VALUES (4, N'testItem4', 541, 8, 1)
INSERT [dbo].[HaulManifest] ([Item], [Description], [WeightPerUnit], [Quantity], [HaulID]) VALUES (5, N'testItem5', 542, 4, 2)
INSERT [dbo].[HaulManifest] ([Item], [Description], [WeightPerUnit], [Quantity], [HaulID]) VALUES (6, N'testItem6', 123, 2, 3)
SET IDENTITY_INSERT [dbo].[HaulManifest] OFF
INSERT [dbo].[HaulRecord] ([HaulID], [Client], [CargoType], [DateHaulBegan], [DateDelivered], [Mileage], [HaulNotes], [EmployeeNumber], [TruckID]) VALUES (1, N'1', 2, CAST(N'2000-01-01' AS Date), CAST(N'2000-02-01' AS Date), 646543, N'notes', 1, 1)
INSERT [dbo].[HaulRecord] ([HaulID], [Client], [CargoType], [DateHaulBegan], [DateDelivered], [Mileage], [HaulNotes], [EmployeeNumber], [TruckID]) VALUES (2, N'2', 2, CAST(N'2005-01-01' AS Date), CAST(N'2006-02-01' AS Date), 64653123, N'notes', 2, 2)
INSERT [dbo].[HaulRecord] ([HaulID], [Client], [CargoType], [DateHaulBegan], [DateDelivered], [Mileage], [HaulNotes], [EmployeeNumber], [TruckID]) VALUES (3, N'3', 1, CAST(N'2005-03-01' AS Date), CAST(N'2006-02-05' AS Date), 689461, N'notes', 3, 3)
INSERT [dbo].[HaulRecord] ([HaulID], [Client], [CargoType], [DateHaulBegan], [DateDelivered], [Mileage], [HaulNotes], [EmployeeNumber], [TruckID]) VALUES (4, N'3', 1, CAST(N'2006-02-02' AS Date), CAST(N'2006-02-25' AS Date), 689461, N'notes', 3, 3)
INSERT [dbo].[MaintenanceCode] ([MaintenanceCodeID], [MaintenanceCode]) VALUES (1, N'Routine')
INSERT [dbo].[MaintenanceCode] ([MaintenanceCodeID], [MaintenanceCode]) VALUES (2, N'Unscheduled')
INSERT [dbo].[MaintenanceType] ([MaintenanceTypeID], [MaintenanceType]) VALUES (1, N'Engine')
INSERT [dbo].[MaintenanceType] ([MaintenanceTypeID], [MaintenanceType]) VALUES (2, N'Transmission')
INSERT [dbo].[MaintenanceType] ([MaintenanceTypeID], [MaintenanceType]) VALUES (3, N'Tires')
INSERT [dbo].[MaintenanceType] ([MaintenanceTypeID], [MaintenanceType]) VALUES (4, N'Body')
INSERT [dbo].[MaintenanceType] ([MaintenanceTypeID], [MaintenanceType]) VALUES (5, N'Electrical')
INSERT [dbo].[MaintenanceType] ([MaintenanceTypeID], [MaintenanceType]) VALUES (6, N'Hydraulic')
INSERT [dbo].[MaintenanceType] ([MaintenanceTypeID], [MaintenanceType]) VALUES (7, N'Pneumatic')
INSERT [dbo].[Trailer] ([TrailerID], [TrailorType], [Capacity], [Mileage], [Description]) VALUES (1, 1, 32164645, 874646513, N'desc')
INSERT [dbo].[Trailer] ([TrailerID], [TrailorType], [Capacity], [Mileage], [Description]) VALUES (2, 2, 96796431, 968468512, N'desc')
INSERT [dbo].[Trailer] ([TrailerID], [TrailorType], [Capacity], [Mileage], [Description]) VALUES (3, 3, 9687, 4651532, N'desc')
INSERT [dbo].[Trailer] ([TrailerID], [TrailorType], [Capacity], [Mileage], [Description]) VALUES (4, 4, 987465321, 875674, N'desc')
INSERT [dbo].[TrailerType] ([TrailerTypeID], [TrailerType]) VALUES (1, N'Tanker')
INSERT [dbo].[TrailerType] ([TrailerTypeID], [TrailerType]) VALUES (2, N'Flat Box')
INSERT [dbo].[TrailerType] ([TrailerTypeID], [TrailerType]) VALUES (3, N'Box')
INSERT [dbo].[TrailerType] ([TrailerTypeID], [TrailerType]) VALUES (4, N'Refrigerated')
INSERT [dbo].[Truck] ([TruckID], [Type], [BodyType], [LicenseNumber], [Description], [EngineType], [FuelType], [CurrentMileage], [EmployeeNumber], [TrailerID]) VALUES (1, N'Long Haul', N'Tractor Trailer', N'1297y1903h', N'desc', N'Diesel', N'Diesel    ', 2334, 1, 1)
INSERT [dbo].[Truck] ([TruckID], [Type], [BodyType], [LicenseNumber], [Description], [EngineType], [FuelType], [CurrentMileage], [EmployeeNumber], [TrailerID]) VALUES (2, N'Long Haul', N'Tractor Trailer', N'907y123944h1', N'desc', N'Diesel', N'Diesel    ', 1231, 1, 1)
INSERT [dbo].[Truck] ([TruckID], [Type], [BodyType], [LicenseNumber], [Description], [EngineType], [FuelType], [CurrentMileage], [EmployeeNumber], [TrailerID]) VALUES (3, N'Short Haul', N'Single Unit', N'087808uy06y9t', N'desc', N'Diesel', N'Diesel    ', 123198465, 1, 1)
INSERT [dbo].[Truck] ([TruckID], [Type], [BodyType], [LicenseNumber], [Description], [EngineType], [FuelType], [CurrentMileage], [EmployeeNumber], [TrailerID]) VALUES (4, N'Short Haul', N'Single Unit', N'asdh9hy9t', N'desc', N'Not Diesel', N'Gasoline  ', 123198465, 1, 1)
SET IDENTITY_INSERT [dbo].[TruckMaintenance] ON 

INSERT [dbo].[TruckMaintenance] ([TruckMaintenanceID], [TruckID], [MaintenanceStartDate], [MaintenanceEndDate], [MaintenanceType], [MaintenanceCode]) VALUES (1, 1, CAST(N'2000-01-01' AS Date), CAST(N'2000-05-03' AS Date), N'1         ', N'1         ')
INSERT [dbo].[TruckMaintenance] ([TruckMaintenanceID], [TruckID], [MaintenanceStartDate], [MaintenanceEndDate], [MaintenanceType], [MaintenanceCode]) VALUES (2, 2, CAST(N'2000-03-06' AS Date), CAST(N'2000-09-12' AS Date), N'2         ', N'2         ')
INSERT [dbo].[TruckMaintenance] ([TruckMaintenanceID], [TruckID], [MaintenanceStartDate], [MaintenanceEndDate], [MaintenanceType], [MaintenanceCode]) VALUES (3, 3, CAST(N'2000-04-05' AS Date), CAST(N'2000-10-11' AS Date), N'3         ', N'3         ')
SET IDENTITY_INSERT [dbo].[TruckMaintenance] OFF
INSERT [dbo].[TruckType] ([TruckTypeID], [TruckType]) VALUES (1, N'Tanker')
INSERT [dbo].[TruckType] ([TruckTypeID], [TruckType]) VALUES (2, N'Flat Bed')
INSERT [dbo].[TruckType] ([TruckTypeID], [TruckType]) VALUES (3, N'Box')
INSERT [dbo].[TruckType] ([TruckTypeID], [TruckType]) VALUES (4, N'Refrigerated')
INSERT [dbo].[YesNo] ([YesNoID], [YesNo]) VALUES (1, N'yes')
ALTER TABLE [dbo].[HaulManifest]  WITH CHECK ADD  CONSTRAINT [FK_HaulManifest_HaulRecord] FOREIGN KEY([HaulID])
REFERENCES [dbo].[HaulRecord] ([HaulID])
GO
ALTER TABLE [dbo].[HaulManifest] CHECK CONSTRAINT [FK_HaulManifest_HaulRecord]
GO
ALTER TABLE [dbo].[HaulRecord]  WITH CHECK ADD  CONSTRAINT [FK_HaulRecord_DriverDemographic1] FOREIGN KEY([EmployeeNumber])
REFERENCES [dbo].[DriverDemographic] ([EmployeeNumber])
GO
ALTER TABLE [dbo].[HaulRecord] CHECK CONSTRAINT [FK_HaulRecord_DriverDemographic1]
GO
ALTER TABLE [dbo].[HaulRecord]  WITH CHECK ADD  CONSTRAINT [FK_HaulRecord_Truck] FOREIGN KEY([EmployeeNumber])
REFERENCES [dbo].[Truck] ([TruckID])
GO
ALTER TABLE [dbo].[HaulRecord] CHECK CONSTRAINT [FK_HaulRecord_Truck]
GO
ALTER TABLE [dbo].[Truck]  WITH CHECK ADD  CONSTRAINT [FK_Truck_DriverDemographic] FOREIGN KEY([EmployeeNumber])
REFERENCES [dbo].[DriverDemographic] ([EmployeeNumber])
GO
ALTER TABLE [dbo].[Truck] CHECK CONSTRAINT [FK_Truck_DriverDemographic]
GO
ALTER TABLE [dbo].[Truck]  WITH CHECK ADD  CONSTRAINT [FK_Truck_Trailer] FOREIGN KEY([TrailerID])
REFERENCES [dbo].[Trailer] ([TrailerID])
GO
ALTER TABLE [dbo].[Truck] CHECK CONSTRAINT [FK_Truck_Trailer]
GO
ALTER TABLE [dbo].[TruckMaintenance]  WITH CHECK ADD  CONSTRAINT [FK_TruckMaintenance_Truck1] FOREIGN KEY([TruckID])
REFERENCES [dbo].[Truck] ([TruckID])
GO
ALTER TABLE [dbo].[TruckMaintenance] CHECK CONSTRAINT [FK_TruckMaintenance_Truck1]
GO
/****** Object:  StoredProcedure [dbo].[DeleteDriverDemographic]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteDriverDemographic] (@EmployeeNumber int)
as
begin
	DELETE FROM DriverDemographic Where EmployeeNumber=@EmployeeNumber;
end
GO
/****** Object:  StoredProcedure [dbo].[DeleteHaulManifest]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteHaulManifest] (@Item int)
as
begin
	DELETE FROM HaulManifest Where Item=@Item;
end
GO
/****** Object:  StoredProcedure [dbo].[DeleteHaulRecord]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteHaulRecord] (@HaulID int)
as
begin
	DELETE FROM HaulRecord Where HaulID=@HaulID;
end
GO
/****** Object:  StoredProcedure [dbo].[DeleteTrailer]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteTrailer] (@TrailerID int)
as
begin
	DELETE FROM Trailer Where TrailerID=@TrailerID;
end
GO
/****** Object:  StoredProcedure [dbo].[DeleteTruck]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteTruck] (@TruckID int)
as
begin
	DELETE FROM Truck Where TruckID=@TruckID;
end
GO
/****** Object:  StoredProcedure [dbo].[DeleteTrunkMaintenance]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[DeleteTrunkMaintenance] (@TruckMaintenanceID int)
as	
begin
	DELETE FROM TruckMaintenance Where TruckMaintenanceID=@TruckMaintenanceID;
end
GO
/****** Object:  StoredProcedure [dbo].[InsertDriverDemographic]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--CREATE PROCEDURE InsertTruck (@Type int, @BodyType int, @LicenseNumber varchar, @Description varchar, @EngineType int, @FuelType int, @EngineType int, @FuelType int, @CurrentMileage float, @EmployeeNumber int, @TrailerID int)
--as
--begin
--	INSERT INTO Truck (Type, BodyType, LicenseNumber, LicenseNumber, Description, EngineType, FuelType, EngineType, FuelType, CurrentMileage, EmployeeNumber, TrailerID)
--	VALUES (@Type, @BodyType, @LicenseNumber, @Description, @EngineType, @FuelType, @EngineType, @FuelType, @CurrentMileage, @EmployeeNumber, @TrailerID);
--end

--CREATE PROCEDURE InsertTruckMaintenance (@TruckID int, @MaintenanceStartDate date, @MaintenanceEndDate date, @MaintenanceType int, @MaintenanceCode int)
--as
--begin
--	INSERT INTO TruckMaintenance (TruckID, MaintenanceStartDate, MaintenanceEndDate, MaintenanceType, MaintenanceCode)
--	VALUES (@TruckID, @MaintenanceStartDate, @MaintenanceEndDate, @MaintenanceType, @MaintenanceCode);
--end

CREATE PROCEDURE [dbo].[InsertDriverDemographic] (@Name varchar, @DateOfBirth Date, @DateOfHire date, @CommercialDriverLicense int)
as
begin
	INSERT INTO DriverDemographic (Name, DateOfBirth, DateOfHire, CommercialDriverLicense)
	VALUES (@Name, @DateOfBirth, @DateOfHire, @CommercialDriverLicense);
end

--CREATE PROCEDURE InsertHaulRecord (@Client int, @CargoType int, @DateHaulBegan date, @DateDelivered date, @Mileage float, @HaulNotes varchar, @EmployeeNumber int, @TruckID int)
--as
--begin
--	INSERT INTO HaulRecord (Client, CargoType, DateHaulBegan, DateDelivered, Mileage, HaulNotes, EmployeeNumber, TruckID)
--	VALUES (@Client, @CargoType, @DateHaulBegan, @DateDelivered, @Mileage, @HaulNotes, @EmployeeNumber, @TruckID);
--end

--CREATE PROCEDURE InsertHaulManifest (@Description varchar, @WeightPerUnit float, @Quantity int, @HaulID int)
--as
--begin
--	INSERT INTO HaulManifest (Description, WeightPerUnit, Quantity, HaulID)
--	VALUES (@Description, @WeightPerUnit, @Quantity, @HaulID);
--end

--CREATE PROCEDURE InsertTrailer (@TrailorType int, @Capacity float, @Mileage float, @Description varchar)
--as
--begin
--	INSERT INTO Trailer (TrailerType, Capacity, Mileage, Description)
--	VALUES (@TrailerType, @Capacity, @Mileage, @Description);
--end

--CREATE PROCEDURE DeleteTruck (@TruckID int)
--as
--begin
--	DELETE FROM Truck Where TruckID=@TruckID;
--end

--CREATE PROCEDURE DeleteTrunkMaintenance (@TruckMaintenanceID int)
--as	
--begin
--	DELETE FROM TruckMaintenance Where TruckMaintenanceID=@TruckMaintenanceID;
--end

--CREATE PROCEDURE DeleteDriverDemographic (@EmployeeNumber int)
--as
--begin
--	DELETE FROM DriverDemographic Where EmployeeNumber=@EmployeeNumber;
--end

--CREATE PROCEDURE DeleteHaulRecord (@HaulID int)
--as
--begin
--	DELETE FROM HaulRecord Where HaulID=@HaulID;
--end

--CREATE PROCEDURE DeleteHaulManifest (@Item int)
--as
--begin
--	DELETE FROM HaulManifest Where Item=@Item;
--end

--CREATE PROCEDURE DeleteTrailer (@TrailerID int)
--as
--begin
--	DELETE FROM Trailer Where TrailerID=@TrailerID;
--end

GO
/****** Object:  StoredProcedure [dbo].[InsertHaulManifest]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertHaulManifest] (@Description varchar, @WeightPerUnit float, @Quantity int, @HaulID int)
as
begin
	INSERT INTO HaulManifest (Description, WeightPerUnit, Quantity, HaulID)
	VALUES (@Description, @WeightPerUnit, @Quantity, @HaulID);
end
GO
/****** Object:  StoredProcedure [dbo].[InsertHaulRecord]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertHaulRecord] (@Client int, @CargoType int, @DateHaulBegan date, @DateDelivered date, @Mileage float, @HaulNotes varchar, @EmployeeNumber int, @TruckID int)
as
begin
	INSERT INTO HaulRecord (Client, CargoType, DateHaulBegan, DateDelivered, Mileage, HaulNotes, EmployeeNumber, TruckID)
	VALUES (@Client, @CargoType, @DateHaulBegan, @DateDelivered, @Mileage, @HaulNotes, @EmployeeNumber, @TruckID);
end
GO
/****** Object:  StoredProcedure [dbo].[InsertTrailer]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--CREATE PROCEDURE InsertTruck (@Type int, @BodyType int, @LicenseNumber varchar, @Description varchar, @EngineType int, @FuelType int, @EngineType int, @FuelType int, @CurrentMileage float, @EmployeeNumber int, @TrailerID int)
--as
--begin
--	INSERT INTO Truck (Type, BodyType, LicenseNumber, LicenseNumber, Description, EngineType, FuelType, EngineType, FuelType, CurrentMileage, EmployeeNumber, TrailerID)
--	VALUES (@Type, @BodyType, @LicenseNumber, @Description, @EngineType, @FuelType, @EngineType, @FuelType, @CurrentMileage, @EmployeeNumber, @TrailerID);
--end

--CREATE PROCEDURE InsertTruckMaintenance (@TruckID int, @MaintenanceStartDate date, @MaintenanceEndDate date, @MaintenanceType int, @MaintenanceCode int)
--as
--begin
--	INSERT INTO TruckMaintenance (TruckID, MaintenanceStartDate, MaintenanceEndDate, MaintenanceType, MaintenanceCode)
--	VALUES (@TruckID, @MaintenanceStartDate, @MaintenanceEndDate, @MaintenanceType, @MaintenanceCode);
--end

--CREATE PROCEDURE InsertDriverDemographic (@Name varchar, @DateOfBirth Date, @DateOfHire date, @CommercialDriverLicense int)
--as
--begin
--	INSERT INTO DriverDemographic (Name, DateOfBirth, DateOfHire, CommercialDriverLicense)
--	VALUES (@Name, @DateOfBirth, @DateOfHire, @CommercialDriverLicense);
--end

--CREATE PROCEDURE InsertHaulRecord (@Client int, @CargoType int, @DateHaulBegan date, @DateDelivered date, @Mileage float, @HaulNotes varchar, @EmployeeNumber int, @TruckID int)
--as
--begin
--	INSERT INTO HaulRecord (Client, CargoType, DateHaulBegan, DateDelivered, Mileage, HaulNotes, EmployeeNumber, TruckID)
--	VALUES (@Client, @CargoType, @DateHaulBegan, @DateDelivered, @Mileage, @HaulNotes, @EmployeeNumber, @TruckID);
--end

--CREATE PROCEDURE InsertHaulManifest (@Description varchar, @WeightPerUnit float, @Quantity int, @HaulID int)
--as
--begin
--	INSERT INTO HaulManifest (Description, WeightPerUnit, Quantity, HaulID)
--	VALUES (@Description, @WeightPerUnit, @Quantity, @HaulID);
--end

CREATE PROCEDURE [dbo].[InsertTrailer] (@TrailerType int, @Capacity float, @Mileage float, @Description varchar)
as
begin
	INSERT INTO Trailer (TrailorType, Capacity, Mileage, Description)
	VALUES (@TrailerType, @Capacity, @Mileage, @Description);
end

--CREATE PROCEDURE DeleteTruck (@TruckID int)
--as
--begin
--	DELETE FROM Truck Where TruckID=@TruckID;
--end

--CREATE PROCEDURE DeleteTrunkMaintenance (@TruckMaintenanceID int)
--as	
--begin
--	DELETE FROM TruckMaintenance Where TruckMaintenanceID=@TruckMaintenanceID;
--end

--CREATE PROCEDURE DeleteDriverDemographic (@EmployeeNumber int)
--as
--begin
--	DELETE FROM DriverDemographic Where EmployeeNumber=@EmployeeNumber;
--end

--CREATE PROCEDURE DeleteHaulRecord (@HaulID int)
--as
--begin
--	DELETE FROM HaulRecord Where HaulID=@HaulID;
--end

--CREATE PROCEDURE DeleteHaulManifest (@Item int)
--as
--begin
--	DELETE FROM HaulManifest Where Item=@Item;
--end

--CREATE PROCEDURE DeleteTrailer (@TrailerID int)
--as
--begin
--	DELETE FROM Trailer Where TrailerID=@TrailerID;
--end

GO
/****** Object:  StoredProcedure [dbo].[procedureDriverAudit]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[procedureDriverAudit] @driverID int
as
select * from Truck
	left join Truck as T on Truck.EmployeeNumber = @DriverID

GO
/****** Object:  StoredProcedure [dbo].[procedureHaulInventory]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[procedureHaulInventory] @dateStart date, @dateEnd date, @truckID int
as
select * from HaulManifest,
	HaulRecord,
	Truck.TruckID, 
	HaulRecord.ID,
	HaulRecord.DateHaulBegan,
	HaulRecord.DateDelivered,
	HaulRecord.CargoType as CargoType
	left join HaulRecord as HR on Haulrecord.TruckID = @truckID
	where (HaulRecord.DateHaulBegan >= @dateStart AND HaulRecord.DateDelivered <= @dateEnd)
	
	order by
	HaulRecord.DateDelivered desc
GO
/****** Object:  StoredProcedure [dbo].[procedureHaulRecord]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[procedureHaulRecord] @dateStart date, @dateEnd date, @truckID int
as
select * from Truck.TruckID, 
	HaulRecord.ID,
	HaulRecord.DateHaulBegan,
	HaulRecord.DateDelivered,
	HaulRecord.CargoType as CargoType
	left join HaulRecord as HR on Haulrecord.TruckID = @truckID
	where (HaulRecord.DateHaulBegan >= @dateStart AND HaulRecord.DateDelivered <= @dateEnd)
	
	order by
	HaulRecord.DateDelivered desc
GO
/****** Object:  StoredProcedure [dbo].[procedureTruckMaintenance]    Script Date: 10/11/2021 7:59:46 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[procedureTruckMaintenance] @dateStart date, @dateEnd date
as
select * from Truck.TruckID, 
	TruckMaintenance.MaintenanceCode, 
	TruckMaintenance.MaintenanceType as MaintenceType, 
	TruckMaintenance.MaintenanceStartDate, 
	TruckMaintenance.MaintenanceEndDate
where (TruckMaintenance.MaintenanceStartDate >= @dateStart AND TruckMaintenance.MaintenanceEndDate <= @dateEnd)
order by
	TruckMaintenance.MaintenanceStartDate desc


GO
USE [master]
GO
ALTER DATABASE [Week2_Assignment] SET  READ_WRITE 
GO