Difference between revisions of "SQL Tutorial"

From SQLZOO
Jump to: navigation, search
Line 2: Line 2:
 
<div class=tutblock>
 
<div class=tutblock>
 
==Tutorial Section==
 
==Tutorial Section==
;1) [[SELECT basics]]:Some simple queries to get you started
+
;1 [[SELECT basics]]:Some simple queries to get you started
;2) [[SELECT from BBC Tutorial|SELECT from BBC]]:In which we query the BBC country profile table.
+
;2 [[SELECT from BBC Tutorial|SELECT from BBC]]:In which we query the BBC country profile table.
;3) [[SELECT from Nobel Tutorial|SELECT from Nobel]]:Additional practice of the basic features using a table of Nobel Prize winners.
+
;3 [[SELECT from Nobel Tutorial|SELECT from Nobel]]:Additional practice of the basic features using a table of Nobel Prize winners.
;4) [[SELECT within SELECT Tutorial|SELECT within SELECT]]:    In which we form queries using other queries.
+
;4 [[SELECT within SELECT Tutorial|SELECT within SELECT]]:    In which we form queries using other queries.
;5) [[SUM and COUNT]]:    In which we apply aggregate functions. [more the same]
+
;5 [[SUM and COUNT]]:    In which we apply aggregate functions. [more the same]
;6) [[The JOIN operation|JOIN]]:    In which we join two tables; ttms and country. [previously music tutorial]
+
;6 [[The JOIN operation|JOIN]]:    In which we join two tables; ttms and country. [previously music tutorial]
;7) [[More JOIN operations]]:    In which we join actors to movies in the Movie Database.
+
;7 [[More JOIN operations]]:    In which we join actors to movies in the Movie Database.
;8) [[Using Null]]:    In which we look at teachers in departments. [previously Scottish Paliament]
+
;8 [[Using Null]]:    In which we look at teachers in departments. [previously Scottish Paliament]
;9) [[Self join]]:    In which we join Edinburgh bus routes to Edinburgh bus routes.
+
;9 [[Self join]]:    In which we join Edinburgh bus routes to Edinburgh bus routes.
 
</div>
 
</div>
 
<div class='refblock'>
 
<div class='refblock'>
  
 
==Reference Section: how to...==
 
==Reference Section: how to...==
;1) [[SELECT Reference|SELECT]]:
+
;1 [[SELECT Reference|SELECT]]:
 
:    How to read the data from a database.  
 
:    How to read the data from a database.  
;2) [[CREATE and DROP Reference|CREATE and DROP]]:
+
;2 [[CREATE and DROP Reference|CREATE and DROP]]:
 
:    How to create tables, indexes, views and other things. How to get rid of them.
 
:    How to create tables, indexes, views and other things. How to get rid of them.
;3) [[INSERT and DELETE Reference|INSERT and DELETE]]
+
;3 [[INSERT and DELETE Reference|INSERT and DELETE]]
 
:    How to put records into a table, change them and how to take them out again.
 
:    How to put records into a table, change them and how to take them out again.
;4) [[DATE and TIME Reference|DATE and TIME]]
+
;4 [[DATE and TIME Reference|DATE and TIME]]
 
:    How to work with dates; adding, subtracting and formatting.
 
:    How to work with dates; adding, subtracting and formatting.
;5) [[Functions Reference|Functions]]
+
;5 [[Functions Reference|Functions]]
 
:    How to use string functions, logical functions and mathematical functions.
 
:    How to use string functions, logical functions and mathematical functions.
;6) [[Users Reference|Users]]
+
;6 [[Users Reference|Users]]
 
:    How to create users, GRANT and DENY access, get at other peoples tables. How to find processes and kill them.
 
:    How to create users, GRANT and DENY access, get at other peoples tables. How to find processes and kill them.
;7) [[Meta Data Reference|Meta Data]]
+
;7 [[Meta Data Reference|Meta Data]]
 
:    How to find out what tables and columns exist. How to count and limit the rows return.
 
:    How to find out what tables and columns exist. How to count and limit the rows return.
 
</div>
 
</div>

Revision as of 03:38, 11 July 2012

Learn about: SQL Server, Oracle, MySQL, DB2, Mimer, PostgreSQL, SQLite and Access.

Tutorial Section

1 SELECT basics
Some simple queries to get you started
2 SELECT from BBC
In which we query the BBC country profile table.
3 SELECT from Nobel
Additional practice of the basic features using a table of Nobel Prize winners.
4 SELECT within SELECT
In which we form queries using other queries.
5 SUM and COUNT
In which we apply aggregate functions. [more the same]
6 JOIN
In which we join two tables; ttms and country. [previously music tutorial]
7 More JOIN operations
In which we join actors to movies in the Movie Database.
8 Using Null
In which we look at teachers in departments. [previously Scottish Paliament]
9 Self join
In which we join Edinburgh bus routes to Edinburgh bus routes.

Reference Section: how to...

1 SELECT
How to read the data from a database.
2 CREATE and DROP
How to create tables, indexes, views and other things. How to get rid of them.
3 INSERT and DELETE
How to put records into a table, change them and how to take them out again.
4 DATE and TIME
How to work with dates; adding, subtracting and formatting.
5 Functions
How to use string functions, logical functions and mathematical functions.
6 Users
How to create users, GRANT and DENY access, get at other peoples tables. How to find processes and kill them.
7 Meta Data
How to find out what tables and columns exist. How to count and limit the rows return.

SQL Resources

SQL User Manuals
[ Mimer | MySQL | Oracle | SQL Server | Access | DB2 | Sybase | Postgres | SQLite ]
Online copies of implementation specific user manuals.
Starting SQL
[ MySQL | Oracle | SQL Server | DB2 | Postgres | Ingres ]
How to connect to the server and execute SQL statements.
Programming with SQL
[ SQL in perl on the Web | SQL from Java ]
SQL standard
[ SQL92 BNF ]
Error Messages
Some error messages from SQL engines