Some SQL statements will not, basic classes, urgent, thank you ~~~~~~~~

lehmann

Table 1: Student Situation Information Form XS
Student ID Name Professional Name Gender Birth Time Total Credits Remarks
Primary key

Table 2: Course schedule KC
Course number Course name Start semester Hours Credits
Primary key

Table III: Students and curriculum XS_KC
Student No. Course No. Grade Credits
Primary key primary key

Query operation
1. Query the course names of all students. No duplicate records are required.
2. Display the course names and highest scores that are grouped by course name.
3. Show the student number and name of the student who was born the same year as "Li Fangfang".
4. Show the course name and average grade of the course chosen by the boy.
5. Display the average scores of all students in the XS_KC table for all courses.
Create cursor
  1. Create a cursor MYCURSOR, the query result set is the total credits of all students, and define the variable ＠score to read the current result into the ＠score variable, and use the WHILE statement to loop, if you find:. Divide one by one: delete the record (in the WHERE clause, use the CURRENT OF cursor name to indicate the record pointed to by the current cursor pointer.) For records between 30 and 40, add 20 points. Close the cursor and delete. Note: CURRENT OF keyword does not appear in the cursor definition when READ-ONLY or FAST FORWARD.


Must be implemented using SQL statements, thank you

fanfan1980

See also exam questions

lehmann

^ _ ^ Was it your previous exam question? ? ?

我是杀人狂

1.select a. Student number, b. Course name from table3 a, table2 b where a. Course number = b. Course number
2.Select course name, max (grade) as grade from table3 a, table2 b where a. Course number = b. Course number group by course name
3.Select student number, name from table1 where year (birth time) in (select year (birth time) from table1 where name = '李 方 方'

wgfxman

1. Query the name of the course selected by all students. No duplicate records are required.
select DISTINCT a. student number, b. course name from KC a, XS_KC
b where a. course number = b. course number
2. SELECT a. Course number, a. Course name, MAX (b. Score) highest score
FROM KC a, XS_KC b where a. Course number = b. Course number GROUP BY a. Course number, a. Course name

3. SELECT student number, name FROM XS
WHERE name <> 'Li Fangfang' AND birth time IN (SELECT birth time FROM XS WHERE name = '李 方 方')

lehmann

4. Show the course name and average grade of the course chosen by the boys. (Note: Gender is bit type, 1 means male, 2 means female)
5. Display the average grades of all courses for each student in the XS_KC table.

lehmann

The format of birth time is like this 1982-10-69

wgfxman

4. Show the course name and average grade of the course chosen by the boys.
SELECT B. Course name, AVG (C. score) average score
FROM XS A, KC B, XS_KC C
WHERE A. Gender = 'Male' AND A. Student ID = C. Student ID AND B. Course ID = C. Course ID
GROUP BY B. Course name

5. Display the average grades of all courses for each student in the XS_KC table.
SELECT student number, SUM (score) / (COUNT (course number)) average score
FROM XS_KC GROUP BY student number

wgfxman

1982-10-69

~-~ What date format is this? .

Others, change it yourself

lehmann

smalldatetime