testmysql.tar.gz

sql spa

Relational databases store data in a row-based table structure. Structured Query Language (SQL) is a core language used to retrieve data from the relational database. One of its most widely used extensions, MySQL, also gives you the power to edit, create, and manage the queried data.

In this course, you’ll be introduced to the basics of relational database management with SQL. You’ll use MySQL to create a table, edit the data, and even change the structure. You’ll also create indexes to quickly access data for faster performance on common queries. Additionally, you’ll explore more complex queries to extract useful data with aggregate functions, grouping, ordering, and creating limits and filters in output tables. Finally, you’ll learn how to combine data from different tables with join functions.

By the end of this course, you’ll be able to create, manage, edit, and merge relational databases with confidence. MySQL will prepare you for data projects large and small.

Getting Started with SQL and Relational Databases

```
select * from customers join orders on orders.customer_id = customers.id;
```
      

```
select * from customers join orders on customers.id = orders.customer_id;
```


Assume that we have two tables, `customers` and `orders`, as follows:
 ``` sql
1) create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
2) create table orders (id int(11) not null auto_increment, customer_id int(11) not null, order_number varchar(255) not null, primary key(id));
3) alter table orders add constraint orders_customer_fk foreign key(customer_id) references customers(id);
```
What is the command to retrieve all the customers together with their orders?

Assume that we have two tables, <code>customers</code> and <code>orders</code>, as follows:
<pre><code class="language-sql">1) create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
2) create table orders (id int(11) not null auto_increment, customer_id int(11) not null, order_number varchar(255) not null, primary key(id));
3) alter table orders add constraint orders_customer_fk foreign key(customer_id) references customers(id);
</code></pre>
What is the command to retrieve all the customers together with their orders?

Assume that we have two tables, `customers` and `orders`, as follows:

 ``` sql
1) create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
2) create table orders (id int(11) not null auto_increment, customer_id int(11) not null, order_number varchar(255) not null, primary key(id));?
3) alter table orders add constraint orders_customer_fk foreign key(customer_id) references customers(id);
```
Also assume that the first customer has three orders and the second customer has five orders. How many rows will the following `select` query bring?

  ```
        select * from customers join orders on orders.customer_id = customers.id;
   ```


Assume that we have two tables, <code>customers</code> and <code>orders</code>, as follows:
<pre><code class="language-sql">1) create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
2) create table orders (id int(11) not null auto_increment, customer_id int(11) not null, order_number varchar(255) not null, primary key(id));?
3) alter table orders add constraint orders_customer_fk foreign key(customer_id) references customers(id);
</code></pre>
Also assume that the first customer has three orders and the second customer has five orders. How many rows will the following <code>select</code> query bring?
<pre><code> select * from customers join orders on orders.customer_id = customers.id;
</code></pre>

Also assume that the first customer has three orders and the second customer has five orders. How many rows will the following `select` query bring?

 ``` sql
        select * from customers join orders on orders.customer_id = customers.id;
```
**(True or False)** It won’t bring the customers that have never placed an order.


Also assume that the first customer has three orders and the second customer has five orders. How many rows will the following <code>select</code> query bring?
<pre><code class="language-sql"> select * from customers join orders on orders.customer_id = customers.id;
</code></pre>
(True or False) It won’t bring the customers that have never placed an order.

```
select count(*) from customers join orders on orders.customer_id = customers.id;
```

```
select count(distinct customers.id) from customers join orders on orders.customer_id = customers.id;
```

Which is the correct SQL statement to count the customers that have placed at least one order?
   


Which is the correct SQL statement to count the customers that have placed at least one order?

```
select * from customers left join orders on orders.customer_id = customers.id where orders.id is null;
```

```
select * from customers join orders on orders.customer_id = customers.id;
```

```
select * from customers left join orders on orders.customer_id = customers.id;
```

```
select * from customers left join orders on orders.customer_id = customers.id where orders.id is not null;
```

Which `select` statement brings customers both with and without an order?


Which <code>select</code> statement brings customers both with and without an order?

It returns the order IDs alongside their total amount.
     

What does this query do?
     
```sql
select sum(order_items.price * order_items.quantity) as total_amount,
order_id from order_items
group by order_id;
```
    


What does this query do?
<pre><code class="language-sql">select sum(order_items.price * order_items.quantity) as total_amount,
order_id from order_items
group by order_id;
</code></pre>

The following SQL statement contains how many SQL subqueries?
``` sql
select customers.first_name from customers join orders on orders.customer_id = customer.id;
```

The following SQL statement contains how many SQL subqueries?
<pre><code class="language-sql">select customers.first_name from customers join orders on orders.customer_id = customer.id;
</code></pre>

The following SQL statement contains how many SQL subqueries?
``` sql
 select * from orders join(
select sum(order_items.price * order_items.quantity) as total_amount, order_id from order_items group by order_id) orders_total_amounts on orders.id = orders_total_amounts.order_id;
 ```
 

The following SQL statement contains how many SQL subqueries?
<pre><code class="language-sql"> select * from orders join(
select sum(order_items.price * order_items.quantity) as total_amount, order_id from order_items group by order_id) orders_total_amounts on orders.id = orders_total_amounts.order_id;
</code></pre>

It brings one row with the least total amount order.



It brings one row with the large total amount order.



It brings all the orders with the large one on top.

It brings all the orders with the least one on top.

What does the following query bring?
``` sql
select sum(order_items.price * order_items.quantity) as total_amount, order_id from order_items
group by order_id order by total_amount desc limit 1;
 ```
  

What does the following query bring?
<pre><code class="language-sql">select sum(order_items.price * order_items.quantity) as total_amount, order_id from order_items
group by order_id order by total_amount desc limit 1;
</code></pre>

It brings the order header of the top 10 large orders.

It brings the details of the top 10 large orders.

It brings only one row with the header details of the least order.

It brings only one row with the header details of the large order.

What does the following query bring?
``` sql
select orders.* from orders join (select sum(order_items.price order_items.quantity) as total_amount, order_id from order_items group by order_id order by total_amount desc limit 1) as best_order on orders.id = best_order.order_id;
```
   

What does the following query bring?
<pre><code class="language-sql">select orders.* from orders join (select sum(order_items.price order_items.quantity) as total_amount, order_id from order_items group by order_id order by total_amount desc limit 1) as best_order on orders.id = best_order.order_id;
</code></pre>

Test your understanding of joins and subqueries.


Quiz on Joins and Subqueries

Test your understanding of joins and subqueries.

Getting Started

Manage MySQL Server

Basic SQL Commands

Add Columns and Indexes

Count and Sort Records

Foreign Keys

Joins, Left Joins And Subqueries

Conclusion

Quiz on Joins and Subqueries