Computer Knowledge

Database and SQL

4,213 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 packaged enterprise solutions

You can safely add new columns in the exsisting JDA tables if required.

  1. True

  2. False
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In JDA systems, modifying existing table structures by adding new columns can break dependencies, cause data migration issues, or violate system constraints. These systems typically require strict schema management through approved change processes rather than ad-hoc column additions.

Multiple choice technology programming languages

If a table doesn't have MANDT as part of the primary key, it is

  1. a structure

  2. invalid

  3. client-independent

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

In SAP, MANDT is the client field (Mandant in German). When MANDT is part of the primary key, data is client-specific (different for each client). Without MANDT in the primary key, the table contains client-independent data shared across all clients. It's still a valid table, not a structure, and 'mandatory' refers to field properties, not the MANDT field itself.

Multiple choice technology programming languages

Which system field returns the number of records returned after a select?

  1. SY-INDEX

  2. SY-DBCNT

  3. SY-RECNO

  4. SY-TABIX

  5. SY-LNCNT
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In ABAP, sy-dbcnt is the system field that stores the number of database table rows affected or returned by the last SQL statement. sy-index is the loop iteration counter, sy-tabix is the index of the current line in an internal table, and sy-lncnt relates to printing line counts.

Multiple choice technology programming languages

Which of the following is NOT a required attribute when creating an ABAP program? A: Application B: Title C: Status D: Type

  1. A&C

  2. A&D

  3. C&D

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

When creating an ABAP program, the required attributes are Type and Title. Application specifies where the program belongs and Status tracks its lifecycle stage, but neither is mandatory. A program can exist without an assigned application or status designation.

Multiple choice technology programming languages

When creating a transparent table in the ABAP Dictionary, which step automatically creates the table in the underlying database?

  1. Adding technical settings to the table

  2. Checking the table syntax

  3. Saving the table

  4. Activating the table
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

To create a transparent table in the ABAP Dictionary, the user must perform several steps. However, only one of these steps automatically creates the table in the underlying database.

The user needs to know that transparent tables are used to store data in the database and can be accessed using SQL commands.

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

A. Adding technical settings to the table: This step is necessary in order to define the structure of the table, such as the fields and their data types, keys, and other technical settings. However, it does not automatically create the table in the database.

B. Checking the table syntax: This step checks the syntax of the table definition and ensures that it is valid. It does not create the table in the database.

C. Saving the table: This step saves the table definition in the ABAP Dictionary, but it does not create the table in the database.

D. Activating the table: This step automatically creates the table in the underlying database. When the user activates the table, the system generates the SQL code to create the table and execute it in the database.

Therefore, the correct answer is:

The Answer is: D

Multiple choice technology programming languages

If you use a RUN statement with a PROC SQL step

  1. SAS ignores the RUN statement, executes the statements as usual.

  2. Stop running due to error.

  3. Executes the code with a warning note

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

PROC SQL executes statement by statement. Because of this interactive execution, SAS ignores any RUN statement encountered during the PROC SQL block and continues normal execution.

Multiple choice technology programming languages

Complete the SELECT clause below to create a new column named Profit by subtracting the values of the column Cost from those of the column Price. select fruit,cost,price, ________________

  1. Profit=price-cost

  2. price-cost as Profit

  3. profit=price-cost

  4. Profit as price-cost
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

In SQL (including PROC SQL), the correct syntax for creating a calculated column with an alias is to place the expression first, followed by the AS keyword and the column name. The syntax 'price-cost as Profit' calculates the difference and names it Profit. Other options either use incorrect operator placement or improper alias syntax.

Multiple choice technology programming languages

Which PROC SQL query will remove duplicate values of MemberType from the query output, so that only the unique values are listed?

  1. proc sql nodup; select membertype from sasuser.frequentflyers;

  2. proc sql; select distinct(membertype) as MemberType from sasuser.frequentflyers;

  3. proc sql; select unique membertype from sasuser.frequentflyers group by membertype;

  4. proc sql; select distinct membertype from sasuser.frequentflyers;
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

The standard SQL syntax to filter out duplicate records from a query's output is SELECT DISTINCT column_name. Other options use incorrect keywords or syntax for duplicate removal.

Multiple choice technology programming languages

What happens if you use a GROUP BY clause in a PROC SQL step without a summary function?

  1. The step does not execute.

  2. The first numeric column is summed by default.

  3. The GROUP BY clause is changed to an ORDER BY clause.

  4. The step executes but does not group or sort data.
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

When GROUP BY is used without an aggregate function in PROC SQL, SAS automatically converts it to an ORDER BY clause. This is a SAS-specific behavior that prevents errors. The query executes and sorts the data rather than producing grouped summaries.

Multiple choice technology programming languages

Which clause in the following program is incorrect? proc sql; select sex,mean(weight) as avgweight from company.employees company.health where employees.id=health.id group by sex;

  1. SELECT

  2. FROM

  3. WHERE

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

The FROM clause is incorrect because it attempts to reference two tables (company.employees and company.health) without proper join syntax. PROC SQL requires explicit JOIN syntax or comma-separated tables with appropriate join conditions in the WHERE clause. The correct FROM clause should specify the join relationship between these tables.

Multiple choice technology programming languages

You are creating a PROC SQL query that will list all employees who have spent (or overspent) their allotted 120 hours of vacation for the current year. The hours that each employee used are stored in the existing column Spent. Your query defines a new column, Balance, to calculate each employee’s balance of vacation hours. Which query will produce the report that you want?

  1. proc sql; select name, spent, 120-spent as calculated Balance from Company.Absences where balance <= 0

  2. proc sql; select name, spent, 120-spent as Balance from Company.Absences where calculated balance <= 0;

  3. proc sql; select name, spent, 120-spent as Balance from Company.Absences where balance <= 0;

  4. proc sql; select name, spent, 120-spent as calculated Balance from Company.Absences where calculated balance <= 0;
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When referring to a calculated column in the WHERE clause, you must use the CALCULATED keyword to inform PROC SQL that the column is created in the SELECT clause. Option B correctly defines the Balance column and references it with 'calculated balance' in the WHERE clause. Options A and C fail without CALCULATED, while D redundantly uses it in the SELECT clause.

Multiple choice technology programming languages

What is the purpose of the following statement ---- PROC SQL INOBS= 10;

  1. Restricts the number of rows (observations) that PROC SQL retrieves from any single source.

  2. Restricts the number of rows (observations) that PROC SQL can print.

  3. Directs the system to start reading the source from 10th observation.

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

The INOBS= option in PROC SQL restricts the number of rows retrieved from any source table to the specified value. This is useful for testing queries on large datasets by limiting input rows. It does not affect output display or starting position - those functions belong to OUTOBS= and FIRSTOBS= respectively.