Lab 9: Database & Queries

Hey, welcome back! Hope you guys had a great break :) 

Here is your last assignment. Yaay :) (I know how you guys feel now, I have been there too!) So let's go ahead and start.

Initial set up

1. You have a file, 'BeachRentals.accdb' in your ICON. Download that. Double-click to open that.
2. This will be your starting point. 

Know the basic queries

• OR: To display names of the users from Colorado or Florida, you use OR this way => "Colorado" or "Florida" in the respective state column when you design the query. Make sure you give the appropriate names in the columns within the double quotes.
• AND:  Similarly, to display names of the users from both Colorado and Florida, you use AND this way => "Colorado" and "Florida" in the respective state column when you design the query.  
• Basic SQL queries: Link for reference

Let's start

• Query-1: From the Owner table: display the first and last names of all owners whose first names contain the string jo. Hint: This is similar to Query 3, in Lab 8. You should use Like. 

• Query-2: From the Client table: display the Names and the State of all clients from Colorado or Florida. Hint: Look at the OR example above.

• Query-3: From all three tables: display owner’s Last and First Name, Client Name, Current Due and Unit Number of all units that are rented by a client from Iowa or Colorado who owes more than 300$ . Hint:
• Splitting works! "display owner’s Last and First Name, Client Name, Current Due and Unit Number" => Get all the corresponding columns (Totally 5) from each table (that are given explicitly).
• "of all units that are rented" => One more column to add here.
• "by a client from Iowa or Colorado" => Get the states using Like
• "who owes more than 300$" => This is similar to Query 1 in Lab 8. 

• Query-4: From the Client table: display the Names and the State of all clients from a state that the user enters. Hint:
• To write a SQL query, you have to switch the view to "SQL View" by clicking the top-left "View" button.
• To match a parameter, say we want the "State"  value to hold a value entered by the user. For this, you'll have a query like this: SELECT <the columns you want> FROM <table> WHERE <table>.State = [Enter state];
• The values given within < >, should be replaced by appropriate values without the angle brackets.

• Query-5: From the Condo Unit table: display Weekly Rate and number of bathrooms of all units with rates greater than or equal to $800 and less than $1000. Hint: Split it up and use "AND". 

• Query-6: From the Condo Unit table: display Unit Number, Bedrooms, Rented & Next Available (this is a column) of all 2 bedroom units which are rented (Yes) and also display Unit Number, Bedrooms, Rented & Next Available of  three bedroom units available after January 1st 2015. Hint: Split it up. Query should be like this: (2 bedrooms and rented) or (3 bedrooms, you don't have to check for rentals). Dates can be compared as numbers and must have # symbol at the beginning and end like this #3/1/2015#.

• Query-7: From all three tables: display owner’s Last and First Name, Client Name and Current Due of all units that have current due greater than $300. Hint: This is similar to Query 1 in Lab 8 and Query 3 in Lab 9. 

• Query-8: From the Condo Unit table: display average weekly rates grouped by number of bedrooms. Hint: From Design on the ribbon click Totals(right-top) to display the Total option on the query. Split it up. Two things: Use AVG function to find average and GROUP BY.  

• Query-9: From the Condo Unit table: display Unit Number, Current Due and Amount Paid. Right-click the Field row in the next open column in the design grid to display a shortcut menu. Click "Zoom" on the shortcut menu to display the Zoom dialog box. Type Total Amount: [Amount Paid] + [Current Due] and click OK.

Lab 8: Introduction to MS Access

A little heads up - This lab is pretty simple. :) 

Initial set up

1. You have a file, 'moviesDB.mdb' in your ICON. Download that. Double-click to open that.
2. This will be your starting point.  

General info about  MS Access
To view tables

1. Double-click on the 'Director:Table' to view the tables and values associated with it. You'll have something, that looks like this:

Switch to 'Design View'

1. Right-click on the table, and click 'Design view' as shown below

View relationship between tables

1. Select Database Tools -> Relationship. Maximize the smaller tables to view the relationships well.
2. Learn and understand: How director and movies table are related? What is the primary key? How is it represented there? What does the connection arrow signify? 

You assignment starts here..

What is a query?
 Queries are used to view a subset of your data or to answer questions about your data.  

Query: Which movies have titles that start with the letter R?  

Which table and data do you need for this query?

Table: Movies
Column: Title - From title select movies which start with R.

Create a query

• Click Create tab -> Query Design (on the ribbon) -> Movies (Inside Show table) -> Add. Follow the picture below. Here, you select the tables that is required to implement the query. Once you have selected the tables you want, close the 'Show table' dialog box.

• Select the column you want as shown below

• Let's assume that we want the Movie 'Titles' column sorted in Ascending order. Therefore, we specify "Ascending" in "Sort:" row (Similar to Field - select from the drop-down list).
•  Make sure that "Show" check box is checked.
• Since we want all movies that start with letter R, we will use R* in the 'Criteria' column.
• '*'(called as 'asterisk') is a wildcard character which represents any combination of letters and numbers. You use it when you know part of a value, and the rest of the data — such as the length or pattern of the value — isn't important.
• You can use the Like operator to find values in a field that match the pattern you specify. Here, the pattern is 'R*" i.e any movie starting with the letter R.
• Save your query: After you write your criteria, press 'ctrl' and 's' keys to save the query. Give it a proper name. After you save, your query will appear on the Left side pane. You can double-click it to see the tables you have generated after running the query. 

• If you want to make changes to your query, you have to switch to design view. To do that, right-click on your query (which is on the left side pane), and click 'Design View' from there. 
• You'll get a page, which looks similar to this. 

• In this way, you'll create the following queries:

1. Which movies were released before 1980, ordered by year of release? 
2. When were each of the Psycho movies released? (Remember: You should have movie title along with release year)
3. Which directors have last names that start with the letter S? (Remember: You should have Director's Last name (1st preference - Ascending order), First Name (2nd preference - Ascending order))
4. Which movies were directed by directors who were born after 1955?  (Remember: This query involves combination of both the tables. You should have Director's Last name (1st preference - Ascending order), First Name (2nd preference - Ascending order) and Birth Year along with Movie Title (Ascending order).)

• Some reference links:
• Like condition - http://www.techonthenet.com/access/queries/like.php
• Query example - http://www.baycongroup.com/access2007/05_access.html

Lab 7: Javascript

Initial set up

1. You have a file, 'Lab-7-start.zip' in your ICON. Download that. Copy it on your desktop. 
2. Remember to extract the file. Right click on the .zip file and click 'Extract all'. Now, you will have a folder named 'Lab-7-start'.
3. Go to the folder 'Lab-7-start'. 
4. To view the design: Right click on 'start-bean.html' - click 'Open with' - click 'Google chrome'.
5. To view the code: Right click on 'start-bean.html' and click 'Edit with Notepad++'. Now you will see the HTML/Javascript code. 
6. Now, you can edit in two ways:
1. Edit the Notepad++ and view the changes on browser by refreshing it
2. Or, copy all the code in your Notepad++ and paste them here http://codepen.io/pen/ and view the changes interactively. 

Know what you have!

1. Remember this? This is CSS. Click here for a quick intro. This is the place you'll focus if you want to change the styles like background image, colors etc.

<style type="text/css">

    body {background-color:saddlebrown; 

               color:darkorange; 

               font-family:helvetica; 

               text-align:center}

 

    table {margin-left:auto; 

               margin-right:auto; 

               text-align:center; 

               background-color:#993300; 

               border-style:solid; 

               border-color:firebrick; 

               border-width:medium; padding:8px }

</style>

2. Assign variables inside <script> tag which you'll be using in your code.

<script type="text/javascript"> 

       var shots=1; 

       var drink="none"; 

 </script>

3. HTML Forms

form tag: HTML forms are used to collect user input. 

action attribute: The action attribute specifies where to send the form-data when a form is submitted.

<form action="" > 

4. HTML table

The <table> tag defines an HTML table. The <tr> element defines a table row, the <th> element defines a table header, and the <td> element defines a table cell. More info on tables.

The <input> tag specifies an input field where the user can enter data. The type attribute specifies the type of <input> element to display. The default type is: text. More info on input tag attributes.

The value attribute is used differently for different input types:

• For "button", "reset", and "submit" - it defines the text on the button
• For "text", "password", and "hidden" - it defines the initial (default) value of the input field
• For "checkbox", "radio", "image" - it defines the value associated with the input (this is also the value that is sent on submit). 

The onclick attribute fires on a mouse click on the element. Here, on mouse click, shots variable is assigned with value 1. More info on HTML event attribute.

<table>

    <tr>

    <td><input type="button" value="1" onclick='shots=1'/></td>

......

5. More on onclick..

onclick can also be used to define a set of instructions (commonly called as 'function').

<td><input type="button" value="Total" onclick=

          
           'var price;                                        //initialize a variable 'price'

var taxRate=0.088;  //initialize a variable 'taxRate' with a initial value = 0.088

if (drink == "espresso") 

           //'if' is a conditional statement. If the variable drink's value is "espresso", then assign the variable //price to 1.40

  price=1.40; 

.......... 

6. Final note:

document.forms[0].total.value="0.00" means, Get the value of 'total'(variable) from the  <form> element (index 0 - first) in the document. More on DOM (Document Object Model).

7. Now that we have a good idea about HTML/CSS/Javascript, follow the instructions below and modify your initial html document to complete your Lab assignment 7.

1) Put a background image on the page that tiles over the whole page. (2 points) Reference link

2) Write appropriate text for titles and buttons. (2 points)

3) Change all table colors, margins. Change all text colors. (2 points)

4) Change identifiers and variable names to descriptive names. Substitute “drink” and “shots” “espresso” etc. with appropriate names. (3 points for variable names, 1 point for the rest)

5) Change price value. (1 point)

6) Computations must work. (2 points)

7) Transfer the page to the MyWeb server and submit a working web link to the page. (3 points)

In the end, if you click any item (Ice cream/Frozen Yogurt/Cookies) and click 'Total' button, it should give you a result like below:

Lab 6: Advanced Excel Features

There are a lot of details which you'll need to look, in this assignment. I will be providing hints for the most important ones. There are 4 parts in this assignment. (You can revise the basics of spreadsheet here - Spreadsheet basics) 

#1. Download Lab-6-start.txt from ICON. This will be your starting point.
#2. Convert your data as shown below into your excel file.

#3. PART - 1

• Format Cells: Follow this link to learn about formatting cells. Format Cells.
• Merge Cells:

           If we want to merge the cells L3,M3,N3 and rows L4,M4,N4, select the cells - click Home on the ribbon and click "Merge & Center".

• Now, you need to modify the excel sheet according to the following rules: (Assuming the data is positioned beginning in Cell A1, we need to move down to make room for some title headings.)

1. Add three rows at the top of worksheet. Merge and center a title "Estimated Total Quarterly Sales, Retail and E-Commerce" that spans columns A through I of row 1. Change the font to be larger and bold.
2. Merge cells C3 and D3 and enter the title Retail Sales.
3. Merge E3 and E4, with the title E-Commerce % Total Sales. 
4. Merge F3 and G3. Enter the title Quarter to Quarter % Change.
5. Place Total Sales in F4 and E-Commerce in G4.
6. Merge H3 and I3. Enter the title Year to Year % Change.
7. Place Total Sales in H4 and E-Commerce in I4.
8. Modify all of the column widths as necessary so these headings fit. 

 In the end, you should have something like this

#4. PART - 2 (Formula)

• Now, we calculate values of the columns by using excel formula. For all the formula, once you have finished it for one cell, you can use the formula for the entire column by dragging the 'small plus sign' (that appears when you hover over small box on the right bottom of the cell) to the cell, until which you want to calculate. 

1. E-Commerce % Total Sales = Total E-Commerce Sales/Total Sales. The corresponding excel formula for row 5 will be =E5/D5. Now, drag it all the way down to fill the entire column.
2. Calculate quarter to quarter changes in cells F5 and G5. The formula is the difference between the current quarter (i.e the corresponding retail sales of the current cell you are in) and previous quarter’s sales divided by previous quarter’s sales.
3. Starting with 2013 1st quarter, enter the formulas to calculate year to year changes for both total sales and e-commerce in cells H5 and I5. This formula is similar to the formulas for part 2 above, except that to get a yearly difference for the same quarter. Check for Q1 in 2013 and Q1 in 2012.

#5. PART - 3 (Formatting) 

• It's time to format the values we have and make it more presentable.

1. Format the values in columns C and D as money, with a dollar sign, commas and no decimal places.
2. Format the remaining values as percentages with 2 decimal places.
3. Add appropriate borders and colors to separate the columns whose values you calculated.
4. Add conditional formatting to display all negative percentage values in columns F and G s in red. Drag the formatting to the entire column using the 'small plus sign' as mentioned above. You can use conditional formatting as shown below: 
5. Conditional formatting for column H and I: when a current value decreases from the corresponding value in the previous quarter, you display those values in bold. Place the conditional format in cells H5 and I5, and then copy it down. Hint: make a new rule. The formula for H5 is = H5 < H6.

#5. PART - 4 (Charts)

1. Create a new worksheet named Graphs on which to draw the chart. Use the string function Concatenate (Use this link to learn about this function concatenate function) to create label values in cell C5 (in a new column). Then copy the formula down the rest of the column using the ‘small plus sign’. After this, your excel should look like this: 
   
2. Create a bar graph based on the new column (C5) and Quarterly Percentage Changes for both Total sales and E-Commerce Sales. Format the value axis numbers as percentages and show the corresponding quarter beneath each pair of sales values for that quarter.

          

#6. Almost done!!

1. Rename the tab of each worksheet Like: ‘Data’ for the sales details and ‘Chart’ for the bar graph. The footer on the left, the date in the middle, and the page number on the right hand side. Hint: Insert, Custom Footer. 
2. Format each page so that when printed, each page shows Prepared by: Your Name in
3. Set the page to Landscape mode. 
4. Use Wrap Text on the Home menu to make all text in a column heading visible.
5. Experiment with Print Preview to determine which pages are better formatted as portrait or landscape when printed. Fit each worksheet into one page!

#7. When you are done, turn in your excel sheet to the dropbox.
#8. Grading (Total-20 points)

      Based on Formula

• Concatenation: 1 point
• % Total: 2 points
• Quarter to Quarter % change: 2.5 points
• Year to year % change: 2.5 points

      Based on Formatting

• Negative values - red, Descending values - bold: 2.5 points each (5 points)
• Rest of formatting (colors, cells): 2 points

      Based on charts and others

• Chart: 3 points
• Fit into one page, landscape, footer: 2 points
• Extra rules (conditional formatting): -1 point

     

Lab 5: Learn HTML canvas

What is a canvas?
  A canvas, is a drawing surface! As simple as that. 

How is it used in HTML5?
The HTML5 Canvas element is an HTML tag similar to the <div>, <a>, or <table> tag, with the exception that its contents are rendered with JavaScript.

What is Javascript?

JavaScript is the programming language of the Web. All modern HTML pages are using JavaScript.

Let's get started!
Initial set up

1. You have a file, 'Lab-5-initial-page.zip' in your ICON. Download that. 
2. Remember to extract the file. Right click on the .zip file and click 'Extract all'. Now, you will have a folder named 'Lab-5-initial-page'.
3. Go to the folder 'Lab-5-initial-page'. 
4. To view the design: Right click on 'Lab-5-initial.html' - click 'Open with' - click 'Google chrome'.
5. To view the code: Right click on 'Lab-5-initial.html' and click 'Edit with Notepad++'. Now you will see the HTML/Javascript code. 
6. Now, you can edit in two ways:
1. Edit the Notepad++ and view the changes on browser by refreshing it
2. Or, copy all the code in your Notepad++ and paste them here http://codepen.io/pen/ and view the changes interactively. 

Know what you have inside 'Lab-5-initial.html'

1. This is how you initialize a canvas. This means that, we are introducing a canvas, with id="myCanvas" and it has the given height and width. We initialize a canvas, inside the <body> tag or <head> tag.

<canvas id="myCanvas" width="500" height="600">

</canvas>

2. Start a script. The canvas element is the actual DOM node that's embedded in the HTML page.  The canvas context is an object with properties and methods that you can use to render graphics inside the canvas element.  The context can be 2d or webgl (3d).

Each canvas element can only have one context.  If we use the getContext() method multiple times, it will return a reference to the same context object. 

Don't worry if you did not understand any of this. Just remember that, you need to define canvas element and context.

<script>

    var c=document.getElementById("myCanvas");

    var ctx=c.getContext("2d");

</script>

3. The cool part! Inside the script, you write functions to draw the shape.

  ctx.beginPath(); // start new shape       

  ctx.fillStyle = "green"; // fill the color of the shape with green

  ctx.lineWidth = 10;       // border thickness

  ctx.strokeStyle = "red"; // border color

  // Draw the circle.

 // (110,120) is the center point(x,y) - 80 is the radius of the circle - 0 is the start angle and  //2*Math.PI is the ending angle (in radians)

  ctx.arc(110, 120, 80, 0, 2*Math.PI); 

  ctx.fill();   // fill it with the color specified above

  ctx.stroke(); // draw!

4. From here, use this websites to help you understand canvas functions.

 HTML5 Canvas - Tutorial 1

 HTML5 Canvas - Tutorial 2 (w3schools)

HTML5 Colors

Using those websites, accomplish the following tasks for Lab-5

1. Colored shapes must be uniformly distributed on the canvas – shapes are not colliding with each other. Make sure it’s well aligned. (2 points)
2. Draw a border to the canvas. Provide a message in case the browser does not support the canvas element. (1 point)
3. Draw a circle – with a border, using the arc( ) method. (1 point)
4. Draw another circle – filled with a gradient that has several color stops. (4 points)
5. Write a message on the canvas using strokeText -- use a different gradient with several color stops as strokeStyle. (3 points)
6. Draw a colored rectangle with borders using fillRect and strokeRect. (2 points)
7. Draw at least 3 small circles of different colors on top of the rectangle. Note: No borders on these circles. (3 points)

After you finish, your final HTML page, should look something like this.

What to submit? (4 points)

If you did it using this - http://codepen.io/pen/ website, copy your code and paste it into your 'Lab-5-initial.html'.

Once you have your assignment ready, upload it to your MyWeb account and turn in the link and the .html file to your dropbox. 

Refer: 

Webpage as hyperlink on ICON

HTML/CSS/JS: Very useful website

I found some of the students, using this interactive website to code in HTML and CSS. You can also use it with Javascript, which you'll be working in future lab assignments. Try it out.

http://codepen.io/pen/

A screenshot is attached to help you start with it. 

Lab 4: Basic Spreadsheet

For this lab, you'll work on some basic concepts and function in spreadsheets. It is a pretty straightforward lab and it is fun to play with spreadsheet charts.

1. What are spreadsheets?

• A spreadsheet is a table of values arranged in rows and columns. Each value may have a predefined relationship to the other values. If you change one value, therefore, you may need to change other values as well.
• Spreadsheet applications (sometimes referred to simply as spreadsheets) are computer programs that let you create and manipulate spreadsheets electronically.
• One of the most widely used Spreadsheet application is, Microsoft Excel.

2. Some basic elements of spreadsheet:

Formula Bar
 In addition to the Ribbon Excel also has a Formula Bar. The Formula Bar is located above of the workbook window and allows you to enter or edit values or formulas in cells or charts. The Formula Bar also displays the value or formula used in the active cell.

3. Learn about various formatting you can do in Excel: http://www.excel-easy.com/basics/format-cells.html

4. Learn about various function/formula which can be used for easy calculation and results. Below are some helpful links about SUM formula. Figure out, how to use AVERAGE formula.

• http://www.wikihow.com/Use-the-Sum-Function-in-Microsoft-Excel
• http://www.excel-easy.com/functions.html
• http://www.excel-easy.com/introduction/formulas-functions.html

5. Some helpful links to learn about creating charts using Excel. Learn about various chart elements like Legends, Chart Title, Data Label,

• http://www.excel-easy.com/data-analysis/charts.html
• http://www.excel-easy.com/examples/pie-chart.html
• http://office.microsoft.com/en-us/excel-help/create-a-chart-HP001233728.aspx#BM5

6. Try it yourself: Sort the columns in your Excel.

7. In the end, you should have a spreadsheet, that looks like this:

Note: The SUM formula is used to calculate the 'Total'. 

8. Submit the Excel file you created in the dropbox.

Lab 3: HTML5 & CSS3

Before you start with Lab-3, I suggest that you go to these links and make yourself familiar with the CSS format.

• HTML-CSS
• Begin CSS here
• Learn about selectors

1. Download the .zip files on ICON. It has two files viz., css_sheet.css and lab-3-start.html. As you already know, lab-3-start.html has the content of the page and css_sheet.css has the styles using which the html's content will be displayed.
2. Modify the files, to obtain the following styles:

• Use rounded corners and borders on all of the blocking elements. (Rounded border)
• Use 2 different linear gradients as background and change background for every blocking element. (Gradients)
• 3 different hover links. Links should change color when you hover. (Hover)
• Use text shadows on at least 2 pieces of text. (TextShadowGenerator, Text Shadow)
• Change the title (inside the title tag) to your name, change most of the original text.
• Use different colors for a good design. (Colors)
• Transfer the page to MyWeb using Filezilla.
• Extra: (Not mandatory) Insert a video inside a new article on the page. The video should play on the “OnMouseover” event, stop on the “onmouseout” event and resize when the page re-sizes. 

Start CSS here..

To begin writing a CSS, follow the same way as you created a HTML file (Click here). In the end, 'Save as.." , '.css' instead of '.html' .

CSS Syntax

• The selector points to the HTML element you want to style.
• The declaration block contains one or more declarations separated by semicolons.
• Each declaration includes a property name and a value, separated by a colon.

Example

myStyle.css

body {
    background-color: lightblue;
}

p {  color: red;  text-align: center;  }

Now let's try to use this with HTML file. The text in bold, tells you how to link a CSS file inside HTML.

myHTML.html

<html>

<head>

 <link rel="stylesheet" type="text/css" href="myStyle.css">

</head>

<body>

 <h1>This is a heading</h1>

 <p>This is a paragraph using CSS style.</p>

</body>

</html>

Now, if you want to modify the tag <p>'s color to 'green', then you can directly go to 'myStyle.css' file and change, ' color: green; '. Try it yourself and see the difference.. Can you see the advantages of CSS now? 

• Modifying styles is easy. Go to .css file directly and change it there!
• Re-usable: Use the same CSS styles for different HTML file.
• Not very complex to learn.
• Fun to play with styles :)

Once you're familiar with the format, go to this link and start learning about changing background, text, fonts etc. It'll be fun! :)

HTML and CSS

Okay! Let's define the HTML- CSS relationship. A simple HTML file looks like this. 

What if we wanna add some styles and make it goofy? Like this?

Or, scary (Halloween-ish)?

And, we'd like to give different styles to the same HTML file? Or perhaps, same style to different HTML files? That's when we need CSS! Let's define CSS now.. 

• Cascading Style Sheets (CSS) is a style sheet language.

• Styles define how to display HTML elements.

• Styles are normally saved in external (separate) .css files. 

• External style sheets enable you to change the appearance and layout of all the pages in a Web site, just by editing one single file!