#sql Interview question Schedule cricket match between teams and generate points table using SQL

Hi All,
This is Ankit Shrivastava and I am working as Data Engineer.
Today this #vlog is regarding mostly asked #sql #sqlinterviewquestions .

This #vlog is regarding how we can schedule cricket match between teams and generate points table.
This is very interesting SQL interview question having very simple approach.
In this video I have used self join to case statement to generate the required output.

Please find the below complete DDL for practice

CREATE TABLE CRICKET_TEAMS(
COUNTRY VARCHAR(50)
);

INSERT INTO CRICKET_TEAMS VALUES('India');
INSERT INTO CRICKET_TEAMS VALUES('Pakistan');
INSERT INTO CRICKET_TEAMS VALUES('England');
INSERT INTO CRICKET_TEAMS VALUES('Australia');

CREATE TABLE SUMMARY(
TEAM_1 VARCHAR(20),
TEAM_2 VARCHAR(20),
winner VARCHAR(20)
);

INSERT INTO SUMMARY VALUES('India','Pakistan','India');
INSERT INTO SUMMARY VALUES('India','England','India');
INSERT INTO SUMMARY VALUES('India','Australia',null);
INSERT INTO SUMMARY VALUES('Pakistan','England','England');
INSERT INTO SUMMARY VALUES('Pakistan','Australia','Pakistan');
INSERT INTO SUMMARY VALUES('England','Australia','Australia');

#sqlinterviewquestions #sqlinterviewquestionsandanswers
#sql #innerjoin #leftjoin #rightjoin #fullouterjoin
#trendinginterview #interview #oracle #interviewtips #mysql #interviewready #interviewpreparation #sqljoins #null #notnull
#sqlserver #sqlservermanagementstudio #etltesting #databaseexercises #databasetesting #testing #data #dataengineering #dataengineer #twitter #elonmusk #sqlserverdba #microsoft #postgresql #postgresql #value #null #ipl #worldcup #t20worldcup #cricket #australia #india #pakistan #england #cricketmatch #amazon #netflix #meta #facebook #google #apple #tcs #infosys #software #selfjoin #bigdata

[saurabhshukla]

Hi this was a great video. But one suggestion for part one, instead of using case for country_id you can directly use ROW_NUMBER which does the same thing. Gives unique ID for each row.

[AtulSingh-bejk]

Please make a video to create a procedure for it.

[AtulSingh-bejk]

Hi..
Suppose, i have a table 'A' with five child table namely 'B', 'C', 'D', 'E', 'F'.
Now, i like to delete the records of 'A' directly without deleting the child table one by one.
Here, the problem is that out of those five child tables one or more can have also child tables.So, it is very difficult to go one by one and delete the all child tables followed by parent table.

Also, there is possibilities of adding one or more child table to any of those tables described above in later point of time.In that case my query which is developed at this moment will not work and i would get error of ''INTEGRITY CONSTRAINT VIOLATION''.
Also, there is no provision of ''on delete cascade'' as per our available data model and
also it can't be modified right now.

So, please kindly help me to resolve the issue of deleting parent record without deleting the immediate child tables one by one and also the associated child tables of the immediate child tables if any one by one.