MySQL Learning note-day2

1, the inquiry "001" course is higher than "002" of all students of the school number;
Select a. ' s# ' from (select ' s# ', score from SC where ' C # ' = "001") A,
(SELECT ' s# ', score from SC where ' C # ' = "002") b
where A.score>b.score and A. ' s# ' =b. ' s# ';

2, the query average score is more than 60 points of the student's number and average score;
Select ' s# ', AVG (score)
From SC
GROUP BY ' s# ' have avg (score) >60;

3, inquiry all students of the school number, name, number of courses selected, total;
Select Student. ' s# ', Student.sname,count (SC. ' C # '), SUM (score)
From Student to Outer join SC on Student. ' s# ' =sc. ' s# '
GROUP by Student. ' s# ', Sname;

4, inquire the surname "Li" the number of teachers;
Select COUNT (Distinct (tname)) from Teacher where tname like "Li%";

5, the inquiry did not learn "cotyledons" teacher class students of the school number, name;
Select Student. ' s# ', student.sname
From Student
where ' s# ' not in (select DISTINCT (sc. ' s# ') is from Sc,course,teacher where SC. ' C # ' =course. ' C # ' and Teacher. ' t# ' =course. ' t# ' and teacher.tname= "cotyledons");

6, the inquiry learned "001" and also learned the number "002" course of the students ' study number, name;
Select Student. ' s# ', student.sname
From STUDENT,SC
where Student. ' s# ' =sc. ' s# ' and SC. ' C # ' = ' 001 ' and exists (SELECT * from SC as sc_2 where sc_2. ' s# ' =sc. ' s# ' and sc_2. ' C # ' = ' 00 2 ");

7, the inquiry has learned "Cotyledons" the teacher teaches all classes The student's school number, the name;
Select ' s# ', Sname from Student where ' s# ' in (select ' s# ' from SC, Course, Teacher
where SC. ' C # ' =course. ' C # ' and Teacher. ' t# ' =course. ' t# ' and teacher.tname= ' cotyledons ' GROUP by ' s# '
Having count (SC. ' C # ') = (select count (' C # ') from Course,teacher where Teacher. ' t# ' =course. ' t# ' and Tname= "cotyledons"));

8, check all the course results are less than 60 points of the student's school number, name;
Select ' s# ', Sname from Student
where ' s# ' not in (select Student. ' s# ' from STUDENT,SC where Student. ' s# ' =sc. ' s# ' and score>60);

9, the inquiry did not learn all the class student's school number, the name;
Select Student. ' s# ', student.sname from STUDENT,SC where Student. ' s# ' =sc. ' s# '
GROUP by Student. ' s# ', Student.sname has count (' C # ') < (select count (' C # ') from Course);

10. Change table field type

ALTER TABLE student modify ' s# ' varchar (15);

PS. Today's interview back, completely blindfolded feeling, four years of work experience is only worth less than 1000, OK, the industry may be really important, to choose an industry, adhere to go on, instead of changing the industry or halfway.

Go to no road to go, or to insist on going on, a person how? Who did not kill a crowd, and then have the company's people? Or perhaps, there will be a class of people who need not accompany at all, just to be alone.

MySQL Learning note-day2