Databases and Tables

A SQL database is an organized collection of data. Or at least that’s what they taught me in college. In real life, it’s only as organized as the people putting data into the database. Databases contain schemas, tables, stored procedures, reports, views and other objects. Most databases will contain multiple tables. Tables contain rows that have data in them. I like to think of a database kinda’ like an Excel spreadsheet. Each tab on a spreadsheet is similar to a table; each row is similar to a row in a database and each column in the spreadsheet is somewhat similar to a column, or attribute. The headers are kinda’ like the schema.

These are overly simplistic explanations. And whenever you oversimplify something, you run the risk of miscommunication, but it helps as a starting place. This page is meant to be a short and easy guide to get started writing SQL queries. More links will appear throughout the page that point to other posts on my site, so stay tuned. Throughout my exercises in this page, I will use the following sample database, which has five records (one for each ID) and seven columns (ID,Site,Contact,Address,City,Zip, and Country).

Below is a selection from the “Customers” table (note that when querying data, SQL commands are NOT case sensitive)

ID Site Contact Address City Zip Country
1 Krypted Charles Edge my house Minneapolis 55418 US
2 Apple Tim Cook spaceship Cupertino 95014 US
3 Microsoft Satya Nadella campus Redmond 98053 US
4 Facebook Mark Zuckerberg foodhall Menlo Park 94025 US
5 JAMF Dean Hager Grain Exchange Minneapolis 55418 US

SQL Statements

Most tasks you will execute against a database are done with SQL statements. Think of this as a query, an insert, a change, or a delete operating. For example, to see all of your data, you would select all of the records from a database using the SELECT statement. Then we’ll ask for all, or *, and tell the command to show us where the data is coming from, which is the Customers table. Finally, we’ll be nice and tidy and put a semi-colon at the end; although if you forget, you can always do so after you hit return:

SELECT * FROM Customers;

The SELECT statement is the most common command I run in SQL. This is how you query data, build reports, derive the layout of a database and so, so much more.

Other Important SQL Commands covered in this series (if there is no link, I haven’t written that article yet):

SELECT – Query and pull information from a database
CREATE TABLE – Create tables in specified databases
DELETE – Delete data
UPDATE – Update data in a database
DROP TABLE – Delete tables
INSERT INTO – Inserts new data into a specified database
CREATE DATABASE – Create databases
ALTER DATABASE – Modify databases
ALTER TABLE – Modify tables
CREATE INDEX – Create indexes
DROP INDEX – Deletes indexes
INNER JOIN – Merge rows in a database