SQL: Select

Assume that we have a table named MyTable and it contain 4 column named ID, Name, Date and City.

Today, I will show some SQL Select command that might useful to SQL Administrator daily task.

1) SELECT * FROM MyTable – Returns all columns/Rows from MyTable
2) SELECT * FROM MyTable WHERE ID=2 – Returns the row(s) which ID has value 2
3) SELECT * FROM MyTable WHERE ID BETWEEN 10 and 20 – Return all row(s) between 10 and 20, this result includes 10 and 20
4) SELECT * FROM MyTable WHERE City IN (‘KL’,’Penang’,’Johore’) – Returns all row(s) which city is KL, Penang, Johore
5) SELECT * FROM MyTable WHERE Name LIKE ‘A%’ – Return all row(s) where the name starts letter A
6) SELECT * FROM MyTable WHERE Name LIKE ‘%A’ – Return all row(s) where the name ends letter A
7) SELECT * FROM MyTable WHERE Name LIKE ‘[ABC]%’ – Return all row(s) of name start with A / B / C
8) SELECT * FROM MyTable WHERE Name LIKE ‘[^ABC]%’ – Return all row(s) of name not start with A and B and C
9) SELECT (Name+space(1)+City) AS Address FROM MyTable – Returns single column address, name and city added together with a space
10) SELECT * FROM MyTable WHERE Name IS NULL – Return all row(s) which Name has null values
11) SELECT * FROM MyTable WHERE Name IS NOT NULL – Return all row(s) which Name has not null values
12) SELECT * FROM MyTable Order By ID Desc – Sort the result set descending order, Asc or not using any sort Ascending order
13) SELECT ‘Visual Studio’ as IDE, ‘2010’ as Version – Creating memory resident result set with two columns[IDE and Version]
14) SELECT Distinct ID FROM MyTable – Returns unique rows based on ID
15) SELECT TOP 10 * FROM MyTable – Return 10 customers randomly
16) SELECT getdate() – Shows the current date
17) SELECT db_name() – shows the database name which you are working on
18) SELECT @@Servername – Shows name of the server
19) SELECT serverproperty (‘Edition’) – You can pass following ServerName, Edition, EngineEdition, ProductLevel to get current information about the server
20) SELECT user_name() – Get current user
21)  SELECT *  into #test FROM MyTable  – Create temporary table #test and insert all records FROM MyTable
22)  SELECT Max(ID) FROM MyTable – Returns Maximum ID FROM MyTable
23)  SELECT * FROM MyTable Compute Max(ID) – Returns two result sets – getting all rows and maximum value of ID