ORA-28001: the password has expired (DBD ERROR: OCISessionBegin)


I received the above error when try to start Oracle Enterprise Manager (OEM)

And the reasons might be sysman,dbsnmp  password expired or lock due to user password setting The simple steps to unlock the sysman user.

STEP 1: Sign in SQLPlus as sysman in my case only password expired as shown in below image  and if remember the old sysman password and accoring
to password setting if allow you to change the
password then change the password
else ,
Sign in as SYSDBA and run the below sql
ALTER USER SYSMAN IDENTIFIED BY NEW_PASSWORD ACCOUNT UNLOCK;
STEP 2: Once you change the password and unlock the sysman password try to log in to OEM


STEP 3: Try to log in as sys  as sysdba ,and once able to log in this will prompt for all user expired password and new password ,assign the new password for expired user password .


STEP 4: Log out and log in again and enjoy working with OEM


I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful to solve your error ? If so, please share it with your friends:

Transactions Questions


1.    Add a new row to a table, delete a row from a table and modify a row in a table. Make the Changes to more than one table. Now enter the command ROLLBACK. What has happened to your changes?

They have all been lost

NOTE: This answer may be wrong depending on your SQL program by default setting is in oracle in auto commit is OFF where as in sql server its ON So explicitly in oracle if we change the default setting of auto commit to ON then this will save the all the transaction.

2.    Produce a further set of inserts, deletes and modifications to the tables, enter the command COMMIT and then produce yet more changes to the data. Now enter the command ROLLBACK. What has happened to your changes?

They have all been lost back to the COMMIT command

NOTE: Set of insert, delete  are DML where as modification to the table is DML statement and all the DML statement are auto commit so upto the modification all the changes made save permanently and after modification is depend on the SQL program.

3.    Produce a further set of inserts, deletes and modifications to the tables, then create a new table called TEMP and then produce yet more changes to the data. Now enter the command ROLLBACK. What has happened to your changes?

They have all been lost back to the creation of the TEMP table

4.    Produce a further set of inserts, deletes and modifications to the tables, and then exit the SQL program. Re-start the SQL program and produce yet more changes to the data. Now enter the command ROLLBACK. What has happened to your changes?

Changes have been made before exiting the program save, but changes made after
restarting it have been lost

I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:





Views Questions

1.    Create a view called VIEWEMP which gives the same displays as EMP but is based on the EMP1 And JOBS tables.

CREATE VIEW viewemp
AS
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp1
INNER JOIN jobs
ON
emp1.jobno = jobs.jobno;

“OR”

CREATE VIEW viewemp
AS
SELECT emp1.*
FROM emp1
INNER JOIN jobs USING(jobno)

2.    Create a view called DEPTSUM with two columns called DEPARTMENT and SUMSAL containing the name of each department and the sum of the salaries for all employees in the department. Look at it using a SELECT command. Now alter one of the salaries in the EMP table using the UPDATE command is the DEPTSUM affected?

CREATE VIEW deptsum
AS
SELECT dname, SUM(sal)
FROM emp
INNER JOIN dept
ON emp.deptno = dept.deptno
GROUP BY dname;

SELECT * FROM deptsum;

UPDATE emp SET sal = 1000 WHERE empno = 7369;

SELECT * FROM deptsum;

DEPTSUM has been updated.

Note: We can only update any table using view if the view is simple view.

3.    Create a view called BOSS which has the name and number of each employee with the name and number of his or her manager (with blanks alongside any employee that has no manager). Give each column in the view a suitable name. Change one of the entries in the EMP table to give an employee a different manager and check this is reected in the BOSS view.

CREATE VIEW boss
AS
SELECT a.ename AS ename, a.empno AS empno,
b.ename AS bname, b.empno AS bossno
FROM emp a
LEFT OUTER JOIN emp b
ON
 a.mgr = b.empno;

SELECT * FROM boss;

UPDATE emp SET mgr=7566 WHERE empno = 7934;

SELECT * FROM boss;

CHECK (empno>= 7000 AND empno<= 8000));

4.    What happens if an attempt is made to insert Delete the DEPTSUM and BOSS views.

DROP VIEW deptsum;
DROP VIEW boss;

5.    Create a view called SALES1 which has all the columns of the EMP table except the salary and commission columns, and with only the rows corresponding to employees in department number 30.

CREATE VIEW sales1
AS
SELECT empno, ename, job, mgr, hiredate, deptno
FROM emp
WHERE deptno = 30;

6.    Create a view called SALES2 which has the same columns as SALES1 except the department name  is included instead of the department number. Do this by basing the new view on a join of the SALES1 view joined to the DEPT table.

CREATE VIEW sales2
AS
SELECT empno, ename, job, mgr, hiredate, dname
FROM sales1
INNER JOIN dept
ON
sales1.deptno = dept.deptno;

7.    Insert a new employee into the SALES1 view with the INSERT command. Look in the EMP table and SALES1 and SALES2 views to check it is there.
INSERT INTO sales1
VALUES (8101, 'BOB', 'ANALYST', 7839, '09/06/1982', 20);

Do it by your self.

8.    Insert a new employee into the SALES2 view with the INSERT command. Can this be done?
INSERT INTO sales2
VALUES (8101, 'BOB', 'ANALYST', 7839, '09/06/1982', 20);

Do it by your self.

9.    Alter the view SALES1 so that it has an additional column called INCOME with the sum of the
salary and commission for each employee. (Is it really possible to alter a view?)

DROP VIEW sales1;
DROP VIEW sales1 CASCADE;

CREATE VIEW sales1
AS
SELECT empno, ename, job, mgr, hiredate, deptno, sal + comm AS income
FROM emp WHERE comm IS NOT null AND deptno = 30
UNION
SELECT empno, ename, job, mgr, hiredate, deptno, sal AS income
FROM emp WHERE comm IS null AND deptno = 30;

“OR”

CREATE VIEW sales1 AS
SELECT empno, ename, job, mgr, hiredate, deptno, NVL2(comm,sal + comm,sal) AS income
FROM emp WHERE  deptno = 30

SELECT * FROM sales1;

It is not possible to alter a view, only to drop it and re-create it

10.    Now the SALES1 view has the extra INCOME column is it possible to insert another employee to the department using this view?

INSERT INTO sales1
VALUES (8101, 'BOB', 'ANALYST', 7839, '09/06/1982', 20);
         
Do it by your self.

I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:





SELECT with Subqueries Questions

1.    Display the names and job titles of all employees with the same job as Jones.

SELECT ename, job FROM emp
WHERE job = (SELECT job FROM emp WHERE ename = 'JONES');

2.    Display the names and department name of all employees working in the same city as Jones.

SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno
WHERE loc = (SELECT loc FROM emp INNER JOIN dept ON emp.deptno = DEPT.deptno
                     WHERE ename = 'JONES');
                “OR”
SELECT ename, dname FROM emp INNER JOIN dept USING (deptno)
WHERE loc = (SELECT loc FROM emp INNER JOIN dept USING (deptno)
                     WHERE ename = 'JONES');

3.    Display the name of the employee whose salary is the lowest.

SELECT ename FROM emp WHERE sal = (SELECT min(sal) FROM emp);
“OR”
SELECT ename FROM emp, (SELECT min(sal) as sal  FROM emp) minsal
WHERE emp.sal = minsal.sal

4.    Display the names of all employees except the lowest paid.

SELECT ename FROM emp WHERE sal> (SELECT min(sal) FROM emp);

5.    Display the names of all employees whose job title is the same as anyone in the sales dept.

SELECT ename FROM emp
WHERE job IN (SELECT DISTINCT job FROM emp INNER JOIN USING(deptno)
WHERE dname = 'SALES');

6.    Display the names of all employees who work in a department that employs an analyst.

SELECT ename FROM emp WHERE deptno IN (SELECT DISTINCT emp.deptno
FROM emp INNER JOIN dept ON emp.deptno = dept.deptno WHERE job = 'ANALYST');

7.    Display the names of all employees with their job title, their current salary and their salary following a 10% pay rise for clerks and a 7% pay rise for all other employees.

SELECT ename, job, sal, 1.1 * sal AS "newsal" FROM emp WHERE job = 'CLERK'
UNION
SELECT ename, job, sal, 1.07 * sal AS "newsal" FROM emp WHERE job <> 'CLERK';

8.    Display the names of all employees with their salary and commission earned. Employees with a null commission _eld should have 0 in the commission column.

SELECT ename, sal, comm FROM emp WHERE comm IS NOT NULL
UNION
SELECT ename, sal, 0 FROM emp WHERE comm IS NULL;
                “OR”
SELECT ename, sal, NVL(comm,0) as comm FROM emp


9.    Display the names of ALL employees with the total they have earned (ie. salary plus commission).

SELECT ename, sal + comm AS "earnings" FROM emp WHERE comm IS NOT NULL
UNION
SELECT ename, sal FROM emp WHERE comm IS NULL;
“OR”
SELECT ename, NVL2(comm,sal + comm,sal) AS "earnings" FROM emp

10.    Repeat the display for the last question but this time display in descending order of earnings.

SELECT ename, sal + comm AS "earnings" FROM emp WHERE comm IS NOT NULL
UNION
SELECT ename, sal FROM emp WHERE comm IS NULL ORDER BY earnings DESC;
“OR”
SELECT ename, NVL2(comm,sal + comm,sal) AS "earnings" FROM emp ORDER BY earnings DESC;

I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:



Join SELECT Command Questions

1.    Display the name of each employee with his department name.

SELECT ename, dname FROM emp , dept where  emp.deptno = dept.deptno;
“OR”
SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
“0R”
SELECT ename, dname FROM emp INNER JOIN dept USING(deptno);

2.    Display a list of all departments with the employees in each department.

SELECT dname, ename FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno;
“OR”
SELECT dname, ename FROM dept LEFT OUTER JOIN emp USING(dept);
“OR”
SELECT dname, ename FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;
“OR”
SELECT dname, ename FROM dept , emp WHERE  dept.deptno = emp.deptno(+)
            NOTE: OUTER keyword is optional in LEFT OUTER JOIN.

3.    Display all the departments with the manager for that department.

SELECT dname, ename FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
WHERE job = 'MANAGER';

4.    Display the names of each employee with the name of his/her boss.

SELECT s.ename, b.ename FROM emp s INNER JOIN emp b ON s.mgr = b.empno;

5.    Display the names of each employee with the name of his/her boss with a blank for the boss of the president.

SELECT s.ename, b.ename FROM emp s LEFT OUTER JOIN emp b ON s.mgr = b.empno;

6.    Display the employee number and name of each employee who manages other employees with the number of people he or she manages.

SELECT a.mgr, b.ename, count(a.mgr) FROM empa INNER JOIN emp b
ON a.mgr = b.empno WHERE a.mgr IS NOT NULL GROUP BY a.mgr, b.ename;

7.    Repeat the display for the last question, but this time display the rows in descending order of the number of employees managed.

SELECT a.mgr, b.ename, count(a.mgr) FROM empa INNER JOIN emp b
ON a.mgr = b.empno WHERE a.mgr IS NOT NULL GROUP BY a.mgr, b.ename
ORDER BY count(a.mgr) DESC;

I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:


Group SELECT Command Questions


1.    Display the maximum, minimum and average salary and commission earned.

SELECT max(sal), min(sal), avg(sal), max(comm), min(comm), avg(comm) FROM emp;

2.    Display the department number, total salary payout and total commission payout for each
department.

SELECT deptno, sum(sal), sum(comm) FROM emp GROUP BY deptno;

3.    Display the department number, total salary payout and total commission payout for each
Department that pays at least one employee commission.

SELECT deptno,sum(sal),sum(comm) FROM emp GROUP BY deptno HAVING sum(comm)> 0;

4.    Display the department number and number of clerks in each department.

SELECT deptno, count(job) FROM emp WHERE job = 'CLERK' GROUP BY deptno;

5.    Display the department number and total salary of employees in each department that employs four or more people.

SELECT deptno, sum(sal) FROM emp GROUP BY deptno HAVING count(empno) >= 4;

6.    Display the employee number of each employee who manages other employees with the number of people he or she manages.

SELECT mgr, count(mgr) FROM emp WHERE mgr IS NOT NULL GROUP BY mgr;


I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:


Creating and Modifying Tables

1. Create a new table called JOBS with two field, a SMALLINT called JOBNO and a 15 character text field called JOB.

CREATE TABLE jobs ( jobno SMALLINT, job VARCHAR(15) );

2. Fill your new JOBS table with null values for the JOBNO and the job values from the EMP table.There should be only one row with each job type (ie. no repeats).

INSERT INTO jobs (job) SELECT DISTINCT job FROM emp;

3. Give a unique job number to each job type.

 UPDATE jobs SET jobno= 10 WHERE job = 'ANALYST';
 UPDATE jobs SET jobno= 20 WHERE job = 'CLERK';
 UPDATE jobs SET jobno= 30 WHERE job = 'MANAGER';
 UPDATE jobs SET jobno= 40 WHERE job = 'SALESMAN';
 UPDATE jobs SET jobno= 50 WHERE job = 'PRESIDENT';

4. Create a new empty table called EMP1. This table should have the same _elds as EMP but  with an additional _field called JOBNO of type SMALLINT. (Note|also make EMPNO and DEPTNO type SMALLINT.

CREATE TABLE "emp1" (
"empno" SMALLINT,
"ename" VARCHAR(15),
"job" VARCHAR(15),
"mgr" SMALLINT,
"hiredate" DATE,
"sal" SMALLINT,
"comm" SMALLINT,
"deptno" SMALLINT,
"jobno" SMALLINT);

5. Fill your new EMP1 table with the data from EMP and JOBS.

INSERT INTO emp1 SELECT emp.*, jobno FROM emp INNER JOIN jobs using(job);

6. Remove the JOB column from your EMP1 table.

ALTER TABLE emp1 DROP COLUMN job;
“OR”
SELECT empno, ename, mgr, hiredate, sal, comm, deptno, jobno
INTO temp FROM emp1;
DROP TABLE emp1;
ALTER TABLE temp RENAME TO emp1;

7. Display the data from the EMP1 and JOBS tables so that the output is identical to the original EMP table.

SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp1
INNER JOIN jobs ON emp1.jobno = jobs.jobno;


8. Add a new Department to the DEPT table, and add a Manager and two Clerks to the EMP table that will belong to the new department.

INSERT INTO dept VALUES (50, 'NEWDEPT', 'LONDON');
INSERT INTO emp VALUES (8001, 'FRED', 'MANAGER', 7839, '14/01/1984', 3100, null, 50);
INSERT INTO emp VALUES (8002, 'JIM', 'CLERK', 8001, '18/04/1984', 1020, null, 50);
INSERT INTO emp VALUES (8003, 'SHEILA', 'CLERK', 8001, '08/12/1984', 955, null, 50);

9. Transfer one of the new clerks to a different department and transfer one of the previously existing clerks to your new department.
UPDATE emp SET deptno = 40, mgr = 7788 WHERE empno = 8002;
UPDATE emp SET deptno = 50, mgr = 8001 WHERE empno = 7876;


I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:


SELECT Command Questions


1. Display all information in the tables EMP and DEPT.

    SELECT * FROM emp;
    SELECT * FROM dept;

2. Display only the hire date and employee name for each employee.

    SELECT hiredate, ename FROM emp;

3. Display the hire date, name and department number for all clerks.

    SELECT hiredate, ename, deptno FROM emp WHERE job = 'CLERK';
     “OR”
   SELECT hiredate, ename, deptno FROM emp WHERE UPPER( job) LIKE %CLERK%';

4. Display the names and salaries of all employees with a salary greater than 2000.

    SELECT ename, sal FROM emp WHERE sal> 2000;

5. Display the names of all employees with an `A' in their name.

    SELECT ename FROM emp WHERE ename LIKE '%A%';

6. Display the names of all employees with exactly 5 letters in their name.

    SELECT ename FROM emp WHERE length(ename)=5

NOTE: length function return the length of the string

7. Display the names and hire dates of all employees hired in 1981 or 1982 (Note in Visual Basic or Access SQL you need to refer to dates in a WHERE clause between #s, eg. #1 Jan 2000#).

    SELECT ename, hiredate FROM emp WHERE TO_CHAR(hiredate,’YYYY’) IN (1981,1982)
    “OR”
     SELECT ename, hiredate FROM emp WHERE hiredate LIKE '%1981' OR     hiredate LIKE '%1982';
    “OR”
     SELECT ename, hiredate FROM emp WHERE hiredate>= '1/1/1981' AND   hiredate<='31/12/1982';

NOTE: TO_CHAR(hiredate,’YYYY’) return the year

8. Display the names and dates of employees with the column headers \Name" and \Start Date"

SELECT ename AS "Name", hiredate AS "Start Date" FROM emp;

9. Display the names and hire dates of all employees in the order they were hired.

  SELECT ename, hiredate FROM emp ORDER BY hiredate;

NOTE: ORDER BY default order is ASC .if needed the data in descending order then use DESC along with column name.
ORDER BY COLUMN_NAME DESC/ASC

10. Display the names and salaries of all employees in reverse salary order.

SELECT ename, sal FROM emp ORDER BY sal DESC;

11. Display `ename of department deptno earned commission $' for each salesman in reverse salary order.

     SELECT ename || ' of department ' || deptno || ' earned commission $' || comm
     FROM emp WHERE job = 'SALESMAN' ORDER BY sal DESC;

NOTE: Instead of pipe(||) we can use nested CONCAT funcation .

12. Display the department numbers of all departments employing a clerk.
      SELECT DISTINCT deptno FROM emp WHERE emp.job = 'CLERK';

I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:


SAVEPOINT

Now we will see how savepoint is used in COMMIT and ROLLBACK.

Savepoint:
  • Way of implementing subtranction .
Syntax:

Savepoint name;

Here Savepoint is a keyword used for creating the savepoint name is name of savepoint to be created.all changes made  after a savepoint has been declared can be undone by issuing  the ROLLBACK to a savepoint name .we can use it save the changes permanently by issuing the COMMIT to a savepoint name.

Example:

  Lets say we create a savepoint ‘s’ by issuing the following statement

Savepoint s;


 Now we are going to update different coloumn by update statement

Update emp set salary=salary+100;

Update emp set emp_contactno=’9830755710’ where emp_name=’abhishek’;


Finally we can leave the above changes by issuing the

Rollback to savepoint s ;

This will discard all the above changes made to database upto the savepoint s.
If we want to store the chages permanently to the database then we can use the commit command instead of rollback like this

Commit to savepoint s;

This will save all the above changes made to database upto the savepoint s.

Related Article:
   I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

ROLLBACK

 In this lesson we are going to study about how to discard all changes made in database.


Rollback:
  • It is used to discard the changes made on database .
  • This command discards all the changes made on the database since the last transaction .
Syntax:

ROLLBACK;

  After excuting  the ROLLBACK statement the state of data is rolled back to the way it was the changes where made .

Example:
  Lets say we are going to update the salary of all employees  with 100.

Update emp set salary=salary+100;


  This statement update the salary of  emp table  with 100 mean while the chages  made on this table not written to the database until the session is closed or the user COMMIT the transaction .
 ROLLBACK  is used in a situation when we want to leave the above changes .So we can ROLLBACK  to above changes made on the database using the ROLLBACK command.

ROLLBACK;


Related Article:

 I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

COMMIT

 In the previous section we have discussed about
 but in this section we  are going to study about how to save changes pemanently in a database .

Commit statement:

  • Commit is a transaction control statement it is used to set the changes permanently in the database.
  • Used to instruct the database to save all changes made  to the database permanently .
  • To end a transaction within  a database and make all changes visible to other users of the database.
Syntax:

COMMIT;

Example:

Lets say we are going to update the salary of all employees  with 100.

  Update emp set salary=salary+100;


After updating the table the changes is not made to visible to other user because the same database is used by many user.so we have to commit the transaction to make it visible to other user and changes made permanently.

Commit;


 After commiting the changes made visible and saved permanently.

Note: DDL statements are autocommit.So be carefull while using DDL statements during the transaction.

Related Article:

 I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Transaction control language

 In this lesson we are going to study about Transaction control language.

Transaction control language(TCL):

  There are three transaction control statement used to control transaction they are :
  1.  Commit
  2.  Rollback
  3.  Savepoint

All changes in oracle database can only be done within a transaction .

Transaction :
  • Transactions is an execution of sequence of SQL statements which modifies the data in the database.
  • Transactions should either end with a COMMIT or ROLLBACK statement.
  • Transaction ensure the data consistency.
Transaction consists of DML statements that make up one consistent change to the data.

Example:

  A transfer of funds between two accounts should include the debit to one account and the credit to another account in same transaction .Both actions should either fail or succeed together,the credit should not be committed wihtout the debit .

Related Article:

 I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Updating a table

 In this lesson we are going to study about how to update  a table data.

Update:
  • Changing the existing data.
  • Must include "where" clause in update statement to change perticular records.

Syntax:

UPDATE Table Table_name Set
Column1=Value..,ColumnN=Value
Where condition


   In this syntax update table,Set and Where are all keywords.Table_name specifies the name of the Table .Column1... are the name of the columns where we are going to change the value in a where clause we specifies the condition to perform the update statement.


Example:-

 Single-rows : Update the city to kolkata of employee abhishek

update empdtls set
city="kolkata"
where emp_name="abhishek"
 
Multiple-rows: salary incremented by 1000 of all MANAGER.

update empdtls set
salary=salary+1000
where job="MANAGER"
 
All-rows: salary incremented by 1000 of all employees

update empdtls set
salary=salary+1000


 I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Truncating Tables

In this lesson we are going to study about how to truncate table.

  Truncate statement remove all the rows from the table also perform these task.
  • It De-allocates all space used by the removed rows and it also sets the NEXT  storage pointer to the free space .
  • More efficient than dropping and re-creating a table .
  • Removing rows with the TRUNCATE statement can be faster than removing all rows with the DELETE statement.
Syntax:

TRUNCATE Table Table_Name;

  In this syntex TRUNCATE is the keyword table name specify the name of the table to truncate .

Example:  Truncate the table : test_employees

TRUNCATE Table test_employees;

Related article :
I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Dropping a table

 In this lesson we are going to study about how to drop a table.
  • Drop table  removes the table and all its data from the database entirely.
  • Drop table is different from deleting all records from the table .
  • Deleting all of the records in the table leaves the table including column and constraint information .
  • Dropping the table removes the table definition as well as all of its rows.
Syntax:

DROP TABLE Table_name;

   In this syntex drop table is the keyword and the table name specify the name of table to be droped.

Example:  Drop the temp_employees table

drop table temp_employees.

Related article:
 I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Copying Data

In this lesson we are going to study about how to copy a data from one table to another table.

Syntex:

Insert into Table_name (column1,column2,....,columnN)  Subquery

here insert is a keyword and into specify the table in which we are going to insert the data and Table_name specify the name of the table ,column1,column2,...,columnN is the name of columns.

Example:

Consider a table : departments.



Now we have created a table: dept with same columns.

create table dept
(id number(7),
name varchar2(25)
)


   We can copy the rows in a insert statement.here we are going to insert the data in newly create dept table from the table departments

insert into dept (id,name)
(select department_id as id,department_name as name
from departments)


   In this insert statement we have written a select statement as subquery.In the select statement we have selected the department_id and given the alias name id,and department_name with alias name name.


I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Deleting Data

  The delete statement delete the single row as well as multiple rows from the table.

Syntax:

Deleting all rows:

     DELETE FROM Table_name;

     Here delete is the keyword and from specify the table in which we are going to delete the data  and table_name specify the name of the table.in this delete statement we can add a where clause  to delete rows according to some condition we can specify the where clause in the end of the statement.

Deleting selected rows:

    DELETE FROM Table_name WHERE  condition;


Example:



Delete all the employee whose department_id is 50

delete  from employee2
where dept_id=50


Delete all the employee

delete  from employee2


  The first statement delete only two rows i,e emp_id 198,199 rows will be deleted after processing this statement by oracle server where as the second statement delete all the rows from the employee2 table .So be carefull when you are writting any delete statement .For safety create a savepoint then perform the any delete operation so that u can rollback your data .we will learn about savepoint and rollback in coming lession .


I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Table creation with subquery

In this section we will disscuss about another methord of creating  a table. Let see how to create a table with the use of subquery but before this  we will see the meaning of subquery

Subquery:

“SQL statement embedded inside a another SQL statement is called a subquery “

Syntax:

CREATE TABLE Table_name (column1,column2,…..,column N) 
AS subquery

    In this syntax create table specify that creation of new table,Table_name specify the name of the table,column1, ….,column N etc specify the columns name of the table, AS is a keyword which states that we are going to create a new table from a exiting table,In subquery we can write the SQL select statement to select the rows from the other table.

Example:
 create a table employees2 from the exiting employees


CREATE TABLE EMPLOYEES2 (ID,FIRST_NAME,LAST_NAME,SALARY,DEPT_ID)
AS
SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,SALARY,DEPARTMENT_ID
FROM EMPLOYEES

Note : The above statement create a employees2 with the same structure and along with data of employees table.

I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

sql developer enter full pathname for java.exe

  In this section we are going to solve the problem which i encoutered the following message repeatedly when trying to start SQL Developer  after installtion of Oracle Database 11g Enterprise: Enter the full pathname for java.exe.

and the solution is very easy just enter the path :

C:\app\abhishek\product\11.1.0\db_1\jdk\bin

after enter the correct path

Step 1: select the file type ...
Step 2: Connect to database . befor connect test the connection and once the connection successfull then save the connetion and connect to database .
Step 3: after successfully connection try to execute some sql Query.
I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Outer Join

In this lesson we are going to study about Outer Join in a database.

OUTER JOIN:  
      In Outer join operation ,all records from the source table are included in the result even though they did't satisfy the join condition .

TYPE OF OUTER JOIN:
  • Left outer join .
  • Right outer join .
  • Full outer join .
SYNTEX:

SELECT Columns Names from both Tables FROM tablenameA
LEFT | RIGHT | FULLOUTER JOIN tablenameB ON condition



Left outer join : 
 
  • Left outer join return the unmatched rows from the left table with matched rows from the right table.
  • It selects all the matched rows from the tableB and unmatched rows from tableA.
  • Specify the outer join operator in the left side table.




Right outer join :
 
  • Return the unmatched rows from the right tableB plus matched rows from the left tableA.
  • Specify the outer join operator in the right side table.
 Full outer join :
  • It is a combination of left and right outer join .
  • Join operation combines the results of both left and right outer join .
  • The joined table will contain all the records from both tables.
  • Fill the values NULL for missing matches on either side .
Example:

SQL :
  •  Left outer join 
          select emp.employee_id,emp.last_name,dept.department_id
          from employees emp
          left outer join departments dept
          on
          emp.department_id=dept.department_id;

  • Right outer join
       select emp.employee_id,emp.last_name,dept.department_id
        from employees emp
        right outer join departments dept
        on
        emp.department_id=dept.department_id;

  • Full outer join 
        select emp.employee_id,emp.last_name,dept.department_id
        from employees emp
        full outer join departments dept
        on
        emp.department_id=dept.department_id;



Related Article
I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebooktwitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: