Personal Web Pages - UNC Charlotte FAQ - UNC Charlotte
Assignment #2 Solution
Tuple Relational Calculus
1. Find the names of suppliers who supply some red part.
{S.sname | Suppliers(S) ^ ((C)((P)(Catalog(C) ^ Parts(P) ^ (C.sid = S.sid) ^ (P.pid = C.pid) ^ (P.color = ‘red’))}
2. Find the sids of suppliers who supply some red part or green part.
{C.sid | Catalog(C) ^ ((P)(Parts(P) ^ ((C.pid = P.pid) ^ (P.color = ‘red’)) V ((C.pid = P.pid) ^ (P.color = ‘green’))) }
3. Find the sids of suppliers who supply some red part or are at 221 Packer Ave.
{S.sid | Suppliers(S) ^ ((S.address = ‘221 Packer Ave.’) v (((C)((P)(Catalog(C) ^ Parts(P) ^ (S.sid = C.sid) ^ (C.pid = P.pid) ^ (P.color = ‘red’))))}
4. Find the sids of suppliers who supply some red part and some green part.
{C.sid | (Catalog(C) ^ ((P)(Parts(P) ^ (C.pid = P.pid) ^ (P.color = ‘red’))) ^ (Catalog(C1) ^ ((P)(Parts(P) ^ (C1.pid = P.pid) ^ (P.color = ‘green’))) ^ (C.sid = C1.sid)}
5. Find the sids of suppliers who supply every red part.
{S.sid | Suppliers(S) ^ (((P)(Parts(P) ^ (P.color=‘red’))) ^ (((C)(Catalog(C) ^ (C.pid = P.pid))) ^ (C.sid = S.sid)}
6. Find the sids of suppliers who supply every part.
{S.sid | Suppliers(S) ^ ((P)(Parts(P)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)}
7. Find the sids of suppliers who supply every red part or supply every green part.
{S.sid | Suppliers(S) ^ ((((P)(Parts(P) ^ (P.color=‘red’)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)) v ((((P)(Parts(P) ^ (P.color=‘green’)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)) }
8. Find the pids of parts supplied at least two different suppliers.
{C.pid | Catalog(C) ^ ((S)(Suppliers(S) ^ (C.sid = S.sid) ^ COUNT(C.sid) >= 2)} or
{C.pid | Catalog(C) ^ ((C1)(Catalog(C1) ^ (C.pid = C1.pid) ^ (C.sid != C1.sid)}
9. Find the pids of the most expensive parts supplied by suppliers named Yosemite Sham.
{C.pid | Catalog(C) ^ ((S)(Suppliers(S) ^ (S.sid = C.sid) ^ (S.name = ‘Yosemite Sham’)) ^ MAX(C.cost))}
10. Find the sids of suppliers who supply every red or green part.
{S.sid | Suppliers(S) ^ ((((P)(Parts(P) ^ (P.color=‘red’)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)) v ((((P)(Parts(P) ^ (P.color=‘green’)) ^ ((C)(Catalog(C) ^ (C.pid = P.pid)) ^ (C.sid = S.sid)) }
SQL Solutions
1. List the names and addresses of all guests in London, alphabetically ordered by name.
SELECT guestName, guestAddress
FROM Guest
WHERE guestAddress LIKE ‘%London%’
ORDER BY guestName;
2. List the bookings for which no dateTo has been specified.
SELECT *
FROM Booking
WHERE dateTo IS NULL;
3. What is the average price of a room?
SELECT AVG(price)
FROM Room;
4. How many different guests have made bookings for August?
SELECT COUNT(DISTINCT guestNo)
FROM Booking
WHERE dateFrom >= DATE‘2008-08-01’ AND dateTo ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- parts and repair ordering system
- provisioning allowance and fitting out support pafos manual
- section 22 interlink supply
- section 01 33 00 shop drawings product data and samples
- introduction defense logistics agency
- new title processing system publisher logon
- dvd vhs video library catalog michigan
- section 5 interlink supply
- ga 60 parts catalog linksaerificationplus
- verification test protocol gl 3 1 1 1f14 revc
Related searches
- faq sheet template
- personal web page
- unsecured personal loans charlotte nc
- narrator for web pages microsoft edge
- microsoft word faq template
- faq template free
- faq sheet template free
- free faq template downloads
- faq sheet template pdf
- faq template pdf
- charlotte observer charlotte water salaries
- charlotte plastic surgery charlotte nc