quesries in dbms


 

The SQL HAVING syntax

The general syntax is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE condition
  4. GROUP BY columnnames
  5. HAVING condition

The general syntax with ORDER BY is:

  1. SELECT columnnames
  2. FROM tablename
  3. WHERE condition
  4. GROUP BY columnnames
  5. HAVING condition
  6. ORDER BY columnnames

 


 

SQL GROUP BY Examples

Problem: List the number of customers in each country. Only include countries with more than 10 customers.

  1. SELECT COUNT(Id), Country
  2. FROM Customer
  3. GROUP BY Country
  4. HAVING COUNT(Id) > 10

Results: 3 records

Count Country
11 india
11 Germany
13 USA

 


Problem: List the number of customers in each country, except the USA, sorted high to low.
Only include countries with 9 or more customers.

  1. SELECT COUNT(Id), Country
  2. FROM Customer
  3. WHERE Country <> ‘USA’
  4. GROUP BY Country
  5. HAVING COUNT(Id) >= 9
  6. ORDER BY COUNT(Id) DESC

Results: 3 records

Count Country
11 France
11 Germany
9 Brazil

 


Problem: List all customer with average orders between $1000 and $1200.

  1. SELECT AVG(TotalAmount), FirstName, LastName
  2. FROM [Order] O JOIN Customer C ON O.CustomerId = C.Id
  3. GROUP BY FirstName, LastName
  4. HAVING AVG(TotalAmount) BETWEEN 1000 AND 1200

Results: 10 records

Average FirstName LastName
1081.215000 nikhil arora
1063.420000 aman singh
1008.440000 Alexander Feuer
1062.038461 Thomas Hardy
1107.806666 Pirkko Koskitalo
1174.945454 Janete Limeira
1073.621428 Antonio Moreno
1065.385000 Rita Müller
1183.010000 José Pedro Freyre
1057.386666 Carine Schmitt

 

Like clause

Like clause is used as condition in SQL query. Like clause compares data with an expression using wildcard operators. It is used to find similar data from the table.


Wildcard operators

There are two wildcard operators that are used in like clause.

  • Percent sign % : represents zero, one or more than one character.
  • Underscore sign _ : represents only one character.

Example of LIKE clause

Consider the following Student table.

s_id s_Name age
101 Adam 15
102 Alex 18
103 Abhi 17
SELECT * from Student where s_name like 'A%';

The above query will return all records where s_name starts with character ‘A’.

s_id s_Name age
101 Adam 15
102 Alex 18
103 Abhi 17

Example

SELECT * from Student where s_name like '_d%';

The above query will return all records from Student table where s_name contain ‘d’ as second character.

s_id s_Name age
101 Adam 15

Example

SELECT * from Student where s_name like '%x';

The above query will return all records from Student table where s_name contain ‘x’ as last character.

s_id s_Name age
102 Alex 18

 

Distinct keyword

The distinct keyword is used with Select statement to retrieve unique values from the table. Distinct removes all the duplicate records while retrieving from database.


Syntax for DISTINCT Keyword

SELECT distinct column-name from table-name;

Example

Consider the following Emp table.

eid name age salary
401 Anu 22 5000
402 Shane 29 8000
403 Rohan 34 10000
404 Scott 44 10000
405 Tiger 35 8000
select distinct salary from Emp;

The above query will return only the unique salary from Emp table

salary
5000
8000
10000

 

AND & OR operator

AND and OR operators are used with Where clause to make more precise conditions for fetching data from database by combining more than one condition together.


AND operator

AND operator is used to set multiple conditions with Where clause.


Example of AND

Consider the following Emp table

eid name age salary
401 Anu 22 5000
402 Shane 29 8000
403 Rohan 34 12000
404 Scott 44 10000
405 Tiger 35 9000
SELECT * from Emp WHERE salary < 10000 AND age > 25

The above query will return records where salary is less than 10000 and age greater than 25.

eid name age salary
402 Shane 29 8000
405 Tiger 35 9000

OR operator

OR operator is also used to combine multiple conditions with Where clause. The only difference between AND and OR is their behaviour. When we use AND to combine two or more than two conditions, records satisfying all the condition will be in the result. But in case of OR, atleast one condition from the conditions specified must be satisfied by any record to be in the result.


Example of OR

Consider the following Emp table

eid name age salary
401 Anu 22 5000
402 Shane 29 8000
403 Rohan 34 12000
404 Scott 44 10000
405 Tiger 35 9000
SELECT * from Emp WHERE salary > 10000 OR age > 25

The above query will return records where either salary is greater than 10000 or age greater than 25.

402 Shane 29 8000
403 Rohan 34 12000
404 Scott 44 10000
405 Tiger 35 9000