Tiny Deathstars of Foulness

If you have growing sets of data, one of the best ways to speed up database performance is to make sure each column in larger tables is indexed. You can easily index a column following this syntax (using the name of your table in the place of tablename and the name of your column in the place of columnname):

ALTER TABLE tablename ADD INDEX (columnname);

So if you have a table called Customers and the following columns (as in the case of my Customers database from the previous exercises):

ID Site Contact Address City Zip Country

You would index them all as follows:

ALTER TABLE Customers ADD INDEX (Contact);
ALTER TABLE Customers ADD INDEX (Address);
ALTER TABLE Customers ADD INDEX (Country);

March 8th, 2016

Posted In: SQL

Tags: , , , , ,

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

January 26th, 2016

Posted In: SQL

Tags: , , , , , , , , ,