Create Stored Procedure
CREATE
PROCEDURE usp_AddCustomer
@CustomerID INT,
@FirstName VARCHAR(30),
@LastName VARCHAR(30),
@Phone VARCHAR(20),
@Email VARCHAR(30)
AS
INSERT
INTO CUSTOMER(CustomerID,FirstName,LastName,Phone,Email)
VALUES
(@CustomerID, @FirstName, @LastName, @Phone,@Email);
EXEC usp_AddCustomer
'8',
'John','Smith','723-555-3432','John@somewhere.com';
SELECT
*
FROM CUSTOMER
WHERE CustomerID=8;
Create Trigger part1
CREATE
TRIGGER reminder
ON CUSTOMER
AFTER
INSERT
AS
RAISERROR ('Notify new Customer inserted', 16, 10);
INSERT
INTO CUSTOMER
VALUES
(8,'Mick',
'Jager','723-599-1111','MJager@somewhere.com');
Create Trigger part2
CREATE
TRIGGER SetOrederItem1
ON CLOTH_ITEM
AFTER
INSERT
AS
DECLARE
@OrderID INT,
@ItemNo INT,
@Item VARCHAR(30),
@Quantity INT,
@UnitPrice MONEY,
@S_ItemNo INT,
@S_UnitPrice MONEY
SELECT @ItemNo=ItemNo, @UnitPrice=UnitPrice
FROM inserted i
SELECT @S_ItemNo=S.ItemNo,@S_UnitPrice=S.UnitPrice
FROM
SERVICE S
WHERE S.ItemNo=@ItemNo
IF (@ItemNo=@S_ItemNo)AND(@UnitPrice<>@S_UnitPrice)
BEGIN
RAISERROR ('Price is different from SERVICE table', 16, 1)
ROLLBACK
TRANSACTION
END
ELSE
BEGIN
PRINT'The modification excuted'
END;
INSERT
INTO CLOTH_ITEM
VALUES
(2009006,6,'Suit-Mens',10,'$6.00');
INSERT
INTO CLOTH_ITEM
VALUES
(2009008,6,'Suit-Mens',10,'$9.00');
Create View
CREATE
VIEW OrderSummaryView
AS
SELECT OrderID,DateIn,DateOut,TotalAmount
FROM ORDER_RECORD;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
namespace ConsoleApplication2
{
//Drow picture use ArrayList in ArrayList
class
Program
{
static
void Main(string[] args)
{
//Initialize parent arraylist
ArrayList al = new
ArrayList();
//Initialize child arraylist
ArrayList childal1 = new
ArrayList();
childal1.Add(" * ");
//Initialize child arraylist
ArrayList childal2 = new
ArrayList();
childal2.Add(" *** ");
//Initialize child arraylist
ArrayList childal3 = new
ArrayList();
childal3.Add(" ***** ");
//Initialize child arraylist
ArrayList childal4 = new
ArrayList();
childal4.Add("*******");
//Initialize child arraylist
ArrayList childal5 = new
ArrayList();
childal5.Add(" ***** ");
//Initialize child arraylist
ArrayList childal6 = new
ArrayList();
childal6.Add(" *** ");
//Initialize child arraylist
ArrayList childal7 = new
ArrayList();
childal7.Add(" * ");
//Put in child array into parent array
al.Add(childal1);
al.Add(childal2);
al.Add(childal3);
al.Add(childal4);
al.Add(childal5);
al.Add(childal6);
al.Add(childal7);
//Display ArrayList data
foreach (ArrayList array in al)
{
//Initialize IEnumerator
IEnumerator enm = array.GetEnumerator();
//Get data using IEnumerator
while (enm.MoveNext())
{
Console.WriteLine(enm.Current.ToString());
}
}
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.IO;
namespace ConsoleApplication5
{
//Sorting programming
//I create 3 classes Product, Sorting and Program.
//Product class has ID, name, price, taxrate, tax and total price.
//Soting class has compare method.
//Program class has user to input product, create their objects and sort by total price.
//Also, this result stores txt file.
class
Program
{
static
void Main(string[] args)
{
//Create ArrayList
ArrayList al = new
ArrayList();
//Input TaxRate
Console.WriteLine("Please input TaxRate.");
//Read TaxRate
string input = Console.ReadLine();
//Convert double
double taxrate = double.Parse(input);
//Set TaxRate value
Product.SetTaxRate(taxrate);
//Declare variable for do while loop
int i = 0;
//Start do while loop
do
{
//Input ID
Console.WriteLine("Please input product ID.");
//Read ID
string input1 = Console.ReadLine();
//Convert integer
int id = int.Parse(input1);
//Input Name
Console.WriteLine("Please input product Name.");
//Read Name
string name = Console.ReadLine();
//Input price
Console.WriteLine("Please input product Price.");
//Read price
string input2 = Console.ReadLine();
//Convert double
double price = double.Parse(input2);
//Create object
Product pr= new Product(id, name, price);
al.Add(pr);
//Ask to input another product
Console.WriteLine("Do you want to input another prodyct? yes-1 no-0");
//Read answer
string input3 = Console.ReadLine();
//Convert integer
i = int.Parse(input3);
//End of do while loop
}
while (i == 1);
//Display current Array order before sorting
foreach (Product pr in al)
{
//Set tax value
pr.SetTax();
//Set total price value
pr.SetTotalPrice();
//Display current value
Console.WriteLine("ID: "+pr.GetID() + "\nName: " + pr.GetName() + "\nPrice: " + pr.GetPrice() + "\nTax: " + pr.GetTax() + "\nTotalPrice: " + pr.GetTotalPrice()+"\n");
}
//Excute sorting
al.Sort(new Sorting());
try
{
//Initialize filestreem
FileStream fs = new
FileStream
(
//Set file access
@"E:\VirtualExpander_J\C#\Fileaccess\exampractice.txt",
FileMode.Create,
FileAccess.ReadWrite,
FileShare.Read
);
//Initialize stream writer
StreamWriter sw = new
StreamWriter(fs);
//Display current Array order after sorting
foreach (Product pr1 in al)
{
//Display current value
Console.WriteLine("ID: " + pr1.GetID() + "\nName: " + pr1.GetName() + "\nPrice: " + pr1.GetPrice() + "\nTax: " + pr1.GetTax() + "\nTotalPrice: " + pr1.GetTotalPrice() + "\n");
//Write current value
sw.WriteLine("ID: " + pr1.GetID() + "Name: " + pr1.GetName() + "Price: " + pr1.GetPrice() + "Tax: " + pr1.GetTax() + "TotalPrice: " + pr1.GetTotalPrice() + "\n");
}
//Close stream writer
sw.Close();
//Close file streem
fs.Close();
}
catch
{
Console.WriteLine("Try block error");
}
}
}
//Product template
class
Product
{
//Declare variables
int ID;
string Name;
double Price;
static
double TaxRate;
double Tax;
double TotalPrice;
//Create method return ID
public
int GetID()
{
return ID;
}
//Create method return Name
public
string GetName()
{
return Name;
}
//Create method return Price
public
double GetPrice()
{
return Price;
}
//Create static method set TaxRate
public
static
void SetTaxRate(double d)
{
TaxRate = d;
}
//Create ststic method return TaxRate
public
static
double GetTaxRate()
{
return TaxRate;
}
//Create method calculate Tax
public
void SetTax()
{
Tax = Price * TaxRate;
}
//Create method return Tax
public
double GetTax()
{
return Tax;
}
//Create method calculate TotalPrice
public
void SetTotalPrice()
{
TotalPrice = Price + Tax;
}
//Create method return TotalPrice
public
double GetTotalPrice()
{
return TotalPrice;
}
//Constructor of Product class
public Product(int id, string name, double price)
{
ID = id;
Name = name;
Price = price;
}
//End of product class
}
//Sorting
class
Sorting : IComparer
{
//Compare method
public
int Compare(object a, object b)
{
//Declare return value
int value = 0;
//Cast object a and b
Product prod = (Product)a;
Product prod1 = (Product)b;
//Get TotalPrice value
double comprice = prod.GetTotalPrice();
double comprice1 = prod1.GetTotalPrice();
//Compare each case
if (comprice > comprice1)
{
value = 10;
}
if (comprice == comprice1)
{
value = 0;
}
if (comprice < comprice1)
{
value = -10;
}
//return result
return value;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication4
{
//Random number
//Someone select number and bet money.
//If this number much the random number, he will play again and select number again.
//If not, he will lose money.
//And programming ask him whether he wants to play again or not.
class
Program
{
static
void Main(string[] args)
{
//Initialize Random
Random rd = new
Random();
//Declare variable for while roop
int h=0;
//He plays at least one time. After playing, he chose to play again or not.
do
{
//Ask him to select number
Console.WriteLine("Pay $5.00 and select numbaer from 1-6.");
//Generate random number
int rd1 = 1 + rd.Next(6);
//Display random number for Debug
Console.WriteLine("Random number " + rd1);
//Read input data
string input1=Console.ReadLine();
//Convert from string to interger
int i=int.Parse(input1);
//Check to mutch input number and random number
if (i == rd1)
{
//Display congraturations and ask 2nd number
Console.WriteLine("Congraturations!! First much!");
Console.WriteLine("Please select 2nd number from 1-6.");
//Generate 2nd random numbaer
int rd2 = 1 + rd.Next(6);
//Display 2nd random number for debug
Console.WriteLine("2nd rndom number " + rd2);
//Read input 2nd data
string input2 = Console.ReadLine();
//Convert from string to interger
int k = int.Parse(input2);
//Check to much 2nd input number and 2nd random number
if (k == rd2)
{
//Display congraturations
Console.WriteLine("Congraturations!! Second much!!");
Console.WriteLine("You win this game and get $1,000.00!!");
break;
}
else
{
//Display his loose
Console.WriteLine("Sorry you lose this game.");
}
}
else
{
//Display his loose
Console.WriteLine("Sorry you lose this game.");
}
//Ask him to play again or not
Console.WriteLine("Do you want to play again? Yes-1 No-0");
//Read input data
string input3 = Console.ReadLine();
//Convert from string to integer
h = int.Parse(input3);
//End of do while loop
}while(h==1);
//Display good bye
Console.WriteLine("Good bye! See you!!");
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication3
{
//Roll die programming
//This example is that someone throws a roll die 500 times.
//And we count how many times each face is shown.
class
Program
{
static
void Main(string[] args)
{
//Initialize Random generator
Random rd = new
Random();
//Initialize variables for each face
int count1=0;
int count2=0;
int count3=0;
int count4=0;
int count5=0;
int count6=0;
//A roll die is thrown 500 times and count each face
for (int i = 0; i <500; i++)
{
//Create random number
int x = 1+rd.Next(6);
//Use swich to count each face
switch (x)
{
//Add the count of face 1
case 1:
count1++;
break;
//Add the count of face 2
case 2:
count2++;
break;
//Add the count of face 3
case 3:
count3++;
break;
//Add the count of face 4
case 4:
count4++;
break;
//Add the count of face 5
case 5:
count5++;
break;
//Add the count of face 6
case 6:
count6++;
break;
}
}
//Show total counts in each face
Console.WriteLine("face 1 is " + count1);
Console.WriteLine("face 2 is " + count2);
Console.WriteLine("face 3 is " + count3);
Console.WriteLine("face 4 is " + count4);
Console.WriteLine("face 5 is " + count5);
Console.WriteLine("face 6 is " + count6);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication1
{
//Exam Practice1 Bug fix
class
Program
{
static
void Main(string[] args)
{
//*Bug* Change to create parent array
//tomykid[] a = new tomykid[3];
animal[] a = new
animal[3];
dog d = new
dog("tomy");
tomy t = new
tomy("tomy", "white");
tomykid tk = new
tomykid("tomykid","black");
//*Bug* Change to be assigned children objects to parent array
//a[0] = (tomykid)d;
//a[1] = (tomykid)t;
//a[2] = tk;
a[0] = (animal)d;
a[1] = (animal)t;
a[2] = (animal)tk;
//*Bug* Change from for to foreach
//for(tomykid k:a)
//{
//Console.WriteLine(k.size()+k.bark());
//}
foreach (animal ani in a)
{
ani.size();
ani.bark();
}
}
}
public
interface
animal
{
//*Bug* Remove interface cannot contain fields
//int i = 10;
void bark();
//*Bug* Add method size()
void size();
}
public
class
dog : animal
{
private
string name;
//*Bug* Add virtual
virtual
public
void bark()
{
Console.WriteLine("dog barks");
}
//*Bug* Add virtual
virtual
public
void size()
{
Console.WriteLine("size unknown");
}
public dog(string i)
{
name = i;
}
}
public
class
tomy : dog
{
string color;
//*Bug* Add override
override
public
void bark()
{
Console.WriteLine("Tomy barks");
}
//*Bug* Add override
override
public
void size()
{
Console.WriteLine("small size");
}
public tomy(string name, string color)
: base(name)
{
this.color = color;
}
}
public
class
tomykid : tomy
{
string haircolor;
//*Bug* Add override
override
public
void bark()
{
Console.WriteLine("tomykid barks");
}
//*Bug* Add override
override
public
void size()
{
Console.WriteLine("medium size");
}
//*Bug* Add :base(color)
public tomykid(string name,string color)
: base(name,color)
{
haircolor = color;
}
}
}
12/04/2010
Hi there,
I back again this class for theoretical exam:)
Concurrency Problems
1. Concurrency problems
If locking is not available and several users access a database concurrently, problems may occur
If their transactions use the same data at the same time.
Lost updates.
Uncommitted dependency (dirty read).
Inconsistent analysis (non-repeatable read).
Phantom reads.
http://msdn.microsoft.com/en-us/library/aa213029%28SQL.80%29.aspx
2. Lost update
Successfully completed update is overridden by another user.
3. Uncommitted dependency (dirty read).
Occurs when one transaction can see intermediate results of another transaction before it has committed.
4. Inconsistent analysis (non-repeatable read).
Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time.
Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading.
However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change.
5. Inconsistent analysis (non-repeatable read) example
6. Phantom Reads
Records that appear in a set being read by another transaction.
Phantom reads can occur when other transactions insert rows that would satisfy the WHERE clause of another transaction's statement.
http://db.apache.org/derby/docs/10.0/manuals/develop/develop71.html
7. Phantom Reads example
8. Back to Marcia's Drycleaning
Stored Procedure 01
Insert a new record in SERVICE table
Stored Procedure 02
Insert a new record in ORDER and ORDER_ITEM table
Also select data from CUSTOMER and SERVICE
Stored Procedure 03
Insert a new record in CUSTOMER table
Concurrency problems may occur between 01 and 02, between 02 and 03.
9. Dirty read SP01(SP03)and SP02
SP02 trusts SERVICE table data, but it has not been committed yet.
10. Non-repeatable reads SP02 and User Operation
Non-repeatable reads may occur between SP02 and other user operations.
10. Phantom read SP01(SP03) and SP02
SP02 trusts SERVICE table data, but it has not been committed yet.
11. Appropriate concurrency control
SP01 INSERT SERVICE
Read Committed
SP03 INSERT CUSTOMER
Read Committed
SP02 INSERT ORDER and SELECT All table
Serializable
I saw the ASP.NET Video, which is very interesting. This learning site has very useful information, and what's more Schott is a very handy person.
While he writes programming code, he explains how to use ASP.NET!!
He is awesomeL
If you are interested in APS.NET, please come and see this video.
http://www.asp.net/learn/videos/video-9641.aspx
This list is skills, which are demanded by IT companies from Seek.co.nz. This research consists of web application developer, software engineer, network engineer and my skills, because I am interested in these jobs. Each job was researched 10 job offers, so there were 30 jobs offers in all.
I researched this sheet in January 2010 due to assignment, so there may be something changed.
リクエストにお答えしてNZで需要のあるスキルリストをアップします。宿題で2010/1にやったので最新ではないです。
こ のリストはウェブアプリディベロッパーとソフトウェアエンジニアとネットワークエンジニアそれぞれ10社がどのようなスキルを求めているのかチェックして ます。若干Must haveとNice to haveが混在しているので、正確な情報が知りたい場合は自分で調査してください。（あくまでも宿題用なので。。。。）
元の調査データが見たい方はこちらをどうぞ。
http://spreadsheets.google.com/ccc?key=0AsgU4bWkOtLvdHVTNnNpdFRaVEhXR0tHeDhfS3hfaWc&hl=en
Web Application Developer | Software Engineer | Network Engineer | |||
1.Skills | |||||
HTML | ○ | ○ | |||
DHTML | ○ | ||||
CSS | ○ | ||||
W3C Standard | ○ | ||||
JavaScript | ○ | ○ | |||
Flash | ○ | ||||
PHP | ○ | ○ | |||
XML | ○ | ○ | |||
Apache | ○ | ○ | |||
Assembler | ○ | ||||
C | ○ | ||||
C++ | ○ | ||||
C# | ○ | ○ | |||
Java | ○ | ○ | |||
ASP.NET | ○ | ○ | |||
.NET | ○ | ||||
MySQL | ○ | ○ | |||
SQL | ○ | ○ | ○ | ||
Oracle | ○ | ○ | |||
Windows Server | ○ | ||||
IBM HTTP server | ○ | ||||
SQL Server | ○ | ○ | |||
Red Hat Server | ○ | ||||
mail server | ○ | ||||
VMware ESX Servers | ○ | ||||
Cisco | ○ | ||||
Linux | ○ | ○ | ○ | ||
Solaris | ○ | ||||
Unix | ○ | ○ | |||
CentOS | ○ | ||||
GNU | ○ | ||||
BASH | ○ | ||||
network | ○ | ○ | |||
Network security | ○ | ||||
WAN protocols | ○ | ||||
Backups | ○ | ||||
Routers | ○ | ||||
Switches | ○ | ||||
Wireless | ○ | ||||
User/Desktop Support | ○ | ||||
2.Personality | |||||
Strong communication skills | ○ | ○ | ○ | ||
Sense of humor | ○ | ||||
High level attention | ○ | ○ | ○ | ||
Proven problem solver | ○ | ○ | ○ | ||
Strong interpersonal skills | ○ | ○ | ○ | ||
Work autonomously | ○ | ○ | ○ | ||
Deliver projects on time | ○ | ○ | ○ | ||
Relevant tertiary qualification | ○ | ○ | ○ | ||
Introduce new & advanced technology | ○ | ○ | ○ | ||
3.Experience | |||||
Experience with Design | ○ | ○ | |||
Experience with Embedded Systems Development | ○ | ||||
Experience with Data Management | ○ | ||||
Development experience | ○ | ○ | |||
Experience working in a IT environment | ○ | ○ | ○ | ||
Experience with Networking | ○ | ||||
