Basic SQL Quiz - 2

Combine multiple exact-match conditions with AND, using = for each exact value. All matching conditions must be in the WHERE clause, not the SELECT clause. LIKE is unnecessary unless you need pattern matching.

The BETWEEN operator selects values within a given range and is inclusive of both endpoints. It is equivalent to using >= and <= combined with AND. BETWEEN works with text, numbers, and dates.

The SELECT DISTINCT statement is used in SQL to return only unique (different) values from a column, filtering out all duplicate entries in the result set.

ORDER BY is the standard SQL clause used to sort query results in ascending or descending order. SORT BY and SORT are not valid SQL keywords for this purpose.

ORDER BY is the correct clause for sorting, and DESC specifies descending order. The column name should not be quoted, and the keyword is ORDER BY not SORT BY.

INSERT INTO table VALUES (values) is the standard SQL syntax for inserting a complete row. The table name must come after INSERT INTO, and VALUES must precede the actual values.

INSERT INTO table (column) VALUES (value) is the correct syntax for inserting into specific columns. The column list in parentheses must come before VALUES.

UPDATE table SET column=value WHERE condition is the correct syntax. SET assigns the new value, and WHERE specifies which rows to update based on the old value.

DELETE FROM table WHERE condition removes rows matching the condition. The WHERE clause is essential to specify which rows to delete.

COUNT(*) is the aggregate function that returns the total number of rows in a table. COLUMNS is not a valid SQL function for counting.

To solve this question, the user needs to know that the LEFT function extracts a specified number of characters from the left side of a given string. The CAST function converts a given expression to a specified data type. The LEN function returns the number of characters in a given string.

In this case, the LEFT function is extracting the first three characters from the string '026-100', which results in '026'. The CAST function is converting this string to an integer data type, resulting in the value 26. The LEN function then determines the number of characters in the resulting string, which is 2. Therefore, the correct answer is:

The Answer is: B. 2

To solve this question, the user needs to know about the SMALLDATETIME data type and how to cast values to it.

In SQL Server, SMALLDATETIME is a data type that stores a date and time with a precision of one minute. It is represented as a two-byte signed integer.

The CAST function is used to convert one data type to another. In this case, the value -1 is being cast to the SMALLDATETIME data type.

Now, let's go through each option and explain why it is right or wrong:

A. 1900-01-01 00:00:00.000: This option is incorrect because the SMALLDATETIME data type has a minimum value of 1900-01-01 00:00:00. The value -1 is less than the minimum value, so it cannot be converted to a SMALLDATETIME.

B. 1899-01-01 00:00:00.000: This option is incorrect because the SMALLDATETIME data type has a minimum value of 1900-01-01 00:00:00. The value -1 is less than the minimum value, so it cannot be converted to a SMALLDATETIME.

C. 1752-01-01 00:00:00.000: This option is incorrect because the SMALLDATETIME data type has a minimum value of 1900-01-01 00:00:00. The value -1 is less than the minimum value, so it cannot be converted to a SMALLDATETIME.

D. The system will generate an error. Only positive integer values can be converted to a SMALLDATETIME data type: This option is incorrect because the SMALLDATETIME data type can store both positive and negative integer values. However, the value -1 is less than the minimum value of the SMALLDATETIME data type, so it cannot be converted to a SMALLDATETIME.

The Answer is: D

ROUND(123.89, -1) rounds to the nearest tens place because the second parameter is -1. 123.89 rounded to the nearest ten is 120, and the default precision for DECIMAL results in SQL Server is two decimal places, giving 120.00. A positive parameter would round to decimal places.

The STR function converts numeric data to character data. The syntax is STR(number, length). If the specified length (3) is shorter than the number of digits in the value (6365), SQL Server returns asterisks to indicate an overflow.

With ARITHABORT OFF and ANSI_WARNINGS OFF, SQL Server returns NULL for division by zero instead of raising an error. ARITHABORT controls query termination on arithmetic errors, while ANSI_WARNINGS controls NULL/division-by-zero behavior. When both are OFF, the result is NULL rather than an error or infinity.

In SQL Server, the syscomments system table stores the actual text of views, stored procedures, triggers, and rules. The sysobjects table contains the names and types of objects, but not their underlying source code.

INT in SQL Server is a signed 32-bit integer with range -2,147,483,648 to 2,147,483,647. The maximum positive value is 2,147,483,647 (2^31 - 1). Options C and D show values for unsigned 32-bit integers, which SQL Server INT does not support.

Washington's birthday (1732) is before the minimum DATETIME value (January 1, 1753). INT can store dates as integers (e.g., YYYYMMDD format) covering any historical date. SMALLDATETIME has the same 1753 limitation. VARCHAR would work but is not ideal for date calculations or sorting.

The public role exists in EVERY database, INCLUDING master. Options A, B, and D are all true statements about the public role: it captures default permissions, cannot be dropped, and cannot have members assigned. Option C is false (and thus the correct answer) because it incorrectly claims the public role is NOT in master.

SMALLDATETIME in SQL Server stores dates from January 1, 1900 to June 6, 2079. The upper limit is 2079-06-06 23:59:59 because SMALLDATETIME uses 2 bytes for time (minutes only). Option D shows the DATETIME maximum, which extends to 9999. The specific date June 6, 2079 is the exact last valid date.