SQL

Question 1

What is the Having and Group By clauses for?

Answer 1

To perform aggregate calculation and filter results on specific conditions.

Question 2

What is the difference between an inner and outer join?

Answer 2

Inner only returns matching rows of the two tables in a join, outer join return all the unmatched rows from either the left, right or both tables in the join.

Question 3

What two sql functions handle null datasets?

Answer 3

Coalesce() and NVL()

Question 4

What does the coalesce function do?

Answer 4

Takes two or more expressions and returns the first expression that is not-null from the list of expressions.

Question 5

What is the difference between BETWEEN and IN operators in SQL?

Answer 5

Between is for a range of values, IN specifies multiple possible values

Question 6

What is a correlated subquery?

Answer 6

A subquery that uses data from the containing query.

Question 7

What are the database Constraints?

Answer 7

1. DEFAULT
2. UNIQUE
3. NOT NULL
4. PRIMARY KEY
5. FOREIGN KEY

Question 8

What is a foreign key?

Answer 8

A database enforced link between tables.

Question 9

What is the difference between INTERSECT and UNION?

Answer 9

INTERSECT return rows that are common to both queries, UNION returns all distinct rows

Question 10

What are some aggregate functions?

Answer 10

1. MIN()
2. COUNT()
3. FIRST()
4. LAST()

Question 11

What are some scalar functions?

Answer 11

1. UCASE()
2. ROUND()
3. NOW()

Question 12

What is the difference between UNION and UNION ALL

Answer 12

UNION is distinct rows, UNION ALL allows duplicates

Question 13

What is the difference between a clustered and non-clustered index?

Answer 13

Database keeps the data in the same order as the keys in the clustered index.

Question 14

What are the 4 relationships in SQL?

Answer 14

1. One-to-One
2. One-to-Many
3. Many-to-Many
4. Self-reference