sql - 删除 Oracle 数据库中外键添加的重复项

Question

我正在 Oracle DB 中的旅馆项目上工作，我有 2 个表(学生、房间)，当我运行查询以获取学生姓名、他们的房间 ID 和提供给他们的设施时，它会返回重复的学生姓名使用所有设施并且在查询中几乎没有编辑，它会返回重复的设施。

如何修复这些重复项？这是代码

    --CREATE TABLE STUDENTS
    Create table students(
    regno integer primary key,
    name varchar2(30),
    phonenum number,
    address varchar2(30),
    roomalloc number 
    );

    Alter Table students Add CONSTRAINT fk_roomalloc
    FOREIGN KEY (roomalloc)
    REFERENCES rooms(roomno);

    Alter table students drop constraint fk_roomalloc

    --ALTER TABLE STUDENTS ADD JOIN DATE
    alter table students add (joindate Date);

    --Alter Table for Gender
    Alter Table students add(gender varchar2(2));
    Alter Table students MODIFY gender NOT NULL;

    --Constraint on Gender
    Alter Table students Add CONSTRAINT gen 
    Check (gender IN('M','F','m','f'));
    Alter Table students MODIFY mess default 'M';

    --Constraint Gender Check for Room Allocation
    Alter Table students Add CONSTRAINTS gen_check
    Check (Room IN(Gender='M' , 'Gender='F'));

    --Constraints:ADD MESS AS TRUE/FALSE
    ALTER TABLE students ADD (mess varchar2(4));
    Alter Table students Add CONSTRAINT mess_present 
    Check (mess IN('Yes','No','yes','no'));
    Alter Table students MODIFY mess default 'No';

    --Drop Constraint
    ALTER TABLE students
    DROP CONSTRAINT mess_present
    --ALTER TABLE NAME AS NOT NULL
    alter table students MODIFY name NOT NULL;
    --ALTER TABLE PHONE NO
    alter table students MODIFY phonenum UNIQUE;
    --ALTER TABLE Room Allocated
    alter table students MODIFY roomalloc Not Null;

    Describe students;

    --DATA ENTRY and Modification
    INSERT INTO students(regno, name, phonenum, address, roomalloc,joindate,mess)
        VALUES (1, 'Haseeb', 012345678,'Rawalpindi',1,'1-sep-14','No');
    INSERT INTO students(regno, name, phonenum, address, roomalloc,joindate)
        VALUES (2, 'Faisal', 03451111111,'Rawalpindi',1,'12-sep-14');
    INSERT INTO students(regno, name, phonenum, address, roomalloc,joindate)
        VALUES (3, 'Shahbaz', 03313214567,'Khewra',1,'15-feb-15');
    INSERT INTO students(regno, name, phonenum, address, roomalloc,joindate)
        VALUES (4, 'Muhaddas', 01235131237,'Kashmir',1,'15-feb-15');
    INSERT INTO students(regno, name, phonenum, address, roomalloc,joindate,mess)
        VALUES (5, 'Haseem', 01254530987,'Islamabad',2,'15-sep-15','Yes');
    INSERT INTO students(regno, name, phonenum, address, roomalloc,joindate,mess)
        VALUES (6, 'Asim', 03341234567,'Muzzafargarh',3,'15-sep-14','Yes');
    INSERT INTO students(regno, name, phonenum, address, roomalloc,joindate,mess)
        VALUES (7, 'Izza', 01231234564,'Sialkot',25,'15-sep-15','Yes');
    INSERT INTO students(regno, name, phonenum, address, roomalloc,joindate,mess)
        VALUES (8, 'Sara', 01231234561,'Narrowal',25,'15-sep-15','Yes');
    INSERT INTO students(regno, name, phonenum, address, roomalloc,joindate,mess)
        VALUES (9, 'Maria', 01231234567,'Wah',4,'25-sep-15','Yes');
    INSERT INTO students(regno, name, phonenum, address,    roomalloc,joindate,Gender)
        VALUES (10, 'Faha', 0123123452,'Okara',4,'26-sep-15','F');

    Update students SET Gender='M' WHERE regno>=1 And regno<=6;
    Update students SET roomalloc=4,Gender='F' WHERE regno>=7 And regno<=9;

    Describe students;

    --Data Fetching Queries

    Select *
    From students,rooms
    Where rooms.facility='Fan/Net/TV'
    ORDER BY roomalloc , regno;

    Drop table students;

    --CREATE TABLE Rooms
    Create table rooms(
    roomno integer primary key,
    facility varchar2(30)
    );

    --Altering Rooms Types
    Alter Table rooms Add CONSTRAINT rtype 
    Check (facility IN('AC/Net/TV','Fan/Net/TV','Fan/Net'));
    Alter Table rooms MODIFY facility default 'Fan/Net';

    Alter table rooms drop constraint str

    INSERT INTO rooms( roomno, facility)
        VALUES (1,'AC/Net/TV');

    INSERT INTO rooms( roomno, facility)
        VALUES (2,'Fan/Net/TV');

    INSERT INTO rooms( roomno, facility)
        VALUES (3,'Fan/Net/TV');

    INSERT INTO rooms( roomno, facility)
        VALUES (4,'Fan/Net');

    Alter table rooms Modify facility UNIQUE 

    Select *
    From rooms;

    Select students.name,rooms.facility
    From students,rooms
    Where roomno=4;

    Drop table rooms

Answer 1

您需要正确连接表格以获得所需的结果:

Select students.name,rooms.facility
From students join rooms on students.roomalloc = rooms.roomno
Where rooms.roomno=4;