Scribd
Upload
28 views

Data Engineer SQL Assignment

1. The query performs a left join between tables A and B on their ID fields. It will return all rows from table A along with any matching rows from table B, including rows where the B.ID is null. 2. The second query adds a WHERE clause to filter results where B.Cntr equals 4000. 3. The third query performs a full outer join between tables A and B on their ID fields. It will return all rows from both tables, along with matched rows from the other table.

Uploaded by

aasd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
28 views

Data Engineer SQL Assignment

1. The query performs a left join between tables A and B on their ID fields. It will return all rows from table A along with any matching rows from table B, including rows where the B.ID is null. 2. The second query adds a WHERE clause to filter results where B.Cntr equals 4000. 3. The third query performs a full outer join between tables A and B on their ID fields. It will return all rows from both tables, along with matched rows from the other table.

Uploaded by

aasd
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

SQL TEST

Part A
You have 2 tables:

Table a (Id int PK, Cntr int)

Id Cntr
1 100
2 200
3 NULL
4 300
5 null
Table b (Id int PK, Cntr int)

Id Cntr
2 NULL
4 4000
5 5000
7 NULL
8 8000

1. What will be the result set of the following query:

SELECT A.Id, A.Cntr, B.Id, B.Cntr

FROM A LEFT JOIN B

ON A.ID=B.ID

2. What will be the result set of the following query:

SELECT A.Id, A.Cntr, B.Id, B.Cntr

FROM A LEFT JOIN B

ON A.ID=B.ID

WHERE B.Cntr = 4000

3. What will be the result set of the following query:

SELECT A.Id, A.Cntr, B.Id, B.Cntr

FROM A LEFT JOIN B

ON A.ID=B.ID

AND B.Cntr = 5000


4. What will be the result set of the following query:

SELECT A.Id, A.Cntr, B.Id, B.Cntr

FROM A FULL JOIN B

ON A.ID=B.ID

Part B

Table A structure:

Field Name Data Type Description / Comments

Id Int Primary key

PolicyId UniqueIdentifier AD76A6C6-405A-44C5-A841-73094B5EB79A - Example


Premium money

PremDate Datetime 2013-04-30 09:59:42.653 -Example

1. Write a query that returns the second max premium of the policies (Expected output PolicyId ,
Premium)
2. Write a query that returns the two most recent dates of each policy (Expected output PolicyId
PremDate)
3. Write a query that returns the diff in days between the PremDate and the previous PremDate of
the same PolicyID (In your answer use the LAG window function) ( Expected output PolicyId,
PremDate,  Previous PremDate,  Diff )
4. Write a query that returns PolicyID distribution
5. Write a query that returns the number of duplicate PolicyIDs
6. Write a query that returns the unique number of PremDates in the table
7. Write a query that returns the total Premium per day ( according to the PremDate )
Table B structure:

Field Name Data Type Description / Comments

PolicyId UniqueIdentifier AD76A6C6-405A-44C5-A841-73094B5EB79A - Example

Agentname String Moshe Raviv

WorkAreaCity String Tel-Aviv

1. Write a query that returns every WorkAreaCity where the average Premium per PolicyId is
greater than 400 ( In your answer use at least one CTE table ) (Expected output WorkAreaCity)
2. Write a query that returns the two most active Agents. Agent activity is based on the number of
Policies ( PolicyId ) that were sold during the last year (Expected output Agentname, Number of
Policies)

Part C
General questions:

1. Given two tables, table A contains 10 records and table B contains 20 records
a. What are the minimum records will be displayed if you will use Inner join?
b. What are the minimum records will be displayed if you will use right join?
2. What is the difference between union and union all?
3. Can you offer a different way to replace the distinct clause in the select statement and still
maintain the same functionality of distinct?
4. If you apply Count (*) on a given table and apply count (distinct<column>) and the <column> is
Primary key, will the results of the queries be different? Explain.

You might also like