Scribd
Upload
35 views5 pages

Practical No 5

The document outlines a practical exercise for implementing SQL queries using various character functions such as INITCAP, LOWER, UPPER, LTRIM, RTRIM, TRANSLATE, REPLACE, and SUBSTRING. It includes the creation of an 'employees' table and examples of SQL queries demonstrating the use of these functions on employee data. The queries manipulate employee names and emails to showcase different string operations.

Uploaded by

kunal bandale
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
35 views5 pages

Practical No 5

The document outlines a practical exercise for implementing SQL queries using various character functions such as INITCAP, LOWER, UPPER, LTRIM, RTRIM, TRANSLATE, REPLACE, and SUBSTRING. It includes the creation of an 'employees' table and examples of SQL queries demonstrating the use of these functions on employee data. The queries manipulate employee names and emails to showcase different string operations.

Uploaded by

kunal bandale
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

Shri Guru Gobind Singhji Institute of Engineering and Technology

Department of Information Technology

Name: Kunal Bandale


Reg. No.: 2022BIT501

PRACTICAL.NO 5
AIM: Implement SQL queries using character functions like initcap, loer, upper, itrim, rtrim,
translate, replace, substring etc.
THEORY:
Creating table and inserting values:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);
INSERT INTO employees VALUES
(1, 'John', 'Doe', 'john.doe@example.com'),
(2, 'Jane', 'Smith', 'jane.smith@example.com'),
(3, 'Bob', 'Johnson', 'bob.johnson@example.com');

1. INITCAP:
SELECT
CONCAT(UPPER(SUBSTRING(first_name, 1, 1)), LOWER(SUBSTRING(first_name, 2))) AS capitalized_first_name,
CONCAT(UPPER(SUBSTRING(last_name, 1, 1)), LOWER(SUBSTRING(last_name, 2))) AS capitalized_last_name,
email
FROM employees;
2. LOWER:
SELECT
LOWER(first_name) AS lower_first_name,
LOWER(last_name) AS lower_last_name,
email
FROM employees;

3. UPPER:
SELECT
UPPER(first_name) AS upper_first_name,
UPPER(last_name) AS upper_last_name,
email
FROM employees;
4. LTRIM and RTRIM:
SELECT
first_name,
last_name,
LTRIM(RTRIM(email)) AS trimmed_email
FROM employees;

5. TRANSLATE:
SELECT
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(first_name, 'a', 'A'), 'e', 'E'), 'i', 'I'), 'o', 'O'), 'u', 'U') AS
replaced_vowels,
last_name,
email
FROM employees;

6. REPLACE:
SELECT
first_name,
last_name,
REPLACE(email, '@example.com', '@newdomain.com') AS updated_email
FROM employees;

7. SUBSTRING:
SELECT
SUBSTRING(first_name, 1, 3) AS first_three_letters,
last_name,
email
FROM employees;

You might also like