Assignment - 1
(1a) CREATE TABLE EMPLOYEE
create table empl089 (empid number (10), empfname varchar(20) , emplname varchar(20) ,
empgender varchar(10));
(1b) ALTER TABLE
Alter table empl089 add city varchar(20);
08990301722
DESCRIBE TABLE
desc empl089;
(1c) INSERT VALUES IN EMPLOYEE TABLE
Insert into empl089 Values (101, 'Rohan', 'Khurana', 'Male', 'Pune');
Insert into empl089 Values (102, 'Ram', 'Sharma', 'Male', 'Delhi');
Insert into empl089 Values(103,'Anshpreet','Singh','Male','Delhi');
Insert into empl089 Values(104,'Nandika','Jain','Female','Delhi');
Insert into empl089 Values(105,'Tejas','Jindal','Male','Haryana');
08990301722
SELECT ALL RECORDS
select * from empl089;
08990301722
ASSIGNMENT – 2
(1a)Create table customer_iitm089
(customerid number (10) , customerfname varchar(20), customerlname varchar(20),
customerstate varchar(15));
(1b)INSERT VALUES
Insert into customer_iitm089 values (201, 'Rohan', 'Thakur', 'Delhi');
Insert into customer_iitm089 values (202, 'Tejas', 'Jindal', 'Haryana');
Insert into customer_iitm089 values (203, 'Mahima', 'Sharma', 'Maharashtra');
Insert into customer_iitm089 values (204, 'Priya', 'Mehta', 'Punjab');
Insert into customer_iitm089 values (205, 'Yash', 'Chandna', 'Delhi');
08990301722
SELECT ALL CUSTOMER RECORD
Select * from customer_iitm089;
(2a) DISPLAY CUSTOMERID AND FIRST NAME
Select customerid, customerfname from customer_iitm089;
08990301722
(2b)DISPLAY CUSTOMER FIRST NAME, CUSTOMER LAST NAME, CUSTOMER STATE
Select customerfname, customerlname, customerstate from customer_iitm089;
(2C) DISPLAY RECORD FROM A SPECIFIC STATE
Select * from customer_iitm089 where customerstate = 'Delhi';
08990301722 Page 6
(2d) DISPLAY FIRST NAME, LAST NAME CONCATENATED WITH STATE
Select concat (customerfname, customerlname ), customerstate from
customer_iitm089;
(2e)DISPLAY CUSTOMER STATE
Select customerstate from customer_iitm089;
08990301722 Page 7
08990301722 Page 8
(2f) DISPLAY NAME OF HAVING A SPECIFIC FIRST NAME
Select customerstate, customerfname, customerlname, customerid
from customer_iitm089 where customerfname='Mahima';
(2g) DISPLAY RECORD OF PEOPLE FROM TWO DIFFERENT STATES
Select * from customer_iitm089 where customerstate ='Delhi' or customerstate
='Haryana';
08990301722 Page 9
(2h) DISPLAY DETAILS OF CUSTOMER WITH SPECIFIC CUSTOMER ID
Select * from customer_iitm089 where customerid = 201;
(2i) DISPLAY DETAILS OF CUSTOMER EXCEPT A SPECIFIC CUSTOMER ID
Select * from customer_iitm089 where customerid <> 201;
08990301722 Page 10
(2j) DISPLAY CUSTOMER ID AS CID
Select customerid as CID from customer_iitm089;
(2k) RETRIEVE ROWS USING ‘BETWEEN’ AND COLUMN CUSTOMER ID
Select * from customer_iitm089 where customerid between 202 and 205;
08990301722 Page 11
(2l) RETRIEVE ROWS USING ‘OR’ AND COLUMNS CUSTOMER STATE
select * from customer_iitm089 where customerstate='Delhi' or customerstate =
'Maharashtra';
(2m) RENAME THE TABLE
Alter Table Customer_iitm089 rename to cust089;
08990301722 Page 12
(2n) DISPLAY DETAILS OF CUSTOMER WITH SPECIFIC CUSTOMER STATE
select * from cust089 where customerstate = 'Haryana';
(2o) DISPLAY ALL THE ROWS
Select * from cust089;
08990301722 Page 13
(2p) RENAME THE COLUMN STATE TO ADDRESS
Alter table cust089 rename column customerstate to customeraddress;
SELECT ALL RECORD FROM CUST
Select * from cust089;
08990301722 Page 14
(2q) DISPLAY THOSE ROWS WHERE STATE NAME BEGINS WITH ‘D’.
SELECT * FROM cust089 where customeraddress like 'D%';
(2r) RETRIEVE ROWS USING LIKE COMMAND IN CUSTOMER FIRST NAME
Select * from cust089 where customerfname like 'Tejas%';
08990301722 Page 15
(2s) SELECT CUSTOMER ADDRESS
select customeraddress from cust089;
(2t) RETRIEVE COLUMNS CUSTOMER ID, CUSTOMER FIRST NAME, CUSTOMER LAST
NAME, CUSTOMER ADDRESS FOR A SPECIFIC NAME
SELECT customerid, customerfname, customerlname, customeraddress from cust089
where customerFName= 'Yash';
08990301722 Page 16
ASSIGNMENT - 3
(1a) CREATE TABLE
create table employee_iitm089 (Empid number (10), empfname VARCHAR(20),
emplname VARCHAR(20), empgender VARCHAR(10), empaddress VARCHAR(50),
empsalary decimal(10, 2), empdepartment int, empdob DATE);
(1b) INSERT AT LEAST FIVE ROWS
insert into employee_iitm089 values (101, 'Om', 'Sharma', 'Male', 'Punjabi Bagh', 90000, 4,
'02-Nov-96');
insert into employee_iitm089 values (102, 'Kritika', 'Malik', 'Female', 'Tilak Nagar', 75000,
1, '27-mar-01 ');
insert into employee_iitm089 values (103, 'Aman', 'Narang', 'Male', 'Hari Nagar', 50000, 2,
'15-may-02');
insert into employee_iitm089 values (104, 'Ishita', 'Mehra', 'Female', 'Uttam Nagar', 45000,
2, '11-may-98');
insert into employee_iitm089 values (105, 'Tejas', 'Jindal', 'Male', 'Dwarka', 45000, 2, '01-
may-98');
08990301722 Page 17
SELECT ALL RECORDS
Select * from employee_iitm089;
08990301722 Page 18
(2a)SELECT EMPNAME FROM ONE EMPDEPARTMENT
Select empfname from employee_iitm089 where empdepartment = 4;
(2b) SELECT EMPFNAME, EMPLNAME, EMPSALARY ORDER BY
EMPSALARY
Select empfname, emplname, empsalary from employee_iitm089 order by
empsalary desc;
08990301722 Page 19
(2c) DISPLAY EMPFNAME AND EMPLNAMME USING BETWEEN COMMAND AND
COLUMN EMPSALARY
Select empfname, emplname from employee_iitm089 where empsalary between 30000
and 50000;
(2d) DISPLAY EMPFNAME AND EMPLNAME WITH SPECIFIC ADDRESS
Select empfname, emplname from employee_iitm089 where empaddress
='Dwarka';
08990301722 Page 20
(2e) DISPLAY EMPFNAME AND EMPLNAME WITH SPECIFIC EMPGENDER
Select empfname, emplname from employee_iitm089 where empgender ='Female';
(2f) DISPLAY EMPFNAME AND EMPLANAME WITH SPECIFIC EMPDOB
Select empfname, emplname from employee_iitm089 where empdob='02-Nov-
96';
08990301722 Page 21
(2g) DISPLAY EMPFNAME AND EMPLNAME WITH SPECIFIC EMPFNAME AND
EMPLNAME
Select empfname, emplname from employee_iitm089 where empfname ='Tejas' and
emplname ='Jindal';
(2h) RENAME COLUMN EMPADDRESS TO EMPCITY
alter table employee_iitm089 rename column empaddress to empcity;
08990301722 Page 22
(2i) DISPLAY EMPFNAME, EMPLNAME ORDER BY EMPCITY
Select empfname, emplname from employee_iitm089 order by empcity desc;
SELECT ALL EMPLOYEE RECORDS
Select * from employee_iitm089;
08990301722 Page 23
(2j) UPDATE EMPCITY USING EMPID COLUMN
Update employee_iitm089 set empcity = 'Jaipur' where empid = 103;
(2k)DELETE USING EMPID COLUMN
Delete from employee_iitm089 where empid = 102;
08990301722 Page 24
(2l) DISPLAY ALL RECORDS
Select * from employee_iitm089;
DISPLAY EMPFNAME, EMPLNAME WITH A PARTICULAR SALARY
Select empfname, emplname from employee_iitm089 where empsalary <> 30000;
08990301722 Page 25
ASSIGNMENT –
(1a) CREATE TABLE
Create table employee_iintm0899 (empid number (10), empfname varchar(10),
emplname varchar(10), empdepartment int, empdesignation varchar(10), empsalary
decimal (10,2));
(1b) INSERT AT LEAST FIVE ROWS
Insert into employee_iintm0899 values (101, 'Muskan', 'Sharma', 101, 'Manager',
100000);
Insert into employee_iintm0899 values (102, 'Rahul', 'Mongia', 110, 'Assistant', 43000);
Insert into employee_iintm0899 values (103, 'Soni', 'Arora', 123, 'HR', 47000);
Insert into employee_iintm0899 values (104, 'Ruhi', 'Mehta', 130, 'Manager', 50000);
Insert into employee_iintm0899 values (105, 'Moksha', 'Sapra', 119, 'Accountant',
57000);
08990301722 Page 26
SELECT ALL RECORDS
Select * from employee_iintm0899;
08990301722 Page 27
(2a) AVERAGE SALARY OF THE EMPLOYEE
Select avg(empsalary) from employee_iintm0899;
(2b) TOTAL SALARY OF THE EMPLOYEE
Select sum(empsalary) from employee_iintm0899;
08990301722 Page 28
(2c) MINIMUM SALARY OF THE MANAGER
Select min(empsalary) from employee_iintm0899 where empdesignation = 'Manager';
(2d) MAXIMUM SALARY OF THE MANAGER
Select max(empsalary) from employee_iintm0899 where
empdesignation = 'Manager';
08990301722 Page 29
(2e) COUNT THE NUMBER OF MANAGERS
Select count(empdesignation) from employee_iintm0899 where empdesignation =
'Manager'
(2f) INCREASE THE SALARY OF THE EMPLOYEES BY 50%
Select empsalary + empsalary*50/100 from employee_iintm0899;
08990301722 Page 30
(2g) COUNT THE NUMBER OF EMPLOYEES
Select count(empid) from employee_iintm0899;
(2h) Count the number of departments
Select count(empdepartment) from employee_iintm0899;
08990301722 Page 31
(2i) COUNT THE NUMBER OF EMPLOYEES FOR A PARTICULAR DEPARTMENT
Select count(empdepartment) from employee_iintm0899 where empdepartment = 101;
(2j) INCREASE THE SALARY BY 10% WHERE THE SALARY >50000
Select empsalary*10/100 from employee_iintm0899 where empsalary>50000;
08990301722 Page 32
(2k) DISPLAY EMPFNAME, EMPLNAME, EMPSALARY, EMPID, EMPDESIGNATION
FOR MANAGER WITH LEAST SALARY
Select empfname, emplname, empsalary, empid, empdesignation from
employee_iintm0899 where empsalary in (select min(empsalary) from
employee_iintm0899 where empdesignation ='Manager');
(2l) DISPLAY SALARY INCREASE BY15% FOR A PARTICULAR DEPARTMENT
select empsalary *15/100 from employee_iintm0899 where empdepartment=110;
08990301722 Page 33
(2m) COUNT THE NUMBER OF EMPLOYEES USING DESIGNATION
Select count(empid), avg(empsalary) from employee_iintm0899 group by
empdepartment having count (empid)>0;
(2n) DISPLAY EMPDESIGNATION THERE SALARY >40000
Select empdesignation from employee_iintm0899 where empsalary > 40000;
08990301722 Page 34
(2o) SELECT AVERAGE SALARY (NOT INCLUDING MANAGERS)
Select avg(empsalary) from employee_iintm0899 where
empdesignation! = 'Manager';
DISPLAY SUM, MAX, MIN, AVERAGE OF SALARY FOR ASSISTANT
SELECT SUM(empsalary), MAX(empsalary), MIN(empsalary), AVG(empsalary)
FROM employee_iintm0899 WHERE empdesignation = 'Assistant';
08990301722 Page 35
