STRUCTURE
QUERY LANGUAGE
By
[Link]
QMC College, Medavakkam.
DEFINITION
SQL consist of a set of facilities for defining,
accessing, and managing relational data base.
Very flexible.
Free from syntax.
High level language.
Advantages:
High level language that provides a higher degree of
abstraction than procedural language.
SQL applications are portable.
Provides well defined results.
SQL is not case sensitive
TYPES OF SQL COMMANDS
1.
2.
3.
4.
5.
DDL (DATA DEFINITION LANGUAGE)
DML (DATA MANIPULATION LANGUAGE)
TCL (TRANSACTION CONTROL LANGUAGE)
DCL (DATA CONTROL LANGUAGE)
DQL (DATA QUERY LANGUAGE)
DDL STATEMENTS
[Link] :
CREATE TABLE <TABLE NAME> <COLUMN1><DATA TYPE 1>,
<COLUMN2><DATA TYPE 2>..);
EXAMPLE :
CREATE TABLE BCA(NAME VARCHAR2(20),
REG_NUM NUMBER(5),
MARK1 NUMBER(5),
MARK2 NUMBER(5),
MARK3 NUMBER(5),
TOTAL NUMBER(5));
(;
) is a terminator operator for sql statement.
[Link]:
ALTER TABLE <BASE TABLE NAME>
ADD/MODIFY<COLUMN1><DATA TYPE 1>,
<COLUMN2><DATA TYPE 2>..);
EXAMPLE :
ALTER TABLE BCA ADD AVG NUMBER(5);
New
To
column added.
modify the column size of the average.
ALTER TABLE BCA MODIFY AVG NUMBER(10,2);
[Link]:
An existing base table can be deleted at any time
by using DROP statement.
DROP TABLE <TABLE NAME>;
EXAMPLE :
DROP TABLE BCA;
DML STATEMENTS
[Link] :
Add new rows to a database table.
INSERT INTO <TABLE NAME> VALUES(VALUE
LIST);
EXAMPLE :
INSERT INTO BCA VALUES
('MANI', 1001, 50,60,70,NULL,NULL);
One
row inserted.
[Link]:
UPDATE <BASE TABLE NAME> SET
<COLUMN NAME>=VALUE
WHERE <COLUMN NAME =VALUE);
EXAMPLE :
UPDATE BCA SET NAME='VASU'
WHERE REG_NUM=1001;
Increase
the mark of all students
UPDATE BCA SET MARK1=MARK1*5;
To
update the total and average
UPDATE BCA SET TOTAL=MARK1+MARK2+MARK3;
UPDATE BCA SET AVG=TOTAL/3;
[Link]:
An existing base table can be deleted at any time
by using DELETE statement.
DELETE FROM <TABLE NAME>;
DELETE FROM <TABLE NAME> WHERE
<COLUMN NAME> = <VALUE>;
EXAMPLE :
DELETE FROM BCA;
DELETE FROM BCA WHERE NAME ='RAM';
TCL STATEMENTS
[Link] :
Is the transactional command used to save changes made by a
transaction to the data base.
COMMIT [WORK];
COMMIT
keyword is the only mandatory.
WORK keyword is optional.
2. ROLLBACK:
Used
base.
to undo the transaction that have not already been committed to the data
ROLLBACK [WORK];
[Link]:
Is a point in the transaction that we can rollback without rolling back
the entire transaction
DCL STATEMENTS
[Link] AND REVOKE :
Is used to allow one or more privileges to a
data base users.
EXAMPLE :
GRANT PRIVILEGE [PRIVILEGE..];
REVOKE PRIVILEGE [PRIVILEGE..];
DQL : DATA QUERY LANGUAGE
QUERY BASIC: 1. SELECT COLUMNS
To get data from tables in a database, we use the
SELECT statement.
SELECT *FROM <TABLE NAME>;
(OR)
SELECT *FROM <TABLENAME> WHERE
<COLUMN NAME> = VALUE;
The SELECT statement list the items, column
names, computed values and etc.
* is used to get all the columns of a particular table.
The WHERE clause instruct SQL to include only
certain rows of data in the result set.
EAMPLE
SELECT*FROM IIIBCA;
SELECT *FROM IIIBCA WHERE
REGNUM=1004;
[Link] RETRIEVAL
Display names, reg numbers of students who
have fail either in mark1 or mark2.
SELECT NAME, REGNUM FROM IIIBCA
WHERE MARK1<30 OR MARK2<30;
SELECT *FROM IIIBCA WHERE AVG<50;
We can use all the comparison operators
(=,<,>,<=,>=) in the WHERE clause.
Also use simple comparison operators (AND , OR,
NOT).
3. ELIMINATING DUPLICATES USING DISTINCT
Eliminate the duplicates from the result set.
SELECT MARK2 FROM IIIBCA;
SELECT DISTINCT MARK2 FROM IIIBCA;
[Link] USING IN
If we want to get the rows which contains certain
values, use the IN conditional expression.
SELECT NAME FROM IIIBCA WHERE AVG IN(65,35);
The same result can be obtained by using two
individual comparisons connected together using OR
operator.
SELECT NAME FROM IIIBCA WHERE AVG=65 OR
AVG=35;
[Link] THE OUTPUT
SELECT statement can produce the data in any
random order.
The output can be ordered specifically using ORDER
BY clause.
The general syntax of ORDERBY is
ORDERBY {expr | position}[ASC/DESC]
SELECT NAME,REGNUM,AVG FROM IIIBCA ORDER
BY NAME ASC;
COMPUTAION USING QUERIES
[Link] FUNCTIONS :
Mathematical operations.
Takes an entire column of data as argument and
produces a single data item as output.
The aggregate functions provided by SQL are:
1. AVG()
: Average
2. COUNT(): Count all the selected rows
3. MAX()
: Maximum value of expressions
4. MIN()
: Minimum value of expressions
5. SUM()
: Sum of values if n
AVG()
: Average
. Find the average total of the students.
SELECT AVG(TOTAL) FROM IIIBCA;
1.
2. COUNT() : Count all the selected rows
. Get the number of rows in the table IIIBCA.
SELECT COUNT(*)FROM IIIBCA;
3. MAX() : Maximum value of expressions
SELECT MAX(AVG)FROM IIIBCA;
[Link]() : Minimum value of expressions
SELECT MIN(AVG)FROM IIIBCA;
[Link]() : Sum of values if n
SELECT SUM(TOTAL)FROM IIIBCA;