SQL QURIES
NOTE: Use execute SQL STATEMENT WINDOW for creation, updation,
deletion and insertion in table of OPEN OFFICE BASE software
1. CREATE TABLE command
Tables are defined with the CREATE TABLE
command. When a table is created, its columns are
named, data types & sizes are supplied for each
column.
Syntax:
CREATE TABLE <table name>(<column
name><data type>[(<size>)],<column name><data
type>[(<size>)],…………);
FOR OPEN OFFICE BASE SOFTWARE
CREATE TABLE "DEMO"
( "ROLLNO" NUMERIC(12),
"NAME" VARCHAR(20),
"FEE" FLOAT(4) );
Entering values
INSERT command :
The rows are added in a relation using insert
command.
Syntax: insert into <table name>
values(<value>,<value>…….);
FOR OPEN OFFICE BASE SOFTWARE
INSERT INTO STU (ID,ROLLNO,SNAME)
VALUES(2,2,'ROB');
The order of values matches the order of columns in insert
command. The columns that are not listed in the insert
command will have their default value, if it is defined for
them, otherwise NULL value.
2. SELECT command
It is a DML command used to query or obtain
information from a table. The SELECT command
can be used with various clauses.
Syntax:
SELECT <column name>[<column name>,…….]
from <table name>;
If we want to see the entire table i.e. every column of
a table, we need not to give a complete list of columns.
The aestrick(*) can be substituted for a complete list
of columns as follows:
SELECT * from emp;
SELECT EMPNAME FROM EMPINFO;
If we want to see some columns of table emp, the
select commnd can be used as follows:
SELECT empno, ename,job from emp;
Note: The order of selection determines the order of
display.
Sorting Results-ORDER BY clause
The ORDER BY clause is used to display the rows of
a table in ascending or descending order of the values
of a field. The data in the table is not sorted by the
ORDER BY command, but rather the data is
displayed in a particular sorted order. The default
order is ascending.
Syntax:
SELECT <column name>, <column name>……
FROM <table name> [WHERE<condition>][ORDER
BY <column name>];
Eg. SELECT * FROM emp ORDER BY ename;
To display the list of employees having salary
more than 2500 in the alphabetical order of their
names, the command would be:
SELECT empno,ename,job FROM emp WHERE
sal>2500 ORDER BY ename;
To display the list of employees in the descending
order of employee number, the command would
be:
SELECT * FROM emp ORDER BY empno DESC;
OR
SELECT * FROM EMPINFO ORDER BY EMPNAME;
3. (Imp) UPDATE command
The UPDATE command is used to modify the
values of one or more columns in a row or a
number of rows. This updation is performed on the
data stored in the table & the actual table is
modified. The UPDATE command uses the
WHERE clause to specify the rows & the SET
operator to change the values.
Syntax:
UPDATE < table name> SET<column name=new
value>;
For eg. UPDATE emp set sal=5000;
It will change the salary of all employees in the
emp table to 5000.
To modiy the salary of only a few employees, the
command would be:
UPDATE emp SET sal=5000 WHERE
deptno=10;
The UPDATE command can also be used to
update more than one column.
For eg. UPDATE emp SET sal=5000, grade=’A’
WHERE dob>{1/1/90}
Numerical computations can also be used in the
SET operator to modify values of columns.
For eg. UPDATE emp set sal=sal+sal*5/100,
grade=’A’ WHERE dob>{1/1/90};
It will increase the salry by 5% & change the
grade to ‘A’ for only those employees whose date
of birth is after 1st Jan 1990.
The values of a column can also be set to NULL
values by the UPDATE command
For eg. UPDATE emp SET grade=NULL
WHERE grade=’D’;
OR
UPDATE EMPINFO SET SALARY =5000
WHERE EMPID=102;
4. DELETE command:
The DELETE command deletes rows from the
table.
Syntax:
DELETE FROM <table name> [WHERE
<condition>];
If the DELETE command is used without a
WHERE clause it will delete all the rows of the
table.
For eg. DELETE FROM emp;
To delete only a few rows, the DELETE
command is given with the WHERE clause.
For eg. DELETE FROM emp WHERE
deptno=10;
OR
DELETE EMPINFO WHERE EMPID = 101;
Selecting From all the Rows- ALL keyword
The ALL argument, if given instead of DISTINCT
displays all the values of a field for every row even if
the values are repeated.
For eg. SELECT ALL from emp;
Selecting Specific Rows- WHERE clause
The WHERE clause allows to define criteria to
determine which rows are to be selected for output. It
allows to give conditions for selection of rows.
Syntax:
SELECT <column name>,[<column name>…..]
FROM <table name> WHERE <condition>
For eg. SELECT empno, ename from emp WHERE
sal>3000;