Scribd
Upload
209 views6 pages

NorthWind MySQL Query Answers

Uploaded by

Daniel Pesantez
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
209 views6 pages

NorthWind MySQL Query Answers

Uploaded by

Daniel Pesantez
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
  • NorthWind Database Practice Queries

Curated by Musili Adebayo

Connect with me:


Musili Adebayo
Musili_Adebayo
Answers to 50 NorthWind Database Practice Queries using
c
MySQL
NB: Please note that the default order for sorting queries/column in SQL is ASC when using the ORDER BY clause.

CREATE DATABASE IF NOT EXISTS northwind;

USE northwind;

1. SELECT CategoryName, Description


FROM [Link]
ORDER BY CategoryName;

2. SELECT ContactName, CompanyName, ContactTitle, Phone


From [Link]
ORDER BY Phone;

3. SELECT UPPER(FirstName) AS FirstName, UPPER( LastName) AS LastName, HireDate


FROM [Link]
ORDER BY HireDate;

4. SELECT OrderID, OrderDate, ShippedDate, CustomerID, Freight


FROM [Link]
ORDER BY Freight Desc
LIMIT 10;

5. SELECT lower(CustomerID) AS ID
FROM [Link];

6. SELECT CompanyName, Fax, Phone,Country, HomePage


FROM [Link]
ORDER BY Country DESC, CompanyName ;

7. SELECT CompanyName,ContactName
FROM [Link]
WHERE City = 'Buenos Aires';

8. SELECT "ProductName", "UnitPrice", QuantityPerUnit"


FROM "Products"
WHERE "UnitsInStock" = 0

9. SELECT ContactName, Address, City


FROM [Link]
WHERE Country NOT IN ("Germany","Mexico", "Spain");

10. SELECT OrderDate, ShippedDate, CustomerID, Freight


FROM [Link]
WHERE OrderDate = '1996-05-21 ';

11. SELECT FirstName,LastName,Country


FROM [Link]
WHERE Country <> 'USA';

Curated by Musili Adebayo


Connect with me:
If you find this resources useful please follow me on my socials and share with others.
Musili Adebayo
Musili_Adebayo
12. SELECT EmployeeID,OrderID,CustomerID,RequiredDate,ShippedDate
FROM [Link]
WHERE ShippedDate > RequiredDate;

13. SELECT City,CompanyName,ContactName


FROM [Link]
WHERE City LIKE "A%" OR City LIKE "B%";

14. SELECT OrderID


FROM [Link]
WHERE mod(OrderID,2)=0;

15. SELECT *
FROM [Link]
WHERE Freight > 500;

16. SELECT ProductName, UnitsInStock,UnitsOnOrder,ReorderLevel


FROM [Link]
WHERE ReorderLevel = 0;

17. SELECT CompanyName,ContactName,Fax


FROM [Link]
WHERE Fax IS NULL;

18. SELECT FirstName, LastName


FROM [Link]
WHERE ReportsTo IS NULL;

19. SELECT OrderID


FROM [Link]
WHERE mod(OrderID,2)=1;

20. SELECT CompanyName,ContactName,Fax


FROM [Link]
WHERE Fax IS NULL
ORDER BY ContactName;

21. SELECT City,CompanyName,ContactName,city


FROM [Link]
WHERE City LIKE "%L%"
ORDER BY ContactName;

22. SELECT FirstName, LastName,BirthDate


FROM [Link]
where BirthDate >= '1950-01-01'
AND BirthDate < '1960-01-01';

OR

Curated by Musili Adebayo


Connect with me:
Musili Adebayo
If you find this resources useful please follow me on my socials and share with others.
Musili_Adebayo
SELECT FirstName, LastName,BirthDate
FROM [Link]
where BirthDate Between '1950-01-01'
AND '1959-12-31';

23. SELECT LastName, FirstName, extract(year from Birthdate) AS BirthYear


FROM [Link];

24. SELECT OrderID, count(OrderID) as NumberofOrders


FROM northwind.`order details`
GROUP BY OrderID
ORDER BY NumberofOrders DESC ;

25. SELECT [Link], [Link], [Link]


FROM [Link] s
JOIN [Link] p
ON [Link] = [Link]
WHERE [Link] IN ('Exotic Liquids','Specialty Biscuits, Ltd.','Escargots Nouveaux')
ORDER BY [Link];

26. SELECT ShipPostalCode, OrderID, OrderDate, RequiredDate, ShippedDate,ShipAddress


FROM [Link]
WHERE ShipPostalCode = '98124';

27. SELECT ContactName, ContactTitle, CompanyName


FROM [Link]
WHERE ContactTitle NOT LIKE "%Sales%";

28. SELECT LastName, FirstName, City


FROM [Link]
WHERE City != "Seattle";

29. SELECT CompanyName, ContactTitle, City, Country


FROM [Link]
WHERE Country IN ("Mexico","Spain")
AND City <> "Madrid";

30. SELECT CONCAT( FirstName,' ', LastName ,' can be reached at ', 'x',Extension ) AS Contactinfo
FROM [Link];

31. SELECT ContactName


FROM [Link]
where ContactName NOT like "_A%";

32. SELECT round (avg (UnitPrice),0) AS AveragePrice,


SUM(UnitsInStock) AS TotalStock,
max(UnitsOnOrder) as MaxOrder
FROM [Link];

Curated by Musili Adebayo


Connect with me:
Musili Adebayo
If you find this resources useful please follow me on my socials and share with others. Musili_Adebayo
33. SELECT [Link], [Link], [Link], [Link], [Link]
FROM [Link] p
JOIN [Link] s
ON [Link] = [Link]
JOIN [Link] C
On [Link] = [Link];

34. SELECT CustomerID, sum(Freight)


FROM [Link]
GROUP BY CustomerID
HAVING sum(Freight) > "200";

35. SELECT [Link], [Link],[Link],[Link],[Link]


FROM northwind.`order details` od
JOIN [Link] o
ON [Link] = [Link]
JOIN [Link] c
ON [Link] = [Link]
WHERE [Link] != '0';

36. SELECT [Link],


CONCAT ([Link], " " ,[Link] )as employee,
CONCAT ([Link]," " , [Link] ) as manager
FROM [Link] a
LEFT JOIN [Link] b
ON [Link] = [Link]
ORDER BY [Link];

37. SELECT avg(UnitPrice) AS AveragePrice,


min(UnitPrice)AS MinimumPrice,
max(UnitPrice)AS MaximumPrice
from [Link];

38. CREATE VIEW CustomerInfo AS


SELECT [Link], [Link], [Link], [Link], [Link],
[Link],[Link],[Link],[Link], [Link], [Link]
FROM
[Link] c
JOIN
[Link] o
ON [Link] = [Link];

39. RENAME TABLE customerinfo TO CustomerDetails;

Curated by Musili Adebayo


Connect with me:
Musili Adebayo
If you find this resources useful please follow me on my socials and share with others. Musili_Adebayo
40. CREATE VIEW ProductDetails AS
SELECT
[Link],[Link],
[Link],[Link], [Link],
[Link], [Link], [Link], [Link],
[Link], [Link]
FROM [Link] s
JOIN [Link] p ON [Link] = [Link]
JOIN [Link] c
ON [Link] = [Link];

41. DROP VIEW IF EXISTS customerdetails;

42. SELECT substring(CategoryName,1,5) as Short_info


FROM [Link];

43. DROP table IF exists shippers_dup;


CREATE TABLE shippers_dup (LIKE [Link]);
INSERT INTO shippers_dup SELECT * FROM [Link];

44. ALTER TABLE shippers_dup


ADD column Email VARCHAR(50);

UPDATE northwind.shippers_dup
SET Email ='speedyexpress@[Link]'
WHERE ShipperID = '1';

UPDATE northwind.shippers_dup
SET Email ='unitedpackage@[Link]'
WHERE ShipperID = '2';

UPDATE northwind.shippers_dup
SET Email ='federalshipping@[Link]'
WHERE ShipperID = '3';

45. SELECT [Link],[Link]


FROM [Link] c
JOIN [Link] p
ON [Link] = [Link]
JOIN [Link] s
ON [Link] = [Link]
WHERE CategoryName = "Seafood";

46. SELECT [Link], [Link], [Link]


FROM [Link] c
JOIN [Link] p
ON [Link] = [Link]
JOIN [Link] s
ON [Link] = [Link]
WHERE [Link] = "5";

Curated by Musili Adebayo


Connect with me:
If you find this resources useful please follow me on my socials and share with others. Musili Adebayo
Musili_Adebayo
47. DROP table IF exists shippers_dup;

48. SELECT LastName, FirstName, Title,DATE_FORMAT(FROM_DAYS(DATEDIFF(CURRENT_DATE, BirthDate)),"%y Years ")


AS Age from [Link];

49. SELECT [Link], count([Link]) AS NumberofOrders


FROM [Link] c
JOIN [Link] o
ON [Link] = [Link]
WHERE [Link] >= '1994-12-31'
GROUP BY [Link]
having count([Link]) > 10;

50. SELECT CONCAT( ProductName,' ', "weighs/is" ," ", QuantityPerUnit, " ", "and cost ","$",ROUND(UnitPrice) ) AS
ProductInfo
FROM [Link];

Curated by Musili Adebayo


Connect with me:
Musili Adebayo
If you find this resources useful please follow me on my socials and share with others.
Musili_Adebayo

Common questions

Powered by AI

To transform employee names to uppercase before retrieval, the UPPER() function is used, as shown in the query "SELECT UPPER(FirstName) AS FirstName, UPPER(LastName) AS LastName" within the NorthWind queries .

The constraint used to find products currently out of stock is "UnitsInStock = 0" in the WHERE clause of the query .

The query selects orders from the 'northwind.orders' table where the ShippedDate is greater than the RequiredDate, indicating a delay in shipment .

The average product price is calculated using the AVG() function on the UnitPrice column. Alongside this, the SUM() function is used to calculate the total stock, and the MAX() function identifies the maximum units on order .

To find customers whose ContactName does not begin with 'A', the query uses "ContactName NOT LIKE '_A%'" in the WHERE clause, which excludes any ContactName starting with 'A' .

A LEFT JOIN is performed on the 'employees' table to pair employees with their managers. The result is a concatenation of employee names with their manager's names, formatted as "CONCAT(a.LastName, ' ', a.FirstName) as employee, CONCAT(b.LastName, ' ', b.FirstName) as manager" .

The ORDER BY clause in SQL sorts the query results by the specified column. The default order for sorting when using ORDER BY is ascending (ASC).

The ROUND() function is used to round numbers in the NorthWind database. It is implemented in queries like SELECT ROUND(UnitPrice) to provide a rounded product price .

Date functions like DATEDIFF and FROM_DAYS are used to calculate the age of employees. The result is formatted using DATE_FORMAT as "DATE_FORMAT(FROM_DAYS(DATEDIFF(CURRENT_DATE, BirthDate)), '%y Years') AS Age," which outputs the age in years .

When the 'products' table is joined with the 'suppliers' table in the NorthWind database, the query retrieves SupplierID, ProductName, and CompanyName from the joined tables. The information is filtered by checking if the CompanyName is in the list ('Exotic Liquids','Specialty Biscuits, Ltd.','Escargots Nouveaux').

Answers to 50 NorthWind Database Practice Queries using MySQL NB: Please note that the default order for sorting queries/c
12. SELECT EmployeeID,OrderID,CustomerID,RequiredDate,ShippedDate FROM northwind.orders WHERE ShippedDate > RequiredDate;
SELECT FirstName, LastName,BirthDate FROM northwind.employees where BirthDate Between '1950-01-01' AND '1959-12-31';
33. SELECT s.SupplierID, s.CompanyName, c.CategoryName, p.ProductName, p.UnitPrice FROM northwind.products p JOIN northwind
40. CREATE VIEW ProductDetails AS SELECT p.ProductID,S.CompanyName, p.ProductName,c.CategoryName, c.Description, p.Quanti
47. DROP table IF exists shippers_dup; 48. SELECT LastName, FirstName, Title,DATE_FORMAT(FROM_DAYS(DATEDIFF(CURRENT_DATE,

You might also like