All slides on GitHub: https://github.com/gdiphilly/intro-to-sql


Intro to SQL

GDI Logo

Vicki Boykis; vickiboykis.com / @vboykis

Welcome!

Girl Develop It is here to provide affordable and accessible programs to learn software through mentorship and hands-on instruction.


Our code of conduct

Some rules

  • We are here for you!
  • Every question is important
  • Help each other
  • Have fun

Tell Us About Yourself

  • Who are you?
  • What's your experience level with SQL?
  • What do you hope to get out of the class?
  • What is your favorite movie, and how many times have you watched it?



We're going to make a table out of this info in part 2, and the data will live here: [Table Link]

About Me

  • Data scientist
  • 9+ years of experience with SQL
  • Favorite movie is Beauty and the Beast, watched 25 times. (and that was just yesterday)
Beauty and the Beast

What to expect from the class

  • This is a complement to the database design class
  • We'll be using the same terminology and building on those concepts
  • But we'll be hands-on
Jim Jazz Hands

By the end of the class, you will be able to query and modify a SQL table to make sense of the data you have in a relational way.

Plan for the day: CRUD

Morning (10-12): cRud

  • What's a database? What's SQL?

  • How to query a database:
    • Select statement (from statement)
      • Select, Distinct
      • Count
    • Group By, Order By
    • Clauses
      • Where
      • Like
    • Joins(Inner/Outer Left/Right)
    • Practice

Afternoon(12:30 - 3): CrUD

    • Super-awesome bonus round:
      • Subqueries
    • Practice
    • How to modify a database
      • Creating a table
      • Udating a table with rows
      • Deleting Rows
        • v.s. deleting tables
    • Optimizing queries
    • Resoures and links
    • Practice
    • GDI Survey

What's a relational database?

A virtual organized file cabinet that makes data available to people and computers across the organization.

Operator

The language of relational databases (1/2)

  • Database: A group of data organized in a logical way(Excel workbook)
    • Schema: Layout of the database, sometimes interchangeable with database (Excel workbook + name of workbook)
    • Table: A subset of a database (Excel worksheet)
      • Rows (records) and columns (fields): Parts of a table
  • CRUD (Create, Read, Update, Delete): How you change the databases and tables in the database

  • Relational Data Model : Data related to each other are stored a certain way and "point" to each other (Like Excel cell references)

The language of relational databases (2/2)

  • Database keys (primary and foreign): The pointers in each table that can link to other tables

  • Indexes: Make databases easier to search

  • RDBMS systems: Types of relational databases that have all the properties of relational data models: MySQL, Oracle, Access, Postgres

  • Queries: How to ask questions of the tables and databases

Databases, schemas, and tables

DB Schema→ Table → Row → Value

Nesting Dolls

Excel File → Workbook → Row → Cell

What's a relational database?

Excel spreadsheets you can join without Vlookups! relational model

What's SQL?

  • SELECT user_name, SUM(total_purchases)
  • FROM billing_database.user_table
  • WHERE user_name LIKE 'Henderson%'
  • AND billing_month = 'January'
  • GROUP BY user_name;

  • It's the language that the database speaks to bring you back data

  • Has a lot of words similar to English, but they have their own meaning as key words

  • Like Siri or Alexa

Different Flavors of SQL

  • How do you say that you want a can of Coca-Cola?
    • Coke, soda or pop?

  • How do you write SQL?

Oracle MySQL
SQL Server Postgres
Access Sybase
DB2

All different flavors of the same language

Databases, schemas, and tables

guy giving horns
  • Reading tables other people wrote

  • Modifying those tables to add your own information/ creating your own tables to read from CSV files, other SQL tables, and other systems.

Databases and CRUD

  • Create INSERT, CREATE
    • Adding rows to your table
  • Read SELECT
    • Picking specific information from your table
  • Update UPDATE, ALTER
    • Changing specifc information in your table
  • Delete DELETE, DROP, TRUNCATE
    • Erase your data

What can you do with SQL?

The power is in the relationship between data sets.


  • How many customers do we have in Los Angeles?

  • What's that person's Facebook and Twitter handle?

  • Did profits go up or down this quarter?

  • How many users are clicking on our new site?

SQL clauses: required order

1 SELECT selects variables
2 FROM opens datasets
3 WHERE restricts observations
4 GROUP BY groups observations
5 HAVING restricts groups
6 ORDER BY sorts results

Let's develop it!

Let's get started reading tables

We'll use MySQL, a popular version of SQL.

If you've done anything online, chances are the transation went through MySQL (Walmart, Kayak, Facebook, Etsy, Verizon)


We'll log on to the front-end of MySQL, PHPMYAdmin.


Logon site: [site]

Your Password: [password]

You should see a login screen:

PHP login screen

How PHPMyAdmin Works

PHP abstraction

Two ways to execute in MySQL: PHPMyAdmin and command line

PHP abstraction

Focusing on phpMyAdmin for the first half of the class.

We have a test dataset of employee records we'll be using in the employee schema.

Options

Employee data:
6 tables in the employees schema

Schema
Data Source

Tables and Schemas

employees is the database and it has 6 tables.

Database/schema used interchangeably

Database → Schema ---> Table

Book ---> Chapter ---> Page

MySQL Database → Employees Schema → employees Table

PHP abstraction

How to name schemas and tables

  • Schema/Databse: employees

  • Tables:
    • employees.employees,
    • employees.departments,
    • employees.salaries
  • And so on.


Usually it's bad practice to name tables the same thing as the schema (confusing), but we'll be ok with the small test data set.

Let's develop it!

Let's run our first query!

Go to the SQL tab and type in:
SELECT * FROM employees.employees; 
select-star

Type out all the SQL you write. It'll be easier to learn.

SELECT * from different tables

Try a couple of these:

SELECT * FROM employees.titles;
SELECT * FROM employees.dept_emp;
SELECT * FROM employees.salaries; 

and check out the tables and info.

Stretch break!

kitty!

Selecting specific columns

Now let's try selecting only certain columns

SELECT title, emp_no FROM employees.titles; 

A note about the semi-colon: you don't really need it, depending on which flavor of SQL you're using, but it's good practice in general to include it. Like a period at the end of an English sentence.

More specific columns. Let's get to know our data.

Selecting one column:

SELECT dept_name FROM employees.departments;

Selecting multiple columns:

SELECT emp_no, dept_no FROM employees.dept_emp;

Distinct: selecting only unique instatnces of that column.

SELECT DISTINCT  title FROM employees.titles;
title
Senior Engineer
Staff
Engineer
Senior Staff
Assistant Engineer
Technique Leader
Manager

Difference between distinct and *

SELECT COUNT(DISTINCT title) FROM employees.titles;
7
                                
SELECT COUNT(title) FROM employees.titles;
443,308
                                        

Difference between distinct and *

Distinct: Selecting only unique instances

Which result set is bigger?

What is each one telling us?

SELECT COUNT(title) FROM employees.titles;
433,308
                                
SELECT COUNT(*) FROM employees.salaries;
2,844,047
                                        

Which table is the largest?
(most records)

SELECT COUNT(*) from employees.departments;
9
                                
SELECT COUNT(*) FROM employees.dept_emp;
331,603
                                        
SELECT COUNT(*) FROM employees.dept_manager;
24
                                                                
SELECT COUNT(*) FROM employees.employees;
300,024
                                                                        
SELECT COUNT(*) FROM employees.salaries;
2,844,047
                                                                                
SELECT COUNT(*) FROM employees.titles;
443,308
                                                                                        

Three parts of SQL

SQL clauses: required order

1 SELECT selects variables
2 FROM opens datasets
3 WHERE restricts observations
4 GROUP BY groups observations
5 HAVING restricts groups
6 ORDER BY sorts results

Now let's group stuff!


                        SELECT type_of_dog,COUNT(*)
                        FROM animals.total_dogs
                        GROUP BY type_of_dog;
                                

Group statements corral data together into categories when we want to count things by groups of things

i.e. How many total dogs vs puppies vs parents.

Pups

Counting titles and salaries

What is this telling us? How many instances of that title are there in the table?
SELECT title, COUNT(*) FROM employees.titles
GROUP by title;
                                

Select Columns

More group by examples

SELECT dept_name, COUNT(*) FROM departments
GROUP BY dept_name;
                                                

Select Columns

How many instances of that department are there in that table?

Group by more than one item: making smaller groups

Group by More than One Item

Let's try some exercises that combine COUNT(*), DISTINCT and GROUP BY

How many unique salaries does each employee have? (how many times have they been given raises?)
SELECT emp_no, COUNT(DISTINCT salary)
FROM salaries
GROUP BY emp_no;
How many managers has each department had?
SELECT dept_no, COUNT(emp_no) FROM dept_manager
GROUP BY dept_no;
How many people that were born on the same day, have the same first name?
SELECT birth_date,first_name, COUNT(*) FROM employees.employees
GROUP BY birth_date, first_name;

Another kind of group-by:
maximum and minimum

When was your youngest employee born? (i.e. are you following labor laws?)

 SELECT MAX(birth_date) FROM employees;

In this case, you don't need the group-by statement since you're not counting groups of people, but looking at the whole table


Which title has been around the longest?(Smallest employee number)

SELECT title, MIN(emp_no) from titles
GROUP BY title;

Order by: Sorts by the terms you want

Will sort by first name ascending, unless you specify desc.


SELECT first_name, gender, COUNT(*)
FROM employees.employees
GROUP BY first_name, gender
ORDER BY first_name;

SELECT first_name, gender, COUNT(*)
FROM employees.employees
GROUP BY first_name, gender
ORDER by first_name DESC;

Ordering by more than one column, will sort by the first, then by the second, ascending


SELECT gender, birth_date FROM employees.employees
ORDER BY gender, birth_date;

Order by exercises

Order by a number ascending

SELECT emp_no, salary FROM employees.salaries
ORDER BY salary ASC;

Order by a number descending

SELECT emp_no, salary FROM employees.salaries
ORDER BY salary DESC;

SQL clauses: required order

1 SELECT selects variables
2 FROM opens datasets
3 WHERE restricts observations
4 GROUP BY groups observations
5 HAVING restricts groups
6 ORDER BY sorts results

Where Clause: Like a Filter

You only want to select certain things.

SELECT first_name, last_name, COUNT(*)
FROM employees.employees
WHERE first_name='Adam'
GROUP BY first_name, last_name;
first_name last_name COUNT(*)
Adam Alpin 1
Adam Alvarado 1

Like statements are really useful in where clauses

"Like" will pick up anything similar to what you entered.

SELECT last_name, COUNT( * )
FROM employees
WHERE last_name LIKE 'S%'
GROUP BY last_name;
Exercises:
  • What happens when you search in all caps?
  • What happens when you do last_name = 'S%''
  • What's the difference between like and =?
    • Like is more like a search engine
    • = means you have to have an exact match

Where clause exercises

Only all people named Mary

SELECT * FROM employees
WHERE first_name='Mary';

Only all of the engineers

SELECT * FROM employees.titles
WHERE title LIKE '%Engineer%';

A couple of things to note about greater/less than/equal to

 WHERE number >='5'; --returns (5,6,7,8...)
 WHERE number > '5'; --returns (6,7,8...)
 WHERE day <= '2017-01-01'; --returns (Jan 1, Dec. 31, Dec. 30…)
 WHERE day < '2017-01-01'; --returns (Dec. 31, Dec. 30…)
 WHERE day != '2017-01-01'; --returns anything other than Jan 1

Cooking with the good suff: joins

One table is great. But the power of SQL comes from being able to join related tables to get information.

Group by More than One Item

Plan for the day: CRUD

Morning (10-12): cRud

  • What's a database? What's SQL?

  • How to query a database:
    • Select statement (from statement)
      • Select, Distinct
      • Count
    • Group By, Order By
    • Clauses
      • Where
      • Like
    • Joins(Inner/Outer Left/Right)
    • Practice

Afternoon(12:30 - 3): CrUD

    • Super-awesome bonus round:
      • Subqueries
    • Practice
    • How to modify a database
      • Creating a table
      • Udating a table with rows
      • Deleting Rows
        • v.s. deleting tables
    • Optimizing queries
    • Resoures and links
    • Practice
    • GDI Survey

Let's connect multiple tables

emp_no is the column that's the primary key for all three tables that allows us to join them

Group by More than One Item
  • We join by primary keys: A field (or set of fields) that uniquely determine a row.
  • Values in PK must be unique in table (no duplicates allowed); Only one PK per table allowed
  • To foreign keys: A field (or set of fields) that is a PK in some other table.

How all the tables join in our schema

Schema

In SQL, a join looks like this:

Group by More than One Item

Often when we join tables, we make them easier to reference later on by aliasing them, but at this point it's a style choice.

You can now select columns that, before, only existed in either table

Find out salary history:

SELECT first_name, last_name, hire_date,salary, from_date, to_date
FROM employees.employees a
JOIN employees.salaries b ON a.emp_no = b.emp_no
first_name last_name hire_date salary from_date to_date
Georgi Facello 6/26/86 60117 6/26/86 6/26/87
Georgi Facello 6/26/86 62102 6/26/87 6/25/88
Georgi Facello 6/26/86 66074 6/25/88 6/25/89

Another join example

When you have two columns from different tables with the same name, you have to reference them specifically.

Group by More than One Item

Different types of joins

Join cheatsheat
Source

Let's develop it!

Left join example

Left join: All the fields in the first table, plus the ones that match in the second table


SELECT first_name, last_name, hire_date,title, c.from_date, c.to_date
FROM employees.employees a
LEFT JOIN titles c ON a.emp_no = c.emp_no;

What if you'd done a right join instead?

Using everything we've learned

What is this query telling us?

SELECT first_name, last_name,a.emp_no, hire_date, 
COUNT(DISTINCT title) , COUNT(DISTINCT salary)
FROM employees.employees a
JOIN employees.salaries b ON a.emp_no = b.emp_no
JOIN employees.titles c ON a.emp_no = c.emp_no
where first_name like 'A%'
GROUP BY first_name, last_name, hire_date,a.emp_no;
All in table

Going all in again:

What is this query bring back?

SELECT first_name, last_name,hire_date,a.emp_no, salary, from_date as
salary_start_date, to_date AS salary_end_date
FROM employees.employees a
JOIN employees.salaries b ON a.emp_no = b.emp_no
WHERE salary >60000
AND from_date >'2000-01-01'
ORDER BY last_name

Anyone whose salary is greater than $60,000 and who was hired after 1/1/2000

Lunch!

kitty!

Now the power of subqueries

They don't know

Using nested logic datasets to query other data sets

Subqueries

A subquery or inner query or nested query is a query within a query. Most of the time, a subquery is used when you know how to search for a value using a SELECT statement, but not the exact value
Small Nested dolls

Subqueries: How much money did only the engineers make?

Engineer subquery

What kind of business questions can we answer now? SQL Power!

Exercises:

  • How many employees do we have?
  • SELECT COUNT(DISTINCT emp_no)
    FROM employees.employees;
  • How many people got raises in 1999?
  •  SELECT * from (
            SELECT emp_no, COUNT(*) AS number_of_salaries 
            FROM salaries
            WHERE from_date>'1998-01-01'
            AND from_date<'2000-01-01'
            GROUP BY emp_no) a
            WHERE number_of_salaries >1
  • What's the most common job title we have?
  • SELECT title, COUNT(DISTINCT a.emp_no) 
    FROM titles a
    JOIN employees b
    ON a.emp_no=b.emp_no
    GROUP BY title

What kind of business questions can we answer now? SQL Power!

Exercises:

  • What's the top salary by department?
  • How much money did non-engineers make?
  • Extra-hard question:
    • How many women engineers got raises in 2000 versus male engineer, and who were their managers?

Rehash

What did we do so far?

  • What a database is
  • What a table is
  • Why relationships are good
  • How to query a table or database with SQL
  • How to read a SQL query
  • How to ask business questions with SQL

We learned cRud; now we learn CrUD

Now that you see how powerful SQL is, let's make some tables of our own. What if we have tons of data we want to query?

Or we want to join our Excel tables to other data?

With great power comes great responsibility

You have the ability to create tables. But also to delete them by accident, to create duplicate data,or to confuse people with data.

Genie

You have the power to make people hate you. Use it wisely :)

Databases, schemas, and tables

DB Schema→ Table → Row → Value

Nesting Dolls

Excel File → Workbook → Row → Cell

First we create our schema

Name it with your first name or something easy you can remember; You'll have to keep typing it, so under 10 characters is optimal

Create Schema

And you should see it show up:

Create Schema

Now we create a table to put in the schema

Let's do favorite movies that we did at the beginning of the class.
Let's have: First Name, Favorite Movie, Number of times watched

Create Schema

First, we need to design it a bit in our head. Which columns do we want? Which one will be the unique primary key? How to we want to name the columns?

Things to think about when designing relational tables

feather
  1. How will you join to other tables?
  2. What kind of data are you storing?
  3. Who else will use your tables?

Quick Overview of Database Design(from Sondra's class)

Relational Database Model

Storing the data as relations

  • eliminates redundancy
  • saves space
  • reduces mistakes (ties in to consistency)

Quick Overview of Database Design(from Sondra's class)

Tables are joined on special fields called keys

  • Primary Key (PK)
    • A field (or set of fields) that uniquely identify a row
    • In math speak, the minimal set of fields that the row is functionally dependent upon


  • Foreign Reference Key (FK)
    • A field (or set of fields) that is a PK in some other table
    • IThere can be multiple FK in a table
    • Points to a row in another table

Data Types

Data usually comes in two types: numbers and charactes

  • Numbers
    • Boolean: True or False [0,1]
    • Int/TinyInt: whole numbers, negatives [5, -7]
    • Decimal: [.5555, 2.40404040]
  • Strings
    • Varchar(can't add/subtract):[Belle, five,5]
      • Have to specify length
    • Blob: [9999349234023403204300000]
  • Dates/Times
    • Date: 2013-01-01, 9/1/2012]
    • Timestamp(UTC) [1385496316]

*Accepted mySQL formats: https://dev.mysql.com/doc/refman/5.0/en/numeric-types.html

Let's look at the data types in the tables we have

By going to STRUCTURE

table structure

Our movie database (with real data)

movie table
  • First Name as the Primary Key and Foreign Key
    • We can join by the name, as long as it's unique
    • But our names aren't unique, so we'll create an incrementer
  • First Name Should be a Character Field.
  • Favorite Movie should be a character Field.
  • Times Watched should be an Integer. We want to be able to add it.

Two ways to execute in MySQL: PHPMyAdmin and command line

two ways to execute sql

For the second half of the class we'll be working with commands more than the UI, but there is usually more than 1 way to do everything in MySQL.

How to create a table with SQL

Run in the sample place you did your select statements:

sql create

Some Data types in MySQL(Part 2)

CHAR() A fixed section from 0 to 255 characters long.
VARCHAR( ) A variable section from 0 to 255 characters long.
TINYTEXT A string with a maximum length of 255 characters.
TEXT A string with a maximum length of 65,535 characters.
BLOB A string with a maximum length of 65,535 characters.
MEDIUMTEXT A string with a maximum length of 16777215 characters.
MEDIUMBLOB A string with a maximum length of 16777215 characters.
LONGTEXT A string with a maximum length of 4294967295 characters.
LONGBLOB A string with a maximum length of 4294967295 characters.

Here's the create statement for one of the tables we've been working with

CREATE TABLE employees (
emp_no      INT             NOT NULL,
first_name  VARCHAR(14)     NOT NULL,
last_name   VARCHAR(16)     NOT NULL,
gender      ENUM ('M','F')  NOT NULL,
hire_date   DATE            NOT NULL,
PRIMARY KEY (emp_no);

Which field is the primary key?
Which fields are numbers?
What's enum*?

Let's get to it

create-table-increment

In PHPMYADMIN: STEP 1

phpmyadmin step 1

In PHPMYADMIN: STEP 2

phpmyadmin step 2

In PHPMYADMIN: STEP 3

Congratulations, it's a table!

phpmyadmin step 3

Now let's do some quick analysis

SELECT * FROM vicki.moviesample;
SELECT first_name FROM vicki.moviesample;
SELECT COUNT(*) FROM vicki.moviesample;

What do we get?

Nothing! because our table has no data. :(...yet

To the SQL: Inserting data into a SQL table

Now that we have the table set up, we can insert the values. Let's load a row of data.

INSERT INTO
yourname.tablename(first_name, favorite_movie,
times_watched) -- column names
VALUES ('Jasmine', 'Aladdin', '456'); -- column values 

Adding data to tables

INSERT INTO vicki.moviesample(first_name, favorite_movie, times_watched)
VALUES ('Alice','Alice in Wonderland', 5);

Now let's check it..wahoo! We have rows!

we have rows

How do we add the rest of the data?

INSERT INTO vicki.moviesample
(first_name, favorite_movie,times_watched)
VALUES ('Vicki', 'Beauty and the Beast', '5');

INSERT INTO vicki.movie_sample
(first_name, favorite_movie,times_watched)
VALUES ('Jasmine', 'Aladdin','456'); 

Break to enter the rest of the data into the table

What kind of issues do you see arising from wrong data entry?

Sheldon

There are much easier ways of getting data into tables

  • Bulk-loading ETL
  • Writing a script in Perl, Ruby, Python, or any other scripting language
  • Manipulating Excel to write your insert/update statements

Our beautiful movie table!

Let's answer some exercises with SQL:

What's the most popular movie?
SELECT favorite_movie,
COUNT(DISTINCT first_name)
FROM yourname.moviesample
GROUP BY favorite_movie
ORDER BY COUNT(DISTINCT first_name) DESC;
  • What's the most popular name this Saturday?
  • How many times was the most someone watched a movie?
  • How many movies have 'The' in the title?
  • How many people watched a favorite movie more than 3 times?

Now, let's make another table

We want to see which decade was more popular among GDI members. This one has the data for the movie name and the year it was made.

another table

How do we add the rest of the data?

SELECT * FROM vicki.moviesample a
JOIN vicki.movie_year b
ON a.movie_title=b. movie_title
  • What happens when you have two fields with the same name in your select statement?
  • What is movie title called in year_released (special role?)
  • What would be a better field to join on?
  • What's the year with the most popular movies?
  • And, what's the decade with the most popular movies? Hint: Take the first three digits of the year
    • select left(year_released,3), count(*) from..

Deleting SQL Values

What kind of issues do you see arising from wrong data entry?

This is the part where you have to be really careful. Most users won't have access to delete tables.

truncate table; Leaves the table structure in tact, just deletes the values. (deleting all values in a spreadsheet, keeping file)


drop table; Deletes EVERYTHING. BADBADBAD. (file goes in the trash)

Grumpy Cat no

Dropping Rows Example

Easy way in UI: The delete button. Be careful!

dropping rows
  • SQL...more accurate.
  • DELETE FROM user1.moviesample WHERE first_name='Shrek';

You can delete with any type of where statement

DELETE FROM moviesample
WHERE first_name like '%S';

DELETE FROM moviesample
WHERE times_watched >='500'; 

Updating the table

  • SELECT is to count things
  • CREATEis to create a table
  • DELETE is to erase
  • UPDATE is to change specific entries

    • UPDATE moviesample
      SET first_name = 'Chihiro', times_watched= 3
      WHERE movie = 'Spirited Away';

      Try it now

Altering the Table

  • SELECT is to count things
  • CREATEis to create a table
  • DELETE is to erase
  • ALTER is to fundamentally change a table:

    • ALTER TABLE icecream DROP COLUMN flavor;
      ALTER TABLE icecream ADD unique (quantity);
      ALTER TABLE icecream MODIFY flavor VARCHAR(35);

      Try it now:

      ALTER TABLE vicki.moviesample ADD COLUMN release_yr
      varchar(4) after times_watched;

Troubleshooting SQL Errors

  • You misspelled something
  • Selection from table you don't have rights to
  • Your group by statements don't have enough columns
  • Query's running too long
  • Format for date/time doesn't match
  • You're selecting from the wrong table in the schema
  • Date type is the wrong type for a join
  • You haven't aliased your table

Best Practices

  • Limit your queries by timerange
  • Only do joins on primary keys
  • Profile your data beforehand
  • Save your SQL queries for future use
  • Be friends with your database people
    • Write explain plans!

A word about non-relational "big data"

big-data

The end!

Here's what we've learned:

  • Reading tables (cRud)
    • Basic SQL Logic
  • Writing tables (CrUD)
    • Basic deletes and updates
  • Best practices for writing SQL and writing tables
Singing in the Rain

More SQL Resources



Practice, practice, practice! Upload your Excel spreadsheets.

Get Involved

  1. Join Slack community
  2. Teaching assist
  3. Teach something
  4. Mentor or be mentored

All forms here

More SQL: Local Groups

Survey

We value your feedback and are always trying to improve.

Vicki Boykis vicki.boykis@gmail.com

vickiboykis.com | @vboykis

http://bit.ly/gdi-sql-2

Thank you to the following people:

Sarah Canfield - converting slides to Reveal A.E.Lavender - Sanity and syntax checks