Find the ID品, Name, city and price of Airbnb places only for private rooms where the
price < $100. Do not show the null values for space First attempt
Copyright By PowCoder代写 加微信 powcoder
Error!!!
Type casting operator
CREATE FUNCTION conv_price(text) RETURNS numeric
AS ‘select ltrim(regexp_replace($1,”,”,””), ”$”)::numeric;’ LANGUAGE SQL
RETURNS NULL ON NULL INPUT;
Don’t want to write this every time!!!
ěselect distinct conv_price(weekly_price) from “Airbnb_listings”
select count(distinct name) from “Airbnb_listings”
select distinct name from “Airbnb_listings” where exists (
select A. id
from “Airbnb_listings” A, “Airbnb_reviews” Ar where A.id = Ar.listing_id
861 tuples !!!!
861 tuples !!!!
select distinct name f from “Airbnb_listings” X
where exists (
select Ar.listing_id
from “Airbnb_reviews” Ar where X.id = Ar.listing_id
select distinct name from “Airbnb_listings” X where exists (
select Ar.listing_id
from “Airbnb_reviews” Ar where X.id = Ar.listing_id
from “Airbnb_listings” A where not exists(
from “Airbnb_reviews” R where A.id = R.listing_id );
Which is correct and why?
select A.*
from “Airbnb_listings” A, “Airbnb_reviews” R where R.listing_id <> A.id;
SELECT first_name, last_name, salary
FROM ag_class_employees
salary = ANY (
SELECT AVG(salary)
FROM ag_class_employees
GROUP BY department_id)
ORDER BY first_name, last_name, salary;
Creates a relation with a “per department” average salary
first_name, last_name, salary
FROM ag_class_employees
salary > ALL (SELECT
salary FROM
ag_class_employees WHERE
department_id =12) ORDER BY salary;
• . matches any character
• \s matches “empty space” characters like space and tab
• \S is the opposite of \s so it matches anything that isn’t a space
• \d matches any number character and \D does the opposite
• \w matches any “word” character (a-z, 0-9) and \W does the opposite
• ^ binds the pattern to the start of the input
• $ binds the pattern to the end of the input
• () mark out portions of the pattern as “matches” to be made available for further
processing
• * matches 0-N repetitions of the character preceding it
• + matches 1-N repetitions of the character preceding it
• {N} matches N repetitions of the character preceding it
• [ ] A bracket expression. Matches a single character that is contained within the
• [abc] matches “a”, “b”, or “c”, and [a-z] specifies a range which matches any lowercase
letter from “a” to “z”.
• [^ ] Matches a single character that is not contained within the brackets
• ^A+ would match strings that start with one-or-more ‘A’ characters
• \d+ would match any combination of one-or-more digits in order
• ^\S would match any string that did not start with white space
SELECT regexp_match(‘(416) 555-1212’, ‘^\D*(\d{3})\D*(\d{3})\D*(\d{4})\D*$’);
{416,555,1212} Mind 叭叭 啊 时 㟹 ˋ
WITH regex AS (
SELECT regexp_match(‘(416) 555-1212’,
‘^\D*(\d{3})\D*(\d{3})\D*(\d{4})\D*$’) AS match
SELECT match[1] AS area_code,
match[2] AS exchange,
match[3] AS local FROM regex;
ˇSELECT (regexp_match(‘foobarbequebaz’, ‘bar.*que’))[1]; ? 红了
fo bat miquel barbeque
SELECT regexp_matches(‘foobarbequebazilbarfbonk’, ‘(b[^b]+)(b[^b]+)’, ‘g’);
SELECT regexp_matches(‘foobarbequebazilbarfbonk’, ‘(b[^b]+)(b[^b]+)’);
select unnest(regexp_match(street,'([0-9]{5})’)) from “Airbnb_listings”
select regexp_match(street,'([0-9]{5})’) from “Airbnb_listings”
select unnest(regexp_match(street,'([0-9]{5})’)) as zip, count(id) as count_by_zip
from “Airbnb_listings”
group by unnest(regexp_match(street,'([0-9]{5})’)) order by count_by_zip desc
select trim(regexp_split_to_table(neighborhood, E'[,]’)) as place
from “Airbnb_listings”
select neighborhood from “Airbnb_listings”;
select trim(regexp_split_to_table(neighborhood, E'[,]’)) as place, count(id) as cnt
from “Airbnb_listings”
group by trim(regexp_split_to_table(neighborhood, E'[,]’))
order by cnt desc
select distinct amenities[1] from “Airbnb_listings” order by amenities[1];
select distinct amenities[1:3] from “Airbnb_listings”
order by amenities[1:3];
select id, amenities
from “Airbnb_listings”
where array_length(amenities,1) is null
select id, amenities
from “Airbnb_listings”
where ‘Carbon Monoxide Detector’ = any(amenities);
{TV,Cable TV,Internet,Wireless Internet,Air Conditioning,Kitchen,Free Parking on Premises,Heating,Washer,Dryer,Smoke Detector,Carbon Monoxide Detector,Fire Extinguisher,Essentials,Shampoo}
with temp as (select distinct id, unnest(amenities) as am from “Airbnb_listings”
order by id)
select A.id, name, amenities from “Airbnb_listings” A, temp T where T.am ilike ‘%pet%’ and
A.id = T.id
select distinct id, unnest(amenities) as am from “Airbnb_listings”
order by id
{TV,Cable TV,Internet,Wireless Internet,Air Conditioning,Pool,Kitchen,Doorman,Gym,Pets live on this property,Dog(s),Cat(s),Other pet(s),Elevator in Building,Buzzer/Wireless Intercom,Heating,Family/Kid Friendly,Washer,Dryer,Smoke Detector,Essentials,Shampoo}
select id, amenities Array constructor
from “Airbnb_listings”
iwhere amenities @> ARRAY[‘Cable TV’, ‘Carbon Monoxide Detector’, ‘Doorman’];
The @> operation i o
eliminates duplicates ARRAY[1,4,3] @> ARRAY[3,1,3] evaluates to true
with empstait as (
select department_id, count(*) as emp_count from ag_class_employees
group by department_id
order by department_id
), depstat as (
select E.department_id, count(E.employee_id) as dep_count
from ag_class_employees E, ag_class_dependents P
where E.employee_id = P.employee_id 善 group by E.department_id
order by E.department_id
select empstat.department_id
from empstat, depstat
where empstat.department_id = depstat.department_id and
empstat.emp_count = depstat.dep_count;
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com