辅导案例-CITS1402

欢迎使用51辅导,51作业君孵化低价透明的学长辅导平台,服务保持优质,平均费用压低50%以上! 51fudao.top
CITS1402 Lab One
Gordon Royle
2020 Semester Two
This lab should be completed by the end of Teaching Week 2. You should submit three
files called Q1.sql, Q2.sql and Q3.sql to cssubmit at https://secure.csse.uwa.edu.
au/run/cssubmit
Learning Aims
This lab is concerned with
1. Creating and populating an SQLite table
2. Examining the table and its entries
3. Basic use of the SELECT statement
We will be using the data about AFL matches from 2010–2019.
Required Setup
All files needed for this lab are in the Week 2 Lab folder of the Labs 2020 area on LMS.
1. The actual database file afl.db
2. A SQL script create_afl to create the database from scratch
Types of command
In the labs, I often use the phrase “command” meaning something that you type into the
computer in order to get a response or accomplish something. To avoid frustration it is
important to carefully distinguish the different types of command.
1. Terminal command
A terminal command is something you type at the Terminal (Mac) or Powershell
(Win) prompt, and that is interpreted by the operating system.
Terminal commands are used to navigate the file system, create directories / folders,
move files etc.
1
2. SQLite dot command (or just dot command)
A dot command is a command that you type at the SQLite prompt. Dot commands
start with a dot, and do not terminate with a semi-colon.
Dot commands are SQLite-specific commands that are mostly concerned with con-
trolling the interface between the user and SQLite. These commands do not work
in other implementations of SQL (MySQL, PostgreSQL, etc.).
3. SQL command
A SQL command is an actual database query that you type at the SQLite prompt.
SQL commands always terminate with a semi-colon, and return a table as output
which (by default) is printed on the terminal window.
SQL commands should adhere to the SQL standard and should work in other
implementations of SQL either unchanged or with minor alterations.
Creating and Populating
Unlike other implementations of SQL, an SQLite database is stored in a single file on
the file system. This file can be transferred from one machine to another, even across
operating systems.
Here are three different ways to start working with a SQLite database, two of which
assume you have access to a pre-existing database file, and the third which assumes you
need to create the database file.
1. Start SQLite with an existing database file
Copy afl.db to your SQLite directory and at the terminal prompt, issue the
command sqlite3 afl.db to simultaneously start SQLite and connect the AFL
database.
2. Start SQLite and connect to an existing database file
Use the terminal command sqlite3 to start SQLite, and then (at the SQLite
prompt), use SQLite command .open afl.db to connect the database
3. Start SQLite and create a new database file
The file create_afl is a file containing the SQL commands necessary to create a
table and fill it with data.
To use this file, start SQLite with the terminal command sqlite3, and then (at the
SQLite prompt), use SQLite command .read create_afl. This reads in the SQL
commands and creates the database, but does not yet write it to file. In order to
keep the database for future use, it must be saved to a file using the dot command
.save afl.db (you can name the file something different to afl.db).
After these steps you should have a database with a single table Game, which you can
verify with the dot command .tables.
2
Examining the Table
1. The word schema refers to the structure of the table (not its contents). In SQLite
this is viewed using a dot command1.
Examine the structure of the table (not the contents) by using a dot command
.schema Game
This shows the code used to create the table, therefore shows the names and types
of the columns.
What happens if you accidentally put a semicolon at the end of the dot command?
2. Look at the entire contents of the table
SELECT * FROM Game;
The * is a special character meaning “everything” or “all the columns”.
3. Look at the entire contents of the table
SELECT * FROM Game
LIMIT 10;
The LIMIT statement simply restricts the number of rows of output.
4. If the query returns more than one row, you can ask for the output to be sorted by
the value in some field.
SELECT * FROM Game
ORDER BY homeScore;
You can add a LIMIT statement after the ORDER statement, but not before.
By default, the ordering is in ascending order. (It is clear what this means for
numbers which are listed from smallest to largest, but not so clear what it means
for non-numeric fields).
You may specify the ordering to be descending.
SELECT * FROM Game
ORDER BY homeScore DESC;
Single-table Selections
1. Conditions can be combined using the logical operators AND, OR and NOT.
This returns all the matches featuring West Coast.
1The SQL standard is to use DESCRIBE Game;
3
SELECT *
FROM Game
WHERE homeTeam="West Coast"
OR awayTeam="West Coast";
This returns all the matches where West Coast or Fremantle was the home team.
SELECT *
FROM Game
WHERE homeTeam="West Coast"
OR homeTeam="Fremantle";
The OR statement must combine two boolean values.
SELECT *
FROM Game
WHERE homeTeam="West Coast" OR "Fremantle";
2. A query can create new columns by combining old ones
To see this, use the dot command .headers on so that SQLite lists the column
names.
SELECT gameDay, homeTeam, awayTeam, homeScore-awayScore
FROM Game
LIMIT 10;
Notice that the name of the new column is homeScore-awayScore which is not a
very useful name.
SELECT gameDay, homeTeam, awayTeam, homeScore-awayScore AS homeMargin
FROM Game
LIMIT 10;
Now the final column is called homeMargin
Questions
You may need to refer to Section 2 of https://www.sqlite.org/lang_expr.html to
look up the syntax for arithmetic and boolean operators, and Section 1 of https://www.
sqlite.org/lang_corefunc.html to look up syntax for arithmetic functions.
Question 1. Write a SQL query that will list the names of the home teams that have
scored strictly more than 175 points. (If a team has scored more than 175 points on
multiple occasions, then their name should appear once for each occasion.)
4
Question 2. Write a SQL query that will list the names of the home teams that have
scored at least 175 points.
Question 3. Repeat the previous query, but using SELECT DISTINCT instead of SELECT.
What happens?
Question 4. Write a SQL query to determine the highest home score in the database?
[Hint: ORDER BY and LIMIT may be useful here.]
Question 5. cssubmit Q1.sql Write a SQL query to list the match details (just use
SELECT *) for the matches that have been decided by exactly one point . In other words,
the winning score is just one point more than the losing score.
Make sure that your output includes the following rows:
2019-07-12|West Coast|Collingwood|77|78|0
2019-05-26|Fremantle|Brisbane|73|72|0
Question 6. cssubmit Q2.sql Write a SQL command that will output a table with
the date, home and away teams and the total number of points scored. Make sure that
the new column is called totalScore.
With .headers on, the first few rows of the output should be:
gameDay|homeTeam|awayTeam|totalScore
2019-09-28|Richmond|GWS Giants|139
2019-09-21|Collingwood|GWS Giants|108
2019-09-20|Richmond|Geelong|151
2019-09-14|Brisbane|GWS Giants|163
Question 7. Write a SQL query that will list the date, home and away teams and total
score with the rows listed in decreasing order of total score.
The first few rows of the output table should be:
5
gameDay|homeTeam|awayTeam|totalScore
2011-07-30|Geelong|Melbourne|280
2011-05-15|Western Bulldogs|Richmond|271
2016-03-27|West Coast|Brisbane|268
Question 8. cssubmit Q3.sql Write a SQL query that will produce the date, home
and away teams and the winning margin of each game. The winning margin is always
positive, and so you may need to look up the function ABS which calculates the absolute
value of a number.
The first few rows of the output table should be:
gameDay|homeTeam|awayTeam|margin
2019-09-28|Richmond|GWS Giants|89
2019-09-21|Collingwood|GWS Giants|4
2019-09-20|Richmond|Geelong|19
Question 9. A “Western Derby” is any match between Fremantle and West Coast.
Write a SQL command that lists the match details (use SELECT *) for all of the western
derbies in the database.
There are two western derbies in each regular season, and the two teams have never met
in a playoff game, so your code should produce exactly 20 rows.
Question 10. Write a SQL query to list the match details of all the playoff matches
that Hawthorn has won. This will require careful attention to how the boolean operators
OR and AND can be combined.
6
51作业君

Email:51zuoyejun

@gmail.com

添加客服微信: abby12468