SQL

Question 1

What is SQL?

Answer 1

It is Structure Query Language.

It is mySQL, PostGRSQL, MarinDB

Question 2

What is ERD?

Answer 2

Entity Relationship Diagram

Question 3

What is a database?

Answer 3

Besides the HTML and CSS that make up the view of a particular page, and the backend logic that dictates the functionality, there’s the collectin of organized information that can easily be accessed, managed, and updated.

Question 4

What are the normal forms?

Answer 4

1 - no multiple data points in a column data row
2 - no repeating data points in a column
3 - columns are not dependent on other non-primary key columns

Question 5

What are the set industry standards?

Answer 5

1) make the table name plural and ALL lowercase
2) use ‘id’ as the primary key - make it auto-increment
3) name foreign keys with singular_table_name_id
4) use created_at and updated_at for the timestamp in EVERY table you create

Question 6

What are the datatypes that you will use 95% of the time?

Answer 6

varchar, char, int, bigint, tinyint, float, text, datetime

Question 7

What can SQL statements do?

Answer 7

SQL statements are used to perform tasks; they can SELECT data, SELECT data WHERE some conditions are true, INSERT data, UPDATE data, DELETE data, and JOIN different tables together.

Question 8

SQL Server?

Answer 8

mySQL server connects first to mySQL Workbench, and later our web applications. This will be a database server, which will be listening for connections on localhost.

Question 9

How to install mySQL and steps?

Answer 9

brew install mysql
brew services start mysql
mysqladmin -u root password ‘root’
mysql -u root -p

installed, configured, and started a MySQL server

Question 10

How do I change the port on mySQL server?

Answer 10

1) create a file named my.cnf
place in the file (port nums you want):
[client]
port = 3307
[mysqld]
port = 3307

2) save this file to /etc/my.cnf
3) run
brew services restart mysql

Question 11

What are the two main ways to import structure (tables and columns) or data (rows or records) or both into your MySQL workbench

Answer 11

If you have an SQL file, you can just copy and paste the commands into the editor and click run. If you have an ERD diagram, you can forward engineer into MySQL workbench.

Question 12

What is a good SQL statement?

Answer 12

SELECT FROM WHERE ORDER BY

Order by must be after WHERE

Question 13

There are two ways to insert or edit

Answer 13

You can select all from a table and directly edit in the GUI or
INSERT INTO (DATABASE).(TABLE) (ATTRIBUTES) VALUES (VALUES FOR ATTRIBUTES)

(assuming you are in the database in MySQL)
INSERT INTO table_name (column_name1, column_name2)
VALUES(‘column1_value’, ‘column2_value’);

Question 14

There are two ways to update our database

Answer 14

UPDATE table_name SET column_name1 = ‘some_value’, column_name2=’another_value’ WHERE condition(s)

IF WHERE CONDITION IS NOT ADDED TO THE UPDATE STATEMENT< THE CHANGES WILL BE APPLIED TO EVERY RECORD IN THE TABLE

Question 15

DELETE requires a special condition, what is it?

Answer 15

If you are getting an error regarding SQL SAFE UPDATES, run the following command to let MySQL Workbench know that you know what you are doing and you want to DELETE stuff from the database.

SET SQL_SAFE_UPDATES = 0;

IF WHERE CONDITION IS NOT ADDDED TO THE DELETE STATEMENT, IT WILL DELETE ALL THE RECORDS ON THE TABLE

Question 16

How do you export a database?

Answer 16

You first need to export with data export, change the location of the export, select the database, create schema..and you should have created the .sql file necessar

Question 17

When we fetch the data from our database, what returns?

Answer 17

The fetch method returns us an array of dictionaries where each dictionary represents a row in the table. For each dictionary, the keys correspond to the column names and the values correspond to that entry’s value

Question 18

What are some new things we see in the dictionary?

Answer 18

The ‘u’ that precede each key or value that is a string. The ‘us indicates a Unicode string that is inherently different from the simple string type. Unicode is used to represent a wider variety of languages and symbols.

The ‘L’ append to each value for the ‘id’ field in the dictionary. This represents a ‘long’ which is a type to store numbers in memory. The ‘long’ type is more accurate but will behave exactly as an int.

Question 19

Is keeping our user data safe part of a full-stack developers responsibility?

Answer 19

Yes, this means we need to store the data safely as well.

Question 20

What is we store PW’s as is in our DB?

Answer 20

To prevent, we need to mask our passwords BEFORE we put them into our database. This is called hashing.

You really just need to know basic md5 hashing and salt

Question 21

What is the difference between hashing and encryption?

Answer 21

Hashing, there is no way to reverse the process algorithmically where encryption you can.

Hashing fact: the same input into a hashni algorithm always produces the same output. In practical terms this means that we can store a hashed password, re-hash a users input on login and compare the two hashed values in order to authenticate passwords

Question 22

Why is salted hashing better?

Answer 22

You can make your data more secure by making the hashing method more random. By random, we mean more unpredictable. md5 hashing is the same no matter what computer runs the code. So to make our hasing more powerful, we will add a salt to our hashed string. A salt is a random unique key used to generate a unique password

Question 23

What is a salt?

Answer 23

A salt is a string of random characters that will be passed to a hashing method (an md5()) along with the string we are trying to has (the submitted password) via concatenation. The hashing method that uses the salt is designed in such a way that it takes the salt to compute the hashed string, using the salt as an ‘ingredient’ in the hashing ‘recipe’

Question 24

How do we generate a salt?

Answer 24

import os, binascii
salt = binascii.b2a_hex(os.urrandom(parameter))

The function called os.urandom() returns a string of bytes. The number of bytes is equal to the parameter provided. This string isn’t a normal alphanumeric string, so we turn it into a string using the function b2a_hex(), which will turn the value into a normal alphanumeric string. This new random string will be our salt. The idea is to store this salt during the registration process.