Practice sql to learn SQL syntax

By Sergey Skudaev

You can use this web page to practice writing SQL select queries for my book Continue Learning SQL by Examples. The database structure for this book examples is slightly different than the one for my first book Learn Sql by Example that is why I created a separate webpage.

Unfotunately, a company, I rent hosting from, has 5.6.32 MySQL version. This version does not support advance SQL features. To run advance SQL examples you need to install your own MySQL to your PC. It is not hard.

Of cause, SQL queries are not limited to select statement. There is update, delete and insert SQL statement, but SELECT SQL statement is the most important and most challenging to learn. Why is SELECT SQL statement most important? Because to write a correct update or delete SQL statement, you must be able to select correct database record that you want to update or delete.

Let us imagine that we have a computer school where we teach different computer programming classes: Java, Visual Basic, C++. For our computer school management, we created a database with the following tables.

One table is for teachers, one for students and one for computer courses. To link tables to each other, we created teachers-schedule table and students-courses table. Fields for these tables are shown in the table below:

For example, if we want to know which student is older than 21 and we write a query:

SELECT * FROM students WHERE age > 21

Or we want to get information a student whose first name is Michael:

SELECT * FROM students WHERE firstname='Michael'

You see how it is easy. Let us try something more complicated.

Students who take a Java class:

SELECT students.firstname, students.lastname from students JOIN student_course ON
students.studentid=student_course.studentid JOIN courses ON
student_course.courseid=courses.courseid
WHERE courses.coursename='Java'

The teacher name who teaches Java class:

SELECT t.firstname, t.lastname FROM teachers t JOIN schedule s ON t.teacherid=s.teacherid
JOIN courses c ON s.courseid=c.courseid WHERE c.coursename='Java'

Copy the query, paste it in the text area and click Submit button. The query result will be displayed!"

A visitor of this page has only permission for executing select queries.

Try to write queries on your own. To find out what data is in any table execute query "SELECT * FROM [table name]"

Write queries:
1. Find which courses Michael Murphy is teaching.
2. Find the schedule when the teaher Michael Murphy has classes.
3. Find the schedule when the student Michael has classes.
3. Find what average student age in Java class is.
4. Find at what time the PHP class starts.

SELECT t.lastname,
       c.coursename, 
       s.starttime,
       s.endtime
FROM (teachers t
      INNER JOIN schedule s ON t.teacherid=s.teacherid)
INNER JOIN courses c ON s.courseid=c.courseid
ORDER BY coursename

fcount=4

I wrote an eBook base on this page. It includes about 100 query examples:
Learn SQL Programming By Examples

Select a query

Select a request and click the Execute button Display all records from students table Display students full names and age, sorted by age Display full name and age for students who is 23 year-old Display full name and age for students who are older than 23, sorted by lastname. Display full name and age for students who are older than 23 but younger than 30, sorted by age. Display full name and age for students whose age between 23 and 30, sorted by age. Display number of students for each age. Display number of students for each age, sorted by age. Display number of students for each age, sorted by age in descending order. Display full name and age for students whose age greater than average age, sorted by age. Display full name and age for students whose age less than average age, sorted by age in descending order. Find students avarege age Display full name and age for the oldest student Display full name and age for the youngest student Display students first name and number of its occurrence, sorted by first name. Display student start date and number of each start date occurrence, sorted by start date. Display student start date and number of each start date occurrence, sorted by start date in descending order. Display full name for students whose first name starts with M. Display full name for students whose first name 5th letter is a. Display full name for students whose firstnames have letter M in the 1st position. Display full name for students whose firstnames have (small L) in the 7th position. Display students full name sorted by length of their firstname in descending order Display first names for students whose firstname occurs more than once. Display number of columns for students table. Display column names for students table. Display column names, column data type and other columns attributes for students table. Display indexes for students table.

Select a request and click the Execute button Display all records from students table Display students full names and age, sorted by age Display full name and age for students who is 23 year-old Display full name and age for students who are older than 23, sorted by lastname. Display full name and age for students who are older than 23 but younger than 30, sorted by age. Display full name and age for students whose age between 23 and 30, sorted by age. Display number of students for each age. Display number of students for each age, sorted by age. Display number of students for each age, sorted by age in descending order. Display full name and age for students whose age greater than average age, sorted by age. Display full name and age for students whose age less than average age, sorted by age in descending order. Find students avarege age Display full name and age for the oldest student Display full name and age for the youngest student Display students first name and number of its occurrence, sorted by first name. Display student start date and number of each start date occurrence, sorted by start date. Display student start date and number of each start date occurrence, sorted by start date in descending order. Display full name for students whose first name starts with M. Display full name for students whose first name 5th letter is a. Display full name for students whose firstnames have letter M in the 1st position. Display full name for students whose firstnames have (small L) in the 7th position. Display students full name sorted by length of their firstname in descending order Display first names for students whose firstname occurs more than once. Display number of columns for students table. Display column names for students table. Display column names, column data type and other columns attributes for students table. Display indexes for students table.

Description of the request:

Query:SELECT t.lastname, c.coursename, s.starttime, s.endtime FROM (teachers t INNER JOIN schedule s ON t.teacherid=s.teacherid) INNER JOIN courses c ON s.courseid=c.courseid ORDER BY coursename

* Note, that when you copy a query from a webpage or notepad window to MySQL client command prompt or in the query textarea on this webpage, if a tab character present after a line within the query, it causes error. Tab characters can cause syntax errors in SQL queries, especially when copied from formatted text like this. Different systems and text editors may interpret tab characters differently, leading to unexpected spacing and formatting issues.

Here are some tips to avoid this issue:

Manual Formatting:

Carefully copy and paste the query, making sure to remove any extra spaces or tabs. Use consistent indentation with spaces instead of tabs.

Text Editor Settings:

Configure your text editor to use spaces instead of tabs for indentation. Check if your text editor has any specific settings for SQL syntax highlighting or formatting that might affect tab character interpretation.