Week 1 Quiz
1. Select the jobs below that may use SQL in their work (select all that apply).
Answers:
- Data Analyst
- QA Engineer
- Backend Developer
- DBA
- Data Scientist
2. How does a data scientist and DBA differ in how they use SQL?
Answer:
DBAs manage the database for other users
3. Which of the following statements are true of Entity Relationship (ER) Diagrams?
Answers:
They identify the Primary Keys
They are usually a representation of a business process.
They show you the relationships between tables.
They usually are represented in a visual format.
4. Select the query below that will retrieve all columns from the customers table.
Answer:
SELECT * FROM customers
5. Select the query that will retrieve only the Customer First Name, Last Name, and Company.
Answer:
SEL ECT FirstName, LastNameE, Company FROM Customers
6. The ER diagram below is depicting what kind of relationship between the EMPLOYEES and CUSTOMERS tables?
Answer:
One-to-Many
7. The data model depicted in the ER diagram below could be described as a _______________.
Answer:
Relational Model
8. When using the "CREATE TABLE" command and creating new columns for that table, which of the following statements is true?
Answer:
You must assign a data type to each column
9. Look at the values in the two columns below. Based on the values in each column, which column could potentially be used as a primary key?
Answer:
column 1
10. In order to retrieve data from a table with SQL, every SQL statement must contain?
Answer:
SELECT
Also Check: Introduction to Data Analytics for Accounting Professionals Quiz Answers
Week 1 Coding
1. Retrieve all the records from the Employees table.
code:
select * from Employees;
What is Robert King's mailing address? Note: You will have to scroll to the right in order to see it.
Answer : 590 Columbia Boulevard West, Lethbridge, AB, CANADA T1K 5N8
2. Retrieve the FirstName, LastName, Birthdate, Address, City, and State from the Employees table.)
code:
select FirstName, LastName, Birthdate, Address, City, State from Employees
Which of the employees listed below has a birthdate of 3-3-1965?
Answer : Steve
3. Retrieve all the columns from the Tracks table, but only return 20 rows.
code:
select * from Tracks limit 20;
What is the runtime in milliseconds for the 5th track, entitled "Princess of the Dawn"? Note: You will need to scroll to the right to see it, and you may want to copy and paste the number to ensure it is entered correctly.
Answer : 375418
Also Check: Introduction to Data Analysis Using Excel Quiz Answers
Week 2 Quiz
1. Filtering data is used to do which of the following? (select all that apply).
Answers:
Narrows down the results of the data.
Reduces the strain on the client application
Reduce the time it takes to run the query
Removes unwanted data in a calculation
Helps you understand the contents of your data
2. You are doing an analysis on musicians that start with the letter “K”. Select the correct query that would retrieve only the artists whose name starts with this letter.
Answer:
SELECT name FROM Artists WHERE name LIKE ‘K%’;
3. A null and a zero value effectively mean the same thing. True or false?
Answer:
False
4. Select all that are true regarding wildcards
Answers:
Wildcards take longer to run compared to a logical operator
Wildcards at the end of search patterns take longer to run
5. Select the statements below that ARE NOT true of the ORDER BY clause (select all that apply).
Answers:
Cannot sort by a column not retrieved
Can be anywhere in the select statement
6. Select all of the valid math operators in SQL (select all that apply).
Answers:
+ (addition)
- (subtraction)
/ (division)
* (multiplication)
7. Which of the following is an aggregate function? (select all that apply).
Answers:
MAX()
MIN()
Count()
8. Which of the following is true of GROUP BY clauses?
Answers:
GROUP BY clauses can contain multiple columns
NULLs will be grouped together if your Group By column contains NULLs
Every column in your select statement must be present in a group by clause, except for aggregated calculations.
9. Select the true statement below.
Answer:
HAVING filters after the data is grouped.
10. Which is the correct order of occurrence in a SQL statement?
Answer:
select, from, where, group by, having
Also Check: The Data Scientist’s Toolbox Quiz Answers - Coursera
Week 2 Coding
1. Find all the tracks that have a length of 5,000,000 milliseconds or more..
Code:
select Name, Milliseconds from Tracks where Milliseconds>=5000000;
How many tracks are returned?
Answer : 2
2. Run Query: Find all the invoices whose total is between $5 and $15 dollars.
Code:
select InvoiceId, Total from Invoices where total between 5 and 15;
While the query in this example is limited to 10 records, running the query correctly will indicate how many total records there are - enter that number below.
Answer : 168
3. Run Query: Find all the customers from the following States: RJ, DF, AB, BC, CA, WA, NY.
Code :
select FirstName, LastName, Company, State from Customers where State in ('RJ', 'DF', 'AB', 'BC', 'CA', 'WA', 'NY')
What company does Jack Smith work for?
Answer : Microsoft Corp
4. Run Query: Find all the invoices for customer 56 and 58 where the total was between $1.00 and $5.00.Run Query: Find all the invoices for customer 56 and 58 where the total was between $1.00 and $5.00.
Code :
select InvoiceID,InvoiceDate, CustomerId, Total from Invoices where CustomerId in (56, 58) AND (Total >= 1.00 and Total <= 5.00)
What was the invoice date for invoice ID 315?
Answer : 10-27-2012
5. Run Query: Find all the tracks whose name starts with 'All'.
Code :
select Name, TrackId from Tracks where Name LIKE "ALL%";
While only 10 records are shown, the query will indicate how many total records there are for this query - enter that number below.
Answer : 15
6. Run Query: Find all the customer emails that start with "J" and are from gmail.com.
Code :
select CustomerID, Email from Customers where Email like "J%gmail.com";
Enter the one email address returned (you will likely need to scroll to the right) below.
Answer : jubarnett@gmail.com
7. Run Query: Find all the invoices from the billing city Brasília, Edmonton, and Vancouver and sort in descending order by invoice ID.
Code :
select InvoiceId,BillingCity,Total from Invoices where BillingCity in ('Brasilia','Edmonton','Vancouver') order by InvoiceId desc;
What is the total invoice amount of the first record returned? Enter the number below without a $ sign. Remember to sort in descending order to get the correct answer.
Answer : 13.86
8. Run Query: Show the number of orders placed by each customer (hint: this is found in the invoices table) and sort the result by the number of orders in descending order.
Code :
select CustomerId , COUNT(InvoiceId) from Invoices group by CustomerId order by COUNT(InvoiceId) DESC
What is the number of items placed for the 8th person on this list? Enter that number below.
Answer : 7
9. Run Query: Find the albums with 12 or more tracks.
Code :
select TrackId, AlbumId from Tracks group by AlbumId having COUNT(DISTINCT TrackId) >= 12;
While the number of records returned is limited to 10, the query, if run correctly, will indicate how many total records there are. Enter that number below.
Answer : 158
Also Check: Write Professional Emails in English - Coursera All Weeks Answers
Week 3 Quiz
1. Which of the following statements is true regarding subqueries?
Answer:
Subqueries always process the innermost query first and the work outward.
2. If you can accomplish the same outcome with a join or a subquery, which one should you always choose?
Answer:
Joins are usually faster, but subqueries can be more reliable, so it depends on your situation.
3. The following diagram is a depiction of what type of join?
Answer:
Inner Join
4. Select which of the following statements are true regarding inner joins. (Select all that apply).
Answers:
Inner joins are one of the most popular types of joins use
There is no limit to the number of table you can join with an inner join.
Performance will most likely worsen with the more joins you make
5. Which of the following is true regarding Aliases?
Answers:
Aliases are often used to make column names more readable.
SQL aliases are used to give a table, or a column in a table, a temporary name.
An alias only exists for the duration of the query.
6. What is wrong with the following query?.
Answers:
Should be using an inner join rather than a left join
Column names do not have an alias
7. What is the difference between a left join and a right join?.
Answer:
The only difference between a left and right join is the order in which the tables are relating.
8. If you perform a cartesian join on a table with 10 rows and a table with 20 rows, how many rows will there be in the output table?
Answer:
200
9. Which of the following statements is true? (select all that apply).
Answers:
Each SELECT statement within UNION must have the same number of columns
The UNION operator is used to combine the result-set of two or more SELECT statements
The columns must also have similar data types
10. Data scientists need to use joins in order to: (select the best answer)
Answer:
Retrieve data from multiple tables.
Week 3 Coding
1. Using a subquery, find the names of all the tracks for the album "Californication".
Code:
select t.Name from Tracks t where t.AlbumId = ( select a.AlbumId from Albums a where a.Title = 'Californication')
What is the title of the 8th track?
Answer : Porcelain
2. Find the total number of invoices for each customer along with the customer's full name, city and email.
Code:
select c.CustomerId, c.FirstName, c.LastName, c.City, c.Email, COUNT(i.InvoiceId) from Customers c join Invoices i on c.CustomerId = i.CustomerId Group by c.CustomerId;
After running the query described above, what is the email address of the 5th person, František Wichterlová? Enter the answer below (feel free to copy and paste)..
Answer : frantisekw@jetbrains.com
3. Retrieve the track name, album, artistID, and trackID for all the albums.
Code :
select t.Name, a.Title, ar.Name, t.TrackId from Artists ar inner join Albums a on ar.ArtistId = a.ArtistId inner join Tracks t on a.AlbumId = t.AlbumId;
What is the song title of trackID 12 from the "For Those About to Rock We Salute You" album? Enter the answer below.
Answer : Breaking The Rules
4. Retrieve a list with the managers last name, and the last name of the employees who report to him or her.
Code :
select mgr.LastName Manager, e.LastName Employee from Employees e left join Employees mgr on e.ReportsTo = mgr.EmployeeId
After running the query described above, who are the reports for the manager named Mitchell (select all that apply)?
Answer : King, Callahan
5. Find the name and ID of the artists who do not have albums.
Code :
select a.Title, ar.Name, ar.ArtistId from Artists ar left join Albums a on ar.ArtistId = a.ArtistId where a.Title is NULL;
After running the query described above, two of the records returned have the same last name. Enter that name below.
Answer : Gilberto
6. Use a UNION to create a list of all the employee's and customer's first names and last names ordered by the last name in descending order.
Code :
select e.FirstName, e.LastName from Employees e UNION select c.FirstName, c.LastName from Customers c order by c.LastName DESC
After running the query described above, determine what is the last name of the 6th record? Enter it below. Remember to order things in descending order to be sure to get the correct answer.
Answer : Taylor
7. See if there are any customers who have a different city listed in their billing city versus their customer city.
Code :
select c.CustomerId, c.FirstName, c.LastName, c.City from Customers c join Invoices i on c.CustomerId = i.CustomerId where c.City <> i.BillingCity;
Indicate your answer below..
Answer : No customers have a different city listed in their billing city versus customer city.
Week 4 Quiz
1. Which of the following are supported in SQL when dealing with strings? (Select all that apply).
Answers:
Lower
Trim
Upper
Concatenate
Substring
2. What will the result of the following statement be? SELECT LEFT('You are beautiful.', 3)
Answers:
u are beautiful
3. What is the results of the following query? select * orders where order_date = ‘2017-07-15’
Answer:
You won't get any results
4. Case statements can only be used for which of the following statements (select all that apply)?
Answers:
Update
Delete
Insert
Select
5. Which of the following is false regarding views?
Answer:
Views will remain after the database connection has ended
6. You are only allowed to have one condition in a case statement. True or false?
Answer:
False
7. Select the correct SQL syntax for creating a view.
Answer:
CREATE VIEW customers AS SELECT * FROM customers WHERE Name LIKE '%I'
8. Profiling data is helpful for which of the following? (Select all that apply)
Answers:
Filter out unwanted data elements
Understanding your data
9. What is the most important step before beginning to write queries?
Answer:
Understanding your data
10. When debugging a query, what should you always remember to do first?
Answer:
Start simple and break it down first
Week 4 Coding
1. Pull a list of customer ids with the customer’s full name, and address, along with combining their city and country together. Be sure to make a space in between these two and make it UPPER CASE.
Code:
select customerid,firstname,lastname,address,city,country from customers where customerid=16;
What is the city and country result for CustomerID 16?
Answer : Mountain View USA
2. Create a new employee user id by combining the first 4 letters of the employee’s first name with the first 2 letters of the employee’s last name. Make the new field lower case and pull each individual step to show your work.
Code:
sselect c.FirstName , substr(c.FirstName, 1, 4) as FirstNameShort , c.LastName , substr(c.LastName, 1, 2) as LastNameShort , LOWER(substr(c.FirstName, 1, 4) || substr(c.LastName,1,2)) as NewID from Customers c;
What is the final result for Robert King?
Answer : RobeKi
3. Show a list of employees who have worked for the company for 15 or more years using the current date function. Sort by lastname ascending.
Code :
select e.LastName,e.FirstName,e.BirthDate,DATE('now') - e.BirthDate as Age,DATE('now') - e.HireDate as Tenure from Employees e where Tenure >= 15 order by e.LastName asc;
What is the lastname of the last person on the list returned?
Answer : Peacock
4. Profiling the Customers table, answer the following question.
Code :
select * from Customers c where c.Company is null;
Are there any columns with null values? Indicate any below. Select all that apply.
Answer : Company, Phone, Fax, Postal Code
5. Find the cities with the most customers and rank in descending order.
Code :
select c.City, count(c.CustomerId) from Customers c group by c.City order by count(c.CustomerId) DESC;
Which of the following cities indicate having 2 customers?
Answer : São Paulo, Mountain View, London
6. Create a new customer invoice id by combining a customer’s invoice id with their first and last name while ordering your query in the following order: firstname, lastname, and invoiceID.
Code :
select c.FirstName || c.LastName || i.InvoiceId as NewInvoiceId from Customers c left join Invoices i on c.CustomerId = i.CustomerId where NewInvoiceId like 'AstridGruber%' order by c.FirstName, c.LastName, i.InvoiceId;
Select all of the correct "AstridGruber" entries that are returned in your results below. Select all that apply.
Answer : AstridGruber273, AstridGruber296, AstridGruber370
Peer Graded Assignment
Data Scientist Role Play: Profiling and Analyzing the Yelp Dataset Worksheet
Part 1: Yelp Dataset Profiling and Understanding
1. Profile the data by finding the total number of records for each of the tables below:
i. Attribute table = 10000
ii. Business table = 10000
iii. Category table = 10000
iv. Checkin table = 10000
v. elite_years table = 10000
vi. friend table = 10000
vii. hours table = 10000
viii. photo table = 10000
ix. review table = 10000
x. tip table = 10000
xi. user table = 10000
2. Find the total number of distinct records for each of the keys listed below:
i. Business = 10000 (id)
ii. Hours = 1562 (business_id)
iii. Category = 2643 (business_id)
iv. Attribute = 1115 (business_id)
v. Review = 10000 (id), 8090 (business_id), 9581 (user_id)
vi. Checkin = 493 (business_id)
vii. Photo = 10000 (id), 6493 (business_id)
viii. Tip = 537 (user_id), 3979 (business_id)
ix. User = 10000 (id)
x. Friend = 11 (user_id)
xi. Elite_years = 2780 (user_id)
3. Are there any columns with null values in the Users table? Indicate "yes," or "no."
Answer: No
SQL code used to arrive at answer:
select id, name, review_count, yelping_since, useful, funny, cool, fans, average_stars,
compliment_hot, compliment_more, compliment_profile, compliment_cute, compliment_list,
compliment_note, compliment_plain, compliment_cool, compliment_funny, compliment_writer, compliment_photos
from user
where id is null
or name is null
or review_count is null
or yelping_since is null
or useful is null
or funny is null
or cool is null
or fans is null
or average_stars is null
or compliment_hot is null
or compliment_more is null
or compliment_profile is null
or compliment_cute is null
or compliment_list is null
or compliment_note is null
or compliment_plain is null
or compliment_cool is null
or compliment_funny is null
or compliment_writer is null
or compliment_photos is null
4. Find the minimum, maximum, and average value for the following fields:
i. Table: Review, Column: Stars
min: 1 max: 5 avg: 3.7082
ii. Table: Business, Column: Stars
min: 1.0 max: 5.0 avg: 3.6549
iii. Table: Tip, Column: Likes
min: 0 max: 2 avg: 0.0144
iv. Table: Checkin, Column: Count
min: 1 max: 53 avg: 1.9414
v. Table: User, Column: Review_count
min: 0 max: 2000 avg: 24.2995
5. List the cities with the most reviews in descending order:
SQL code used to arrive at answer:
select city, sum(review_count)
from business
group by city
order by sum(review_count) desc
Copy and Paste the Result Below:
+-----------------+-------------------+
| city | sum(review_count) |
+-----------------+-------------------+
| Las Vegas | 82854 |
| Phoenix | 34503 |
| Toronto | 24113 |
| Scottsdale | 20614 |
| Charlotte | 12523 |
| Henderson | 10871 |
| Tempe | 10504 |
| Pittsburgh | 9798 |
| Montréal | 9448 |
| Chandler | 8112 |
| Mesa | 6875 |
| Gilbert | 6380 |
| Cleveland | 5593 |
| Madison | 5265 |
| Glendale | 4406 |
| Mississauga | 3814 |
| Edinburgh | 2792 |
| Peoria | 2624 |
| North Las Vegas | 2438 |
| Markham | 2352 |
| Champaign | 2029 |
| Stuttgart | 1849 |
| Surprise | 1520 |
| Lakewood | 1465 |
| Goodyear | 1155 |
+-----------------+-------------------+
6. Find the distribution of star ratings to the business in the following cities:
i. Avon
SQL code used to arrive at answer:
select stars as [Star Rating], count(stars) as [Count]
from business b
where city = 'Avon'
group by stars
Copy and Paste the Resulting Table Below (2 columns - star rating and count):
+-------------+-------+
| Star Rating | Count |
+-------------+-------+
| 1.5 | 1 |
| 2.5 | 2 |
| 3.5 | 3 |
| 4.0 | 2 |
| 4.5 | 1 |
| 5.0 | 1 |
+-------------+-------+
ii. Beachwood
SQL code used to arrive at answer:
select stars as [Star Rating], count(stars) as [Count]
from business b
where city = 'Beachwood'
group by stars
Copy and Paste the Resulting Table Below (2 columns - star rating and count):
+-------------+-------+
| Star Rating | Count |
+-------------+-------+
| 2.0 | 1 |
| 2.5 | 1 |
| 3.0 | 2 |
| 3.5 | 2 |
| 4.0 | 1 |
| 4.5 | 2 |
| 5.0 | 5 |
+-------------+-------+
7. Find the top 3 users based on their total number of reviews:
SQL code used to arrive at answer:
select name, review_count
from user
order by review_count desc
limit 3
Copy and Paste the Result Below:
+--------+--------------+
| name | review_count |
+--------+--------------+
| Gerald | 2000 |
| Sara | 1629 |
| Yuri | 1339 |
+--------+--------------+
8. Does posing more reviews correlate with more fans?
- No
Please explain your findings and interpretation of the results:
- N/A
SQL code:
select name, review_count, fans
from user
order by fans desc
limit 10
Results:
+-----------+--------------+------+
| name | review_count | fans |
+-----------+--------------+------+
| Amy | 609 | 503 |
| Mimi | 968 | 497 |
| Harald | 1153 | 311 |
| Gerald | 2000 | 253 |
| Christine | 930 | 173 |
| Lisa | 813 | 159 |
| Cat | 377 | 133 |
| William | 1215 | 126 |
| Fran | 862 | 124 |
| Lissa | 834 | 120 |
+-----------+--------------+------+
9. Are there more reviews with the word "love" or with the word "hate" in them?
Answer: more reviews with the word "love"
SQL code used to arrive at answer:
select (select count(text)
from review
where text like "%love%") as love_text,
(select count(text)
from review
where text like "%hate%") as hate_text
Results:
+-----------+-----------+
| love_text | hate_text |
+-----------+-----------+
| 1780 | 232 |
+-----------+-----------+
OR:
SELECT 'love' Word, COUNT(text) [Total Count]
FROM review
WHERE text LIKE '%love%'
UNION
SELECT 'hate' Word, COUNT(text) [Total Count]
FROM review
WHERE text LIKE '%hate%'
+------+-------------+
| Word | Total Count |
+------+-------------+
| hate | 232 |
| love | 1780 |
+------+-------------+
10. Find the top 10 users with the most fans:
SQL code used to arrive at answer:
select name, fans
from user
order by fans desc
limit 10
Copy and Paste the Result Below:
+-----------+------+
| name | fans |
+-----------+------+
| Amy | 503 |
| Mimi | 497 |
| Harald | 311 |
| Gerald | 253 |
| Christine | 173 |
| Lisa | 159 |
| Cat | 133 |
| William | 126 |
| Fran | 124 |
| Lissa | 120 |
+-----------+------+
11. Is there a strong correlation between having a high number of fans and being listed
as "useful" or "funny?"
SQL code used to arrive at answer:
select name, fans, useful, funny
from user
order by fans desc, useful desc, funny desc
limit 20
Copy and Paste the Result Below:
+-----------+------+--------+--------+
| name | fans | useful | funny |
+-----------+------+--------+--------+
| Amy | 503 | 3226 | 2554 |
| Mimi | 497 | 257 | 138 |
| Harald | 311 | 122921 | 122419 |
| Gerald | 253 | 17524 | 2324 |
| Christine | 173 | 4834 | 6646 |
| Lisa | 159 | 48 | 13 |
| Cat | 133 | 1062 | 672 |
| William | 126 | 9363 | 9361 |
| Fran | 124 | 9851 | 7606 |
| Lissa | 120 | 455 | 150 |
| Mark | 115 | 4008 | 570 |
| Tiffany | 111 | 1366 | 984 |
| bernice | 105 | 120 | 112 |
| Roanna | 104 | 2995 | 1188 |
| .Hon | 101 | 7850 | 5851 |
| Angela | 101 | 158 | 164 |
| Ben | 96 | 1180 | 1155 |
| Linda | 89 | 3177 | 2736 |
| Christina | 85 | 158 | 34 |
| Jessica | 84 | 2161 | 2091 |
+-----------+------+--------+--------+
Please explain your findings and interpretation of the results:
- N/A
Part 2: Inferences and Analysis
- N/A
Post a Comment