Empid |
EmpName |
Department |
ContactNo |
EmailId |
EmpHeadId |
101 |
Isha |
E-101 |
1234567890 |
isha@gmail.com |
105 |
102 |
Priya |
E-104 |
1234567890 |
priya@yahoo.com |
103 |
103 |
Neha |
E-101 |
1234567890 |
neha@gmail.com |
101 |
104 |
Rahul |
E-102 |
1234567890 |
rahul@yahoo.com |
105 |
105 |
Abhishek |
E-101 |
1234567890 |
abhishek@gmail.com |
102 |
CREATE
TABLE employee (
empid INT NOT NULL PRIMARY KEY,
empname VARCHAR(100) NOT NULL,
department VARCHAR(50) NOT NULL,
contactno BIGINT NOT NULL,
emailid VARCHAR(100) NOT NULL,
empheadid INT NOT NULL
);
#2
Insert record in table as shown above.
INSERT
INTO Employees (Empid, EmpName, Department, ContactNo, EmailId, EmpHeadId)
VALUES
(101,
'Isha', 'E-101', 1234567890, 'isha@gmail.com', 105),
(102,
'Priya', 'E-104', 1234567890, 'priya@yahoo.com', 103),
(103,
'Neha', 'E-101', 1234567890, 'neha@gmail.com', 101),
(104,
'Rahul', 'E-102', 1234567890, 'rahul@yahoo.com', 105),
(105,
'Abhishek', 'E-101', 1234567890, 'abhishek@gmail.com', 102);
#3 Create a table With name as EmpDept
DeptId |
DeptName |
Dept_off |
DeptHead |
E-101 |
HR |
Monday |
105 |
E-102 |
Development |
Tuesday |
101 |
E-103 |
House Keeping |
Saturday |
103 |
E-104 |
Sales |
Sunday |
104 |
E-105 |
Purchage |
Tuesday |
104 |
create table empdept(
deptid
varchar(50) primary key,
deptname
varchar(100),
dept_off
varchar(100),
depthead
int foreign key references employee(empid));
#4. Insert the records in table as shown above.
INSERT INTO
Departments (DeptId, DeptName, Dept_off, DeptHead)
VALUES
('E-101', 'HR', 'Monday',
105),
('E-102', 'Development',
'Tuesday', 101),
('E-103', 'Hous Keeping',
'Saturday', 103),
('E-104', 'Sales', 'Sunday', 104),
('E-105', 'Purchage',
'Tuesday', 104);
#5. Create a table With name as EmpSalary:
EmpId | Salary | IsPermanent |
---|---|---|
101 | 2000 | Yes |
102 | 10000 | Yes |
103 | 5000 | No |
104 | 1900 | Yes |
105 | 2300 | Yes |
#6. Insert the records in table as shown above.
INSERT
INTO Employees (Empid, Salary, IsPermanent)
VALUES
(101,
2000, 'Yes'),
(102,
10000, 'Yes'),
(103,
5000, 'No'),
(104,
1900, 'Yes'),
(105,
2300, 'Yes');
Start with Practical Query
1.Select the detail of the employee whose name start with P.
Ans : select * from employee where empname
like 'p%'
2.How many permanent candidate take salary more than 5000.
Ans: select count(salary) as count from empsalary where ispermanent='yes' and salary>5000
3.Select the detail of employee whose emailId is in gmail.
Ans:select * from employee where emaildid like '%@gmail.com'