Database Course –Exam 2 SOLUTIONS (Fa 2016) Time: 75 mins Name:______________________

Q1 [10%]

Available varchar2(3) not null CHECK(Available = 'yes' or Available = 'no')

PeriodRent varchar2(5) not null check (PeriodRent > 0 and PeriodRent <= 12) ,

Q2 [42%].

> --a) Print apartment number, building number and floor number of apartments that are available.

SELECT ANum, BuildingNum, FloorNum

FROM Apartment

WHERE Available = 'yes'

ANUM BUILDINGNUM FLOORNUM

---- ----------- --------

1006 13 3

1007 11 1

> --b) Print names of clients who are not currently active clients. A client is considered as active if he/she is referenced in RentedBy relation.

SELECT CName from Client

WHERE CId NOT IN (Select CId FROM RentedBy)

CNAME

--------------------

Smith, John

Smith, Sheryl

> --c) Print information about clients who have signed more than one lease contract since January 2014.

SELECT C.CId, C.CName, C.SSN, C.Phone

FROM Client C, RentedBy R

WHERE C.CID = R.CID

AND R.StartRent >= to_date('01-Jan-2014', 'DD-MON-YYYY')

GROUP BY C.CId, C.CName, C.SSN, C.Phone

HAVING COUNT(c.CId) >= 2

CID CNAME SSN PHONE

--------------- -------------------- --------- ------------

105 Mickson, Mick 333333333 414-333-5555

100 Johnson, John 666666666 414-555-5555

> --d) Print name of clients who paid a rent of at least $1000 for every apartment they have rented.

(SELECT C.CName FROM Client C, RentedBy R

WHERE C.CID = R.CID

AND R.Rent >= 1000)

MINUS

(SELECT C.CName FROM Client C, RentedBy R

WHERE C.CID = R.CID

AND Rent < 1000)

CNAME

--------------------

Johnson, Joe

Johnson, John

> -- Alternatively

SELECT C.CNAME

FROM (SELECt R.CId, MIN(R.Rent) MIN_RENT FROM RentedBy R GROUP BY R.CId) TEMP, CLIENT C

WHERE C.CId = TEMP.CId AND TEMP.MIN_RENT >= 1000

CNAME

--------------------

Johnson, Joe

Johnson, John

> --e) Print the average rent of the RENTAL database.

SELECT AVG(RENT)

FROM RentedBy

AVG(RENT)

---------

1039.375

> --f) Consider the following SQL query :

SELECT R.ANUM, COUNT(*)

FROM RENTEDBY R

GROUP BY R.ANUM

HAVING COUNT(*) > 1

ANUM COUNT(*)

---- --------

1001 2

1003 2

> --Rewrite the above SQL query without using the GROUP BY and HAVING clauses so that the result query still produces the same result.

SELECT distinct ANum, CountApt

FROM ( SELECT R.ANUM, (select count(*) CountApt from RentedBy RB where R.ANum = RB.ANum) AS CountApt FROM RentedBy R)

where CountApt > 1

ANUM COUNTAPT

---- --------

1003 2

1001 2

> -- without count

> SELECT DISTINCT R.ANum

FROM RentedBy R

WHERE 1 < (select count(*) countValue from RentedBy RB WHERE R.ANum = RB.ANum)

ANUM

----

1001

1003

Q3 [48%].

Page 1 of 1