The Little SQLisper

Introduction

A relational database consists of one or more relations (tables), each of which is a collection of records (rows) containing specific fields (columns, attributes). Every record has the same fields, and each field contains exactly one flavor of data. (This distinguishes a relation from a computer spreadsheet, which might be organized in a tabular fashion, but in which an arbitrary cell might contain text, numbers, or a formula.)

The purpose of SQL is to first create these tables and populate them with records; then to extract and organize records according to specific criteria. A statement in SQL describes what you want, but not how to obtain it. (This distinguishes it from procedural computer languages such as Fortran, C or Java, which concentrate on tiny step-by-step details of how.)

Imagine you're at the greengrocer with your mother, and she dispatches you to get three pounds of Granny Smith apples, ripe ones with no bruises. Were the grocery a database, she's just described a specific table (apples, rather than pears or bananas) containing records (the individual apples) each with attributes (variety, weight, ripeness, number of bruises); and also an aggregate function, their total weight.

This tutorial is modeled after The Little Lisper, a classic book describing the LISP programming language. It consists of example-result pairs; if you executed each chunk of SQL code, the database would transform to the given state. Each example builds upon the previous one.

Data Definition Language

create table tablename(
	[fieldname fieldtype [fieldspecifiers]? ,]*
);

insert into tablename
	[( fieldname [, fieldname]* )]?
	values ( fieldname [, fieldname]* )
;

update tablename
	set [fieldname=value [, fieldname=value]* ]+
	[where condition]?
;

delete from tablename
	where condition
;

alter table tablename
	add column fieldname
;

drop table tablename;

create table Employee( name varchar, passwd character(8), height integer, born date );

Employee
name	passwd	height	date

insert into Employee values ('John Doe', 'ab1z@3cd', 72, #1974-10-13#); insert into Employee (name, height) values ('Jane Doe', 68);

Employee
name	passwd	height	date
John Doe	ab1z@3cd	72	1974-10-13
Jane Doe	null	68	null

update Employee set passwd = '123@$#4' where name = 'Jane Doe'; update Employee set name = 'John Q. Doe', height = 70 where name = 'John Doe';

Employee
name	passwd	height	date
John Q. Doe	ab1z@3cd	70	1974-10-13
Jane Doe	123@$#4	68	null

delete from Employee where name = 'Jane Doe'; alter table Employee add column lunch time;

Employee
name	passwd	height	date	lunch
John Q. Doe	ab1z@3cd	70	1974-10-13	null

drop table employee; create table Product( id integer not null, name varchar not null, price numeric(6,2), features bit(4) ); insert into Product values(123, 'widget', 26.99, B'1001'); insert into Product values(732, 'gizmo', 8100.29, B'1111'); insert into Product values(733, 'doodad', null, null);

Product
id	name	price	features
123	widget	26.99	1001
732	gizmo	8100.29	1111
733	doodad	null	null

Data Manipulation Language

select (* | fieldname [, fieldname]*)
	from      tablename
	[where    condition]?
	[group by fieldname [, fieldname]* ]?
	[having   condition]?
	[order by fieldname [, fieldname]* ]?
;

In a query with all five clauses, first a set of rows are selected from a table. (Or, in a join, a combination of tables.) Next they're grouped by one or more fields, then entire groups are dropped if they don't match the having clause. Finally, the rows within each group are ordered.

update Employee set passwd = '123@$#4' where name = 'Jane Doe'; update Employee set name = 'John Q. Doe', height = 70 where name = 'John Doe';


id	name	price	features
123	widget	26.99	1001
732	gizmo	8100.29	1111
733	doodad	null	null

select name, price from Product;


name	price
widget	26.99
gizmo	8100.29
doodad	null

update Employee set passwd = '123@$#4' where name = 'Jane Doe'; update Employee set name = 'John Q. Doe', height = 70 where name = 'John Doe';


id	name	price	features
123	widget	26.99	1001

update Employee set passwd = '123@$#4' where name = 'Jane Doe'; update Employee set name = 'John Q. Doe', height = 70 where name = 'John Doe';


name	price	features
gizmo	8100.29	1111

Aggregate Functions

Scores
student	q1	q2	q3
Agarwal	10	10	10
Bentley	20	20	10
Cadwall	30	30	10
Corazon	10	10	20
Enterby	10	20	10
Fukomoa	30	30	30
Granttz	10	20	30

select q1 as score, count(q1) as count from Scores group by q1 order by count(score1) desc;


score	count
10	4
30	2
20	1

select q1 as score, count(q1) as count from Scores group by q1 order by count(score1) desc;

[? Double-check these results ?]