Something important in SQL


1:- Add identity column in table
     ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1) 

2:- count columns from table

SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'upload_file'

3:- delete duplicate records from table
SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)
Or
DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID)FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)
4:  select total table name,rows and columns from database
     SELECT sysobjects.Name , sysindexes.Rows,count(c.column_id)as columns
FROM sysobjects
      INNER JOIN sysindexes
      ON sysobjects.id = sysindexes.id
     INNER JOIN sys.columns c
     ON sysobjects.id = c.object_id
   
    WHERE
    type = 'U'
    AND sysindexes.IndId < 2
   group by sysobjects.Name , sysindexes.Rows
   ORDER BY sysobjects.name
   
5: cursor demo
                declare @id int
      declare @name char(40)
declare @address char(20)    
declare @salary char(30)
     
      declare test_Cursor cursor
      for select * from table1
      open test_Cursor
      fetch test_Cursor into  @id, @name, @address, @salary
      while (@@FETCH_STATUS = 0)
      begin
       print @id    
       fetch test_Cursor into @id, @name, @address, @salary
       end 
      close test_Cursor
      deallocate test_Cursor
      go


6: cursor with Stored procedure

      create procedure Print_Artists_Name
      as
      declare @id int
      declare @name char(40)
declare @address char(20)    
declare @salary char(30)
     
      declare test_Cursor cursor
      for select * from table1
      open test_Cursor
      fetch test_Cursor into  @id, @name, @address, @salary
      while (@@FETCH_STATUS = 0)
      begin
       print @id    
       fetch test_Cursor into @id, @name, @address, @salary
       end 
      close test_Cursor
      deallocate test_Cursor
      go
     
      exec Print_Artists_Name

7: check if Stored procedure exist or not
        if exists (select * from sysobjects where name = ‘procedure_name’)
        begin
       drop procedure procedure_name
        end
        go
        create procedure procedure_name
        as
        . . . . 
        . . . .
        . . . . 
 
8:  running total in sql server
     SELECT a1.Name, a1.salary, sum(a2.salary) Running_Total
FROM table1 a1, table1 a2
WHERE a1.salary <= a2.salary or (a1.salary=a2.salary and a1.Name = a2.Name)
GROUP BY a1.Name, a1.salary
ORDER BY a1.salary DESC, a1.Name DESC;

9: sales % of total sales (Cumulative percent to total)

SELECT a1.Name, a1.salary,sum(a2.salary)/(SELECT SUM(salary) FROM table1) Pct_To_Total
FROM table1 a1, table1 a2
WHERE a1.salary <= a2.salary or (a1.salary=a2.salary and a1.Name = a2.Name)
GROUP BY a1.Name, a1.salary
ORDER BY a1.salary DESC, a1.Name DESC;





10: SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = CASE WHEN MONTH(@myDateTime)
IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0
AND
YEAR(@myDateTime) % 100 != 0)
OR
(YEAR(@myDateTime) % 400 = 0)
THEN 29
ELSE 28 END
END
RETURN @rtDate
END
GO

Exec:-
SELECT dbo.udf_GetNumDaysInMonth(GETDATE()) NumDaysInMonth
GO

11: Is there a function that returns the name of the day, ex. Monday

     SELECT DATENAME(DW,GETDATE()) + ' ' + DATENAME(MM,GETDATE()) + ' ' +DATENAME(YY, GETDATE())

-- RESULT SET AS (Friday September 2010)

12: function that returns the week dayname

          CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
WHEN 1 THEN 'Sunday'
WHEN 2 THEN 'Monday'
WHEN 3 THEN 'Tuesday'
WHEN 4 THEN 'Wednesday'
WHEN 5 THEN 'Thursday'
WHEN 6 THEN 'Friday'
WHEN 7 THEN 'Saturday'
END
RETURN (@rtDayofWeek)
END
GO

-- Call this function like this:
SELECT dbo.udf_DayOfWeek(GETDATE()) AS DayOfWeek
13: how to find age (get age from date of birth sql)
      DECLARE @date datetime, @tmpdate datetime, @years int, @months int, @days int
SELECT @date = '4/12/1987'

SELECT @tmpdate = @date

SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())

SELECT @years, @months, @days

14: How To Delete a null record

The code below will show you how exactly you delete a row with a NULL value. You can not use =NULL but you have to use IS NULL

CREATE TABLE #TestDeleteNull (id INT identity, SomeDate DATETIME)
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(GETDATE())
INSERT #TestDeleteNull VALUES(NULL)


--Check what's in the table
SELECT * FROM #TestDeleteNull

--This won't work
DELETE #TestDeleteNull
WHERE SomeDate = NULL
--(0 row(s) affected)

--This is the way
DELETE #TestDeleteNull
WHERE SomeDate IS NULL
--(1 row(s) affected)

--Check again
SELECT * FROM #TestDeleteNull

15:-How to return random results from SQL SERVER
Sometimes you want to display 4 random articles/pics/you name it on your web page and you don’t want to write a lot of code.
SQL SERVER 2000 has a neat little function called NEWID() that can help you out.

USE pubs

SELECT top 4 * FROM dbo.authors
ORDER BY NEWID()

16: Date formatting in SQL Server
Declare @d datetime
select @d = getdate()

select @d as OriginalDate,convert(varchar,@d,100) as ConvertedDate,
100 as FormatValue,
'mon dd yyyy hh:miAM (or PM)' as OutputFormat
union all
select @d,convert(varchar,@d,101),101,'mm/dd/yy'
union all
select @d,convert(varchar,@d,102),102,'yy.mm.dd'
union all
select @d,convert(varchar,@d,103),103,'dd/mm/yy'
union all
select @d,convert(varchar,@d,104),104,'dd.mm.yy'
union all
select @d,convert(varchar,@d,105),105,'dd-mm-yy'
union all
select @d,convert(varchar,@d,106),106,'dd mon yy'
union all
select @d,convert(varchar,@d,107),107,'Mon dd, yy'
union all
select @d,convert(varchar,@d,108),108,'hh:mm:ss'
union all
select @d,convert(varchar,@d,109),109,'mon dd yyyy hh:mi:ss:mmmAM (or PM)'
union all
select @d,convert(varchar,@d,110),110,'mm-dd-yy'
union all
select @d,convert(varchar,@d,111),111,'yy/mm/dd'
union all
select @d,convert(varchar,@d,112),112,'yymmdd'
union all
select @d,convert(varchar,@d,113),113,'dd mon yyyy hh:mm:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,114),114,'hh:mi:ss:mmm(24h)'
union all
select @d,convert(varchar,@d,120),120,'yyyy-mm-dd hh:mi:ss(24h)'
union all
select @d,convert(varchar,@d,121),121,'yyyy-mm-dd hh:mi:ss.mmm(24h)'
union all
select @d,convert(varchar,@d,126),126,'yyyy-mm-dd Thh:mm:ss:mmm(no spaces)'

17: How To Split column In SQL
Suppose you have a column in a table that has multiple pieces of information in it that
I need to break out into various columns. The column is random but the
values I need to separate out are the number and the UN number as below:

245 HELIUM, COMPRESSED 2.2 UN1046


I need to separate the 2.2 and the UN1046 into different columns. How do I
parse this?

CREATE TABLE Inventory (ItemDescription VARCHAR(99))
INSERT Inventory VALUES ('245 HELIUM, COMPRESSED 2.2 UN1046' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 6.6 UN99' )
INSERT Inventory VALUES ('24adada5 HELIsadasdadUM, sdsdsd 446.6777 UN9988888' )
INSERT Inventory VALUES ('24adada5 HEdUM, sdsdsd 446.0 UN9988' )


SELECT RIGHT(ItemDescription,PATINDEX('% %',
REVERSE(ItemDescription))-1) AS COL1,
LTRIM(REVERSE(LEFT(REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription))))),
PATINDEX('% %',REVERSE(LEFT(ItemDescription,(LEN(ItemDescription)-PATINDEX('% %', REVERSE(ItemDescription)))))))))
AS COL2
FROM Inventory

18: Find all Primary and Foreign Keys In A Database

SELECT tc.TABLE_NAME AS PrimaryKeyTable,
tc.CONSTRAINT_NAME AS PrimaryKey,
COALESCE(rc1.CONSTRAINT_NAME,'N/A') AS ForeignKey ,
COALESCE(tc2.TABLE_NAME,'N/A') AS ForeignKeyTable
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 ON tc.CONSTRAINT_NAME =rc1.UNIQUE_CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 ON tc2.CONSTRAINT_NAME =rc1.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE ='PRIMARY KEY'
ORDER BY tc.TABLE_NAME,tc.CONSTRAINT_NAME,rc1.CONSTRAINT_NAME

19: what are the ypes of locking available in Sybase.
1.  Page Locak
2.  Shared Lock
3.  Exclusive Lock
4.  Table Lock
5.   Intent Lock

20: what are index

Indexs help sql server locate data.they speed up the data retrieval by pointing sql server to the location of table column;’s data on disk.There are two type of index.

Clustered index-clustered index force sqlserver continually sort and re-sort the rows of table so that physical order is always Same as the logical order. you can have only one cluster index per table.

Non_cluster index-Do not require the physical order of rows to be the same as their index order.each non clusterd index can provide access to the data in a different order.you can have 249 nonclusterd index per table
21: Find duplicate rows in a table?
SELECT sid, mark, COUNT(*) AS Counter
FROM marks
GROUP BY sid, mark
HAVING (COUNT(*) > 1)

22: First day of the month
      SELECT DATENAME(dw, DATEADD(dd, - DATEPART(dd, GETDATE()) + 1, GETDATE())) AS FirstDay

23: How To Copy Data and Structure of One Table To Another New Tables
select * into newtable from originaltable where 1=1

24: What is Stored procedure?
A stored procedure is a set of Structured Query Language (SQL) statements that you assign a name to and store in a database in compiled form so that you can share it between a number of programs.

1.  They allow modular programming.
2.  They allow faster execution.
3.  They can reduce network traffic.
4.  They can be used as a security mechanism.

25: How to know how many tables contains empno as a column in a database?
SELECT COUNT(*) AS Counter
FROM syscolumns
WHERE (name = 'empno')

26 : Dif. Between CHARINDEX and PATINDEX
The CHARINDEX and PATINDEX functions return the starting position of a pattern you specify. PATINDEX can use wildcard characters, but CHARINDEX cannot.
                select PATINDEX('%U%','suraj singh')
select CHARINDEX ('U','suraj singh')
      

Comments

Post a Comment

Popular posts from this blog

MOLAP, ROLAP, And HOLAP

In the OLAP world, there are mainly two different types: Multidimensional OLAP (MOLAP) and Relational OLAP (ROLAP). Hybrid OLAP (HOLAP) refers to technologies that combine MOLAP and ROLAP. MOLAP This is the more traditional way of OLAP analysis. In MOLAP, data is stored in a multidimensional cube. The storage is not in the relational database, but in proprietary formats. Advantages : Excellent performance: MOLAP cubes are built for fast data retrieval, and is optimal for slicing and dicing operations. Can perform complex calculations: All calculations have been pre-generated when the cube is created. Hence, complex calculations are not only doable, but they return quickly. Disadvantages : Limited in the amount of data it can handle: Because all calculations are performed when the cube is built, it is not possible to include a large amount of data in the cube itself. This is not to say that the data in the cube cannot be derived from a large amount of data. Indeed, this...
Read more

Difference Between CURRENT_TIMESTAMP and GETDATE()

Image
SQL SERVER – Difference Between CURRENT_TIMESTAMP and GETDATE() – CURRENT_TIMESTAMP Equivalent in SQL Server January 4, 2012 by  s uraj A common question – I often get from Oracle/MySQL Professionals: “What is the Equivalent to CURRENT_TIMESTAMP in SQL Server?” Here is a common question I often get from SQL Server Professionals: “What are differences between Difference Between CURRENT_TIMESTAMP and GETDATE ()?” Very simple question but have showed up so frequently that I feel like to write about it. Well in SQL Server GETDATE() is Equivalent to CURRENT_TIMESTAMP. However, if you use CURRENT_TIMESTAMP in your select statement it will work fine. You can see in the above example – both of them returns the same value. Now let us go to next question regarding difference between GETDATE and CURRENT_TIMESTAMP. Well, the matter of the fact, there is no difference between them in SQL Server ( Reference Link ). CURRENT_TIMESTAMP is an ANS...
Read more

Convert Seconds to HH:MM:SS

Convert Seconds to HH:MM:SS Today 2 of my colleagues from reporting team had this requirement. They had one table where Seconds were stored as INT and in report they wanted to convert the seconds to HH:MM:SS format.  They already had  1  solution ready with them.   It was something like: --------------------------------------------------------------------------------------------------------------- DECLARE  @Seconds  INT SET  @Seconds = 3800 SELECT   CONVERT ( VARCHAR (10),@Seconds/3600)     + ':'     + RIGHT( '00' + CONVERT ( VARCHAR (2),(@Seconds%3600)/60),2)     + ':'     + RIGHT( '00' + CONVERT ( VARCHAR (2),@Seconds%60),2)  AS  [HH:MM:SS]    GO --------------------------------------------------------------------------------------------------------------- -- The output--  01:03:20 It was working fine, but my colleagues were looking for som...
Read more