2.8 Inserting, updating, and deleting rows

What departments are changed? UPDATE Department SET Name = 'Administration' WHERE ManagerID IS NOT NULL; Administration All departments except Technical Support All departments

All departments except Technical Support because only Technical Support has a NULL manager, so all other departments are renamed Administration

What departments are deleted? DELETE FROM Department;

All departments; No WHERE clause is specified, so all rows are deleted

What department managers are changed? UPDATE Department SET ManagerID = 2538;

All managers; no WHERE clauses exists, so all departments are assigned the same manager

What is missing to change Marketing's Code to 55 and Name to Administration? UPDATE Department SET Code = 55, Name = 'Administration' WHERE ___;

Code = 12 The Marketing department's Code is 12, so only the Marketing department's row is changed

What is missing to delete only Sales? DELETE FROM Department WHERE ___;

Code = 82; only the Sales row has Code 82

Complete the statement to delete the row containing 'Nepal'. In the WHERE clause, identify the row to be deleted using the ISOCode2 column.

DELETE FROM Country WHERE ISOCode2 = 'NP';

CREATE TABLE Department ( Code TINYINT UNSIGNED, Name VARCHAR(20), ManagerID SMALLINT UNSIGNED ); 1) Which statement correctly inserts Sales?

INSERT Department VALUES (82, 'SALES', 6381);

Complete the statement to insert a row with Name 'Egypt', ISOCode 2 'EG', and Population 98423595

INSERT INTO Country (Population, ISOCode2, Name) VALUES (98423595, 'EG', 'EGYPT');

Which statement correctly inserts Technical Support?

INSERT INTO Department (Code, Name) VALUES (99, 'Technical Support');

CREATE TABLE Department ( Code TINYINT UNSIGNED, Name VARCHAR(20), ManagerID SMALLINT UNSIGNED ); 1) Which statement correctly inserts Engineering?

INSERT INTO Department (Code, Name, ManagerID) VALUES (44, 'Engineering', 2538);

Which statement correctly inserts Marketing?

INSERT INTO Department (Name, ManagerID, Code) VALUES ('Marketing', 6381, 12);

What is missing to change "Sales" to "Custodial"? UPDATE Department SET ___ WHERE Code = 82; 'Sales' = 'Custodial' Department = 'Custodial' Name = 'Custodial'

Name = 'Custodial'

CREATE TABLE Department ( Code SMALLINT UNSIGNED DEFAULT 1000, Name VARCHAR(20), ManagerID SMALLINT ); 1) Which alteration to the CREATE TABLE statement sets the default Name to Accounting?

Name VARCHAR(20) DEFAULT 'Accounting',

What columns are NULL after the following statement executes? INSERT INTO Department () VALUES ();

Name and ManagerID; Neither Name nor ManagerID have a default, so both columns are assigned NULL

What departments are deleted? DELETE FROM Department WHERE ManagerID = 6381;

Sales and Marketing, since they have the same ManagerID

Complete the statement to change 'Vanuatu' to 'Botswana'. In the WHERE clause, identify the row to be changed using the ISOCode2 column.

UPDATE Country SET Name = 'Botswana' WHERE ISOCode2 = 'VU';

INSERT INFO

clause names the table and columns where data is to be added. the keyword INTO is optional

VALUES

clause specifies the column values to be added

SET

clause to specify the new column values. An optional WHERE clause specifies which rows are updated. Omitting the WHERE clause results in all rows being updated.

TRUNCATE

deletes all rows from a table; it is nearly identical to a DELETe statement with no WHERE clause except for minor differences that depends on the database system

DELETE

deletes existing statements in a table

FROM

it is followed by the table name whose rows are to be deleted; an optional WHERE clause specifies which rows should be deleted. omitting the WHERE clause results in all rows in the table being deleted

DEFAULT

keyword and default value follow the column name and data type in a CREATE TABLE statement. The column is assigned the default value, rather than NULL, when omitted from an INSERT statement.

UPDATE

modifies existing rows in a table

INSERT

statement adds rows to a table