SQL Basics

1) What are the types of languages in SQL?

There are 5 types of languages they are

--> DRL (Data Retrieval Language): It is used to get the data and we use the command

SELECT statement

Select * from EMP;

--> DDL (Data Definition Language): It is used to change the table structure it

Is an auto Commit language. Here we use the commands

CREATE, ALTER, DROP, TRUNCATE, RENAME

i) CREATE: It is used to create a table structure

Ex: Create table table_name (id number, name varchar2 (10), date_of_birth date);

Create table EMP (emp_id number, emp_name varchar2 (10), join_date date);

ii) ALTER: It is used to change the table structure. It has 4 types

ADD the column, MODIFY the column, RENAME the column, and DROP the column

1)                  add:--     alter table emp add(commission number)

2)                  drop:--    alter table emp drop column commission;

3)                  rename:-- alter table emp rename column emp_name to name;

4)                  modify:-- alter table emp modify emp_name varchar2(15);

iii) DROP: It is used to delete the table from the data base

Ex: Drop table Table_name;

Drop Table EMP;

iv) RENAME: It is used to change the name of the table in data base

Ex: Rename EMP to employee;

v) TRUNCATE: It is used to delete the data from the table and never get back the data

Ex: truncate table EMP;

--> DML (Data Manipulation Language): It is used to modify the table data we need to commit

Manually we use the commands are INSERT, UPDATE, DELETE

i) INSERT: It is used to insert data in to table.

Ex: insert into EMP (empno, ename) values (10,’abc’);

ii) UPDATE: It is used to modify the table data.

Ex: update EMP set sal=5000 where empno=1;

iii) DELETE: It is used to delete the table data

Ex: delete from EMP where empno=1;

--> DCL (Data Control Language): It is used to give permissions to access table by another

Person or restrict the access.

i) GRANT: It is used to grant the permissions to access the table to specified person

Ex: Grant all on table_name to user_name; // all means select, insert, update, delete

Permissions.

Grant all on EMP to PRAVEENA;

ii) REVOKE: It is used to cancel the permissions which are granted to the user

Ex: Revoke all on table_name from user_name;

Revoke all on EMP from PRAVEENA;

--> TCL (Transaction Control Language): We use the commands are

SAVEPOINT, ROLLBACK, COMMIT

i) SAVEPOINT: It is used to give the specified point after some time we need to undo the

Operations At This Point.

Ex: savepoint savepoint_name;

Savepoint a;

Insert into EMP (empno, ename) values (10,’praveena’);

----------------

----------------

Savepoint b;

Delete from EMP where empno=5;

Savepoint c;

Update EMP set sal=5000 where empno=4;

After this we need to save up to ‘savepoint b’ means just we have to give the statement like this

Savepoint b;

Then it will save up to b;

Savepoint note: it will mark position in the buffer.

ii) ROLLBACK: It is an undo operation we can also specify the savepoint here.

It is applicable for

Only DML commands not applicable for DDL because it is an auto commit

Ex: rollback to savepoint_name;

We retrieve the data back means follows this query rollback b;

2) What is the difference between TRUNCATE & DELETE?

TRUNCATE                                                               DELETE

1) We can’t get data back                                        1) we can get data back by using rollback

2)  After data is deleted storage space released.      2) After data is deleted storage space not released.

3) What are the types of joins in SQL?

i) Self join: It is used to join the same table with it

Ex: select e1.ename “manager”, e2.ename “employee” from EMP e1, EMP e2 where

e1.empno=e2.mgr;

Select e.ename manager, e1.ename salesman from EMP e, EMP e1 where e.emp=e1.mgr;

ii) Equi join: Join which contains ‘=’ operator then it is called equi join

Ex: select e.ename from EMP e, dept d where e.deptno=d.deptno;

iii) Outer join: It has two types left outer join and right outer join that is ‘+’ it is in left side of the

‘=’ operator then it is left outer join if ‘+’operator in right side to ‘=’ operator then it

Is right outer join

Ex: left outer join: -- select e.ename, e.empno, d.deptno, d.dname from EMP e, dept d where e.deptno=d.deptno (+);

 Right outer join: select e.ename, e.empno, d.deptno, d.dname from EMP e, dept d where e.deptno (+) =d.deptno;

iv) Non Equi join: If the query contains other than equal to operator then it is called non equi join

Ex: select e.ename from EMP e, dept d where e.deptno >= (+) d.deptno;

4) What are the different types of operators in SQL?

i) Arithmetic operators: +, -, /, *

Select empno, sal+comm. From EMP;

Select empno, (sal+comm)*12 from EMP;

ii) Relational operators (or) Comparision operators: =, <> (not equal to)! =, >, <, >=, < =

Ex: -- Select * from EMP where sal>2000;

Select * from EMP where sal<>2000;

iii) Logical Operators: AND: it returns true if both conditions satisfied

Select * from EMP where comm. Is not null;

OR:  it returns true if any one condition satisfy

Select * from EMP where sal=1000 or deptno=10;

NOT:   it returns true if condition is not satisfy

Select * from EMP where sal=1000 and deptno=10;

iv)  Set Operators: UNION: Gives the result from both the queries excluding duplicates

UNION ALL: Gives the result from both the queries including duplicates

INTERSECT: Gives the result which is common to both the queries

MINUS: Gives the result from first table which are not present in the second query.

 

Concatenation Operator: -- By using concatenation operator we can combine one column value with               another column value.

Select empno||sal from EMP;

5) What are wild cards?

% (percentile): more than one character it checks.

Ex: select ename from EMP where ename like ‘A%’; //output is Adams etc

‘_’ (underscore): exact one character it takes.

Ex: select ename from EMP where ename like ‘Adam_’ //output is Adams.

6) What is dual table? Can we modify it?

Dual is a default and empty table we can’t modify because it is used by all users.

Dual table has only one column with data type varchar2 and size is one

Ex: select * from dual;   or desc dual;

Select sysdate from dual;

Select 1+1 from dual;

7) What is functions in SQL and define types?

Functions are of two types

1)                  single row functions: operates on each row and gives result per row

Ex: select length (ename) from EMP;

2)                  multiple row functions: operates on each group and gives result per group

Ex: select max (sal) from EMP;

Single row functions: These are of 5 types

1)      Character functions

Two types:

Case manipulation:

Upper: -- convert any case into upper case

Ex: select upper (Praveen) from dual;

Lower: -- convert any case into lower

Ex: select lower (Praveen) from dual;

Init cap: -- convert starting letter of each word into upper case remaining letters to            lower case

Character manipulation:

Length: -- select length (ename) from EMP;

Concat: -- select ename||job from EMP;

Substr: -- select job, substr (job, 4) from EMP; [PRESIDENT means it shows SIDENT]

Instr: -- select ename, Instr (ename,'A') from EMP; [JAMES means it shows the A                       position in JAMES now the position 2]

Rpad: -- select sal, Rpad (sal, 10,'*') from EMP; -- 3000******

Lpad:  -- select sal, lpad (sal, 10,'*') from EMP; -- ******3000

Trim -- trim the character

Select trim ('h' from 'hello world') from dual; -- ello world

Translate: -- it replaces one set of sequence of characters another set of sequence of characters

Select translate ('hello world','ew','xy') from dual; e is replace with x and w is replace with y

Replace: -- it replace the set of characters

Select replace (‘pravin’,’in’,’i’) from dual –pravi

Conditional Expressions: --

Give you the use of IF-THEN-ELSE logic with in a SQL statement

Use two methods:

Case Expression: --

Select ename, job, sal, case ename when 'SCOTT' then 10+sal

When 'SMITH' then 15+sal

When 'JAMES' then 20+salElse sal end from EMP;

Decode function: -- select ename, job, Sal, decode (ename,'SCOTT', 10+sal,

'SMITH', 15+sal,

'JAMES', 20+sal,

Sal) from EMP;

2) Number functions

1) Conversion function:

Round: Round value to specified decimal

Ex: select round (45.926, 2) from dual; ---45.93

Trunk: Truncates value to specified decimal

Ex: select trunk (45.926, 2) from dual; -- 45.92

Mod: Return remainder of division

Ex: select mod (1600, 300) from dual; -- 100

2) General functions: these functions work with any data type and pertain to using null Value.

--> NVL:               converts a null value to an actual value

Ex: select comm, NVL (comm, 143) from EMP;

--> NVL2:          if exp1 is not null, nvl2 returns exp2. If exp2 in null, nvl2 returns

Exp3.the Argument 1 can have any data type.

Ex: select ename, sal, comm, nvl2 (comm, sal+comm, sal) from EMP;

--> NULLIF:      compares two expressions and returns null if they are equal, or first

Expression if they are not equal.

Ex: select ename, length (ename) "exp1",

Job, length (job)     "exp2",

Nullif (length (ename), length (job)) from EMP;

--> COALESCE: return the non-null expression in the expression list

Ex; select ename, comm, sal, coalesce (comm, sal, 10) from EMP;

Note:  the advantage of the coalesce function over the Nvl      function is that the coalesce function

Can take multiple alternative values.

If the first expression is not null, it returns that expression, otherwise it does a coalesce

Of the remaining expression

2)      Date functions:

--> MONTHS_BETWEEN: number of months between two days

Ex: select MONTHS_BETWEEN ('11-04-2011','01-08-2012') months from dual;

--> ADD_MONTHS: add calendar months to date

Ex: select ADD_MONTHS ('11-04-2011', 6) from dual;

--> NEXT_DAY: next day of the date specified

Ex: select NEXT_DAY ('11-04-2011','SATURDAY') from dual;

--> LAST_DAY: last day of the month

Ex: select LAST_DAY ('11-04-2011') from dual;

--> ROUND: round date

Ex: select round (sysdate,'month') from dual;

Ex: select round (sysdate,'year') from dual;

--> TRUNC: trunc date

Ex:  select trunc (sysdate,'month') from dual;

Ex:   select trunc (sysdate,'year') from dual;

Multiple row functions or group functions:

1)                  Max: maximum value of expression ignoring null values.

2)                  Min : minimum value of expression ignoring null values

Ex: select max (hiredate), min (hiredate) from EMP;

3)                  Avg: average value of n, ignoring null values

4)                  Sum: sum value of n, ignoring null values

Ex: select avg (sal), sum (sal) from EMP;

5)                  Count: count all selected rows using *, including duplicates and rows with nulls.

Ex: select count (*) from EMP where deptno=10;

8) What is Cartecian product?

‘Where’ clause is omitted or excluded in the query then the result will be the Cartecian product

Ex: select emp.ename, dept.dname from EMP, dept;

9) What is group by and having clause in sql?

It is used to makes the group in the table what ever columns present in the select clause should be Present in the group by clause. ‘Having’ it is used to restrict the group functions.

When ever we use ‘group by’ then we have to use ‘having’ to restrict the rows because ‘where’ is Not accepted in the ‘group by’ clause.

Ex: select Deptno, max (sal) from EMP group by Deptno having max (sal)>1000;

10) What is subquery and ‘in’ query?

Query with in a query is called subquery

Ex: select * from EMP where sal > (select sal from EMP where ename=’ADAMS’);

Query with in condition with a sub query is called ‘in query’

Ex: select * from EMP where Deptno in (select Deptno from dept);

11) What is constraints and specify types?

Constraints means rules or conditions which are applicable when ever we use the DML commands

Like insert, update and delete these are of different types

i) NOT NULL: It ensures that there are no null values in the specified column.

Ex: create table emp3 (empid number, sal number, empno number constraint

Constraint_name not null);

(Or)

Create table emp3 (empid number, sal number, empno number not null);

ii) UNIQUE: It ensures that there is no duplicate value.

Ex: create table emp3 (empid number unique, sal number, empno number);

iii) CHECK: Defines the condition that every row must satisfy.

Ex: create table emp3 (empid number, sal number check (sal>1000), empno number);

iv) PRIMARY KEY: It is the combination of NOT NULL+UNIQUE

Ex: create table emp3 (empid number primary key, sal number, empno number);

v) FOREIGN KEY: It defines the columns as a foreign key and builds the relation ship between

Primary key and foreign key.

Ex: create table emp3 (empid number, sal number, empno number,

Foreign key (empno) references EMP (empno));

12) What is ‘on delete cascade’ and ‘on delete set null’?

If we want to delete the primary key values first we need to delete the child rows then only we can

Delete the parent rows. By using ‘on delete cascade’ (or) ‘on delete set null’ we can delete the

Parent records directly

On delete cascade:  if we delete parent records automatically child rows deleted.

Ex: create table emp3 (empno number primary key, Deptno number, Sal number, foreign

Key (Deptno) references dept1 (Deptno) on delete cascade);

On delete set null: if we delete parent records foreign key values set to be null.

Ex: create table emp3 (empno number primary key, Deptno number, sal number, foreign

Key (Deptno) references dept1 (Deptno) on delete set null);

13) What are data base objects?

These are of 5 types

Tables, Views, Sequences, Synonyms, Indexes

Views: it is data base object which is creating based on the table or tables, view or views. It acts

Like a window by which u can access the data. View does not contain any memory on its

Own. It is two types

i) Simple views: It does not contains group by clause & functions only one table & DML

Operations are used.

Ex: create or replace view view1 as select empno, sal, comm, sal+comm "total" from EMP;

ii) Complex views: It does not contains DML operations and deals with more than one

Table, group by clause and functions.

Ex: create or replace view view1 as select empno, sal, comm, sal+comm "total", dname

From EMP, dept where emp.deptno=dept.deptno;

Sequences: it is used to generate the primary key values.

Ex: create sequence sequence_name

Increment by 1

Start with 1

Max 100

Synonyms: IT GIVES THE PERMANENT ALIAS NAME FOR A TABLE SO WE SELECT

THE TABLE WITH BOTH THE NAMES JUST LIKE A CLONE OF TABLE IF WE DO

ANY IN TABLE OR SYNONYM AUTOMATICALLY EEFECTS TO BOTH TABLE

AND SYNONYM

Ex: 1) CREATE SYNONYM employee for EMP;

2) DROP SYNONYM EMPLOYEE;

3) CREATE SYNONYM EMPLO FOR RAVIKANTH.EMP1;   --EMPLO IS SYNONYM

NAME AND RAVIKANTH IS USER EMP1 IS TABLE 1

4) CREATE PUBLIC SYSNONYM EMPLO FOR RAVI.EMP; --USE FOR ALL USERS

5) DROP PUBLIC SYNONYM EMPLO;

Indexes: IT IS USED FOR HIGH PERFORMANCE AT THE TIME OF RETREIVING

Ex: 1) CREATE INDEX ind_sal ON EMP (sal); //CREATED FOR COLUMN SAL

2) CREATE UNIQUE INDEX ui_mgr ON EMP (mgr);

3) CREATE INDEX ind_sal ON EMP (sal, empno);

14) What are pseudo columns?

Nextval: it will give next value available in the sequence.

Currval: it will give current value available in the sequence.

Rowid:  for each row in every table one id is generated in memory to identify the row it is constant.

Rownum: for each row in every table sequence number is generated and it is not constant