Solving a SQL MCQ (Online Assessment) Question like a PRO 🦸

Solve High Quality SQL interview questions of product based companies : www.namastesql...
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer #datanalytics

[@ankitbansal6]

Page 12 question number 116 www.namastesql.com/coding-problems

[@Datapassenger_prashant]

Sir, it's not accessisble

[@pandeyRaman]

It's locked...

[@theraizadatalks14]

-- 27- SQL lists every game with goals scored by each team /* -- Drop the tables if they already exist DROP TABLE IF EXISTS goal; DROP TABLE IF EXISTS game; DROP TABLE IF EXISTS eteam; -- Create table for 'eteam' CREATE TABLE eteam ( id CHAR(3) PRIMARY KEY, teamname VARCHAR(100), coach VARCHAR(100) ); -- Insert data into 'eteam' INSERT INTO eteam (id, teamname, coach) VALUES ('POL', 'Poland', 'Franciszek Smuda'), ('RUS', 'Russia', 'Dick Advocaat'), ('CZE', 'Czech Republic', 'Michal Bilek'), ('GRE', 'Greece', 'Fernando Santos'); -- Create table for 'game' CREATE TABLE game ( id INT PRIMARY KEY, mdate DATE, stadium VARCHAR(100), team1 CHAR(3), team2 CHAR(3), FOREIGN KEY (team1) REFERENCES eteam(id), FOREIGN KEY (team2) REFERENCES eteam(id) ); -- Insert data into 'game' INSERT INTO game (id, mdate, stadium, team1, team2) VALUES (1001, '2012-06-08', 'National Stadium, Warsaw', 'POL', 'GRE'), (1002, '2012-06-08', 'Stadion Miejski (Wroclaw)', 'RUS', 'CZE'), (1003, '2012-06-12', 'Stadion Miejski (Wroclaw)', 'GRE', 'CZE'), (1004, '2012-06-12', 'National Stadium, Warsaw', 'POL', 'RUS'); -- Create table for 'goal' CREATE TABLE goal ( matchid INT, teamid CHAR(3), player VARCHAR(100), gtime INT, FOREIGN KEY (matchid) REFERENCES game(id), FOREIGN KEY (teamid) REFERENCES eteam(id) ); -- Insert data into 'goal' INSERT INTO goal (matchid, teamid, player, gtime) VALUES (1001, 'POL', 'Robert Lewandowski', 17), (1001, 'GRE', 'Dimitris Salpingidis', 51), (1002, 'RUS', 'Alan Dzagoev', 15), (1002, 'RUS', 'Roman Pavlyuchenko', 82); */ ------------------------------------------------------------ Select * from eteam Select * from game Select * from goal ------------------------------------------------------------ Select mdate, team1, team2, sum(case when team1=teamid then 1 else 0 end) as team1_score, sum(case when team2=teamid then 1 else 0 end) as team2_score from game gm left join goal gl on gm.id = gl.matchid group by mdate, team1, team2 ------------------------------------------------------------

[@typing07]

Thanks @ankit for addressing this question. It will really help full.

[@Swarnab-wq8km]

Hi Ankit, can you do video on incremental testing

[@FromPlanetZX]

Hi All, I need help, I tried to create the table in MS SQL Server. While inserting records into goal table, for goal_time column, for value '67:12:00' , it is throughing an error How to rectify that.

[@rahulmehla2014]

ankit have you included all 100 interview questions, that you used to have as a course, in practice arena ?

[@ankitbansal6]

Yes and more questions apart from that

[@VikashKumar0409]

-- Create Team Table CREATE TABLE Team ( id CHAR(1) PRIMARY KEY, teamname VARCHAR(50), coach VARCHAR(50) ); -- Create Goal Table CREATE TABLE Goal ( matchid INT, teamid CHAR(1), player VARCHAR(50), goal_time INT, ); -- Create Game Table CREATE TABLE Game ( id INT PRIMARY KEY, mdate DATE, stadium VARCHAR(50), team1 CHAR(1), team2 CHAR(1), ); -- Insert Data into Team Table INSERT INTO Team (id, teamname, coach) VALUES ('A', 'Team_A', 'Coach_A'), ('B', 'Team_B', 'Coach_B'), ('C', 'Team_C', 'Coach_C'), ('D', 'Team_D', 'Coach_D'); -- Insert Data into Goal Table INSERT INTO Goal (matchid, teamid, player, goal_time) VALUES (101, 'A', 'A1', 17), (101, 'A', 'A9', 58), (101, 'B', 'B7', 89), (102, 'D', 'D10', 63); -- Insert Data into Game Table INSERT INTO Game (id, mdate, stadium, team1, team2) VALUES (101, '2019-01-04', 'stadium1', 'A', 'B'), (102, '2019-01-04', 'stadium3', 'D', 'E'), (103, '2019-01-10', 'stadium1', 'A', 'C'), (104, '2019-01-13', 'stadium2', 'B', 'E');

[@denkasmic_6947]

It's locked

[@SaurabhGupta-oo2jo]

can you please provide CREATE AND INSERT queries of this question? Thanks

[@Damon-007]

Take screenshot of the table and upload in hchatgpt and write"give me sql script of all tables in above pic "

[@Hkumar_new]

Sorry, sir, but sir many questions hard language, means questions are more encapsulated by self , not much explained

[@ankitbansal6]

Thanks for the feedback, I'll try to make the questions more clear in future videos.

[@rahulsharma5078]

Make videos in Hindi also 😂

[@-es2bf]

It already is

[@sandeepanand3834]

my solution became a bit lengthy with cte1 as ( select game.match_date, game.team1, sum(case when goal.team_id is null then 0 else 1 end) score_1 from game left join goal on game.match_id = goal.match_id and game.team1 = goal.team_id group by game.match_id, game.team1 ), cte2 as ( select game.match_date, game.team2, sum(case when goal.team_id is null then 0 else 1 end) score_2 from game left join goal on game.match_id = goal.match_id and game.team2 = goal.team_id group by game.match_id, game.team2 ) select cte1.*, cte2.team2, cte2.score_2 from cte1 join cte2 on cte1.match_date = cte2.match_date

[@8754266374]

this question is in SQLZOO

[@theraizadatalks14]

/* -- Drop the tables if they already exist DROP TABLE IF EXISTS goal; DROP TABLE IF EXISTS game; DROP TABLE IF EXISTS eteam; -- Create table for 'eteam' CREATE TABLE eteam ( id CHAR(3) PRIMARY KEY, teamname VARCHAR(100), coach VARCHAR(100) ); -- Insert data into 'eteam' INSERT INTO eteam (id, teamname, coach) VALUES ('POL', 'Poland', 'Franciszek Smuda'), ('RUS', 'Russia', 'Dick Advocaat'), ('CZE', 'Czech Republic', 'Michal Bilek'), ('GRE', 'Greece', 'Fernando Santos'); -- Create table for 'game' CREATE TABLE game ( id INT PRIMARY KEY, mdate DATE, stadium VARCHAR(100), team1 CHAR(3), team2 CHAR(3), FOREIGN KEY (team1) REFERENCES eteam(id), FOREIGN KEY (team2) REFERENCES eteam(id) ); -- Insert data into 'game' INSERT INTO game (id, mdate, stadium, team1, team2) VALUES (1001, '2012-06-08', 'National Stadium, Warsaw', 'POL', 'GRE'), (1002, '2012-06-08', 'Stadion Miejski (Wroclaw)', 'RUS', 'CZE'), (1003, '2012-06-12', 'Stadion Miejski (Wroclaw)', 'GRE', 'CZE'), (1004, '2012-06-12', 'National Stadium, Warsaw', 'POL', 'RUS'); -- Create table for 'goal' CREATE TABLE goal ( matchid INT, teamid CHAR(3), player VARCHAR(100), gtime INT, FOREIGN KEY (matchid) REFERENCES game(id), FOREIGN KEY (teamid) REFERENCES eteam(id) ); -- Insert data into 'goal' INSERT INTO goal (matchid, teamid, player, gtime) VALUES (1001, 'POL', 'Robert Lewandowski', 17), (1001, 'GRE', 'Dimitris Salpingidis', 51), (1002, 'RUS', 'Alan Dzagoev', 15), (1002, 'RUS', 'Roman Pavlyuchenko', 82); */ ------------------------------------------------------------ Select * from eteam Select * from game Select * from goal ------------------------------------------------------------