1.Difference between SQL Server and PostgreSQL

S.No SQL Server PostgreSQL

1 INSERT t VALUES (…) This syntax is not allowed. Allows: INSERT INTO t VALUES (…)

2 BULK INSERT and BCP uses COPY instead (which has the functionality of both BCP and BULK INSERT)

3 Management Studio pgAdmin

4 Bit type Boolean type (accepts values true and false)

5 IDENITTY Has sequencers (like Oracle)

6 default schema is dbo default schema is PostgreSQL

7 Default Listening on 1433 Default listening on 5432

8 datatype: varchar(max) datatype: text

9 Key is clustered by default key is not clustered by default (and it is enforced by a constraint and not an an index!)

10 User Defined Data Types Domains

11 user: sa user: postgres

12 No such thing NATURAL and USING joins

13 SELECT TOP 10 * FROM t SELECT * FROM t LIMIT 10

14 Query plans read from right to left Query plan read from left to right

15 Estimate Query Plan: CTRL+L Estimate Query Plan: F7

2.Difference between Cross Join and Full Outer Join

S.No Cross Join Full Outer Join

1 No join conditions are specified. A combination of both left and right outer joins.

2 Results in pairs of rows. Results in every row from both of the tables , at least once.

3 Results in Cartesian product of two tables.

Assigns NULL for unmatched fields.

3.Difference between SQL Server and Oracle

S.No SQL Server Oracle

1 SQL History:

IBM introduced structured Query Language (SQL) as the language to interface with its prototype relational database management system; System R. Oracle Corporation introduced the first commercially available SQL relational database management system in 1979. Today, SQL has become an industry standard, and Oracle Corporation clearly leads the world in RDBMS technology. SQL is used for all types of DB activities by all type of users. The basic SQL commands can be learned in a few hours and even the most advanced commands can be mastered in a few days.

Oracle History:

Oracle Corp is the leading supplier for S/w products, headquartered in Redwood shores, California, USA. It was founded by Larry Ellison, Bob Miner and Ed Oates in 1977. Now they have 43,000 Employees in 150 countries. Oracle first commercial RDBMS was built in 1979, and it is the first to support the SQL. Oracle is the first S/w company to develop and deploy 100 % Internet-enabled enterprise Software.

2 SQL (Structure Query Language):

When a user wants to get some information from any DB file, he can issue a query. Structured query language (SQL), pronounced “Sequel”, is the set of commands that all programs and users must use to access data within the Oracle. SQL is a high performance fault tolerant data base management system. The database is mostly maintained by SQL language, which is conceded as the heart of the RDBMS.

Oracle (RDBMS):

Oracle is fastest and easiest way to create applications in MS windows. It provides the ability to store and access data. Whether you are experienced or new to windows in programming, Oracle provides you with the complete set of tools to simplify rapid application development. The Oracle refers to the method used to create the graphical user inter face. There is no need to write numerous lines of code to describe the appearance and location of inter face elements.

3 SQL Technology:

SQL is divided into four parts:

DDL (Data Definition Language): Create, Alter, Drop, Rename, Truncate.

Oracle Technology:

Oracle DB structure is divided into two parts, one is called Physical structure (these files define the operating system that make up the DB, each Oracle DB is made by three types

DML (Data Manipulate Language): Select, Update and Delete, Insert, Into.

DCL (Data Control Language): Grant, Revoke

TCL (Transaction Control Language): Commit, Rollback.

of files, data-files, redo logs file-controls file) and the other is called Logical structure (these files define the logical areas of storage like schema, table spaces, segments and extents).

4 Advantages:

• Provides easy access to all data.

• Flexibility in data molding.

• Reduced data storage and redundancy.

• Provides a high-level manipulation language.

• SQL can save data in common PC file formats that can be imported into other application (like Ms-Excel).

• SQL is not case sensitive. • It can enter one or more

lines. • Tabs and indents can be

used to make code more readable.

• Can be used by a range of users.

• It is a nonprocedural language (English-like language).

Advantages:

• Data consistency • Integration of data • Easy file generation • Increased security • Easy updating of records • No wastage of time • Enforcement of standards • Controlled data redundancy • Reduce the total expenditures • Searching of particular data is

easy • Dispose of heavy files and

register work • The work of three persons is

reduced to one • Instant intimation of

modification of information

5 Differences:

• SQL is a tool for all DB like DBMS, RDBMS, T-SQL, and SQL Plus.

• SQL maintains different RDBMS.

• SQL is combination of different commands and functions that why, SQL is worked for Oracle DB as a

Differences:

• Oracle Corp is the world’s leading supplier of S/w products.

• Oracle is the platform, where we develop and implement different DB designs and software.

• Oracle is the combination of different S/w products, where

command prompt shell (SQL is the command prompt shell, where we can communicate with any DB).

they work together for designing DB.

• Oracle works with different front and back end products/tools (like SQL).

4.Difference between View and Stored Procedure

S.No View Stored Procedure

1 Does not accepts parameters Accept parameters

2 Can be used as a building block in large query.

Cannot be used as a building block in large query.

3 Can contain only one single Select query.

Can contain several statement like if, else, loop etc.

4 Cannot perform modification to any table.

Can perform modification to one or several tables.

5 Can be used (sometimes) as the target for Insert, update, delete queries.

Cannot be used as the target for Insert, update, delete queries.

5.Difference between IN and EXISTS

S.No IN EXISTS

1 Returns true if specified value matches any value in the sub query or a list.

Return true if sub query contain any rows.

2 The sub query will run first and then only outer query.

The Outer query will ran first and then only sub query.

3 IN is slower than EXISTS. The IN is used in the widely For Static variables for eg: select name from table where ID in (Select ID from table2).

Exists is faster than IN.The Outer query will run first and then only inner query.So it will reduce the over head. The Exists is useful mostly in IF conditional statements.

4 Example:

SELECT id,[Name]FROM dbo.tableaWHERE id IN (SELECT id

Example:

SELECT id,[Name]FROM dbo.tablea AS aWHERE EXISTS (SELECT id2

FROM dbo.tableb) FROM dbo.tablebWHERE id2 = a.id)

Please visit my blog @ http://onlydifferencefaqs.blogspot.in/