Introduction to SQL Server and the Structure Query

Introduction to SQL Server and the Structure Query Language Dave Valentine dk. Valz@comcast. net www. ingenioussql. com

Agenda What is SQL Server What is SQL Table, Columns, Rows SELECT FROM WHERE SQL Functions GROUP BY ORDER BY

About Dave MCP MCSA: SQL Server 2012 Database Architect / Database Developer Adjunct Professor @Ingenious. SQL dk. Valz@comcast. net Ingenious. SQL. com

What is a Database Collection of structured/organized data Typically stored on a computer or in the cloud Simple to Complex Small to Large What is stored in a database

RDBMS Relational Database Management System Relational Database Tables Rows Columns Popular RDBMS: • SQL Server • Oracle • My. SQL

SQL Structured Query Language ANSI Standard RDBMS independent SQL Flavors DDL – Data Definition Language DML – Data Manipulation Language

Tables Database constructs that store data Collection of related data Rows and Columns Spreadsheets

Columns Set of data definitions for data that is stored in a table Types • Text • • Numerical • • VARCHAR, NVARCHAR, NCHAR BIT, TINYINT, SMALLINT, BIGINT, DECIMAL, FLOAT, MONEY Date • DATETIME, DATE, TIME Fields Attributes

Rows • Set of related data values stored in a table • Single record in spreadsheet • Tuple • Record Address ID 22 23 24 25 26 Address 1234 Music Ave 609 Freemont Street 1850 Birch Street 1713 Dixon Drive 2121 SQL Lane City Shakopee Las Vegas Wayzata Mahtomedi Bemidji State MN NV MN MN MN ZIP 11111 22222 55555 44444 33333

SELECT The SELECT statement returns data from a database table Data is returned in the form of a result set. Basic SELECT syntax SELECT Col 1, Col 2, … Col. N FROM Table. Name; Col 1, Col 2, … Col. N are the columns of a table Table. Name whose values you want to return. All the columns available in the table Table. Name can be returned using the syntax SELECT * FROM Table. Name Is SELECT * a good idea?

FROM Defines where the data will be retrieved in a SELECT statement Simple Complex Optional

WHERE Used to filter records in a dataset Basic WHERE syntax SELECT Col 1, Col 2, … Col. N FROM Table. Name WHERE Col 1 = [Some Value] Typically follows the FROM statement Simple Complex Optional

Comparison Operators Mathematical symbol to compare two expressions Primarily used in the WHERE clause Outcome yields a Boolean value Common Operators • = Equal to • > Greater than • < Less than • >= Greater than or equal to • <= Less than or equal to • <> Not equal to

Logical Operators • Logical operators compare two or more conditions to filter data sets • Yields Boolean values (True or False) • SQL Common Operators • NOT • AND • OR • LIKE • BETWEEN • IN • Truth Tables • Operation Precedence

Truth Tables NOT TRUE FALSE TRUE AND TRUE FALSE FALSE OR TRUE FALSE TRUE FALSE

Operator Precedence Operators at the top of this list are evaluated before an operators in the lower parts of this list. • ~ • *, /, % • +, - • =, >, <, >=, <>, !=, !>, !< • NOT • AND • ALL, ANY, BETWEEN, IN, LIKE, OR, SOME • =

String Functions Built in functionality that can manipulate expressions Popular SQL Server String Functions: • CONCAT Adds two or more strings together ( + ) • LEFT Extracts a number of characters from a string (starting from left) • LEN Returns the length of a string • LOWER Converts a string to lower-case • LTRIM Removes leading spaces from a string • REPLACE Replaces all occurrences of a string within a string, with a new string • REVERSE Reverses a string and returns the result • RIGHT Extracts a number of characters from a string (starting from right) • RTRIM Removes trailing spaces from a string • SUBSTRING Extracts some characters from a string • UPPER Converts a string to upper-case

Date Functions Built in functionality that manipulates date expressions Popular SQL Server String Functions • GETDATE • GETUTCDATE Returns the current database system UTC date and time • DATEADD Adds a time/date interval to a date and then returns the date • DATEDIFF Returns the difference between two dates • DAY Returns the day of the month for a specified date • MONTH Returns the month part for a specified date (1 to 12) • YEAR Returns the Year part for a specified date . Returns the current database system date and time

Numerical Functions Built in functionality that aggregates numerical columns Popular SQL Server Numerical Functions • ABS Returns the absolute value of a number • POWER Returns the value of a number raised to the power of another number • ROUND Rounds a number to a specified number of decimal places • SQRT Returns the square root of a number • SQUARE Returns the square of a number • SUM Calculates the sum of a set of values • AVG Returns the average value in a set of values • COUNT Returns the number of records in a set of values • MAX Returns the maximum value in a set of values • MIN Returns the minimum value in a set of values

Product ID 1 2 2 3 3 4 4 4 Name Adjustable Race Bearing Ball BB Ball Bearing Headset Ball Bearings Number Sold 2500 2000 1000 800 500 200 800 600

GROUP BY Used to arrange identical rows of data into groups Groups are determined by the columns specified Basic GROUP BY Syntax SELECT Col 1, Col 2 FROM Table. Name WHERE Col 3 = [Some. Value] GROUP BY Col 1, Col 2 Typically follows the WHERE statement Commonly used with aggregating functions

ORDER BY Used to sort the result set in ascending or descending order Default is ascending Order is determined by the columns specified Basic ORDER BY Syntax SELECT Col 1, Col 2 FROM Table. Name WHERE Col 3 = [Some. Value] GROUP BY Col 1, Col 2 ORDER BY Col 1 ASC, Col 2 DESC Typically one of the last statements in a query Caution with numerical data stored as characters

Table Joins Joining tables to single result set Found in the FROM section of a query Types • INNER • LEFT • RIGHT • CROSS • FULL Basic JOIN Syntax SELECT Col 1, Col 2 FROM Table. Name 1 tn 1 INNER JOIN Table. Name 2 tn 2 ON tn 1. Col 1 = tn 2. Col 1 WHERE Col 3 = [Some. Value] GROUP BY Col 1, Col 2

Advanced Functions ISNULL Returns the expression if it not null else the specified value COALESCE Returns the first non null value in the specified expression list CAST Casts an expression to another data type CONVERT Converts an expression to another data type • Convert Dates to String • https: //docs. microsoft. com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql

Summary SQL and SQL Server Table, Columns, Rows SELECT FROM WHERE SQL Functions GROUP BY ORDER BY

Questions Dave Valentine • @ingenious. SQL • ingenious. SQL. com • linkedin. com/in/ingenioussql