[connect to]
CONNECT TO '{appldir}\db'

[basic select]
SELECT * FROM users

[order by]
SELECT * 
FROM users
ORDER BY #userid ASC, productid DESC

[group by]
SELECT products.productname AS product,count(users.userid) AS quantity
FROM users,products
WHERE users.productid=products.productid
GROUP BY product
ORDER BY product DESC

[having]
SELECT products.productname AS product,count(users.userid) AS quantity
FROM users,products
WHERE users.productid=products.productid
GROUP BY product
HAVING quantity>10
ORDER BY product DESC

[join 2 tables]
SELECT users.userid,users.username, products.productname
FROM users,products
WHERE users.productid=products.productid

[join 2 aliased tables]
SELECT u.userid,u.username, p.productname
FROM users u,products p
WHERE u.productid=p.productid


[join 3 tables]
SELECT users.username, products.productname, authors.authorname
FROM users, products, authors
WHERE (users.productid=products.productid) and products.authorid=authors.authorid)

[insert into]
INSERT INTO users (userid,username) VALUES (600,'user-600')

[update]
UPDATE users SET gender='male'
WHERE userid=600

[delete]
DELETE FROM users
WHERE userid=600

[delete]
DELETE FROM users

[calculated fields]
SELECT userid, username, (sofi*2) AS sofi2, sofi
FROM users

[output functions]
SELECT userid, UPPER(username) as username, (sofi*2) AS sofi2, sofi
FROM users

[alter table add column]
ALTER TABLE users ADD COLUMN email

[alter table drop column]
ALTER TABLE users DROP COLUMN email

[insert into select]
INSERT INTO users SELECT * FROM users

[update with calculation]
UPDATE users SET sofi=2*sofi

[update with format]
update users set userid=format(userid,'%.8d')

[dateadd function]
SELECT dateadd('m',1,birthday) AS newd, username
FROM users
WHERE userid=1

[easter function]
SELECT easter(year(date)) as easterdate, username FROM users


----------------------
[basic select]
select * from users

select count(userid) as aantal from users

[create table]
create table users (userid,username)

insert into users values(1,'Jan Verhoeven')

select username from users where userid='1' or userid='2'

select username from users where userid=1 or userid=2

select users.userid,users.username, products.productname from users,products where users.productid=products.productid

insert into users (userid,username) values (600,'user-600')

update users set sofi=45

select * from users where productid in (select product id from products where productname like 'Ico%')

select count(userid), username, productid from users group by productid having userid>10 order by productid


select products.productname as product,count(users.userid) as quantity from users,products where users.productid=products.productid group by product having quantity>10 order by product desc



select * from users order by #userid asc, productid desc


select format((userid),'%4.2f') as bob from users

select users.username, products.productname, authors.authorname
from users, products, authors
where (users.productid=products.productid) and (products.authorid=authors.authorid) 