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