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

Source: Internet
Author: User
Tags ming

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.