How to get primary key column in Oracle?

Execute following command :

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM ALL_CONSTRAINTS cons, ALL_CONS_COLUMNS cols
WHERE cols.table_name = 'TABLE_NAME'
AND cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;


:):)  Enjoy

Difference between inner join and outer join

Assuming you're joining on columns with no duplicates, which is by far the most common case:

• An inner join of A and B gives the result of A intersect B, i.e. the inner part of a venn diagram intersection.
• An outer join of A and B gives the results of A union B, i.e. the outer parts of a venn diagram union.

Examples
Suppose you have two Tables, with a single column each, and data as follows:

A    B
-    -1    32    43    54    6

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.
Inner join
An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;select a.*,b.*  from a,b where a.a = b.b;

a | b
--+--3 | 34 | 4

Left outer join
A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;select a.*,b.*  from a,b where a.a = b.b(+);

a |  b  
--+-----1 | null2 | null3 |    34 |    4

Full outer join
A full outer join will give you the union of A and B, i.e. All the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b  
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4null |    6null |    5

Removing Leading Zeros From Column in Table

Improving the ResultSet

I had missed including all zeros in my sample set which was an overlook. Here is the new sample which includes all zero values as well.

USE tempdb
GO-- Create sample tableCREATE TABLE Table1 (Col1 VARCHAR(100))INSERT INTO Table1 (Col1)SELECT '0001'UNION ALLSELECT '000100'UNION ALLSELECT '100100'UNION ALLSELECT '000 0001'UNION ALLSELECT '00.001'UNION ALLSELECT '01.001'UNION ALLSELECT '0000'

Result :

SELECTCASE PATINDEX('%[^0 ]%', Col1 + ' ‘')WHEN 0 THEN ''ELSE SUBSTRING(Col1, PATINDEX('%[^0 ]%', Col1 + ' '), LEN(Col1))END
FROM Table1

Joins in Sql Server

Joins in SQL SERVER 2008 R2 :

Query :


SELECT
a.[d_no],
a.[l_no],
a.[m_cd],
b.[m_desc],
a.[p_cd]
FROM [Test].[db].[TABLE1] a left outer join [Test].[db].[table2] b
on a.[m_cd]= b.[m_cd]
where <Your conditions>

INNER JOIN

This join returns rows when there is at least one match in both the tables.

OUTER JOIN

There are three different Outer Join methods.

LEFT OUTER JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.

RIGHT OUTER JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.

FULL OUTER JOIN
This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.

CROSS JOIN

This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

 

How to get top " n " number of rows from table

Suppose you want top n values from table , so execute following query
with replacement of following item as mention below :

1 )  Replace your our tablename with table_name]

2 )  Replace your "n" with row numbers  (how many rows you want )

[

Note :  You can do multiply and division operations also

]

Query for Top rows :

SELECT

MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 1 + 2 ) ORDER BY Row_Number;


Query for Bottom  rows :

SELECT MainSQL.* FROM ( SELECT t.*,ROW_NUMBER() OVER ( ORDER BY 1 ) Row_Number,
COUNT(1) OVER ( ORDER BY 1 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) Row_Count
FROM (SELECT * FROM [table_name]) t ORDER BY 1 ) MainSQL WHERE Row_Number >= 1
AND Row_Number < ( 10 - 2 ) ORDER BY Row_Number;

WHAT IS - > " SELECT * FROM [TABLE_NAME] WHERE 1=1 " ??

What is

select * from xxx where 1=0 what does this mean?
also there is one
select * from yyy where 1=1

??
??

ANSWER ->


1. Select * from table where 1=0
return just header of the fields (attribute)
but with 0 rows

2. Select * from table where 1=1
return whole table entries
this is same as select * from table


Simple ans.

Logical ?

If the list of conditions is not known at compile time and is instead built at run time, you don't have to worry about whether you have one or more than one condition. You can generate them all like:

and <condition> 

and concatenate them all together. With the 1=1 at the start, the initial and has something to associate with.
I've never seen this used for any kind of injection protection, as you say it doesn't seem like it would help much. I have seen it used as an implementation convenience. The SQL query engine will end up ignoring the 1=1 so it should have no performance impact.


 

" DUAL " IN SQL SERVER / ORACLE

What is DUAL in Oracle?

Dual is a table that is created by Oracle together with data dictionary. It consists of exactly one column named “dummy”, and one record. The value of that record is X.

You can check the content of the DUAL table using the following syntax.

SELECT * FROM dual

It will return only one record with the value ‘X’.

What is the reason for following error in SQL Server?

Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dual’.

The reason behind the error shown above is your attempt to SELECT values from DUAL table in SQL Server. This table does not exist in SQL Server. Continue reading for workaround.

What is the Equivalent of DUAL in SQL Server to get current datetime?

Oracle:

select sysdate from dual

SQL Server:

SELECT GETDATE()

What is the equivalent of DUAL in SQL Server?

None. There is no need of Dual table in SQL Server at all.

Oracle:

select ‘something’ from dual

SQL Server:

SELECT ‘something’

I have to have DUAL table in SQL Server, what is the workaround?

If you have transferred your code from Oracle and you do not want to remove DUAL yet, you can create a DUAL table yourself in the SQL Server and use it.

Here is a quick script to do the said procedure.

CREATE TABLE DUAL(DUMMY VARCHAR(1)
)GOINSERT INTO DUAL (DUMMY)VALUES ('X')GO

After creating the DUAL table above just like in Oracle, you can now use DUAL table in SQL Server.