SQL

Outline

SQL (Structured Query Language) has managed to sustain its interest in the Computing industry, having been first specified in 1970 by Edgar F. Codd’s. While software languages such as C, Pascal, and FORTRAN have all peaked and waned, it is SQL that has sustained itself, and is now one of the most ask-for skill in Computing. Why does it work so well? Well it’s because the designers got it right first time, and there has been very little need to change the overall structure of the language.

I currently use ASP.MVC for my Web development, and which hides a great the actual requests to the database, but underneath it is still using SQL requests. It is thus important for any computing student to understand its scope, and how it is used to get data from data sources. With this in mind I’ve created a range of SQL requests which illustrate the basic principles of SQL.

Simple Examples

I have created a simple table:

ID FirstName Surname FullAddress Test 1 Test 2 Gender Age
1 Fred Smith 10 Fake Street 10 20 M 30
2 Bert Smith 1 Round Lane 30 40 M 40
3 Bob Malcolm 5 Square Road 100 30 M 22
4 Eve Almond 11 Full Lane 45 40 F 56
5 Freddy Smith 111 Edinburgh Road 50 50 M 43

Using this, I have created a number of sample SQL commands which illustrate some basic features:

SELECT * FROM db1 Try
SELECT * FROM db1 ORDER BY Surname Try
SELECT * FROM db1 ORDER BY Surname DESC Try
SELECT * FROM db1 ORDER BY Age Try
SELECT * FROM db1 ORDER BY Gender Try
SELECT FirstName FROM db1 WHERE (Gender='M') Try
SELECT FirstName FROM db1 WHERE (Gender='F') Try
SELECT Surname FROM db1 WHERE (Gender='M') Try
SELECT Surname FROM db1 WHERE (Gender='F') Try
SELECT First(Surname) FROM db1 WHERE (Gender='M') Try
SELECT Last(Surname) FROM db1 WHERE (Gender='M') Try
SELECT Max(Age) FROM db1 Try
SELECT Min(Age) FROM db1 Try
SELECT FirstName FROM db1 WHERE (Surname='Smith' OR Surname='Almond') Try
SELECT Avg([Test 1]) FROM db1 Try
SELECT Avg([Test 1]) FROM db1 WHERE (Age>30) Try
SELECT Sum([Test 1]) FROM db1 Try
SELECT Sum([Test 1]) FROM db1 WHERE (Age>30) Try
SELECT Count(FirstName ) FROM db1 WHERE (Age<30) Try
SELECT Count(FirstName ) FROM db1 WHERE (Age>30) Try
SELECT Count(FirstName ) FROM db1 WHERE (Age=30) Try
SELECT FirstName,Surname FROM db1 Try
SELECT DISTINCT Surname FROM db1 Try
SELECT FirstName,Surname,Age,[Test 1] FROM db1 WHERE (Gender='M') Try
SELECT FirstName,Surname,[Test 1],[Test 2] FROM db1 WHERE (Gender<>'M') Try
SELECT FirstName,Surname,[Test 1],Age FROM db1 WHERE Age BETWEEN 10 AND 50 Try
SELECT FirstName,Surname,[Test 1],Age FROM db1 WHERE Age IN (22,56,33) Try
SELECT FirstName,Surname,[Test 1],Age FROM db1 WHERE Surname LIKE 'Sm%' Try
SELECT FirstName,Surname,[Test 1],Age FROM db1 WHERE Surname LIKE '[AaSsUu]%' Try
SELECT FirstName,Surname,[Test 1],Age FROM db1 WHERE Surname NOT LIKE 'Sm%' Try
SELECT Gender,AVG([Test 1]) FROM db1 GROUP BY Gender Try
SELECT FirstName,Surname,[Test 1] from db1 where Surname in ( 'Smith', 'Almond') ORDER BY Surname Try

Other examples

These examples use the following table:

ID Surname Firstname Age Gender Interest Country
7 Smith Fred 43 Male Soccer Scotland
8 Balley Bert 44 Male Rugby England
9 Allan Karen 20 Female Tennis USA
10 Carroll Kirk 25 Female Soccer Canada
11 Smith Allan 50 Male Golf Scotland
12 MacDonald Iain 22 Male Golf Scotland
13 McAlpine Carol 33 Female Tennis Mexico
14 Wilson Mike 33 Male Tennis England
15 Smith Karen 40 Female Golf USA
16 Buchan Martin 20 Male Football Scotland

For this you can try the following:

 SELECT * FROM Table1 Try
 SELECT * FROM Table1 ORDER BY Surname Try
 SELECT * FROM Table1 ORDER BY Surname DESC Try
 SELECT * FROM Table1  ORDER BY Age Try
 SELECT * FROM Table1  ORDER BY Gender Try
 SELECT * FROM Table1  ORDER BY Interest Try
 SELECT Surname,Interest,Gender FROM Table1  WHERE (Gender='Male') Try
 SELECT Surname,Interest,Gender FROM Table1  WHERE (Gender='Female') Try
 SELECT Surname,Interest,Age FROM Table1  WHERE (Age>30) Try
 SELECT Surname,Interest FROM Table1  WHERE (Interest LIKE 'S%') Try
 SELECT Max(Age) FROM Table1 Try
 SELECT Min(Age) FROM Table1 Try
 SELECT FirstName,Country FROM Table1 WHERE (Country='USA' OR Country='Canada') Try
 SELECT FirstName,Country FROM Table1 WHERE NOT (Country='USA' OR Country='Canada') Try
 SELECT FirstName,Country,Interest FROM Table1 WHERE (Country='USA' AND Interest='Golf') Try
 SELECT FirstName,Country,Interest FROM Table1 WHERE (Country='USA' OR Interest='Golf') Try
 SELECT Avg(Age) FROM Table1 Try
 SELECT Count(FirstName) FROM Table1 WHERE (Interest='Soccer') Try
 SELECT FirstName,Surname,Interest,Age FROM Table1 WHERE Age IN (22,40,42,56,33) Try
 SELECT FirstName,Surname,Interest,Age FROM Table1 WHERE Interest IN ('Golf','Soccer','Rugby') Try
 SELECT FirstName,Surname,Interest,Age FROM Table1 WHERE Interest NOT IN ('Golf','Soccer','Rugby') Try
 SELECT FirstName,Surname,Interest,Age FROM Table1 WHERE Interest LIKE 'R%' Try
 SELECT FirstName,Surname,Interest,Age FROM Table1 WHERE Interest LIKE '[RrSsUu]%' Try
 SELECT FirstName,Surname,Interest,Age FROM Table1 WHERE Interest NOT LIKE 'S%' Try
 SELECT * FROM Table2 Try
 SELECT * FROM Table1 UNION SELECT * FROM Table2 Try

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s