0

Identify the SQL Statements

Description: You will be asked a question for SQL statement, Choose appropriate answer from the options.
Number of Questions: 20
Created by:
Tags: databases sql
Attempted 0/20 Correct 0 Score 0

To drop an index, we must be the owner of the index or have the ______ privilege.

  1. DROP ANY INDEX

  2. DROP ALL INDEX

  3. REMOVE ANY INDEX

  4. REMOVE ALL INDEX


Correct Option: A

If a table is dropped, which of the following statement is true.

  1. indexes and sequences are automatically dropped, but views and constraints remain

  2. views and indexes are automatically dropped, but constraints and sequences remain

  3. indexes and constraints are automatically dropped, but views and sequences remain

  4. views and sequences are automatically dropped, but indexes and constraints remain


Correct Option: C

A function-based index is an index based on expressions

  1. True

  2. False


Correct Option: A

Which of the below statement is worth creating an index

  1. The table is small

  2. The columns are often used as a condition in the query

  3. Most queries are expected to retrieve more than 2 to 4 percent of the rows in the table

  4. The table is updated frequently


Correct Option: B

More indexes on a table is always better for faster queries

  1. True

  2. False


Correct Option: B

AI Explanation

To answer this question, you need to understand the concept of indexes in a database table.

Indexes in a database are used to improve the performance of queries by allowing the database engine to quickly locate and retrieve the desired data. While indexes can speed up query execution, it is not always the case that adding more indexes to a table will result in faster queries. In fact, adding too many indexes to a table can have negative effects on performance.

Here's an explanation for each option:

Option A) True - This option is incorrect because adding more indexes to a table does not always guarantee faster queries. Each index comes with its own overhead in terms of storage and maintenance, and it can slow down data modification operations (such as INSERT, UPDATE, DELETE). Additionally, if the database optimizer does not choose the appropriate index for a query, it may not be able to take advantage of the indexes effectively.

Option B) False - This option is correct because adding more indexes to a table does not always result in faster queries. The decision to add an index should be based on careful analysis of the queries that will be executed on the table, considering factors such as the selectivity of the columns being queried, the frequency of data modifications, and the overall performance goals of the application.

The correct answer is B) False.

What does SQL stand for?

  1. Structured Question Language

  2. Strong Question Language

  3. Structured Query Language

  4. Structured Query Limited


Correct Option: C

Which SQL statement is used to extract data from a database?

  1. FETCH

  2. GET

  3. SELECT

  4. EXTRACT


Correct Option: C

AI Explanation

To answer this question, you need to understand SQL (Structured Query Language), which is a programming language used for managing and manipulating relational databases.

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

Option A) FETCH: The FETCH statement is used in SQL to retrieve rows from a cursor and move the cursor position to the next row. It is not used to extract data from a database directly.

Option B) GET: The GET statement is not a valid SQL statement for extracting data from a database. There is no GET statement in SQL.

Option C) SELECT: The SELECT statement is the correct SQL statement used to extract data from a database. It is used to retrieve data from one or more tables in a database based on specified conditions.

Option D) EXTRACT: The EXTRACT statement is used in SQL to extract parts of a date or time value, such as extracting the year or month from a date. It is not used to extract data from a database directly.

The correct answer is C) SELECT. This option is correct because the SELECT statement is the primary SQL statement used for extracting data from a database.

Which SQL statement is used to update data in a database?

  1. UPDATE

  2. MODIFY

  3. SAVE AS

  4. SAVE


Correct Option: A

Which SQL statement is used to delete data from a database?

  1. REMOVE

  2. COLLAPSE

  3. DELETE

  4. REMOVE ALL


Correct Option: C

Which SQL statement is used to insert new data in a database?

  1. ADD NEW

  2. ADD RECORD

  3. INSERT NEW

  4. INSERT INTO


Correct Option: D

With SQL, how do you select a column named "FirstName" from a table named "Persons"?

  1. SELECT Persons.FirstName

  2. EXTRACT FirstName FROM Persons

  3. SELECT FirstName FROM Persons

  4. SELECT FROM FirstName IN Persons


Correct Option: C
  1. SELECT *.Persons

  2. SELECT Persons

  3. SELECT * FROM Persons

  4. SELECT [all] FROM Persons


Correct Option: C

With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" is "Peter"?

  1. SELECT * FROM Persons WHERE FirstName='Peter'

  2. SELECT [all] FROM Persons WHERE FirstName='Peter'

  3. SELECT [all] FROM Persons WHERE FirstName LIKE 'Peter'

  4. SELECT * FROM Persons WHERE FirstName<>'Peter'


Correct Option: A

With SQL, how do you select all the records from a table named "Persons" where the value of the column "FirstName" starts with an "a"?

  1. SELECT * FROM Persons WHERE FirstName='%a%'

  2. SELECT * FROM Persons WHERE FirstName='a_'

  3. SELECT * FROM Persons WHERE FirstName LIKE 'a%'

  4. SELECT * FROM Persons WHERE FirstName LIKE '%a'


Correct Option: C

The OR operator displays a record if ANY conditions listed are true. The AND operator displays a record if ALL of the conditions listed are true

  1. True

  2. False


Correct Option: A

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

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

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

  3. SELECT * FROM Persons WHERE FirstName<>'Peter' AND LastName<>'Jackson'

  4. SELECT WHERE FirstName='Peter' AND 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"?

  1. SELECT LastName>'Hansen' AND LastName

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

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

  4. SELECT * FROM Persons WHERE LastName IN 'Hansen' AND 'Pettersen'


Correct Option: C
Explanation:

To solve this question, the user needs to know how to use SQL to select specific records from a table based on certain criteria. Specifically, they need to know how to use the WHERE clause to filter records based on a specified range of values.

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

A. SELECT LastName>'Hansen' AND LastName'Hansen' AND LastName

Which SQL statement is used to return only different values?

  1. SELECT UNIQUE

  2. SELECT DISTINCT

  3. SELECT DIFFERENT

  4. SELECT SAME


Correct Option: B

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

  1. SELECT * FROM Persons ORDER FirstName DESC

  2. SELECT * FROM Persons SORT BY 'FirstName' DESC

  3. SELECT * FROM Persons ORDER BY FirstName DESC

  4. SELECT * FROM Persons SORT 'FirstName' DESC


Correct Option: C

AI Explanation

To return all the records from a table named "Persons" sorted descending by "FirstName" in SQL, you can use the following query:

C. SELECT * FROM Persons ORDER BY FirstName DESC

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

Option A) SELECT * FROM Persons ORDER FirstName DESC This option is incorrect because the correct syntax is to use the keyword "BY" after "ORDER" to specify the column to sort by. So, it should be "ORDER BY" instead of just "ORDER".

Option B) SELECT * FROM Persons SORT BY 'FirstName' DESC This option is incorrect because the correct syntax is to use "ORDER BY" instead of "SORT BY" to specify the sorting order.

Option C) SELECT * FROM Persons ORDER BY FirstName DESC This option is correct because it uses the correct syntax to sort the records in descending order based on the "FirstName" column. The keyword "ORDER BY" is used to specify the sorting order, and "DESC" is used to indicate descending order.

Option D) SELECT * FROM Persons SORT 'FirstName' DESC This option is incorrect because there is no "SORT" keyword in SQL. The correct keyword to use for sorting is "ORDER BY".

The correct answer is C. SELECT * FROM Persons ORDER BY FirstName DESC. This option is correct because it uses the correct syntax to sort the records in descending order based on the "FirstName" column.

- Hide questions