sql - What is wrong with my queries? -
all code correct queries (which @ bottom) don't seem run. checked on w3schools.com more information, seems there's nothing wrong code.
can take , explain?
--drop tables-- drop table job; drop table employee; drop table purchase; drop table soccerball; drop table client; --create tables-- create table client ( clientid int not null, clientname varchar(20) not null, street varchar(20) not null, city varchar(20) not null, zipcode varchar(5) not null, phone varchar(15) null, emailaddr varchar(50) null, primary key(clientid) ); create table soccerball ( ballid int not null, ballsize number(1) not null, color varchar(20) not null, material varchar(20) not null, primary key(ballid) ); create table purchase ( purchaseid int not null, clientid int not null, ballid int not null, purchasedate date not null, primary key (purchaseid), foreign key (clientid) references client (clientid), foreign key (ballid) references soccerball (ballid) ); create table employee ( employeeid int not null, employeename varchar(50) not null, primary key (employeeid) ); create table job ( jobid int not null, purchaseid int not null, employeeid int not null, assigndate date not null, primary key (jobid), foreign key (employeeid) references employee (employeeid) ); --insert data-- insert client values (1, 'adidas', '123 adidas lane', 'adida', '22209', '1-800-555-1987', 'corporate@adidas.com'); insert client values (2, 'nike', '234 nike drive', 'nikail', '22182', '1-888-555-9876', 'corporate@nike.com'); insert client values (3, 'puma', '345 puma blvd', 'puma city', '20912', '1-800-555-6247', 'corporate@puma.com'); insert client values (4, 'reebok', '456 reebok cove', 'old reebok', '20902', '1-800-555-1000', 'corporate@reebok.com'); insert client values (5, 'vans', '567 vans lane', 'vancouver', '20725', '1-900-666-2000', 'corporate@vans.com'); insert client values (6, 'jordan', '234 nike drive', 'nikail', '22182', '1-888-555-8765', 'corporate@jordan.com'); insert client values (7, 'diadora', '345 puma blvd', 'puma city', '20912', '1-800-555-4126', 'corporate@diadora.com'); insert soccerball values (11, 1, 'blue', 'felt'); insert soccerball values (12, 3, 'red', 'leather'); insert soccerball values (13, 3, 'yellow', 'faux leather'); insert soccerball values (14, 5, 'black', 'leather'); insert soccerball values (15, 4, 'white', 'faux leather'); insert soccerball values (16, 3, 'purple', 'rubber'); insert soccerball values (17, 1, 'blue', 'faux leather'); insert purchase values (21, 2, 11, date '2013-01-01'); insert purchase values (22, 4, 12, date '2012-03-24'); insert purchase values (23, 7, 15, date '2013-05-01'); insert purchase values (24, 5, 13, date '2005-12-30'); insert purchase values (25, 6, 16, date '1999-01-23'); insert employee values (111, 'vivin viswanathan'); insert employee values (222, 'andy edwards'); insert employee values (333, 'frank hellwig'); insert employee values (444, 'vandana janeja'); insert job values (1111, 21, 111, date '2013-01-02'); insert job values (5555, 22, 111, date '2012-03-26'); insert job values (2222, 23, 222, date '2013-05-08'); insert job values (3333, 24, 222, date '2006-01-02'); insert job values (4444, 25, 111, date '1999-01-30'); --queries-- --query 1--show me information in database select * client; select * soccerball; select * purchase; select * employee; select * job; --"forgot" quantity attribute. adding that-- alter table purchase add quantity number(5); update purchase set quantity = '12000' clientid = '1'; update purchase set quantity = '2492' clientid = '2'; update purchase set quantity = '94203' clientid = '3'; update purchase set quantity = '4394' clientid = '4'; update purchase set quantity = '0' clientid = '5'; update purchase set quantity = '4832' clientid = '6'; update purchase set quantity = '10002' clientid = '7'; select * purchase; --queries continued-- --query 2--i want know how many clients have select count(*) client; --query 3--which client ordered soccer balls?*********** select max(quantity), clientname purchase p, client c p.clientid = c.clientid; --query 4--how many projects each employe have?*********** select employeeid count(*) job group employeeid; --query 5--first purchases, first served. show me order of purchases date*********** select clientname, purchasedate purchase p, client c p.clientid = c.clientid , order purchasedate; --query 6--show me kind of balls purchased*********** select b.ballid, b.ballsize, b.color, b.material soccerball b, purchase p, client c p.clientid = c.clientid , p.ballid = b.ballid , c.clientid = 1; --query 7--show me purchased balls************** select b.ballid, b.ballsize, b.color, b.material, c.clientid, clientname soccerball b, purchase p, client c p.clientid = c.clientid , p.ballid = b.ballid group clientname; thanks much!
you have several issues queries.
query 3 missing group by aggregate function:
select max(quantity), clientname purchase p inner join client c on p.clientid = c.clientid group clientname; query 4 missing comma between columns employeeid , count(*):
select employeeid, count(*) job group employeeid; query 5 has errant and before order by:
select clientname, purchasedate purchase p inner join client c on p.clientid = c.clientid order purchasedate; query 6 want of balls purchased, don't think want filter on clientid:
select b.ballid, b.ballsize, b.color, b.material soccerball b inner join purchase p on p.ballid = b.ballid inner join client c on p.clientid = c.clientid; query 7 using group by on 1 column not work correctly:
select b.ballid, b.ballsize, b.color, b.material, c.clientid, clientname soccerball b inner join purchase p on p.ballid = b.ballid inner join client c on p.clientid = c.clientid; see sql fiddle demo.
as side note, notice updated queries use inner join syntax instead of commas between tables , joins in clause.
Comments
Post a Comment