Computer Knowledge

Database and SQL

3,929 Questions

Master structured query language commands, database joins, table constraints, and alias generation. This section covers relational database management concepts and query outputs essential for technical aptitude. These technical questions feature prominently in banking IT officer exams and computer knowledge sections.

SQL queries and aliasesDatabase table constraintsDatabase joins and transformationsStored procedures and functions

Database and SQL Questions

Multiple choice technology databases

SELECT NVL(null,'testing') FROM DUAL ;

  1. testing

  2. null

  3. False

  4. error
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

The NVL function in Oracle SQL evaluates the first argument. If it is null, it returns the second argument. Since the first argument is null, the query returns the string 'testing'.

Multiple choice technology

The SQLPLUS command used to stop display between pages is

  1. set break

  2. set pause

  3. set gap

  4. None of the Above
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The SQL*Plus command SET PAUSE ON pauses display between pages, waiting for user input before continuing. SET BREAK controls page breaks, SET GAP is not a valid SQL*Plus command, and None of the Above is incorrect since SET PAUSE is the right answer.

Multiple choice technology

Which of the following denote group clause in SQL

  1. having

  2. group by

  3. Where

  4. Both A. and B.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

GROUP BY and HAVING are both group-related clauses in SQL. GROUP BY aggregates data into groups, while HAVING filters those groups after aggregation. WHERE filters individual rows before grouping, not groups themselves.

Multiple choice technology

Which of the following is TRUE about Views in SQL?

  1. Views are windows of existing table

  2. Creation of view is done by using CREATE VIEW statement

  3. Views do not contain data but table from which it is created contain data

  4. All the Above
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To answer this question, the user needs to have a basic understanding of Views in SQL.

A. This option is incorrect. Views are not windows of existing tables; rather, they are virtual tables that are created using a SELECT statement.

B. This option is correct. Views in SQL are created using the CREATE VIEW statement. This statement allows you to define a view based on a SELECT statement that retrieves data from one or more tables.

C. This option is also correct. Views do not contain any data themselves. Instead, they are virtual tables that are defined based on the data in one or more existing tables. Any changes made to the data in the underlying tables will be reflected in the view.

D. This option is correct. All of the above statements are true about views in SQL.

Therefore, the answer is: D. All the Above

Multiple choice technology web technology

What does the following SQL statement return? Assume the value of SysDate is 05-05-5555 Select SysDate / 5 from dual

  1. 01-01-1111

  2. Error

  3. 01

  4. 1111
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In SQL databases like Oracle, SysDate returns a value of the DATE datatype. Performing division on a date value directly is an invalid operation and results in a datatype mismatch error. The distractors incorrect because they assume date arithmetic allows division resulting in modified date or numeric values.

Multiple choice technology

The SQL function SIGN (-50) gives value as

  1. -50

  2. 50

  3. -1

  4. 1
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

The SIGN function returns -1 for negative numbers, 0 for zero, and 1 for positive numbers. Since -50 is negative, SIGN(-50) returns -1, not the original value or its absolute value.

Multiple choice technology

Consider an EMP table having data as follows: EMPNOEMPNAME 1000SOPHIA 1001RITA 1002MARY Which of the following SQL query will fetch all columns with name RITA only?

  1. SELECT FROM EMP WHERE EMPNAME='RITA';

  2. SELECT FROM EMP MATCHING EMPNAME='RITA';

  3. SELECT * FROM EMP WHERE EMPNAME='RITA';

  4. SELECT # FROM EMP MATCHING EMPNAME='RITA';
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

SELECT * FROM EMP WHERE EMPNAME='RITA' correctly uses * for all columns and proper WHERE clause syntax. Options A and B are missing the * for all columns, while 'MATCHING' is not valid SQL syntax - WHERE clause should be used for filtering.

Multiple choice technology mainframe

How to create LIKE table

  1. Using the CREATE parameter in a LIKE table statement

  2. Using the LIKE parameter

  3. Using the LIKE parameter in a CREATE table statement

  4. None of the above
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

A LIKE table is created using the LIKE parameter within a CREATE TABLE statement. The syntax is CREATE TABLE new-table LIKE existing-table, which copies the structure (column definitions) of an existing table. The LIKE parameter is specified within the CREATE statement itself, not as a separate parameter or standalone statement.

Multiple choice technology programming languages

Dataset x Rev1 Rev2 Rev3 Rev4 1 3 4 5 data y; set x; z=mean(?); run; Fill in the mean function call.

  1. mean(Rev1-Rev4)

  2. mean(of Rev:)

  3. mean(Rev1 Rev2 Rev3 Rev4)

  4. mean(of Rev1,Rev2,Rev3,Rev3)
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

The mean(of Rev:) syntax uses the SAS variable list shortcut 'of Rev:' which refers to all variables starting with 'Rev'. This is the most concise and correct way to calculate the mean of Rev1, Rev2, Rev3, and Rev4. Option A is incorrect because the range syntax with dash only works with numbered variables. Option C is incorrect because mean() requires the OF keyword when listing variables.

Multiple choice technology databases

What is true about subqueries ?

  1. A single row subquery can retrieve data from only one table.

  2. A SQL query statement cannot display data from table B that is referred to in its subquery,unless table B is included in the main query's FROM clause.

  3. A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause

  4. A single row subquery can retrieve data from more than one table.
Reveal answer Fill a bubble to check yourself
B,D Correct answer
Explanation

Subqueries can retrieve data from multiple tables regardless of whether they're single-row or multiple-row types. However, any column you want to display in your SELECT list must come from tables in the main query's FROM clause - you cannot display columns from tables that only appear in the subquery.

Multiple choice technology databases

What is true about an equijoin?

  1. You can join a maximum of two tables through an equijoin.

  2. You can join a maximum of two columns through an equijoin.

  3. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.

  4. To join two tables through an equijoin, the columns in the join condition must be primary key and foreign key columns.

  5. You specify an equijoin condition in the SELECT or FROM clauses of a SELECT statement.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

To solve this question, the user needs to have knowledge of SQL and database concepts related to joining tables.

A. This option is incorrect because you can join any number of tables through an equijoin, not just a maximum of two.

B. This option is incorrect because you can join any number of columns through an equijoin, not just a maximum of two.

C. This option is correct. In a SQL statement, you can join n tables (all having single column primary keys) by specifying a minimum of n-1 join conditions.

D. This option is incorrect because while it is true that you can join two tables through an equijoin, the columns in the join condition do not have to be primary key and foreign key columns. They just need to have matching values.

E. This option is incorrect because you specify an equijoin condition in the WHERE clause of a SELECT statement.

Therefore, the correct answer is:

The Answer is: C. You can join n tables (all having single column primary keys) in a SQL statement by specifying a minimum of n-1 join conditions.