Practical Database Guide with RDBMS(MySQL) & NoSQL(MongoDB)

Name: Practical Database Guide with RDBMS(MySQL) & NoSQL(MongoDB)
Rating: 4.4 (113 reviews)

Data Types, Functions, Operators, JOINS, Store Procedures, Indexing with real-world interview questions, assignments...

Created byNirmal Joshi

Last updated 10/2024

English

What you'll learn

Best of both database types - RDBMS(MySQL) & NoSQL(MongoDB)
Basic to advanced SQL queries with complex data-structures
NoSQL database structure designing using MongoDB
Practical hands-on assignments with their solutions
Database Indexing, Stored Procedures & Transactions…
Prepare for real- world interview questions

Course content

17 sections • 103 lectures • 7h 16m total length

Installing MySQL on Windows3:55
Installing MySQL on macOS3:00
SQLYog Installation2:00
SQLYog Walkaround & Executing Basic Queries6:24

MySQL DataTypes4:33
Relative Questions:
1. What is the concept of data type and why do you need to define it with columns?
Numeric DataTypes5:20
Relative Questions:
1. What are the different numeric datatypes supported by MySQL?
Numeric DataTypes - Decimal3:46
Relative Questions:
1. How will a decimal type store value if the size given is (5,2)?
Numeric Datatypes - Double & Float, Bool, Boolean4:34
Relative Questions:
1. Explain the difference between double and float?
2. In which situation you choose the bool or boolean type?
Date and Time Datatypes - Date, DateTime, Timestamp7:18
Relative Questions:
1. What is the difference between date, datetime & timestamp data type?
2. In which format MySQL stores the date datatype?
3. What will be the query to check the timezone?
4. How can you change the timezone?
5. Which is the default timezone for MySQL?
Date and Time Data Types - Time2:25
Relative Questions:
1. How does MySQL interpret time values? Example - 1112
String Data Types - Char and Varchar4:20
Relative Questions:
1. What is the difference between CHAR & VARCHAR?
String Data Types - Blob & Text2:36
Relative Questions:
1. What is the purpose of BLOB Datatype?
2. What is the difference between BLOB & TEXT Datatype?
String Data Types - ENUM2:21
Relative Questions:
1. Explain the datatype ENUM.

Database Storage Engines - Quick Reference3:50
Relative Questions:
1. What is a database storage engine?
2. What is the difference between InnoDB and MyISAM?
Creating & Understanding Tables8:36
Relative Questions:
1. What is the use of AUTO_INCREMENT?
2. What is NULL & NOT NULL
3. What is the purpose of DEFAULT column?
4. What is the Primary Key?
Introduction to Foreign Key4:17
Relative Questions:
1. What is Foreign Key?
2. What do you mean by referential integrity?
3. What is the CASCADE option?
Creating Tables With Foreign Key11:25
Relative Questions:
1. How can you add/remove foreign key constraint at a later stage?
Alter Table Command - ADD, MODIFY5:11
Relative Questions:
1. How will you add a column in the existing table?
Alter Table Command - CHANGE, DROP, RENAME3:22
Relative Questions:
1. What is the difference between CHANGE and MODIFY ?
2. In which scenarios you will use CHANGE or MODIFY ?
3. Is it possible to rename a field name or a column name?
4. How will you rename a table?

Insert Query [Inserting A Record To Table]6:13
Relative Questions:
1. How do you INSERT values in a table?
2. Is it necessary to give column names with the INSERT statement?
Insert Ignore Into Clause - INSERT IGNORE INTO & ON DUPLICATE KEY4:25
Relative Questions:
1. How do we update the column value using insert statements?
Inserting Hierarchical Data4:23
Relative Questions:
1. What is the purpose of LAST_INSERT_ID function in MySQL?
Update Single Row3:41
Relative Questions:
1. How will you update values of column?
2. What happens if you do not use the WHERE clause?
3. Is it possible to give expressions with the UPDATE statement instead of fixed/literal values?
Updating Multiple Records (The “IN” Operator)2:33
Relative Questions:
1. Instead of OR, how will you use the IN operator?

WHERE Clause Introduction1:21
Relative Questions:
1. What is the WHERE Clause?
WHERE Clause With Equality Operator2:58
Relative Questions:
1. What is equality comparison?
2. With which data types can equality operators be used?
3. Find records from the address table where the district name is “Ontario”.
WHERE Clause With Comparison Operators5:27
Relative Questions:
1. Find records of customers whose birth date is greater than 1st JAN 2001 and then sort the data by birth date.
AND Operator5:03
Relative Questions:
1. Select records from the film table with release_year greater than 2005 and replacement_cost greater than 20 and also the length should be greater than equals to 100.
2. Select records from film table where language_id equals to 1 and release_year smaller than 2000
3. Select records from the film table with release_year between 2005 and 2010 and sort the data by release_date in descending order.
4. What will the AND operator return if the condition is true & what will it return if it is false?
OR Operator5:19
Relative Questions:
1. Select records from the film table where language id is 1 or 6.
2. Select records from the film table whose rating should be ‘NC-17’ or ‘PG-13’ and the release year should be greater than 2015 and then sort the result by rating and release_year in desc order.
IN Operator4:18
Relative Questions:
1. Fetch records from the film table where release year is 2000, 2005 and 2010 and also it should not include ‘PG’ and ‘G’ rating and then sort the data by release year ascending and rating in descending order.
BETWEEN Operator3:37
Relative Questions:
1. Fetch customers whose birth date falls between 1st Jan 2000 and 31st Dec 2001 from the customer table.
LIKE Operator6:04
Relative Questions:
1. Get the records from customer table where first name contains “ON” like tony, sony etc.
REGEX Operator9:27
Relative Questions:
1. What is the difference between LIKE and the REGEX operator?
2. Get records where the first name should start from a letter between A to F and should contain exactly four letters.
LIMIT Operator5:50
Relative Questions:
1. What is offset in the LIMIT clause and how do we use it.
2. Get the 5th highest record from the film table by its length and replacement_cost.
IS NULL Operator2:30
Relative Questions:
1. Find records from the customer table where the birthdate is null.
2. With which data types can we use the IS NULL operator?

Alias3:02
Relative Questions:
1. How to assign aliases with spaces.
Introduction to JOINS2:26
Relative Questions:
1. What is a join command and when can we use join?
INNER JOIN10:00
Relative Questions:
1. Get the records from the customer table with address and city name and also the address2 should be null.
2. What happens if I do not use the INNER keyword with JOIN?
3. Which is the default JOIN if you just give the JOIN keyword?
LEFT JOIN3:42
Relative Questions:
1. What is the difference between INNER JOIN and LEFT JOIN?
RIGHT JOIN2:37
SELF JOIN4:47
Relative Questions:
1. How SELF JOIN is different from LEFT, RIGHT OR INNER JOIN.
2. Write a query to get all the records from the product category table with or without parent category.
CROSS JOIN5:04
Relative Questions:
1. Find all the films released in the year 2000 and the length of the film should be greater than 50 and smaller than 65 with the combination of all the languages first using explicit and then implicit join.

GROUP BY5:45
Relative Questions:
1. Get Total No of films by its length and release_year
2. What is the purpose of GROUP BY clause?
3. How do you get the aggregate or summary result within a query?
Aggregate functions (COUNT(), SUM(), AVG(), MIN(), MAX())8:52
Relative Questions:
1. Get min, max, average and total number of films in all the years and languages.
2. How do you get the average,minimum or maximum value?
3. Explain the purpose of GROUP_CONCAT( ) function.
HAVING Clause3:56
Relative Questions:
1. What is the use of HAVING clause?
2. Can we use HAVING clause without GROUP BY clause.

Requirements

As such this is for programmers but there are no prerequisites.

Description

Whether you are a full-stack, mobile, or even an AI developer - storing and managing DATA is the KEY!

Why am I mentioning these three types of developers?
Because there are tremendous job opportunities in these skill-sets and knowing RDBMS and NoSQL is a compulsion nowadays for these developers.

There is a shortage of hands-on talent (I am in this industry for the past 25 years and I know what talent pool is required to fulfill such said jobs).

Gone are those days when companies used to hire professionals on just one skill-set. Nowadays corporations are looking for multi-dimensional individuals who can perform a bunch of work together.

That’s the reason this course is designed and delivered in such a way that you get a 360-degree view of the development community especially when it comes to the database part i.e., MySQL as the medium to learn RDBMS concepts and MongoDB to learn about NoSQL concepts.

Once you go through this course, I am pretty sure you won’t have to refer to any further material to get to know about RDBMS and NoSQL.

It is totally up to you whether you want to learn just one of them or both. This is a comprehensive and progressive course that covers both the concepts in detail along with questions asked in real-world interviews. The practical assignments are those coding exercises that are asked by tech teams of top IT companies and which you need to learn in day-to-day programming.

This is a hands-on course and we'll work on a wide variety of assignments so that you know how the data is dealt with in real-world scenarios.

By the end of this course, I am confident that you'll have all the knowledge that you need to work with MySQL & MongoDB for your next projects!

Who this course is for:

Anyone who wants to learn databases
Developers who are working with databases(or plan to work in the future)
Application, web and mobile app developers

Practical Database Guide with RDBMS(MySQL) & NoSQL(MongoDB)

What you'll learn

Explore related topics

Course content

Installation4 lectures • 15min

Managing Database2 lectures • 8min

Datatypes9 lectures • 37min

Managing Tables6 lectures • 37min

Modifying Data in MySQL5 lectures • 21min

Querying Data1 lecture • 5min

Sorting Data1 lecture • 5min

Filtering Data11 lectures • 52min

Joining Tables7 lectures • 32min

Grouping Data3 lectures • 19min

Requirements

Description

Who this course is for: