Advanced Database Concepts - Review of Assignment 2
PostgreSQL - How to solve complicated queries

Problem 1

Find the bid and title of each book that costs between $10 and $40 and that was bought by a student who majors in both CS and Math.

Break down the problem

• find the bid and title of each book(b)
  • book(b) costs between $10 and $40
  • book(b) bought(y) by a student(s)
    • student(s) majors(m) in CS and
    • student(s) majors(m) in Math

combine the first two

• find the bid and title of each book(b) costs between $10 and $40
  • book(b) bought(y) by a student(s)
    • student(s) majors(m) in CS and
    • student(s) majors(m) in Math

Query using IN

select b.bookno, b.title from book b
where b.price >= 10 and b.price <=40 and b.bookno in (
    select y.bookno from buys y, student s where y.sid = s.sid and s.sid in (
        (select m.sid from major m where m.mojor = ‘CS’)
        intersect
        (select m.sid from major m where m.mojor = ‘Math’)
    )
);

Query using EXISTS

select b.bookno, b.title from book b
where b.price >= 10 and b.price <=40 and exists (
    select * from buys y, student s
    where b.bookno = y.bookno and y.sid = s.sid and s.sid in (
        (select m.sid from major m where m.major = 'CS')
        intersect
        (select m.sid from major m where m.major = 'Math')
    )
);

Problem 2

Find the sid and name of each student who bought a book that is cited by a higher-priced book.

Beak down the problem

• find sid and name of each student(s)
  • student(s) bought(y) a book(b)
    • book(b) is cited(c) by a book(k)
      • price of book(k) is higher than price of book(b)

Query using EXISTS

select s.sid, s.sname from student s
where exists (
    select * from buys y, book b
    where s.sid = y.sid and y.bookno = b.bookno and exists (
        select * from cites c, book k
        where b.bookno = c.citedbookno and c.bookno = k.bookno
            and k.price > b.price
    )
);

Query using IN

select s.sid, s.sname from student s
where s.sid in (
    select y.sid from buys y, book b
    where y.bookno = b.bookno and b.bookno in (
        select c.citedbookno from cites c, book k
        where c.bookno = k.bookno and k.price > b.price
    )
);

Here we can see, EXISTS and IN are interchangeable.

Query using SOME. When comparing some number, we can use SOME

select distinct s.sid, s.sname from student s
where exists (
    select * from buys y, book b
    where s.sid = y.sid and y.bookno = b.bookno and b.price < SOME (
        select k.price from cites c, book k
        where b.bookno = c.citedbookno and c.bookno = k.bookno
    )
)
order by s.sid, s.sname;

DISTINCT is optional. Since the result is a bag, it could contain duplicate items, you can use DISTINCT to eliminate duplicate items.
ORDER BY is optional. Just to order the items in result.

Problem 3

Find the bookno of each book that cites another book b. Furthermore, b should be a book cited by at least two books.

Break down the problem

• Find the bookno of each book(k)
  • book(k) cites another book(b)
    • book(b) is cited(C1) by book(b1), and
    • book(b) is cited(c2) by book(b2), and
    • book(b1) and book(b2) are different
      Actually, the query of book(k), book(b), book(b1) and book(b2) are non-necessary.

Reinterpret the problem

• find bookno in cite(c1)
  • citedbookno in cite(c1) is the same of that in cite(c2), and
  • bookno in cite(c1) is different from that in cite(c2)

Query

select distinct c1.bookno from cites c1, cites c2
where c1.bookno <> c2.bookno and c1.citedbookno = c2.citedbookno
order by c1.bookno;

Problem 4

Find the bid of each book that was not bought by any student.

Break down the problem

• find the bid of each book(b)
  • book(b) was not bought by any student

Query using NOT IN

select b.bookno from book b where b.bookno not in (
    select y.bookno from buys y
);

Problem 5

Find the sid of each student who did not buy all books that cost more than $50.

Understanding the problem

In other words, find the sid of each student for whom there exists a book that cost more than $50 and that is not among the books bought by that student.

Break down the problem

• find the sid of each student(s)
  • there exists one book(b) cost more than $50
    • book(b) is not bought(y) by student(s)

Query using EXISTS

select s.sid from student s where exists (
    select * from book b
    where b.price > 50 and b.bookno not in (
        select y.bookno from buys y where y.sid = s.sid
    )
);

Problem 6

Find the bookno of each book that was bought by a student who majors in CS but that was not bought by any student who majors in Math.

Break down the problem

• find bookno of each book(b)
  • book(b) bought(y1) by a student(s1)
    • student(s1) majors(m2) in CS
  • book(b) was not bought(y2) by any student(s2)
    • student(s2) majors(m2) in Math
      Actually, the query of book(b), student(s1) and student(s2) are non-necessary.
      From Problem 3 and 6, we can see, there is no need to do the specific query of some item in the action relations such as cites and buys.

Reinterpret the problem

• the set of bookno of buys(y1) of student that majors(m1) in CS
• except
• the set of bookno of buys(y2) of student that majors(m2) in Math

Query using EXCEPT

select * from (
    (
        select y1.bookno from buys y1, major m1
        where y1.sid = m1.sid and m1.major = 'CS'
    )
    except(
        select y2.bookno from buys y2, major m2
        where y2.sid = m2.sid and m2.major = 'Math'
    )
) u order by u.bookno;

Reinterpret the problem

• find bookno of buys(y1) of student that majors(m1) in CS
  • bookno of buys(y1) is not in bookno of buys(y2) of student that majors(m2) in Math

Query using NOT IN

select distinct y1.bookno from buys y1, major m1
where y1.sid = m1.sid and m1.major = 'CS' and y1.bookno not in (
    select distinct y2.bookno from buys y2, major m2
    where y2.sid = m2.sid and m2.major = 'Math'
) order by y1.bookno;

Problem 7

Find the sid and name of each student who has at single major and who only bought books that cite other books.

Understanding the problem

Find the sid of each student who has at single major and such that there does not exist a book bought by that student that is not among the books that cite other books.

Break down the problem

• Find the sid of each student(s)
  • [student has at single major] => student(s) in
    • the set of student that has major(m)
    • except
    • the set of student in major(m1) and major(m2)
      • major(m1) and major(m2) are different
  • [student only bought books that cite other books] => not exists
    • a book bought(y) by student(s) is not in
      • the set of books that cite(c) other books

Query

select s.sid, s.sname from student s
where s.sid in (
    (select m.sid from major m)
    except(
        select m1.sid from major m1, major m2
        where m1.sid = m2.sid and m1.major <> m2.major
    )
) and not exists (
    select * from buys y where y.sid = s.sid and y.bookno not in (
        select c.bookno from cites c
    )
);

Problem 8

Find the sid and major of each student who did not buy any book that cost less than $30.

Reinterpret the problem

Find the sid and major of each student such that there not exists a book cost less than $30 that among the books bought by the student.

Break down the problem

• find the sid and major(m) of each student(s), not exists
  • a book(b) cost less than 30 not in
    • books bought(y) by student(s)

Query using EXISTS

select s.sid, m.major from student s, major m
where s.sid = m.sid and not exists (
    select * from book b where b.price <30 and b.bookno in (
        select y.bookno from buys y where y.sid = s.sid
    )
);

Alternative reinterpret the problem

Find the sid and major of each student such that there not exists a book bought by the student that among the books cost less than $30.

Break down the problem

• find the sid and major(m) of each student(s), not exists
  • a book(b) bought(y) by student(s) not in
    • books cost less than 30

Query using EXISTS

select s.sid, m.major from student s, major m
where s.sid = m.sid and not exists (
    select * from buys y where y.sid = s.sid and y.bookno in (
        select b.bookno from book b where b.price < 30
    )
);

Problem 9

Find each (s,b) pair where s is the sid of a student and b is the bookno of a book whose price is the highest among the books bought by that student.

Break down the problem

• find sid of student(s) and bookno of book(b)
  • price of book(b) that bought by the student(s) is highest among
    • set of books(k) bought(y) by student(s)

Query using ALL

select y1.sid, b1.bookno from buys y1, book b1
where y1.bookno = b1.bookno and b1.price >= all (
    select b2.price from book b2, buys y2
    where b2.bookno = y2.bookno and y2.sid = y1.sid
);

Reinterpret the problem

Find each $(s,b)$ pair where $s$ is the sid of a student and $b$ is the bookno of a book that bought by the student such that there not exists a book bought by the student that the price of it is not higher than the book.

Break down the problem

• find sid of a student and bookno of a book(b1) bought(y1) by the student, not exists
  • price of book(k1) bought(y2) by the student higher than that of book(b)

Query using NOT EXISTS

select y1.sid, b1.bookno from buys y1, book b1
where y1.bookno = b1.bookno and not exists (
    select * from buys y2, book b2
    where y2.bookno = b2.bookno and y2.sid = y1.sid and b2.price > b1.price
);

Problem 10

Without using the ALL predicate, list the price of the next to most expensive books.

Reinterpret the problem

find the price of book(b) that there exists a book(b1) whose price is higher than that of book(b) and there not exist two books(b2,b3) whose prices are satisfying b.price < b2.price and b2.price < b3.price.

Query using EXISTS and NOT EXISTS

select distinct b.price from book b where exists (
    select * from book b1 where b.price < b1.price
) and not exists (
    select * from book b2, book b3
    where b.price < b2.price and b2.price < b3.price
);

Problem 11 ？？

Find the triples (s,b1,b2) where s is the sid of a student who if he or she bought book b1 then he or she also bought book b2. Furthermore, b1 and b2 should be different.

Query using EXCEPT

select count(*) from (
    (
        select s.sid, b1.bookno, b2.bookno
        from student s, book b1, book b2
        where b1.bookno <> b2.bookno
    )
    except(
        select s.sid, b1.bookno, b2.bookno
        from student s, book b1, book b2
        where (s.sid, b1.bookno) in (
            select y.sid, y.bookno from buys y
        )
        and (s.sid, b2.bookno) not in (
            select y.sid, y.bookno from buys y
        )
    )
) s;

Query using UNION

select count(*) from (
    (
        select s.sid, b1.bookno, b2.bookno
        from buys s, buys b1, buys b2
        where b1.bookno <> b2.bookno and b1.sid = s.sid and b2.sid = s.sid
    )
    union (
        select b1.bookno, b2.bookno, s.sid
        from book b1, book b2, student s
        where b1.bookno <> b2.bookno and b1.bookno not in (
            select y.bookno from buys y where y.sid = s.sid
        )
    )
) u;

Problem 12 ？？

Find the sid of each student who bought none of the books cited by book with bookno 2001.

Reinterpret the problem

Find the sid of each student there not exists a book bought by the student that the book is cited by the book with bookno 2001.

Break down the problem

• find sid of each student(s), not exists
  • books bought(y) by student(s) in
    • books cited(c) by student(s)

Query using NOT EXISTS and IN

select s.sid from student s where NOT EXISTS (
    select * from buys y where y.sid = s.sid and y.bookno in (
        select c.citedbookno from cites c where c.bookno = 2001
    )
);

Problem 13 ？？

Find the tuples (b1,b2) where b1 and b2 are the booknos of two different books that were bought by exactly one CS student.

Query

select distinct y1.bookno, y2.bookno
from buys y1, buys y2, major m
where y1.bookno <> y2.bookno and y1.sid = y2.sid and
y1.sid = m.sid and m.major = 'CS' and not exists (
    select m1.sid from major m1, buys y3, buys y4
    where m1.major = 'CS' and m1.sid <> m.sid and
    y3.sid = y4.sid and y3.sid = m1.sid and 
    y3.bookno = y1.bookno and y4.bookno = y2.bookno
)
order by y1.bookno, y2.bookno;

Problem 14 ？？

Find the sid of each student who only bought books whose price is greater than the price of any book that was bought by all students who majors in ’Math’.

Query

select s.sid from buys s where not exists (
    select y.bookno from buys y, book b
    where y.sid = s.sid and y.bookno = b.bookno and b.price <= some (
        select distinct b.price from book b where not exists (
            select m.sid from major m 
            where m.major = 'Math' and m.sid not in (
                select y.sid from buys y where y.bookno = b.bookno
            )
        )
    )
);

Input Data

drop table buys;
drop table cites;
drop table book;
drop table major;
drop table student;

create table student(
   sid integer,
    sname varchar(15),
    primary key (sid)
);
create table major(
    sid integer,
    major varchar(15),
    primary key (sid, major),
    foreign key (sid) references student (sid)
);
create table book(
    bookno integer,
    title varchar(30),
    price integer,
    primary key (bookno)
);
create table cites(
    bookno integer,
    citedbookno integer,
    primary key (bookno, citedbookno),
    foreign key (bookno) references book (bookno),
    foreign key (citedbookno) references book (bookno)
);
create table buys(
    sid integer,
    bookno integer,
    primary key (sid, bookno),
    foreign key (sid) references student (sid),
    foreign key (bookno) references book (bookno)
);

-- Data for the student relation.
INSERT INTO student VALUES(1001,'Jean');
INSERT INTO student VALUES(1002,'Maria');
INSERT INTO student VALUES(1003,'Anna');
INSERT INTO student VALUES(1004,'Chin');
INSERT INTO student VALUES(1005,'John');
INSERT INTO student VALUES(1006,'Ryan');
INSERT INTO student VALUES(1007,'Catherine');
INSERT INTO student VALUES(1008,'Emma');
INSERT INTO student VALUES(1009,'Jan');
INSERT INTO student VALUES(1010,'Linda');
INSERT INTO student VALUES(1011,'Nick');
INSERT INTO student VALUES(1012,'Eric');
INSERT INTO student VALUES(1013,'Lisa');
INSERT INTO student VALUES(1014,'Filip');
INSERT INTO student VALUES(1015,'Dirk');
INSERT INTO student VALUES(1016,'Mary');
INSERT INTO student VALUES(1017,'Ellen');
INSERT INTO student VALUES(1020,'Ahmed');

-- Data for the book relation.
INSERT INTO book VALUES(2001,'Databases',40);
INSERT INTO book VALUES(2002,'OperatingSystems',25);
INSERT INTO book VALUES(2003,'Networks',20);
INSERT INTO book VALUES(2004,'AI',45);
INSERT INTO book VALUES(2005,'DiscreteMathematics',20);
INSERT INTO book VALUES(2006,'SQL',25);
INSERT INTO book VALUES(2007,'ProgrammingLanguages',15);
INSERT INTO book VALUES(2008,'DataScience',50);
INSERT INTO book VALUES(2009,'Calculus',10);
INSERT INTO book VALUES(2010,'Philosophy',25);
INSERT INTO book VALUES(2012,'Geometry',80);
INSERT INTO book VALUES(2013,'RealAnalysis',35);
INSERT INTO book VALUES(2011,'Anthropology',50);

-- Data for the buys relation.
INSERT INTO buys VALUES(1001,2002);
INSERT INTO buys VALUES(1001,2007);
INSERT INTO buys VALUES(1001,2009);
INSERT INTO buys VALUES(1001,2011);
INSERT INTO buys VALUES(1001,2013);
INSERT INTO buys VALUES(1002,2001);
INSERT INTO buys VALUES(1002,2002);
INSERT INTO buys VALUES(1002,2007);
INSERT INTO buys VALUES(1002,2011);
INSERT INTO buys VALUES(1002,2012);
INSERT INTO buys VALUES(1002,2013);
INSERT INTO buys VALUES(1003,2002);
INSERT INTO buys VALUES(1003,2007);
INSERT INTO buys VALUES(1003,2011);
INSERT INTO buys VALUES(1003,2012);
INSERT INTO buys VALUES(1003,2013);
INSERT INTO buys VALUES(1004,2006);
INSERT INTO buys VALUES(1004,2007);
INSERT INTO buys VALUES(1004,2008);
INSERT INTO buys VALUES(1004,2011);
INSERT INTO buys VALUES(1004,2012);
INSERT INTO buys VALUES(1004,2013);
INSERT INTO buys VALUES(1005,2007);
INSERT INTO buys VALUES(1005,2011);
INSERT INTO buys VALUES(1005,2012);
INSERT INTO buys VALUES(1005,2013);
INSERT INTO buys VALUES(1006,2006);
INSERT INTO buys VALUES(1006,2007);
INSERT INTO buys VALUES(1006,2008);
INSERT INTO buys VALUES(1006,2011);
INSERT INTO buys VALUES(1006,2012);
INSERT INTO buys VALUES(1006,2013);
INSERT INTO buys VALUES(1007,2001);
INSERT INTO buys VALUES(1007,2002);
INSERT INTO buys VALUES(1007,2003);
INSERT INTO buys VALUES(1007,2007);
INSERT INTO buys VALUES(1007,2008);
INSERT INTO buys VALUES(1007,2009);
INSERT INTO buys VALUES(1007,2010);
INSERT INTO buys VALUES(1007,2011);
INSERT INTO buys VALUES(1007,2012);
INSERT INTO buys VALUES(1007,2013);
INSERT INTO buys VALUES(1008,2007);
INSERT INTO buys VALUES(1008,2011);
INSERT INTO buys VALUES(1008,2012);
INSERT INTO buys VALUES(1008,2013);
INSERT INTO buys VALUES(1009,2001);
INSERT INTO buys VALUES(1009,2002);
INSERT INTO buys VALUES(1009,2011);
INSERT INTO buys VALUES(1009,2012);
INSERT INTO buys VALUES(1009,2013);
INSERT INTO buys VALUES(1010,2001);
INSERT INTO buys VALUES(1010,2002);
INSERT INTO buys VALUES(1010,2003);
INSERT INTO buys VALUES(1010,2011);
INSERT INTO buys VALUES(1010,2012);
INSERT INTO buys VALUES(1010,2013);
INSERT INTO buys VALUES(1011,2002);
INSERT INTO buys VALUES(1011,2011);
INSERT INTO buys VALUES(1011,2012);
INSERT INTO buys VALUES(1012,2011);
INSERT INTO buys VALUES(1012,2012);
INSERT INTO buys VALUES(1013,2001);
INSERT INTO buys VALUES(1013,2011);
INSERT INTO buys VALUES(1013,2012);
INSERT INTO buys VALUES(1014,2008);
INSERT INTO buys VALUES(1014,2011);
INSERT INTO buys VALUES(1014,2012);
INSERT INTO buys VALUES(1017,2001);
INSERT INTO buys VALUES(1017,2002);
INSERT INTO buys VALUES(1017,2003);
INSERT INTO buys VALUES(1017,2008);
INSERT INTO buys VALUES(1017,2012);
INSERT INTO buys VALUES(1020,2012);

-- Data for the cites relation.
INSERT INTO cites VALUES(2012,2001);
INSERT INTO cites VALUES(2008,2011);
INSERT INTO cites VALUES(2008,2012);
INSERT INTO cites VALUES(2001,2002);
INSERT INTO cites VALUES(2001,2007);
INSERT INTO cites VALUES(2002,2003);
INSERT INTO cites VALUES(2003,2001);
INSERT INTO cites VALUES(2003,2004);
INSERT INTO cites VALUES(2003,2002);

-- Data for the cites relation.
INSERT INTO major VALUES(1001,'Math');
INSERT INTO major VALUES(1001,'Physics');
INSERT INTO major VALUES(1002,'CS');
INSERT INTO major VALUES(1002,'Math');
INSERT INTO major VALUES(1003,'Math');
INSERT INTO major VALUES(1004,'CS');
INSERT INTO major VALUES(1006,'CS');
INSERT INTO major VALUES(1007,'CS');
INSERT INTO major VALUES(1007,'Physics');
INSERT INTO major VALUES(1008,'Physics');
INSERT INTO major VALUES(1009,'Biology');
INSERT INTO major VALUES(1010,'Biology');
INSERT INTO major VALUES(1011,'CS');
INSERT INTO major VALUES(1011,'Math');
INSERT INTO major VALUES(1012,'CS');
INSERT INTO major VALUES(1013,'CS');
INSERT INTO major VALUES(1013,'Psychology');
INSERT INTO major VALUES(1014,'Theater');
INSERT INTO major VALUES(1017,'Anthropology');

select * from student;
select * from major;
select * from book;
select * from cites;
select * from buys;