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

Published by

Mick Genie

Mick Genie is the founder of MickGenie.com and working at ExaBytes Network Sdn Bhd and WPWebHost web host company. He is expertise in Windows and Linux environment especially web hosting related information, tips and trick as well as the IT Information.