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
No comments:
Post a Comment