#
# Sample miniSQL script file.  
#

drop table test 
\p\g

drop table test2
\p\g


create table test (
	name	char(10),
	age	int,
	phone	char(20)
)
\p\g

create index idx1 on test (name)\p\g

create table test2 (
	user	char(10),
	name	char(30)
) \p\g

create index idx1 on test2 (user)\p\g

insert into test ( name, age, phone)
	values ( 'Bambi', 26, '(075) 951450')
\p\g


insert into test ( name, age, phone)
	values ( 'paulp', 25, '(075) 951412')
\p\g


insert into test2 (user, name) values ('paulp','Paul Pyyvaara') \p\g
insert into test2 (user, name) values ('Bambi','David J. Hughes') \p\g


#
# This has a NULL name field and should fail due to the "not null"
# clause implied by the primary key 
#

insert into test (age, phone)
	values ( 99, '9999')
\p\g



#
# This should fail because of the non-unique key value
#

insert into test (name, age, phone)
	values ('Bambi', 26, '(075) Home_Number')
\p\g


#
# Keyed lookup
#
select phone from test where name = 'Bambi'
\p\g


select * from test \p\g

#
# Paul's birthday :-)
#
update test set age = 26 where name = 'paulp'
\p\g


select * from test \p\g

#
# Non-key passed lookup
#
select * from test where phone = '(075) 951412'
\p\g



select * from test where name like '%am%'
\p\g


#
# Do a join
#

select test2.name, test.phone
	from test2,test
	where test2.user = test.name
\p\g

#
# Try a sorted one
#

select test2.name, test.phone
	from test2,test
	where test2.user = test.name
	order by test2.name 
\p\g



