Question

pls help me with these SQL questions

Task 10 (4 marks) Write an SQL statement to display fan categories, the number of tickets in each category the fan bought (change the alias to "NUMBER OF TICKETS PURCHASED") along with the sum of prices (change the alias to TOTAL PRICES') in each category the fans paid. Sort the result by total price in an ascending order. Task 11 (5 marks) Write an SQL statement to display match ID and the count of tickets sold for each match ONLY if the number of tickets sold of this match is more than 1 and the open year of the stadium on which the match will be played is not 2013. Task 12 (5 marks) Write an SQL statement to display the stadium name and the number of matches played in each stadium (change the alias to "NUMBER OF MATCHES"). Display only the top two stadiums where most matches were held. Sort the result by the number of matches played on each stadium in a descending order. NOTE: Do Not use a static (fixed) value to determine the largest number of matches] Task 13 (5 marks) Write an SQL statement to display team name and its manager if Croatia is one of the teams in any match (either as a first or a second team). Manager's name should be displayed as initialisation of first name followed by a dot and then the last name. The alias of the manager column should be changed to "Manager Initials". For example, if manager's name is David Smith, it will be displayed as D. Smith

Here are tables

TEAM table

Answer 1

Task 10- Aggregate function COUNT() and SUM() are used along with group by clause to get the result. ORDER BY is used to display the result in ascending order.

SELECT f.FANCATEGORY,COUNT(t.TICKETID) AS "NUMBER OF TICKET PURCHASED",
SUM(t.PRICE) AS "TOTAL PRICES"
FROM FAN AS f
INNER JOIN TICKET AS t
ON f.FANID = t.FANID
GROUP BY f.FANCATEGORY
ORDER BY "TOTAL PRICES" ASC;

Task 11- Aggregate function COUNT() is used with group by clause. HAVIN is used to filter the result for greater than 1 count of ticket

SELECT t.MATCHID, COUNT(t.TICKETID)
FROM TICKET AS t
INNER JOIN MATCHES AS m
m.MATCHID = t.MATCHID
WHERE YEAR(m.MATCHID) <> '2013'
GROUP BY t.MATCHID
HAVING COUNT(t.TICKETID) > 1;

Task 12- Aggragate function COUNT() is used to get the result. LIMIT is used to limit the result to top two rows.

SELECT s.STADIUMNAME, COUNT(m.MATCHID) AS "NUMBER OF MATCHES"
FROM STADIUM AS s
INNER JOIN MATCHES AS m
ON m.STADIUMID = s.STADIUMID
GROUP BY s.STADIUMNAME
ORDER BY "NUMBER OF MATCHES" DESC
LIMIT 2;

Task 13- Subquery is used to get the result. String function LEFT() and CONCAT() is used to display the manager name in a specified way.

SELECT m.FIRSTTEAM, (SELECT COUNTRY, CONCAT(LEFT(MANAGER, 1), ". ",RIGHT(MANAGER, charindex(' ', MANAGER)-1) FROM TEAMS WHERE TEAMID = m.FIRSTTEAM),
m.SECONDTEAM, (SELECT COUNTRY, CONCAT(LEFT(MANAGER, 1), ". ",RIGHT(MANAGER, charindex(' ', MANAGER)-1) FROM TEAMS WHERE TEAMID = m.SECONDTEAM)
FROM MATCHES;