Gain insights into SQL by exploring database creation, data manipulation, and advanced queries. Learn about practical applications and practice for SQL interviews to build confidence and readiness.

sql.tar.gz

The ability to work SQL is becoming an increasingly in-demand skill, both for software developers and people in less technical roles. If you’re interested in learning SQL and have no prior experience with it, then this course will be your light in a dark tunnel.

You’ll start by covering the basics of SQL such as how to create a database, how to insert, query, and update data. You’ll also learn fundamental concepts that developers and data scientists use everyday such as multi-table operations, nested queries, and how to set up views. 

Throughout, you’ll get to execute SQL queries in your browser and see results in real-time - you won’t need to worry about set-up.

At the end of this course, you’ll also get some hands-on practice with common SQL interview questions, so when the time comes, you’ll be ready and confident to answer any question that comes your way. Let’s get started!

An Introductory Guide to SQL

In the previous lesson we worked with a very simple create table statement. In this lesson we'll gradually apply more restrictions on what our table can accept.

>Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command **./DataJek/Lessons/4lesson.sh** and wait for the mysql prompt to start-up.

-- The lesson queries are reproduced below for convenient copy/paste into the terminal. 

-- Query 1
CREATE TABLE Actors (
Id INT AUTO_INCREMENT,
FirstName VARCHAR(20) NOT NULL,
SecondName VARCHAR(20) NOT NULL,
DoB DATE NOT NULL,
Gender ENUM('Male','Female','Other') NOT NULL,
MaritalStatus ENUM('Married', 'Divorced', 'Single', 'Unknown') DEFAULT "Unknown",
NetWorthInMillions DECIMAL NOT NULL,
PRIMARY KEY (Id));

-- Query 2
CREATE TABLE IF NOT EXISTS Actors (
Id INT AUTO_INCREMENT,
FirstName VARCHAR(20) NOT NULL,
SecondName VARCHAR(20) NOT NULL,
DoB DATE NOT NULL,
Gender ENUM('Male','Female','Other') NOT NULL,
MaritalStatus ENUM('Married', 'Divorced', 'Single', 'Unknown') DEFAULT "Unknown",
NetWorthInMillions DECIMAL NOT NULL,
PRIMARY KEY (Id));


1. We'll want to restrict a user from adding duplicate rows. 

   In a relational database we can uniquely identify a row by designating a single column or a set of columns as the primary key. 

   We discuss the concept of a primary key in depth in a later lesson but for now, it suffices to know that a column or multiple columns acting as the primary key serve to uniquely identify each row in a table. In the case of our __Actors__ table, we may be tempted to choose the combination of the first name and the second name as the primary key. However, there is a remote possibility that two actors have the same first and last names. In fact, even if we choose all the columns to form the primary key, an obviously bad design choice, we'll not be assured each row can be uniquely identified. One way out of this predicament is to add a column that assigns a numeric ID starting from one that is incremented each time we add a new row. Since every row will be associated with a different integer, we can be assured that every row can be uniquely identified using the ID column.

2. If we create a new column ID and designate it as the primary key for the __Actors__ table, then we must know the highest value of the ID column in the table, add one to it and insert the result as the Id of a new row. MySQL provides a feature __AUTO_INCREMENT__ that automatically assigns the next integer in the sequence to the ID column of a new row.

   The auto increment sequence begins at 1 for an empty table. The following restrictions exist when using __AUTO_INCREMENT__ feature:

* There can be only one column marked as __AUTO_INCREMENT__ in a table. 
* The __AUTO_INCREMENT__ column can’t have a default value.
* The __AUTO_INCREMENT__ column must be indexed.

   The __AUTO_INCREMENT__ feature isn’t portable to other databases and the counter is reset when we truncate or drop a table.

3. The next restriction we want to put on the users of __Actors__ table is to disallow inserting null as a column value. We can mark a column as __NOT NULL__ to achieve this purpose.

4. Imagine a situation for the __Actors__ table where you want to add a record to the table but aren’t aware of the marital status of the Actor. In such a scenario, we can set a default value for a particular column using the __DEFAULT__ keyword. The default value specified must be a constant. The default value will be used whenever a user omits a value for a column that has a default specified. We’ll recreate the table __Actors__ and add another enum value "Unknown" to the list. We’ll also mark other columns as __NOT NULL__ so that users are forced to provide valid values for other columns.

   >We can also use __NOT NULL__ and __DEFAULT__ together.

5. The following MYSQL statement adds all the restrictions we just talked about:

   ```mysql
   CREATE TABLE Actors (
   Id INT AUTO_INCREMENT,
   FirstName VARCHAR(20) NOT NULL,
   SecondName VARCHAR(20) NOT NULL,
   DoB DATE NOT NULL,
   Gender ENUM('Male','Female','Other') NOT NULL,
   MaritalStatus ENUM('Married', 'Divorced', 'Single', 'Unknown') DEFAULT "Unknown",
   NetWorthInMillions DECIMAL NOT NULL,
   PRIMARY KEY (Id));
   ```

6. Contrast the output of the __DESC__ command from the previous lesson below:


This lesson continues the discussion on creating tables and how to add restrictions when defining them.

Introduction

Basic SQL

Joins

Nested Queries

Multi Table Operations

Views

Stored Procedures

Triggers

Miscellaneous

Practice & Prep

Epilogue

... continue