- Get all unique
ShipNames
from the Order table that contain a hyphen '-'
- Details: In addition, get all the characters preceding the (first) hyphen. Return ship names alphabetically. Your first row should look like
Bottom-Dollar Markets|Bottom
SELECT DISTINCT ShipName || '|' || SUBSTR(ShipName, 1, INSTR(ShipName, '-') -1)
FROM `Order`
WHERE ShipName LIKE '%-%'
ORDER BY ShipName;
调用函数:
-
INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;SUBSTR(str,pos,len): 从pos开始的位置,截取len个字符;![](https://i-blog.csdnimg.cn/blog_migrate/7c9ca3279b0bb52083b494be53965b0c.png)
-
2rd Problem Description
- Indicate if an order's
ShipCountry
is in North America. For our purposes, this is 'USA', 'Mexico', 'Canada'
- Details: You should print the Order
Id
, ShipCountry
, and another column that is either 'NorthAmerica'
or 'OtherPlace'
depending on the Ship Country. Order by the primary key ( Id
) ascending and return 20 rows starting from Order Id
15445
Your output should look like 15445|France|OtherPlace
or 15454|Canada|NorthAmerica
SELECT Id,
ShipCountry,
(CASE WHEN ShipCountry IN ('USA','Mexico','Canada') THEN 'NorthAmerica' ELSE 'OtherPlcae' END) as ShipRegion
FROM 'Order'
WHERE Id>=15445
ORDER BY ID
LIMIT 20;
![](https://i-blog.csdnimg.cn/blog_migrate/2eda74f5ac50df8bea7648f586f88a21.png)
-
3rd Problem Description
- For each
Shipper
, find the percentage of orders which are late - Details: An order is considered late if ShippedDate > RequiredDate . Print the following format, order by descending percentage, rounded to the nearest hundredths, like United Package|23.44
select a.companyname, round((select count(*) from 'order' o, shipper b where ShippedDate > RequiredDate and b.id== o.shipvia and b.companyname == a.companyname)*100.0/(select count(*) from 'order' o, shipper b where b.id== o.shipvia and b.companyname == a.companyname), 2) as percentage
from shipper a
order by percentage desc;
![](https://i-blog.csdnimg.cn/blog_migrate/15a7b907e529e85e889db908f674bac1.png)
-
4th Problem Description
- Compute some statistics about categories of products
- Details: Get the number of products, average unit price (rounded to 2 decimal places), minimum unit price, maximum unit price, and total units on order for categories containing greater than 10 products. Order by Category
Id
. Your output should look like Beverages|12|37.98|4.5|263.5|60
SELECT CategoryName || '|' ||
COUNT(*) || '|' ||
round(AVG(UnitPrice), 2) || '|' ||
MIN(UnitPrice) || '|' ||
MAX(UnitPrice) || '|' ||
SUM(UnitsOnOrder) AS Q4
FROM Product, Category
WHERE Product.CategoryId = Category.Id
GROUP BY CategoryId
HAVING COUNT(*) > 10
ORDER BY CategoryId;
![](https://i-blog.csdnimg.cn/blog_migrate/788fc4b8bb4fa646aa011391d4bc6250.png)
-
5th Problem Description
- For each of the 8 discontinued products in the database, which customer made the first ever order for the product? Output the customer's
CompanyName
and ContactName
- Details: Print the following format, order by
ProductName
alphabetically: Alice Mutton|Consolidated Holdings|Elizabeth Brown
select productname,companyname, contactname
from customer c, `order` o, orderdetail t, product p
where c.id == o.customerid and o.id == t.orderid and t.productid == p.id and p.discontinued == 1 and not exists(select o2.orderdate from customer c2, `order` o2, orderdetail t2, product p2 where c2.id == o2.customerid and o2.id == t2.orderid and t2.productid == p2.id and p2.discontinued == 1 and p.id==p2.id and o2.orderdate < o.orderdate)
order by productname;
![](https://i-blog.csdnimg.cn/blog_migrate/2cb9a620fce71ddce764103f6b5ba59c.png)
-
6th Problem Description
- For the first 10 orders by
CutomerId BLONP
: get the Order's Id
, OrderDate
, previous OrderDate
, and difference between the previous and current. Return results ordered by OrderDate
(ascending) - Details: The "previous"
OrderDate
for the first order should default to itself (lag time = 0). Use the julianday()
function for date arithmetic (example). Use lag(expr, offset, default) for grabbing previous dates. Please round the lag time to the nearest hundredth, formatted like 17361|2012-09- 19 12:13:21|2012-09-18 22:37:15|0.57
select Id,
OrderDate,
PrevOrderDate,
round(julianday(OrderDate) - julianday(PrevOrderDate),2)
from (
select Id,
OrderDate,
LAG(OrderDate,1,OrderDate)
OVER (order by OrderDate asc) as PrevOrderDate
from `Order`
where CustomerId = 'BLONP'
Order by OrderDate asc
LIMIT 10
);
![](https://i-blog.csdnimg.cn/blog_migrate/0b802b7ef3c1f57eb475d4cc667c0277.png)
-
7th Problem Description
- For each
Customer
, get the CompanyName, CustomerId, and "total expenditures". Output the bottom quartile of Customers, as measured by total expenditures. - Details: Calculate expenditure using
UnitPrice
and Quantity
(ignore Discount
). Compute the quartiles for each company's total expenditures using NTILE. The bottom quartile is the 1st quartile, order them by increasing expenditure. Make sure your output is formatted as follows (round expenditure to the nearest hundredths): Bon app|BONAP|4485708.49
SELECT *
FROM(
SELECT IFNULL(CompanyName, 'MISSING_NAME') AS CompanyName, CustomerId, ROUND(SUM(UnitPrice * Quantity) ,2) AS TotalExpenditures,
NTILE(4) OVER(ORDER BY CAST(ROUND(SUM(UnitPrice * Quantity) ,2) AS float)) AS ID
FROM `Order`
JOIN OrderDetail ON `Order`.Id = OrderDetail.OrderId
LEFT JOIN Customer ON `Order`.CustomerId = Customer.Id
GROUP BY CustomerId
ORDER BY TotalExpenditures
)
WHERE ID = 1
;
![](https://i-blog.csdnimg.cn/blog_migrate/9f8e56b98551324f595ac73ba5708d5a.png)
-
8th Problem Description
- Find the youngest employee serving each
Region
. If a Region is not served by an employee, ignore it. - Details: Print the
Region Description
, First Name
, Last Name
, and Birth Date
. Order by Region Id
SELECT RegionDescription || '|' || FirstName || '|' || LastName || '|' || MAX(BirthDate) AS Q9
FROM Employee EE,EmployeeTerritory ET, Territory T, Region R
WHERE EE.Id = ET.EmployeeId AND ET.TerritoryId = T.Id AND T.RegionId = R.Id
GROUP BY R.Id
ORDER BY R.Id;
![](https://i-blog.csdnimg.cn/blog_migrate/db8a76a4a9f18f990660cbf1dd136c07.png)
-
9th Problem Description
- Concatenate the
ProductName
s ordered by the Company 'Queen Cozinha'
on 2014-12-25
. - Order the products by Id (ascending). Print a single string containing all the dup names separated by commas like
Mishi Kobe Niku, NuNuCa Nuß-Nougat-Creme...
--non-recursive
WITH cte AS (
SELECT ProductName
FROM Product P, OrderDetail OD, `Order` O , Customer C
WHERE P.Id = OD.ProductId AND OD.OrderId = O.Id AND O.CustomerId = C.Id AND C.CompanyName = 'Queen Cozinha' AND OrderDate LIKE '2014-12-25%'
ORDER BY P.Id
)
SELECT GROUP_CONCAT(cte.ProductName, ',')
FROM cte;
--recursive
WITH RECURSIVE
init AS (
SELECT ProductName, RANK() over (ORDER BY P.Id) AS Rank, RANK() over (ORDER BY P.Id) + 1 AS Next
FROM Product P,
OrderDetail OD,
`Order` O,
Customer C
WHERE P.Id = OD.ProductId
AND OD.OrderId = O.Id
AND O.CustomerId = C.Id
AND C.CompanyName = 'Queen Cozinha'
AND OrderDate LIKE '2014-12-25%'
ORDER BY P.Id
),
concate(ProductName, Rank, Next) AS (
SELECT ProductName, Rank, Next
FROM init
WHERE RANK = 1
UNION ALL
SELECT concate.ProductName|| ', ' || init.ProductName , init.Rank, init.Next
FROM concate
JOIN init ON init.Rank = concate.Next
),
final(ProductName, Rank, Next) AS (
SELECT ProductName, Rank, MAX(Rank)
FROM concate
)
SELECT ProductName
FROM final;
![](https://i-blog.csdnimg.cn/blog_migrate/814ae2b1de93e1cf65506db515ac3784.png)