Login


Forgot password?

SQL Description


SQL-Structured Query Language

SQL is a language that helps us to communicate to the database in the form of queries. Using this, we can store, manipulate and retrieve data in databases.

Example:

SELECT * from Students;

What Can SQL do?

About Database Management System (DBMS):

Responsible for all types of operations such as retrieving the data, storing the data, modifying the data and removal of data. It acts as an intermediate between the user and the database.

Types of DBMS:

  1. HDBMS 2. NDBMS  3. RDBMS

HDBMS - Hierarchical Database Management System

 In this, all the data are stored in a tree structure. In this, insertion was easy but retrieving data was difficult.

NDBMS – Network Database Management System

 In this, all the data are stored in a tree structure and all the data are interconnected with respect to NDBS retrieving was easy but insertion was difficult.

 RDBMS – Relational Database Management System

 With respect to RDBMS, all the data are stored in tables and those tables may have some relation. In this insertion and retrieving the data, modifying the data and removal of data was easy.

Sub languages of SQL:

>> Data Query Language

>> Data Definition Language

>> Data Manipulation Language

>> Data Transition Language

>> Data Control Language

Numeric Types:

Numeric data types represent numerical values.

 

Data Type

Description

INTEGER

Represents an integer. The minimum and maximum values depend on the DBMS.

SMALLINT

Same as INTEGER type except that it might hold a smaller range of values, depending on the DBMS.

BIGINT

Same as INTEGER type except that it might hold a larger range of values, depending on the DBMS.

DECIMAL(p, s)

Exact numerical, precision p, scale s. A decimal number that is a number that can have a decimal point in it. The size argument has two parts: precision and scale. The scale cannot exceed the precision. Precision comes first, and a comma must separate from the scale argument.

NUMERIC(p, s)

Exact numerical, precision p, scale s. The maximum precision depends on the DBMS.

FLOAT(p)

Approximate numerical, mantissa precision p. Precision is greater than or equal to 1 and the maximum precision depends on the DBMS.

REAL

Same as FLOAT type except that the DBMS defines the precision.

DOUBLE PRECISION

Same as FLOAT type (DBMS defines the precision) but greater than that of REAL.

Example : A table using numeric data types

CREATE TABLE test ( 

  id    DECIMAL PRIMARY KEY,  

name   VARCHAR(100),     -- up to 100 characters c

ol1   DECIMAL (5,2),    -- three digits before the decimal and two behind 

col2   SMALLINT,         -- no decimal point 

col3   INTEGER,          -- no decimal point  

col4   BIGINT,           -- no decimal point.

col5   FLOAT (2),        -- two or more digits after the decimal place  

col6   REAL,  

col7   DOUBLE PRECISION

);

DBMS Numeric Types: 

DBMS and version

Types

MySQL 5.7

INTEGER(TINYINT, SMALLINT, MEDIUMINT, INT BIGINT, INTEGER)

FIXED-POINT(DECIMAL, NUMERIC)

FLOATING-POINT(FLOAT, DOUBLE)

BIT-VALUE(BIT),

PostgreSQL 9.5.3

SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, DOUBLE PRECISION, SMALLSERIAL, SERIAL, BIGSERIAL

SQL Server 2014

EXACT NUMERICS(BIGINT, BIT, DECIMAL, INT, MONEY, NUMERIC, SMALLINT, SMALLMONEY, TINYINT)

APPROXIMATE NUMERICS(FLOAT, REAL )

Oracle 11g

NUMBER 

FLOATING-POINT(BINARY_FLOAT, BINARY_DOUBLE)

 

Datetime Types:

The datetime data types represent the date and time of day. 

Data Type

Description

DATE

Represents a date. Format : yyyy-mm-dd

TIME WITHOUT TIME ZONE

Represents a time of day without time zone. Format : hh:mm:ss

TIME WITH TIME ZONE

Represents a time of day with time zone. Format : yyyy-mm-dd AT TIME ZONE -06:00.

TIMESTAMP WITHOUT TIME ZONE

Represents a combination of DATE and TIME values separated by a space. Format : yyyy-mm-dd hh:mm:ss

TIMESTAMP WITH TIME ZONE

Represents a combination of DATE and TIME values separated by a space with time zone. Format : yyyy-mm-dd hh:mm:ss AT TIME ZON

 

Example : A table using Datetime data types

CREATE TABLE test (  

id   DECIMAL PRIMARY KEY,  

col1 DATE,    -- store year, month and day (Oracle: plus hour, minute and seconds)  

col2 TIME,  

col3 TIMESTAMP(9), -- a timestamp with 9 digits after the decimal of seconds  

col4 TIMESTAMP WITH TIME ZONE   -- a timestamp including the name of a timezone  

);  

DBMS Date and Time Types: 

DBMS and version

Types

MySQL 5.7

DATE, TIME, DATETIME, TIMESTAMP, YEAR

PostgreSQL 9.5.3

TIMESTAMP [ WITHOUT TIME ZONE ], TIMESTAMP WITH TIME ZONE, DATE, TIME[ WITHOUT TIME ZONE ], TIME WITH TIME ZONE,

SQL Server

DATE, DATETIME2, DATETIME, DATETIMEOFFSET, SMALLDATETIME, TIME

Oracle 11g

DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

 

Interval Type:

It stores the quantity of time between two datetime values. For example between 10:00 and 12:30 is an interval of 02:30 (2 hours and 30 minutes). There are two classes of intervals. The first one called year-month intervals, includes no fields other than YEAR and MONTH, though not both are required. The other class called day-time intervals that can include any fields other than YEAR or MONTH.

Data Type

Description

Year-month

Includes no fields other than YEAR and MONTH, though not both are required.

Day-time

Includes any fields other than YEAR or MONTH. These intervals can contain a day value, hour value, minute value, second value, or some combination thereof.

 

Example : A table using Interval data types

CREATE TABLE test (  

id    DECIMAL PRIMARY KEY,  

col1 INTERVAL YEAR TO MONTH,  

col2 INTERVAL DAY TO SECOND(6)   -- an interval with 6 digits after the decimal of seconds 

);

DBMS Interval Types:

DBMS and version

Types

MySQL 5.7

NOT SUPPORTED

PostgreSQL 9.5

INTERVAL

SQL Server 2014

NOT SUPPORTED

Oracle 11g

INTERVAL YEAR TO MONTH

INTERVAL DAY TO SECOND

 

SQL OPERATORS

An operator is a reserved word or a character used primarily in an SQL statement's WHERE clause to perform operation(s), such as comparisons and arithmetic operations. These Operators are used to specify conditions in an SQL statement and to serve as conjunctions for multiple conditions in a statement.

SQL Arithmetic Operators 

Assume 'variable a' holds 10 and 'variable b' holds 20. 

Operator

Description

Example

+ (Addition)

Adds values on either side of the operator.

a + b will give 30

- (Subtraction)

Subtracts right hand operand from left hand operand.

a - b will give -10

* (Multiplication)

Multiplies values on either side of the operator.

a * b will give 200

/ (Division)

Divides left hand operand by right hand operand.

b / a will give 2

% (Modulus)

Divides left hand operand by right hand operand and returns remainder.

b % a will give 0

 

SQL Comparison Operators 

Assume 'variable a' holds 10 and 'variable b' holds 20.

Operator

Description

Example

=

Checks if the values of two operands are equal or not, if yes then condition becomes true.

(a = b) is not true.

!=

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.

(a != b) is true.

<> 

Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.

(a <> b) is true.

Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.

(a > b) is not true.

Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.

(a < b) is true.

>=

Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.

(a >= b) is not true.

<=

Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.

(a <= b) is true.

!<

Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.

(a !< b) is false.

!>

Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

(a !> b) is true.

SQL Logical Operators

Here is a list of all the logical operators available in SQL. 

Sr.No.

Operator & Description

1

ALL

The ALL operator is used to compare a value to all values in another value set.

2

AND

The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.

3

ANY

The ANY operator is used to compare a value to any applicable value in the list as per the condition.

4

BETWEEN

The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.

5

EXISTS

The EXISTS operator is used to search for the presence of a row in a specified table that meets a certain criterion.

6

IN

The IN operator is used to compare a value to a list of literal values that have been specified.

7

LIKE

The LIKE operator is used to compare a value to similar values using wildcard operators.

8

NOT

The NOT operator reverses the meaning of the logical operator with which it is used. Eg: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.

9

OR

The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause.

10

IS NULL

The NULL operator is used to compare a value with a NULL value.

11

UNIQUE

The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

SQL CREATE Database 

The SQL CREATE DATABASE statement is used to create a new SQL database.

Syntax

The basic syntax of this CREATE DATABASE statement is as follows − 

CREATE DATABASE DatabaseName;

Always the database name should be unique within the RDBMS.

Example

If you want to create a new database <testDB>, then the CREATE DATABASE statement would be as shown below − 

SQL> CREATE DATABASE testDB; 

SQL DROP or DELETE Database 

The SQL DROP DATABASE statement is used to drop an existing database in SQL schema.

Syntax

The basic syntax of DROP DATABASE statement is as follows − 

DROP DATABASE DatabaseName;

Always the database name should be unique within the RDBMS.

Example

If you want to delete an existing database <testDB>, then the DROP DATABASE statement would be as shown below − 

SQL> DROP DATABASE testDB;

NOTE − Be careful before using this operation because by deleting an existing database would result in loss of complete information stored in the database.

SQL - SELECT Database, USE Statement

When you have multiple databases in your SQL Schema, then before starting your operation, you would need to select a database where all the operations would be performed.

The SQL USE statement is used to select any existing database in the SQL schema.

Syntax

The basic syntax of the USE statement is as shown below − 

USE DatabaseName;

Always the database name should be unique within the RDBMS.\

You can check the available databases as shown below −

SQL> SHOW DATABASES;

+---------------------------+

| Database                      |

+---------------------------+

| information_schema   |

| AMROOD                   |

| TUTORIALSPOINT    |

| mysql                          |

| orig                             |

| test                             |

+---------------------------+

6 rows in set (0.00 sec)

Now, if you want to work with the AMROOD database, then you can execute the following SQL command and start working with the AMROOD database. 

SQL> USE AMROOD;

 Creating a basic table involves naming the table and defining its columns and each column's data type.

The SQL CREATE TABLE statement is used to create a new table.

Syntax

The basic syntax of the CREATE TABLE statement is as follows − 

CREATE TABLE table_name(

   column1 datatype,

   column2 datatype,

   column3 datatype,

   .....

   columnN datatype,

   PRIMARY KEY( one or more columns )

);

CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.

Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with the following example.

A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. You can check the complete details at Create Table Using another Table

Example

The following code block is an example, which creates a CUSTOMERS table with an ID as a primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table − 

SQL> CREATE TABLE CUSTOMERS(

   ID   INT   NOT NULL,

   NAME VARCHAR (20)  NOT NULL,

   AGE  INT   NOT NULL,

   ADDRESS  CHAR (25) ,

   SALARY   DECIMAL (18, 2),      

   PRIMARY KEY (ID)

);

You can verify if your table has been created successfully by looking at the message displayed by the SQL server, otherwise you can use the DESC command as follows −

 

SQL - CREATE Table

Creating a basic table involves naming the table and defining its columns and each column's data type.

The SQL CREATE TABLE statement is used to create a new table.

Syntax

The basic syntax of the CREATE TABLE statement is as follows −

CREATE TABLE table_name(

   column1 datatype,

   column2 datatype,

   column3 datatype,

   .....

   columnN datatype,

   PRIMARY KEY( one or more columns )

);

CREATE TABLE is the keyword telling the database system what you want to do. In this case, you want to create a new table. The unique name or identifier for the table follows the CREATE TABLE statement.

Then in brackets comes the list defining each column in the table and what sort of data type it is. The syntax becomes clearer with the following example.

A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement. You can check the complete details at Create Table Using another Table.

Example

The following code block is an example, which creates a CUSTOMERS table with an ID as a primary key and NOT NULL are the constraints showing that these fields cannot be NULL while creating records in this table −

SQL> CREATE TABLE CUSTOMERS(

   ID   INT              NOT NULL,

   NAME VARCHAR (20)     NOT NULL,

   AGE  INT              NOT NULL,

   ADDRESS  CHAR (25) ,

   SALARY   DECIMAL (18, 2),      

   PRIMARY KEY (ID)

);

You can verify if your table has been created successfully by looking at the message displayed by the SQL server, otherwise you can use the DESC command as follows –

Now, you have CUSTOMERS table available in your database which you can use to store the required information related to customers.

SQL - DROP or DELETE Table

The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.

NOTE − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.

Syntax

The basic syntax of this DROP TABLE statement is as follows −

DROP TABLE table_name;

 Example

Let us firstverify the CUSTOMERS table and then we will delete it from the database as shown below –

This means that the CUSTOMERS table is available in the database, so let us now drop it as shown below

SQL> DROP TABLE CUSTOMERS;   

Query
OK, 0 rows affected (0.01 sec)

 Now, if you would try the DESC command, then you will get the following error −

SQL>  DESC CUSTOMERS;­­­­ERROR 1146 (42S02):

Table
'TEST.CUSTOMERS' doesn't exist

 Here, TEST is the database name which we are using for our examples.

 SQL - INSERT Query

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

Syntax

There are two basic syntaxes of the INSERT INTO statement which are shown below.

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  VALUES (value1, value2, value3,...valueN);

 Here, column1, column2, column3,...columnN are the names of the columns in the table into which you want to insert the data.

The SQL INSERT INTO syntax will be as follows −

INSERT INTO TABLE_NAME VALUES (value1, value2,value3,...valueN);

Example

The following statements would create three records in the CUSTOMERS table. 

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)VALUES (1, 'Chithra', 23, 'Bangalore', 20000.00); 

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)VALUES (2, 'Chethan', 25, 'Kolar', 15000.00); 

INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES (3, 'Madhusudhan', 28, 'Mysore', 25000.00);

 You can create a record in the CUSTOMERS table by using the second syntax as shown below.

INSERT INTO CUSTOMERS VALUES (4, 'Vishruthi,’25’ 'Mumbai', 65000.00 );

 All the above statements would produce the following records in the CUSTOMERS table as shown below.

 

SQL - SELECT Query 

The SQL SELECT statement is used to fetch the data from a database table which returns this data in the form of a result table. These result tables are called result-sets.

Syntax

The basic syntax of the SELECT statement is as follows − 

SELECT column1, column2, columnN FROM table_name;

 Here, column1, column2... are the fields of a table whose values you want to fetch. If you want to fetch all the fields available in the field, then you can use the following syntax.

SELECT * FROM table_name;

 Example

Consider the CUSTOMERS table having the following records –

The following code is an example, which would fetch the ID, Name and Salary fields of the customers available in CUSTOMERS table.

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS;

This would produce the following result −

If you  want to fetch all the fields of the CUSTOMERS table, then you should   use the following query.

SQL> SELECT * FROM CUSTOMERS;

 This would produce the result as shown below.

 

SQL - WHERE Clause

The SQL WHERE clause is used to specify a condition while fetching the data from a single table or by joining with multiple tables. If the given condition is satisfied, then only it returns a specific value from the table. You should use the WHERE clause to filter the records and fetching only the necessary records. 

Syntax

The basic syntax of the SELECT statement with the WHERE clause is as shown below.

SELECT column1, column2, columnN  FROM table_name WHERE [condition]

 We can specify a condition using the comparison or logical operators like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear.

 Example

Consider the CUSTOMERS table having the following records –

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000;

 This would produce the following result –


+----+----------+-------------+
| ID | NAME   | SALARY|
+----+----------+-------------+
|  4 | Chaitali  |  6500.00 |
|  5 | Hardik   |  8500.00 |
|  6 | Komal   |  4500.00 |
|  7 | Muffy    | 10000.00 |
+----+----------+------------+

 

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS  WHERE NAME = 'Hardik';

 This would produce the following result −

+----+----------+---------------+
| ID | NAME     | SALARY   |
+----+----------+---------------+
|  5 | Hardik   |  8500.00    |
+----+----------+--------------+

SQL - AND and OR  Operators 

The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called as the conjunctive operators.

These operators provide a means to make multiple comparisons with different operators in the same SQL statement. 

The AND Operator

The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.

Syntax

The basic syntax of the AND operator with a WHERE clause is as follows −

SELECT column1, column2, columnN FROM table_nameWHERE [condition1] AND [condition2]...AND [conditionN];

 Example 

Consider the CUSTOMERS table having the following records −

Following is an example, which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 and the age is less than 25 years −

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 AND age < 25;

 This would produce the following result −

+----+-------+----------+
| ID | NAME  | SALARY   |
+----+-------+----------+
|  6 | Komal |  4500.00 |
|  7 | Muffy | 10000.00 |
+----+-------+----------+

The  OR  Operator

The OR operator is used to combine multiple conditions in an SQL statement's WHERE clause. 

Syntax

The basic syntax of the OR operator with a WHERE clause is as follows − 

SELECT column1, column2, columnN FROM table_name WHERE [condition1] OR [condition2]...OR [conditionN]

 You can combine N number of conditions using the OR operator. For an action to be taken by the SQL statement, whether it be a transaction or query, the only any ONE of the conditions separated by the OR must be TRUE.

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
|
ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+

|
  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|
  2 | Khilan   |  25 | Delhi     |  1500.00 |
|
  3 | kaushik  |  23 | Kota      |  2000.00 |
|
  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|
  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|
  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following code block has a query, which would fetch the ID, Name and Salary fields from the CUSTOMERS table, where the salary is greater than 2000 OR the age is less than 25 years.

SQL> SELECT ID, NAME, SALARY FROM CUSTOMERS WHERE SALARY > 2000 OR age < 25;

 This would produce the following result −

+----+----------+----------+
| ID | NAME     | SALARY   |

+----+----------+----------+

|  3 | kaushik  |  2000.00 |

|  4 | Chaitali |  6500.00 |

|  5 | Hardik   |  8500.00 |

|  6 | Komal    |  4500.00 |

|  7 | Muffy    | 10000.00 |

+----+----------+----------+

SQL - LIKE Clause

The SQL LIKE clause is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction with the LIKE operator.

The percent sign represents zero, one or multiple characters. The underscore represents a single number or character. These symbols can be used in combinations.

Syntax  

The basic syntax of % and _ is as follows − 

SELECT FROM table_name WHERE column LIKE 'XXXX%' 
or
SELECT FROM table_name WHERE column LIKE '%XXXX%' 
or
SELECT FROM table_nameWHERE column LIKE 'XXXX_' 
or
SELECT FROM table_name WHERE column LIKE '_XXXX' or SELECT FROM table_name WHERE column LIKE '_XXXX_'

 

SQL - Having Clause

The HAVING Clause enables you to specify conditions that filter which group results appear in the results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax

The following code block shows the position of the HAVING Clause in a query. 

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

 The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following code block has the syntax of the SELECT statement including the HAVING clause –

SELECT column1, column2 FROM table1, table2 WHERE [ conditions]GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2

 Example

Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+

 The HAVING Clause enables you to specify conditions that filter which group results appear in the results.

The WHERE clause places conditions on the selected columns, whereas the HAVING clause places conditions on groups created by the GROUP BY clause.

Syntax

The following code block shows the position of the HAVING Clause in a query.

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

 The HAVING clause must follow the GROUP BY clause in a query and must also precede the ORDER BY clause if used. The following code block has the syntax of the SELECT statement including the HAVING clause −

SELECT column1, column2 FROM table1, table2 WHERE [ conditions ]GROUP BY column1, column2 HAVING [ conditions ] ORDER BY column1, column2

 Example

Consider the CUSTOMERS table having the following records.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+

|
  1 | Ramesh    |   32 | Ahmedabad  |   2000.00 |
|   2 | Khilan    |   25 | Delhi      |   1500.00 |
|   3 | kaushik   |   23 | Kota       |   2000.00 |
|   4 | Chaitali  |   25 | Mumbai     |   6500.00 |
|   5 | Hardik    |   27 | Bhopal     |   8500.00 |
|   6 | Komal     |   22 | MP        |   4500.00 |

Following is an example, which would display a record for a similar age count that would be more than or equal to 2.

SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS GROUP BY age HAVING COUNT(age) >= 2;

 This would produce the following result −

+----+--------+-----+---------+---------+
| ID | NAME   | AGE | ADDRESS | SALARY  |
+----+--------+-----+---------+---------+

|  2 | Khilan |  25 | Delhi   | 1500.00 |

+----+--------+-----+---------+---------+

 Following is an example, which would display a record for a similar age count that would be more than or equal to 2.

SQL > SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS GROUP BY age HAVING COUNT(age) >= 2;

 This would produce the following result −

+----+--------+-----+---------+-------------+
| ID | NAME   | AGE | ADDRESS | SALARY  |
+----+--------+-----+---------+---------+

|  2| Khilan |  25 | Delhi   | 1500.00  |

+----+--------+-----+---------+---------+

 

SQL - ORDER BY Clause 

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

Syntax

The basic syntax of the ORDER BY clause is as follows − 

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];

 Example

Consider the CUSTOMERS table having the following records − 

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

The following code block has an example, which would sort the result in an ascending order by the NAME and the SALARY −

SQL> SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;

This would produce the following result − 

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

+----+----------+-----+-----------+----------+

 The following code block has an example, which would sort the result in the descending order by NAME.

SQL> SELECT * FROM CUSTOMERS ORDER BY NAME DESC;

 This would produce the following result −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

+----+----------+-----+-----------+----------+

 SQL - Group By 

The SQL ORDER BY clause is used to sort the data in ascending or descending order, based on one or more columns. Some databases sort the query results in an ascending order by default.

Syntax

The basic syntax of the ORDER BY clause is as follows –

SELECT column-list FROM table_name [WHERE condition] [ORDER BY column1, column2, .. columnN] [ASC | DESC];

Example

Consider the CUSTOMERS table having the following records −

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |

+----+----------+-----+-----------+----------+

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

+----+----------+-----+-----------+----------+

The following code block has an example, which would sort the result in an ascending order by the NAME and the SALARY −

SQL> SELECT * FROM CUSTOMERS ORDER BY NAME, SALARY;

This would produce the following result –  

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+

|  4 | Chaitali |  25 | Mumbai    |  6500.00 |

|  5 | Hardik   |  27 | Bhopal    |  8500.00 |

|  3 | kaushik  |  23 | Kota      |  2000.00 |

|  2 | Khilan   |  25 | Delhi     |  1500.00 |

|  6 | Komal    |  22 | MP        |  4500.00 |

|  7 | Muffy    |  24 | Indore    | 10000.00 |

|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |

+----+----------+-----+-----------+----------+

SQL Select DISTINCT

In SQL, the DISTINCT keyword is used in the SELECT statement to retrieve unique values from a database table. Any value that has a duplicate will only show up once.

Syntax

SELECT DISTINCT "column_name FROM "table_name";

"table_name" is the name of the table where data is stored, and "column_name" is the name of the column containing the data to be retrieved.

SQL SELECT COUNT 

The COUNT function in SQL is used to calculate the number of rows returned from the SQL statement.

Syntax

The syntax for the COUNT function is,

SELECT COUNT (<expression>)
FROM "table_name";

 <expression> can be a column name, an arithmetic operation, or a star (*). When we use COUNT(*), we mean "count everything."

It is also possible to have one or more columns in addition to the COUNT function in the SELECT statement. In those cases, these columns need to be part of the GROUP BY clause as well:

SELECT "column_name1", "column_name2", ... "column_nameN", COUNT (<expression>)
FROM "table_name";
GROUP BY "column_name1", "column_name2", ... "column_nameN";

COUNT is often combined with DISTINCT to calculate the number of unique values. The syntax for this is as follows:

SELECT COUNT (DISTINCT <expression>)
FROM­­ "table_name";

Example 1: Simple COUNT  ­­­­­­­­­Operation

To find the number of rows in this table, we key in,

SELECT COUNT(Store_Name)
FROM Store_Information;

 Result:

4

mysql> select count(dept) from employee;

+-------------+

| count(dept) |

+-------------+

|           5 |

+-------------+

1 row in set (0.00 sec)

SQL SELECT TOP

The TOP keyword restricts the number of results returned from a SQL statement in Microsoft SQL Server.

Syntax

The syntax for TOP is as follows:

SELECT TOP [TOP argument] "column_name"
FROM "table_name";

where [TOP argument] can be one of two possible types:

  1. [N]: The first Nrecords are returned.
  2. [M] PERCENT: The number of records corresponding to M%of all qualifying records are returned.

SQL SELECT NULL 

In SQL, NULL means that data does not exist. NULL does not equal to 0 or an empty string. Both 0 and empty string represent a value, while NULL has no value.

Any mathematical operations performed on NULL will result in NULL. For example,

10 + NULL = NULL

Aggregate functions such as SUMCOUNTAVG,&nbspMAX, and&nbspMIN exclude NULL values. This is not likely to cause any issues for SUMMAX, and MIN. However, this can lead to confusion with AVG and COUNT.