The keyword DISTINCT is used to remove duplicate values from the resulting list. However, in the original version of SEQUEL, the removal of duplicates was automatic, ie., it need not be specified explicitly.
Consider the following query block:
SELECT DISTINCT job_status
The list shown below will be displayed as the output:
The rows can be selected from a table by specifying the range of values of a column, both implicitly and explicitly.
Example: We can specify an implicit range as follows:
WHERE e_no BETWEEN 5 AND 10
The query returns all rows corresponding to the employees whose e_nos are between 5 and 10.
Rows from a table may be displayed by any specified sorting sequence. The sorting sequence may be specified based on the order of the values of a single or multiple columns of the table.
Example: the following query block displays the numbers and the names of the employees, arranging employee numbers in an ascending order.
SELECT e_no, e_name
ORDER BY e_no;
The column whose values are to be arranged in order must be specified in the ORDER BY clause.
Ascending order is the default. To arrange in descending order, the key word DESC may be used in the ORDER BY clause as follows:
Insert and Update Commands
A new row(tuple) can be inserted into a table as follows:
INSERT INTO projects
VALUES ('CS201', COMPLIER', 3);
If the list of columns is not specified along with the table (where the row is being inserted) , then the value must be listed for every column in the table in the same sequence in which the columns appeared during the creation of the table by CREATE TABLE clause. The INSERT clause may be used to copy rows with the same or less number of columns of a table into another table. For example,
INSERT INTO account (e_no, e_name, basic, DA, GROSS)
SELECT e_no, e_name, basic_salary, basic_salary * 0.29.
Basic_salary * 1.29)
Values of any number of columns can be updated by using the UPDATE clause. The general syntax of the UPDATE clause as follows:
UPDATE <table name>
SET <column name> = <expression>,
<column name> = <expression>,
<column name> = <expression>
UPDATE operates on all the rows that meet the WHERE clause conditions. If the WHERE clause is omitted, all the rows will be updated.
Built in Functions
Most of the versions of SQL which are now commercially available support a set of built - in functions for string manipulation and numeric computation. The character string and numeric functions may be used in SELECT, WHERE and ORDER BY clauses.
When several rows have a common value in one or more columns, they can be treated in SQL as one group by using the GROUP BY clause.
After grouping the rows in a table based on the common values across the rows, summary statistics can easily be computed for each such group present in the table.
HAVING Clause for selecting of the Groups
After the grouping has been done, the HAVING clause can be used to retrieve data from some of the groups selected according to the condition specified in the HAVING clause.
The syntax of the delete statement is as follows:
DELETE FROM <table name>
The above statement can be used to delete those rows of a table which meet the condition stated in the WHERE clause. If the WHERE clause is omitted, then all the rows of the table will be deleted.