{"id":2318,"date":"2014-02-08T03:15:43","date_gmt":"2014-02-07T21:45:43","guid":{"rendered":"http:\/\/sqlhints.com\/?p=2318"},"modified":"2014-02-08T03:15:43","modified_gmt":"2014-02-07T21:45:43","slug":"querying-data-using-select-in-sql-server","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2014\/02\/08\/querying-data-using-select-in-sql-server\/","title":{"rendered":"Querying Data Using SELECT In Sql Server"},"content":{"rendered":"

Sql Server Tutorial<\/a>\u00a0Lesson 5: Querying Data Using SELECT<\/h2>\n

SELECT is one of the basic construct of Sql Server, which basically facilitates the retrieval of information from Tables. This lesson covers the following Topics with extensive list of real-time examples.<\/p>\n

    \n
  1. Introduction to SELECT<\/strong> Statement<\/li>\n
  2. Using WHERE<\/strong> Clause<\/li>\n
  3. Using Boolean Operators AND<\/strong>, OR<\/strong> and NOT<\/strong><\/li>\n
  4. Using LIKE<\/strong> Predicate<\/li>\n
  5. Using BETWEEN<\/strong> Clause<\/li>\n
  6. Table and Column Name Alias<\/strong><\/li>\n
  7. Using ORDER BY<\/strong> Clause<\/li>\n
  8. Concatenation<\/strong><\/li>\n<\/ol>\n

    To demo these features let us first create the Employee table with seven employee records as depicted in the below image by using the following script:<\/p>\n

    \"QueryingDataUsingSELECT\"<\/a><\/p>\n

    CREATE DATABASE SqlHintsQueryingDataDemo\r\nGO\r\nUSE SqlHintsQueryingDataDemo\r\nGO\r\nCREATE TABLE dbo.Employee\r\n(EmployeeId INT, Name NVARCHAR(50),\r\nDOJ DateTime,City NVarchar(50), Salary Money)\r\nGO\r\nINSERT INTO dbo.Employee VALUES(1,'ShreeGanesh Biradar','2011\/12\/18','Pune',45000)\r\nINSERT INTO dbo.Employee VALUES(2,'Sandeep Patil','2010\/02\/24',NULL,55000)\r\nINSERT INTO dbo.Employee VALUES(3,'Abhi Akkanna','2008\/03\/22','Bangalore',89000)\r\nINSERT INTO dbo.Employee VALUES(4,'Sandy Thomas','2008\/04\/28','Delhi',39000)\r\nINSERT INTO dbo.Employee VALUES(5,'Kalpana Biradar','2013\/11\/15','Bangalore',60000)\r\nINSERT INTO dbo.Employee VALUES(6,'Basav Biradar','2012\/11\/15','Bangalore',54000)\r\nINSERT INTO dbo.Employee VALUES(7,'Deepak Kumar','2006\/04\/08','Hyderabad',75000)<\/pre>\n
    1. Introduction to SELECT Statement<\/h2>\n
    Demo 1: Retrieve All the Records from the Employee Table<\/strong><\/p>\n
    SELECT * FROM dbo.Employee<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"QueryingDataUsingSELECTDemo1\"<\/a><\/p>\n
    Demo 2: Retrieve only the Required Information<\/strong><\/p>\n
    In most of the scenario’s we don’t need the complete table data, so it is always best practice to include the columns which are required in the Select query to reduce unnecessary data transfer over the Network and IO’s.<\/p>\n
    SELECT EmployeeId,Name,City FROM dbo.Employee<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"QueryingDataUsingSELECTDemo2\"<\/a><\/p>\n
    2. Using WHERE Clause<\/h2>\n
    Demo 1: Get all the Employees whose City is Bangalore<\/em><\/strong><\/p>\n
    SELECT * FROM dbo.Employee WHERE City = 'Bangalore'<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingWHEREClause1\"<\/a><\/p>\n
    Demo 2: Get all the Employees whose city is not Bangalore<\/em><\/strong><\/p>\n
    SELECT * FROM dbo.Employee WHERE City <> 'Bangalore'<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingWHEREClause4\"<\/a><\/p>\n
    Note: This query ignored the employees whose City column value is NULL, because NULL value can’t be compared with some value (i.e. Null means unknown value, so it can’t be used to compare with any know values). \u00a0The only operation we can do with NULL is we can check whether it is NULL or NOT NULL as shown in the below Demos 3 an 4.<\/em><\/p>\n
    Demo 3: Get all the Employees whose city column has some value (i.e. employees whose City column value is NOT NULL)<\/strong><\/p>\n
    SELECT * FROM dbo.Employee WHERE City is NOT NULL<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingWHEREClause2\"<\/a><\/p>\n
    Demo 4: Get all the Employees whose city column value is null<\/strong><\/p>\n
    SELECT * FROM dbo.Employee WHERE City is NULL<\/pre>\n
    RESULT: <\/strong><\/p>\n
    \"UsingWHEREClause3\"<\/a><\/p>\n
    3. Using Boolean Operators AND, OR and NOT<\/h2>\n
    Demo 1 Using Boolean Operator AND<\/em>:<\/strong> Get all the Employees whose City is Bangalore and Salary is above 55000<\/p>\n
    SELECT * FROM dbo.Employee \r\nWHERE City = 'Bangalore' AND Salary > 55000<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingBooleanOperatorAND\"<\/a><\/p>\n
    Demo 2 Using Boolean Operator OR<\/em>:\u00a0<\/strong>Get all the Employees whose city is either Bangalore or have salary above 62000<\/p>\n
    SELECT * FROM dbo.Employee \r\nWHERE City = 'Bangalore' OR Salary > 62000<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingBooleanOperatorOR\"<\/a><\/p>\n
    Demo 3 Using Boolean Operator NOT<\/em>:<\/strong> Get all the Employees whose city is other than Bangalore and Hyderabad<\/p>\n
    SELECT * FROM dbo.Employee \r\nWHERE City NOT IN ('Bangalore','Hyderabad')<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingBooleanOperatorNOT\"<\/a><\/p>\n
    Note: The above query didn’t return the employees whose is CITY column value is NULL. As explained previously NULL means unknown value, it can’t be compared with a known value.<\/em><\/p>\n
    4. Using LIKE Predicate<\/h2>\n
    Demo 1: Get all the Employees who have word deep<\/em> anywhere in their Name.<\/strong><\/p>\n
    SELECT * FROM dbo.Employee WHERE Name Like '%deep%'<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"LIKE1\"<\/a><\/p>\n
    Demo 2: Get all the Employees whose Name starts with the word dee<\/em><\/strong><\/p>\n
    SELECT * FROM dbo.Employee WHERE Name Like 'deep%'<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"LIKE2\"<\/a><\/p>\n
    Demo 3: Get all the Employees whose Name starts with the\u00a0character\u00a0a<\/em> or b<\/em> or c<\/em><\/strong>.
    \nNote the below three SELECT statements are equivalent<\/p>\n
    SELECT * FROM dbo.Employee WHERE Name like '[a-c]%'\r\nSELECT * FROM dbo.Employee WHERE Name like '[abc]%'\r\nSELECT * FROM dbo.Employee WHERE Name like '[a,b,c]%'<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"LIKE3\"<\/a><\/p>\n
    Demo 4: Get all the Employees whose Name starts with the\u00a0character\u00a0a<\/em> or b<\/em> or c<\/em> and second\u00a0character must be a<\/em><\/strong><\/p>\n
    SELECT * FROM dbo.Employee WHERE Name like '[a-c][a]%'<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"LIKE4\"<\/a><\/p>\n
    Demo 5: Get all the Employees whose Name is not starting with letter a<\/em> or b<\/em> or c<\/em><\/strong><\/p>\n
    SELECT * FROM dbo.Employee WHERE Name like '[^a-c]%'<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"LIKE5\"<\/a><\/p>\n
    5. Using BETWEEN Clause<\/h2>\n
    BETWEEN clause can be used to compare range of values.<\/p>\n
    Demo 1: Get all the Employees whose Salary is between 45000 to 60000.<\/strong><\/p>\n
    SELECT * FROM dbo.Employee\r\nWHERE Salary between 45000 AND 60000<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingBETWEENClause1\"<\/a><\/p>\n
    Demo 2: Above Demo 1<\/em> query can also be written without between Clause as below:<\/strong><\/p>\n
    SELECT * FROM dbo.Employee \r\nWHERE Salary >= 45000 AND Salary <= 60000<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingBETWEENClause2\"<\/a><\/p>\n
    6. Table and Column Name Alias<\/h2>\n
    Sql Server provides an option to give temporary alias name for the Table and it’s Column Names in the query. In that way we can give an meaning full alias to the Tables. And if two tables are joined both have the same column name in it, then we have to write two part column names i.e. [Table Name].[Column Name] otherwise Sql server gives an ambiguous column name error. If table name is too long it looks to clumsy, so better give a short alias name for the table and use this alias table name in the Two part column name specification to avoid ambiguity.<\/p>\n
    Demo 1: Table Alias Name demo.<\/strong>\u00a0In this demo example for Employee Table the alias name specified is E in the FROM clause. Because of this we can access the employee table column names by prefixing E.\u00a0<\/strong><\/em><\/p>\n
    SELECT E.Name, E.City FROM dbo.Employee E<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"TableAndColumnNameAlias1\"<\/a><\/p>\n
    Demo 2: Table and Column Name Alias demo.<\/strong>\u00a0In this demo example for Employee Table the alias name specified is E in the FROM clause. Because of this we can access the employee table column names by prefixing\u00a0E. .\u00a0<\/strong><\/em>And for the Name column we are specifying the alias name as ‘Employee Name’ and for City column the alias name is ‘Employee City’<\/p>\n
    SELECT E.Name AS 'Employee Name', E.City AS [Employee City]\r\nFROM dbo.Employee AS E<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"TableAndColumnNameAlias2\"<\/a><\/p>\n
    7. Using ORDER BY Clause<\/h2>\n
    ORDER BY<\/strong> Clause can be used to sort the result set based on the Column Value.<\/p>\n
    Demo 1: Sort the Employee records based on Name column value.<\/strong> The default sorting of the ORDER BY clause is in the Ascending Orders.<\/p>\n
    SELECT Name, City FROM dbo.Employee\r\nORDER BY Name<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingOrderByClause4\"<\/a><\/p>\n
    Demo 2: Sort the Employee records based on Name column value in the descending order.<\/strong> Here using the keyword DESC in conjunction with ORDER By clause to sort the records by Name in Descending order.<\/p>\n
    SELECT Name, City FROM dbo.Employee\r\nORDER BY Name DESC<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"UsingOrderByClause2\"<\/a><\/p>\n
    8. Concatenation<\/h2>\n
    ‘+’ symbol \u00a0is used for concatenating string values<\/p>\n
    Demo 1: Concatenate Name and City Column Value.<\/strong><\/p>\n
     <\/p>\n
    SELECT Name + City as [Name & City]\r\nFROM dbo.Employee<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"ConcateNation1\"<\/a><\/p>\n
    Note: \u00a0If ‘+’ symbol is used to concatenate the values, then if one of the values is NULL then resultant concatenated value will also be NULL.
    \n<\/em><\/p>\n
    Demo 2: One way of avoiding NULL as the RESULT of concatenation if one of the value of the to be concatenated is NULL is to use the ISNULL function like below. Here ISNULL function returns an empty string if the value is NULL otherwise the specified value.<\/strong><\/p>\n
    SELECT ISNULL(Name,'') + ISNULL(City,'') AS [Name & City]\r\nFROM dbo.Employee<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"ConcateNation6\"<\/a><\/p>\n
    Demo 3: Add an empty space between Name and City.<\/strong><\/p>\n
    SELECT ISNULL(Name,'') + ' ' + ISNULL(City,'') AS [Name & City]\r\nFROM dbo.Employee<\/pre>\n
    RESULT:<\/strong><\/p>\n
    \"ConcateNation5\"<\/a><\/p>\n
    You may like to go through the string function CONCAT()<\/a> which is introduced in Sql Server 2012 for concatenation.<\/p>\n","protected":false},"excerpt":{"rendered":"
    Sql Server Tutorial\u00a0Lesson 5: Querying Data Using SELECT SELECT is one of the basic construct of Sql Server, which basically facilitates the retrieval of information from Tables. This lesson covers the following Topics with extensive list of real-time examples. Introduction to SELECT Statement Using WHERE Clause Using Boolean Operators AND, OR and NOT Using LIKE … Continue reading Querying Data Using SELECT In Sql Server<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[3,242],"tags":[292,289,297,295,291,290,294,287,38,986,996,296,293,288],"class_list":["post-2318","post","type-post","status-publish","format-standard","hentry","category-sql-server","category-sql-server-tutorial","tag-between-clause","tag-boolean-operators-and","tag-column-name-alias","tag-concatenation","tag-like-predicate","tag-or-and-not","tag-order-by-clause","tag-querying-data-using-select-in-sql-server","tag-select","tag-sql-server","tag-sql-server-tutorial","tag-table-alias","tag-table-and-column-name-alias","tag-where-clause"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-Bo","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/2318","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=2318"}],"version-history":[{"count":42,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/2318\/revisions"}],"predecessor-version":[{"id":5784,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/2318\/revisions\/5784"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=2318"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=2318"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=2318"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}