create table SalesPeople( Snum int constraint Snum_key1 primary key, Sname varchar(255) constraint Sname_unique1 unique, city varchar(15) constraint city_not_null1 not null, comm number ); create table Customers( Cnum int constraint Cnum_key primary key, Cname varchar(255), City varchar(15), Grade int, Snum int, constraint Snum_foreign foreign key (Snum) references SalesPeople(Snum) ); create table Orders( Onum int constraint Onum_key primary key, Amt number, Odate date, Cnum int, Snum int, constraint Cnum_foreign foreign key (Cnum) references Customers(Cnum), constraint Snum_foreign1 foreign key (Snum) references SalesPeople(Snum) ); insert into SalesPeople values('1001','Peel','London',0.123); insert into SalesPeople values('1002' ,'Serres', 'Sanjose' ,0.13); insert into SalesPeople values('1004', 'Motika', 'Landon' ,.11); insert into SalesPeople values('1007' ,'Rifkin' ,'Barcelona' ,.15); insert into SalesPeople values('1003' ,'Axelrod' ,'Newyork' ,.10); insert into Customers values(2001, 'Hoffman' ,'London', 100, 1001); insert into Customers values(2002, 'Giovanni' ,'Rome', 200, 1003); insert into Customers values(2003, 'Liu' ,'Sanjose', 200, 1002); insert into Customers values(2004, 'Grass' ,'Berlin', 300, 1002); insert into Customers values(2006, 'Clemens' ,'London', 300, 1001); insert into Customers values(2008, 'Cisneros' ,'Sanjose', 100, 1007); insert into Customers values(2007, 'Pereira' ,'Rome', NULL, 1004); insert into Orders values(3001, 18.69, '3-OCT-1990', 2008, 1007); insert into Orders values(3003, 767.19, '3-OCT-1990', 2001, 1001); insert into Orders values(3002, 1900.10, '3-OCT-1990', 2007, 1004); insert into Orders values(3005, 5160.45, '3-OCT-1990', 2003, 1002); insert into Orders values(3006, 1098.16, '3-OCT-1990', 2008, 1007); insert into Orders values(3009, 1713.23, '4-OCT-1990', 2002, 1003); insert into Orders values(3007, 75.75, '4-OCT-1990', 2004, 1002); insert into Orders values(3008, 4273.00, '5-OCT-1990', 2006, 1001); insert into Orders values(3010, 1309.95, '6-OCT-1990', 2004, 1002); insert into Orders values(3011, 9891.88, '6-OCT-1990', 2006, 1001); -------------------------------------- ------------------------------------- SELECT COUNT(Snum) FROM SalesPeople WHERE Sname LIKE 'a%' OR Sname LIKE 'A%'; ------------------------------------------ SELECT COUNT(Snum) FROM SalesPeople WHERE city = 'Newyork'; ------------------------------------------------ SELECT COUNT(Snum) FROM SalesPeople WHERE city IN ('Landon','Paris'); SELECT COUNT(Onum), Odate FROM Orders GROUP BY Odate; SELECT COUNT(Snum) FROM SalesPeople; select Snum FROM orders GROUP BY Snum HAVING SUM(Amt) > 2000; select count(Snum),Snum,Odate FROM orders GROUP BY Snum,Odate; select cname from customers where cname LIKE 'G%' order by cname ASC FETCH FIRST 1 ROWS ONLY ; select max(amt),snum from orders where snum = 1002 or snum = 1007 group by snum; select onum,max(amt), snum from orders group by snum,onum having max(amt) > 3000; select count(snum) from salespeople group by city, comm; select sum(amt) from orders;