psql -u -h goldfish
and enter your userid and password at the prompt. If you are outside the DCS network you can connect via ssh to ssh.dcs.ed.ac.uk and then proceed as above. You will find yourself in this amazingly clunky world of command-line interaction -- a great way to find out what programming was like 25 years ago!
[You should have your userid and password . Go to the JCMB if not.
If your password doesn't work, send me mail.]
Try entering something very simple like
table blah (blahblah integer);
insert into blah values(3);
insert into blah values(4);
select * from blah;
You rapidly discover that this is not the way to deal with
SQL and that you should be editing file
and opening it from SQL. This is done with the \i <filename> command.. So, for example , I created the file blah.txt with the following contents
create table likes(drinker char(10), beer char(10));
into likes values('Edward', 'Bud');
insert into likes values('Edward', 'Molson');
insert into likes values('James', 'Belhaven');
insert into likes values('James', 'Bud');
insert into likes values('James', 'Molson');
insert into likes values('Stuart', 'McEwans');
people and the beers they don't like
// PLEASE NOTE THE USE OF COMMENTS!!!
(select distinct l1.drinker, l2.beer from likes l1, likes l2)
(select * from likes);
Then I typed
A note on the first homework problem. Use VARCHAR(something) for the type of names. This way you can get at the last two characters with a combination of the SUBSTR and CHAR_LENGTH functions. Look in the user manual, chapter 5, for information on functions.
Notes on SQL:
This gives you an alternative way of breaking up queries into subqueries,
but this may not be optimal (shouldn't matter on small examples) and you
have to remember to delete the tables in the likely event that you got
things wrong the first time round!