| Lifelong Learning | ||||||
| Reports Written In Pursuit Of Professional Diploma On IT | ||||||
|
Contents Report on Educational Technology |
Review Notes On SQL BCP I found out that BCP can successfully import and export both txt and xls files. If you experience a problem importing xls files, then you may not have a Microsoft Excel driver in the System DSN portion of your ODBC Data Source Applet in your Control Panel. With regard to mdb files (Microsoft Access), I found out through testing in my computer that bcp exports a file that is unreadable by Microsoft Access. Using DTS Import/Export Wizard, however, I can successfully import and export mdb (Microsoft Access) tables. Before you test this, please create first a blank database using your Microsoft Access software from where you can place your export or import test file. SP_HELP Sp_help can also be used to check the structure of a view as in sp_help vwMyView. In this way, the view is treated just like any other table with information given almost identical to that of a table. DATEADD AND DATEDIFF Dateadd is used to add numbers to different dateparts of a date while datediff is used to find the difference in dateparts between two dates. The syntax of DATEADD is dateadd (datepart, number, date). The syntax of DATEDIFF is datediff (datepart, date1, date2). Datename returns the name of the datepart from a given date. Its syntax is: datename (datepart, date). NOTABLE DATEPARTS
Yy
Year DEFAULT CONSTRAINT The syntax for adding default constraint is: add constraint dfObjectName default "Value" for ColumnName. NULLABLE COLUMNS You cannot add a primary key constraint to a column that has been created as nullable. A column created without the clause "not null" is assumed to be nullable. UPDATING TABLES I found out through testing that you can update different attributes of a table at one time by using a comma to separate the different attributes and new values. However, you cannot update two different tables at one time when the update is done through a view. You need to break the update into two update statements. INTEGRITY TYPE If the integrity constraint results from the implementation of a business rule, then the integrity will most probably be a user-defined type. CLUSTERED INDEX Only one clustered index can be created per table so build it on attributes that are unique and not frequently modified. NONCLUSTERED INDEX The create index command creates a nonclustered index by default. Use nonclustered index for columns used in joins and where clauses and whose values are modified frequently. WHICH INDEX TO MAKE FIRST A clustered index should be created before a nonclustered index because a clustered index changes the order of rows. A nonclustered index would need to be rebuilt if it has been created before a clustered index. COMPOSITE INDEX When you combine two attributes to have a unique key, the resulting key is called composite key. Under the rules of 2nd Normal Form, all non-key attributes must be functionally dependent on the whole key and not on just one part of the key. 3RD NORMAL FORM Under the rules of the 3rd Normal Form, all non-key attributes must be functionally dependent only on the primary key and not on any other attribute. USING THE CHECK CONSTRAINT TO VALIDATE INPUT DATA The syntax for adding check constraint to make sure the input contains only numeric characters is: add constraint ckObjectName check ( cColumnName like " [0-9] [0-9] [0-9] [0-9] " ). WHAT TYPE OF INTEGRITY? When each row can be uniquely identified using primary key, this is entity integrity. When you specify valid types, ranges, and formats for input data, this is domain integrity. When you use foreign key to reference a primary key, this is referential integrity. When the manager says, this is how the data should be, then this is user-defined data integrity. WITH ENCRYPTION You use the clause with encryption for creating or altering procedures whose code you want to hide. WITH RECOMPILE You use the with recompile clause to optimize procedures that use indexes that are frequently modified. ALL ABOUT VIEWS A view does not store data like a table. It is a query stored as an object that derives data from one or more tables. New columns added to the underlying table after a view has been created are not automatically included in the view. You need to alter the view to include the new column in the view. Dropping a table that references a view does not automatically drop the view. You must explicitly drop the view. Sp-rename can be used to change the name of the view without affecting the permissions granted on the view. DIFFERENCE BETWEEN BATCH AND STORED PROCEDURE A batch is a group of SQL statements submitted together for execution. It is parsed, optimized, and compiled every time it is executed. Stored procedures are pre-compiled objects consisting of many SQL statements stored and executed as a single unit. USE OF PARAMETERS IN STORED PROCEDURES Variables declared without using the output clause in create or alter procedure statement are considered for use as input. Procedures with this type of parameters will require values to be passed to them by the invoker when they are executed or called. Variables declared as for output in the create or alter procedure statement are designed to be used for sending back values to the invoker. Procedures created or altered with such parameters will allow the values in those variables to be displayed locally or passed on to an invoking procedure. Hence, the invoking procedure needs to provide for its own set of variables where values may be passed when the called procedure is invoked. A called procedure can also be designed to pass a return code to the calling procedure in the form of an integer that can signal successful or unsuccessful execution. Hence, the calling procedure must declare a variable and use it to capture the return code and proceed with different logic depending on the value of the integer returned. CAN YOU EXECUTE A TRIGGER? Triggers are invoked automatically when an attempt is made to modify the data in the table that the trigger protects. They cannot be called by any object other than a trigger action on the trigger table. In SQL Server, you cannot create user-defined triggers on system tables. CAN YOU DEFINE MORE THAN ONE SAME TYPE TRIGGERS ON A SINGLE TABLE? Yes, and the triggers will fire based on the order of their creation. WHEN SHOULD YOU USE A TRIGGER When you require that a delete in one table will also result in the delete of related data in another able or if your application requires that a column be validated against a column in another table. WHAT MAGIC TABLES ARE USED WHEN SQL Server makes use of two extra tables when a trigger is set when table values are modified. When an insert trigger is fired, both the trigger table and the inserted table contain the row that is to be inserted. When a delete trigger is fired, the object row is deleted from the trigger table and placed in the deleted table. When an update trigger is fired, the row to be deleted is placed in the deleted table and the row that is to be inserted is placed in the inserted and the trigger tables. Values in the magic tables may then be placed in local variable and examined the results of which may be used to determine how further processing should proceed. CAN YOU BIND TWO OR MORE RULES TO THE SAME COLUMN OR DATATYPE? No, only one rule can be bound to a given column or datatype. A new rule bound to a column or datatype already bound to another rule will replace the old one. A rule bound to a user-defined datatype does not replace a rule bound to the column using that datatype. You cannot define a rule for a system-defined datatype. SYNTAX FOR CREATING/DROPPING RULES AND DEFAULTS Create rule rule_name as @clolumn_name conditional_expression Create default default_name as constant_expression Drop rule rule_name Drop default default_name SYNTAX FOR BINDING/UNBINDING RULES AND DEFAULTS Sp_bindrule rule_name, 'object.name' Sp_unbindrule 'object.name' Sp_bindefault default_name, 'object.name' Sp_unbindefault 'object.name' SYNTAX FOR USER DEFINED DATATYPE Sp_addtype type_name, system_datattype Sp_droptype 'type_name' WHY USE USER-DEFINED DATATYPE? To ensure consistency in type and length properties of attributes used in different tables. DIFFERENCE BETWEEN INSERT INTO AND SELECT INTO Use insert into when copying data form an existing table to another existing table. The syntax is: insert into table_name1 select column-names from table_name2. Use select into to copy contents of one table to a new table. The syntax is: select columns_list into new_table_name from table_names where conditions. Select into cannot be used in a view declaration statement. HOW DO YOU REMOVE ALL ROWS FROM A TABLE AT ONCE? Use the truncate table command with syntax: truncate table table_name. The truncate table command deletes rows faster than the delete from table command. It does not have a where clause and does not fire a trigger. DIFFERENCE BETWEEN AND/OR If either of two conditions is true, OR returns true. AND returns true only if both conditions are true. WHEN TO USE OUTER JOIN Outer join is used when you want to specify which table should control what rows will be selected. Unlike inner join which can join several tables, outer join can be used for joining two tables only. Left outer join lets the first table mentioned to control which rows are selected. Right outer join lets the second table control which rows will be selected. SYNTAX OF SUBSTRING FUNCTION The syntax of the substring function is: substring ( Expression, start, length ). For example, substring ('Oying', 2, 4) will return 'ying'. NOTABLE STRING FUNCTIONS Ascii returns the ascii equivalent of a character. Char returns the character equivalent of an ascii code value. Len returns the number of characters in the expression. Lower converts character expression into lower case while upper converts it into upper case. FOR WHAT IS THE KEYWORD DISTINCT USED? The distinct keyword is used to eliminate duplicate values from a column in the query output. HOW TO INCLUDE COMMENTS IN BATCHES/PROCEDURES 1. Enclose multiline comments between /* and */ 2. Precede one-line comments with two dashes -- WHAT ARE SYSTEM FUNCTIONS They are used to access SQL Server, database, or user-related information. They are used frequently on system stored procedures. Examples of system function include host_name() which returns the host computer name of the client process and db_name() which returns the database name. HOW MAY SUCH SYSTEM RELATED INFORMATION BE ACCESSED ALTERNATIVELY? System-defined variables also called global variables which are preceded by two @ signs to distinguish them from locally-defined variables that are preceded by only one @ sign can also be used to access system information. The variable @@servername returns the name of the SQL server while the @@version returns the date of the current version. The @@error returns a zero value after a successful command execution and other integer values for execution with errors. The @@rowcount returns the number of rows affected by the last query. WHAT IS INCLUDED WHEN YOU USE THE KEYWORD BETWEEN? Between specifies a range of constant expressions that includes both the upper and lower boundary values. For example, between 0 and 10 means greater or equal than 0 and less or equal than 10. CAN I DEFINE MULTIPLE CHECK CONSTRAINTS IN A SINGLE COLUMN? Yes and they are evaluated in the order in which they were defined. COMPUTE AND COMPUTE BY Compute calculates an aggregate value for all rows for one or more columns while compute by calculates group aggregate values. To get the desired effect, compute by must be used in conjunction with the order by command. Download MS Word Version (.doc) of this document. |
|
||||
|
Copyright 2008 Aureo P. Castro Email: [email protected] |
||||||