Homework
CS 122A: Introduction to Data Management – Winter 2017
Homework 5: Structured Query Language (SQL) (100 points)
Du e Date: Tuesday, Feb 21 (5:00 PM)
Submission
All HW assignments should contain both your student ID and your name and must be submitted online, formatted in SQL script form per the instructions provided on Piazza, through the associated dropbox on EEE. See the table below for the HW submission opportunities. Note that after 5 PM on Thursday no further HW submissions will be accepted. (We will be releasing the solution at that time.) Please strive to get all your work in on time! If possible, try to save the one dropped assignment for the end of the term when you are most likely to want/need it.
Submission
All HW assignments should contain both your student ID and your name and must be submitted online, formatted in SQL script form per the instructions provided on Piazza, through the associated dropbox on EEE. See the table below for the HW submission opportunities. Note that after 5 PM on Thursday no further HW submissions will be accepted. (We will be releasing the solution at that time.) Please strive to get all your work in on time! If possible, try to save the one dropped assignment for the end of the term when you are most likely to want/need it.
|
Date / Time
|
Grade Implications
|
|
Tuesday, Feb 21 (5:00 PM)
|
Full credit will be available
|
|
Wednesday, Feb 22 (5:00PM)
|
20 points will be deducted
|
|
Thursday, Feb 23 (5:00 PM)
|
40 points will be deducted
|
Structured Query Language (SQL) [100 pts]
Congratulations! TopicalBirds is happy with your database design work and the expertise that you’ve demonstrated based on the relational algebra and calculus. Now it’s time to get real – it’s time to use MySQL and its implementation of the SQL query language to write a number of queries that they envision needing for their planned applications.
Schema, Data, and Tools
TopicalBirds is happy using the relations resulting from HW #2. You can refer to the provided solution to remind yourself of their schemas. You will also be able to see the relations’ schemas in MySQL Workbench when you are using it for this assignment. A new sample data set will be provided for you to use in testing your queries. More information about how to load the schema and associated sample data – and how to enter and run queries – can be found in the instructions linked from HW #5’s entry on the course wiki page. You are to use MySQL for all of the queries in this assignment and turn in the queries and results per the provided instructions.
Congratulations! TopicalBirds is happy with your database design work and the expertise that you’ve demonstrated based on the relational algebra and calculus. Now it’s time to get real – it’s time to use MySQL and its implementation of the SQL query language to write a number of queries that they envision needing for their planned applications.
Schema, Data, and Tools
TopicalBirds is happy using the relations resulting from HW #2. You can refer to the provided solution to remind yourself of their schemas. You will also be able to see the relations’ schemas in MySQL Workbench when you are using it for this assignment. A new sample data set will be provided for you to use in testing your queries. More information about how to load the schema and associated sample data – and how to enter and run queries – can be found in the instructions linked from HW #5’s entry on the course wiki page. You are to use MySQL for all of the queries in this assignment and turn in the queries and results per the provided instructions.
1
Write the following queries in SQL against the TopicalBirds.com test relations. Show the result
of each query that you wrote where requested to do so. Please note that you will not get
points for providing the result of a query on this assignment if your SQL query is syntactically
incorrect (i.e., if it doesn’t execute). Since you have a “live” system at your disposal, this
should not be an issue – you will be able to run all of your queries that way. (For the last two
problems, you may find one or more of the following hints helpful: (i) Given two date or time
values, you can use the timediff(val2, val1) function to calculate the duration between them.
(ii) To limit the number of results returned by a query, you can use the LIMIT clause in SQL. (iii)
You can put a subquery in the FROM clause of a query and treat it (in the outer query) as
though it were a stored table.)
1. [10pts] Print the name, gender, and birthdate of birds who live on “Alicia Pass” street. a) [7pts] SQL Query:
SELECT first_name,last_name, gender, birthdate
FROM Bird, User
WHERE address_street = 'Alicia Pass' AND User.tag = Bird.btag;
b) [3pts] Result:
2. [10pts] Print the contact information (email and complete address) for users who have uttered at least one highly positive chirp (i.e., at least one chirp with a sentiment of 1.0).
a) [7pts] SQL Query:
SELECT email, address_number, address_street, address_city, address_state, address_country, address_mailcode
FROM User
WHERE EXISTS (
SELECT *
FROM Chirp as c WHERE c.sentiment = 1 AND btag = User.tag);
b) [3pts] Result:
1. [10pts] Print the name, gender, and birthdate of birds who live on “Alicia Pass” street. a) [7pts] SQL Query:
SELECT first_name,last_name, gender, birthdate
FROM Bird, User
WHERE address_street = 'Alicia Pass' AND User.tag = Bird.btag;
b) [3pts] Result:
2. [10pts] Print the contact information (email and complete address) for users who have uttered at least one highly positive chirp (i.e., at least one chirp with a sentiment of 1.0).
a) [7pts] SQL Query:
SELECT email, address_number, address_street, address_city, address_state, address_country, address_mailcode
FROM User
WHERE EXISTS (
SELECT *
FROM Chirp as c WHERE c.sentiment = 1 AND btag = User.tag);
b) [3pts] Result:
2
3. [10pts] Print the tag and name of the bird that uttered the most positive chirp about the
topic ‘battery’.
a) [7pts] SQL Query:
SELECT c1.btag,first_name,last_name
FROM Chirp as c1,About,Topic,bird
WHERE c1.btag = About.btag AND c1.btag = bird.btag
AND c1.cno = About.cno AND About.id = Topic.id AND Topic.name = 'battery' AND NOT EXISTS (
SELECT *
FROM Chirp as c2,About,Topic WHERE c2.btag = About.btag AND c2.cno = About.cno
AND About.id = Topic.id
AND Topic.name = 'battery'
AND c2.sentiment > c1.sentiment
);
b) [3pts] Result:
a) [7pts] SQL Query:
SELECT c1.btag,first_name,last_name
FROM Chirp as c1,About,Topic,bird
WHERE c1.btag = About.btag AND c1.btag = bird.btag
AND c1.cno = About.cno AND About.id = Topic.id AND Topic.name = 'battery' AND NOT EXISTS (
SELECT *
FROM Chirp as c2,About,Topic WHERE c2.btag = About.btag AND c2.cno = About.cno
AND About.id = Topic.id
AND Topic.name = 'battery'
AND c2.sentiment > c1.sentiment
);
b) [3pts] Result:
3
4. [10pts] Print the tag, gender, and birthdate of those birds who have chirped about either of
the topics “Surface” or “Kindle”.
a) [7pts] SQL Query:
SELECT Bird.btag, Bird.gender, Bird.birthdate FROM (
SELECT DISTINCT Chirp.btag FROM Chirp, About, Topic WHERE Topic.name = 'Surface' AND About.id = Topic.id
AND Chirp.cno = About.cno AND Chirp.btag = About.btag UNION
SELECT DISTINCT Chirp.btag
FROM Chirp, About, Topic
WHERE Topic.name = 'Kindle'
AND About.id = Topic.id
AND Chirp.cno = About.cno
AND Chirp.btag = About.btag) O2, Bird
WHERE Bird.btag = O2.btag;
b) [3pts] Result:
a) [7pts] SQL Query:
SELECT Bird.btag, Bird.gender, Bird.birthdate FROM (
SELECT DISTINCT Chirp.btag FROM Chirp, About, Topic WHERE Topic.name = 'Surface' AND About.id = Topic.id
AND Chirp.cno = About.cno AND Chirp.btag = About.btag UNION
SELECT DISTINCT Chirp.btag
FROM Chirp, About, Topic
WHERE Topic.name = 'Kindle'
AND About.id = Topic.id
AND Chirp.cno = About.cno
AND Chirp.btag = About.btag) O2, Bird
WHERE Bird.btag = O2.btag;
b) [3pts] Result:
4
5. [10pts] Print the tag, gender, and birthdate of those birds who have chirped about both of
the topics “iPhone” and “gadget”.
a) [7pts] SQL Query:
SELECT Bird.btag, Bird.gender, Bird.birthdate FROM (
SELECT DISTINCT Chirp.btag
FROM Chirp, About, Topic
WHERE Topic.name = 'iPhone'
AND About.id = Topic.id
AND Chirp.cno = About.cno
AND Chirp.btag = About.btag ) O1, (
SELECT DISTINCT Chirp.btag
FROM Chirp, About, Topic
WHERE Topic.name = 'gadget'
AND About.id = Topic.id
AND Chirp.cno = About.cno
AND Chirp.btag = About.btag ) O2, Bird
WHERE O1.btag = O2.btag
AND Bird.btag = O1.btag;
b) [3pts] Result:
a) [7pts] SQL Query:
SELECT Bird.btag, Bird.gender, Bird.birthdate FROM (
SELECT DISTINCT Chirp.btag
FROM Chirp, About, Topic
WHERE Topic.name = 'iPhone'
AND About.id = Topic.id
AND Chirp.cno = About.cno
AND Chirp.btag = About.btag ) O1, (
SELECT DISTINCT Chirp.btag
FROM Chirp, About, Topic
WHERE Topic.name = 'gadget'
AND About.id = Topic.id
AND Chirp.cno = About.cno
AND Chirp.btag = About.btag ) O2, Bird
WHERE O1.btag = O2.btag
AND Bird.btag = O1.btag;
b) [3pts] Result:
5
6. [10pts] Print the tags and business names of watchers who own no ads but are listening for
chirps from at least one male bird.
a) [7pts] SQL Query:
SELECT wtag,bname
FROM Watcher
WHERE Watcher.wtag
NOT IN (SELECT wtag FROM Ad) AND Watcher.wtag IN (
SELECT BirdListen.tag
FROM BirdListen, Bird
WHERE BirdListen.btag = Bird.btag AND Bird.gender='M');
b) [3pts] Result:
7. [10pts] Compute and print the number of topics that appear to be more interesting than the topic “Sprint” – i.e., topics with at least one user-specified interest level that’s higher than any of those for the topic “Sprint”.
a) [7pts] SQL Query:
SELECT COUNT( DISTINCT i1.id) FROM Interest as i1
WHERE i1.level > ALL(
a) [7pts] SQL Query:
SELECT wtag,bname
FROM Watcher
WHERE Watcher.wtag
NOT IN (SELECT wtag FROM Ad) AND Watcher.wtag IN (
SELECT BirdListen.tag
FROM BirdListen, Bird
WHERE BirdListen.btag = Bird.btag AND Bird.gender='M');
b) [3pts] Result:
7. [10pts] Compute and print the number of topics that appear to be more interesting than the topic “Sprint” – i.e., topics with at least one user-specified interest level that’s higher than any of those for the topic “Sprint”.
a) [7pts] SQL Query:
SELECT COUNT( DISTINCT i1.id) FROM Interest as i1
WHERE i1.level > ALL(
b) [3pts] Result:
SELECT i2.level
FROM Topic as t2, Interest as i2 WHERE t2.id = i2.id
AND t2.name = 'Sprint');
FROM Topic as t2, Interest as i2 WHERE t2.id = i2.id
AND t2.name = 'Sprint');
6
8. [10pts] To help in identifying potential Russian bot-birds, print the tags and email addresses
of those birds who have done nothing but parrot chirps from bird “realDonaldTrump” (i.e.,
birds all of whose chirps are parroted Trump chirps).
a) [7pts] SQL Query:
SELECT btag, email
FROM Bird, User
WHERE Bird.btag = User.tag AND NOT EXISTS(
SELECT *
FROM Chirp
WHERE Bird.btag = Chirp.btag AND Chirp.parrots_btag IS NULL)
AND NOT EXISTS( SELECT *
FROM Chirp
WHERE Bird.btag = Chirp.btag
AND Chirp.parrots_btag != 'realDonaldTrump');
b) [3pts] Result:
9. [10pts] Print the maximum watcher fee among watchers who have specified the highest possible level of interest (i.e., level 5) in two or more different topics.
a) [7pts] SQL Query:
SELECT MAX(fee)
FROM Watcher
WHERE Watcher.wtag IN (
a) [7pts] SQL Query:
SELECT btag, email
FROM Bird, User
WHERE Bird.btag = User.tag AND NOT EXISTS(
SELECT *
FROM Chirp
WHERE Bird.btag = Chirp.btag AND Chirp.parrots_btag IS NULL)
AND NOT EXISTS( SELECT *
FROM Chirp
WHERE Bird.btag = Chirp.btag
AND Chirp.parrots_btag != 'realDonaldTrump');
b) [3pts] Result:
9. [10pts] Print the maximum watcher fee among watchers who have specified the highest possible level of interest (i.e., level 5) in two or more different topics.
a) [7pts] SQL Query:
SELECT MAX(fee)
FROM Watcher
WHERE Watcher.wtag IN (
b) [3pts] Result:
SELECT tag
FROM Interest
WHERE Interest.level = 5
GROUP BY tag HAVING count(*) > 1);
FROM Interest
WHERE Interest.level = 5
GROUP BY tag HAVING count(*) > 1);
7
10. [10pts] Find the 5 biggest parrots (i.e., the top 5 birds based on their parroted chirp
counts) and print their bird tags and the associated parrot counts in descending order.
a) [7pts] SQL Query:
SELECT c.btag, count(*) as count
FROM Chirp c WHERE c.parrots_btag IS NOT NULL GROUP BY c.btag
ORDER BY count DESC
LIMIT 5;
b) [3pts] Result:
11. [10pts EXTRA CREDIT] Find the 5 quickest acts of parroting (i.e., the top 5 cases of parroting based on the shortness of the time delay between the appearance of a chirp and the parroting of that chirp) and print the associated time delays, the chirper and parrot bird tags and chirp numbers, and also the chirp text, in ascending time delay order.
a) [7pts] SQL Query:
SELECT O1.chirper, O1.btag, O1.cno, O1.text, TIMEDIFF(parrot_date,send_date) + TIMEDIFF(parrot_time, send_time) as RESPONSE
FROM (
SELECT c1.date as send_date, c1.time as send_time, c2.date as
a) [7pts] SQL Query:
SELECT c.btag, count(*) as count
FROM Chirp c WHERE c.parrots_btag IS NOT NULL GROUP BY c.btag
ORDER BY count DESC
LIMIT 5;
b) [3pts] Result:
11. [10pts EXTRA CREDIT] Find the 5 quickest acts of parroting (i.e., the top 5 cases of parroting based on the shortness of the time delay between the appearance of a chirp and the parroting of that chirp) and print the associated time delays, the chirper and parrot bird tags and chirp numbers, and also the chirp text, in ascending time delay order.
a) [7pts] SQL Query:
SELECT O1.chirper, O1.btag, O1.cno, O1.text, TIMEDIFF(parrot_date,send_date) + TIMEDIFF(parrot_time, send_time) as RESPONSE
FROM (
SELECT c1.date as send_date, c1.time as send_time, c2.date as
parrot_date, c2.parrots_btag, as chirper, c2.time as parrot_time, c2.btag as
btag,
AND c1.btag = c2.parrots_btag) O1 ORDER BY RESPONSE
LIMIT 5;
b) [3pts] Result:
AND c1.btag = c2.parrots_btag) O1 ORDER BY RESPONSE
LIMIT 5;
b) [3pts] Result:
c2.cno as cno, c2.text as text
FROM Chirp as c1, Chirp as c2
WHERE c1.cno = c2.parrots_cno
WHERE c1.cno = c2.parrots_cno
8
9
Comments
Post a Comment