How does database indexing work?

 Why is it needed?

When data is stored on disk-based storage devices, it is stored as blocks of data. These blocks are accessed in their entirety, making them the atomic disk access operation. Disk blocks are structured in much the same way as linked lists; both contain a section for data, a pointer to the location of the next node (or block), and both need not be stored contiguously.

Due to the fact that a number of records can only be sorted on one field, we can state that searching on a field that isn’t sorted requires a Linear Search which requires N/2 block accesses (on average), where N is the number of blocks that the table spans. If that field is a non-key field (i.e. doesn’t contain unique entries) then the entire tablespace must be searched at N block accesses.

Whereas with a sorted field, a Binary Search may be used, which has log2 N block accesses. Also since the data is sorted given a non-key field, the rest of the table doesn’t need to be searched for duplicate values, once a higher value is found. Thus the performance increase is substantial.

What is indexing?

Indexing is a way of sorting a number of records on multiple fields. Creating an index on a field in a table creates another data structure which holds the field value, and a pointer to the record it relates to. This index structure is then sorted, allowing Binary Searches to be performed on it.

The downside to indexing is that these indices require additional space on the disk since the indices are stored together in a table using the MyISAM engine, this file can quickly reach the size limits of the underlying file system if many fields within the same table are indexed.

How does it work?

Sql Server material 20

SUBQUERY: A subquery is a query that is nested inside a SELECT, INSERT, UPDATE, or DELETE statement, or inside another subquery. A subquery can be used anywhere an expression is allowed. In this case first the inner query executes and basing upon the result generated by it the outer query executes to generate the final output.

           

WAQ to find the details of employees earning the highest salary.

Sol: SELECT * FROM EMP WHERE SAL=

(SELECT MAX(SAL) FROM EMP)

WAQ to find the details of employees earning the second highest salary.

Sol: SELECT * FROM EMP WHERE SAL=

(SELECT MAX(SAL) FROM EMP WHERE SAL<

(SELECT MAX(SAL) FROM EMP))

WAQ to find the details of employees working in sales department.

Sol: SELECT * FROM EMP WHERE DEPTNO=

(SELECT DEPTNO FROM DEPT WHERE DNAME=’SALES’)

  

WAQ to find the details of employees working in Mumbai.

Sol: SELECT * FROM EMP WHERE DEPTNO=

(SELECT DEPTNO FROM DEPT WHERE LOC=’MUMBAI’)

WAQ to find the details of employees who are earning more than the highest salary of deptno 30

Sol: SELECT * FROM EMP WHERE SAL>

            (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=30)

OR

SELECT * FROM EMP WHERE SAL>

            ALL (SELECT SAL FROM EMP WHERE DEPTNO=30)

-In this case we can use the ALL operator which will compare an expression with set of values, where the expression has to satisfy the condition with all the values.

WAQ to find the details of employees who are earning less than the lowest salary of deptno 20

Sol: SELECT * FROM EMP WHERE SAL<

            (SELECT MIN(SAL) FROM EMP WHERE DEPTNO=20)

OR

SELECT * FROM EMP WHERE SAL<

            ALL(SELECT SAL FROM EMP WHERE DEPTNO=20)

WAQ to find the details of employees who are earning less than the highest salary of deptno 10

Sol: SELECT * FROM EMP WHERE SAL<

            (SELECT MAX(SAL) FROM EMP WHERE DEPTNO=10)

OR

SELECT * FROM EMP WHERE SAL<

            ANY(SELECT SAL FROM EMP WHERE DEPTNO=10)

-In the place of ANY we can use SOME operator also.

- In this case we can use the ANY/SOME operatorS which will compare an expression with set of values, where the expression has to satisfy the condition with at least a single value.

WAQ to find the details of employees who are earning the highest salary in each department.      

            SELECT * FROM EMP WHERE SAL IN

(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)

WAQ to find the details of seniors in each department.       

            SELECT * FROM EMP WHERE HIREDATE IN

(SELECT MIN(HIREDATE) FROM EMP GROUP BY DEPTNO)

<<< Previous                                                                                                 Will Come...

Sql Server material 19

CLAUSES: SQL Server provides with the following clauses that can be used in the SELECT statements:

·         WHERE

·         GROUP BY

·         HAVING

·         ORDER BY

The complete syntax of the SELECT statement looks as following:

            SELECT <select_list>  FROM <tname>

[ WHERE search_condition ]

[ GROUP BY group_by_expression ]

[ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ]

           

WHERE Clause: The WHERE clause is a filter that defines the conditions each row in the source tables must meet to qualify for the SELECT. Only rows that meet the conditions contribute data to the result set. Data from rows that do not meet the conditions is not used.

            SELECT * FROM EMP WHERE JOB=’MANAGER’

            SELECT * FROM EMP WHERE DEPTNO=20

GROUP BY Clause: The GROUP BY clause partitions the result set into groups based on the values in the columns of the group_by_list. For example, the Emp table has 3 values in Deptno column. A GROUP BY Deptno clause partitions the result set into 3 groups, one for each value of Deptno.

WAQ to find the highest salaries for each department.

Sol: SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO

WAQ to find the highest salaries for each job.

Sol: SELECT JOB, MAX(SAL) FROM EMP GROUP BY JOB

WAQ to find the highest salaries for each department in it for each job.

Sol: SELECT DEPTNO, JOB, MAX(SAL) FROM EMP GROUP BY DEPTNO, JOB

Note: While using the GROUP By clause the select_list of the query should contain only the following:

            -Group Functions or Aggregate Functions

            -Columns used in the Group By Clause

            -Constants.

WAQ to find the number of employees working for each department.

Sol: SELECT DEPTNO, COUNT(*)  FROM EMP GROUP BY DEPTNO

WAQ to find the number of employees working for each department only if the number is greater than 3.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP GROUP BY DEPTNO HAVING COUNT(*)>3

HAVING Clause: The HAVING clause is an additional filter that is applied to the result set. Logically, the HAVING clause filters rows from the intermediate result set built from applying any FROM, WHERE, or GROUP BY clauses in the SELECT statement. HAVING clauses are typically used with a GROUP BY clause.

WAQ to find the number of Clerk’s working for each department.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO

WAQ to find the number of Clerk’s working for each department only if the count is greater than 1.

Sol: SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO HAVING COUNT(*)>1

ORDER BY order_list[ ASC | DESC ]

The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted in an ascending or descending sequence.

            SELECT * FROM EMP ORDER BY SAL

            SELECT * FROM EMP ORDER BY SAL DESC

            SELECT * FROM EMP ORDER BY SAL, COMM

<<< Previous                                                                                                                           Next >>>

Sql Server material 18

Set Operators: Combines the results of two or more queries into a single result set.

The following are basic rules for combining the result sets of two queries by using SET Operators:

• The number and the order of the columns must be the same in all queries.
• The data types must be compatible.

UNION: Combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.

            SELECT JOB FROM EMP WHERE DEPTNO=10

            UNION

            SELECT JOB FROM EMP WHERE DEPTNO=30

UNION ALL: These is same as UNION but in this case duplicates will not be eliminated.

            SELECT JOB FROM EMP WHERE DEPTNO=10

            UNION ALL

            SELECT JOB FROM EMP WHERE DEPTNO=30

INTERSECT: Returns any distinct values that are returned by both the query on the left and right sides of the INTERSECT operand.

            SELECT JOB FROM EMP WHERE DEPTNO=10

            INTERSECT

SELECT JOB FROM EMP WHERE DEPTNO=30

EXCEPT: Returns any distinct values from the query to the left of the EXCEPT operand that are not also returned from the right query.

            SELECT JOB FROM EMP WHERE DEPTNO=10

            EXCEPT

SELECT JOB FROM EMP WHERE DEPTNO=30


<<< Previous                                                                                                             Next >>>

Sql Server material 17

-WAQ to find the details of employees whose jobs are CLERK, MANAGER AND SALESMAN

Sol: SELECT * FROM EMP WHERE JOB=’CLERK’ OR JOB=’MANAGER’ OR JOB=’SALESMAN’

Sol: SELECT * FROM EMP WHERE JOB IN (’CLERK’, ’MANAGER’, ’SALESMAN’)

-In Operator Determines whether a specified value matches any value in the list.

-WAQ to find the details of all employees except PRESIDENT AND MANAGER.

Sol: SELECT * FROM EMP WHERE JOB != ’MANAGER’ AND JOB != ’PRESIDENT’

Sol: SELECT * FROM EMP WHERE JOB NOT IN (’MANAGER’, ’PRESIDENT’)

-WAQ to find the details of employees who name starts with character S.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘S%’

-Like Operator determines whether a specific character string matches a specified pattern. A pattern can include regular characters and wildcard characters. During pattern matching, regular characters must exactly match the characters specified in the character string. However, wildcard characters can be matched with arbitrary fragments of the character string. Using wildcard characters makes the LIKE operator more flexible than using the = and != string comparison operators.

           

            % - it represents any string of zero or more characters.

-WAQ to find the details of employees whose name contains M in it.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘%M%’

-WAQ to find the details of employees whose name is SMITH, when the spelling of the name is not known exactly as SMITH OR SMYTH.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘SM_TH’

OR

Sol: SELECT * FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX(‘SMYTH’)

           

            _(underscore) - it represents any single character.

WAQ to find the details of employees whose name starts with a characters between A to S.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘[A-S]%’

[ ] – it represents any single character within the specified range ([a-f]) or set ([abcdef]).

WAQ to find the details of employees whose name starts with any of the character “ABCDE”.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘[ABCDE]%’

WAQ to find the details of employees whose name starts with a characters not between A to S.

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘[^A-S]%’

WAQ to find the details of employees whose name starts with characters apart from “ABCDE”

Sol: SELECT * FROM EMP WHERE ENAME LIKE ‘[^ABCDE]%’

OR

Sol: SELECT * FROM EMP WHERE ENAME NOT LIKE ‘[ABCDE]%’

WAQ to find the details of employees whose job is CLERK and earning 3000.

Sol: SELECT * FROM EMP WHERE JOB=’CLERK’ AND SAL=3000

WAQ to find the details of employees whose job is MANAGER as well as earning more than 3000.

Sol: SELECT * FROM EMP WHERE JOB=’MANAGER’ OR SAL>3000

WAQ to find the details of employees whose salary is not equal to 3000.

Sol: SELECT * FROM EMP WHERE NOT SAL=3000

<<< Previous                                                                                                                           Next >>>

Sql Server material 16

-WAQ to find the details of employees whose job is CLERK.

Sol: SELECT * FROM EMP WHERE JOB=’CLERK’

-WAQ to find the details of all employees except SALESMAN.

Sol: SELECT * FROM EMP WHERE JOB != ‘SALESMAN’

(OR)

Sol: SELECT * FROM EMP WHERE JOB <> ‘SALESMAN’

-WAQ to find the details of employees who are earning more than 3000

Sol: SELECT * FROM EMP WHERE SAL>3000

-WAQ to find the details of employees who are earning less than 2500

Sol: SELECT * FROM EMP WHERE SAL<2500

-WAQ to find the details of employees who are earning with in a range of 2500 and 4000

Sol: SELECT * FROM EMP WHERE SAL>=2500 AND SAL<=4000

Sol: SELECT * FROM EMP WHERE SAL BETWEEN 2500 AND 4000

-Between operator is used for specifying with a range of values to test.

-WAQ to find the details of employees who are earning less than 1500 as well as more than 3500

Sol: SELECT * FROM EMP WHERE SAL<1500 OR SAL>3500

Sol: SELECT * FROM EMP WHERE SAL NOT BETWEEN 1500 AND 3500

<<< Previous                                                                                                                        Next >>>

Sql Server material 15

Operators: An operator is a symbol specifying an action that is performed on one or more expressions. The lists the operator categories that SQL Server supports:

            -Arithmetic Operators

            -Assignment Operator

            -Comparison Operators

            -Logical Operators

            -Concatenation Operator

Arithmetic Operators: Arithmetic operators perform mathematical operations on two expressions of one or more of the data types of the numeric data type category. Those are:

            +          -           Addition

-           -           Subtraction

*          -           Multiplication

/           -           Division

%         -           Modulo

Assignment Operators: The equal sign (=) is the only assignment operator.

Comparison Operators: Comparison operators test whether two expressions are the same. Comparison operators can be used on all expressions except expressions of the text, ntext, or image data types. Those are:

            =          -           Equal to

            >          -           Greater than

            <          -           Less than

            >=        -           Greater than or equal to

            <=        -           Less than or equal to

            <>        -           not equal to

            !=         -           not equal to

            !<         -           not less than

            !>         -           not greater than

Logical Operators: Logical operators test for the truth of some condition. Logical operators, like comparison operators, return a Boolean value of TRUE or FALSE.

Those are:

• ALL                TRUE if all of a set of comparisons are TRUE
• AND               TRUE if both Boolean expressions are TRUE
• ANY               TRUE if any one of a set of comparisons are TRUE
• BETWEEN     TRUE if the operand is within a range
• EXISTS          TRUE if a subquery contains any rows
• IN                    TRUE if the operand is equal to one of a list of expressions.
• LIKE               TRUE if the operand matches a pattern
• NOT                Reverses the value of any other Boolean operator
• OR                  TRUE if either Boolean expression is TRUE
• SOME             TRUE is some of a set of comparisons are TRUE

String Concatenation Operator: The plus sign (+) is the string concatenation operator that enables string concatenation.

<<< Previous                                                                                                                         Next >>>