Differencebetween SQL, Pl-SQL and T-Sql

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.

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

T-SQL and PL/SQL are two completely different programming languages with different syntax, type system, variable declarations, built-in functions and procedures, and programming capabilities.

The only thing they have in common is direct embedding of SQL statements, and storage and execution inside a database.

(In Oracle Forms, PL/SQL is even used for client-side code, though integration with database-stored PL/SQL is (almost) seemless)

difference between tsql, access sql and pl/sql

TSQL - Transact SQL

PL/SQL - Is SQL for Oracle SQL Server

Access SQL - Is a hacked version of SQL

TSQL and PL/SQL are both based off of SQL ISO/ANSI Standards. It depends on the version of SQL Server(TSQL) or Oracle(PL/SQL) for what ISO/ANSI version they are using. See http://en.wikipedia.org/wiki/SQL for more info.

TSQL and PL/SQL just have extra functionality beyond ISO/ANSI SQL put in by the companies that made them. In general the ISO/ANSI SQL standard deal specifically with how you can Query a database and what structures you can have in the database (e.g. tables, triggers, stored procs, etc). TSQL and PL/SQL are true programming languages in that they can do loops and other things that a programming languages can do. Simply put TSQL and PL/SQL are turing complete and the ISO standards are not.

I am not sure about Access. I think it is just a hacked up version of SQL.

Difference between Script, Stored Procedure, T-SQL, PL/SQL

Here's some rough definitions to explain the differences.

• PL/SQL - a SQL variation specific to Oracle databases.
• T-SQL (Transact-SQL) - a SQL variation specific to Microsoft (and Sybase) databases.
• Stored Procedure - a set of SQL commands that is precompiled and stored on the server for reuse
• Script - a set of SQL commands that is run ad-hoc (not precompiled / not meant for reuse)

There are more differences between Stored Procedures and Scripts (e.g. sprocs can have parameters, etc.) but that's the fundamental difference.

What are the differences between T-SQL, SQL Server and SQL

SQL is the basic ANSI standard for accessing data in a relational database. When you see "MSSQL" it is referring to Microsoft SQL Server, which is the entire database architecture and not a language. T-SQL is the proprietary form of SQL used by Microsoft SQL Server. It includes special functions like cast, convert, date(), etc. that are not part of the ANSI standard.

You will also see things like plSQL, which is Oracle's version of SQL, and there are others as well (mySQL has its own version, for example, and Microsoft Access uses Jet SQL.)

It is important to note the the ANSI standard for SQL has different releases (for example, 92 or 99, representing the year it was released.). Different database engines will advertise themselves as "mostly ANSI-92" compliant or "fully ANSI-99" compliant, etc, and any exceptions will usually be documented.

So although "SQL is SQL", every engine uses its own "flavor" of it, and you do have to do a little reading on the particular platform before you just dive in.

A further note - the SQL extensions, like T-SQL, are generally considered full-fledged programming languages, complete with looping, if/then, case statements, etc. SQL itself is limited to simply querying and updating data and is not considered a true programming language.

Wikipedia has a decent article for an overview here:
http://en.wikipedia.org/wiki/SQL

Advantages in PL/SQL than T-SQL?

They are different because they were written by two different sets of programmers. There is always more than one way to solve a problem. They go beyond the standard SQL because they are looking for something that either isn't in the standard and should have been, is a nice to have feature (that will sell more units) or they want something that will perform faster (performance is critical in Enterprise databases). They are trying to sell their product, so of course they want to make something that is enough different that it will be a selling point.

What is the difference between MSSQL and TSQL?

MS SQL is simply a short version of the (complete) product name Microsoft SQL Server. (Similar to "MS Office", "MS Windows" or "MS Access").

T-SQL is the SQL dialect that the product Microsoft SQL Server is using - and is short for "Transact-SQL" (thanks Aaron for reminding me!)

I wouldn't call the dialect that Microsoft Access is using SQL. It's a query language that somehow resembles SQL

PL/SQL creating a palindrome tester function

You:

• do not need a size in the function's signature;
• have RERURN instead of RETURN
• Do not have types on your variables.
• PALINNDROME is misspelt at the end of the function.

You can fix it like this:

CREATE OR REPLACE FUNCTION PALINDROME(
  WORD VARCHAR2
) RETURN INT
IS
  N PLS_INTEGER := LENGTH(WORD) / 2;
  I PLS_INTEGER := 1;
  J PLS_INTEGER := LENGTH(WORD);
BEGIN
  WHILE I <= N AND SUBSTR(WORD, I, 1) = SUBSTR(WORD, J, 1) LOOP
    I := I + 1;
    J := J - 1;
  END LOOP;
  IF I = N + 1 THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END PALINDROME;
/

Note: You do not need a special case when the word has an odd length as the middle letter is always equal to itself.

But you can simplify it to:

CREATE OR REPLACE FUNCTION PALINDROME(
  WORD VARCHAR2
) RETURN INT
IS
BEGIN
  FOR I IN 1 .. LENGTH(word)/2 LOOP
    IF SUBSTR(WORD, I, 1) <> SUBSTR(WORD, -I, 1) THEN
      RETURN 0;
    END IF;
  END LOOP;

  RETURN 1;
END PALINDROME;
/

db<>fiddle here

---

Related Topics