Oracle basic knowledge note (11) Comprehensive Table creation, update, and query exercises

The following table is saved in a database with information about a student sports meeting:
Athlete sporter (athlete ID: sporterid, athlete name, athlete gender sex, department)
Project item (Project No. itemid, project name itemname, project competition location)
Score grade (athlete id, Project id itemid, point mark)

Use SQL statements to complete the following functions:

1. Create a table and add constraints on the corresponding fields;

Define the primary key and foreign key constraints of each table;
Enter the name and Department of the athlete;
Points must be null, or 6, 4, 2, 0, respectively, representing the first, second, and third points and other points. Note that the rankings can be placed in parallel, and the subsequent ranking cannot be improved, for example, if there are two sides tied for the first, there is no second.

2. Insert the specified data to the table:

Athletes (1001, Li Ming, male, computer department 1002, Zhang San, male, Mathematics Department 1003, Li Si, male, computer department 1004, Wang Er, male, physics department 1005, Li Na, female, department of Psychology 1006, Sun Li, female, Department of mathematics) project (X001, men's 5 km, a playground X002, a men's spear, a playground X003, men's long jump, two playground X004, women's high jump, second playground X005, Women's 3 km, three Playground) 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. obtain the highest system name and points of the current total points.

B. Find the name of each project and the name of the champion for the competition on the playground.

C. Find out the names of the other students who participated in all the projects that James participated in.

D. Check that Zhang San scored 0 points for all prohibited drugs. Please modify the score in the database.

E. After consultation by the organizing committee, the women's promotion project needs to be deleted.

 

I. Create a table

Drop table grade PURGE; drop table sporter PURGE; drop table item PURGE; create table sporter (sporterid NUMBER (4) Primary key, name VARCHAR2 (20) Not null, sex VARCHAR2 (10) CHECK (sex IN ('male', 'female '), department VARCHAR2 (20) Not null); create table item (itemid VARCHAR2 (4) Primary key, itemname VARCHAR2 (20) Not null, location VARCHAR2 (20) 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)));

In addition, the recycle bin should be considered in Oracle.

2. Add data

1. Add athlete data:

Insert into sporter (sporterid, name, sex, department) VALUES (1001, 'Lilim', 'male', 'computer system'); insert into sporter (sporterid, name, sex, department) VALUES (1002, 'Zhang san', 'male', 'department of mathematics '); insert into sporter (sporterid, name, sex, department) VALUES (1003, 'Lily', 'male', 'computer system'); insert into sporter (sporterid, name, sex, department) VALUES (1004, 'Wang er', 'male', 'physics '); insert into sporter (sporterid, name, sex, department) VALUES (1005, 'Lila', 'female, 'Psychology '); insert into sporter (sporterid, name, sex, department) VALUES (1006, 'Sunlil', 'female, 'department of mathematics ');

2. Add project data

Insert into item (itemid, itemname, location) VALUES ('x001', 'Men 100', 'one playground '); insert into item (itemid, itemname, location) VALUES ('x002', 'Men's road', 'one playground '); insert into item (itemid, itemname, location) VALUES ('x003', 'Man Long Jump ', 'Second playground '); insert into item (itemid, itemname, location) VALUES ('x004', 'Women swapping', 'second playground '); insert into item (itemid, itemname, location) VALUES ('x005 ', 'Women's 123456', 'three playground ');

3. Increase the score of an athlete's Project

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

Iii. Data Operations

1. Find the system name with the highest total points and their points.

1. Determine the data table to be used:

Sporter table: Obtain the Department name;
Grade Table: Find points;

2. Determine the known association field: sporter. sporterid = grade. sporterid;

Step 1: Combine the sporter table with the grade Table

SELECT s.department,g.markFROM sporter s,grade gWHERE s.sporterid=g.sporterid;

Step 2: Obtain the points obtained by each department for the project. Then, group the query group based on the department name.

SELECT s.department,SUM(g.mark)FROM sporter s,grade gWHERE s.sporterid=g.sporteridGROUP BY s.department;

Now that you know the scores of each department, you can find the highest total points in two ways:

Method 1: If the same points are not considered, all data is sorted in descending order from high to low, and the first data is obtained.

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;

Practice 2: consider the same points problem, you must first nest the grouping function to find the highest points, and then use this content to filter with the previous group.

SELECT s.department,SUM(g.mark) sumFROM sporter s,grade gWHERE s.sporterid=g.sporteridGROUP BY s.departmentHAVING 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 the name of each project in the playground and the name of the champion.

1. Determine the data table to be used:

Sporter table: name of the athlete;
Item table: Project name;
Grade table: the champion's information is calculated based on the score;

2. Determine the known associated fields:

Athlete and score: sporter. sporterid = grade. sporterid;
Project and score: item. itemid = grade. itemid;

Step 1: Determine the ID of a playground Project

SELECT itemid FROM item WHERE location = 'playground ';

Step 2: Obtain the champion's score, because each project has a champion's score for each project.

SELECT I. itemid, MAX (g. mark) maxFROM item I, grade gWHERE I. location = 'one playground 'AND I. itemid = g. itemidGROUP BY I. itemid;

Step 3: find the name of the Athlete Based on the score

SELECT s. name, g. itemid, temp. maxFROM sporter s, grade g, (SELECT I. itemid iid, MAX (g. mark) max FROM item I, grade g WHERE I. location = 'playground 1' AND I. itemid = g. itemid group by I. itemid) tempWHERE s. sporterid = g. sporterid AND temp. iid = g. itemid AND g. mark = temp. max;

Step 4: Find the project name and introduce the item table

SELECT s. name, g. itemid, temp. max, I. itemnameFROM sporter s, grade g, (SELECT I. itemid iid, MAX (g. mark) maxFROM item I, grade gWHERE I. location = 'playground 1' AND I. itemid = g. itemidGROUP BY I. itemid) temp, item iWHERE s. sporterid = g. sporterid AND temp. iid = g. itemid AND g. mark = temp. maxAND g. itemid = I. itemid AND temp. iid = I. itemid;

3. Find out the names of the other students who participated in all the projects that James participated in.

1. Determine the data table to be used:

Sporter table: ID of the athlete of John;
Grade Table: locate the project ID based on it;
Sporterid: Specifies the project ID based on the grade Table and the previous sporter table, and finds the athlete's ID and name;

2. Determine the known association field: sporter. sporterid = grade. sporterid;

Step 1: Determine Michael's athlete ID

SELECT s. sporteridFROM sporter sWHERE s. name = 'zhang san ';

Step 2: locate the ID of the participating project based on the athlete's ID

SELECT g. itemidFROM grade gWHERE g. sporterid = (SELECT s. sporterid FROM sporter s WHERE s. name = 'zhang san ');

Step 3: Modify the preceding query and find the IDs of all athletes.

SELECT g. sporteridFROM grade gWHERE g. itemid IN (SELECT g. itemid FROM grade g WHERE g. sporterid = (SELECT s. sporterid FROM sporter s WHERE s. name = 'zhang san '));

Step 4: Find the athlete's name based on the athlete's number

SELECT nameFROM sporterWHERE 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. Check that Zhang San scored 0 points for all prohibited drugs. Please modify the score in the database.

Now I only know the name of Michael Jacob, but I don't know the athlete number. The score must be updated in the grade table by the athlete number.

UPDATE grade SET mark =0WHERE sporterid = (SELECT sporterid FROM sporter WHERE name = 'zhangsan ');

5. After negotiation by the organizing committee, the women's high jump competition project needs to be deleted.

After the project is deleted, the corresponding score should also disappear, and cascade deletion has been configured before, so you can directly Delete the parent table.

Delete from item WHERE itemname = 'Women swapping ';