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?