sql basic selects

22
Bob Litsinger [email protected] SQL Select . . . the very basics

Upload: bob-litsinger

Post on 05-Jul-2015

214 views

Category:

Documents


2 download

DESCRIPTION

Using single tables only, this slide show reviews and explains basic SQL syntax in T-SQL.

TRANSCRIPT

Page 1: Sql Basic Selects

Bob [email protected]

SQL Select

. . . the very basics

Page 2: Sql Basic Selects

Bob [email protected]

Introduction

This slide set covers basic select statements against a single table.

It does not cover:

Joins and other table to table interactions.

Using sub-queries in a primary query.

The use of the many functions available in SQL.

There are a few examples of functions, and an additional topics list at the end.

Page 3: Sql Basic Selects

Bob [email protected]

Contents

SQL Select . . . the very basics

Introduction 2 More on ORDER BY 13

Contents 3 Having 14

English like 4 Operators 15

Simple Select 5 Qualifiers for WHERE 16

Specifying the fields 6 More on qualifiers for WHERE 17

Introducing WHERE clauses 7 BETWEEN with datetime type 18

Exploring information 8 Wildcards with LIKE 19

Count – How Many Products 9 ALIASES, CAST and CONVERT 20

A few aggregations 10 Other Capabilities 21

Establishing order 11 Queries Unleashed 22

More on GROUP BY 12

Bob [email protected]

Page 4: Sql Basic Selects

Bob [email protected]

English like

Basic SQL is very intuitive for users because a request is English like:

. . . but using this structure, we can ask complicated questions.

Bob [email protected]

Page 5: Sql Basic Selects

Bob [email protected]

Simple Select

I wonder what types of products Adventure * Will show us all the

details (called fields) about the Categories

To much information? You can get just the ID and Name by being more specific.

Bob [email protected]

Page 6: Sql Basic Selects

Bob [email protected]

Specify the fields

So, let’s get the Category ID, Name and Manager:

Using the Category ID for Bikes (1), the types of bikes can be found in the Subcategory table.

Bob [email protected]

Page 7: Sql Basic Selects

Bob [email protected]

Introducing WHERE clauses

To limit the Sub Categories to bikes, a WHERE clause is added:

Bob [email protected]

Page 8: Sql Basic Selects

Bob [email protected]

Exploring information

Looking only at Road Bikes in the Product line up will enable us to explore some data in other ways.

Gives us a list of 43 bikes

More are listed

Bob [email protected]

Page 9: Sql Basic Selects

Bob [email protected]

Count - How Many Products

The count(*) function provides a simple count of the bikes

This assigns a name to a column. If the name has spaces, keywords or some other characters it requires brackets.

The word AS is optional, but makes things clearer. If there are no spaces or invalid characters, brackets are not needed.

All Products

Just Road Bikes

Introducing column labels

Bob [email protected]

Page 10: Sql Basic Selects

Bob [email protected]

A few aggregations.I can add a label to each row. It is listed as a query

item and is in quotes.

Calculates the average value.

Finds the maximum, i.e. highest, value

Finds the minimum, i.e. lowest, value

Notice the WHERE clause can include many conditions

To calculate the aggregates the query needs to know how the listing is grouped.

Aggregations allow analysis of numeric values.

Bob [email protected]

Simple count of number of Road 52 cm road bikes by color

A variation: Finds the distinct number of colors of road bikes (for all sizes).

Page 11: Sql Basic Selects

Bob [email protected]

Establishing order

This is a function that returns the number of the month. Functions will be discussed briefly later.

SUM() is the most frequently used aggregate.

Notice something else new. An ORDER BY clause was added to put the months in order by occurrence.

Page 12: Sql Basic Selects

Bob [email protected]

More on GROUP BY

If the GROUP BY clause is left off

However, if the aggregate applies only to the entire query with no breakdown by any category, then a GROUP BY is not needed.

If the GROUP BY clause is incomplete

Color is not listed!

Page 13: Sql Basic Selects

Bob [email protected]

More on ORDER BY

It is always the last cause. Sorting is the last step for the SQL engine, and the request for it is placed last.

Can be against multiple fields, and any field order.

Does not need to agree with the fields or field order in GROUP BY

Is the only place in a query script where a field alias can be used.

Page 14: Sql Basic Selects

Bob [email protected]

HAVING

Works like a “WHERE” clause but for aggregations

Page 15: Sql Basic Selects

Bob [email protected]

Operators

+ Addition and Concatenation- Subtraction* Multiplication/ Division= Equals

<> Does Not Equal!= Does Not Equal>= Greater Than or Equal To<= Less Than or Equal To> Greater Than< Less Than% Mod operator (also used as wildcard)

Used in WHERE clause . . .

. . . or to calculate a value.

Page 16: Sql Basic Selects

Bob [email protected]

Qualifiers for WHERE

There are some special operators that can be used in the WHERE clause.

These can be used with NOT.

For LIKE there are even some special wildcard characters.

Page 17: Sql Basic Selects

Bob [email protected]

More on qualifiers forWHERE

IN and NOT IN

Be sure your range is “inclusive” when you use between.

Using a single beginning letter will not get the Subcategories that begin with “M.” To get all these add zzz:

With datetime data type

Page 18: Sql Basic Selects

Bob [email protected]

BETWEEN with datetime type

The datetime data type has a time with it. If the data actually records time between will leave out records if you rely on implicit text to datetime conversion. This small test tables shows the problem.

These two queries solve the problem:

Both get all the records.

Page 19: Sql Basic Selects

Bob [email protected]

Wildcards with LIKE

Page 20: Sql Basic Selects

Bob [email protected]

Aliases, Convert and Cast

Some ways to make things more readable are shown in the slide

Many of the slides use field aliases to provide user friendly column labels.

CAST and CONVERT are both used in places. These make query results more presentable.

There are other options (like STR) and CONVERT/CAST setting that you can use. There are specific settings in CONVERT for date formats.

Page 21: Sql Basic Selects

Bob [email protected]

Other Capabilities

Advanced aggregations like CUBE, ROLLUP and PIVOT

Specific Math, String, Date, Text, Image and other Functions

Program controls like IF, WHILE, CASE, etc.

Stored Procedures and Functions

System Functions and Procedures

Triggers and Database Management

Page 22: Sql Basic Selects

Bob [email protected]

Queries Unleashed

Use tables together with JOIN, UNION, EXCEPT and MERGE.

Use sub-queries to help the extend the power of a primary query.

To really tapped the power of a relational database by using multiple tables.

Check out other existing and future slide shows for examples of other SQL functions, capabilities and scripting.