Gain insights into using SQL for relational databases. Learn about creating, editing, and managing tables with MySQL, creating indexes, complex queries, and joining tables for optimized data retrieval.

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

```mysql
alter students add column (address varchar(255));
```
      

```mysql
alter table students add column (address varchar(255) not null);
```
     

 ```mysql
alter table students insert column (address varchar(255) not null);
```
    

 ```mysql
alter table students add one column (address varchar(255) not null);
```

Assuming that we have the `students` table, what is the command to add a new column named `address` of the `varchar(255)` type and that can’t be null?


Assuming that we have the <code>students</code> table, what is the command to add a new column named <code>address</code> of the <code>varchar(255)</code> type and that can’t be null?


```mysql
create index students_last_name_first_name_idx on students(last_name, first_name);
```

```mysql
create unique index customers_name_uidx on customers(name);
```

```mysql
create index students_last_name_first_name_idx on (last_name, first_name);
```

```mysql
create index students_last_name_first_name_idx on students(name);
```

Assume that we have the `students` table with `id`, `first_name`, and `last_name` columns. Which command would create an index on both `last_name` and `first_name`?

Assume that we have the <code>students</code> table with <code>id</code>, <code>first_name</code>, and <code>last_name</code> columns. Which command would create an index on both <code>last_name</code> and <code>first_name</code>?

How many indexes in the following table definition have we covered?

 ```
          CREATE TABLE `orders` (
          `id` int(11) NOT NULL AUTO_INCREMENT,
          `ordered_at` datetime NOT NULL,
          `created_at` datetime NOT NULL,
          `updated_at` datetime NOT NULL,
          `state_responsible_id` int(11) DEFAULT NULL,
          `customer_id` int(11) DEFAULT NULL,
          `customer_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
          `brand_id` int(11) DEFAULT NULL,
          `country_id` int(11) DEFAULT NULL,
          `language_id` int(11) DEFAULT NULL,
          `currency_id` int(11) DEFAULT NULL,
          PRIMARY KEY (`id`),
          KEY `orders_state_responsible_fk` (`state_responsible_id`),
          KEY `customer_order_idx` (`customer_id`,`customer_type`),
          KEY `orders_brand_fk` (`brand_id`),
          KEY `orders_country_fk` (`country_id`),
          KEY `orders_language_fk` (`language_id`),
          KEY `orders_currency_fk` (`currency_id`),
          CONSTRAINT `orders_brand_fk` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`),
          CONSTRAINT `orders_country_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`),
          CONSTRAINT `orders_currency_fk` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
          CONSTRAINT `orders_language_fk` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`),
          CONSTRAINT `orders_state_responsible_fk` FOREIGN KEY (`state_responsible_id`) REFERENCES `people` (`id`)
          ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
 ```


How many indexes in the following table definition have we covered?
<pre><code> CREATE TABLE `orders` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ordered_at` datetime NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `state_responsible_id` int(11) DEFAULT NULL,
 `customer_id` int(11) DEFAULT NULL,
 `customer_type` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
 `brand_id` int(11) DEFAULT NULL,
 `country_id` int(11) DEFAULT NULL,
 `language_id` int(11) DEFAULT NULL,
 `currency_id` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `orders_state_responsible_fk` (`state_responsible_id`),
 KEY `customer_order_idx` (`customer_id`,`customer_type`),
 KEY `orders_brand_fk` (`brand_id`),
 KEY `orders_country_fk` (`country_id`),
 KEY `orders_language_fk` (`language_id`),
 KEY `orders_currency_fk` (`currency_id`),
 CONSTRAINT `orders_brand_fk` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`),
 CONSTRAINT `orders_country_fk` FOREIGN KEY (`country_id`) REFERENCES `countries` (`id`),
 CONSTRAINT `orders_currency_fk` FOREIGN KEY (`currency_id`) REFERENCES `currencies` (`id`),
 CONSTRAINT `orders_language_fk` FOREIGN KEY (`language_id`) REFERENCES `languages` (`id`),
 CONSTRAINT `orders_state_responsible_fk` FOREIGN KEY (`state_responsible_id`) REFERENCES `people` (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ;
</code></pre>

 You should try to delete the customers that aren’t active.
    

You should try to `add column` to the table.


You should introduce an index on the `name` column of the `customers` table.
      

You should upgrade the MySQL server version.


You see that your query to retrieve a customer by `name` takes a long time to finish. What should you look for when trying to solve this problem?


You see that your query to retrieve a customer by <code>name</code> takes a long time to finish. What should you look for when trying to solve this problem?

Test your knowledge about indexes and add new columns.



Quiz on Indexing

Test your knowledge about indexes and add new columns.

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 Indexing