Module 2 Structured Query Language Lecture 1 Basic

Module 2: Structured Query Language Lecture 1: Basic SELECT Syntax 1/6/2022 Pierce College CIS 261 SQL 1

The SELECT statement SELECT select_list [ WITH <common_table_expression>] SELECT select_list [ INTO new_table ] [ FROM table_source ] [ WHERE search_condition ] [ GROUP BY group_by_expression ] [ HAVING search_condition ] [ ORDER BY order_expression [ ASC | DESC ] ] http: //technet. microsoft. com/en-us/library/ms 189499. aspx • Capitalize reserved terms • Separate lines for each term 1/6/2022 Pierce College CIS 261 SQL 2

SELECT The SELECT statement has many optional clauses: • WHERE specifies which rows to retrieve. • GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group. • HAVING selects among the groups defined by the GROUP BY clause. • ORDER BY specifies an order in which to return the rows. http: //en. wikipedia. org/wiki/Select_%28 SQL%29 1/6/2022 Pierce College CIS 261 SQL 3

Correlating SELECT Statement to ER Diagram SELECT First. Name, Last. Name FROM Patient WHERE Last. Name = ‘Smith’ ORDER BY First. Name; 1/6/2022 Pierce College CIS 261 SQL 4

Query Window Entering the following SQL into the query window will return the results shown in the image: USE Northwnd GO SELECT * FROM [dbo]. [Employees] 1/6/2022 Pierce College CIS 261 SQL 5

Successful SELECT Query A successful SELECT query will return the rows selected on the RESULTS tab, and the count of rows affected on the MESSAGES tab. USE Northwnd GO SELECT * FROM [dbo]. [Employees] 1/6/2022 Pierce College CIS 261 SQL 6

SQL Query with Errors An unsuccessful query will return the error in the MESSAGES tab. It is important to read and understand the message, as this will help you to correct the issue(s) with the query. There is no more efficient way to debug a query than to read and understand the error message. USE Northwnd GO SELECT DISTINCT [Country] FROM [dbo]. [Customers] 1/6/2022 Pierce College CIS 261 SQL 7

Selecting Columns You can drag and drop table names, columns and other artifacts from the Object Explorer to the Query Window in SQL Server Management Studio. However, you can also type the information into the Query Window. 1/6/2022 Pierce College CIS 261 SQL 8

Module 2: Structured Query Language Lecture 2: Variations on the Basic SELECT Statement 1/6/2022 Pierce College CIS 261 SQL 10

DISTINCT Use DISTINCT to get a list of unique countries - no duplicate records. Entering the following SQL into the query window will return the results shown in the image: USE Northwnd GO SELECT DISTINCT [Country] FROM [dbo]. [Customers] 1/6/2022 Pierce College CIS 261 SQL 11

Column Alias Use a column alias to change how the column header will read. This is especially useful when you have expressions in the SELECT list, but can be used anytime you need a more descriptive column header. Note that this does not change the table, just the results of the query. Entering the following SQL into the query window will return the results shown in the image: SELECT [Customer. ID] AS "Customer Identification" FROM [dbo]. [Customers] 1/6/2022 Pierce College CIS 261 SQL 12

Table Alias Use a table alias to change how the columns are selected. This is especially useful when you have JOINS in the query, but can be used anytime you need a more readable query. Note that this does not change the table, just the results of the query. The top query in the diagram uses full four part naming, while the bottom uses a table alias. The query with the table alias is much easier to read. Both return the same results. SELECT c. Customer. ID , c. [Company. Name] , c. [Contact. Name] FROM [Northwnd]. [dbo]. [Customers] c 1/6/2022 Pierce College CIS 261 SQL 13

Merging Data in a Query To merge column data, use plus symbol and desired spacers. As example + ', ' + to merge the separate address parts into one field. Be sure to give this new column an alias. This data is NOT saved. SELECT [Customer. ID] , [Company. Name] , [Address] + ', ' + [City] + ', ' + [Postal. Code] + ', ' + [Country] AS Address FROM [dbo]. [Customers] Later you will see how to use built-in functions (CONCAT and SPACE) to do the same thing. 1/6/2022 Pierce College CIS 261 SQL 14

Module 2 Structured Query Language Lecture 3 – Single Row Aggregate Functions 1/6/2022 Pierce College CIS 261 SQL 15

COUNT The following uses COUNT to return a count of all of the orders in the “Order Details” table. Note that a column alias has been added. Note also that this query returns a scalar value. You cannot add another non-aggregate field to the SELECT statement without using a GROUP BY clause, covered in a later lesson. SELECT COUNT(Order. ID) AS "Count Of Orders" FROM [dbo]. [Order Details] 1/6/2022 Pierce College CIS 261 SQL 16

MAX The following uses MAX to return the highest Unit. Price in the “Order Details” table. Note that a column alias has been added. Note also that this query returns a scalar value. You cannot add another non-aggregate field to the SELECT statement without using a GROUP BY clause, covered in a later lesson. SELECT Max(Unit. Price) as "Max Unit Price" FROM [dbo]. [Order Details] 1/6/2022 Pierce College CIS 261 SQL 17

SUM The following uses an expression that multiplies the Unit. Price and Quantity fields to return the total in the “Order Details” table. Note that a column alias has been added. The query created a computed column that returns a scalar value. SELECT SUM(Unit. Price * Quantity) as "Order Totals" FROM [dbo]. [Order Details] 1/6/2022 Pierce College CIS 261 SQL 18

MIN The following uses an expression that multiplies the Unit. Price and Quantity fields to return the minimum order value in the “Order Details” table. Note that a column alias has been added. The query created a computed column that returns a scalar value. SELECT MIN(Unit. Price * Quantity) as "Min Order" FROM [dbo]. [Order Details] 1/6/2022 Pierce College CIS 261 SQL 19

AVG The following uses an expression that multiplies the Unit. Price and Quantity fields to return the average order value in the “Order Details” table. Note that a column alias has been added. The query created a computed column that returns a scalar value. SELECT AVG(Unit. Price * Quantity) as “Avg Order" FROM [dbo]. [Order Details] 1/6/2022 Pierce College CIS 261 SQL 20

This work is licensed under a Creative Commons Attribution 4. 0 International License Led by Bellevue College, the Health e. Workforce Consortium was formed to elevate Health Information Technology workforce development locally and nationally and provide career paths into this promising field for veterans and others. The nine-college consortium includes Bellevue College, Bellingham Technical College, Clark College, Clover Park Technical College, Northern Virginia Community College, Pierce College, Renton Technical College, Spokane Community College, and Whatcom Community College. The Health Information and Management Systems Society (HIMSS) is also a primary partner. This workforce solution was 100% funded by an $11. 7 m grant awarded by the U. S. Department of Labor's Employment and Training Administration, Grant #TC-23745 -12 -60 -A-53. The solution was created by the grantee and does not necessarily reflect the official position of the U. S. Department of Labor. The Department of Labor makes no guarantees, warranties, or assurances of any kind, express or implied, with respect to such information, including any information on linked sites and including, but not limited to, accuracy of the information or its completeness, timeliness, usefulness, adequacy, continued availability or ownership. 1/6/2022 Pierce College CIS 261 SQL 21