A Gentle Introduction to

needmoreneedmoreΔιαχείριση Δεδομένων

28 Νοε 2012 (πριν από 4 χρόνια και 6 μήνες)

310 εμφανίσεις

A Gentle
Introduction
to

SQL


Tutorials and
reference.



Interactive SQL tutorial.

SQL Server, Oracle, MySQL, PostgreSQL, DB2 and Access.

SELECT statements including joins and functions.


Tutorials

1)
SELECT:


In which we query the CIA World
Factbook.

2)
SELECT in SELECT:


In which we form queries using other
queries.

3)
SUM & COUNT:


In which we
summarise and aggregate
and order.

4)
JOINS:


In which we join actors to movies in the
Movie Database.

5)
OUTER JOINS:


In which we include the parties without
people and the

people outwith parties in
the Scottish Parliament.

6)
SELF JOINS:


In which we join Edinburgh bus routes
to Edinburgh bus routes.

SQL User Manuals


[
MySQL

|
Oracle

| SQL Server

|
Access

|
DB2

|
Po
stgres
]

Online copies of implementation specific
user manuals.

Starting SQL


[
MySQL

|
Oracle

|
SQL Server

]

How to connect to the server and
execute SQL statements.

Programming with SQL


[
SQL in perl on the Web

|
SQL from Java

]

Quick reference: how to...

1) CREATE and DRO
P
:

[
MySQL

|
Oracle

|
SQL Server

|
DB2

|
Access

|
Postgres
]

How to create tables, indexes, views
and other things. How to get rid of
them.

2) INSERT
and DELETE


[
MySQL

|
Oracle

|
SQL Server

|
DB2

|
Access

|
Postgres
]

How to put records into a table, change
them and how to take them out again.

3) D
ATES


[
MySQL

|
Oracle

|
SQL Server

|
DB2

|
Access

|
Postgres
]

How to work with dates; adding,
subtracting and formatting.

4) FUNCTIONS


[
MySQL

|
Oracle

|
SQL Server

|
DB2

|
Access

|
Postgre
i]

How to use string functions, logical
functions and mathematical functions.

5) USERS


[
MySQL

|
Oracle

|
SQL Server

|
DB2

|
Access

|
Postgres
]

How to create users, give them
access, get at other peoples tables.
How to find processes and kill them.

6) META DATA


[
MySQL

|
Oracle

|
SQL Server

|
DB2

|
Access

|
Postgres
]

How to find out what tables and
columns exist. How to count and limit
the rows return.

Assessments



Neeps
: A timetable database

Other information



Online SQL by Gordon Rus
sell




Musicians
: Concerts and compositions



Southwind
: Buying and selling



Dressmaker
: Constr
ucting clothing



Congestion Charging
: Monitoring traffic
(
old questions
)



Weather data
for Southhampton




Album

Tracks

Style
: Music data

Answers



SELEC
T




SELECT in SELECT




SUM & COUNT




JOINS




OUTER JOINS




ACME




OUTER JOINS




Top of the Pops




A Gentle Introduction to XML




What is SQL?




How to use these tutorials.




Links to SQL resources.




Comments from readers.




Clear landmines


Diversions



What's the point of trigonometry?




Top of the Pops: using dates




ACME products: dealing with money.




GISQLOG: users attempts at this
material




Thrice Unfactorised Enigma




Triangular or Square Enigma


By
Andrew Cumming

of the
School of Computing

of
Napier University
, Edinburgh, UK.
1999
-
2003

Original at
http://sqlzoo.net

CIA

World Factbook

This tutorial introduces SQL as a query language. We will

be using the
SELECT

command
on the table cia:

cia(
name
, region, area, population, gdp)

Exercises

Using the
SELECT

statement.


1a

Read the notes about this table.

Issue the command:
SELECT name, region, population FROM cia
. Look at
the output.


SELECT name, region, population FROM cia

S
ubmit Query
R
eset

Before attempting the following questions read this!
How to use
WHERE

to filter records.



2a

Show the name for the four countries that have a
population of at least 200 million. (200 million is
200000000, there are eight zeros)



S
ubmit Query


2b

Give the name and the
per capita

GDP for those
countries with a population of at least 200 million.



S
ubmit Query


2c

Show the
name

and
population

in millions for the
countries of
'South America'




S
ubmit Query


2d

Show the
population

for
'France', 'Germany',
'Italy'




S
ubmit Query


2e

Identify the countries which have names including
the word
'United'




S
ubmit Query

That's enough for the first tutorial.
The next tutorial concerns nested
SELECT

statements
using the same
cia

table.


Exercises

Usi
ng the
SELECT

statement.


1a

Read the notes about this table.

Issue the command:
SELECT name, region, population FROM cia
. Look at
the output.


SELECT name, region, population FROM cia

S
ubmit Query
R
eset

Before attempting the following questions read this!
How to use
WHERE

to filter records.



2a

Show the name for the four countries that have a
population of at least 200 million. (200 mill
ion is
200000000, there are eight zeros)


SELECT name FROM cia WHERE population>200000

S
ubmit Query


2b

Give the name and the
per capita

GDP for those
countries with a population of at least 200 million.


SELECT name, gdp/population FROM cia
WHERE population>200000000

S
ubmit Query


2c

Show the
name

and
population

in millions for the
countries of
'South America'



SELECT name, round(population/1000000) FROM ci
WHERE region='South America'

S
ubmit Query


2d

Show the
population

for
'France', 'Germany',
'Italy'



SELECT name, population FROM cia
WHERE name IN ('France','Germany','Italy')

S
ubmit Query


2e

Identify the coun
tries which have names including
the word
'United'



SELECT name FROM cia
WHERE name LIKE '%United%'

S
ubmit Query