SQL Fundamentals

Structured Query Language Brian Foote Fox Valley Technical College

What is the plan for today? • Learn about databases • Learn about SQL Server • Learn about SQL • Whatever else you want to do!

• foote@fvtc.edu • 920-735-2568 • Skype : brian.d.foote • Twitter : @footefvtc About Me

• UW-Eau Claire • IT Consultant – 18 years • Cap Gemini, Greenbrier & Russell, Fujitsu, Skyline, Rush Lake Consulting • CUNA Mutual Group, Kimberly-Clark, The Real Estate Group, Bemis, Associated Bank, JJ Keller, ThedaCare, Oshkosh Chamber of Oshkosh, Pierce • Husband and father of 3 daughters. • Duck Hunter/Basketball Coach • Teaching – 8th semester Brian Foote

SSMS – SQL Server Management Studio – Microsoft’s Query tool to their own database format. HOW?

SSMS – SQL SERVER MANAGEMENT STUDIO Start All Programs Microsoft SQL Server SSMS

CONNECTING TO SQL SERVER Information you need to connect: Server Database Credentials

SSMS – Connecting to SQL Server CONNECTING TO SQL SERVER

SQL is short for Structured Query Language and is a widely used database language, providing means of data manipulation (store, retrieve, update, delete) and database creation. SQL Server vs SQL? SQL OVERVIEW

• What is SQL and why is it important? • Retrieve data from a single table. • SELECT • SELECT INTO • ORDER BY • WHERE CONDITIONS • AGGREGATE SELECTS SQL OVERVIEW

• Retrieve data from a multiple tables. • JOIN (INNER, LEFT, RIGHT, OUTER) • SELECT • SELECT INTO • ORDER BY • WHERE CONDITIONS • AGGREGATE SELECTS SQL OVERVIEW

SELECT Statement – Used to retrieve data from the database table. Syntax : SELECT Column1, Column2, Column3, * FROM Table1 Exercise : • Simple column SELECT • Simple * SELECT Example : SELECT * FROM Customers SELECT Name, Address. City, State, ZIP FROM Customers SQL – SELECT – 1 TABLE

The SQL ORDER BY clause comes in handy when you want to sort your SQL result sets by some column or columns. Syntax : SELECT * FROM Table1 ORDER BY COLUMN (ORDER BY COLUMN #) Exercise : Simple column SELECT ORDER BY Example : SELECT * FROM Customers ORDER BY City, LastName SQL – SELECT - ORDER

The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query. Syntax : SELECT Column1, Column2, Column3, FROM Table1 WHERE <CONDITION> Exercise : Simple column SELECT WHERE Example : SELECT * FROM Customers WHERE State = ‘WI’ SQL – SELECT - WHEREHI

The SQL DISTINCT clause is used together with the SQL SELECT keyword, to return a dataset with unique entries for certain database table column. Syntax : SELECT DISTINCT Column1, Column2, Column3, INTO Table2 FROM Table1 Exercise : Simple column SELECT DISTINCT Example : SELECT DISTINCT LastName FROM Customers SQL – SELECT - DISTINCT

Condition Must be True or False Relational Operators = Equal <> Not Equal < Less Than <= Less Than or Equal To > Greater than >= Greater than or Equal To The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query. SQL – SELECT - WHERE

The SQL AND clause is used when you want to specify more than one condition in your SQL WHERE clause, and at the same time you want all conditions to be true. Syntax : SELECT Column1, Column2, Column3, FROM Table1 WHERE <CONDITION> AND <DIFFERENT CONDITION> Exercise : Simple column SELECT WHERE AND Example : SELECT * FROM Customers WHERE State = ‘WI’ AND CITY = ‘MADISON’ SQL – SELECT - AND

The SQL OR statement is used in similar fashion and the major difference compared to the SQL AND is that OR clause will return all rows satisfying any of the conditions listed in the WHERE clause. Syntax : SELECT Column1, Column2, Column3, FROM Table1 WHERE <CONDITION> OR <DIFFERENT CONDITION> Exercise : Simple column SELECT WHERE OR Example : SELECT * FROM Customers WHERE State = ‘WI’ OR State = ‘MN’ SQL – SELECT - OR

Other information • Be aware or data type. • String data must in double quotes. • Numeric data does not. • Order of precedence. 1. ~ (Bitwise NOT) 2. * (Multiply), / (Division), % (Modulo) 3. + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract) 4. =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators) 5. NOT 6. AND 7. ALL, ANY, BETWEEN, IN, LIKE, OR, SOME 8. = (Assignment) The SQL WHERE clause is used to select data conditionally, by adding it to already existing SQL SELECT query. SQL – SELECT - OOP

BETWEEN - TRUE if the operand is within a range. test_expression [ NOT ] BETWEEN begin_expression AND end_expression EXISTS - TRUE if a sub query contains any rows. EXISTS {SELECT STATEMENT} IN - TRUE if the operand is equal to one of a list of expressions. test_expression [ NOT ] IN ( subquery | expression [ ,...n ] ) NOT - Reverses the value of any other Boolean operator. [ NOT ] boolean_expression SQL – SELECT - OTHER

LIKE - TRUE if the operand matches a pattern. match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] % - Any string of zero or more characters. (WHERE title LIKE '%computer%' ) _ - Any single character. (WHERE au_fname LIKE '_ean') [ ] - Any single character within the specified range ([a-f]) or set ([abcdef]). (WHERE au_lname LIKE '[C-P]arsen' ) [^] - Any single character not within the specified range ([^a-f]) or set ([^abcdef]). (WHERE au_lname LIKE 'de[^l]%' ) SQL – SELECT - LIKE

The SQL SELECT INTO statement is used to select data from a SQL database table and to insert it to a different table at the same time. Syntax : SELECT Column1, Column2, Column3, INTO Table2 FROM Table1 Exercise : Simple column SELECT INTO Example : SELECT Name, Address. City, State, ZIP INTO NewCustomers FROM Customers SELECT * FROM Customers SQL SELECT INTO

COUNT MAX MIN AVG SUM Structured Query Language Aggregate Operators SQL - AGGREGATE

The SQL COUNT clause is used together with the SQL SELECT keyword, to return a dataset with unique entries for certain database table column. Syntax : SELECT COUNT(*) FROM Table1 Exercise : Simple column SELECT COUNT Example : SELECT COUNT(*) FROM Customers SQL – AGGREGATE – COUNT

The SQL MAX aggregate function allows us to select the highest (maximum) value for a certain column. Syntax : SELECT MAX(Column1) FROM Table1 Exercise : SELECT MAX Example : SELECT MAX(DOB) AS MaxDOB FROM Customers SQL – AGGREGATE - MAX

The SQL MIN aggregate function allows us to select the lowest (minimum) value for a certain column. Syntax : SELECT MIN(Column1) FROM Table1 Exercise : SELECT MIN Example : SELECT MAX(DOB) AS MinDOB FROM Customers SQL – AGGREGATE - MIN

The SQL SUM aggregate function allows selecting the total for a numeric column. Syntax : SELECT SUM(Column1) FROM Table1 Exercise : SELECT SUM Example : SELECT SUM(SaleAmount) AS TotalSales FROM Sales SQL – AGGREGATE - SUM

The SQL AVG aggregate function allows selecting the total for a numeric column. Syntax : SELECT AVG(Column1) FROM Table1 Exercise : SELECT AVG Example : SELECT AVG(SaleAmount) AS AVGTotalSales FROM Sales SQL – AGGREGATE - AVERAGE

The SQL GROUP BY statement is used along with the SQL aggregate functions like SUM to provide means of grouping the result dataset by certain database table column(s). Syntax : SELECT COLUMN2, SUM(Column1) FROM Table1 GROUP BY COLUMN2 Exercise : SELECT GROUP BY Example : SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee SQL – AGGREGATE – GROUP BY

The SQL HAVING clause is used to restrict conditionally the output of a SQL statement, by a SQL aggregate function used in your SELECT list of columns. Syntax : SELECT COLUMN2, <aggregate function> (Column1) FROM Table1 GROUP BY COLUMN2 HAVING <aggregate function> (Column1) condition Exercise : SELECT HAVING Example : SELECT Employee, SUM (Hours) FROM EmployeeHours GROUP BY Employee HAVING SUM (Hours) > 24 SQL – AGGREGATE HAVING

STRUCTURED QUERY LANGUAGE JOINS The SQL JOIN clause is used whenever we have to select data from 2 or more tables. Join Types • INNER - Returns all rows from both tables as long as there is a match between the columns we are matching On. • OUTER – Returns rows from the joined tables if they match or not.

The INNER JOIN will select all rows from both tables as long as there is a match between the columns we are matching on. SELECT <select_list> FROM Table_A A INNER JOIN Table_B B ON A.Key = B.Key SQL – INNER JOIN

LEFT - The LEFT OUTER JOIN or simply LEFT JOIN (you can omit the OUTER keyword in most databases), selects all the rows from the first table listed after the FROM clause, no matter if they have matches in the second table. SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key SQL – LEFT JOIN

RIGHT - The RIGHT OUTER JOIN or just RIGHT JOIN behaves exactly as SQL LEFT JOIN, except that it returns all rows from the second table (the right table in our SQL JOIN statement). SELECT <select_list> FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key SQL – RIGHT JOIN

FULL OUTER -This query will return all of the records from both tables, joining records from the left table (table A) that match records from the right table (table B). -SELECT <select_list> FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key SQL – FULL OUTER

Left Excluding JOIN -This query will return all of the records in the left table (table A) that do not match any records in the right table (table B). SELECT <select_list> FROM Table_A A LEFT JOIN Table_B B ON A.Key = B.Key WHERE B.Key IS NULL SQL – LEFT JOIN EXCLUDING

Right Excluding JOIN -This query will return all of the records in the right table (table B) that do not match any records in the left table (table A). SELECT <select_list> FROM Table_A A RIGHT JOIN Table_B B ON A.Key = B.Key WHERE A.Key IS NULL

Outer Excluding JOIN This query will return all of the records in the left table (table A) and all of the records in the right table (table B) that do not match. SELECT <select_list> FROM Table_A A FULL OUTER JOIN Table_B B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL SQL – OUTER JOIN EXCLUDING

Structured Query Language Multiple Tables and Aggregate Operators • COUNT • MAX • MIN • AVG • SUM SQL – AGGREGATE MULTIPLE TABLES

Structured Query Language Other Things • Elimination of Nulls (ISNULL) • Use of subquerys when the joins just do not seem right. • Temp Tables (local (#) and global (##) • Export to Excel • Stored Procedures SQL – OTHER THINGS

Sources • Microsoft Developer Network - http://msdn.microsoft.com/en- us/sqlserver/default.aspx • SQL Tutorial - http://www.sql-tutorial.net/ • Code project: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx SQL – ONLINE SOURCES

Contact Info • Fox Valley Technical College • Brian Foote • foote@fvtc.edu CONTACT INFO

SQL Fundamentals

In this document