TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

DBMS SQL Clauses

DBMS SQL Clauses with DBMS Overview, DBMS vs Files System, DBMS Architecture, Three schema Architecture, DBMS Language, DBMS Keys, DBMS Generalization, DBMS Specialization, Relational Model concept, SQL Introduction, Advantage of SQL, DBMS Normalization, Functional Dependency, DBMS Schedule, Concurrency Control etc.

<< Back to DBMS

SQL Clauses

The following are the various SQL clauses:


DBMS SQL Clauses

1. GROUP BY

  • SQL GROUP BY statement is used to arrange identical data into groups. The GROUP BY statement is used with the SQL SELECT statement.
  • The GROUP BY statement follows the WHERE clause in a SELECT statement and precedes the ORDER BY clause.
  • The GROUP BY statement is used with aggregation function.

Syntax

SELECT column
FROM table_name
WHERE conditions 
GROUP BY column
ORDER BY column

Sample table:

PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST
Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Item6 Cpm1 3 25 75
Item7 Com1 5 30 150
Item8 Com1 3 10 30
Item9 Com2 2 25 50
Item10 Com3 4 30 120

Example:

SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST 
GROUP BY COMPANY;

Output:

Com1   5
Com2   3
Com3   2

2. HAVING

  • HAVING clause is used to specify a search condition for a group or an aggregate.
  • Having is used in a GROUP BY clause. If you are not using GROUP BY clause then you can use HAVING function like a WHERE clause.

Syntax:

SELECT column1, column2 
FROM table_name
WHERE conditions 
GROUP BY column1, column2 
HAVING conditions
ORDER BY column1, column2;

Example:

SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST 
GROUP BY COMPANY
HAVING COUNT(*)>2;

Output:

Com1   5
Com2   3

3. ORDER BY

  • The ORDER BY clause sorts the result-set in ascending or descending order.
  • It sorts the records in ascending order by default. DESC keyword is used to sort the records in descending order.

Syntax:

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1, column2... ASC|DESC;

Where

ASC: It is used to sort the result set in ascending order by expression.

DESC: It sorts the result set in descending order by expression.

Example: Sorting Results in Ascending Order

Table:

CUSTOMER

CUSTOMER_ID NAME ADDRESS
12 Kathrin US
23 David Bangkok
34 Alina Dubai
45 John UK
56 Harry US

Enter the following SQL statement:

SELECT *
FROM CUSTOMER
ORDER BY NAME;

Output:

CUSTOMER_ID NAME ADDRESS
34 Alina Dubai
23 David Bangkok
56 Harry US
45 John UK
12 Kathrin US

Example: Sorting Results in Descending Order

Using the above CUSTOMER table

SELECT *
FROM CUSTOMER
ORDER BY NAME DESC;

Output:

CUSTOMER_ID NAME ADDRESS
12 Kathrin US
45 John UK
56 Harry US
23 David Bangkok
34 Alina Dubai

Next TopicDBMS SQL Aggregate function




Related Links:

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf