Exercise 2 and PL/SQL

Rownum If the primary key is not sorted, it will not become

- query Student information without credit --select *1 from Z_stu_cour ZSC WHERE zsc.stu_code=zs.code)

Exsits High Efficiency If there is a return of your query true to show the existence of the record
When there is a null value in it, the use of not
Not in equals not equal to all (! =all)
In the equivalent of =any any one

--= ZC. Cour_codegroup by ZC. Cour_name, ZC. Cour_code

--=' International Journalism major '=ZSC GROUP by. Stu_code

SELECT * from Z_course ZC;
SELECT * from Z_student ZS;
SELECT * from Z_stu_cour ZSC;
--(a) Simple query
--Query all content in student tables
SELECT * from Z_student ZS
--Check the student's name in the table, professional
SELECT zs.name,zs.major from Z_student ZS
--Check the student table for various majors
SELECT DISTINCT zs.major from Z_student ZS
--The name of the first five students in the enquiry form, professional
SELECT zs.name,zs.major,rownum from z_student zs WHERE rownum<6
--Check the student's number and name and the age of each student as of now
SELECT ZS. CODE,
Zs.name,
(To_char (sysdate, ' yyyy ')-To_char (ZS. BIRTHDAY, ' yyyy ')) age
From Z_student ZS
--Find out all the information for students majoring in international journalism
SELECT *
From Z_student ZS
JOIN Z_course ZC
On ZS. MAJOR = ZC. Cour_code
WHERE ZS. MAJOR = (SELECT ZC. Cour_code
From Z_course ZC
WHERE ZC. Cour_name like ('% international news% '))
--Query the names and majors of students born in 1991
SELECT Zs.name, ZS. Major,zs.birthday from Z_student zs WHERE to_char (zs.birthday, ' yyyy ') =1991
--Inquiry history, advertising, all student information of international journalism majors
SELECT *
From Z_student ZS
JOIN Z_course ZC
On ZS. MAJOR = ZC. Cour_code
WHERE ZS. MAJOR in (SELECT ZC. Cour_code
From Z_course ZC
WHERE ZC. Cour_name like ('% international news% ')
OR ZC. Cour_name like ('% history% ')
OR ZC. Cour_name like ('% ad% '))
--Query name is two words of surname Wang, surname Chen, surname Li's all student information
SELECT zs.name from z_student zs WHERE zs.name like (' King _ ') or zs.name like (' Li _ ') or zs.name like (' Chen _ ')
--Query for student information without credit
SELECT * from Z_student ZS WHERE zs.code not in (SELECT DISTINCT zsc.stu_code from Z_stu_cour ZSC)
SELECT * from Z_student ZS where not EXISTS (select 1 from z_stu_cour ZSC WHERE zsc.stu_code=zs.code)
--Query the computer professional not to record the birthday student information
SELECT *
From Z_student ZS
WHERE ZS. MAJOR like ('% computer% ')
and ZS. BIRTHDAY is NULL
--Query According to the professional descending, student number in ascending order all students information
SELECT * from Z_student zs ORDER by Zs.major DESC, Zs.code ASC
--The name of the first three students in the enquiry form, professional, to the present age and in descending order of age
SELECT Zs.name,
ZS. MAJOR,
(To_char (sysdate, ' yyyy ')-To_char (ZS. BIRTHDAY, ' yyyy ')) age
From Z_student ZS
ORDER by (To_char (sysdate, ' yyyy ')-To_char (ZS. BIRTHDAY, ' yyyy ')) DESC

--(ii) Summary of data
--Query All course codes and average scores for each course and sort them by course number, and then reject course codes not c-adv-240 courses
SELECT DISTINCT ZSC. Cour_code, AVG (ZSC. AGREE)
From Z_stu_cour ZSC
GROUP by ZSC. Cour_code
Having ZSC. Cour_code <> ' c-adv-240 '
ORDER by ZSC. Cour_code
--Check out the highest and lowest scores for each course code
SELECT Zsc.cour_code, MAX (Zsc.agree), MIN (Zsc.agree) from Z_stu_cour zsc GROUP by Zsc.cour_code
--The students who inquire for the stu-1023 number are the grades of each class
SELECT zsc.cour_code,zsc.agree from Z_stu_cour zsc WHERE zsc.stu_code= ' stu-1023 '
--Find out how many people are in the history profession
SELECT ZS. MAJOR, COUNT (ZS. CODE)
From Z_student ZS
WHERE ZS. MAJOR = (SELECT ZC. Cour_code
From Z_course ZC
WHERE ZC. Cour_name like ('% history% '))
GROUP by ZS. MAJOR
--Query Each profession how many people
SELECT Zs.major,count (ZS. CODE) from Z_student ZS GROUP by Zs.major
--Find out how many men and women are in each profession
SELECT Zs.major,decode (zs.sex,1, ' male ', ' female '), COUNT (*) from Z_student Zs GROUP by Zs.major,zs.sex
--To find out the student number with an average of 50 or more students in all courses
SELECT ZSC. Stu_code
From Z_stu_cour ZSC
WHERE ZSC. Cour_code in (SELECT ZSC. Cour_code
From Z_stu_cour ZSC
Having AVG (ZSC. AGREE) > 50
GROUP by ZSC. Cour_code)

--Query Each student has a few course scores above 80 points
SELECT Zsc.stu_code,count (*) from Z_stu_cour zsc WHERE zsc.agree > GROUP by Zsc.stu_code
--(c) Connection enquiry
--Query All student's number, name, professional course number, Grade
SELECT Zs.name,zs.code,zsc.cour_code,zsc.agree from Z_student Zs joins Z_stu_cour ZSC on Zs.code=zsc.stu_code
--Query the name and grade of the student whose course number is c-his-336
SELECT Zs.name,zsc.agree from Z_student Zs joins Z_stu_cour ZSC on Zs.code=zsc.stu_code WHERE zsc.cour_code= ' c-his-336 '
--Inquire about the student number, name, and result of the course of basic art of advertising major
SELECT ZS. CODE, Zs.name, ZSC. AGREE
From Z_student ZS
JOIN Z_stu_cour ZSC
On ZS. CODE = ZSC. Stu_code
JOIN Z_course ZC
On ZSC. Cour_code = ZC. Cour_code
WHERE ZC. Cour_name = ' Basic Art '
--Inquiry for all student information and achievements of elective course number c-news-101 This course
SELECT * from Z_student ZS joins Z_stu_cour ZSC on Zs.code=zsc.stu_code WHERE zsc.cour_code= ' c-news-101 '
--Check birthday is student information on the same day
SELECT *
From Z_student ZS
WHERE ZS. BIRTHDAY in (SELECT ZS1. BIRTHDAY
From Z_student ZS1
GROUP by ZS1. BIRTHDAY
Having COUNT (ZS1. BIRTHDAY) > 1)
--Check the course name, course number, and average scores of all students in each course
SELECT ZC. Cour_name, ZC. Cour_code,avg (ZSC. AGREE)
From Z_stu_cour ZSC
JOIN Z_course ZC
On ZSC. Cour_code = ZC. Cour_code
GROUP by ZC. Cour_name, ZC. Cour_code
--Check the average scores of all students
SELECT AVG (zsc.agree) from Z_stu_cour ZSC
--Query the average number of each course in each major
SELECT AVG (zsc.agree) from Z_stu_cour zsc GROUP by Zsc.cour_code
--the average score for each student in an international journalism major with an average score of more than 40 points
--(iv) use of sub-queries
--query average score is greater than or equal to the course number of c-adv-239 and average
SELECT ZSC. Cour_code, AVG (ZSC. AGREE)
From Z_stu_cour ZSC
GROUP by ZSC. Cour_code
Having AVG (ZSC. AGREE) >= (SELECT AVG (ZSC1). AGREE)
From Z_stu_cour ZSC1
WHERE ZSC1. Cour_code = ' c-adv-239 '
GROUP by ZSC1. Cour_code)
--the rate of pass for the course under the history major (the number of people with a course score above 50 divided by the total number)
SELECT ZSC. Cour_code,
SUM (case
When ZSC. AGREE >
1
ELSE
0
END)/COUNT (*)
From Z_stu_cour ZSC
WHERE ZSC. Cour_code in
(SELECT ZC. Cour_code
From Z_course ZC
CONNECT by ZC. P_cour_code = PRIOR ZC. Cour_code
START with ZC. Cour_name = ' History major ')
GROUP by ZSC. Cour_code
--Inquire about student information and course information that does not take the course of c-news-101
SELECT zs.*, zc.*
From Z_course ZC
JOIN Z_stu_cour ZSC
On ZC. Cour_code = ZSC. Cour_code
JOIN z_student ZS
On ZSC. Stu_code = ZS. CODE
WHERE ZC. Cour_code <> ' c-news-101 '
--Query student information without a course score
SELECT ZS. CODE
From Z_student ZS
JOIN Z_stu_cour ZSC
On ZS. CODE = ZSC. Stu_code
WHERE ZSC. AGREE not in (SELECT ZSC. AGREE from Z_stu_cour ZSC)

Cycle:
DECLARE
M number (5);
Sname VARCHAR2 (20);

BEGIN
m:=50;
/* LOOP
EXIT when m<0;
Dbms_output.put_line (m);
M:=m-1;
END loop;*/
/* While m>0
LOOP
Dbms_output.put_line (m);
M:=m-1;
END loop;*/

/*for N in 1..5 LOOP
Dbms_output.put_line (n);
END loop;*/
SELECT zs.name into sname from z_student Zs WHERE zs.id=m;
Dbms_output.put_line (' query result: ' | | sname);
EXCEPTION
When No_data_found Then
Dbms_output.put_line (' No query results ');
END;

1. PL/SQL Basic syntax format
2. Recording type type...is record (,,,);
3. Process Control:
3.1 Pieces of Judgment (two)
Mode one: If...then elsif then...else...end if;
Way two: Case...when...then. End
3.2 Cycle structure (three types)
Mode one: Loop...exit when. End Loop;
Mode two: While...loop...end loop;
Way three: for N in ... Loop...end Loop;
3.3goto, exit
4, the use of cursors (similar with iterator)
5. Exception handling (three kinds)
6. Write a stored function (with return value), stored procedure (no return value)
7, will write a trigger

Exercise 2 and PL/SQL