create table employee
(
emp_id varchar2(4) primary key,
emp_fname varchar2(22) NOT NULL,
emp_lname varchar2(22),
emp_age number(3),
emp_dob Date not null,
emp_join_date Date DEFAULT SYSDATE,
basic_salary number(6),
emp_department varchar2(22) constraint chkdepartment check(emp_department in('System','HR','MARKETING','FINANCE')),
emp_experince number(3),
Email_id varchar2(22) unique,
Contact_No number(22),
Address varchar2(22) NOT NULL
)
OUTPUT: Table created.
2. INSERT INTO EMPLOYEE
VALUES(:emp_id,:emp_fname ,:emp_lname,:emp_age,:emp_dob,:emp_join_date,:basic_salary,:emp_department,:emp_experince,:Email_id,:Contact_No,:Address);
OUTPUT: 1 row(s) inserted.
0.10 seconds
3. describe empployee
Output:
Table
Column Data Type Length Precision Scale Primary Key Nullable Default Comment
EMPLOYEE
EMP_ID
Varchar2 4 - - 1 - - -
EMP_FNAME
Varchar2 22 - - - - - -
EMP_LNAME
Varchar2 22 - - -
- -
EMP_AGE
Number - 3 0 -
- -
EMP_DOB
Date 7 - - - - - -
EMP_JOIN_DATE
Date 7 - - -
SYSDATE -
BASIC_SALARY
Number - 6 0 -
- -
EMP_DEPARTMENT
Varchar2 22 - - -
- -
EMP_EXPERINCE
Number - 3 0 -
- -
EMAIL_ID
Varchar2 22 - - -
- -
CONTACT_NO
Number - 22 0 -
- -
ADDRESS
Varchar2 22 - - - - - -
1 - 12
4.select * from EMPLOYEE
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E001 KABLU MANDAL 24 04-AUG-87 04-SEP-10 200000 System 2 kablumndl546@gmail.com 8956954876 WEST BENGAL
E002 MANISH KUMAR 24 12-AUG-87 04-SEP-09 22000 HR 5 manish@gmail.com 756789594 RANCHI
E003 HARRY PRASAD 24 12-AUG-90 04-SEP-09 33000 HR 5 harry@gmail.com 756789594 RANCHI
5. update EMPLOYEE
set basic_salary=basic_salary+1000 where emp_id='E001';
output: 1 row(s) updated.
6. delete from EMPLOYEE where emp_id='E001';
output: 1 row(s) deleted
8.create table employee_copy as select * from employee;
Output: Table created.
0.32 seconds
9DROP TABLE EMPLOYEE
Output: Table dropped.
Table dropped.
2.07 seconds
10ALTER TABLE EMPLOYEE MODIFY (EMP_ID VARCHAR2(11)).
Output: Table altered.
0.20 seconds
11ALTER TABLE EMPLOYEE ADD CONSTRAINT unqemail unique(email_id);
Output: Table altered.
12.ALTER TABLE EMPLOYEE DROP CONSTRAINT SYS_C004783;
Output: Table dropped.
0.11 seconds
13ALTER TABLE EMPLOYEE ADD CONSTRAINT CAGE NOT NULL(EMP_AGE)
Output: Table altered.
14.ALTER TABLE EMPLOYEE ADD(SEX VARCHAR2(2))
Output: Table altered.
15. UPDATE EMPLOYEE SET SEX='M' WHERE EMP_ID='E002';
output: 1 row(s) updated.
16. SELECT EMP_ID,EMP_FNAME,EMP_AGE FROM EMPLOYEE;
Output:
EMP_ID EMP_FNAME EMP_AGE
E001 KABLU 24
E002 MANISH 24
E003 HARRY 24
17.SELECT DISTINCT BASIC _SALARY FROM EMPLOYEE;
Output:
BASIC_SALARY
33000
201000
22000
18. SELECT * FROM EMPLOYEE WHERE EMP_ID='E002' OR EMP_FNAME='KABLU';
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E001 KABLU MANDAL 24 04-AUG-87 04-SEP-10 201000 System 2 kablumndl546@gmail.com 8956954876 WEST BENGAL
E002 MANISH KUMAR 24 12-AUG-87 04-SEP-09 22000 HR 5 manish@gmail.com 756789594 RANCHI
19. SELECT * FROM EMPLOYEE WHERE EMP_DEPARTMENT='HR' AND EMP_EXPERINCE=3;
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E002 MANISH KUMAR 24 12-AUG-87 04-SEP-09 22000 HR 5 manish@gmail.com 756789594 RANCHI
E003 HARRY PRASAD 24 12-AUG-90 04-SEP-09 33000 HR 5 harry@gmail.com 756789594 RANCHI
20. SELECT * FROM EMPLOYEE WHERE EMP_FNAME LIKE 'K%';
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E001 KABLU MANDAL 24 04-AUG-87 04-SEP-10 201000 System 2 kablumndl546@gmail.com 8956954876 WEST BENGAL
21SELECT * FROM EMPLOYEE WHERE EMP_AGE BETWEEN 25 AND 40;
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E001 KABLU MANDAL 24 04-AUG-87 04-SEP-10 201000 System 2 kablumndl546@gmail.com 8956954876 WEST BENGAL
E002 MANISH KUMAR 24 12-AUG-87 04-SEP-09 22000 HR 5 manish@gmail.com 756789594 RANCHI
E003 HARRY PRASAD 24 12-AUG-90 04-SEP-09 33000 HR 5 harry@gmail.com 756789594 RANCHI
22SELECT EMP_ID AS "EMPLOYEE_NAME" , EMP_FNAME AS "FIRST_NAME" FROM EMPLOYEE;
Output:
EMPLOYEE_NAME FIRST_NAME
E001 KABLU
E002 MANISH
E003 HARRY
23. SELECT EMP_ID AS "EMPLOYEE_NAME" , EMP_FNAME AS "FIRST_NAME" FROM EMPLOYEE GROUP
BY EMP_ID,EMP_FNAME;
Output:
EMPLOYEE_NAME FIRST_NAME
E003 HARRY
E001 KABLU
E002 MANISH
24. SELECT EMP_ID,EMP_FNAME,EMP_AGE FROM EMPLOYEE ORDER BY EMP_ID ;
Output:
EMP_ID EMP_FNAME EMP_AGE
E001 KABLU 24
E002 MANISH 24
E003 HARRY 24
25SELECT EMP_ID,EMP_FNAME,EMP_AGE FROM EMPLOYEE ORDER BY EMP_ID DESC ;
Output:
EMP_ID EMP_FNAME EMP_AGE
E003 HARRY 24
E002 MANISH 24
E001 KABLU 24
26.SELECT MAX(BASIC_SALARY),EMP_ID FROM EMPLOYEE GROUP BY EMP_ID;
Output:
EMP_ID EMP_FNAME EMP_AGE
E003 HARRY 24
E002 MANISH 24
E001 KABLU 24
27.SELECT MIN(BASIC_SALARY),EMP_ID FROM EMPLOYEE GROUP BY EMP_ID;
Output:
MIN(BASIC_SALARY) EMP_ID
22000 E002
201000 E001
33000 E003
28. SELECT MIN(BASIC_SALARY),EMP_ID FROM EMPLOYEE GROUP BY EMP_ID;
Output:
MIN(BASIC_SALARY) EMP_ID
22000 E002
201000 E001
33000 E003
29. SELECT AVG(BASIC_SALARY),EMP_ID FROM EMPLOYEE GROUP BY EMP_ID;
Output:
AVG(BASIC_SALARY) EMP_ID
22000 E002
201000 E001
33000 E003
30.CREATE VIEW EMP_VIEW AS SELECT EMP_ID,BASIC_SALARY,DEPARTMENT FROM EMPLOYEE;
Output: View created.
31.CREATE VIEW COM_VIEW AS SELECT EMP_ID,SALARY,AGE,DEPARTMENT_ID,DEPARTMENT_NAME
FROM EMPLOYEE E,DEPARTMENT D WHERE E.EMP_ID=D.DEPARTMENT_ID
Output: View created.
32.SELECT * FROM EMP_VIEW;
Output:
EMP_ID BASIC_SALARY
E001 201000
E002 22000
E003 33000
33.DROP VIEW EMP_VIEW;
Output:
View dropped.
34.CREATE INDEX EMP_ID_INDEX ON EMPLOYEE (EMP_ID);
Output: Index created.
36.SELECT * FROM USER_INDEXES WHERE TABLE_NAME=’EMPLOYEE’;
37.CREATE SEQUENCE EMP_ID_SEQ START WITH 100 INCREMENT BY 1 MAXVALUE 1000
Output: Sequence created.
38. SELECT * FROM USER_SEQUENCES;
Output:
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
EMP_ID_SEQ 1 1000 1 N N 20 100
LOCATIONS_SEQ 1 9900 100 N N 0 3300
DEPARTMENTS_SEQ 1 9990 10 N N 0 280
EMPLOYEES_SEQ 1 999999999999999999999999999 1 N N 0 207
39. SELECT EMP_ID_SEQ.CURRVAL FROM DUAL;
Output:100
40 SELECT EMP_ID_SEQ.NEXTVAL FROM DUAL;
Output:101
41. CREATE SYNONYM SYN_EMPS FOR EMPLOYEE;
Output:
42.SELECT * FROM USER_SYNONYMS;
Output: Synonym created.
0.03 seconds
43.DROP INDEX EMP_INDEX;
Output:index dropped
44.DROP SEQUENCE EMP_ID_SEQ
Output: Sequence dropped.
(
emp_id varchar2(4) primary key,
emp_fname varchar2(22) NOT NULL,
emp_lname varchar2(22),
emp_age number(3),
emp_dob Date not null,
emp_join_date Date DEFAULT SYSDATE,
basic_salary number(6),
emp_department varchar2(22) constraint chkdepartment check(emp_department in('System','HR','MARKETING','FINANCE')),
emp_experince number(3),
Email_id varchar2(22) unique,
Contact_No number(22),
Address varchar2(22) NOT NULL
)
OUTPUT: Table created.
2. INSERT INTO EMPLOYEE
VALUES(:emp_id,:emp_fname ,:emp_lname,:emp_age,:emp_dob,:emp_join_date,:basic_salary,:emp_department,:emp_experince,:Email_id,:Contact_No,:Address);
OUTPUT: 1 row(s) inserted.
0.10 seconds
3. describe empployee
Output:
Table
Column Data Type Length Precision Scale Primary Key Nullable Default Comment
EMPLOYEE
EMP_ID
Varchar2 4 - - 1 - - -
EMP_FNAME
Varchar2 22 - - - - - -
EMP_LNAME
Varchar2 22 - - -
- -
EMP_AGE
Number - 3 0 -
- -
EMP_DOB
Date 7 - - - - - -
EMP_JOIN_DATE
Date 7 - - -
SYSDATE -
BASIC_SALARY
Number - 6 0 -
- -
EMP_DEPARTMENT
Varchar2 22 - - -
- -
EMP_EXPERINCE
Number - 3 0 -
- -
EMAIL_ID
Varchar2 22 - - -
- -
CONTACT_NO
Number - 22 0 -
- -
ADDRESS
Varchar2 22 - - - - - -
1 - 12
4.select * from EMPLOYEE
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E001 KABLU MANDAL 24 04-AUG-87 04-SEP-10 200000 System 2 kablumndl546@gmail.com 8956954876 WEST BENGAL
E002 MANISH KUMAR 24 12-AUG-87 04-SEP-09 22000 HR 5 manish@gmail.com 756789594 RANCHI
E003 HARRY PRASAD 24 12-AUG-90 04-SEP-09 33000 HR 5 harry@gmail.com 756789594 RANCHI
5. update EMPLOYEE
set basic_salary=basic_salary+1000 where emp_id='E001';
output: 1 row(s) updated.
6. delete from EMPLOYEE where emp_id='E001';
output: 1 row(s) deleted
8.create table employee_copy as select * from employee;
Output: Table created.
0.32 seconds
9DROP TABLE EMPLOYEE
Output: Table dropped.
Table dropped.
2.07 seconds
10ALTER TABLE EMPLOYEE MODIFY (EMP_ID VARCHAR2(11)).
Output: Table altered.
0.20 seconds
11ALTER TABLE EMPLOYEE ADD CONSTRAINT unqemail unique(email_id);
Output: Table altered.
12.ALTER TABLE EMPLOYEE DROP CONSTRAINT SYS_C004783;
Output: Table dropped.
0.11 seconds
13ALTER TABLE EMPLOYEE ADD CONSTRAINT CAGE NOT NULL(EMP_AGE)
Output: Table altered.
14.ALTER TABLE EMPLOYEE ADD(SEX VARCHAR2(2))
Output: Table altered.
15. UPDATE EMPLOYEE SET SEX='M' WHERE EMP_ID='E002';
output: 1 row(s) updated.
16. SELECT EMP_ID,EMP_FNAME,EMP_AGE FROM EMPLOYEE;
Output:
EMP_ID EMP_FNAME EMP_AGE
E001 KABLU 24
E002 MANISH 24
E003 HARRY 24
17.SELECT DISTINCT BASIC _SALARY FROM EMPLOYEE;
Output:
BASIC_SALARY
33000
201000
22000
18. SELECT * FROM EMPLOYEE WHERE EMP_ID='E002' OR EMP_FNAME='KABLU';
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E001 KABLU MANDAL 24 04-AUG-87 04-SEP-10 201000 System 2 kablumndl546@gmail.com 8956954876 WEST BENGAL
E002 MANISH KUMAR 24 12-AUG-87 04-SEP-09 22000 HR 5 manish@gmail.com 756789594 RANCHI
19. SELECT * FROM EMPLOYEE WHERE EMP_DEPARTMENT='HR' AND EMP_EXPERINCE=3;
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E002 MANISH KUMAR 24 12-AUG-87 04-SEP-09 22000 HR 5 manish@gmail.com 756789594 RANCHI
E003 HARRY PRASAD 24 12-AUG-90 04-SEP-09 33000 HR 5 harry@gmail.com 756789594 RANCHI
20. SELECT * FROM EMPLOYEE WHERE EMP_FNAME LIKE 'K%';
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E001 KABLU MANDAL 24 04-AUG-87 04-SEP-10 201000 System 2 kablumndl546@gmail.com 8956954876 WEST BENGAL
21SELECT * FROM EMPLOYEE WHERE EMP_AGE BETWEEN 25 AND 40;
Output:
EMP_ID EMP_FNAME EMP_LNAME EMP_AGE EMP_DOB EMP_JOIN_DATE BASIC_SALARY EMP_DEPARTMENT EMP_EXPERINCE EMAIL_ID CONTACT_NO ADDRESS
E001 KABLU MANDAL 24 04-AUG-87 04-SEP-10 201000 System 2 kablumndl546@gmail.com 8956954876 WEST BENGAL
E002 MANISH KUMAR 24 12-AUG-87 04-SEP-09 22000 HR 5 manish@gmail.com 756789594 RANCHI
E003 HARRY PRASAD 24 12-AUG-90 04-SEP-09 33000 HR 5 harry@gmail.com 756789594 RANCHI
22SELECT EMP_ID AS "EMPLOYEE_NAME" , EMP_FNAME AS "FIRST_NAME" FROM EMPLOYEE;
Output:
EMPLOYEE_NAME FIRST_NAME
E001 KABLU
E002 MANISH
E003 HARRY
23. SELECT EMP_ID AS "EMPLOYEE_NAME" , EMP_FNAME AS "FIRST_NAME" FROM EMPLOYEE GROUP
BY EMP_ID,EMP_FNAME;
Output:
EMPLOYEE_NAME FIRST_NAME
E003 HARRY
E001 KABLU
E002 MANISH
24. SELECT EMP_ID,EMP_FNAME,EMP_AGE FROM EMPLOYEE ORDER BY EMP_ID ;
Output:
EMP_ID EMP_FNAME EMP_AGE
E001 KABLU 24
E002 MANISH 24
E003 HARRY 24
25SELECT EMP_ID,EMP_FNAME,EMP_AGE FROM EMPLOYEE ORDER BY EMP_ID DESC ;
Output:
EMP_ID EMP_FNAME EMP_AGE
E003 HARRY 24
E002 MANISH 24
E001 KABLU 24
26.SELECT MAX(BASIC_SALARY),EMP_ID FROM EMPLOYEE GROUP BY EMP_ID;
Output:
EMP_ID EMP_FNAME EMP_AGE
E003 HARRY 24
E002 MANISH 24
E001 KABLU 24
27.SELECT MIN(BASIC_SALARY),EMP_ID FROM EMPLOYEE GROUP BY EMP_ID;
Output:
MIN(BASIC_SALARY) EMP_ID
22000 E002
201000 E001
33000 E003
28. SELECT MIN(BASIC_SALARY),EMP_ID FROM EMPLOYEE GROUP BY EMP_ID;
Output:
MIN(BASIC_SALARY) EMP_ID
22000 E002
201000 E001
33000 E003
29. SELECT AVG(BASIC_SALARY),EMP_ID FROM EMPLOYEE GROUP BY EMP_ID;
Output:
AVG(BASIC_SALARY) EMP_ID
22000 E002
201000 E001
33000 E003
30.CREATE VIEW EMP_VIEW AS SELECT EMP_ID,BASIC_SALARY,DEPARTMENT FROM EMPLOYEE;
Output: View created.
31.CREATE VIEW COM_VIEW AS SELECT EMP_ID,SALARY,AGE,DEPARTMENT_ID,DEPARTMENT_NAME
FROM EMPLOYEE E,DEPARTMENT D WHERE E.EMP_ID=D.DEPARTMENT_ID
Output: View created.
32.SELECT * FROM EMP_VIEW;
Output:
EMP_ID BASIC_SALARY
E001 201000
E002 22000
E003 33000
33.DROP VIEW EMP_VIEW;
Output:
View dropped.
34.CREATE INDEX EMP_ID_INDEX ON EMPLOYEE (EMP_ID);
Output: Index created.
36.SELECT * FROM USER_INDEXES WHERE TABLE_NAME=’EMPLOYEE’;
37.CREATE SEQUENCE EMP_ID_SEQ START WITH 100 INCREMENT BY 1 MAXVALUE 1000
Output: Sequence created.
38. SELECT * FROM USER_SEQUENCES;
Output:
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
EMP_ID_SEQ 1 1000 1 N N 20 100
LOCATIONS_SEQ 1 9900 100 N N 0 3300
DEPARTMENTS_SEQ 1 9990 10 N N 0 280
EMPLOYEES_SEQ 1 999999999999999999999999999 1 N N 0 207
39. SELECT EMP_ID_SEQ.CURRVAL FROM DUAL;
Output:100
40 SELECT EMP_ID_SEQ.NEXTVAL FROM DUAL;
Output:101
41. CREATE SYNONYM SYN_EMPS FOR EMPLOYEE;
Output:
42.SELECT * FROM USER_SYNONYMS;
Output: Synonym created.
0.03 seconds
43.DROP INDEX EMP_INDEX;
Output:index dropped
44.DROP SEQUENCE EMP_ID_SEQ
Output: Sequence dropped.
Comments
Post a Comment