Beginner SQL help

BruteForce · August 2, 2021, 7:50pm

…

That confused smiley doesn’t look very confused.

SredniVashtar · August 2, 2021, 10:54pm

Yeah, I really like how it gets a little harder each question. First you are doing simple joins on the laptop/pc sales, then very complex joins on a navy-ship database, then injection hacks on the us voter database (kidding?) Also it remembers my first ever queries!!.. which are about as immature and illegible as the poetry I was writing at the same time.

SELECT DISTINCT Classes.Country, Final.Qty, Final.Year
From Classes LEFT JOIN
(SELECT A.Country, A.Qty, B.Year
FROM
(SELECT Z.country, MAX(Z.RQty) as Qty
FROM
(SELECT classes.Country, blue.AllQty AS RQty, MIN(blue.launched) As Year
FROM Classes,
(SELECT Country, Sum(new.shipslaunched) AS AllQty, new.launched
From classes,
(SELECT class, COUNT(launched) AS shipslaunched, launched
From ships GROUP BY launched, class) AS new
where new.class=classes.class
AND launched is NOT NULL
GROUP BY country, new.launched
) AS blue
Where classes.country=blue.country
GROUP BY classes.country, blue.AllQty
) AS Z
GROUP BY Z.country) AS A,
(SELECT classes.Country, blue.AllQty AS RQty, MIN(blue.launched) As Year
FROM Classes,
(SELECT Country, Sum(new.shipslaunched) AS AllQty, new.launched
From classes,
(SELECT class, COUNT(launched) AS shipslaunched, launched
From ships GROUP BY launched, class) AS new
where new.class=classes.class
AND launched is not null
GROUP BY country, new.launched
) AS blue
Where classes.country=blue.country
GROUP BY classes.country, blue.AllQty) AS B
WHERE A.Qty=B.RQty AND A.Country=B.Country) AS Final
ON Classes.Country=Final.Country

select
row_number() OVER (order by ranked.maker, ranked.rank),
case when rank= (select
min(rank)
from (select case when type=‘laptop’ then number+1 when type=‘pc’ then number-1 else number end rank, maker, type
from (select DENSE_RANK() OVER(order by type) number, maker, type
from (select distinct maker, type
from product)as t1) as possible) as ranked2
where ranked.maker=ranked2.maker
) then ranked.maker
else ‘’
end make
, ranked.type
from (select case when type=‘laptop’ then number+1 when type=‘pc’ then number-1 else number end rank, maker, type
from (select DENSE_RANK() OVER(order by type) number, maker, type
from (select distinct maker, type
from product)as t1) as possible) as ranked

SteveGrondin · August 5, 2021, 10:28pm

Must PIVOT always aggregate? I would like to use it on a non-numeric unique column with non-numeric associated values.

Suppose you have a table like

Policy	ItemType	ItemName
A	Insured	Anne Ant
A	Owner	Anne Ant
A	Bene	Arthur Ant
B	Insured	Bob Badger
B	Owner	Barbara Badger
B	Bene	Barbara Badger

I’d like

Policy	Insured	Owner	Bene
A	Anne Ant	Anne Ant	Arthur Ant
B	Bob Badger	Barbara Badger	Barbara Badger

PIVOT would be nice, but the ITEMTypes are not so numerous CASE could be a solution.

ETA STRING_AGG seems like it would work, but I get a syntax error. I used STRING_AGG in a non-pivot query to see if I could get it to work, and it does. Same syntax throws an error in PIVOT. Hmm.

SteveGrondin · August 6, 2021, 5:12pm

So, here is the 1st error

STRING_AGG syntax that passes in a SELECT STRING_AGG(…) GROUP BY construction fails here. Don’t know why. Turns out MAX will work in my case, but won’t give you what you want if there were multiples and you wanted concatenation.
Same syntax error here

when I try to allow for any ItemTypeName. IN doesn’t seem to function like it does in a WHERE clause.
So, I can survive with this:

Other suggested solutions were using dynamic SQL, which is fine, but doesn’t directly answer why

1695814 · October 12, 2021, 9:25pm

This should be so easy…

This is Exercise 18 from the previously mentioned SELECT statement exercises: learning stage

Here are the tables:

Product

Product
maker	model	type
A	1232	PC
A	1233	PC
A	1276	Printer
A	1298	Laptop
A	1401	Printer
A	1408	Printer
A	1752	Laptop
B	1121	PC
B	1750	Laptop
C	1321	Laptop
D	1288	Printer
D	1433	Printer
E	1260	PC
E	1434	Printer
E	2112	PC
E	2113	PC

Printer

Printer
code	model	color	type	price
1	1276	n	Laser	400
6	1288	n	Laser	400
4	1401	n	Matrix	150
5	1408	n	Matrix	270
2	1433	y	Jet	270
3	1434	y	Jet	290

From the Printer table I want only those that have color=‘y’. Of those, pick the one with the lowest price. Obviously, this is model=1433 with a price of 270.00.

That should get joined somehow against the Product table so that I get that the Maker is ‘D’.

The result then be displayed as…


maker	price
D	270.00

If I just wanted the min price I would do this

code

Select min(price)
from printer
where color = ‘y’

…but I don’t know how to bring in the model variable without it finding the min by model.

Here’s my code, so far:

Select Product.maker, Printer.price
from Printer
INNER JOIN 
 Product ON Product.model = Printer.model
where Printer.color = 'y'

It gives me the two lines of data for color printers…


maker	price
D	270.00
E	290.00
…but I don’t know how to get the MIN of those.

dr_t_non-fan · October 12, 2021, 11:04pm

How about this?

Summary

Select b.maker, b.model, a.price
From
(Select model, min(price)
from printer
where color = ‘y’) a
INNER JOIN
(select maker, Model
From
Product ) b
ON a.model = b.model

Or, this:

Summary

Select b.maker, b.model, min(a.price)
From
(Select model, price
from printer
where color = ‘y’) a
INNER JOIN
(select maker, Model
From
Product) b
ON a.model = b.model

(fixed)

Marcie · October 12, 2021, 11:38pm

I think you ~~have~~ had your a’s & b’s mismatched.
(Fixed)

knoath · October 12, 2021, 11:59pm

Select Product.maker, Printer.price
from Printer
INNER JOIN
Product ON Product.model = Printer.model
where Printer.color = ‘y’
and Printer.price in (select min(price) from Printer where color = ‘y’)

1695814 · October 13, 2021, 1:11am

Even though that gives the correct answer, it labels it as incorrect:

I’ve gotten a message like that before, but didn’t know what to do about it, so I just moved on.

1695814 · October 13, 2021, 1:14am

I’ll poke around some more with your suggested solutions. The gonkulator produced error messages for each, so I’ll have to try to figure out what they mean.

SteveGrondin · October 13, 2021, 2:01pm

OK, I am confused by the question. Makers of the cheapest color printers? Sounds like they expect more than 1. If the instruction is “find the lowest price for a color printer, and list all makers that have a color printer at that price” I might have phrased it as “…Maker(s) of the cheapest color printer(s)”.

I had a task that I needed just one record even if the max had a duplicate value (separate keys, same testing value).
I used FIRST VALUE. First Value MS TSQL Help

SredniVashtar · October 14, 2021, 2:30pm

Throw a distinct onto the query. Maker A might sell 50 cheapest models.

knoath · October 15, 2021, 3:01am

Or you could just add at the bottom -

Group by Product.maker, Printer.price

if you don’t want duplicate rows.

1695814 · October 19, 2021, 7:50pm

Thanks for all the hep.

A little more googling found me this answer which was “accepted” as correct by our russian oligarchs.

https://stackoverflow.com/questions/10031965/mysql-cheapest-color-printer

select distinct Pro.maker, Pri.price
from Product Pro
join Printer Pri on Pro.model = Pri.model
where Pri.price = (select min(price) from Printer where color = 'y') 
  and Pri.color = 'y'

Things I’ve found interesting:

They did a “Join” instead of an “Inner Join”

1695814 · October 19, 2021, 8:35pm

I’m old and still having trouble with the GoA software. If I had read this correctly at any of the 1st through n-1th times that I read it I would’ve had an acceptable solution.

Here is knoath’s code with SredniVashtar’s fix:

Select distinct Product.maker, Printer.price
from Printer
INNER JOIN
Product ON Product.model = Printer.model
where Printer.color = 'y'
and Printer.price in (select min(price) from Printer where color = 'y')

I see that it is, essentially, the same as the stackoverflow code.

Followup question: join vs inner join – inner join wasn’t necessary in this case because of the various “where” restrictions – because there was a restriction on each table therefore, by default, the records had to be on both tables. Correct?

SredniVashtar · October 19, 2021, 8:44pm

“Join” and “Inner join” are identical.
Similarly “Left Join” and “Left Outer Join” are identical.

Here’s even weirder syntax that I like but will definitely irritate your mates–

Select distinct Product.maker, Printer.price
from Printer, Product
where Product.model = Printer.model
and Printer.color = ‘y’
and Printer.price in (select min(price) from Printer where color = ‘y’)

1695814 · October 19, 2021, 8:49pm

It works!

…and actually makes more sense to me than all that JOIN stuff.

SteveGrondin · October 19, 2021, 9:16pm

It seems that is an alternative CROSS JOIN syntax. Either syntax, an (INNER) JOIN or a CROSS JOIN … WHERE might result in the same execution plan. I think both do a cartesian product and then only certain combinations are included, but this is from the depths of memory. I do remember that reducing the tables before the JOINing them (whether CROSS, INNER or OUTER), helps improve performance.

1695814 · November 16, 2021, 7:40pm

I found an answer key online, so I’ll be able to use this to help me out:

github.com/SENSBoD/SQL_learn_exercises

SQL_learn_exercises.sql

master

-- 1. Find the model number, speed AND hard drive capacity for ALL the PCs WITH prices below $500.Result set: model, speed, hd.
SELECT model, speed, hd 
FROM PC
WHERE price < 500

-- 2. List ALL printer makers. Result set: maker.
SELECT DISTINCT maker AS Maker
FROM Product
WHERE TYPE = 'Printer'

-- 3. Find the model number, RAM AND screen size of the laptops WITH prices over $1000.
SELECT model, ram, screen 
FROM Laptop
WHERE price > 1000

-- 4. Find ALL records FROM the Printer table containing data about color printers.
SELECT *
FROM Printer
WHERE color = 'y'

This file has been truncated. show original

Marcie · November 16, 2021, 7:54pm

That’s no fun (for us).