Lesson 8 Structured Query Language

Consider the SQL statement and the relation shown in the exhibit: How many records will be returned by the SELECT statement? Select* FROM Employees WHERE loc_num = '1006' AND salary > 40000;

1

Based on the Location relation shown in the exhibit, how many records will be returned by this SQL statement? Select* FROM Location WHERE I_num = 3 OR NOT I_state ='NY'

4

Consider the SQL statement and the relation shown in the exhibit: SELECT* FROM Employees WHERE title LIKE '%Manager'; How many records will be returned by the SELECT statement?

4

Amanda is working with the Customers relation and needs to insert the new record indicated in the exhibit. A record in the Customers relation cannot have a null value in the Sales_office column or the Sales_Rep_No column, and neither column has a default value defined. What will happen when Amanda inserts the new record indicated?

An error will occur and the insert operation will not be allowed.

Which of the following SQL statements will create a database named Creative_Collectibles?

CREATE SCHEMA Creative_Collectibles;

Consider the Customers relation shown in the exhibit: Which of the following SQL statements would properly remove the record for MacroWidget?

DELETE FROM Customers WHERE Cust_No = 1012;

Which of the following SQL statements will delete a database named Creative_Collectibles and all its database objects?

DROP SCHEMA Creative_ Collectibles;

Which of the following SQL statements can be used to delete a relation from a schema?

DROP TABLE

Which subset of SQL is used to define privileges for database users?

Data Control Language (DCL)

Which subset of SQL is used to create relations and views?

Data Definition Language (DDL)

Which of the following are the three main subsets of SQL?

Data Definition Language (DDL), Data Manipulation Language (DML) and Data Control Language (DCL)

Which subset of SQL is used to update, delete, insert and retrieve data?

Data Manipulation Language (DML)

Consider the Employee relation shown in the exhibit: What would be the effect of executing the following SQL statement? UPDATE Employee SET salary = 52000, title = 'VP', loc_num = NULL WHERE salary = 47000;

Glen Heer would be given a promotion and a raise and be removed from his current location.

Ken is the database administrator for ACME Inc. He granted Glen, the general manager, ALL PRIVILEGES on the Products relation and included the WITH GRANT OPTION clause. Glen, in turn, granted SELECT privileges on the Products relation to his assistant, Amanda. Glen, whose user name is gheer455, has now accepted a position on the board of directors, and no longer requires any access to the Products relation. Ken issues the following SQL command: REVOKE ALL PRIVILEGES ON Products FROM gheer455; Which of the following will happen?

Glen will be unable to perform any operations on the Products relation, and Amanda will be unable to perform any operations on the Products relation.

Consider the relations shown in the exhibit: Which of the following SQL statements would enter records for which the Satisfaction_Rate value is 90 or above from the Customers relation into the High_Satisfaction relation?

INSERT INTO High_Satisfaction SELECT * FROM Customers WHERE Satisfaction_Rate >=90';

Consider the relation shown in the exhibit: Which of the following SQL statements will correctly add a record to the Location relation?

INSERT INTO Location VALUES (5, 'Mesa', '480-926-9214', '480-926-9200');

Which of the following is true of the SQL DELETE Statement?

It cannot be used to remove individual columns.

When executed, what will the following SQL statement accomplish? DROP SCHEMA Holistic_Herbs;

It will delete a database named Holistic_Herbs and all its database objects.

Consider the following SQL statement: SELECT * FROM Employees WHERE salary BETWEEN 36000 AND 33100; Based on the data shown in the exhibit, how many records will be returned when the SQL statement is executed?

None

SQL is considered as what type of language?

Nonprocedural

David has found it necessary to add a Wave_Magnitude field to the Tsunami_Stats table in his Tsunami database. The table already contains data. What will be the value contained in the Wave_Magnitude field after David adds the field using the ALTER TABLE statement as follows? ALTER TABLE Tsunami_Stats ADD COLUMN Wave_Magnitude INTEGER;

Null

Consider the relation shown in the exhibit: Which of the following SQL statements represents the simplest way to retrieve all attributes for all records in the relation?

SELECT * FROM Assets;

Consider the following SQL statement: SELECT * FROM EMPLOYEES WHERE title = 'Web Designer' OR title = 'Programmer'; Based on the data shown in the exhibit, which of the following SQL statements is equivalent to the statement shown above?

SELECT * FROM Employees WHERE title IN ('Web Designer', 'Developer', 'Programmer');

Consider the relation shown in the exhibit: Which of the following SELECT statements will return records for all managers?

SELECT * FROM Emplyees WHERE title LIKE '%Manager';

Consider the Employees relation shown in the exhibit: Which of the following SELECT statements will return records for employees Cain, Fish, Harp and Heer?

SELECT * FROM Employees WHERE last_name BETWEEN 'C' AND 'i';

Consider the Location relation and the result set shown in the exhibit: Which of the following SQL statements would produce the result set?

SELECT Asset_id AS "Tag #", OS AS Platform, Location AS "Room #" FROM Assets;

Consider the Assets relation shown in the exhibit: Which of the following SQL statements would return the Assets2 relation from the Assets relation?

SELECT Asset_id, OS, User, Location FROM Assets WHERE OS = 'XP' AND NOT Asset_id = 10001;

Consider the relation shown in the exhibit: Which of the following SQL statements would return a relation that excludes all locations in New York except for the location on Nassau Blvd.?

SELECT* FROM location WHERE I_num = 3 OR NOT I_state ='NY';

Consider the Employees relation and the Result set shown in the exhibit: Which of the following SELECT statements will return the result set?

SELECT* FROM Employees WHERE loc_num = '1298' OR salary <= 34000;

Consider the Employees relation shown in the exhibit: Which of the following SELECT statements will list the records in ascending order by loc_num and then descending order by last_name?

SELECT* FROM Employees ORDER BY loc_num, last_name DESC;

Which of the following is the standard language used to interact with relational databases?

Structured Query Language (SQL)

Consider the following SQL statement that returns all attributes from a relation named South_Pacific_Tsunamis: SELECT * FROM South_Pacific_Tsunamis SPT; The SQL statement demonstrates the use of which of the following?

Table aliases

Consider the relation and the SQL statement shown in the exhibit: Which of the following tables shows the Customers relation after the execution of the SQL statement? DELETE FROM Customers;

The blank one

Consider the following SQL statement: INSERT INTO Employee VALUES (00005, 'Jill', 'Jackson', 's_rep'); What can be determined regarding the structure of the Employee relation?

The first column in the relation has a numeric data type.

Consider the Employee relation shown in the exhibit: What would be the effect of executing the following SQL statement? UPDATE Employee SET salary = 35000, title = 'senior accountant', loc_num = NULL WHERE salary < 33100;

Two rows, row 2 and row 3, will be updated as they meet the criteria.

If you used the Employee relation shown in the exhibit, which of the following SQL statements would give the accountant a 10 percent salary increase and move him to location 1005?

UPDATE Employee SET salary = salary+(salary*.1), loc_num ='1005' WHERE title = 'accountant';

Consider the relation shown in the exhibit: Which of the following is the most likely data type for the Location attribute?

VARCHAR

Ken needs to update the Trans-Atlantic and Pan-Pacific relations in his travel database. In each case the cost for a coach ticket must be increased by 10 percent. Which of the following SQL statements will update both relations?

You cannot update more than one relation in a single UPDATE statement.

David decides to alter the Tsunami_Stats table in his Tsunami database by specifying a default value of 3 for the Wave_Magnitude field. Which of the following ALTER TABLE statements will accomplish this task?

ALTER TABLE Tsunami_Stats ALTER COLUMN Wave_Magnitude SET DEFAULT 3;

Which of the following are keywords used in Boolean expressions?

AND, OR, and NOT

David is managing the Earthquakes relation. What will be the result of executing the following SQL statement? DELETE FROM Earthquakes;

All the records in the Earthquakes relation will be deleted.

The entire organization or structure of a database is its

schema