tl;dr

If you are looking for a good way to acquire SQL knowledge or to deepen it without effort, I can recommend this online database. Here is a description of its structure. Here you can run SQL queries. You don’t need a database or a client tool for this - great!

Scroll down for a list of tasks to practice SQL.

Long version

A friend of mine is very interested in computer science, but didn’t really know how to get into the subject. He asked me if I could help him with this. So I created a github repository and showed him how to use git.

We have been using this repository for exchange ever since. At regular intervals I publish tasks there, which he then works on. We started with Java as a very common programming language, but on the agenda is an all-round look at the most important technologies in IT. Thus, of course, SQL must not be missing.

Since he had neither set up a database nor used Docker, I was looking for a simple way to explain the basics of SQL to him. I ended up with this online database. The advantage: No installation is necessary.

The database represents a typical scenario: A database with products, orders, users, etc. The structure is described here: https://www.dofactory.com/sql/sample-database. Of course, all of this would be pointless without the ability to execute SQL queries. This can be done directly via the browser: https://www.dofactory.com/sql/sandbox.

A great thing!

The following is a list of tasks for learning SQL. Have fun!

  • Execute the following statement:
    SELECT SUM (TotalAmount) FROM [Order]
    
  • Execute the following statement:
    SELECT Firstname, LastName FROM Customer
    
  • Execute the following statement:
    SELECT Firstname, LastName FROM Customer WHERE Firstname LIKE '%A%'
    
  • Execute the following statement:
    SELECT * FROM [Order]
    
  • Question 1: Why is order here in brackets?
  • Execute the following statement:
      SELECT C.FirstName, C.LastName,
              '$' + CONVERT(varchar, CAST(O.TotalAmount AS Money), 1) AS Total,
              O.OrderNumber
      FROM [Order] O
      JOIN Customer C ON O.CustomerId = C.Id
      ORDER BY O.OrderDate DESC
    
  • Question 2: Can you explain this statment?
  • Execute the following statement:
    SELECT SUM(TotalAmount) FROM [Order]
    
  • Question 3: What is a statement to get the average amount of the TotalAmount field of the Order table?
  • Execute the following statment:
    SELECT c.*,o.totalAmount FROM Customer c
    JOIN [Order] o
    ON o.CustomerId=c.Id
    
  • Execute the following statement:
    SELECT c.*,o.totalAmount FROM Customer c
    JOIN [Order] o
    ON o.CustomerId=c.Id
    Order by totalAmount DESC
    
  • Question 4: What does DESC do?
  • Question 5: Can you explain the statement below?
    SELECT c.Firstname, c.Lastname,o.totalAmount, o.CustomerId FROM Customer c
    JOIN [Order] o
    ON o.CustomerId=c.Id
    GROUP BY c.Firstname, c.Lastname, o.totalAmount, o.CustomerId
    ORDER BY c.Firstname
    
  • Question 7: Can you explain the statement below?
    SELECT Firstname, Lastname,SUM(totalAmount) AS Sum,AVG(totalAmount) AS Average
    FROM Customer c JOIN [Order] o
    ON o.CustomerId=c.Id
    GROUP BY Firstname, Lastname
    ORDER BY Firstname
    
  • Question 8: Who Shopped for More than $ 100,000? Tip: Keyword * Having *
  • Bonus question: What does a statement look like that gives back the first and last name of the person who has spent the most money overall?