Question

Still working on SQL, but now trying to run some commands to pull data from the databases. The last 3 statements Im getting errors but I am not sure how to fix them. What do I need to do differently to those statements to make it work?

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), 
SaleDate date, 
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-may-2018','1234567890')
;

Insert into Employee values ('002','Jane Doe','36000','259 Orange Ave, Olympia WA','16-sep-2018','2355695485')
;

Insert into Employee values ('003','Stan Lee','45000','958 State Street, Olympia WA','04-may-2016','1232344567')
;

Insert into Employee values ('004','Peter Snow','45250','259 Capital Way, Seattle WA','04-may-2016','9876543211')
;

Insert into Employee values ('005','Tony Stark','37500','599 Apple Way, Lacey WA','07-jul-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','08-jun-2019')
;

Insert into Sales values ('002','012','101','002','1','456124','09-may-2019')
;

Insert into Sales values ('003','011','102','001','1','456125','10-may-2019')
;

Insert into Sales values ('004','014','106','003','2','456126','02-jun-2019')
;

Insert into Sales values ('005','013','103','002','5','456127','03-jul-2019')
;

SELECT * from Employee; 



SELECT * from Inventory; 



SELECT * from Sales where SaleDate BETWEEN '09-may-2019' and '09-jun-2019'; 



SELECT "ProductID", sum (CurrentInventory-UnitsSold) from Inventory GROUP BY "ProductID



SELECT * from Sales, where EmpID is '002';



SELECT TOP 10 ProductID, SUM(UnitsSold) UnitsSold FROM Sales BETWEEN '09-may-2019' and '09-jun-2019';

Answer 1

If you have any doubts, please give me comment...

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),

SaleDate date,

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-may-2018','1234567890');

Insert into Employee values ('002','Jane Doe','36000','259 Orange Ave, Olympia WA','16-sep-2018','2355695485');

Insert into Employee values ('003','Stan Lee','45000','958 State Street, Olympia WA','04-may-2016','1232344567');

Insert into Employee values ('004','Peter Snow','45250','259 Capital Way, Seattle WA','04-may-2016','9876543211');

Insert into Employee values ('005','Tony Stark','37500','599 Apple Way, Lacey WA','07-jul-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','08-jun-2019');

Insert into Sales values ('002','012','101','002','1','456124','09-may-2019');

Insert into Sales values ('003','011','102','001','1','456125','10-may-2019');

Insert into Sales values ('004','014','106','003','2','456126','02-jun-2019');

Insert into Sales values ('005','013','103','002','5','456127','03-jul-2019');

SELECT * from Employee;

SELECT * from Inventory;

SELECT * from Sales where SaleDate BETWEEN '09-may-2019' and '09-jun-2019';

SELECT ProductID, sum(CurrentInventory-UnitsSold) from Inventory GROUP BY ProductID;

SELECT * from Sales where EmpID = '002';

SELECT TOP 10 ProductID, SUM(UnitsSold) UnitsSold FROM Sales WHERE SaleDate BETWEEN '09-may-2019' and '09-jun-2019';