Homework SoursePlease I need this to be done in oracle sql I only need the
Please I need this to be done in oracle sql. I only need the commands. you can test it in any other database
CREATE TABLE employee
(
employeeid NUMBER NOT NULL CONSTRAINT pk_employee PRIMARY KEY,
last_name VARCHAR(25) NOT NULL,
first_name VARCHAR(25) ,
address VARCHAR(25) ,
city VARCHAR(25) ,
state CHAR(2) ,
ZIP VARCHAR(10)
);
--Create representative table
CREATE TABLE representative
(
representativeid NUMBER NOT NULL CONSTRAINT pk_represent PRIMARY KEY,
last_name VARCHAR(25) NOT NULL,
first_name VARCHAR(25) NOT NULL,
ssn VARCHAR(11) NOT NULL
CONSTRAINT uniq_ssn UNIQUE,
address VARCHAR(25) NOT NULL,
city VARCHAR(25) NOT NULL,
state VARCHAR(25) NOT NULL,
ZIP VARCHAR(10) NOT NULL,
hiredate DATE DEFAULT SYSDATE NOT NULL
);
--Create my_car table
CREATE TABLE my_car
(
vin VARCHAR2(17) NOT NULL CONSTRAINT pk_vin PRIMARY KEY,
make VARCHAR2(25),
model VARCHAR2(25),
Caryear NUMBER,
color VARCHAR2(10),
suggestedprice NUMBER (*, 7) NOT NULL,
sold CHAR(1) DEFAULT \'N\'
CONSTRAINT check_sold CHECK(sold = \'Y\' OR sold = \'N\'),
busines_discount DECIMAL DEFAULT 0
CONSTRAINT check_busines_discount CHECK(busines_discount between 0 and 1)
);
--Create my_busines table
CREATE TABLE my_busines
(
businesid NUMBER NOT NULL CONSTRAINT pk_business PRIMARY KEY,
employeeid NUMBER NOT NULL CONSTRAINT busines_employee_fk REFERENCES employee(employeeid),
vin VARCHAR2(17) NOT NULL CONSTRAINT busines_car_fk REFERENCES my_car(vin),
representativeid NUMBER NOT NULL CONSTRAINT busines_represent_fk REFERENCES representative(representativeid),
datesold DATE DEFAULT SYSDATE NOT NULL,
businesamount NUMBER (*, 7) NOT NULL,
taxamount NUMBER (*, 7) NOT NULL
);
--Create my_assistant table
CREATE TABLE my_assistant
(
assistantid NUMBER CONSTRAINT pk_assistant PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
first_name VARCHAR2(25) NOT NULL,
ssn CHAR(11) NOT NULL
CONSTRAINT uniq_ssn_mecanic UNIQUE,
address VARCHAR2(25) NOT NULL,
city VARCHAR2(25) NOT NULL,
state VARCHAR2(25) NOT NULL,
ZIP VARCHAR2(10) NOT NULL,
hiredate DATE DEFAULT SYSDATE NOT NULL,
baserate NUMBER (* , 7) NOT NULL
);
--Create my_assistant table
CREATE TABLE my_tasks
(
tasksid NUMBER CONSTRAINT pk_tasks PRIMARY KEY,
description VARCHAR2(55) NOT NULL,
standardhours NUMBER(*, 7) NOT NULL,
category VARCHAR2(25) NOT NULL
);
--Create my_tasksappointment table
CREATE TABLE my_tasksappointment
(
apptid NUMBER NOT NULL CONSTRAINT pk_appt PRIMARY KEY,
vin VARCHAR2(17) NOT NULL CONSTRAINT tasksappointment_car_fk REFERENCES my_car(vin),
employeeid NUMBER NOT NULL CONSTRAINT tasksappointment_employee_fk REFERENCES employee(employeeid),
assistantid NUMBER NOT NULL CONSTRAINT tasksappointment_assistant_fk REFERENCES my_assistant(assistantid),
tasksid NUMBER NOT NULL CONSTRAINT tasksappointment_tasks_fk REFERENCES my_tasks(tasksid),
appt_date_time DATE NOT NULL,
reason VARCHAR2(25),
date_time_completed DATE
);
Update the taxamount column for every car that has been sold. The new tax amount is 8% of the saleamount.
List the last name and state for every representative who does not live in Orlando. ascending (A to Z)
sarah NY
List the most expensive car sold for each make. (largest amount first)
make highest sale
toyota 4545665
List the last name and ID of every representative who has sold more than 1 car. (largest number of sales first).
Representative ID Car Sold
Mike 55 10
List the make, model, year, color, and suggested price for all unsold cars. (largest suggested price first)
Format:
MAKE MODEL VEHICLEYEAR COLOR
SUGGESTEDPRICE
------------------------- ------------------------- ------------------------------------ -------------------toyota camry 2016 black
45000
List the first name, last name, address, city, state, and ZIP for all employees. the output in ascending order of state and city.
NAME ADDRESS
--------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------John Smith 56 buena, usa, NY356985
Don john 67 dgd, Celibration, FL 386954
Find the average sale amount of all sold cars.
Format:
Avg Sale Amount
---------------
45689.45698
List the average sale amount and the maximum sale amount for each car model within make.
the output by make then model (A to Z)
MAKE MODEL Avg Sale $ Max Sale $
------------------------- ------------------------- ---------- ----------toyota camry 18500 18400
Ford Fusion 51000 68000
List the employee name and the total amount spent for all tasks by that employee. largest number first
EMPLOYEE Total $
------------------------------ ----------
DONA 695.085
Edwards 226.525
List all task appointments scheduled in 2016. order by date and then time
CAR EMPLOYEE DESCRIPTION ASSISTANT TIME
--------------------- ----------- ----------------------- ------------------------------
2012 Ford Taurus DONA Oil change Johnson 15-FEB-
12 09:00:00
2012 Ford Taurus RICK Oil change Smith 12-APR-
12 09:15:00
Delete all tables.
delete tables are in the proper order so they can be executed without error.
The tables with foreign keys you must delete the child table before the parent table.
Delete all the named constraints.
Solution
CREATE TABLE employee
(
employeeid NUMBER NOT NULL CONSTRAINT pk_employee PRIMARY KEY,
last_name VARCHAR(25) NOT NULL,
first_name VARCHAR(25) ,
address VARCHAR(25) ,
city VARCHAR(25) ,
state CHAR(2) ,
ZIP VARCHAR(10)
);
--Create representative table
CREATE TABLE representative
(
representativeid NUMBER NOT NULL CONSTRAINT pk_represent PRIMARY KEY,
last_name VARCHAR(25) NOT NULL,
first_name VARCHAR(25) NOT NULL,
ssn VARCHAR(11) NOT NULL
CONSTRAINT uniq_ssn UNIQUE,
address VARCHAR(25) NOT NULL,
city VARCHAR(25) NOT NULL,
state VARCHAR(25) NOT NULL,
ZIP VARCHAR(10) NOT NULL,
hiredate DATE DEFAULT SYSDATE NOT NULL
);
--Create my_car table
CREATE TABLE my_car
(
vin VARCHAR2(17) NOT NULL CONSTRAINT pk_vin PRIMARY KEY,
make VARCHAR2(25),
model VARCHAR2(25),
Caryear NUMBER,
color VARCHAR2(10),
suggestedprice NUMBER (*, 7) NOT NULL,
sold CHAR(1) DEFAULT \'N\'
CONSTRAINT check_sold CHECK(sold = \'Y\' OR sold = \'N\'),
busines_discount DECIMAL DEFAULT 0
CONSTRAINT check_busines_discount CHECK(busines_discount between 0 and 1)
);
--Create my_busines table
CREATE TABLE my_busines
(
businesid NUMBER NOT NULL CONSTRAINT pk_business PRIMARY KEY,
employeeid NUMBER NOT NULL CONSTRAINT busines_employee_fk REFERENCES employee(employeeid),
vin VARCHAR2(17) NOT NULL CONSTRAINT busines_car_fk REFERENCES my_car(vin),
representativeid NUMBER NOT NULL CONSTRAINT busines_represent_fk REFERENCES representative(representativeid),
datesold DATE DEFAULT SYSDATE NOT NULL,
businesamount NUMBER (*, 7) NOT NULL,
taxamount NUMBER (*, 7) NOT NULL
);
--Create my_assistant table
CREATE TABLE my_assistant
(
assistantid NUMBER CONSTRAINT pk_assistant PRIMARY KEY,
last_name VARCHAR2(25) NOT NULL,
first_name VARCHAR2(25) NOT NULL,
ssn CHAR(11) NOT NULL
CONSTRAINT uniq_ssn_mecanic UNIQUE,
address VARCHAR2(25) NOT NULL,
city VARCHAR2(25) NOT NULL,
state VARCHAR2(25) NOT NULL,
ZIP VARCHAR2(10) NOT NULL,
hiredate DATE DEFAULT SYSDATE NOT NULL,
baserate NUMBER (* , 7) NOT NULL
);
--Create my_assistant table
CREATE TABLE my_tasks
(
tasksid NUMBER CONSTRAINT pk_tasks PRIMARY KEY,
description VARCHAR2(55) NOT NULL,
standardhours NUMBER(*, 7) NOT NULL,
category VARCHAR2(25) NOT NULL
);
--Create my_tasksappointment table
CREATE TABLE my_tasksappointment
(
apptid NUMBER NOT NULL CONSTRAINT pk_appt PRIMARY KEY,
vin VARCHAR2(17) NOT NULL CONSTRAINT tasksappointment_car_fk REFERENCES my_car(vin),
employeeid NUMBER NOT NULL CONSTRAINT tasksappointment_employee_fk REFERENCES employee(employeeid),
assistantid NUMBER NOT NULL CONSTRAINT tasksappointment_assistant_fk REFERENCES my_assistant(assistantid),
tasksid NUMBER NOT NULL CONSTRAINT tasksappointment_tasks_fk REFERENCES my_tasks(tasksid),
appt_date_time DATE NOT NULL,
reason VARCHAR2(25),
date_time_completed DATE
);
