3.2.1 Lecture SQL - 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

4

Uploaded by ChefStar3152

Report
/* Queries for Week 3 by Whitney Conner last updated 1.25.22 */ # Why do we use ANSI? So that our code is more compatible with # other SQL vendor implementations SET sql_mode = 'ANSI'; #Connect to a schema USE sakila; /*---------- # 1 SELECT ---------------------------*/ # The basics # First, select all rows using the asterick (*) SELECT * FROM category; # Select only the unique store ids from the store table # This is how I figure out how to filter for a value... what values might exist. # Note those lines were written on one line... highlight them and use the format painter. # What happens? /*---------- # 2 WHERE ---------------------------*/ -- Pull a list of films rated PG 13. Return only the film id, title, rating, rental_rate and length SELECT film_id, title, rating, rental_rate, length FROM film WHERE rating = 'PG-13' ; /* change the filter to pull only films that have a length over 90 minutes */ SELECT film_id, title, rating, rental_rate, length FROM film #### ADD HERE #### ; /* what's the data type for length? text values need single quotes around them. numbers do not */ /*if you're not sure, try select distinct length from film; to see what the possible values are */ /*---------- # 3 GROUP BY ---------------------------*/ # When you GROUP BY, every field in the SELECT statement must either be aggregated (e.g., SUM, COUNT) # Or, appear in the GROUP BY clause. # Why? Because group by groups... so if you add something to the SELECT that's not in the groups, # SQL doesn't know what to do with it (except count or sum it) # how many records are returned? why? SELECT release_year FROM film ; # how many records are returned? why? SELECT DISTINCT release_year
FROM film ; # how many records are returned? why? SELECT release_year FROM film GROUP BY release_year ; #debugging 101 # select the unique combination of store_id and whether a customer is active # what's wrong with this statement? -- SELECT `active`, store_id -- FROM customer -- GROUP BY `active`; #pull the combinations of rental rates and replacement costs in the film table, only for those films that are rated PG-13 SELECT rental_rate, replacement_cost FROM film #### ADD HERE #### #### ADD HERE #### ; /*---------- # 4 HAVING ---------------------------*/ # this is an example, but we'll cover this more next week # when we talk about functions... # recall that HAVING requires a GROUP BY to be with it #Pull a list of films that have over 10 actors listed in the film_actor table /* SELECT film_id, count(*) FROM film_actor GROUP BY film_id HAVING count(*) > 10 ; */ #Your turn: # Pull a list of city ids and district that have more than 1 associated address, based on the address table # Hint - use count(*) to find the # of associated addresses. We'll learn more about # aggregate functions like count next week ...... # another way to think of it is that count by city/district is not part of the address table (i.e., it's not a row level detail)*/ ### ADD HERE ### SELECT city_id, district, count(*) FROM address GROUP BY city_id, district HAVING COUNT(*) > 1; /* why can't the filter be applied using a where clause? because the filter is not based on an individual addresses, it's the addresses in a group of districts and postal_codes... /* more later...*/ /*---------- # 5 ORDER BY ---------------------------*/ # pull the unique combinations of ratings and rental rates from the film table and sort by rating then rental_rate SELECT rating, rental_rate FROM film
GROUP BY rating, rental_rate ORDER BY rating, rental_rate ; # reverse the sort order for rating (Z to A) SELECT rating, rental_rate FROM film GROUP BY rating, rental_rate ### ADD HERE ### ; /*---------- # 6 LIMIT ---------------------------*/ # a good way to preview data in a table! # only pull the first 4 records from the last query... # Where do you add it? /*---------- # 7 Rename some columns with the alias (AS) ---------------------------*/ # Note that column names (using AS) should go in backticks (show up in red) if they have a space # in them (e.g., `Movie Rating`) # While text strings go in single quotes and show up in orange (e.g., 'G' or 'PG') # This query renames both the rating and rental_rate columns SELECT rating AS `Movie Rating`, rental_rate AS Price FROM film WHERE rating = 'G' GROUP BY rating, rental_rate ORDER BY rating desc, rental_rate ; # SELECT title and length in this query. Rename the length field to run_time. SELECT #### ADD HERE #### FROM film WHERE rating = 'G' ORDER BY length desc ; /*---------- # 8 Debugging code continued ---------------------------*/ # What's wrong with this query? SELECT customer_id, first_name, last_name FROM customer WHERE firstname = 'MARY' ; # What's wrong with this query? SELECT film_id, title, `description`, rental_rate FROM film WHERE rental_rate >'$0.99' ; # What's wrong with this query? SELECT film_id, title length FROM film ; # ALSO WATCH FOR LIMITING RESULTS... it's good practice while testing, # but could mess up your results if you need to pull back everything.
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
/*---------- YOUR TURN ---------------------------*/ /* Try to answer these questions using the employees schema */ USE employees; /* 1. How many unique job titles exist in the company? */ SELECT DISTINCT title FROM titles; # 7 /* 2. Write a query to show when the first female employee was hired. Include first name, last name and hire date in your query results */ SELECT first_name, last_name, hire_date FROM employees WHERE gender = 'F' ORDER BY hire_date LIMIT 1; # shirish ossenbruggen /* 3. Pull the list of salaries over $150,000. Include employee number, salary and to_date in the result */ /* Sort the results by to_date (high to low) and then by salary (high to low) */ SELECT emp_no, salary, to_date FROM salaries WHERE salary > 150000 ORDER BY to_date DESC, salary DESC; # 36 returned /* 4. Bonus - Write a query to pull the list of last names shared by over 200 employees */ SELECT last_name, COUNT(*) AS Total FROM employees GROUP BY last_name HAVING COUNT(*) > 200; # 155 returned

Related Documents

StormNalinHW13.pdf
WK6.docx
Ethical use of AI.docx
assignemnt2.docx
ch 19 - lifespan.docx
4.2.1 Lecture - 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
Lab_11_worksheet.docx
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
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
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
Text book image
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
Text book image
Programming with Microsoft Visual Basic 2017
Computer Science
ISBN:9781337102124
Author:Diane Zak
Publisher:Cengage Learning
SEE MORE TEXTBOOKS