Complete SQL With MySQL - SQL Bootcamp - SQL Interview Q & A

Here I define the term DBMS - DataBase Management System. Student of SQL must have basic understanding of the term DBMS

Defining the term RELATION against TABLE - Important properties of RDBMS - Data redundancy and how to avoid it

What is SQL - Origin of SQL - Properties of SQL as language - Classification of SQL statements - Brief history of SQL

Download and Installation of the current version of MySQL DBMS from official website - Step-by-Step instructions

Downloading the sample schema package EMPLOYEE from MySQL official website - Step by step installation from MySQL workbench GUI tool - Testing the installation

Connect to a database schema - USE <database> command - Understand the basic structure of SELECT statement - WHERE clause - Displaying entire row with * projection

Study of logical operator AND in SQL to combine two conditions, which must be met to list the rows, with examples.

Study of logical operator OR in SQL to combine two conditions, either of which must be met to list the rows, explained with examples.

Combined use of AND & OR. Rule of Precedence in respect of AND & OR. Alter the use by using brackets

IN operator as replacement of multiple OR operator used for comparison of common expression with different values for equality. NOT IN the negation of IN operator by preceding it with NOT.

How to deal with range criteria in the WHERE clause of SELECT statement. Use & limitations of BETWEEN operators. NOT BETWEEN operator.

Learn how to search the data from the tables based on NULL values. Also important are the properties of NULL, explained with examples.

Learn about search based on patterns of characters - wildcards % and _ - Explained with examples

Sorting the data in the given order by using ORDER BY clause - Important properties of ORDER BY

LIMIT operator to limit the number of rows to display and skip - How to implement pagination when table contains a large number of rows.

MySQL proprietary operator || as Logical OR. Explained with examples.

Providing list of columns to display in SELECT statement - Projection

Providing meaningful names to the columns in the result of the SELECT query - Using column aliases - Multi-word column aliases - Example

Performing math in SQL SELECT statement - Using arithmetic operators in query

DISTINCT Operator - Suppress duplication in the listing - Properties of DISTINCT clause

NULL values in SQL expressions - How to use IFNULL() function - Example

Dealing with multiple NULL values - COALESCE() Function - Similarity and differences between COALESCE and NULLIF functions - Working examples

String concatenation function CONCAT() - Enable pipe concatenation operator - Effect of NULL string on concatenation - Practical examples

Retrieving the system date and timestamp - Default format for display of the date  - Functions CURDATE(), CURRENT_DATE, CURRENT_TIMESTAMP, DATE_FORMAT - Displaying the date in different formats - Separating the date components

How to perform date and time arithmetic with examples - Temporal operator INTERVAL

Understanding the  basic nested query execution - outlining the steps for solving complex problems - Sub-query example

Deal with multiple values returned by subquery with IN operator - Error code 1242 Subquery returns more than 1 row

Comparing multiple values as set in the subquery - Positional correspondence of values in set with subquery projection

Correlated subquery technique - Operation and properties of correlated subquery - When and when not to use explained with example

Boolean operator EXISTS and its negation NOT EXISTS - Typical use of correlated subquery with EXISTS operator with case example

How to handle the situation when there is possibility of a sub-query returning NULL - Work around explained with example

SQL ALL operator as replacement for multiple AND operators and particularly suitable for > and < comparison operators

SQL ANY operator as replacement of multiple OR operators and particularly suitable for > and < comparison operators

Basic join operation using INNER JOIN clause - NATURAL JOIN clause as replacement for INNER JOIN - Equijoin method by applying explicitly equality between join columns of two tables in predicate

Applying filter to the JOIN queries - Appending WHERE clause to the INNER JOIN query - Appending WHERE clause to the NATURAL JOIN clause - Appending a filter condition to the EQUI-JOIN query

SQL join based on range or non-equality condition - Applying filter criteria to non-equijoin query

SQL OUTER JOIN operation to list extraneous data from either of the tables involved in join - The rows for which there is no matching row in another table.

MySQL does not FULL JOIN option - Learn how to simulate the effect of FULL JOIN with UNION operator - Combining the results of two queries by using UNION operator

MySQL Self Join Operation - Use of Self Join Operation - Solving problem using self join like operation

Introduction to group functions - MAX(), MIN(). SUM(), AVG(), COUNT() - Properties of group functions

Learn about very basic use of group functions - Also learn how to deal with the NULLable column - Experience the property of group functions ignore NULL and its effect

MySQL group function WHERE clause use to find the aggregation over one single group of rows by providing the condition - Explained with an example

Use of GROUP BY clause - Why use GROUP BY clause - Properties of GROUP BY clause - Example of use

Use of HAVING clause with group function - Important properties of HAVING clause

Interview challenge - How to find Maximum of Average - Tricky solution by using In-Line view - Use of in-line view

How to use group function along with join operation in a SELECT statement - Solution to an interview challenge

Using group functions in different modes of subqueries - Examples & explanation

Creating a table including first creating a database and switching to it from scratch. Columns, data types and the database engine. Resources to more information about data types and database engines

Process of creating a table from an existing table explained - Copying the columns and data from the source table into new table with creation - All the different methods demonstrated

Adding of single or multiple columns to an existing column with or without specifying a position.

Learn how to drop single or multiple columns with or without data being present.

Copying the whole column structure from source with creating new table without importing data from source.

How to drop single or multiple tables at once. DROP TABLE Statement.

You will learn the the concept of data integrity - definition of data integrity - types of data integrity

Learn how to create a table with primary key constraint defined on a column - Table level and column level definition of the constraint - Properties of Primary Key constraint

Learn how to drop or add primary key or any constraint to an existing table with or without data - Limitations on adding constraint when the data is present in the table

Learn how to create UNIQUE constraint - Properties and behaviour of UNIQUE constraint

Learn about how to create CHECK constraint and properties of CHECK constraint explained with example

Learn how to create, add and drop default constraint - Specify a default value for a column - Working examples

Learn about NOT NULL constraint - How to combine the NOT NULL constraint with DEFAULT constraint - Working examples

Learn about Referential Integrity in MySQL - Implementation of FOREIGN KEY - Default behavior of FOREIGN KEY - Primary Key & Foreign Key relationship.

Learn about ON DELETE CASCADE clause as extension to FOREIGN KEY constraint - Conditions which lead to use the clause - Actual demonstration with an example

Learn about ON DELETE SET NULL clause as extension to FOREIGN KEY constraint - Circumstances which lead to the use of this clause - Actual demonstration with example

Learn about the FOREIGN KEY ON UPDATE CASCADE clause and the circumstances which lead to using it - Example included