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

```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.

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