Task #$2$: Inserting Rows

Staff

staffNo sName position salary branchNo bAddress

SL$21$ John White Manager $30000$ B$00522$ Deer Rd$.,$ London

SG$37$ Ann Beech Assistant $12000$ B$003163$ Main St$.,$ Glasgow

SG$14$ David Ford Supervisor $18000$ B$003163$ Main St$.,$ Glasgow

SA$9$ Mary Howe Assistant $9000$ B$00716$ Argyll St$.,$ Aberdeen

SG$5$ Susan Brand Manager $24000$ B$003163$ Main St$.,$ Glasgow

SL$41$ Julie Lee Assistant $9000$ B$00522$ Deer Rd$.,$ London

Which of the following SQL Insert statement adds one employee to the table above? Additionally, explain the rationale on each of the choices on why the SQL statement works or does not work.

INSERT INTO Staff VALUES $($SA$4,$Alan$,$Brown$,$Assistant$,8300,$B$007,16$ Argyll St$.,$ Aberdeen$)$;

INSERT INTO Staff $($staffNo$,$ sName, branchNo$)$ VALUES $($SA$4,$Alan Brown$,$B$007)$;

INSERT INTO Staff VALUES $($SA$4,$Alan Brown$,$Assistant$,$ NULL, $$B$007,16$ Argyl Rd$.,$ Aberdeen$)$;

Task #$3$

Inserting Rows

Staff

staffNo sName position salary branchNo bAddress

SL$21$ John White Manager $30000$ B$00522$ Deer Rd$.,$ London

SG$37$ Ann Beech Assistant $12000$ B$003163$ Main St$.,$ Glasgow

SG$14$ David Ford Supervisor $18000$ B$003163$ Main St$.,$ Glasgow

SA$9$ Mary Howe Assistant $9000$ B$00716$ Argyll St$.,$ Aberdeen

SG$5$ Susan Brand Manager $24000$ B$003163$ Main St$.,$ Glasgow

SL$41$ Julie Lee Assistant $9000$ B$00522$ Deer Rd$.,$ London

Which of the following SQL Insert statement adds one employee to the table above?

INSERT INTO Staff VALUES $($SA$4,$Alan$,$Brown$,$Assistant$,8300,$B$007,16$ Argyll St$.,$ Aberdeen$)$;

INSERT INTO Staff $($staffNo$,$ sName, branchNo$)$ VALUES $($SA$4,$Alan Brown$,$B$007)$;

INSERT INTO Staff VALUES $($SA$4,$Alan Brown$,$Assistant$,$ NULL, $$B$007,16$ Argyl Rd$.,$ Aberdeen$)$;

Task #$4$

Inserting Rows

Staff

staffNo sName position salary branchNo bAddress

SL$21$ John White Manager $30000$ B$00522$ Deer Rd$.,$ London

SG$37$ Ann Beech Assistant $12000$ B$003163$ Main St$.,$ Glasgow

SG$14$ David Ford Supervisor $18000$ B$003163$ Main St$.,$ Glasgow

SA$9$ Mary Howe Assistant $9000$ B$00716$ Argyll St$.,$ Aberdeen

SG$5$ Susan Brand Manager $24000$ B$003163$ Main St$.,$ Glasgow

SL$41$ Julie Lee Assistant $9000$ B$00522$ Deer Rd$.,$ London

Based on a close reading of the choices for Task #$3,$ name one anomaly $($mistake$)$ that could rise when inserting records?

Task #$5$: Updating Rows

Staff

staffNo sName position salary branchNo bAddress

SL$21$ John White Manager $30000$ B$00522$ Deer Rd$.,$ London

SG$37$ Ann Beech Assistant $12000$ B$003163$ Main St$.,$ Glasgow

SG$14$ David Ford Supervisor $18000$ B$003163$ Main St$.,$ Glasgow

SA$9$ Mary Howe Assistant $9000$ B$00716$ Argyll St$.,$ Aberdeen

SG$5$ Susan Brand Manager $24000$ B$003163$ Main St$.,$ Glasgow

SL$41$ Julie Lee Assistant $9000$ B$00522$ Deer Rd$.,$ London

Which of the following SQL statement properly adjusts an in the table above?

UPDATE Staff SET bAddress $=195$ Post Rd$.,$ Glasgow$$ WHERE staffNo $=$SG$37$;

UPDATE Staff SET bAddress $=195$ Post Rd$.,$ Glasgow$$;

UPDATE Staff SET bAddress $=195$ Post Rd$.,$ Glasgow$$ WHERE branchNo $=$B$003$;

Task #$6$

Updating Rows

Staff

staffNo sName position salary branchNo bAddress

SL$21$ John White Manager $30000$ B$00522$ Deer Rd$.,$ London

SG$37$ Ann Beech Assistant $12000$ B$003163$ Main St$.,$ Glasgow

SG$14$ David Ford Supervisor $18000$ B$003163$ Main St$.,$ Glasgow

SA$9$ Mary Howe Assistant $9000$ B$00716$ Argyll St$.,$ Aberdeen

SG$5$ Susan Brand Manager $24000$ B$003163$ Main St$.,$ Glasgow

SL$41$ Julie Lee Assistant $9000$ B$00522$ Deer Rd$.,$ London

Based on a close reading of the choices in Task #$5,$ name one anomaly $($mistake$)$ that could rise when updating records?

Task #$7$

Deleting Rows

Staff

staffNo sName position salary branchNo bAddress

SL$21$ John White Manager $30000$ B$00522$ Deer Rd$.,$ London

SG$37$ Ann Beech Assistant $12000$ B$003163$ Main St$.,$ Glasgow

SG$14$ David Ford Supervisor $18000$ B$003163$ Main St$.,$ Glasgow

SA$9$ Mary Howe Assistant $9000$ B$00716$ Argyll St$.,$ Aberdeen

SG$5$ Susan Brand Manager $24000$ B$003163$ Main St$.,$ Glasgow

SL$41$ Julie Lee Assistant $9000$ B$00522$ Deer Rd$.,$ London

Which of the following SQL statement properly deletes a staff record. Additionally explain the rationale for each of the choices $($correct and wrong ones$)?$

DELETE FROM Staff WHERE staffNo $=$SA$9$;

DELETE FROM Staff;

DELETE FROM Staff WHERE position $=$Assistant$$;