Oracle Notes (11) Build table, update, query comprehensive exercises

Oracle Notes (11) Build table, update, query comprehensive exercises

There is a database of information about a student sports meeting, and the following tables are saved:

• Athlete Sporter (athlete number Sporterid, athlete name name, athlete sex sex, Affiliation department number Department)
• Item Item (Project number itemid, project name ItemName, Project match location)
• Score Grade (athlete ID, item number itemid, points mark)

Use the SQL statement to complete the following functions:

1, build the table, and add the constraint on the corresponding fields;

• Define the primary key and foreign key constraints for each table;
• The name and affiliation of the athlete cannot be empty;
• Points to the null value, or 6,4,2,0, respectively, representing the first, two, three and other ranking points, note that the ranking can have a side-by-side position, the following rankings do not advance, for example, if there are two tied first, there is no second.

2. Insert the specified data into the table:

Athletes

1001, Li Ming, male, computer department

1002, Zhang San, male, Mathematics department

1003, John Doe, male, computer department

1004, Wang Er, male, Department of Physics

1005, Li Na, female, psychology department

1006, Sun-hee, female, Mathematics department)

Project

X001, man 5-kilometer, a playground

X002, Men's javelin, a playground

X003, men's long jump, two playgrounds

X004, women's high jump, two playgrounds

X005, women 3-kilometer, three playgrounds)

Points

1001,x001,6

1002,x001,4

1003,x001,2

1004,x001,0

1001,x003,4

1002,x003,6

1004,x003,2

1005,x004,6

1006,x004,4

1003,x002,6

1005,x002,4

1006,x002,2

1001,x002,0)

3, complete the following query requirements:

A, to find out the current total points of the highest name, and its integral.

B, find out the name of each project and its champion in a playground.

C. Find out the names of other students who have participated in all the projects attended by Zhang San.

D, after the Zhang San because of the use of illicit drugs, the results are recorded 0 points, please make corresponding changes in the database.

E, after consultation with the organizing Committee, the need to delete women to increase the competition project.

I. Establishment of the table

DROP TABLE grade PURGE;

DROP TABLE Sporter PURGE;

DROP TABLE Item PURGE;

CREATE TABLE Sporter (

Sporterid Number (4) PRIMARY KEY,

Name VARCHAR2 () not NULL,

Sex VARCHAR2 CHECK(Sex in (' Male ', ' female ')),

Department VARCHAR2 () not NULL

);

CREATE TABLE Item (

Itemid VARCHAR2 (4) PRIMARY KEY,

ItemName VARCHAR2 () not NULL,

Location VARCHAR2 (+) not NULL

);

CREATE TABLE Grade (

Sporterid Number (4) REFERENCES Sporter (Sporterid) on DELETE CASCADE,

Itemid VARCHAR2 (4) REFERENCES item (itemid) on DELETE CASCADE,

Mark Number (1) CHECK (mark in (0,2,4,6))

);

And in Oracle, consider the problem with the Recycle Bin.

Ii. increasing the number of data

1. Increase athlete Data:

INSERT into Sporter (sporterid,name,sex,department) VALUES (1001, ' Li Ming ', ' Male ', ' computer Department ');

INSERT into Sporter (sporterid,name,sex,department) VALUES (1002, ' Zhang San ', ' Male ', ' math Department ');

INSERT into Sporter (sporterid,name,sex,department) VALUES (1003, ' John Doe ', ' Male ', ' computer Department ');

INSERT into Sporter (sporterid,name,sex,department) VALUES (1004, ' King II ', ' Male ', ' physics Department ');

INSERT into Sporter (sporterid,name,sex,department) VALUES (1005, ' Li Na ', ' female ', ' psychology Department ');

INSERT into Sporter (sporterid,name,sex,department) VALUES (1006, ' Sun-hee ', ' female ', ' math Department ');

2. Increase Project Data

INSERT into Item (itemid,itemname,location) VALUES (' x001 ', ' Man 5-kilometer ', ' one playground ');

INSERT into Item (itemid,itemname,location) VALUES (' x002 ', ' Men's javelin ', ' one playground ');

INSERT into Item (itemid,itemname,location) VALUES (' x003 ', ' Men's long jump ', ' two playgrounds ');

INSERT into Item (itemid,itemname,location) VALUES (' x004 ', ' Women's high jump ', ' two playgrounds ');

INSERT into Item (itemid,itemname,location) VALUES (' x005 ', ' Women 3-kilometer ', ' three playgrounds ');

3, increase the athlete's project achievement

INSERT into Grade (Sporterid,itemid,mark) VALUES (1001, ' x001 ',6);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1002, ' x001 ',4);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1003, ' x001 ',2);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1004, ' x001 ',0);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1001, ' x003 ',4);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1002, ' x003 ',6);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1004, ' x003 ',2);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1005, ' x004 ',6);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1006, ' x004 ',4);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1003, ' x002 ',6);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1005, ' x002 ',4);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1006, ' x002 ',2);

INSERT into Grade (Sporterid,itemid,mark) VALUES (1001, ' x002 ',0);

Third, data operation

1, to find out the current total points of the highest name, and its integral.

1. Determine the data table to be used:

• Sporter table: Find out the name of the department;
• Grade table: Find points;

2, determine the known correlation field: Sporter.sporterid=grade.sporterid;

First step: combine the Sporter table and the Grade table

SELECT S.department,g.mark

From Sporter S,grade g

WHERE S.sporterid=g.sporterid;

The second step: the above is to find out each system for the project to obtain the integral, then the above query grouping, grouped by the name of the system.

SELECT S.department,sum (G.mark)

From Sporter S,grade g

WHERE S.sporterid=g.sporterid

GROUP by S.department;

Now that you know the results of each department, there are two ways to find the information with the highest total points:

• Procedure one: Regardless of the problem of the same integral, all data is sorted from highest to lowest, taking the first data.

SELECT * FROM (

SELECT s.department,sum (G.mark) SUM

From Sporter S,grade g

WHERE S.sporterid=g.sporterid

GROUP by S.department

ORDER by Sum DESC)

WHERE rownum=1;

• Procedure two: Consider the problem of the same integral, you must first make the grouping function nesting, to find out the highest integral is how much, and then use this content and the previous packet filter.

SELECT s.department,sum (G.mark) SUM

From Sporter S,grade g

WHERE S.sporterid=g.sporterid

GROUP by S.department

Having SUM (g.mark) = (

SELECT MAX (SUM (g.mark)) sum

From Sporter S,grade g

WHERE S.sporterid=g.sporterid

GROUP by S.department);

2. Find out the name of each project and its champion in a playground.

1. Determine the data table to be used:

• Sporter table: The name of the athlete;
• Item table: project name;
• Grade Table: The information of the championship depends on the score calculation;

2. Determine the known associated fields:

• Athlete and score: Sporter.sporterid=grade.sporterid;
• Projects and accomplishments: Item.itemid=grade.itemid;

The first step: determine the ID of a project on a playground

SELECT itemid from item WHERE location= ' a playground ';

The second step: to find the results of the championship, because each project has the title score of each project

SELECT I.itemid,max (G.mark) MAX

From Item I,grade g

WHERE i.location= ' A playground ' and i.itemid=g.itemid

GROUP by I.itemid;

The third step: According to this score, to find the corresponding athlete's name

SELECT S.name,g.itemid,temp.max

From Sporter S,grade G, (

SELECT i.itemid Iid,max (G.mark) MAX

From Item I,grade g

WHERE i.location= ' A playground ' and i.itemid=g.itemid

GROUP by I.itemid) Temp

WHERE S.sporterid=g.sporterid and Temp.iid=g.itemid and G.mark=temp.max;

Fourth Step: find the project name and introduce the item table

SELECT S.name,g.itemid,temp.max,i.itemname

From Sporter S,grade G, (

SELECT i.itemid Iid,max (G.mark) MAX

From Item I,grade g

WHERE i.location= ' A playground ' and i.itemid=g.itemid

GROUP by I.itemid) Temp,item I

WHERE S.sporterid=g.sporterid and Temp.iid=g.itemid and G.mark=temp.max

and G.itemid=i.itemid and Temp.iid=i.itemid;

3. Find out the names of the other students who have participated in all the projects attended by Zhang San.

1. Determine the data table to be used:

• Sporter table: Zhang San's athlete ID;
• Grade table: The ID of the item according to which it was found;
• Sporterid: According to the grade table and the previous Sporter table to determine the project ID, find the athlete's number and name;

2, determine the known correlation field: Sporter.sporterid=grade.sporterid;

First step: determine the athlete number of Zhang San

SELECT S.sporterid

From Sporter S

WHERE s.name= ' Zhang San ';

Step Two: find the number of the participating item according to the athlete's number

SELECT G.itemid

From Grade G

WHERE g.sporterid= (

SELECT S.sporterid

From Sporter S

WHERE s.name= ' Zhang San ');

Step Three: modify the above query to find all the athlete's number

SELECT G.sporterid

From Grade G

WHERE G.itemid in (

SELECT G.itemid

From Grade G

WHERE g.sporterid= (

SELECT S.sporterid

From Sporter S

WHERE s.name= ' Zhang San '));

Fourth Step: find the athlete's name according to the athlete's number

SELECT Name

From Sporter

WHERE Sporterid in (

SELECT G.sporterid

From Grade G

WHERE G.itemid in (

SELECT G.itemid

From Grade G

WHERE g.sporterid= (

SELECT S.sporterid

From Sporter S

WHERE s.name= ' Zhang San ')))

And Name<> ' Zhang San ';

4, after the Zhang San because of the use of illegal drugs, the results are recorded 0 points, please make corresponding changes in the database.

Now just know the name of Zhang San, but do not know the athlete number, and the results need to be updated in the grade table by the athlete number.

UPDATE grade SET mark=0 WHERE sporterid= (

SELECT Sporterid from Sporter WHERE name= ' Zhang San ');

5. After consultation with the organizing Committee, the women's high jump competition item needs to be deleted.

The corresponding scores should also disappear after the item is deleted, and the cascade Delete has been configured before, so delete the parent table directly.

DELETE from item WHERE itemname= ' Women's high jump ';

Oracle Notes (11) Build table, update, query comprehensive exercises