COMP3311 20T3 SQLite Database Systems

Summary information about what SQLite v3 provides.
In the examples below, x, y, s, etc. are attributes.

Data Types

Base Data Types integer, real, char(N), varchar(N), text, date, time, datetime
Domains create domain Name as BaseType check Constraint;
Tuple Types create type Name as (Attr1 Type1, ..., Attrn Typen);

Expressions

Arithmetic + - * / % e.g. x + 3,   x + y + z,   y % 4
Logic and or not = <> e.g. x > 5 and x < 10,   z <> 'abc',
x between 6 and 9
String || like ilike ~ ~* e.g. s1 || s2,   name like '%abc%'
string literals: 'John''s book',   e'hello\n'
Note: double-quotes ("...") are not strings
NULL is null, coalesce() e.g. x is null,   y is not null,
note that x = null always fails,
coalesce(x,y,99) value is first non-null or 99

Functions

Case-insensitive SQL pattern matching Attribute like Pattern
String concatenation String1 || String2
String concatenation aggregrate group_concat(TextAttribute[,Separator])
Substring substr(TextAttribute,Start,Length)
Aggregates count(), min(), max(), avg(), sum()

Constraints

Attribute check, not null, unique e.g. check (x >= 0),  x not null,
x unique
Foreign key foreign key foreign key (Attributes)
references Table(Attributes)
e.g. foreign key (x,y) references R(a,b)
Primary key primary key primary key (Attributes)
e.g. primary key (x)
implies x is not null & unique

Defining Things

Tables create table Name (Attributes+Constraints);
e.g. 
create table R (
   s   text primary key,
   x   integer not null,
   y   real check (y > 0),
   foreign key (x) references T(x)
);
Views drop view if exists Name;
create or replace view Name
as SQL_Statement
e.g. 
drop view if exists Movies;
create view Movies as
select mid as id, name as title
from   Titles
where  format = 'movie';
Functions No stored procedures in SQLite
Domains No domain definitions in SQLite
Enums No enums in SQLite

Queries

General format 
select Expressions as Names 
from   Table1
       join Table2 on JoinCondition1
       ...
       join Tablen on JoinConditionn-1
where  Condition
group  by Attributes
having    Condition
order  by Attributes
Examples 
-- assume schema: R(x,y,z), S(a,b)

-- show all info from table R
select * from R;

-- show unique x values from table R
select distinct x from R;

-- show unique x values from table R
select *
from   R join S on R.x = S.a
where  R.y > 5;

-- how many of each x value
select x, count(*) from  R group by x;

-- how many of each common x value
select x, count(*)
from   R
group  by x
having count(*) > 10;

-- how many of each R.x value is
-- associated with an S.a value;
-- if no associated S.a value, count = 0
select x, count(a)
from   R left outer join S on R.x = S.a
group  by x;
Notes
  • no right or full outer joins

Commands outside sqlite3

Getting a list of databases ls
Connect to a database to ask SQL queries sqlite3 DatabaseName
or, in the exam, simply sql
Create a database sqlite3 DatabaseName
Remove a database rm DatabaseName

Commands within sqlite3

Exit the SQL shell .q or control-D
Get a list of tables/views in a database .schema
Execute an SQL statement select * from TableName;
Excecute SQL commands from a file .read FileName
Go to previous command Up-arrow
Create a view drop view if exists V;
create or replace V(a,b,c)
as select x,y,z ...
Union, Intersect, Difference SelectStatement1 union SelectStatement2