What is the difference between SQL, PL-SQL and T-SQL?

• SQL is a query language to operate on sets.

  It is more or less standardized, and used by almost all relational database management systems: SQL Server, Oracle, MySQL, PostgreSQL, DB2, Informix, etc.

• PL/SQL is a proprietary procedural language used by Oracle
• PL/pgSQL is a procedural language used by PostgreSQL
• TSQL is a proprietary procedural language used by Microsoft in SQL Server.

Procedural languages are designed to extend SQL’s abilities while being able to integrate well with SQL. Several features such as local variables and string/data processing are added. These features make the language Turing-complete.

They are also used to write stored procedures: pieces of code residing on the server to manage complex business rules that are hard or impossible to manage with pure set-based operations.

http://stackoverflow.com/questions/1043265/what-is-the-difference-between-sql-pl-sql-and-t-sql

SQL distinct for 2 fields in a database

How about simply:

select distinct c1, c2 from t

or

select c1, c2, count(*)
from t
group by c1, c2

http://stackoverflow.com/questions/192924/sql-distinct-for-2-fields-in-a-database

What exactly we mean by Precompiled SQL Statement

If a statement is used multiple times in a session, precompiling it provides better performance than sending it to the database and compiling it for each use. The more complex the statement, the greater the performance benefit.
If a statement is likely to be used only a few times, precompiling it may be inefficient because of the overhead involved in precompiling, saving, and later deallocating it in the database.
Precompiling a dynamic SQL statement for execution and saving it in memory uses time and resources. If a statement is not likely to be used multiple times during a session, the costs of doing a database prepare may outweigh its benefits. Another consideration is that once a dynamic SQL statement is prepared in the database, it is very similar to a stored procedure. In some cases, it may be preferable to create stored procedures and have them reside on the server, rather than defining prepared statements in the application.

http://www.coderanch.com/t/299852/JDBC/databases/Precompiled-SQL-Statement

Using an ORM or plain SQL?

Speaking as someone who spent quite a bit of time working with JPA (Java Persistence API, basically the standardized ORM API for Java/J2EE/EJB), which includes Hibernate, EclipseLink, Toplink, OpenJPA and others, I’ll share some of my observations.

1. ORMs are not fast. They can be adequate and most of the time adequate is OK but in a high-volume low-latency environment they’re a no-no;
2. In general purpose programming languages like Java and C# you need an awful lot of magic to make them work (eg load-time weaving in Java, instrumentation, etc);
3. When using an ORM, rather than getting further from SQL (which seems to be the intent), you’ll be amazed how much time you spend tweaking XML and/or annotations/attributes to get your ORM to generate performant SQL;
4. For complex queries, there really is no substitute. Like in JPA there are some queries that simply aren’t possible that are in raw SQL and when you have to use raw SQL in JPA it’s not pretty (C#/.Net at least has dynamic types–var–which is a lot nicer than an Object array);
5. There are an awful lot of “gotchas” when using ORMs. This includes unintended or unexpected behavior, the fact that you have to build in the capability to do SQL updates to your database (by using refresh() in JPA or similar methods because JPA by default caches everything so it won’t catch a direct database update–running direct SQL updates is a common production support activity);
6. The object-relational mismatch is always going to cause problems. With any such problem there is a tradeoff between complexity and completeness of the abstraction. At times I felt JPA went too far and hit a real law of diminishing returns where the complexity hit wasn’t justified by the abstraction.

There’s another problem which takes a bit more explanation.

The traditional model for a Web application is to have a persistence layer and a presentation layer (possibly with a services or other layers in between but these are the important two for this discussion). ORMs force a rigid view from your persistence layer up to the presentation layer (ie your entities).

One of the criticisms of more raw SQL methods is that you end up with all these VOs (value objects) or DTOs (data transfer objects) that are used by simply one query. This is touted as an advantage of ORMs because you get rid of that.

Thing is those problems don’t go away with ORMs, they simply move up to the presentation layer. Instead of creating VOs/DTOs for queries, you create custom presentation objects, typically one for every view. How is this better? IMHO it isn’t.

I’ve written about this in ORM or SQL: Are we there yet?.

My persistence technology of choice (in Java) these days is ibatis. It’s a pretty thin wrapper around SQL that does 90%+ of what JPA can do (it can even do lazy-loading of relationships although its not well-documented) but with far less overhead (in terms of complexity and actual code).

This came up last year in a GWT application I was writing. Lots of translation from EclipseLink to presentation objects in the service implementation. If we were using ibatis it would’ve been far simpler to create the appropriate objects with ibatis and then pass them all the way up and down the stack. Some purists might argue this is Bad™. Maybe so (in theory) but I tell you what: it would’ve led to simpler code, a simpler stack and more productivity.

http://stackoverflow.com/questions/494816/using-an-orm-or-plain-sql/494853#494853

How to retrieve the current value of an oracle sequence without increment it?

SELECT last_number
  FROM all_sequences
 WHERE sequence_owner = ''
   AND sequence_name = '';

You can get a variety of sequence metadata from user_sequences, all_sequences anddba_sequences.

These views work across sessions.

EDIT:

If the sequence is in your default schema then:

SELECT last_number
  FROM user_sequences
 WHERE sequence_name = '';

If you want all the metadata then:

SELECT *
  FROM user_sequences
 WHERE sequence_name = '';

Hope it helps…

EDIT2:

A long winded way of doing it more reliably if your cache size is not 1 would be:

SELECT increment_by I
  FROM user_sequences
 WHERE sequence_name = 'SEQ';

      I
-------
      1

SELECT seq.nextval S
  FROM dual;

      S
-------
   1234

-- Set the sequence to decrement by 
-- the same as its original increment
ALTER SEQUENCE seq 
INCREMENT BY -1;

Sequence altered.

SELECT seq.nextval S
  FROM dual;

      S
-------
   1233

-- Reset the sequence to its original increment
ALTER SEQUENCE seq 
INCREMENT BY 1;

Sequence altered.

Just beware that if others are using the sequence during this time – they (or you) may get

ORA-08004: sequence SEQ.NEXTVAL goes below the sequences MINVALUE and cannot be instantiated

Also, you might want to set the cache to NOCACHE prior to the resetting and then back to its original value afterwards to make sure you’ve not cached a lot of values.

http://stackoverflow.com/questions/10210273/how-to-retrieve-the-current-value-of-an-oracle-sequence-without-increment-it

How do you do many to many table outer joins?

SELECT * FROM foo
  LEFT OUTER JOIN (foo2bar JOIN bar ON (foo2bar.bid = bar.bid AND zid = 30))
  USING (fid);

Tested on MySQL 5.0.51.

This is not a subquery, it just uses parentheses to specify the precedence of joins.

http://stackoverflow.com/questions/372399/how-do-you-do-many-to-many-table-outer-joins

SQL SELECT Ordering columns with Null Values

…or in order to avoid value clashing…

SELECT 
   ColumnA, 
   ColumnB
FROM YourTable
ORDER BY 
   CASE WHEN ColumnB IS NULL THEN 1 ELSE 0 END ASC,
   ColumnB

http://stackoverflow.com/questions/5492589/sql-select-ordering-columns-with-null-values

Stored Procedures vs Parameterized Queries [closed]

From the .NET tag (and only from the tag) I’m going assume SQL Server here. Parametrized queries are excellent for projects that have a single application and modest security requirements. The require less effort to set up, and many ORM systems are going to use them if you like it or not.

On the other hand, if your database is handled by multiple applications and you need to enforce consistency of access, or if you need complex security (partial access to tables, either by column or by row or both) you should be using stored procedures. They allow you to enforce your security requirements and provide a consistent interface so applications don’t violate your data rules.

As far as performance goes, I haven’t noticed enough difference between the two to care these days: parametrized queries plan caching seems to work well enough.

http://stackoverflow.com/questions/230668/stored-procedures-vs-parameterized-queries

SQL to concatenate column values from multiple rows

There are a few ways depending on what version you have

http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

SELECT pid, LISTAGG(Desc, ' ') WITHIN GROUP (ORDER BY seq) AS desc
FROM   B
GROUP BY pid;

Then join to A to pick out the pids you want.

http://stackoverflow.com/questions/4686543/sql-to-concatenate-column-values-from-multiple-rows

How to calculate average of a column and then include it in a select query in oracle?

Your group by is what aggregates your average, and it is grouping by the whole table (I am assuming you did this to allow the select for everything) Just move your avg into another subquery, remove the overarching group by and that should solve it.

SELECT id, m_name AS "Mobile Name", cost AS Price,
    (SELECT AVG(cost) FROM mobile) AS Average,
    cost-(SELECT AVG(cost) FROM mobile) AS Difference 
FROM mobile;

When you run the basic SELECT AVG(cost) statement it is naturally grouping by the column specified (cost in this case) as that is what you are requesting. I would suggest reading up more on GROUP BY and aggregates to get a better grasp on the concept. That should help you more than just a simple solution.
UPDATE:
The answer below is actually from David’s answer. It makes use the analytical functions. Basically, what is happening is that on each AVG call, you are telling the engine what to use for the function (in this case, nothing). A decent writeup on analytical functions can be found here and here and more with a google on the matter.

SELECT id, m_name AS "Mobile Name" cost AS Price, AVG(cost) OVER( ) AS Average, 
    cost - AVG(cost) OVER ( ) AS Difference
    FROM mobile

However, if your SQL engine allows for variables, you could just as easily do the below answer. I actually prefer this for future maintainability/readability. The reason is that a variable with a good name can be very descriptive to future readers of the code, versus an analytical function that does require a little bit more work to read (especially if you do not understand the over function).
Also, this solution duplicates the same query twice, so it might be worth storing your average in a SQL variable. Then you ca change your statement to simply use that global average
This is variables in SQL-Server (you will have to adapt it for your own instance of SQL)

DECLARE @my_avg INT;
SELECT @my_avg = AVG(cost) FROM Mobile;

    SELECT id, m_name AS "Mobile Name", cost AS Price,
        @my_avg AS Average, cost-@my_avg AS Difference 
    FROM mobile;

This solution will read a lot cleaner to future readers of your SQL, too

http://stackoverflow.com/questions/9647693/how-to-calculate-average-of-a-column-and-then-include-it-in-a-select-query-in-or