SQL 2

Question 1

SELECT trips.date,
trips.dropoff,
riders.username
FROM trips
LEFT JOIN riders
ON trips.rider_id = riders.id;

Answer 1

this LEFT JOIN only returns columns you want

Question 2

In our airplanes database, you’d like to know which flights had an origin airport with an elevation greater than 2000 feet. We can do this with a subquery.

Answer 2

SELECT *
FROM flights
WHERE origin in (
SELECT code
FROM airports
WHERE elevation > 2000);

Question 3

non-correlated subquery

Answer 3

a subquery that can be run independently of the outer query and as we saw, can be used to complete a multi-step transformation.

Question 4

but we can also perform transformations on a single table. For instance, sometimes we need to aggregate in multiple steps - like taking an average of a count.

in the e.g. The inner query provides the distance flown by day, and the outer query uses the inner query’s result set to compute the average by day of week of a given month.

Answer 4

SELECT a.dep_month,
a.dep_day_of_week,
AVG(a.flight_distance) AS average_distance
FROM (
SELECT dep_month,
dep_day_of_week,
dep_date,
sum(distance) AS flight_distance
FROM flights
GROUP BY 1,2,3
) a
GROUP BY 1,2
ORDER BY 1,2;

Question 5

correlated subquery

Answer 5

In a correlated subquery, the subquery can not be run independently of the outer query. The order of operations is important in a correlated subquery:

A row is processed in the outer query.

Then, for that particular row in the outer query, the subquery is executed.

This means that for each row processed by the outer query, the subquery will also be processed for that row. In this example, we will find the list of all flights whose distance is above average for their carrier.

SELECT id
FROM flights AS f
WHERE distance > (
SELECT AVG(distance)
FROM flights
WHERE carrier = f.carrier);

Question 6

By default, the UNION operator selects only distinct values.What if we wanted to allow duplicate values?

Answer 6

We can do this by using the ALL keyword with UNION, with the following syntax:

SELECT column_name(s) FROM table1

UNION ALL

SELECT column_name(s) FROM table2;

Question 7

utilize a subquery to find the average sale price over both order_items and order_items_historic tables.

Answer 7

SELECT id, avg(a.sale_price) FROM (
SELECT id, sale_price FROM order_items
UNION ALL
SELECT id, sale_price FROM order_items_historic) AS a
GROUP BY 1;

Pay attention to “AS a “ here. “a” is an alias assigned to the new table created by the Union. the letter does not have to be “a”, it could be anything you want to use to name the new table

Question 8

INTERSECT

Answer 8

INTERSECT is used to combine two SELECTstatements, but returns only common rows returned by the two SELECT statements.

For instance, we might want to know what brands in our newly acquired store are also in our legacy store. We can do so using the following query:

SELECT brand FROM new_products

INTERSECT

SELECT brand FROM legacy_products;

Question 9

EXCEPT

Answer 9

EXCEPT is constructed in the same way, but returns distinct rows from the first SELECTstatement that aren’t output by the second SELECT statement.

Suppose we want to see if there are any categories that are in the new_products table that aren’t in the legacy_products table. We can use an EXCEPTquery to perform this analysis:

SELECT category FROM new_products

EXCEPT

SELECT category FROM legacy_products;

Question 10

in table airports, there’s a column elevation, but no elevation_tier. what code returns the table below?

elevation_tier COUNT(*)

High 103

Low 243

Medium 298

Answer 10

SELECT
CASE
WHEN elevation < 500 THEN ‘Low’
WHEN elevation BETWEEN 500 AND 1999 THEN ‘Medium’
WHEN elevation >= 2000 THEN ‘High’
ELSE ‘Unknown’
END AS elevation_tier
, COUNT(*)
FROM airports
GROUP BY 1;

In the above statement, END is required to terminate the statement, but ELSE is optional. If ELSE is not included, the result will be NULL.

Question 11

explain what the code does

SELECT state,
COUNT(CASE WHEN elevation >= 2000 THEN 1 ELSE NULL END) as count_high_elevation_aiports
FROM airports
GROUP BY state;

Answer 11

identify the total amount of airports with high elevation by state

Question 12

if we wanted to sum the total flight distance and compare that to the sum of flight distance from a particular airline (in this case, United Airlines) by origin airport, we could run the following query:

Answer 12

SELECT origin, sum(distance) as total_flight_distance, sum(CASE WHEN carrier = ‘UA’ THEN distance ELSE 0 END) as total_united_flight_distance
FROM flights
GROUP BY origin;

Question 13

Oftentimes we’d like to combine aggregates, to create percentages or ratios.

In the instance of the last query, we might want to find out the percent of flight distance that is from United by origin airport. We can do this simply by using the mathematical operators we need in SQL:

Answer 13

SELECT origin,
100.0*(sum(CASE WHEN carrier = ‘UN’ THEN distance ELSE 0 END)/sum(distance)) as percentage_flight_distance_from_united FROM flights
GROUP BY origin;

Question 14

Imagine that each dessert in our baked_goods table is inspected 2 hours, 30 minutes, and 1 day after the manufacture time. To derive the inspection date for each baked good, we can use the following query

Answer 14

SELECT DATETIME(manufacture\_time, '+2 hours', '30 minutes', '1 day') as inspection\_time
FROM baked\_goods;

Question 15

Numeric functions can be used to transform numbers. Some common SQLite mathematical functions are included below that take numeric data types as inputs:

Answer 15

SELECT (number1 + number2);: Returns the sum of two numbers. Similar, SQL can be used for subtraction, multiplication, and division.

SELECT CAST(number1 AS REAL) / number3;: Returns the result as a real number by casting one of the values as a real number, rather than an integer.

SELECT ROUND(number, precision);: Returns the numeric value rounded off to the next value specified. e.g. SELECT ROUND(ingredients_cost, 4) as rounded_cost FROM baked_goods;

Question 16

A couple more useful numeric SQL functions are included below: MAXand MIN.

`

Answer 16

MAX(n1,n2,n3,…): MIN(n1,n2,n3,…)

Question 17

A common use case for string manipulation in SQL is concatenation of strings. In SQLite, this is written as

Answer 17

SELECT string1 || ‘ ‘ || string2;

e.g. SELECT city || ‘ ‘ || state as location FROM bakeries;

String functions are again, very database specific, and it is best practice to consult documentation before proceeding.

Question 18

Another useful string function in SQL is REPLACE():. recall syntax

Answer 18

REPLACE(string,from_string,to_string)

The function returns the string stringwith all occurrences of the string from_string replaced by the string to_string.

For example in baked_goods, there is a column named ingredients. The ingredients strings are formatted with underscores, such as baking_soda and vanilla_extract. To make these values more readable, we might like to replace the underscores with spaces. We can do so by using the following query:

SELECT id, REPLACE(ingredients,’_’,’ ‘) as item_ingredients from baked_goods;

Question 19

Most dates in databases are timestamps, what do they look like

Answer 19

timestamps, which have hours and minutes, as well as the year, month, and day. Timestamps look like 2015-01-05 14:43:31

We can easily select the date an item was ordered at with the date function and the ordered_at field:

select date(ordered_at) from orders;

Question 20

See the 2 tables attached. Code for the daily revenue from customers ordering kale smoothies?

Answer 20

select date(ordered_at), round(sum(amount_paid), 2)
from orders
join order_items on
orders.id = order_items.order_id
where name = ‘kale-smoothie’
group by 1
order by 1;

Question 21

Using the same 2 tables in the last card, how do you generate this table attached?

Answer 21

select name, round(sum(amount_paid) /
(select sum(amount_paid) from order_items) * 100.0, 2) as pct
from order_items
group by 1
order by 2 desc;

Question 22

the basic structure of a casestatement:

Answer 22

case {condition}

when {value1} then {result1}

when {value2} then {result2}

else {result3}

end

e.g. The code below returns attached

select
case name
when ‘kale-smoothie’ then ‘smoothie’
when ‘banana-smoothie’ then ‘smoothie’
when ‘orange-juice’ then ‘drink’
when ‘soda’ then ‘drink’
when ‘blt’ then ‘sandwich’
when ‘grilled-cheese’ then ‘sandwich’
when ‘tikka-masala’ then ‘dinner’
when ‘chicken-parm’ then ‘dinner’
else ‘other’
end as category, round(1.0 * sum(amount_paid) /
(select sum(amount_paid) from order_items) * 100, 2) as pct
from order_items
group by 1
order by 2 desc;

Question 23

how to use the count function to count distinct columns in a table.

Answer 23

You can use the DISTINCT keyword within the COUNT aggregate function:

SELECT COUNT(DISTINCT column_name) AS some_alias FROM table_name

Question 24

One way to easily create and organize temporary results in a query is with CTEs, What is it and what is its syntax

Answer 24

Common Table Expressions, also known as with clauses. The with clauses make it easy to define and use results in a more organized way than subqueries. You define CTEs by adding a WITH clause directly before your SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs,

These clauses usually look like this:

with {subquery_name} as ( {subquery_body} )

select …

from {subquery_name}

where …

Question 25

understand code to calculate Daily Average Revenue Per User, using these attached tables

Answer 25

with daily_revenue as (
select
date(created_at) as dt,
round(sum(price), 2) as rev
from purchases
where refunded_at is null
group by 1
),
daily_players as (
select
date(created_at) as dt,
count(distinct user_id) as players
from gameplays
group by 1
)
select
daily_revenue.dt,
daily_revenue.rev / daily_players.players
from daily_revenue
join daily_players using (dt);

Question 26

from daily_revenue

join daily_players using (dt);

we used using instead of on in the join clause. This is a special case join. When to use?

Answer 26

When the columns to join have the same name in both tables you can use usinginstead of on. Our use of the usingkeyword is in this case equivalent to this clause:

from daily_revenue

join daily_players on

daily_revenue.dt = daily_players.dt;

Question 27

explain for following date join

date(g1.created_at) =

date(datetime(g2.created_at, ‘-1 day’))

Answer 27

This means “only join rows where the date in g1 is one less than the date in g2”, which makes it possible to see if users have returned!

Question 28

Using the tables attached what does the code Below return?

select
date(g1.created_at) as dt,
round(100 * count(distinct g2.user_id) /
count(distinct g1.user_id)) as retention
from gameplays as g1
left join gameplays as g2 on
g1.user_id = g2.user_id
and date(g1.created_at) = date(datetime(g2.created_at, ‘-1 day’))
group by 1
order by 1
limit 100;

Answer 28

see attached

Question 29

SQL IN Operator

Answer 29

IN operator allows you to specify multiple values in a WHERE clause.The IN operator is a shorthand for multiple OR conditions.e.g.

SQL IN Operator

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, …);

or:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

Question 30

What is the difference between IN and ALL and ANY operators in SQL ?

Answer 30

SQL> SQL> – Use the ANY operator in a WHERE clause to compare a value with any of the values in a list. SQL>

SQL> – You must place an =, <>, <, >, <=, or >= operator before ANY.

SQL> SQL> SELECT * 2 FROM employee 3 WHERE salary > ANY (2000, 3000, 4000);

For In Operator

SQL> – Use the IN operator in a WHERE clause to compare a value with any of the values in a list.

but with the In you cannot use =, <>, <, >, <=, or >=

Maybe for better understanding, these two conditions are equivalent. It’s a matter of taste which one you use (provided the RDBMS supports both of them)

… WHERE x IN (SELECT Y FROM THE_TABLE) … WHERE x =ANY (SELECT Y FROM THE_TABLE)

and these also

… WHERE x NOT IN (SELECT Y FROM THE_TABLE) … WHERE x <>ALL (SELECT Y FROM THE_TABLE)

Actually my personal habit is to use IN for list expression (like WHERE x IN (2,4,6,8) and =ANY, resp. <>ALL for sub-queries.

Question 31

What is the difference between the EXISTS and IN clause in SQL?

Answer 31

EXISTS is much faster than IN, when the sub-query results is very large.

IN is faster than EXISTS, when the sub-query results is very small.

EXISTS will tell you whether a query returned any results. e.g.:

SELECT * FROM Orders o

WHERE EXISTS (

SELECT *

FROM Products p

WHERE p.ProductNumber = o.ProductNumber)

IN is used to compare one value to several, and can use literal values, like this:

SELECT *

FROM Orders

WHERE ProductNumber IN (1, 10, 100)

You can also use query results with the IN clause, like this:

SELECT *

FROM Orders

WHERE ProductNumber IN (

SELECT ProductNumber

FROM Products

WHERE ProductInventoryQuantity > 0)

Question 32

when to use self join in sql

Answer 32

answer 1:

A self join is simply when you join a table with itself. There is no SELF JOIN keyword, you just write an ordinary join where both tables involved in the join are the same table. One thing to notice is that when you are self joining it is necessary to use an alias for the table otherwise the table name would be ambiguous.

It is useful when you want to correlate pairs of rows from the same table, for example a parent - child relationship. The following query returns the names of all immediate subcategories of the category ‘Kitchen’.

SELECT T2.name

FROM category T1

JOIN category T2

ON T2.parent = T1.id

WHERE T1.name = ‘Kitchen’

answer 2:

sql - What is SELF JOIN and when would you use it? - Stack Overflow<style>body,.top-bar{margin-top:1.9em}</style>

Well, one classic example is where you wanted to get a list of employees and their immediate managers:

select e.employee as employee, b.employee as boss

from emptable e, emptable b

where e.manager_id = b.empolyee_id

order by 1

It’s basically used where there is any relationship between rows stored in the same table.

employees.

multi-level marketing.

machine parts.

Question 33

Difference between CTE (common table expressions) and View

Answer 33

A CTE is a temporary/logical View, it is not store physically. It is a named query, the result for which is only available to the very next query after the CTE is defined. CTE is defined using WITH clause.
A View is a physical object that is present in the database. View is as good as a Table but it doesn’t store data physically as compared to a table, only the data schema is stored in View. View,when referred, pulls data by executing the query that is associated with it.
The biggest difference between a CTE and View, is that, View or derived table cannot call itself, whereas CTE can call itself and hence support recursion.
In many databases, views have options, for instance to index them.

Question 34

what is CREATE VIEW Statement

Answer 34

In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.
CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;
A view always shows up-to-date data! The database engine recreates the data, using the view’s SQL statement, every time a user queries a view.

Question 35

SQL CHECK Constraint

Answer 35

The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a single column it allows only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
The following SQL creates a CHECK constraint on the “Age” column when the “Persons” table is created. The CHECK constraint ensures that you can not have any person below 18 years:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);

Question 36

CREATE INDEX Statement

Answer 36

The CREATE INDEX statement is used to create indexes in tables. Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries. E.g.
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column1, column2, …);

Question 37

AUTO INCREMENT Field

Answer 37

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
The following SQL statement defines the "ID" column to be an auto-increment primary key field in the "Persons" table:
CREATE TABLE Persons (
 ID int NOT NULL AUTO\_INCREMENT,
 LastName varchar(255) NOT NULL,
 FirstName varchar(255),
 Age int,
 PRIMARY KEY (ID)
);

Question 38

Difference between VARCHAR and TEXT in mysql

Answer 38

TEXT -

>fixed max size of 65535 characters (you cannot limit the max size)
>takes 2 + c bytes of disk space, where c is the length of the stored string.
>cannot be part of an index
VARCHAR(M)

>variable max size of M characters
>M needs to be between 1 and 65535
>takes 1 + c bytes (for M ≤ 255) or 2 + c (for 256 ≤ M ≤ 65535) bytes of disk space where c is the length of the stored string can be part of an index

Question 39

SQL Comments
Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements. How do u start one?

Answer 39

Single line comments start with –.
Multi-line comments start with /* and end with */.

Question 40

The SELECT INTO statement

Answer 40

The SELECT INTO statement copies data from one table into a new table.
E.g. Copy only some columns into a new table:
SELECT column1, column2, column3, …
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

Question 41

The SQL INSERT INTO SELECT Statement

Answer 41

The INSERT INTO SELECT statement copies data from one table and inserts it into another table.
INSERT INTO SELECT requires that data types in source and target tables match
The existing records in the target table are unaffected. E.g. Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;