Snippets

sql

SELECT ROWNUM example

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 |
Following is an example on Oracle server, which would fetch top 3 records from CUSTOMERS table:
SQL> SELECT * FROM CUSTOMERS
WHERE ROWNUM <= 3;
This would produce the following result:

| ID | NAME    | AGE | ADDRESS   | SALARY  |
+----+---------+-----+-----------+---------+
|  1 | Ramesh  |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan  |  25 | Delhi     | 1500.00 |
|  3 | kaushik |  23 | Kota      | 2000.00 |
Read more: SQL - TOP, LIMIT or ROWNUM Clause

ROWNUM Oracle

sql

Operators in The WHERE Clause

Operators in The WHERE Clause

The following operators can be used in the WHERE clause:
Operator Description
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as !=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
Read more: SQL WHERE Clause

WHERE-clause operators

sql

The HAVING Clause

The HAVING Clause

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

SQL HAVING Syntax

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

HAVING-clause GROUP-BY-clause aggregate-functions

sql

SQL HAVING Example

SQL HAVING Example


|OrderID |EmployeeID|ShipperID  |
+--------+----------+-----------+
|10248	 | 5        |	3       |
+--------+----------+-----------+
|10249	 | 6        |	1       |
+--------+----------+-----------+
|10250	 | 4        |	2       |
And a selection from the "Employees" table:

|EmployeeID |LastName |
+-----------+---------+
|1          |Davolio  |
+-----------+---------+
|2          |Fuller   |
+-----------+---------+
|3          |Leverling|
Now we want to find if any of the employees has registered more than 10 orders.
We use the following SQL statement:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM (Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID)
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 10;
Read more: SQL HAVING Clause

COUNT-Function

sql

SQL HAVING Example

SQL HAVING Example


|OrderID |EmployeeID|ShipperID  |
+--------+----------+-----------+
|10248	 | 5        |	3       |
+--------+----------+-----------+
|10249	 | 6        |	1       |
+--------+----------+-----------+
|10250	 | 4        |	2       |
And a selection from the "Employees" table:

|EmployeeID |LastName |
+-----------+---------+
|1          |Davolio  |
+-----------+---------+
|2          |Fuller   |
+-----------+---------+
|3          |Leverling|
Now we want to find if the employees "Davolio" or "Fuller" have registered more than 25 orders.
We add an ordinary WHERE clause to the SQL statement:
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
WHERE LastName='Davolio' OR LastName='Fuller'
GROUP BY LastName
HAVING COUNT(Orders.OrderID) > 25;

sql

SQL DROP INDEX, DROP TABLE, and DROP DATABASE

SQL DROP INDEX, DROP TABLE, and DROP DATABASE

Indexes, tables, and databases can easily be deleted/removed with the DROP statement.
Read more: SQL DROP

sql

The DROP INDEX Statement

The DROP INDEX Statement

The DROP INDEX statement is used to delete an index in a table.
DROP INDEX Syntax for MS Access:
DROP INDEX index_name ON table_name
DROP INDEX Syntax for MS SQL Server:
DROP INDEX table_name.index_name
DROP INDEX Syntax for DB2/Oracle:
DROP INDEX index_name
DROP INDEX Syntax for MySQL:
ALTER TABLE table_name DROP INDEX index_name
Read more: SQL DROP

MySQL SQL-server DROP-statement DROP-INDEX DB2 MS-Access

sql

The DROP TABLE Statement

The DROP TABLE Statement

The DROP TABLE statement is used to delete a table.
DROP TABLE table_name
Read more: SQL DROP

DROP-TABLE

sql

The DROP DATABASE Statement

The DROP DATABASE Statement

The DROP DATABASE statement is used to delete a database.
DROP DATABASE database_name
Read more: SQL DROP

DROP-DATABASE

sql

The TRUNCATE TABLE Statement

The TRUNCATE TABLE Statement

What if we only want to delete the data inside the table, and not the table itself?
Then, use the TRUNCATE TABLE statement:
TRUNCATE TABLE table_name
Read more: SQL DROP

TRUNCATE-TABLE

Loading rules...