Uncover the essentials of MySQL server, including its setup, SQL commands, data manipulation, and advanced topics like joins and subqueries.

Computer Science Bootcamp

Relational database management system (RDBMS) enables the creation, management, and organization of relational databases. At the same time, Structured Query Language (SQL) is a standardized programming language used to interact with databases, perform data manipulation, retrieve information, and construct complex queries to extract valuable insights from the stored data. This module will teach us to set up and communicate with the MySQL server. We’ll then delve into fundamental SQL commands, such as creating databases and tables and manipulating data. Next, we’ll explore the topics like adding columns and indexes and working with foreign keys. Additionally, we’ll gain expertise in fetching data from multiple tables using joins and subqueries. By the end of this module, we’ll have gained a comprehensive understanding of MySQL server, empowering us to advance our career by developing robust and efficient database solutions.

Relational Databases and SQL

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.

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