Number of rows in the result
Question
TheDataMonk
Follow Me
About TheDataMonkGrand Master
I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)Follow Me
Related questions
The Given Data set, Table has transitive dependencies. Explain Which Attributes are partially dependent. How will you overcome this? Explain the approach?
Write a query to Plot a Relationship between the highest followers in every country (Country Wise Highest Followers) and show which country has the highest followers and which country has the lowest one. Explain the approach?
Write a Query to Find the account with the highest number of followers in every country. Explain the approach?
Assign Rank To each Category by authentic engagement in each country and return the category with a rank less than 3. Explain the Approach?
Write a Query to check for Missing values in the given table? Explain how will you deal with the missing data for qualitative and quantitative data?
Answers ( 4 )
Joining the 2 tables based on their values (A_values & B_values) and counting number of rows:
Tables: A & B. Columns: A_values & B_values
Inner: (Selects all the values common to both the tables)
SELECT COUNT(*) FROM A INNER JOIN B
ON A.A_values= B.B_values
Left: (Selects all values from A: Intersection values plus only A)
SELECT COUNT(*) FROM A
LEFT JOIN B
ON A.A_values= B.B_values
RIGHT: (Selects all values from B: Intersection values plus only B)
SELECT COUNT(*) FROM A RIGHT JOIN B
ON A.A_values= B.B_values
CROSS: In cross join, each value of the first table gets matched with each value of the second table, like a cartesian product. All types of possible combinations are derived from cross join.
SELECT COUNT(*) FROM A CROSS JOIN B
A – 1,2,3,4,5
B – 1,2,3,Null,Null
Left JOIN-1,2,3,4,5
RIGHT Join-1,2,3,Null,Null
CROSS Join
1,1
1,2
1,3
1,Null
1,Null
2,1
2,2
2,3
2,Null
2,Null
3,1
3,2
3,3
3,Null
3,Null
4,1
4,2
4,3
4,Null
4,Null
5,1
5,2
5,3
5,Null
5,Null
INNER JOIN
1,2,3
There are two tables A,B with 5 rows
A – 1,2,3,4,5
B – 1,2,3,Null,Null
The output when you do-
-Left – 5 rows – all of Table A
-Right – 5 rows – all of Table 5
-Cross – 25 rows – all rows of Table A mapped with all rows of Table B. Null doesn’t affect results.
-Inner Join – 3 rows (1,2,3) of Table A and Table B
There are two tables A,B with 5 rows
A – 1,2,3,4,5
B – 1,2,3,Null,Null
What will be the output when you do
-Left Join : So according to the definition of the left join is that it will return all record form the left table &
matched record from the Right table. All 5 rows of Table A where as 3 matching rows of Table B.
-Right Join : So according to the definition of the right join is that it will return all record from the right table
& matched record from the Left Table. All 5 rows of Table B along with the nulls where as 3 matching rows of
Table A.
-Cross Join : For Cross Join it creates all paired combinations of the rows of the table that will be joined i.e., M*N (
Considering that M & N are rows of the given tables ). All total of 25 records will be shown along with the nulls.
-Inner Join : For inner join, so according to the definition it will return all the records that have matched in both the sides.
All the matching values in both the table will be shown i.e., first 3 matching rows from Table A and Table B.