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.
S.No SQL Server Oracle 1SQL 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. 2SQL (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. 3SQL Technology:SQL is divided into four parts:DDL (Data Definition Language): Create, Alter, Drop, Rename, Truncate.DML (Data Manipulate Language): Select, Update and Delete, Insert, Into.DCL (Data Control Language): Grant, RevokeTCL (Transaction Control Language): Commit, Rollback.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 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). 4Advantages:
- 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).
- 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
- 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 command prompt shell (SQL is the command prompt shell, where we can communicate with any DB).
- 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 they work together for designing DB.
- Oracle works with different front and back end products/tools (like SQL).
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.
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. 4Example:
WHERE id IN (SELECT id
FROM dbo.tablea AS a
WHERE EXISTS (SELECT id2
WHERE id2 = a.id)