Write SQL queries for the following: select * From hr.employees 1. display emplo

Question

Write SQL queries for the following:

select * From hr.employees

1. display employee_id,first_name, email , email in all lowercase
ex 100,Steven,SKING,sking

2. Display employee_id, first_name, commision_pct, new_commission_pct
Logic for new_commission_pct - If its null display 0 other wise continue to display commission_pct

3. Display employee_id, first_name, phone_number, area_code
Logic for area_code- Display first 3 characters of each phone number only
ex 100,Steven,510.123.4567,510

4. Display count of people whose phone_number starts with 590. Display the result as emp_area_code_590 with the count.

5. display employee_id, first_name, length_of_first_name
ex 100,Steven,6

6. What is the maximum length of table and colun length in Oracle data. Can table name or column names have spaces

7. display employee_id,name,email,email_in_Initcap (First character in upper case followed by rest in lowercase)
100,Steven,SKING,Sking

Explanation / Answer

1.SELECT EMPLOYEE_ID,FIRSTNAME,EMAIL,LCASE(EMAIL) FROM EMPLOYEE;

2.(SELECT EMPLOYEE_ID,FIRSTNAME,COMMISSION_PCT FROM EMPLOYEE) UNION (SELECT NEW_COMMISON_PCT ,CASE WHEN COMMISON_PCT IS NULL THEN 0 WHEN COMMISSON_PCT IS NOT NULL THEN COMMISSION_PCT AS END RESULT FROM EMPLOYEE);

3.SELECT EMPLOYEE_ID,FIRSTNAME,PHONENUMBER,MID(PHONENUMBER,1,3) AS AREA_CODE FROM EMPLOYEE;

4.SELECT COUNT(EMPLOYEE_ID) FROM EMPLOYEE WHERE PHONENUMBER LIKE "590%";

5.SELECT EMPLOYEE_ID,FIRSTNAME,LENGTH(FIRSTNAME) FROM EMPLOYEE;

6. Table and column names should not contain spaces .it contains all alphabets , numbers and some special symbols.both table name and column name should start with either alphabet or "_".

The maximum number of rows can possible is 2 ^ 64

The maximum number of colums in general is 1024.

7.SELECT EMPLOYEE_ID,FIRSTNAME,EMAIL,INITCAP(EMAIL) FROM EMPLOYEE;

ALL QUERIES WILL GIVE CORRECT OUTPUTS. IF YOU HAVE ANY DOUBTS POST A COMMENT

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.