QuestionQuestion

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.

Solution PreviewSolution Preview

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;...
$34.00 for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Database Development Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.

Decision:
Upload a file
Continue without uploading

SUBMIT YOUR HOMEWORK
We couldn't find that subject.
Please select the best match from the list below.

We'll send you an email right away. If it's not in your inbox, check your spam folder.

  • 1
  • 2
  • 3
Live Chats