Levels Tought:
Elementary,Middle School,High School,College,University,PHD
Teaching Since: | Apr 2017 |
Last Sign in: | 5 Weeks Ago, 3 Days Ago |
Questions Answered: | 4870 |
Tutorials Posted: | 4863 |
MBA IT, Mater in Science and Technology
Devry
Jul-1996 - Jul-2000
Professor
Devry University
Mar-2010 - Oct-2016
Â
All of the following problems should be performed using the AutoDealer3 database (attached) .Turn in ONE file on Moodle called homework5.sql and be sure to use inline comments in your SQL code to indicate the problem numbers. Comments are written like this: /* Problem 1 */.
1. (2 points) Using the Mechanics and ServiceAppointments tables, create an SQL query that will list out the number of service appointments each mechanic has each day. The results should look like this (be sure to get the column headings correct!):
FirstName |
LastName |
AppointmentDate |
# of Appointments |
Buck |
Thompson |
2013-02-15 |
7 |
Mac |
Jones |
2013-02-15 |
7 |
2. (2 points) Using the Customers, Cars, and ServiceAppointments table, create an SQL query that will list, in chronological order, the customers’ appointment dates and times, as well as the year, manufacturer, and model of their cars. The output should look like this:
FirstName |
Lastname |
Year |
Manufacturer |
Model |
AppointmentDate |
AppointmentTime |
Ron |
Naik |
2004 |
Volkswagen |
Tiguan |
2013-02-15 |
10:00:00 |
Sherry |
Traylor |
2005 |
Chevrolet |
Suburban |
2013-02-15 |
10:00:00 |
Matt |
Crisp |
1998 |
Chysler |
300 |
2013-02-15 |
11:00:00 |
Dana |
Roman |
2002 |
Ford |
Focus |
2013-02-15 |
11:00:00 |
Shannon |
Schimelman |
2001 |
Nissan |
Frontier |
2013-02-15 |
11:45:00 |
Pradeep |
Boglovits |
2003 |
Chevrolet |
Tahoe |
2013-02-15 |
13:00:00 |
Derek |
Curlis |
2009 |
Chevrolet |
Malibu |
2013-02-15 |
13:00:00 |
Ana |
Prager |
2011 |
Chevrolet |
Camaro |
2013-02-15 |
13:30:00 |
Jeff |
Allen |
2012 |
Volkswagen |
Jetta |
2013-02-15 |
14:00:00 |
Melanie |
Westrick |
1999 |
Nissan |
Cube |
2013-02-15 |
14:00:00 |
Ron |
Naik |
1997 |
Nissan |
Altima |
2013-02-15 |
15:00:00 |
Dana |
Roman |
2006 |
Honda |
Pilot |
2013-02-15 |
15:00:00 |
Pradeep |
Boglovits |
2008 |
Ford |
Fiesta |
2013-02-15 |
16:00:00 |
Matt |
Crisp |
2003 |
Toyota |
Tundra |
2013-02-15 |
16:00:00 |
3. (3 points) Write an SQL query to create a list of all the services ever performed, including the number of times they have been performed and the total cost of those services.  You will need to use the AppointmentServices table, the ServiceAppointments table, and the Services table.  The output should be sorted in descending order based on total cost. Here is what the output should look like (please note the column header names):
ServiceName |
# times performed |
Total Cost |
Oil Change |
5 |
149.75 |
Maintenance Level 3 |
1 |
119.95 |
Maintenance Level 2 |
1 |
79.95 |
Radiator Flush |
1 |
79.95 |
Tire Rotation |
3 |
59.85 |
Inspection |
4 |
59.80 |
Maintenance Level 1 |
1 |
39.95 |
4. (3 points) Write an SQL query that will show the number of services performed for each customer and the total cost of those services. You will need information from the Customers, ServiceAppointments, AppointmentServices, and Services tables. The output should be sorted by customer last name. Here is what the output should look like (make sure column headers are correct!):
FirstName |
LastName |
# services performed |
Total Cost |
Jeff |
Allen |
1 |
14.95 |
Pradeep |
Boglovits |
2 |
34.90 |
Matt |
Crisp |
3 |
64.85 |
Derek |
Curlis |
1 |
119.95 |
Ron |
Naik |
2 |
44.90 |
Ana |
Prager |
1 |
29.95 |
Dana |
Roman |
1 |
79.95 |
Shannon |
Schimelman |
2 |
109.90 |
Sherry |
Traylor |
1 |
39.95 |
Melanie |
Westrick |
2 |
49.90 |
(2 points)Â Write an SQL query to create the following output:
Date |
Time |
Mechanic |
Service |
Cost |
Customer |
2013-02-15 |
10:00:00 |
Mac Jones |
Inspection |
$14.95 |
Ron Naik |
2013-02-15 |
10:00:00 |
Mac Jones |
Oil Change |
$29.95 |
Ron Naik |
2013-02-15 |
11:00:00 |
Mac Jones |
Inspection |
$14.95 |
Matt Crisp |
2013-02-15 |
11:00:00 |
Mac Jones |
Oil Change |
$29.95 |
Matt Crisp |
2013-02-15 |
11:00:00 |
Mac Jones |
Tire Rotation |
$19.95 |
Matt Crisp |
2013-02-15 |
11:45:00 |
Mac Jones |
Oil Change |
$29.95 |
Shannon Schimelman |
2013-02-15 |
11:45:00 |
Mac Jones |
Radiator Flush |
$79.95 |
Shannon Schimelman |
2013-02-15 |
13:00:00 |
Mac Jones |
Inspection |
$14.95 |
Pradeep Boglovits |
2013-02-15 |
13:00:00 |
Mac Jones |
Tire Rotation |
$19.95 |
Pradeep Boglovits |
2013-02-15 |
14:00:00 |
Mac Jones |
Inspection |
$14.95 |
Jeff Allen |
2013-02-15 |
10:00:00 |
Buck Thompson |
Maintenance Level 1 |
$39.95 |
Sherry Traylor |
2013-02-15 |
11:00:00 |
Buck Thompson |
Maintenance Level 2 |
$79.95 |
Dana Roman |
2013-02-15 |
13:00:00 |
Buck Thompson |
Maintenance Level 3 |
$119.95 |
Derek Curlis |
2013-02-15 |
13:30:00 |
Buck Thompson |
Oil Change |
$29.95 |
Ana Prager |
2013-02-15 |
14:00:00 |
Buck Thompson |
Oil Change |
$29.95 |
Melanie Westrick |
2013-02-15 |
14:00:00 |
Buck Thompson |
Tire Rotation |
$19.95 |
Melanie Westrick |
Please pay careful attention to the column headings as well as how the data in the Cost column is formatted. (HINT: numeric values may need to be converted to a non-numeric type in order to combine them with any special characters in the results). Also note how the first and last names have been combined and how the data is sorted.
Â
Â
USE [master]
GO
/****** Object: Database [AutoDealer3]
Script Date: 2/21/2014 8:58:21 AM
******/
CREATE DATABASE [AutoDealer3]
GO
USE [AutoDealer3]
GO
/****** Object: Table [dbo].[AppointmentServices]
Script Date: 2/21/2014
8:58:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AppointmentServices](
[AppointmentID] [int] NOT NULL,
[ServiceID] [char](2) NOT NULL,
CONSTRAINT [PK_AppointmentServices] PRIMARY KEY CLUSTERED
(
[AppointmentID] ASC,
[ServiceID] 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].[Banks]
Script Date: 2/21/2014 8:58:23 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Banks](
[RoutingNumber] [char](9) NOT NULL,
[BankName] [varchar](50) NULL,
CONSTRAINT [PK_Banks] PRIMARY KEY CLUSTERED
(
[RoutingNumber] 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].[Cars]
Script Date: 2/21/2014 8:58:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Cars](
[VehicleID] [char](15) NOT NULL,
[Manufacturer] [varchar](20) NULL,
[Model] [varchar](20) NULL,
[Color] [varchar](12) NULL,
[Year] [int] NULL, [Cost] [decimal](12, 2) NULL,
CONSTRAINT [PK_Cars] PRIMARY KEY CLUSTERED
( [VehicleID] 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].[CarSales]
Script Date: 2/21/2014 8:58:23 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CarSales](
[CustomerID] [int] NOT NULL,
[VehicleID] [char](15) NOT NULL,
[SalesDate] [date] NOT NULL,
[SalesPersonID] [int] NOT NULL,
[BankID] [char](9) NOT NULL,
[Price] [decimal](9, 2) NOT NULL,
CONSTRAINT [PK_CarSales] PRIMARY KEY CLUSTERED
(
[CustomerID] ASC,
[VehicleID] 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].[Customers]
Script Date: 2/21/2014 8:58:23 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Customers](
[ID] [int] NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[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].[Mechanics]
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON Script Date: 2/21/2014 8:58:23 AM GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Mechanics](
[ID] [int] NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[StartDate] [date] NULL,
CONSTRAINT [PK_Mechanics] PRIMARY KEY CLUSTERED
(
[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].[SalesPeople]
Script Date: 2/21/2014 8:58:23 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SalesPeople](
[ID] [int] NOT NULL,
[FirstName] [varchar](50) NULL,
[LastName] [varchar](50) NULL,
[StartDate] [date] NULL,
CONSTRAINT [PK_SalesPeople] PRIMARY KEY CLUSTERED
(
[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].[SalesVisitInterests]
Script Date: 2/21/2014
8:58:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SalesVisitInterests](
[SalesVisitID] [int] NOT NULL,
[VehicleID] [char](15) NOT NULL,
CONSTRAINT [PK_SalesVisitInterests] PRIMARY KEY CLUSTERED
(
[SalesVisitID] ASC,
[VehicleID] 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].[SalesVisits] Script Date: 2/21/2014 8:58:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SalesVisits](
[SalesVisitID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NOT NULL,
[VisitDate] [date] NOT NULL,
CONSTRAINT [PK_SalesVisits] PRIMARY KEY CLUSTERED
(
[SalesVisitID] 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].[ServiceAppointments]
Script Date: 2/21/2014
8:58:23 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ServiceAppointments](
[AppointmentID] [int] NOT NULL,
[CustomerID] [int] NOT NULL,
[VehicleID] [char](15) NOT NULL,
[AppointmentDate] [date] NOT NULL,
[AppointmentTime] [time](0) NOT NULL,
[MechanicID] [int] NOT NULL,
CONSTRAINT [PK_ServiceAppointments] PRIMARY KEY CLUSTERED
(
[AppointmentID] 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].[Services]
Script Date: 2/21/2014 8:58:23 AM
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Services](
[ServiceID] [char](2) NOT NULL,
[ServiceName] [varchar](50) NULL,
[AllottedTime] [int] NULL,
[Cost] [decimal](6, 2) NULL,
CONSTRAINT [PK_Services] PRIMARY KEY CLUSTERED
(
[ServiceID] 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].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (1,
N'IN')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (1,
N'OC')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (2,
N'M1')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (3,
N'IN')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (3,
N'OC')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (3,
N'TR')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (4,
N'M2')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (5,
N'OC')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (5,
N'RF')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (6,
N'IN')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (6,
N'TR')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (7,
N'M3')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (8,
N'OC')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (9,
N'IN')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (10,
N'OC')
GO
INSERT [dbo].[AppointmentServices] ([AppointmentID], [ServiceID]) VALUES (10,
N'TR')
GO
INSERT [dbo].[Banks] ([RoutingNumber], [BankName]) VALUES (N'053000196', N'Bank
of America')
GO
INSERT [dbo].[Banks] ([RoutingNumber], [BankName]) VALUES (N'053112712',
N'Aquesta')
GO
INSERT [dbo].[Banks] ([RoutingNumber], [BankName]) VALUES (N'055003492', N'HSBC
National Bank')
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'ATL968LXW390R ', N'Volkswagen', N'Tiguan', N'Red', 2004,
CAST(20720.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year], [Cost]) VALUES (N'ATN939EHX518P ', N'Chevrolet', N'Suburban', N'Blue', 2005,
CAST(30937.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'AXV928XPA557L ', N'Chysler', N'300', N'Blue', 1998,
CAST(14732.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'AYK603STJ915Q ', N'Ford', N'Focus', N'Black', 2002,
CAST(24317.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'BAD307HWL202L ', N'Nissan', N'Frontier', N'Red', 2001,
CAST(30012.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'CIX452LCA824J ', N'Chevrolet', N'Tahoe', N'Orange', 2003,
CAST(10993.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'CPX560LRH462Q ', N'Chevrolet', N'Malibu', N'White', 2009,
CAST(11669.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'CXE488ZZP310K ', N'Chevrolet', N'Camaro', N'Blue', 2011,
CAST(15977.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'DKW986LRF744K ', N'Volkswagen', N'Jetta', N'Black', 2012,
CAST(21749.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'DLY856JOW065J ', N'Nissan', N'Cube', N'Gold', 1999,
CAST(18908.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'EBR160TJU474B ', N'Nissan', N'Altima', N'Gold', 1997,
CAST(26906.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'EHZ337VIS833C ', N'Honda', N'Pilot', N'Black', 2006,
CAST(26300.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'ELX404KXP301U ', N'Ford', N'Fiesta', N'Black', 2008,
CAST(15232.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'EOI158MTU291A ', N'Toyota', N'Tundra', N'Silver', 2003,
CAST(15147.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'EQV536NJH730J ', N'Ford', N'Mustang', N'Black', 2007,
CAST(21042.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'ESP390LEW281B ', N'Volkswagen', N'Beetle', N'Black', 2009,
CAST(23259.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'ESX208WZS412R ', N'Chevrolet', N'Malibu', N'White', 2006,
CAST(19707.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year], [Cost]) VALUES (N'FBB715DSQ874S ', N'Nissan', N'Frontier', N'Black', 2002,
CAST(20944.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'FKD979RJF195E ', N'Ford', N'Fiesta', N'Black', 2002,
CAST(17153.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'FRC958WNB023P ', N'Ford', N'Focus', N'Black', 2013,
CAST(14597.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'FUV377FYK349H ', N'Chevrolet', N'Tahoe', N'Silver', 2012,
CAST(28047.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'FZM316GZL472R ', N'Toyota', N'Camry', N'White', 2006,
CAST(12223.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'GHV305MEG301R ', N'Toyota', N'Camry', N'Silver', 2009,
CAST(9869.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'GPT973ROF970T ', N'Volkswagen', N'Beetle', N'Black', 2008,
CAST(21379.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'GWH698TWX269W ', N'Nissan', N'Cube', N'Gold', 2008,
CAST(26908.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'GWX444ITV848R ', N'Ford', N'Fiesta', N'Black', 2007,
CAST(14770.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'HDR469FNP011S ', N'Ford', N'Fiesta', N'Silver', 2011,
CAST(10761.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'HTU791AKF984L ', N'Honda', N'Civic', N'Orange', 2009,
CAST(11944.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'HWP073RTW134I ', N'Chevrolet', N'Corvette', N'Silver', 2003,
CAST(22264.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'IIL684DZK615B ', N'Chevrolet', N'Monte Carlo', N'Gold', 2000,
CAST(19022.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'IMP048HYB446H ', N'Toyota', N'4Runner', N'Blue', 1998,
CAST(24436.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'ISL138RYB234C ', N'Nissan', N'Armada', N'White', 1998,
CAST(30646.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'IUO393MFL917C ', N'Chevrolet', N'Monte Carlo', N'Blue', 2004,
CAST(20045.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year], [Cost]) VALUES (N'IXC994SDU592F ', N'Nissan', N'Cube', N'Black', 1999,
CAST(21761.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'JPE149FJA732O ', N'Nissan', N'Armada', N'Blue', 2011,
CAST(9424.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'JQZ148KRR372F ', N'Honda', N'Crosstour', N'Black', 2005,
CAST(12675.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'KEQ147IOU683X ', N'Chevrolet', N'Monte Carlo', N'White',
2005, CAST(15203.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'KFA495UQT464S ', N'Chevrolet', N'Suburban', N'Orange', 2010,
CAST(19783.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'LBZ632LKI099H ', N'Nissan', N'Armada', N'White', 2011,
CAST(19805.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'LCG844FON360I ', N'Chevrolet', N'Corvette', N'Orange', 2002,
CAST(29816.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'LDY979WTJ060K ', N'Volkswagen', N'Jetta', N'Silver', 2002,
CAST(21466.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'LKR813OSG867G ', N'Toyota', N'4Runner', N'Silver', 2009,
CAST(28183.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'LWO866HKO934Z ', N'Nissan', N'Armada', N'White', 2005,
CAST(29214.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'LZJ419CFC457J ', N'Volkswagen', N'Tiguan', N'White', 2008,
CAST(9370.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'MAH425RQK787S ', N'Honda', N'Accord', N'Blue', 2004,
CAST(28390.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'MDT194YGF704Q ', N'Nissan', N'Altima', N'Blue', 2000,
CAST(13870.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'MEF819UBL671G ', N'Volkswagen', N'Jetta', N'Blue', 2006,
CAST(23707.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'MOO812GXL343T ', N'Chysler', N'Town & Country', N'Orange',
2008, CAST(9722.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'NGU492HSI763G ', N'Volkswagen', N'Passat', N'Gold', 1999,
CAST(17434.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year], [Cost]) VALUES (N'NPM517URO747O ', N'Toyota', N'Prius', N'Blue', 2000,
CAST(23813.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'PCS756ISI197O ', N'Chysler', N'300', N'Blue', 2011,
CAST(27867.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'POV701WPZ119L ', N'Toyota', N'Tundra', N'Blue', 1999,
CAST(19595.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'PXL279VHN042N ', N'Chevrolet', N'Tahoe', N'Orange', 2008,
CAST(12475.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'QHW300UAR242Q ', N'Ford', N'Fusion', N'White', 2011,
CAST(13065.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'QIU403COU317I ', N'Honda', N'Crosstour', N'Gold', 2006,
CAST(29078.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'QLT113IHU647S ', N'Chevrolet', N'Suburban', N'Black', 2000,
CAST(12842.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'QYG755MRN532O ', N'Chevrolet', N'Corvette', N'Silver', 2005,
CAST(26513.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'QZO626ZSA926A ', N'Chevrolet', N'Tahoe', N'Red', 2007,
CAST(23093.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'RDM861EQE740J ', N'Honda', N'Crosstour', N'Blue', 1999,
CAST(23711.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'ROY181EQM628S ', N'Volkswagen', N'Passat', N'Blue', 2004,
CAST(21379.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'RPZ128JDC915W ', N'Chevrolet', N'Tahoe', N'Black', 2004,
CAST(30093.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'RQG635HYC165U ', N'Toyota', N'Tundra', N'White', 2005,
CAST(14094.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'SBI969OQW990N ', N'Toyota', N'Camry', N'White', 2013,
CAST(20439.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'SDH094VMS041W ', N'Ford', N'Fusion', N'Orange', 1999,
CAST(25801.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'SJA246VOE585Y ', N'Toyota', N'Camry', N'Blue', 1999,
CAST(23895.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year], [Cost]) VALUES (N'SMR917QVP285K ', N'Chevrolet', N'Malibu', N'Gold', 2002,
CAST(12297.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'SWP390IUS246M ', N'Volkswagen', N'Jetta', N'Orange', 2003,
CAST(12780.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'SZO056UYL283Y ', N'Chevrolet', N'Corvette', N'Gold', 1998,
CAST(26684.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'TDD397EOK721P ', N'Honda', N'Civic', N'Red', 2002,
CAST(28304.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'TOL115ZGL164G ', N'Volkswagen', N'Jetta', N'Blue', 2013,
CAST(10741.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'TOQ544IFZ838E ', N'Toyota', N'Tundra', N'Gold', 2003,
CAST(24813.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'TTJ188PBJ049V ', N'Toyota', N'Tundra', N'Silver', 2000,
CAST(23800.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'TWO305EEN449V ', N'Ford', N'Fusion', N'Silver', 1998,
CAST(9641.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'UHC457CHJ476G ', N'Honda', N'Accord', N'Blue', 2006,
CAST(12813.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'UHP391YMS802R ', N'Ford', N'Focus', N'Orange', 2010,
CAST(13454.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'UKT072VXT616D ', N'Chevrolet', N'Corvette', N'Blue', 2008,
CAST(25543.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'UNH165DAE672N ', N'Honda', N'CR-Z', N'White', 2007,
CAST(22684.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'UOT863VSR817J ', N'Nissan', N'Altima', N'Silver', 1998,
CAST(22908.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'VEF918ZZC099R ', N'Honda', N'Civic', N'Red', 2003,
CAST(22903.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'VHD838DMM722P ', N'Toyota', N'Corolla', N'Gold', 1998,
CAST(14731.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'VMJ281ZEU752T ', N'Nissan', N'Cube', N'Orange', 2009,
CAST(19417.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year], [Cost]) VALUES (N'VQT745LXX507E ', N'Ford', N'Fiesta', N'Orange', 2002,
CAST(24150.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'VWU519OUG411K ', N'Nissan', N'Cube', N'Red', 2007,
CAST(30607.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'WGQ914COF174T ', N'Honda', N'Accord', N'White', 2011,
CAST(29853.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'WPA442IVA467V ', N'Toyota', N'Corolla', N'Red', 2005,
CAST(24931.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'WVB028HEC530R ', N'Ford', N'Mustang', N'Blue', 2008,
CAST(24469.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'WZF772BDH141P ', N'Toyota', N'Tundra', N'Orange', 2001,
CAST(16451.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'WZZ748FGT545E ', N'Chysler', N'300', N'Orange', 2010,
CAST(27658.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'XDF251XRS801U ', N'Volkswagen', N'Passat', N'Silver', 1998,
CAST(11895.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'XKZ546BCK157M ', N'Volkswagen', N'Jetta', N'Red', 2007,
CAST(22706.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'XVV027IXQ600Z ', N'Toyota', N'Camry', N'Orange', 2013,
CAST(24884.00 AS Decimal(12, 2)))
GO
INSERT [dbo].[Cars] ([VehicleID], [Manufacturer], [Model], [Color], [Year],
[Cost]) VALUES (N'XZC881OSA178P ', N'Chysler', N'3...
USE AutoDealer3
GO
SELECT Mechanics.FirstName, Mechanics.LastName,
ServiceAppointments.AppointmentDate,
count( ServiceAppointments.AppointmentID )
AS '# of Appointments'
FROM Mechanics, ServiceAppointments
WHERE Mechanics.ID = ServiceAppointments.MechanicID AND
ServiceAppointments.AppointmentDate = '2013-02-15'
GROUP BY Mechanics.ID;
-----------