Database Concepts & Administration Exam 2

Unlike base table data, a view ___ normally store data values

does NOT

Using views in INSERT, UPDATE, and DELETE statements is ___

problematic

You can use ___ to rename a column or a table or give a name to a computed column or formatted column

AS

The ___ is used with a SELECT statement to return only unique values or unique rows

DISTINCT

___ DATABASE deletes the database including all tables in the database

DROP

To find who is the supervisor of each employee, you should use:

SELECT ... FROM Employe E INNER JOIN Employee S on E.SupervisorID = S. SUpervisorID

A ___, sometimes called a nested query or inner query, is a query within another SQL query

subquery

The ___ is placed inside parentheses ()

subquery

When a table with a FK constraint references a table's PK, the table with the PK cannot be deleted with a DROP TABLE statement unless the table with the FK constraint is deleted first

TRUE

When two tables are combined by comparing columns from the first and second tables, it is possible to compare any columns with comparable data types although the result set may not make any logical sense

TRUE

You can use all of JOIN, subquery, aggregate functions with GROUP BY and HAVING, WHERE, and ORDER BY in one query

TRUE

* is used to display all ___ in a table

columns

A subquery is ___ when the subquery's WHERE clause references a column from the outer query

correlated

1,147,463,647

int

A(n) ___ compares columns with an operator other than =. such as, <,>

non-equijoin

When you want to assure that a user must type in some value(not leaving a column blank), you should use:

null value control(constraint)

43.23

numeric(4,2)

5623.56

numeric(6,2)

Symbols as a country name

nvarchar(50)

To retrieve all suppliers including those who may not have received any purchase order, you should us:

SELECT ... FROM Supplier LEFT OUTER JOIN PurchaseOrder ON

Which of the following is the correct syntax of the ROUND function to round up/down to 2 decimals

SELECT ..., ROUND(UnitPriceCharged*DiscountRate, 2)

To retrieve records from Customer table whose DateOfBirth is before January 1, 2017, which of the following SQL statements do you need to use?

SELECT ... FROM Customer WHERE DateOfBirth < '1/1/2017'

To delete only one existing record among 100,000 records stored in the Order Line table, you should use:

DELETE FROM OrderLine WHERE ProductID = 1 AND OrderNo = 1

To retrieve records from Customer table whose DateOfBirth is January 1, 2020, which of the following SQL statements do you need to use?

SELECT ... FROM Customer WHERE DateOfBirth = '1/1/2017'

To join Product and OrderLine tables, you should use:

SELECT*FROM Product P INNER JOIN OrderLine OL ON P.ProductID = OL.ProductID

To join Supplier and Purchase Order tables, you should use:

SELECT*FROM Supplier S INNER JOIN PurchaseOrder P ON S.SupplierID = P.SupplierID

The ___ statement displays rows from a table named in the ___ clause

SELECT, FROM

SELECT...FROM...WHERE HireDate BETWEEN '2000-01-01' AND '20202-01-01' is equal to

SELECT...FROM...HireDate >= '2000-01-01' AND HireDate <= '2019-12-31'

___ is a high-level computer language for storing, manipulating, & retrieving data in a relational database

SQL

___ returns the lowercase s

LOWERS(S)

Which of the following is "not equal"?

LastName != 'Smith' , NOT LastName = 'Smith' , LastName <> 'Smith'

___ TABLE deletes a table including all records of the table

DROP

11:59pm

time

Integer between 0 and 255

tinyint

Correlated subqueries often use the ___ operator

EXISTS

___ operator, which returns TRUW if a subquery selects at least one row and FALSE if no rows are selected

EXISTS

___ is used to specify which logical operators are evaluated first

( )

The ___ TABLE statement adds, deletes, or modifies columns on an existing table

ALTER

___ returns the string that results from concatenating the string arguments

CONCAT(s1,s2,...)

A constraint an be given a name using the ___ keyword, followed by the constraint name and declaration

CONSTRAINT

To format values stored in DATETIME data type column to the 'mm/dd/yyyy' format in SQL Server, you should use:

CONVERT(VARCHAR(10), OrderDate ,101)

____ creates a materialized view

CREATE MATERIALIZED VIEW

___ returns the difference between 2 dates in number of days

DATEDIFF( )

The ___ constraint is used in a CREATE TABLE statement to specify a column's default value when no value is provided

DEFAULT

When a table with a FK constraint references a table's PK, the table with the PK can be deleted with a DROP TABLE statement even when the table with the FK constraint is still in the database

FALSE

You can NOT nest a SELECT statement in another SELECT statement

FALSE

Aggregate functions are commonly used with the ___ clause

GROUP BY

The ___ clause groups rows with identical values into a set of summary rows when you want to use an aggregate function for each group

GROUP BY

The ___ operator is used in a WHERE clause to determine if a value matches one of several values

IN

In Microsoft SQL Server, to add a new purchase order record to the Purchase Order table whose PK (PONo) uses IDENTITY to automatically put a sequential number in Microsoft SQL Server, you should use:

INSER INTO PurchaseOrder (OrderDate, SupplierID) VALUES (getdate(),1)

The ___ adds rows to a table

INSERT INTO

In Microsoft SQL Server, to add a new customer order record to the Customer Order table whose PK (OrderNo) uses IDENTITY to automatically put a sequential number, you should use:

INSERT INTO CustomerOrder (OrderDate, CustomerID) VALUES (getdat(), 1)

To add records using records in another table, you should use:

INSERT INTO tablename1 (PKfield, field1, field2) SELECT PKID, field1, field2 FROM tablename 2 WHERE field3 > 0

The ___ in an INSERT statement specified the table and columns where data is to be added

INTO

___ tests if a value is not NULL

IS NOT NULL

___ tests if a value is NULL

IS NULL

While = requires exact match, the ___ operator with the two wildcard characters '%' and '_' allows you to do partial match

LIKE

___ returns the month from the date data value

MONTH( )

Which of the following logical operator is NOT supported by the WHERE clause?

NOR

The ___ constraint is used in a CREATE TABLE statement to prevent a column from having a NULL value

NOT NULL

The ___ clause sorts a query result set by one of more columns in ascending or descending order

ORDER BY

Which of the following is NOT an aggregate function?

ROUND( )

___ returns n rounded to d decimal places

ROUND(x, y)

To find companies whose name contains 'jack' anywhere in its name, which of the following SQL statements do you need to use?

SELECT ... FROM ... WHERE CompanyName LIKE %jack%

To sort records from Customer table using DateOfBirth in the ascending order, which of the following SQL statements do you need to use?

SELECT ... FROM Customer ORDER BY DateOfBirth

To sort records from Customer table using DateOfBirth in descending order, which of the following SQL statements do you need to use?

SELECT ... FROM Customer ORDER BY DateOfBirth DESC

To retrieve all records from the Employee table and all records from the Customer Order table regardless the records have matching records in the other table, you should use:

SELECT ... Employee FULL OUTTER JOIN CustomerOrder ON...

To count the number of records for different CustomerType values, which of the following SQL statements do you need to use?

SELECT CustomerType, COUNT(CustomerID) FROM Customer GROUP BY CustomerType

To create a list of products that were sold at a price higher than the average list price of all products, you should use:

SELECT ProductID FROM OrderLine WHERE PriceCharged > (SELECT AVG(ListPrice) FROM Product)

To find products whose Unit Price is greater than 100 but less than 1000, which of the following SQL statements do you need to use?

SELECT ProductID, ProductName, UnitPrice FROM Product WHERE UnitPrice >100 AND UnitPrice < 1000

___ returns the substring from string that starts at position pos and has length len

SUBSTRING(string, pos, len)

A view can be used in a SELECT statement as if it is a table

TRUE

The ___ constraint ensures that all column values are unique

UNIQUE

The ___ statement modifies data values of existing rows in a table

UPDATE

To update the values of two fields of an existing record in the Customer table, you should use:

UPDATE Customer SET LastName = 'New Last Name', FirstName = 'New First Name' WHERE CustomerID = 1

To update the values of two columns of an existing record in the Supplier table, you should use:

UPDATE Supplier SET SupplierName = 'New Supplier Name', DateModified = getdate() WHERE SupplierID = 1

The ___ specifies the column values to be added

VALUES

When you create a new table in SQL Server the default choice is:

a sorted table

Which of the following is NOT an objective of selecting a proper data type for table columns?

accommodate all possible values, minimize storage space, support all data manipulation

To avoid "ambiguous column name" error when you join tables, you should:

add a table alias in front of every column name (using AS), declare a table alias in the FROM clause (using AS)

Which of the following is NOT a goal of the physical database design?

assure all tables are in 3NF

3,357,483,823

bigint

GROUP BY clause must appear ___ the ORDER BY clause and ___ the WHERE clause

before, after

Which of the following is an advantage of views

can be used to hide sensitive data from database users by permitting access to the view but NOT to the base table, complex or difficult SELECT statements can be saved as a view and database users can use the view with a simpler query, & an optimal SELECT statement can be saved as a view and distributed to database users

Abbreviation of states of the US

char(2)

02101 (one of the zip codes in Boston)

char(5)

A ___ combines two tables without comparing columns and all possible combinations of rows from both tables appear in the result

cross join

___ creates, alters, and drops tables

data definition language (DDL)

Which of the following is a sublanguage of SQL

data definition language (DDL), data query language (DQL), data manipulation language (DML), data control language (DCL)

9/24/2022

date

9/24/2022 11:59pm

datetime

____ should be used to put a value of a column like the Order Date column of the Customer Order table

default value constraint

A(n) ___ compares columns of two tables with the = operator

equijoin

When two tables are combined by comparing columns from those, what are the first and second tables in the FROM clause called?

first: left & second: right

___ (CustomerID) REFERENCES Customer (CustomerID) can create a FK constraint when you create Customer Order table

foreign key

When two tables are combined by comparing columns from the first and second tables, usually a ___ key of one table to the ___ key of another are compared

foreign, primary

___ selects all left and right table rows, regardless of match

full outer join

If Customer Order table and Order Line table store records (rows) of the last 10 years, you should use:

horizontal partitioning

___ selects only matching left and right table rows

inner join

Which of the following is a common error when database users write INSERT statements?

inserting duplicate PK or FK data values that do match an existing PK, inserting PK data values for auto-increment columns, & inserting NULL values for columns that are NOT NULL

Which one of the following is FALSE about denormalization?

it reduces a chance of having insertion anomalies

A9n) ___ is a SELECT statement that combines data from two tables into a single query result

join

When two tables are combined by comparing columns from the first and second tables, those columns ___ data types

must have comparable

One Japanese letter

nchar(1)

___ (EmployeeID) can create the PK constraint when you create Employee table

primary key

___ (StudentID, CourseNo, Year, Semester) can create the composite PK constraint when you create Enrollment table

primary key

When you want to reject a negative value from the Quantity Ordered column of the Order Line table, you should use:

range control (check constraint)

___ selects all right table rows, but only matching left table rows

right outer join

An SQL ___ is a complete command composed of one or more ___

statement, clause

In a ___, a table is joined to itself

self-join

9/24/2022 11:59pm up to June 6, 2079

smalldatetime

32,321

smallint

Which of the following is TRUE about referential integrity rule??

to make SET TO DEFAULT referential integrity rule, the FK column must have the default value

Which of the following is TRUE about referential integrity rule?

to make SET TO NULL referential integrity rule, the FK column must allow the NULL value

Which of the following is NOT a good candidate for denormalization?

two tables with a one-to-one relationship, a reference table, associative entity

When you want to assure that two employees will NOT have the exact same login, you should use:

unique constraint

Human's last name in English

varchar(50)

A(n) ___ is a query saved in a database

view

Which of the following is TRUE about foreign key constraint on the PK and matching FK, Customer ID of the Customer table and Customer ID of the Customer Order table?

when you type in 4 as Customer ID of a record in Customer Order table, one of records in the Customer table must have 4 in its Customer ID column

The ___ constraint limits the range of a column's values

CHECK

___ DATABASE statement creates a new database

CREATE

___ TABLE statement creates a new table

CREATE

The ___ statement creates a view

CREATE VIEW

When you modify the data values of existing rows in a table, you use the ___ to specify the new column values

SET

An SQL statement may be written on a single line, but good practice is to write each clause on a separate line

TRUE

When you modify the data values of existing rows in a table, you use the ___ clause to determine which rows are updated, without it, all rows in that table will be updated

WHERE

When you remove existing rows in a table, you use the ____ clause to determine which rows are updated, without it, all rows in that table will be deleted

WHERE

The ___ clause is combined with the ___ statement to filter the result set

WHERE, SELECT

To delete only one existing record among 1,000 records stored in the Customer table, you should use

DELETE FROM Customer WHERE CustomerID = 1

The ___ statement remove existing rows from a table

DELETE

To use an aggregate function to filter some rows from a result set, you need to use ____

HAVING

To retrieve records from Customer table whose DateOfBirth is January 1, 2017, or February 2, 2017, or March 3, 2017, which of the following SQL statements do you need to use?

SELECT ... FROM Customer WHERE DateOfBirth IN ('1/1/2017', '2/2/2017', '3/3/2017')

To retrieve all customers, including those who may not have place any order, you should use:

SELECT ... FROM CustomerOrder RIGHT OUTER JOIN Customer ON...

To retrieve Order Line records whose ShippedDate field does not have any data value yet, which of the following SQL statement do you need to use?

SELECT ... FROM OrderLine WHERE ShippedDate IS NULL

To multiply QuantityOrdered and UnitPriceCharged of OrderLine table and name the column as Total, which of the following SQL statements do you need to use?

SELECT ..., QuantityOrdered*UnitPriceCharged AS Total... FROM OrderLine

To concatenate First Name, a space, and Last Name of employee records, which of the following SQL statements do you need to use?

SELECT CONCAT(FirstName, ' ', LastName) FROM Employee

To retrieve two columns of the Customer table, which of the following SQL statements do you need to use?

SELECT LastName, FirstName FROM Customer

To create a list of customers and employees that are in two separate tables, you should use:

SELECT LastName, FirstName, EmailAddress FROM Customer UNION SELECT LastName, FIrstName, EmailAddress FROM Employee

To count the number of records for different OrderDate values in year 2017, which of the following SQL statements do you need to use?

SELECT OrderDate, COUNT(OrderNo) FROM CustomerOrder WHERE YEAR(OrderDate) = 2017 GROUP BY OrderDate

To display only customer orders that requested more than 3 products, which of the following SQL statements do you need to use?

SELECT OrderNo, COUNT(ProductID) FROM OrderLine GROUP BY OrderNo HAVING COUNT(ProductID)>3

To display only customer orders that requested more than 5 products, which of the following SQL statements do you need to use?

SELECT OrderNo, COUNT(ProductID) FROM OrderLine GROUP BY OrderNo HAVING COUNT(ProductID)>5

To compute total using Quantity Ordered, Unit Price Charged, and Discount Rate for each row in the Order Line table when Discount Rate contains values like 0,0.05,0.1 for "no discount", "5% discount", or "10% discount", respectively, you should use:

SELECT QuantityOrdered * UnitPriceCharged * (1-DiscountRate) FROM OrderLine

Any number of rows may be added with a single INSERT statement

TRUE

In a correlated subquery, the subquery is executed for each row the outer query tries to examine

TRUE

The result set of an outer join contains the NULL values in the columns from a table for the rows that do NOT have a matching row in that table because there is not data value to display

TRUE

Which of the following is a reason why using views in INSERT, UPDATE, and DELETE statements is problematic?

a view may NOT contain the PK of the base table, a view may contain a column that does NOT correspond to a column of the base table, & a view may join two or more base tables and thus DELETE operations in a view mat not be defined to clearly show which row should be the deleted from the base table/tables

When a table stores only 300 records at most, which of the following data types should you use for the primary key field assigning sequential numbers (1,2..300)?

the PK field should be smallint

The HAVING clause must appear ___ the GROUP BY clause but ___ the optional ORDER BY clause

after, before

A(n) ___ is a temporary name assigned to a column or table using the AS keyword although AS is NOT required

alias

If indexes speed up data retrieval, then why don't we index all fields in a transactional database?

the overhead of maintaining the indexes from inserting, deleting, and updating the indexed records may outweigh the benefits derived

Two tables are combined by comparing columns from the first and second tables, usually ___ the = operator

with

A subquery is ___ when the subquery's WHERE clause references a column from the outer(main) query

correlated

___ grants and revokes permissions to and from users

data control language (DCL)

Which one of the following is FALSE about referential integrity rule on the PK and matching FK, Customer ID of the Customer table and Customer ID of Customer Order table?

you chose the Restrict(or no action) option of the referential integrity. now, you can delete a record from Customer Order table only after you delete the record of the customer from the Customer table who has placed that customer order

Which of the following is FALSE about index?

email address column of Employee table should have a non-unique secondary index, PK field should have a non-unique secondary index, FK columns automatically have a unique index, & Candidate key columns should have non-unique secondary index

___ selects all left table rows, but only matching right table rows

left outer join

A ____ view is a view for which data is stored at all times, which requires refreshing the view when the data in a base table used in the view are changed

materialized