Question

write a SQL statements to perform the following:

Select all years a World Series game was played
Select all losing teams that played in a World Series game
Select all winning and losing teams that played in a World Series game
Select all cities of a winning or losing team that played in a World Series game
Select all winning and losing teams that played in a World Series game, and provide aliases of "Winning Team" and "Losing Team"
Select all cities of a winning or losing team that played in a World Series game and provide aliases of "Winning City" and "Losing City"
Select all winning teams that played in a World Series game and provide an alias of "Winning Team"; list each only once
Select all losing teams that played in a World Series game; provide an alias of "Losing Team"; list each only once
Select all winning teams that played in a World Series; list each team and city combination once
Select all losing teams that played in a World Series game; list each team and city combination once

DROP TABLE Wins_S001;
This command demonstrates how to drop a table. Do not drop the table and then try to add records.

CREATE TABLE Wins_S001(Team CHAR(20),
                       City CHAR(20),
                       Year_T INT NOT NULL PRIMARY KEY,
                       LoserTeam CHAR(20),
                       LoserCity CHAR(20));

DESCRIBE Wins_S001;

INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Red Sox', 'Boston', 1903, 'Pirates', 'Pittsburgh');

INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Giants', 'New York', 1905, 'Athletics', 'Philadelphia');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('White Sox', 'Chicago', 1906, 'Cubs', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cubs', 'Chicago', 1907, 'Tigers', 'Detroit');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cubs', 'Chicago', 1908, 'Tigers', 'Detroit');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Pirates', 'Pittsburgh', 1909, 'Tigers', 'Detroit');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Athletics', 'Philadelphia', 1910, 'Cubs', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Athletics', 'Philadelphia', 1911, 'Giants', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Red Sox', 'Boston', 1912, 'Giants', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Athletics', 'Philadelphia', 1913, 'Giants', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Braves', 'Boston', 1914, 'Athletics', 'Philadelphia');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Red Sox', 'Boston', 1915, 'Phillies', 'Philadelphia');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Red Sox', 'Boston', 1916, 'Robins', 'Brooklyn');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('White Sox', 'Chicago', 1917, 'Giants', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Red Sox', 'Boston', 1918, 'Cubs', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Reds', 'Cincinnati', 1919, 'White Sox', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Indians', 'Cleveland', 1920, 'Robins', 'Brooklyn');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Giants', 'New York', 1921, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Giants', 'New York', 1922, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1923, 'Giants', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Senators', 'Washington', 1924, 'Giants', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Pirates', 'Pittsburgh', 1925, 'Nationals', 'Wasnington');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 1926, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1927, 'Pirates', 'Pittsburgh');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1928, 'Cardinals', 'St. Louis');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Athletics', 'Philadelphia', 1929, 'Cubs', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Athletics', 'Philadelphia', 1930, 'Cardinals', 'St. Louis');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 1931, 'Athletics', 'Philadelphia');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1932, 'Cubs', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Giants', 'New York', 1933, 'Nationals', 'Wasnington');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 1934, 'Tigers', 'Detroit');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Tigers', 'Detroit', 1935, 'Cubs', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1936, 'Giants', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1937, 'Giants', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1938, 'Cubs', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1939, 'Reds', 'Cincinnati');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Reds', 'Cincinnati', 1940, 'Tigers', 'Detroit');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1941, 'Dodgers', 'Brooklyn');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 1942, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1943, 'Cardinals', 'St. Louis');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 1944, 'Browns', 'St. Louis');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Tigers', 'Detroit', 1945, 'Cubs', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 1946, 'Red Sox', 'Boston');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1947, 'Dodgers', 'Brooklyn');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Indians', 'Cleveland', 1948, 'Braves', 'Boston');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1949, 'Dodgers', 'Brooklyn');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1950, 'Phillies', 'Philadelphia');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1951, 'Giants', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1952, 'Dodgers', 'Brooklyn');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1953, 'Dodgers', 'Brooklyn');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Giants', 'New York', 1954, 'Indians', 'Cleveland');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Dodgers', 'Brooklyn', 1955, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1956, 'Dodgers', 'Brooklyn');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Braves', 'Milwaukee', 1957, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1958, 'Braves', 'Milwaukee');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Dodgers', 'Los Angeles', 1959, 'White Sox', 'Chicago');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Pirates', 'Pittsburgh', 1960, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1961, 'Reds', 'Cincinnati');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1962, 'Giants', 'San Francisco');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Dodgers', 'Los Angeles', 1963, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 1964, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Dodgers', 'Los Angeles', 1965, 'Twins', 'Minnesota');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Orioles', 'Baltimore', 1966, 'Dodgers', 'Los Angeles');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 1967, 'Red Sox', 'Boston');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Tigers', 'Detroit', 1968, 'Cardinals', 'St. Louis');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Mets', 'New York', 1969, 'Orioles', 'Baltimore');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Orioles', 'Baltimore', 1970, 'Reds', 'Cincinnati');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Pirates', 'Pittsburgh', 1971, 'Orioles', 'Baltimore');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Athletics', 'Oakland', 1972, 'Reds', 'Cincinnati');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Athletics', 'Oakland', 1973, 'Mets', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Athletics', 'Oakland', 1974, 'Dodgers', 'Los Angeles');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Reds', 'Cincinnati', 1975, 'Red Sox', 'Boston');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Reds', 'Cincinnati', 1976, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1977, 'Dodgers', 'Los Angeles');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1978, 'Dodgers', 'Los Angeles');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Pirates', 'Pittsburgh', 1979, 'Orioles', 'Baltimore');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Phillies', 'Philadelphia', 1980, 'Royals', 'Kansas City');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Dodgers', 'Los Angeles', 1981, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 1982, 'Brewers', 'Milwaukee');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Orioles', 'Baltimore', 1983, 'Phillies', 'Philadelphia');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Tigers', 'Detroit', 1984, 'Padres', 'San Diego');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Royals', 'Kansas City', 1985, 'Cardinals', 'St. Louis');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Mets', 'New York', 1986, 'Red Sox', 'Boston');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Twins', 'Minnesota', 1987, 'Cardinals', 'St. Louis');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Dodgers', 'Los Angeles', 1988, 'Athletics', 'Oakland');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Athletics', 'Oakland', 1989, 'Giants', 'San Francisco');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Reds', 'Cincinnati', 1990, 'Athletics', 'Oakland');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Twins', 'Minnesota', 1991, 'Braves', 'Atlanta');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Blue Jays', 'Toronto', 1992, 'Braves', 'Atlanta');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Blue Jays', 'Toronto', 1993, 'Phillies', 'Philadelphia');

INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Braves', 'Atlanta', 1995, 'Indians', 'Cleveland');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1996, 'Braves', 'Atlanta');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Marlins', 'Florida', 1997, 'Indians', 'Cleveland');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1998, 'Padres', 'San Diego');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 1999, 'Braves', 'Atlanta');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 2000, 'Mets', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Diamondbacks', 'Arizona', 2001, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Angels', 'Anaheim', 2002, 'Giants', 'San Francisco');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Marlins', 'Florida', 2003, 'Yankees', 'New York');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Red Sox', 'Boston', 2004, 'Cardinals', 'St. Louis');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('White Sox', 'Chicago', 2005, 'Astros', 'Houston');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 2006, 'Tigers', 'Detroit');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Red Sox', 'Boston', 2007, 'Rockies', 'Colorado');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Phillies', 'Philadelphia', 2008, 'Rays', 'Tampa Bay');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Yankees', 'New York', 2009, 'Phillies', 'Philadelphia');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 2010, 'Rangers', 'Texas');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Giants', 'San Franciso', 2011, 'Tigers', 'Detroit');
INSERT INTO Wins_S001(Team, City, Year_T, LoserTeam, LoserCity)VALUES('Cardinals', 'St. Louis', 2012, 'Yankees', 'New York');

COMMIT;

// Select all columns of a table

SELECT * from Wins_S001;

// Select to view one columns of a table

SELECT Team from Wins_S001;

// Select to view multiple columns of a table

SELECT Team, City from Wins_S001;

// Assign an Alias to a column of a table

SELECT Team "Baseball Team", City "Home City" from Wins_S001;

// Command to eliminate duplicate in output

SELECT DISTINCT Team "Baseball Team", City "Home City" from Wins_S001;

SELECT UNIQUE Team "Baseball Team", City "Home City" from Wins_S001;

// Command to concatenation of column content

SELECT DISTINCT Team || City from Wins_S001;

SELECT DISTINCT Team || City "Team / City" from Wins_S001;

// Command to concatenation of column content

SELECT DISTINCT Team || City from Wins_S001;

SELECT DISTINCT Team || City "Team / City" from Wins_S001;

// Restricting Rows

SELECT Team, City, Year_T
FROM Wins_S001
WHERE Year_T > 1995;

// Restricting Rows

SELECT Team, City, Year_T
FROM Wins_S001
WHERE Year_T = 1998;

// Finds all Cardianls
SELECT Team, City, Year_T
FROM Wins_S001
WHERE Team = 'Cardinals';

// No matches found
SELECT Team, City, Year_T
FROM Wins_S001
WHERE Team = 'cardinals';

// Restricting Rows

SELECT Team, City, Year_T
FROM Wins_S001
WHERE Team <= 'Cardinals';

// Restricting Rows

SELECT Team, City, Year_T
FROM Wins_S001
WHERE Team != 'Cubs';

// Restricting Rows
// All 1990 - 2000 Years
// Include 1990 and 2000

SELECT Team, City, Year_T
FROM Wins_S001
WHERE Year_T BETWEEN 1990 AND 2000;

// Restricting Rows
// All 1990 - 2000 Years
// Include 1990 and 2000

SELECT Team, City, Year_T
FROM Wins_S001
WHERE Year_T IN (1990, 1995, 1999);

// Restricting Rows
// All years except 1990, 1995, and 1999

SELECT Team, City, Year_T
FROM Wins_S001
WHERE Year_T NOT IN (1990, 1995, 1999);

// Restricting Rows
// All games played in the 90s

SELECT Team, LoserTeam, Year_T
FROM Wins_S001
WHERE Year_T LIKE '196_';

// Restricting Rows
// All games where the winning team name starts with C

SELECT Team, LoserTeam, Year_T
FROM Wins_S001
WHERE Team LIKE 'C%';

// Restricting Rows
// All games where the winning team name or losing team name starts with C

SELECT Team, LoserTeam, Year_T
FROM Wins_S001
WHERE Team LIKE 'C%' OR LoserTeam LIKE 'C%';

// Restricting Rows
// All games where the winning team name or losing team name starts with C
// Order by year

SELECT Team, LoserTeam, Year_T
FROM Wins_S001
WHERE Team LIKE 'C%' OR LoserTeam LIKE 'C%'
ORDER BY Year_T;

// Restricting Rows
// All games where the winning team name or losing team name starts with C
// Order by year in descending order

SELECT Team, LoserTeam, Year_T
FROM Wins_S001
WHERE Team LIKE 'C%' OR LoserTeam LIKE 'C%'
ORDER BY Year_T DESC;

// Restricting Rows
// All games where the winning team name or losing team name starts with C
// Order by winning team name

SELECT Team, LoserTeam, Year_T
FROM Wins_S001
WHERE Team LIKE 'C%' OR LoserTeam LIKE 'C%'
ORDER BY Team, LoserTeam;

Answer 1

1. Select year_t from Wins_S001;

This query will retrieves all the years game was played.

2. Select LoserTeam from Wins_S001;

This will retrieve the loser teams when the game played.

3. Select Team,LoserTeam from Wins_S001;

This will retrieve all the winning and losing teams.

4. Select City,LoserCity from Wins_S001;

This will retrieve the cities of teams.

5. Select Team as Winning_Team, LoserTeam as Loser_Team from Wins_S001;

This will retrieve all the teams with the required aliases.