SQL

Question 1

What’s the difference between CREATE VIEW and CREATE OR REPLACE VIEW?

Answer 1

The latter drops a view with the name specified in the statement if it already exists, and creates it again, saving you from running into errors, and saving you time by not needing to drop the view before

Question 2

Which are the 4 types of JOINs?

Answer 2

Left, right, inner, and outer

Question 3

What’s a self join?

Answer 3

A join that links a table to itself

Question 4

Which are the 3 statements to join 2 tables?

Answer 4

ON, WHERE, and USING

Question 5

Is cross join the same as self join?

Answer 5

No

Question 6

When to use indexes?

Answer 6

Whenever you want to improve performance

Question 7

Which are the 4 primitives data types?

Answer 7

Integer, string, boolean, and float

Question 8

What’s the difference between CHAR and VARCHAR?

Answer 8

The former only accepts fixed-length values, whereas the latter accepts values regardless of their length

Question 9

Which are the 3 main types of relationships?

Answer 9

One-to-one, one-to-many, and many-to-many

Question 10

Can a primary key be nullable?

Answer 10

No

Question 11

Can a boolean column be a primary key?

Answer 11

No

Question 12

Which are the constraints you can set on a column?

Answer 12

Not null, unique, primary key, foreign key, check, and default

Question 13

What does the rollback statement mean within a transaction block?

Answer 13

If an error occurs within the block, it will rollback the data to its previous status, discarding any changes that might have occurred during its execution

Question 14

How do you guarantee referencial data integrity?

Answer 14

Foreign keys and constraints

Question 15

What would you use a stored procedure for?

Answer 15

To improve performance for a complex query, as it would compile once, and run based on an execution plan that is created the first time the procedure is ran