Question

2. Enter the following record into the Inventory table using data listed below:

Make

Model

Yr

Description

CarCondition

Cost

Selling Price

Date Arrived

Date Sold

RepNumber

Pontiac

Grand Am

2005

4-Door, Red

Excellent

$8,000

$9,990

5/5/08

6/1/08

1

Lincoln

Town Car

2001

2-Door, White

Good

$5,500

$5,995

4/15/08

4/20/08

3

Chevrolet

Cavalier

2005

4-Door, Blue

Excellent

$7,000

5/15/08

Toyota

Corolla

2001

4-Door, Black

Fair

$4,000

$4,500

5/1/08

Ford

Tempo

2002

2-Door, Red

Poor

$2,000

$2,300

5/5/08

Chevrolet

Lumina

2005

2-Door, White

Excellent

$8,500

5/12/08

Ford

Focus

2003

5 Speed, Black

Good

$6,500

$7,000

4/20/08

4/30/08

1

Ford

Escort

2000

2-Door, White

Excellent

$5,500

5/3/08

Plymouth

Neon

2001

4-Door, Blue

Good

$6,500

5/1/08

Ford

Taurus LX

2003

Wagon, Gray

Excellent

$8,200

5/20/07

3. The Chevrolet Lumina was sold on 5/20/08 by Bauer for $9,300. Update the INVENTORY table and modify date sold, selling price, and repnumber.
4. Write SQL statements to query the following information
   1. All vehicles available for sale with a selling price less than $6,000, sorted by cost in descending order.
   2. All vehicles sold, grouped by Sales Rep.
   3. All vehicles made by Ford and Toyota.

Answer 1

HERE In the Inventory table I used the column name as [Date Arrived] because in the column name we cannot use the space directly. So in the bracket we write like that or just DateArrived. LIkewise I named the column

I used Microsoft Sql SErver and this query language is not case sensitive. So capital or small letter name will be same. Also dollar symbol or the comma used to seperate the cost is removed because its not needed As a float I m inserting.

--TABLE CREATION QUERY 
Create table Inventory(Make VARCHAR(20),Model VARCHAR(20),Yr INT,Description VARCHAR(20),CarCondition VARCHAR(20),Cost FLOAT,
[Selling Price] FLOAT,[Date Arrived] DATETIME,[Date Sold] DATETIME,RepNumber INT )


--INSERTING INTO THE INVENTORY TABLE QUERY
INSERT INTO INVENTORY VALUES('Pontiac','Grand Am',2005,'4-Door, Red','Excellent',8000,9990,'5/5/2008','6/1/2008',1)
INSERT INTO INVENTORY VALUES('Lincoln','Town Car',2001,'2-Door, White','Good',5500,5995,'4/15/08','4/20/08',3)
INSERT INTO INVENTORY VALUES('Chevrolet','Cavalier',2005,'4-Door, Blue','Excellent',7000,'','5/15/08','','')
INSERT INTO INVENTORY VALUES('Toyota','Corolla',2001,'4-Door, Black','Fair',4000,4500,'5/1/08','','')
INSERT INTO INVENTORY VALUES('Ford','Tempo',2002,'2-Door, Red','Poor',2000,2300,'5/5/08','','')
INSERT INTO INVENTORY VALUES('Chevrolet','Lumina',2005,'2-Door, White','Excellent',8500,'','5/12/08','','')
INSERT INTO INVENTORY VALUES('Ford','Focus',2003,'5 Speed, Black','Good',6500,7000,'4/20/08','4/30/08',1)
INSERT INTO INVENTORY VALUES('Ford','Escort',2000,'2-Door, White','Excellent',5500,'','5/3/08','','')
INSERT INTO INVENTORY VALUES('Plymouth','Neon',2001,'4-Door, Blue','Good',6500,'','5/1/08','','')
INSERT INTO INVENTORY VALUES('Ford','Taurus LX',2003,'Wagon, Gray','Excellent',8200,'','5/20/07','','')


--SELECT * FROM INVENTORY


--  FOR UPDATING THE CHEVROLET LUMINA
UPDATE Inventory SET [Date Sold]='5/20/08',[Selling Price]=9300 WHERE MAKE='Chevrolet' AND MODEL='Lumina'

--Q(1) All vehicles available for sale with a selling price less than $6,000, sorted by cost in descending order.

SELECT * FROM Inventory  WHERE [SELLING PRICE]<6000 ORDER BY COST DESC



--Q(2) All vehicles sold, grouped by Sales Rep.

SELECT REPNUMBER,COUNT(REPNUMBER)[COUNT] FROM INVENTORY WHERE [Date Sold]!='' GROUP BY RepNumber


--Q(3) All vehicles made by Ford and Toyota

SELECT * FROM Inventory WHERE MAKE IN ('Ford','Toyota')