Data Manipulation Language using MySQL
Question:
Work with Data Manipulation Language (DML) using MySQL. You will create the database, multiple tables, insert data and retrieve information based on the questions given below.
1)Show all the records of employees who are managers and salesmen. Use the IN operator.)
| EMPLOYEE
ID |
EMP
NAME |
EMP
GENDER |
BRANCH
ID |
EMP
SALARY |
EMP
COMMISSION |
EMP
POSITION |
EMP
CONTACT |
| 21912 | Sarimah Ibrahim | F | 1 | 5000.00 | 1300.00 | Salesman | 019-6548254 |
| 21913 | Phuah Weng Chun | M | 2 | 5500.00 | 1500.00 | Salesman | 019-7778525 |
| 21914 | Muthu Samy | M | 2 | 12000.00 | 8000.00 | Manager | 012-3335588 |
| 21915 | Azrul Ali | M | 1 | 13000.00 | 8800.00 | Manager | 017-8889997 |
| 21917 | Kamal Hassan | M | 4 | 5150.00 | 2000.00 | Salesman | 017-2878888 |
| 21918 | Kamal
Ahmad |
M | 4 | 6000.00 | 2000.00 | Asst.
Manager |
017-2585220 |
2) List all the employees’ details where the name starts with “Kam”.
| EMPLOYEE
ID |
EMP
NAME |
EMP
GENDER |
BRANCH
ID |
EMP
SALARY |
EMP
COMMISSION |
EMP
POSITION |
EMP
CONTACT |
| 21917 | Kamal Hassan | M | 4 | 5150.00 | 2000.00 | Salesman | 017-2878888 |
| 21918 | Kamal
Ahmad |
M | 4 | 6000.00 | 2000.00 | Asst.
Manager |
017-2585220 |
3) List all the employees’ details, where the name ends with “San”.
| EMPLOYEE
ID |
EMP
NAME |
EMP
GENDER |
BRANCH
ID |
EMP
SALARY |
EMP
COMMISSION |
EMP
POSITION |
EMP
CONTACT |
| 21917 | Kamal Hassan | M | 4 | 5150.00 | 2000.00 | Salesman | 017-2878888 |
Summary:
Initially, the table is created using the DDL command CREATE. And then insert the details of the employees using the INSERT command. Next, the DML command SELECT is used to retrieve certain records from the table.
Explanation:
Data Manipulation Language using MySQL:
Firstly we are using create command to create the table employee and insert the employee details’ one by one.
CREATE command:
create table table_name( col_1, datatype, col_2, datatype, ... );
INSERT command:
insert into table_name (column1, column3, …)
values (value1, value2, …);
SQL CODE FOR CREATING TABLE EMPLOYEE AND INSERTING VALUES INTO IT AND DISPLAY IT:
create table Employee(
Employee_ID int, Emp_Name varchar(30),
Emp_Gender char(1), Branch_Id int,
Emp_Commission float(2), Emp_Salary float(2),
Emp_Position varchar(20),Emp_Contact varchar(20)
);
insert into Employee(Employee_ID, Emp_Name, Emp_Gender, Branch_Id, Emp_Commission, Emp_Salary, Emp_Position, Emp_Contact )
values('21912', 'Sarimah Ibrahim', 'F', '1', '1300.00', '5000.00', 'Salesman', '019-6548254');
insert into Employee(Employee_ID, Emp_Name, Emp_Gender, Branch_Id, Emp_Commission, Emp_Salary, Emp_Position, Emp_Contact )
values('21913', 'Phuah Weng Chun', 'M', '2', '1500.00', '5500.00', 'Salesman', '019-778525');
insert into Employee(Employee_ID, Emp_Name, Emp_Gender, Branch_Id, Emp_Commission, Emp_Salary, Emp_Position, Emp_Contact )
values('21914', 'Muthu Samy', 'M', '2', '8000.00', '12000.00', 'Manager', '012-3335588');
insert into Employee(Employee_ID, Emp_Name, Emp_Gender, Branch_Id, Emp_Commission, Emp_Salary, Emp_Position, Emp_Contact )
values('21915', 'Azrul Ali', 'M', '1', '8800.00', '13000.00', 'Manager', '017-8889997');
insert into Employee(Employee_ID, Emp_Name, Emp_Gender, Branch_Id, Emp_Commission, Emp_Salary, Emp_Position, Emp_Contact )
values('21917', 'Kamal Hassan', 'M', '4', '2000.00', '5150.00', 'Salesman', '017-2878888');
insert into Employee(Employee_ID, Emp_Name, Emp_Gender, Branch_Id, Emp_Commission, Emp_Salary, Emp_Position, Emp_Contact )
values('21918', 'Kamal Ahmad', 'M', '4', '2000.00', '6000.00', 'Asst.Manager', '017-2585220');
Select * from Employee;
The output after insertion is:
SQL CODE FOR DISPLAYING SALESMAN AND MANAGER:
select * from Employee
where Emp_Position in (‘Salesman’, ‘Manager’);
SQL CODE FOR DISPLAYING Emp_Name starting with Kam.
select * from Employee
where Emp_Name like ‘kam%’;
SQL CODE FOR DISPLAYING Emp_Name ending with san.
select * from Employee
where Emp_Name like ‘%san’;
