SQL statement exercises and answers

1 student table student

S # student ID, sname name, difdate date, class grade

2 course schedule course

C # course No., name: cname

3 transcript score

S # student ID c # course number score


-- 1 count the number of persons in each class
Select grade, count (sname) from ze_student group by grade;

-- 2. The average score of 2007 students with no score is 0;
Select a. sname, (select avg (nvl (B. score, 0) from ze_score B where B. s # = a. s #) from ze_student a where grade = 2007;

-- 3: average score of each subject and minimum score of the highest score: 2007; two decimal places rounded
Select B. c #, avg (B. score), max (B. score), min (nvl (B. score, 0) from ze_student a, ze_score B where B. s # =. s # and. grade = 2007 group by B. c #;

-- 4: add 5 points to level 1 mathematics
Update ze_score
Set score = nvl (score, 0) + 5
Where s # in (select s # from ze_student where grade = 2007) and c # = (select c # from ze_course where cname = 'mat ');

-- If the score is 5 to 90, the score is excellent. If the score is 90 to 85, the score is good. If the score is 60, the average score is passing.
Select s #, c,
Case
When c> = 90 then 'excellent'
When c <90 and c> = 60 then 'pass'
Else 'failed' end as jige
From (select s #, avg (nvl (score, 0) as c from ze_score group by s #) order by jige desc;


-- 6. Number of people born in the same month
Select to_char (difdate, 'mm') as month, count (s #) as birthdate from ze_student group by to_char (difdate, 'mm ');

-- 7 pass rate and average score of each subject are reserved for 2 places
-- Pass rate
Select c #, avg (nvl (score, 0) as average score, sum (nvl (score, 0) as total score, count (s #) as number of subjects,
Trunc (sum (
Case when nvl (score, 0)> 60 then '1'
Else '0' end)/count (s #), 2) as pass rate
From ze_score group by c #;

-- Pass rate per person
Select s #, avg (nvl (score, 0) as average score, sum (nvl (score, 0) as total score, count (c #) as total account,
Sum (
Case when nvl (score, 0)> 60 then 1
Else 0 end
)/Count (c #) as pass rate
From ze_score group by s #;


-- 8 deleting a name is the result of Michael's University Chinese
Select * from ze_score where s # in (select s # from ze_student where sname in 'zhang san') and c # = (select c # from ze_course where cname = '文 ');

-- 9 replace mathematics with advanced mathematics
Update ze_course set cname = 'higher mathematics 'where cname like' % mathematics % ';

-- 10 format. The student ID is changed to 0 after less than 12 characters starting with S;
-- Query
Select concat ('s ', lpad (S #, 11, 0) as s # from ze_score;
Select concat ('s ', lpad (S #, 11, 0) as s # from ze_student;

-- Format
Update ze_score set s # = concat ('s ', lpad (S #, 9, 0 ));
Update ze_student set s # = concat ('s ', lpad (S #, 9, 0 ));

Four football teams

Select a. name, B. name from qiu a, qiu B where a. name <B. name;

Commit
Rollback
Server Type
Server Protocol
Global Database Name
Server IP Address
Server port number
Username and password