frequent sql interview questions

difference between ‘where’ and ‘having’ clause

‘where’ clause is used to apply a condition on an column directly.

Usage
select * from table_emp where col_emp_sal<10000;

‘having’ clause is used to apply a condition on a aggregate column.

Usage
select sum(col_emp_sal) as aggr_col_emp_sal from table_emp having aggr_col_emp_sal<10000;

Note: In the above query we cannot use where clause on aggregate alias ‘aggr_col_emp_sal’

difference between ‘distinct’ and ‘group by’ clauses

‘distinct’ keyword is used to remove duplicates
D1.select distinct(col_dept_id) from Table_Employee;  

‘group by’ enables us to use aggregate functions, see in more detail here
G1: select col_sales_date, sum(col_sales) from Table_Sales GROUP BY col_sales_date;
G2: select sum(col_sales) from Table_Sales;

If D1&G2 is considered, both returns the same result but the way they work internally, their execution plans varies across the database servers.

In MS SQL: The execution plan for both the queries is same and hence there wont be any difference
Doing an “EXPLAIN SELECT DISTINCT” shows “Using where; Using temporary ” MySQL will create a temporary table.

vs a “EXPLAIN SELECT a,b, c from T1, T2 where T2.A=T1.A GROUP BY a” just shows “Using where”

difference between UNION and UNION ALL?

UNION Combines Rows From the Two Tables and Excludes all Duplicates Rows in the Result. Execution time wise, this is slower than UNION ALL since it has to eliminate duplicates.
UNION All Returns all Rows in the UNION Operation, including Duplicates. Execution wise it is faster than UNION but it can perform slower at times due to the greater amount of data transferred across the network.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Mawazo

Mostly technology with occasional sprinkling of other random thoughts

amintabar

Amir Amintabar's personal page

101 Books

Reading my way through Time Magazine's 100 Greatest Novels since 1923 (plus Ulysses)

Seek, Plunnge and more...

My words, my world...

ARRM Foundation

Do not wait for leaders; do it alone, person to person - Mother Teresa

Executive Management

An unexamined life is not worth living – Socrates

Diabolical or Smart

Nitwit, Blubber, Oddment, Tweak !!

javaproffesionals

A topnotch WordPress.com site

thehandwritinganalyst

Just another WordPress.com site

coding algorithms

"An approximate answer to the right problem is worth a good deal more than an exact answer to an approximate problem." -- John Tukey