COP4710代做、NBA Database代写、代做SQL语言、SQL程序设计调试

日期：2020-03-07 10:28

COP4710 Spring 2020 Project 2 - NBA Database

Due: 11:30pm, March 5, 2020 (Thursday)

I. Introduction

In this project, you are expected to write some SQL queries and execute them

in a database server we built for you. The schemas of the tables will be given

and such tables are loaded with real NBA statistics data. Specifically, the

database contains the following six tables:

1. coaches_season, each tuple of which describes the performance of one

coach in one season;

2. teams, each tuple of which gives the basic information of a team;

3. players, each tuple of which gives the basic information of one player;

4. player_rs, each tuple of which gives the detailed performance of one

player in one regular season;

5. player_rs_career, each tuple of which gives the detailed regular-season

performance of one player in his career;

6. draft, each tuple of which shows the information of an NBA draft.

II. Getting started

Your job should be done in the machine named element.csee.usf.edu (not the

same one you used for project 1). The DBMS used in this project is

PostgreSQL (www.postgresql.org). After remotely logging into element.csee

by ssh with your NetID and password, you can connect to the database using

the following command:

/usr/local/pgsql/bin/psql NBA

and you should see a prompt like this:

NBA=>

and now you are ready to type in any SQL statements you want. Try some

simple things like "select * from teams;" to make sure you are really

connected. Query against all the tables to get familar with the schema of each

table. Note that there has to be a semicolon after each SQL statement you

write, otherwise the system will not start processing your query.

III. Queries to write

Your task in this project is to write the following queries in SQL and make sure

they run in the PostgreSQL database. If you make any assumptions, clearly

state them as comments in your submitted file.

1. Find all the coaches who have coached exactly TWO teams. List their first

names followed by their last names;

2. Find all the players who played in a Boston team or a Denver team. List

their first names only.

3. Find those who happened to be a coach and a player in the same team in

the same season. List their first names, last names, the team where this

happened, and the year(s) when this happened.

4. Find the average height (in centimeters) of each team coached by Phil

Jackson in each season. Print the team name, season and the average height

value (in centimeters), and sort the results by the average height.

5. Find the coach(es) (first name and last name) who have coached the

largest number of players in year 2003.

6. Find the coaches who coached in ALL leagues. List their first names

followed by their last names.

7. Find those who happened to be a coach and a player in the same season,

but in different teams. List their first names, last names, the season and the

teams this happened.

8. Find the players who have scored more points than Michael Jordan did.

Print out the first name, last name, and total number of points they scored.

9. Find the most successful coach in regular seasons in history. The level of

success of a coach is measured as season_win /(season_win + season_loss).

Note that you have to count in all seasons a coach attended to calculate this

value.

10. List the name(s) of school(s) that sent the second largest number of

drafts to NBA. List the name of each school and the number of drafts sent.

Note that the data is not perfectly formatted for our use. For that, you have to

try some other techniques that are not a part of the SQL, but are supported by

PsotgreSQL. For example, the ID of the same palyer (e.g., ILKID) can be

shown as all upper case in one table (e.g., players) but in a mixture of upper

and lower cases in another table (e.g., draft). To find more matches, you can

use the 'LOWER' or 'UPPER' function to transform a string to all lower or

upper case and then do the match.

IV. Due date, submission:

Due: 11:30pm, March 5, 2020 (Thursday) via the Canvas assignment link.

When you feel comfortable with your queries, copy and paste them to a text

file named proj2-xxx.sql where xxx is your NetID. Submit this file only! Put all

comments, assumption statements in this file (note that a line that starts with

"--" is regarded as comments and will not be processed by DBMS). The

purpose of having this script file is to run all queries written in the file by typing

one command in psql:

NBA=> \i proj2-xxx.sql

The output of this script can be dumped to a file named "proj2-xxx.out" by

NBA=> \o proj2-xxx.out

You can type ' \o ' to change the output device back to your psql console.

Attention!! You must try the above command to run all your queries as a

whole batch in the submitted file and make sure all queries work in the way

you expected. You will get heavy deductions (up to 30%) if your query only

works when typed into the console one by one!

Acknowledgement

Data used in this project is provided by basketballconference.com.