Suggested Solution

1. Suggested Solution 6a) Select title, acode from book; where category = ‘IT’ or category = ‘COMP’ select title, acode from book where category in (‘IT’,’COMP’)

2. Suggested Solution 6b) Select avg(price) from book

3. Suggested Solution 6c) Select max(price-cost) from book

4. Suggested Solution 6d) select acode from book where price-cost = (select max(price-cost) from book)

5. Suggested Solution 6e) select category, count(title) from book group by category

6. Suggested Solution 6f) select distinct a.acode from book as a, book as b where a.acode=b.acode and a.pcode <> b.pcode

7. Suggested Solution 6f) select acode from book group by acode having count(pcode) > 1

8. Suggested Solution 6g) select acode, count(distinct pcode) from book as a where pcode in (select pcode from book as b where a.acode=b.acode) group by acode

9. Suggested Solution 6h) Duplication of data will result if author’s data and / or publisher’s data are stored in table ‘book.dbf’ for information of each author/publisher will be repeated for every copy of book written/published by an author / a publisher Data duplication will give rise to data redundancy and data inconsistency

10. Suggested Solution 7a) select rnum from route where fee < 5

11. Suggested Solution 7b) select rnum from route where tc1=tc2

12. Suggested Solution 7c) select avg(fee) from route

13. Suggested Solution 7d) select max(fee) from route

14. Suggested Solution 7e) select rnum from route where fee=(select max(fee) from route)

15. Suggested Solution 7f) select min(iif(first1<first2,first1,first2)) from route

16. Suggested Solution 7g) select count(rno) from route where rnum=’007’

17. Suggested Solution 7h) select rnum, count(rno) from bus group by rnum

18. Suggested Solution 7i) select avg(fee) from route as r, bus as b where r.rnum = b.rnum

19. Suggested Solution 7j) select count(rno) from route as a, bus as b where r.rnum=b.rnum and (tc1=‘TST’ or tc2=‘TST’)