Question

1. Can you please describe the different kinds of joins used in SQL?

2. What SQL syntax would you use to ensure you are not returning duplicates in your data sets?

3. What SQL syntax would you use to change data in a table if you had write access to the table? Having write access means the data manipulation would occur in the table being pulled from.

4. What SQL syntax would you use to change data in a table if you only had read access to the table? Having read access means the data manipulation would occur in the dataset rather than in the table being pulled from.

5. What methodologies could you use to improve the performance of a SQL query?

6. What SQL syntax could you use to filter date ranges in your dataset?

Answer 1

QUES 1. Consider the tables as follows :-

The result of JOIN is shown in the third table :-

Query :-

SELECT ID, NAME, AGE, AMOUNT

FROM CUSTOMERS, ORDERS

WHERE CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

SELECT ID, NAME, AGE, AMOUNT

FROM CUSTOMERS

<JOIN_TYPE> ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

The <JOIN_TYPE> can be one of the top 4 of the following :-

1. INNER JOIN :- returns the set of rows that are common to both tables. In short, it performs an intersection from both tables. The inner keyword is optional as illustrated above.

2. LEFT JOIN :- returns all records from left table and it doesn't matter if there are no matches in the right table.

3. RIGHT JOIN :- returns all records from the right table and it doesn't matter if there are no matches for the corresponding entries in left table.

4. FULL JOIN :-  returns all records where there is a match in one of the tables. It is equivalent to union operation on both tables.

5. CARTESIAN JOIN :- returns the cartesian product of the sets of rows from the joined tables

6. SELF JOIN :- used to join a table to itself as if it were two separate tables, temporarily renaming one of them using an alias

** 5. & 6. are rarely used, so they can also be omitted.

If you liked this answer, please give a thumbs up rating. Feel free to comment for any explanations/ clarifications.