Database Server Demo

Northwind Traders - SQL SERVER

  • COMPANIES

  • COMPANY CONTACTS
    Alfreds Futterkiste
    Maria Anders
    Sales Representative
    030-0074321
    Ana Trujillo Emparedados y helados
    Ana Trujillo
    Owner
    (5) 555-4729
    Antonio Moreno Taquería
    Antonio Moreno
    Owner
    (5) 555-3932
    Around the Horn
    Thomas Hardy
    Sales Representative
    (171) 555-7788
    Berglunds snabbköp
    Christina Berglund
    Order Administrator
    0921-12 34 65
    Blauer See Delikatessen
    Hanna Moos
    Sales Representative
    0621-08460
    Blondesddsl père et fils
    Frédérique Citeaux
    Marketing Manager
    88.60.15.31
  • SQL CODE
    DECLARE @br CHAR(6) = '<br />'

    SELECT TOP 14 rowstyle, datavalue FROM
        (
        SELECT CustomerID, 20 AS rowstyle, CompanyName AS datavalue
        FROM Customers
        UNION
        SELECT CustomerID, 10 AS rowstyle, ContactName + @br +
        ContactTitle + @br + Phone AS datavalue
        FROM Customers
        ) x
    ORDER BY x.CustomerID, x.rowstyle DESC;
  • COMMENTS
    Comments for sql code;
  • PRODUCTS

  • PRODUCTS
    Chai
    $18.00
    Chang
    $19.00
    Aniseed Syrup
    $10.00
    Chef Anton's Cajun Seasoning
    $22.00
    Chef Anton's Gumbo Mix
    $21.35
    Grandma's Boysenberry Spread
    $25.00
    Uncle Bob's Organic Dried Pears
    $30.00
    Northwoods Cranberry Sauce
    $40.00
    Mishi Kobe Niku
    $97.00
    Ikura
    $31.00
  • SQL CODE
    SELECT TOP 20 rowstyle, datavalue FROM
        (
        SELECT ProductID, 20 AS rowstyle,
        ProductName AS datavalue
        FROM Products
        UNION
        SELECT ProductID, 10 AS rowstyle,
        '$' + CAST(UnitPrice AS VARCHAR(10)) AS datavalue
        FROM Products
        ) x
    ORDER BY x.ProductID, x.rowstyle DESC;
  • COMMENTS
    Comments for sql code;
  • ORDERS

  • ORDERS
    Vins et alcools Chevalier
    Ordered: 07/04/1996
    Shipped: 07/16/1996
    Processed: 12 days.
    59 rue de l'Abbaye
    Reims
    France
    Toms Spezialitäten
    Ordered: 07/05/1996
    Shipped: 07/10/1996
    Processed: 5 days.
    Luisenstr. 48
    Münster
    Germany
    Hanari Carnes
    Ordered: 07/08/1996
    Shipped: 07/12/1996
    Processed: 4 days.
    Rua do Paço, 67
    Rio de Janeiro
    Brazil
    Victuailles en stock
    Ordered: 07/08/1996
    Shipped: 07/15/1996
    Processed: 7 days.
    2, rue du Commerce
    Lyon
    France
    Suprêmes délices
    Ordered: 07/09/1996
    Shipped: 07/11/1996
    Processed: 2 days.
    Boulevard Tirou, 255
    Charleroi
    Belgium
    Hanari Carnes
    Ordered: 07/10/1996
    Shipped: 07/16/1996
    Processed: 6 days.
    Rua do Paço, 67
    Rio de Janeiro
    Brazil
    Chop-suey Chinese
    Ordered: 07/11/1996
    Shipped: 07/23/1996
    Processed: 12 days.
    Hauptstr. 31
    Bern
    Switzerland
    Richter Supermarkt
    Ordered: 07/12/1996
    Shipped: 07/15/1996
    Processed: 3 days.
    Starenweg 5
    Genève
    Switzerland
    Wellington Importadora
    Ordered: 07/15/1996
    Shipped: 07/17/1996
    Processed: 2 days.
    Rua do Mercado, 12
    Resende
    Brazil
    HILARION-Abastos
    Ordered: 07/16/1996
    Shipped: 07/22/1996
    Processed: 6 days.
    Carrera 22 con Ave. Carlos Soublette #8-35
    San Cristóbal
    Venezuela
  • SQL CODE
    DECLARE @br CHAR(6) = '<br />'

    SELECT TOP 20 rowstyle, datavalue from
        (
        SELECT orders.OrderID, 20 AS rowstyle,
        orders.ShipName AS datavalue
        FROM dbo.orders
        union
        SELECT orders.OrderID, 10 AS rowstyle,
        'Ordered: ' +
        CONVERT(varchar(10),orders.OrderDate, 101) + @br +
        'Shipped: ' +
        CONVERT(varchar(10),orders.ShippedDate, 101) + @br +
        'Processed: ' +
        CAST(datediff(day,orders.OrderDate,orders.ShippedDate)
         AS VARCHAR(8)) + ' days. ' + @br +
        orders.ShipAddress + @br +
        orders.ShipCity + @br +
        orders.ShipCountry AS datavalue
        FROM dbo.orders
        ) x
    ORDER BY x.OrderID, x.rowstyle DESC;
  • COMMENTS
    Comments for sql code;
  • SALES

  • SALES
    Côte de Blaye (1998) - $67324.25
    Thüringer Rostbratwurst (1998) - $33683.27
    Raclette Courdavault (1998) - $26345.00
    Camembert Pierrot (1998) - $17295.12
    Tarte au sucre (1998) - $15992.93
    Uncle Bob's Organic Dried Pears (1998) - $12306.00
    Manjimup Dried Apples (1998) - $11090.25
    Wimmers gute Semmelknödel (1998) - $10696.20
    Ikura (1998) - $9002.40
    Sir Rodney's Marmalade (1998) - $8704.26
    Carnarvon Tigers (1998) - $8496.88
    Alice Mutton (1998) - $8131.50
    Ipoh Coffee (1998) - $7525.60
    Gnocchi di nonna Alice (1998) - $7225.70
    Rössle Sauerkraut (1998) - $6990.48
    Flotemysost (1998) - $6863.88
    Mozzarella di Giovanni (1998) - $6525.00
    Vegie-spread (1998) - $6453.30
    Lakkalikööri (1998) - $6335.10
    Chang (1998) - $6299.45
    Chai (1998) - $6295.50
    Gumbär Gummibärchen (1998) - $6044.57
    Pâté chinois (1998) - $5660.40
    Pavlova (1998) - $5424.17
    Boston Crab Meat (1998) - $5317.60
    Sirop d'érable (1998) - $5261.10
    Perth Pasties (1998) - $4808.48
    Nord-Ost Matjeshering (1998) - $4744.34
    Gudbrandsdalsost (1998) - $4640.40
    Steeleye Stout (1998) - $4632.30
    Northwoods Cranberry Sauce (1998) - $4592.00
    Mascarpone Fabioli (1998) - $4568.00
    Queso Cabrales (1998) - $4383.75
    Chartreuse verte (1998) - $4260.60
    Maxilaku (1998) - $4196.00
    Inlagd Sill (1998) - $3937.75
    Grandma's Boysenberry Spread (1998) - $3917.00
    Original Frankfurter grüne Soße (1998) - $3755.05
    Konbu (1998) - $3725.10
    Queso Manchego La Pastora (1998) - $3575.80
    Gorgonzola Telino (1998) - $3465.63
    Ravioli Angelo (1998) - $3441.75
    Sir Rodney's Scones (1998) - $3415.00
    Outback Lager (1998) - $3395.25
    Scottish Longbreads (1998) - $3262.50
    Sasquatch Ale (1998) - $3241.00
    Chef Anton's Gumbo Mix (1998) - $3042.38
    Rhönbräu Klosterbier (1998) - $2953.53
    Jack's New England Clam Chowder (1998) - $2915.75
    Schoggi Schokolade (1998) - $2853.50
    Gustaf's Knäckebröd (1998) - $2793.00
    Singaporean Hokkien Fried Mee (1998) - $2788.80
    Escargots de Bourgogne (1998) - $2427.40
    Guaraná Fantástica (1998) - $2317.50
    Louisiana Fiery Hot Pepper Sauce (1998) - $2022.91
    Teatime Chocolate Biscuits (1998) - $1974.32
    Spegesild (1998) - $1817.28
    Tunnbröd (1998) - $1557.00
    Chef Anton's Cajun Seasoning (1998) - $1501.50
    Gravad lax (1998) - $1456.00
    Laughing Lumberjack Lager (1998) - $1444.80
    NuNuCa Nuß-Nougat-Creme (1998) - $1236.20
    Gula Malacca (1998) - $1135.89
    Aniseed Syrup (1998) - $1080.00
    Zaanse koeken (1998) - $930.05
    Valkoinen suklaa (1998) - $893.75
    Rogede sild (1998) - $891.10
    Filo Mix (1998) - $862.40
    Tourtière (1998) - $658.95
    Geitost (1998) - $476.63
    Longlife Tofu (1998) - $400.00
    Tofu (1998) - $371.30
    Mishi Kobe Niku (1998) - $291.00
    Röd Kaviar (1998) - $179.70
    Chocolade (1998) - $86.70
    Louisiana Hot Spiced Okra (1998) - $17.00
  • SQL CODE
    SELECT ROW_NUMBER() OVER (ORDER BY P.ProductName ASC) AS ProductID,
    20 AS rowstyle,
    P.ProductName + ' (1998) - $' +
    CAST(SUM(ROUND(CONVERT(MONEY, Quantity * (1 - Discount) * Od.UnitPrice), 2)) AS NVARCHAR(10))
    AS datavalue
    FROM dbo.[Order Details] OD, Orders O, Products P, Categories C
    WHERE OD.OrderID = O.OrderID
    AND OD.ProductID = P.ProductID
    AND P.CategoryID = C.CategoryID
    AND DATEPART(YEAR,O."OrderDate") = '1998'
    GROUP BY P.ProductName
    ORDER BY SUM(ROUND(CONVERT(MONEY, Quantity * (1 - Discount) * Od.UnitPrice), 2)) DESC;
  • COMMENTS
    Comments for sql code;
  • EMPLOYEES

  • EMPLOYEES
    Ms. Nancy Davolio
    Sales Representative (USA) WA Seattle
    Ext: 5467
    DOB: 12/08/1948, Age: 75

    Notes:
    Education includes a BA in psychology from Colorado State University in 1970. She also completed "The Art of the Cold Call." Nancy is a member of Toastmasters International.

    Dr. Andrew Fuller
    Vice President, Sales (USA) WA Tacoma
    Ext: 3457
    DOB: 02/19/1952, Age: 72

    Notes:
    Andrew received his BTS commercial in 1974 and a Ph.D. in international marketing from the University of Dallas in 1981. He is fluent in French and Italian and reads German. He joined the company as a sales representative, was promoted to sales manager in January 1992 and to vice president of sales in March 1993. Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.

    Ms. Janet Leverling
    Sales Representative (USA) WA Kirkland
    Ext: 3355
    DOB: 08/30/1963, Age: 60

    Notes:
    Janet has a BS degree in chemistry from Boston College (1984). She has also completed a certificate program in food retailing management. Janet was hired as a sales associate in 1991 and promoted to sales representative in February 1992.

    Mrs. Margaret Peacock
    Sales Representative (USA) WA Redmond
    Ext: 5176
    DOB: 09/19/1937, Age: 86

    Notes:
    Margaret holds a BA in English literature from Concordia College (1958) and an MA from the American Institute of Culinary Arts (1966). She was assigned to the London office temporarily from July through November 1992.

    Mr. Steven Buchanan
    Sales Manager (UK) London
    Ext: 3453
    DOB: 03/04/1955, Age: 69

    Notes:
    Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree in 1976. Upon joining the company as a sales representative in 1992, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London. He was promoted to sales manager in March 1993. Mr. Buchanan has completed the courses "Successful Telemarketing" and "International Sales Management." He is fluent in French.

    Mr. Michael Suyama
    Sales Representative (UK) London
    Ext: 428
    DOB: 07/02/1963, Age: 61

    Notes:
    Michael is a graduate of Sussex University (MA, economics, 1983) and the University of California at Los Angeles (MBA, marketing, 1986). He has also taken the courses "Multi-Cultural Selling" and "Time Management for the Sales Professional." He is fluent in Japanese and can read and write French, Portuguese, and Spanish.

    Mr. Robert King
    Sales Representative (UK) London
    Ext: 465
    DOB: 05/29/1960, Age: 64

    Notes:
    Robert King served in the Peace Corps and traveled extensively before completing his degree in English at the University of Michigan in 1992, the year he joined the company. After completing a course entitled "Selling in Europe," he was transferred to the London office in March 1993.

    Ms. Laura Callahan
    Inside Sales Coordinator (USA) WA Seattle
    Ext: 2344
    DOB: 01/09/1958, Age: 66

    Notes:
    Laura received a BA in psychology from the University of Washington. She has also completed a course in business French. She reads and writes French.

    Ms. Anne Dodsworth
    Sales Representative (UK) London
    Ext: 452
    DOB: 01/27/1966, Age: 58

    Notes:
    Anne has a BA degree in English from St. Lawrence College. She is fluent in French and German.

  • SQL CODE
    DECLARE @br CHAR(6) = '<br />'

    SELECT TOP 20 rowstyle, datavalue FROM (
    SELECT E.EmployeeID, 20 AS rowstyle,
    E.TitleOfCourtesy + ' ' + E.FirstName
    + ' ' + E.LastName AS datavalue
    FROM dbo.Employees E
    UNION
    SELECT E.EmployeeID,
    10 AS rowstyle,
    E.Title + ' (' + E.Country + ') ' +
    + ' ' + COALESCE(E."Region", ' ')
    + ' ' + E."City"
    + @br + ' Ext: ' + E.Extension
    + @br + ' DOB: ' + CONVERT(nvarchar(10), E.BirthDate,101) + ',
    Age: ' + CAST((0+ FORMAT(GETDATE(),'yyyyMMdd') - FORMAT(E.BirthDate,'yyyyMMdd') ) /10000 AS VARCHAR(8))
    + @br + @br + 'Notes: ' + @br + CAST(E.Notes AS NVARCHAR(MAX)) + @br + @br
    AS datavalue
    FROM dbo.Employees E
    ) x
    ORDER BY x.EmployeeID, x.rowstyle DESC;
  • COMMENTS
    Comments for sql code;
Close
\