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

What type of column do we want to hold time stamp values?


What type of column do we want to hold time stamp values?

The type of a column that is a foreign key to another table/column needs to have the same ________ as the destination column.

The type of a column that is a foreign key to another table/column needs to have the same ________ as the destination column.

This is a command to create a `customers` table. Which column is optional?

```
create table customers (id int(11) not null auto_increment, first_name varchar(255) not null, last_name varchar(255) not null, primary key(id));
```

This is a command to create a <code>customers</code> table. Which column is optional?
<pre><code>create table customers (id int(11) not null auto_increment, first_name varchar(255) not null, last_name varchar(255) not null, primary key(id));
</code></pre>

**(True or False)** The following sequence of commands will end with an error because it tries to insert an order for a nonexistent customer:

```
create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
```
       
```
create table orders (id int(11) not null auto_increment, customer_id int(11) not null, primary key(id));
```

```
insert into orders (customer_id) values (15);
```

(True or False) The following sequence of commands will end with an error because it tries to insert an order for a nonexistent customer:
<pre><code>create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
</code></pre>
<pre><code>create table orders (id int(11) not null auto_increment, customer_id int(11) not null, primary key(id));
</code></pre>
<pre><code>insert into orders (customer_id) values (15);
</code></pre>

```
create index orders_order_number_uidx on orders(order_number);
```
     

```
create unique index orders_order_number_uidx orders(order_number);
```
     

```
create unique index orders_order_number_uidx on orders(order_number);
```
    

```
create unique index orders_order_number_uidx on orders;
```

Which command creates a unique index on the `order_number` column of the `orders` table?

Which command creates a unique index on the <code>order_number</code> column of the <code>orders</code> table?

**(True or False)** The following sequence of statements will fail:

```
 create table orders (id int(11) not null auto_increment, order_number varchar(255) not null, primary key(id));
```
 
```
create unique index orders_order_number_uidx on orders(order_number);
```

  ```
insert into orders (order_number) values ('ABC00001');
```

  ```
insert into orders (order_number) values ('ABC00001');
```

(True or False) The following sequence of statements will fail:
<pre><code> create table orders (id int(11) not null auto_increment, order_number varchar(255) not null, primary key(id));
</code></pre>
<pre><code>create unique index orders_order_number_uidx on orders(order_number);
</code></pre>
<pre><code>insert into orders (order_number) values ('ABC00001');
</code></pre>
<pre><code>insert into orders (order_number) values ('ABC00001');
</code></pre>

**(True or False)** After the three statements, the `customer_id` column of the `orders` table will be a foreign key to the `id` column of the `customers` table:

```
 alter table orders add constraint orders_customer_fk foreign key(customer_id) references customers(id);
```

```
create table orders (id int(11) not null auto_increment, customer_id int(11) not null, primary key(id));
```


```
create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
```


 

(True or False) After the three statements, the <code>customer_id</code> column of the <code>orders</code> table will be a foreign key to the <code>id</code> column of the <code>customers</code> table:
<pre><code> alter table orders add constraint orders_customer_fk foreign key(customer_id) references customers(id);
</code></pre>
<pre><code>create table orders (id int(11) not null auto_increment, customer_id int(11) not null, primary key(id));
</code></pre>
<pre><code>create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
</code></pre>

**(True or False)** The following sequence of commands will fail:

```
create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
```
        
```
create table orders (id int(11) not null auto_increment, customer_id int(11) not null, primary key(id));
```

```
alter table orders add constraint orders_customer_fk foreign key(customer_id) references customers(id);
```

```
insert into customers (name) values ('Panos');
```

```
select * from customers;
```

        
```
insert into orders (customer_id) values (1);
```

(True or False) The following sequence of commands will fail:
<pre><code>create table customers (id int(11) not null auto_increment, name varchar(255) not null, primary key(id));
</code></pre>
<pre><code>create table orders (id int(11) not null auto_increment, customer_id int(11) not null, primary key(id));
</code></pre>
<pre><code>alter table orders add constraint orders_customer_fk foreign key(customer_id) references customers(id);
</code></pre>
<pre><code>insert into customers (name) values ('Panos');
</code></pre>
<pre><code>select * from customers;
</code></pre>
<pre><code>insert into orders (customer_id) values (1);
</code></pre>

In this lesson, we will test our understanding related to foreign keys.

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 Foreign Keys