Database Systems: Solved Assignments

BIT231: Database Systems
Assessment 2 - Software Application
Question 2.
Part B.
1.
SELECT *
FROM branch
WHERE no_employees BETWEEN 10 AND 15;
2.
SELECT * FROM publisher WHERE publisher_name REGEXP “t” ;
3.
SELECT * FROM book WHERE book_title LIKE “Databases”;
4.
SELECT * FROM author INNER JOIN wrote ON
author.author_no=wrote.author_no LEFT OUTER JOIN ON
wrote.book_code=book.book_code WHERE book.book_title=”Higher
Creativity”;
SELECT book FROM author INNER JOIN wrote ON
author.author_no=wrote.author_no LEFT OUTER JOIN ON
wrote.book_code=book.book_code WHERE book.book_title=”Higher
Creativity”;
5.
SELECT book.book_title FROM book inner join publisher WHERE
publisher_city=”NY” ON publisher.publisher_code=book.publisher_code;
6.
SELECT book.bookcode, book.book_title FROM book INNER JOIN wrote ON
book.bookcode=wrote.bookcode INNER JOIN
author.author_no=wrote.author_no;
7.

SELECT bookcode,MAX(unit_on_hand) AS MaxUnits, MIN(unit_on_hand) AS
MinUnits
FROM invent;
8.
SELECT book.bookcode, book.book_title FROM book INNER JOIN wrote ON
book.bookcode=wrote.bookcode INNER JOIN
author.author_no=wrote.author_no WHERE author.first=”Christie” AND
author.last=”Agatha”;
9.
SELECT bookcode,book_title FROM book WHERE book_type=”FIC” OR
publisher_code=”BB”;
10.
SELECT book_title, average(book_price) FROM book GROUP BY book_type ;
11.
SELECT book.book_title,book.book_price FROM book inner join publisher ON
book.publisher_code=publisher.publisher_code WHERE
publisher.publisher_name=”Addison Wesley”;
12.
Select (90/100*book_price) as discount FROM book;