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 mat
ch 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
