Demetrius_assignment_10

rtf

School

Tallahassee Community College *

*We aren’t endorsed by this school

Course

2540

Subject

Computer Science

Date

Dec 6, 2023

Type

rtf

Pages

Uploaded by JudgeOkapiMaster952

Student name: Demetrius Melton Date: 11/9/2023 This hands-on activity will help practice the following chapter 9 learning objectives  Single-row functions return a result for each row or record processed.  Case conversion functions, such as UPPER, LOWER, and INITCAP, can be used to alter the letter case of character strings.  Character manipulation functions can be used to extract substrings (portions of a string), identify the position of a substring in a string, replace occurrences of a string with another string, determine the length of a character string, and trim spaces or characters from strings.  Nesting one function inside another allows performing multiple operations on data.  Simple number functions, such as ROUND and TRUNC, can round or truncate a number on both the left and right side of a decimal.  The MOD function is used to return the remainder of a division operation.  Date functions can be used to perform calculations with dates or change the format of dates entered by a user.  Regular expressions enable complex pattern-matching operations.  The NVL, NVL2, and NULLIF functions are used to identify and manipulate NULL values.  The TO_CHAR function enables you to display numeric data and dates in a specific format.  The DECODE function allows determining the resulting value by testing for equality to a specific value.  The searched CASE expression enables you to evaluate conditions to determine the resulting value.  The SOUNDEX function looks for records based on the phonetic pronunciation of characters.  The DUAL table can be helpful when testing functions. Q1 Produce a list of all customer names in which the first letter of the first and last names is in uppercase and the rest are in lowercase. SQL> SELECT INITCAP(firstname) "First Name", INITCAP(lastname) "Last Name" 2 FROM customers; First Name Last Name ---------- ---------- Bonita Morales Ryan Thompson Leila Smith Thomas Pierson Cindy Girard Meshia Cruz Tammy Giana Kenneth Jones Jorge Perez Jake Lucas Reese Mcgovern

William Mckenzie Nicholas Nguyen Jasmine Lee Steve Schell Michell Daum Becca Nelson Greg Montiasa Jennifer Smith Kenneth Falah 20 rows selected. Q2 Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text “NOT REFERRED” if the customer wasn’t referred to JustLee Books by another customer or “REFERRED” if the customer was referred. FIRSTNAME LASTNAME NVL2(REFERRE ---------- ---------- ------------ BONITA MORALES NOT REFERRED RYAN THOMPSON NOT REFERRED LEILA SMITH NOT REFERRED THOMAS PIERSON NOT REFERRED CINDY GIRARD NOT REFERRED MESHIA CRUZ NOT REFERRED TAMMY GIANA REFERRED KENNETH JONES NOT REFERRED JORGE PEREZ REFERRED JAKE LUCAS NOT REFERRED REESE MCGOVERN NOT REFERRED WILLIAM MCKENZIE NOT REFERRED NICHOLAS NGUYEN REFERRED JASMINE LEE NOT REFERRED STEVE SCHELL NOT REFERRED MICHELL DAUM REFERRED BECCA NELSON NOT REFERRED GREG MONTIASA NOT REFERRED JENNIFER SMITH REFERRED KENNETH FALAH NOT REFERRED 20 rows selected. Q3 Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies. SQL> SELECT title, TO_CHAR(quantity*(paideach-cost), '$999.99') "PROFIT" 2 FROM books JOIN orderitems USING (isbn) 3 WHERE order# = 1002; TITLE PROFIT ------------------------------ --------

DATABASE IMPLEMENTATION $49.10 Q4 Display a list of all book titles and the percentage of markup for each book. The percentage of markup should be displayed as a whole number (that is, multiplied by 100) with no decimal position, followed by a percent sign (for example, .2793 = 28%). (The percentage of markup should reflect the difference between the retail and cost amounts as a percent of the cost.) SQL> SELECT title, ROUND((retail-cost)/cost*100, 0)||'%' 2 FROM books; TITLE ROUND((RETAIL-COST)/COST*100,0)||'%' ------------------------------ ----------------------------------------- BODYBUILD IN 10 MINUTES A DAY 65% REVENGE OF MICKEY 55% BUILDING A CAR WITH TOOTHPICKS 59% DATABASE IMPLEMENTATION 78% COOKING WITH MUSHROOMS 60% HOLY GRAIL OF ORACLE 61% HANDCRANKED COMPUTERS 15% E-BUSINESS THE EASY WAY 44% PAINLESS CHILD-REARING 87% THE WOK WAY TO COOK 51% BIG BEAR AND LITTLE DOVE 68% HOW TO GET FASTER PIZZA 68% HOW TO MANAGE THE MANAGER 107% SHORTEST POEMS 83% 14 rows selected. Q5 Display the current day of the week, hour, minutes, and seconds of the current date setting on the computer you’re using. SQL> SELECT TO_CHAR(CURRENT_DATE, 'DAY, HH:MI:SS') 2 FROM DUAL; TO_CHAR(CURRENT_DATE,'DAY,HH:MI:SS') ---------------------------------------------- THURSDAY , 10:06:24 Q6 Create a list of all book titles and costs. Precede each book’s cost with asterisks so that the width of the displayed Cost field is 12. SQL> SELECT title, LPAD(cost, 12, '*') 2 FROM books; TITLE LPAD(COST,12,'*') ------------------------------ ------------------------------------------------ BODYBUILD IN 10 MINUTES A DAY *******18.75 REVENGE OF MICKEY ********14.2 BUILDING A CAR WITH TOOTHPICKS ********37.8 DATABASE IMPLEMENTATION ********31.4 COOKING WITH MUSHROOMS ********12.5

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

HOLY GRAIL OF ORACLE *******47.25 HANDCRANKED COMPUTERS ********21.8 E-BUSINESS THE EASY WAY ********37.9 PAINLESS CHILD-REARING **********48 THE WOK WAY TO COOK **********19 BIG BEAR AND LITTLE DOVE ********5.32 HOW TO GET FASTER PIZZA *******17.85 HOW TO MANAGE THE MANAGER ********15.4 SHORTEST POEMS *******21.85 14 rows selected. Q7 Determine the length of each customer’s last name. Display the name before each last name’s length. SQL> SELECT DISTINCT lastname, LENGTH(lastname) "Length" 2 FROM customers; LASTNAME Length ---------- ---------- MORALES 7 THOMPSON 8 SMITH 5 PIERSON 7 GIRARD 6 CRUZ 4 GIANA 5 JONES 5 PEREZ 5 LUCAS 5 MCGOVERN 8 MCKENZIE 8 NGUYEN 6 LEE 3 SCHELL 6 DAUM 4 NELSON 6 MONTIASA 8 FALAH 5 19 rows selected. Q8 Using today’s date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title, publication date, current date, and age. SQL> SELECT title, pubdate, SYSDATE, 2 TRUNC(MONTHS_BETWEEN(SYSDATE, pubdate), 0) Age 3 FROM books; TITLE PUBDATE SYSDATE AGE ------------------------------ --------- --------- ---------- BODYBUILD IN 10 MINUTES A DAY 21-JAN-05 09-NOV-23 225 REVENGE OF MICKEY 14-DEC-05 09-NOV-23 214

BUILDING A CAR WITH TOOTHPICKS 18-MAR-06 09-NOV-23 211 DATABASE IMPLEMENTATION 04-JUN-03 09-NOV-23 245 COOKING WITH MUSHROOMS 28-FEB-04 09-NOV-23 236 HOLY GRAIL OF ORACLE 31-DEC-05 09-NOV-23 214 HANDCRANKED COMPUTERS 21-JAN-05 09-NOV-23 225 E-BUSINESS THE EASY WAY 01-MAR-06 09-NOV-23 212 PAINLESS CHILD-REARING 17-JUL-04 09-NOV-23 231 THE WOK WAY TO COOK 11-SEP-04 09-NOV-23 229 BIG BEAR AND LITTLE DOVE 08-NOV-05 09-NOV-23 216 HOW TO GET FASTER PIZZA 11-NOV-06 09-NOV-23 203 HOW TO MANAGE THE MANAGER 09-MAY-03 09-NOV-23 246 SHORTEST POEMS 01-MAY-05 09-NOV-23 222 14 rows selected. Q9 Determine the calendar date of the next occurrence of Wednesday, based on today’s date SQL> SELECT NEXT_DAY(SYSDATE, 'WEDNESDAY') 2 FROM DUAL; NEXT_DAY( --------- 15-NOV-23 Q10 Produce a list of each customer number and the third and fourth digits of his or her zip code. The query should also display the position of the first occurrence of a 3 in the customer number, if it exists. SQL> SELECT customer#, SUBSTR(zip, 3, 2), 2 INSTR(customer#, 3) 3 FROM customers; CUSTOMER# SUBSTR(Z INSTR(CUSTOMER#,3) ---------- -------- ------------------ 1001 32 0 1002 40 0 1003 30 4 1004 70 0 1005 11 0 1006 21 0 1007 71 0 1008 00 0 1009 51 0 1010 31 0 1011 60 0 1012 11 0 1013 71 4 1014 41 0 1015 11 0 1016 50 0 1017 00 0 1018 20 0 1019 96 0

1020 60 0 20 rows selected.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Related Documents

Python Data Representations Week1.docx

Basic Python Syntax Week3.docx

Strings Week1.docx

Basic Python Syntax Week1.docx

ECE 578 quiz 8.1 & 8.2.jpeg

AVERAGE and Financial ratios completed.xlsx

SCS 100 Module One Activity Template (1) (2) (1).docx

case study.docx

HW3-DecisionTrees.docx

Lab2_2023_Questions.docx

Week 1_ Homework-Review.pdf

01-04_task1 (1).pdf

Recommended textbooks for you

C++ Programming: From Problem Analysis to Program...

Computer Science

ISBN:9781337102087

Author:D. S. Malik

Publisher:Cengage Learning

C++ for Engineers and Scientists

Computer Science

ISBN:9781133187844

Author:Bronson, Gary J.

Publisher:Course Technology Ptr

Programming with Microsoft Visual Basic 2017

Computer Science

ISBN:9781337102124

Author:Diane Zak

Publisher:Cengage Learning

EBK JAVA PROGRAMMING

Computer Science

ISBN:9781305480537

Author:FARRELL

Publisher:CENGAGE LEARNING - CONSIGNMENT

EBK JAVA PROGRAMMING

Computer Science

ISBN:9781337671385

Author:FARRELL

Publisher:CENGAGE LEARNING - CONSIGNMENT

Programming Logic & Design Comprehensive

Computer Science

ISBN:9781337669405

Author:FARRELL

Publisher:Cengage

SEE MORE TEXTBOOKS

Recommended textbooks for you

C++ Programming: From Problem Analysis to Program...
Computer Science
ISBN:9781337102087
Author:D. S. Malik
Publisher:Cengage Learning
C++ for Engineers and Scientists
Computer Science
ISBN:9781133187844
Author:Bronson, Gary J.
Publisher:Course Technology Ptr
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
EBK JAVA PROGRAMMING
Computer Science
ISBN:9781305480537
Author:FARRELL
Publisher:CENGAGE LEARNING - CONSIGNMENT
EBK JAVA PROGRAMMING
Computer Science
ISBN:9781337671385
Author:FARRELL
Publisher:CENGAGE LEARNING - CONSIGNMENT
Programming Logic & Design Comprehensive
Computer Science
ISBN:9781337669405
Author:FARRELL
Publisher:Cengage