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 programming languages

Bonus dataset ID Bonus 123 5000 456 7000 744 3500 Salary Dataset ID Salary 123 70000 456 80000 978 55000 Given the PROC SQL query and tables shown below, which output is generated? proc sql; select s.*, bonus from bonus as b right join salary as s on b.id= s.id;

  1. ID Salary Bonus 123 70000 5000 456 80000 7000 978 55000 3500

  2. ID Salary Bonus 123 70000 5000 456 80000 7000 744 . 3500

  3. ID Salary Bonus 123 70000 5000 456 80000 7000 744 55000 3500

  4. ID Salary Bonus 123 70000 5000 456 80000 7000 978 55000 .
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

A right join preserves all rows from the right table (salary), which contains IDs 123, 456, and 978. The bonus table only has matching values for IDs 123 and 456, leaving ID 978 with a null (missing) bonus value in the output.

Multiple choice technology programming languages

Which statement about the use of table aliases is false?

  1. Table aliases must be used when referencing identical table names from different libraries.

  2. Table aliases can be referenced by using the keyword AS.

  3. Table aliases (or full table names) must be used when referencing a column name that is the same in two or more tables.

  4. Table aliases must be used when using summary functions.
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

Table aliases are optional when applying summary functions like SUM or AVG unless needed to disambiguate identical column names across multiple joined tables. The other statements correctly describe SQL syntax rules, including using the AS keyword and disambiguating columns.

Multiple choice technology programming languages

Which of the following will cause PROC SQL to list rows that have no data in the Address column?

  1. WHERE address is missing

  2. WHERE address not exists

  3. WHERE address is null

  4. both a and c
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In SAS PROC SQL, both IS NULL and IS MISSING are equivalent syntax for identifying missing values. Option D correctly identifies that both conditions work to filter rows with no Address data, while Options A and C each give only one of the two valid syntaxes.

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

Use the CALCULATED keyword to reference a computed column in the WHERE clause. Option B correctly uses 'where calculated balance <= 0' to filter on the derived Balance column. Options A and C omit the required CALCULATED keyword, while Option D incorrectly places it in the SELECT clause.

Multiple choice technology databases

What does the following SQL statement do: SELECT Customer, COUNT(Order) FROM Sales GROUP BY Customer HAVING COUNT(Order) >5

  1. Selects the total number of orders from the Sales table, if this number is greater than 5

  2. Selects all customers from table Sales that have made more than 5 orders.

  3. Selects all Customers from the Sales table

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

To understand this SQL statement, the user needs to know the basic structure of SQL queries. In this case, the statement selects two fields, Customer and COUNT(Order), from the Sales table. It then groups these fields by Customer and applies a filter using the HAVING clause to only return rows where the count of orders for that customer is greater than 5.

A. This option is incorrect because the statement is not selecting the total number of orders, but rather the count of orders for each customer.

B. This option is correct. The statement is selecting all customers from the Sales table that have made more than 5 orders.

C. This option is incorrect because the statement is not selecting all customers, but rather only those who have made more than 5 orders.

D. This option is incorrect because option B is correct.

Therefore, the answer is: B. Selects all customers from table Sales that have made more than 5 orders.

Multiple choice technology databases

Which of the following SQL statements is correct?

  1. TRUNCATE Sales TABLE

  2. TRUNCATE * FROM TABLE Sales

  3. TRUNCATE TABLE Sales

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

The correct SQL syntax for removing all rows from a table is TRUNCATE TABLE followed by the table name. This command quickly deletes all data while keeping the table structure intact. Options A and B use incorrect syntax, and D cannot be correct since only one syntax is proper.

Multiple choice technology databases

What is a stored procedure?

  1. A process for data export/import.

  2. A set of SQL statements stored and executed as a single entity.

  3. A process to convert tables.

  4. Data stored in a database.
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

A stored procedure is a precompiled collection of one or more SQL statements stored in the database. It can be executed as a single unit by calling its name, which improves code reusability, reduces network traffic, and enhances security by controlling data access through defined procedures.

Multiple choice technology databases

what does SQL Lack

  1. The complexity and size of the SQL standard means that most databases DO NOT implement the entire standard.

  2. The simplicity and size of the SQL standard means that most databases DO implement the entire standard.

  3. The simplicity and size of the SQL standard means that most databases DO NOT implement the entire standard.

  4. The complexity and size of the SQL standard means that most databases DO implement the entire standard.
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

SQL is a comprehensive standard with many optional features. Database vendors typically implement only the core features needed for their target use cases, not the entire standard. Options B and D incorrectly claim databases implement the full standard. Option C incorrectly describes SQL as simple.

Multiple choice technology databases

Database Key is a combination of

  1. Record Number

  2. Page Number

  3. Area Name

  4. Page Name

  5. Area Code

  6. Record Name
Reveal answer Fill a bubble to check yourself
A,B,E Correct answer
Explanation

A database key uniquely identifies a specific record and consists of three components: the record number within its page, the page number within the area, and the area code. This combination ensures each record has a unique address across the entire database.

Multiple choice technology databases

In a set ORDER is defined as LAST. When we insert a new member record occurence, this record will become.

  1. last member of the set

  2. first member of the set

  3. will be inserted after the current location in the set

  4. will be inserted before the current location in the set
Reveal answer Fill a bubble to check yourself
A Correct answer
Explanation

When ORDER is LAST, new member records are inserted at the end of the set, making them the last member. This maintains insertion order where newer records always follow existing ones.

Multiple choice technology databases

In a set ORDER is defined as FIRST. When we insert a new member record occurence, this record will become.

  1. last member of the set

  2. first member of the set

  3. will be inserted after the current member in the set

  4. will be inserted before the current member in the set
Reveal answer Fill a bubble to check yourself
B Correct answer
Explanation

When ORDER is FIRST, new member records are inserted at the beginning of the set, becoming the first member. This means newer records appear before older ones in the set order.

Multiple choice technology databases

In a set ORDER is defined as NEXT. When we insert a new member record occurence, this record will become.

  1. last member of the set

  2. first member of the set

  3. will be inserted after the current member in the set

  4. will be inserted before the current member in the set
Reveal answer Fill a bubble to check yourself
C Correct answer
Explanation

In COBOL sets, when ORDER is defined as NEXT, new member occurrences are inserted immediately after the current member of the set. This is the standard behavior for NEXT-ordered set insertion in COBOL database systems.

Multiple choice technology databases

In a set ORDER is defined as PRIOR. When we insert a new member record occurence, this record will become.

  1. last member of the set

  2. first member of the set

  3. will be inserted after the current member in the set

  4. will be inserted before the current member in the set
Reveal answer Fill a bubble to check yourself
D Correct answer
Explanation

In a CODASYL database, when a set's member order is defined as PRIOR, inserting a new member record inserts it immediately before the current record of the set. Options specifying last, first, or after the current member do not match the definition of the PRIOR order.

Multiple choice technology databases

The Data Manipulation Language statements are

  1. INSERT

  2. UPDATE

  3. SELECT

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

INSERT, UPDATE, and SELECT are all classified as Data Manipulation Language (DML) statements in SQL. While SELECT is sometimes categorized separately as DQL (Data Query Language) in academic contexts, in practical database usage and certification exams, all three operations are grouped under DML as they manipulate or query data within tables. Therefore option D (All of the above) is correct.