i'm new sql , i'trying query on data i've entered find bottles of wine sold danmurphys less $20. query i've composed down bottom syntax isn't right , i'm not sure i'm doing wrong.
any offer me appreciated!
create table retailer (retail_id nvarchar(4) not null, retail_name nvarchar(30) not null, primary key (retail_id)); create table wine (wine_id nvarchar(3) not null, wine_name nvarchar(30) not null, wine_vint nvarchar(4) not null, primary key(wine_id)); create table dist (retail_id nvarchar(4) not null, wine_id nvarchar(3) not null, wine_price decimal(6,2) not null, constraint pk_dist primary key ( retail_id, wine_id ), foreign key (retail_id) references retailer (retail_id), foreign key (wine_id) references wine (wine_id)); insert wine values('101','grange','2010'); insert wine values('102','grange','2006'); insert wine values('103','reserve shiraz','2013'); insert wine values('104','grey label shiraz','2012'); insert wine values('105','patricia shiraz','2009'); insert wine values('106','ten acres shiraz','2012'); insert wine values('107','double barrel shiraz','2012'); insert wine values('108','platinum label shiraz','2006'); insert retailer values('1001', 'dan murphys'); insert retailer values('1002', 'woolworths'); insert dist values('1001','101','750'); insert dist values('1001','102','700'); insert dist values('1001','103','10'); insert dist values('1001','104','35'); insert dist values('1001','105','50'); insert dist values('1001','106','25'); insert dist values('1001','107','15'); insert dist values('1001','108','170'); insert dist values('1002','103','9'); insert dist values('1002','104','33'); insert dist values('1002','105','44'); select distinct wine.wine_name, wine.wine_vint, dist.wine_price wine wine_price < 20 inner join dist on wine.wine_id = dist.retail_id inner join wine on dist.retail_id = retailer.retail_id retailer.retail_name = 'danmurphys'
you there. during string comparison, needs match have in database. may write query in following way:
select wine.wine_name, wine.wine_vint, dist.wine_price wine inner join dist on (wine.wine_id = dist.wine_id) inner join retailer on (dist.retail_id = retailer.retail_id) retailer.retail_name = 'dan murphys' , wine_price < 20 
Comments
Post a Comment