from class; 12、查询学过“张三”老师2门课以上的同学的学号、姓名； select sid,sname from student where sid in( select student_id from score where course_id in( select cid from teacher,course where teacher.tname = '张三' and teacher.tid = course.teacher_id) group by student_id having count(student_id)>=2); 13、查询教授课程超过2门的老师的id和姓名 select tid,tname from teacher where tid in( select teacher_id from course group by teacher_id having count(teacher_id) >2); 14、查询学过编号“1”课程和编号“2”课程的同学的学号、姓名； select sid,sname from student where sid in( select student_id from score where student_id = 1 or student_id = 2); 15、查询没有带过高年级的老师id和姓名； select tid,tname from teacher where tid not in( select tid from teach2cls where cid in( select t1.cid from( select class.cid,class.caption,class_grade.gname, case when class_grade.gid between 1 and 2 then '低年级' when class_grade.gid between 3 and 4 then '中年级' when class_grade.gid between 5 and 6 then '高年级' else 0 end as grade_layer from class,class_grade where class.grade_id=class_grade.gid)as t1 where t1.grade_layer='高'));
engine=MergeTree()
partition by weight
order by tuple()""")
client.execute("""INSERT INTO tag_sums
SELECT * FROM tags
JOIN (SELECT mbid, sum(weight) FROM tags WHERE weight > 10 GROUP BY mbid) USING (mbid)""")
client.execute("ALTER TABLE tag_sums ADD COLUMN rel_weight Float32")
client.execute("ALTER TABLE tag_sums UPDATE rel_weight = divide(weight,ttl_weight) WHERE weight > 0")
## can play with parameters to get nice number of tags
select count(*) from (
select tag, count(tag) from tags where weight > 10 group by tag having count(tag) > 30 order by count(tag) desc
select count(*) from (
select tag, count(tag) from tag_sums where rel_weight > 0.05 group by tag having count(tag) > 30)
# * investigating problems with NULL byte
# c2 = 25024374
# c = 24374
# exactly 25m in db atm
# sed -n '25024372p;25024376p' < ttl_tags.csv
# awk 'NR >= 25024372 && NR <= 25024376'
# 28117686-25000000
on update cascade, foreign key(cid) references class(cid) on delete cascade on update cascade ); 插入数据 insert into teach2cls(tid,cid) values (1,1), (1,2), (2,1), (3,2); 1、自行创建测试数据； 2、查询学生总人数； select count(sid) as student_sum from student; #聚集函数 count(*) 3、查询“生物”课程和“物理”课程成绩都及格的学生id和姓名； select sid,sname from student where sid in(select student_id from score where score >=60 and (course_id =1 or course_id =3)); #多表子查询 4、查询每个年级的班级数，取出班级数最多的前三个年级； select caption,count(*) from class group by grade_id DESC limit 3; #分组 降序 5、查询平均成绩最高和最低的学生的id和姓名以及平均成绩；
# -*- coding: utf-8 -*- # @Time : 2017/9/8 12:51 # @Author : Aries # @Site : # @File : mysql 查询作业.py # @Software: PyCharm # 1、查询所有的课程的名称以及对应的任课老师姓名 select c.cname as "课程名",t.tname as "教师名" from course c,teacher t where c.teacher_id=t.tid; # 2、查询学生表中男女生各有多少人 select gender as "性别",count(1) as "数量" from student group by gender; # 3、查询物理成绩等于100的学生的姓名 select * from student where sid in (select student_id from score where num=100 and course_id=(select cid from course where cname="物理")); # 4、查询平均成绩大于八十分的同学的姓名和平均成绩 SELECT sname 姓名,平均成绩 from student RIGHT JOIN (SELECT student_id,avg(num) 平均成绩 from score GROUP BY student_id HAVING avg(num)>80) as A on student.sid=A.student_id; # 5、查询所有学生的学号，姓名，选课数，总成绩 SELECT student_id 学号,sname 姓名,COUNT(course_id) 课程数,SUM(num) 总分 FROM score LEFT JOIN student ON score.student_id=student.sid GROUP BY student_id; # 6、 查询姓李老师的个数
and cpc_entry_id in ('1', '3')
) t1
where charge_info_map['expids'] is not null
and charge_info_map['slot'] in ('1','3')-- and get_json_object(charge_info_map['expids'], '$.CTR') in ('601', '602') )
group by case when get_json_object(charge_info_map['expids'], '$.OCPC_CALB_CVR') is null then '-1'
else get_json_object(charge_info_map['expids'], '$.OCPC_CALB_CVR')
END
) order_log
on pv_log.cvr_pv_exp_id = order_log.cvr_order_exp_id
left join (
select case when get_json_object(expids, '$.OCPC_CALB_CVR') is null then '-1'
else get_json_object(expids, '$.OCPC_CALB_CVR')
END as cvr_click_exp_id,
count(1) as click_num,
sum(price)/100 as price,
sum(final_charge)/100 as final_charge,
sum(bid)/100 as bid
from log.dpods_baymax_wm_reporter_service_cpvlog
where dt BETWEEN '20180427' and '$$yesterday_compact'
and act=2
and isvalid='PASS'
and FROM_UNIXTIME(cast (SUBSTR(log_time,1,10) as bigint),'yyyyMMdd')=dt
and log_file_name='baymax-reporter-charge-log'
and slot in ('1', '3')
group by case when get_json_object(expids, '$.OCPC_CALB_CVR') is null then '-1'
else get_json_object(expids, '$.OCPC_CALB_CVR')
END
) click_log
on pv_log.cvr_pv_exp_id = click_log.cvr_click_exp_id
Write an SQL query to find the customer_number for the customer who has placed the largest number of orders.
The test cases are generated so that exactly one customer will have placed more orders than any other customer.
The query result format is in the following example.
'''
# Write your MySQL query statement below
select customer_number from (
select customer_number, count(order_number) as total from orders
group by customer_number
order by total desc ) k limit 1
#solution for the follow-up
select customer_number
from orders
group by customer_number
having count(order_number) = (select count(*) as count
from orders
group by customer_number
order by count desc limit 1);
# If all elements can be made equal, return False, otherwise return True.
# Example:arr[ ] = [128, 4, 2]
# 128, 4*2*2*2*2*2 = 128, 2*2*2*2*2*2*2 = 128
# -- True
# arr[ ] = [65, 4, 2]
# -- False
### Answer
Find prime factoriztion of that number and check if the values are only 2 or 7
https://paulrohan.medium.com/prime-factorization-of-a-number-in-python-and-why-we-check-upto-the-square-root-of-the-number-111de56541f
### Question 9 - Time for a response on a messaging application
# self join such that A.receiver_id=B.sender_id AND B.receiver_id=A.sender_id AND A.timestamp<=B.timestamp
# Extract B.timestamp-A.timestamp to check if it is less than 5 mins
### Question 10 - Calculating student attendance using SQL
select grade_level, sum(PresentStatus)/count(PresentStatus) as OverallAttendanceRate
from (select date, grade_level, (case when attendance_status IN ('present', 'tardy') then 1 else 0 end) as PresentStatus
from student_attendance_log as A LEFT JOIN student_demographic as B ON A.student_id=B.student_id
group by date, grade_level
HAVING date='2018-03-12') as P
### Question 12 - Finding the value closest to 0
# You are given a list of Q 1D points, return the value in Q that is the closest to value j.
# Q = [1, -1, -5, 2, 4, -2, 1] ; j = 3
# We would return two values in this case: 2, 4
import numpy as np
Q = np.sort(Q,axis=None)
Diff_array = []
for i in range(0,len(Q)):
Diff_array.append(np.absolute(Q[i]-j))
min_Diff_array = np.min(Diff_array)
last_day/92 as lastday,
case
when n_buy=0 or b.num is null then 0
when n_buy!=0 then b.num/n_buy
end as repeatbuy,
case
when week.num is null then 0
when week.num is not null then week.num
end as weekbuy,
case
when month.num is null then 0
when month.num is not null then month.num
end as monthbuy
from yi_train_brand
left outer join
(select brand_id,count(user_id) as num
from
(select brand_id,
user_id,
count(distinct visit_datetime) as num
from yi_train_set
where type=1
group by brand_id,user_id)a
where a.num>1
group by brand_id)b
on b.brand_id=yi_train_brand.brand_id
left outer join
(select distinct brand_id,1 as num
from yi_train_set
where type=1 and visit_datetime>'07-08')week
on week.brand_id=yi_train_brand.brand_id