Multiple Subqueries Using IMDB
Order ID 53563633773 Type Essay Writer Level Masters Style APA Sources/References 4 Perfect Number of Pages to Order 5-10 Pages Description/Paper Instructions
Multiple Subqueries Using IMDB
will cover these queries in lecture; feel free to copy/paste and execute to get a feel for them. These will be similar to the lab questions!
USE IMDB
/* SUBQUERY usage – including multiple subqueries */
/* Titles with a rating greater than five: */
SELECT *
FROM title_ratings
WHERE averageRating > 5;
/* Shows belonging to this group: */
SELECT tconst,
primaryTitle,
startYear
FROM title_basics
WHERE tconst IN
(SELECT tconst
FROM title_ratings
WHERE averageRating > 5);
/* Shows which are musicals or romances: */
SELECT *
FROM title_genre
WHERE genre IN (‘Musical’, ‘Romance’);
/* Data for shows belonging to the previous group but not this one: */
SELECT tconst,
primaryTitle,
startYear
FROM title_basics
WHERE tconst IN
(SELECT tconst
FROM title_ratings
WHERE averageRating > 5
)
AND tconst NOT IN
(SELECT tconst
FROM title_genre
WHERE genre IN (‘Musical’, ‘Romance’)
);
/* Nested subquery version – functional, but unnecessary (and confusing): */
SELECT tconst,
primaryTitle,
startYear
FROM title_basics
WHERE tconst IN
(SELECT tconst
FROM title_ratings
WHERE averageRating > 5
AND tconst NOT IN
(SELECT tconst
FROM title_genre
WHERE genre IN (‘Musical’, ‘Romance’)
)
);
/* More logical nested subquery: names of directors who have worked
on titles with rankings greater than five: */
SELECT DISTINCT name_basics.primaryName
FROM title_directors,
name_basics
WHERE name_basics.nconst = title_directors.nconst
AND title_directors.tconst IN
(SELECT tconst
FROM title_basics
WHERE tconst IN
(SELECT tconst
FROM title_ratings
WHERE averageRating > 5)
);
/* Subquery in the FROM clause – acts as a “virtual table”.
Note: must include “AS” label, or you get an error! */
SELECT *
FROM
(SELECT primaryTitle AS MOVIENAME,
runtimeMinutes AS LENGTH
FROM title_basics
WHERE titleType = ‘movie’
AND startYear = 1984
) AS FILMSFROM1984
ORDER BY MOVIENAME;
/* Common Table Expressions (aka CTE’s) */
/* Create a “virtual table” which calculates final age (questionable results!): */
WITH my_cte AS
(SELECT nconst,
primaryName,
deathYear,
birthYear,
ISNULL(deathYear – birthYear, 0) AS finalAge
FROM name_basics)
SELECT *
FROM my_cte
ORDER BY finalAge DESC;
/* Why can’t we include an ORDER BY clause in the CTE? Note the error */
WITH my_cte AS
(SELECT nconst,
primaryName,
deathYear,
birthYear,
ISNULL(deathYear – birthYear, 0) AS finalAge
FROM name_basics
ORDER BY finalAge)
SELECT *
FROM my_cte
ORDER BY finalAge DESC;
/* Use CTE to calculate how may times each person has performed a role;
In outer query, limit results to more than one occurrence: */
WITH profession_cte AS
(SELECT NB.primaryName,
TP.category,
COUNT(*) AS TOTAL
FROM title_principals TP JOIN name_basics NB ON TP.nconst = NB.nconst
GROUP BY NB.primaryName, TP.category)
SELECT *
FROM profession_cte
WHERE TOTAL > 1
ORDER BY primaryName, category;
/* HAVING clause usage */
/* ID’s of directors who have worked on more than one film: */
SELECT TD.nconst,
COUNT(*) AS TOTALS
FROM title_directors TD JOIN title_basics TB ON TD.tconst = TB.tconst
GROUP BY TD.nconst
HAVING COUNT(*) > 1
ORDER BY TD.nconst;
/* Names of these directors, along with the names of their films: */
SELECT TD.nconst,
NB.primaryName,
TB.primaryTitle
FROM title_directors TD JOIN title_basics TB ON TD.tconst = TB.tconst
JOIN name_basics NB ON NB.nconst = TD.nconst
WHERE NB.nconst IN
(
SELECT TD.nconst
FROM title_directors TD JOIN title_basics TB ON TD.tconst = TB.tconst
GROUP BY TD.nconst
HAVING COUNT(*) > 1
)
ORDER BY TD.nconst,
NB.primaryName,
TB.primaryTitle;
/* All genres, along with the total number of titles for each: */
SELECT TG.genre,
COUNT(*)
FROM title_genre TG
GROUP BY TG.genre
ORDER BY COUNT(*);
/* Genres whose total number of titles is in a specified range: */
SELECT TG.genre,
COUNT(*)
FROM title_genre TG
GROUP BY TG.genre
HAVING COUNT(*) BETWEEN 50000 AND 100000
ORDER BY COUNT(*);
/* Titles in these genres: */
SELECT TB.primaryTitle,
TG.genre
FROM title_basics TB,
title_genre TG
WHERE TB.tconst = TG.tconst
AND TG.genre IN
(SELECT genre
FROM title_genre TG
GROUP BY TG.genre
HAVING COUNT(*) BETWEEN 50000 AND 100000
)
ORDER BY TB.primaryTitle;
/* Writers born after 1950, and their total number of titles: */
SELECT NB.primaryName,
NB.birthYear,
COUNT(*) AS TITLES
FROM name_basics NB JOIN title_writers TW ON NB.nconst = TW.nconst
WHERE NB.birthYear > 1950
GROUP BY NB.primaryName,
NB.birthYear
ORDER BY NB.primaryName,
NB.birthYear;
/* The same list, limited to totals in a given range: */
SELECT NB.primaryName,
NB.birthYear,
COUNT(*) AS TITLES
FROM name_basics NB JOIN title_writers TW ON NB.nconst = TW.nconst
WHERE NB.birthYear > 1950
GROUP BY NB.primaryName,
NB.birthYear
HAVING COUNT(*) BETWEEN 10 AND 100
ORDER BY NB.primaryName,
NB.birthYear;
/* Directors born after 1950, and their total number of titles: */
SELECT NB.primaryName,
NB.birthYear,
COUNT(*) AS TITLES
FROM name_basics NB JOIN title_directors TD ON NB.nconst = TD.nconst
WHERE NB.birthYear > 1950
GROUP BY NB.primaryName,
NB.birthYear
ORDER BY NB.primaryName,
NB.birthYear;
/* The same – but limit to directors who have written between 10 and 100 titles: */
SELECT NB.primaryName,
NB.birthYear,
COUNT(*) AS TITLES
FROM name_basics NB JOIN title_directors TD ON NB.nconst = TD.nconst
WHERE NB.birthYear > 1950
AND NB.nconst IN
(SELECT NB2.nconst
FROM name_basics NB2 JOIN title_writers TW ON NB2.nconst = TW.nconst
WHERE NB2.birthYear > 1950
GROUP BY NB2.nconst
HAVING COUNT(*) BETWEEN 10 AND 100
)
GROUP BY NB.primaryName,
NB.birthYear
ORDER BY NB.primaryName,
NB.birthYear;
/* How could we display the number of titles written? It’s calculated in the
subquery, and not available in the outer SELECT clause…
Join all three tables in the outer query, and calculate totals on
DISTINCT values: */
SELECT NB.primaryName,
NB.birthYear,
COUNT(DISTINCT TD.tconst) AS TITLESDIRECTED,
COUNT(DISTINCT TW.tconst) AS TITLESWRITTEN
FROM name_basics NB JOIN title_directors TD ON NB.nconst = TD.nconst
JOIN title_writers TW ON NB.nconst = TW.nconst
WHERE NB.birthYear > 1950
AND NB.nconst IN
(SELECT TW.nconst
FROM title_writers TW
WHERE NB.nconst = TW.nconst
GROUP BY TW.nconst
HAVING COUNT(*) BETWEEN 10 AND 100
)
GROUP BY NB.primaryName,
NB.birthYear
ORDER BY NB.primaryName,
NB.birthYear;
/* Re-write to eliminate the need for the subquery (title_writers is
already available in the FROM clause): */
SELECT NB.primaryName,
NB.birthYear,
COUNT(DISTINCT TD.tconst) AS TITLESDIRECTED,
COUNT(DISTINCT TW.tconst) AS TITLESWRITTEN
FROM name_basics NB JOIN title_directors TD ON NB.nconst = TD.nconst
JOIN title_writers TW ON NB.nconst = TW.nconst
WHERE NB.birthYear > 1950
GROUP BY NB.primaryName,
NB.birthYear
HAVING COUNT(DISTINCT TW.tconst) BETWEEN 10 AND 100
ORDER BY NB.primaryName,
NB.birthYear;
/* ISNULL and COALESCE */
/* Consider titles with no value in the “originalTitle” column: */
SELECT TB.primaryTitle,
TB.originalTitle
FROM title_basics TB
WHERE originalTitle IS NULL;
/* Replace this empty value with a default: */
SELECT TB.primaryTitle,
ISNULL(TB.originalTitle,'[NONE]’) AS ISNULLoriginal
FROM title_basics TB
WHERE originalTitle IS NULL;
/* The same effect, using COALESCE: */
SELECT TB.primaryTitle,
COALESCE(TB.originalTitle,'[NONE]’) AS COALESCEoriginal
FROM title_basics TB
WHERE originalTitle IS NULL;
/* COALESCE allows you to list multiple values; the first non-null one gets returned: */
SELECT TB.primaryTitle,
TB.originalTitle,
TB.startYear,
TB.endYear,
COALESCE(TB.originalTitle, STR(TB.startYear), STR(TB.endYear), ‘[NONE]’) AS FirstValue
FROM title_basics TB
WHERE originalTitle IS NULL;
/* Ranking functions */
/* Consider a subset of people: */
SELECT NB.primaryName,
NB.birthYear,
NB.deathYear
FROM name_basics NB
WHERE NB.birthYear = 1935
AND NB.deathYear IS NOT NULL;
/* Calculate rank based on several criteria. Note that display order does
not change their ranking: */
SELECT NB.primaryName,
NB.birthYear,
NB.deathYear,
ROW_NUMBER() OVER (ORDER BY NB.deathYear) AS DeathOrder,
ROW_NUMBER() OVER (ORDER BY NB.primaryName) AS NameOrder
FROM name_basics NB
WHERE NB.birthYear = 1935
AND NB.deathYear IS NOT NULL
ORDER BY 3;
/* The same query using RANK. Notice tie values (and gaps in the sequence): */
SELECT NB.primaryName,
NB.birthYear,
NB.deathYear,
RANK() OVER (ORDER BY NB.deathYear) AS DeathOrder,
RANK() OVER (ORDER BY NB.primaryName) AS NameOrder
FROM name_basics NB
WHERE NB.birthYear = 1935
AND NB.deathYear IS NOT NULL
ORDER BY 3;
/* DENSE_RANK gets rid of the gaps: */
SELECT NB.primaryName,
NB.birthYear,
NB.deathYear,
DENSE_RANK() OVER (ORDER BY NB.deathYear) AS DeathOrder,
DENSE_RANK() OVER (ORDER BY NB.primaryName) AS NameOrder
FROM name_basics NB
WHERE NB.birthYear = 1935
AND NB.deathYear IS NOT NULL
ORDER BY 3;
RUBRIC
QUALITY OF RESPONSE NO RESPONSE POOR / UNSATISFACTORY SATISFACTORY GOOD EXCELLENT Content (worth a maximum of 50% of the total points) Zero points: Student failed to submit the final paper. 20 points out of 50: The essay illustrates poor understanding of the relevant material by failing to address or incorrectly addressing the relevant content; failing to identify or inaccurately explaining/defining key concepts/ideas; ignoring or incorrectly explaining key points/claims and the reasoning behind them; and/or incorrectly or inappropriately using terminology; and elements of the response are lacking. 30 points out of 50: The essay illustrates a rudimentary understanding of the relevant material by mentioning but not full explaining the relevant content; identifying some of the key concepts/ideas though failing to fully or accurately explain many of them; using terminology, though sometimes inaccurately or inappropriately; and/or incorporating some key claims/points but failing to explain the reasoning behind them or doing so inaccurately. Elements of the required response may also be lacking. 40 points out of 50: The essay illustrates solid understanding of the relevant material by correctly addressing most of the relevant content; identifying and explaining most of the key concepts/ideas; using correct terminology; explaining the reasoning behind most of the key points/claims; and/or where necessary or useful, substantiating some points with accurate examples. The answer is complete. 50 points: The essay illustrates exemplary understanding of the relevant material by thoroughly and correctly addressing the relevant content; identifying and explaining all of the key concepts/ideas; using correct terminology explaining the reasoning behind key points/claims and substantiating, as necessary/useful, points with several accurate and illuminating examples. No aspects of the required answer are missing. Use of Sources (worth a maximum of 20% of the total points). Zero points: Student failed to include citations and/or references. Or the student failed to submit a final paper. 5 out 20 points: Sources are seldom cited to support statements and/or format of citations are not recognizable as APA 6th Edition format. There are major errors in the formation of the references and citations. And/or there is a major reliance on highly questionable. The Student fails to provide an adequate synthesis of research collected for the paper. 10 out 20 points: References to scholarly sources are occasionally given; many statements seem unsubstantiated. Frequent errors in APA 6th Edition format, leaving the reader confused about the source of the information. There are significant errors of the formation in the references and citations. And/or there is a significant use of highly questionable sources. 15 out 20 points: Credible Scholarly sources are used effectively support claims and are, for the most part, clear and fairly represented. APA 6th Edition is used with only a few minor errors. There are minor errors in reference and/or citations. And/or there is some use of questionable sources. 20 points: Credible scholarly sources are used to give compelling evidence to support claims and are clearly and fairly represented. APA 6th Edition format is used accurately and consistently. The student uses above the maximum required references in the development of the assignment. Grammar (worth maximum of 20% of total points) Zero points: Student failed to submit the final paper. 5 points out of 20: The paper does not communicate ideas/points clearly due to inappropriate use of terminology and vague language; thoughts and sentences are disjointed or incomprehensible; organization lacking; and/or numerous grammatical, spelling/punctuation errors 10 points out 20: The paper is often unclear and difficult to follow due to some inappropriate terminology and/or vague language; ideas may be fragmented, wandering and/or repetitive; poor organization; and/or some grammatical, spelling, punctuation errors 15 points out of 20: The paper is mostly clear as a result of appropriate use of terminology and minimal vagueness; no tangents and no repetition; fairly good organization; almost perfect grammar, spelling, punctuation, and word usage. 20 points: The paper is clear, concise, and a pleasure to read as a result of appropriate and precise use of terminology; total coherence of thoughts and presentation and logical organization; and the essay is error free. Structure of the Paper (worth 10% of total points) Zero points: Student failed to submit the final paper. 3 points out of 10: Student needs to develop better formatting skills. The paper omits significant structural elements required for and APA 6th edition paper. Formatting of the paper has major flaws. The paper does not conform to APA 6th edition requirements whatsoever. 5 points out of 10: Appearance of final paper demonstrates the student’s limited ability to format the paper. There are significant errors in formatting and/or the total omission of major components of an APA 6th edition paper. They can include the omission of the cover page, abstract, and page numbers. Additionally the page has major formatting issues with spacing or paragraph formation. Font size might not conform to size requirements. The student also significantly writes too large or too short of and paper 7 points out of 10: Research paper presents an above-average use of formatting skills. The paper has slight errors within the paper. This can include small errors or omissions with the cover page, abstract, page number, and headers. There could be also slight formatting issues with the document spacing or the font Additionally the paper might slightly exceed or undershoot the specific number of required written pages for the assignment. 10 points: Student provides a high-caliber, formatted paper. This includes an APA 6th edition cover page, abstract, page number, headers and is double spaced in 12’ Times Roman Font. Additionally, the paper conforms to the specific number of required written pages and neither goes over or under the specified length of the paper. GET THIS PROJECT NOW BY CLICKING ON THIS LINK TO PLACE THE ORDER
CLICK ON THE LINK HERE: https://www.perfectacademic.com/orders/ordernow
Also, you can place the order at www.collegepaper.us/orders/ordernow / www.phdwriters.us/orders/ordernow
Do You Have Any Other Essay/Assignment/Class Project/Homework Related to this? Click Here Now [CLICK ME]and Have It Done by Our PhD Qualified Writers!!