Structured Query Language

Consider the following SQL statement: SELECT * FROM Employee WHERE loc_num = '1003' AND NOT salary >= 18000; Using the Employee relation shown in the exhibit, how many records will be included in the result set of this SQL statement?

0

Consider the Employee relation shown in the exhibit. Employee Relation How many records will be returned by the following SQL statement? SELECT * FROM Employee WHERE salary > 36000;

1

Consider the following SQL statement: SELECT * FROM Employee WHERE salary BETWEEN 33100 AND 36000 AND NOT salary IN (33100, 36000); Considering the data shown in the exhibit, how many records will be returned when the SQL statement is executed?

1

Consider the following SQL statement: SELECT * FROM Employee WHERE loc_num = '1003' AND NOT salary > 18000; Using the Employee relation shown in the exhibit, how many records will be included in the result set of this SQL statement?

1

Consider the following SQL statement: SELECT * FROM Employee WHERE loc_num = '1004' AND title = 'manager'; Using the Employee relation shown in the exhibit, how many records will be included in the result set of this SQL statement?

1

Consider the following SQL statement: SELECT * FROM Orders WHERE Order_amt >= 1000 OR Cust_No = '1011'; Using the Orders relation shown in the exhibit, how many records will be returned by this SQL statement?

3

Consider the following SQL statement: SELECT * FROM Employee WHERE loc_num = '1004' OR title = 'manager'; Using the Employee relation shown in the exhibit, how many records will be included in the result set of this SQL statement?

3

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

3

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

All the records in the Wine_List relation will be deleted.

Which of the following commands can a DBA use to create a database?

CREATE SCHEMA

Consider the Registration relation shown in the exhibit. Registration Relation What will be the result set returned by the following SQL statement? SELECT Last_Name, First_Name, Student_ID FROM Registration WHERE Course_Code = 'M3455';

Chambers Teri S320 Trujillo Carlos S255

Consider the relation shown in the exhibit. Which of the following SQL statements would properly remove the record for the location at 410 N. Center St. in Mesa, AZ?

DELETE FROM Location WHERE l_num = 4;

Which of the following commands can a DBA use to delete a database?

DROP SCHEMA

Ken is the database administrator of the ACME Corporation database. The Employees relation is shown in the exhibit. Which group of SQL statements will give the HR Manager all rights over the table and grant selection privileges to all database users?

GRANT ALL ON Employees TO '00001'; GRANT SELECT ON Employees TO PUBLIC;

Ken is the database administrator of the ACME Corporation database. The Employees relation is shown in the exhibit. Which of the following SQL statements will give the HR Manager selection rights over the table and the ability to pass that privilege on to other users?

GRANT SELECT ON Employees TO '00001' WITH GRANT OPTION;

Ken is the database administrator of the ACME Corporation database. The Employees relation is shown in the exhibit. Ken wants to give the HR manager the ability to modify the records in the Employees relation, and the Administrative Assistant must have the ability to access the information in order to generate reports. Which group of SQL statements will accomplish this task?

GRANT UPDATE ON Employees TO '00001'; GRANT SELECT ON Employees TO '00003';

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, '12 Main St.', 'Mesa', 'AZ', '85204', '408-926-9214', '480-926-9200');

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_street, l_city, l_state, l_zip, l_phone, l_fax) VALUES (5, '12 Main St.', 'Mesa', 'AZ', '85204', '408-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? The figure shows the Location Relation table and the Newe record table. The Location Relation table has four columns I_num, I_street, I_city, I_state, I_zip, I_phone, and I_fax, with values in four rows. The New Record table shows an extra record that is to be added in the Location Relation table.

INSERT INTO Location (l_num, l_street, l_city, l_state, l_zip, l_phone, l_fax) VALUES (5, '71 Brook St.', 'Garden City', 'NY', '11530', NULL, '516-248-4300);

Consider the relation shown in the exhibit. Which of the following SQL statements would input data from the Location relation into the NY_Locations relation?

INSERT INTO NY_Locations SELECT l_num, l_city, l_state, l_zip, l_phone FROM Location WHERE l_state = 'NY';

What does the following DML statement accomplish? UPDATE Employees set salary = salary + salary*.1;

It increases the salary value for each record in the Employees table by 10 percent.

Ken is the database administrator for ACME, Inc. At one time, he had granted Alice, the Human Resources department manager, ALL PRIVILEGES on the Employees relation. Alice's user name is "Alice_012." Alice has now accepted a new position as the Vice President of Marketing, and no longer requires any access to the Employees relation. Which of the following SQL statements should Ken execute to ensure that Alice does not have unauthorized access to the Employees relation?

REVOKE ALL PRIVILEGES ON Employees FROM Alice_012;

Ken is the database administrator for ACME, Inc. He has granted Alice, a department manager, ALL PRIVILEGES on the Employee relation and included the WITH GRANT OPTION clause. Alice, in turn, granted SELECT privileges on the Employees relation to her assistant, Roger, and she included the WITH GRANT OPTION clause as well. Which of the following describes Roger's current privileges?

Roger can select data in the Employees relation and he can pass the SELECT privilege on to other users.

Consider the following relations shown in the exhibit. Using the Customers Relation, which of the following SQL statements would return the Customers2 Relation?

SELECT * FROM Customers WHERE Sales_Office = 'Atlanta';

Consider the following relations shown in the exhibit. Using the Customers Relation, which of the following SQL statements would return the Customers2 Relation?

SELECT * FROM Customers WHERE Satisfaction_Rate >= 92;

Consider the Employee relation shown in the exhibit. Which of the following SQL statements would return records for employees who do not work at location 1003 and who earn less than $34,000?

SELECT * FROM Employee WHERE NOT (loc_num = 1003 OR salary >= 34000); Loc_num should be an integer datatype, so putting quotes around it is a bad idea because it forces an implicit conversion. It can cause indexes to not be used, and give unexpected results in comparisons and sorting since '1003' is greater than '10000'

Consider the Employee relation shown in the exhibit. Which of the following SQL statements would return records for employees who work at location 1003 and do not earn more than $18,000?

SELECT * FROM Employee WHERE loc_num = '1003' AND NOT salary > 18000;

Consider the Orders relation shown in the exhibit: Employee Relation Which of the following SQL statements will return the records in ascending order by customer name and descending order by order amount for each customer?

SELECT * FROM Orders ORDER BY Cust_Name, Order_amt DESC;

Consider the Orders relation shown in the exhibit: Orders Relation Which of the following SQL statements will return the records for orders placed by Xyz Corp and list them in descending order by order amount?

SELECT * FROM Orders WHERE Cust_No = '1013' ORDER BY Order_amt DESC;

Consider the relation shown in the exhibit. Which of the following SQL statements would return a relation that excludes all orders less than $1,000 unless the order was placed by MicroWidget?

SELECT * FROM Orders WHERE Order_amt >= 1000 OR Cust_No = '1011';

Consider the Orders relation shown in the exhibit. Which of the following SQL statements would return the Orders2 Relation from the Orders Relation?

SELECT * FROM Orders WHERE Order_num = '1095';

Consider the following SQL statement: SELECT * FROM Employee WHERE loc_num = '1002' OR loc_num = '1004'; Considering the data shown in the exhibit, which of the following SQL statements is equivalent to the statement shown above?

SELECT * FROM Employee WHERE loc_num IN ('1002', '1004');

Consider the relation shown in the exhibit. Which of the following SQL statements would return all records for students with last names beginning with the letter "C"?

SELECT * FROM Registration WHERE Last_Name LIKE 'C%';

Consider the following relations shown in the exhibit. Using the Customers Relation, which of the following SQL statements would return the Customers2 Relation?

SELECT Cust_Name, Sales_Office, Sales_Rep_No FROM Customers WHERE Sales_Office = 'Chicago';

Consider the Customers and Orders relations shown in the exhibit. Which SQL statement would return the Order History Relation shown in the exhibit?

SELECT Customers.* , Orders.ord_num, Orders.ord_date FROM Customer, Orders WHERE Customers.ID_num = Orders.ID_num';

Consider the Registration relation shown in the exhibit. Registration Relation Rhonda needs to apply a SQL statement that will return the following unique course codes: M3455 A4343 S4511 M4422 M4433 S2212 Which SQL statement applied to the Registration relation will return this data?

SELECT DISTINCT Course_Code from Registration;

Which of the following is true of SQL?

SQL contains only data definition, data manipulation and data control commands.

Ken is the database administrator of the ACME Corporation database. The Employees relation is shown in the exhibit. What will be the result if Ken issues the following group of SQL statements? GRANT UPDATE, INSERT, DELETE ON Employees TO '00001'; GRANT UPDATE (title) ON Employees TO '00003';

The HR Manager will be able to modify records, add records and delete records, and the Administrative Assistant will be able to modify the title field only.

Consider the following DDL statement: CREATE SCHEMA ProTemps_Schema; What is the effect of executing this statement?

The statement will create a new, empty database named ProTemps_Schema

Consider the DBDL description of a relation for tracking employee information shown in the exhibit. What do you see as a potential problem with the table description? Employees (emp_num: integer NOT NULL emp_first_name: variable length character string length 25 NOT NULL emp_last_name: variable length character string length 25 NOT NULL emp_hire_date: date NOT NULL emp_dept: variable length character string length 15 NOT NULL emp_mgr: variable length character string length 25 NOT NULL emp_salary: variable length character string length 10 NOT NULL Primary Key: emp_num

The user will not be able to perform arithmetic operations on the emp_salary field.

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

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

Consider the relation shown in the exhibit. Location Relation Dara uses the following SQL statement to insert a new record: INSERT INTO Location (l_num, l_phone, l_fax) VALUES (5, '516-248-4329, '516-248-4300'); What values will be inserted into the l_street, l_city, l_state and l_zip fields for the new record?

These fields will be filled with null values.

Using the Employee relation shown in the exhibit, which of the following SQL statements would give each manager a ten percent salary increase?

UPDATE Employee SET salary = salary+(salary*.1) WHERE title = 'manager';

Schema is another term for:

database

A named group of related tables, views, and other database objects is a(n):

schema.

Consider the relation shown in the exhibit. Ken executes the following SQL statement: DELETE FROM Location WHERE l_state = 'AZ'; Location Relation Which of the following tables shows the Location relation after the execution of the SQL statement?

seven by five chart.

Consider the following SQL statement: SELECT * FROM Employee WHERE loc_num = '1004' AND title = 'manager'; Using the Employee relation shown in the exhibit, what is the output of this SQL statement?

seven by two chart.