SQL 服务器 : 2 questions for homework can't figure it out

Question

这两个问题都将根据此表计算:

CREATE TABLE WINE
(
    wineID              INT,
    wineName            VARCHAR(50),
    wineYear            CHAR(4),
    winePrice           DECIMAL(5,2),
    wineDesc            VARCHAR(255),
    wineHarvest         DATE,
    wineAlcohol         DECIMAL(5,2),
    wineSugar_gl            DECIMAL(5,2),   
    wineAcidity_gl          DECIMAL(3,2),
    winePHLevel         DECIMAL(3,2),
    wineIsMaloLactic        BIT,
    wineCaseProduction      SMALLINT,

    CONSTRAINT pk_wine_wineID   PRIMARY KEY(wineID)
);

/*22 New Wine Records*/

INSERT INTO WINE VALUES(1,'Dean''s List Chardonnay','2007', 27.95, 'This Chardonnay provides enticing aromas of apricots, vanilla bean and butterscotch. A medium bodied Chardonnay, with a silky mouth texture, and nice acidity for balance. Flavours of caramel, peach and candied lemon dominate the palate', '2007-09-20',13.0, 3.1, 5.6, 3.4, 0, 200);

INSERT INTO WINE VALUES(2,'Barrel Fermented Chardonnay','2008', 18.95, 'This Chardonnay provides tantalizing aromas of caramel, vanilla bean and peach pie. This full-bodied wine has a silky mouth texture, offering characteristics of butterscotch and baked apples on the palate, leading to a long and lingering finish.', '2008-10-13',13.5, 3, 6, 3.46, 0, 320);

INSERT INTO WINE VALUES(3,'Unoaked Chardonnay','2009', 14.95, 'With lively aromas of citrus and melon, this refreshing Chardonnay offers flavours of lemon and green apple on the palate', '2009-10-29',13.0, 6, 7, 3.3, 1, 190);

INSERT INTO WINE VALUES(4,'Gewurztraminer','2009', 14.95, 'This is a highly aromatic Gewurztraminer, offering aromas of lychee, rose petals and citrus fruit. On the palate,this wine has lively acidity, with flavours of grapefruit and orange marmalade', '2009-10-14',12.0, 5, 7.2, 3.2, 1, 293);

INSERT INTO WINE VALUES(5,'Sauvignon Blanc','2008', 14.95, 'Our 2008 Sauvignon Blanc is highly aromatic providing aromas of citrus and fresh cut grass. This wine is nicely balanced, with refreshing acidity and offers flavours of citrus fruit and green fig on the palate', '2008-09-29',12.0, 4, 6.9, 3.4, 1, 270);

INSERT INTO WINE VALUES(6,'Riesling ','2008', 13.95, 'Our 2008 Riesling offers aromas of lemon and lime on the nose, followed by flavours of green apple and citrus fruit on the palate. This is a nicely balanced wine offering crisp acidity, and a lovely finish', '2008-10-08',11.5, 9, 6.8, 3.2, 1, 420);

INSERT INTO WINE VALUES(7,'Riesling Semi-Dry','2009', 13.95, 'Our 2009 Riesling offers aromas of citrus fruit and peach on the nose, followed by flavours of green apple and citrus fruit on the palate. A nicely balanced wine with crisp acidity, and a hint of sweetness', '2009-10-30',12.0, 18, 8.5, 3, 1, 227);

INSERT INTO WINE VALUES(8,'College White','2008', 10.95, 'A Chardonnay/Vidal blend offering aromas of peach and orange blossoms. This wine is nicely balanced, with mild acidity and offers flavours of tropical fruit on the palate', '2008-09-20',12.0, 5.9, 6.2, 3.3, 1, 250);

INSERT INTO WINE VALUES(9,'Dean''s List Prodigy Icewine','2009', 37.95, 'This Vidal/Chardonnay/Riesling blend has a very expressive nose, with aromas of candied lemon and tropical fruit. Flavours of mango and honey coat the palate, with a perfect balance between acidity and sweetness', '2010-01-29',11.0, 211, 8.6, 3.4, 1, 96);

INSERT INTO WINE VALUES(10,'Dean''s List Savant Icewine','2008', 69.95, 'This Icewine offers aromas of strawberry jam, and rhubarb on the nose, with similar flavours on the palate. This wine has a beautiful mouth texture, with a perfect balance between sweetness and acidity', '2009-01-21',12.0, 225, 3.4, 8.8, 1, 61);

INSERT INTO WINE VALUES(11,'Dean''s List Cabernet Franc Icewine','2009', 29.95, 'This Cabernet Franc Icewine offers aromas of strawberry, honey and rhubarb on the nose, with similar flavours on the palate. This wine has a beautiful mouth texture, with a perfect balance between sweetness and acidity', '2010-01-29',11.0, 210, 8.4, 3.4, 1, 472);

INSERT INTO WINE VALUES(12,'Dean''s List Meritage','2007', 47.95, 'This complex Meritage offers black currants, plum and cedar on the nose, with flavours of cassis, dark chocolate and blackberry. Full bodied, with firm yet ripe tannins, and a long and lingering finish', '2006-10-01',13.0, 2.8, 6, 3.51, 0, 250);

INSERT INTO WINE VALUES(13,'Dean''s List Pinot Noir Canadian Oak Project','2007', 38.95, 'This limited edition Pinot offers aromas of ripe strawberries, cigar and mocha. This medium bodied red provides firm, yet ripe tannins and flavours of black cherry and vanilla bean', '2007-10-10',13.0, 3, 5.8, 3.5, 0, 61);

INSERT INTO WINE VALUES(14,'Meritage ','2005', 37.95, 'This powerhouse Meritage offers jammy black fruit, cassis and eucalyptus on the nose, with similar flavours on the palate. Full-bodied, with firm yet ripe tannins and a beautifully long finish', '2004-10-01',13.0, 2.8, 7.5, 3.51, 0, 275);

INSERT INTO WINE VALUES(15,'Dean''s List Pinot Noir','2007', 32.95, 'With aromas of beet root, earth and sour cherry, this Pinot offers raspberry and vanilla bean on the palate. A medium bodied Pinot with firm, yet ripe tannins leading to a long finish', '2007-10-10',13.5, 4, 5.8, 3.5, 0, 350);

INSERT INTO WINE VALUES(16,'Shiraz','2006', 18.95, 'With aromas of cracked black pepper, and red fruit, this Shiraz offers flavours of raspberry, plum and spice. Medium bodied with smooth tannins, and a long and lingering finish', '2005-10-25',13.0, 2.3, 6.6, 3.25, 0, 275);

INSERT INTO WINE VALUES(17,'Cabernet Franc','2007', 18.95, 'This is a very rich Cabernet Franc with an expressive nose of blackberry jam and crème de cassis. Flavours of black fruit, baking spice and black licorice coat the palate, with a pleasant balance between fruit, tannin, acidity and alcohol. Lovely finish', '2007-10-25',13.0, 4, 6.5, 3.63, 0, 458);

INSERT INTO WINE VALUES(18,'Pinot Noir','2008', 15.95, 'This Pinot provides aromas of black cherry and earth, followed by flavours of sour cherry and vanilla. Light-medium bodied Pinot with smooth tannins, and a lingering finish', '2008-10-07',13.0, 4.5, 6.2, 3.8, 0, 410);

INSERT INTO WINE VALUES(19,'Merlot','2006', 15.95, 'With aromas of plum, and black cherries, this Merlot offers firm yet ripe tannins and flavours of black berries and currants. Nice finish', '2006-10-24',13.0, 4.4, 6.5, 3.51, 0, 370);

INSERT INTO WINE VALUES(20,'Cabernet Sauvignon','2006', 15.95, 'With a nose of black fruits and spice, this Cabernet Sauvignon offers flavours of cassis and anise on the palate', '2006-10-29',13.0, 5, 6.7, 3.5, 0, 270);

INSERT INTO WINE VALUES(21,'Baco Noir','2006', 11.95, 'Little known in the rest of the world, Baco is a signature hybrid for Ontario. This Baco Noir offers aromas of raisin, vanilla and black cherry, with similar flavours on the palate', '2006-09-01',11.5, 5.5, 9.1, 3.32, 0, 140);

INSERT INTO WINE VALUES(22,'College Red','2008', 10.95, 'This Cabernet Franc/Baco Noir blend offers aromas of jammy fruit, mocha and sweet bell pepper, with similar flavours on the palate. An easy drinking red wine with soft tannins and a pleasant finish', '2008-09-23',12, 3, 7, 3.6, 0, 590);

1.我们收获 2009 年的冰酒已经多少天了？

SELECT wineName
    ,DATEDIFF(DAY, wineHarvest, GETDATE()) AS '# of
 Days Since Harvest'
FROM WINE
WHERE wineName LIKE '%Icewine'
    AND YEAR(wineYear) = '2009';

这是我的教授告诉我的“看一下 wineYear 列的数据类型，然后问问自己 YEAR () 函数的用途，然后再试一次。你已经非常接近了。”但我只是不知道..

2.含糖量/gl最高的酒的名称、价格、酒精度是多少？

SELECT MAX(wineSugar_gl) AS wineName
    ,winePrice
    ,wineAlcohol
FROM WINE;

虽然对于这一个，我遇到了一个错误，但我不知道如何修复它。我需要它来显示含糖量最高的 Wine 的名称价格和酒精度百分比。

它们看起来像这样:

Answer 1

1. YEAR 函数用于解析时间、日期、smalldatetime、datetime、datetime2 或 datetimeoffset 值中的年份。实际上，您不需要在查询中使用它，因为已经有一个列可以显示每种 Wine 的年份。

   SELECT wineName
,DATEDIFF(DAY, wineHarvest, GETDATE()) AS '# of
收获后的天数
来自 Wine
WHERE wineName LIKE '%Icewine'
AND wineYear = '2009';

2. 您当前的查询正在运行聚合函数 (MAX()) - 这里不需要。聚合函数用于累积多行的值。但是，你的值(value)已经摆在桌面上了!您可以执行以下操作:

   选择前 1
wineSugar_gl
,酒价
,酒类
来自 Wine
按 wineSugar_gl DESC 排序；