Database SQL Analyser: Tutorial 2[Back] [SQL Test] [Database] This page shows how SQL commands can be applied to a database: |
Database contents
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 |
Presentation
Tutorial
Which commands are used for the following:
- View the database from Table 1 Solution
- View the database from Table 2 Solution
- View the database from Table 1 and Table 2 Solution
- View the database in ascending order of the country Solution
- Average age of those who are an interest in Golf, Soccer, or Rugby Solution
- The number of people interested in Rugby Solution
- The number of people not interested in Rugby Solution
Samples
SQL Commands used
- 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