Database Lab Report

Author: Ching

Original address: http://blog.csdn.net/qingdujun/article/details/29028363

developing server-side applications using SQL Server first, the experimental category
Comprehensive experiment
ii. purpose of the experiment
Familiar with the development of backend server-side applications.
third, the experimental environment
SQL Server family of database management systems
Iv. contents of the experiment
For the student-curriculum database, write the stored procedure to complete the following functions:
1. Browse the student records of a department by article by article (using cursors);
2. Statistics of the distribution of the results of any one course, that is, according to the number of statistics in each score section;
3. To count the average scores and rankings of each student;
4. The students ' selection grades were changed from percentile to hierarchical (i.e. A, B, C, D, E) display.
Five, the experimental requirements
Submit the source program and identify the necessary comments. Ensure that the program compiles and runs correctly and writes the experiment report carefully.
VI. Experimental Process
1. Create a database and data table ... The specific code and the results created are as shown in 1.
Note: The following 3 tables, all visual creation, table initialization content as follows (modeled after the textbook input values), the relationship
The main code is underlined.

Student table: Student (Sno, sname,ssex,sage,sdept)

Timetable: Course (Cno, Cname,ccredit)

Student Timetable: SC (sno,cno, Grade)

2. Create a stored procedure 1 complete ... function, specific code and running results as shown in R1.

Specific Code 1:

--1. Browse student records for a department (using cursors)--drop PROCEDURE lookdept-Delete stored procedure-----------stored procedure:----------------CREATE PROCEDURE lookdept Dept NCHAR (2)--dept is the parameter Asdeclare @sno nchar (a), @sname nchar (a), @sex the NCHAR (), @age int; --Define declare SP CURSOR FOR--description cursor Select Sno,sname,ssex, Sage from Student WHERE sdept = @dept; --The query results are pushed into the buffer and the SELECT statement is not executed at this time open SP; --Opens the cursor, at which point the cursor points to the first record fetch NEXT from the SP into @sno, @sname, @sex, @age; --Push cursor while @ @fetch_status = 0-Note: = 0 indicates successful execution beginprint @[email protected][email protected]+convert (nchar, @age) [email Protected]; --show fetch NEXT from SP into @sno, @sname, @sex, @age; --Push cursor endclose sp; --Close the cursor deallocate sp; --delete cursor------------perform operation:----------------EXEC lookdept ' CS as follows; --Query Computer department student Records

Execution result R1:

Figure: R1

3. Create a stored procedure 2 complete ... function, specific code and operating results as shown in 3.
Specific Code 2:

--2. Statistics of the distribution of the results of any course, that is, according to the statistics of each fractional segment;--drop PROCEDURE scoresec-Delete stored procedure-----------stored procedure: The following----------------CREATE PROCEDURE Scoresec @cname nchar (--cname) is a parameter Asdeclare @cno nchar (15);  --Define variable SELECT @cno =cno--Find course number from Coursewhere [email protected]select @cname Course name from the course name, COUNT (case is grade<60 then 1 END) ' 60 points below ', count (case when grade>=60 and grade<70 then 1 END) ' 60 Minutes-70 minutes ', count (case when grade>=70 and grade< ; 1 END) ' 70 points-80 points ', count (case grade>=80 and grade<90 then 1 END) ' 80 minutes-90 minutes ', count (case when grade>=90 Then 1 END) ' 90 + ' from scwhere Cno = @cnoGROUP by Grade;------------do the following:------------------EXEC scoresec ' database '

Execution result R2:

Figure: R2

4. Create a stored procedure 3 complete ... function, specific code and operating results as shown in 4.
Specific Code 3:

--3. To count each student's average score and rank--drop PROCEDURE avgrank--Delete stored procedure-----------stored procedure:----------------CREATE PROCEDURE Avgrankasselect below Sname name, Student.sno number, AVG (Grade) average score from Sc,studentwhere SC. Sno = student.sno--Name query group by SC. Sno,sname,student.sno--group order by average score desc--Descending rank------------perform the following actions:------------------EXEC Avgrank

Execution result R3:

Figure: R3

5. Create a stored procedure 4 complete ... function, specific code and operating results as shown in 5
Specific Code 4:

--4. The students ' selection grades were changed from percentile to hierarchical (i.e. A, B, C, D, E) display. --drop PROCEDURE Rank100-Delete stored procedure-----------stored procedure: The following----------------CREATE PROCEDURE rank100asselect Sname name, SC.  Sno, Cname course name, (Casewhen grade<60 Then ' E ' when grade>=60 and grade<70 then ' D ' when grade>=70 and grade<80 Then ' C ' when the grade>=80 and grade<90 then ' B ' is grade>=90 then ' A ' END ' level from Sc,student,coursewhere SC. Sno = Student.sno and SC. Cno = course.cno--Name, course name Query------------do the following:------------------EXEC Rank100

Execution result R4:

Figure: R4

Vii. Summary of the experiment

These 2 weeks have been busy with all sorts of things, and database operations have been dragged on until this morning to start writing. According to the requirements of the job, down, step by step, the function is all realized. The function of the implementation period, access to a lot of Internet information, of course, textbooks have to go back and forth several times, no way, they are weak can. During the main lookup of the keyword SQL, case, when, AVG, of course, there are cursors. During the completion period, the main problems encountered are the following:

storage structure 1: Print does not show anything
Resolution: It turns out that the SELECT statement at my cursor description is wrong.
Error:

DECLARE SP Cursor FOR--describes the cursor select @Sno, @Sname, @Ssex, @Sage from Student WHERE sdept = @dept; --The query results are pushed into the buffer and the SELECT statement is not executed at this time

That's right:

DECLARE SP Cursor for--description cursor Select Sno,sname,ssex, Sage from Student WHERE sdept = @dept; --The query results are pushed into the buffer and the SELECT statement is not executed at this time

Storage structure 2: The main problem is how the code simplifies the problem. I searched the internet for the use of case-when and so on.
Main Learning: (Note: The following reference Eshizhan of the blog Park)
Case has 2 main forms of expression
1) Simple case function:

Case Sexwhen ' 1 ' then ' Male ' when ' 2 ' then ' women ' else ' other ' END

2) Case search function:

Casewhen sex = ' 1 ' Then ' male ' when sex = ' 2 ' Then ' women ' else ' other ' END

Storage Structure 3: Mainly aggregation functions, grouping problems.
Because I want to display the name, the number, the average score, so the following code appears:

SELECT Sname Name, student.sno number, AVG (Grade) Average score

The problem arises: parsing the statement is fine, but when executing the statement, always prompt for such error "The column ' Student.sname ' in the select list is not valid because the column is not contained in an aggregate function or a GROUP BY clause. ”
Workaround:
So when I grouped, it was all included, wit ah.

GROUP by SC. Sno,sname,student.sno--Group

Storage Structure 4: This is basically not a problem, it's easy.

ideas and Suggestions : In fact, if there is time, I would like to do an interface version of the query ~ ~ ~ or time is very tight AH!!! You can only wait until the class is set up. In addition, because my computer is installed on the WPS, in order to avoid the use of Office open, format changes affect reading, I will document into. pdf format, easy to read.

This report SQL Server database download: Http://pan.baidu.com/s/1gdABS4N Password: 14io