Next, I learned SQL queries and practiced the examples in Database System Overview-Higher Education Press.

Next, learn about SQL queries.

 

Introduction to database systems-Higher Education Press

Gao Yu's book is good and fluent in language. the example is not difficult, but it can explain the problem. I have debugged them all in VFP. Of course, the problem is inevitable. Let's discuss it here.

Some useful websites:

If you want to understand SQL clearly, do not read the ANSI White Paper.
Http://www.contrib.andrew.cmu.edu /~ Shadow/SQL/

Write SQL query: Let's start with basic knowledge
Http://www.microsoft.com/china/msdn/library/data/sqlserver/ssequerybasics.mspx

What if there is no VFP? Download Visual FoxPro v6.0

If you are not at ease with version D, try ms SQL 2005, which is free of charge and can be downloaded from the MS website.
Ms SQL 2005 express Edition
Http://msdn.microsoft.com/vstudio/express/ SQL /register/default.aspx

Http://msdn.microsoft.com/vstudio/express/ SQL /default.aspx

Student Student ID SnO Name Sname Gender Ssex Age Sage System Sdept 95001 LI Yong Male 20 CS 95002 Liu Chen Female 19 Is 95003 Wang Min Female 18 Ma 95004 Zhang Li Male 19 Is
Course Course No. CNO Course name Cname Advance Course Cpno Credits Ccredit 1 Database 5 4 2 Mathematics 2 3 Information System 1 4 4 Operating System 6 3 5 Data Structure 7 4 6 Data Processing 2 7 PASCAL Language 6 4	SC Student ID SnO Course No. CNO Score Grade 95001 1 92 95001 2 85 95001 3 88 95002 2 90 95002 3 80

 

Eg1
Query the student ID and name of all student tasks.
Select SnO, sname from student

Eg2
Query the name, student ID, and Department of all non-students.
Select sname, SnO, sdept from student

Eg3
Query detailed records of all students.
Select * from student
Select SnO, sname, ssex, sage, sdept from student

Eg4
Check the name and year of birth of all students.
Select snname, 2006-sage from student

Eg5
To query the names of all students, the Year of birth and the names of all students, Use lowercase letters to indicate the names of all students.
Select sname, 'year of birth: ', 2006-sage, islower (sdept) from student
Select sname, 'year of birth: ', 2006-sage, lower (sdept) from student
(### Another error was found. The previous statement in the book returned T (ture) or F (false) values ).
Select sname name, 'year of birth: 'birth, 2006-sage birthday, lower (sdept) department from student

Eg6
Query the student ID of the selected course.
Select SnO from SC

Eg7
Query the list of all students in the computer department.
Select sname from student where sdept = 'cs'

Eg8
Query the names and ages of all students under the age of 20.
Select sname, sage from student where sage <20
Select sname, sage from student where not Sage> = 20

Eg9
Check the student ID for which the exam result fails.
Select distinct SnO from SC where Grade <60
(### The book is wrong again. You can read the book)

Eg10
Query the name, department, and age of a student between the ages of 20 and 23.
Select sname, sdept, sage from student where sage between 20 and 23

Eg11
Query the names of students aged between 20 and 23.
Select sname, sdept, sage from student where Sage not between 20 and 23

Eg12
Query the names and gender of students in the Information Department (is), Mathematics Department (MA), and computer department (CS.
Select sname, ssex from student where sdept in ('is, 'M', 'cs ')

Eg13
The name and gender of a student who is neither an Information Department, a mathematics department, nor a Computer Science Department.
Select sname, ssex from student where sdept in ('is, 'M', 'cs ')

Eg14
Query the details of students whose student ID is 95001.
Select * from student where SnO like '20140901'
Select * from student where SnO = '201312'

Eg15
Query the name, student ID, and surname of all students surnamed Liu.
Select sname, SnO, ssex from student where sname like 'Liu %'

Eg16
Query the names of all three students whose names are 'ouyang.
Select sname from student where sname like 'Liu _'
Select sname from student where sname like 'ouyang __'
(### Take this book for granted)

Eg17
Query the name and student ID of a student whose 2nd characters are Yang Zi.
Select sname, SnO from student where sname like '_ min'
(### Only in this way can we find out)

Eg18
Query the names of all students not surnamed Liu.
Select sname from student where sname not like 'Liu %'

Eg19
Query the course number and credits of the db_design course.
Select CNO, ccredit from course where cname like 'db/_ Design' escape '/'

Eg20
Query the details of a course that starts with "DB _" and starts with "DB _" and starts with "I.
Select * from course where cname like 'db/_ % I _ 'escape '/'

Eg21
Some students do not take the test after taking the course, so there is a Course Selection Record, but there is no test score. query the student's student ID and the corresponding course number.
Select SnO, CNO from SC where grade is null

Eg22
Query all student ID and course number with scores.
Select SnO, CNO from SC where grade is not null

Eg23
Query the names of students under the age of 20 in the computer system.
Select sname from student where sdept = 'cs 'and sage <20

Eg24
Query the student ID and score of the course No. 3 selected. The query results are sorted in descending order of scores.
Select SnO, grade from SC where CNO = '3' order by grade ASC
Select SnO, grade from SC where CNO = '3' order by grade DESC

Eg25
Query the status of all students. The query results are listed in ascending order of the Department number, and the students in the same department are listed in descending order of age.
Select * from student order by sdept, sage DESC

Eg26
Query the total number of students.
Select count (*) from student

Eg27
Query the number of students who have selected the course.
Select count (SNO) from SC
Select count (distinct SnO) from SC

Eg28
Calculate the average score of students of course 1.
Select AVG (grade) from SC where CNO = '1'

Eg29
Query the maximum number of students in an optional course.
Select max (grade) from SC where CNO = '1'

Eg30
Calculate the number of courses and the number of students selected.
Select CNO, count (SNO) from SC group by CNO

Eg31
Query the student ID of more than three courses.
Select SnO from SC group by SnO having count (*)> 3

Eg32
Query the status of each student and their optional courses.
Select student. *, SC. * from student, SC where student. Sno = SC. SnO

Eg33
Use natural connection for example 32.
Select student. Sno, sname, ssex, sage, sdept, CNO, grade from student, SC where student. Sno = SC. SnO

Eg34
Query the indirect electives of each course (that is, the first course of the first course ).
Select first. CNO, second. cpno from course first, course second where first. cpno = second. CNO
(### The query results in VFP are different from those in the book. It also takes null as a priority.

Eg35
Query all students who have taken course 2 and scored more than 90 points.
Select student. Sno, sname from student, SC where student. Sno = SC. SnO and SC. CNO = '2' and SC. Grade> 90

Eg36
Query the student ID, name, and Elective Course name and score.
Select student. Sno, sname, cname, grade from student, SC, course where student. Sno = SC. SnO and SC. CNO = course. CNO

Eg37
Query students in the same department as Liu Chen.
Select SnO, sname, sdept from student where sdept in (select sdept from student where sname = 'Liu chen ')

Eg38
Query the student ID and name of the course named information system.
Select SnO, sname from student where SnO IN (select SnO from SC where CNO in (select CNO from course where cname = 'infosystem '))
(### Check too deeply)

Eg39
Query the names and ages of students younger than a certain student in other systems.
Select sname, sage from student where sage <Any (select sage from student where sdept = 'ais ')

Eg40
Query the names and ages of students younger than all students in other systems.
Select sname, sage from student where sage <all (select sage from student where sdept = 'is) and sdept <> 'is'

Eg41
Query the names of all students who have selected course 1.
Select sname from student where exists (select * from SC where SnO = student. SnO and CNO = '1 ')

Eg42
Query the names of students not taking course 1.
Select sname from student where not exists (select * from SC where SnO = student. SnO and CNO = '1 ')

Eg43
Query the names of students who have selected all courses.
Select sname from student where not exists (select * from course where not exists (select * from SC where SnO = student. SnO and CNO = course. CNO ))
(### Check too deeply)

Eg44
Query the number of all students whose courses are at least 95002 selected.
Select distinct SnO from SC scx where not exists (select * from SC SCY where SCY. sno = '200' and not exists (select * from SC SCZ where SCZ. sno = SCZ. snO and SCZ. CNO = SCY. CNO ))
(### Check too deeply)

Eg45
Query students of the Computer Science Department and students not older than 19 years old.
Select * fro mstudent where sdept = 'cs 'Union select * from student where sage <= 19

Eg46
Query the students who have selected course 1 or course 2
Select SnO from SC where CNO = '1' Union select SnO from SC where CNO = '2'

Eg47
Query the intersection between students of the Computer Science Department and students not older than 19 years old.
Select * from student where sdept = 'cs 'and sage <= 19

Eg48
Query the intersection of the student set in Elective Course 1 and the student set in Elective Course 2.
This example is used to query the students who have taken both course 1 and course 2.
Select SnO from SC where CNO = '1' and SnO IN (select SnO from SC where CNO = '2 ')

Eg49
Query students of the Computer Science Department and students not older than 19 years old.
Select * from student where sdept = 'cs 'and sage> 19

Eg50
No

 

Download Code