create a table in oracle
Dept (dno number(3) primary key, dname varchar2(30))
Write the following using alter table command in SQL
1. add dloc varchar2(30) to dept table
2. add not null constraint to dloc column in dept table
3. add unique constraint to dname column in dept table
4. Insert 4 records with dno,dname as 10,SALES, 20,PRODUCTION,30,QUALITY,40,ACCOUNTS
5. Update dname value as 'QUALITY CONTROL' for the dno 30
create a table in oracle
EMP(eno number(3) primary key, ename varchar2(30) not null, sal number(8,2) , dno
number(3) )
Write the following using alter table command in SQL
1. add desg varchar2(20) not null in emp table
2. add check constraint to sal column that sal>0
3. add doj date not null to emp table
Write the queries
1. insert 10 records in emp table
2. update salary of all employees with increment of 1000
3. delete all employees whose salry is below 10000
4. list employee names in descending order
5. List employee name, salary who works in dept 10
6. list employee information whose salary between 10000 to 20000
7. list emp name, sal , desg of all clerks who works in dept 10
8. list emp info whose salary is not between 20000 to 30000
9. Display all the details of all employees
10. Retrieve the name and salary of every employee
11 Retrieve all distinct salary values
12 Retrieve all employees in department 10 whose salary is between 10,000 and 25000
13. display all employees whose joined after 01-01-2022
14. display all employees whose joined between 01-01-2022 to 01-02-2024
15. display all employees whose joined after 01-01-2022 and works in dept 10 ,20
ANSWERS
CREATE TABLE Dept ( dno NUMBER(3) PRIMARY KEY, dname VARCHAR2(30));
Add dloc column to Dept table
ALTER TABLE Dept ADD dloc VARCHAR2(30);
Add NOT NULL constraint to dloc column
ALTER TABLE Dept MODIFY dloc VARCHAR2(30) NOT NULL;
Add UNIQUE constraint to dname column
ALTER TABLE Dept ADD CONSTRAINT dept_dname_uk UNIQUE (dname);
Insert Records into Dept Table
INSERT INTO Dept (dno, dname) VALUES (10, 'SALES');
INSERT INTO Dept (dno, dname) VALUES (20, 'PRODUCTION');
INSERT INTO Dept (dno, dname) VALUES (30, 'QUALITY');
INSERT INTO Dept (dno, dname) VALUES (40, 'ACCOUNTS');
Update dname to QUALITY CONTROL for dno = 30
UPDATE Dept SET dname = 'QUALITY CONTROL' WHERE dno = 30;
update dloc of all records
UPDATE Dept SET dloc = 'DELHI' WHERE dno = 10;
UPDATE Dept SET dloc = 'MUMBAI' WHERE dno = 20;
UPDATE Dept SET dloc = 'CHENNAI' WHERE dno = 30;
UPDATE Dept SET dloc = 'BENGALURU' WHERE dno = 40;
Create emp table
CREATE TABLE Emp ( eno NUMBER(3) PRIMARY KEY, ename VARCHAR2(30) NOT NULL,
sal NUMBER(8,2), dno NUMBER(3));
Add desg column (NOT NULL) to Emp table
ALTER TABLE Emp ADD desg VARCHAR2(20) NOT NULL;
Add CHECK constraint to sal column (sal > 0)
ALTER TABLE Emp ADD CONSTRAINT emp_sal_ck CHECK (sal > 0);
Add doj column (NOT NULL) to Emp table
ALTER TABLE Emp ADD doj DATE NOT NULL;
Insert 10 records into Emp table
INSERT INTO Emp VALUES (101, 'RAVI', 12000, 10, 'CLERK', DATE '2022-01-10');
INSERT INTO Emp VALUES (102, 'ANITA', 18000, 20, 'MANAGER',DATE '2021-12-15');
INSERT INTO Emp VALUES (103, 'SUNIL', 9000, 10, 'CLERK', DATE '2023-03-12');
INSERT INTO Emp VALUES (104, 'KIRAN', 22000, 30, 'ENGINEER',DATE '2022-06-05');
INSERT INTO Emp VALUES (105, 'MEENA', 15000, 10, 'CLERK', DATE '2024-01-20');
INSERT INTO Emp VALUES (106, 'ARUN', 30000, 20, 'MANAGER',DATE '2020-09-18');
INSERT INTO Emp VALUES (107, 'POOJA', 17000, 40, 'HR', DATE '2022-11-25');
INSERT INTO Emp VALUES (108, 'VIJAY', 14000, 10, 'CLERK', DATE '2023-07-10');
INSERT INTO Emp VALUES (109, 'NEHA', 26000, 30, 'ENGINEER',DATE '2021-04-30');
INSERT INTO Emp VALUES (110, 'RAHUL', 11000, 20, 'CLERK', DATE '2022-02-14');
Update salary of all employees (increment by 1000)
UPDATE Emp SET sal = sal + 1000;
Delete employees whose salary is below 10000
DELETE FROM Emp WHERE sal < 10000;
List employee names in descending order
SELECT ename FROM Emp ORDER BY ename DESC;
List employee name and salary working in dept 10
SELECT ename, sal FROM Emp WHERE dno = 10;
List employee information whose salary is between 10000 and 20000
SELECT * FROM Emp WHERE sal BETWEEN 10000 AND 20000;
List emp name, salary, designation of all CLERKs in dept 10
SELECT ename, sal, desg FROM Emp WHERE desg = 'CLERK' AND dno = 10;
List emp info whose salary is NOT between 20000 and 30000
SELECT * FROM Emp WHERE sal NOT BETWEEN 20000 AND 30000;
Display all details of all employees
SELECT * FROM Emp;
Retrieve the name and salary of every employee
SELECT ename, sal FROM Emp;
Retrieve all DISTINCT salary values
SELECT DISTINCT sal FROM Emp;
Employees in dept 10 with salary between 10000 and 25000
SELECT * FROM Emp WHERE dno = 10 AND sal BETWEEN 10000 AND 25000;
Or
SELECT * FROM Emp WHERE dno = 10 AND sal >= 10000 AND sal<=25000;
Employees who joined after 01-01-2022
SELECT * FROM Emp WHERE doj > DATE '2022-01-01';
Employees who joined between 01-01-2022 and 01-02-2024
SELECT * FROM Emp WHERE doj BETWEEN DATE '01-JAN-2022' AND '01-FEB-2024';
Employees who joined after 01-01-2022 and work in dept 10 or 20
SELECT * FROM Emp WHERE doj > '01-JAN-2022' AND dno IN (10, 20);