Skip to content
GitLab
Select Git revision
  • release-v1
  • 6800h
  • pru
  • nico
  • 9800z
  • 9800h
  • zywoo
  • lovestory
  • 6800z
  • 6800+
  • 6800++
  • 6800
  • moneys
  • recordlock6793
  • niko
  • riven
  • neon
  • god
  • jettnotl
  • ellenjoe
Find file BlameHistoryPermalink
aggtest.txt 3.03 KiB
1 
create table grade (course char(20),id int,score float);
2 
insert into grade values('DataStructure',1,95);
3 
insert into grade values('DataStructure',2,93.5);
4 
insert into grade values('DataStructure',4,87);
5 
insert into grade values('DataStructure',3,85);
6 
insert into grade values('DB',1,94);
7 
insert into grade values('DB',2,74.5);
8 
insert into grade values('DB',4,83);
9 
insert into grade values('DB',3,87);
10 
select sum(id),sum(score) from grade;
11 
select MAX(id) as max_id from grade;
12 
select MIN(score) as min_score from grade where course = 'DB';
13 
select COUNT(course) as course_num from grade;
14 
select COUNT(*) as row_num from grade;
15 
select SUM(score) as sum_score from grade where id = 1;
16 
select COUNT(score) as row_num,SUM(score) as sum_score from grade where course = 'DB';
17 
drop table grade;
18
19 
create table grade (course char(20),id int,score float);
20 
insert into grade values('DataStructure',1,95);
21 
insert into grade values('DataStructure',2,93.5);
22 
insert into grade values('DataStructure',3,94.5);
23 
insert into grade values('ComputerNetworks',1,99);
24 
insert into grade values('ComputerNetworks',2,88.5);
25 
insert into grade values('ComputerNetworks',3,92.5);
26 
insert into grade values('C++',1,92);
27 
insert into grade values('C++',2,89);
28 
insert into grade values('C++',3,89.5);
29 
select id,MAX(score) as max_score,MIN(score) as min_score,SUM(score) as sum_score from grade group by id;
30 
select id,MAX(score) as max_score from grade group by id having COUNT(*) >  3;
31 
insert into grade values ('ParallelCompute',1,100);
32 
select id,MAX(score) as max_score from grade group by id having COUNT(*) >  3;
33 
select id,MAX(score) as max_score,MIN(score) as min_score from grade group by id having COUNT(*) > 1 and MIN(score) > 88;
34 
select course ,COUNT(*) as row_num , COUNT(id) as student_num , MAX(score) as top_score, MIN(score) as lowest_score from grade group by course;
35 
select COUNT(*) as row_num, course  , COUNT(id) as student_num , MAX(score) as top_score, MIN(score) as lowest_score from grade group by course having COUNT(*) > 1 and MIN(score) > 88;
36 
insert into grade values('DataStructure',1,86.5);
37 
insert into grade values('DataStructure',1,99.2);
38 
insert into grade values('DataStructure',1,74.9);
39 
insert into grade values('ComputerNetworks',2,99);
40 
insert into grade values('ComputerNetworks',2,88.5);
41 
insert into grade values('ComputerNetworks',2,92.5);
42 
select id,MAX(score) as max_score from grade group by id,course having COUNT(*) >  2;
43 
select course,id,MAX(score) as max_score from grade group by id,course having COUNT(*) >  2;
44 
select course,id from grade group by id,course having MIN(score) >  80;
45
46
47 
drop table grade;
48
49 
create table grade (course char(20),id int,score float);
50 
insert into grade values('DataStructure',1,95);
51 
insert into grade values('DataStructure',2,93.5);
52 
insert into grade values('DataStructure',3,94.5);
53 
insert into grade values('ComputerNetworks',1,99);
54 
insert into grade values('ComputerNetworks',2,88.5);
55 
insert into grade values('ComputerNetworks',3,92.5);
56 
select id , score from grade group by course;
57 
select id, MAX(score) as max_score from grade where MAX(score) > 90 group by id;