Example 1.0 complete. Next page will be used to test your abilities with data manipulation.

16. Write  SQL statement to create  variable called Variable1 that can handle the value such as "Welcome to planet earth".

17. Write  SQL statement that constructs a table called Table1 with the following fields:

a.     Field1 - this field stores numbers such as 1, 2, 3 etc.

b.     Field2 - this field stores the date and time.

c.      Field3 - this field stores the text up to 500 characters.

18. Write  SQL statement that adds the following records to Table1:

  Field1

 Field2

 Field3

   34

 1/19/2012 08:00 AM

 Mars Saturn

   65

 2/15/2012 10:30 AM

 Big Bright Sun

   89

 3/31/2012 09:15 PM

 Red Hot Mercury

  

19. Write  SQL statement to change the value for Field3 in Table1 to the value stored in Variable1 (From question 16), on the record that is 34.

20. Write  SQL statement for record 89 to return the total number of characters for Field3.

21. Write SQL statement for record 65 to return the first occurrence of a space in Field3.

22. Write  SQL statement for record 65 to return the value "Bright" from Field3.

23. Write  SQL statement for record 34 to return the day from the Field2.

24. Write  SQL statement for record 34 to return the first day of the month from the Field2.

25. Write  SQL statement for record 34 to return the previous end of the month from the Field2.

26. Write  SQL statement for record 34 to return the day of the week from the Field2.

27. Write  SQL statement for record 34 to return the date as CCYYMMDD from the Field2.

28. Write  SQL statement to add a new column, Field4 (data type can be of any preference), to Table1.

29. Write  SQL statement to remove record 65 from Table1.

30. Write  SQL statement to wipe out all records in Table1.

31. Write  SQL statement to remove Table1.

32. Create  sql statement that returns the TerritoryName,  SalesPerson (LastName Only) ship method, credit card type (If no credit card, it should say cash), OrderDate and TotalDue for ALL Transactions in the NorthWest Territory.