• Week 03 Monday
• Context (Reminder)
• Assignment 1
• Exercise: Exploring the Assignment 1 Database
• Modifying a database
• Another Example Database: Beers/Bars/Drinkers
• Exercise: Delete/Update Examples
• SQL Query Language
• SQL Queries
• Semantics of SELECT..WHERE
• Semantics of JOIN
• Exercise: Queries on Simple Beer DB

❖ Week 03 Monday

In today's lecture ...

• Assignment 1 (briefly)
• Insert, delete, update
• SQL Query Language

Things to do ...

• Quiz 1 ... most got 4/4 ... 50 did not submit
• Quiz 2 due before Friday midnight
• Set up your PostgreSQL server
  (590 students have logged in to vxdb2 and have /localstorage)

❖ Context (Reminder)

Before typing commands, think about the context:

Prompt		Context
$ or %		in Linux shell run Unix commands, e.g. ls, cd
db=#		in psql run SQL commands, e.g. select, update
db>		in sqlite3 run SQL commands, e.g. select, update
none		in vim,   you are doomed but try :q or ZZ

❖ Assignment 1

Beer Database, with info on

• Beers: particular batches of beer (name,style,etc.)
• Breweries: companies that make beer
• Ingredients that go into making beer (e.g. hops, malt)
• Locations: places where Breweries are located

Goal:   write some SQL and PLpgSQL code to extract info

Due:   11:59pm Friday 13 October   (end week 5)

Supplied:   ass1.sql template,   ass1.dump

Submit:   ass1.sql

❖ Exercise: Exploring the Assignment 1 Database

Explore the ass1 database using psql

• what are the tables?
• how many beers are there?
• are there any views? any functions?
• what style of beer is Toohey's New?
• what is the longest beer name?
• are there duplicate beers? breweries?
• what is the most ridiculous beer name?

❖ Modifying a database

SQL commands for changing data in a database

insert into Table values ( Val1, Val2, ... )
delete from Table where Condition
update Table set Field = Value where Condition
create table Table ( Attributes/Constraints )
drop table Table
copy Table from ...   -- PostgreSQL specific

PostgreSQL commands for manipulating databases

dropdb DatabaseName
createdb DatabaseName
psql DatabaseName -f DumpFile
pg_dump DatabaseName > NewDumpFile

❖ Modifying a database (cont)

Examples of making changes to a database:

-- Student(id,name,degree),  Course(id,title,term)
-- Enrolment(stuID,crsID,grade,mark)

insert into Student(id,name,degree) values (5654321,'John',3778);
insert into Student(name,degree) values ('John',3778) returning id;
insert into Student(id,name,degree) values (default,'John',3778);

insert into Course values (987, 'COMP3311', '23T1);

insert into Enrolment(stuID,crsID) values(5654321, 987);

-- update mark for one student
update Enrolment set mark=75, grade='DN' where stuID = 5654321;
-- adjust marks for all students in the course
update Enrolment set mark=mark*1.1 where crsID = 987;

-- a student drops the course
delete from Enrolment where stuID = 5554321 and crsID = 987;

❖ Another Example Database: Beers/Bars/Drinkers

Consider the following ER model:

Note: this is not the Assignment 1 database

❖ Exercise: Delete/Update Examples

Carry out the following actions on the beer database:

• Insert a new beer "Jumping the Shark" from MoonDog
• Sell "Jumping the Shark" for $5.00 at Marble Bar
• Increase the prices for all beers by 10%
• Remove all drinkers who live in Randwick
• Remove all beers selling for more than 5.00
• Close down the Marble Bar (it really did close)

❖ SQL Query Language

Basic querying mechanism in SQL:

SELECT expressions
FROM   tables
WHERE  condition
ORDER  BY attributes

expressions are typically attribute names

tables usually involves JOIN on multiple tables

condition is a boolean expression on attributes

❖ SQL Queries

Functionality provided by SQL ...

Filtering: extract attributes from tuples, extract tuples from tables

SELECT b,c FROM R(a,b,c,d) WHERE a > 5

Combining: merging related tuples from different tables

... FROM R(x,y,z) JOIN S(a,b,c) ON R.y = S.a

Summarising: aggregating values in a single column

SELECT max(mark) FROM Enrolments ...

Set operations: union, intersection, difference

❖ SQL Queries (cont)

More functionality provided by SQL ...

Grouping: forming subsets of tuples sharing some property

... GROUP BY R.a

(forms groups of tuples from R sharing the same value of a)

Group Filtering: selecting only groups satisfying a condition

... GROUP BY R.a HAVING max(R.b) < 75

Renaming: assign a name to a component of a query

SELECT a as name
FROM Employee(a,b,c) e WHERE e.b > 50000

❖ Semantics of SELECT..WHERE

The query

SELECT * FROM R WHERE Condition

can be treated as

Results = {}
foreach tuple T in R {
   if T satisfies Condition
      add T to Results
}

❖ Semantics of JOIN

The query

SELECT * FROM R JOIN S ON R.x = S.y WHERE Condition

can be treated as

Results = {}
foreach tuple TR in R {
   foreach tuple TS in S {
      if TR.x == TS.y {
         Tjoined = TR+TS  # combined
         if Tjoined satisfies Condition
            add Tjoined to Results
      }
   }
}

❖ Exercise: Queries on Simple Beer DB

Answer these queries on the Beer database:

1. What beers are made by Toohey's?
2. Show beers with headings "Beer", "Brewer".
3. How many different beers are there?
4. How many different brewers are there?
5. Find pairs of beers by the same manufacturer.
6. (a) Which beers does John like?
   (b) Find the brewers whose beers John likes.
7. (a) How many beers does each brewer make?
   (b) Which brewer makes the most beers?
   (c) Which beers are the only one made by their brewer?