Create table customer CustomerID NUMBER PRIMARY KEY Lastname

Create table customer(

Customer_ID NUMBER PRIMARY KEY,

Last_name VARCHAR2(25) NOT NULL,

First_name VARCHAR2(25)

)

Create table salesperson(

Sales_person_ID NUMBER PRIMARY KEY,

Last_name VARCHAR2(25) NOT NULL,

First_name VARCHAR2(25),

ssn VARCHAR2(11) ENCRYPT NOT NULL,

Hiredate DATE NOT NULL,

CONSTRAINT ssn_unique UNIQUE(ssn)

state VARCHAR(25) NOT NULL,

);

Create table car(

Vin# NUMBER PRIMARY KEY,

make VARCHAR2(70),

model VARCHAR2(40),

Vehicle_year NUMBER,

color VARCHAR2(20),

price NUMBER(7,2) NOT NULL,

sold CHAR(1) DEFAULT \'N\',

sale_discount NUMBER(3,2) DEFAULT 0,

CONSTRAINT check_sold CHECK(sold = \'Y\' OR sold = \'N\'),

CONSTRAINT check_sale_discount CHECK(sale_discount between 0 and 1)

);

Create table sale

(

Sale_ID NUMBER PRIMARY KEY,

Customer_ID NUMBER NOT NULL,

CONSTRAINT sale_customer_fkey REFERENCES customer(Customer_ID)

taxamount NUMBER (*, 2) NOT NULL

);

Update the taxamount column for the sol cars . The new tax amount is 8% of the saleamount.

List the last name and state for every sales person who does not live in olando. (A to Z)

List the most expensive vehicle sold for each make(largest amount first)

List the last name and ID of every salesperson who has sold more than 1 vehicle.(largest amount first

List the make, model, year, color, and suggested price for all unsold vehicles.(largest suggested price first)

List the first name, last name, address, city, state, and ZIP for all customers ascending order of state and city.

Find the average sale amount of all sold cars.

  

Solution

1. List the last name and state for every sales person who does not live in olando. (A to Z)

SELECT Last_name, state FROM SALESPERSON WHERE state != \'olando\'

2. List the most expensive vehicle sold for each make(largest amount first)

SELECT model, make, color, max(price) from car group by make

3. List the make, model, year, color, and suggested price for all unsold vehicles.(largest suggested price first)

SELECT make, model, year, color FROM car WHERE SOLD = \'N\'

4. List the first name, last name, address, city, state, and ZIP for all Customer ascending order of state and city.

SELECT Last_name, First_name, address, city, state, ZIP order by state ASC, city ASC

5. Find the average sale amount of all sold cars.

select avg(price- (sale_discount*price/100) from car where sold = \'Y\'

2 of the questions are not clear.

Update the taxamount column for the sol cars . The new tax amount is 8% of the saleamount.

List the last name and ID of every salesperson who has sold more than 1 vehicle.(largest amount first)

reason: There is no relationship between salesperson and sale table