| 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
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 |
WHERE-clause operators
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
|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.
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
|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.
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;
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-INDEX DB2 MS-Access
DROP TABLE table_name
Read more: SQL DROP
DROP-statement DROP-TABLE
DROP DATABASE database_name
Read more: SQL DROP
DROP-DATABASE
TRUNCATE TABLE table_name
Read more: SQL DROP
TRUNCATE-TABLE