Difference between revisions of "SQL Tutorial"

From SQLZOO
Jump to: navigation, search
 
(95 intermediate revisions by 5 users not shown)
Line 1: Line 1:
−
<p class=slogan><span>Learn about:</span> SQL Server, Oracle, MySQL, DB2, Mimer, PostgreSQL, SQLite <span>and</span> Access.</p>
+
__NOTOC__
 +
<div style='position:absolute;right:5px'>{{Languages}}</div>
 +
<p class=slogan><span>Learn SQL using:</span> SQL Server, Oracle, MySQL, DB2, <span>and</span> PostgreSQL.</p>
 
<div class=tutblock>
 
<div class=tutblock>
−
==Tutorial Section==
+
==Tutorials: Learn SQL in stages==
−
; [[SELECT basics|1 SELECT basics]]:Some simple queries to get you started
+
; [[SELECT basics|0 SELECT basics]]:Some simple queries to get you started
−
; [[SELECT from BBC Tutorial|2 SELECT from BBC]]:In which we query the BBC country profile table.
+
; [[SELECT names|1 SELECT name]]:Some pattern matching queries
 +
; [[SELECT from WORLD Tutorial|2 SELECT from World]]:In which we query the World country profile table.
 
; [[SELECT from Nobel Tutorial|3 SELECT from Nobel]]:Additional practice of the basic features using a table of Nobel Prize winners.
 
; [[SELECT from Nobel Tutorial|3 SELECT from Nobel]]:Additional practice of the basic features using a table of Nobel Prize winners.
 
; [[SELECT within SELECT Tutorial|4 SELECT within SELECT]]:    In which we form queries using other queries.
 
; [[SELECT within SELECT Tutorial|4 SELECT within SELECT]]:    In which we form queries using other queries.
 
; [[SUM and COUNT|5 SUM and COUNT]]:    In which we apply aggregate functions. [[The_nobel_table_can_be_used_to_practice_more_SUM_and_COUNT_functions.|more the same]]
 
; [[SUM and COUNT|5 SUM and COUNT]]:    In which we apply aggregate functions. [[The_nobel_table_can_be_used_to_practice_more_SUM_and_COUNT_functions.|more the same]]
−
; [[The JOIN operation|6 JOIN]]:    In which we join two tables; ttms and country. [[Music_Tutorial |previously music tutorial]]
+
; [[The JOIN operation|6 JOIN]]:    In which we join two tables; game and goals. [[Music_Tutorial |previously music tutorial]]
 
; [[More JOIN operations|7 More JOIN operations]]:    In which we join actors to movies in the Movie Database.
 
; [[More JOIN operations|7 More JOIN operations]]:    In which we join actors to movies in the Movie Database.
−
; [[Using Null|8 Using Null]]:    In which we look at teachers in departments. [previously Scottish Paliament]
+
; [[Using Null|8 Using Null]]:    In which we look at teachers in departments. [[Scottish_Parliament |previously Scottish Parliament]]
 +
; [[NSS Tutorial|8+ Numeric Examples]]:    In which we look at a survey and deal with some more complex calculations.
 
; [[Self join|9 Self join]]:    In which we join Edinburgh bus routes to Edinburgh bus routes.
 
; [[Self join|9 Self join]]:    In which we join Edinburgh bus routes to Edinburgh bus routes.
 +
; [[Tutorial_Quizzes|10 Tutorial Quizzes]]:    Test your knowledge with multiple choice quizzes
 +
; [[DDL Student Records|11 Tutorial Student Records]]: Creating a database
 +
; [[DDL General Election|12 Tutorial DDL]]: Instructions on how to import data and analyse it in mysql
 
</div>
 
</div>
 
<div class='refblock'>
 
<div class='refblock'>
  
−
==Reference Section: how to...==
+
==Reference: 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]]
Line 29: Line 36:
 
;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.
 +
;8 [[Hacks Reference|SQL Hacks]]
 +
:  Some SQL Hacks, taken from "SQL Hacks" published by O'Reilly
 +
;9 [[SQL with PHP|Using SQL with PHP on Amazon EC2 servers]]
 +
:  Video tutorials showing how to run MySQL, PHP and Apache on Amazon's EC2 cloud servers.
 +
:  Simpler instructions for students following module [[INF08104]] at Edinburgh Napier University
 +
;10 [[Transactions|An introduction to transactions]]
 +
:  Video tutorials showing how sessions can interfere with each other and how to stop it.
 +
;11 [[SQL with C#|Using SQL with C# in Visual Studio]]
 +
:  Video tutorials showing how to connect to a database in Visual Studio.
 +
 
</div>
 
</div>
 +
<div class='resblock' style='clear:left'>
 +
<div style='clear:left'></div>
 +
 +
==Assessments: More involved examples for confident users==
 +
Each assessment includes 15 questions graded easy, medium and hard.
 +
;[[Module Feedback]]
 +
:    Responses from students on their learning experience.
 +
;[[Help Desk]]
 +
:    Calls to a support desk
 +
;[[Guest House]]
 +
:    Managing bookings for a hotel
 +
;[[AdventureWorks|Adventure Works]]
 +
:    An example based on a wholesaler (based on MicroSoft's sample database)
 +
;[[Neeps|University Timetables]]
 +
:    Schedules for lectures and tutorials
 +
;[[Musicians]]
 +
:    Bands, Concerts, Performances
 +
;[[Dressmaker]]
 +
:    Making and ordering dresses
 +
;[[Congestion Charging]]
 +
:    Monitoring and charging cars going in and out of London
 +
 +
==Challenges==
 +
*[[White Christmas]]
 +
 
==SQL Resources==
 
==SQL Resources==
−
;SQL User Manuals:   [ Mimer | MySQL | Oracle | SQL Server | Access | DB2 | Sybase | Postgres | SQLite ]
+
*[http://sqlzoo.net/wiki/SELECT_basics?answer=1 Select Basics - Answers]
 +
*[http://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial?answer=1 Select from World - Answers]
 +
*See if you can spot the pattern
 +
;SQL User Manuals: [http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Mimer_SQL_Engine.htm Mimer][http://dev.mysql.com/doc/refman/5.1/en/select.html MySQL] [http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm Oracle] [http://msdn.microsoft.com/en-us/library/ms189826%28v=sql.90%29.aspx SQL Server] [http://office.microsoft.com/en-us/assistance/CH062526881033.aspx Access] [http://pic.dhe.ibm.com/infocenter/db2luw/v9r7/index.jsp DB2] [http://sybooks.sybase.com/sybooks/sybooks.xhtml Sybase] [http://www.postgresql.org/docs/current/static/index.html Postgres] [http://www.sqlite.org/lang.html SQLite]
 
:Online copies of implementation specific user manuals.
 
:Online copies of implementation specific user manuals.
−
;Starting SQL:    [ MySQL | Oracle | SQL Server | DB2 | Postgres | Ingres ]
+
;Starting SQL:    [ [[Starting MySQL|MySQL]] | [[Starting Oracle|Oracle]] | [[Starting SQL Server|SQL Server]] | [[Starting DB2|DB2]] | [[Starting Postgres|Postgres]] | [[Starting Ingres|Ingres]] ]
 
:    How to connect to the server and execute SQL statements.
 
:    How to connect to the server and execute SQL statements.
−
;Programming with SQL:    [ SQL in perl on the Web | SQL from Java ]
+
;Programming with SQL:    [ [[SQL in perl on the Web]] | [[SQL from Java]] ]
−
;SQL standard:    [ SQL92 BNF ]
 
 
;Error Messages
 
;Error Messages
−
:    Some error messages from SQL engines
+
:    [[SQL Error Messages|Some error messages from SQL engines]]
 +
;Temporary Assessment section, before the main menu is finished.
 +
:[[Assessment Temp]]
 +
</div>
 +
<div style="margin-left:1em;clear:both">
 +
[[To do list]]
 +
</div>
 +
<!--
 +
{{Languages}}
 +
-->

Latest revision as of 17:52, 31 May 2018

Language:Project:Language policy English  • 日本語 • 中文

Learn SQL using: SQL Server, Oracle, MySQL, DB2, and PostgreSQL.

Tutorials: Learn SQL in stages

0 SELECT basics
Some simple queries to get you started
1 SELECT name
Some pattern matching queries
2 SELECT from World
In which we query the World 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; game and goals. 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 Parliament
8+ Numeric Examples
In which we look at a survey and deal with some more complex calculations.
9 Self join
In which we join Edinburgh bus routes to Edinburgh bus routes.
10 Tutorial Quizzes
Test your knowledge with multiple choice quizzes
11 Tutorial Student Records
Creating a database
12 Tutorial DDL
Instructions on how to import data and analyse it in mysql

Reference: 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.
8 SQL Hacks
Some SQL Hacks, taken from "SQL Hacks" published by O'Reilly
9 Using SQL with PHP on Amazon EC2 servers
Video tutorials showing how to run MySQL, PHP and Apache on Amazon's EC2 cloud servers.
Simpler instructions for students following module INF08104 at Edinburgh Napier University
10 An introduction to transactions
Video tutorials showing how sessions can interfere with each other and how to stop it.
11 Using SQL with C# in Visual Studio
Video tutorials showing how to connect to a database in Visual Studio.

Assessments: More involved examples for confident users

Each assessment includes 15 questions graded easy, medium and hard.

Module Feedback
Responses from students on their learning experience.
Help Desk
Calls to a support desk
Guest House
Managing bookings for a hotel
Adventure Works
An example based on a wholesaler (based on MicroSoft's sample database)
University Timetables
Schedules for lectures and tutorials
Musicians
Bands, Concerts, Performances
Dressmaker
Making and ordering dresses
Congestion Charging
Monitoring and charging cars going in and out of London

Challenges

SQL Resources

SQL User Manuals
MimerMySQL 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 ]
Error Messages
Some error messages from SQL engines
Temporary Assessment section, before the main menu is finished.
Assessment Temp

To do list

Retrieved from "https://sqlzoo.net/w/index.php?title=SQL_Tutorial&oldid=39523"