| Lifelong Learning | ||||||
| Reports Written In Pursuit Of Professional Diploma On IT | ||||||
|
Contents Report on Educational Technology |
Learning SQL Using Microsoft's Query Analyzer Microsoft had included a powerful but easy to use utility for managing and analyzing data in its SQL Server Software product. Aside from acting as a user-friendly interface to your data base, the Query Analyzer can be used as an effective medium for learning SQL ( Structured Query Language). A Pentium II or III computer running Microsoft NT Server Version 4 with Service Pack 6a installed would be an ideal platform for installing the Microsoft SQL Server software. Evaluation copies of the software may be downloaded from the Internet and may also be included in a CD ROM accompanying a book on Microsoft SQL Server. When installing the software, be sure to include any tutorials or sample databases if any are made available. When using Windows 98 or Millennium Edition, make sure that you install the desktop version of the software. Unlike in Windows NT, MS SQL Server does not start automatically when you boot your Windows 98 or Millennium computer. Therefore, make sure that you start your MS SQL Server manually using your Service Manager before using your Query Analyzer. You can of course let the SQL server start automatically by checking this option in your Service Manager window. What is SQL? SQL is a standard language for the development and implementation of relational databases. Other approaches for structuring databases have been tried in the past such as the hierarchical model but so far, the relational approach has stood the test of time. Microsoft SQL Server is a relational database management system, a software product that makes it easy to create and use a relational database. In the same category are Oracle, SyBase, DB2, and the open source Postgress and MySql. Under a database setup, information is organized into tables which presents information under a two-dimensional structure consisting of columns and rows. The columns represent the particular characteristic of an object which we are interested in and the rows represent the values of those characteristics for a particular instance of an object. SQL includes both the commands for creating tables and manipulating the data in the tables to come up with useful information. You should be aware that data base development efforts comprise a multitude of tasks that include planning the structure of the database, setting up and populating the tables, and using SQL commands to transform data into information that will underlie an organization s actions and decisions. Database Design A key point in designing the database structure is deciding what tables will comprise our database. You should be aware that there are rules that should be followed in the structuring of our tables to ensure a trouble-free database operation in the days ahead. We will want the initial set of tables and data elements we have identified to undergo a normalization process that will result in the optimal set of tables for our database. We will aim to at least achieve a tables setup that is in the so-called third normal form wherein our data are distributed optimally in a certain set of tables with data redundancy and duplication eliminated. We would also want our tables to pass a series of integrity tests to make sure that only valid data will be accepted and that data relationships are maintained. Only after we are satisfied that our table structure are in order can we begin to use SQL in building our database. Using the Query Analyzer, we can then begin on our journey toward learning the finer points of SQL. In our computer, we start the Query Analyzer and type SQL commands to use our database. We tell the Query Analyzer to execute the commands we typed by highlighting the command and pressing F5 on our keyboards. The Create Database command will enable us to establish and name our database while the Create Table command enables us to create empty tables for our data. We use the Insert command to populate our tables and the Update command to change the values in our tables. We use the Delete command to remove a row from any of our tables. If we make a mistake in setting up our tables, we can use the Drop Table command to get rid of a table or we can use the Alter Table command to change the structure of our tables. We use the select into command to copy data values from an existing table to a new one. To copy data items to an existing table, we use the insert into command. DataTypes We would want to make sure that we use the correct size and type for the data items in our table. We use the character data type for textual data that will have a fixed size and specify variable character for data that will contain only as much storage space as the data requires. We can use a lot of numeric data types that are too many to mention here. Suffice it is to say that SQL can handle integer, floating point, and monetary numeric data. We use datetime to specify a date data type. Not only can we use the existing system data types but we can also make our very own based on the existing types. We can use the sp_addtype utility to add a so-called user-defined data type. We use the sp_help utility to have a look at the structures and object properties in our database. We use the Identity modifier to tell Microsoft SQL Server that we want a certain data item to be automatically generated and the not null modifier to specify that the data item cannot be left unfilled. Once the database has been set up, Microsoft SQL Server offers a powerful set of data manipulation commands that can respond to complex queries. Other Commands, Operators, and Functions The Select command will be our most used command in this phase of our learning process as it is the one used to specify a row of data we want to retrieve. We learn how to use the where clause to more specifically identify the row of interest to our query. We use algebraic, logical, and string operators to compare numeric, datetime and textual data. We use the in operator to test if the value in question belongs to a set of enumerated items and choose the between operator if we want to find out if the data is within the range that we specify. We use the like operator together with the % and _ signs to check if the series of letters are present in the data in question. We use the square brackets to specify the various figures that may be present or not in the data to qualify the row to be included in the result set. To specify a different column heading, we type the heading we want in quotes and together with the equal sign prepend it to the actual column name. To specify a different arrangement of results, we use the order by clause. We use the count function with the distinct modifier to check how many affected rows there are and the minimum. maximum, and average functions to find out how much of these are present in the database. We use the sum function together with the group by clause to report subtotals for each subgroup category. We use the top operator to display only the top portion of the table based on arrangement prescribed by the order by clause. We use the compute by clause to report an aggregate value at the bottom of the list. We use the dateadd and the datediff functions to add and subtract dates. We specify datepart to refer to what portion of the date we are interested in and use datename to display a particular weekday or month. But we must not forget the round function through which we can specify how accurate we want our numeric data to be. In addition and for text data, we can also use the upper and lower functions to change the case of our character data. We use join to get to data in another table using a foreign key to relate the data to another table. We use the left or right outer join to force all rows in a particular table to be displayed. We use union to merge similar column from two or more tables. To make a new table without using the Create Table command, we use the into clause together with the select command. We use a sub-query which is a self-contained select statement to retrieve data needed by our main query. Here, we must not forget to put the trailing right parentheses that will enclose the sub-query. Tips and Problems In using joins, we make sure that we plan our select statement in such a way that we are able to relate one particular table to another using foreign keys. Taking a peek first at the relevant tables makes it easier to relate the tables to one another in a more concrete way than by just merely referring to the database schema. Misspelling a table name or a column name and forgetting to put commas between column names are sure ways to get an error message from Microsoft SQL Sever. We may get wrong results when only a portion of the query has been executed because we did not highlight the whole query before executing it. We might want to check also if we are in the right database since Microsoft SQL Server will complain if the table and column names in your command are not found in the database you are using. To correct the problem, click on the correct database on the dropdown box or execute the command use database name. We use constraints as a means of ensuring the integrity of our data items. We identify a column as a primary key to make sure that it will not contain null or duplicate values. We use the foreign key constraint to make sure that any data item entered has a primary key that it can reference. For non-key columns, we can use the unique and check constraints to prevent duplication and validate data respectively. We use the default constraint to assign a constant value to the column. Microsoft SQL Server allows us to introduce constraints without having to alter our tables. We can create rules and defaults to introduce additional specifications to control data values during inserts and updates. After binding them to a particular column in a specific table, they are implemented as if they were created during table creation or modification. Views and Stored Procedures Views are virtual tables that provide a customized way of looking at related information. We create views to restrict access to columns of data from different tables. One point to remember when updating data using views is that we can only update one table at a time. Stored procedures are groups of SQL statements submitted for execution as a single executable unit. They act like a shortcut so we need to type only a single command to execute a group of statements. They are pre-compiled and optimized so we may want to add the with recompile option to our procedures if out data changes frequently. Just like in conventional programming, stored procedures make use of flow control statements like if-else, while, and case. They also make use of both system and user-defined variables. We prefix user-defined variables with a single @ sign to differentiate them from system-defined variables which are prefixed with a double @ sign. Variables are useful as temporary storage of data from our tables. We can use them for getting values from the user, passing arguments between procedures, and displaying or printing values. They are assumed to be used for input only unless we specify them as for output also during variable declaration or as parameters for stored procedures. We should always include the for output specification in variable or parameter declaration if we intend to display or print the contents of the variable. MS SQL Server gets a bit picky when passing variables from one procedure to another. We need to specify output variables in the create procedure line of the called procedure if we want the procedure to return two or more variables. In the calling procedure, we need first to declare the local variables that will receive the values from the called procedure and then identify and specify them for output when executing the called procedure. Triggers are special-purpose stored procedures that are automatically executed in response to an event that modifies data in the database. We can design triggers to respond to adding records to a table, changing the values of some columns, or deleting records from a table. Triggers are useful for maintaining data consistency and integrity by making sure that changes in one table are reflected in the data in other tables. They are created for a particular table and for a particular purpose that is, whether they will be executed whenever an insert, update, or delete operation had just been performed. Transactions enable us to control the changes that we make to our database. They give us the option to make our changes permanent or revert the database to its original state before we made our changes. We use the begin transaction command to signal MS SQL Server that the changes that follows can be either made final or recalled. We use the commit transaction command to make our updates final and the rollback transaction command to discard the changes we have made. The save transaction command is used to separate those transactions that may be safely made permanent from those that we may want to recall or make final. MS SQL Server provides two means to transfer data to and from our database. We use the bulk copy utility (bcp) to copy data using the DOS command line interface. We can also use the Data Transformation Services (DTS) wizard which is a GUI based user-friendly interface. We can transfer our SQL data to and from text, MS Excel or MS Access files using these two utilities. There are problems though when using bcp to import and export MS Access files. Learning SQL using the Microsoft SQL Server s Query Analyzer is a fun and interesting way of adding to your stock of computer knowledge. Most interactive Internet application these days make use of data bases for storing and retrieving all sorts of information. A great number of these are using relational databases created and maintained using SQL. As of the present, there is no database technology that can beat the power and flexibility of the SQL based relational database model. Therefore, learning SQL may prove to be a beneficial long term investment for a computer professional. Download MS Word Version (.doc) of this document. |
|
||||
|
Copyright 2008 Aureo P. Castro Email: [email protected] |
||||||