Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

MYSQL > Writing complex query > Flashcards

Writing complex query Flashcards

1
Q

In this complex query which will sql process first, the first or second SELECT query?

    • Find products that are more
    • expensive than lettuce (id = 3)

SELECT *
FROM products
WHERE unit_price > (
SELECT unit_price
FROM products
WHERE product_id = 3
)

A

parenthesis will process first, the subquery in this query

    • Find products that are more
    • expensive than lettuce (id = 3)

SELECT *
FROM products
WHERE unit_price > (
SELECT unit_price
FROM products
WHERE product_id = 3
)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

True or False

A subquery is limited within a WHERE clause

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q
    • exercise
    • In sql_hr database
    • Find employees who earn more than average

SELECT *
FROM employees
WHERE salary > (
SELECT AVG(salary)
FROM employees
)

A

notice the avg calculation is performed first in the parenthesis within a sub query

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

This query is returning what information

– Find products that have never been ordered

SELECT DISTINCT product_id
FROM order_items

A

A list of items that have been ordered by product_id

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

This query is returning what?

– Find products that have never been ordered

SELECT *
FROM products
WHERE product_id NOT IN (
SELECT DISTINCT product_id
FROM order_items
)

A

All the items in NOT IN the subquery list of distinct items ordered, so it’s excluding product_id sweet pea sprouts not being ordered.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Explain the steps in completing this exercise

– Find clients without invoices

SELECT *
FROM clients
WHERE client_id NOT IN (
SELECT distinct client_id
FROM invoices
)

A
  1. get all the distinct client_id in the invocies table

SELECT distinct client_id
FROM invoices

  1. then exclude the clients from the client table NOT IN this list created by the subquery

SELECT *
FROM clients
WHERE client_id NOT IN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

True or False

Many subqueries and joins can be performed interchangeablely

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Cite the two types of joins

A

inner and outter

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Same results or not?

– SELECT *
– FROM clients
– WHERE client_id NOT IN (
– SELECT distinct client_id
– FROM invoices
– )
SELECT *
FROM clients
LEFT JOIN invoices USING (client_id)
WHERE invoice_id IS NULL

A

Same results

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Given the tables involved explain this query

– Find customers who have ordered lettuce (id=3)
– Select customer_id, first_name, last_name
select *
from customers
where customer_id in (
select o.customer_id
from order_items oi
join orders o using (order_id)
where product_id = 3
)

A

subquery with a join on customer_id and order_id on the different tables

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Given the same two table explain this query

select distinct customer_id,
first_name,
last_name
from customers c
join orders o using (customer_id)
join order_items oi using (order_id)
where oi.product_id = 3

A

The two joins here are clear and filtered result from the where clause, easier reading than a subquery route and distinct being used cause a customer appeared more than once in the query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Memorize the steps here and understand the question being asked.

    • select invoices larger than all invoices of
    • client 3
A

Returning all the clients that have a invoice_total > than client #3

select *
from invoices
where invoice_total > (
select max(invoice_total)
from invoices
where client_id = 3
)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Will this query work without the ALL clause?

– select invoices larger than all invoices of
– client 3
select *
from invoices
where invoice_total > all (
select invoice_total
from invoices
where client_id = 3
)

A

No, the ALL clause is calculating all the values instead of just returning a single value in a where clause

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

True or False

the MAX and ALL sql clauses are comprable

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is the count(*) clause doing here?

– select clients with at least two invoices

select *
from clients
where client_id IN (
select client_id
from invoices
group by client_id
having count(*) >= 2
)

A

count everything, can see the direct results by including it in the select clause

select client_id, count(*)

then remove the count(*) column with the not needed tally

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

True or False, this query is equalivalent

WHERE client_id IN (subquery)

WHERE client_id = ANY (subquery)

A

True same results

17
Q

This is an example of what type of sql query?

select *
from employees e
where salary > (
select avg(salary)
from employees
where office_id = e.office_id
)

A

Correlated query, meaning that the outter sub query will be executed based on the number of records being returned. This not returning a single avg across the entire table but avg based on office_id.

18
Q

Exercise

    • Get invoices that are larger than the
    • clients average invoice amount
A

select *
from invoices i
where invoice_total >(
select avg(invoice_total)
from invoices
where client_id = i.client_id
)

19
Q

True or False

In the following query the bold portion is considered the inner join

select *
from invoices i
where invoice_total >(
select avg(invoice_total)
from invoices
where client_id = i.client_id
)

A

False

20
Q

True or False

In the following query the bold portion is considered the outter join

select *
from invoices i
where invoice_total >(
select avg(invoice_total)
from invoices
where client_id = i.client_id
)

A

False

21
Q

Refactor this query using the EXISTS clause.

– select clients that have an invoice

select *
from clients
where client_id IN (
select distinct client_id
from invoices
)

A

select *
from clients c
where exists (
select client_id
from invoices
where client_id = c.client_id
)

22
Q

True or False

The EXISTS clause is much more efficient than the IN clause

A

True

23
Q

Explain why the EXISTS clause is more efficient than the IN clause?

select *
from clients c
where exists (
select client_id
from invoices
where client_id = c.client_id
)

A

the boolean is first returned to the EXISTS clause, then the subquery where clause is performed, unlike the

where client_id IN

would return all the records no matter how many rows are affected

24
Q

Exercise

    • find the products that have never been ordered
    • use the sql_store database
A

select *
from products p
where not exists(
select product_id
from order_items
where product_id = p.product_id
)

25
Q

Write this SELECT subquery to return this table

A

select
invoice_id,
invoice_total,
(select avg(invoice_total)
from invoices) as invoice_average,
invoice_total - (select invoice_average) as difference
from invoices

26
Q

Write this SELECT subquery to return this table

ERROR!

A

select
client_id,
name,
(select sum(invoice_total)
from invoices
where client_id = c.client_id) as total_sales
(select avg(invoice_total) from invoices) as average
(select total_sales - average) as difference
from clients c

27
Q

True or False

You can use an alias in a sql function

A

False

28
Q

Write a select subquery that would return this result

A

select
invoice_id,
invoice_total,
(select avg(invoice_total)
from invoices) as invoice_average,
invoice_total - (select invoice_average) as difference
from invoices

29
Q

Exercise

Write a query that would return this result. Total sales are calculated per client.

A

select
client_id,
name,
(select sum(invoice_total)
from invoices
where client_id = c.client_id) as total_sales
from clients c

30
Q

Just study how to do this

select
client_id,
name,
(select sum(invoice_total)
from invoices
where client_id = c.client_id) as total_sales,
(select avg(invoice_total) from invoices) as average,
(select total_sales - average) as difference
from clients c

A

notice no aggregate funcs is being performed on the alias’s

select
client_id,
name,
(select sum(invoice_total)
from invoices
where client_id = c.client_id) as total_sales,
(select avg(invoice_total) from invoices) as average,
(select total_sales - average) as difference
from clients c

31
Q

True or False

When using a subquery in the FROM clause an alias is always required?

A

True, even if the alias isn’t being used

select *
from (
select
client_id,
name,
(select sum(invoice_total)
from invoices
where client_id = c.client_id) as total_sales,
(select avg(invoice_total) from invoices) as average,
(select total_sales - average) as difference
from clients c
) as what_the_fuck

32
Q

Just study this from subquery, it’s best to keep sales_summary as a view to use on other virtual tables.

A

select *
from (
select
client_id,
name,
(select sum(invoice_total)
from invoices
where client_id = c.client_id) as total_sales,
(select avg(invoice_total) from invoices) as average,
(select total_sales - average) as difference
from clients c
) as sales_summary
where total_sales is not null

MYSQL (19 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions