Super Classic SQL exercises, implemented on Teradata

Topic Source: 63681089

Teradata Implementations:

drop table Student;create table student (s_id varchar), sname varchar, sage date,sex varchar); INSERT INTO Student values (' 01 ', ' Lei ', ' 1990-01-01 ', ' Male '), insert into Student values (' 02 ', ' Money electricity ', ' 1990-12-21 ', ' Male '); INSERT INTO S Tudent values (' 03 ', ' Sun Feng ', ' 1990-05-20 ', ' Male '); insert into Student values (' 04 ', ' Levin ', ' 1990-08-06 ', ' Male '); INSERT INTO S Tudent values (' 05 ', ' Zhou Mei ', ' 1991-12-01 ', ' female '), insert into Student values (' 06 ', ' Wu Lan ', ' 1992-03-01 ', ' female '); INSERT INTO S  Tudent values (' 07 ', ' Qingshou ', ' 1989-07-01 ', ' female '), insert into Student values (' 08 ', ' Wang Ju ', ' 1990-01-20 ', ' Female '); select * FROM Student;create Table Course (c_id varchar), CNAME varchar (t_id varchar), insert into course values (' 01 ', ' Language ', INSERT into Course values (' 02 ', ' Math ', ' n '), insert into Course values (' 03 ', ' English ', ' n '); select * FROM Course;cre Ate table teacher (t_id varchar), tname varchar), insert into teacher values (' 01 ', ' Zhang San '); INSERT into teacher values (' 02 ', ' John Doe '); INSERT INTO TeacherVALUES (' 03 ', ' Harry '); select * FROM Teacher;create table SC (s_id varchar), c_id varchar, score decimal (18,1)); Insert I Nto SC values (' n ', ' on ', ' + '), insert into SC values (' n ', ' ", ' + '), insert into SC values (' n ', '", '); Insert int o SC values (' Up ', ' n ', ' + '), insert into SC values (' in ', ' ' "') ', ' INSERT into SC values '  SC values (' Up ', ' n ', ' + '), insert into SC values (' n ', ' ', '), insert into SC values (' n ', ' ", '); INSERT INTO SC VALUES (' n ', ', ', ', '), insert into SC values (' n ', ' ", ' + '), insert into SC values (' n ', '", '), insert into SC V Alues (' On ', ' n ', ' + '); INSERT into SC values (' ", '" ","), insert into SC values (' ', ' n ', '); INSERT into SC Val UEs (' n ', ' a ', '); INSERT into SC values (' ' ', ' ' n ', ', '), insert into SC values (' ", ' 98 '); select * FROM SC Ord  Er by s_id, c_id;/*1*/select T1.s_id,t1.c_id,t1.score,t2.c_id,t2.score from SC t1 INNER join SC t2on t1.s_id=t2.s_id and T1.c_id= ' t2.c_id= ' and' T1.score>t2.score;select ' and a.s_id,a.c_id,b.s_id,b.c_id from (select *from sc where c_id= ' ") Aleft join (SELECT * FROM SC where c_id= ' ") bon a.s_id=b.s_idwhere a.score >b.score;/*1.1*/select T1.s_id,t1.c_id,t2.s_id,t2.c_idfrom  SC t1 INNER join SC t2on t1.s_id=t2.s_id and t1.c_id= ' ' and t2.c_id= ' "," Select *from (select *from sc where c_id= ' ') a  Left JOIN (SELECT * from SC where c_id= ' Bon ") a.s_id=b.s_id where b.s_id is not null;/*1.2*/select *from (select *from SC where c_id= ' n ') a LEFT join (SELECT * from SC where c_id= ' ") bon a.s_id=b.s_id;/*1.3*/select * from SC where c_id= ' s_id ' and ' Not in ' (select s_id from SC where c_id= ' ");/*2*/select A.s_id,b.sname,avg (A.score) from SC a LEFT join Studen T Bon a.s_id=b.s_id Group by a.s_id,b.snamehaving avg (a.score) >=60;/*3*/select * from student where s_id in (select S_i D from SC GROUP by s_id)/*4*/select A.s_id,a.sname,count (b.s_id), sum (b.score) from student a LEFT join SC Bon a.s_id=b.s_id Group by a.s_id, A.sname;/*4.1*/select a.s_id,b.s_id, A.countclass,a.totlescorefrom (select S_id,count (s_id) countclass,sum (score) Totlescore From SC Group by s_id) Aleft join student bon a.s_id=b.s_id/*5*/Select COUNT (*) from teacher where tname like ' li% '; /*6*/select *from Student A LEFT join SC bon a.s_id=b.s_idleft join course con b.c_id= c.c_idleft join teacher Don C.t_i D =d.t_idwhere D.tname= ' Zhang San ';/*7*/select a.s_id,a.sname,a.sage,a.sex from student a LEFT join SC b on a.s_id =b.s_id havin G Count (b.s_id) <3group by A.s_id,a.sname,a.sage,a.sex,/*8*/select * from student where s_id in (select DISTINCT s_id f Rom SC where c_id in (select c_id from SC where s_id= ') ")/*9*/select * from student where s_id in (select s_id from SC where c_id in (select c_id from SC where s_id= ' ") and s_id<> ' ' GROUP by S_ID have Count (s_id) >=3);/*10*/se Lect * FROM student where s_id not in (select s_id from SC where c_id in (select c_id from Course where t_id in (select T_ ID from teacher where tname= ' Zhang San ')));/*11*/select A.s_id,a.sname, B.avg_scorefrom student a right join (select S_ID, avg (score) Avg_score from SC where Scor E&LT;60 GROUP BY S_ID have Count (score) >=2) Bon a.s_id =b.s_id; /*12*/select A.s_id,a.sname,a.sage,a.sex, B.score from student a right joins SC B on a.s_id=b.s_id where b.c_id= ' ' and B. Score<60order by B.score Desc;
/*13* Difficulty: High to low average results show all the students ' grades and average scores */select s_id, Max (case c_id "then score else 0 End" A,max (case c_id when ' "Then score else 0 End" B,max (case c_id when ' c,avg ' and ' score else 0 End ') (score) from Scgroup to s_id order by 5 D Esc;/*14*/select A.c_id,a.cname,b.highest,b.lowest,b.avgscore,c.jigelv,d.middle,e.excellent,f.great,g.people_  Numberfrom Course a LEFT join (select C_id,max (Score) highest, min (score) lowest, AVG (score) Avgscorefrom SC Group by C_ID) Bon a.c_id=b.c_idleft Join (select c_id, (sum (case if score>=60 then 1 else 0 end) *1.00/count (*) *100) Jigelv from S C GROUP by c_id) Con a.c_id=c.c_idleft join (select c_id, (sum (case if score>=70 and score<80 then 1 else 0 end) * * .00/count (*) *100) Middle from SC Group by C_ID) Don A.c_id=d.c_idleft join (select c_id, (sum (case score>=80 and SCO Re<90 then 1 else 0 end) *1.00/count (*) *100) Excellent from SC Group by C_ID) Eon A.c_id=e.c_idleft join (select c_id, (su M (case if score>=90 then 1 else0 end) *1.00/count (*) *100) Great from SC Group by c_id) Fon a.c_id=f.c_idleft join (select C_id,count (*) People_number fr Om SC Group by c_id) Gon A.c_id=g.c_idorder by g.people_number,a.c_id;/*15,15.1 Row_number () Up () rank () over () Dense_r Ank () */select s_id, C_id,score,row_number () over (partition by c_id ORDER BY score Desc) Rank1from Sc;select S_id,c_id,scor E,rank () over (partition by c_id ORDER BY score Desc) Rank1from Sc;select S_id,c_id,score,dense_rank () over (partition by c_i D ORDER BY score Desc) Rank1from Sc;/*16*/select s_id,sum (Score), rank () over (order by sum (SCORE) desc) from SC GROUP by s_i D
/*16.1*/select S_id,sum (Score), Dense_rank () over (order by sum (SCORE) desc) from SC Group by S_id;/*17*/select c_id, SUM (ca SE when score<=60 then 1 Else 0 end) A1, (SUM (case if score<=60 then 1 else 0 end) *1.00/count (*)) A2from SC Grou P by C_idorder by C_id;/*18*/select * FROM (select C_id,s_id,score,rank () over (partition by c_id ORDER BY score Desc) ran K1from SC) b where b.rank1<=3/* Method II: Difficulty */select a.c_id,a.s_id, A.score,b.score from SC Aleft join SC b on a.c_id=b.c_id and A.score<b.scoregroup by a.s_id,a.c_id,a.scorehaving count (b.s_id) <3order by A.c_id,a.score Desc;select * FROM SC a WHERE (select count (*) from SC where c_id=a.c_id and Score>a.score) <3order by a.c_id, A.score Desc/*19*/select C_ID, COUNT (*) from SC GROUP by C_id;/*20*/select s_id to SC GROUP by S_ID have Count (s_id) =2/*21*/select sex,count (SE x) from student GROUP by Sex/*22*/select * from student where sname like '% wind% ';/*23*/select A.s_id,b.countnumber from Stude NT Aleft Join (select Sname, Sex,count (*) CountNumber from student Group by Sname,sex) Bon A.sname=b.snameand a.sex=b.sexwhere b.countnumber>1;/* Use of To_char () */select s_id, sname from student where to_char (sage, ' yyyy ') =1990/*25*/select C_id,avg (score) Avgscore From SC GROUP by c_id ORDER by Avgscore Desc,c_id;/*26*/select A.s_id,a.sname,avg (b.score) avgscorefrom student a left Joi N SC bon a.s_id=b.s_id Group by a.s_id, a.snamehaving avg (b.score) >85;/*27*/select A.sname,b.scorefrom student A left J Oin sc bon a.s_id=b.s_idleft join course con b.c_id=c.c_id where c.cname= ' math ' and B.score<60;/*28*/select A.s_id,a.snam E,b.c_id,b.score,c.cnamefrom student a LEFT join SC bon a.s_id=b.s_idleft join course con b.c_id=c.c_idorder by A.S_ID,B.C _id;/*29%%*/select a.sname,c.cname,b.scorefrom (select S_id,c_id,score from SC where score>70) b left Joincourse C on B . C_id=c.c_idleft join student aon b.s_id=a.s_id;/*30*/select c_id,count (*) from SC where score<60group by c_id;/*31*/s Elect A.c_id,count (*) from course A LEFT join SC bon A.c_id=b.c_idgroup by a.c_id 
/*32*/select C_id,count (*) from SC GROUP by C_ID;
/*33 percent results do not repeat */select top 1* from SC where c_id in (select c_id from Course where t_id in (select t_id from teacher where T Name= ' Zhang San ')) Order by score desc;/*34 percent results repeat, query the students who have enrolled in the course of "Zhang San", the highest grade student information and their achievements */select a.s_id,a.sname,b.score,b.c_id From (select C_id,max (score) Maxscorefrom SC Group by C_ID) E left join SC B on E.maxscore=b.score and e.c_id=b.c_id L EFT Join course C on b.c_id=c.c_id left join teacher D on c.t_id=d.t_id left join student A on a.s_id=b.s_id where D.tnam  E= ' Zhang San '; /*dense_rank () */select E.s_id,e.c_id,e.score from (select s_id, c_id, Score,dense_rank () over (partition by c_id order by S _id) Rank1 from SC) e left joins course C on e.c_id=c.c_id left joins teacher D on c.t_id=d.t_id where D.tname= ' Zhang San ' and E.ra  Nk1=1;  Select Top 1* from (select s_id, c_id, Score,dense_rank () over (partition by c_id ORDER by s_id) Rank1 from SC E left Join  Course C on e.c_id=c.c_id left join teacher D on c.t_id=d.t_id where D.tname= ' Zhang San '; /* Different student courses are the same, scores are the same */select A.S_ID,A.C_ID,A.SCORE,B.S_id,b.c_id,b.score from SC a LEFT join SC B in A.score=b.score and a.s_id>b.s_id and a.c_id=b.c_id where B.score is n OT null; /*35, check the student number, course number, student score */select C.s_id,max (c.c_id) C_id,max (C.score) score from SC cleft join (select s_id, AVG (Score) A from SC Group by s_id) Bon C.s_id=b.s_idwhere c.score=b.agroup by c.s_idhaving count (0) = (select count (0) from S c where s_id=c.s_id)/* There are three lines, how to merge into a line */select A.s_id,a.c_id,b.s_id,b.c_id,a.score,b.score from (select s_id,c_id, score,  Rank () over (partition by s_id order by score) Rank1 from SC) A INNER join (select s_id,c_id, Score,rank () over (partition by s_id ORDER by score) Rank1 to SC) b on A.rank1=b.rank1 and a.s_id=b.s_id and a.c_id<b.c_id; /*36*/SELECT * FROM (select S_id,c_id,score,rank () over (partition by c_id order by score) Rank1 from SC) a where A.rank  1<3;  /*37*/select C_id,count (*) from Scgroup by c_idhaving count (*) >5; /*38*/Select s_id from SC GROUP by S_ID have Count (c_id) >=2/*39*/select s_id from SCgroup by S_ID has count (c_id) = 3; 
/*40*/select s_id,sname,extract (year from date)-extract (year from Sage) the age from student;

　　

Super Classic SQL exercises, implemented on Teradata