Skip to main content

Posts

Showing posts from 2011

Unicode-Why do some SQL strings have an 'N' prefix?

 You may have seen Transact-SQL code that passes strings around using an N prefix. This denotes that the subsequent string is in Unicode (the N actually stands for National language character set). Which means that you are passing an NCHAR, NVARCHAR or NTEXT value, as opposed to CHAR, VARCHAR or TEXT. Unicode is typically used in database applications which are designed to facilitate code pages which extend beyond the English and Western Europe code pages. Example: Chinese.

Trigger command

Trigger command is used to show notification if something is altered in the table like the table could be updated or deleted etc As an example to show how the trigger command works, I made a table and here is the query-- SET NOCOUNT ON /*helps to turn on to notify us  whenever there is update or insert operations done on our table*/ CREATE TABLE Source (Sou_ID int IDENTITY, Sou_Desc varchar(10)) go CREATE TRIGGER tr_Source_INSERT ON Source FOR INSERT AS PRINT GETDATE() go    INSERT Source (Sou_Desc) VALUES ('Test 1')   output::: --------------------- today's date and time

declaring a Variable and using in example

This example shows how to declare a variable and as an example, i have written a code of printing multiples of 2 upto 10 values declare @multi2 integer declare @ans integer set @multi2=0 set @ans=1 while @multi2<11 begin set @ans= @multi2 *2; print '2 *'+cast(@multi2 as varchar)+'='+cast(@ans as varchar) set @multi2= @multi2+1 end

To select last 'N' records in a table

This query will fetch you last N rows from the table-- select * from emp where empno not in (select top ( (select count(*) from emp) - 'N') empno from emp ) or  If you are using ORACLE SQL then the query for the same question is -- select * from ( select rownum as rank,empno,sal from emp ) where rank>((select max(rownum) from emp)-'N');

SQL DATENAME Function - Year, Quarter, Month, Day, Hour, Minute and Milisecond.

It return specifies the part of the date name and the DATENAME for Date Time such as Year, Quarter, Month, Day, Hour, Minute and Milisecond. SQL DATENAME Syntax DATENAME ( datepart , date ) Example SELECT GETDATE() = 2011-11-29 15:33:17.153 SELECT DATENAME(year, GETDATE()) SELECT DATENAME(yy, GETDATE()) SELECT DATENAME(yy, GETDATE()) - It will return value = 2011 SELECT DATENAME(quarter, GETDATE()) SELECT DATENAME(qq, GETDATE()) SELECT DATENAME(q, GETDATE()) -It will return value = 4 (because 1 quarter equal to 3 month,Detail see below table) Month Quarter Value January - March 1 April - June 2 July - September 3 October - December 4 SELECT DATENAME(month, GETDATE()) SELECT DATENAME(mm, GETDATE()) SELECT DATENAME(m, GETDATE()) - It will return value = November SELECT DATENAME(dayofyear, GETDATE()) SELECT DATENAME(dy, GETDATE()) SELECT DATENAME(y, GETDATE()) - It will return value = 333 (this is calculate total day from 1 jan 2007 until 28 nov 2011) SELECT DATENAME(day, GETDATE()) SEL

SQL Statement Example for SELECT, UPDATE and DELETE

Name Income Company Position Angle 4500 Ajax Tech Limited Manager Helen 6900 Programmingschools Limited CEO Micheal 4000 SQLTutorial Limited DBA Nick 5000 Programmingschools Limited Programmer Table Name : Testing SQL STATEMENT - Example Result with SQL Statement Select All data from a table (same as above table)- SELECT * FROM TESTING SELECT NAME,COMPANY FROM TESTING name company Angle Ajax Tech Limited Helen Programmingschools Limited Micheal SQLTutorial Limited Nick Programmingschools Limited SELECT DISTINCT COMPANY FROM TESTING company Ajax Tech Limited Programmingschools Limited SQLTutorial Limited SELECT COUNT(*) FROM TESTING 4 SELECT * FROM TESTING WHERE NAME='HELEN' Name Income Company Position Helen 6900 Programmingschools Limited CEO SELECT * FROM TESTING WHERE NAME='HELEN' AND COMPANY='Programmingschools Limited' Name Income Company Position Helen 6900 Programmingschools Limited CEO SELECT * FROM TESTING WHERE NAME='HELEN' OR COMPANY='P

SQL CAST and CONVERT

It converts an expression from one data type to another. CAST and CONVERT have similar functionality. SQL CAST and CONVERT Syntax Using CAST: CAST ( expression AS data_type ) Using CONVERT: CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) Example of SQL Cast and Convert SQL Cast and Convert - String SELECT SUBSTRING('CAST and CONVERT', 1, 3) Return Value = CAS (it get from index 1 to 3) SELECT CAST('CAST and CONVERT' AS char(3)) Return Value = CAS (it get 3 char only) SQL Cast and Convert - Date Time -Converting date time to character data(vachar) -The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the Without century year(yy). -Add 100 to a style value to get a four-place year that includes the century year(yyyy). -Below is example for converting 1 format of date time to different format of date time, so that it can be use in various condition. Value of current Date Time GETDATE() SELECT (GETDATE()) = 2007-06

MODULO function in SQL Server

SELECT EmployeeID , LastName , EmployeeID % 2 FROM Employees WHERE EmployeeID % 2 = '0' Also we can use Like Statement to achieve this :: Select * from Employees where ID Like '%[02468]' --for even number of records Select * from Employees where ID Not Like '%[02468]' --for Odd number of records