SQL Query Question: So I have a database with the tables below, data has also be

Question

SQL Query Question:

So I have a database with the tables below, data has also been aded into these tables,

My question is a skill called 'thinking' needs to be changed to 'Deep thinking', Im not allowed to change any consistancy constraints and not allowed to put ON CASCADE on, all I can use is INSERT UPDATE or DELETE statements.

Any help would be awesome thanks guys

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE SKILL(
sname          VARCHAR(30)     NOT NULL, /* Skill name                 */
   CONSTRAINT SKILL_pkey PRIMARY KEY ( sname ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
CREATE TABLE SREQUIRED(
sname          VARCHAR(30)     NOT NULL, /* Skill name                 */
requires      VARCHAR(30)     NOT NULL, /* Skill required       */
slevel        DECIMAL(2)      NOT NULL, /* Level required       */
   CONSTRAINT SREQUIRED_pkey PRIMARY KEY ( sname, requires ),
   CONSTRAINT SREQUIRED_fkey1 FOREIGN KEY ( sname)
       REFERENCES SKILL( sname ),
   CONSTRAINT SREQUIRED_fkey2 FOREIGN KEY ( requires )
       REFERENCES SKILL( sname ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* Relational tables implementing classes of objects           */
/*                                   */
CREATE TABLE APPLICANT(              /* Applicants           */
anumber         DECIMAL(6)      NOT NULL, /* Applicant number           */
fname           VARCHAR(20)     NOT NULL, /* First name                 */
lname       VARCHAR(30)   NOT NULL, /* Last name           */
dob            DATE        NOT NULL, /* Date of birth       */
city       VARCHAR(30)   NOT NULL, /* City           */
state       VARCHAR(20)   NOT NULL, /* State           */
phone       DECIMAL(10)   NOT NULL, /* Phone number       */
fax       DECIMAL(10)       , /* Fax number           */
email       VARCHAR(50)       , /* E-mail address       */
   CONSTRAINT APPLICANT_pkey PRIMARY KEY ( anumber ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
CREATE TABLE EMPLOYER(              /* Employers           */
ename           VARCHAR(100)    NOT NULL, /* Employer name              */
city            VARCHAR(30)     NOT NULL, /* City                       */
state           VARCHAR(20)     NOT NULL, /* State                      */
phone           DECIMAL(10)     NOT NULL, /* Phone number               */
fax             DECIMAL(10)             , /* Fax number                 */
email           VARCHAR(50)             , /* E-mail address             */
web             VARCHAR(50)             , /* Web site address           */
        CONSTRAINT EMPLOYER_pkey PRIMARY KEY ( ename ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
CREATE TABLE POSITIONS(              /* Advertised positions   */
pnumber         DECIMAL(8)      NOT NULL, /* Position number            */
title           VARCHAR(30)     NOT NULL, /* Position title             */
salary       DECIMAL(9,2)   NOT NULL, /* Salary           */
extras       VARCHAR(50)            , /* Extras           */
bonus       DECIMAL(9,2)       , /* End of year bonus       */
specification   VARCHAR(2000)   NOT NULL, /* Specification       */
ename       VARCHAR(100)   NOT NULL, /* Employer name       */
   CONSTRAINT POSITION_pkey PRIMARY KEY ( pnumber ),
   CONSTRAINT POSITION_fkey FOREIGN KEY ( ename)
       REFERENCES EMPLOYER( ename ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
CREATE TABLE EMPLBY(              /* Former employers       */
anumber         DECIMAL(6)      NOT NULL, /* Applicant number           */
ename       VARCHAR(100)   NOT NULL, /* Employer name       */
fromdate   DATE       NOT NULL, /* Employed from       */
todate       DATE           , /* Employed to       */
   CONSTRAINT EMPLBY_pkey PRIMARY KEY ( anumber, ename, fromdate ),
   CONSTRAINT EMPLBY_fkey1 FOREIGN KEY ( anumber )
       REFERENCES APPLICANT( anumber ),
   CONSTRAINT EMPLBY_fkey2 FOREIGN KEY ( ename )
       REFERENCES EMPLOYER( ename ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */
/* Relational tables implementing associations               */
/*                                   */
CREATE TABLE SPOSSESSED(
anumber           DECIMAL(6)    NOT NULL, /* Applicant number           */
sname       VARCHAR(30)   NOT NULL, /* Skill name                 */
slevel       DECIMAL(2)   NOT NULL, /* Skill level                */
   CONSTRAINT SPOSSESSED_pkey PRIMARY KEY ( anumber, sname ),
   CONSTRAINT SPOSSESSED_fkey1 FOREIGN KEY ( anumber )
               REFERENCES APPLICANT ( anumber )
               ON DELETE CASCADE,
   CONSTRAINT SPOSSESSED_fkey2 FOREIGN KEY ( sname )
               REFERENCES SKILL ( sname ),
   CONSTRAINT SPOSSESSED_check1 CHECK ( slevel IN
                   ( 1,2,3,4,5,6,7,8,9,10 ) ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE SNEEDED(
pnumber           DECIMAL(8)    NOT NULL, /* Position number            */
sname       VARCHAR(30)   NOT NULL, /* Skill name                 */
slevel       DECIMAL(2)   NOT NULL, /* Skill level                */
   CONSTRAINT SNEEDED_pkey PRIMARY KEY ( pnumber, sname ),
   CONSTRAINT SNEEDED_fkey1 FOREIGN KEY ( pnumber )
               REFERENCES POSITIONS ( pnumber )
               ON DELETE CASCADE,
   CONSTRAINT SNEEDED_fkey2 FOREIGN KEY ( sname )
               REFERENCES SKILL ( sname ),
   CONSTRAINT SNEEDED_check1 CHECK ( slevel IN
                   ( 1,2,3,4,5,6,7,8,9,10 ) ) );

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

CREATE TABLE APPLIES(
anumber       DECIMAL(6)   NOT NULL, /* Applicant number       */
pnumber       DECIMAL(8)   NOT NULL, /* Position number       */
appdate       DATE       NOT NULL, /* Application date        */
   CONSTRAINT APPLIES_pkey PRIMARY KEY ( anumber, pnumber ),
   CONSTRAINT APPLIES_fkey1 FOREIGN KEY ( anumber )
               REFERENCES APPLICANT ( anumber )
               ON DELETE CASCADE,
   CONSTRAINT APPLIES_fkey2 FOREIGN KEY ( pnumber )
               REFERENCES POSITIONS ( pnumber )
               ON DELETE CASCADE);

/* ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ */

Explanation / Answer

SKILL is the table that contains ‘sname’ and we have to change ‘sname’ from ‘thinking to Deep thinking’

First we will insert the sname ‘Deep thinking’ in SKILL table as:

INSERT INTO SKILL (sname) VALUES (‘Deep thinking’)

Now we will find out which all tables has sname as foreign key and modify that tables sname with ‘thinking’ to ‘Deep thinking’. Because of the above insert statement we are on safe side to update the sname to ‘Deep thinking’ .

The other tables referenced to SKILL table’s sname is

1. SREQUIRED
2. SPOSSESSED
3. SNEEDED

Updating the sname in above tables:

Sql : UPDATE SNEEDED
SET sname = REPLACE(sname,’thinking’,’Deep thinking’)

Sql : UPDATE SPOSSESSED
SET sname = REPLACE(sname,’thinking’,’Deep thinking’)

In the case of SREQUIRED table both sname and requires may have ‘thinking’ as values. So two updates are required

Sql : UPDATE SREQUIRED
SET sname = REPLACE(sname,’thinking’,’Deep thinking’)

Sql : UPDATE SREQUIRED
SET requires = REPLACE(requires,’thinking’,’Deep thinking’)

After all these updates we can safely remove the sname ‘thinking’ from SKILL Table :

Sql : DELETE FROM SKILL WHERE sname = ‘thinking’

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.