Ph: 220082007
Quick Ref.
Interactive SQL tutorial, learn about: SQL Server, Oracle, MySQL, DB2, Mimer, PostgreSQL, SQLite and Access.
Sample Query.

Population of more than 200 million.

Show the name and the population in descending order of population for each country that has a population of more than 200000000


SELECT name, population
  FROM bbc
 WHERE population>200000000
ORDER BY population DESC
Results
Sample Subquery.

Twice the area of India.

Show the name, region and area for each country with an area of two times the area of India.


SELECT name, area
  FROM bbc
 WHERE area>=
  (SELECT 2*area FROM bbc WHERE name='India')
Results

Tutorial Section

0) SELECT:
Some simple queries to get you started
1) SELECT:
In which we query the BBC country profile table.
1b) More practice with SELECT:
Additional practice of the basic features using a table of Nobel Prize winners.
2) SELECT in SELECT:
In which we form queries using other queries.
3) SUM and COUNT:
In which we apply aggregate functions. [more the same]
4) The JOIN operation:
In which we join two tables; ttms and country. [previously music tutorial]
5) More JOIN operations:
In which we join actors to movies in the Movie Database.
6) Using Null:
In which we look at teachers in departments. [previously Scottish Paliament]
7) Self join:
In which we join Edinburgh bus routes to Edinburgh bus routes.
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 ]

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.
Error Messages
Some error messages from SQL engines

Answers

Answers to the tutorial questions.

SELECT SELECT in SELECT SUM and COUNT JOIN I JOIN II OUTER JOIN (teachers) OUTER JOIN (Parliament) ACME SELF JOIN Top of the Pops

Assessments

These exercises involve larger databases. Answers to these questions are not available to students.

AdventureWorks: Flogging sports gear. Assessment for CO22008 2007/8 Neeps: A timetable database Musicians: Concerts and compositions Southwind: Buying and selling Dressmaker: Constructing clothing Congestion Charging: Monitoring traffic (old questions) Weather data for Southhampton Album Tracks Style: Music data

Other information

SQL Formatter: Use sqlparser.com to convert any SQL into a readable and colorful style Exploit SQL Injection Attack: Practice Hacking using an SQL Injection attack against our test system. Relational Database Theory including a quiz. What is SQL? About SQLzoo. How to say SQL.

Diversions

What's the point of trigonometry? Top of the Pops: using dates ACME products: dealing with money. Thrice Unfactorised Enigma Triangular or Square Enigma
[SQLzoo in German] [SQLzoo in Japanese 日本語]

By Andrew Cumming of the School of Computing of Napier University, Edinburgh, UK. 1999-2009

You are viewing a mobilized version of this site...
View original page here

Mobilized by Mowser Mowser