BIT231: Database Systems Assessment 2 - Software Application Solution

This document presents a comprehensive solution for the BIT231 Database Systems Assessment 2, focusing on SQL queries and database manipulation within the SAS Studio environment. The solution includes the creation of tables, data insertion, and execution of various SQL queries to address specific requirements outlined in the assignment brief. The assignment involves updating and deleting records, retrieving data based on various criteria (e.g., book price, publisher location, book types), and performing more complex queries involving joins, aggregations, and subqueries. The solution also demonstrates how to use functions like COUNT, MIN, MAX, AVG, and DISTINCT to extract meaningful insights from the database. The document provides the SQL code, screenshots of the execution results, and explanations of the queries to help students understand the implementation and logic behind each task. The assessment covers a range of SQL concepts, including data definition language (DDL) for table creation, data manipulation language (DML) for data insertion, update, and deletion, and data query language (DQL) for retrieving information based on different conditions. The solution addresses all parts of the assignment, including those involving complex joins, subqueries, and aggregate functions, demonstrating a solid understanding of database concepts and practical SQL implementation.

Contents
Question 1:..................................................................................................................................................2
Part A:......................................................................................................................................................2
PART B:....................................................................................................................................................3
Question 2:..................................................................................................................................................7
PART A:...................................................................................................................................................7
PART B:..................................................................................................................................................12
PART C:..................................................................................................................................................22

Question 1:
Part A:
Tables are created and data is inserted into the tables in this part:
Executed and tables created:
Data Insertion:

PART B:
a) Update the price from 3.95 to 5.50 of book in FIC:
Before updating the record:

Update Query:
Proc sql;
Update assgnt.book20
set book_price20=5.50
where book_type20='FIC'
and book_price20=3.95;
QUIT;

Execution of the query:
After update query execution:
b) Delete books in SFI type and publisher code is ‘BB’:

Delete Query:
This has reference in Invent20 and wrote20 hence deleting first from both the tables and then
deleting from book20:
Proc sql;
Delete from assgnt.invent20 where bookcode20='2226';
Delete from assgnt.wrote20 where bookcode20='2226';
Delete from assgnt.book20
where book_type20='SFI'
and publisher_code20='BB';
QUIT;

Question 2:
PART A:
1) All data from author table:
Query:
Proc sql;
Select * from assgnt .author20;
QUIT;

2) Retrieve the book price for the Book “Stranger”:
Query:
Proc sql;
Select book_title20,book_price20 from assgnt.book20
where book_title20='Stranger';
QUIT;
3) Books in alphabetic order and price greater than 5.5:
Proc sql;
Select * from assgnt.book20
where book_price20>5.5
order by book_title20 asc;
QUIT;

4) Publisher not located in New York.
Proc sql;
Select Distinct PUB.publisher_name20 as publisherName
from assgnt.book20, assgnt.publisher20 PUB
where PUB.publisher_city20 ~='New York';
QUIT;

5) Distinct Book Types:
Proc sql;
Select Distinct book_type20
from assgnt.book20;
QUIT;

6)
Proc sql;
Select bookcode20,book_title20
from assgnt.book20
where book_type20 in ('FIC', 'MYS','ART');
QUIT;

PART B:
1) Branch names whose employees range of 10 and 15:
Proc sql;
select branch_name20
from assgnt.branch20
where no_employees20 between 10 and 15;
QUIT;
2) Publisher havig t in their names: