Databases: Merging & Analysis in SQL

SELECT Winery, Name, Vintage, Score, Price
FROM wine
WHERE Appellation = 'Napa Valley' AND Score >= 95
ORDER BY Score DESC, Price;

SELECT w.Grape, w.Winery, w.Appellation, w.Name, w.Score
FROM wine w, grapes g, appellations a
WHERE w.Grape = g.Grape
  AND w.Appellation = a.Appellation
  AND g.Color = 'White'
  AND a.County = 'Sonoma'
  AND w.Score >= 93
ORDER BY w.Score DESC, w.Winery;

SELECT w.Winery, w.Name, w.Appellation, w.Price, w.Score
FROM wine w
  JOIN appellations a ON w.Appellation = a.Appellation
WHERE w.Grape = 'Pinot Noir'
  AND a.Area = 'Central Coast'
  AND w.Vintage IN (2008, 2009)
  AND w.Price < 40
ORDER BY w.Price, w.Score DESC;

SELECT Grape, COUNT(*) AS NumWines,
       ROUND(AVG(Score), 1) AS AvgScore,
       ROUND(AVG(Price), 2) AS AvgPrice
FROM wine
GROUP BY Grape
HAVING COUNT(*) >= 10
ORDER BY AvgScore DESC;

SELECT a.County, COUNT(*) AS NumWines, SUM(w.Cases) AS TotalCases
FROM wine w, appellations a
WHERE w.Appellation = a.Appellation
  AND w.Score >= 90
  AND a.County <> 'N/A'
GROUP BY a.County
ORDER BY TotalCases DESC;

SELECT g.Color, COUNT(*) AS NumWines,
       MIN(w.Price) AS Cheapest, MAX(w.Price) AS MostExpensive,
       COUNT(DISTINCT w.Winery) AS Wineries
FROM wine w, grapes g
WHERE w.Grape = g.Grape
GROUP BY g.Color;

SELECT Winery, Name, Grape, Vintage, Score
FROM wine
WHERE Appellation = 'Paso Robles'
  AND Score = (SELECT MAX(Score)
               FROM wine
               WHERE Appellation = 'Paso Robles');

SELECT DISTINCT Winery
FROM wine
WHERE Grape = 'Zinfandel' AND Score >= 90
  AND Winery IN (SELECT Winery
                 FROM wine
                 WHERE Grape = 'Syrah' AND Score >= 90)
ORDER BY Winery;

SELECT w1.Grape, w1.Winery, w1.Name, w1.Score, w1.Price
FROM wine w1
WHERE w1.Score >= 94
  AND w1.Score > (SELECT AVG(w2.Score) FROM wine w2
                  WHERE w2.Grape = w1.Grape)
  AND w1.Price < (SELECT AVG(w2.Price) FROM wine w2
                  WHERE w2.Grape = w1.Grape)
ORDER BY w1.Score DESC, w1.Price;

SELECT Campus, Location, Year
FROM campuses
WHERE Year < 1940
ORDER BY Year;

SELECT f.Year, f.CampusFee
FROM fees f, campuses c
WHERE f.Campus = c.Id
  AND c.Campus = 'California Polytechnic State University-San Luis Obispo'
  AND f.Year BETWEEN 2000 AND 2005
ORDER BY f.Year;

SELECT e.Year, e.Enrolled, d.Degrees
FROM campuses c, enrollments e, degrees d
WHERE c.Id = e.Campus
  AND c.Id = d.Campus
  AND e.Year = d.Year
  AND c.Campus = 'California Polytechnic State University-San Luis Obispo'
  AND e.Year BETWEEN 2000 AND 2004
ORDER BY e.Year;

SELECT c.Campus, de.Undergraduate, de.Graduate
FROM campuses c
  JOIN discEnr de ON de.Campus = c.Id
  JOIN disciplines d ON d.Id = de.Discipline
WHERE d.Name = 'Engineering' AND de.Year = 2004
ORDER BY de.Undergraduate DESC;

SELECT c.Campus, SUM(d.Degrees) AS TotalDegrees
FROM campuses c, degrees d
WHERE c.Id = d.Campus
  AND d.Year BETWEEN 1995 AND 2004
GROUP BY c.Id, c.Campus
ORDER BY TotalDegrees DESC;

SELECT County, COUNT(*) AS NumCampuses
FROM campuses
GROUP BY County
HAVING COUNT(*) > 1
ORDER BY NumCampuses DESC;

SELECT c.Campus, e.FTE, f.Faculty,
       ROUND(e.FTE / f.Faculty, 1) AS StudentFacultyRatio
FROM campuses c, enrollments e, faculty f
WHERE c.Id = e.Campus
  AND c.Id = f.Campus
  AND e.Year = 2004 AND f.Year = 2004
ORDER BY StudentFacultyRatio DESC;

SELECT c.Campus, de.Undergraduate
FROM campuses c, discEnr de, disciplines d
WHERE c.Id = de.Campus
  AND d.Id = de.Discipline
  AND d.Name = 'Computer and Info. Sciences'
  AND de.Year = 2004
  AND de.Undergraduate = (SELECT MAX(de2.Undergraduate)
                          FROM discEnr de2, disciplines d2
                          WHERE d2.Id = de2.Discipline
                            AND d2.Name = 'Computer and Info. Sciences'
                            AND de2.Year = 2004);

SELECT c.Campus, f.CampusFee
FROM campuses c, fees f
WHERE c.Id = f.Campus
  AND f.Year = 2005
  AND f.CampusFee > (SELECT AVG(CampusFee)
                     FROM fees
                     WHERE Year = 2005)
ORDER BY f.CampusFee DESC;