Introduction to Databases for Business Analytics
Week 4: SQL 2
Term 2 2022
Lecturer-in-Charge: Kam-Fung (Henry) : Tutors:
Copyright By PowCoder代写 加微信 powcoder
PASS Leader:
• There are some file-sharing websites that specialise in buying and selling academic work to and from university students.
• If you upload your original work to these websites, and if another student downloads
and presents it as their own either wholly or partially, you might be found guilty of collusion — even years after graduation.
• These file-sharing websites may also accept purchase of course materials, such as copies of lecture slides and tutorial handouts. By law, the copyright on course materials, developed by UNSW staff in the course of their employment, belongs to UNSW. It constitutes copyright infringement, if not academic misconduct, to trade these materials.
Acknowledgement of Country
UNSW Business School acknowledges the Bidjigal (Kensington campus) and Gadigal (City campus) the traditional custodians of the lands where each campus is located.
We acknowledge all Aboriginal and Islander Elders, past and present and their communities who have shared and practiced their teachings over thousands of years including business practices.
We recognise Aboriginal and Islander people’s ongoing leadership and contributions, including to business, education and industry.
UNSW Business School. (2022, May 7). Acknowledgement of Country [online video]. Retrieved from https://vimeo.com/369229957/d995d8087f
Single Row Functions
These functions will all manipulate the data in some way. ❑Are applied to the column specified only.
❑Apply to every record individually and in the same way.
❑The following are non exhaustive lists of some functions under each category.
Case Conversion Functions
❑LOWER → returns a string with all characters converted to lowercase.
❑UPPER → returns a string with all characters converted to uppercase.
❑INITCAP → converts the first letter of every word to uppercase, and every other letter to lowercase.
Case Conversion Functions (Demo) ❑List all information about countries in COUNTRIES table.
SELECT * FROM COUNTRIES;
❑Output the country name in its UPPER CASE letters, where its country_id is ‘AU’.
SELECT UPPER(country_name) FROM COUNTRIES WHERE country_id = ‘AU’;
Character Manipulation Functions
Find the length of strings, extract certain parts of the string (e.g., first few characters only) and replace character in a string.
❑ SUBSTR → Extract a portion of the string.
❑ INSTR → Return the numeric position of a substring within the string
❑ LENGTH → Width of the character string
❑ LPAD → Left-pad a string with another string, to a certain length.
❑ RPAD → Right-pad a string with another string, to a certain length.
❑ LTRIM → Remove all occurrences of a substring from the left-hand side of the string ❑ RTRIM → Same as LTRIM, but removes from the right-hand side
❑ REPLACE → Substitute a string with another string.
Character Manipulation Functions
❑List all information about departments SELECT * FROM DEPARTMENTS;
❑List all department IDs by appending ‘10’ to the front and make it as a 5- character string, where ’10’ indicates the 10th university in Australia.
SELECT LPAD(department_id, 5, ‘100’) from DEPARTMENTS; SELECT LPAD(department_id, 5, ‘10′) from DEPARTMENTS;
Which one is correct?
❑ Rename the column
SELECT LPAD(department_id, 5, ‘100’) AS “LeftPadData” from DEPARTMENTS;
Number Functions
❑ROUND → Round numeric fields to the stated precision ❑TRUNC → Delete numeric points to the stated precision ❑MOD → Remainder of a division operator
❑ABS → Absolute value of a field
❑POWER → Statistical power
Number Functions (Demo)
❑We can use the ‘DUAL’ table to do miscellaneous calculations
❑Calculate 23
SELECT POWER(2, 3) FROM DUAL;
❑Find the remainder of 4 divided by 3 SELECT MOD(4, 3) FROM DUAL;
Date Functions
❑Dates are often tricky to deal with due to different formats (e.g., American vs Australian date format)
❑Some examples are:
❑MONTHS_BETWEEN → Months difference between two dates ❑ADD_MONTHS → Add a set number of months to a date field ❑NEXT_DAY → The next given weekday after the given date ❑SYSDATE → Today’s current date, as per your computer system
Date Functions (Demo) ❑Output the current date
SELECT SYSDATE FROM DUAL;
❑Output the date of upcoming Sunday after 20th June 2022 SELECT NEXT_DAY( DATE ‘2022-06-20’, ‘SUNDAY’) FROM DUAL;
❑Calculate how many months between 2nd June 2022 and 1st January 2020
SELECT MONTHS_BETWEEN(To_date(’02-06-2022′, ‘DD-MM-YYYY’), To_date(‘01-01-2020’, ‘DD-MM-YYYY’)) “Months” FROM DUAL;
Other useful functions
❑ NVL → Substitute a value for any NULLS
❑ NVL2 → Substitute a value for any NULLS, and a value for any fields that are not null ❑ NULLIF → Return true if the value is null
❑ TO_CHAR → Use to format values (e.g., convert dates and numbers)
❑ TO_NUMBER → Convert a string to a number field (useful for arithmetic calculations) ❑ SOUNDEX → Use to find values based on their “sound”
❑ CASE → Do different functions depending upon various conditions
Other useful functions (Demo) ❑ List all information about drinks
SELECT * FROM DRINKS; — a table you have created in Week 3 Lab
❑ Compare the price of all drinks using a preset value, say 8 dollars SELECT DRINK_NAME, PRICE,
WHEN PRICE > 8 THEN ‘The price is greater than 8’ WHEN PRICE = 8 THEN ‘The price is 8’
ELSE ‘The price is under 8’
END “PriceCheck” FROM DRINKS;
Useful Online Resources for SQL
Practice Exercises and Training Videos
Online Web Tutorials Tutorials Point
https://www.tutorialspoint.com/sql/
w3schools.com
https://www.w3schools.com/sql/
Sign up is free
https://www.datacamp.com/
Highly Recommended:
Intro to SQL for Data Science
Training Videos
LinkedIn Learning – Free access as a UNSW
student: https://www.linkedin.com/learning/
Highly Recommended:
Introduction to Oracle SQL Developer
Source: keepmeme.com
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com