|
|
The goal of this project is to develop Advanced
SQL Programming Capability.
l
Create the following three tables and
populate each table using SQL statements provided by the link(sql.txt).
Suppose NO NULL values are allowed in the tables. Note that final grading might
be based on a different dataset
Paper(paperid,
title, confname).
Author(ssn, name, institution).
WrittenBy (paperid, ssn,
ordernum).
// The attribute ordernum is to
define a sequence of authors for each paper, such as the first author, the
second author, and so on.
l
(15 pts)Write the following 15 advanced-level queries
as required.
?
Some assumptions in the database
ü All institution names occur in table
Author.
ü For each author, the number of papers that he published in each
conference is different.
ü Names of authors are unique.
ü All conference names occur in table Paper
1. (1 point)List the titles of the papers that
are written by exactly one author who published the most number of papers. You
need to consider the case in which multiple authors might publish the same
total number of papers.
Expected answer)
Integrating compression and execution in column-oriented database systems
2. (1 point)List the titles of the papers in
which author “123456789” appears before “987654321”, not only necessarily
together.
Expected answer)
Automatic physical
design tuning: workload as a sequence
Efficient Image
Classification on Vertically Decomposed Data
3. (1 point)List the names of the authors who
published the most number of papers in “ICDE” conference.
Expected answer) Mark
4. (1 point)List the names of the authors who
have published in every conference.
Expected answer) Jordon, Drake
5. (1 point)List the names of the authors who
have published exactly one paper in each conference.
Expected answer) “None”
6. (1 point)List the names of institutions
that have never published papers in “ICDE” but have published some papers in
“SIGMOD”.
Expected answer) NSU
7. (1 point)List the titles of all the papers
that are authored by “123456789” but not authored by “987654321”.
Expected answer)
Integrating
compression and execution in column-oriented database systems
Efficient reverse
k-nearest neighbor search in arbitrary metric spaces
P2P Directories for
Distributed Web Search
The Impact of Ranker
Quality on Rank Aggregation Algorithms
Taming Compliance
with Sarbanes-Oxley Internal Controls Using Database Technology
8.
(1 point)For each conference, list the
name of the most published author.
Expected answer)
SIGMOD, Jordon
VLDB, John
ICDE, Mark
9. (1 point)List the name of the
conference in which “John” and “Mark” published the most number of
papers(not necessarily the same papers).
Expected answer) VLDB
10. (1 point)List the names of the conferences
in which both “John” and “Mark” have published some papers (not necessarily the
same papers), but the number of papers published by “John” in that conference is
greater than the number of papers published by “Mark”.
Expected answer) VLDB
11. (1 point)Delete all the papers that have
more than four authors.
Paper to be deleted)
3000-06-001
12. (1 point)Delete the last coauthor of those
papers that are published in “ICDE” conference and the last coauthor is from
“WSU”.
Tuple to be deleted)
(3000-06-002, 123456789,3)
13. (1 point)Delete all the authors who have
never published a paper.
Author to be
deleted) Yam
14. (1 point)Change the first author to
“123456789” and the second author
to “987654321’ for those papers whose original first author is “987654321’ and
second author is “123456789”. You can use two update statements if necessary.
Paper to be updated)
2000-06-005
15.
(1
point)Add a “*” as a suffix to the names of the authors who have published more
than 3 papers in “ICDE”.
Author to be
processed) Mark
Note that in order to find the answer for each
query above, you can use any available interfaces to access the Oracle DB in CS
department, such as SQL*Plus from Web
provided by the link (http://srv10db.cs.wayne.edu
l Submission (Read
carefully and follow the direction)
Please submit your result of this project
(only SQL statements without results) in a text file named with your WSU access
id, for example, “aa1111_CSC4710_Proj3.txt”, via Digital Dropbox on Blackboard. Note
that answers on papers are not acceptable for this project.