Question

SQL Homework, and Im still getting errors. Not sure why, but says I am missing a parentheses somewhere in the first table and the last table has some sort of conflict. Can someone please check my work and see where I went wrong and why? Thanks!

create table Employee(
EmpID char(10),
Name varchar2(50),
Salery char(10),
Address varchar2(50),
HireDate date(dd/mm/yyyy),
Phone char(10),
primary key(EmpID))
;

create table Inventory(
ProductID char(10),
ProductName varchar2(50),
UnitPrice char(10),
CurrentInventory char(10),
MonthlySales char(10),
PrecentOfPrice char(10),
Primary key(ProductID))
;

create table Transaction(
TransactionID char(10),
TransactionCode varchar2(50),
Primary key(TransactionID))
;

create table Sales(
EmpID char(10),
ProductID char(10),
CustomerID char(10),
TransactionID char(10),
UnitsSold char(10),
PO# char(10),
Date date(dd/mm/yyyy),
Primary key(CustomerID),
Foreign key(EmpID) References Employee(EmpID),
Foreign key(ProductID) References Inventory(ProductID),
Foreign key(TransactionID) References Transaction(TransactionID))
;

Insert into Employee values ('001','John Smith','35500','123 Main Street, Olympia WA','05/05/2018','1234567890');
Insert into Employee values ('002','Jane Doe','36000','259 Orange Ave, Olympia WA','09/16/2018','2355695485');
Insert into Employee values ('003','Stan Lee','45000','958 State Street, Olympia WA','04/05/2016','1232344567');
Insert into Employee values ('004','Peter Snow','45250','259 Capital Way, Seattle WA','04/05/2016','9876543211');
Insert into Employee values ('005','Tony Stark','37500','599 Apple Way, Lacey WA','07/07/2017','8795642312');
Insert into Inventory values ('010','Pride and Prejudice','15.99','56','12','50');
Insert into Inventory values ('011','Dune','17.75','48','8','65');
Insert into Inventory values ('012','Jurrasic Park','19.99','42','10','50');
Insert into Inventory values ('013','Target Hiroshima','21.99','12','2','45');
Insert into Inventory values ('014','One Second After','16.99','40','10','50');
Insert into Transaction values ('001','Cash');
Insert into Transaction values ('002','Credit');
Insert into Transaction values ('003','Check');
Insert into Sales values ('001','010','100','002','1','456123','05/08/2019');
Insert into Sales values ('002','012','101','002','1','456124','05/09/2019');
Insert into Sales values ('003','011','102','001','1','456125','05/10/2019');
Insert into Sales values ('004','014','101','003','2','456126','06/02/2019');
Insert into Sales values ('005','013','103','002','5','456127','06/04/2019');

Answer 1

Answer is as follows :

In Table Employee,

The HireDate is declared as Date with (dd/mm/yyyy). This is incorrect way of declaring a date variable.

You have to simply use HireDate Date

In Table Sales,

The Date is keyword in SQL, so you have to use other name of attribute i.e. "Date_" (say). and you cannot use (dd/mm/yyyy) as per format. You can simply use Date_ date

In Inserting of values in table Sales,CustomerID is said to be priamry key, but there are same values for different sales. Primary key attributes has always unique values.

So for this concern, please remove Primary Key constraint from CustomerID.

Don't use # in name of any type of attribute.

In Table Transaction, You cannot use table name as Transaction. This is keyword in SQL used for transaction processing.

So please use Transaction_

I update the code as follows without errors :

create table Employee(
EmpID char(10),
Name varchar2(50),
Salery char(10),
Address varchar2(50),
HireDate date,
Phone char(10),
primary key(EmpID));

create table Inventory(
ProductID char(10),
ProductName varchar2(50),
UnitPrice char(10),
CurrentInventory char(10),
MonthlySales char(10),
PrecentOfPrice char(10),
Primary key(ProductID));

create table Transaction_(
TransactionID char(10),
TransactionCode varchar2(50),
Primary key(TransactionID));

create table Sales( EmpID char(10),
ProductID char(10),
CustomerID char(10),
TransactionID char(10),
UnitsSold char(10),
PO char(10),
Date_ date,
Foreign key(EmpID) References Employee(EmpID),
Foreign key(ProductID) References Inventory(ProductID),
Foreign key(TransactionID) References Transaction_(TransactionID));

Insert into Employee values ('001','John Smith','35500','123 Main Street, Olympia WA','05/05/2018','1234567890');
Insert into Employee values ('002','Jane Doe','36000','259 Orange Ave, Olympia WA','09/16/2018','2355695485');
Insert into Employee values ('003','Stan Lee','45000','958 State Street, Olympia WA','04/05/2016','1232344567');
Insert into Employee values ('004','Peter Snow','45250','259 Capital Way, Seattle WA','04/05/2016','9876543211');
Insert into Employee values ('005','Tony Stark','37500','599 Apple Way, Lacey WA','07/07/2017','8795642312');

Insert into Inventory values ('010','Pride and Prejudice','15.99','56','12','50');
Insert into Inventory values ('011','Dune','17.75','48','8','65');
Insert into Inventory values ('012','Jurrasic Park','19.99','42','10','50');
Insert into Inventory values ('013','Target Hiroshima','21.99','12','2','45');
Insert into Inventory values ('014','One Second After','16.99','40','10','50');

Insert into Transaction_ values ('001','Cash');
Insert into Transaction_ values ('002','Credit');
Insert into Transaction_ values ('003','Check');

Insert into Sales values ('001','010','100','002','1','456123','05/08/2019');
Insert into Sales values ('002','012','101','002','1','456124','05/09/2019');
Insert into Sales values ('003','011','102','001','1','456125','05/10/2019');
Insert into Sales values ('004','014','101','003','2','456126','06/02/2019');
Insert into Sales values ('005','013','103','002','5','456127','06/04/2019');

select * from Employee;
select * from Inventory;
select * from Transaction_;
select * from Sales;

if there is any query please ask in comments...