Gain insights into relational databases and SQL. Learn about creating, managing, and editing tables using MySQL. Explore complex queries, indexes, and join functions for efficient data handling.

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

`create table items (item_id int(11) not null auto_increment, description varchar(255) not null, rate numeric(5,2) not null, primary key(item_id));`

<code>create table items (item_id int(11) not null auto_increment, description varchar(255) not null, rate numeric(5,2) not null, primary key(item_id));</code>

 Create the `items` table. Items should have
        `item_id` as the `primary key (integer, not null and auto incremented)`,
        a `description` of type `varchar` with maximum length of 255, and the `rate` of
        type `numeric` with `5` digits before and `2` digits after decimal point. Both `description` and `rate` should not be `null`.



Create the <code>items</code> table. Items should have
<code>item_id</code> as the <code>primary key (integer, not null and auto incremented)</code>,
a <code>description</code> of type <code>varchar</code> with maximum length of 255, and the <code>rate</code> of
type <code>numeric</code> with <code>5</code> digits before and <code>2</code> digits after decimal point. Both <code>description</code> and <code>rate</code> should not be <code>null</code>.

`create unique index items_description_uidx on items(description);`

<code>create unique index items_description_uidx on items(description);</code>

Write a command that creates a unique index on the `description` column of the `items` table.




Write a command that creates a unique index on the <code>description</code> column of the <code>items</code> table.

<code>select count(*) from items;</code>

Write a query that counts the entries in the `items` table.


Write a query that counts the entries in the <code>items</code> table.

`select count(*) from items where rate > 10;`

<code>select count(*) from items where rate &gt; 10;</code>

Write a command that returns the number of items whose `rate` is greater than `10`.




Write a command that returns the number of items whose <code>rate</code> is greater than <code>10</code>.

`select * from items order by description;`

<code>select * from items order by description;</code>

Write a query to get the list of items ordered by the `description` attribute.




Write a query to get the list of items ordered by the <code>description</code> attribute.

`select * from items order by rate desc;`

<code>select * from items order by rate desc;</code>

Assuming that the `items` table has a `rate` column, how will you get the items sorted by `rate` with the most expensive result at the top?




Assuming that the <code>items</code> table has a <code>rate</code> column, how will you get the items sorted by <code>rate</code> with the most expensive result at the top?

`select * from items order by rate desc limit 1;`

<code>select * from items order by rate desc limit 1;</code>

How can you get the most expensive item from the list of `items`? 
>Hint: Limit the result that `select` will reply back to you.




How can you get the most expensive item from the list of <code>items</code>?
<blockquote>
Hint: Limit the result that <code>select</code> will reply back to you.
</blockquote>

Test your understanding of the create table, index, count, where, and order by commands.

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 Counting, Sorting, and Limiting Results