Create the following SQL Queries The database contains the following six tables:
ID: 3723779 • Letter: C
Question
Create the following SQL Queries
The database contains the following six tables:
coaches_season, each tuple of which describes the performance of one coach in one season;
teams, each tuple of which gives the basic information of a team;
players, each tuple of which gives the basic information of one player;
player_rs, each tuple of which gives the detailed performance of one player in one regular season;
player_rs_career, each tuple of which gives the detailed regular-season performance of one player in his career;
draft, each tuple of which shows the information of an NBA draft.
DBMS used is PostgreSQL
10. List the name(s) of school(s) that sent the second largest number of drafts to NBA. List the name of each school and the number of drafts sent.
coaches_season, each tuple of which describes the performance of one coach in one season;
teams, each tuple of which gives the basic information of a team;
players, each tuple of which gives the basic information of one player;
player_rs, each tuple of which gives the detailed performance of one player in one regular season;
player_rs_career, each tuple of which gives the detailed regular-season performance of one player in his career;
draft, each tuple of which shows the information of an NBA draft.
Explanation / Answer
// Hello, There might have been some gap in information provided
// as there was no table structure given, i assumed it as per the verbal information
// and have written two queries corresponding to two statements
// I hope my assumptions are right and the query is helpful, if there is change in requirement
// please let me know in comments i'll update and answer with it.
// Thanks, and leave a thumbs up if the solution helps.
// 1. List the name(s) of school(s) that sent the second largest number of drafts to NBA
SELECT schoolname,MAX( temp.count )
FROM (SELECT schoolname, count(schoolname) as count
FROM draft d
group by(schoolname)) as temp
WHERE temp.count < ( SELECT max(count)
from (SELECT schoolname, count(schoolname) as count
FROM draft d
group by(schoolname)) as maxtable)
group by temp.schoolname
// 2. List the name of each school and the number of drafts sent.
SELECT schoolname, count(schoolname) as count
FROM draft d
group by(schoolname)
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.