SQL Tutorial: Essential SQL

---
Hi, my name is Dima, and I am excited to welcome you my SQL course.

In this course you will find four chapters that cover a wide range of SQL skills which you can leverage when working with real-world data.

In the first chapter, you will review some of the most commonly used SQL commands to ensure you are prepared to tackle both practical problems as well as every exercise covered in this course.

In chapter two you will learn new techniques you can use to find the data you need to answer real world questions.

Chapter three will teach you how to manage the data in your database.

Finally, in chapter four you will learn how to use best practices when writing your SQL scripts.

Throughout the course you will work with a modified version of a tutorial database named Pagila. This database emulates a collection of tables you would find in a DVD rental store. The ten tables, shown here, range from actor information to customer payments. You will work with this database to sharpen your SQL skills for real-world applications.

So let's begin with a review of the most essential SQL commands.

We will work with the query shown here.

This query returns the title and the length of the longest running family-friendly documentaries.

Let's dissect this query see what makes it work.

Virtually every SQL statement for returning data from a database will have a SELECT and a FROM command.

The SELECT command must be followed by a comma-separated list of the names of the columns you want to be returned from your query. You can also use the star character to return all columns.

The FROM command specifies the table that will be used in your query.

In this portion of the query we specify that the columns, title and length are selected from the film table.

To add on additional tables to this query we need to use a JOIN statement.

Here an INNER JOIN is used to bring in category information about films in order to identify which are documentaries.

To effectively use a join you need to first ensure that your first table has a clear alias by using the AS command.

Then you need to specify the type of join your are using, in this case an INNER JOIN. For this command you need to specify the table you are joining to and the alias you are assigning to this new table. An inner join returns the records that exist in both tables based on the columns used to join them. In this course we will exclusively rely on INNER JOINS.

Finally, you need to specify the columns that will be used to merge the two tables together by using the ON command. This syntax leverages the aliases you defined to clearly specify the columns used for the merge.

Next, a WHERE statement is used to filter the results in order to return the Documentaries that have family-friendly ratings, in our case G and PG.

For this filter a WHERE command is used and is followed by the condition that must be met.

To add additional conditions, in this case the ratings, the AND command is used.

Finally the IN command is used to create a list of items to filter the results.

Since we are interested in returning the longest running films we need to sort our results by length.

This is accomplished by using the ORDER BY command which must be followed by the column name used for sorting.

ORDER BY sorts in ascending order by default so in order to return the longest running films we need to use the DESC command to ensure the sort is descending instead.

Putting all of these elements together returns the title and lengths of the longest running family-friendly documentaries.

Now it's your turn to work with this database to practice these commonly used SQL commands.

#SQLTutorial #DataCamp #SQL #RealWorld #Problems