TCL - TRANSACTION CONTROL LANGUAGE

TRANSACTION CONTROL LANGUAGE

COMMIT -- once commit means to save all pending changes permanent storage.
ROLLBACK -- To discard all pending clauses
SAVEPOINT -- It is marker 
DDL operation is auto commit.

SQL> CREATE TABLE TCL(Cid Number);

Table created.
SQL> INSERT into TCL values(1);
SQL> INSERT into TCL values(2);
SQL> INSERT into TCL values(3);
SQL> commit;

Commit complete.

SQL> INSERT into TCL values(4);
SQL> INSERT into TCL values(5);
SQL> SAVEPOINT S5;

Savepoint created.

SQL> SELECT * FROM TCL;

       CID                                                                      
----------                                                                      
         1                                                                      
         2                                                                      
         3                                                                      
         4                                                                     
         5                                                                      

SQL> ROLLBACK TO S5;

Rollback complete.

SQL> SELECT * FROM TCL;

       CID                                                                      
----------                                                                      
         1                                                                      
         2                                                                      
         3                                                                      
         4                                                                      
         5                                                                      
SQL> INSERT into TCL values(6);
SQL> INSERT into TCL values(7);
SQL> SELECT * FROM TCL;

       CID                                                                      
----------                                                                      
         1                                                                      
         2                                                                      
         3                                                                      
         4                                                                      
         5                                                                                                                                            
5 rows selected.

SQL> CREATE TABLE A1(ID Number);
Table created.

SQL> INSERT into A1 values(1);
SQL> commit;

Commit complete.

SQL> INSERT into A1 values(2);
SQL> INSERT into A1 values(3);
SQL> CREATE TABLE A2(ID Number);

Table created.

SQL> INSERT into A2 values(4);
SQL> INSERT into A2 values(5);
SQL> INSERT into A2 values(1);
SQL> INSERT into A2 values(2);

SQL> rollback;

Rollback complete.

SQL> SELECT * FROM A1;

        ID                                                                      
----------                                                                      
         1                                                                      
         2                                                                      
         3                                                                      
SQL> SELECT * FROM A2;
no rows selected

DML : DATA MANIPULATION LANGUAGE

DML : DATA MANIPULATION LANGUAGE ( DATA LEVEL OPERATIONS PERFORMED)

INSERT 
UPDATE 
DELETE 
MERGE

INSERT 

Its used to insert the values in the tables.

Syntax: 

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)  
VALUES (value1, value2, value3,...valueN);

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

-- if using INSERT ALL statement means its ends with DUAL; 

UPDATE 

--old records is replced by new records 

UPDATE TABLE_NAME SET COLUMN_NAME = 'NEW DATA' WHERE COLUMN_NAME = 'OLD DATA'; syntax

UPDATE course SET Cname= 'Parthi' WHERE Cname = 'Karthi'; -- 'karthi' is replaced  with 'parthi'

UPDATE course SET Cid=100,Cname='Arthi' WHERE Cname= 'karthi'; - karthi cname is replaced with arthi and cid is also change into 100

UPDATE course SET Cid=10; if without mentioning WHERE Clause it will update all the column.

DELETE

DELETE FROM table_name; -- Syntax Its DELETE all records in the table. 

-- if mentioning without where condition in DELETE statement all records will be deleted.

DELETE FROM course WHERE Cname='parthi' -- the cname contains parthi column is deleted from the table .

DDL: DATA DEFINITION LANGUAGE

DDL Data Definition Language

CREATE 
ALTER 
   ADD
   MODIFY
   RENAME
   DROP
RENAME 
DROP
TRUNCATE 


TABLE

Database Design Document
Combination of rows and column with data or structure.

CREATE

Syntax : CREATE TABLE Table_Name( Column 1 Datatypes,Column 2 Datatypes); 

CREATE TABLE Student113
(
Sid Number,
Sname varchar(10),
SLocation varchar(10)
);

Table created

ALTER - ADD, MODIFY, RENAME, DROP

ADD

ALTER TABLE Student113 ADD Sdept_id Number(3);
ALTER TABLE Student113 ADD Dob Date;
ALTER TABLE Student113 ADD resume long;

MODIFY

ALTER TABLE STUDENT113 MODIFY Sdept_id char(2);

RENAME 

ALTER TABLE Student113 RENAME COLUMN RESUME TO FEEDBACK;

desc student111;

ALTER TABLE STUDENT113 Drop Column FEEDBACK;

TABLE ALTERED

RENAME : RENAME the table name into an another name here the name is student113 now rename student113 nme into student234

RENAME STUDENT113 TO STUDENT234;

Statement processed

DESC student234;

Drop : Drop command is used to delete the structure and data.

Drop table Student111;

Table Dropped 

Desc Student111;

Object to be described could not be found.-- because the structure and table is dropped.

TRUNCATE: Its used to delete the data only 

TRUNCATE table student234;

Table Truncated

The data only truncated table structure is same.

To View the dropped table:

Select * from recyclebin; -- this query is used to view the dropped table list.

To Retrieve the dropped table

FLASHBACK -- Its used to retrieve the dropped table

FLASHBACK TABLE student111 TO BEFORE DROP RENAME TO student222;
    
FLASHBACK TABLE students TO BEFORE DROP; -- this is the syntax for the flashback here students is table name 

PURGE -- Its used to delete permanently in the database

Syntax : purge table t1;

The table t1 is already dropped but it stores in the recyclebin if the above syntax is used to delete the table inthe reycle bin also.


FLASHBACK TABLE t1 TO BEFORE DROP;

ORA-38305: object not in RECYCLE BIN -- now t1 objects not in the recycle bin.

JOINS INTERVIEW QUESTIONS

What is joins? What are all the difference types of joins available?

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

Equi join,

outer join,

  left outer join 

  right outer join

  full outer join

Self Join

Cross Join

Natural Join

  

Explain outer join and its types with example.

outer join 

  left outer join -Matched Records from left side table 

  right outer join - Matched Records from right side table

  full outer join- Matched and Unmatched Records from both side. 

What is Self Join and why is it required?

With in the table join performed means its self join. 

if one column is referred by another column with in the table means its required. 

What is the difference between inner and outer join? Explain with example. 

Inner Join: Its not include the unmatched records.

outer join: Its show unmatched records also.

What is a Cartesian product.

Cartesian Product means it compares two or more table result m*n output. ex if the table contains 5 row and next table contains 6 rows means the output returns 30 rows.

 

If I try to Fetch data from 25 tables. How many number of join condition required?

If N number table means N-1 number of conditions needed 

so 24 join conditions needed to fetch in the table.

Write a query to display the last name, department number, and department name for all employees

select e.last_name, d.department_id,d.department_name
from employees e,departments d
where e.department_id= d.department_id;

Create a unique listing of all jobs that are in department 30. Include the location of department 90 in the output.

select distinct e.job_id,d.location_id 
from  employees e,  departments d
where e.department_id = d.department_id
and d.department_id=80; 

Write a query to display the employee last name, department name, location ID, and city of all employees who earn a commission. 

select e.commission_pct,e.last_name, d.department_name,d.location_id,l.city
from employees e, departments d,locations l
where e.department_id=d.department_id 
and d.location_id=l.location_id and commission_pct is not null;

Display the employee last name and department name for all employees who have an a (lowercase) in their last names. Place your SQL statement in a text file named lab4_4.sql.

select lower(e.last_name), d.department_name 
from employees e, departments d
where e.department_id=d.department_id 
and last_name like '%a';  

Write a query to display the last name, job, department number, and department name for all employees who work in Toronto. 

select e.last_name,e.job_id,e.department_id,d.department_name 
from employees e, departments d,locations l
where e.department_id = d.department_id and d.location_id=l.location_id and l.city= 'Toronto'; 

Display the employee last name and employee number along with their manager’s last name and manager number. Label the columns Employee, Emp#, Manager, and Mgr#, respectively. Place your SQL statement in a text file named lab4_6.sql.

select e.last_name "Employee",e.employee_id "Emp#" ,m.last_name 
"Manager",m.employee_id"Mgr#"
from employees e , employees m
where e.manager_id=m.employee_id; 

Modify lab4_6.sql to display all employees including King, who has no manager. Place your SQL statement in a text file named lab4_7.sql. Run the query in lab4_7.sql 

select e.last_name "Employee",e.employee_id "Emp#" ,m.last_name 
"Manager",m.employee_id"Mgr#" from employees e, employees m
where e.manager_id=m.employee_id(+) order by e.employee_id;

Create a query that displays employee last names, department numbers, and all the employees who work in the same department as a given employee. Give each column an appropriate label.

select e.department_id,e.last_name,m.last_name
from employees e full join employees m
on e.department_id=m.department_id 
where e.employee_id<>m.employee_id 
order by department_id;

JOINS IN SQL

JOINS

•  EQUI JOIN 
•  OUTER JOIN

1.     RIGHT OUTER JOIN
2.     LEFT OUTER JOIN
3.     FULL OUTER JOIN

• SELF JOIN 
• CROSS JOIN

EQUI joins

Matched Records from both table

select * from std;
select * from course;

select sname, cname  
from std, course
where std.cid=course.cid;

ALIAS NAME IN JOINS

select s.sname, c.cname  
from std s, course c
where s.cid=c.cid;

OUTER JOINS

LEFT OUTER JOIN

Its display unmatched records from left hand side.if mentionion left outer join using right 

hand side of the employees

select sname, cname  
from std, course
where std.cid=course.cid(+);

RIGHT OUTER JOIN

Its display unmatched records from right hand side.if mentionion left outer join using left 

hand side of the employees

select sname, cname  
from std, course
where std.cid(+)=course.cid;

FULL OUTER JOIN

Its display both matched and unmatched records from the table . In the full outer join the syntax is

Replacing where clause by on clause conditions are to be entered after the on clause Join is mentioned after the from clause 

Select col1,col2
from tab1 Full Join tab2 
on conditions 

select sname, cname  
from std full outer join course
on std.cid=course.cid;

Full Outer join with 3 tables

select e.first_name, e.department_id, d.location_id,l.city  
from employees e full outer join departments d  
on e.department_id=d.department_id 
full outer join locations l 
on d.location_id= l.location_id  

select sname,cname
from std, course;

SELF JOIN 

With in the table perform join conditions means its a self join

consider one example 

select employee_id, first_name,manager_id from employees;

select e1.first_name"Employee Name" ,e2.first_name "Manager Name"
from employees e1,employees e2
where e1.manager_id = e2.employee_id order by 1;

select * from std;
select * from course;
select * from faculty;

select s.sname,c.cname,f.fname
from std s, course c, faculty f
where s.cid=c.cid
and  c.cid=f.cid;

CROSS JOIN

Cross join is a cartesian product no of rows in the first table is joined no of rows in a second table.

Example :

With out mentining any join conditions in query is retrieve all the records with the 

combination of two or more tables

Select s.sname,c.cname 
from stud s,course c

NOTES: IF COMPARING N NUMBER OF TABLES AND PERFORMS JOINS MEANS IN WHERE CLAUSE N-1 

CONDITIONS.

n number of table 
n-1 of condition

GROUP FUNCTIONS INTERVIEW QUESTIONS

Group functions work across many rows to produce one result.

True

Group functions include nulls in calculations. 

False. Group functions ignore null values. If you want to include null values, use the NVL function. 

The WHERE clause restricts rows prior to inclusion in a group calculation.

True

Display the highest, lowest, sum, and average salary of all employees. Label the columns Maximum, Minimum, Sum, and Average, respectively. Round your results to the nearest whole number. Place your SQL statement in a text file named lab5_6.sql.

SELECT   ROUND(MAX(salary),0) "Maximum", ROUND(MIN(salary),0) "Minimum", ROUND(SUM(salary),0) "Sum", ROUND(AVG(salary),0) "Average" FROM     employees;

Modify the query in lab5_4.sql to display the minimum, maximum, sum, and average salary for each job type. Resave lab5_4.sql to lab5_5.sql. Run the statement in lab5_5.sql.

SELECT   job_id, ROUND(MAX(salary),0) "Maximum", ROUND(MIN(salary),0) "Minimum", ROUND(SUM(salary),0) "Sum", ROUND(AVG(salary),0) "Average" FROM     employees GROUP BY job_id;

Write a query to display the number of people with the same job. 

Select job_id,count(job_id) from employees group by job_id;

Determine the number of managers without listing them. Label the column Number of Managers. Hint: Use the MANAGER_ID column to determine the number of managers

select count( distinct manager_id) " Number of Managers" from employees;

Write a query that displays the difference between the highest and lowest salaries. Label the column DIFFERENCE.

select Max(salary)-Min(Salary) " Difference" from employees;

Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any groups where the minimum salary is less than $6,000. Sort the output in descending order of salary.

Select manager_id,min(salary)from employees where manager_id is not null group by manager_id having Min(salary)>6000 order by min(salary) desc;

Write a query to display each department’s name, location, number of employees, and the average salary for all employees in that department. Label the columns Name, Location, Number of People, and Salary, respectively. Round the average salary to two decimal places.

select d.department_name"NAME",d.location_id "LOCATIONS", count(*)"NUMBER OF PEOPLE" ,round(avg(e.salary),2) "SAL" from departments d, employees e  where e.department_id=d.department_id group by d.department_name,d.location_id;

Create a query that will display the total number of employees and, of that total, the number of employees hired in 1995, 1996, 1997, and 1998. Create appropriate column headings.

SELECT  COUNT(*) total, 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),2005,1,0))"1995", 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1996,1,0))"1996", 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1997,1,0))"1997", 

        SUM(DECODE(TO_CHAR(hire_date, 'YYYY'),1998,1,0))"1998" FROM    employees;

SINGLE ROW FUNCTIONS INTERVIEW QUESTIONS

What are the types of functions available in oracle.

-Single Row Function
  Case MAnipulation Function
  Character Manipulation 
  Genaral funtion 
  Number Function
  Date Function

-Multiple Row Function
  Group Functions

Difference between single row function and multiple row function.

Single Row Function 
Input Sinlge Row 
Output Single Row 

Multiple Row Function
Input Multiple Rows
Output Single Rows

List out all the case and character functions.

CASE MANIPULATION FUNCTION

UPPER()
LOWER()
INITCAP()

CHARACTER MANIPULATION FUNCTION

SUBSTR()  INSTR()
LTRIP()   RTRIM()
LPAD()    RPAD()
REPLACE() TRANSLATE()
LENGTH()
CONCAT()
REVERSE()

Display first three characters from first name.

SELECT substr(First_name,1,3)"First_Name" FROM employees;

Display last two character from last name.

SELECT substr(last_name,-2,2)"LAST_NAME" FROM Employees;

Display all the first name and position of a in that name (first occurrence of a).    

SELECT First_name, instr(first_name,'a',1) FROM employees;

Display all the first name and position of a in that name (second occurrence of a)

SELECT First_Name,instr(First_name,'a',1,2) FROM employees;  

Display all the name which contain two or more number of a 's in the first name.

SELECT First_Name,instr(First_name,'a',1,2) from employees where instr(First_name,'a',1,2)<>0 

Difference between SUBSTR and INSTR function.

SUBSTR :
It returns a specified portion of a string 

INSTR
It returns a character position(ie occurances of the character).

Difference between REPLACE and TRANSLATE function.

Replace:The Replace Function replaces single character with multiple characters. 

Translate: Translate Function replaces single character with single character only.

Difference between LPAD and RPAD.

LPAD : To adjust the left hand side padding of charecters,numbers.
RPAD : To adjust the right hand side padding of charecters, numbers.

Difference between LTRIM and RTRIM.

LTRIM: Trim the charecters and numbers in left hand side.
RTRIM: Trim the charecters and numbers in Right hand side.

Display all the first name and its length.

SELECT First_name, length(first_name) from employees;

List out all the number functions in oracle.

ROUND();
MOD();
TRUNC();
POWER(); 

List out all the Date functions in oracle?

ADD_MONTHS();
MONTHS_BETWEEN();
NEXT_DAY();
LAST_DAY();

Display all the first name and their total year of experience. rename first name column name as name and second column name as Year of Exep.

SELECT First_Name "Name" ,round(months_between(sysdate,hire_date)/12)"Year of Experiance" from employees;

How to display months between two given date.

select months_between('12-jan-2016','12-dec-2016')from dual;

Write a query to display today's date.

Select sysdate from dual;

Write a query to display the date after 3 months from today.

Select add_months(sysdate, 3) from dual;

Display last date of the current month.

Select last_day(sysdate)from dual; 

Display the up coming Wednesday date.

Select next_day(sysdate,'wednesday') from dual; 

Which date function return number as output.  

To_Number()

What are all the type conversion functions available.

To_Char()
To_Date()
To_number()


How to convert date into character.

TO_CHAR ( Value, [format])

Value  : A Number or Date that will be converted to a string
Format  : This is the format it will be used to convert value to a string.

How to convert character in to date.

TO_DATE( string, format)

String  : The string that will be converted to a date.
Format : The format that will be used to convert string to date.

What is the use of general function.

Its Mainly used to process the NULL Values.

Explain NVL, NVL2 , NULLIF and COALESCE function with example.

NVL() : Its accept two arguments if the first arguments is null and its display the second arguments else its display the first arguments.

consider one example
select first_name,commission_pct,nvl(commission_pct,1)from employees;

NVL2( ): Its accept three arguments if the first arguments is null and its display the third arguments else its display the second arguments.

select first_name,commission_pct,nvl2(commission_pct,1,3)from employees;

Null If (): It accepts two arguments return null  if both arguments are equal else it will display the first arguments.

COALESCE():  It accept N Number of arguments returns the first not null values from the expression list. and Instead of NVL() we can use the COALESCE() .

select first_name,commission_pct,coalesce(commission_pct,null,null,1,null)from employees;
SQL> select nullif(5,10) from dual;COALESCE

What are all the aggregate functions available in oracle.

Aggregate functions means group function

MIN()
MAX()
SUM()
AVG()
COUNT()

Write a query to select maximum salary from employees table.

SELECT MAX(SALARY) FROM EMPLOYEES;

Write a query to select second maximum salary from employees table.

SELECT max(salary) FROM Employees  WHERE salary NOT IN (SELECT max(salary) FROM Employees)  ;

Display average salary in the department 90.

select avg(salary) from employees where department_id=90;

Display number of employees working in department 90 and 60.

select count(*) from employees where department_id in( '90','60');

Display all the department id and its maximum salary.

Select department_id,max(salary) from employees group by department_id order by 1;

Display all the department id and number of employees working in that department.   

select department_id, count(*) from employees group by department_id;

Display all the department id and salary allocated for that department.

select department_id,sum(salary) from employees group by department_id order by 1; 

Display all the department id and number of employees working in that department. Total no employees working for the particular department must be greater than 30.

select department_id, count(*)from employees group by department_id having count(*)>30;  

Difference between WHERE clause and HAVING clause. 

Where 

Its followed by from clause

Having

Its followed by group by clause

Write a query to display the current date. Label the column Date.

Select sysdate "Date" from dual;

For each employee, display the employee number, last_name, salary, and salary increased by 15% and expressed as a whole number. Label the column New Salary. Place your SQL statement in a text file named lab3_2.sql.

Select Employee_id,Last_name, Salary,Round(( Salary*(15/100)+salary)) as "New Salary" From 

Employees;

Modify your query lab3_2.sql to add a column that subtracts the old salary from the new salary. Label the column Increase. Save the contents of the file as lab3_4.sql. Run the revised query. 

Select   Employee_id,Last_name, Salary,Round(( Salary*(15/100)+salary))  as "New  Salary",Round(( Salary*(15/100)+salary)) - Salary as "Increment" From Employees;

Write a query that displays the employee’s last names with the first letter capitalized and all other letters lowercase, and the length of the names, for all employees whose name starts with J, A, or M. Give each column an appropriate label. Sort the results by the employees’ last names.

Select Initcap(Last_name) Last_name ,Length(Last_name) Length From Employees Where Last_Name like 'J%' or Last_Name like 'A%' or Last_Name like 'M%'

order by Last_name desc;

For each employee, display the employee’s last name, and calculate the number of months between today and the date the employee was hired. Label the column MONTHS_WORKED. Order your results by the number of months employed. Round the number of months up to the closest whole number.

Select Last_Name, Round (Months_between(sysdate ,Hire_date)) "Months Worked" from employees;

Write a query that produces the following for each employee: <employee last name> earns <salary> monthly but wants <3 times salary>. Label the column Dream Salaries.

Select Last_Name ||' Earns ' || salary ||'Monthly But Wants '|| Salary*3 "Dream Salaries" from employees;

Create a query to display the last name and salary for all employees. Format the salary to be 15 characters long, left-padded with $. Label the column SALARY. 

select last_name, Lpad(salary,'15','$') from employees; 

Display each employee’s last name, hire date, and salary review date, which is the first Monday after six months of service. Label the column REVIEW. Format the dates to appear in the format similar to “Monday, the Thirty-First of July, 2000.”

select Last_name ,Hire_date , add_months(Hire_date,6) as saldate from employees;

SELECT last_name,hire_date,TO_CHAR(NEXT_DAY(ADD_MONTHS(hire_date, 6), 'Monday'),'DAY,"THE" DDSP "OF" MONTH YYYY') "REVIEW"

FROM employees;

Display the last name, hire date, and day of the week on which the employee started. Label the column DAY. Order the results by the day of the week starting with Monday. 

Select last_name, hire_date, to_char(To_date(Hire_date),'Day') "DAY" from employees order by to_char(hire_date-1,'d');

Select last_name, hire_date, to_char((Hire_date),'Day') from employees;

Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, put “No Commission.” Label the column COMM.

Select last_name,NVL(NULL,'No Commission') from employees;

Select last_name,commission_pct, NVL(Null,'No Commission') from employees;

Create a query that displays the employees’ last names and commission amounts. If an employee does not earn commission, put “No Commission.” Label the column COMM.

SELECT Last_Name,NVL(TO_Char(Commission_pct),'No Commision') "COMM" FROM Employees;

Create a query that displays the employees’ last names and indicates the amounts of their annual salaries with asterisks. Each asterisk signifies a thousand dollars. Sort the data in descending order of salary. Label the column EMPLOYEES_AND_THEIR_SALARIES. 

SELECT last_name||' '|| rpad(' ', (salary*12)/1000, '*') EMPLOYEES_AND_THEIR_SALARIES FROM  employees ORDER BY salary DESC;

Using the DECODE function, write a query that displays the grade of all employees based on the value of the column JOB_ID, as per the following data:

Job Grade

AD_PRES A

ST_MAN B

IT_PROG C

SA_REP D

ST_CLERK E

None of the above 0

SELECT job_id, decode (job_id, 'ST_CLERK',  'E', 

                                'SA_REP',   'D', 

                               'IT_PROG',   'C', 

                               'ST_MAN',    'B', 

                               'AD_PRES',   'A', '0')GRADE FROM employees;