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 sportername, 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 constraints to the corresponding fields

2. Enter 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 Li, 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, woman 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

）

Complete the following query requirements:

|-Find out the name of the system with the highest total points at present and its integral

|-Find out the name of each project and the title of the champion in a playground

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

|-After checking Zhang San because of the use of illegal drugs, the results are recorded as 0 points, please make the corresponding changes in the database

|-The women's high jump program needs to be deleted after consultation with the organizing committee

--Delete data table

drop table Sporter Purge;

drop table item purge;

drop table grade purge;

--Create a data table

CREATE TABLE Sporter (

Sporterid Number (4),

Name VARCHAR2 () NOT NULL,

Sex VARCHAR2 (20),

Department VARCHAR2 (20),

Constraint Pk_sporterid primary KEY (Sporterid)

);

CREATE TABLE Item (

Itemid varchar2 (4),

ItemName varchar2 () NOT NULL,

Location VARCHAR2 (+) NOT NULL,

Constraint Pk_itemid primary key (Itemid)

);

CREATE TABLE Grade (

Sporterid Number (4),

Itemid varchar2 (4),

Mark Number (1),

Constraint Fk_sporterid foreign KEY (Sporterid) references Sporter (sporterid) on DELETE CASCADE,

Constraint Fk_itemid foreign key (Itemid) references item (ITEMID) on DELETE cascade

);

--Increase test data--athletes

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 ', ' mathematics 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 Li ', ' female ', ' mathematics Department ');

--Add test data--project

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 ');

--Increase test data--score

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);

--Things to submit

Commit

Data manipulation

1. Find the name of the system with the highest total points at present and its integral

   Determine the data table to use:

|-grade Table: Statistics total points

|-sporter Table: System name

Determine the known associated fields:

|-Athletes and grades: Sporter.sporterid=grade.sporterid

The first step: Change the Query method, the name of each department and the results achieved

Select S.department,g.mark

From Sporter S,grade g

where S.sporterid=g.sporterid;

The second step: the above query has duplicate data, so you can group directly, using the sum () function to count the total.

Select S.department,sum (G.mark)

From Sporter S,grade g

where S.sporterid=g.sporterid

Group BY S.department;

The third step: in order to find the highest results, so long need to nest statistical functions, and statistical functions are nested after nesting does not allow any fields, including grouping fields.

Select Max (sum (g.mark))

From Sporter S,grade g

where S.sporterid=g.sporterid

Group BY S.department;

Fourth Step: Filter The results of the second step after grouping, and filter with the result of the third step.

Select S.department,sum (G.mark)

From Sporter S,grade g

where S.sporterid=g.sporterid

GROUP BY S.department

Having sum (g.mark) = (

Select Max (sum (g.mark))

From Sporter S,grade g

where S.sporterid=g.sporterid

GROUP BY S.department

）;

Find out the name of each project in a playground and the name of the champion determine the data table to use |-item table: Project name |-sporter table: Name |-grade table: Achievements

Determine known associated fields

|-Project and result: Item.itemid=grade.itemid;

|-Athletes and grades: Sporter.sporterid=grade.sporterid

The first step: Find out the number of all the events on the playground, because only the number can be found in the score

Select Itemid

From item

Where location= ' a playground ';

Step two: Find the winner of the project held in this label

Select Itemid,max (Mark)

From grade

Where Itemid in (

Select Itemid

From item

Where location= ' a playground ')

Group BY Itemid;

Step three: Find athlete data that matches this score

Select G.sporterid

From Grade G, (

Select Itemid Iid,max (Mark) max

From grade

Where Itemid in (

Select Itemid

From item

where location = ' a playground ')

GROUP BY Itemid) Temp

where G.itemid=temp.iid and G.mark=temp.max;

Step Fourth: Increase project information and athlete information

Select G.sporterid,s.name,g.mark,i.itemname

From Grade G, (

Select Itemid Iid,max (Mark) max

From grade

Where Itemid in (

Select Itemid

From item

where location = ' a playground ')

Group by Itemid) Temp,item I,sporter s

where G.itemid=temp.iid and G.mark=temp.max

and Temp.iid=i.itemid and S.sporterid=g.sporterid;

3. Find out the names of other students who have participated in the project Zhang San

Determine the data table to use

|-sporter table: Athlete's name

|-grade Table: Item number

First step: Find the athlete number of Zhang San

Select Sporterid

From Sporter where Name= ' Zhang San ';

Step two: Zhang San all the participating projects are in the grade table

Select Itemid

From grade

Where Grade.sporterid= (

Select Sporterid

From Sporter

Where Name= ' Zhang San '

);

Step three: Find the athlete number based on this item number

Select Sporterid

From grade

Where Itemid in (

Select Itemid

From grade

Where Grade.sporterid= (

Select Sporterid

From Sporter

Where Name= ' Zhang San '));

Fourth step: Find athlete information according to athlete number

SELECT *

From Sporter

where Sporterid in (

Select Sporterid

From grade

Where Itemid in (

Select Itemid

From grade

Where Grade.sporterid= (

Select Sporterid

From Sporter

Where Name= ' Zhang San ')))

and name! = ' Zhang San ';

4. After checking Zhang San because of the use of illicit drugs, the results are recorded as 0 points, please make the corresponding changes in the database.

Update grade Set Mark=0

where Sporterid = (

Select Sporterid

From Sporter

Where Name= ' Zhang San '

);

5. After consultation with the organizing Committee, it is necessary to delete the women high jump competition items.

Delete from item where itemname= ' Women's high jump ';

This article from the "10281302" blog, reproduced please contact the author!

Build table, update, query comprehensive exercises