[ramakris@arabidopsis ~]$ psql -U naren -d example
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

example=#

example=# \d
No relations found.

example=# create table courses (
example(# courseid INTEGER,
example(# univnumber CHAR(10),
example(# name VARCHAR(30),
example(# difficulty INTEGER
example(# );
CREATE

example=# \d
    List of relations
  Name   | Type  | Owner
---------+-------+-------
 courses | table | naren
(1 row)

example=# \d courses;
                Table "courses"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 courseid   | integer               |
 univnumber | character(10)         |
 name       | character varying(30) |
 difficulty | integer               |

example=# select * from courses;
 courseid | univnumber | name | difficulty
----------+------------+------+------------
(0 rows)

example=# INSERT INTO courses
example-# VALUES(1,'CS 5984','Computation for the Life Sciences',1.0);
ERROR:  value too long for type character varying(30)

example=# INSERT INTO courses
example-# VALUES(1,'CS 5984','Computation 4 Life',1.0);
INSERT 246423 1

example=# select * from courses;
 courseid | univnumber |        name        | difficulty
----------+------------+--------------------+------------
        1 | CS 5984    | Computation 4 Life |          1
(1 row)

example=# delete from courses
example-# where courseid=1;
DELETE 1

example=# select * from courses;
 courseid | univnumber | name | difficulty
----------+------------+------+------------
(0 rows)

example=# INSERT INTO courses
example-# VALUES(1,'CS 5984','Computation 4 Life',1);
INSERT 246424 1

example=# INSERT INTO courses
example-# VALUES(1,'CS 4604','intro to db',3.5);
INSERT 246425 1

example=# select * from courses;
 courseid | univnumber |        name        | difficulty
----------+------------+--------------------+------------
        1 | CS 5984    | Computation 4 Life |          1
        1 | CS 4604    | intro to db        |          4
(2 rows)

example=# select * from courses
example-# where difficulty > 3;
 courseid | univnumber |    name     | difficulty
----------+------------+-------------+------------
        1 | CS 4604    | intro to db |          4
(1 row)

example=# select * from courses where difficulty > 3;
 courseid | univnumber |    name     | difficulty
----------+------------+-------------+------------
        1 | CS 4604    | intro to db |          4
(1 row)

example=# select * from courses;
 courseid | univnumber |        name        | difficulty
----------+------------+--------------------+------------
        1 | CS 5984    | Computation 4 Life |          1
        1 | CS 4604    | intro to db        |          4
(2 rows)

example=# drop table courses;
DROP

example=# select * from courses;
ERROR:  Relation "courses" does not exist

example=# create table courses (
example(# courseid INTEGER PRIMARY KEY,
example(# univnumber CHAR(10),
example(# name VARCHAR(30),
example(# difficulty INTEGER
example(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'courses_pkey' for table 'courses'
CREATE

example=# select * from courses;
 courseid | univnumber | name | difficulty
----------+------------+------+------------
(0 rows)

example=# \d courses;
                Table "courses"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 courseid   | integer               | not null
 univnumber | character(10)         |
 name       | character varying(30) |
 difficulty | integer               |
Primary key: courses_pkey

example=# drop table courses;
DROP

example=# create table courses (
example(# courseid INTEGER PRIMARY KEY,
example(# univnumber CHAR(10) NOT NULL,
example(# name VARCHAR(30),
example(# difficulty INTEGER DEFAULT 2
example(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'courses_pkey' for table 'courses'
CREATE

example=# \d courses;
                Table "courses"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 courseid   | integer               | not null
 univnumber | character(10)         | not null
 name       | character varying(30) |
 difficulty | integer               | default 2
Primary key: courses_pkey

example=# INSERT INTO courses
example-# VALUES(1,'CS 5984','Computation 4 Life',1);
INSERT 246443 1

example=# INSERT INTO courses
example-# VALUES(1,'CS 4604','intro to db',3.5);
ERROR:  Cannot insert a duplicate key into unique index courses_pkey

example=# INSERT INTO courses
example-# VALUES(2,'CS 4604','intro to db',3.5);
INSERT 246445 1

example=# select * from courses;
 courseid | univnumber |        name        | difficulty
----------+------------+--------------------+------------
        1 | CS 5984    | Computation 4 Life |          1
        2 | CS 4604    | intro to db        |          4
(2 rows)

example=# insert into courses
example-# values(3,'CS 3414','numerical methods');
INSERT 246446 1

example=# insert into courses
example-# values(3,'CS 3414','numerical methods');
INSERT 246446 1
example=# select * from courses;
 courseid | univnumber |        name        | difficulty
----------+------------+--------------------+------------
        1 | CS 5984    | Computation 4 Life |          1
        2 | CS 4604    | intro to db        |          4
        3 | CS 3414    | numerical methods  |          2
(3 rows)

example=# insert into courses
example-# values(4,'graphics');
INSERT 246447 1

example=# select * from courses;
 courseid | univnumber |        name        | difficulty
----------+------------+--------------------+------------
        1 | CS 5984    | Computation 4 Life |          1
        2 | CS 4604    | intro to db        |          4
        3 | CS 3414    | numerical methods  |          2
        4 | graphics   |                    |          2
(4 rows)

example=# delete from courses where courseid=4;
DELETE 1

example=# select * from courses;
 courseid | univnumber |        name        | difficulty
----------+------------+--------------------+------------
        1 | CS 5984    | Computation 4 Life |          1
        2 | CS 4604    | intro to db        |          4
        3 | CS 3414    | numerical methods  |          2
(3 rows)

example=# insert into courses(courseid,name)
example-# values(4,'graphics');
ERROR:  ExecAppend: Fail to add null value in not null attribute
univnumber

example=# select * from courses;
 courseid | univnumber |        name        | difficulty
----------+------------+--------------------+------------
        1 | CS 5984    | Computation 4 Life |          1
        2 | CS 4604    | intro to db        |          4
        3 | CS 3414    | numerical methods  |          2
(3 rows)

example=# insert into courses(courseid,univnumber)
example-# values(4,'CS 4104');
INSERT 246448 1

example=# select * from courses;
 courseid | univnumber |        name        | difficulty
----------+------------+--------------------+------------
        1 | CS 5984    | Computation 4 Life |          1
        2 | CS 4604    | intro to db        |          4
        3 | CS 3414    | numerical methods  |          2
        4 | CS 4104    |                    |          2
(4 rows)

example=# create table students (
example(# sid INTEGER PRIMARY KEY,
example(# name CHAR(30),
example(# address VARCHAR(255),
example(# gender CHAR(1),
example(# age INTEGER,
example(# gpa FLOAT
example(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'students_pkey' for table 'students'
CREATE

example=# \d
    List of relations
   Name   | Type  | Owner
----------+-------+-------
 courses  | table | naren
 students | table | naren
(2 rows)

example=# \d students;
               Table "students"
 Column  |          Type          | Modifiers
---------+------------------------+-----------
 sid     | integer                | not null
 name    | character(30)          |
 address | character varying(255) |
 gender  | character(1)           |
 age     | integer                |
 gpa     | double precision       |
Primary key: students_pkey

example=# insert into students
example-# values(1,'Mark','Blacskburg','M',23,4.0);
INSERT 246452 1

example=# delete from students where id=1;
ERROR:  Attribute 'id' not found

example=# delete from students where sid=1;
DELETE 1

example=# insert into students
example-# values(1,'Mark','Blacksburg','M',23,4.0);
INSERT 246454 1

example=# select * from students;
 sid |              name              |  address   | gender | age | gpa
-----+--------------------------------+------------+--------+-----+-----
   1 | Mark                           | Blacksburg | M      |  23 |   4
(1 row)

example=# insert into students
example-# values(2,'Kathy','Blacksburg','F',21,4.0);
INSERT 246455 1

example=# create table takes (
example(# sid INTEGER,
example(# cid INTEGER,
example(# PRIMARY KEY (sid,cid)
example(# );
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
'takes_pkey' for table 'takes'
CREATE

example=# insert into takes values(1,1);
INSERT 246459 1
example=# insert into takes values(1,3);
INSERT 246460 1
example=# insert into takes values(2,2);
INSERT 246461 1
example=# insert into takes values(2,4);
INSERT 246462 1
example=# insert into takes values(2,3);
INSERT 246463 1

example=# select * from takes;
 sid | cid
-----+-----
   1 |   1
   1 |   3
   2 |   2
   2 |   4
   2 |   3
(5 rows)

example=# select name,difficulty
example-# from students,takes,courses
example-# where students.sid = takes.sid AND
example-# takes.cid = courses.courseid;
ERROR:  Column reference "name" is ambiguous

example=# select students.name,difficulty
example-# from students,takes,courses
example-# where students.sid = takes.sid AND
example-# takes.cid = courses.courseid;
              name              | difficulty
--------------------------------+------------
 Mark                           |          1
 Kathy                          |          4
 Mark                           |          2
 Kathy                          |          2
 Kathy                          |          2
(5 rows)

example=# \q
[ramakris@arabidopsis ~]$ psql -U naren -d example
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

example=# \d
    List of relations
   Name   | Type  | Owner
----------+-------+-------
 courses  | table | naren
 students | table | naren
 takes    | table | naren
(3 rows)

example=# select * from takes;
 sid | cid
-----+-----
   1 |   1
   1 |   3
   2 |   2
   2 |   4
   2 |   3
(5 rows)

example=# create view justforme(name,address) AS
example-# select students.name, students.address
example-# from students,takes,courses
example-# where students.sid = takes.sid
example-# AND takes.cid = courses.courseid
example-# AND courses.univnumber='CS 3414';
CREATE

example=# \d
     List of relations
   Name    | Type  | Owner
-----------+-------+-------
 courses   | table | naren
 justforme | view  | naren
 students  | table | naren
 takes     | table | naren
(4 rows)

example=# select * from justforme;
              name              |  address
--------------------------------+------------
 Mark                           | Blacksburg
 Kathy                          | Blacksburg
(2 rows)

example=# \q
[ramakris@arabidopsis ~]$ psql -U naren -d example
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

example=# \d
     List of relations
   Name    | Type  | Owner
-----------+-------+-------
 courses   | table | naren
 justforme | view  | naren
 students  | table | naren
 takes     | table | naren
(4 rows)

example=# select * from justforme;
              name              |  address
--------------------------------+------------
 Mark                           | Blacksburg
 Kathy                          | Blacksburg
(2 rows)

example=# \q

[ramakris@arabidopsis ~]$ psql -U naren -d example -c "select * from
justforme"
              name              |  address
--------------------------------+------------
 Mark                           | Blacksburg
 Kathy                          | Blacksburg
(2 rows)

[ramakris@arabidopsis ~]$ psql -U naren -d example -c "select * from
justforme" -o "justforme.file"

[ramakris@arabidopsis ~]$ cat justforme.file
              name              |  address
--------------------------------+------------
 Mark                           | Blacksburg
 Kathy                          | Blacksburg
(2 rows)

[ramakris@arabidopsis ~]$ psql -U naren -d example
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

example=# copy courses to stdout;
1       CS 5984         Computation 4 Life      1
2       CS 4604         intro to db     4
3       CS 3414         numerical methods       2
4       CS 4104         \N      2

example=# copy courses to stdout using delimiters '%';
1%CS 5984   %Computation 4 Life%1
2%CS 4604   %intro to db%4
3%CS 3414   %numerical methods%2
4%CS 4104   %\N%2

example=# \q
[ramakris@arabidopsis ~]$ psql -U naren -d example -c "copy courses to
stdout using delimiters '%'"
1%CS 5984   %Computation 4 Life%1
2%CS 4604   %intro to db%4
3%CS 3414   %numerical methods%2
4%CS 4104   %\N%2

[ramakris@arabidopsis ~]$ cat demo
create table demo (
   id INTEGER PRIMARY KEY,
   demoname CHAR(35)
);

INSERT into demo
VALUES(12,'Microsoft XP');

INSERT INTO DEMO
Values(1,'Flying Washing Machine');

INSERT into demo
VALUES(2,'Bird talking');

INSERT into demo
VALUES(3,'magic coffee pot');

[ramakris@arabidopsis ~]$ psql -U naren -d example -f demo
psql:demo:4: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
index 'demo_pkey' for table 'demo'
CREATE
INSERT 246470 1
INSERT 246471 1
INSERT 246472 1
INSERT 246473 1

[ramakris@arabidopsis ~]$ psql -U naren -d example
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

example=# \d
     List of relations
   Name    | Type  | Owner
-----------+-------+-------
 courses   | table | naren
 demo      | table | naren
 justforme | view  | naren
 students  | table | naren
 takes     | table | naren
(5 rows)

example=# select * from demo;
 id |              demoname
----+-------------------------------------
 12 | Microsoft XP
  1 | Flying Washing Machine
  2 | Bird talking
  3 | magic coffee pot
(4 rows)

example=# drop view justforme;
DROP

example=# \d
    List of relations
   Name   | Type  | Owner
----------+-------+-------
 courses  | table | naren
 demo     | table | naren
 students | table | naren
 takes    | table | naren
(4 rows)

example=# \q
[ramakris@arabidopsis ~]$ cat demo2
CREATE VIEW whatiwaslookingfor(name) AS
   SELECT demoname
   FROM demo
   where id = 1 or id = 3 or id =5;

[ramakris@arabidopsis ~]$ psql -U naren -d example -f demo2
CREATE

[ramakris@arabidopsis ~]$ psql -U naren -d example -c "copy
whatiwaslookingfor to stdout"
ERROR:  You cannot copy view whatiwaslookingfor

[ramakris@arabidopsis ~]$ psql -U naren -d example -c "select * from
whatiwaslookingfor"
                name
-------------------------------------
 Flying Washing Machine
 magic coffee pot
(2 rows)