Practice Exam 3
代写quiz | 代做sql – 该题目是一个常规的sql的练习题目代写, 涵盖了sql等程序代做方面, 该题目是值得借鉴的quiz代写的题目
Due No due date Points 100 Questions 20 Time Limit None
Allowed Attempts Unlimited
Submitted Mar 30 at 4:40pm
Take the quiz Again
Please refer to the schema (click to open in new tab) for all questions on this
exam.
(file:///courses/225464/files/27274747/download?download_frd=1)
UnansweredUnanswered Question 1 0 / 5 pts
Which of the following would be the best table definition for Interests?
CREATE TABLE Interests
(Email VARCHAR(20),
Interest VARCHAR(20),
UNIQUE (Email, Interest),
FOREIGN KEY (Email) REFERENCES RegularUser (Email));
Correct AnswerCorrect Answer
CREATE TABLE Interests
(Email VARCHAR(20) NOT NULL UNIQUE,
Interest VARCHAR(20) NOT NULL UNIQUE,
FOREIGN KEY (Email) REFERENCES RegularUser (Email));
UnansweredUnanswered Question 2 0 / 5 pts
What is the result of the following statement?
INSERT INTO RegularUser
VALUES ('[email protected]', '1987-11-28', 'Portland', 'Austin', 29000);
Correct AnswerCorrect Answer (^) The row is inserted (^) The row is not inserted due to a primary key constraint violation (^) The row is not inserted due to a foreign key constraint violation (^) The row is not inserted due to a unique key constraint violation
Question 3^0 /^5 pts
Answer 1:
UnansweredUnanswered
[ Select ] is violated when this statement is executed.
INSERT INTO Interests (Email)
(SELECT Email
FROM Interests
EXCEPT ALL
SELECT Email
FROM RegularUser);
You AnsweredYou Answered (You left this blank)
Correct AnswerCorrect Answer Unique key constraint
UnansweredUnanswered Question 4 0 / 5 pts
Which sql query is equivalent to
SELECT DISTINCT CurrentCity, Interest
FROM (RegularUser NATURAL JOIN Interests);
SELECT DISTINCT r.CurrentCity, i.Interest
FROM RegularUser r, Interests i
WHERE r.Email = i.Email;
Correct AnswerCorrect Answer
SELECT CurrentCity, InterestFROM (RegularUser NATURAL JOIN Interests);^
SELECT DISTINCT r.CurrentCity, i.Interest
FROM RegularUser r, Interests i
WHERE r.Email = i.Email;
and
SELECT r.CurrentCity, i.Interest
FROM RegularUser r, Interests i;
SELECT r.CurrentCity, i.InterestFROM RegularUser r, Interests i;^
UnansweredUnanswered Question 5 0 / 5 pts
Which SQL query produces this result?
BirthDate CurrentCity
1986-11-02 Newark
1987-11-28 Portland
SELECT BirthDate, CurrentCity
FROM RegularUser
WHERE (HomeTown = 'San Francisco' OR HomeTown = 'Austin')
AND Salary > 27000;
Correct AnswerCorrect Answer
SELECT BirthDate, CurrentCity
FROM RegularUser
WHERE Email = '[email protected]';
SELECT DISTINCT BirthDate, CurrentCity
FROM RegularUser
WHERE Email IN (SELECT Email FROM Interests WHERE Interest = 'Music');
SELECT DISTINCT BirthDate, CurrentCity FROM RegularUser^
UnansweredUnanswered Question 6 0 / 5 pts
How many rows are returned by this query?
SELECT r.Email, i.Email, i.Interest
FROM RegularUser r, Interests i;
You AnsweredYou Answered
Correct AnswersCorrect Answers 300 (with margin: 0 )
UnansweredUnanswered Question 7 0 / 5 pts
How many rows are returned by this query?
SELECT Email
FROM RegularUser
INTERSECT
SELECT Email
FROM Interests;
You AnsweredYou Answered
Correct AnswersCorrect Answers 5 (with margin: 0 )
UnansweredUnanswered Question 8 0 / 5 pts
How many rows are returned by this query?
SELECT Email
FROM Interests
EXCEPT ALL
SELECT Email
FROM RegularUser;
You AnsweredYou Answered
Correct AnswersCorrect Answers 10 (with margin: 0 )
UnansweredUnanswered Question 9 0 / 5 pts
Which SQL query is equivalent to
SELECT DISTINCT HomeTown
FROM RegularUser
WHERE Email IN (SELECT Email FROM Interests WHERE Interest = Music);
SELECT DISTINCT HomeTown
FROM Interests, RegularUser
WHERE Interest = 'Music';
SELECT HomeTown
FROM Interests, RegularUser
WHERE Interest = 'Music';
SELECT DISTINCT HomeTown
FROM (Interests NATURAL JOIN RegularUser)
WHERE Interest = 'Music';
Correct AnswerCorrect Answer
UnansweredUnanswered Question 10 0 / 5 pts
SELECT MIN(Salary)
FROM RegularUser;
returns the same result as
SELECT DISTINCT Salary
FROM RegularUser
WHERE Salary <= ALL (SELECT Salary FROM RegularUser);
Correct AnswerCorrect Answer (^) True (^) False
UnansweredUnanswered Question 11 0 / 5 pts
Which of these statements are true about this query? (Check off any that apply.)
SELECT Count(*) AS Scount
FROM RegularUser
WHERE CurrentCity = 'San Francisco' AND Salary < 15000;
Correct AnswerCorrect Answer (^) It returns a single row It returns the number of users who currently live in the city of San Francisco and whose salary is less than 15, Correct AnswerCorrect Answer Correct AnswerCorrect Answer (^) It returns a single column called Scount
UnansweredUnanswered Question 12 0 / 5 pts
For the current state of the two tables,
SELECT *
FROM (Interests i LEFT OUTER JOIN RegularUser r ON i.Email = r.Email);
returns more rows than
SELECT *
FROM (Interests i JOIN RegularUser r ON i.Email = r.Email);
(^) True Correct AnswerCorrect Answer (^) False
UnansweredUnanswered Question 13 0 / 5 pts
For the current state of the two tables,
SELECT *
FROM (RegularUser r LEFT OUTER JOIN Interests i ON i.Email = r.Email);
returns more rows than
SELECT *
FROM (RegularUser r JOIN Interests i ON i.Email = r.Email) ;
Correct AnswerCorrect Answer (^) True (^) False
UnansweredUnanswered Question 14 0 / 5 pts
What does this query return?
SELECT HomeTown, COUNT(*) FROM RegularUser
GROUP BY HomeTown HAVING COUNT(*) > 5;
Atlanta
San Francisco
(^) None of these Austin 1 Austin 1 Austin 1 Austin 1 Austin 1 Austin 1 Correct AnswerCorrect Answer Austin 6
UnansweredUnanswered Question 15 0 / 5 pts
What value is returned by this SQL query?
SELECT SALARY * 3 FROM (RegularUser NATURAL JOIN Interests)
WHERE HomeTown LIKE '%San%' AND Interest LIKE '%g%';
You AnsweredYou Answered
Correct AnswersCorrect Answers 84,000 (with margin: 0 )
UnansweredUnanswered Question 16 0 / 5 pts
Which relationship must always hold true between the following two SQL
statements on every valid state of the RegularUser and Interests tables?
SELECT DISTINCT Email FROM Interests;
will not return more rows than
SELECT Email FROM RegularUser;
Correct AnswerCorrect Answer
SELECT Email FROM RegularUser;
will return more rows than
SELECT DISTINCT Email FROM Interests;
SELECT Email FROM RegularUser;
will return the same rows as
SELECT DISTINCT Email FROM Interests;
SELECT DISTINCT Email FROM Interests;
will return more rows than
SELECT Email FROM RegularUser;
UnansweredUnanswered Question 17 0 / 5 pts
Which of the statements must always be true with regard to the following SQL
statement on every valid state of the RegularUser and Interests tables?
SELECT Email FROM RegularUser
UNION
SELECT Email FROM Interests;
The number of rows returned is equal to the number of unique Emails in the Interests
table.
(^) The number of rows returned is equal to the number of rows in the Interests table. The number of rows returned is equal to the number of Emails in the RegularUser table. Correct AnswerCorrect Answer (^) None of these
Question 18^0 /^5 pts
Answer 1:
Answer 2:
UnansweredUnanswered
Provide the result for this query.
SELECT MIN(r.Salary), MAX(r.Salary)
FROM RegularUser r
WHERE r.HomeTown = Austin AND
EXISTS (SELECT *
FROM Interests
WHERE Email = r.Email);
[ Select ] [ Select ]
You AnsweredYou Answered (You left this blank)
Correct AnswerCorrect Answer 11000
You AnsweredYou Answered (You left this blank)
Correct AnswerCorrect Answer 26000
UnansweredUnanswered Question 19 0 / 5 pts
What is the result of this query?
SELECT HomeTown, Count(*) AS NumUsers
FROM RegularUser
GROUP BY HomeTown
ORDER BY HomeTown;
HomeTown NumUsers
Atlanta 5
Austin 6
San Francisco 5
HomeTown NumUsers
Austin 6
Atlanta 5
San Francisco 5
Portland 3
Dallas 1
HomeTown NumUsers
San Francisco 5
Portland 3
Dallas 1
Austin 6
Atlanta 5
HomeTown NumUsers
Atlanta 5
Austin 6
Dallas 1
Portland 3
San Francisco 5
Correct AnswerCorrect Answer
UnansweredUnanswered Question 20 0 / 5 pts
Suppose we create the following view for our relational schema:
CREATE VIEW HighSalaryHomeTowns (HomeTown)
AS SELECT DISTINCT HomeTown
FROM RegularUser
WHERE Salary > 25000;
How many rows would result from this query?
SELECT r.Email
FROM RegularUser AS r, HighSalaryHomeTowns AS h
WHERE r.CurrentCity = h.HomeTown;
You AnsweredYou Answered
Correct AnswersCorrect Answers 4 (with margin: 0 )