Question

The Common Requirements Of All Questions Are The Same. Read Each Question Carefully And Submit ... Question: The common requirements of all questions are the same. Read each question carefully and submit a ... The common requirements of all questions are the same. Read each question carefully and submit a query to fulfill for each question. Also, students need to submit the result of the corresponding query under the query. The query result should be copied and pasted as it is printed on the screen without any modification. If necessary, the column names appearing in the result should be converted appropriately using "AS" statement. Also, assignment has to be written with Microsoft Word for feedback. For technical issue, other formats, such as image, sql, are not appropriate to receive feedback and comments. Don’t delete or change questions. You just need to insert both your TEXT query and captured screenshot result under your query.

1. Using SELECT statement with CHR function, || Operator, and referring ASCII table, print your FULL name, although the example shows your instructor’s first name only. Also, there should be a space between first and last name (10 points). Eg) SELECT CHR(67)||CHR(104)||CHR(114)||CHR(105)||CHR(115) from dual; ---------------------------------------- Chris

2. Show all columns from locations table. If value of postal_code column is null, then replace null values with “No Postal Code”. If value of state_province column is null, then replace null values with “No State”. (10 points)

3. Calculate how many days the employees have been employed. You need to show hire_date, current date, and difference between hire_date and current date. (10 points) .

4.Using the NVL function, show the same results of the following select statement. (5 points) SELECT first_name, salary, commission_pct, NVL2(commission_pct, salary + salary * commission_pct, salary) compensation FROM employees WHERE first_name LIKE 'T%';

5. Using the CONCAT function, show the same results of the following select statement. (5 points) SELECT last_name || ', ' || first_name, department_id, job_id FROM employees WHERE department_id = 50 ORDER BY last_name, first_name;

This all the information given.

ACCOUNTS: CUST_NAME,ACC_OPEN_DATE, SALARY

BONUSES: EMPLOYEE_ID, BONUS

COUNTRIES: COUNTRY_ID, COUNTRY_NAME, REGION_ID

DBUSER: USER_ID, USERNAME, CREATED_BY, CREATED DATE

DEPARTMENTS: DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID

EMPLOYEES: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISION_PCT, MANAGER_ID, DEPARTMENT_ID

JOB_HISTORY: EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID

JOBS: JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY

LOCATIONS: LOCATION_ID, STREET_ADDRESS, POSTAL_COSE, CITY, STATE_PROVINCE, COUNTRY_ID

ORDERS: ORDER_ID, ORDER_DATE,ORDER_MODE, CUSTOMER_ID, ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID

REGIONS: REGION_ID, REGION_NAME

Answer 1

Que.2) Answer:-

1. Below query shows all columns from the Locations table:

>select * from Locations

Example Output:

2. If the value of postal_code column is null, then replace null values with “No Postal Code”:

>Update Locations set Postal_Code = 'No Postal Code' where Postal_Code is NULL

Example Output:

3. If the value of state_province column is null, then replace null values with “No State”.

>Update Locations set State_Province='No State' where State_Province is NULL

Example Output:

Que.3) Answer:-

For Example, the Employees table has the following data as:

Query to find the difference between Hire_Date & Current_Date (The employment period of employees):

>select *, datediff(day,Hire_Date,GETDATE()) as Eployement_Period_In_Days from Employees

Output: (Please see the last column(Eployement_Period_In_Days) for the employment period of employees in days)