Course Schedule and Upcoming Batches
| Please call us to confirm the dates for new batches | |||||
|---|---|---|---|---|---|
15,000 +S.T. | 285 |
||||
This course is open for Enrollment and Payment.
In case you want to Register now and take up this course later, you can register by clicking on " Register" section.
If you have any query, you can call/mail or send your query by clicking on " Send a Query" section.
About the Course
SQL is the foundation of any Data science study. Even after the advent of NoSQL, most of the applications still use SQL to manipulate and manage the data.
A strong knowledge in SQL is required for a person who is willing to start a career in Application Programming, Business Intelligence, Analytics or in a cloud based software product space. SQL knowledge is basically required for any IT project where RDBMSs are used for data storage and data manipulation.
This course is designed to give a learner the end to end knowledge on SQL and related constructs.
Course Objective
After completion of this course, you will be able to:
- Learn the basics of Database Management Systems.
- Learn Data Retrieval by SQL.
- Learn Calculations,Aliases and Functions
- Learn Sorting, Column-Based, Row-Based and Boolean Logic.
- Learn Inexact Matches like Pattern Matching & Matching by Sound.
- Learn How to Summarizing the Data.
- Learn about Inner Join, Outer Join and Self Join.
- Learn about Views.
- Learn about Set Logic and Subqueries.
- Learn how to Modify the Data.
- Learn Triggers.
- Learn Stored Procedures.
- Learn PL/SQL.
- Learn to Maintain the Tables.
- Learn Analytic Functions.
- Learn how to tune the SQL.
- Carry out a SQL project on the dataset provided by us.
Why is it the right course for you?
If you want to get deep knowledge on SQL then this is the right course for you. There are lot of roles where a good knowledge of SQL is a must. For example, the Analyst, Application Programmer, Business Intelligence professionals, Database Administrators all should have good knowledge of SQL.
Pre-requisites
No prior knowledge of SQL or Database is required. Any person with a basic understanding of IT systems can take up this course.
Course Curriculum
1) Database Management Systems
Learning Objectives
In this module you will understand what is Database and Database Management System. You will learn about various types of legacy and latest DBMS. You will also learn that why is it so important to learn the SQL to be able to become a true data professional.
Topics
Database, Database Management System, Types of Database Models, Hierarchical Model, Network Model , Object-Oriented Model, Relational Model, NoSQL Databases, Column Oriented DBMS, Row-oriented systems, Document Oriented Database, Key Value Databases, Graph Database, Comparison of Row Oriented versus Column Oriented Databases.
2) Introduction to SQL
Learning Objectives
In this module you will get an introduction of SQL and what are we going to cover in this course.
Topics
Set Operations, Structured Query Language(SQL), SQL Statements, Data Definition Language (DDL), Data Manipulation Language (DML), Data Control Language (DCL)
3) Basic Data Retrieval
Learning Objectives
You will learn about basic data retrieval using SQL.
Topics
Primary and Foreign Keys, Datatypes,A Simple SELECT, Specifying Columns, Column Names with Embedded Spaces
4) Calculations and Aliases
Learning Objectives
In this module you will learn how to create calculation fields and how to created aliases for columns, data sets and tables.
Topics
Calculated Fields, Arithmetic Calculations, Concatenating Fields, Column Aliases, Table Aliases.
5) Functions
Learning Objectives
In this module you will learn about SQL Function and how to use them and what are the important functions.
Topics
Character Functions, Composite Functions, Numeric Functions, Conversion Functions.
6) Sorting Data
Learning Objectives
In this module you will learn about sorting and how to do sorting based on complex criteria.
Topics
Adding a Sort, Sorting in Ascending Order, Sorting in Descending Order, Sorting by Multiple Columns, Sorting by a Calculated Field, Details on Sort Sequences.
7) Column-Based and Row-Based Logic
Learning Objectives
In this module, you will learn how to limit the number of rows based on certain logic in the SQL code.
Topics
IF-THEN-ELSE Logic, Applying Selection Criteria, WHERE Clause Operators, Limiting Rows, Limiting Rows with a Sort.
8) Boolean Logic
Learning Objectives
You will learn about the importance of Boolean Logic and how to implement it.
Topics
Complex Logical Conditions, The AND Operator, The OR Operator, Using Parentheses, The NOT Operator, The BETWEEN Operator, The IN Operator, Boolean Logic and NULL Values.
9) Inexact Matches
Learning Objectives
In this module you will learn about how to extract data based on pattern matching.Hive and HiveQL, how to install Apache, Loading and Querying Data in Hive and so on.
Topics
Pattern Matching, Wildcards, Matching by Sound.
10) Summarizing Data
Learning Objectives
This is a great feature of SQL where summary can be calculated based on various criteria and many special functions can be applied on summarized data as well.
Topics
Eliminating Duplicates, Aggregate Functions, The COUNT Function, Grouping Data, Multiple Columns and Sorting, Selection Criteria on Aggregates.
11) Joins
Learning Objectives
In this module you will learn about various types of join operations.
Topics
Inner Join - Joining Two Tables, The Inner Join, Table Order in Inner Joins, Alternate Specification of Inner Joins, Table Aliases
Outer Join - The Outer Join, Table Order in Outer Joins, Left Joins, Testing for NULL Values, Right Joins, Full Joins
Self Joins - How to create self join and Usage of self joins
12) Views
Learning Objectives
Views are important part of Databases. There are lot of practical usage of Views in applications. Most of the security strategies for reading data are built around Views.
Topics
Creating Views , Referencing Views, Benefits of Views, Modifying and Deleting Views, Simple View, Complex View, Modifying Data through a View, Indexed Views, Partitioned Views, Materialized View.
13) Subqueries
Learning Objectives
In this module, you will learn why Sub queries are important and how to use them in a SQL statement.
Topics
Types of Subqueries, Using a Subquery as a Data Source, Using a Subquery in Selection Criteria, Correlated Subqueries, The EXISTS Operator, Using a Subquery as a Calculated Column
14) Set Logic
Learning Objectives
This module will give a fair idea about how to use Set operators like UNION, MINUS etc.
Topics
Using the UNION Operator, Distinct and Non-Distinct Unions, Intersecting Queries, MINUS Operator.
15) Modifying Data
Learning Objectives
This module will give good idea about Insert, Delete and Modify operations.
Topics
Inserting Data, Deleting Data, Updating Data, Correlated Subquery Updates.
16) Triggers
Learning Objectives
In this module you will learn about Triggers. Triggers are used to automate the before or after operations on data.
Topics
Triggers, Schema-level triggers, Row and Statement Level Triggers, After Each - Row Level Trigger, After - Statement Level Trigger, Before Each - Row Level Trigger, Before - Statement Level Trigger.
17) Stored Procedures
Learning Objectives
In this module you will learn about Stored Procedures. Stored Procedures are used to create a set of SQL statements to be saved as one. This makes it easy to call them from multiple places. This also saves time in compiling.
Topics
Creating Stored Procedures, Parameters in Stored Procedures, Executing Stored Procedures, Modifying and Deleting Stored Procedures, Sample Stored Procedure.
18) PL/SQL
Learning Objectives
In this module you will learn about PL/SQL. PL/SQL is a combination of SQL along with the procedural features of programming languages. In PL/SQL you can use conditional statements (if-else statements) and loops statements as well. It also provides exception handling.
Topics
- What is PL/SQL?
- The PL/SQL Engine
- Declaration Section
- Execution Section
- Exception Section
- How a Sample PL/SQL Block Looks
- Advantages of PL/SQL
- PL/SQL Placeholders
- PL/SQL Variables
- Scope of PS/SQL Variables
- PL/SQL Constants
- PL/SQL Records
- Conditional Statements in PL/SQ
- Iterative Statements in PL/SQL
- Simple Loop
- While Loop
- FOR Loop
- Cursors in PL/SQL
- Implicit cursors
- Explicit cursors
- PL/SQL Functions
- How to pass parameters to Procedures and Functions in PL/SQL?
- Exception Handling
- Triggers in PL/SQL
- PL/SQL tables
- Dynamic SQL in PL/SQL
- Nested blocks in PL/SQL
19) Maintaining Tables
Learning Objectives
In this module you will learn about DDL and other table Maintainance functions.
Topics
Data Definition Language, Table Attributes, Table Columns, Primary Keys and Indexes, Foreign Keys, Creating Tables, Creating Indexes.
20) Analytic Functions
Learning Objectives
Analytic Functions are very important for Analysis and for Business Intelligence usage. Most of the SQL languages provide support for Analytics functions.
Topics
- Introduction, Relation between Analytic function and Aggregate function
- Analytic Function Syntax
- Query_partition_clause
- Order_by_clause
- Windowing_clause
- AVG
- CORR
- COUNT
- COVAR_POP
- COVAR_SAMP
- CUME_DIST
- DENSE_RANK
- FIRST
- LAST
- FIRST_VALUE
- LAG
- LAST_VALUE
- LEAD
- MAX
- MIN
- NTH_VALUE
- NTILE
- PERCENT_RANK
- PERCENTILE_CONT
- RANK
- ROW_NUMBER
- RATIO_TO_REPORT
- Linear Regression Functions
- STDDEV
- SUM
- VARIANCE
21) SQL Tuning
Learning Objectives
SQL tuning is an important aspect of SQL programming. A query which takes too much time and resources is not only bad for its own use but also bad for overall system performance and degrades other query performances as well.
Topics
- Introduction to rule-based optimization
- Introduction to cost-based optimization
- Collecting table and index statistics
- Using column histograms
- Changing the default optimizer modes
- Using TKPROF
- Using SQL Trace
- SQL reusability within the library cache
- Table high-water mark
- Table striping and table partitions
- Using EXPLAIN PLAN
- Interpreting EXPLAIN PLAN Output
- Using indexes to improve performance
- Identifying full-table scans
- Re-writing SQL queries
- Using hints to improve SQL performance
- Using parallel query to improve performance
22) Project Work
Learning Objectives
In this module you will be given a dataset and you need to perform specified tasks on this dataset using any of the available database.
Topics
Database creation, Table/View creation, DDL, DML , Stored Procedure, Trigger and other activities which are studied in this course and as mentioned in the Project work.
Project Assessment
Project assessment will be done by the Trainer and a grade will awarded based on the performance on project. Grading will be done on the scale of 1 to 5, 5 being the outstanding performance and 1 being the Unsatisfactory performance. Scale is as below:
- Outstanding
- Very Good
- Good
- Average
- Unsatisfactory
Course Duration
Online Classes : 21 Hours
There will be seven instructor led interactive online classes during the course. Each class will be of approximately three hours. If you miss a class then you can reschedule it in a different batch or you can also access Class video recordings anytime.Project Work : 25 Hours
Project work will be given to learners to be completed during the course duration.
Course Work : 40 Hours
Study material of 40 hours or more will be given as a course work to be completed.
Exam : 1 Hour
A proctored exam of 1 hour will be conducted for final assessment.
Course Features
-
Live Classes
Live Instructor Led classes from Industry Experts. Option to choose from Online or Classroom Lectures. Case studies from real life projects.
-
Ongoing Research
Research team works hard to bring out the latest innovations and best practices of course subjects. Courses are evolving continuously; they never get stale.
-
Be More Productive
Get work related tips and perform your work more efficiently. Once you know the tricks of trade, you become more productive.
-
Industry Experts
Classes are conducted by Industry Experts. Learners gain from world class curriculum and extensive experiences of Trainers as well.
-
Easy Reschedule
Have you missed a class? Don't worry !!, You can watch the class videos or you can also request for a reschedule. We will invite you for next class for Free.
-
Learning Material
Learners get unlimited access to online and offline materials. Don't worry if you miss any class, we will be providing you a repeat class online or offline.
-
Learning Support
Learners are encouraged to ask online and offline questions. Our team of Trainers makes it a priority to answer these questions.
-
Money-back Guarantee
If you are not satisfied with the quality then take full refund within the seven days of first class. No questions will be asked.
-
Career Support
Learner's resume is reviewed and a one-to-one discussion is arranged with an expert to advice on career roadmap and job opportunities. For details, visit Career Centre
Certification
Certificate of Participation
A certificate of participation will be awarded after participating in the training program. The name of certificate is "BIWHIZ CERTIFICATE OF PARTICIPATION ON SQL".
Certification after Completion
A certification will be issued after assessment of assignments, course work requirements, projects and a written test as per the course curriculum. After successfully completing all the requirements and passing the written test, a certification will be issued. The name of the certification is "BIWHIZ CERTIFICATION ON SQL".
Certificate Issuing Authority
BIWHIZ is part of the company "Business Intelligence Consultant and Services LLP". Certificates are issued by "Business Intelligence Consultant and Services LLP". This is a registered company with Ministry of Corporate Affairs, Government of India.
Sample Exam Questions
This sample is only for illustrative purpose and only basic level questions are displayed here. Actual exam questions may be completely different with different format as well. Please contact your coordinator to know more about prevailing exam format.
Correlated subqueries are used?
- To increase the performance
- For aggregation
- For data definition
- To increase the readability
Which is not permitted for date and timestamp?
- Division
- Concatenation
- Addition
- Subtraction
Which holds the highest precedence?
- Division (/)
- Brackets ( () )
- Subtraction
- Multiplication (*)
What is the result for WHERE 1=1?
- Syntax Error
- Equivalent to Select *
- Returns First Row
- Returns First Column
What is the difference b/w Inner Join and Outer Join?
- Inner works with same table while outer works with other tables
- Outer join gives better performance
- Inner join gives better performance
- Inner Join keeps same number of rows
Which is true?
- NULL are included in Join
- NULL are included in Analytic functions
- NULL are not considered for Aggregates
- NULL=NULL returns rows when both are NULL
Frequently Asked Questions
-
Is it necessary to have any prior knowledge of Databases before starting this course?
Absolutely No !!
-
Is this course for me? What value would it add to my career?
If you are planning to work in any Data related field or if you are already working in one, then an advanced level of knowledge on SQL will be crucial for your career growth. Data manipulation is done in more than 80% IT projects and most of them use SQL to do that, this can give you a fair idea of why SQL is so important.
-
Where would I be developing and running SQL Project?
You can download any freely available database like MySQL. We will guide you how to download MySQL and how to install it on your system. We will provide you a dataset where you can carry out all your data operations and do practicals.
-
Will this course help me in getting SQL certifications from Oracle or Microsoft etc?
We have included all major topics of advanced SQL, so you should not have any problem going for any other SQL certification. We believe that our course and certification is more inclusive and exhaustive than any other tool based SQL certifications.
-
I guess I need to have very fast Internet connection to be able to attend these courses?
Generally 1 MB connection is sufficient however lower bandwidth connections are also working fine.
-
Can I request for another class if I miss it?
You can watch the recorded session video or you can also attend the same class in next batch.
-
Why Certification is included in the course?
This is to make sure that a learner has understood the course content and BIWHIZ has verified the knowledge level of the Learner. Certification is the proof that you have achieved a certain level of expertise on course and any authorized organization can verify that with us.
-
What if I am not able to clear the Certification exam in first attempt?
You can take extra attempts for free.
-
What is the learning support and would it be available after completing the course as well?
You can raise your queries and doubts during and post training period. All queries will be resolved by SQL experts.
-
Do you help in career related issues as well, like reviewing resume, mentoring for my career growth?
Yes, we have a panel of SQL experts who will guide and mentor you; please check Career Centre for more details.
-
Will you sell my data to Recruitment/marketing companies or will you use it for Recruitment or any other activities not related with Training?
NO, Never. Your details are highly confidential and safe with us. But If you are willing to accept any such calls then you can inform us in advance with a specific need and we will contact only for those specific requirements. For a detailed privacy policy please check Terms, Conditions & Privacy Policy.
Register Here
Please register for this course here. Even if you are not ready to Enrol now, you can register now and get an intimation about our next batch whenever it is starting.