CSc 4710/6710, Database Systems
Spring 2008
Practice Problems for Exam2
Consider the following relational schema for portfolio management:
MEMBER(MID,password,fname,lname,address,email)
SECURITY(SYMBOL,cname)
TRANSACTION(MID,SYMBOL,transdate,transtype,quantity)
The MEMBER table consists of information about members of the portfolio management system. The SECURITY table keeps track of companies whose shares are purchased and sold by members. The TRANSACTION table records each "buy" or "sell" transaction made by each member. The transtype column can have one of the two values "buy" or "sell".
All primary keys are shown in capitals. The MID attribute in the TRANSACTION table is a foreign key referring to the MEMBER table and the SYMBOL attribute in the TRANSACTION table is a foreign key referring to the SECURITY table.
- Write SQL statements to answer the following queries:
- Get names of members who have purchased ORCL shares. Note: 'ORCL' is the symbol value for a security.
- Get names of members who have purchased only ORCL shares.
- Get company names of securities whose shares are purchased by all members.
- Get the names of members who have purchased shares from all of the companies that member with MID=1000 has purchased shares from.
- Write an SQL view definition that contains total number of shares of each company currently owned by each member. The view should have three columns: MID, SYMBOL, NUMSHARES. Hint: The number of shares of a company owned by a member equals the sum of quantity of all buy transactions minus the sum of quantity of all sell transactions for that company by that member. You may break the view definition into more than one CREATE VIEW statements if it is necessary.
Consider the following relational database scheme:
s(SNO,sname,street,city)
p(PNO,pname,color)
sp(SNO,PNO,qty)
where the s relation records information relating to suppliers
the p relation records information about parts, and
the sp relation records information about which supplier
supplies which part and in what quantity. The primary keys are shown
in upper-case and the sp relation has two foreign keys: sno
referring to the s relation and pno referring to the
p relation.
Express the following queries in Datalog:
- Find the names of suppliers who supply at least one red part.
- Find the names of suppliers who do not supply any red part.
- Find the names of suppliers who supply all red parts.
- Find the names of suppliers who supply only red parts.
Page Maintained by raj@cs.gsu.edu