SQL
It
is known as structural query language. Database understands only query
language. Using the sql we can communicate to oracle server. The Oracle Server
supports ANSI standard SQL. SQL is the language used to communicate with the
server to access, manipulate, and control data.
CLAUSES
1.
Select
2.
From
3.
Where
4.
Group by
5.
Having
6.
Order by
Note:
·
We cannot interchange the position but
we can switch the position.
·
Semicolon (;) is used to terminate the
statement.
·
Row is case sensitive but column is
not case sensitive.
·
Using comma (,) we can use many columns.
·
We don’t write db name in sql in some
cases because we login to DB before execute the statement.
·
With the Oracle Server, you can store
and manage information by using the SQL language and PL/SQL engine.
1. SELECT
A SELECT statement retrieves information from
the database. Using a SELECT statement, you can do the following:
• Projection:
choose the columns in a table that you want returned by your query.
• Selection:
choose the rows in a table that you want returned by a query.
•
Joining: You can bring data together that is stored in
different tables.
SYNTEX:
SELECT *| {[DISTINCT]
column|expression [alias],...} FROM table;
EXAMPLE:
·
Select name from emp;
·
Select * from emp;
·
Select
name , salary from emp;
·
Select
distinct name ,salary-100 from emp;
Note
·
Distinct is used for the different value rather than similar
value. For example if there is table abhi having name column and there is two
name similar like pawan it going to show only one pawan if we using distinct
before column.
2. From:
FROM identifies which table. A FROM clause, which specifies the table
containing the columns listed in the SELECT clause in the syntax.
EXAMPLE:
·
Select name from emp;
·
Select * from emp;
3. Where
Restrict the rows returned by using the WHERE
clause. A WHERE clause contains a condition that must be met, and it directly
follows the FROM clause. If the condition is true, the row meeting the
condition is returned. The WHERE clause can compare values in columns, literal
values, arithmetic expressions, or functions. It consists of three elements:
•
Column name
•
Comparison condition
•
Column name, constant, or list of value.
Syntax:
SELECT *|{[DISTINCT] column|expression
[alias],...} FROM table [WHERE condition(s)];
Example:
·
Select * from emp where deptno = 10;
·
Select name, dept from emp where
last_name = ‘Kumar’;
·
Select distinct name ,salary from emp
where salary<=1000;
4. Group by
Divide
rows in a table into smaller groups by using the GROUP BY clause. You can then
use the group functions to return
summary information for each group.
SYNTEX:
SELECT column, group_function
(column) FROM table [WHERE condition] [GROUP BY group_by_expression] [ORDER BY
column];
EXAMPLE:
·
SELECT department_id,
AVG(salary) FROM employees GROUP BY department_id ;
·
SELECT department_id
dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;
Note:
·
If you include a group function in a
SELECT clause, you cannot select individual results as well, unless the
individual column appears in the GROUP BY clause. You receive an error message
if you fail to include the column list in the GROUP BY clause.
·
Using a WHERE clause, you can exclude
rows before dividing them into groups.
·
You must include the columns in the
GROUP BY clause.
·
You cannot use a column alias in the
GROUP BY clause.
·
By default, rows are sorted by
ascending order of the columns included in the GROUP BY list.
·
You can override this by using the
ORDER BY clause.
5. Having
You use the HAVING clause to specify which
groups are to be displayed, and thus, you further restrict the groups on the
basis of aggregate information.
SYNTEX:
SELECT column, group_function FROM table [WHERE
condition]
[GROUP BY group_by_expression] [HAVING
group_condition] [ORDER BY column];
EXAMPLE:
·
SELECT department_id, MAX(salary) FROM
employees GROUP BY department_id HAVING MAX(salary)>10000 ;
·
SELECT job_id, SUM(salary) PAYROLL
FROM employees WHERE job_id NOT LIKE ’%REP%’ GROUP BY job_id HAVING SUM(salary)
> 13000 ORDER BY SUM(salary);
Note:
We
Use the HAVING clause to restrict groups:
1.
Rows are grouped.
2.
The group function is applied.
3. Groups matching
the HAVING clause are displayed.
6. Order by
It used
to sort rows with the ORDER BY clause. The ORDER BY clause comes last in the
SELECT statement. It must be the last clause of the SQL statement. You can
specify an expression, or an alias, or column position as the sort condition.
·
ASC: ascending order, default
·
DESC: descending order
SYNTAX:
SELECT *|
{[DISTINCT] column|expression [alias],...} FROM table ORDER BY column|alias;
EXAMPLE:
·
SELECT last_name, salary, hire_date
FROM employees ORDER BY hire_date ;
·
Select * from emp order by salary
desc;
·
Select name, salary, deptid from emp
where salary >= 1000 order by deptid, salary asc;
Note
·
ASC is default in query.
·
Numeric values are displayed with the
lowest values first—for example, 1–999.
·
Date values are displayed with the earliest
value first—for example, 01-JAN-92 before 01-JAN-95.
·
Character values are displayed in
alphabetical order—for example, A first and Z last.
·
Null values are displayed last for
ascending sequences and first for descending sequences.
OPERATOR:
·
ARITHMETIC OPERATOR
1.
You can use arithmetic
operators in any clause of a SQL statement except in the FROM clause.
2.
Arithmetic operator are +, -, *, /
3.
Bracket having high
priority.
4.
Example:
Select last_name, salary,
12*salary+100 from emp;
·
LOGIC OPERATOR
1.
Logic operator are “AND ” “OR” “NOT”
2. Select * from emp where salary =3000
and age =21;
·
COMPARISON OPERATOR
1.
Comparison operators are >, <,
>=, <=, !=, =
2.
Select * from emp where sal>=1000;
·
CONCATENATION OPERATOR
1.
Concatenation operator are || is used
to connect two column or strings
2.
Select name ,||’is’||mgr from emp;
·
SET OPERATOR
1.
Union (value not repeat)
2.
Union all (value repeat )
3.
Intersect (common value)
4.
Minus (not common in both)
5.
Example:
Select deptno from emp union/union
all/ minus/intersect select deptno from emp2;
GENERAL OPERATOR:
·
IN
Fetch
multiple values from the same column.
Example:
Select * from emp where sal in (2000,
3000, 4000);
·
BETWEEN
It
used to find the value range from higher to the lower.
Example
Select
* from emp where sal between 2000 and 6000;
·
LIKE
Search
for the similar character.
Select
ename from emp where ename like %K%;
‘%S’
its meaning ‘S’ at last;
‘S%’
its meaning ‘S’ at first;
‘%S%’
its meaning ‘S’ at any place;
For
particular place S____A% start with S and end with A.
·
NULL
It
used to find the null values.
Example:
Select
* fro m emp where sal is NULL;
CATEGORIES OF SQL
COMMAND
·
DDL
(Data definition language used at column and table level auto commit )
1.
Create (Example: create table abc
(name varchar (5)) ;)
2.
Drop (Example: drop table abc; alter table abc
drop column age ;)
3.
Rename (Example: alter table abc
rename column sal to salary; rename table abc to xyz;)
4.
Truncate (Example: truncate table
abc;)
5.
Alter (Example: alter table abc rename
abcd;)
6.
Add (Example: alter table abc add
column age number (5);)
7.
Modify (Example: )
·
DML
(Data manipulation language)
1.
Update (Example: update emp set
deptno=50 where sal=1000;)
2.
Insert (insert into abs values (‘kunal’
1000, ‘m’);)
3.
Delete (Example: delete from xyz;)
4.
Merge (Example: see below)
·
DCL
(Data control language)
1.
Grant (Example: grant connect,
resource to abhi )
2.
Revoke (Example: revoke connect to
abhi)
3.
Commit (commit)
4.
Rollback (rollback)
DATA TYPE:
·
Character
1.
Char
·
Reserve memory
·
Store 1-2000 bytes
·
Ignore white spaces(don’t count
whitespace)
2.
Varchar
·
Release memory
·
Store 2-4000 bytes
·
Don’t ignore white spaces
·
Number
o
It store 1-38 byte
·
Date
o
No specific size
FUNCTION:
1.
Single Row
·
Character manipulation
1.
ASCII()
2.
CHR()
3.
LTRIM()
4.
RTRIM()
5.
LPAD()
6.
RPAD()
7.
SUBSTR()
8.
INSTR()
9.
LENGTH()
10.
CONCAT()
·
Case manipulation
1.
UPPER()
2.
LOWER()
3.
INITCAP()
2.
Multiple Row
1.
SUM()
2.
AVERAGE()
3.
COUNT()
4.
MIN()
5.
MAX()
3.
Number Function:
1.
SQRT()
2.
POWER()
3.
ABS()
4.
ROUND()
5.
TRUNC()
6.
MOD()
7.
SIGN()
4.
Date Function:
1.
SYSDATE()
2.
CURRENT_TIMESTAMP()
3.
LAST_DAY()
4.
NEXT_DAY()
5.
ADD_MONTHS()
6.
EXTRACT()
7.
MONTHS_BETWEEN()
JOINS:
When
data from more than one table in the database is required, a join condition
is used. Rows in one table can be joined to rows in another table according to
common values existing in corresponding columns, that is, usually primary and
foreign key columns.
1. Cartesian
product or cross join
Everything multiplied
Example:
Select ename, dname from emp, dept;
2. Equijoins
Equijoins are also called simple joins or inner joins.
Example:
SELECT employees.employee_id,
employees.last_name, employees.department_id, departments.department_id,
departments.location_id FROM employees, departments
WHERE employees.department_id =
departments.department_id
3. Non
equijoins
Example:
SELECT e.last_name, e.salary, j.grade_level
FROM employees e, job_grades j WHERE e.salary BETWEEN j.lowest_sal AND
j.highest_sal;
4. Self
joins
Example:
SELECT worker.last_name || ’ works for ’ ||
manager.last_name FROM employees worker, employees manager WHERE
worker.manager_id = manager.employee_id ;
5. Outer
joins
Example:
SELECT table1.column, table2.column
FROM table1, table2 WHERE table1.column
(+) = table2.column;
DATABASE OBJECTS:
1.
TABLE
It stores data. Tables
can be created at any time, even while users are using the database. You do not
need to specify the size of any table. The size is ultimately defined by the
amount of space allocated to the database as a whole. Table structure can be
modified online.
User Tables:
Are a collection of tables created and
maintained by the user
Contain user information
Data Dictionary:
Is a collection of tables created and
maintained by the Oracle Server
Contain database information
All data dictionary tables are owned by the
SYS user.
Syntax:
CREATE TABLE [schema.]table(column datatype [DEFAULT expr][, ...]);
Example:
Create table abc (name varchar (5));
Select * from userb.employee;
See the names of tables owned by the user.
SELECT table_name FROM user_tables ;
View distinct object types owned by the user.
SELECT DISTINCT object_type FROM user_objects
;
View tables, views, synonyms, and sequences
owned by the user.
SELECT * FROM user_catalog ;
2.
VIEW
It is like a window. It is like a snap shot of
table. It is subset of one or more than one table. It doesn’t store the memory.
By default it is in read write mode.
Example:
Create view a as select * from emp;
Select * from a;
Create view b as select e.name, d.deptno from
emp e, dept d where e.deptno = d.deptno;
Select * from b;
Create view b as select e.name, d.deptno from
emp e, dept d where e.deptno = d.deptno with read only;
Create or replace view b as select ename, job
from emp;
Force view
It is view of the non existing table.
Create force view kunal as select * from bank;
Drop view s;
3.
INDEX
We used index mainly for the faster search. It
has two type bitmap and btree. Btree is default. Btree has high cardinality but
bitmap have low cardinality and has replication like male female. If we going
to update using btree, it will easily update because it update only rows.
Update store less memory.
Example
Create index a on emp (name);
Create
bitmap index b on emp (deptno);
4.
SYNONYMS
It is database object having the similar
meaning of the database object. It can be public as well as the private. Generally
private is default.
Example:
Create synonyms s for scott.emp.ename;
Select * from s;
Drop synonyms s;
5.
SEQUENCE
It is a database object that will generate the
sequence number. It has two function nextval() and currval(). If sequence once
created we can’t create sequence once more. Drop it and create once more.
Example:
Create sequence sq start with 1 increment by 1
maxvalue 100 cycle;
Update emp set srno = sq.nextval;
Select sq.nextval from dual;
Select sq.currval from dual;
CONSTRAINTS:
1.
NOT NULL
It used to check the value should not null
while inserting. It is two type table label and column label. It is only
constraints that we can modify.
Example:
Create table dark (name varchar (5) constraint
y not null);çcolumn
label
Create table dark (name varchar (5),
constraint y not null);çTable
label
2.
CHECK
It used to check the condition it is also
table label as well as column label
Example:
Create table xyz (name varchar (4), age number
(5) constraints x check (age>18));çcolumn
Create table xyz (name varchar (4), age number
(5), sal number (10) constraints x check (age>18 and sal>1000));ç table
label
3.
PRIMARY KEY
It is unique but can’t be null. It can be used
only once in the table.
Example:
Create table xxx (name varchar (5) account_no
number (10) constraints a primary key);
4.
UNIQUE KEY
It is unique and it can be null
Example:
Create table abhi (name varchar (5) age number
(10) constraints y unique)
5.
FOREIGN KEY
It used to give the reference of the primary
key.
Example:
Create table abc (name varchar (4), sal number
(5), constraints x foreign key (name, sal) reference abc);
USER:
We
can create user and we can provide the roles and privilege to required user on
system and on object.
1. Create user kamlesh identified by
munna;
2. Grant connect, resource, sysdba to
kamlesh; (privilege on system)
3.
Grant
update on scott.emp to kamlesh; (privilege on object)
4.
Grant
connect, resource, sysdba to kamlesh with admin option;
5.
update
on scott.emp to kamlesh with grant option;
ROLE:
It
is combination of many privileges. We can give it to many users at the same
time.
1. Create role darknight;
2. Grant resource, connect to darknight;
3. Grant darknight to v1, v2, v3,
.......v100;
MERGE:
We
can do merging of two or more table in to a single table with some condition:
Merge into abc a using (select salary,
name from emp) e on (e.salary = a.salary) set when matched then update set a.name
=e.name when not matched then insert (a.salary, a.name) values (e.salary,
a.name);
COMPLEX QUERY:
It
is combination of many query nested inside a single query.
EXAMPLE:
Select * from EMP where sal in (select
sal from EMP where mgr=7698) and ename in (select ename from EMP where job in
(select job from EMP where empno in (select empno from EMP where deptno in
(select deptno from EMP where sal=3000 and hire_date like ‘%81%’))));
SOME TIPS:
1. Line size:-it is total screen size.
Default:80
2. Set linesize 100
3. Show linesize
4. Page size :- it is length of page
5. Show pagesize
6. Set pagesize 30
7. Forward
slash (/) (it will give previous query)
8.
Describe statement describe the
structure of a table
9.
Desc EMP;
1.Tell me about ur self?
