Solutions for exercise set # 7 (given both in Lisp and in SQL):
; ; Find details of all projects in London ; (setq ans1 (retrieve '( ((j (jno jname))) (equal (j city) 'london)))) (format t "Ans 1: ~s ~%" ans1) ; ; Find JNAME values for projects supplied by supplier S1 ; (setq ans2 (retrieve '( ((j (jname))) (exists spj (and (equal (spj sno) 's1) (equal (spj jno) (j jno)) ) )))) (format t "Ans 2: ~s ~%" ans2) ; ; Get supplier name values for suppliers who supply project J1 ; (setq ans3 (retrieve '( ((s (sname))) (exists spj (and (equal (spj jno) 'j1) (equal (spj sno) (s sno)) ) )))) (format t "Ans 3: ~s ~%" ans3) ; ; Get supplier name values for suppliers who supply a ; London project with a red part ; (setq ans4 (retrieve '( ((s (sname))) (exists j (exists p (exists spj (and (equal (p color) 'red) (and (equal (j city) 'london) (and (equal (s sno) (spj sno)) (and (equal (p pno) (spj pno)) (equal (j jno) (spj jno)) ) ) ) )) ))))) (format t "Ans 4: ~s ~%" ans4) ; ; Get the names of parts used on every London project ; (setq ans5 (retrieve '( ((p (pname))) (forall j (exists spj (implies (equal (j city) 'london) (and (equal (p pno) (spj pno)) (equal (j jno) (spj jno)) ) ) ) )))) (format t "Ans 5: ~s ~%" ans5) ; ; That's all, folks! ;
/* Solutions in SQL. Find details of all projects in London */ select jno, jname from j where city = 'LONDON'; /* Find JNAME values for projects sppplied by supplier S1 */ select distinct JNAME from j, spj where j.jno = spj.jno and spj.sno = 'S1'; /* Find supplier names for suppliers who supply project J1 */ select distinct sname from s, spj where spj.jno = 'J1' and spj.sno = s.sno; /* Get supplier name values for suppliers who supply a London project with a red part. */ select distinct sname from s, p, j, spj where p.color = 'RED' and j.city = 'LONDON' and s.sno = spj.sno and p.pno = spj.pno and j.jno = spj.jno; /* Get the names of parts used on every LONDON project. */ select distinct pname from p where not exists (select * from j where j.city = 'LONDON' and not exists (select * from spj where p.pno = spj.pno and j.jno = spj.jno)); /* That's all, folks! */