Single-Row Functions --============================================== /* 1. Use the CONCAT function (not the || operator) to concatenate the customers’ last and first names into one column. Leave a ', ' between names. Give the column an alias: "Customer Full Name". Sort your results by the column alias. */ /* 2. Write a query using the SUBSTR function to display a list of distinct area codes (first 3 digits of phone number) for employees. Name the column “Area Codes”. Sort your results by the column alias. */ /* 3. Write a query using the LENGTH function to display a list of books with titles that are longer than 50 characters. Name the column “Long Titles”. Also, display the book titles of these books. Sort your results by longest title first and then by title. */ /* 4. Write a query using the MOD function to display a list of employees with salaries that are odd numbers. [You may need to look up Oracle's MOD() function.] For these employees, display Last name First name Current salary Using Oracle NVL() function [Look up the Oracle NVL() function to find out how it works.] Display Phone first and name the column as "Phone First" Display Email first and name the column as "Email First" Sort results by last and first names. */ /* 5. Write a query to calculate years of service for each employee. Calculate years of service in two ways and display them as two separate columns. 1. SYSDATE-HIRE_DATE and name the column "Years of Service 1" 2. MONTHS_BETWEEN() function and name the column "Years of Service 2" BE SURE YOUR RESULTS ARE IN YEARS. Round each result to integers (whole numbers). Display Years of Service 1 Years of Service 2 Last Name First Name Sort results so the employee with most years of service displays first. */ /* 6. Write a SQL statement using CASE to classify employees into salary bands based on their salary: • LOW = 0 – 5000 • MID = 5000 – 10000 • HIGH = 10001 – 50000 Name the result of the CASE statement "Salary Band" Your results should display the employees’ name, salary, and salary band. Sort your results by salary descending and and salary band, then last and first names. */
Single-Row Functions --============================================== /* 1. Use the CONCAT function (not the || operator) to concatenate the customers’ last and first names into one column. Leave a ', ' between names. Give the column an alias: "Customer Full Name". Sort your results by the column alias. */ /* 2. Write a query using the SUBSTR function to display a list of distinct area codes (first 3 digits of phone number) for employees. Name the column “Area Codes”. Sort your results by the column alias. */ /* 3. Write a query using the LENGTH function to display a list of books with titles that are longer than 50 characters. Name the column “Long Titles”. Also, display the book titles of these books. Sort your results by longest title first and then by title. */ /* 4. Write a query using the MOD function to display a list of employees with salaries that are odd numbers. [You may need to look up Oracle's MOD() function.] For these employees, display Last name First name Current salary Using Oracle NVL() function [Look up the Oracle NVL() function to find out how it works.] Display Phone first and name the column as "Phone First" Display Email first and name the column as "Email First" Sort results by last and first names. */ /* 5. Write a query to calculate years of service for each employee. Calculate years of service in two ways and display them as two separate columns. 1. SYSDATE-HIRE_DATE and name the column "Years of Service 1" 2. MONTHS_BETWEEN() function and name the column "Years of Service 2" BE SURE YOUR RESULTS ARE IN YEARS. Round each result to integers (whole numbers). Display Years of Service 1 Years of Service 2 Last Name First Name Sort results so the employee with most years of service displays first. */ /* 6. Write a SQL statement using CASE to classify employees into salary bands based on their salary: • LOW = 0 – 5000 • MID = 5000 – 10000 • HIGH = 10001 – 50000 Name the result of the CASE statement "Salary Band" Your results should display the employees’ name, salary, and salary band. Sort your results by salary descending and and salary band, then last and first names. */
Database Systems: Design, Implementation, & Management
11th Edition
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Chapter11: Database Performance Tuning And Query Optimization
Section: Chapter Questions
Problem 20P
Related questions
Question
- Single-Row Functions
--==============================================
/*
1. Use the CONCAT function (not the || operator) to concatenate the customers’
last and first names into one column.
Leave a ', ' between names.
Give the column an alias: "Customer Full Name".
Sort your results by the column alias.
*/
/*
2. Write a query using the SUBSTR function to display a list
of distinct area codes (first 3 digits of phone number) for employees.
Name the column “Area Codes”.
Sort your results by the column alias.
*/
/*
3. Write a query using the LENGTH function to display a list of books with
titles
that are longer than 50 characters.
Name the column “Long Titles”.
Also, display the book titles of these books.
Sort your results by longest title first and then by title.
*/
/*
4. Write a query using the MOD function to display a list of employees with
salaries that are odd numbers.
[You may need to look up Oracle's MOD() function.]
For these employees, display
Last name
First name
Current salary
Using Oracle NVL() function
[Look up the Oracle NVL() function to find out how it works.]
Display Phone first and name the column as "Phone First"
Display Email first and name the column as "Email First"
Sort results by last and first names.
*/
/*
5. Write a query to calculate years of service for each employee.
--==============================================
/*
1. Use the CONCAT function (not the || operator) to concatenate the customers’
last and first names into one column.
Leave a ', ' between names.
Give the column an alias: "Customer Full Name".
Sort your results by the column alias.
*/
/*
2. Write a query using the SUBSTR function to display a list
of distinct area codes (first 3 digits of phone number) for employees.
Name the column “Area Codes”.
Sort your results by the column alias.
*/
/*
3. Write a query using the LENGTH function to display a list of books with
titles
that are longer than 50 characters.
Name the column “Long Titles”.
Also, display the book titles of these books.
Sort your results by longest title first and then by title.
*/
/*
4. Write a query using the MOD function to display a list of employees with
salaries that are odd numbers.
[You may need to look up Oracle's MOD() function.]
For these employees, display
Last name
First name
Current salary
Using Oracle NVL() function
[Look up the Oracle NVL() function to find out how it works.]
Display Phone first and name the column as "Phone First"
Display Email first and name the column as "Email First"
Sort results by last and first names.
*/
/*
5. Write a query to calculate years of service for each employee.
Calculate years of service in two ways and display them as two separate
columns.
1. SYSDATE-HIRE_DATE and name the column "Years of Service 1"
2. MONTHS_BETWEEN() function and name the column "Years of Service 2"
BE SURE YOUR RESULTS ARE IN YEARS.
Round each result to integers (whole numbers).
Display
Years of Service 1
Years of Service 2
Last Name
First Name
Sort results so the employee with most years of service displays first.
*/
/*
6. Write a SQL statement using CASE to classify employees into salary bands
based on their salary:
• LOW = 0 – 5000
• MID = 5000 – 10000
• HIGH = 10001 – 50000
Name the result of the CASE statement "Salary Band"
Your results should display the employees’ name, salary, and salary band.
Sort your results by salary descending and and salary band, then last and first
names.
*/
columns.
1. SYSDATE-HIRE_DATE and name the column "Years of Service 1"
2. MONTHS_BETWEEN() function and name the column "Years of Service 2"
BE SURE YOUR RESULTS ARE IN YEARS.
Round each result to integers (whole numbers).
Display
Years of Service 1
Years of Service 2
Last Name
First Name
Sort results so the employee with most years of service displays first.
*/
/*
6. Write a SQL statement using CASE to classify employees into salary bands
based on their salary:
• LOW = 0 – 5000
• MID = 5000 – 10000
• HIGH = 10001 – 50000
Name the result of the CASE statement "Salary Band"
Your results should display the employees’ name, salary, and salary band.
Sort your results by salary descending and and salary band, then last and first
names.
*/
Transcribed Image Text:H
HOL_REGIONS
PK Region ID
HOL_JOBS
PK Job ID
HOL_CUSTOMERS
PK Customer ID
KFK Location_ID
++
++
H
To HOL_LOCATIONS
DBMS 130: HOL Small Company ERD
HOL_COUNTRIES
PK Country_ID
FK Region_ID
HOL_JOB_HISTORY
PK Job History_ID
FK Employee_ID
FK Job_ID
FK Department_ID
HOL_ORDERS
PK Order Number
KFK Customer_ID
|+
+H
HOL_LOCATIONS
PK Location ID
KFK Country_ID
HOL_EMPLOYEES
HPK Employee ID
FK Department_ID
FK Job_ID
HOL_ORDER_ITEMS
PK Order Item ID
FK ISBN
KFK|_Order_Number
+H
HOL_DEPARTMENTS
HPK Department ID
FK Location_ID
HOL_BOOKS
+ PK ISBN
FK Book_Category_ID
++
HOL_BOOK_CATEGORY
KPK Book_Category_ID
Transcribed Image Text:FK
FK
FK
FK
FK
PK
FK
FK
FK
U
FK
PK
FK
FK
FK
Legend
Tables provided
Tables you complete
Region ID
Region Name
Country Code
Country Name
Region ID
Location ID
Street
City
State Province
Postal Code
Country 10
Department ID
Department Name
Manager ID
Location ID
Customer ID
First Name
Middle Name
Last Name
Email
Phone
Birth Date
Gender
Location ID
Order Number
Order Date
Order Total
Customer_ID
Sales Person ID
Order Item ID
Unit Price
Quantity
Order Number
FK
FK ISEN
HOL REGIONS
INTEGER
VARCHAR2(250)
HOL_COUNTRIES
VARCHAR2 250)
INTEGER
HOL_LOCATIONS
INTEGER
VARCHAR2(250)
VARCHAR2(250)
CHAR(2)
VARCHAR2(30)
INTEGER
HOL DEPARTMENTS
INTEGER
VARCHAR2 250)
INTEGER
INTEGER
HOL_CUSTOMERS
INTEGER
VARCHAR2(250)
VARCHAR2(250)
|VARCHAR2 250
VARCHAR2/250)
VARCHAR2(30)
DATE
VARCHAR2(30)
INTEGER
HOL ORDERS
INTEGER
DATE
NUMBER 18,2)
INTEGER
INTEGER
HOL_ORDER_ITEMS
INTEGER
NUMBER 12,2)
INTEGER
INTEGER
INTEGER
FK
FK
NN
U
FK
NN
NN
FK
FK
FK
FK
NN
FK
FK
FK
NN
FK
PRIMARY KEY
FOREIGN KEY
FK
NN
NOT NULL
UNIQUE
Employee ID
First Name
Middle Name
Last Name
Email
Phone
Hire Date
Current Salary
Commision Pet
FK
FK Employee D
Bonus
Job ID
Manager ID
Department ID
Job ID
Job Title
Max Salary
Min Salary
Job History ID
Start Date
End Date
Job ID
Department ID
HOL EMPLOYEES
ISBN
Book Title
Book Description
Book Price
Book Reviews
User Rating
Book_Category ID
INTEGER
VARCHAR2 250)
VARCHAR2(250)
VARCHAR2(250)
VARCHAR2(250)
VARCHAR2(30)
DATE
|NUMBER 12,2)
NUMBER(2,2)
NUMBER 12,2)
INTEGER
INTEGER
INTEGER
Book Category ID
Book_Category_Neme
HOL JOBS
HOL_JOB HISTORY
INTEGER
VARCHAR2(250)
NUMBER(12,2)
NUMBER 12,2)
IDENTITY
INTEGER
DATE
DATE
INTEGER
INTEGER
HOL BOOKS
INTEGER
VARCHAR2(250)
VARCHAR2(4000)
NUMBER 12,2)
INTEGER
|NUMBER(4,2)
INTEGER
HOL_BOOK_CATEGORY
INTEGER
VARCHAR2(250)
Book_Category_Description VARCHAR2(4000)
Expert Solution
Introduction
As my company rule I can answer first 3 question only. Note For the First single row section you don't provide any table structure. So I cant add output screenshot.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781285196145
Author:
Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:
Cengage Learning
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781305627482
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning