SQL subquery examples

Join a subquery

SELECT DISTINCT Email FROM Accounts JOIN 
(
    SELECT CouponId, UserId, AccountId 
    FROM Coupons JOIN Users USING(UserId) 
    WHERE CouponType=123
) AS tbl
USING(AccountId);

Database Interview Questions

What is the difference between a "where" clause and a "having" clause?
“Where” is a restriction statement. You use where clause to restrict data being
accessed from the database. Where clause is used before result is retrieved. But
having clause is used after retrieving the data. Having clause is a kind of filtering
command.

What is "normalization"? "Denormalization"? Why do you sometimes want
to denormalize?
Normalizing data means eliminating redundant information from a table and
organizing the data so that future changes to the table are easier. Denormalization
means allowing redundancy in a table. The main benefit of denormalization is
improved performance with simplified data retrieval and manipulation. This is done
by reduction in the number of joins needed for data processing.

What types of join algorithms can you have?
There are three kinds of JOINS in SQL Server, Nested table, Merge and Hash joins.
All of the above depend upon the resources available, MERGE and HASH consume
lots of memory; so, a system that is low on memory would sparingly resort to the
above two join methodologies.

Useful SQL queries

Drop two columns from a table

ALTER TABLE MyTable DROP COLUMN Column1, DROP COLUMN Column2;

Add twocolumns into a table

ALTER TABLE MyTable ADD COLUMN Column1 INT(11) DEFAULT '0', ADD COLUMN Column2 INT(11) DEFAULT '0';