Database query examples and SQL statements

drop database if exists SS;create database SS;use SS;create table Student ( Sno char(9) primary key, Sname char(20) unique, Ssex char(2), Sage smallint, Sdept char(20));create table Course(  Cno char(4) primary key,  Cname char(40),  Cpno char(4) references Course(Cno),  Ccredit smallint  );create table SC(    Sno char(9) references Student(Sno),   Cno char(4) references Course(Cno),   Grade smallint,   primary key(Sno,Cno));

 

Bytes -------------------------------------------------------------------------------------------------

Example 1: query the name and date of birth of all students.
Problem Analysis:
The data to be queried is: sname, birth year (this column is not in the table, but can be calculated)
Which tables can obtain the data to be queried: sname is the attribute of student, and sage is also the attribute of Student. Therefore, the ontology needs to be queried.
The result can be obtained in student, where the date of birth is the current year minus the age.
Query statement: Select sname, 20012-sage
From student;
---------------------------------------------------------------------------------
Example 2: query the student ID of the course
Problem Analysis:
Data to be queried: SnO
Analysis: The SNO of the ancestor corresponding to the course No. CNO is not empty,
The SC table already contains the CNO information and the student ID. Remove duplicate data.
Because multiple CNO can correspond to one sno1
Which tables can obtain the data to be queried: SnO and CNO are SC attribute columns, so the required information can be obtained in SC.
Query statement: Select distinct SnO
From SC;
---------------------------------------------------------------------------------
Example 3: query the names and gender of Students in CS, Ma, and is.
Problem Analysis:
Data to be queried: sname, ssex
Which tables can obtain the data to be queried: sname and ssex are the attribute columns of student, so you can query the data in the student table.
Query statement:
1) Select sname, ssex
From student
Where sdept = 'cs 'or sdept = 'Ma' or sdept = 'ais ';
You can also use the predicate in to find that the attribute belongs to the ancestor of the specified set. Therefore, query 2
2) Select sname, ssex
From student
Where sdept in ('cs ', 'M', 'is ');
Bytes ----------------------------------------------------------------------------------------------
Example 4): query the number of course Numbers (CNO) of each course and the number of students corresponding to the course number.
Problem Analysis:
Data to be queried: CNO, number of students selected
The tables used for query: CNO And sno are SC attribute columns.
Analysis: since a CNO can correspond to multiple SnO, you can use group by to divide values with the same CNO into one group,
Use count to calculate the number of rows in the same group. Use count (CNO ).
Query statement:
Group first
1): Select CNO, SnO from SC group by CNO, SnO
/* Note that you cannot write select CNO, SnO from SC group by CNO, because there is a principle in group.
That is, all columns after the SELECT statement must appear after the group by clause */
2): Select CNO, count (SNO) as num/* count (SNO) is to calculate how many SnO or how many SnO are in the same group */
From
Group by CNO

Note that the purpose of group by is to refine the target objects of Aggregate functions. If no
The aggregate function applies to the entire query result, for example, select count (*) from student, count (*)
The lecture calculates the number of all tuples, that is, the total number of students. After grouping, the clustering function applies to each group, that is, each group has
Function value, so the count (SNO) above is the number of SnO corresponding to when CNO is a value A, rather than the sno of all query results.
Certificate ------------------------------------------------------------------------------------------------------------------------------------
Example 5: query the student ID of more than two courses
Problem Analysis:
Queried data: SnO
Query tables used: because the course information is in SC, you only need to use SC tables.
Analysis: because an SnO can correspond to multiple CNO, you can use group by to divide the values with the same SnO into one group.
You can get the course selection information corresponding to an SnO, and then count each group with Count (*). Here we use the having phrase for filtering.
Query statement: Select SnO from SC group by SnO having count (*)> 2;
Bytes ---------------------------------------------------------------------------------------------------------------------------
Example 6: query the Sno. (name) sname, cname (Course name), and grade (score) of each student)
Problem Analysis:
Query data: SnO, sname, cname, grade
Involved tables: SnO and sname are student attribute columns, cname is course attribute columns, and grade is SC attribute columns. Therefore, student, course, and SC tables are used.
Analysis: The three tables are associated with SnO and CNO.
Query statement: select student. Sno, sname, cname, grade
From student, SC, Course
Where student. Sno = SC. SnO and SC. CNO = course. CNO;
Bytes --------------------------------------------------------------------------------------------------------------------------
Example 7): query the students in the same department as "Liu Chen ".
Problem Analysis:
Query data: Student information. The condition is the same as that of Liu Chen,
Unknown data: the system where Liu Chen is located
Known Data: Student name Liu Chen
Involved tables: Student
Analysis: Unknown data can be queried based on known data.
Query statement:
1) determine the system where Liu Chen is located
Select sdept from student where sname = "Liu Chen"
2) Search for the student information in the same department and 1.
Select * from student where sdept = 'cs ';
In combination, select * from student where sdept in (select sdept from student where sname = "Liu Chen ");
Each student can have only one department, so there is only one query result in 1, so you can also write the following statement
: Select * from student where sdept = (select sdept from student where sname = "Liu Chen ");
In fact, you can also use your own connection to query: Think of student as two tables for A and B, then the question can be described as follows:
To query the student information of A, the system where A is located must be the same as that of the student whose name is Liu Chen in B. You can write it as follows:
Select * from student as a where sdept = (select sdept from student as B where sname = "Liu Chen ");
And then improve
Select a. * from student A, student B where a. sdept = B. sdept and B. sname = "Liu Chen ";
Bytes -----------------------------------------------------------------------------------------------------------------------------
Example 8): Find the course number of each student that exceeds the average score of the course he or she chooses.


Problem Analysis:
Ask another question: The SnO and CNO scores of each student exceed the average scores of the class.
Query statement: Select SnO, CNO from SC where grade> (select AVG (grade) from SC)
It is easy to write the following statement for all the problems compared with those changed
Select SnO, CNO from SC as a where grade> = (select AVG (grade) from SC as B where a. Sno = B. SnO );

Bytes ----------------------------------------------------------------------------------------------------------------------------

Example 9): Set the score of all students in the computer system to zero.
Problem Analysis:
Involved tables: The score table is in SC, and the department sdept is in student table. Therefore, two tables SC and student are involved.

Query statement 1): Update SC set grade = 100
Where 'cs '= (select sdept from student where student. Sno = SC. SnO );

Query Statement 2): Update SC set grade = 0
Where SC. SnO IN (select student. Sno from student where sdept = 'cs ');