dbms exercise 2

 welcome

----------------------------------------------------------------------

CREATE TABLE EMPLOYEE (

    ENO NUMBER (4),

    ENAME VARCHAR2(50) NOT NULL,

    DEPTNO NUMBER (2),

    JOBTITLE VARCHAR2(50),

    DOB DATE,

    SALARY NUMBER(8,2),

    COMM NUMBER(8,2),

    CONSTRAINT PM_KEY PRIMARY KEY(ENO),

    CONSTRAINT CK CHECK (SALARY > 0),

    CONSTRAINT CKR CHECK (DEPTNO BETWEEN 10 AND 90)

);

ALTER TABLE EMPLOYEE

ADD CONSTRAINT JT CHECK ( JOBTITLE IN ('MANAGER','ANALYST','CLERK','SALESMAN'));

ALTER TABLE EMPLOYEE
ADD CONSTRAINT com_gr_slry CHECK (COMM < SALARY);
CREATE OR REPLACE TRIGGER trigger_doj
BEFORE INSERT OR UPDATE ON EMPLOYEE
FOR EACH ROW
BEGIN
IF :NEW.DOJ = TRUNC(SYSDATE) THEN
RAISE_APPLICATION_ERROR(-20001,'Date of Joining (DOJ) cannot be the current date.');
END IF;
END;
/

INSERT INTO EMPLOYEE VALUES(1001,'John Smith',10,'MANAGER',TO_DATE('2022-01-15','YYYY-MM-DD'),5500.00,NULL);
INSERT INTO EMPLOYEE VALUES(1002,'Alice Brown',20,'ANALYST',TO_DATE('2022-03-20','YYYY-MM-DD'),4500.00,500.00);
INSERT INTO EMPLOYEE VALUES(1003,'Mark Wilson',10,'CLERK',TO_DATE('2022-06-10','YYYY-MM-DD'),2500.00,NULL);
INSERT INTO EMPLOYEE VALUES(1004,'Sarah Davis',30,'SALESMAN',TO_DATE('2022-08-05','YYYY-MM-DD'),3500.00,1000.00);
INSERT INTO EMPLOYEE VALUES(1005,'Robert Jones',20,'ANALYST',TO_DATE('2023-01-15','YYYY-MM-DD'),4200.00,600.00);
INSERT INTO EMPLOYEE VALUES(1006,'Emma Miller',30,'CLERK',TO_DATE('2023-03-01','YYYY-MM-DD'),2800.00,NULL);
INSERT INTO EMPLOYEE VALUES(1007,'Jamse Anderson',10,'MANAGER',TO_DATE('2023-05-20','YYYY-MM-DD'),5800.00,800.00);
UPDATE EMPLOYEE
SET COMM = COMM - 100
WHERE COMM > 500;
UPDATE EMPLOYEE 
SET SALARY = SALARY + (SALARY * 0.10) 
WHERE DOJ < TO_DATE('2023-01-01','YYYY-MM-DD');
UPDATE EMPLOYEE
SET COMM = 300
WHERE DEPTNO = 10 AND COMM IS NULL;
ALTER TABLE EMPLOYEE
    DROP CONSTRAINT JT;

ALTER TABLE EMPLOYEE
    ADD CONSTRAINT JT CHECK (JOBTITLE IN('MANAGER','ANALYST','CLERK','SALESMAN','SENIOR CLERK'));


UPDATE EMPLOYEE
SET JOBTITLE = 'SENIOR CLERK' 
WHERE  JOBTITLE =  'CLERK' AND SALARY >= 2500 ;
UPDATE EMPLOYEE
SET COMM = 200
WHERE DEPTNO = 30 AND COMM IS NULL;

DELETE EMPLOYEE 
WHERE COMM IS NULL AND SALARY <3000;
UPDATE EMPLOYEE
SET SALARY = SALARY+(SALARY*0.15)
WHERE DEPTNO = 10 AND JOBTITLE = 'MANAGER';
UPDATE EMPLOYEE
SET SALARY = SALARY+(SALARY*0.10)
WHERE DEPTNO = 20 AND JOBTITLE = 'ANALYST';
UPDATE EMPLOYEE
SET SALARY = SALARY+(SALARY*0.05)
WHERE JOBTITLE = 'CLERK' AND JOBTITLE = 'SENIOR CLERK';
UPDATE EMPLOYEE
SET COMM = 1000
WHERE SALARY >= 5000 AND JOBTITLE = 'MANAGER';
UPDATE EMPLOYEE
SET COMM = 800
WHERE SALARY >= 4000 AND JOBTITLE = 'ANALYST';
UPDATE EMPLOYEE
SET COMM = 500
WHERE SALARY < 4000 AND COMM IS NULL;


DELETE EMPLOYEE
WHERE JOBTITLE = ' CLERK ' AND SALARY < 3000;

DELETE EMPLOYEE
WHERE JOBTITLE = 'ANALYST' AND COMM < 600;

DELTER EMPLOYEE
WHERE JOBTITLE = 'MANAGER' AND DEPTNO != 10;

ALTER TABLE EMPLOYEE
DROP CONSTRAINT JT;

ALTER TABLE EMPLOYEE
ADD CONSTRAINT JT CHECK (JOBTITLE IN(
    'MANAGER',
    'SENIOR MANAGER',
    'LEAD MANAGER',
    'ANALYST',
    'SENIOR ANALYST',
    'LEAD ANALYST',
    'CLERK',
    'SENIOR CLERK',
    'SALESMAN'
    ));
UPDATE EMPLOYEE
SET JOBTITLE = 'SENIOR MANAGER'
WHERE SALARY > 5000 AND COMM IS NOT NULL AND JOBTITLE = 'MANAGER' ;

UPDATE EMPLOYEE
SET JOBTITLE = 'SENIOR ANALYST'
WHERE SALARY > 5000 AND COMM IS NOT NULL AND JOBTITLE = 'ANALYST';

UPDATE EMPLOYEE
SET JOBTITLE = 'LEAD MANAGER'
WHERE SALARY > 4000 AND DEPTNO = 20 AND JOBTITLE = 'MANAGER';

UPDATE EMPLOYEE
SET JOBTITLE = 'LEAD ANALYST'
WHERE SALARY > 4000 AND DEPTNO = 20 AND JOBTITLE = 'ANALYST';




Comments

Post a Comment

Popular posts from this blog

alter table

 The ALTER TABLE command in Oracle Database is used to modify the structure of an existing table . You can use it to add, modify, or drop columns, constraints, or even rename the table itself. 1. Syntax of ALTER TABLE ALTER TABLE table_name [ADD column_definition] [MODIFY column_definition] [DROP COLUMN column_name] [RENAME COLUMN old_column_name TO new_column_name] [ADD CONSTRAINT constraint_definition] [DROP CONSTRAINT constraint_name] [RENAME TO new_table_name]; 2. Key Use Cases of ALTER TABLE A. Adding a Column You can add one or more columns to an existing table. Syntax: ALTER TABLE table_name ADD column_name datatype [constraint]; Example: ALTER TABLE EMP1 ADD phone_number VARCHAR2(15); B. Modifying a Column You can modify the data type, size, or constraints of an existing column. Syntax: ALTER TABLE table_name MODIFY column_name new_datatype [constraint]; Example: ALTER TABLE EMP1 MODIFY NAME VARCHAR2(30) NOT NULL; C. Dropping a Column You...
Read more

DDL create table

Here’s a comprehensive guide to the CREATE TABLE command in Oracle Database, including its syntax, options, constraints, and examples. Purpose of CREATE TABLE The CREATE TABLE command is used to define a new table in the database. It specifies the table name, its columns, data types, constraints, and other attributes. Syntax CREATE TABLE table_name ( column1 datatype [constraint], column2 datatype [constraint], ..., [table_constraint] ); Key Components table_name : Name of the table being created. It must be unique in the schema. column_name : Name of a column in the table. datatype : Data type of the column (e.g., NUMBER , VARCHAR2 , DATE , etc.). constraint : Optional constraints applied to columns (e.g., NOT NULL , PRIMARY KEY ). table_constraint : Constraints at the table level (e.g., PRIMARY KEY , FOREIGN KEY ). Supported Data Types in Oracle Common Data Types: Data Type Description Example NUMBER Stores num...
Read more