1. There are 2 tables.
Column in table 1 have values: 1 0 1 and Column in table 2 have
values: 1 0 1 0 1. what will be the total number of rows as an output if we Inner Join both
the tables
2. What will be the output for left join instead of inner join ?
3. Which one is faster: count and count D ? Why?
Select from table 1 Inner join table2
On table1.column1=table2.column1;
Select * from table1 left join table 2
On table1.column1= table2.column2;
Select count(*) from table1 inner join table 2
On table1.column1= table2.column2;
1) What is row number in SQL? What is it’s use ?
2) Difference between limit and row number ?
3) Can we use two different joins in same query?
4) How many maximum joins we can use in one query?
5) Lets say we have 2 column: ID and city, each ID is unique , but two or more ID’S
can have same city name, write query to get max ID by each city.
6) Now in the above asked question, we want to see that cities only which have
more than 2 ID’S?
7) Same table ,but this time there is a date column too and there are multiple
dates for each city, for example,2-march-2019,2-march-2020 dates are for the city
Jaipur . How to get the latest date for each city?
1)What is row number in SQL? What is it’s use ?
The ROW_NUMBER() is assigns a sequential integer to each row within the
partition of a
result set . The row number starts with 1 for the first row in each partition.
SELECT
ROW_NUMBER() OVER(PARTITION BY recovery _ model _ desc ORDER BY name ASC)
AS Row,
name, recovery _ model _ desc
FROM student WHERE student _ id < 5;
TABLE 2
Row name recovery desc
1 master SIMPLE
2 model FULL
3 msd SIMPLE
4 tempb SIMPLE
2)Difference between limit and row number ?
LIMIT : LIMIT clause to select a limited number of records for use
ROW NUMBER: row number is an analytics function which assigns a unique
number to each row to which it is apply and its usually began with 1.
3)Can we use two different joins in same query?
Yes we use two different joins in same query.
4)How many maximum joins we can use in one query?
earlier days limit was 256, but lately its all limited by server
resources available.
5)Lets say we have 2 column: ID and city, each ID is unique , but two or more ID’S
can have same city name, write query to get max ID by each city.
6)Now in the above asked question, we want to see that cities only which
have more than 2 ID’S?
7) Same table ,but this time there is a date column too and there are multiple
dates for each city, for example,2-march-2019,2-march-2020 dates are for the city
Jaipur . How to get the latest date for each city?
[Link] will be the output for this query : SELECT count(*)
from table1, table2
[Link] a table be join to table itself?
3. How to determine the number of duplicates in a tables .
[Link] the number of non-null entries in a column.
5. How to calculate max salary of employee?
6. What is an alternative for TOP clause in SQL ?
1. What will be the output for this query : SELECT count(*) from table1, table2
2. Can a table be join to table itself?
Yes a table be join to table itself . We can do this with the help of self join.
3)How to determine the number of duplicates in a tables .
4)Count the number of non-null entries in a column.
Select count(salary) from detail
Where salary is not null ;
5)How to calculate max salary of employee?
6)What is an alternative for TOP clause in SQL ?
There is an alternative to TOP clause, which is to use ROWCOUNT. Use ROWCOUNT
with care, as it can lead you into all sorts of problems if it’s not turned off.