{"id":623,"date":"2011-07-29T18:03:44","date_gmt":"2011-07-29T18:03:44","guid":{"rendered":"http:\/\/craftydba.com\/?p=623"},"modified":"2012-12-28T17:28:20","modified_gmt":"2012-12-28T17:28:20","slug":"selecting-data-part-1","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=623","title":{"rendered":"Selecting Data – Part 1"},"content":{"rendered":"

The SELECT<\/a> reservered word is part of the Data Manipulation Language (DML) defined by Codd<\/a> and is used to query data from the database as recordsets. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. All reserved words that I introduce below will be in CAPS below.<\/p>\n

Today, I would like to review simple SELECT<\/a> queries using the AdventureWorks<\/a> Sample database supplied by Microsoft.<\/p>\n

The most basic use of the SELECT statement is to return a constant as a record set.<\/p>\n

-- Return string constant\r\nSELECT 'Harry Potter and the Deathly Hollows Part 2' AS NewMovie\r\n\r\n-- Return integer constant\r\nSELECT 712 AS NewInteger\r\n\r\n-- Return decimal constant\r\nSELECT 5.234 AS NewReal\r\n<\/span><\/pre>\n<\/p>\n
To really use this statement to retrieve data, we need to supply the name of an existing table.  This is known as the FROM clause.  The example below uses a three part notation to describe the database name, schema owner, and table name to SELECT data from.<\/p>\n
-- Three part notation for table name\r\nSELECT * FROM [AdventureWorks2008R2].[Person].[AddressType]\r\n<\/span><\/pre>\n<\/p>\n
Instead of supply this three part notation, we can specify which database we want to USE up front.  The GO reserved word executes and commits prior statements.  The example below uses the Adventure Works database and returns the TOP 10 persons from the table.<\/p>\n
-- Switch to correct database to drop 1 part\r\nUSE AdventureWorks2008R2;\r\nGO\r\n\r\n-- Show top 10 persons (predicate)\r\nSELECT TOP 10 * FROM Person.Person\r\n<\/span><\/pre>\n<\/p>\n
This query is not to helpful since it does not specify the rows we want.  The WHERE clause is used to supply an expression that filters the data.  The first query looks for any relatives of Bill Gates.<\/p>\n
-- Any relatives of Bill Gates? (where clause)\r\nSELECT * FROM Person.Person \r\nWHERE LastName = 'Gates'\r\n<\/span><\/pre>\n<\/p>\n
An table alias, AS reserved word, can be used to allow for shorter notation when referencing fields.  This is most useful when tables with the same field name are joined and a ambigous reference is made.<\/p>\n
The examples below show the use of standard comparison operators with the unique ID on the person table to return various rows.<\/p>\n
--  Equal\r\nSELECT * FROM Person.Person AS P \r\nWHERE P.BusinessEntityID = 7\r\n\r\n-- Greater Than; Less Than\r\nSELECT * FROM Person.Person AS P \r\nWHERE P.BusinessEntityID > 6 AND P.BusinessEntityID < 8\r\n\r\n-- Greater Than or Equal; Less Than or Equal\r\nSELECT * FROM Person.Person AS P \r\nWHERE P.BusinessEntityID >= 6 AND P.BusinessEntityID <= 8\r\n\r\n-- Not Equal To\r\nSELECT TOP 10 * FROM Person.Person AS P \r\nWHERE P.BusinessEntityID <> 5\r\n<\/span><\/pre>\n<\/p>\n
So far, we have been returning every column in the table using the * reserved word.  This is extremely wasteful in network traffic, bandwidth, if we were only interested in the city name and postal code of Seattle, Washington.  The example below returns the extact information we want.<\/p>\n
-- Selecting just a few fields (141)\r\nSELECT A.[City], A.[PostalCode]\r\nFROM [AdventureWorks2008R2].[Person].[Address] AS A\r\nWHERE A.[City] = 'Seattle'\r\n<\/span><\/pre>\n<\/p>\n
Sometimes, we want to find information for two cities.  The IN reserved word uses set notation to allow for multiple filters in a shortened format.<\/p>\n
-- In search condition (343)\r\nSELECT A.[City], A.[PostalCode]\r\nFROM [AdventureWorks2008R2].[Person].[Address] AS A \r\nWHERE A.[City] IN ('Seattle', 'Berkeley')\r\n<\/span><\/pre>\n<\/p>\n
Another simple query is to return all the Cities that start with y.  The LIKE reserved work uses a regular expression pattern to filter rows.  Therefore, we specify a pattern to search for all cities that start with y.<\/p>\n
-- Like search condition (213)\r\nSELECT A.[City], A.[PostalCode]\r\nFROM [AdventureWorks2008R2].[Person].[Address] AS A \r\nWHERE A.[City] LIKE 'y%'\r\n<\/span><\/pre>\n<\/p>\n
As you can see, each of the queries that we execute are returning multiple rows from the sample database.  Instead we want to return all DISTINCT rows for cities that are IN Seatle, Berkeley, Yakima, and York.  The query below does just that.<\/p>\n
-- Get distinct answers\r\nSELECT DISTINCT A.[City], A.[PostalCode]\r\nFROM [AdventureWorks2008R2].[Person].[Address] AS A \r\nWHERE A.[City] IN ('Seattle', 'Berkeley', 'Yakima', 'York')\r\n<\/span><\/pre>\n<\/p>\n
Regular expressions can be very powerful.  The following statement returns all cities that start with “Sea”.<\/p>\n
-- Regular expressions, cities that start with SEA (221)\r\nSELECT A.[City], A.[PostalCode]\r\nFROM [AdventureWorks2008R2].[Person].[Address] AS A \r\nWHERE A.[City] LIKE '[S][e][a]%'\r\n<\/span><\/pre>\n<\/p>\n
Boolean expressions (AND, OR, NOT) can be used to join multiple expressions to get the desired result. The query to find postal codes for Seattle and Redmond can be rewritten with the OR reserve word.<\/p>\n
-- Using OR (262)\r\nSELECT A.[City], A.[PostalCode]\r\nFROM [AdventureWorks2008R2].[Person].[Address] AS A \r\nWHERE A.[City] = 'Seattle' OR A.[City] = 'Redmond'\r\n<\/span><\/pre>\n<\/p>\n
Using the AND reserve work on the same query returns no data since the condition can not be meet.<\/p>\n
-- Using AND (0)\r\nSELECT A.[City], A.[PostalCode]\r\nFROM [AdventureWorks2008R2].[Person].[Address] AS A \r\nWHERE A.[City] = 'Seattle' AND A.[City] = 'Redmond'\r\n<\/span><\/pre>\n<\/p>\n
The NOT reserve word can be used to find all matches that do not meet a criteria.<\/p>\n
-- Using NOT (19473)\r\nSELECT A.[City], A.[PostalCode]\r\nFROM [AdventureWorks2008R2].[Person].[Address] AS A \r\nWHERE NOT (A.[City] = 'Seattle')\r\n<\/span><\/pre>\n<\/p>\n
Last but not least, there are times that we want to look at the TOP n rows.  All other data is not of interest at the current time.  The following query returns the three lowest cost items by using the ORDER BY clause and the TOP keyword.<\/p>\n
-- Returns three records \r\nSELECT TOP 3 *\r\nFROM [AdventureWorks2008R2].[Production].[Product] p\r\nWHERE P.ListPrice <> 0\r\nORDER BY P.ListPrice ASC\r\n<\/span><\/pre>\n<\/p>\n
However, this query is not truely accurate since there are three products (records) in the database at $4.99. Using the WITH TIES reserved word allows these identical rows to show up in the result set.<\/p>\n
-- Returns five records (3 @ 4.99)\r\nSELECT TOP 3 WITH TIES *\r\nFROM [AdventureWorks2008R2].[Production].[Product] p\r\nWHERE P.ListPrice <> 0\r\nORDER BY P.ListPrice\r\n<\/span><\/pre>\n<\/p>\n
To recap this discussion, the SELECT statement can be very complex equation laced with multiple levels of SYNTAX.  We have just started looking at the tip of the iceberg with this statement.  Next time I talk about the SELECT statement, I will be introducing how to join multiple tables or use sub-queries to get the results that you want.<\/p>\n","protected":false},"excerpt":{"rendered":"
The SELECT reservered word is part of the Data Manipulation Language (DML) defined by Codd and is used to query data from the database as recordsets. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data. All reserved words that I introduce below will be in CAPS below. Today, I would like to review simple SELECT queries using the AdventureWorks Sample database supplied by Microsoft. The most basic use of the SELECT statement is to return a constant as…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[47,31,42,12,38,45,15,46,49,48,40,37,28,44,41,29,39,43],"class_list":["post-623","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-and","tag-database-developer","tag-distinct","tag-free-code","tag-from","tag-in","tag-john-f-miner-iii","tag-like","tag-not","tag-or","tag-order-by","tag-select","tag-sql-server","tag-table-alias","tag-top","tag-tsql","tag-where","tag-with-ties"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/623","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=623"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/623\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=623"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=623"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=623"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}