In this lab, you will install PostgreSQL, create, and populate a database. You will also practice some SQL commands. It is important that you install PostgreSQL on your own computer. In a future lab, we will connect your database to your website using Node.js.
What To Do
Part 1: Install PostgreSQL
For this part of the lab, you will install PostgreSQL on your computer.
Step 1.If you are on Linux, use the following commands to install PostgreSQL:
$ sudo apt-get install postgresql postgresql-contrib
Start PostgreSQL using this commands:
$ service postgresql start
If you are on Windows, you can download the installer here.
If you are on a Mac, you can install PostgreSQL using these instructions.
Part 2: SQL
For the second part of the lab, you will create a database, populate it, and runs some queries.
Step 1.Create a database named football_db. Enter the following command into the
PostgreSQL terminal:
postgres=#| create database football_db;
Switch to the newly created database:
postgres=#| \c football_db;
Step 2.You will now create and populate two tables in your database, one for the football players and one for the football games. Download the file “tables.txt” here. Copy and paste the SQL commands into your Postgres terminal.
Step 3.Create a separate text file and name it “lab4.txt”. From here on out, you will record all of your SQL commands/statements in this file.
Step 4.Write SQL statements to complete the following tasks (remember to record them your lab4.txt file):
Write a SQL statement to create a new table to hold information about the different universities competing in football. The table should hold the following information:
University Name (text)
Date Established (date)
Address (address)
Student Population (int)
Acceptance Rate (decimal)
2. Write a SQL statement to insert the following university information:
University Name
CU Boulder
Date Established
1876
Address
1100 28th St, Boulder, CO 80303
Student Population
35,000
Acceptance Rate
80%
Write a SQL statement to list the football players name and major, organized by major in college.
Write a SQL statement to list all of the football players names and rushing yards who have 70 rushing yards or more.
Write a SQL statement to list all of the games played against Nebraska (show all game information).
Write a SQL statement to list all of the games CU Boulder has won.
Write a SQL statement to list all of the games played in the fall 2018 season (show team names and game dates).
Write a SQL statement to create a view that counts the total number of winning games.
Write a SQL statement to create a view that counts the total number of games played.
Write a SQL statement that will count how many games “Cedric Vega” has played in during his entire football career.
Write a SQL statement that will calculate the average number of rushing yards for “Cedric Vega” based on the number of games he has played.
What To Turn In
Submit your lab4.txt file on Canvas.
