Program -1
Create a table called Employee & execute the following.
Employee(EMPNO,ENAME,JOB, MANAGER_NO, SAL, COMMISSION)
1. Create a user and grant all permissions to theuser.
2. Insert the any three records in the employee table contains attributes
EMPNO,ENAME JOB, MANAGER_NO, SAL, COMMISSION and use rollback.
Check the result.
3. Add primary key constraint and not null constraint to the employee table.
4. Insert null values to the employee table and verify the result.
[Link] a user and grant all permissions to the user.
Connected to:
Oracle Database 10g Enterprise Edition Release [Link].0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> Connect sys as sysdba;
Enter password: ******
Connected.
SQL> create user bitise identified by bit123;
User created.
SQL> GRANT CONNECT, RESOURCE TO bitise;
Grant succeeded.
SQL> CREATE TABLE Employee (
2 EMPNO NUMBER(5),
3 ENAME VARCHAR2(50),
4 JOB VARCHAR2(50),
5 MANAGER_NO NUMBER(5),
6 SAL NUMBER(10, 2),
7 COMMISSION NUMBER(10, 2)
8 );
Table created.
SQL> GRANT SELECT, INSERT, UPDATE, DELETE ON Employee TO bitise;
Grant succeeded.
SQL> GRANT CREATE TABLE, CREATE SEQUENCE TO bitise;
Grant succeeded.
SQL> grant all privileges to bitise identified by bit123;
Grant succeeded.
SQL> CREATE TABLE Employee (
2 EMPNO NUMBER(5),
3 ENAME VARCHAR2(50),
4 JOB VARCHAR2(50),
5 MANAGER_NO NUMBER(5),
6 SAL NUMBER(10, 2),
7 COMMISSION NUMBER(10, 2)
8 );
SQL> select privilege from dba_sys_privs
2 where grantee='employee'
3 order by 1;
no rows selected
SQL> commit;
Commit complete
SQL>Disconnect;
2. Insert the any three records in the employee table contains attributes EMPNO,ENAME
JOB, MANAGER_NO, SAL, COMMISSION and use rollback. Check the result.
INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
VALUES (1, 'John Doe', 'Manager', NULL, 5000, 1000);
INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
VALUES (2, 'Jane Smith', 'Developer', 1, 4000, NULL);
INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
VALUES (3, 'Alice Johnson', 'Analyst', 1, 3500, 500);
-- Rollback the transaction
ROLLBACK;
SELECT * FROM Employee;
SQL> no rows selected;
3. Add primary key constraint and not null constraint to the employee table.
ALTER TABLE Employee
ADD CONSTRAINT pk_employee PRIMARY KEY (MANAGER_NO);
Add NOT NULL constraint to ENAME, JOB, and SAL columns
ALTER TABLE Employee
MODIFY (ENAME VARCHAR2(50) NOT NULL,
JOB VARCHAR2(50) NOT NULL,
SAL NUMBER(10, 2) NOT NULL);
[Link] null values to the employee table and verify the result.
INSERT INTO Employee24 (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
VALUES (4, NULL, 'Tester', 1, NULL, NULL);
INSERT INTO Employee1 (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
VALUES (5, 'Jack Smith', NULL, 1, NULL, NULL);
