This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Practise SQL in your browser with SQL Fiddle Part thirteen of a sixteen-part series of blogs
Some of our poor delegates don't have the luxury of a home computer to install SQL on (even worse some have made bad life choices and picked a Mac). The good news is that you can still practise your SQL skills in your web browser, using the impressive SQL Fiddle site. Have no fear, SQL Fiddle is here, a browser based sandbox to bring you cheer! Installing and configuring SQL Fiddle SQL Fiddle Exercise 1 - SELECT SQL Fiddle Exercise 2 - Order By SQL Fiddle Exercise 3 - Numeric Filters SQL Fiddle Exercise 4 - Date Filter SQL Fiddle Exercise 5 - Text Filters SQL Fiddle Exercise 6 - Cast or Convert Functions SQL Fiddle Exercise 7 - Conditional Function SQL Fiddle Exercise 8 - Counting Words SQL Fiddle Exercise 9 - Formatted Dates SQL Fiddle Exercise 10 - Date Calculations SQL Fiddle Exercise 11 - Inner Join SQL Fiddle Exercise 12 - Outer Join (this blog) SQL Fiddle Exercise 13 - Grouping Data SQL Fiddle Exercise 14 - Having SQL Fiddle Exercise 15 - All together now!

Practise SQL in your browser with SQL Fiddle

Part thirteen of a sixteen-part series of blogs

Some of our poor delegates don't have the luxury of a home computer to install SQL on (even worse some have made bad life choices and picked a Mac). The good news is that you can still practise your SQL skills in your web browser, using the impressive SQL Fiddle site. Have no fear, SQL Fiddle is here, a browser based sandbox to bring you cheer!

Posted by Sam Lowrie on 30 June 2021

SQL Fiddle Exercise 12 - Outer Join

There are some situations where an inner join just won't help. Write a statement to return the FamilyID column from the Purchase table (using DISTINCT to return a unique column):

This shows that there are 5 different families referenced in the purchases table.

Now add this code in the left-hand side of the SQL Fiddle browser and rebuild your schema to include a new Family table in your model:

CREATE TABLE Family

([FamilyId] int, [FamilyName] varchar(9), [Cost] numeric)

;

INSERT INTO Family

([FamilyId], [FamilyName], [Cost])

VALUES

(1, 'Reptile', 2.99),

(2, 'Fish', 1.5),

(3, 'Bird', 3.49),

(4, 'Amphibian', 0.59),

(5, 'Mammal', 5),

(6, 'Insect', 4.28)

;

Note that there are 6 families. Amend your query above to link your purchase table to your family table (you will need to add f. or [family]. in front of the [FamilyID] column to make sure that you pick this up from the correct table in your SELECT command):

Interestingly the query still only returns 5 rows, even though above you clearly added 6 into the families table!

To return all the rows from the Family table we need to change the type of join. Depending on how you wrote the join try Right Outer or Left Outer Join.

Your query should now return all 6 rows!

Check the answer here.

Parts of this blog
Installing and configuring SQL Fiddle SQL Fiddle Exercise 1 - SELECT SQL Fiddle Exercise 2 - Order By SQL Fiddle Exercise 3 - Numeric Filters SQL Fiddle Exercise 4 - Date Filter SQL Fiddle Exercise 5 - Text Filters SQL Fiddle Exercise 6 - Cast or Convert Functions SQL Fiddle Exercise 7 - Conditional Function SQL Fiddle Exercise 8 - Counting Words SQL Fiddle Exercise 9 - Formatted Dates SQL Fiddle Exercise 10 - Date Calculations SQL Fiddle Exercise 11 - Inner Join SQL Fiddle Exercise 12 - Outer Join (this blog) SQL Fiddle Exercise 13 - Grouping Data SQL Fiddle Exercise 14 - Having SQL Fiddle Exercise 15 - All together now!

Parts of this blog