Data Manipulation: Retrieval Operations

Using PosgreSQL

• \d will show you the tables that have been created in your database

• \? Will show you all the PostgreSQL commands (as opposed to SQL commands)

• \d tablename will show you the attributes of tablename

• drop table tablename; will drop a table so you can start again.

• select * from tablename; will show you all the data in your table.

• \q will quit psql

Inserting data

• Use the insert keyword:
INSERT INTO products VALUES (1, 'Cheese', 9.99);

• The data values are listed in the order in which the columns appear in the table, separated by commas.

• To avoid knowing the order of the columns you can also list the columns explicitly.

• INSERT INTO products (product_no, name, price) VALUES (1, 'Cheese', 9.99);
INSERT INTO products (name, price, product_no) VALUES ('Cheese', 9.99, 1);

• If you don't have values for all the columns, you can omit some of them. In that case, the columns will be filled with their default values.

Queries – retrieval of information

• For SQL queries, you use the SELECT statement
select the attributes
from tables
where certain conditions exist

– Select - list the attributes desired, separated by commas

– From - list the relation, separated by commas

– Where - the condition is true (selects the tuples)

• Select A1, A2,...An
From R1, R2,...Rm
Where predicate [and predicate] [or predicate]

The Select clause

• Query: Find the names of all branches in the loan relation.

• In relational algebra we would say:
_{Π branch-name} (Loan)

• In SQL it is:

select branch_name
from Loan;

Duplicate tuples now appear.

• Duplicates are not allowed in sets (i.e. relations)

– In practice, removing duplicate tuples is time consuming

– So SQL allows duplicates.

• To force the elimination of duplicate tuples use distinct
select distinct branch_name
from loan;

• In general, an SQL table is not required to have a key, although in most cases there will be one.

• The keyword all is used to specify not to remove duplicate tuples;

– it is the default.

Other options with Select

• The symbol * is used to denote "all the attributes” .
select *
from loan;

• The select clause can contain arithmetic expressions.
select branch_name, loan_num, amount*100
from loan;

The WHERE clause

• Query: Find all loan numbers for loans made at the Perryridge branch with loan amounts greater than $1200.

• In relational algebra we would say :
π _loan-number(σ_{branch-name=Perryridge AND amount >1200} Loan)

• In SQL it is:
select loan_num
from loan
where branch_name="Perryridge" and amount> 1200;

Using between with where

• The between and not between operators:
select loan_num
from loan
where amount between 9000 and 10000;

• This is equivalent to
(amount>90000) and (amount<10000)

Joining tables

• The FROM clause by itself defines a Cartesian product if multiple tables are named;

• You must use a where clause to make it a join.

• Query: For all customers who have a loan from the bank, find their names and the branch from which they have the loan.
select customer-name, branch-name
from borrower, loan
where borrower.loan_num = loan.loan_num;
(this is the join condition)

• If two attributes have the same name, they must be qualified by prefixing the relation name to the attribute name, separated by a dot.

• Query: Find the names and loan numbers of all customers who have a loan at the Perryridge branch.

•
select distinct c_name, borrower.loan_num
from borrower, loan
where borrower.loan_num=loan.loan_num
and branch_name=‘Perryridge’;

• You have two conditions here; one the join condition, the other the condition to select tuples

The Renaming (aliasing)

• You can rename either the tables or columns in a statement using as.

• Renaming a column
select distinct c_name,
borrower.loan_num as loan_id
from borrower, loan
where loan_id = loan.loan_num
and branch_name="Perryridge;

• The “as” is optional, and may be omitted.

• Renaming a table
select distinct c_name, B.loan_num
from borrower B, loan L
where B.loan_num=L.loan_num;

Mandatory renaming

• When a table is joined to itself, renaming becomes mandatory

• Query: For each employee, retrieve the employees first and last name and the first and last name of his or her immediate supervisor.

• select E.fname, E.lastname, S.fname, S.lname
from employee as E, employee as S
where E.superssn = S.ssn;

String Operations

• The most commonly used operation on strings is pattern matching.

• Two symbols describe patterns:

– 1) Percent (%): The % character matches any substring.

– 2) Underscore (_): The _ character replaces a single character

• Patterns are case sensitive

Examples using strings:

• ‘Perry%’ matches any string beginning with "Perry".

• ‘%idge%’ matches any string containing "idge" as a substring.

• ‘_ _ _’ matches any string of exactly three characters.

• ‘_ _ _%’ matches any string of at least three characters.

• ‘_ _5_ _ _ _ _ _ _’ matches any date in the 1950s

• The like operator is the comparison operator.
select c_name
from customer
where c_street like "%Main%";

Ordering the Display of Tuples

• order by - used to control the order in which the tuples are displayed.

• select distinct c_name
from borrower as B, loan as L
where B.loan_num=L.loan_num
and branch_name ="Perryridge”
order by c_name;

• The default is ascending order; but may specify using desc or asc.

• order by amount desc, loan_num asc;

• order by requires a sort, so may be costly.

Set Operations

• The operations union, intersect, and except operate about the same as their algebra counterparts. They are all union compatible.

• Each automatically eliminates duplicate tuples. If we want to retain all duplicates write union all (or intersect all, etc)

Set Operations: union

• Find all the customers having a loan, an account or both at the bank.
(select c_name from depositor)
union
(select c_name from borrower);

Set Operations:intersect & except

• Find all customers who have both a loan and an account at the bank.
(select distinct c_name from depositor)
intersect
(select distinct c_name from borrower);

Except (difference)query: Find all customers who have an account but no loan at the bank
(select distinct c_name from depositor)
except
(select distinct c_name from borrower);