1.
Write a query to display 4 characters extracted from the column ‘dept’
Code-
-- create
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name varchar(30) NOT NULL,
dept varchar(20) NOT NULL,
salary INT,
DOJ DATE
);
-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales', 20000, '2020-01-08');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting', 39000, '2019-06-12');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales', 13000, '2021-03-04');
INSERT INTO EMPLOYEE VALUES (0004, 'Ivy', 'HR', 25000, '2016-05-08');
INSERT INTO EMPLOYEE VALUES (0005, 'Dev', 'HR', 25000, '2014-05-07');
INSERT INTO EMPLOYEE VALUES (0006, 'Sam', 'Accounting', 40000, '2018-05-03');
-- Query
select substr(dept,1,4) from EMPLOYEE;
Output-
2. Write a query to display the position of occurrence of string ‘COUNT’ in string ‘ACCOUNTING’
Code-
select instr('Accounting','count');
Output-
3. Write a query to convert and display string ‘empid’ into Uppercase.
Code-
select upper('empid');
Output-
4. Write a query to delete the column ‘DOJ’
Code-
Alter table EMPLOYEE
drop DOJ;
select * from EMPLOYEE;
Output-
5. Write a query to add a new column ‘Location’.
Code-
Alter table EMPLOYEE
add Location varchar(20);
Update EMPLOYEE
set Location = 'Delhi'
where empId = 0001;
select * from EMPLOYEE;
Output-
6. Write a query to change the column name ‘dept’ to ‘Department’
Code-
Alter table EMPLOYEE
change dept Department varchar(20);
select * from EMPLOYEE;
Output –
7. Write a query to change the location of all employees to ‘Mumbai’ where Department is
‘Accounting’.
Code-
update EMPLOYEE
set location ='Mumbai'
where Department = 'Accounting';
select * from EMPLOYEE;
Output-
8. Write a query to display all records where location is ‘NULL’.
Code-
Select * from EMPLOYEE where location is NULL;
Output-
9. Write a query to display all employee names whose salary is between 30,000 to 50,000.
Code-
Select distinct(name) from EMPLOYEE where salary between 30000 and 50000;
Output-
10. Write a query to display the records where employee name starts with the letter ‘I’.
Code-
Select * from EMPLOYEE where name like 'I%';
Output-
\
11. Write a query to display records of all employees whose names do not contain the letter ‘E’.
Code-
Select * from EMPLOYEE where name not like '%e%';
Output-
12. Write a query to change the data type of the column ‘Salary’ from Integer to Float.
Code-
Alter table EMPLOYEE
modify salary float;
desc EMPLOYEE;
Output-
13. Write a query to order the table by ‘Salary’ in ascending order.
Code-
select* from EMPLOYEE
order by salary;
Output-
14. Write a query to increase the salary of the ‘Sales’ Department by 10,000.
Code-
Update EMPLOYEE
set salary=salary+10000
where Department='Sales';
Output-
15. Write a query to display all the separate Departments available.
Code-
select distinct(Department) from EMPLOYEE;
output-
16. Write a query to display the employee name, department and location of all employees.
Code-
select name,Department,location from EMPLOYEE;
Output-
17. Write a query to display the total number of employees.
Code-
select count(*) from EMPLOYEE;
output-
18. Write a query to display the minimum salary being paid within each department.
Code-
select Department,min(salary) from EMPLOYEE
group by Department;
Output-
19. Write a query to display the count of employees in each department.
Code-
select Department,count(*) from EMPLOYEE
group by Department;
Output-
20. Write a query to delete all records where the location is not mentioned.
Code-
Delete from EMPLOYEE
where location is NULL;
select * from EMPLOYEE;
Output-
