SQl 1

Use Bakery Select * from Person; Select PersonLastname, PersonFirstname, PersonEmail From Person; Select PersonLastname, PersonFirstname, PersonEmail From Person Where PersonEmail is not null Order by PersonLastName;

Here is the SQL we did in class

Select PersonLastName, PersonFirstname, PersonEmail
From Person
Where PersonFirstName = 'Tabitha';

Select PersonLastName, PersonFirstname, PersonEmail
From Person
Where PersonFirstName Like 'T%';

Select * from Product

Select * from Product where productprice > 3
Select * from Product where productprice = 3
Select * from Product where productprice >= 3
Select * from Product where productprice between 2.5 and 3.5
Select * from Product where productprice >= 2.5 
and ProductPrice <= 3.5

Select * from Sale where SaleDate >= '10/31/2016'
Select * from saleDetail

Select Sale.SaleKey,SaleDate,PersonLastName, Employeekey, ProductName, 
SaleDetailPriceCharged, SaleDetailQuantity,SaleDetailDiscount,
SaleDetailSaleTaxPercent,SaleDetailEatInTax
From Person
inner join Sale
on Person.PersonKey=Sale.CustomerKey
inner Join SaleDetail
on Sale.SaleKey=SaleDetail.SaleKey
inner Join Product
on Product.ProductKey=SaleDetail.ProductKey
Where Sale.SaleKey=1

Select * from Product

Insert into Sale (SaleDate, CustomerKey, EmployeeKey)
Values(GetDate(),5,2)
Insert into SaleDetail(SaleKey, 
ProductKey, SaleDetailPriceCharged, 
SaleDetailQuantity, SaleDetailDiscount, 
SaleDetailSaleTaxPercent, 
SaleDetailEatInTax)
Values(ident_current('Sale'),3,2.25,1,0.0,0.09,0),
(ident_current('Sale'),6,1.25,1,0.0,0.9,0)

Select * from SaleDetail 

Select * from Person

Begin tran

Update Person Set PersonLastName='Smith'
Where PersonKey = 2

rollback tran

Commit tran

Arrays

Here are the first examples

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ArrayExamples
{
    class Program
    {
        /* overview of arrays*/

        static void Main(string[] args)
        {
            //manually laying out an array
            //Each element has an index number
            //indexes always start with 0
            int[] myArray = new int[5];
            myArray[0] = 1;
            myArray[1] = 3;
            myArray[2] = 14;
            myArray[3] = 12;
            myArray[4] = 8;

            //you can access an array element by its index
            Console.WriteLine("The third element is {0}", myArray[2]);

            int sum = 0;
            //calculating the sum
            for(int i =0; i<myArray.Length;i++)
            {
                Console.WriteLine(myArray[i]);
                sum += myArray[i]; //sum=sum + myArray[i]
            }

            Console.WriteLine("the sum is {0}", sum);
            Console.WriteLine("The average is {0}", (double)sum / myArray.Length);

            //getting the max
            int max = 0;
            for (int i = 0; i < myArray.Length; i++)
            {
                if (myArray[i] > max)
                {
                    max = myArray[i];
                }
            }

            Console.WriteLine("The maximum value is {0}", max);

            //alternate, easier way. C# contains methods
            //for sum, average, minimum and maximum
            Console.WriteLine(myArray.Sum());
            Console.WriteLine(myArray.Average());
            Console.WriteLine(myArray.Max());
            Console.WriteLine(myArray.Min());

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

the second set of examples

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace arrayExamples2
{
    class Program
    {
        /*more array examples */
        static void Main(string[] args)
        {
            //one way to initialize an array
            //string[] teams = new string[5];
            //another way to initialize an array
            string[] teams = { "Seahawks", "Cardinals", "Rams", "Fourty Niners" };

            //loop through the array and output its content
            for(int i = 0; i < teams.Length; i++)
            {
                Console.WriteLine(teams[i]);
            }

            Console.WriteLine();

            //sort the array elements
            Array.Sort(teams);

            for (int i = 0; i  < teams.Length; i++)
            {
                Console.WriteLine(teams[i]);
            }

            //declare but not iniialize an array
            string[] foods;

            
            Console.WriteLine("how many foods do you want to list?");
            int number = int.Parse(Console.ReadLine());

            //initialize an array in
            foods = new string[number];

            for (int i=0;i <foods.Length;i++)
            {
                //add elements to the array dynamically
                Console.WriteLine("Add a food");
                foods[i] = Console.ReadLine();
            }

            // a different kind of loop: it loops
            //through all the objects in a collection
            //of objects in this case strings
            foreach(string s in foods)
            {
                Console.WriteLine(s);
            }

            //a two dimensional array
            string[,] Books = new string[3, 2];
            Books[0, 0] = "Lord of the rings";
            Books[0, 1] = "J.R.R Tolkein";
            Books[1, 0] = "Ulysses";
            Books[1, 1] = "James Joyce";
            Books[2, 0] = "Gravity's Rainbow";
            Books[2, 1] = "Thomas Pinchon";

            Console.WriteLine("Enter a title");
            string title = Console.ReadLine();

            for (int i = 0; i  < 3; i++)
            {
                if (title.Equals(Books[i, 0]))
                {
                    Console.WriteLine(Books[i, 1]);
                }
               
            }


            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

peer excercise

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace peerArray
{
    class Program
    {
        static void Main(string[] args)
        {
            int[] myArray = new int[20];
            Random rand = new Random();

            for(int i = 0;i<20;i++)
            {
                myArray[i] = rand.Next(1, 101);
                Console.WriteLine(myArray[i]);
            }

            Console.WriteLine();
            Console.WriteLine("the Max is {0}", myArray.Max());
            Console.WriteLine("the Min is {0}", myArray.Min());

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

SQL For creating databases and tables

Create Database Books2

use Books2

Create table book
(
  BookKey int identity(1,1) primary key,
  BookTitle nvarchar(255) not null,
  BookYear int null,
  BookPrice money not null
);

Go

Create table Author
(
   AuthorKey int identity(1,1) primary key,
   AuthorName nvarchar(255) not null
);

Go
Create table AuthorBook
(
  AuthorKey int not null,
  BookKey int not null,
  Constraint pk_AuthorBook primary key (AuthorKey, Bookkey),
  Constraint fk_Author foreign key (AuthorKey) references Author (authorKey),
  Constraint fk_Book foreign key (BookKey) references Book (BookKey)
 );

 Insert into Book(BookTitle, BookYear, BookPrice)
 Values('Charlotte''s Web',1952,7.5),
 ('American Gods',1999,8.50),
 ('The once and Future King',1950, 3.5)

 Insert into Author(AuthorName)
 values('E.B.White'),
 ('Neil Gaiman')

 Insert into AuthorBook(AuthorKey, bookKey)
 Values(1,1),
 (2,2),
 (2,3)

 Select * from Book
 Select * from Author
 Select * from AuthorBook

Loops

For loops

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ForLoopExcercise
{

    class Program
    {
        /*This program will explore
         * repetition with for loops
         * for loops are best for counted loops
         * where you know how many loops 
         * you want to do.
         *Steve Conger 10/18/2016
         */


        static void Main(string[] args)
        {
            int number = 0;
            long number2 = 0;
            Console.WriteLine("How many times do you want to loop?");
            number = int.Parse(Console.ReadLine());
            Console.WriteLine("Enter the number to get exponents");
            number2 = long.Parse(Console.ReadLine());
            Console.WriteLine();

            for (int i = 1; i <= number; i++)
            {
                number2 *= number2; // number=number * number
                Console.WriteLine(number2);
            }

            Console.WriteLine();
            for(int i=0;i<=10;i++)
            {
                Console.WriteLine(i);
                  
            }

            for (int i = 0; i <= 10; i+=2)
            {
                Console.WriteLine(i);
            }

            //infint loop
            //for (int i = 1; i > 0; i++)
            //{
            //    Console.WriteLine(i);
            //}

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }
    }
}

While Loops

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WhileLoopExample
{
    class Program
    {
        /*
         * This program shows while loops
         * the first provides a menu of 
         * opens and loops until the user 
         * chooses 4 for exit.
         * Within the loop is another loop
         * that loops until the user enters
         * a valid integer menu choice
         */
        static void Main(string[] args)
        {
            int choice = 1;
            //loop until they choose 4 to exit
            while (choice != 4)
            {
                Console.WriteLine("Hello:\t\t1");
                Console.WriteLine("Hello:\t\t2");
                Console.WriteLine("Hello3:\t\t3");
                Console.WriteLine("Goodbye:\t4");


                bool goodChoice = false;

                //loop until they enter a valid integer
                while (!goodChoice)
                {
                    Console.WriteLine("Enter your choice");
                    goodChoice = int.TryParse(Console.ReadLine(), out choice);

                    if(!goodChoice)
                    {
                        Console.WriteLine("Enter an integer 1 to 4");
                    }
                    
                }

                switch(choice)
                {
                    case 1:
                        Console.WriteLine("Hello One");
                        break;
                    case 2:
                        Console.WriteLine("Hello two");
                        break;
                    case 3:
                        Console.WriteLine("Hello three");
                        break;
                    case 4:
                        Console.WriteLine("Goodbye");       
                        break;
                    default:
                        Console.WriteLine("Invalid choice");
                        break;
                }//end switch
            
            }//end loop

            /*
             * do loops do the same thing as
             * while loops with one difference
             * they test the condition at the 
             * end of the loop. That means that
             * they always run at least once.
             * whereas a while loop may never 
             * run if the initial condition is false
             */
            string continues= "yes";
            do
            {
                Console.WriteLine("continue yes/no");
                continues = Console.ReadLine();
                continues = continues.ToLower();
            }while(continues.Equals("yes"));

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }//end main
    }//end class
}//end namespace

Afternoon version of Student Normalization

Normalization first Take

First normal form:

There should be no repeated fields, and no arrays. All the entries in an attribute should be of the same kind. Break out any repetitions, give everything a key

2nd Normal form:

You should remove all functional dependencies: (Sub Themes, groups of attributes that relate to each other but not to the topic (key) of the entity

3rd Normal form

Is about removing transient dependencies--attributes that modify or describe another attribute and not the key

Here is the student entity not normalized

Here is the student ERD after normalization

Entity Relational Design

An entity is depicted as a box. The top is the title or name of the entity. Next is a primary key, a separator, and then the attributes that describe the entity. Bold font indicates that an attribute is required.

There are three types of relationships between entities

One-to-Many, which is the normal relationship among entities and will account for 95% of all relationships. Here is one Department has many employees.

The one side always points to the primary key, the crow's foot (the three point prong) always points to the many side

One-to-One. This is quite rare but is legal. It says for every record in one table there is exactly one matching record in the second table. One example of this is when a table is split up for security reasons. For instance, the public information about an employee could be in one table and the private in another.

The third relationship is Many-to-Many. This is legal in design, but no database can process it. Whenever you have a many to many relationship, you must resolve it into two one-to-many relationships by creating a linking table. For instance, assume an employee can be in more than one department. That means that each employee can be in many departments and each department contains many employees. A many to many relationship. To resolve this we create a linking table, here called EmployeeDepartment.

Here is the coffee shop ERD we did in class

Selection Statements

Here is the code for our selection examples

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SelectionExamples
{
    class Program
    {
        /*This program provides examples of
         * if statements and switches
         * Steve Conger 10/11/2016*/

        static void Main(string[] args)
        {
            int number;
            Console.WriteLine("Enter number");
            //try parse returns a boolean (true/false)
            //if the number is good it assigns it to the variable
            //outside the method (out)

            bool goodNumber = int.TryParse(Console.ReadLine(),out number);

            if(goodNumber == false)
            {
                Console.WriteLine("restart and enter a valid integer");
                Console.WriteLine("Press any key to exit");
                Console.ReadKey();
                return; //ends the program
            }

            // >, <, >=, <=, ==, != (not equal)
            //with strings it is often best to use .Equals
            if (number > 10)
            {
                Console.WriteLine("the number is greater than 10");
            }
            else if(number==10)
            {
                Console.WriteLine("the number is 10");
            }
            else
            {
                Console.WriteLine("The number is less than 10");
            }

            Console.WriteLine("Enter the name of the current month");
            string month = Console.ReadLine();
            // || or , && and

            if(month.Equals("October") || month.Equals("october"))
            {
                Console.WriteLine("Happy Halloween");

            }
            else
            {
                Console.WriteLine("You are a bit confused");
            }


            //switch statement
            int choice;
            Console.WriteLine("Enter an integer between 1 and 4");
            bool good = int.TryParse(Console.ReadLine(), out choice);
            //!good same as good !== true or good == false
            if(!good)
            {
                return;
            }

            //switch statement
            switch(choice)
            {
                case 1:
                    Console.WriteLine("You entered 1");
                    break;
                case 2: //fall through to three
                case 3:
                    Console.WriteLine("You chose 2 or 3");
                    break;
                case 4:
                    Console.WriteLine("You entered 4");
                    break;
                default://captures anything else
                    Console.WriteLine("Not a valid choice");
                    break;
            }

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();


            
        }
    }
}

here is the code for the birthday program

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Birthdate
{
    class Program
    {
        static void Main(string[] args)
        {
            DateTime birthday;
            DateTime today = DateTime.Now;
            int age;
            
            
            
            //you can try parse for DateTime as well
            Console.WriteLine("enter your birth date");
            bool goodDate = DateTime.TryParse(Console.ReadLine(), out birthday);

            if (!goodDate)
            {
                Console.WriteLine("enter a valid birth date");
                bool good = DateTime.TryParse(Console.ReadLine(), out birthday);
                //nested if statement
                if(!good) //give a second chance
                {
                    Console.WriteLine("Sorry still not valid");
                    Console.WriteLine("Press any key to exit");
                    Console.ReadKey();
                    return;
                }//end inner if

            }//end outer if
            //just subtract years. We could also do months and days
            age = today.Year - birthday.Year;
            Console.WriteLine("You are {0} years old", age);

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();
        }//end main
    }//end program class
}//end namespace

Here is a solution for the peer excercise

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PeerIf
{
    class Program
    {
        static void Main(string[] args)
        {
            string password = "P@ssw0rd1";
            string username = "customer";
            string user;
            string pass;

            Console.WriteLine("Enter your user name");
            user = Console.ReadLine();

            Console.WriteLine("Enter your password");
            pass = Console.ReadLine();

            if(password.Equals(pass) && username.Equals(user))
            {
                Console.WriteLine("Welcome user");
            }
            else
            {
                Console.WriteLine("invalid password or user name");
            }

            /*an alternative way
             * if(username.Equals(user)
             * {
             *     if(password.Equals(pass)
             *     {
             *         Console.WriteLine("Welcome");
             *     }
             *     else
             *     {
             *          Console.WriteLIne("Inavalid");
             *     }

             * }
             * */

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();





        }
    }
}

Using Integers and doubles

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NumberExamples
{
    class Program
    {
        /// 

        /// This program shows examples of integers
        /// and doubles with math operators
        /// Steve Conger 10/6/2016
        /// 
        /// 
        static void Main(string[] args)
        {
            //integers and doubles
            //operators + - * / %
            //decaring number variables
            string name;
            int number;
            int number2;
            int answer;
            double answer2;
            double number3;

            //getting values from the user
            //anything entered on the console is a string
            //it must be converted into the appropriate
            //type of number
            Console.WriteLine("Enter the first Integer");
            number = int.Parse(Console.ReadLine());

            Console.WriteLine("Enter the second Integer");
            number2 = int.Parse(Console.ReadLine());

            //this takes a console entry and converts into a double 
            //(with decimal places)
            Console.WriteLine("Enter a meal amount");
            number3 = double.Parse(Console.ReadLine());

            

            answer = number + number2;

            Console.WriteLine("The sum of {0} + {1} = {2}", number, number2, answer);
            answer = number2 / number;

            Console.WriteLine("The quotient of {0} / {1} = {2}", number2, number, answer);

            answer = number2 % number;
            Console.WriteLine("The remainder of {0} / {1} = {2}", number2, number, answer);

            //this casts the integer number2 into a double. it allows the division
            //to return decimal places
             answer2= (double)number2 / number;

            //Math is a built in library of math functions. 
            //Unlike the format codes this actually changes
            //the underlying number
            answer2 = Math.Round(answer2, 2);
           
            //{0:F@} would format the number to only show 2 decimal places
            //but it does not change the underlying numbers
            //c formats the number to look like currency
            Console.WriteLine("The double quotient of {0} / {1} = {2}", number2, number, answer2);
            Console.WriteLine("The double quotient of {0} / {1} = {2:C}", number2, number, answer2);

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();



        }//end main
    }//end program
}//end namespace

First assignment Code

Here is the first part of assignment 1.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Assignment1_1
{
    /*This program will take in a user's
    name and email and display it 
    last name, first name -- email
    Steve Conger, 10/4/2016
    */

    class Program
    {
        //starting point of the program
        static void Main(string[] args)
        {
            string firstName;
            string lastName;
            string email;

            Console.WriteLine("Enter your first name");
            firstName = Console.ReadLine();

            Console.WriteLine("Enter your last name");
            lastName = Console.ReadLine();

            Console.WriteLine("Enter your email");
            email = Console.ReadLine();

            Console.WriteLine("{0}, {1}--{2}", lastName, firstName, email);
            Console.WriteLine(lastName + ", " + firstName + "--" + email);

            Console.WriteLine("Press any key to exit");
            Console.ReadKey();


        }//end of Main
    }//end of class program
}//end of namespace

Here is the second part of assignment one

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Assignment1_2
{
    class Program
    {
        /*This program produces a
         * mailing label
         * steve conger 10/4/2016
         * */
        static void Main(string[] args)
        {
            Console.WriteLine("Enter your full name");
            string fullName = Console.ReadLine();

            Console.WriteLine("Enter your street address");
            string address = Console.ReadLine();

            Console.WriteLine("Enter your City");
            string city = Console.ReadLine();

            Console.WriteLine("Enter your State");
            string state = Console.ReadLine();

            Console.WriteLine("Enter your zip code");
            string zipcode = Console.ReadLine();

            Console.WriteLine();

            Console.WriteLine(fullName);
            Console.WriteLine(address);
            Console.WriteLine("{0}, {1} {2}",city, state, zipcode);

            Console.WriteLine();

            //an alternate way with line break excape characters (\n)
            Console.WriteLine(fullName+"\n" + address + "\n" + city + ", " + state + " " + zipcode);

            Console.WriteLine();

             Console.WriteLine("Press any key to exit");
            Console.ReadKey();

        }
    }
}