Intro to Databases Checkpoint- Web Dev track @ Bloc

intro_to_database.txt

 #### Exercises

 1. What data types do each of these values represent?

     "A Clockwork Orange" : String
        42 :  Integer
      09/02/1945 : Integer/Date
       98.7: Float
      $15.99: Float/Currency 

 2 .Explain when a database would be used. Explain when a text file would be used.
      Databases are used to store larger amount of data that can be quickly read and modified. Databases are stored in binary format which is not readable by humans, therefore, use text files when the document needs to be read by humans. 
       
 3. Describe one difference between SQL and other programming languages.
       Unlike other other languages, SQL is declarative and not procedural. Thus, we don’t define the algorithm/method to be used on our database search, instead we define what needs to be searched and the database engine decides what is the most efficient way to search. 

 4. In your own words, explain how the pieces of a database system fit together at a high level.
          Databases are organized into tables that consists of columns and rows. Each row contains the data pertaining to an entry while the columns define what the data represent.  The value in each table cell can be queried. 

 5. Explain the meaning of table, row, column, and value.

       Table: Structure that holds a collection of related data 
       Row:  it is the collection of a conceptual unit 
       Column:  define what the data represents 
       Value: data contained in a cell
 
 6. List three data types that can be used in a table.
          String, float, integer

 7. Given this payments table, provide an English description of the following queries and include their results:

   a)    SELECT date, amount       
          FROM payments;

           This query selects the dates with corresponding amounts from the payments table

	---
	**Query #1**
   	 SELECT date, amount
    	     FROM payments;

	| date                     | amount    |
	| ------------------------ | --------- |
	| 2016-05-01T00:00:00.000Z | 1500.0000 |
	| 2016-05-10T00:00:00.000Z | 37.0000   |
	| 2016-05-15T00:00:00.000Z | 124.9300  |
	| 2016-05-23T00:00:00.000Z | 54.7200   |

	---


  b)  SELECT amount
        FROM payments
       WHERE amount > 500;

            This query ask for amounts  that are higher than 500 from the payments table 

     amount
       1500.0000


   c)  SELECT *
        FROM payments
        WHERE payee = 'Mega Foods';

               This query selects all the columns available where the payee is Mega Foods 

date                      payee        amount       memo
2016-05-15T00:00:00.000Z  Mega Foods  124.9300     Groceries


 8. Given this users table, write SQL queries using the following criteria and include the output:

   a) The email and sign-up date for the user named DeAndre Data.

       
	SELECT email, signup
	FROM users
	WHERE name = 'DeAndre Data';


email                    signup
[email protected]     2008-01-20T00:00:00.000Z


   b)The user ID for the user with email '[email protected]'.

	SELECT userid
	FROM users
	WHERE email = '[email protected]';

   userid
     1

  c)All the columns for the user ID equal to 4.

	SELECT *
	FROM users
	WHERE userid = 4;

userid  name              email                   signup
  4     Brandy Boolean [email protected] 1999-10-15T00:00:00.000Z