Structured Query Language

Consider the SQL statement and the relation shown in the exhibit: How many records will be returned by the SELECT statement?

1

Based on the Location relation shown in the exhibit, how many records will be returned by this SQL statement?

4

Consider the SQL statement and the relation shown in the exhibit: How many records will be returned by the SELECT statement?

4

David decides to alter the Tsunami_Stats table in his Tsunami database by removing the 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 DROP DEFAULT;

Which of the following are keywords used in Boolean expressions?

AND, OR, and NOT

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

CREATE SCHEMA Creative_Collectibles;

Consider the following SQL statement: SELECT Asset_id AS ""Tag #"", OS AS Platform, Location AS ""Room #"" FROM Assets; This SQL statement demonstrates the use of which of the following?

Column aliases

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

Data Manipulation Language (DML)

To which level of the SQL standard do most modern day DBMS products conform?

Entry

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

It cannot be used to remove individual columns.

Consider the User list shown in the exhibit. What does the following SQL statement accomplish? GRANT UPDATE on Orders(cust_name, cust_address, cust_phone) TO

It gives Sam Spade the ability to change information in the cust_name, cust_address and cust_phone fields in the Orders relation.

Consider the Employees relation and the ordered result set shown in the exhibit. Which of the following SELECT statements will list the records as shown in the result set?

SELECT * FROM Employees ORDER BY loc_num, salary DESC;

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

SELECT * FROM Employees WHERE last_name BETWEEN 'C' AND 'I' AND NOT last_name = 'Harp';

Which of the following is the latest SQL standard?

SQL99

Consider the relation and the SQL statement shown in the exhibit. Which of the following results will be retrieved by the SQL statement?

The three by three chart.

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.

XYZ Company often uses the services of temporary employees to update its order tracking database. Temporary employees are allowed access through a view named Emp_View. Emp_View is a virtual table that allows temporary employees to enter product ordering information, including order date, customer information, and product, pricing and shipping details. The company decides that a field named Order_Time should be added to the view. Which of the following ALTER TABLE statements can you use to alter the Emp_View virtual table and add an additional field?

You cannot use the ALTER TABLE command to alter the structure of a table created as a view.

The entire organization or structure of a database is its _______.

schema

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.

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;

Blank one.

What type of aliases are supported by SQL?

Column aliases and table aliases

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

Create an empty database.

Which of the following SQL data types is used for numbers that must be expressed in scientific notation?

DOUBLE PRECISION

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 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;

There would be no rows updated because no records meet the criteria.

How many levels of conformity to the SQL standard are designated by the ISO?

Three

How many main subsets does Structured Query Language (SQL) consist of?

Three

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 WHERE Satisfaction_Rate < 81;

the five by four chart. 7403

Consider the relation and the SQL statement shown in the exhibit. Which of the following results will be retrieved by the SQL statement?

the six by three chart.

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;

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, and he included the WITH GRANT OPTION clause, as well. Which of the following describes Amanda's current privileges?

Amanda can select data in the Products relation and she can pass the SELECT privilege on to other users.

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.

Consider the following SQL statement that returns one attribute from a relation named Assets and one attribute from a relation named Departments: SELECT a.asset_id as ""Tag #"", d.manager_name AS ""Manager"" FROM Assets a, Departments d WHERE a.dept_id=d.dept_id; The SQL statement demonstrates the use of which of the following?

Column aliases and table aliases

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)

Consider the User list shown in the exhibit. Which group of SQL statements will give the General Manager all rights over the Products relation and grant selection privileges to all database users?

GRANT ALL ON Products TO gheer455; GRANT SELECT ON Products TO PUBLIC;

David has completed his design work on the Tsunami database. Which of the following SQL statements will allow Ken to read and add records to the Zones relation?

GRANT SELECT, INSERT ON Zones TO Ken;

Ken is the administrator of the ACME Corporation database. The Users list shown in the exhibit indicates various employees, their titles and their user names. Ken is beginning to assign permissions to various relations. Ken must give the Sales Reps the ability to read, add and modify records in the Orders relation. Which SQL statement will accomplish this task and not assign any unnecessary privileges?

GRANT SELECT, INSERT, UPDATE ON Orders TO sspade, srawlins;

Ken is the administrator of the ACME Corporation database. The Users list shown in the exhibit indicates various employees, their titles and their user names. Ken needs to assign permissions to various relations. The HR Manager must be able to read, add, change and delete records in the Employees relation. She must also be able to pass the permissions to her assistant. Which SQL statement or statements will accomplish this task and not assign any unnecessary privileges?

GRANT SELECT, INSERT, UPDATE, DELETE ON Employees TO fcain352 WITH GRANT OPTION;

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');

Consider the relation and the new record shown in the exhibit. Which of the following SQL statements will correctly add the new record to the Location relation?

INSERT INTO Location (l_num, l_name, l_phone, l_fax) VALUES (5, 'Garden City', NULL, NULL);

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 (l_num, l_name, l_phone, l_fax) VALUES (5, 'Mesa', '480-926-9214', '480-926-9200');

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 Employee 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 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 following SQL statement: SELECT * FROM EMPLOYEE WHERE title = 'Web Designer' OR title = 'Developer' 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 Employees WHERE title LIKE '%Manager';

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 l_num = 3 OR NOT l_state = 'NY';

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;

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 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 Assets relation shown in the exhibit: Which of the following SQL statements will return unique operating systems?

SELECT DISTINCT OS FROM Assets;

Which of the following statements are common in SQL?

SELECT and CREATE

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

The SQL standard is administered by which of the following bodies?

The International Organization for Standardization (ISO) and the American National Standards Institute (ANSI)

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.