Database samples | dba.mykontacts.com

COMPANIES

COMPANY CONTACTS

Alfreds Futterkiste

Maria Anders
Sales Representative
Berlin Germany 030-0074321

Ana Trujillo Emparedados y helados

Ana Trujillo
Owner
México D.F. Mexico (5) 555-4729

Antonio Moreno Taquería

Antonio Moreno
Owner
México D.F. Mexico (5) 555-3932

Around the Horn

Thomas Hardy
Sales Representative
London UK (171) 555-7788

Berglunds snabbköp

Christina Berglund
Order Administrator
Luleå Sweden 0921-12 34 65

Blauer See Delikatessen

Hanna Moos
Sales Representative
Mannheim Germany 0621-08460

Blondesddsl père et fils

Frédérique Citeaux
Marketing Manager
Strasbourg France 88.60.15.31

SQL CODE

SELECT customers."CustomerID", 20 AS rowstyle,
customers."CompanyName" AS datavalue
FROM public.customers
UNION
SELECT customers."CustomerID", 10 AS rowstyle,
customers."ContactName" || ' ' ||
customers."ContactTitle" || ' ' ||
customers."Phone" AS datavalue
FROM public.customers
FETCH FIRST 15 ROWS ONLY ;

COMMENTS

Display a random selection of companies.

PRODUCTS

PRODUCTS

Chai

$18
Specialty Biscuits, Ltd.(UK)

Chang

$19
Exotic Liquids(UK)

Aniseed Syrup

$10
Exotic Liquids(UK)

Chef Anton's Cajun Seasoning

$22
New Orleans Cajun Delights(USA)

Chef Anton's Gumbo Mix

$21.35
New Orleans Cajun Delights(USA)

Grandma's Boysenberry Spread

$25
Grandma Kelly's Homestead(USA)

Uncle Bob's Organic Dried Pears

$30
Grandma Kelly's Homestead(USA)

SQL CODE

SELECT P."ProductID", 20 AS rowstyle,
P."ProductName" AS datavalue
FROM products P
UNION
SELECT P."ProductID", 10 AS rowstyle,
'$' || P."UnitPrice" || ' ' ||
S."CompanyName" || '(' ||
S."Country" || ')' AS datavalue
FROM public.products P LEFT OUTER JOIN public.suppliers S
on P."SupplierID" = S."SupplierID"

COMMENTS

ORDERS

ORDERS

Vins et alcools Chevalier

Ordered: 1996-07-04
Shipped: 1996-07-16
Processing time: 12 days.
59 rue de l'Abbaye
Reims
France

Toms Spezialitäten

Ordered: 1996-07-05
Shipped: 1996-07-10
Processing time: 5 days.
Luisenstr. 48
Münster
Germany

Hanari Carnes

Ordered: 1996-07-08
Shipped: 1996-07-12
Processing time: 4 days.
Rua do Paço, 67
Rio de Janeiro
Brazil

Victuailles en stock

Ordered: 1996-07-08
Shipped: 1996-07-15
Processing time: 7 days.
2, rue du Commerce
Lyon
France

Suprêmes délices

Ordered: 1996-07-09
Shipped: 1996-07-11
Processing time: 2 days.
Boulevard Tirou, 255
Charleroi
Belgium

Hanari Carnes

Ordered: 1996-07-10
Shipped: 1996-07-16
Processing time: 6 days.
Rua do Paço, 67
Rio de Janeiro
Brazil

Chop-suey Chinese

Ordered: 1996-07-11
Shipped: 1996-07-23
Processing time: 12 days.
Hauptstr. 31
Bern
Switzerland

SQL CODE

SELECT orders."OrderID", 20 AS rowstyle,
orders."ShipName" AS datavalue
FROM public.orders
UNION
SELECT orders."OrderID", 10 AS rowstyle,
'Ordered: ' || orders."OrderDate" || ' ' ||
'Shipped: ' || orders."ShippedDate" || ' ' ||
'Processing time: ' ||
orders."ShippedDate" - orders."OrderDate"
|| ' days. ' ||
orders."ShipAddress" || ' ' ||
orders."ShipCity" || ' ' ||
orders."ShipCountry" AS datavalue
FROM public.orders

COMMENTS

SALES

SALES

Côte de Blaye (1998) - $67324.25

Thüringer Rostbratwurst (1998) - $33683.26

Raclette Courdavault (1998) - $26345.00

Camembert Pierrot (1998) - $17295.12

Tarte au sucre (1998) - $15992.92

Uncle Bob's Organic Dried Pears (1998) - $12306.00

Manjimup Dried Apples (1998) - $11090.25

Wimmers gute Semmelknödel (1998) - $10696.19

Ikura (1998) - $9002.40

Sir Rodney's Marmalade (1998) - $8704.26

Carnarvon Tigers (1998) - $8496.87

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.87

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.16

SQL CODE

SELECT row_number() over () AS "ProductID",
20 AS rowstyle,
P."ProductName" || ' (1998) - $' ||
round(SUM (OD."Quantity" * (1-OD."Discount") * OD."UnitPrice"::decimal)::numeric,2)
as datavalue
FROM public.order_details AS OD, Orders O, Products P, Categories C
WHERE OD."OrderID" = O."OrderID"
and OD."ProductID" = P."ProductID"
and P."CategoryID" = C."CategoryID"
and to_char(O."OrderDate",'YYYY') = '1998'
GROUP BY P."ProductName"
ORDER BY SUM (OD."Quantity" * (1-OD."Discount") * OD."UnitPrice"::decimal) DESC

COMMENTS

EMPLOYEES

EMPLOYEES

Ms. Nancy Davolio

Sales Representative (USA) WA Seattle
Ext: 5467
DOB: 1948-12-08, Age: 76 years

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: 1952-02-19, Age: 73 years

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: 1963-08-30, Age: 61 years

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: 1937-09-19, Age: 87 years

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: 1955-03-04, Age: 70 years

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: 1963-07-02, Age: 61 years

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.

SQL CODE

SELECT E."EmployeeID" AS "ProductID",
20 AS rowstyle,
E."TitleOfCourtesy" || ' ' || E."FirstName"
|| ' ' || E."LastName" AS datavalue
FROM public.Employees E
UNION
SELECT E."EmployeeID" AS "ProductID",
10 AS rowstyle,
E."Title" || ' (' || E."Country" || ')'
|| ' ' || COALESCE(E."Region", '')
|| ' ' || E."City"
|| ' Ext: ' || E."Extension"
|| ' DOB: ' || E."BirthDate" || ', Age: ' ||
EXTRACT(YEAR FROM AGE(CAST(E."BirthDate" AS date)))
|| ' Notes: ' || E."Notes" || ' '
as datavalue
FROM public.Employees E

COMMENTS

Database Server Demo

Northwind Traders - POSTGRESQL

COMPANIES

PRODUCTS

ORDERS

SALES

EMPLOYEES