sql basic selects
Post on 05-Jul-2015
214 Views
Preview:
DESCRIPTION
TRANSCRIPT
Bob Litsingerbob.litsinger@gmail.com
SQL Select
. . . the very basics
Bob Litsingerbob.litsinger@gmail.com
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.
Bob Litsingerbob.litsinger@gmail.com
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 Litsingerbob.litsinger@gmail.com
Bob Litsingerbob.litsinger@gmail.com
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 Litsingerbob.litsinger@gmail.com
Bob Litsingerbob.litsinger@gmail.com
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 Litsingerbob.litsinger@gmail.com
Bob Litsingerbob.litsinger@gmail.com
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 Litsingerbob.litsinger@gmail.com
Bob Litsingerbob.litsinger@gmail.com
Introducing WHERE clauses
To limit the Sub Categories to bikes, a WHERE clause is added:
Bob Litsingerbob.litsinger@gmail.com
Bob Litsingerbob.litsinger@gmail.com
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 Litsingerbob.litsinger@gmail.com
Bob Litsingerbob.litsinger@gmail.com
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 Litsingerbob.litsinger@gmail.com
Bob Litsingerbob.litsinger@gmail.com
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 Litsingerbob.litsinger@gmail.com
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).
Bob Litsingerbob.litsinger@gmail.com
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.
Bob Litsingerbob.litsinger@gmail.com
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!
Bob Litsingerbob.litsinger@gmail.com
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.
Bob Litsingerbob.litsinger@gmail.com
HAVING
Works like a “WHERE” clause but for aggregations
Bob Litsingerbob.litsinger@gmail.com
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.
Bob Litsingerbob.litsinger@gmail.com
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.
Bob Litsingerbob.litsinger@gmail.com
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
Bob Litsingerbob.litsinger@gmail.com
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.
Bob Litsingerbob.litsinger@gmail.com
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.
Bob Litsingerbob.litsinger@gmail.com
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
Bob Litsingerbob.litsinger@gmail.com
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.
top related