How to design a SQL subquery?
The problem in question states:
Return all accessible information about paintings of a painter that are more expensive than the average price of all paintings of the respective painter. Use a correlated query in the WHERE clause. For simplicity, also use a convenient view, instead of a join in the outer query.
Here is a link to the Schema
Here is the view I've been provided with:
And here is the partial solution I currently have:
SELECT
*
FROM
art
WHERE
price > (SELECT AVG(price) AS 'avg_price'
FROM
art
WHERE
GROUP BY
painter_id);
So, I know have a query whose return is based on a single piece of information returned from the subquery, but the subquery returns a table of information (in this case, the average price of all the paintings created by a specific painter, grouped by painter_id). I know I need to pull out one entry from this table based on the painter_id from the painting in question. My problem is, I have no idea how to go about making that comparison. Am I implementing the subquery correctly? If not, what do I need to change to get back on track; and if so, what am I missing? Will I need a second subquery to do this, or can I do it with just one?
I am new to SQL, and just learned about subqueries earlier this week. I do know the basics of regular queries, but I currently have a tenuous idea of how queries and subqueries work with each other.
Edit
This is what the solution should look like in the Result Grid:
mysql
edited Nov 16 '18 at 20:26
halfer
14.7k759116
asked Nov 16 '18 at 8:08
Jon DerrJon Derr
185
add a comment |
The problem in question states:
Return all accessible information about paintings of a painter that are more expensive than the average price of all paintings of the respective painter. Use a correlated query in the WHERE clause. For simplicity, also use a convenient view, instead of a join in the outer query.
Here is a link to the Schema
Here is the view I've been provided with:
And here is the partial solution I currently have:
SELECT
*
FROM
art
WHERE
price > (SELECT AVG(price) AS 'avg_price'
FROM
art
WHERE
GROUP BY
painter_id);
So, I know have a query whose return is based on a single piece of information returned from the subquery, but the subquery returns a table of information (in this case, the average price of all the paintings created by a specific painter, grouped by painter_id). I know I need to pull out one entry from this table based on the painter_id from the painting in question. My problem is, I have no idea how to go about making that comparison. Am I implementing the subquery correctly? If not, what do I need to change to get back on track; and if so, what am I missing? Will I need a second subquery to do this, or can I do it with just one?
I am new to SQL, and just learned about subqueries earlier this week. I do know the basics of regular queries, but I currently have a tenuous idea of how queries and subqueries work with each other.
Edit
This is what the solution should look like in the Result Grid:
mysql
edited Nov 16 '18 at 20:26
halfer
14.7k759116
asked Nov 16 '18 at 8:08
Jon DerrJon Derr
185
add a comment |
The problem in question states:
Return all accessible information about paintings of a painter that are more expensive than the average price of all paintings of the respective painter. Use a correlated query in the WHERE clause. For simplicity, also use a convenient view, instead of a join in the outer query.
Here is a link to the Schema
Here is the view I've been provided with:
And here is the partial solution I currently have:
SELECT
*
FROM
art
WHERE
price > (SELECT AVG(price) AS 'avg_price'
FROM
art
WHERE
GROUP BY
painter_id);
So, I know have a query whose return is based on a single piece of information returned from the subquery, but the subquery returns a table of information (in this case, the average price of all the paintings created by a specific painter, grouped by painter_id). I know I need to pull out one entry from this table based on the painter_id from the painting in question. My problem is, I have no idea how to go about making that comparison. Am I implementing the subquery correctly? If not, what do I need to change to get back on track; and if so, what am I missing? Will I need a second subquery to do this, or can I do it with just one?
I am new to SQL, and just learned about subqueries earlier this week. I do know the basics of regular queries, but I currently have a tenuous idea of how queries and subqueries work with each other.
Edit
This is what the solution should look like in the Result Grid:
mysql
edited Nov 16 '18 at 20:26
halfer
14.7k759116
asked Nov 16 '18 at 8:08
Jon DerrJon Derr
185
The problem in question states:
Return all accessible information about paintings of a painter that are more expensive than the average price of all paintings of the respective painter. Use a correlated query in the WHERE clause. For simplicity, also use a convenient view, instead of a join in the outer query.
Here is a link to the Schema
Here is the view I've been provided with:
And here is the partial solution I currently have:
SELECT
*
FROM
art
WHERE
price > (SELECT AVG(price) AS 'avg_price'
FROM
art
WHERE
GROUP BY
painter_id);
So, I know have a query whose return is based on a single piece of information returned from the subquery, but the subquery returns a table of information (in this case, the average price of all the paintings created by a specific painter, grouped by painter_id). I know I need to pull out one entry from this table based on the painter_id from the painting in question. My problem is, I have no idea how to go about making that comparison. Am I implementing the subquery correctly? If not, what do I need to change to get back on track; and if so, what am I missing? Will I need a second subquery to do this, or can I do it with just one?
I am new to SQL, and just learned about subqueries earlier this week. I do know the basics of regular queries, but I currently have a tenuous idea of how queries and subqueries work with each other.
Edit
This is what the solution should look like in the Result Grid:
mysql
mysql
edited Nov 16 '18 at 20:26
halfer
14.7k759116
asked Nov 16 '18 at 8:08
Jon DerrJon Derr
185
edited Nov 16 '18 at 20:26
halfer
14.7k759116
asked Nov 16 '18 at 8:08
Jon DerrJon Derr
185
edited Nov 16 '18 at 20:26
halfer
14.7k759116
edited Nov 16 '18 at 20:26
halfer
14.7k759116
edited Nov 16 '18 at 20:26
halfer
14.7k759116
14.7k759116
asked Nov 16 '18 at 8:08
Jon DerrJon Derr
185
asked Nov 16 '18 at 8:08
Jon DerrJon Derr
185
asked Nov 16 '18 at 8:08
Jon DerrJon Derr
185
185
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Your query is very close, you just need to connect (correlate) the subquery to the main query, using the value of painter_id:
SELECT *
FROM art a1
WHERE price > (SELECT AVG(price)
FROM art a2
WHERE a2.painter_id = a1.painter_id);
Note that since you are qualifying the subquery with the painter_id you no longer need the GROUP BY clause. Also you don't need an alias for the average price as you don't ever access that column directly.
Another way to do the same thing is with a JOIN to a table of average price values:
SELECT *
FROM art a1
JOIN (SELECT painter_id, AVG(price) AS avg_price
FROM art
GROUP BY painter_id) a2
ON a2.painter_id = a1.painter_id AND a1.price > a2.avg_price
answered Nov 16 '18 at 8:35
NickNick
38.2k132443
Oh, I see! Thank you so much, I didn't realize instantiating a table multiple times (for lack of a better term) like that would have that effect!
– Jon Derr
Nov 16 '18 at 8:52
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53333778%2fhow-to-design-a-sql-subquery%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Your query is very close, you just need to connect (correlate) the subquery to the main query, using the value of painter_id:
SELECT *
FROM art a1
WHERE price > (SELECT AVG(price)
FROM art a2
WHERE a2.painter_id = a1.painter_id);
Note that since you are qualifying the subquery with the painter_id you no longer need the GROUP BY clause. Also you don't need an alias for the average price as you don't ever access that column directly.
Another way to do the same thing is with a JOIN to a table of average price values:
SELECT *
FROM art a1
JOIN (SELECT painter_id, AVG(price) AS avg_price
FROM art
GROUP BY painter_id) a2
ON a2.painter_id = a1.painter_id AND a1.price > a2.avg_price
answered Nov 16 '18 at 8:35
NickNick
38.2k132443
Oh, I see! Thank you so much, I didn't realize instantiating a table multiple times (for lack of a better term) like that would have that effect!
– Jon Derr
Nov 16 '18 at 8:52
add a comment |
Your query is very close, you just need to connect (correlate) the subquery to the main query, using the value of painter_id:
SELECT *
FROM art a1
WHERE price > (SELECT AVG(price)
FROM art a2
WHERE a2.painter_id = a1.painter_id);
Note that since you are qualifying the subquery with the painter_id you no longer need the GROUP BY clause. Also you don't need an alias for the average price as you don't ever access that column directly.
Another way to do the same thing is with a JOIN to a table of average price values:
SELECT *
FROM art a1
JOIN (SELECT painter_id, AVG(price) AS avg_price
FROM art
GROUP BY painter_id) a2
ON a2.painter_id = a1.painter_id AND a1.price > a2.avg_price
answered Nov 16 '18 at 8:35
NickNick
38.2k132443
Oh, I see! Thank you so much, I didn't realize instantiating a table multiple times (for lack of a better term) like that would have that effect!
– Jon Derr
Nov 16 '18 at 8:52
add a comment |
Your query is very close, you just need to connect (correlate) the subquery to the main query, using the value of painter_id:
SELECT *
FROM art a1
WHERE price > (SELECT AVG(price)
FROM art a2
WHERE a2.painter_id = a1.painter_id);
Note that since you are qualifying the subquery with the painter_id you no longer need the GROUP BY clause. Also you don't need an alias for the average price as you don't ever access that column directly.
Another way to do the same thing is with a JOIN to a table of average price values:
SELECT *
FROM art a1
JOIN (SELECT painter_id, AVG(price) AS avg_price
FROM art
GROUP BY painter_id) a2
ON a2.painter_id = a1.painter_id AND a1.price > a2.avg_price
answered Nov 16 '18 at 8:35
NickNick
38.2k132443
Your query is very close, you just need to connect (correlate) the subquery to the main query, using the value of painter_id:
SELECT *
FROM art a1
WHERE price > (SELECT AVG(price)
FROM art a2
WHERE a2.painter_id = a1.painter_id);
Note that since you are qualifying the subquery with the painter_id you no longer need the GROUP BY clause. Also you don't need an alias for the average price as you don't ever access that column directly.
Another way to do the same thing is with a JOIN to a table of average price values:
SELECT *
FROM art a1
JOIN (SELECT painter_id, AVG(price) AS avg_price
FROM art
GROUP BY painter_id) a2
ON a2.painter_id = a1.painter_id AND a1.price > a2.avg_price
answered Nov 16 '18 at 8:35
NickNick
38.2k132443
answered Nov 16 '18 at 8:35
NickNick
38.2k132443
answered Nov 16 '18 at 8:35
NickNick
38.2k132443
answered Nov 16 '18 at 8:35
NickNick
38.2k132443
38.2k132443
Oh, I see! Thank you so much, I didn't realize instantiating a table multiple times (for lack of a better term) like that would have that effect!
– Jon Derr
Nov 16 '18 at 8:52
add a comment |
Oh, I see! Thank you so much, I didn't realize instantiating a table multiple times (for lack of a better term) like that would have that effect!
– Jon Derr
Nov 16 '18 at 8:52
Oh, I see! Thank you so much, I didn't realize instantiating a table multiple times (for lack of a better term) like that would have that effect!
– Jon Derr
Nov 16 '18 at 8:52
Oh, I see! Thank you so much, I didn't realize instantiating a table multiple times (for lack of a better term) like that would have that effect!
– Jon Derr
Nov 16 '18 at 8:52
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53333778%2fhow-to-design-a-sql-subquery%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
