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
