Question

USE SQL

CREATE TABLE IF NOT EXISTS football_games (
visitor_name VARCHAR(30),       /* Name of the visiting team                     */
home_score SMALLINT NOT NULL,   /* Final score of the game for the Buffs         */
visitor_score SMALLINT NOT NULL,/* Final score of the game for the visiting team */
game_date DATE NOT NULL,        /* Date of the game                              */
players INT[] NOT NULL,         /* This array consists of the football player ids (basically a foreign key to the football_player.id) */
PRIMARY KEY(visitor_name, game_date) /* A game's unique primary key consists of the visitor_name & the game date (this assumes you can't have multiple games against the same team in a single day) */
);

CREATE TABLE IF NOT EXISTS football_players(
id SERIAL PRIMARY KEY,       /* Unique identifier for each player (it's possible multiple players have the same name/similiar information) */
name VARCHAR(50) NOT NULL,   /* The player's first & last name */
year VARCHAR(3),             /* FSH - Freshman, SPH - Sophomore, JNR - Junior, SNR - Senior */
major VARCHAR(4),            /* The unique 4 character code used by CU Boulder to identify student majors (ex. CSCI, ATLS) */
passing_yards SMALLINT,      /* The number of passing yards in the players entire football career */
rushing_yards SMALLINT,      /* The number of rushing yards in the players entire football career */
receiving_yards SMALLINT,    /* The number of receiving yards in the players entire football career*/
img_src VARCHAR(200)         /* This is a file path (absolute or relative), that locates the player's profile image */
);

INSERT INTO football_games(visitor_name, home_score, visitor_score, game_date, players)
VALUES('Colorado State', 45, 13, '20200831', ARRAY [1,2,3,4,5]),
('Nebraska', 33, 28, '20200908', ARRAY [2,3,4,5,6]),
('New Hampshire', 45, 14, '20200915', ARRAY [3,4,5,6,7]),
('UCLA', 38, 16, '20200928', ARRAY [4,5,6,7,8]),
('Arizona State', 28, 21, '20201006', ARRAY [5,6,7,8,9]),
('Southern California', 20, 31, '20201013', ARRAY [6,7,8,9,10]),
('Washington', 13, 27, '20201020', ARRAY [7,8,9,10,1]),
('Oregon State', 34, 41, '20201027', ARRAY [8,9,10,1,2]),
('Arizona', 34, 42, '20201102', ARRAY [9,10,1,2,3]),
('Washington State', 7, 31, '20201110', ARRAY [10,1,2,3,4]),
('Utah', 7, 30, '20201117', ARRAY [1,2,3,4,5]),
('California', 21, 33, '20201124', ARRAY [2,3,4,5,6])
;

INSERT INTO football_players(name, year, major, passing_yards, rushing_yards, receiving_yards)
VALUES('Cedric Vega', 'FSH', 'ARTS', 15, 25, 33),
('Myron Walters', 'SPH', 'CSCI', 32, 43, 52),
('Javier Washington', 'JNR', 'MATH', 1, 61, 45),
('Wade Farmer', 'SNR', 'ARTS', 14, 55, 12),
('Doyle Huff', 'FSH', 'CSCI', 23, 44, 92),
('Melba Pope', 'SPH', 'MATH', 13, 22, 45),
('Erick Graves', 'JNR', 'ARTS', 45, 78, 98 ),
('Charles Porter', 'SNR', 'CSCI', 92, 102, 125),
('Rafael Boreous', 'JNR', 'MATH', 102, 111, 105),
('Jared Castillo', 'SNR', 'ARTS', 112, 113, 114);

1.Write an SQL Script to create a new table to hold information on the competing universities. The table should hold the following information:

University Name (VARCHAR(200)) (Note: University Name should be unique and set as PRIMARY KEY)
Date Established (Date)
Address (Text)
Student Population (Int)
Acceptance Rate (Decimal)

2.Write an insert statement to add the University Information The table should hold the following information:

University Name :- CU Boulder
Date Established :- April 1st, 1876
Address :- 1100 28th St, Boulder, CO 80303
Student Population :- 35,000
Acceptance Rate :- 80%

1.Write a script to create a view that counts the total number of winning games. Output the view.

Output:

  

2.Write a script to create a view that counts the total number of games played. Output the view.

Output:

     

3. Write a script that uses the two views you created (5.1 and 5.2) to calculate the percent of wins.

Output:

4. Write a script to list all of the games played against Nebraska

5. Write a script to list all of the games CU Boulder has won

6. Write a script to list all of the games played in the Fall 2020 Season, September 1st through December 31st

7.Write a script to list the majors of the Football players and calculate how many of them are in each of the majors listed. Rename the column where you calculate the majors to "number_of_players".

  

Answer 1

1.)Write an SQL Script to create a new table to hold information on the competing universities

CREATE TABLE IF NOT EXISTS Universities(
University_Name VARCHAR(200) PRIMARY KEY,  
Date_Established VARCHAR(30),          
Address VARCHAR(100),           
Student_Population INT,    
Acceptance_Rate DECIMAL(2,2)     
);

2.) Write an insert statement to add the University Information The table

INSERT INTO Universities(University_Name, Date_Established, Address, Student_Population, Acceptance_Rate) VALUES ( 'CU Boulder', 'April 1st,1876', '1100 28th St, Boulder, CO 80303', '35000', '80');

1.Write a script to create a view that counts the total number of winning games

CREATE VIEW Winning_games AS SELECT COUNT(home_score) AS win FROM football_games where home_score > visitor_score; /* This is the view that shows number of matches home team wins.*/

CREATE VIEW Winning_games AS SELECT COUNT(home_score) AS winner FROM football_games where home_score != visitor_score; /* This is the view that shows one of two teams wins the match .*/

2.Write a script to create a view that counts the total number of games played.

CREATE VIEW game_played AS SELECT COUNT(home_score) AS played FROM football_games;

3.Write a script that uses the two views you created (5.1 and 5.2) to calculate the percent of wins.

SELECT (win/played)*100 AS win_percentage FROM Winning_games, game_played;

//win percentage of home team

4. Write a script to list all of the games played against Nebraska

SELECT * FROM football_games WHERE visitor_name = 'Nebraska';

5. Write a script to list all of the games CU Boulder has won

SELECT * FROM football_games WHERE visitor_name = 'CU Boulder' AND home_score < visitor_score; // Here CU Boulder is considered as visitor team.

6. Write a script to list all of the games played in the Fall 2020 Season, September 1st through December 31st

SELECT * FROM football_games WHERE game_date BETWEEN 'September 1st, 2020' AND 'December 31st, 2020';

7.Write a script to list the majors of the Football players and calculate how many of them are in each of the majors listed. Rename the column where you calculate the majors to "number_of_players".

SELECT major, COUNT(id) AS number_of_players FROM football_players group by major;