# Development Tasks

### Task 1:

Write a query to retrieve all the standard assortments of a style `12235974`.

#### Solution

select * from assortments
where assortment_types_id=5219611 --got it from assortment_types table
and style_colls_id in (SELECT style_colls_id FROM style_colls 
                       where styles_id in (select styles_id from styles 
                                           where style_number='12235974'));
                                           
                                           
-- Using Joins

select * from assortments a
inner join assortment_types att on a.assortment_types_id=att.assortment_types_id and att.TYPE_NAME='Standard'
inner join style_colls sc on a.style_colls_id=sc.style_colls_id 
inner join styles s on sc.styles_id=s.styles_id and s.style_number=12235974;

### Task 2:

SET DEFINE OFF

select * from styles 
where brands_id_2 in (select brands_id FROM brands 
                      where brand_name='JACK&JONES JEANS INTELLIGENCE') 
and styles_id in (select styles_id from style_colls 
                  where collections_id in (select collections_id from collections 
                                           where collection_term='22 MAIN #3')) 
FETCH NEXT 10 ROWS ONLY;

-- Using Joins

SET DEFINE OFF
select * from styles s
inner join brands b on s.brands_id_2=b.brands_id and b.brand_name='JACK&JONES JEANS INTELLIGENCE'
inner join style_colls sc on s.styles_id=sc.styles_id
inner join collections co on sc.collections_id=co.collections_id and co.collection_term='22 MAIN #3'
inner join departments d on b.departments_id=d.departments_id and d.department_name='Jack & Jones'
FETCH NEXT 10 ROWS ONLY;