Rabia Aydınkaya Rabia Aydınkaya - 2 days ago 5
SQL Question

just queries. List the birthday of recievers who order “shoes”.2.List the name of carriers who deliver orders of the lowest and the highest price.

[CARRIER table]

Carrier_ID Name Gender Birthday


2001 Aylin Female 29.03.1975
2002 Arif Male 29.09.1996
2003 Berker Male 13.11.1970
2004 Sezgin Male 13.06.1988
2005 Yeliz Female 11.08.1951
2006 Dolunay Male 17.07.1974
2007 Reşide Female 10.01.1960
2008 Münir Male 27.06.1989


[ORDER table]

Order_ID Description Price Carrier_ID

1001 24” Monitor 199 2001
1002 Cloths 45 2003
1003 Vaporizer 24 2006
1004 Book 19 2002
1005 Graphics Card 450 2003
1006 Shoes 37 2007
1007 Bag 14 2008
1008 Processor 299 2008

Answer

Trying to put this together with the little bit of information you have available in your post, and in the comment you left... It appears that you have two tables Carrier and Order. First of all, I do see in the Order table, you have entries with a Description of "Shoes". I would hope though, that you would have a lookup table that contains an entry for each of your products / order types, but just going on what you have given us so far, the less ideal approach would be:

For your first question:

List the birthday of recievers who order “shoes”

select c.Birthday
from Carrier c
join Order o
on o.Carrier_ID = c.Carrier_ID
where o.Description = 'Shoes';

Ideally, you should have a lookup table for your products/order types that would contain a unique identifier and the description of the product. You should then store the unique identifier in the order table instead of the description. Then you would join to the product lookup table to get the information about that product.

With the newly provided information, here is likely a better query which does the same as the first one, but more properly:

select c.Birthday
from Carrier c
join Order o
on o.Carrier_ID = c.Carrier_ID
where o.Order_ID = 1006;

As for part two of your question:

List the name of carriers who deliver orders of the lowest and the highest price.

with MinPrice_CTE (MinPrice)
as
(
   select Min(Price) as MinPrice
   from Orders
),
MaxPrice_CTE (MaxPrice)
as
(
   select Max(Price) as MaxPrice
   from Orders
)
select c.Name, 
   case when o.Price = MinPrice then 'Minimum Price' 
   else 'Maximum Price' end as PriceGroup
from Carriers c
join Orders o
on o.Carrier_ID = c.Carrier_ID
where o.Price in (MinPrice, MaxPrice);
Comments