SQL Statements For Cool Cotton Database

Cool Cotton Database is created by the following SQL statements:

create table shirt(
styleCode char(6),
salesPrice number,
primary key(styleCode));

create table shirtColor(
style char(6),
colorCode char(2),
primary key(style, colorCode),
foreign key(style) references shirt(styleCode));

create table shirtSize(
basicStyle char(6),
sizeOption number,
primary key(basicStyle, sizeOption),
foreign key(basicStyle) references shirt(styleCode));

create table designer(
designerID varchar(10),
designerName varchar(30),
primary key(designerID));

create table originalDesign(
theStyle char(6),
theDesigner varchar(10),
adoptedWhen date,
designPlan varchar(200),
primary key(theStyle),
foreign key(theStyle) references shirt,
foreign key(theDesigner) references designer);

create table factory(
factoryID varchar(10),
factoryName varchar(30),
primary key(factoryID));

create table purchaseDeal(
item char(6),
producedBy varchar(10),
productName varchar(10),
purchasePrice number,
quantity number,
orderDate date,
primary key(item, producedBy, productName),
foreign key(item) references shirt,
foreign key(producedBy) references factory);

Write SQL statements for the queries specified below:

1. Find styles and names of original designers of all shirts produced by SUPER STAR factory.

2. Find all colors (colorCodes) that have ever been used in those shirt styles designed by SMART DRESSER designers that exist in sizes(options) greater then 20.

3. Fine name of factory that has (at any point in time) produced the same shirt style under different product names.

4. Find styles and sales prices of shirts that have outstanding purchase orders (orders not yet due as of today, not knowing what date it is today.)

5. Find styles and sales prices of the most expensive shirt(s).

6. Find the sales prices of the shirt style(s) with the highest purchase price that has ever been ordered.

This material may consist of step-by-step explanations on how to solve a problem or examples of proper writing, including the use of citations, references, bibliographies, and formatting. This material is made available for the sole purpose of studying and learning - misuse is strictly forbidden.

1. Find styles and names of original designers of all shirts produced by SUPER STAR
factory.

select s.THESTYLE, d.DESIGNERNAME
from DESIGNER d inner join
(
select DISTINCT o.THEDESIGNER, o.THESTYLE from ORIGINALDESIGN o
where o.THESTYLE in
(
    select p.ITEM from FACTORY f inner join PURCHASEDEAL p
      on f.FACTORYID = p.PRODUCEDBY
      where f.FACTORYNAME = 'SUPER STAR'
   )
) s on d.DESIGNERID = s.THEDESIGNER;...