MGTF495 SQL Midterm Exam
For questions 1-10, provide short answers (between 2 and 4 lines)
For Q11 – Q14, correct the query given the user’s intent
For Q15 – Q19 write SQL queries corresponding to the questions asked. Use the same tables as used in class
Copyright By PowCoder代写 加微信 powcoder
What is a data model?
What is semistructured data?
What is the difference between a database and a database management system?
What is a primary key in a relation?
What is a subquery?
What is a database schema?
How many results will the query “select 1 from Person” return? Why?
Why should one avoid Cartesian Products?
What is an SQL Window function?
What is the difference between natural join and equijoin?
The intention is to find the number of articles published per year by each news source
select src, year(publishdate) as year, count(*)
from usnewspaper
where publishdate > ‘01-01-2020’ and publishdate < ‘12-31-2021’
group by src, year
order by src,year;
The intention is to find departments to which no employee is assigned. Your corrected query should still have the NOT IN structure.
SELECT C.department_name
FROM ag_class_departments C
WHERE C.department_id NOT IN (select department_id
from ag_class_employees E)
The intention is to get every employee in the result irrespective of the other query conditions.
select E.employee_id
from ag_class_employees E left join (
ag_class_departments D left join ag_class_locations L
on D.location_id = L.location_id)
on E.department_id = D.department_id
L.postal_code like ‘%9%’
The intention is to find listings with only daily price and no security deposit. We want to use EXISTS
select id, exists(select id
from “Airbnb_listings” B
where A.security_deposit is null and
A.weekly_price is null and
A.monthly_price is null
from “Airbnb_listings” A
order by id
15-19 根据指令写SQL代码 （课上用的是postgresql,如果要连数据库找我要账号密码。）
Which Airbnb listings in the neighborhood of Fort Totten does not have cable TV? List their amenities.
Which Airbnb listings don’t have a street address beginning with a digit (e.g, 12th street) or a single letter (e.g., F street)? What’s the average number of bedrooms for these listings?
Find the count of news articles published after January 1, 2020 that mention “football” or “basketball” by the month and the year. Use the news column.
How many Foxnews articles (src has the substring fox) that have “Trump” in the title has “Trump” in the body (news column) more than 10 times?
Find reviews of Airbnb places where the reviewer name uses only non-English characters (punctuation marks should be ignored)
程序代写 CS代考 加微信: powcoder QQ: 1823890830 Email: powcoder@163.com