Wednesday, May 30, 2007

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() = 2007-05-30 23:13:38.763

SELECT DATENAME(year, GETDATE())
SELECT DATENAME(yy, GETDATE())
SELECT DATENAME(yy, GETDATE())
- It will return value = 2007

SELECT DATENAME(quarter, GETDATE())
SELECT DATENAME(qq, GETDATE())
SELECT DATENAME(q, GETDATE())
-It will return value = 2 (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 = May

SELECT DATENAME(dayofyear, GETDATE())
SELECT DATENAME(dy, GETDATE())
SELECT DATENAME(y, GETDATE())
- It will return value = 150 (this is calculate total day from 1 jan 2007 until 30 may 2007)

SELECT DATENAME(day, GETDATE())
SELECT DATENAME(dd, GETDATE())
SELECT DATENAME(d, GETDATE())
- It will return value =
30

SELECT DATENAME(week, GETDATE())
SELECT DATENAME(wk, GETDATE())
SELECT DATENAME(ww, GETDATE())
- It will return value = 23 (this is 23rd week from 1 jan 2007)

SELECT DATENAME(hour, GETDATE())
SELECT DATENAME(hh, GETDATE())
- It will return value = 23 (time for 24 hour)

SELECT DATENAME(minute, GETDATE())
SELECT DATENAME(mi, GETDATE())
SELECT DATENAME(n, GETDATE())
- It will return value = 13 (minute)

SELECT DATENAME(second , GETDATE())
SELECT DATENAME(ss, GETDATE())
SELECT DATENAME(s, GETDATE())
- It will return value = 38 (second)

SELECT DATENAME(millisecond , GETDATE())
SELECT DATENAME(ms, GETDATE())
- It will return value = 763 (milisecond)

SQL Inner Join, Left Join and Right Join

Below is a Example for SQL inner join, SQL Left Join and SQL Right Join.

GuestIDName
1001Nick
1002Jack
1003Apple
Table A

ProductGuestID
Table1002
Chair1003
Almari1002

Table B

SQL inner join
Example for SQL inner join
- Join more than 1 table to get other table data
1. SELECT A.Name, B.Product FROM A INNER JOIN B ON A.GuestID=B.GuestID
Result :


NameProduct
JackTable
AppleChair
JackAlmari

2. SELECT A.Name, B.Product FROM A INNER JOIN B ON A.GuestID=B.GuestID WHERE B.Product = 'tABLE'


Result:
NameProduct
JackTable

Get Number of Row in Select Statement

For SQL Server 2000, you can using below SQL Query or SQL statement to get number of row for a SELECT Statement.

SELECT rank=COUNT(*), A1.countryname, A1.countrycode FROM country A1, country A2 WHERE A1.countryname + A1.countrycode >= A2.countryname + A2.countrycode GROUP BY A1.countryname, A1.countrycode ORDER BY rank


Table (Before using the above SQL Query): SELECT * FROM TABLE



Table (After using the above SQL Query): It add 1 more column to show the number of Row for this table.





For SQL Server 2005, you can using below SQL Query or SQL statement to get number of row for a SELECT Statement.

SELECT rank() OVER (ORDER BY A1.countryname, A1.countrycode) as rank, A1.countryname, A1.countrycode FROM country A1 ORDER BY rank

The result will same as above image.

For more information about SQL Query Syntax or SQL statement Syntax,click here
For more information about Example of SQL Query or SQL statement,click here

Select Statement for Get Number of Days in a Month

Using SQL statement or SQL Query to calculating the Number of Days in a Month

select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) as varchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))

Example on may(month), you just copy and paste the SQL Query or SQL statement (Above blue color) to the SQL Query Analyzer. You will get the answer is 31 days for May(month).

Tuesday, May 29, 2007

SQL Statement Example for SELECT, UPDATE and DELETE

Below is Sql Statement with Example provided. It will help SQL beginner more understanding about the usage of SQL statement. For more information about SQL Syntax, click here.

Name

Income

Company

Position

Angle4500Ajax Tech LimitedManager
Helen6900Programmingschools LimitedCEO
Micheal4000SQLTutorial LimitedDBA
Nick5000Programmingschools LimitedProgrammer
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

namecompany
AngleAjax Tech Limited
HelenProgrammingschools Limited
MichealSQLTutorial Limited
NickProgrammingschools 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'

NameIncomeCompanyPosition
Helen6900Programmingschools LimitedCEO

SELECT * FROM TESTING WHERE NAME='HELEN' AND COMPANY='Programmingschools Limited'
NameIncomeCompanyPosition
Helen6900Programmingschools LimitedCEO

SELECT * FROM TESTING WHERE NAME='HELEN' OR COMPANY='Pragrammingschools Limited'

NameIncomeCompanyPosition
Helen6900Programmingschools LimitedCEO
Nick5000Programmingschools LimitedProgrammer

SELECT * FROM TESTING WHERE COMPANY IN ('Pragrammingschools Limited')

NameIncomeCompanyPosition
Helen6900Programmingschools LimitedCEO
Nick5000Programmingschools LimitedProgrammer

SELECT * FROM TESTING WHERE COMPANY LIKE '%PROGRAMMINGSCHOOLS%'

NameIncomeCompanyPosition
Helen6900Programmingschools LimitedCEO
Nick5000Programmingschools LimitedProgrammer

SELECT * FROM TESTING WHERE ORDER BY COMPANY
NameIncomeCompanyPosition
Angle4500Ajax Tech LimitedManager
Helen6900Programmingschools LimitedCEO
Nick5000Programmingschools LimitedProgrammer
Micheal4000SQLTutorial LimitedDBA

SELECT * FROM TESTING WHERE ORDER BY COMPANY DESC

NameIncomeCompanyPosition
Micheal4000SQLTutorial LimitedDBA
Helen6900Programmingschools LimitedCEO
Nick5000Programmingschools LimitedProgrammer
Angle4500Ajax Tech LimitedManager

SELECT SUM(INCOME),COMPANY FROM TESTING GROUP BY COMPANY

SUM(Income)Company
4500Ajax Tech Limited
11900Programmingschools Limited
4000SQLTutorial Limited

SELECT COUNT(*),COMPANY FROM TESTING GROUP BY COMPANY

COUNT(*)COMPANY
1Ajax Tech Limited
2Programmingschools Limited
1SQLTutorial Limited

SELECT SUM(INCOME),COMPANY FROM TESTING GROUP BY COMPANY HAVING (SUM(INCOME)>5000)

SUM(INCOME)COMPANY
11900Programmingschools Limited

For Sql Update and Sql Delete statement, click here

SQL Statement Example for UPDATE and DELETE

UPDATE Statement
UPDATE statement is used to edit and modify the data in a table. Below showing the example for Update and Delete Statement.



Name

Income

Company

Position

Angle4500Ajax Tech LimitedManager
Helen6900Programmingschools LimitedCEO
Micheal4000SQLTutorial LimitedDBA
Nick5000Programmingschools LimitedProgrammer


Table Name: Testing

SQL Statement With Example

UPDATE TESTING SET POSITION='BOSS' WHERE NAME='HELEN'
- Position Column updated from CEO to BOSS using the update statement with 1 condition name=helen.


NameIncomeCompanyPosition
Angle4500Ajax Tech LimitedManager
Helen6900Programmingschools LimitedBOSS
Micheal4000SQLTutorial LimitedDBA
Nick5000Programmingschools LimitedProgrammer




UPDATE TESTING SET POSITION='Head of Department' WHERE NAME='HELEN' AND COMPANY='Programmingschools Limited'
- Position Column updated from BOSS to Head of Department using the update statement with 2 condition name=helen and company=Programmingschools Limited.


NameIncomeCompanyPosition
Angle4500Ajax Tech LimitedManager
Helen6900Programmingschools LimitedHead of Department
Micheal4000SQLTutorial LimitedDBA
Nick5000Programmingschools LimitedProgrammer



DELETE Statement
DELETE FROM TESTING WHERE NAME='Angle'
-Delete row using delete statement with condition name=angle.


NameIncomeCompanyPosition
Helen6900Programmingschools LimitedHead of Department
Micheal4000SQLTutorial LimitedDBA
Nick5000Programmingschools LimitedProgrammer




DELETE FROM TESTING
-Whole table deleted


For more information for SELECT Statement, click here



Tuesday, May 22, 2007

SQL Syntax - SELECT,UPDATE and DELETE

Below is the Lists of possible syntax for SELECT,UPDATE and DELETE

SELECT Syntax - Get Information from a table in database
SELECT ColumnName1,ColumnName2, ColumnName3 FROM TableName
SELECT DISTINCT ColumnName FROM TableName

SELECT COUNT (ColumnName) FROM TableName
SELECT ColumnName FROM TableName WHERE ColumnName = value
SELECT ColumnName FROM TableName WHERE ColumnName1 = value AND ColumnName2 = value

SELECT ColumnName FROM TableName WHERE ColumnName1 = value OR ColumnName2 = value
SELECT ColumnName FROM TableName WHERE ColumnName IN (Value1,Value2)
SELECT ColumnName FROM TableName WHERE ColumnName BETWEEN (Value1 AND Value2)

SELECT ColumnName FROM TableName WHERE ColumnName Like '%Value1%'
SELECT ColumnName FROM TableName ORDER BY ColumnName
SELECT ColumnName1 , SUM(ColumnName2 ) FROM TableName GROUP BY ColumnName1 HAVING (Arithematic Condition)


UPDATE Syntax
UPDATE TableName SET ColumnName = Value WHERE {Condition}
UPDATE TableName SET ColumnName = Value WHERE ColumnName = value

DELETE Syntax
DELETE TableName WHERE {Condition}
DELETE TableName WHERE ColumnName = value

What is SQL?

SQL (Structured Query Language) is a computer language used to create, retrieve, update and delete data from relational database management systems. SQL works with database programs like MS SQL Server, MS Access, MSDE, MySQL, DB2, Informix, MS SQL Server, Oracle, Sybase, etc.

SQL Data Manipulation Language (DML)
DML is a syntax for executing queries and DML component of SQL comprises have four basic statements:
SELECT - Retrieve rows from tables
UPDATE - Modify the rows of tables
DELETE - Remove rows from tables
INSERT - Add new rows to tables
.

SQL Data Definition Language (DDL)
DDL is used to create and destroy databases, database table and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. DML component of SQL comprises have four basic statements:
CREATE - Creates a new database table
ALTER - Alters / changes a database table
DROP - delete a database table