Sql Code Masterz

What is normalization? Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

Indexes help us retrieve data from tables quicker. Let's use an example to illustrate this point: Say we are interested in reading about how to grow peppers in a gardening book. Instead of reading the book from the beginning until we find a section on peppers, it is much quicker for us to go to the index section at the end of the book, locate which pages contain information on peppers, and then go to these pages directly. Going to the index first saves us time and is by far a more efficient method for locating the information we need. The same principle applies for retrieving data from a database table. Without an index, the database system reads through the entire table (this process is called a 'table scan') to locate the desired information. With the proper index in place, the database system can then first go through the index to find out where to retrieve the data, and then go to these locations directly to get the needed data. This is much faster. ...

The SOUNDEX code is a substitution code using the following rules: The first letter of the surname is always retained. The rest of the surname is compressed to a three digit code using the following coding scheme: A E I O U Y H W not coded B F P V coded as 1 C G J K Q S X Z coded as 2 D T coded as 3 L coded as 4 M N coded as 5 R coded as 6 Consonants after the initial letter are coded in the order they occur: HOLMES = H-452 ADOMOMI = A-355 The code always uses initial letter plus three digits. Further consonants in long names are ignored: VONDERLEHR = V-536 Zeros are used to pad out shorter names: BALL = B-400 SHAW = S-000 Double consonants are treated as one letter: BALL = B-400 As are adjacent consonants from the same code group: JACKSON = J-250 A consonant following an initial letter from the same code group is ignored: SCANLON = S-545 Abbreviated prefixes should be spelt out in full: ST JOHN = SAINTJOHN = S-532 Apostrophes and hyphens are ...

SQL Integrity Constraints Integrity Constraints are used to apply business rules for the database tables. The constraints available in SQL are Foreign Key, Not Null, Unique, Check. Constraints can be defined in two ways 1) The constraints can be specified immediately after the column definition. This is called column-level definition. 2) The constraints can be specified after all the columns are defined. This is called table-level definition. 1) SQL Primary key: This constraint defines a column or combination of columns which uniquely identifies each row in the table. Syntax to define a Primary key at column level: column name datatype [CONSTRAINT constraint_name] PRIMARY KEY Syntax to define a Primary key at table level: [CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..) column_name1, column_name2 are the names of the columns which define the primary Key. The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional. For Example:...

SQL Tuning/SQL Optimization Techniques: 1) The sql query becomes faster if you use the actual columns names in SELECT statement instead of than '*'. For Example: Write the query as SELECT id, first_name, last_name, age, subject FROM student_details; Instead of: SELECT * FROM student_details; 2) HAVING clause is used to filter the rows after all the rows are selected. It is just like a filter. Do not use HAVING clause for any other purposes. For Example: Write the query as SELECT subject, count(subject) FROM student_details WHERE subject != 'Science' AND subject != 'Maths' GROUP BY subject; Instead of: SELECT subject, count(subject) FROM student_details GROUP BY subject HAVING subject!= 'Vancouver' AND subject!= 'Toronto'; 3) Sometimes you may have more than one subqueries in your main query. Try to minimize the number of subquery block in your query. For Example: Write the query as SELECT name FROM employee ...