The table name
Student (s #, Sname, Sage, Ssex) students table s # : student id. Sname: student's name; Sage; Student age; Ssex: student gender
Course schedule c # (c #, Cname, T#) : Course number; Cname: course name; T# : teacher number
SC (s #, c #, score) result table s # : student id. C # : course number; Score: score
The Teacher (T#, Tname) Teacher table T# : Teacher number; Tname: teachers name
Question 1: query "001" than "002" course grade high all the student number of the students;

Question 2: query have not learned "Ye Ping" teacher class student id, name

Question 3: delete "Ye Ping" learning lesson teacher of SC table record

Question 4: to make the "sc" table "Ye Ping" teacher teach classes grades are change the average score for this course

CodePudding user response:

Roughly wrote once, no check, the next best provide build table script and test data, so convenient point

Select distinct a.s #, s.s name
The from sc. A,
Sc b,
Student s
Where a.s # #=b.s
And a.s # #=s.s
And a.c #='001'
And the biggest #='002'
And a.s core & gt; B.s core;

Select distinct s.s #, s.s name
The from sc,
Student s
Where sc. S # #=s.S
And not the exists (select 1
The from course c,
The teacher t
Where c.t # #=t.t
And Arthur c. #=sc. C #
And t.t name='Ye Ping');

The delete from sc
Where the exists (select 1
The from course c,
The teacher t
Where c.t # #=t.t
And Arthur c. #=sc. C #);

The update sc
The set of sc. Score=(select avg (score)
The from sc c
Where sc.=Arthur c. c # #)
Where the exists (select 1
The from course c,
The teacher t
Where c.t # #=t.t
And Arthur c. #=sc. C #);

CodePudding user response:

The latter two SQL wrote Ye Ping conditions, less added below the
The delete from sc
Where the exists (select 1
The from course c,
The teacher t
Where c.t # #=t.t
And Arthur c. #=sc. C #
And t.t name='Ye Ping');

The update sc
The set of sc. Score=(select avg (score)
The from sc c
Where sc.=Arthur c. c # #)
Where the exists (select 1
The from course c,
The teacher t
Where c.t # #=t.t
And Arthur c. #=sc. C #
And t.t name='Ye Ping');

CodePudding user response:

Great god I want to ask you a question, a great god genuflect is begged solve

The use of A charindex function, the name field in the table store "beauty", respectively, in which position? (write specific SQL statement)?


Using the variable assignment of above said last month on the first day, today of last month, the first day of this month and today (write specific SQL statements)

CodePudding user response:

ORACLE charindex function, SQL SERVER, you say here is ORACLE section

CodePudding user response:

I have a problem don't know how to solve, could you help me to solve it, thank you very much
Oracle database table is the pseudo column rowid, please use the rowid. Write a SQL statement, the only records found in the following records, and its flag column updates to Y
No name sex flag
Zhang SAN 1 N
Zhang SAN 1 N
Zhang SAN 1 N