Central Institute of Information Technology ^®
Plot No.2, Near Jerryl Lawns, RPTS Road, Opp. Momentum Classes, Laxminagar, Nagpur - 440022
Email: ciit_nagpur@yahoo.com Mobile: 9011501192

Join Sql Classes in Nagpur at CiiT

Oracle Sql Course - Learn SQL from basic to an expert level

Job-Oriented Course with Comprehensive Syllabus at an Affordable Fee - 100% Placement Assistance

SQL (Structured Query Language) also known as Sequeal is used to communicate with databases. Sql is designed for managing data in a relational database. Sql is used in software development by programmers and in data analysis. Learning SQL can significantly boost your career in IT industry.

SQL Course PreRequisite : No prior coding experience is required. This course is designed such that anyone or beginners willing to learn SQL can take it.

SQL Course Duration : 1 Month PL/SQL Course

Oracle SQL (Structured Query Language) Syllabus

Introduction to Databases

Overview, importance, types and components of databases

Introduction to SQL and its role in managing databases

Databases used in industry eg. Oracle, MySQL, PostgreSQL, MS SQL Server, DB2 etc

History and Introduction of Oracle Database and its features

Installing Oracle Database

Dbms and Rdbms Concept and key differences between them

Normalization

Entity Relationship Diagram (ER Diagram)

What is SQL( Structured Query Language)

Role of SQL in Database Management

SQL Vs NoSQL Vs Elastic SQL

Understanding SQL Syntax

Different type of SQL statements

Data Definition Language (DDL) (Create, Alter, Drop, Rename ,Truncate) - Table, View, Synonyms

Data Manipulation Language (DML) (Insert, Update, Delete)

Transaction Control Language(TCL) (Commit, Rollback, Savepoint, Set Transaction)

Data Control Language (DCL) (Grant and Revoke)

Data Query Language (DQL) (Select, From, Where, Group By, Having, Order by)

Atomicity (Row, Statement)

Use of Ampersand : &, &&

Select Syntax : Select, From, Where, Group by, Having clause

Data Retrival from Single and Multiple Table

Different type of JOINs

Cartesian product/Cross Product

Sub-Query, Scalar SubQuery in Select,

Self Join, Outer join, Left join, right join

Hierarchical / Tree Structured Query

Pseudo Columns (Rowid, Rownum, User, Uid, Level)

Integrity Constraints (Add, Enable, Disable, Drop)

Domain Constraint: (DataType, Width, Null, Check)

Key Constraint: (Primary, Foreign, Unique)

Operators

Set/Expression (Intersect, Minus, Union)

Row (All, Distinct, Prior, Connect By)

Logical (All, And, Any, Between, Exits, In, Not In, Like, Or, Some,)

Comparison/Relational ((), =, !=, <>, >, >=, <, <=)

Arithmetic (+, -, *, /, **)

Evaluation Order

Aggregate/Group Functions

Sum(), Avg(), Min(), Max(), Count()

Number Functions

Abs(), Ceil(), Floor(), Mod(), Power(), Round(), Sqrt(), Trunc()

Date Functions

Add_Month(), Last_Day(), Months_Between(), Next_Day(), Extract()

Character Functions

Ascii(), Chr(), Initcap(), Instr(), Substr(), Length(), Lower(), Upper(), Lpad(), Rpad(), Ltrim(), Rtrim(), Translate(), Soundex()

Conversion Functions

To_Char(), To_Date()

Others Functions

Greatest(), Least(), Decode(), Nvl()

Analytical Queries using Advanced SQL Functions

Case Expressions

Multiple Insert Statement

Merge Statement

Regular Expression

Statistical Functions

Model Quries

Backup

Basic SQL Syntax :

Create Table Courses(Id Number(2) , Name Varchar(40) , 
                  Constraint pk_course_id Primary Key(Id), Constraint uk_course_name  Unique(Name));
					
Create Table Students (RollNo Number(4), Name Varchar(15), BirthDate Date, Course Number(2),
                       Constraint pk_Rollno Primary Key(rollno),
 		       Constraint fk_course Foreign Key(Course)  References  Courses(Id) );

Insert Into Courses Values(1 , 'Java Course');					  
Insert Into Courses Values(2 , 'Java FullStack Developer Course');					  
Insert Into Courses Values(3 , 'SQL Course');					  
Insert Into Courses Values(4 , 'PL/SQL Course');

Insert into Students Values(1,'Sachin','01-Jan-2000', 3);
Insert into Students Values(2,'Anup','5-Feb-2000', 1);
Insert into Students Values(3,'Nitin','15-Jun-2000', 2);
Insert into Students Values(4,'Anup','5-Feb-2000', 1); 

Delete Students Where RollNo = 4;                     -- Duplicate Row
Update Students Set Name = 'Mr.'||Name Where RollNo = 1;

Select * from Tab;                                    -- Table List
Select * from Courses;                                -- List all courses
Select * from Students;                               -- List all Students

Select s.RollNo, s.Name, s.BirthDate, c.Name Course   -- List data from multiple tables  
From   Students s, Courses c                          -- Cartesian product
Where  c.id = s.course                                -- Equi Join 
Order  by s.RollNo Asc;                               -- Sort data in ascending order of roll no

Create View Course_Student_View  As                   -- Create VIEW
Select c.Name Course, Count(s.rollno) Total_Students  
From Courses c, Students s 
Where c.id = s.course 
Group by c.name;

Desc  Course_Student_View;

Select * from  Course_Student_View;                   -- List data from VIEW