BIT231: Database Systems Assessment 2 - SQL Queries Solution

This document provides the solution for a Database Systems assessment, focusing on SQL queries. The assignment requires students to write SQL code for a bookstore database, using their student ID's last two digits for table and attribute naming. The solution includes SQL code for various queries, such as selecting data based on conditions, joining tables, and using aggregate functions. The solution also addresses the requirement of providing screenshots of the SQL code execution in SAS Studio and a separate TXT/SQL file with all the SAS PROC SQL code. The assignment includes two parts, with the first part requiring the student to write SQL queries and the second part involving more complex queries involving subqueries and aggregate functions. The solution covers all the specified questions and provides a comprehensive understanding of database querying techniques.

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;

Part C.
1)
SELECT author.first,author.last,COUNT(*) AS 'number of books'
FROM author,wrote,book
WHERE author.Authour_no =wrote.Authour_noand wrote.Book_code=book.Book_code
GROUP BY author.name
2)SELECT book_title
FROM book
WHERE bookcode IN( SELECT bookcode
FROM invent
WHERE branch_no IN (SELECT branch_no
FROM branch
WHERE branch_name='Henrys Downtown')
3) SELECT TOP 1 book_name
FROM (SELECT DISTINCT TOP 2 book_price
FROM book ORDER BY book_price ASC)
ORDER BY book_price
4)
SELECT TOP 1 book_name

FROM (
SELECT DISTINCT TOP 2 book_name
FROM invent
ORDER BY units_on_hands DESC) a
ORDER BY units_on_hands
5)SELECT book_title
FROM book
WHERE bookcode IN( SELECT bookcode
FROM invent
WHERE branch_no NOT IN (SELECT branch_no
FROM branch
WHERE branch_name='Henrys Downtown';
6)
SELECT
`publisher.publisher_code`, `publisher.publisher_name`, `book.publisher_code`,
COUNT(book.publisher_code) as `number_of_books_published` FROM `publisher` INNER JOIN
`book` ON `book.publisher_code` = `publisher.publisher_code` WHERE
`number_of_books_published` > 9 GROUP BY `publisher.publisher_code `;
7)
SELECT `*`, `wrote.Bookcode`, `wrote.Author_number`, `book.bookcode`,
COUNT(wrote.bookcode) as `number_of_books_published`, `invent.bookcode`,
`invent.branch_no`, `branch.branch_no`, `branch.branch_location`
FROM `author` INNER JOIN `wrote` ON `wrote.Author_number` = `author.author_no`
LEFT JOIN `book` ON `book.bookcode` =`wrote.Bookcode`
LEFT OUTER JOIN `invent` ON `invent.bookcode` = `book.bookcode`
LEFT OUTER JOIN `branch` ON `branch.branch_no` = `invent.branch_no`
WHERE `number_of_books_published` > 2 AND `wrote.Author_number` = ?
GROUP BY `wrote.Author_number `;
8)
SELECT COUNT(book.book_type) as `total_book_type`, `book.book_type`, `book.publish_date`
FROM `book` WHERE `total_book_type` > 5 ORDER BY `book.publish_date` DESC;