Database SQL Analyser: Tutorial 1 (SQL statements)[Back] [SQL Test] [Database] This page shows how SQL commands can be applied to a database: |
Database contents
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 |
Tutorial
Which commands are used for the following:
- View the database in ascending order of Test 1 Solution
- View the database in descending order of Test 2 Solution
- Average of Test 1 for male students Solution Average=62.5%
- Average of Test 1 for female students Solution Average=45%
- Average of Test 2 for male students Solution Average=37.5%
- Average of Test 2 for female students Solution Average=40%
- Average of Test 1 for all students (apart from those with the Surname of Smith) Solution Average=72.5% 9 for Test 1 for all non-Smtith surnames
- Surname and Test results of students aged between 40 and 55 Solution Bert Smith and Freddy Smith
- Average of female and average for male students for Test 2 Solution Female: 40%, Male: 37.25%
Samples
- Order by Surname
- Order by Surname (Desending)
- Order by Age
- Order by Gender
- First name of male persons
Presentation
SQL Commands used
- 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 TOP 1 (Surname) FROM db1 WHERE (Gender='M') Try
- SELECT Top 1 (Surname) FROM db1 WHERE (Gender='M') ORDER BY Surname DESC 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