| Ex No: 15 |
Working with Strings |
| Aim: |
To demonstrate the String Operations available in MYSQL. |
| Procedure |
|
|
a) Table Creation - Create employee table with details such as employee id, first name, last name, date of birth, gender and contact number. |
|
b) Table Creation - Create working table with details such as employee id, department, post, joining date and salary. |
|
c) Insert 10 records in employee and working table. |
|
d) Display employee details with salary. |
|
e) Display Employee Name full name with Department and designation. |
|
f) Display employee first name in lowercase and last name in uppercase. |
|
g) Display details of employee whose first name length is greater than 6. |
|
h) Display employee name with initials. |
|
i) Display details of employee whose name is a palindrome. |
|
|
| Query |
| a) Employee Table Creation |
CREATE TABLE EMPLOYEE(
EMPID VARCHAR(4) PRIMARY KEY,
FIRSTNAME VARCHAR(20),
LASTNAME VARCHAR(20),
DOB DATE,
GENDER VARCHAR(7),
CONTACT VARCHAR(10));
|
| b) Working Table Creation |
CREATE TABLE WORKING(
EMPID VARCHAR(4),
DEPT VARCHAR(20),
POST VARCHAR(20),
JOINDATE DATE,
SALARY INT);
|
| c) Insert 10 record in each Table
|
Employee Table
INSERT INTO EMPLOYEE(EMPID, FIRSTNAME, LASTNAME, DOB, GENDER, CONTACT)
VALUES ('1001', 'Balu', 'Ramesh', '1986-10-23','male','9123412345');
Working Table
INSERT INTO WORKING (EMPID, DEPT, POST, JOINDATE, SALARY)
VALUES ('1034', 'HR', 'Manager', '2012-04-16', '15000');
|
| d) Query to Display Employee details with salary |
SELECT EMPLOYEE.*,SALARY
FROM EMPLOYEE, WORKING
WHERE EMPLOYEE.EMPID=WORKING.EMPID;
|
| e) Query to Display Employee full name with department and post |
SELECT CONCAT(FIRSTNAME,' ',LASTNAME) NAME, DEPT DEPARTMENT, POST
FROM EMPLOYEE, WORKING
WHERE EMPLOYEE.EMPID=WORKING.EMPID;
|
| f) Query to Display Firstname in lowercase and Lastname in uppercase |
SELECT LCASE(FIRSTNAME), UCASE(LASTNAME) FROM EMPLOYEE;
|
| g) Query to Display Employee with lenght of name greater than 6 |
SELECT EMP.EMPID, EMP.FIRSTNAME, WK.DEPT, WK.POST, WK.SALARY
FROM EMPLOYEE EMP, WORKING WK
WHERE EMP.EMPID=WK.EMPID AND LENGTH(EMP.FIRSTNAME)>6;
|
| h) Query to Display Employee Name with initials |
SELECT CONCAT(UCASE(SUBSTRING(LASTNAME,1,1)),'. ',FIRSTNAME) NAME
FROM EMPLOYEE;
|
| i) Query to Display details of employee whose name is palindrome |
SELECT EMP.FIRSTNAME, EMP.DOB, EMP.DOB, WK.JOINDATE
FROM EMPLOYEE EMP, WORKING WK
WHERE EMP.EMPID=WK.EMPID AND EMP.FIRSTNAME=REVERSE(EMP.FIRSTNAME);
|
| Result: |
Thus String Manipulation functions has been performed using MYSQL queries. |