- 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 nameFirst nameCurrent 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).DisplayYears of Service 1Years of Service 2Last NameFirst NameSort 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 – 50000Name 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 firstnames.*/ HHOL_REGIONSPK Region IDHOL_JOBSPK Job IDHOL_CUSTOMERSPK Customer IDKFK Location_ID++++HTo HOL_LOCATIONSDBMS 130: HOL Small Company ERDHOL_COUNTRIESPK Country_IDFK Region_IDHOL_JOB_HISTORYPK Job History_IDFK Employee_IDFK Job_IDFK Department_IDHOL_ORDERSPK Order NumberKFK Customer_ID|++HHOL_LOCATIONSPK Location IDKFK Country_IDHOL_EMPLOYEESHPK Employee IDFK Department_IDFK Job_IDHOL_ORDER_ITEMSPK Order Item IDFK ISBNKFK|_Order_Number+HHOL_DEPARTMENTSHPK Department IDFK Location_IDHOL_BOOKS+ PK ISBNFK Book_Category_ID++HOL_BOOK_CATEGORYKPK Book_Category_ID FKFKFKFKFKPKFKFKFKUFKPKFKFKFKLegendTables providedTables you completeRegion IDRegion NameCountry CodeCountry NameRegion IDLocation IDStreetCityState ProvincePostal CodeCountry 10Department IDDepartment NameManager IDLocation IDCustomer IDFirst NameMiddle NameLast NameEmailPhoneBirth DateGenderLocation IDOrder NumberOrder DateOrder TotalCustomer_IDSales Person IDOrder Item IDUnit PriceQuantityOrder NumberFKFK ISENHOL REGIONSINTEGERVARCHAR2(250)HOL_COUNTRIESVARCHAR2 250)INTEGERHOL_LOCATIONSINTEGERVARCHAR2(250)VARCHAR2(250)CHAR(2)VARCHAR2(30)INTEGERHOL DEPARTMENTSINTEGERVARCHAR2 250)INTEGERINTEGERHOL_CUSTOMERSINTEGERVARCHAR2(250)VARCHAR2(250)|VARCHAR2 250VARCHAR2/250)VARCHAR2(30)DATEVARCHAR2(30)INTEGERHOL ORDERSINTEGERDATENUMBER 18,2)INTEGERINTEGERHOL_ORDER_ITEMSINTEGERNUMBER 12,2)INTEGERINTEGERINTEGERFKFKNNUFKNNNNFKFKFKFKNNFKFKFKNNFKPRIMARY KEYFOREIGN KEYFKNNNOT NULLUNIQUEEmployee IDFirst NameMiddle NameLast NameEmailPhoneHire DateCurrent SalaryCommision PetFKFK Employee DBonusJob IDManager IDDepartment IDJob IDJob TitleMax SalaryMin SalaryJob History IDStart DateEnd DateJob IDDepartment IDHOL EMPLOYEESISBNBook TitleBook DescriptionBook PriceBook ReviewsUser RatingBook_Category IDINTEGERVARCHAR2 250)VARCHAR2(250)VARCHAR2(250)VARCHAR2(250)VARCHAR2(30)DATE|NUMBER 12,2)NUMBER(2,2)NUMBER 12,2)INTEGERINTEGERINTEGERBook Category IDBook_Category_NemeHOL JOBSHOL_JOB HISTORYINTEGERVARCHAR2(250)NUMBER(12,2)NUMBER 12,2)IDENTITYINTEGERDATEDATEINTEGERINTEGERHOL BOOKSINTEGERVARCHAR2(250)VARCHAR2(4000)NUMBER 12,2)INTEGER|NUMBER(4,2)INTEGERHOL_BOOK_CATEGORYINTEGERVARCHAR2(250)Book_Category_Description VARCHAR2(4000)

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 System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

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.

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.
*/

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

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

SEE SOLUTION Check out a sample Q&A here

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.