CS61A-Discussion 11

Declarative Programming

Posted by Tianxiang Gao on June 19, 2015

1 Declarative Programming

Imperative programming is a programming style where code is written as a set of instructins for the computer. In this section, we introduce declarative programming - code that declares what we want, not how to compute it.

1.1 The SQL Language

Syntax

select [columns] from [tables] where [condition] order by [order]

2 Creating Tables

2.1 Questions

  1. select name from records where supervisor = 'Oliver Warbucks';
  2. select * from records where name = supervisor;
  3. select name from records where salary > 50000 order by name;

2.2 Joins

2.3 Questions

  1. select e.name, e.salary, s.name, s.salary 
        from records as e, records as s
        where e.supervisor = s.name and s.salary > 2 * e.salary;
    
  2. select e.name from records as e, records as s
        where e.supervisor = s.name and e.division <> s.division;
    
  3. 
    select day, time from records as r, meeting as m
        where r.division = m.division and r.suppervisor = 'Oliver Warbucks';
    

2.4 Extra Questions

  1. select b.name from records as a, records as b 
        where a.supervisor = b.name and b.supervisor <> b.name
    
  2. Alyssa P Hacker
    Ben Bitdiddle
    Ben Bitdiddle
    Ben Bitdiddle
    There are multiple people with Ben Bitdiddle as supervisor, and joining tables together does not remove these duplicates.
  3. select from records as e, records as s, meeting as em, meeting as sm
        where e.supervisor = s.name and e.division = em.division and s.division = sm.division and em.day = sm.day
    

Local Tables

Syntax

with
    table(columns) as (
    select [cols] from [nms] where [cond] order by [order]
    )

3.1 Recursion

create table natural as
    with num(n) as (
        select 0 union
        select n + 1from num
            where n < 3
    )
    select * from num;

3.2 Questions

  1. create table factorial as 
        with fact(n, nfact) (
            select 0, 1 union
            select n+1, nfact*n from fact
                where n < 10
        )
        select nfact from fact;
    
  2. create table squares as with sq(n, nsq) as ( select 1, 1 union select n+1, (n+1) * (n+1) from sq where n < 10 ) select nsq from sq;
  3. create table cycles_of_3 as 
        with mod_3(a, b, c) as (
            select 0, 1, 2 union
            select a + 3, b + 3, c + 3 from mod_3
            where a < 12
        )
        select * from mod_3;
    

3.3 Extra Questions

  1. create table permutation as
        select a.n as p, b.n as q, c.n as r 
        from natural as a, natural as b, natural as c;