MySQL Learning note-day2

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.