1 / 82

SQL Database Programming Essentials

Learn about SQL database programming essentials such as using batches, concatentation with cast and convert functions, variables, TSQL statements, table variables, and more in this comprehensive guide.

yahto
Download Presentation

SQL Database Programming Essentials

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. © 2002, Mike Murach & Associates, Inc.

  2. © 2002, Mike Murach & Associates, Inc.

  3. © 2002, Mike Murach & Associates, Inc.

  4. Batches and the GOcommand © 2002, Mike Murach & Associates, Inc.

  5. GO • To signal the end of a batch, you use the GO command. • A GO command isn’t required after the last batch in a script or for a script that contains a single batch. © 2002, Mike Murach & Associates, Inc.

  6. © 2002, Mike Murach & Associates, Inc.

  7. © 2002, Mike Murach & Associates, Inc.

  8. © 2002, Mike Murach & Associates, Inc.

  9. © 2002, Mike Murach & Associates, Inc.

  10. © 2002, Mike Murach & Associates, Inc.

  11. © 2002, Mike Murach & Associates, Inc.

  12. © 2002, Mike Murach & Associates, Inc.

  13. Concatenation, cast, convert © 2002, Mike Murach & Associates, Inc.

  14. Concatenation ( + ) • The SQL standard uses || for concatentation • Sql Server TSql uses + for concatenation • Example:SELECT fname + ' ' + lname as name FROM employees © 2002, Mike Murach & Associates, Inc.

  15. operands must be character data • In SQL Server - TSql ...concatenation MUST take two character values (varchar, char, nchar, etc) as its operands • Neither parameter can be numeric or date types • The following WILL cause an ERROR (if salary is a money column) SELECT fname + salary FROM employees © 2002, Mike Murach & Associates, Inc.

  16. cast • Use the SQL standard "cast" operator to convert data from one datatype into another • The following WILL work:SELECT fname + cast(salary as varchar(10))FROM employees © 2002, Mike Murach & Associates, Inc.

  17. convert • You can use the TSQL "convert" function instead of cast. • The following is equivalent to the previous example:SELECT fname + convert(varchar(10), salary)FROM employees © 2002, Mike Murach & Associates, Inc.

  18. Difference between CAST and CONVERT • differences • standards • CAST is a standard • CONVERT is TSQL specific • extra functionality • CONVERT has one additional piece of functionality that CAST does not have • see next slide © 2002, Mike Murach & Associates, Inc.

  19. Convert (type, data, style) • Convert takes an optional 3rd (integer) parameter that determines the "style" (AKA format) of the data after the cast. • If the data is a money value then format has the following possible values:0 - no commas, two decimal points1 - yes commas, two decimal points2 - no commas, four decimal points © 2002, Mike Murach & Associates, Inc.

  20. styles for dates, float, real • See the following URL: • http://doc.ddart.net/mssql/sql70/ca-co_1.htm © 2002, Mike Murach & Associates, Inc.

  21. TSQL statements © 2002, Mike Murach & Associates, Inc.

  22. USE statement • USE <databaseName> • The use statement switches a script to use the specified database. • A single script can work in more than one database by issuing the use statement several times © 2002, Mike Murach & Associates, Inc.

  23. PRINT statement • PRINT <string expression> • Prints out the value of the string expression. © 2002, Mike Murach & Associates, Inc.

  24. Variables © 2002, Mike Murach & Associates, Inc.

  25. © 2002, Mike Murach & Associates, Inc.

  26. © 2002, Mike Murach & Associates, Inc.

  27. © 2002, Mike Murach & Associates, Inc.

  28. © 2002, Mike Murach & Associates, Inc.

  29. © 2002, Mike Murach & Associates, Inc.

  30. © 2002, Mike Murach & Associates, Inc.

  31. © 2002, Mike Murach & Associates, Inc.

  32. © 2002, Mike Murach & Associates, Inc.

  33. Table Variables © 2002, Mike Murach & Associates, Inc.

  34. Scalar vs. table variables A The variables that we've seen so far can hold a single data item and is defined with a standard data type. These are called scalar variables. A variable that can hold an entire table is called a "table variable" (see next slide ...) © 2002, Mike Murach & Associates, Inc.

  35. © 2002, Mike Murach & Associates, Inc.

  36. © 2002, Mike Murach & Associates, Inc.

  37. © 2002, Mike Murach & Associates, Inc.

  38. Derived tables © 2002, Mike Murach & Associates, Inc.

  39. Derived Tables • The term "Derived Table" is simply a table that is created as the result of a subquery in the FROM clause. © 2002, Mike Murach & Associates, Inc.

  40. Temporary Tables © 2002, Mike Murach & Associates, Inc.

  41. © 2002, Mike Murach & Associates, Inc.

  42. © 2002, Mike Murach & Associates, Inc.

  43. © 2002, Mike Murach & Associates, Inc.

  44. © 2002, Mike Murach & Associates, Inc.

  45. © 2002, Mike Murach & Associates, Inc.

  46. © 2002, Mike Murach & Associates, Inc.

  47. IF BEGIN ... ENDELSE BEGIN ... END © 2002, Mike Murach & Associates, Inc.

  48. © 2002, Mike Murach & Associates, Inc.

  49. © 2002, Mike Murach & Associates, Inc.

  50. © 2002, Mike Murach & Associates, Inc.

More Related