Database Management Exam 2

Which SQL keyword is used to change the structure, properties or constraints of a table? A. ALTER B. SET C. CREATE D. SELECT

A. ALTER

In an SQL query, which SQL keyword is used to link two conditions that both must be true for the rows to be selected? A. AND B. EXISTS C. OR D. IN

A. AND

In an SQL query of two tables, which SQL keyword indicates that we want data from all the rows of one table to be included in the result, even if the row does not correspond to any data in the other table? A. Both LEFT JOIN and RIGHT JOIN are correct B. RIGHT JOIN C. LEFT JOIN D. INCLUDE

A. Both LEFT JOIN and RIGHT JOIN are correct

Which SQL keyword is used to delete a table's structure? A. DROP B. DELETE C. DISPOSE D. ALTER

A. DROP

In an SQL query, which SQL keyword is used to specify the table(s) to be used? A. FROM B. WHERE C. EXISTS D. SELECT

A. FROM

In an SQL query, which SQL keyword is used with built-in functions to group together rows that have the same value in a specified column or columns? A. GROUP BY B. ORDER BY C. SELECT D. SORT BY

A. GROUP BY

Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you want to find all employees who are also customers, which SQL keyword would you most likely use? A. INTERSECT B. UNION C. EXCEPT D. UNION ALL

A. INTERSECT

Based on the tables below, which of the following SQL commands would return the average customer balance grouped by SalesRepNo?GENERAL SALES DATABASE: A. SELECT AVG (Balance) FROM CUSTOMER GROUP BY SalesRepNo; B. SELECT AVG (Balance) FROM CUSTOMER WHERE SalesRepNo; C. SELECT AVG (Balance) FROM CUSTOMER, SALESREP WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo; D. SELECT AVG (Balance) FROM CUSTOMER ORDER BY SalesRepNo;

A. SELECT AVG (Balance) FROM CUSTOMER GROUP BY SalesRepNo;

In an SQL query, which built-in function is used to total numeric columns? A. SUM B. AVG C. COUNT D. MAX

A. SUM

Assuming the "Quantity" column of an ORDER table contains integer data, what does COUNT(Quantity) compute? A. The number of non-null values in the Quantity column B. The number of rows in the ORDER table C. The number of distinct values in the Quantity column D. The number of non-zero values in the Quantity column

A. The number of non-null values in the Quantity column

In an SQL query, which SQL keyword is used to state the condition that specifies which rows are to be selected? A. WHERE B. FROM C. SET D. SELECT

A. WHERE

In an SQL query, which of the following symbols is used by ANSI SQL to represent a single unspecified character? A. _ (underscore) B. ? (question mark) C. * (asterisk) D. % (percent)

A. _ (underscore)

When one SQL query is embedded in another SQL query, this is referred to as a ________. A. subquery B. join C. WHERE Query D. subset query

A. subquery

The SQL keyword used to limit column values to specific values is ________. A. UNIQUE B. CHECK C. CONSTRAINT D. NOT NULL

B. CHECK

Which keyword is used to remove one or more rows from a table? A. ERASE B. DELETE C. SET D. INSERT

B. DELETE

Referential integrity constraints are implemented in SQL using the ________ syntax. A. PRIMARY KEY B. FOREIGN KEY C. DEFAULT D. UNIQUE

B. FOREIGN KEY

In an SQL query, which SQL keyword is used to sort the result table by the values in one or more columns? A. GROUP BY B. ORDER BY C. SELECT D. SORT BY

B. ORDER BY

Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following would find all employees whose name begins with the letter "S" using Microsoft Access? A. SELECT *FROM EMPLOYEE WHERE Name IN ['S']; B. SELECT *FROM EMPLOYEE WHERE Name LIKE 'S*'; C. SELECT *FROM EMPLOYEE WHERE Name LIKE 'S%'; D. SELECT EmpNo FROM EMPLOYEE WHERE Name LIKE 'S';

B. SELECT *FROM EMPLOYEE WHERE Name LIKE 'S*';

Suppose tables EMPLOYEE and CUSTOMER both store address information, and you want to send a letter to all employees and customers of your company to make a major announcement. Which SQL keyword would you most likely use here? A. INTERSECT B. UNION C. UNION ALL D. JOIN

B. UNION

Based on the tables below, which of the following SQL statements would increase the balance of the Gonzales account by $100 to a total of $450? A. SELECT Gonzales FROM CUSTOMER INSERT VALUES (450) INTO Balance; B. UPDATE CUSTOMER SET Balance = 450 WHERE CustName = 'Gonzales'; C.INSERT INTO CUSTOMER VALUES (450) SELECT Balance WHERE CustName = 'Gonzales'; D. SELECT Gonzales FROM CUSTOMER INSERT VALUES PLUS (100) INTO Balance;

B. UPDATE CUSTOMER SET Balance = 450 WHERE CustName = 'Gonzales';

Which SQL keyword is used to impose restrictions on a table, data or relationship? A. SET B. SELECT C. CONSTRAINT D. CREATE

C. CONSTRAINT

Suppose your company stores EMPLOYEE and CUSTOMER data in separate tables. If you want to find all customers who are not also employees, which SQL keyword would you most likely use? A. INTERSECT B. UNION C. EXCEPT D. UNION ALL

C. EXCEPT

In an SQL query, which SQL keyword is used with GROUP BY to select groups meeting specified criteria? A. AND B. WHERE C. HAVING D. IN

C. HAVING

Which SQL keyword is used to add one or more rows of data to a table? A. DELETE B. SELECT C. INSERT D. UPDATE

C. INSERT

Which SQL keyword is used to change the values of an entire column? A. SELECT B. CHANGE C. SET D. INSERT

C. SET

When the correct SQL command is used to delete a table's structure, what happens to the data in the table? A. If the deleted table was a parent table, the data is added to the appropriate rows of the child table. B. If the deleted table was a child table, the data is added to the appropriate rows of the parent table. C. The data in the table is also deleted. D. Nothing because there was no data in the table since only an empty table can be deleted.

C. The data in the table is also deleted.

Which SQL keyword is used to change one or more rows in a table? A. SELECT B. INSERT C. UPDATE D. MODIFY

C. UPDATE

If you have a foreign key in a CUSTOMER table that references the primary key in a SALESREP table, the ON DELETE CASCADE syntax means that ________. A. when a CUSTOMER is deleted, that CUSTOMER's SALESREP is also deleted B. when a CUSTOMER is deleted, all SALESREPS are also deleted C. when a SALESREP is deleted, all CUSTOMERs of that SALESREP are also deleted D. when the SALESREP table is dropped, all CUSTOMERs must be deleted

C. when a SALESREP is deleted, all CUSTOMERs of that SALESREP are also deleted

In an SQL query, the built-in functions SUM and AVG work with columns containing data of which of the following data types? A. Integer B. Numeric C. Char D. Both Integer and Numeric are correct

D. Both Integer and Numeric are correct

In an SQL query, which SQL keyword is used to determine if a column value is equal to any one of a set of values? A. AND B. EXISTS C. OR D. IN

D. IN

Given the SQL statement: CREATE TABLE SALESREP ( SalesRepNo int NOT NULL, RepName char(35) NOT NULL, HireDate date NOT NULL, CONSTRAINT SalesRepPK PRIMARY KEY (SalesRepNo), CONSTRAINT SalesRepAK1 UNIQUE (RepName) ); We know that ________. A. RepName is the primary key B. RepName is a foreign key C. RepName is a surrogate key D. RepName is a candidate key

D. RepName is a candidate key

Given a table with the structure: EMPLOYEE (EmpNo, Name, Salary, HireDate), which of the following would find all employees whose name begins with the letter "S" using MySQL? A. SELECT * FROM EMPLOYEE WHERE Name IN ['S']; B. SELECT EmpNo FROM EMPLOYEE WHERE Name LIKE 'S'; C. SELECT * FROM Name WHERE EMPLOYEE LIKE 'S*'; D. SELECT * FROM EMPLOYEE WHERE Name LIKE 'S%';

D. SELECT * FROM EMPLOYEE WHERE Name LIKE 'S%';

Based on the tables below, which of the following commands in SQL would return only the name of the sales representative and the name of the customer for each customer that has a balance greater than 400? A. SELECT * FROM SALESREP, CUSTOMER WHERE Balance > 400; B. SELECT DISTINCT RepName, CustName FROM SALESREP, CUSTOMER WHERE Balance > 400; C. SELECT * FROM SALESREP, CUSTOMER WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo AND Balance > 400; D. SELECT RepName, CustName FROM SALESREP, CUSTOMER WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo AND Balance > 400;

D. SELECT RepName, CustName FROM SALESREP, CUSTOMER WHERE SALESREP.SalesRepNo = CUSTOMER.SalesRepNo AND Balance > 400;

In an SQL query, which SQL keyword is used to specify the column names to be used in a join? A. FROM B. SELECT C. JOIN D. WHERE

D. WHERE

One advantage of using the CONSTRAINT phrase to define a primary key is that the database designer controls the ________. A. name of the table B. name of the foreign key field C. name of the primary key field D. name of the constraint

D. name of the constraint

A WHERE clause can contain only one condition.

FALSE

SQL can only query a single table.

FALSE

The DELETE keyword is used to delete a table's structure.

FALSE

The MODIFY keyword is used to change a column value.

FALSE

The MODIFY keyword is used to change the structure, properties or constraints of a table.

FALSE

The SQL keyword WHERE is used to specify the table(s) that contain(s) the data to be retrieved.

FALSE

The built-in function SUM can be used with any column.

FALSE

One advantage of using the CONSTRAINT command to define a primary key is that the database designer controls the name of the constraint.

TRUE

One or more rows can be added to a table by using the INSERT statement.

TRUE

Rows in a table can be changed by using the UPDATE statement.

TRUE

SQL includes a data definition language, a data manipulation language, and SQL/Persistent stored modules.

TRUE

SQL is not a complete programming language. Rather it is a data sublanguage.

TRUE

The CHECK keyword is used to limit column values to specific values.

TRUE

The CONSTRAINT keyword can be used in conjunction with the SQL keywords PRIMARY KEY and FOREIGN KEY.

TRUE

The CREATE TABLE statement is used to name a new table and describe the table's columns.

TRUE

The PRIMARY KEY keyword is used to designate the column(s) that are the primary key for the table.

TRUE

The SET keyword is used to specify a new value when changing a column value.

TRUE

The SQL built-in function AVG computes the average of values in numeric columns.

TRUE

The SQL built-in function COUNT computes the number of rows in a query.

TRUE

The SQL keyword GROUP BY instructs the DBMS to group together those rows that have the same value in a column.

TRUE

The SQL keyword LIKE is used in SQL expressions to select partial string values.

TRUE

The SQL keyword SELECT is used to specify the columns to be listed in the query results.

TRUE

The UNIQUE keyword is used to define alternate keys.

TRUE

To obtain all columns, use an asterisk (*) wildcard character instead of listing all the column names.

TRUE

Unless it is being used to copy data from one table to another, the INSERT statement can be used to insert only a single row into a table.

TRUE