SCUDB HomeWork1

      1st Problem Description

• 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个字符；

• 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;

  

• 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;
  
  

  

• 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;

  

• 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;

  

• 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
  );

  

• 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
  ;

  

• 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;

  

• 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;