Experiment 4 of 2020 database system of Shandong University

What's more

Experiment 1 of 2020 database system of Shandong University
Experiment 2 of 2020 database system of Shandong University
Experiment 3 of 2020 database system of Shandong University
Experiment 4 of 2020 database system of Shandong University
Experiment 5 of 2020 database system of Shandong University
Experiment 6 of 2020 database system of Shandong University
2020 Database System Experiment 7 of Shandong University
Experiment 8 and 9 of 2020 database system of Shandong University

Write in front

When doing database experiments, you must be calm, so that you can find the errors and correct them. At the same time, if you find that SQL statements always report errors, "you must be wrong, but you just don't know where it is!"

Secondly, the more complex points in SQL statements have been annotated by bloggers. I hope you must read the idea and write it yourself instead of blindly Ctrl+C and Ctrl+V. remember!!

Experiment 4

The main contents of Experiment 4 are as follows:
Master the alter statement and whether it can be used to operate the attributes in the table;

Use of the update... set... where clause;

Processing the string and deleting the corresponding characters in the string;

4-1 list the pub user as student in the following table_ 41 and data are copied to the table test4 of the primary user_ 01, use the alter table statement to add a column to the table: "total score: sum_score".
Use the update statement and use pub.student_course, statistics of "total score";
Idea:
1. First, use alter table test4_01 add sum_score int to add the corresponding attribute column. It should be noted that the attribute column after the add clause also has the type description following this column;
2. Use the update... set... Structure to calculate the total score of each student, and then fill in the sum_score column;

alter table test4_01
	add sum_score int

update test4_01 S
set sum_score =
	(select sum(score)
	from pub.student_course T
	where S.sid = T.sid
	group by sid)

4-2 list the pub user as student in the following table_ 41 and data are copied to the table test4 of the primary user_ 02, use the alter table statement to add the column "average score: avg_score" (1 digit after the decimal point) to the table.
Using pub.student_course, statistics "average score", rounded to one decimal place
Idea:
1. In fact, the idea is similar to 4-1. Add corresponding columns, and then calculate the average score respectively;
2. Keep 1 digit after the decimal point and use the round() function;
It should be noted that AVG_ When defining the score attribute column, because one decimal place is reserved, you need to use numeric (3,1) to set it~~

alter table test4_02
	add avg_score numeric(3, 1)

update test4_02 S
set avg_score = 
	(select round(avg(score), 1)
	from pub.student_course T
	where S.sid = T.sid
	group by sid)

4-3 list the pub user as student in the following table_ 41 and data are copied to the table test4 of the primary user_ 03, use the alter table statement to add a column to the table: "Total Credits: sum_credit".
Use the update statement and use pub.student_course, pub.course, statistics of "total credits";
At this time, it should be noted that credits can be calculated only after passing the grade. Credits can only be calculated once if multiple grades of a course pass.
Idea:
1. Use alter... Add... To add the corresponding attribute column. At the same time, note that the definition of column attribute is int;
2. Pay attention to the prompt in the title "if you pass multiple grades in a course, only one credit will be counted", so we can't simply use score > 60 and sum(credit); The correct method should be to get the highest score of each student in each course. Use this highest score to judge whether it is > 60. If it is greater than 60, credit will be counted once; Since the highest score of each student is unique, we only calculate it once in sum(credit), rather than multiple times as the former.

alter table test4_03
	add sum_credit int

update test4_03 t0
set sum_credit = 
	(select sum(credit)
	from pub.course natural join 
		(select sid, cid, max(score) max_score		-- Get the highest score of each student in each course
		from pub.student_course
		group by sid, cid) t1
	where t0.sid = sid
	and t1.max_score >= 60				-- Use the highest score to judge
	group by sid)

4-4 list the pub user as student in the following table_ 41 and data are copied to the table test4 of the primary user_ 04. Find the corresponding department number did according to the column department name dname to pub.department, and backfill the corresponding department number into the Department name column dname. If there is no corresponding department name in the table, the content in the column dname will remain unchanged.
Idea:
1. First, use create table... as select *... To create pub.student_ copy all data in 41 to test4_04 medium;
2. Then use dname to determine whether the dname is in the pub.department;
3. If yes, update the dname to the corresponding did; Otherwise, it will not be updated;

create table test4_04 as
select *
from pub.student_41

update test4_04 t0
set dname =
	(select did
	from pub.department
	where dname = t0.dname)
where t0.dname in
	(select dname
	from pub.department)

4-5 list the pub user as student in the following table_ 41 and data are copied to the table test4 of the primary user_ 05, use the alter table statement to add four columns to the table: "total score: sum_score", "average score: avg_score", "total credit: sum_credit" and "department No.: did varchar(2)".
(1) Using pub.student_course, pub.course, statistics of "total score";
(2) Using pub.student_course, pub.course, statistics "average score", rounded to 1 decimal place;
(3) Using pub.student_course, pub.course, statistics of "total credits";
(4) To pub.department and pub.department according to department name_ 41, find the corresponding number and fill in the department number. If there is no corresponding department, fill in 00.
Note: after executing update, the data in the query table may change in order, which is normal because the data is out of order in the table. When order is needed, it can be implemented through orderby.
Idea:
1. First, use the create statement to copy tables and data;
2. Then use the alter table statement to add the corresponding column to the copied table; Note that alter cannot be added together, so attribute columns need to be added column by column;
3. Finally, use the update... Set... Clause to update the attribute columns in the table (set can update multiple columns at the same time ~ ~)

It should be noted that when the department number is updated, it is now to find the corresponding department number from two tables instead of one table (it was stuck for a long time / (ㄒ o ㄒ) / ~ ~), and the two tables can be connected through the union. (the code is long because it is a synthesis of the previous problems)

create table test4_05 as
select *
from pub.student_41

alter test4_05
	add sum_score int
-----------------Separate-----------------------
alter test4_05
	add avg_score numeric(3, 1)
-----------------Separate-----------------------
alter test4_05
	add sum_credit int
-----------------Separate-----------------------
alter test4_05
	add did varchar(2)

update test4_05 t0
set
sum_score = 
	(select sum(score)
	from pub.student_course t1
	where t0.sid = t1.sid
	group by sid), 
avg_score =
	(select round(avg(score), 1)
	from pub.student_course t2
	where t0.sid = t2.sid
	group by sid),
sum_credit = 
	(select sum(credit)
	from pub.course natural join 
		(select sid, cid, max(score) max_score
		from pub.student_course
		group by sid, cid) t1
	where t0.sid = sid
	and t1.max_score >= 60
	group by sid),
did = 
case
	when dname in
	(	(select dname
		from pub.department)
		union
		(select dname
		from pub.department_41)	)
	then
	(select did
	from 
	(	(select dname, did
		from pub.department)
		union
		(select dname, did
		from pub.department_41)	)
	where dname = t0.dname)
else '00'
end

4-6 set the student under the pub user_ 42 and data are copied to the table test4 of the primary user_ 06, sort out the data in the table and repair those non-standard data: eliminate all spaces in the name column;
Idea:
1. Use create to copy tables and data;
2. To delete spaces, we can use the function replace(string, target_str, replace_str);
  
  The first parameter string represents the string to be modified; The second parameter is target_str represents the character to be modified in the string (in order to find the character); The third parameter is replace_str indicates that target needs to be_ STR replaces the character;
  
  In this problem, we can use the replace function as follows: replace(name, '', ''), find the space and delete it; (the translate() function has a similar function!)

create table test4_06 as
select *
from pub.student_42

update test4_06
set name = replace(name, ' ', '')

4-7 set the student under the pub user_ 42 and data are copied to the table test4 of the primary user_ 07, sort out the data in the table and repair those non-standard data: standardize the gender column (it is necessary to determine which gender data are non-standard, that is, those different from most are non-standard);
Idea:
1. Use the create clause to copy the table and the data in the table;
2. To see what is non-standard data, we can first use:
  select distinct sex, count(sex) from test4_07 group by sex
  To view the so-called "non-standard data". It is found that the non-standard data is the sex value with the word "sex", and there may be spaces before and after some sex values;
3. For the specific deletion method, we can still use the replace() function;

create table test4_07 as
select *
from pub.student_42

update test4_07
set sex = 
case
	when sex like '%male%' then 'male'		-- %Indicates that one or more characters are omitted
	when sex like '%female%' then 'female'
	else sex
End

4-8 set the student under the pub user_ 42 and data are copied to the table test4 of the primary user_ 08, sort out the data in the table and repair those non-standard data: standardize the class column (determine which classes are non-standard first).
Idea:
1. First, use create to copy the table and the data in the table;
2. Then use
  select distinct class, count(class) from test4_08 group by class
  Let's see which class data are non-standard data. It is found that the non-standard class data is followed by the word "level". We only need to use the replace() function to remove it;

create table test4_08 as
select *
from pub.student_42

update test4_08
set class = replace(class, 'level', '')

4-9 set the student under the pub user_ 42 and data are copied to the table test4 of the primary user_ 09, sort out the data in the table and repair those non-standard data: if the age is null, set the student's age according to the date of birth (age up to 2012, i.e. age = 2012 year of birth), and do not change if the age is not null.
Idea:
1. First, use create to copy the table and the data in the table;
2. Then use the update and where decision statements to update the age column;
3. The extract() function mentioned in Experiment 3 was also used~~

create table test4_09 as
select *
from pub.student_42

update test4_09
set age = 2012 - extract(year from birthday)
where age is null

4-10 set the student under the pub user_ 42 and data are copied to the table test4 of the primary user_ 10, sort out the data in the table and repair those non-standard data:
(1) Eliminate all spaces in the name column;
(2) Eliminate all spaces in the Department name column;
(3) Standardize the gender column (first determine which gender data are not standardized, that is, those different from most are not standardized);
(4) Standardize the class column (you need to determine which classes are not standardized first).
(5) If the age is null, set the student's age according to the birth date (the age up to 2012, i.e. age = 2012 year of birth). If the age is not null, do not change it.
Idea:
1. It is the synthesis of all the previous problems. Since multiple columns can be set in set at the same time, they can be connected;

create table test4_10 as
select *
from pub.student_42

update test4_10
set name = replace(name, ' ', ''),
dname = replace(dname, ' ', ''), 
sex = 
case
	when sex like '%male%' then 'male'
	when sex like '%female%' then 'female'
	else sex
end,
class = replace(class, 'level%', ''),
age =
case 
	when age is null then 2012 - extract(year from birthday)
	else age
end

Emphasize again: you must practice yourself after understanding your ideas~~
Please correct the problem!

Posted by metuin on Sat, 02 Oct 2021 17:12:40 -0700

Programmer Group

Experiment 4 of 2020 database system of Shandong University

What's more

Write in front

Experiment 4

Hot Keywords