Basic SQL Quiz - 2

Description: Basic SQL Quiz - 2
Number of Questions: 20
Created by:
Tags: sql
Attempted 0/20 Correct 0 Score 0

With SQL, how do you select all the records from a table named "Persons" where the "FirstName" is "Peter" and the "LastName" is "Jackson"?

sql
  1. SELECT * FROM Persons WHERE FirstName LIKE 'Peter' AND LastName LIKE 'Jackson'

  2. SELECT * FROM Persons WHERE FirstName='Peter' AND LastName='Jackson'

  3. SELECT FirstName='Peter', LastName='Jackson' FROM Persons


Correct Option: B

With SQL, how do you select all the records from a table named "Persons" where the "LastName" is alphabetically between (and including) "Hansen" and "Pettersen"?

sql
  1. SELECT LastName>'Hansen' AND LastName

  2. SELECT * FROM Persons WHERE LastName BETWEEN 'Hansen' AND 'Pettersen'

  3. SELECT * FROM Persons WHERE LastName>'Hansen' AND LastName


Correct Option: B

Which SQL statement is used to return only different values?

sql
  1. SELECT UNIQUE

  2. SELECT INDENTITY

  3. SELECT DIFFERENT

  4. SELECT DISTINCT


Correct Option: D

Which SQL keyword is used to sort the result-set?

sql
  1. SORT BY

  2. ORDER

  3. ORDER BY

  4. SORT


Correct Option: C

With SQL, how can you return all the records from a table named "Persons" sorted descending by "FirstName"?

sql
  1. SELECT * FROM Persons SORT BY 'FirstName' DESC

  2. SELECT * FROM Persons ORDER BY FirstName DESC

  3. SELECT * FROM Persons ORDER FirstName DESC

  4. SELECT * FROM Persons SORT 'FirstName' DESC


Correct Option: B

With SQL, how can you insert a new record into the "Persons" table?

sql
  1. INSERT INTO Persons VALUES ('Jimmy', 'Jackson')

  2. INSERT ('Jimmy', 'Jackson') INTO Persons

  3. INSERT VALUES ('Jimmy', 'Jackson') INTO Persons


Correct Option: A

AI Explanation

To insert a new record into the "Persons" table using SQL, the correct syntax is:

A. INSERT INTO Persons VALUES ('Jimmy', 'Jackson')

Let's go through each option to understand why it is correct or incorrect:

Option A) INSERT INTO Persons VALUES ('Jimmy', 'Jackson') - This option is correct because it follows the correct syntax for inserting a new record. The "INSERT INTO" statement is used to specify the table name (Persons), followed by the "VALUES" keyword to specify the values for each column in the table.

Option B) INSERT ('Jimmy', 'Jackson') INTO Persons - This option is incorrect because it does not follow the correct syntax. The "INSERT INTO" statement should be used instead of just "INSERT", and the table name (Persons) should come after the "INTO" keyword.

Option C) INSERT VALUES ('Jimmy', 'Jackson') INTO Persons - This option is incorrect because it also does not follow the correct syntax. The "INTO" keyword should come before the "VALUES" keyword, and the table name (Persons) should come after the "INTO" keyword.

The correct answer is A) INSERT INTO Persons VALUES ('Jimmy', 'Jackson'). This option is correct because it follows the correct syntax for inserting a new record into the "Persons" table.

With SQL, how can you insert "Olsen" as the "LastName" in the "Persons" table?

sql
  1. INSERT INTO Persons (LastName) VALUES ('Olsen')

  2. INSERT ('Olsen') INTO Persons (LastName)

  3. INSERT INTO Persons ('Olsen') INTO LastName


Correct Option: A

How can you change "Hansen" into "Nilsen" in the "LastName" column in the Persons table?

sql
  1. UPDATE Persons SET LastName='Hansen' INTO LastName='Nilsen'

  2. UPDATE Persons SET LastName='Nilsen' WHERE LastName='Hansen'

  3. MODIFY Persons SET LastName='Hansen' INTO LastName='Nilsen

  4. MODIFY Persons SET LastName='Nilsen' WHERE LastName='Hansen'


Correct Option: B

With SQL, how can you delete the records where the "FirstName" is "Peter" in the Persons Table?

sql
  1. DELETE FROM Persons WHERE FirstName = 'Peter'

  2. DELETE ROW FirstName='Peter' FROM Persons

  3. DELETE FirstName='Peter' FROM Persons


Correct Option: A

With SQL, how can you return the number of records in the "Persons" table?

sql
  1. SELECT COLUMNS() FROM Persons

  2. SELECT COUNT(*) FROM Persons

  3. SELECT COLUMNS(*) FROM Persons

  4. SELECT COUNT() FROM Persons


Correct Option: B

What will be the output of the following statement? SELECT LEN(CAST(LEFT('026-100', 3) AS INT))

sql
  1. 2

  2. 3

  3. 7

  4. Statement will generate an error.


Correct Option: A
Explanation:

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

sql
  1. 1900-01-01 00:00:00.000

  2. 1899-01-01 00:00:00.000

  3. 1752-01-01 00:00:00.000

  4. The system will generate an error. Only positive integer values can be converted to a SMALLDATETIME data type.


Correct Option: D
Explanation:

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

What will be the output of the following statement? SELECT ROUND(123.89, -1)

sql
  1. 120.00

  2. 123.00

  3. 123.90

  4. 124.00


Correct Option: A

What will be the output of the following statement? SET ARITHABORT OFF SET ANSI_WARNINGS OFF SELECT 100/0

sql
  1. Null

  2. 0

  3. Infinity

  4. An error is generated.


Correct Option: A

View text definitions are stored in which system table?

sql
  1. sysobjects

  2. syscolumns

  3. syscomments

  4. sysviews


Correct Option: C

What's the maximum value can an INT data type hold?

sql
  1. 2,147,483,647

  2. 2,147,483,648

  3. 4,294,967,295

  4. 4,294,967,296


Correct Option: A

What is the best data type to store the birthdays of the US Presidents, starting with George Washington's birthday of February 22, 1732?

sql
  1. DATETIME

  2. INT

  3. SMALLDATETIME

  4. VARCHAR


Correct Option: B

AI Explanation

To answer this question, we need to consider the type of data that will be stored and the requirements for storing the birthdays of the US Presidents.

Option A) DATETIME - This option is incorrect because the DATETIME data type is used to store date and time values, not just dates. Since we only need to store the birthdays of the US Presidents, which do not include specific times, this data type is not necessary.

Option B) INT - This option is correct because it is a suitable data type to store the birthdays of the US Presidents. The INT data type can store whole numbers, which can be used to represent dates in a specific format (e.g., YYYYMMDD). In this case, we can store the birthdays as integers in the format 17320222 for George Washington's birthday.

Option C) SMALLDATETIME - This option is incorrect because the SMALLDATETIME data type is also used to store date and time values, similar to DATETIME. Since we only need to store the birthdays of the US Presidents without specific times, this data type is not necessary.

Option D) VARCHAR - This option is incorrect because the VARCHAR data type is used to store alphanumeric characters, such as text strings. While it is possible to store the birthdays as strings in the format 'February 22, 1732', using the INT data type would be more efficient and appropriate.

The correct answer is B) INT. This option is correct because it is a suitable data type to store the birthdays of the US Presidents as whole numbers, representing dates in a specific format.

Which of the following is NOT a valid description of the public role?

sql
  1. The public role captures all default permissions for users in a database.

  2. The public role cannot be dropped.

  3. The public role is contained in every database, including msdb, tempdb, model, and all user databases except in the master database for security purposes.

  4. The public role cannot have users, groups, or roles assigned to it.


Correct Option: C

What is the maximum date value that can be stored in a SMALLDATETIME data type?

sql
  1. June 6, 2079

  2. July 6, 2079

  3. December 31, 2079

  4. December 31, 9999


Correct Option: A

AI Explanation

To answer this question, we need to understand the range of values that can be stored in the SMALLDATETIME data type.

The SMALLDATETIME data type in SQL Server is used to store dates and times with a precision of one minute. It has a range from January 1, 1900, to June 6, 2079.

Let's go through each option to determine the correct answer:

Option A) June 6, 2079 - This option is correct because it falls within the range of values that can be stored in the SMALLDATETIME data type.

Option B) July 6, 2079 - This option is incorrect because it falls outside the range of values that can be stored in the SMALLDATETIME data type.

Option C) December 31, 2079 - This option is incorrect because it falls outside the range of values that can be stored in the SMALLDATETIME data type.

Option D) December 31, 9999 - This option is incorrect because it falls outside the range of values that can be stored in the SMALLDATETIME data type.

The correct answer is A) June 6, 2079. This option is correct because it falls within the range of values that can be stored in the SMALLDATETIME data type.

- Hide questions