Complied vs Interpreted languages

Programming languages generally fall into one of two categories: Compiled or Interpreted. With a compiled language, code you enter is reduced to a set of machine-specific instructions before being saved as an executable file. With interpreted languages, the code is saved in the same format that you entered. Compiled programs generally run faster than interpreted ones because interpreted programs must be reduced to machine instructions at runtime. However, with an interpreted language you can do things that cannot be done in a compiled language. For example, interpreted programs can modify themselves by adding or changing functions at runtime. It is also usually easier to develop applications in an interpreted environment because you don't have to recompile your application each time you want to test a small section.

Puzzle : The Elder Twin

One day Kerry celebrated her birthday. Two days later her older twin brother, Terry, celebrated his birthday. How come?

Sol : At the time she went into labor, the mother of the twins was traveling by boat. The older twin, Terry, was born first early on March 1st. The boat then crossed the international date line (or anytime zone line) and Kerry, the younger twin, was born on February the 28th. In a leap year the younger twin celebrates her birthday two days before her older brother..

http://www.bellaonline.com/articles/art39652.asp

Why can’t variable names start with numbers?

Because then a string of digits would be a valid identifier as well as a valid number.

int 17 = 497;
int 42 = 6 * 9;
String 1111 = "Totally text";

int 2d = 42;
double a = 2d;
What is a? 2.0? or 42?

Hint, if you don't get it, d after a number means the number before it is a double literal

http://stackoverflow.com/questions/342152/why-cant-variable-names-start-with-numbers

Correlated SubQuery -- Find Nth maximum value in SQL Server

Use Pubs
Go

Create table Employee
(
Eid int,
Name varchar(10),
Salary money
)
Go

Insert into Employee values (1,'harry',3500)
Insert into Employee values (2,'jack',2500)
Insert into Employee values (3,'john',2500)
Insert into Employee values (4,'xavier',5500)
Insert into Employee values (5,'steven',7500)
Insert into Employee values (6,'susana',2400)
Go
A simple query that can find the employee with the maximum salary, would be:

Select * from Employee where salary = (Select max(Salary) from Employee)
How does this query work?

The SQL Engine evaluates the inner most query and then moves to the next level (outer query). So, in the above example inner query i.e. Select max(Salary) from Employee is evaluated first. This query will return a value of 7500 (based on the sample data shown as above). This value is substituted in the outer query and it is evaluated as:

Select * from Employee where salary = (7500)
Returns:

Eid Name Salary
5 steven 7500
If the same syntax is applied to find out the 2nd or 3rd or 4th level of salary, the query would become bit complex to understand. See the example below:

Select * from Employee where salary =
(Select max(Salary) from Employee where salary
< (Select max(Salary) from Employee where Salary < (Select max(Salary) from Employee where Salary <…………………………………………… N The above query would go on and on, depending on the level of salary that is to be determined. As mentioned earlier, the SQL Engine evaluates the inner most query first and moves the next outer level. One wouldn’t want to write such a big query just to find out this simple information. The same result can be achieved with a simple syntax and easily understandable logic, by using a CORRELATED SUBQUERY.
 As a "Rule of Thumb" keep these points in mind, when you use a correlated sub-query :
1) Correlated sub-query is a performance overhead to the database server and so, you have to use it only if it is required
2) Avoid using Correlated subquery on large tables, as the inner query is evaluated for each row of the outer query Having said that, let’s look at the query that captures the Nth maximum value: Select * From Employee E1 Where (N-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where E2.Salary > E1.Salary)
(Where N is the level of Salary to be determined)

In the above example, the inner query uses a value of the outer query in its filter condition meaning; the inner query cannot be evaluated before evaluating the outer query. So each row in the outer query is evaluated first and the inner query is run for that row. Let’s look into the background process of this query, by substituting a value for N i.e. 4,(Idea is to find the 4th maximum salary):

Select * From Employee E1 Where
(4-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
Since the outer query’s value is referred in the inner query, the operation is done row-by-row. Based on the sample data as shown above, the process starts with the following record:

Employee E1
----------------------------------
Eid Name Salary
1 harry 3500
The salary of this record is substituted in the inner query and evaluated as:

Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 3500
Above query returns 2 (as there are only 2 salaries greater than 3500). This value is substituted in the outer query and will be evaluated as:

Select * From Employee E1 Where (4-1) = (2)
The "where" condition evaluates to FALSE and so, this record is NOT fetched in the result.

Next the SQL Engine processes the 2nd record which is:

Employee E1
----------------------------------
Eid Name Salary
2 jack 2500
Now the inner query is evaluated as:

Select Count(Distinct(E2.Salary)) From Employee E2
Where E2.Salary > 2500
This query returns a value of 3 (as there are 3 salaries greater than 2500). The value is substituted in the outer query and evaluated as:

Select * From Employee E1 Where (4-1) = (3)
The "where" condition evaluates to TRUE and so, this record IS fetched in the result. This operation continues for all the remaining records. Finally the result shows these 2 records:

Eid Name Salary
2 jack 2500
3 john 2500
The above query works in the same manner in Oracle and Sybase as well. Applying the same logic, to find out the first maximum salary the query would be:

Select * From Employee E1 Where
(1-1) = (Select Count(Distinct(E2.Salary)) From Employee E2 Where
E2.Salary > E1.Salary)
If you are able to understand this functionality, you can workout various other queries in the same manner. The bottom line is, the query should be efficient and NOT resource hungry.

http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server

Important SQL Queries

Deleting only dupes from a table

DELETE FROM mytable
WHERE EXISTS
( SELECT name1, age1, MIN(id) as cf_prog
FROM mytable TAB02
WHERE ( mytable.name1 = TAB02.name1 ) AND
( mytable.age1 = TAB02.age1 )
GROUP BY name1, age1
HAVING ( count(*) > 1 ) AND
( mytable.id <> cf_prog ) )

Selecting Dupes from a table

select cargo_id, dest_id
from routing t1
where
( select count(*)
from routing t2
where t2.dest_id = t1.dest_id ) > 1


Finding the Nth maximum from a table

select *
from yourTable X
where n-1 =
(select count(*)
from yourTable
where salary > X.salary)
order by salary desc

Selecting the top n rows from a table


select *
from employee X
where n >
(select count(*)
from employee
where dept = X.dept
and salary > X.salary)
order by dept, salary desc

Where vs Having

SQL Standard says that WHERE restricts the result set before returning rows and HAVING restricts the result set after bringing all the rows. So WHERE is faster. On SQL Standard compliant DBMSs in this regard, only use HAVING where you cannot put the condition on a WHERE (like computed columns in some RDBMSs.)
HAVING clauses should be used to apply conditions on group functions, otherwise they can be mvoed into the WHERE condition.

http://stackoverflow.com/questions/328636/which-sql-statement-is-faster-having-vs-where

SQL - Cursors

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

Please note that cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row. In the Cursor Performance article I discovered that cursors are over thirty times slower than set based alternatives.
The basic syntax of a cursor is:

DECLARE @AuthorID char(11)

DECLARE c1 CURSOR READ_ONLY
FOR
SELECT au_id
FROM authors

OPEN c1

FETCH NEXT FROM c1
INTO @AuthorID

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @AuthorID

FETCH NEXT FROM c1
INTO @AuthorID

END

CLOSE c1
DEALLOCATE c1
The DECLARE CURSOR statement defines the SELECT statement that forms the basis of the cursor. You can do just about anything here that you can do in a SELECT statement. The OPEN statement statement executes the SELECT statement and populates the result set. The FETCH statement returns a row from the result set into the variable. You can select multiple columns and return them into multiple variables. The variable @@FETCH_STATUS is used to determine if there are any more rows. It will contain 0 as long as there are more rows. We use a WHILE loop to move through each row of the result set.

The READ_ONLY clause is important in the code sample above. That dramatically improves the performance of the cursor.

In this example, I just print the contents of the variable. You can execute any type of statement you wish here. In a recent script I wrote I used a cursor to move through the rows in a table and call a stored procedure for each row passing it the primary key. Given that cursors are not very fast and calling a stored procedure for each row in a table is also very slow, my script was a resource hog. However, the stored procedure I was calling was written by the software vendor and was a very easy solution to my problem. In this case, I might have something like this:

EXEC spUpdateAuthor (@AuthorID)
instead of my Print statement. The CLOSE statement releases the row set and the DEALLOCATE statement releases the resources associated with a cursor.

If you are going to update the rows as you go through them, you can use the UPDATE clause when you declare a cursor. You'll also have to remove the READ_ONLY clause from above.

DECLARE c1 CURSOR FOR
SELECT au_id, au_lname
FROM authors
FOR UPDATE OF au_lname
You can code your UPDATE statement to update the current row in the cursor like this

UPDATE authors
SET au_lname = UPPER(Smith)
WHERE CURRENT OF c1

http://www.sqlteam.com/article/cursors-an-overview

SQL interview Questions

Q: 1. What are two methods of retrieving SQL?
A: SELECT statement, cursors