MySQL > select * from ‘MySQL’;

This is the 25th day of my participation in the August Genwen Challenge.More challenges in August

MySQL > select * from ‘MySQL’;

11. Output the student ID, name and department name of the top 2 students in “Advanced Mathematics”

SQL > alter TABLE name;

The student database db_student consists of three tables: Student, course and sc. The table structure is as follows:

1, Student (table) :

field describe type
sno Student id char(7)
sname The name char(10)
ssex gender char(2)
sage age SMALLINT
sdept Department of VARCHAR(20)

2. What is the course?

field describe type
cno Course no. char(10)
cname Course name VARCHAR(20)
ccredit credits SMALLINT
semster semester SMALLINT
period school SMALLINT

3. Sc (Course Selection List)

field describe type
sno Student id char(7)
cno Course no. char(10)
grade results SMALLINT 
SELECT s.sno,sname,sdept from student s,sc,course c WHERE s.sno=sc.sno and sc.cno=c.cno AND cname='Advanced Mathematics'

ORDER BY grade DESC LIMIT 2;
Copy the code

12. Query the borrowing information of 2015, output the reader card number, ISBN number and borrowing date.

Borrow form: CNO card number, BNO CALL number, Rdate return date

select cno,bno,rdate from borrow
where year(rdate)=2015;
Copy the code

SQL query: Query personnel information: including employees and customers

Query personnel information: including employees and customers. (Employee data comes first)

Id, Name, and Type are displayed. For an employee, type is Employee; for a customer, type is Customer.

Employee table: Employee

Customer table: Customer

The query output is as follows:

SELECT employee_id AS id,employee_name AS 'name'.'employees'type
FROM employee
UNION 
SELECT customer_id AS id,customer_name AS 'name'.'customers'type
FROM customer;
Copy the code

Select * from books whose titles include the keyword “Internet”

Query information about books whose titles include the keyword “network”, output isbn, title, author.

The table structure is as follows:

Book table: BNO, BNAME, author, price, quantity

select bno,bname,author from book
where bname like '% % network';
Copy the code

15. The transcript includes the following (student ID, course code, grade, teacher ID), query query the total number of students enrolled in the course.

SELECT count(DISTINCT xh) from cj;
Copy the code

Select * from student where number of elective courses > 3 and average score > 70

Select * from student where student id, number of courses and average score > 70 and rank by average.

The grade table includes the following (student ID, course code, grade, teacher id). The structure of the table is defined as follows:

select xh,count(*),avg(cj) from cj   where cj>60  group by xh
having count(*) >3 and avg(cj)>70   order by 3 desc;
Copy the code

17. Count the number of electors for each course, and ask to output course code, course name, number of students with grades (grade is not NULL), highest score, lowest score, average score (take the whole round function), sorted by course number.

SQL > alter TABLE name;

The student database db_student consists of three tables: Student, course and sc.

The table structure is like the beginning of the article — the code is as follows:

select sc.cno,cname,count(grade),max(grade),min(grade),round(avg(grade))
from sc,course
where sc.cno=course.cno
group  by  cno
order by cno;
Copy the code

Select * from student where average score is 2-5; select * from student where average score is 2-5; select * from student where average score is 2-5; [Do not consider null values]

SQL > alter TABLE name;

The student database db_student consists of three tables: Student, course and sc.

The table structure is like the beginning of the article — the code is as follows:

select sc.sno,sname,round(avg(grade))
from student,course,sc
where student.sno=sc.sno and course.cno=sc.cno
group by sc.sno
order by round(avg(grade)) desc
limit 1.4;
Copy the code

19.SQL query: Query the total number of customers ordering in 2016

SQL > alter table order;

SELECT COUNT(DISTINCT customer_id) from `order` WHERE order_date LIKE '% 2016%';
Copy the code

20. Query card number, name, number of books borrowed, title list (ascending by title, separated by underscore _), ascending by card number.

The table structure is as follows:

Card form: CNO card number, name, class class

Book table: BNO, BNAME, author, price, quantity

Borrow form: CNO card number, BNO CALL number, Rdate return date

select card.cno,name.count(*),
GROUP_CONCAT(bname order by bname separator '_') 
from card,borrow,book
where card.cno=borrow.cno and borrow.bno=book.bno
group by card.cno
order by card.cno;
Copy the code

The end:

The solution to the above problem is not unique, the solution in the sample is what I understand and works successfully, if you see here or just for you, I hope you can click 👍 or ⭐ thank you;

There are mistakes, welcome to point out in the comments, the author will see the modification.