Database systems and open sourcing

The ial videos mainly discusses on how to retrieve and manipulate using ADO. NET, the use of sub-queries, creating and using stored procedures (sprocs), other T-SQL programming elements available for use in sproc, and Visual Basic and C# syntax required in employing sprocs in your applications. There are two basic methods of working with data from your applications: (1) using ADO. NET’s objects in the retrieval, manipulation and navigation through tables’ rows and columns, and (2) executing SQL statements from ADO. NET. SQL or Structured Query Language is a programming language designed for CRUD (Create, Read, Update, and Delete) operations on table data. An Ad Hoc query enables interaction with a database without having to create an application. SQL Server Management Studio Express, a companion tool of the SQL Server 2005 Express Edition, should be installed for creating Ad Hoc queries. After installation, first step is to save all databases to be used in Microsoft SQL Server/MSSQL. 1/MSSQL/Data in the Program Files. Then open the SQL Server Management Express and create or attach a database. In writing Ad Hoc queries, select ” New Query” button and select which database you are querying from on the toolbar. The SELECT statement is used to read/retrieve data from the database. Specific commands can be highlighted execute only certain commands. Also, results can be formatted to save into a CSV file or a Text file. To print a friendlier version of the results, column names can be given aliases. Number of results can be limited by including a WHERE clause. To aggregate values in specific columns, use SUM function, while the Count function calculates the number of records in a selected table. Mathematical and Conditional operations can also be executed via Ad Hoc query, as well as determining Minimum, Maximum and Average values. Different Date functions are also available for use. To select a subset of entire field information, use Substring function with defined initial index and length. In selecting rightmost or leftmost characters, use Right and Left functions with defined lengths. Manipulate text using Upper (for uppercase), Lower (for lowercase) and Reverse (for reversing text) functions. To retrieve records that fit a particular pattern, use LIKE clause, while IN clause is used to return records that are in one of the specified lists. Ordering records is done using Ascending or Descending functions. GROUP BY statements specify which group of fields to total, while JOIN statements retrieve fields from different tables and ORDER BY statements group results together in a given order. Sub-queries can be created by employing more than one SELECT statements in a query, where the results returned by one SELECT statement serves as an input to other SELECT statements. To insert values in a table, INSERT function is used and it should be noted to include all non-nullable fields. UPDATE statements are the most potentially destructive among SQL commands, particularly when a WHERE clause is left off. Changes made by updating the data cannot be recovered; hence it will be helpful to secure a backup copy of your database before testing. Stored Procedures (sprocs) are reusable, named queries or T-SQL, analogous to VB or C# methods which are defined with a set of input/output data and return value parameters. They can include one or more statements combining SELECTs, INSERTs, DELETEs and UPDATEs, ultimately making your VB or C# code cleaner. Sprocs can be created only once using the command CREATE, use ALTER for making changes. Sprocs enable variable declaration and assignment, looping and branching statements, code blocks and cursors. They can be used to manage access to your database and can be made as integration point for all applications that need access. Sprocs must only be used when it makes sense (i. e. multi-user client applications, high throughput applications, finer granularity of security requirements, etc.). Most parts of the videos are no longer forward. I think they are more suitable to those with experience already. I find the different examples in using the SQL syntax very helpful in though. References: Microsoft. (2011). Manipulating Database Data 2006. Retrieved May 9, 2011, from http://www. asp. net/sql-server/videos/manipulating-database-data  Microsoft. (2011). More Structured Query Language. Retrieved May 9, 2011, from http://www. asp. net/sql-server/videos/more-structured-query-language  Microsoft. (2011). Creating and Using Stored Procedures 2006. Retrieved May 9, 2011, from http://www. asp. net/sql-server/videos/creating-and-using-stored-procedures  Microsoft. (2011). Using SQL Server Management Studio 2006. Retrieved May 9, 2011, from http://www. asp. net/sql-server/videos/using-sql-server-management-studio