fix your tv now  

Hey ppl i am back with an interesting update on furniture and accessories. To know about it more just feel free to explore the post below.

Recently i bought a latest soni bravia 41 inch LCD tv. I really found very difficult to fix it in my room wall. One of my friend suggested about TV wall mount . It's really a cool accessory that made my job much easier. I am sure you all will like it very much.

what for you waiting still ? just go and grab the opportunity now... don't forget to spread the word to all your friends. Keep checking my blog for more latest updates.

kindly leave your comments if any below..

Have fun and Enjoy life ;-)

Easy to use  

Hey ppl i am back with an interesting update on home appliances. To know about it more just read on...

Have you ever tried out laundry hamper before ??? if not then explore the link to know about it more. It's an easy to use tool that will helps to lift bulk laundry items. I am sure you all will like it very much. Just fill, zip and roll thats it you are done with it. It really gives you very good comfort. I found it really useful. Don't forget to spread the word to all your friends. Keep checking my blog for more latest tips and updates.
Have fun and Enjoy life... kindly leave your comments if any below...

Searching Your Perfumes Easily  

Perfumes are often presented as part of a gift set at the best moment, or to get matching with aroma in your body, if now you are searching for womens perfume and fragrances you should check this http://www.savebuckets.co.uk/browse/body-care-cosmetics/perfumes/women/. This site is like Amazon, but this site is especially for perfumes. In this site you can search a lot of perfumes that you like and find the cheapest prices. This site link to another sites which sell over a hundred of perfumes like Britney Spears- Fantasy, Gucci Rush, Diesel for elite unlimited women and many others, you don’t need to search in Google or Amazon to find the best price for your perfumes.. You can compare prices for perfumes in this site. So, you can find the cheapest perfumes or the perfumes you like for a perfect gift or treat yourself with easily searching in this site. Don’t forget to look out for special offers or discounts when searching for a perfume.

SQL assignments  


1. Using select statements

1. Write a query to list all the tables in HR schema
2. Display employeeID, first name, last name, email, phone number from employees table.
3. Display all columns of JOB_HISTORY table.
4. Display the first name, last name concatenated and appearing under the column “EMPLOYEE NAME”.
5. Write a query to display a sentence like Employee name working as JOB_ID for every employee record.
6. Write a query to display names of all the employees with their annual salary.


2. Scoping and ordering of rows

1. Write a query to select rows from the employees table with the department number of 30.
2. Write a query to select the name, job, and salary and department number of all employees except purchase clerks from department number 30.
3. Display the employee name and department number of all employees in dept 10 and 30 in alphabetical order by name.
4. Display the employee name, job_id and email of those employees who were hired between 1 Jan 1993 and 1 Dec 1995.Order the results in the ascending order of hiredate.
5. Write a query to search for employees with the pattern 'A_B' in their name.

3. SQL Functions

1. Write a query to display second string of  job_title in the Jobs table only if the job_title has more than one string.
2. Replace occurrences of Manager in the job_title of jobs table with analyst.
3. Write a query to display the previous job history of the employees with years of experience in each job category.
4. Write a query to display the details of employees with ‘ON BENCH’ value for department ID if no employees have been assigned to that department.
5. Write a query to display revised salary for selected employees
a. If he is a manager give 50%hike
b. If he is a representative give 20%hike
c. if he is a programmer give 30%hike.
(Use case expression)
6. Write a query to display country wise details with the region name.
(Use the data in the region table and use decode function)


4. Joins

1. Write a query to display names of employees and department names of those employees who are working in Europe.
2. Write a query to display department wise details including the Manager’s name and if a particular department does not have any manager, display the message ‘no manager assigned yet’.
3. Display information on all the department managers.
Manager ID, Manager Name, His current department name and previous department name.
4. Display old job title and current job title for each employee in the organization.

5. Group Functions & Group by, Having Clause 

1. Find the number of employees working in each country with country name and no of employees.
2. Find the number of employees   reporting to each department head.
3. To return the number of employees and their average yearly salary across all possible combinations of department and job category.
Display department name, job_title, total no of employees, average salary.

6. Sub-Queries

1. Write a query to give all employees in the employees table a 10% raise if they have changed jobs.
2. List the number of employees working in each department with the department name.
3. Find the details of employees who have switched jobs at least twice.
4. Find top 3 earners in the organization with their department names.
5. Display the manager number and the salary of the lowest paid employee for that manager. Exclude anyone whose manager is not known. Exclude any group where the minimum salary is less than 1000. Sort the output in descending order of salary.
6. Suppose you need to know the employee name and department name of all employees that work in a department that has at least 3 employees. Write a SQL query to retrieve this information. Order the list in alphabetical order first by department name, then by employee name


7. DDL and DML statements

1. Create a table manager_data which stores information about all the managers
Manager ID, Manager Name, Phone number, email, department name and location. The department name should be a valid one having reference in the existing dept table in the HR schema.
2. Ensure that all the department names are unique.
3.Insert all the employees contact details having employeeID, location ID and location address into contact table and insert the details of emails of all the employees into email_info table which will store employeeID, employee name and his email_id.
(Using Multitable insert)
4. Write a query to retrieve the details of the employees with employeeID, name,job_id and department name. If they are working in sales department, insert those records into sales_employees table with employeeID, name and job_id. If they are also managers insert those records into sales_managers table with employeeID,name and Job_ID.
5.Insert all employee records into experience wise employee processing tables.
Level 1-having 6 to 10 years of experience i.e.
Level 2- having 11 to 15 years of experience
Level 3- having 16 to 20 years of experience

 8. Views, indexes and synonyms

1. Create a non-unique index on department name of departments table. And query the associated data dictionary to confirm the index creation.

2. Create a view called SALARY_VIEW based on the employee names, department names, job_title, salary and annual salary for all employees.

3. Create a sequence to be used with primary key column of locations table. The sequence should start at 4000 and have a maximum value of 5000.Have your sequence increment by 100 numbers. Insert two rows into locations table. And use the sequence values for the location ID column.

EXCEPTIONS in PLSQL  

PREDEFINED EXCEPTIONS

1.
SQL> DECLARE
2 VAR EMP.SAL%TYPE;
3 BEGIN
4 INSERT INTO STUDENT VALUES(6780,'KARTHIK',12); // this statement not executed due to no data found error at line 5
5 SELECT SAL INTO VAR FROM EMP WHERE ENAME='ABC';
6 DBMS_OUTPUT.PUT_LINE(VAR);
7 END;
8 /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5


SQL> SELECT *FROM STUDENT;

ROLLNO NAME STD
---------- ---------- ----------
2346 TARUN 9
7890 MARIE 11

SQL> DECLARE
2 VAR EMP.SAL%TYPE;
3 BEGIN
4 INSERT INTO STUDENT VALUES(6780,'KARTHIK',12);// now this statement get executed by handling no data found exception
5 SELECT SAL INTO VAR FROM EMP WHERE ENAME='ABC';
6 DBMS_OUTPUT.PUT_LINE(VAR);
7 EXCEPTION
8 WHEN NO_DATA_FOUND THEN
9 DBMS_OUTPUT.PUT_LINE('no such employee');
10 END;
11 /

no such employee

PL/SQL procedure successfully completed.



SQL> SELECT *FROM STUDENT;

ROLLNO NAME STD
---------- ---------- ----------
2346 TARUN 9
7890 MARIE 11
6780 karthik 12



2.
SQL> DECLARE
2 RESULT NUMBER(4);
3 BEGIN
4 RESULT:=5/0;
5 EXCEPTION
6 WHEN NO_DATA_FOUND THEN
7 DBMS_OUTPUT.PUT_LINE('No such data');
8 WHEN OTHERS THEN
9 DBMS_OUTPUT.PUT_LINE(SQLCODE);
10 DBMS_OUTPUT.PUT_LINE(SQLERRM);
11 END;
12 /

-1476
ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.




USER DEFINED EXCEPTIONS


• for logical error handling



SQL> SELECT *FROM DEPT;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON


SQL> DECLARE
2 INVALID_DEPTNO EXCEPTION;
3 BEGIN
4 UPDATE DEPT
5 SET DNAME='MANAGEMENT',LOC='CHENNAI'
6 WHERE DEPTNO=&DNO;
7 IF(SQL%NOTFOUND) THEN
8 RAISE INVALID_DEPTNO;
9 END IF;
10 COMMIT;
11 EXCEPTION
12 WHEN INVALID_DEPTNO THEN
13 DBMS_OUTPUT.PUT_LINE('No such deptno');
14 END;
15 /

Enter value for dno: 50
old 6: WHERE DEPTNO=&DNO;
new 6: WHERE DEPTNO=50;

No such deptno

PL/SQL procedure successfully completed.


NON PREDEFINED EXCEPTIONS

1 DECLARE
2 unik_con EXCEPTION;
3 PRAGMA EXCEPTION_INIT(unik_con,-0001); // Here -0001 is the std code for unique key violation error and unik_con is user defined
4 BEGIN
5 INSERT INTO dept values(10,'Finance','ch');
6 EXCEPTION
7 WHEN unik_con THEN
8 DBMS_OUTPUT.PUT_LINE('Exception Caught');
9 when others then
10 DBMS_OUTPUT.PUT_LINE('Other Exceptions');
11* END;
SQL> /
Exception Caught


RAISE_APPLICATION_ERROR

in executable section

1 DECLARE
2 EMP_NF EXCEPTION;
3 PRAGMA EXCEPTION_INIT(EMP_NF,-20013);
4 BEGIN
5 DELETE FROM EMP WHERE EMPNO=0009;
6 IF(SQL%NOTFOUND) THEN
7 RAISE_APPLICATION_ERROR(-20013,'No such empno');
8 END IF;
9* END;
SQL> /
DECLARE
*
ERROR at line 1:
ORA-20013: No such empno
ORA-06512: at line 7


1 DECLARE
2 EMP_NF EXCEPTION;
3 PRAGMA EXCEPTION_INIT(EMP_NF,-20013);
4 BEGIN
5 DELETE FROM EMP WHERE EMPNO=0009;
6 IF(SQL%NOTFOUND) THEN
7 RAISE_APPLICATION_ERROR(-20013,'No such empno');
8 END IF;
9 EXCEPTION
10 WHEN EMP_NF THEN
11 DBMS_OUTPUT.PUT_LINE('Exception caught');
12* END;

SQL> /
Exception caught

in exception section

SQL> DECLARE
2 VAR EMP.SAL%TYPE;
3 BEGIN
4 SELECT SAL INTO VAR FROM EMP WHERE ENAME='ABC';
5 DBMS_OUTPUT.PUT_LINE(VAR);
6 EXCEPTION
7 WHEN NO_DATA_FOUND THEN
8 RAISE_APPLICATION_ERROR(-20009,'no such employee');
9 END;
10 /
DECLARE
*
ERROR at line 1:
ORA-20009: no such employee
ORA-06512: at line 8


PROPAGATION

Basic propagation:

SQL> declare
2 bla number;
3 begin
4 declare
5 no number;
6 begin
7 select deptno into no from emp where deptno=80; -- no data found exception arised
8 dbms_output.put_line('This wont be printed'); -- skipped
9 exception
10 when no_data_found then
11 dbms_output.put_line('Exception caught here');
12 end;
13 select deptno into bla from emp where ename='KING';
14 dbms_output.put_line(bla); -- prints value 10
15 end;
16 /

Exception caught here
10

PL/SQL procedure successfully completed.




Relevant exception caught (rest of the statements skipped)

SQL> declare
2 bla number;
3 begin
4 declare
5 no number;
6 begin
7 select deptno into no from emp where deptno=10; -- control transferred to line 15
8 dbms_output.put_line('This wont be printed');
9 exception
10 when no_data_found then
11 dbms_output.put_line('Exception not caught here');
12 end;
13 select deptno into bla from emp where ename='KING';
14 dbms_output.put_line('bla wont be printed');
15 exception
16 when too_many_rows then
17 dbms_output.put_line('Many rows access exception');
18 end;
19 /
Many rows access exception

PL/SQL procedure successfully completed.



Scope of exception:

SQL> declare
2 bla number;
3 begin
4 declare
5 no number;
6 begin
7 select deptno into no from emp where deptno=80; -- control transferred to line 9
8 dbms_output.put_line('This wont be printed');
9 exception
10 when no_data_found then
11 dbms_output.put_line('Exception caught here');
12 end;
13 select deptno into bla from emp where ename='KING';
14 dbms_output.put_line(bla);
15 exception -- This wont get caught
16 when no_data_found then
17 dbms_output.put_line('Exception not caught');
18 end;
19 /

Exception caught here
10

PL/SQL procedure successfully completed.



Calling exception from exception (Re-raise)

SQL> declare
2 bla number;
3 begin
4 declare
5 no number;
6 begin
7 select deptno into no from emp where deptno=80; -- ctrl tranferred to line 9
8 dbms_output.put_line('This wont be printed');
9 exception
10 when no_data_found then
11 raise; -- ctrl transferred to line 15
12 end;
13 select deptno into bla from emp where ename='KING';
14 dbms_output.put_line(bla);
15 exception
16 when no_data_found then
17 dbms_output.put_line('Exception caught here oly');
18 end;
19 /
Exception caught here oly

PL/SQL procedure successfully completed.





Older Posts