Posting background
Because I learned the principles of spatial databases on the computer, I had the opportunity to create my own database and connect it to complete the questions. When I was working on the database principles before, I completed a preliminary understanding and use of Oracle, but the uninstallation of Oracle and the password setting still left a deep impression on me. Therefore, postgresql is used to complete this course. The answers given by the teacher are basically based on mysql and oracle, so I record the answers I completed for the first time on the computer here to facilitate the reference of other people in need or junior students.
Question 1
Read Chapter 9 of “Spatial Database Experiment Tutorial” to establish a university database. Enter the information of at least 4 departments in the database; each department must have at least 150 teacher information, 200 student information, and 200 course information; each student must take at least 20 elective courses.
With the previous foundation of Oracle, the establishment of the University database is very simple. It just so happened that the teacher gave the code, which is quoted directly here.
/*use University*/;
drop table if exists SC ;
drop table if exists TC;
drop table if exists Course;
drop table if exists Teacher;
drop table if exists Student;
drop table if exists Department;
/*Department information table Department (Dno,Dname,Daddress)*/
create table Department(
Dno int,
Dname char(50),
Daddress char(50),
primary key (Dno)
);
insert into Department(Dno,Dname,Daddress) values(1,'School of Earth Sciences','Main Building East');
insert into Department(Dno,Dname,Daddress) values(2,'Resource College','Main Building West');
insert into Department(Dno,Dname,Daddress) values(3,'Caihua College','Caihua Building');
insert into Department(Dno,Dname,Daddress) values(4,'Environmental College','Wenhua Building');
insert into Department(Dno,Dname,Daddress) values(5,'College of Engineering','Hydraulic Engineering Building');
insert into Department(Dno,Dname,Daddress) values(6,'School of Geophysics and Spatial Information','Geophysical Building');
insert into Department(Dno,Dname,Daddress) values(7,'School of Mechanical and Electronic Information','Teaching Second Floor');
insert into Department(Dno,Dname,Daddress) values(8,'School of Economics and Management','Economic and Management Building');
insert into Department(Dno,Dname,Daddress) values(9,'School of Foreign Languages','North First Floor');
insert into Department(Dno,Dname) values(10,'School of Information Engineering');
insert into Department(Dno,Dname,Daddress) values(11,'School of Mathematics and Physics','Basic Committee Building');
insert into Department(Dno,Dname,Daddress) values(12,'Jewelry College','Jewelry Building');
insert into Department(Dno,Dname,Daddress) values(13,'Politics and Law School','Politics and Law Building');
insert into Department(Dno,Dname,Daddress) values(14,'School of Computer Science','North First Floor');
insert into Department(Dno,Dname) values(15,'College of Distance and Continuing Education');
insert into Department(Dno,Dname) values(16,'School of International Education');
insert into Department(Dno,Dname,Daddress) values(17,'Sports Department','Gymnasium');
insert into Department(Dno,Dname,Daddress) values(18,'School of Art and Media','Yi Chuan Building');
insert into Department(Dno,Dname,Daddress) values(19,'Marxist College','Guardian Building');
insert into Department(Dno,Dname,Daddress) values(20,'Jiangcheng College','Jiangcheng Campus');
/*Student information table Student (Sno, Sname, Ssex, Sage, Dno)*/
create table Student(
Sno char(11) ,
Sname char (8),
Sex char(2),
Sage int,
Dno int,
primary key (Sno),
foreign key (Dno) references Department(Dno)
);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000231','Lu Yan','M',18,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091004391','Yan Rong','M',19,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091001598','Wang Haitao','M',20,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091003085','Yuan Heng','M',18,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000863','Meng Fei','M',17,14);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000934','Luo Zhenjun','M',19,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000961','Zeng Xuejun','F',18,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000983','Baxiang','M',19,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091001175','Zhou Lei','M',18,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091001261','Ma Huan','M',17,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091001384','Chen Liang','M',20,8);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20081003492','Yijiaxin','M',19,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20081001197','Li Zicong','M',17,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20081001266','Cai Jingxue','F',19,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20081001888','Zhao Linyun','F',20,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20091000481','Jiangbei','M',17,5);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000199','Sun Zhongxiao','M',18,11);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000424','Yang Guang','M',17,11);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000481','Zhang Yongqiang','M',16,11);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000619','Chen Bo','M',20,11);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000705','Tang Wenpan','M',18,11);
insert into Student(Sno, Sname, Ssex, Sage, Dno) values('20101000802','Suhain','M',17,11);
/*Teacher information table Teacher (Tno, Tname, Ttitle, Dno)*/
create table Teacher(
Tno int primary key,
Tname char (8),
Ttitle char (8),
Dno int,
foreign key (Dno) references Department(Dno)
);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(1,'He Xiaofeng','Associate Professor',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(2,'Liu Gang','Professor',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(3,'Li Xingxing','Professor',11);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(4,'Weng Pingzheng','Lecturer',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(5,'Li Chuanchuan','Lecturer',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(6,'Wang Yuanyuan','Lecturer',14);
insert into Teacher(Tno, Tname, Ttitle, Dno) values(7,'Kong Xiafang','Associate Professor',14);
/*Course information table Course (Cno, Cname, Cpno, Ccredit)*/
create table Course(
Cno int primary key ,
Cname char (50),
Cpno int ,
CCredit int,
foreign key(Cpno) references Course(Cno)
);
insert into Course(Cno, Cname, Ccredit) values(2,'Advanced Mathematics',8);
insert into Course(Cno, Cname, Ccredit) values(6,'C language programming',4);
insert into Course(Cno, Cname, Ccredit) values(7,'University Physics',8);
insert into Course(Cno, Cname, Ccredit) values(8,'University Chemistry',3);
insert into Course(Cno, Cname, Ccredit) values(10,'Software Engineering',2);
insert into Course(Cno, Cname, Ccredit) values(12,'A Brief History of the United States',2);
insert into Course(Cno, Cname, Ccredit) values(13,'General History of China',6);
insert into Course(Cno, Cname, Ccredit) values(14,'College Chinese',3);
insert into Course(Cno, Cname, Cpno, Ccredit) values(5,'data structure',6,4);
insert into Course(Cno, Cname, Cpno, Ccredit) values(4,'operating system',5,4);
insert into Course(Cno, Cname, Cpno, Ccredit) values(1,'Database Principle',5,4);
insert into Course(Cno, Cname, Cpno, Ccredit) values(3,'Information System',1,2);
insert into Course(Cno, Cname, Cpno, Ccredit) values(9,'Assembly language',6,2);
insert into Course(Cno, Cname, Cpno, Ccredit) values(11,'spatial database',1,3);
/*Student course selection table SC (Sno, Cno, Grade)*/
create table SC(
Sno char(11),
Cno int,
Grade int,
primary key(Sno, Cno),
foreign key(Sno) references Student(Sno),
foreign key (Cno) references Course(Cno)
);
insert into SC values('20091003085',1,90);
insert into SC values('20091000863',1,98);
insert into SC values('20091000934',1,89);
insert into SC values('20091000961',1,85);
insert into SC values('20081001197',1,79);
insert into SC values('20081001266',1,97);
insert into SC values('20081001888',1,60);
insert into SC values('20091000481',1,78);
insert into SC values('20101000199',1,65);
insert into SC values('20101000424',1,78);
insert into SC values('20101000481',1,69);
insert into SC values('20091000863',6,90);
insert into SC values('20091000934',6,90);
insert into SC values('20091000961',6,87);
/*Teacher’s teaching schedule TC (Tno, Cno, Site)*/
create table TC(
Tno int,
Cno int,
Site char(50),
primary key (Tno,Cno),
foreign key(Tno) references Teacher(Tno),
foreign key (Cno) references Course(Cno)
);
insert into TC values(1,1,'Teaching the first floor 407');
insert into TC values(1,6,'Teaching the first floor 307');
insert into TC values(2,10,'Teaching 2nd Floor 217');
insert into TC values(3,2,'Jiao 3rd Floor 507');
insert into TC values(4,5,'Jiao 3rd Floor 208');
insert into TC values(6,3,'Comprehensive Building 207');
insert into TC values(7,4,'Teaching 2nd Floor 817');
insert into TC values(5,9,'Teaching First Floor 207');
Topic 2
Use PL/SQL language programming to achieve the following tasks: 1) Define a stored procedure Increase_Grade with input parameters to increase the grades of all students with course number 1 by 5%; require the course number to be passed in as a stored procedure parameter, and call the stored procedure . 2) Define a stored procedure Average_Student_Grade with input and output parameters to calculate the average grade of all elective courses for a student.
After establishing the database, use postgresql to directly create the stored procedure. In postgresql, the establishment of stored procedures and functions is as shown in the following code:
create or replace function name(paramNAME dataType)
The difference between the two is that stored procedures have return values and functions have no return values. You can learn more about it later, but this is all I know at this time. The specific code is given below. The meaning of the code needs to be understood by yourself, so I will not go into details here.
CREATE OR REPLACE FUNCTION Average_Student_Grade
(
IN student_id char(11)
)
RETURNS TABLE (average_grade DECIMAL(10,2))
LANGUAGE plpgsql
AS $$
BEGIN
RETURN QUERY
SELECT AVG(grade)
FROM sc
WHERE sno = student_id;
END;
$$;
DO$$
DECLARE
average DECIMAL(10,2);
BEGIN
SELECT average_grade INTO average
FROM Average_Student_Grade('20091003085');
RAISE NOTICE 'Average Grade: %', average;
END
$$;
CREATE OR REPLACE FUNCTION Increase_Grade(course_id INT)
RETURNS VOID AS $$
BEGIN
UPDATE sc
SET Grade = Grade * 1.05
WHERE Cno = course_id;
END;
$$ LANGUAGE plpgsql;
SELECT Increase_Grade(1);
Topic 3: Use programming languages (Java, Python, C#, C++) to develop a database application based on JDBC, connect to the University database, and output the total score of each student’s elective courses
1. Load driver
This is the question that I encountered the most difficulties with, so I recorded the process in detail and downloaded the idea first. Then download the driver. The address is https://jdbc.postgresql.org/download/.
I downloaded version 42.6.0. Don’t move after downloading, just create a new project in idea. 
After the establishment is completed, as shown in the figure below:
Then add the downloaded driver to the project.
Click file->project structure. Then enter modules.
Click the plus sign, click the first one. Select the driver you downloaded.
Click the small blue box, click apply, and click okj to complete the configuration.
2. Connect to database
Click database in the upper right corner, then click the plus sign and select postgresql in data source.
Then fill in the configuration. If it is a local machine, you do not need to change anything. Just fill in the username and password. If no other operations are performed, user can fill in postgres. Here I show the initial configuration interface and my own database and configuration. Among them, 10.22 is the database where the university is located.
Initial configuration interface:
My database configuration and idea configuration interface.
Click Test Connection and it will show success. The first connection requires downloading the driver file. Click Download to complete the download. If you want to display all the content in the database, select Schemas, check the data, select Show temolete databases below, and click apply and ok.
3. Code
Then there is the connection code. The teacher has already given it. Just change the imported package, class name, url, username and password to your own. The following code is modified by me. If you want to use it, just change the url, username and password.
import org.postgresql.Driver;
import java.lang.reflect.InvocationTargetException;
import java.sql.*;
public class Main
{
public static void main( String[] args )
{
String sql = "select sno , sum(grade) sg from SC group by sno";
Connection connection = getPostgreSQLConnection();
try {
Statement statement =connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
String sno = resultSet.getString("sno");
double sg = resultSet.getDouble("sg");
System.out.println(sno);
System.out.println(sg);
}
}
catch (SQLException e){
e.printStackTrace();
}
}
/**
* Get PostgreSQL Connection
* @return
*/
public static Connection getPostgreSQLConnection(){
Connection conn=null;
try {
Class.forName("org.postgresql.Driver");//Find the class where the PostgreSQL driver is located
String url="jdbc:postgresql://localhost:5432/10.22"; //URL address
String username="postgres";
String password="123";
conn= DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
}
Run to complete this question.
Topic 4
Download the China 2020 Administrative Division Coding Table, construct the data table guo_adcode, use PL/SQL to develop a function, enter the name of an administrative region, and return the names of all lower-level administrative regions of the administrative region.
The teacher has given the code for building the table. It is worth mentioning that I imitated the teacher to create a pattern. Nothing else is different.
CREATE SCHEMA IF NOT EXISTS chinamap;
Question code:
CREATE OR REPLACE FUNCTION "chinamap"."province_sub_adnames"(pname VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
pcode VARCHAR;
flag BOOLEAN := TRUE;
r VARCHAR := '';
rec RECORD;
BEGIN
SELECT "code" INTO pcode
FROM guo_adcode
WHERE guo_adcode.name LIKE CONCAT(pname, '%');
pcode := SUBSTRING(pcode FROM 1 FOR LENGTH(pcode) - 4);
-- Dealing with municipalities
IF pcode = '11' OR pcode = '12' OR pcode = '31' OR pcode = '50' THEN
pcode := CONCAT(pcode, '%');
ELSE
pcode := CONCAT(pcode, '__00');
END IF;
--Dynamic SQL
FOR rec IN EXECUTE 'SELECT name FROM chinamap.guo_adcode WHERE code LIKE ''' || pcode || '''' LOOP
IF flag THEN
flag := FALSE;
CONTINUE;
END IF;
r := CONCAT(r, rec.name);
END LOOP;
RETURN r;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION "chinamap"."city_sub_adnames"(pname VARCHAR)
RETURNS VARCHAR AS $$
DECLARE
pcode VARCHAR;
cur CURSOR (pcode VARCHAR) FOR SELECT name FROM chinamap.guo_adcode WHERE code LIKE pcode ORDER BY code;
rec RECORD;
r VARCHAR := '';
pname1 VARCHAR := '%' || pname;
flag BOOLEAN := TRUE;
BEGIN
SELECT "code" INTO pcode
FROM chinamap.guo_adcode
WHERE chinamap.guo_adcode.name LIKE CONCAT(pname1, '%');
pcode := SUBSTRING(pcode FROM 1 FOR LENGTH(pcode) - 2);
-- Dealing with municipalities
IF pcode = '1100' OR pcode = '1200' OR pcode = '3100' OR pcode = '5000' THEN
RAISE NOTICE 'Municipality is a provincial administrative region, please call province_sub_adnames';
RETURN '';
ELSE
pcode := CONCAT(pcode, '%');
OPEN cur(pcode);
LOOP
FETCH cur INTO rec;
EXIT WHEN NOT FOUND;
IF flag THEN
flag := FALSE;
CONTINUE;
END IF;
r := CONCAT(r, rec.name);
END LOOP;
CLOSE cur;
END IF;
RETURN r;
END;
$$ LANGUAGE plpgsql;
Problems you may encounter when using postgresql.
One is to right-click the database and select create script to write code.
The other is, if the following problem occurs, enter services.msc and start a service, which is given at the end.
The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. PostgreSQL skill treeSQL advanced skillsRecursive query 7038 people are learning the system