Analyzing Big Data with SQL Quiz Answer. This course is offered by “Coursera”. In this post you will get Analyzing Big Data with SQL Quiz Answer | 100% Correct Answer
Analyzing Big Data with SQL Quiz Answer Coursera
Offered By ”Cloudera”
Week 1 Core Quiz
1.
Question 1
Which best describes what the circled area in the following screen capture is used for?
1 point
- Clicking on a database name and seeing what tables are in the database
- Browsing databases and the tables within them
- Choosing which query engine to use
- Entering and running SQL statements
2.
Question 2
Which steps in Hue will list the tables within a database?
1 point
- Use the active database selector to change to the database, and then run SHOW TABLES
- Run SHOW DATABASES and then run LIST TABLES
- Use the active database selector to change to the database, and then run SHOW DATABASES
- Use the active database selector to change to the database, and then run LIST TABLES
- Run SHOW DATABASES and then run SHOW TABLES
3.
Question 3
What are the steps to run a SELECT statement in Hue?
1 point
- Navigate to the Table Browser and click on the table you want to use. Enter the SELECT statement in the text box. Then run the statement by typing Enter.
- Navigate to the Table Browser and click on the table you want to use. Enter the SELECT statement in the text box. Then run the statement either by clicking the blue Execute arrow or typing Ctrl (Control) + Enter.
- Click the Query button or Query dropdown to select the query editor you want to use. Enter the SELECT statement in the text box. Then run the statement by typing Enter.
- Click the Query button or Query dropdown to select the query editor you want to use. Enter the SELECT statement in the text box. Then run the statement either by clicking the blue Execute arrow or typing Ctrl (Control) + Enter.
4.
Question 4
Which of the following provide a user interface where you can enter and run SQL queries? Check all that apply.
1 point
- Hue
- Linux
- JDBC
- Impala Shell
- SQuirreL SQL Client
Week 2 Core Quiz
1.
Question 1
Which is the best description of the SELECT statement in SQL? (Note, this is not referring to the SELECT list.)
1 point
- The statement most often used to define data structures
- The clause for choosing which table to pull data from
- The statement for choosing which database to work in
- The only statement for creating query results
2.
Question 2
Which of the following can be achieved using a SELECT statement with Hive or Impala? Check all that apply.
1 point
- Displaying the output of an expression
- Loading a file of data into a table
- Listing all the data in a table
- Listing all the tables in a database
- Displaying specific columns in a table
- Displaying the names of the available databases
- Displaying the data in a table using a specific order for the columns
3.
Question 3
The customers table in the default database has columns cust_id, name, and country (all string types). Which of the following are valid SELECT statements? Check all that apply.
1 point
- SELECT customers FROM default;
- SELECT customers;
- SELECT name, cust_id FROM customers;
- SELECT name;
- SELECT ‘Brendon’;
- SELECT Arfa;
- SELECT * FROM customers;
4.
Question 4
This SELECT statement returns one result. What is the result?
SELECT 3 + 2 * 5;
1 point
Enter answer here
| 13 |
5.
Question 5
The result of DESCRIBE fun.games; gives this result:
name type comment
id int
name string
inventor string
year string
min_age tinyint
min_players tinyint
max_players tinyint
list_price decimal(5,2)
Assume you are using Impala, which does not implicitly cast data types. Which of the following are valid to use in a SELECT list for this table?
1 point
- min_players-min_age
- min_players, list_price
- abs(name)
- name + 10
- ceil(list_price + 0.08*list_price)
- inventor
6.
Question 6
Consider this query:
SELECT game, shop, price, round(0.08*price,2) AS tax FROM fun.inventory;
Which are the correct column names in the header of the result set for this query?
1 point
- game, shop, price, AS, tax
- game, shop, price, _c4
- game, shop, price, _c3
- game, shop, price, tax
- game, shop, price, _c3, AS, tax
- game, shop, price, _c4, AS, tax
- game, shop, price, round(0.08*price,2)
- game, shop, price, round(0.08*price,2), AS, tax
7.
Question 7
This SELECT statement returns one result. What is the result?
SELECT floor(5 – 6.5);
1 point
Enter answer here
| 5.25 |
8.
Question 8
Suppose you want to calculate when each game in the fun.games table celebrated its 10th anniversary. (For information about this table, see the result of the DESCRIBE statement in Problem 5 above.) You might try the following query, but using Impala, it will cause an error:
SELECT year + 10 FROM fun.games;
Which of the following would correct the error and make the calculation correctly? Check all that apply.
1 point
- SELECT year + cast(10 STRING) FROM fun.games;
- SELECT cast(year INT) + 10 FROM fun.games;
- SELECT cast(year AS INT) + 10 FROM fun.games;
- SELECT year + cast(10 AS STRING) FROM fun.games;
9.
Question 9
The statement DESCRIBE workforce; has the following result:
name type comment
name string
occupation string
salary int
Which is the best statement to get a list of the occupations used in the table?
1 point
- SELECT salary FROM workforce;
- SELECT DISTINCT salary FROM workforce;
- SELECT occupation, salary DISTINCT FROM workforce;
- SELECT DISTINCT occupation, salary FROM workforce;
- SELECT salary DISTINCT FROM workforce;
- SELECT DISTINCT occupation FROM workforce;
- SELECT occupation FROM workforce;
- SELECT occupation DISTINCT FROM workforce;
10.
Question 10
You are working in the default database and want to list all the data in the crayons table, which is in the wax database. Which of the following allow you to do that? Check all that apply.
1 point
- Change the current database to wax and run SELECT * FROM crayons;
- Run SELECT * FROM crayons;
- Change the current database to crayons and run SELECT * FROM wax;
- Run SELECT * FROM wax.crayons;
- Run SELECT crayons FROM wax;
- Run SELECT crayons.* FROM wax;
11.
Question 11
Which of the following are true of keywords (such as SELECT and FROM) and identifiers (such as names of tables and columns) in Hive and Impala? Check all that apply.
1 point
- By convention, keywords are often shown uppercase
- Identifiers must be lowercase
- Keywords can never be used as identifiers
- Keywords are always case-insensitive
Week 3 Core Quiz
1.
Question 1
For which of these tasks would you need to use a WHERE clause? Check all that apply.
1 point
- For a table of pets, including their owners and ages, finding the range of values in their ages
- For a table that includes which of many offices each employee works, finding all the employees in the Chicago office
- For a table of inventory items, including quantity and price, finding all inventory items priced under $5
- For a table of web logs, which show the IP addresses of every visit, removing rows with duplicate IP addresses
2.
Question 2
The following query will fail:
SELECT name, shop, aisle FROM fun.inventory WHERE price + 5;
What are the issues with this query? Check all that apply.
1 point
- The table reference in the FROM clause cannot have a dot ( .) in the name.
- The expression in the WHERE clause must evaluate to a Boolean value
- The expression in the WHERE clause must be in the SELECT list
- The column in the WHERE clause is not in the SELECT list
3.
Question 3
Write and run a query on wax.crayons to find colors with 205 as the red value. Which of the following colors are returned? Check all that apply.
1 point
- Almond
- Antique Brass
- Atomic Tangerine
- Banana Mania
- Mahogany
- Silver
- Tan
- Wisteria
4.
Question 4
Select the expressions that are equivalent to x != 2 in SQL. Check all that apply.
1 point
- x < 2 OR x > 2
- NOT x = 2
- x < 2 AND x > 2
- x NOT = 2
- x <> 2
- x < 2 OR 2 > x
5.
Question 5
The table table_name includes the following row:
id bool1 bool2 bool3
34 true false true
Which of the following would include that row in the result set? Check all that apply.
1 point
- SELECT * FROM table_name WHERE bool1 AND NOT (bool2 OR bool3)
- SELECT * FROM table_name WHERE NOT bool1 OR bool2 AND bool3
- SELECT * FROM table_name WHERE NOT bool2 AND bool3
- SELECT * FROM table_name WHERE bool1 AND bool2 OR bool3
- SELECT * FROM table_name WHERE NOT (bool1 AND bool2)
6.
Question 6
Which of the following would provide results that include a row with int_x=-25? Check all that apply.
1 point
- SELECT * FROM table_name WHERE int_x IN (-50, 0);
- SELECT * FROM table_name WHERE int_x BETWEEN (-50, 0);
- SELECT * FROM table_name WHERE int_x NOT IN -50 AND 0;
- SELECT * FROM table_name WHERE int_x BETWEEN -50 AND 0;
- SELECT * FROM table_name WHERE int_x NOT BETWEEN -50 AND 0;
- SELECT * FROM table_name WHERE int_x NOT BETWEEN (-50, 0);
- SELECT * FROM table_name WHERE int_x IN -50 AND 0;
- SELECT * FROM table_name WHERE int_x NOT IN (-50, 0);
7.
Question 7
The following shows just a few rows from a table for students in a school. (GPA is grade point average, where 4.0 means the student is getting the highest scores possible. Absences is how many days the student has not attended school, and detention is a punishment for bad behavior.)
students
id name age gpa absences detentions
930 Olufunmilayo Ayton 16 4.00 3 2
667 Vincent Michaelson 15 2.53 12 0
907 Asa Quigg 15 3.57 1 0
168 Kiran Patil 17 3.28 0 3
368 Amaal Al-Amin 16 4.00 NULL 2
Check all the students whose rows would be included in the results of
SELECT name FROM students WHERE absences < 2;
1 point
- Olufunmilayo Ayton
- Vincent Michaelson
- Asa Quigg
- Kiran Patil
- Amaal Al-Amin
8.
Question 8
The offices table in the default database on the VM includes one row with NULL in state_province column. Which of the following would provide that row in the result? Check all that apply.
1 point
- SELECT * from default.offices WHERE state_province=”Santa Fe” AND state_province IS NOT NULL
- SELECT * from default.offices WHERE state_province=”Santa Fe” OR state_province IS NULL
- SELECT * from default.offices WHERE state_province IS NULL
- SELECT * from default.offices WHERE state_province=”Santa Fe” AND state_province IS NULL
- SELECT * from default.offices WHERE state_province IS NOT NULL
- SELECT * from default.offices WHERE state_province=”Santa Fe” OR state_province IS NOT NULL
9.
Question 9
The following shows Amaal Al-Amin’s data from a table for students in a school. (GPA is grade point average, where 4.0 means the student is getting the highest scores possible. Absences is how many days the student has not attended school, and detention is a punishment for bad behavior.)
id name age gpa absences detentions
368 Amaal Al-Amin 16 4.00 NULL 2
Which of the following WHERE clauses would include Amaal’s row when used in a SELECT query? Check all that apply.
1 point
- WHERE gpa > 3.50 AND absences < 3
- WHERE gpa < 3.50 OR absences < 3
- WHERE absences < 3
- WHERE gpa < 3.50 AND absences < 3
- WHERE NOT absences < 3
- WHERE gpa > 3.50 OR absences < 3
10.
Question 10
You have a database in which some bad data in the column named score is marked with NULL and some is marked with the value -1. For your purposes, you can do more with values marked -1, so you want to replace all NULL values in the score column with -1 but otherwise leave the score values as they are. Which of the following will do this? Check all that apply.
1 point
- CASE WHEN score IS NULL THEN -1 ELSE score END
- ifnull(score, -1) Note: For some engines this is nvl(score, -1)
- nullif(score, -1)
- if(score IS NULL, -1, score)
- if(score = -1, NULL, score)
- CASE WHEN score = -1 THEN NULL ELSE score END
Week 4 Core Quiz
1.
Question 1
Below is a table with three rows. What is the value of AVG(items) for this table?
order_id items total
829 3 38.92
220 7 107.06
1043 2 19.98
1 point
Enter answer here
| 4 |
2.
Question 2
Which of the following statements are valid? (The column color is a string column, and both red and blue are integer columns.) Check all that apply.
1 point
SELECT color, MIN(red) FROM wax.crayons;
SELECT MIN(blue + red) FROM wax.crayons;
SELECT blue + MIN(red) FROM wax.crayons;
SELECT -20 + MIN(red) FROM wax.crayons;
SELECT MIN(-20 + red) FROM wax.crayons;
3.
Question 3
The flights dataset includes the departure delay (in minutes) and the scheduled time of departure (as an integer, for example 3:14 in the afternoon is 1514). Write and run a query to find the average delay of only those flights that were scheduled to depart after 1:00 in the afternoon. Do not include those scheduled for exactly 1:00. Report to the nearest minute. Note: There are two columns related to departure time—be sure you’re using the scheduled departure time.
1 point
Enter answer here
| 13 |
4.
Question 4
Here is the default.orders table:
order_id cust_id empl_id total
1 c 1 24.78
2 a 4 28.54
3 b 3 48.69
4 b 3 -16.39
5 z 2 29.92
How many columns and rows does the result of this query have?
SELECT cust_id, COUNT(*), SUM(total)
FROM default.orders
GROUP BY cust_id;
1 point
2 columns, 1 row
2 columns, 4 rows
2 columns, 5 rows
3 columns, 1 row
3 columns, 4 rows
3 columns, 5 rows
4 columns, 1 row
4 columns, 4 rows
4 columns, 5 rows
6 columns, 1 row
6 columns, 4 rows
6 columns, 5 rows
5.
Question 5
In the fly.flights table, the air time of each flight is given in minutes by the air_time column. Write and run a query to find the average air_time of the flights, in hours, to the nearest tenth of an hour.
1 point
Enter answer here
| 1.8 |
6.
Question 6
Write and run a query on the fly.planes table that would answer the question, “How many different manufacturer values are there for each type of aircraft?” Then use the results to enter the number of different values for balloon manufacturers are included in the table.
(Note: For this problem, you do not need to control for variations in how the same manufacturer is entered. For example, “ACME Balloons, Inc.” and “ACME Balloons” are two different values, even though they probably are for the same manufacturer. )
1 point
Enter answer here
| 528 |
7.
Question 7
For a table of students enrolled at a college, the query SELECT MIN(age) FROM students; gave one row in the results, with only one column. The value was 16. The query SELECT COUNT(*) FROM students WHERE age IS NULL returned the value 2827. Choose which of the following statements is most accurate and informative:
1 point
The lowest known age of a student in the students table is 16.
The lowest age of a student in the students table is 16.
The lowest age of a student in the students table is unknown.
8.
Question 8
Which SELECT statements will return the same result as SELECT COUNT(type) AS num_types FROM fly.planes; Check all that apply.
1 point
- SELECT COUNT(*) AS num_types FROM fly.planes;
- SELECT COUNT(DISTINCT type) AS num_types FROM fly.planes;
- SELECT COUNT(*) AS num_types FROM fly.planes WHERE tz IS NULL;
- SELECT COUNT(ALL type) AS num_types FROM fly.planes;
- SELECT COUNT(*) AS num_types FROM fly.planes WHERE type IS NOT NULL;
9.
Question 9
Write and run a query in the VM to find all the airports with average departure delays of more than 30 minutes. (Note that you want the origin airports, not the destinations. Also, the dep_delay column is given in minutes.) How many airports have more than 30 minutes for their average departure delay?
1 point
Enter answer here
| 53 |
10.
Question 10
Choose the SELECT statement that returns a result set describing, for each carrier, the average air time for the flights that have a departure delay longer than the flight’s air time, and only for carriers with more than 70,000 of those flights.
1 point
SELECT carrier, AVG(air_time) FROM flights
GROUP BY carrier
WHERE dep_delay > air_time
HAVING COUNT(*) > 70,000;
SELECT carrier, AVG(air_time) FROM flights
WHERE dep_delay > air_time
GROUP BY carrier HAVING count(*) > 70000;
SELECT carrier, AVG(air_time) FROM flights
GROUP BY carrier
HAVING dep_delay > air_time AND COUNT(*) > 70,000;
SELECT carrier, AVG(air_time) FROM flights
WHERE dep_delay > air_time AND COUNT(*) > 70,000
GROUP BY carrier;
Week 5 Core Quiz
1.
Question 1
The fly.flights table has the following schema:
column type
year smallint
month tinyint
day tinyint
dep_time smallint
sched_dep_time smallint
dep_delay smallint
arr_time smallint
sched_arr_time smallint
arr_delay smallint
carrier string
flight smallint
tailnum string
origin string
dest string
air_time smallint
distance smallint
Choose the valid SELECT statements. Check all that apply.
1 point
- SELECT * FROM fly.flights ORDER BY distance, air_time, delay;
- SELECT carrier, COUNT(*) FROM fly.flights GROUP BY carrier ORDER BY carrier;
- SELECT carrier, COUNT(*) FROM fly.flights ORDER BY carrier GROUP BY carrier;
- SELECT * FROM fly.flights ORDER BY distance;
2.
Question 2
Select all the statements that return the same result as SELECT * FROM flights ORDER BY carrier;
1 point
- SELECT * FROM flights ORDER BY carrier ASC;
- SELECT * FROM flights ORDER BY carrier ASCENDING;
- SELECT * FROM flights ORDER BY carrier DESC;
- SELECT * FROM flights ORDER BY -carrier ASC;
3.
Question 3
Suppose you want to find the longest-distance flights in the fly.flights table for a particular carrier, and then find the flights with the shortest air time.
Write a query to return the data in fly.flights for American Airlines (carrier is AA) so that they are sorted by distance with the longest distance first, and for those that tie distances, by air_time with the shortest air time first. Execute the query in Hue using Impala. What’s the shortest air time for the longest distance?
1 point
Enter answer here
4.
Question 4
Write and run a SQL query to determine which airport in the fly.airports table is closest to the geographical (not magnetic) North Pole, using the following calculation for the distance in kilometers, using the latitude (lat) column: distance = 6371 * 2 * asin(least(1, sin(radians(90 – lat) / 2)))
(Note: The least function chooses the minimum value among two or more scalar values—similar to the MIN function, but MIN works on values in a column.)
Which airport is closest to the geographical North Pole?
1 point
- Aberdeen Regional Airport
- Cowra Airport
- Wainwright Airport
- Wiley Post Will Rogers Memorial Airport
- Zephyrhills Municipal Airport
5.
Question 5
Select the queries that will return exactly the same result as the query:
SELECT * FROM fly.planes ORDER BY year DESC;
when executed by Impala. Check all that apply.
1 point
- SELECT * FROM fly.planes ORDER BY year ASC;
- SELECT * FROM fly.planes ORDER BY year ASC NULLS FIRST;
- SELECT * FROM fly.planes ORDER BY year NULLS FIRST;
- SELECT * FROM fly.planes ORDER BY year ASC NULLS LAST;
- SELECT * FROM fly.planes ORDER BY year;
- SELECT * FROM fly.planes ORDER BY year DESC NULLS LAST;
- SELECT * FROM fly.planes ORDER BY year NULLS LAST;
- SELECT * FROM fly.planes ORDER BY year DESC NULLS FIRST;
6.
Question 6
Select the queries that will run without error in Hive. Check all that apply.
1 point
SELECT model FROM fly.planes ORDER BY type;
SELECT model, year FROM fly.planes ORDER BY 2019 – year;
SELECT * FROM fly.planes ORDER BY type;
SELECT model, type FROM fly.planes ORDER BY type;
SELECT model, 2019 – year AS age_in_2019 FROM fly.planes ORDER BY year;
SELECT model, 2019 – year AS age_in_2019 FROM fly.planes ORDER BY age_in_2019;
7.
Question 7
Select the valid SQL queries. Check all that apply.
1 point
- SELECT arr_time, AVG(arr_delay) AS avg_arr_delay
- FROM flights WHERE origin = ‘LAX’
- GROUP BY arr_time
- HAVING avg_arr_delay > 45
- LIMIT 1000;
SELECT arr_time, AVG(arr_delay) AS avg_arr_delay
FROM flights WHERE origin = ‘LAX’
GROUP BY arr_time
HAVING avg_arr_delay > 45
LIMIT -100;
SELECT arr_time, AVG(arr_delay) AS avg_arr_delay
FROM flights WHERE origin = ‘LAX’
LIMIT 100
GROUP BY arr_time
HAVING avg_arr_delay > 45;
SELECT arr_time, AVG(arr_delay) AS avg_arr_delay
FROM flights LIMIT 100
WHERE origin = ‘LAX’
GROUP BY arr_time
HAVING avg_arr_delay > 45;
SELECT arr_time, AVG(arr_delay) AS avg_arr_delay, 100 AS row_limit
FROM flights WHERE origin = ‘LAX’
GROUP BY arr_time
HAVING avg_arr_delay > 45
- LIMIT row_limit;
8.
Question 8
Which clause should you use with Impala to return rows 1001 through 1050 of a result set?
1 point
- OFFSET 1001,1050
- OFFSET 1001 LIMIT 1050
- LIMIT 1001,1050
- LIMIT 50 OFFSET 1000
- LIMIT 50 OFFSET 1001
- LIMIT 1050 OFFSET 1000
9.
Question 9
Select the appropriate uses for the LIMIT clause. Check all that apply.
1 point
- Filter individual rows based on conditions
- Protect against returning an unexpectedly large number of rows
- Randomly sample from a large table
- Reduce the compute resources used by the SQL engine
- Return a few rows from a table to inspect some of the values
10.
Question 10
In what order does a SQL engine execute the clauses of a SELECT statement?
1 point
FROM, WHERE, SELECT, GROUP BY, HAVING, ORDER BY, LIMIT
FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT
FROM, WHERE, GROUP BY, SELECT, HAVING, ORDER BY, LIMIT
Week 6 Core Quiz
1.
Question 1
The questions in this quiz intentionally use tables that are not in the VM. You should be able to answer the questions without running any queries.
1 point
I acknowledge that I do not need to run any queries for the following questions. I will not be able to run them because the tables do not exist on the VM.
2.
Question 2
Which of these queries produces the same result set as the following query?
SELECT * FROM table1
UNION
SELECT * FROM table2;
1 point
- SELECT * FROM table1 UNION ALL SELECT * FROM table2
- SELECT * FROM table1 UNION DISTINCT SELECT * FROM table2
3.
Question 3
Choose the best query to run in Impala to return the distinct union of the columns zip_plus_4 (type STRING, has values like ‘94306-0001’) in the california_emp table and zip (type INT, has values like 94105) in the california_offices table.
1 point
- SELECT zip_plus_4 FROM california_emp UNION DISTINCT SELECT CAST(zip AS STRING) FROM california_offices;
- SELECT zip_plus_4 AS zipcode FROM california_emp UNION DISTINCT SELECT zip AS zipcode FROM california_offices;
- SELECT zip_plus_4 FROM california_emp UNION DISTINCT SELECT zip FROM california_offices;
- SELECT zip_plus_4 AS zipcode FROM california_emp UNION DISTINCT SELECT CAST(zip AS STRING) AS zipcode FROM california_offices;
- SELECT CAST(zip_plus_4 AS INT) AS zipcode FROM california_emp UNION DISTINCT SELECT zip AS zipcode FROM california_offices;
4.
Question 4
The zip column (type INT) in the california_offices table has values from 90001 to 95899. The zip column (also type INT) in the oregon_offices table has values from 97030 to 97440. Which value is guaranteed to be in the top row of the result set when you run the following query with Impala?
SELECT zip FROM california_offices
UNION ALL
SELECT zip FROM oregon_offices
ORDER BY country DESC;
1 point
90001
95899
97030
97440
No particular value is guaranteed to be in the top row
5.
Question 5
The california_offices table has 65 rows, and the oregon_offices table has 5 rows. How many rows does the following query return when you run it with Impala?
SELECT zip FROM california_offices
UNION ALL
SELECT zip FROM oregon_offices
LIMIT 2;
1 point
Enter answer here
6.
Question 6
The california_offices and california_emp tables each have a column named office_id. All other columns have unique names between the two tables. Which of the following are valid join queries that Impala would run successfully on the VM, if these tables existed on the VM? Check all that apply.
1 point
SELECT name, e.office_id AS office_id, city, salary
FROM california_offices
JOIN california_emp ON o.office_id = e.office_id;
SELECT name, california_emp.office_id, city, salary
FROM california_emp
JOIN california_offices
ON california_emp.office_id = california_offices.office_id;
SELECT name, office_id, city, salary
FROM california_offices AS o
JOIN california_emp AS e ON o.office_id = e.office_id;
SELECT name, e.office_id AS office_id, city, salary
FROM california_offices AS o
JOIN california_emp AS e ON o.office_id = e.office_id;
7.
Question 7
Which of the following are valid join queries for Impala? Check all that apply.
1 point
SELECT o.office_id as office, AVG(salary) AS avg_salary
FROM california_emp e
JOIN california_offices o ON o.office_id = e.office_id
GROUP BY office
ORDER BY avg_salary;
SELECT name, o.office_id AS office
FROM california_emp e
JOIN california_offices o ON o.office_id = e.office_id
WHERE office = ‘CA009’;
SELECT o.office_id AS office, COUNT(*) AS number_of_employees
FROM california_emp e
JOIN california_offices o ON o.office_id = e.office_id
GROUP BY office;
SELECT name, o.office_id AS office, city
FROM california_emp e
JOIN california_offices o ON o.office_id = e.office_id
ORDER BY office DESC, name DESC;
8.
Question 8
The california_emp table includes one row with name=’Sandy Tilbrook’, with office_id=’CA086′. There is no row in california_offices with office_id=’CA086′. However, there is a office_id=’CA070′ in california_offices with city=’Redding’, but no rows in california_emp have office_id=’CA070′. (There are no other rows with city=’Redding’.) Choose the response that best describes how these rows will be included in the result set of this query:
SELECT name, city, salary
FROM california_emp e
INNER JOIN california_offices o ON e.office_id = o.office_id;
1 point
- A row with name=’Sandy Tilbrook’ will be included, and a row with city=’Redding’ will be included
- A row with name=’Sandy Tilbrook’ with be included, but no row with city=’Redding’ will be included
- No row with name=’Sandy Tilbrook’ will be included, and no row with city=’Redding’ will be included
- A row with city=’Redding’ will be included, but no row with name=’Sandy Tilbrook’ will be included
9.
Question 9
Which FROM clauses could you use to return data about all the employees in california_emp, even the remote workers who are not assigned to an office (office_id=NULL) or those erroneously assigned to a non-existent office? Select all that apply.
1 point
FROM california_offices o RIGHT OUTER JOIN california_emp e ON e.office_id=o.office_id
FROM california_emp e RIGHT OUTER JOIN california_offices o ON e.office_id=o.office_id
FROM california_offices o LEFT OUTER JOIN california_emp e ON e.office_id=o.office_id
FROM california_emp e LEFT OUTER JOIN california_offices o ON e.office_id=o.office_id
10.
Question 10
Which of the following queries returns only the employees whose office IDs do not match any office IDs found in the offices table?
1 point
SELECT empl_id, name
FROM california_offices o
LEFT OUTER JOIN california_emp e ON e.office_id = o.office_id
WHERE e.office_id IS NULL;
SELECT empl_id, name
FROM california_emp e
LEFT OUTER JOIN california_offices o ON e.office_id = o.office_id
WHERE o.office_id IS NULL;
SELECT empl_id, name
FROM california_emp e
LEFT OUTER JOIN california_offices o ON e.office_id = o.office_id
WHERE office_id IS NULL;
SELECT empl_id, name
FROM california_offices o
LEFT OUTER JOIN california_emp e ON e.office_id = o.office_id
WHERE o.office_id IS NULL;
SELECT empl_id, name
FROM california_emp e
LEFT OUTER JOIN california_offices o ON e.office_id = o.office_id
WHERE e.office_id IS NULL;
Peer-graded Assignment: Analyzing Big Data with SQL
Click Here To Download
[Attention]: – To get any assignment File you must need to click here to subscribe to YouTube and after that fill up this google form]
