SQL Database Fundamentals - Regional 2016

Aggregate functions ignore null values except for which of the following functions? - AVG - COUNT - MAX - SUM

COUNT

The acronym SQL stands for - Standard Query Language - Structured Quantitative Language - Structured Query Language - None of the above

Structured Query Language

RDBMS is the basis for SQL, and for all modern database systems except: - MS SQL Server - IBM DB2 - RPG DB - MySQL

RPG DB

Which of the following can SQL not do? - execute queries against a database - retrieve data from a database - insert records in a database - SQL can do all of the above

SQL can do all of the above

SQL can create views in a database. - True - False

False

Which of the following is not an Exact Numeric data type in SQL Server? - Float - Int - Decimal - Tinyint

Float

What is not true about a data dictionary? - It is a file that defines the basic organization of a database. - Is easily edited by the database users - Contains a list of all files in the database, - Contains the number of records in each file, and the names and types of each field.

Is easily edited by the database users

A row is to a tuple as a column is to an attribute. - True - False

True

Which of the following is NOT a data type category of SQL Server? - Exact Numerics - Approximate Numerics - ANSI character strings - Unicode character strings

ANSI character strings

What is not true about NULL Values? - A value of NULL indicates that the value is unknown - A value of NULL is different from an empty or zero value - All null values are equal - Comparisons between two null values, or between a NULL and any other value, return unknown because the value of each NULL is unknown

All null values are equal

In order to create a website or Windows desktop application that accesses and displays data from a database, you will need: - An RDBMS database program (i.e. MS Access, SQL Server, MySQL) - To use a server-side scripting language, like PHP or ASP - To use SQL to get the data you want - All of the above

All of the above

In referencing the format notation for the UPDATE command: UPDATE target SET col_name1=..., the UPDATE target specifies a(n): - Target table - Cursor - File to update - All of the above

All of the above

When a schema is created, it is owned by a principal. A principal is any entity or object that has access to SQL Server resources. Which of the following is included in SQL Server resources? - Windows domain logins - Database roles - Application roles - All of the above are SQL Server resources

All of the above are SQL Server resources

Which of the following are restrictions on the TRUNCATE TABLE command: - The table is referenced by a FOREIGN KEY constraint - The table participates in an indexed view - The table was published using transactional or merge replication - All of the above are restrictions

All of the above are restrictions

Which of the following is true about the domain of a database? - The set of all entities that can be used in a database is called the domain of the database. - Database domains can be finite or infinite. - Most (but not all) database domains include strings and numbers as subsets, and hence they are infinite. - All of the above are true.

All of the above are true

A database schema: - is the structure of a database system - is described in a formal language supported by the database management system - defines the tables, the fields in each table, and the relationships between fields and tables. - All of the above are true about a database schema

All of the above are true about a database schema

The ALTER statement is used to accomplish which of the following in an existing database table? - ADD - DELETE - MODIFY - All of the above can be accomplished with the ALTER statement

All of the above can be accomplished with the ALTER statement

SQL commands are generally grouped into four categories. Which of the following is not a category of SQL commands? - DDL (Data Definition Language) - DAL (Data Abstraction Language - DML (Data Manipulation Language) - DCL (Data Control Language)

DAL (Data Abstraction Language)

Which of the following is not a Date and Time data type in SQL Server? - Datetime - Smalldatetime - Datetime2 - Datetimeinset

Datetimeinset

In relational databases, a table is a set of data elements (values) using a model of vertical rows (which are identified by their name) and vertical columns, the cell being the unit where a row and column intersect. - True - False

False

RDBMS is an acronym for Relational Database Monitoring System. - True - False

False

Which of the following is NOT consistent with good database design? - Embrace data redundancy as a cross-check - Consider the current network, operating system, and software being used, including software that will interface with the database - Database user requirements or user expectations - Need for data to interface across multiple operating systems or be available to other programs

Embrace data redundancy as a cross-check

ALTER TABLE does not acquire a schema modify (SCH-M) lock on the table to make sure that no other connections reference even the metadata for the table during the change. - True - False

False

DROP TABLE can be used to drop a table that is referenced by a FOREIGN KEY constraint. - True - False

False

SQL can create new tables in a database, but cannot create a new database. - True - False

False

Which of the following will be removed in a future version of Microsoft SQL Server? - Nvarchar(max) - Image - Varchar - Varbinary(max)

Image

SQL is an ANSI standard, what does the acronym ANSI stand for: - American North Standards Institute - Algorithmic Notations Standards Institute - American National Standards Initiative - None of the above

None of the above

Which of the following is an example of a DAL command? - ALTER - CREATE - DROP - None of the above are DAL commands

None of the above are DAL commands

Which of the following are Character String data types in SQL Server? - Char - Varchar - String - Only A and B

Only A and B

Which of the following is NOT removed by the DROP TABLE in SQL Server? - Triggers - Constraints - Views - Permissions

Permissions

The CREATE command is used to create objects such as tables, indexes, views, stored procedures and triggers. - True - False

True

The SET clause in an UPDATE command specifies the columns in the table to update and their new values. - True - False

True

The following SQL Server T-SQL code is syntactically correct. CREATE TABLE dbo.Employee (EmployeeID int PRIMARY KEY CLUSTERED); - True - False

True

You can change the length, precision, or scale of a column by specifying a new size for the column data type in the ALTER COLUMN clause. - True - False

True

Which of the following is NOT true about table names: - They can have performance benefits - They make databases self-documenting and easier to code against - They should reflect their business meaning - All of the above are true about table names

They can have performance benefits

A FOREIGN KEY constraint is used to reference another table. Foreign keys can be single-column keys or multicolumn keys. - True - False

True

Aggregate functions can be used as expressions in the select list of a SELECT statement. - True - False

True

Aggregate functions perform a calculation on a set of values and return a single value. - True - False

True

Database schemas are generally stored in a data dictionary. - True - False

True

If you omit the WHERE clause, every row in the table is updated with the same value. - True - False

True

In SQL Server, each column, local variable, expression, and parameter has a related data type. A data type is an attribute that specifies the type of data that the object can hold. - True - False

True

Permissions are a set of defined levels of access to a securable and are applied individually or collectively to a securable. - True - False

True

SQL can set permissions on tables, procedures, and views. - True - False

True

Tables can also be referred to as an array because they have similar properties. - True - False

True

Which of the following update statements are syntactically correct assuming the references to the table and field names are legitimate - UPDATE Customers SET ContactName= "Alfred Schmidt", City="Hamburg" WHERE CustomerName= "Alfreds Futterkiste"; - UPDATE Customers USING ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Futterkiste'; - UPDATE TABLE Customers SET ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Futterkiste'; - UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg' WHERE CustomerName='Alfreds Futterkiste';

UPDATE Customers SET ContactName='Alfred Schmidt', City = 'Hamburg' WHERE CustomerName='Alfred Futterkiste';

A relational database is: - A single table containing all the data - A database that does not incorporate the table/key model - uses the BASE system (basically available, soft-state, eventually consistent) - a collection of data items organized as a set of formally-described tables from which data can be accessed

a collection of data items organized as a set of formally-described tables from which data can be accessed

In flat file databases, a table: - is a set of data elements (values) using a model of vertical columns and horizontal rows - cell is the unit where a row and column intersect - has a specified number of columns, but can have any number of rows - is all of the above

is all of the above

Which of the following is NOT true about the TRUNCATE TABLE command in SQL Server: - removes all rows from a table without logging the individual row deletions? - is similar to the DELETE statement with no WHERE clause - is slower than the DELETE command - uses fewer system and transaction log resources

is slower than the DELETE command

The definition of a Domain is: - A normalized set of tables - The possible values of an attribute, i.e. column - The possible values of an attribute, i.e. row - A collection of records

the possible values of an attribute, i.e. column

If the user does not supply a constraint name in a CREATE statement, - a logical error will result - a syntax error will be generated and the statement will not consummate - the system will supply a constraint name - SQL Server will ignore the constraint and the statement will consummate without reference to the constraint

the system will supply a constraint name