4.2.1 Lecture - Start

sql

School

University of Kentucky *

*We aren’t endorsed by this school

Course

324

Subject

Computer Science

Date

Feb 20, 2024

Type

sql

Pages

Uploaded by ChefStar3152

/* Week 4 - Operators, FUNCTIONS and CASE */ SET sql_mode = 'ANSI'; #Connect to a schema USE sakila; /*--------------CONCEPT 1: OPERATORS--------------------------*/ #Arithmetic Operators /* calculate sales tax on the payment amounts in the payment table, assuming 6% */ SELECT payment_id, amount as subtotal, #calculate tax #calculate total FROM payment ; #Comparison operators.. we did that last time. Any questions? # not equals SELECT film_id, title, `description`, rating FROM film WHERE rating != 'R' ; #Logical operators + Comparison Operators # What if we want to exclude film rate R and films rate NC-17? SELECT film_id, title, description, rating FROM film WHERE rating != 'R' #what would you add? ; # Can you think of two logical operators we could use instead? SELECT film_id, title, `description`, rating FROM film WHERE #use two logical operators... ; #find all the customers with first name KELLY, TRACY or MARY SELECT first_name, last_name FROM customer WHERE first_name IN ('KELLY','TRACY','MARY') ; # find all films that might be about armageddon based on the title SELECT film_id, title, `description`, rating, rental_rate FROM film WHERE title LIKE '%ARMAGEDDON%' ; #What impact is there if we change the % wildcard position? #find the films with replacement cost between $15 and $20 SELECT film_id, title, replacement_cost FROM film WHERE replacement_cost BETWEEN 15 AND 20

; #bonus: write the statement using comparison operators instead #be sure you get the same result SELECT film_id, title, replacement_cost FROM film #what statement will guarantee you get the same result using comparison operators/ ; /*--------------CHALLENGE 1: START --------------------------*/ USE northwinds; -- will apply for both challenge questions # Find a list of products that would likely satisfy a chocolate craving SELECT * FROM products #add a filter...; #Pull a list of customers (ID and company name) in North America (Canada, USA or Mexico). #Can you come up with two ways to accomplish this using different logical operators? SELECT CustomerID, CompanyName, Country FROM customers #how will you filter your list? ; /*--------------CHALLENGE 1: END --------------------------*/ /*--------------CONCEPT 2: STRING FUNCTIONS --------------------------*/ USE sakila; # Functions accept an input parameter, perform an action or calculation and return a single result /* Use a string function to make the amounts display with 2 decimal places */ SELECT payment_id, amount AS subtotal, amount * 0.06 AS tax, amount * 1.06 AS total FROM payment ; #what function could we use to add a $ sign in front of the amount? #COALESE pulls the first non-null value from a list #Example use cases... home v. cell phone; work v. personal email; name v. preferred name fields SELECT rental_id, rental_date, return_date, COALESCE(return_date, rental_date) AS most_recent_interaction FROM rental ORDER BY return_date; #Substrings - let's parse out the department number from the department code in the employees table. SELECT dept_no, SUBSTR(dept_no,4,1) AS dept_digit

FROM employees.departments; /*--------------CONCEPT 2: AGGREGATE MATHEMATICAL FUNCTIONS, GROUP BY and HAVING --------------------------*/ /* NOTE: COUNT, MIN, MAX are relevant for text or numeric fields. AVG, STD, SUM are only relevant for numeric fields.*/ # text field SELECT COUNT(first_name), MIN(first_name), MAX(first_name), AVG(first_name), SUM(first_name), STD(first_name) FROM sakila.customer; # numeric field SELECT COUNT(address_id), MIN(address_id), MAX(address_id), AVG(address_id), SUM(address_id), STD(address_id) FROM sakila.customer; #find the average payment amount overall payments SELECT AVG(amount) FROM payment ; #modify the query to also show the total amount across all payments. #find the average payment amount for each customer... need to add customer_id and include a group by #... then add a count and a sum... # COUNT tells us how many payments each customer made # SUM tells us how much (in total) each customer paid SELECT customer_id, AVG(amount) FROM payment GROUP BY customer_id; #filter for customers who spend more than $5 on average with a specific staff member. #because we need a filter on an aggregate, we need to use HAVING SELECT customer_id, staff_id, AVG(amount) FROM payment GROUP BY customer_id, staff_id HAVING AVG(amount) > 5.00 ORDER BY customer_id, staff_id; /*--------------CHALLENGE 2: MATHEMATICAL FUNCTIONS--------------------------*/ #find the total number of payments SELECT /*what function should you use to determine the number of payments? */ FROM payment ; #find the total number of payments collected per staff member SELECT /*what column do you need to add to the select statement? */ FROM payment /*what clause do you need to aggregate per staff member?*/ ; #filter for only staff who have collected at least 8000 payments. SELECT /*what column do you need to add to the select statement? */

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

FROM payment /*what clause do you need to aggregate per staff member? what filter?*/ ; /*--------------CHALLENGE 2: END --------------------------*/ #More about COUNT #What is the difference between each of these count results? SELECT count(*), count(rental_id), count(customer_id), count(distinct customer_id), count(return_date) FROM rental; /*--------------CONCEPT 3: DATE FUNCTIONS --------------------------*/ #determine the number of rentals each day. SELECT DATE(rental_date), COUNT(rental_id) FROM rental GROUP BY DATE(rental_date); #sort the list above by the total rentals (high to low) SELECT DATE(rental_date), COUNT(rental_id) FROM rental GROUP BY DATE(rental_date) ORDER BY COUNT(rental_id) desc; #determine the total payments collected by day of week. #note that the group by required both fields that weren't part of the SUM! SELECT DAYOFWEEK(payment_date), DAYNAME(payment_date),SUM(amount) FROM payment GROUP BY DAYOFWEEK(payment_date), DAYNAME(payment_date); #sort the list by day of week SELECT DAYOFWEEK(payment_date), DAYNAME(payment_date),SUM(amount) FROM payment GROUP BY DAYOFWEEK(payment_date), DAYNAME(payment_date) ORDER BY DAYOFWEEK(payment_date); /*-------------- CHALLENGE 3: DATE FUNCTIONS --------------------------*/ USE sakila; #modify the query to show the total amount paid by month and year /*if you don't know the right function, use the https://dev.mysql.com/doc/refman/8.0/en/func-op-summary-ref.html to search for options*/ SELECT /*what functions and fields here? */ FROM payment GROUP BY /*what functions and fields here? */; /*-------------- CHALLENGE 3: END--------------------------*/ /*--------------CONCEPT 4: CASE STATEMENT --------------------------*/ /*WHEN TO USE THEN: to reclassify a record to create a conditional calculation any time you think "if this then that" */ /*HOW THE LOGIC WORKS: the first condition is checked if true, the "THEN" statement is applied and no

other conditions are checked if the first condition is false, the next condition is checked. If true, the "THEN" statement is applied. If not, the next condition is checked. This continues until the last WHEN statement returns a false. If an ELSE condition exists, that statement is returned. If there is no ELSE condition, then NULL is returned*/ /* classify the films by audience based on the ratings value. Note that the column, ratings, comes after the CASE keyword */ SELECT film_id, title, rating, CASE rating WHEN 'G' THEN 'Kids' WHEN 'PG' THEN 'Kids' ELSE 'Adult' END AS `audience` FROM film; /* Classify the film length. Note that we’re doing a comparison on the length field, not just checking the value of the length field. No column is listed after CASE. Instead, the expression comes after the WHEN keyword */ SELECT film_id, title, length, CASE WHEN length < 90 THEN 'Short' WHEN length BETWEEN 90 AND 120 THEN 'Average' ELSE 'Long' END AS `film length` FROM film; # Classify customers based on the number of rentals they've had: # Movie Buffs > 40 rentals # Friday Night Flicks 30 - 40 rentals # Occasional Viewer 20 - 30 rentals # Infrequent Viewer < 20 rentals SELECT customer_id, COUNT(rental_id), CASE #what logic should we apply? END AS `Customer Segment` FROM rental GROUP BY customer_id ; # For customers with 30 rentals, which category are they falling in? add an order by to investigate

/*classify each rental as 'returned' or checked-out' based whether the return date is NULL*/ /*special case for NULLs. Why can't we use = or !=*/ /*because NULL is not a value and therefore can't be equal to anything (per SQL standards)*/ SELECT *, CASE WHEN return_date IS NULL THEN 'checked-out' WHEN return_date IS NOT NULL THEN 'returned' ELSE 'unknown' END AS `rental status` FROM rental ; /*---------------Challenge 4: CASE + other functions we've learned--------------------*/ #4.1 /*classify rentals that were checked out and returned on the same day as 'Fast'*/ /*classify rentals that were checked out for more than a week (>7 days) as 'Slow'*/ /*classify all other rentals as 'Typical'*/ #4.2 /*BONUS: What if we decide to charge extra for films with special features: -- deleted scenes $0.50 -- commentaries $0.75 -- behind the scenes $1.00 calculate the new rental rate */ /*hint: start by running this query to remind yourself what the values are like in special_features*/ SELECT DISTINCT special_features from film; /*hint: you'll want to use arithmetic operators, logical operators and CASE*/ #4.3 /*what happened to films with more than one bonus feature? check film_id 4 */ /*how would you modify the logic to ensure the highest price gets applied*/ #4.4 /*what if we wanted to apply a charge for EACH special_feature in the film? we'd have to write when statements to capture each possible case. for example*/ /*---------------Challenge 4: END--------------------*/

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

INFT 1207.Lab1.Advanced Python.pdf

StormNalinHW13.pdf

WK6.docx

Ethical use of AI.docx

assignemnt2.docx

ch 19 - lifespan.docx

3.2.1 Lecture SQL - Start.sql

CYB 210 Network Configuration Scavenger Hunt Template.docx

lab_2_worksheet.docx

lab_2_worksheet.doc

lab_13_worksheet.docx

Lab_6_worksheet.docx

Recommended textbooks for you

Programming with Microsoft Visual Basic 2017

Computer Science

ISBN:9781337102124

Author:Diane Zak

Publisher:Cengage Learning

A Guide to SQL

Computer Science

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Course Technology Ptr

Np Ms Office 365/Excel 2016 I Ntermed

Computer Science

ISBN:9781337508841

Author:Carey

Publisher:Cengage

COMPREHENSIVE MICROSOFT OFFICE 365 EXCE

Computer Science

ISBN:9780357392676

Author:FREUND, Steven

Publisher:CENGAGE L

Fundamentals of Information Systems

Computer Science

ISBN:9781305082168

Author:Ralph Stair, George Reynolds

Publisher:Cengage Learning

Enhanced Discovering Computers 2017 (Shelly Cashm...

Computer Science

ISBN:9781305657458

Author:Misty E. Vermaat, Susan L. Sebok, Steven M. Freund, Mark Frydenberg, Jennifer T. Campbell

Publisher:Cengage Learning

SEE MORE TEXTBOOKS

Recommended textbooks for you

Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
COMPREHENSIVE MICROSOFT OFFICE 365 EXCE
Computer Science
ISBN:9780357392676
Author:FREUND, Steven
Publisher:CENGAGE L
Fundamentals of Information Systems
Computer Science
ISBN:9781305082168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Enhanced Discovering Computers 2017 (Shelly Cashm...
Computer Science
ISBN:9781305657458
Author:Misty E. Vermaat, Susan L. Sebok, Steven M. Freund, Mark Frydenberg, Jennifer T. Campbell
Publisher:Cengage Learning