In this video we will discuss an advanced interview question on SQL asked in a Data Engineer interview based on Game of Thrones Database.
Kickoff your data analytics journey : https://www.namastesql.com/

Script:
Create the 'king' table
CREATE TABLE king (
k_no INT PRIMARY KEY,
king VARCHAR(50),
house VARCHAR(50)
);

Create the 'battle' table
CREATE TABLE battle (
battle_number INT PRIMARY KEY,
name VARCHAR(100),
attacker_king INT,
defender_king INT,
attacker_outcome INT,
region VARCHAR(50),
FOREIGN KEY (attacker_king) REFERENCES king(k_no),
FOREIGN KEY (defender_king) REFERENCES king(k_no)
);

delete from king;
INSERT INTO king (k_no, king, house) VALUES
(1, 'Robb Stark', 'House Stark'),
(2, 'Joffrey Baratheon', 'House Lannister'),
(3, 'Stannis Baratheon', 'House Baratheon'),
(4, 'Balon Greyjoy', 'House Greyjoy'),
(5, 'Mace Tyrell', 'House Tyrell'),
(6, 'Doran Martell', 'House Martell');

delete from battle;
Insert data into the 'battle' table
INSERT INTO battle (battle_number, name, attacker_king, defender_king, attacker_outcome, region) VALUES
(1, 'Battle of Oxcross', 1, 2, 1, 'The North'),
(2, 'Battle of Blackwater', 3, 4, 0, 'The North'),
(3, 'Battle of the Fords', 1, 5, 1, 'The Reach'),
(4, 'Battle of the Green Fork', 2, 6, 0, 'The Reach'),
(5, 'Battle of the Ruby Ford', 1, 3, 1, 'The Riverlands'),
(6, 'Battle of the Golden Tooth', 2, 1, 0, 'The North'),
(7, 'Battle of Riverrun', 3, 4, 1, 'The Riverlands'),
(8, 'Battle of Riverrun', 1, 3, 0, 'The Riverlands');
for each region find house which has won maximum no of battles. display region, house and no of wins
select * from battle;
select * from king;

Zero to hero(Advance) SQL Aggregation:
   • All About SQL Aggregations | SQL Adva...  

Most Asked Join Based Interview Question:
   • Most Asked SQL JOIN based Interview Q...  

Solving 4 Trick SQL problems:
   • Solving 4 Tricky SQL Problems  

Data Analyst Spotify Case Study:
   • Data Analyst Spotify Case Study | SQL...  

Top 10 SQL interview Questions:
   • Top 10 SQL interview Questions and An...  

Interview Question based on FULL OUTER JOIN:
   • SQL Interview Question Based on Full ...  

Playlist to master SQL :
   • Complex SQL Questions for Interview P...  

Rank, Dense_Rank and Row_Number:
   • RANK, DENSE_RANK, ROW_NUMBER SQL Anal...  

#sql #dataengineer #got