Oracle notes (11) Comprehensive Table creation, update, and query exercises

Link to the comprehensive exercise for creating, updating, and querying Oracle notes (11): Oracle notes (1) Introduction to Oracle and installation of explain (2) SQLPlus command http://www.bkjia.com/database/201209/154051.html#oraclenotes (3) scott user's table structure partition (4) simple query, restricted query, data sorting http://www.bkjia.com/database/201209/154054.html?oraclenote (5) Single Row function partition (6) Multi-Table query partition (7) data Update, transaction processing, and pseudo-column partition (8) complex query and summary (10) create and manage a table at http://www.bkjia.com/database/201209/154063.html#oraclenotes (9) the constraint http://www.bkjia.com/database/201209/154317.html has a database of Student Sports Information and stores the following table: athlete sporter (athlete ID: sporterid, athlete name, athlete sex, department) project item (project number itemid, project name itemname, project competition location) score grade (athlete id, project number itemid, point mark) Please 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 for each table. The athlete's name and department cannot be blank. The points must be null, it can be 6, 4, 2, 0, indicating the points of the first, second, third, and other rankings respectively. Note that the rankings can be placed in parallel, and the following ranking cannot be improved. For example, if there are two tied for the first, there is no second name. 2. Insert the specified data into the table: www.2cto.com athletes (1001, Li Ming, male, computer department 1002, Zhang San, male, Mathematics Department 1003, Li Si, male, computer department 1004, Wang Er, male, Department of Physics 1005, Li Na, female, Department of Psychology 1006, Sun Li, female, Department of mathematics) project (X001, men 5 km, playground X002, men's INS, playground X003, man long jump, second playground X004, women's high jump, second playground X005, Women's 3 km, three Playground) points (www.2cto.com 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 system name with the highest total points and its 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. 1. CREATE a TABLE www.2cto.com 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 NUMBE R (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 the recycle bin should be considered in Oracle. Ii. add data 1. Add athlete data: www.2cto.com insert into sporter (sporterid, name, sex, department) VALUES (1001, 'lilim', 'male ', 'computer system'); insert into sporter (sporterid, name, sex, department) VALUES (1002, 'zhang san', 'mal', 'mathematics department '); 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, departmen T) 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', 'Man 100 ', 'One playground '); insert into item (itemid, itemname, location) VALUES ('x002', 'Men urler', 'one playground'); insert into item (itemid, itemname, location) VALUES ('x003 ', 'Men's shortput', 'second playground'); insert into item (itemid, itemname, location) VALUES ('x004 ', 'female High Jump ', 'second playground'); insert into item (itemid, itemname, location) VALUES ('x005 ', 'Women's 123', 'three playground '); 3. Add an athlete's project score www.2cto.com 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, itemi D, 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); 3. Data operation 1. obtain the highest system name and its points. 1. Determine the data table to be used: sporter table: Obtain the system name; grade Table: Find the points; 2. Determine the known association field: sporter. sporterid = grade. sporterid; Step 1: Combine the sporter and grade tables with 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, there are two ways to find the highest total points: Practice 1: do not consider the same points, all data is sorted in descending order from high to low, and the first data is obtained. Www.2cto.com 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, you must first nest the grouping function to find the maximum points, then, use this content to filter it out from 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 and the name of the champion in the playground. 1. Determine the data table to be used: sporter table: athlete name; item table: Project name; grade Table: Calculate the champion's information based on the score; 2. Determine known association fields: www.2cto.com athletes and scores: sporter. sporterid = grade. sporterid; item and score: item. itemid = grade. itemid; Step 1: Determine the IDSELECT itemid FROM item WHERE location = 'one playground 'of a playground project; Step 2: Obtain the champion's score, because each project has its own project champion score, SELECT I. itemid, MAX (g. mark) maxFROM item I, grade gWHERE I. location = 'playground 1' AND I. itemid = g. itemidGROUP BY I. itemid; Step 3: SELECT s according to the score. name, g. ite Mid, 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 www.2cto.com 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. locati On = '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 other students who participated in all the projects that John participated in. 1. Determine the data table to be used: sporter table: athlete ID of Michael Jacob; grade Table: locate the ID of the project according to it; sporterid: locate the athlete's ID and name based on the Project ID determined by the grade Table and the previous sporter table. 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: Find the ID of the project to be selected based on the athlete's ID. 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 athletes ID www.2cto.com SELECT g. sporteridFROM 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'); Step 4: find the name of the athlete from 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'; www.2cto.com 4. After investigation, Zhang san' scored 0 points for all prohibited drugs. Library. 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 = 0 WHERE sporterid = (SELECT sporterid FROM sporter WHERE name = 'zhang san'); 5. After negotiation by the organizing committee, you must delete the women's high jump competition project. 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 ';