Library

Course: MySQL Database Bootcamp: Go from SQL Beginner to Expert

MySQL Database Bootcamp: Go from SQL Beginner to Expert

  • Life Time Access
  • Certificate on Completion
  • Access on Android and iOS App
About this Course

This course will help you in reading and writing complex SQL queries using one of the most demanding database in industry which is mysql. These skills are also applicable to any other major SQL database like Microsoft SQL Server,Oracle, and much more.

This course is designed for Students as well Software professionals who are willing to learn, understand the technique to create databases, tables or how to query that database.This course includes Creating database, different ways to insert values in the database as well as selecting queries and different types of joins.

Each section includes practice problems or additional e-books to help reinforce what you learn in the video tutorials.

If you are already a SQL programmer and looking for a reference course then this course is not for you.

Basic knowledge
  • No prior SQL or technical experience is required.
  • No software License is required. We will install MySQL and workbench which is freely available on site
  • A Windows or Mac machine where we will install the database and practice our queries
  • Basic computer knowledge is required to learn from this course
What you will learn
  • Create your own database or insert values in existing databases
  • Write Advance SQL queries
  • Become a proficient MySQL Workbench user(Create, import, export and query databases)
  • Handle complex SQL joins(inner,Outer,Cross & Self)
  • Student will be able to Create & modify tables with setting constraints to columns.
  • Learn some of the most useful built-in functions in SQL
Curriculum
Number of Lectures: 29
Total Duration: 02:17:04
Introduction & Environment Setup
Setup your Database and Table
  • Create Your Data Base and Table  
  • Database and Table Creation Quiz  
  • Insert value in your DataBase  
  • Insertion Quiz  
Querying the Data
  • SELECT - Retrieve data and Filtering  
  • Assignment of SELECT - Retrieve data and Filtering Chapter  

    In this Assignment, you will practice SELECT and WHERE Statements which we have learned in the previous chapter.


    For your reference you can Download the attached txt file which have all the answers.


    Questions for this assignment

    1. Write an SQL Query to fetch all records from Customers Table.
    2. Write an SQL Query to fetch only Customer ID, Name, Gender from Customers Table.
    3. Write an SQL Query to fetch all the customers whose country is 'United State'.
    4. Write an SQL Query to fetch all Indian Name, Email, PhoneNo and DOB.
    5. Write an SQL Query to fetch records of all Female Employee.
    6. Write an SQL Query to fetch records of all country except China.
    7. Write an SQL Query to fetch all customers whose age is greater than 35.
    8. Write an SQL Query to fetch customer Id, name and DOB whose DOB is less than 1990-12-31.
    9. Write an SQL Query to fetch all customer details whose age is less than and equal to 30.
    10. Write an SQL Query to fetch all Customer Name, email and country except whose age is 60.
  • SELECT - Logical Operators  
  • Assignment of SELECT - Logical Operators Chapter  

    In this Assignment, you will practice WHERE Statement with Logical operators which we have learned in the previous chapter.


    For your reference you can Download the attached txt file which have all the answers.


    Questions for this assignment

    1. Get the all males customers detail who are from France.
    2. Fetch all Customers detail whose DOB is 8th Feb 1981 or who name is Myrna.
    3. Get the Customer Name, Country and Age of customers whose name is Jane and DOB is greater than 31st Dec 1990.
    4. Get the name of whose Customer whose Age is less than and equal to 30 or Country is 'United State' or Gender is Male.
    5. Fetch only those customers name who are not from South Korea.
    6. Fetch all Customer details whose country is Japan and age is greater than 30.
    7. Get the Name, Age and Country of male customers whose country is India or United States
    8. Fetch all customers whose age is between 40 to 50 and Country is France and China.
  • SELECT - Patterns & Sorting  
  • Assignment of SELECT - Patterns & Sorting Chapter  

    Question 1 to Question 5 : Related to 'Pattern and Sorting' chapter

    Question 6 to Question 8 : Related to all previous chapters.

    For your reference you can Download the attached txt file which have all the answers.


    Questions for this assignment

    1. Fetch all details of those customers whose Email contain .gov.
    2. Fetch all details of those customers whose country start with south.
    3. Get all details of those customers whose phone no start and end with any character but 2nd and 4th position contain 1 and 4.
    4. List those customers whose name second character is e and name end with ta ordered by country in descending order.
    5. Sort the customers country in descending order then by age in ascending order and last sort date of birth in descending order.
    6. Select those customers whose email contain .com and country is India or united states.
    7. Sort the above query result by country in descending order.
    8. Get the list of those customers whose age between 30 to 50 and country last two characters are ia.
Advance Queries
  • SELECT - Aggregate Functions  
  • Assignment of SELECT - Aggregate Functions Chapter  

    In this Assignment, you will practice Aggregate Functions which we have learned in the previous chapter.


    Question 1 to Question 6 : Related to 'Aggregate Functions' chapter

    Question 7 to Question 9 : Related to all previous chapters.

    For your reference you can Download the attached txt file which have all the answers.


    Questions for this assignment

    1. Calculate the total salary of New York employee.
    2. Calculate the maximum and minimum salary of the employee.
    3. Calculate the average price of 2011 (Year) employee.
    4. Calculate the count of Services Department employee.
    5. Calculate the highest salary of Recruiting Manager.
    6. Fetch only distinct joining year of employees.
    7. Calculate the total salary of all Accounting Department employees whose joining year is from 1990 to 2000.
    8. Calculate the salary and total count of those employees whose first name ends with 'n' and name contains 4 letters.
    9. Calculate the Average Salary and total count of those employee whose Salary is less than 170000 and designation contain Analyst.
  • SELECT - Grouping & Having  
  • Assignment of SELECT - Group By & Having Chapter  

    In this Assignment, you will practice Group By & Having Statements which we have learned in the previous chapter.


    For your reference you can Download the attached txt file which have all the answers.


    Questions for this assignment

    1. List the number of employee in each department.
    2. Get Work Location and Total salary with respect to an each Work Location.
    3. Sort the above query result (Total Salary) in Descending order.
    4. List the number of employees from each year for each Work Location.
    5. Get the Work Location, Joining Year, Count of employee, Total Salary and Average Salary with respect to each year and each Work Location.
    6. Get the Designation, Total Salary with respect to Designation where total Salary greater than 600000.
    7. Fetch all the result expect Joining Year 1990 from above Query. (You have to use Where Clause)
    8. Sort the above query result (Total Salary) in Descending order.
    9. Get Department, Total employee with respect to each Department where Total employee is more than and equal to 10.
    10. Get Department, Total Salary with respect to each Department where Total Salary Range from 2000000 to 3000000. (You have to use Between Operator with Having Clause)
Constraints
  • Delete & Update your records  
  • Assignment of Delete & Update  

    In this Assignment, you will practice DELETE and UPDATE Statements which we have learned in the previous chapter.



    For your reference you can Download the attached txt file which have all the answers.


    Questions for this assignment

    1. Delete those employees those salary is '32880'.
    2. Delete those employees those Employee id is 1495 Or Designation is 'Quality Engineer'.
    3. Update those employees salary with 30000 whose Department is Business Development and Designation is VP Sales.
    4. Delete those employees those joining year is 1994 and those Designation is Software Consultant.
    5. Update employee Firstname to Rachel, Lastname to Karl and Email to rachelkarl@hot.com whose Phone no is 9722531345.
  • Drop & Alter your Table  
  • Assignment of Drop & Alter Table  

    In this Assignment, you will practice Drop and Alter Statements which we have learned in the previous chapter.


    

    Questions for this assignment

    1. Write a SQL statement to rename the table address to address_new.
    2. Write a SQL statement to add a column postal_code to the table address.
    3. Write a SQL statement to add a columns ID as the first column of the table address.
    4. Write a SQL statement to drop a columns ID to the table address.
    5. Write a SQL statement to add a column postal_code after city to the table address.
    6. Write a SQL statement change the data type of the column country_id to integer in the table address.
    7. Write a SQL statement to drop the column city and street address from the table address.
    8. Write a SQL statement to change the name of the column state_province to state, keeping the data type and size same.
  • Constraints  
  • Assignment of Constraints  

    In this Assignment, you will practice Constraints which we have learned in the previous chapter.






    Questions for this assignment

    • Create a Customers Table with given Data.
    • Create a Orders Table with given Data Type and Constraints.
    • Create a Payments Table with given Data Type and Constraints.
    • Add a below columns in Customers Table with following details.

    DOB datetime NOT NULL

    Email varchar(55) UNIQUE

    • Remove customer_id Foreign Key from Payments table.
    • Add status column with 'Confirmed' Default value in Orders Table.
    • Add a below columns in Payments Table with following details.

    Order_id - Int(6) - Foreign Key (Id in Orders Table)

    Invoice - Int(6) - UNIQUE


SQL Table Joins
  • Introduction of Join  
  • Inner Join  
  • Assignment of Inner Join  

    In this Assignment, you will practice INNER JOIN Statements which we have learned in the previous chapter.



    Questions for this assignment

    1. Write a SQL statement to find the brand name for each of the car models. (cars.id, model_name, color_name, code)
    2. In the above query find only USA cars.
    3. Write a SQL statement to find the color for each of the cars. (cars.id, model_name, color_name, code)
    4. In the above query find only those cars with Color code '#000000' and '#FF0000'.
    5. Write a SQL statement to find the color and brand for each of the cars. (cars.id, model_name, color_name, code ,brand_name, country)


  • Outer Join  
  • Assignment of Outer Join  

    In this Assignment, you will practice OUTER JOIN Statements which we have learned in the previous chapter.

    Questions for this assignment

    1. Write a SQL statement to make a list for the car models which either has brand name or not. (cars.id, model_name, brand_name, country)
    2. Write a SQL statement to make a list for the car brands which either has car model or not.(cars.id, model_name, brand_name, country)
    3. Write a SQL statement to make a list for the car models which either has color name or not.(cars.id, model_name, color_name, code)
    4. Write a SQL statement to make a list for the car colors which either has car model or not.(cars.id, model_name, color_name, code)
  • Cross Join  
  • Assignment of Cross Join  

    In this Assignment, you will practice CROSS JOIN Statements which we have learned in the previous chapter.

    Questions for this assignment

    1. Write a SQL statement to make a Cartesian product(cross join) between cars and car brands tables. (cars.id, model_name, car_brands.id, brand_name, country)
    2. Write a SQL statement to make a Cartesian product(cross join) between cars and car colors tables. (cars.id, model_name, car_colors.id, color_name, code)
    3. Write a SQL statement to make a Cartesian product(cross join) between cars, car brands tables and car colors. (cars.id, model_name, car_colors.id, color_name , car_brands.id, brand_name, country)
    4. Do some modification in above statement
    5. Only those cars will appear for all brands and colors which belong to Germany, Japan and USA country.
    6. Arrange the result in ascending order of cars id, colors id and country
Reviews (0)