NatureServe Learns SQL

"lesson plans" for learning SQL put together by Lynn Kutner

— comments, complaints, and suggestions welcome!

GOAL: Achieve a basic understanding of SQL so we can do our jobs in Biotics. Provide incentive to move forward with learning SQL and provide support to each other as we learn.

A Basic SQL Tutorial (Written by Shelley Cooke, ID)

Instructions for Modifying SQL/Query Builder Document

Online Resources:

Teach Yourself SQL in 21 Days, Second Edition

http://members.tripod.com/er4ebus/sql/

Interactive SQL Tutorials:

http://sqlzoo.net/

http://www.sqlcourse.com/

http://sqlcourse2.com/

http://www.1keydata.com/sql/sql.html

Links to other SQL resources:

http://databases.about.com/cs/sql/

NOTES:

Each "lesson" was intended to take an hour or so to complete.

This document includes hyperlinks to online resources. So even if something doesn't look like an URL, try clicking on the underlined blue text.

LESSON 1:

Teach Yourself SQL in 21 Days, Second Edition

A) Introduction

Lynn's NOTE: a very quick read; basic orientation to the "book"

Overview

Conventions used in this book

B) Day 1 Introduction to SQL

Lynn's NOTE: SKIM THIS CHAPTER ! (Actually – you could probably skip this entirely; I did, however, find some of the background information to be sort of interesting.)

History and structure behind SQL; history and function of relational databases.

C) Day 2 Introduction to the Query: The SELECT Statement

Lynn's NOTE: This is where things start to get fun!

By the end of the day you will be able to do the following:

· Write an SQL query

· Select and list all rows and columns from a table

· Select and list selected columns from a table

· Select and list columns from multiple tables

Please do QUIZ and EXERCISES at the end of the lesson. Answers can be found at:

F Answers to Quizzes and Exercises

LESSON 2:

A) Day 3 Expressions, Conditions, and Operators

Objectives

On Day 2, "Introduction to the Query: The SELECT Statement," you used SELECT and FROM to manipulate data in interesting (and useful) ways. Today you learn more about SELECT and FROM and expand the basic query with some new terms to go with query, table, and row, as well as a new clause and a group of handy items called operators. When the sun sets on Day 3, you will

· Know what an expression is and how to use it

· Know what a condition is and how to use it

· Be familiar with the basic uses of the WHERE clause

· Be able to use arithmetic, comparison, character, logical, and set operators

· Have a working knowledge of some miscellaneous operators

Please do QUIZ and EXERCISES at the end of the lesson. Answers can be found at:

F Answers to Quizzes and Exercises

A helpful resource:

A Glossary of Common SQL Statements

B) For more hands-on practice writing SELECT statements and practicing syntax, you might also want to check out the interactive exercises at:

http://www.sqlcourse.com/select.html

LESSON 3:

A) Day 4 Functions: Molding the Data You Retrieve

Lynn’s NOTE: Some of these functions probably won't be very relevant in our daily work (such as statistical and trigonometric functions); others will be very helpful for common needs like concatenation, changing upper / lowercase, the equivalent of "justlen", and other text formatting. I recommend that you pick and choose the functions that seem most relevant for your work.

Objectives

Today we talk about functions. Functions in SQL enable you to perform feats such as determining the sum of a column or converting all the characters of a string to uppercase. By the end of the day, you will understand and be able to use all the following:

· Aggregate functions

· Date and time functions

· Arithmetic functions

· Character functions

· Conversion functions

· Miscellaneous functions

These functions greatly increase your ability to manipulate the information you retrieved using the basic functions of SQL that were described earlier this week. The first five aggregate functions, COUNT, SUM, AVG, MAX, and MIN, are defined in the ANSI standard. Most implementations of SQL have extensions to these aggregate functions, some of which are covered today. Some implementations may use different names for these functions.

Please do QUIZ and EXERCISES at the end of the lesson. Answers can be found at:

F Answers to Quizzes and Exercises

B) For more hands-on practice writing SELECT statements and practicing syntax, you might also want to check out the interactive exercises at:

http://sqlcourse2.com/agg_functions.html

http://sqlcourse2.com/math.html

LESSON 4:

Teach Yourself SQL in 21 Days, Second Edition

A) Day 5 Clauses in SQL

Lynn's NOTE: This chapter is full of commands very similar to what we currently do in BCD at TCL in typical "SELECT" and "LIST" commands. Of course, the syntax is different, but I can imagine lots of uses for the information in this section.

Objectives

By the end of the day you will understand and be able to use the following clauses:

· WHERE

· STARTING WITH

· ORDER BY

· GROUP BY

· HAVING

Please do QUIZ and EXERCISES at the end of the lesson.

Answers can be found at: F Answers to Quizzes and Exercises

B) For more hands-on practice writing SELECT statements and practicing syntax, you might also want to check out the interactive exercises at:

http://sqlcourse2.com/groupby.html

http://sqlcourse2.com/having.html

http://sqlcourse2.com/orderby.html

LESSON 5:

Teach Yourself SQL in 21 Days, Second Edition

A) Day 6 Joining Tables

Objectives

Today you will learn about joins. This information will enable you to gather and manipulate data across several tables. By the end of the day, you will understand and be able to do the following:

· Perform an outer join

· Perform a left join

· Perform a right join

· Perform an equi-join

· Perform a non-equi-join

· Join a table to itself

Introduction

One of the most powerful features of SQL is its capability to gather and manipulate data from across several tables. Without this feature you would have to store all the data elements necessary for each application in one table. Without common tables you would need to store the same data in several tables. Imagine having to redesign, rebuild, and repopulate your tables and databases every time your user needed a query with a new piece of information. The JOIN statement of SQL enables you to design smaller, more specific tables that are easier to maintain than larger tables.

Please do QUIZ and EXERCISES at the end of the lesson.

Answers can be found at: F Answers to Quizzes and Exercises

B) For more hands-on practice writing SELECT statements and practicing syntax, you might also want to check out some of the interactive exercises at:

http://sqlcourse2.com/joins.html

LESSON 6:

Teach Yourself SQL in 21 Days, Second Edition

A) Day 7 Subqueries: The Embedded SELECT Statement

Objectives

A subquery is a query whose results are passed as the argument for another query. Subqueries enable you to bind several queries together. By the end of the day, you will understand and be able to do the following:

· Build a subquery

· Use the keywords EXISTS, ANY, and ALL with your subqueries

· Build and use correlated subqueries

Please do QUIZ and EXERCISES at the end of the lesson.

Answers can be found at: F Answers to Quizzes and Exercises