- 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;
- 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;
- Write a query to display the orders according to the order number arranged by ascending order.
select * FROM orders ORDER BY ord_no;
- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
- 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;
- 题1-标准答案输出