databases Online Quiz - 200
| Description: databases Online Quiz - 200 | |
| Number of Questions: 20 | |
| Created by: Aliensbrain Bot | |
| Tags: databases |
In a SELECT statement that includes a WHERE clause, where is the GROUP BY clause placed in the SELECT statement?
-
Immediately after the SELECT clause
-
After the ORDER BY clause
-
After the WHERE clause
-
Before the WHERE clause
AI Explanation
To answer this question, you need to understand the order in which clauses are typically placed in a SQL SELECT statement.
The correct answer is C) After the WHERE clause.
In a SELECT statement, the WHERE clause is used to filter the rows returned by the query based on a specific condition. The GROUP BY clause, on the other hand, is used to group the rows based on one or more columns.
The typical order of clauses in a SELECT statement is as follows:
- SELECT clause
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- ORDER BY clause
Therefore, the GROUP BY clause is placed after the WHERE clause in the SELECT statement. It is used to group the rows after filtering them based on the conditions specified in the WHERE clause.
Which SELECT statement will the result ‘ello world’ from the string ‘Hello World’?
-
SELECT SUBSTR( ‘Hello World’,1) FROM dual;
-
SELECT INITCAP(TRIM (‘Hello World’, 1,1)) FROM dual;
-
SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;
-
SELECT LOWER(SUBSTR(‘Hello World’, 2, 1) FROM dual;
To solve this question, the user needs to know the basic SQL functions and how they can be applied to strings. The user must evaluate each option and determine which one will result in the desired output of 'ello world' from the given string 'Hello World'.
Now, let's go through each option and explain why it is right or wrong:
A. SELECT SUBSTR( ‘Hello World’,1) FROM dual;
This option selects a substring from the given string starting at the first character. Since the starting point is at the first character, the entire string 'Hello World' will be returned. Therefore, this option is incorrect.
B. SELECT INITCAP(TRIM (‘Hello World’, 1,1)) FROM dual;
This option capitalizes the first letter of the given string and removes any leading or trailing spaces. However, the second and third parameters of the TRIM function are invalid and will cause an error. Therefore, this option is also incorrect.
C. SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;
This option removes the letter 'H' from the given string, converts it to lowercase, and removes any leading or trailing spaces. The resulting string will be 'ello world', which is the desired output. Therefore, this option is correct.
D. SELECT LOWER(SUBSTR(‘Hello World’, 2, 1) FROM dual;
This option selects a substring from the given string starting at the second character and ending after one character. The resulting substring will be 'e'. This substring is then converted to lowercase. Therefore, this option is incorrect.
The Answer is: C. SELECT LOWER(TRIM (‘H’ FROM ‘Hello World’)) FROM dual;
Which SQL statement generates the alias Annual Salary for the calculated column SALARY*12?
-
SELECT ename, salary*12 ‘Annual Salary’ FROM employees;
-
SELECT ename, salary*12 “Annual Salary” FROM employees;
-
SELECT ename, salary*12 AS Annual Salary FROM employees;
-
SELECT ename, salary*12 AS INITCAP(“ANNUAL SALARY”) FROM employees
AI Explanation
To generate the alias "Annual Salary" for the calculated column SALARY*12, you can use the AS keyword in the SELECT statement. Let's go through each option to determine which one is correct.
Option A) SELECT ename, salary*12 'Annual Salary' FROM employees; This option is incorrect because it uses single quotes ('') instead of double quotes ("") or square brackets ([]). In SQL, double quotes or square brackets are used to define column aliases, not single quotes.
Option B) SELECT ename, salary*12 "Annual Salary" FROM employees; This option is correct because it uses double quotes ("") to define the column alias "Annual Salary". This is the correct syntax to generate an alias in SQL.
Option C) SELECT ename, salary*12 AS Annual Salary FROM employees; This option is incorrect because it does not use proper syntax for defining the column alias. The AS keyword should be followed by the alias name without any spaces. In this case, it should be written as AS "Annual Salary".
Option D) SELECT ename, salary*12 AS INITCAP("ANNUAL SALARY") FROM employees; This option is incorrect because it tries to use the INITCAP function to format the column alias. The INITCAP function is not necessary for defining the alias and would result in a syntax error.
Based on the explanations above, the correct answer is Option B) SELECT ename, salary*12 "Annual Salary" FROM employees.
Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?
-
ORDER BY SALARY > 5000
-
GROUP BY SALARY > 5000
-
HAVING SALARY > 5000
-
WHERE SALARY > 5000
You need to calculate the total of all salaries in the accounting department. Which group function should you use?
-
MAX
-
MIN
-
SUM
-
TOTAL
AI Explanation
To calculate the total of all salaries in the accounting department, you should use the SUM group function.
Let's go through each option to understand why it is correct or incorrect:
Option A) MAX - The MAX function is used to find the maximum value in a set of values. It would not provide the total sum of salaries in the accounting department, so this option is incorrect.
Option B) MIN - The MIN function is used to find the minimum value in a set of values. It would not provide the total sum of salaries in the accounting department, so this option is incorrect.
Option C) SUM - The SUM function is used to calculate the total sum of a set of values. This is the correct option because it allows you to add up all the salaries in the accounting department to get the total.
Option D) TOTAL - There is no built-in group function called TOTAL in most database systems. Therefore, this option is incorrect.
The correct answer is C) SUM. This option is correct because the SUM function allows you to calculate the total sum of all salaries in the accounting department.
Which SELECT statement should you use to extract the year from the system date and display it in the format "1998"?
-
SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;
-
SELECT TO_DATE(SYSDATE,'yyyy') FROM dual;
-
SELECT DECODE(SUBSTR(SYSDATE, 8), 'YYYY') FROM dual;
-
SELECT DECODE(SUBSTR(SYSDATE, 8), 'year') FROM dual;
To solve this question, the user needs to know the SQL syntax for extracting specific parts of a date and formatting date values. The user must use the correct function to extract the year from the system date and format it as "1998".
Option A is correct. This option uses the TO_CHAR function to convert the system date to a character string in the format specified by the second argument ('yyyy' in this case). This function extracts the year from the system date and formats it as a 4-digit string. The output will be "1998".
Option B is incorrect. This option uses the TO_DATE function, which is used to convert a character string to a date value. In this case, the first argument is already a date value, so using TO_DATE is unnecessary. Also, the second argument ('yyyy') specifies the format of the output, not the input.
Option C is incorrect. This option uses the DECODE function to extract the year from the system date. However, the syntax is incorrect. DECODE requires at least three arguments: the first is the value to be compared, the second is the comparison value, and the third is the result if the comparison is true. In this case, the first argument (SUBSTR(SYSDATE, 8)) is missing a second argument to compare it to.
Option D is incorrect. This option also uses the DECODE function, but the syntax is incorrect. The SUBSTR function extracts a substring from the system date starting at position 8, which is the day of the month. There is no 'year' substring in this position, so the output will not be correct.
The Answer is: A. SELECT TO_CHAR(SYSDATE,'yyyy') FROM dual;
In which scenario would TOP N analysis be the best solution?
-
You want to identify the most senior employee in the company.
-
You want to find the manager supervising the largest number of employees
-
You want to identify the person who makes the highest salary for all employees.
-
You want to rank the top three sales representatives who have sold the maximum
To determine the best solution for TOP N analysis, the user must understand what TOP N analysis is and the context in which it can be applied. TOP N analysis is a method of ranking data by selecting the top N items based on a specific criterion. The user should choose option D because it requires identifying the top three sales representatives who have sold the maximum, which is a typical use case for TOP N analysis.
Now, let's go through each option and explain why it is right or wrong:
A. You want to identify the most senior employee in the company. This option is wrong because identifying the most senior employee does not require ranking or selecting the top N employees based on a specific criterion. It only involves identifying one employee based on their seniority level.
B. You want to find the manager supervising the largest number of employees. This option is wrong because finding the manager supervising the largest number of employees does not require ranking or selecting the top N employees based on a specific criterion. It only involves identifying one manager based on the number of employees they supervise.
C. You want to identify the person who makes the highest salary for all employees. This option is wrong because identifying the person who makes the highest salary does not require ranking or selecting the top N employees based on a specific criterion. It only involves identifying one employee based on their salary level.
D. You want to rank the top three sales representatives who have sold the maximum. This option is correct because identifying the top three sales representatives who have sold the maximum requires ranking and selecting the top N employees based on a specific criterion (sales revenue), which is a typical use case for TOP N analysis.
Therefore, the answer is: D.
Evaluate the SQL statement: SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual; Which values are displayed?
-
46 and 45
-
46 and 45.93
-
50 and 45.93
-
50 and 45.9
AI Explanation
To evaluate the given SQL statement, let's break it down step by step:
The statement is: SELECT ROUND(45.953, -1), TRUNC(45.936, 2) FROM dual;
The ROUND function rounds a number to a specified number of decimal places. In this case, the number 45.953 is rounded to the nearest 10th place, which is 50.
The TRUNC function truncates a number to a specified number of decimal places. In this case, the number 45.936 is truncated to 2 decimal places, which is 45.93.
Therefore, the values displayed by the SQL statement are 50 and 45.93.
Let's go through each option to understand why it is correct or incorrect:
Option A) 46 and 45 - This option is incorrect because the ROUND function rounds 45.953 to 50, not 46.
Option B) 46 and 45.93 - This option is incorrect because the ROUND function rounds 45.953 to 50, not 46.
Option C) 50 and 45.93 - This option is correct because the ROUND function rounds 45.953 to 50, and the TRUNC function truncates 45.936 to 45.93.
Option D) 50 and 45.9 - This option is incorrect because the TRUNC function truncates 45.936 to 45.93, not 45.9.
The correct answer is Option C.
special purpose subset of enterprise data used by a particular department, function or application
-
Data dictionary
-
Data marts
-
Data sets
-
Nodes
facility Which allows you to preview how Teradata will execute a requested query.
-
Help
-
Run
-
Explain
-
complie
Tables must always be assigned a Primary Index at creation
-
True
-
False
Primary Index can not be
-
Null
-
dropped
-
have duplicates
-
All of the above
_________ distributes the row to the appropriate AMP
-
NODE
-
clique
-
Bynet
-
PE
What Service / Server in the Microsoft SQL BI tool support Creation / rendering of reports?
-
SSAS
-
SSIS
-
SSRS
-
SQL RDBMS
To answer this question, the user needs to have an understanding of the Microsoft SQL BI tool components and their respective functionalities.
SSAS (SQL Server Analysis Services) is a component of the Microsoft SQL BI tool that enables data analysis and mining by creating OLAP cubes. It is not used for creating or rendering reports.
SSIS (SQL Server Integration Services) is a component of the Microsoft SQL BI tool that is used to perform data integration tasks such as data extraction, transformation, and loading. It is not used for creating or rendering reports.
SSRS (SQL Server Reporting Services), on the other hand, is a component of the Microsoft SQL BI tool that supports the creation and rendering of reports. It provides a platform for designing, publishing, and delivering reports to end-users. Therefore, option C is the correct answer.
SQL RDBMS (Relational Database Management System) is a general term that refers to software systems used to manage relational databases. It is not a specific component of the Microsoft SQL BI tool and does not support the creation or rendering of reports.
Therefore, the answer is: C. SSRS.
What is Business Intelligence?
-
A method of storing and presenting key enterprise data
-
Allows End users to analyze data to understand business results
-
To accurately forecast future results
-
All of the above
To understand the concept of Business Intelligence, it is the process of transforming data into actionable insights to help businesses make informed decisions.
To answer this question, the user must know the definition of Business Intelligence. Now, let's go through each option and explain why it is right or wrong:
A. A method of storing and presenting key enterprise data: This option is partially correct, but it does not encompass the entire concept of Business Intelligence. While storage and presentation of enterprise data is a part of BI, it is not the full picture.
B. Allows End users to analyze data to understand business results: This option is also partially correct. BI tools allow end-users to analyze data to understand business results. But again, it is not the complete definition of BI.
C. To accurately forecast future results: This option is incorrect. While BI can help businesses forecast future results, it is not the sole purpose of BI.
D. All of the above: This option is the correct answer. Business Intelligence involves storing and presenting data, analyzing data to understand business results, and accurately forecasting future results. Therefore, option D is the correct answer.
The Answer is: D
What Service / Server in the Microsoft SQL BI tool set allows for ETL kind of activities?
-
SSAS
-
SSIS
-
SSRS
-
SQL RDBMS
To answer this question, the user needs to be familiar with the Microsoft SQL BI toolset and its components.
A. SSAS (SQL Server Analysis Services) is a service that performs online analytical processing (OLAP) and data mining functionalities. It is used for creating and managing data models and providing access to multidimensional data. It does not provide ETL kind of activities.
B. SSIS (SQL Server Integration Services) is a server for data integration and workflow applications. It is used for building and maintaining data integration workflows. It provides ETL (Extract, Transform, Load) functionalities. Therefore, this option is correct.
C. SSRS (SQL Server Reporting Services) is a server-based reporting platform that is used for creating and publishing reports. It does not provide ETL kind of activities.
D. SQL RDBMS is a relational database management system that provides the ability to store, manage, and retrieve data. It does not provide ETL kind of activities.
Therefore, the answer is:
The Answer is: B. SSIS
Which of the following causes a lock that is being held by an application using the Cursor Stability isolation level to be released?
-
The cursor is moved to another row
-
The row the cursor is on is deleted by the application
-
The row the cursor is on is deleted by another application
-
The row the cursor is on needs to be updated by another application
When the program name is not in the plan, we get
-
-810
-
-801
-
-805
-
-825
B37 abend during SPUFI is due to
-
Space Issues
-
Data Issues
-
No records found while querying the table
-
Both a & b
AI Explanation
To answer this question, we need to understand what a B37 abend is and its possible causes.
A B37 abend is an error that occurs in IBM's Mainframe environment, specifically in the Job Control Language (JCL) when executing a job or a utility like SPUFI (SQL Processor Using File Input). It indicates that there is insufficient space available to complete the job or utility.
Given this information, let's go through each option to understand why it is correct or incorrect:
Option A) Space Issues - This option is correct because a B37 abend is indeed caused by insufficient space. When executing SPUFI, if there is not enough space allocated for the job or utility, it will result in a B37 abend.
Option B) Data Issues - This option is incorrect. While data issues can cause errors in SPUFI or other utilities, a B37 abend specifically relates to space issues and not data issues.
Option C) No records found while querying the table - This option is incorrect. A B37 abend does not occur due to a lack of records found while querying a table. It is solely related to space issues.
Option D) Both a & b - This option is incorrect. A B37 abend is only due to space issues and not data issues.
Therefore, the correct answer is A) Space Issues. This option is correct because a B37 abend during SPUFI is indeed caused by insufficient space.