Alphalearningschool
        

<<< Prev

Next >>>

Up
SQL Syntax

SQL is followed by a unique set of rules and guidelines called Syntax. This tutorial gives you a quick start with SQL by listing all the basic SQL Syntax.

All the SQL statements start with any of the keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, SHOW and the entire statements end with a semicolon (;).

The most important point to be noted here is that SQL is case insensitive, which means SELECT and select have same meaning in SQL statements. Whereas, MySQL makes difference in table names. So, if you are working with MySQL, then you need to give table names as they exist in the database. 

All the examples given  here have been tested with a MySQL server.

SQL SELECT Statement

SELECT column1, column2....columnN FROM table_name;

SQL DISTINCT Claus

SELECT DISTINCT column1, column2....columnN FROM   table_name;

SQL WHERE Clause

SELECT column1, column2....columnN FROM   table_name WHERE  CONDITION;

SQL AND/OR Clause

SELECT column1, column2....columnN FROM   table_name WHERE  CONDITION-1 {AND|OR} CONDITION-2;

SQL IN Clause

SELECT column1, column2....columnN FROM   table_nameWHERE  column_name IN (val-1, val-2,...val-N);

 SQL BETWEEN Clause

SELECT column1, column2....columnN FROM   table_nameWHERE  column_name BETWEEN val-1 AND val-2;

SQL LIKE Clause

SELECT column1, column2....columnN FROM   table_name WHERE  column_name LIKE { PATTERN };

SQL ORDER BY Clause

SELECT column1, column2....columnN FROM   table_nameWHERE  CONDITIONORDER BY column_name {ASC|DESC};

SQL GROUP BY Clause

SELECT SUM(column_name)FROM   table_nameWHERE  CONDITION GROUP BY column_name;

SQL COUNT Clause

SELECT COUNT(column_name)FROM   table_name WHERE  CONDITION;

 SQL HAVING Clause

SELECT SUM(column_name)FROM   table_name WHERE  CONDITION GROUP BY column_nameHAVING (arithematic function condition);

SQL CREATE TABLE Statement

 CREATE TABLE table_name(column1 datatype,column2 datatype,column3 datatype,.....columnN datatype,PRIMARY KEY( one or more columns ));

SQL DROP TABLE Statement

DROP TABLE table_name;

 SQL CREATE INDEX Statement

CREATE UNIQUE INDEX index_name ON table_name ( column1, column2,...columnN);

 SQL DROP INDEX Statement

ALTER TABLE table_name DROP INDEX index_name;

 SQL DESC Statement

DESC table_name;

SQL TRUNCATE Statement  

TRUNCATE TABLE table_name;

SQL ALTER TABLE Statement

ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_type};

SQL ALTER TABLE Statement (Rename)

ALTER TABLE table_name RENAME TO new_table_name;

SQL INSERT INTO Statement

INSERT INTO table_name (column1, column2....columnN)VALUES (value1, value2....valueN);

 SQL UPDATE Statement

UPDATE table_name SET column1 = value1, column2 = value2....columnN=valueN[ WHERE  CONDITION ];

SQL DELETE Statement

DELETE FROM table_name WHERE  {CONDITION};

SQL CREATE DATABASE Statement

CREATE DATABASE database_name;

SQL DROP DATABASE Statement

DROP DATABASE database_name;

SQL USE Statement

USE database_name;

SQL COMMIT Statement

COMMIT;

SQL ROLLBACK Statement

ROLLBACK;

SQL DATATYPES

A data type is a set of representable values. Every representable   value belongs to at least one data type and some belong to several data types. SQL supports three sorts of data types: predefined data types, constructed types, and user-defined types. 
Predefined data types are sometimes called the "built-in data types", though not in this International Standard. Every predefined data type is a subtype of itself and of no other data types. It follows that every predefined data type is a supertype of itself and of no other data types.

Examples of SQL Data Types:

Literal

Examples

Character string

'59', 'Python'

Numeric

48, 10.34, 2., .001, -125, +5.33333, 2.5 E2, 5E-3

Boolean

TRUE, FALSE, UNKNOWN

Datetime

DATE, '2016-05-14', TIME '04:12:00',TIMESTAMP ‘2016-05-14 10:23:54’

Interval

INTERVAL ‘15-3’ YEAR TO MONTH, INTERVAL ‘23:06:5.5’ HOUR TO SECOND

 

Character String Types:

A character string data type is described by a character string data type descriptor.

Data Type

Description

CHARACTER

Character string, fixed length.
A string of text in an implementer-defined format. The size argument is a single nonnegative integer that refers to the maximum length of the string. Values for this type must enclose in single quotes.

CHARACTER VARYING (VARCHAR)

Variable length character string, maximum length fixed.

CHARACTER LARGE OBJECT (CLOB)

A Character Large OBject (or CLOB) is a collection of character data in a database management system, usually stored in a separate location that is referenced in the table itself.

NATIONAL CHARACTER (NCHAR) 

NATIONAL CHARACTER type is the same as CHARACTER except that it holds standardized multibyte characters or Unicode characters.

NATIONAL CHARACTER VARYING (NCHAR VARYING)

NATIONAL CHARACTER VARYING type is the same as CHARACTER VARYING except that it holds standardized multibyte characters or Unicode characters.

NATIONAL CHARACTER LARGE OBJECT (NCLOB)

NCLOB type is the same as CLOB except that it holds standardized multibyte characters or Unicode characters.

DBMS Character String Types: 

  DBMS and version

Types

MySQL 5.7

CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET

PostgreSQL 9.5.3

CHARACTER VARYING(n), VARCHAR(n), CHARACTER(n), CHAR(n), TEXT

SQL Server

CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, NTEXT

Oracle 11g

CHAR, VARCHAR2, VARCHAR, NCHAR, NVARCHAR2, CLOB, NCLOB, LONG

 

Boolean Type:

The data type boolean comprises the distinct truth values true and false .The boolean data type also supports the unknown truth value as the null value unless prohibited by a NOT NULL constraint. 

Data Type

Description

BOOLEAN

Stores truth values - either TRUE or FALSE.

 

Example : A table using boolean type

CREATE TABLE test (  

id  DECIMAL PRIMARY KEY,  

col1 BOOLEAN

);

DBMS Boolean Types:

DBMS and version

Types

MySQL 5.7

TINYINT(1)

PostgreSQL 9.5

BOOLEAN

SQL Server 2014

BIT

Oracle 11g

BOOLEAN

 

Binary large object Type:

A binary string is a sequence of octets that does not have either a character set or collation associated with it and is described by a binary data type descriptor 

Data Type

Description

BINARY LARGE OBJECT (BLOB).

BLOB stores a long sequence of bytes.

 

Example : A table with columns of fixed and variable  size binary data and a BLOB

CREATE TABLE test (  

id    DECIMAL PRIMARY KEY,  

col1   BINARY(8),      -- exactly 8 byte 

col2   VARBINARY(140), -- up to 140 byte  

col3   BLOB            -- very large data: store picture, sound ...

);

 

DBMS Binary Types:

DBMS and version

Types

MySQL 5.7

TINYBLOB, BLOB, MEDIUMBLOB, LONGBLOB

PostgreSQL 9.5

BYTEA

SQL Server 2014

BLOB

Oracle 11g

LOB

 

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.

  • Arithmetic operators
  • Comparison operators
  • Logical operators
  • Operators used to negate conditions 

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 (%)
  • The underscore (_)

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.