home / study / engineering / computer science / questions and answers / please i
ID: 3791279 • Letter: H
Question
home / study / engineering / computer science / questions and answers / please i need this to be done in oracle sql. i ...
Question: Please I need this to be done in oracle sql. I onl...
Bookmark
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
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.
Explanation / Answer
--Update the taxamount column for every car that has been sold. The new tax amount is 8% of the saleamount.
update my_busines set taxamount=0.08 where vin in (select vin from my_car where sold='Y')
--List the last name and state for every representative who does not live in Orlando. ascending (A to Z)
select last_name,state from representative where city !='Orlando' order by state
--List all task appointments scheduled in 2016. order by date and then time
select * from my_tasksappointment where year(appt_date_time)='2016' order by date_time_completed
--drop table <table name>
--Delete all tables.
--delete the tables who are created at last
drop table my_tasksappointment
drop table my_tasks
drop table my_assistant
drop table my_busines
drop table my_car
drop table representative
drop table employee
--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.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.