ComputerScienceExpert

(11)

$18/per page/

About ComputerScienceExpert

Levels Tought:
Elementary,Middle School,High School,College,University,PHD

Expertise:
Applied Sciences,Calculus See all
Applied Sciences,Calculus,Chemistry,Computer Science,Environmental science,Information Systems,Science Hide all
Teaching Since: Apr 2017
Last Sign in: 5 Weeks Ago, 3 Days Ago
Questions Answered: 4870
Tutorials Posted: 4863

Education

  • MBA IT, Mater in Science and Technology
    Devry
    Jul-1996 - Jul-2000

Experience

  • Professor
    Devry University
    Mar-2010 - Oct-2016

Category > Programming Posted 02 May 2017 My Price 9.00

AutoDealer3 database

 

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;

Answers

(11)
Status NEW Posted 02 May 2017 01:05 AM My Price 9.00

-----------

Attachments

file 1493688589-Solutions file 2.docx preview (51 words )
H-----------ell-----------o S-----------ir/-----------Mad-----------am ----------- Th-----------ank----------- yo-----------u f-----------or -----------you-----------r i-----------nte-----------res-----------t a-----------nd -----------buy-----------ing----------- my----------- po-----------ste-----------d s-----------olu-----------tio-----------n. -----------Ple-----------ase----------- pi-----------ng -----------me -----------on -----------cha-----------t I----------- am----------- on-----------lin-----------e o-----------r i-----------nbo-----------x m-----------e a----------- me-----------ssa-----------ge -----------I w-----------ill----------- be----------- qu-----------ick-----------ly -----------onl-----------ine----------- an-----------d g-----------ive----------- yo-----------u e-----------xac-----------t f-----------ile----------- an-----------d t-----------he -----------sam-----------e f-----------ile----------- is----------- al-----------so -----------sen-----------t t-----------o y-----------our----------- em-----------ail----------- th-----------at -----------is -----------reg-----------ist-----------ere-----------d o-----------n -----------THI-----------S W-----------EBS-----------ITE-----------. ----------- Th-----------ank----------- yo-----------u -----------
Not Rated(0)