CS 411 Midterm

Write an SQL query to create an index named RepBal with the keys RepNum and Balance. The query starts with "CREATE INDEX ..."

CREATE INDEX RepBal ON Customer (RepNum, Balance) ;

Write a query to return the number of rows where the State field is 'AZ' from the table Customers.

SELECT Count(state) AS NumOfAZ FROM customers WHERE state="AZ"

Write a query to return the number of rows where the State field is 'AZ' from the table Customers and provide a total of the Payment field.

SELECT Count(state) AS NumOfAZ, SUM(payment) AS totalPay FROM customers WHERE state="AZ"

Based on the Customer relation shown below, write a query to list the number, name, credit limit, and balance for all customers with credit limits that exceed their balances. Customer (CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum)

SELECT CustomerNum, customername,creditlimit, balance FROM Customer WHERE creditlimit > balance

Based on the relation shown, for each sales rep, write a query to list the rep number, the number of customers assigned to the rep, and the average balance of the rep's customers. The result should be grouped by rep number and ordered by rep number. Customer ( CustomerNum, CustomerName, Street, City, State, PostalCode, Balance, CreditLimit, RepNum )

SELECT RepNum, COUNT(*), AVG(Balance) FROM Customer GROUP BY RepNum ORDER BY RepNum ;

Based on the code shown, write a query to change the postal code of the student with ID 11433 to 14455. Student (StudentID, FirstName, LastName, Street, City, State, PostalCode)

UPDATE Student SET Zip='14455' WHERE StudentID='11433' ;

How are multiple entities stored in a database? a. each entity is stored as a row b. each entity is stored as an attribute c. each entity is stored as a table d. each entity is stored as a column

a

If B is functionally dependent on A, what can you also say? a. A functionally determines B b. A functionally determines another attribute in the table c. B functionally determines A d. B does not determine any other attribute

a

Program-data dependence is caused by: a. file descriptions being stored in each database application. b. data descriptions being stored on a server. c. data descriptions being written into programming code. d. data cohabiting with programs.

a

Which advantage of database processing makes it easier to make a change in the database structure? a. data independence b. integrity constraints c. redundancy control d. security controls

a

Which of the following contains a repeating group where the primary key is OrderNum? a. Orders (OrderNum, OrderDate, (ItemNum, NumOrdered) ) b. Orders (OrderNum, OrderDate, ItemNum, NumOrdered ) c. Orders (OrderNum, OrderDate) d. Orders (OrderNum, ItemNum, NumOrdered)

a

Which of the following is a characteristic of a table that is in first normal form? a. It doesn't contain repeating groups. b. It has at least two foreign keys. c. It doesn't have a primary key d. There are no alternate keys.

a

Which of the following is considered a disadvantage of a database system? a. a larger file size b. data dependence c. reduced integrity d. reduced productivity

a

Which of the following is true if two tables have the same number of columns and their corresponding columns represent the same type of data? a. They are union compatible. b. They are intersection compatible. c. They are difference compatible. d. They are product compatible.

a

Which term is another word for a row in a table? a. Tuple b. Field c. Group d. Relation

a

In SQL, what type of integrity are you enforcing by using the CHECK clause? a. Referential b. legal-values c. entity d. structural

b

What is an alternate key? a. foreign key b. column that could be a primary key but was not chosen c. primary key d. row that could be a primary key but was not chosen

b

What is each key that meets the criteria for a primary key called? a. alternate key b. candidate key c. functional key d. normal key

b

Which SQL command should you use to delete the Storehouse field from the Item table? a. ALTER TABLE Item DELETE Storehouse ; b. ALTER TABLE Item DROP COLUMN Storehouse ; c. ALTER Item DELETE Storehouse ; d. ALTER TABLE DELETE Storehouse ;

b

Which of the following statements is correct? a. In a nondatabase, file-oriented environment, data is often partitioned into several disjointed systems with each system having its own collection of files. b. User data cannot be combined and shared among authorized users. c. Database users should not have access to the same information. d. Controlling redundancy is easier with the nondatabase approach.

b

Which statement removes the table Category from a DBMS? a. DELETE TABLE Category ; b. DROP TABLE Category ; c. REMOVE TABLE Category ; d. RID TABLE Category

b

Which term can be described as the duplication of data and storing it in multiple locations? a. data independence b. redundancy c. data integrity d. Security

b

What is a column that you create for an entity to serve solely as the primary key and that is visible to users? a. synthetic key b. weak entity c. artificial key d. natural key

c

Which database term is a person, place, object, event, or idea for which you want to store and process data? a. attribute b. DBMS c. entity d. DBA

c

Which of the following is NOT true about a relation in a two-dimensional table? a. The entries in the table are single-valued. b. All values in a column are values of the same attribute. c. Two or more columns can share the same name. d. The order of rows is immaterial.

c

o create the primary key clause for the OrderLine table on the OrderNum and ItemNum fields, which statement should you use? a. PRIMARY KEY (OrderLine, OrderNum, ItemNum) b. KEY (OrderNum, ItemNum) c. PRIMARY KEY (OrderNum, ItemNum) d. PRIMARY (OrderNum, ItemNum)

c

In relational algebra, what command causes only certain columns to be included in the new table? a. SELECT b. DELETE c. PROGRAM d. PROJECT

d

What database rule states that no field that is part of the primary key may accept null values? a. legal-values integrity b. structural integrity c. referential integrity d. entity integrity

d

What type of functions are Count, Sum, Avg, Max, and Min? a. Accumulation b. allowed c. primary d. aggregate

d

When is a 1NF table automatically a 2NF table? a. when two or more columns make up the primary key b. when there are no interrelation constraints c. when there are only nonkey columns d. when the primary key contains only a single column

d

Which of the following is a disadvantage of using an index? a. You can only add an index when the table is created. b. You can't use indexes with foreign keys. c. Record retrieval is less efficient. d. The index must be updated whenever the data in the database changes.

d

Which of the following is true about big data? a. unstructured big data doesn't contain metadata b. a Twitter tweet is an example of structured big data c. all big data can be handled with traditional DBMS tools d. big data may be structured or unstructured

d

Which of the following statement fragments will only allow a CreditLimit of $5,000, $7,500, or $15,000? a. (CreditLimit IN (5000, 7500, 15000)) b. CHECK ((5000, 7500, 15000)) c. CHECK (CreditLimit LIKE (5000, 7500, 15000)) d. CHECK (CreditLimit IN (5000, 7500, 15000))

d