SQL HAVING Clause

By -

SQL HAVING Clause

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL HAVING Syntax

image

SQL HAVING Example

We have the following "Orders" table:

image

Now we want to find if any of the customers have a total order of less than 2000. We use the following SQL statement:

image

Now we want to find if the customers "Kumari" or "Jensen" have a total order of more than 1500. We add an ordinary WHERE clause to the SQL statement:

image

The result-set will look like this:

image

Comments

Post a Comment

Popular posts from this blog

Concurrency Control:Shadow Paging

By -
Image
Shadow Paging An alternative to log-based crash-recovery techniques is shadow paging . The shadow-paging technique is essentially an improvement on the shadow-copy technique that we saw in Section 15.3. Under certain circumstances, shadow paging may require fewer disk accesses than do the log-based methods discussed previously. There are, however, disadvantages to the shadow-paging approach, as we shall see, that limit its use. For example, it is hard to extend shadow paging to allow multiple transactions to execute concurrently. As before, the database is partitioned into some number of fixed-length blocks, which are referred to as pages . The term page is borrowed from operating systems, since we are using a paging scheme for memory management. Assume that there are n pages, numbered 1 through n . (In practice, n may be in the hundreds of thousands.) These pages do not need to be stored in any particular order on disk (there are many reasons why they do not, as we saw in Chapter 1...
Read more

Choice of Evaluation Plans

By -
Image
Choice of Evaluation Plans Generation of expressions is only part of the query-optimization process, since each operation in the expression can be implemented with different algorithms. An evaluation plan is therefore needed to define exactly what algorithm should be used for each operation, and how the execution of the operations should be coordinated. Figure 14.4 illustrates one possible evaluation plan for the expression from Figure 14.3. As we have seen, several different algorithms can be used for each relational operation, giving rise to alternative evaluation plans. Further, decisions about pipelining have to be made. In the figure, the edges from the selection operations to the merge join operation are marked as pipelined; pipelining is feasible if the selection operations generate their output sorted on the join attributes. They would do so if the indices on branch and account store records with equal values for the index attributes sorted by branch-name . Interaction of Eva...
Read more

Entity-Relationship Model part2

By -
Image
Refining the COMPANY database schema by introducing relationships By examining the requirements, six relationship types are identified. All are binary relationships( degree 2) Listed below with their participating entity types: WORKS_FOR (between EMPLOYEE, DEPARTMENT) MANAGES (also between EMPLOYEE, DEPARTMENT) CONTROLS (between DEPARTMENT, PROJECT) WORKS_ON (between EMPLOYEE, PROJECT) SUPERVISION (between EMPLOYEE (as subordinate), EMPLOYEE (as supervisor)) DEPENDENTS_OF (between EMPLOYEE, DEPENDENT) ER DIAGRAM – Relationship Types are: WORKS_FOR, MANAGES, WORKS_ON, CONTROLS, SUPERVISION, DEPENDENTS_OF R elationship Types In the refined design, some attributes from the initial entity types are refined into relationships: Manager of DEPARTMENT -> MANAGES Works_on of EMPLOYEE -> WORKS_ON Department of EMPLOYEE -> WORKS_FOR etc In general, more than one relationship type can exist between the same participating entity types MANAGES and WORKS_FOR are distinct relationshi...
Read more