sql语句 题目

1. 查看customer数据结构
   DESC customer;
   

2. 只要前十行
   SELECT * FROM customer LIMIT 10;

3. 每位顾客购物总量 GROUP BY
   SELECT customer_id,SUM(amount)
   FROM Customer GROUP BY customer_id;

4. WHERE IN
   SELECT CustomerID,Quantity,Country FROM da-course.clv WHERE Country IN (‘Hongkong’,‘Canada’);

5. WHERE OR
   SELECT CustomerID,Quantity,Country FROM da-course.clv WHERE Country = ‘Hongkong’ OR Country=‘Canada’;

6. CONCAT 合并项 ORDER BY排序
   SELECT CONCAT(lastname ，‘，’，firstname）FROM Table ORDER BY lastname DESC;

7. HAVING聚合
   SELECT customer_id,sum(amount) AS ‘total_amount’
   FROM payment
   GROUP BY customer_id
   HAVING SUM(amount) BETWEEN 20 AND 200;

8.LIKE 以M姓氏开头,购买数量在20-200的结果
SELECT customer_id,sum(amount) AS ‘total_amount’
FROM payment
WHERE customer_id in (SELECT customer_id FROM customer WHERE last_name LIKE ‘M%’)
GROUP BY customer_id
HAVING SUM(amount) BETWEEN 20 AND 200;

8. BETWEEN AND HAVING场景三
SELECT customer_id,sum(amount) FROM payment
WHERE payment_date BETWEEN ‘2005-5-30’ AND ‘2005-8-15’ AND customer_id BETWEEN 15 AND 30
GROUP BY customer_id
HAVING sum(amount) >50 ;

9. substring 截取字符串
   SELECT Name, HireDate FROM tblEmployees WHERE substring(HireDate,1,4)=2009;
   

10.场景二：SELECT customer_id,sum(amount) AS ‘total_amount’,round((datediff(current_date,payment_date))/30,0) AS ‘Month_till_now’
FROM payment
WHERE rental_id <9999 AND rental_id IS NOT NULL and customer_id NOT IN (30,90) AND customer_id NOT LIKE ‘5%’
GROUP BY customer_id,Month_till_now
ORDER BY sum(amount)DESC,
customer_id;

11.查询姓氏以字母G开头员工的姓名,出生年份和性别
SELECT YEAR(birth_date),concat(last_name,first_name) ‘Employee name’,gender
FROM employees WHERE last_name LIKE ‘g%’;

12.查询姓氏以B字母开头员工的姓名,开始工作的年纪,并且重命名为 Start towork Age,性别,并且把性别重命名为;男性和女性,按照员工的岁数进行倒序
SELECT
concat(first_name,’ ',last_name) ‘Employee Name’,
ROUND(datediff(hire_date,birth_date)/365) AS ‘AGE of finfing Job’,
CASE WHEN gender =‘M’ THEN ‘男性’
WHEN gender =‘F’ THEN ‘女性’
END AS ‘性别’
FROM employees
WHERE last_name LIKE ‘B%’
ORDER BY ROUND(datediff(hire_date,birth_date)/365) DESC;

13.
SELECT MAX(amount),MIN(amount),AVG(amount),SUM(amount) FROM payment;
14.
SELECT customer_id,MAX(amount),MIN(amount),AVG(amount),SUM(amount) FROM payment
WHERE payment_date BETWEEN ‘2005-05-15’ AND ‘2005-07-15’
GROUP BY customer_id;
15.
SELECT customer_id,SUM(amount) ,rental_id FROM payment
WHERE rental_id IN (100,105,112,130)
GROUP BY customer_id,rental_id;
16.
SELECT customer_id,max(amount)-min(amount) ‘diff’ FROM payment
WHERE EXTRACT(MONTH FROM payment_date)=‘06’
GROUP BY customer_id;
17.
SELECT customer_id,MIN(amount) FROM payment
GROUP BY customer_id HAVING MIN(amount)>=0.99 and customer_id>50;

18.
SELECT COUNT(rental_id) FROM rental;
19.
SELECT LPAD(EXTRACT(YEAR_MONTH FROM rental_date),7,’*’) ‘DATE’,COUNT(rental_id) ‘COUNTS’ FROM rental
GROUP BY DATE;
20.
SELECT customer_id,COUNT(rental_id) FROM rental GROUP BY customer_id;
21.请计算每一种物品有多少个客户租赁。
SELECT rental_id,COUNT(customer_id) FROM rental GROUP BY rental_id;