SQL

Question 1

INNER JOIN

Answer 1

Select all records from Table A and Table B, where the join condition is met. Intersection.

Question 2

RIGHT JOIN

Answer 2

Select all records from Table B, along with records from Table A for which the join condition is met (if at all).

Question 3

FULL JOIN

Answer 3

Select all records from Table A and Table B, regardless of whether the join condition is met or not.

Question 4

Left outer join

Answer 4

A left outer join will give all rows in A, plus any common rows in B

Question 5

Right outer join

Answer 5

A right outer join will give all rows in B, plus any common rows in A.

Question 6

Full outer join

Answer 6

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn’t have a corresponding datum in B, then the B portion is null, and vice versa.

Question 7

core aggregate functions

Answer 7

AVG, COUNT, MIN, MAX, SUM

Question 8

normalization

Answer 8

To free the collection of relations from undesirable insertion, update and deletion dependencies;

To reduce the need for restructuring the collection of relations, as new types of data are introduced, and thus increase the life span of application programs;

To make the relational model more informative to users;

To make the collection of relations neutral to the query statistics, where these statistics are liable to change as time goes by.

Question 9

stored procedure

Answer 9

is a subroutine available to applications that access a RDBMS

Uses

• data-validation (integrated into the database)
• access-control mechanisms.
• consolidate and centralize logic that was in applications. - execution of several SQL statements

One can use nested stored procedures by executing one stored procedure from within another.

Question 10

SUBSTRING

Answer 10

SUBSTRING(‘hello’, 3, 1)

index 1 based!!

one letter is substring(‘hello’, 2, 2)

Question 11

CASE

Answer 11

CASE
         WHEN blah THEN blah
         WHEN blah THEN blah
         ELSE blah
END

Question 12

group by and getting id

Answer 12

joining back with subselect inside join to join columns that match and get id