0% found this document useful (0 votes)

14 views7 pages

SQL Query Interview Questions Part 5

Q: What mechanisms ensure that the target table is always updated with only the currently required data, and how are they beneficial?

Two key mechanisms ensure this: using DELETE queries for outdated records and implementing priority systems with LOAD_FLAG in lookup tables. The DELETE command restricts the data timeline, keeping the table relevant by flushing entries older than the set threshold. Simultaneously, LOAD_FLAG prioritizes the current data type to load. Together, these mechanisms sustain data freshness and integrity, reducing the need for manual data management and increasing efficiency .

Q: How does the management of LOAD_FLAG in a lookup table facilitate automated data load processing in SQL environments?

The LOAD_FLAG in a lookup table acts as a marker for which data to load next, automating the decision process. Each load cycle consists of checking CONTENTS_LKP for the current LOAD_FLAG, loading the corresponding content type, and then updating the flag for the next type. This cyclical update facilitates systematic data transitions without manual intervention, ensuring consistent data turnover .

Q: How does the use of a lookup table in SQL assist in managing sequential data load operations?

A lookup table assists sequential data load operations by defining priorities and controlling the order using flags. The CONTENTS_LKP table, for example, contains PRIORITY and LOAD_FLAG fields, where only one content type has an active flag at any load cycle. The load process involves checking this table to decide which content type to process next, ensuring controlled and predictable data sequences .

Q: What are the logical steps involved in creating a SQL sequence that selects random items from a table without repeating recently processed entries?

Firstly, a nested SELECT statement identifies entries that have not been processed recently, using a NOT EXISTS clause against a target weeding out duplicates. Then, results are randomized through ORDER BY DBMS_RANDOM.VALUE and limited to the desired count with ROWNUM. These logical steps find random entries while ensuring they haven't appeared in recent loads, effectively controlling data redundancy in the target load .

Q: What steps are necessary to configure a SQL table that only permits a single type of data object at a time, and how can this be efficient in practice?

The process begins by creating a lookup table that indicates the type, priority, and current load status. The target table is then truncated before each load to ensure it receives only the current data type. Efficiency is maintained by scheduling data loads via SQL scripts that check and update the lookup table's LOAD_FLAG, rotating the content type to be loaded using a controlled sequence. This mechanism keeps the data in the target table timely and relevant as it changes per scheduled load .

Q: Explain how a priority system in lookup tables helps organize data operations efficiently in SQL.

The priority system in lookup tables directs operations by establishing a hierarchy of actions. Each content type or data operation is given a priority number, defining its processing order. During automated loads, SQL references this order to determine the sequence of data handling. Adjustments to priorities can reorder processes swiftly without overhauling underlying system logic, promoting organized and flexible data management .

Q: How can SQL be used to ensure that a target table only contains products loaded within the last 30 days, and never older products?

To ensure the target table only contains products loaded within the last 30 days, SQL can be used to periodically delete older records. This is achieved by executing a DELETE statement that removes entries with an INSERT_DATE older than 30 days, specifically: DELETE FROM TGT_PRODUCTS WHERE INSERT_DATE < SYSDATE - 30. This approach systematically maintains the table with only relevant data .

Q: In what ways does the DELETE query enhance performance in maintaining a SQL table with dynamic data requirements?

The DELETE query enhances performance by cleaning up obsolete data, ensuring the database contains only relevant, up-to-date entries. By executing DELETE FROM TGT_PRODUCTS WHERE INSERT_DATE < SYSDATE - 30 periodically, it prevents data bloat and optimizes query performance as the target table consistently remains small and manageable, which is especially crucial in systems where timeliness of data is critical .

Q: How can a round-robin approach be applied in SQL to load content types in a specific sequence into a target table?

A round-robin loading strategy can be implemented using a lookup table, which includes a LOAD_FLAG to indicate which content type to load. Initially, the CONTENTS_LKP table has priorities and a LOAD_FLAG. SQL updates this flag to rotate through content types. Each loading instance involves truncating the target table, then inserting records of the content type marked by LOAD_FLAG in CONTENTS_LKP. After loading, the LOAD_FLAG is updated, cycling through content priorities sequentially .

Q: What strategy can be implemented to select random products from a products table to load into a target table, ensuring no duplicates exist from recent loads?

The strategy involves using a nested SELECT statement combined with a NOT EXISTS clause to ensure uniqueness. By selecting from the PRODUCTS table where no matching PRODUCT_ID is found in TGT_PRODUCTS, the query ensures no duplicates if the product has been loaded recently. This is followed by ordering the selection randomly using DBMS_RANDOM.VALUE and limiting the number of records with ROWNUM .

Uploaded by

RaJu Bhai

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

0% found this document useful (0 votes)

14 views7 pages

SQL Query Interview Questions Part 5

Uploaded by

RaJu Bhai

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

11/29/2018 SQL Query Interview Questions - Part 5

Home Data Warehouse Informatica Informatica Scenarios Informatica Cloud Oracle Unix Hadoop

Search... Search
SQL Query Interview Questions - Part 5
Write SQL queries for the below interview questions:
Popular Posts

Informatica Scenario Based Interview Questions with

Answers - Part 1
1. Load the below products table into the target table.
Unix Sed Command to Delete Lines in File - 15 Examples

CREATE TABLE PRODUCTS String Functions in Hive

(
Top Examples of Awk Command in Unix
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(30) Sed Command in Unix and Linux Examples
);
Design/Implement/Create SCD Type 2 Effective Date
Mapping in Informatica
INSERT INTO PRODUCTS VALUES ( 100, 'Nokia');
INSERT INTO PRODUCTS VALUES ( 200, 'IPhone'); Date Functions in Hive
INSERT INTO PRODUCTS VALUES ( 300, 'Samsung');
INSERT INTO PRODUCTS VALUES ( 400, 'LG'); SQL Queries Interview Questions - Oracle Part 1
INSERT INTO PRODUCTS VALUES ( 500, 'BlackBerry');
Top Unix Interview Questions - Part 1
INSERT INTO PRODUCTS VALUES ( 600, 'Motorola');
COMMIT; Update Strategy Transformation in Informatica

SELECT * FROM PRODUCTS;

Have Questions? Follow Me
PRODUCT_ID PRODUCT_NAME
-----------------------
100 Nokia
200 IPhone
300 Samsung
400 LG

[Link] 1/7
11/29/2018 SQL Query Interview Questions - Part 5

500 BlackBerry vijay bhaskar

600 Motorola Add to circles

The requirements for loading the target table are:

Select only 2 products randomly.
Do not select the products which are already loaded in the target table with in the last
30 days.
Target table should always contain the products loaded in 30 days. It should not
contain the products which are loaded prior to 30 days.
Solution:

First we will create a target table. The target table will have an additional column INSERT_DATE
to know when a product is loaded into the target table. The target
table structure is
994 have me in circles View all

CREATE TABLE TGT_PRODUCTS

(
PRODUCT_ID INTEGER,
PRODUCT_NAME VARCHAR2(30),
INSERT_DATE DATE
);

The next step is to pick 5 products randomly and then load into target table. While selecting
check whether the products are there in the

INSERT INTO TGT_PRODUCTS

SELECT PRODUCT_ID,
PRODUCT_NAME,
SYSDATE INSERT_DATE
FROM
(
SELECT PRODUCT_ID,

[Link] 2/7
11/29/2018 SQL Query Interview Questions - Part 5

PRODUCT_NAME
FROM PRODUCTS S
WHERE NOT EXISTS (
SELECT 1
FROM TGT_PRODUCTS T
WHERE T.PRODUCT_ID = S.PRODUCT_ID
)
ORDER BY DBMS_RANDOM.VALUE --Random number generator in oracle.
)A
WHERE ROWNUM <= 2;

The last step is to delete the products from the table which are loaded 30 days back.

DELETE FROM TGT_PRODUCTS

WHERE INSERT_DATE < SYSDATE - 30;

2. Load the below CONTENTS table into the target table.

CREATE TABLE CONTENTS

(
CONTENT_ID INTEGER,
CONTENT_TYPE VARCHAR2(30)
);

INSERT INTO CONTENTS VALUES (1,'MOVIE');

INSERT INTO CONTENTS VALUES (2,'MOVIE');
INSERT INTO CONTENTS VALUES (3,'AUDIO');
INSERT INTO CONTENTS VALUES (4,'AUDIO');
INSERT INTO CONTENTS VALUES (5,'MAGAZINE');
INSERT INTO CONTENTS VALUES (6,'MAGAZINE');
COMMIT;

SELECT * FROM CONTENTS;

[Link] 3/7
11/29/2018 SQL Query Interview Questions - Part 5

CONTENT_ID CONTENT_TYPE
-----------------------
1 MOVIE
2 MOVIE
3 AUDIO
4 AUDIO
5 MAGAZINE
6 MAGAZINE

The requirements to load the target table are:

Load only one content type at a time into the target table.
The target table should always contain only one contain type.
The loading of content types should follow round-robin style. First MOVIE, second
AUDIO, Third MAGAZINE and again fourth Movie.

Solution:

First we will create a lookup table where we mention the priorities for the content types. The
lookup table “Create Statement” and data is shown below.

CREATE TABLE CONTENTS_LKP

(
CONTENT_TYPE VARCHAR2(30),
PRIORITY INTEGER,
LOAD_FLAG INTEGER
);

INSERT INTO CONTENTS_LKP VALUES('MOVIE',1,1);

INSERT INTO CONTENTS_LKP VALUES('AUDIO',2,0);
INSERT INTO CONTENTS_LKP VALUES('MAGAZINE',3,0);
COMMIT;

SELECT * FROM CONTENTS_LKP;

[Link] 4/7
11/29/2018 SQL Query Interview Questions - Part 5

CONTENT_TYPE PRIORITY LOAD_FLAG

---------------------------------
MOVIE 1 1
AUDIO 2 0
MAGAZINE 3 0

Here if LOAD_FLAG is 1, then it indicates which content type needs to be loaded into the target
table. Only one content type will have LOAD_FLAG as 1. The other content types will have
LOAD_FLAG as 0. The target table structure is same as the source table structure.

The second step is to truncate the target table before loading the data

TRUNCATE TABLE TGT_CONTENTS;

The third step is to choose the appropriate content type from the lookup table to load the source
data into the target table.

INSERT INTO TGT_CONTENTS

SELECT CONTENT_ID,
CONTENT_TYPE
FROM CONTENTS
WHERE CONTENT_TYPE = (SELECT CONTENT_TYPE FROM CONTENTS_LKP WHERE LOAD_FLAG=1

The last step is to update the LOAD_FLAG of the Lookup table.

UPDATE CONTENTS_LKP
SET LOAD_FLAG = 0
WHERE LOAD_FLAG = 1;

[Link] 5/7
11/29/2018 SQL Query Interview Questions - Part 5

UPDATE CONTENTS_LKP
SET LOAD_FLAG = 1
WHERE PRIORITY = (
SELECT DECODE( PRIORITY,(SELECT MAX(PRIORITY) FROM CONTENTS_LKP) ,1 , PRIORIT
FROM CONTENTS_LKP
WHERE CONTENT_TYPE = (SELECT DISTINCT CONTENT_TYPE FROM TGT_CONTENTS)
);

SQL Query Interview Questions

SQL Query Interview Questions On Connect By Clause
Oracle Analytical Functions
How to find (calculate) median using oracle sql query
Oracle Complex Queries

If you like this post, then please share it on Google by clicking on the +1 button.

No comments:

Enter your comment...

Comment as: Google Accoun

Publish Preview

[Link] 6/7
11/29/2018 SQL Query Interview Questions - Part 5

Newer Post Home Older Post

[Link] 7/7

Common questions