ROW VALUE CONCATE STUFF USING XML PATH , SUBSTRING USING XML PATH , COALESCE

 

-- #1 using stuff function and xml path
SELECT STUFF(
    (SELECT ',' + product_category_name
    FROM dbo.product_category
    FOR XML PATH ('')),1,1,'')

-- #2 using substring function and xml path
SELECT SUBSTRING(
    (SELECT ',' + product_category_name
    FROM dbo.product_category
    FOR XML PATH ('')),2,10000)

-- #4 using coalesce when there is null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + COALESCE(product_category_name,'')
FROM dbo.product_category
SELECT @concat_string;
GO

-- #4 using coalesce when there is not any null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + product_category_name
FROM dbo.product_category 
WHERE product_category_name IS NOT NULL
SELECT @concat_string;
GO

Local and global temporary tables in SQL Server

There are two types of temporary tables:
 local and global. 
Local temporary tables 
            are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced.
Local temporary tables are deleted after the user disconnects from the instance of SQL Server. 

Global temporary tables 
           are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

• Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.
• Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.
• Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.
• Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted.

how to get nth hightst salary/student query

Here we will present one possible answer to finding the nth highest salary first, and the explanation of that answer after since it’s actually easier to understand that way. Note that the first answer we present is actually not optimal from a performance standpoint since it uses a subquery, but we think that it will be interesting for you to learn about because you might just learn something new about SQL. If you want to see the more optimal solutions first, you can skip down to the sections that says “Find the nth highest salary without a subquery” instead.
The SQL below will give you the correct answer – but you will have to plug in an actual value for N of course. This SQL to find the Nth highest salary should work in SQL Server, MySQL, DB2, Oracle, Teradata, and almost any other RDBMS:

SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

DECLARE @tbl table(sid int,mark int)
insert into @tbl values(1,234)
insert into @tbl values(2,134)
insert into @tbl values(3,334)
insert into @tbl values(1,534)
insert into @tbl values(2,634)
insert into @tbl values(3,634)
insert into @tbl values(10,834) -- ??

SELECT mark,max(mark) FROM @tbl group by mark order by mark desc--having max(mark)

SELECT * FROM @tbl WHERE mark=(SELECT top 1 mark FROM @tbl WHERE mark<>(SELECT top 1 mark FROM @tbl order by mark desc) ORDER BY mark desc)

SELECT * /*This is the outer query part */
FROM @tbl s1
WHERE (3-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(s2.mark))
FROM @tbl s2
WHERE s2.mark > s1.mark)

DELETE Duplicate Records Using CTE

Syntax
With cte_duplicate (name, age, salary, rownumber)
as (
select name,age,salary, row_number()over(partition by name, age , salary order by name, age , salary)as rank from tbl_dublicatedata
)
delete from cte_duplicate where rownumber<>1  

Example :

BEGIN
   --Declare Statemant
    DECLARE @tblduplicatedata TABLE(name VARCHAR(50),age INT,salary INT);

    --Insert Statements
    INSERT INTO @tblduplicatedata(name,age,salary)VALUES('Sayali',12,45000);
    INSERT INTO @tblduplicatedata(name,age,salary)VALUES('Geet',15,85000);
    INSERT INTO @tblduplicatedata(name,age,salary)VALUES('Sayali',12,45000);
    
    WITH cte_duplicate (name, age, salary,rownumber)
        AS (
        SELECT name,age,salary, row_number()OVER(PARTITION BY name, age , salary ORDER BY name, age , salary)AS rank FROM @tblduplicatedata
        )
        DELETE FROM cte_duplicate WHERE rownumber<>1  

        SELECT * FROM @tblduplicatedata
END
 

how to know collation of a/all tables

List all table and collation name list 
 
SELECT col.name, col.collation_name,object_NAME(object_id),*
FROM sys.columns col WHERE collation_name='SQL_Latin1_General_CP1_CI_AS'


SELECT col.name, col.collation_name,object_NAME(object_id),*
FROM sys.columns col WHERE collation_name='Latin1_General_CI_AI'

Get collation name of a particular table
SELECT col.name, col.collation_name,object_NAME(object_id),*
FROM sys.columns col WHERE  object_id = OBJECT_ID('table_name')

How to get all table names in a database in sql server

SELECT db_name(), name,* FROM sys.tables;