SQL This is a three (3) part question that parallels our Perl homework, where yo
ID: 3829288 • Letter: S
Question
SQL
This is a three (3) part question that parallels our Perl homework, where you created information about your favorite team.
#!/usr/bin/perl
# Perl Assignment - Hash of Hashes
# J Student
# Professional Sports Teams using a Hash of Hashes
# Sports Team Founded Owner Leader
# Saint Louis Rams - NFL 1936 Stan Kroenke Jeff Fisher
# Saint Louis Cardinals - MLB 1882 William O DeWitt, Jr Mike Matheny
# Saint Louis Blues - NHL 1967 Tom Stillman David Backes
# Saint Louis Aces - WTT 1994 Kinosabe Sports Rick Leach
# Kansas City Wizards - MLS 1995 Sporting Club Peter Vermes
# I have created the following array:
@teams = ("Saint Louis Rams - NFL", "Saint Louis Cardinals - MLB", "Saint Louis Blues - NHL", "Saint Louis Aces - WTT" , "Kansas City Wizards - MLS");
# and the following Hash of Hashes:
%missouriTeams = ( "Saint Louis Rams - NFL" => { yearFounded => 1936,
owner => "Stan Kroenke",
leader => "Jeff Fisher",
},
"Saint Louis Cardinals - MLB" => { yearFounded => 1882,
owner => "William O DeWitt, Jr",
leader => "Mike Matheny"
},
"Saint Louis Blues - NHL" => { yearFounded => 1967,
owner => "Tom Stillman",
leader => "David Backes"
},
"Saint Louis Aces - WTT" => { yearFounded => 1994,
owner => "Kinosabe Sports",
leader => "Rick Leach",
},
"Kansas City Wizards - MLS" => { yearFounded => 1995,
owner => "Sporting Club",
leader => "Peter Vermes"
},
);
# To print out sorted Team information in the Hash of Hashes (ascending order):
print (" Missouri Teams - sorted by Team Name ascending: ");
printf("%-20s %-6s %-10s %-25s ", "Team", "Year", "Owner", "Leader");
@sortedKeys = sort (@teams);
for $teamName (@sortedKeys) {
$yearFounded = $missouriTeams{$teamName}{'yearFounded'};
$owner = $missouriTeams{$teamName}{'owner'};
$leader = $missouriTeams{$teamName}{'leader'};
printf("%-20s %-6i %-10s %-25s ", $teamName, $yearFounded, $owner, $leader);
print " ";
}
# To print out sorted Team information in the Hash of Hashes (descending order):
print (" Missouri Teams - sorted by Team Name decending: ");
printf("%-20s %-6s %-10s %-25s ", "Team", "Year", "Owner", "Leader");
@reverseKeys = reverse (@sortedKeys);
for $teamName (@reverseKeys) {
$yearFounded = $missouriTeams{$teamName}{'yearFounded'};
$owner = $missouriTeams{$teamName}{'owner'};
$leader = $missouriTeams{$teamName}{'leader'};
printf("%-20s %-6i %-10s %-25s ", $teamName, $yearFounded, $owner, $leader);
print " ";
}
print " HTML Page containing information on Missouri Teams: ";
print "<html> ";
print "<head> ";
print "<title>Missouri Teams</title>";
print "</head> ";
print "<body> ";
print "<H1>Missouri Professional Sports Teams</H1> ";
print "<table border=1> ";
print "<tr><th>Team</th><th>Founded</th><th>Owner</th><th>Leader</th></tr> ";
for $teamName (sort keys %missouriTeams ) {
$yearFounded = $missouriTeams{$teamName}{'yearFounded'};
$owner = $missouriTeams{$teamName}{'owner'};
$leader = $missouriTeams{$teamName}{'leader'};
print "<tr><td>$teamName</td><td>$yearFounded</td><td>$owner</td><td>$leader</td></tr> ";
}
print "</table> ";
print "</body> ";
print "</html> ";
Part I: Using SQL, develop one (1) CREATE TABLE statement that would create a database table within Oracle APEX. Define a primary key, you are welcome to add additional columns as needed to your table. Don't worry about a Foreign key or creating any other tables
Part II: Using SQL, develop a set of five (5) INSERT statements to populate your table with information about each team.
Part III: Create one (1) select statement that selects all columns and rows in your table. Sort it by your team name. Don't use an asterisk (like "SELECT * FROM team"), instead, please specify each column.
... All SQL statements are really in your lecture notes ... especially the script I provided that creates and populates the emp, dept, and salgrade tables. Look at how I created, inserted, and selected from the emp table. Just mimic that and try out your work in Oracle APEX to make sure it all works. I just need to see the statements in SQL that do parts I, II, and III. If you can't think of good primary key, just add a column as your primary key called "team_id" that is the same type as empno in our sample emp table.
To verify it works, try out your SQL statements in Oracle APEX to create, populate, and query your table.
Explanation / Answer
1)
CREATE TABLE Team(
Team_id int NOT NULL PRIMARY KEY,Team_name varchar2(50),year_founded int,Owner varchar2(50),Leader varchar2(50)
);
2)
INSERT INTO Team values(001,'Saint Louis Rams - NFL',1936,'Stan Kroenke','Jeff Fisher');
INSERT INTO Team values(002,'Saint Louis Cardinals - MLB',1882,'William O DeWitt','Jr Mike Matheny');
INSERT INTO Team values(003,'Saint Louis Blues - NHL',1967,'Tom Stillman','David Backes');
INSERT INTO Team values(004,'Saint Louis Aces - WTT',1994,'Kinosabe Sports','Rick Leach');
INSERT INTO Team values(005,'Kansas City Wizards - MLS',1995,'Sporting Club','Peter Vermes');
3)
SELECT Team_id,Team_name,year_founded,Owner,Leader FROM Team
ORDER BY Team_name;
The above query will sort the table in ascending order based on the team name.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.