write a SQL statements to perform the following: Select all years a World Series
ID: 3835042 • Letter: W
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;
Explanation / Answer
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.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.