Become A SQL Developer (LinkedIn)

Question 1

What is CRUD?

Answer 1

Create, Read, Update, Delete

Question 2

What does ACID relate to?

Answer 2

It relates to completing a transaction so that all elements are completed or none at all e.g. debiting a bank account and crediting another

Question 3

What does ACID stand for?

Answer 3

Transactions should follow a set of principles. Transactions must be:

Atomic - the transaction is indivisible. It cannot be separated out.

Consistent - whatever the transaction does, it must leave the database in a valid or consistent state. Integrity should not be violated.

Isolated - while the activities in the transaction are being completed, nothing else can make changes to the data involved.

Durable - the information that we change during the transaction, actually gets written to the database.

Question 4

What is a transaction?

Answer 4

It’s a type of setup that tells the RDBMS that all CRUD elements must be complete or none at all.

Question 5

What’s the first step in building the database?

Answer 5

Ask what data it needs to store. What are the entities, attributes, datatypes, and keys (primary, foreign and composite?

Question 6

What kind of diagram is good for a database?

Answer 6

Entity Relationship Diagram (ERD)

Question 7

What are the naming conventions for table and attribute names

Answer 7

Table name - Start with a capital letter and use the plural as in Customers

Attributes - use upper camel case and use singular

Question 8

What’s the difference between CHAR and VARCHAR

Answer 8

VARCHAR will store up to the number of characters you tell it but CHAR will reserve that amount in memory every single time - a huge impact when dealing with very large databases.

Question 9

What are two data types that we can use for the date - what’s the difference?

Answer 9

DATE and DATETIME - the latter stores the time also

Question 10

Show a decimal datatype with 3 digits before the decimal and 2 after?

Answer 10

DECIMAL(3,2)

Question 11

What does NULL mean?

Answer 11

It means the absence of a value

Question 12

What are the next steps (step 2) in creating the database?

Answer 12

Start looking at the relationships between the tables/entities.

Question 13

What is a one to many relationship.

Answer 13

It is where one row of a table connects to more than one row of another table e.g. a customer table and food dishes table. A food dish can appear many times on the customer’s tables (when we log their favourite dish). One dish may be the favourite of many customers. The foreign key will be on the many side i.e. on the customer’s side. It will appear many times in the customers table (in the favourite dish column)

Question 14

What is a cascading delete?

Answer 14

Is where you delete a record and the database goes on and deletes other records associated with that record?

Question 15

What is the third step in creating the database?

Answer 15

Normalize the database

Question 16

What is 1NF?

Answer 16

It requires that we have only one value in any one cell. It should not contain composite or multivalued entries.
We should also not have repeating groups in the table i.e. columns that contain the same type of data e.g. favourite dishes from the menu e.g. fav1, fav 2 etc. You can also just put in fav’s and put in all the favourite dishes - both are violations of 1NF.
Each row in the table must also contain a unique set of values.

Question 17

What is 3NF?

Answer 17

Values should not be stored if they can be calculated from another non-key field.

3NF tells you that each field in a row should represent something unique about a record

Question 18

What is denormalization?

Answer 18

The process of intentionally breaking the normalization rules.

Question 19

When is it appropriate to denormalize a database and why would you do it?

Answer 19

After normalization has occurred. It is not an excuse to skip normalization. We do it because e.g. if might be very resource costly to calculate a value by gathering lots of information from lots of different tables. Instead of doing this, we might just include a calculated value as a column in one of the tables.

Question 20

What is 2NF?

Answer 20

Ensure that a non-key field is not dependent on only part of a composite key (if it is then you will probably need to create another table with the part and the non-key field).