SQL-Formatting Output

1. Write a SQL statement to display the commission with the percent sign ( % ) with salesman ID, name and city columns for all the salesmen.

• 疑问：题目的意思应该是，输出数据时将commission的值转化为百分比值
  网站给出的标准答案并没有实现此功能
  ------ 标准答案
  SELECT salesman_id,name,city,’%’,commission*100
  FROM salesman;

  SELECT name, city, salesman_id, CONCAT(100*commission, '%') as commission
  FROM salesman;
  

2. Write a SQL statement to find out the number of orders booked for each day and display it in such a format like “For 2001-10-10 there are 15 orders”.
   问题同1
   ------ 标准答案
   SELECT ’ For’,ord_date,’,there are’,
   COUNT (DISTINCT ord_no),‘orders.’
   FROM orders
   GROUP BY ord_date;

   SELECT CONCAT('For ', ord_date, ' there are ', COUNT(*), ' orders')
   FROM orders
   GROUP BY ord_date；
   

3. Write a query to display the orders according to the order number arranged by ascending order.

   select *
   FROM orders
   ORDER BY ord_no;
   

4. Write a SQL statement to arrange the orders according to the order date in such a manner that the latest date will come first then previous dates.

   SELECT *
   FROM orders
   ORDER BY ord_date DESC;
   

5. Write a SQL statement to display the orders with all information in such a manner that, the older order date will come first and the highest purchase amount of same day will come first.

   SELECT *
   FROM orders
   ORDER BY ord_no, purch_amt DESC;
   

6. Write a SQL statement to display the customer name, city, and grade, etc. and the display will be arranged according to the smallest customer ID.

   SELECT *
   FROM customer
   ORDER BY customer_id;
   

7. Write a SQL statement to make a report with salesman ID, order date and highest purchase amount in such an arrangement that, the smallest salesman ID will come first along with their smallest order date.

   SELECT salesman_id, ord_date, MAX(purch_amt)
   FROM orders
   GROUP BY salesman_id, ord_date
   ORDER BY salesman_id, ord_date;
   

8. Write a SQL statement to display customer name, city and grade in such a manner that, the customer holding highest grade will come first.

   SELECT cust_name, city, grade
   FROM customer
   ORDER BY grade DESC;
   

9. Write a SQL statement to make a report with customer ID in such a manner that, the largest number of orders booked by the customer will come first along with their highest purchase amount.

   SELECT customer_id, COUNT(DISTINCT ord_no), MAX(purch_amt) 
   FROM orders 
   GROUP BY customer_id 
   ORDER BY COUNT(DISTINCT ord_no) DESC;
   

10. Write a SQL statement to make a report with order date in such a manner that, the latest order date will come last along with the total purchase amount and total commission (15% for all salesmen) for that date.

    SELECT ord_date, SUM(purch_amt), 0.15*SUM(purch_amt)
    FROM orders
    GROUP BY ord_date
    ORDER BY ord_date;
    

来源：w3resource

• 题1-标准答案输出