SQL Zoo

1 Learning Objectives

Objectives

In this lesson you will develop an understanding of the learning strand Data and data representation on your progress ladder, working towards blue/indigo level.

  • How to Query data on one table using a typical query language.
  • How data can be structured in multiple tables to make it useful
  • The term Primary Key as a unique identifier for each record.
  • How to select and use the correct data type for a field.

2 SQL - Structured Query Language

Learn It

  • SQL is an abbreviation for structured query language, and pronounced either see-kwell or as separate letters.
  • The original version called SEQUEL (structured English query language) was designed by an IBM research center in 1974 and 1975.
  • SQL is a standardized query language for requesting information from a database.
  • Query is another word for question, in essence we are asking the Database a question.
  • Let's look at it's structure
SELECT field(s) FROM table(s)

Select and From are in uppercase, representing the fact that they are commands and therefore 
do not change. Field and table are lowercase representing the section that depends upon the 
Database in question.

Example - A School Database

SELECT First_Name,Last_Name,Grades FROM Exams.

First_Name, Last_Name and Grades are the fields that we are interested in obtaining the 
records for. Exams is the Table that contains all of the records for students who have 
taken their GCSE's.

Running this command will list all the student's names and their grades. This isn't exactly
helpful as we haven't listed the subjects, so we won't know which grades apply for which subjects.
A better approach would be to include subjects and then GROUP the subjects or students together.
Let's look how we do this by taking a trip to the SQL Zoo.
  • Click on the following link to take a trip to SQL Zoo.
  • The Website looks looks like this.

SQLZoo.png

  • Think, Pair, Share: we will work through the first question of the first tutorial as a group, let's start by having an attempt at the question yourself.
  • If you get stuck, share your thoughts with the person next to you first, and then be ready to share your findings with the class.
  • Click Here https://sqlzoo.net/

Badge It - Silver

Learning Strand: Data and Data Representation

  • Finish the questions for the first Tutorial 0 SELECT - Basics and the move on.
  • Complete the next Tutorial 1 SELECT - name
  • Ensure you have completed both Tutorials, capturing evidence that you have completed the tasks using the snip-it tool.
  • Submit all of your evidence on the BourneToLearn website.

Badge It - Gold

Learning Strand: Data and Data Representation

  • Research why data is split over a range of tables using a technique known as Normalisation
  • What are the benifits of Normalisation and why was it important in the 1970's and 1980's?
  • What was the Millennium bug?
  • How did storing a date as 3 bytes instead of 4 bytes cause the Millennium bug?
  • Write your answers to the questions above in your favourtie text editor and submit your work to the BourneToLearn Website.

Badge It - Platinum

Learning Strand: Data and Data Representation

  • Research the term foreign key in relation to Databases.
  • In order to combine data together from multiple tables we have to join them up using the foreign keys as a guide.
  • Research how to join a table using SQL reading through guidance carefully.
  • Complete the 6 JOIN Tutorial on the SQLZoo website.
  • Acheivement Point awarded for all 3 badges awarded.

Author: A Stout

Emacs 25.1.1 (Org mode 8.2.10)