SQL Introduction and Tutorial 2

[jroyce1]

If you need to freshen up your basics on SQL or if you need to train a new employee on a gradient approach to SQL, here is a good introduction and tutorial.

An SQL Introduction

Personal blog: The Code Blog

 

[GeertVerhoeven]

This is also a good starting place:

http://www.w3schools.com/sql/sql_intro.asp

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[Golom]

Hmmmm

The COUNT keyword is used in the SELECT, [red]GROUP BY[/red] or HAVING clauses.

Never heard of that one. In fact access says "Cannot have an aggregate function in Group By.

 

[jroyce1]

Check this out:

SELECT column1, COUNT(1)
FROM table
WHERE criteria
GROUP BY column1
HAVING COUNT(1) > 5;

Give that a try...


Personal blog: The Code Blog

 

[GeertVerhoeven]

jroyce, the query you provided uses a Count in the having clause and not in the group by clause. What Golom means that it is not possible to have an aggregate in a group by clause which is correct.

So this is not valid:
SELECT column1, COUNT(1)
FROM table
WHERE criteria
GROUP BY COUNT(1)

Geert Verhoeven
Consultant @ Ausy Belgium

My Personal Blog

 

[jroyce1]

Ahh...I see what you mean --- GROUP BY does not the COUNT keyword. Thanks for the correction. I fixed this on the Blog post.

Personal blog: The Code Blog

 

[Golom]

Please take this in the spirit in which it's intended. You have put together a good basic introduction to SQL but there a few typos and not-quite-accurate statements in it. Use as you see fit.

INTO

The INTO keyword indicates that you are inserting records into another table. For example in Microsoft Access you can insert all records from one table into another table by using the following: SELECT * INTO FROM

Not quite. You are creating a new table and inserting records into it. This does not work for inserting records into an existing table. Also, the example syntax should be "SELECT * INTO [red]SomeNewTable[/red] FROM ...". As you have it, "FROM" will be taken as the new table name.

In the description of LIKE

The syntax for is as follows

should be

The syntax for [red]it[/red] is as follows

BETWEEN command ...

Technically, BETWEEN is not a command. It is an SQL predicate.

SELECT *
FROM address_list
WHERE (state = 'AK' AND eye_color = 'blue') OR state = 'CA'

If you don't use the parentheses your query will return all people in Alaska or anyone with blue eyes or anyone in California.

In general you are correct but your example is unfortunate because it will return the same result with or without the parentheses because AND is processed before OR and AND occurs first in this example.

The GROUP BY clause specifies is you are grouping ...

should be

The GROUP BY clause specifies [red]if[/red] you are grouping ...

... column listing to order by (optional)

No it isn' optional. An Order By without a list of columns or ordinal positions is invalid.

This means you want to give a count of ALL columns.

No. It gives a row count ... not a column count.

 

[jroyce1]

Golom, thanks for the detailed tips and I fixed these points in my blog!

Personal blog: The Code Blog

 

[r937]

golom, nice sleuthing, star from me too

this thread just proves once again that it's harder to write a basic tutorial than an advanced tutorial



r937.com | rudy.ca

 

[Golom]

Just a few more ... and I promise to stop bugging you after this.

SELECT
The SELECT statement indicates that you which to query and retrieve information from a database. The select_list specifies the type of information (or column names) to retrieve. The keyword ALL or the wildcard character asterisk (*) could be used to signify all columns in the database.

should be

SELECT
The SELECT statement indicates that you [red]wish[/red] to query and retrieve information from a database. The select_list specifies the type of information (or column names) to retrieve. The keyword ALL or the wildcard character asterisk (*) could be used to signify all columns in the [red]table[/red].

INTO ...
SELECT * newtablename INTO FROM tablename

should be

INTO ...
SELECT * [red]INTO newtablename[/red] FROM tablename

OR Clause SQL

OR ...
SELECT *
FROM address_list
WHERE state = 'CA' OR (state = 'AK' AND eye_color = 'blue')

Unfortunately this also would work the same way with or without parentheses. This, on the other hand does need them.

OR ...
SELECT *
FROM address_list
WHERE (eye_color = 'blue' OR eye_color = 'hazel') AND state = 'CA'

With parentheses it returns those in California with blue OR hazel eyes.
Without parentheses it returns those in California with hazel eyes plus everyone with blue eyes regardless of state.

GROUP BY
The GROUP BY clause specifies if you are grouping (or aggregating) any of the columns in your SELECT statement. For example if you want to display a count of all addresses in Dublin you would use the GROUP BY clause to group the results by City (optional).

I see what you're getting at but the reference to "Dublin" suggests that Grouping By "city" will return the results for "Dublin". It won't. It will return the results for ALL cities in the table. Further, the placement of "(optional)" suggests that the field "City" is optional. It isn't. The Group By clause may be optional but, if specified, then the field-list is required.

 

[JarlH]

ALL <> *

INTO is not a part of the SELECT statement as described. (SELECT col1 INTO :hv1, col2 INTO :hv2 etc.)

The AS keyword is optional.

COUNT(1) in the ORDER BY clause is not valid. All expressions in the ORDER BY clause must contain a valid column reference.

 

[Golom]

You are correct in that INTO is a separate optional clause that, if used, follows the last field in SELECT and precedes the FROM clause.

Select * INTO myNewTable From myOldTable

will copy all fields(*) and all records from "myOldTable" to "myNewTable".

Certainly the syntax that you illustrated does not work. Only one INTO clause is allowed.

 

[JarlH]

Oops, you're absolutely right, thanks! Of course only one single INTO clause is allowed. i.e it should have been:

SELECT col1, col2 INTO :hv1, :hv2 FROM sometable

(I realize I shouldn't be posting without reading what I've written! Shame on me...)

Note that SELECT * INTO myNewTable FROM myOldTable isn't ANSI SQL. The ANSI way is

INSERT INTO myNewTable SELECT * FROM myOldTable

 

[r937]

actually, it isn't quite the same -- you need to CREATE myNewTable first



r937.com | rudy.ca

 

[Golom]

You're correct again. SELECT * INTO ..." is not ANSI SQL. Guess I got hung up on what jroyce1 was doing in his blog.

 

[jroyce1]

SELECT * INTO sometable FROM someothertable works in SQL Server as well as MS Access SQL view. Doesn't work in Oracle SQL though where you must create the table before inserting records into it.

Personal blog: The Code Blog

 

[jroyce1]

Golom, Thanks for the corrections. I fixed these now.

Personal blog: The Code Blog

 

[Golom]

jroyce1

The "INTO" clause is what is called an SQL extension and MS has incorporated it into their RDBMS offerings. Oracle has not. Don't know about things like MySQL or SyBase (Rudy?)

If you are going to upgrade your SQL primer, it is probably wise to make the distinction between ANSI SQL and SQL as implemented by various vendors.

You could just go to

http://www.r937.com/

and ask him what the differences are!

 

[r937]

INTO works in sybase as far as i know (i can't test it, because i don't have sybase, and i can't be bothered even looking into whether or not they offer a download)

doesn't work in mysql, though

in mysql, there is a different extension -- you can use a SELECT in the CREATE TABLE statement!!

CREATE TABLE myNewTable ( columns )
SELECT columns FROM myOldTable





r937.com | rudy.ca

 

[JarlH]

That's almost ANSI SQL nowadays, this is the correct ANSI way:

CREATE TABLE myNewTable ( columns ) AS
(SELECT columns FROM myOldTable) WITH [NO] DATA

SQL-2003, extension T172.