The TOP clause is used to specify the number of records to return.
Example: In an employee table needs to return top 10 employee getting highest salary
Query: select top 10 colName from tableName order by salary Desc OR
select top 10 percent colName from tableName order by salary Desc (To know top 10% highest paying employee)
SELECT column_name(s)
FROM table_name
LIMIT number
Example
SELECT *
FROM Persons
LIMIT 5
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number
Example
SELECT *
FROM Persons
WHERE ROWNUM <=5
SQL wildcards can substitute for one or more characters when searching for data in a database.
SQL wildcards must be used with the SQL LIKE operator.
With SQL, the following wildcards can be used:
You can give a table or a column another name by using an alias. This can be a good thing to do if you have very long or complex table names or column names.
An alias name could be anything, but usually it is short.
SELECT column_name(s)
FROM table_name
AS alias_name
SELECT column_name AS alias_name
FROM table_name
Assume we have a table called "Persons" and another table called "Product_Orders". We will give the table aliases of "p" and "po" respectively.
Now we want to list all the orders that "Ola Hansen" is responsible for.
We use the following SELECT statement:
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p,
Product_Orders AS po
WHERE p.LastName='Hansen' AND p.FirstName='Ola'
Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.
JOIN: Return rows when there is at least one match in both tables --> OR say common data in both table
LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
FULL JOIN: Return rows when there is a match in one of the tables
The UNION operator is used to combine the result-set of two or more SELECT statements.
Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order.
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.
SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2
PS: The column names in the result-set of a UNION are always equal to the column names in the first SELECT statement in the UNION.
The SELECT INTO statement selects data from one table and inserts it into a different table.
The SELECT INTO statement is most often used to create backup copies of tables.
We can select all columns into the new table:
SELECT *
INTO new_table_name [IN externaldatabase] -->database name is optional
FROM old_tablename
Or we can select only the columns we want into the new table:
SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename
Example:
SELECT *
INTO Persons_Backup
FROM Persons
We can also use the IN clause to copy the table into another database:
SELECT *
INTO Persons_Backup IN 'Backup.mdb'
FROM Persons
The CREATE DATABASE statement is used to create a database.
CREATE DATABASE database_name
The CREATE TABLE statement is used to create a table in a database.
CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)
Constraints are used to limit the type of data that can go into a table.
Constraints can be specified when a table is created (with the CREATE TABLE statement) or after the table is created (with the ALTER TABLE statement).
We will focus on the following constraints:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
The next chapters will describe each constraint in details.
More..