Difference Between Different Types of SQL

Difference between different types of SQL?

SQL is Structured Query Language is a
database computer language designed
for managing data in relational
database management systems (RDBMS).

PostgreSQL is an object-relational
database management system
(ORDBMS).1 It is released under a
BSD-style license and is thus free
software. As with many other
open-source programs, PostgreSQL is
not controlled by any single company,
but has a global community of
developers and companies to develop
it.

SQLite is an ACID-compliant embedded
relational database management system
contained in a relatively small (~225
KB1) C programming library. The
source code for SQLite is in the
public domain.

MySQL (pronounced /maɪˌɛskjuːˈɛl/1
My S-Q-L, or "My sequel"
/maɪˈsiːkwəl/) is a relational
database management system (RDBMS)2
which has more than 6 million
installations. 3 MySQL stands for
"My Structured Query Language". The
program runs as a server providing
multi-user access to a number of
databases.

Full list of differences between different versions of SQL (MySQL, SQL Server, etc?)

On a coder level, you generally want to be aware of ANSI sql and track how the databases differ from that.

Regular select/insert/delete/update commands are fairly portable.
Generally speaking, things that will be different are non-core-syntax aspects like:

• functions. any getXXX(), rtrim(xxx) are, more often that not, vendor specific. Anyway, functions tend to mess up index use as well, so best used sparingly.
• update/from, delete/from. Those are, I believe, vendor-specific.
• dates. are surprisingly non-portable, with everyone and his dog having a different way to specify date masks for example.
• procedural/trigger extensions. Any procedural language is likely to have a lot of scope for vendor-specificity.

• case sensitivity. some databases are all UPPERCASE, some all lowercase, some are insensitive.

• any "extra stuff" in a query that isn't a column. Things like LIMIT, rownum, rowid are generally vendor-specific.

• old outer join syntax, like 'e.department_id = d.department_id(+)'

• "complicated queries". Let's say you want to flush out order lines for orders that have been entirely fullfilled.

  delete from order_lines where not exists
  (select 1 from order_lines s
  where s.order_no = order_lines.order_no
  and s.status <> 'fullfilled')

  DB2, in the past at least, would have likely complained about a "correlated subquery", which basically boils down to referencing a table which you are in the process of modifying, order_lines in this case.

Basically, you should kinda be OK if you stick to older ANSI SQL. I worked on PeopleSoft for years and it was deeply drilled into us to write multi-db sql (DB2/Oracle/MSSQL, etc...), without resorting to writing vendor-specific code (i.e. 1 query for Oracle, 1 for MSSQL...). It can be done, just requires a suspicious mindset about anything that looks like vendor shortcuts & sql enhancements, regardless of how attractive they are.

For specific queries, head off to sqlfiddle.com and you can test a number of cases. And, unfortunately, I don't know of any books that aim to teach actual, as opposed to proposed, ANSI SQL. And, even more unfortunately, the worthies @ ANSI SQL standards have not seen fit to make their standard freely available.

At an admin level, such as all the DDL for create table/index/..., there are way more differences to be aware of, starting with datatypes.

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

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 single and double quotes in SQL?

Single quotes are used to indicate the beginning and end of a string in SQL. Double quotes generally aren't used in SQL, but that can vary from database to database.

Stick to using single quotes.

That's the primary use anyway. You can use single quotes for a column alias — where you want the column name you reference in your application code to be something other than what the column is actually called in the database. For example: PRODUCT.id would be more readable as product_id, so you use either of the following:

• SELECT PRODUCT.id AS product_id
• SELECT PRODUCT.id 'product_id'

Either works in Oracle, SQL Server, MySQL… but I know some have said that the TOAD IDE seems to give some grief when using the single quotes approach.

You do have to use single quotes when the column alias includes a space character, e.g., product id, but it's not recommended practice for a column alias to be more than one word.

difference between money & numeric data types?

Never use MONEY; 1) It is proprietary, so porting it is a pain. It is one of the many "Sybase Code Museum" features from decades ago. Remember the early versions of UNIX?

2) Writing code in dialect when you don't need to make you sound like a hillbilly to people that speak the language. You are better off with DECIMAL(s,p) so you can use a properly sized column.

3) It does display and formatting in the back end, with commas and dollar signs. That defeats the purpose of a tiered architecture.

4) The MONEY data type has rounding errors.

Using more than one operation (multiplication or division) on money
columns will produce severe rounding errors. A simple way to visualize
money arithmetic is to place a ROUND() function calls after every
operation. For example,

Amount = (Portion / total_amt) * gross_amt
can be rewritten using money arithmetic as:
Amount = ROUND(ROUND(Portion/total_amt, 4) * gross_amt, 4)

Rounding to four decimal places might not seem an issue, until the
numbers you are using are greater than 10,000.

check here https://social.msdn.microsoft.com/Forums/sqlserver/en-US/de0e5cfe-b984-4700-b81f-a0478a65daf1/difference-between-numeric-and-money-data-type-in-sql-server?forum=transactsql

---

Related Topics