CategorySQL classes

Built in Functions in SQL

How to use Built Function in SQL

How to use Built Function in SQL

create table emp11(id int,name char(10),surname char(10),salary int,jdate date,rdate date,dept varchar(20),location varchar(20),dob date)

insert into emp11 values(1,’vipul’,’sane’,20000,’12/23/2015′,’12/7/2019′,’sql’,’kolhapur’,’3/8/1994′)

insert into emp11 values(2,’kushal’,’ghadge’,25000,’1/24/2016′,’8/12/2018′,’servicing’,’satara’,’12/24/1989′)

insert into emp11 values(3,’abhi’,’patil’,90000,’10/13/2015′,’4/30/2018′,’sap’,’pune’,’6/23/1993′)

insert into emp11 values(4,’tej’,’ghatage’,30000,’12/26/2015′,’7/3/2017′,’ccna’,’mumbai’,’7/3/1992′)

insert into emp11 values(5,’prafull’,’sutar’,35000,’8/17/2016′,’11/27/2018′,’automation’,’sagali’,’9/5/1995′)

select * from emp11

Lotus it hub

 

 

  • Concate

 

select concat(name,surname) from emp11

Built in Functions

create table emp11(id int,name char(10),surname char(10),salary int,jdate date,rdate date,dept varchar(20),location varchar(20),dob date)

insert into emp11 values(1,’vipul’,’sane’,20000,’12/23/2015′,’12/7/2019′,’sql’,’kolhapur’,’3/8/1994′)

insert into emp11 values(2,’kushal’,’ghadge’,25000,’1/24/2016′,’8/12/2018′,’servicing’,’satara’,’12/24/1989′)

insert into emp11 values(3,’abhi’,’patil’,90000,’10/13/2015′,’4/30/2018′,’sap’,’pune’,’6/23/1993′)

insert into emp11 values(4,’tej’,’ghatage’,30000,’12/26/2015′,’7/3/2017′,’ccna’,’mumbai’,’7/3/1992′)

insert into emp11 values(5,’prafull’,’sutar’,35000,’8/17/2016′,’11/27/2018′,’automation’,’sagali’,’9/5/1995′)

select * from emp11

 

  • Concate

 

select concat(name,surname) from emp11

 

  • substr

 

select substr(name,2) from emp11

 

  • instr

 

select instr(name,’i’) from emp11

 

  • length

 

select length(location) from emp11

 

  • upper

 

select upper(name)from emp11

Louts it hub

  • lower

 

select lower(name)from emp11

Louts it hub

  • reverse

 

select reverse(name)from emp11

 

  • initcap

 

select initcap(name)from emp11

Louts it hub

  • mod

 

select mod(id,2)from emp11

select * from emp11 where mod(id,2)=1

 

  •  greatest

 

create table tab7(a int,b int,c int)

insert into tab7 values(10,20,3)

insert into tab7 values(30,4,60)

insert into tab7 values(90,6,12)

select * from tab7

select greatest(a,b,c) from tab7

Louts it hub

  • Least

 

select least(a,b,c) from tab7

 

  •  Sqrt

 

select sqrt(salary) from emp11

Lotus it hub

 

  •  Round

 

select round(sqrt(salary)) from emp11

Lotus it hub

 

  •  Trim

 

select trim(name) from emp11

Lotus it hub

 

  •  Ltrim

 

select ltrim(name) from emp11

Louts it hub

  •  Rtrim

 

select rtrim(name) from emp11

Louts it hub

  •  Lpad

 

select lpad(name,7,’er.’) from emp11 where id=1

Louts it hub

  •  Rpad

 

select lpad(name,8,’er.’) from emp4 where id=1

Louts it hub

  •  Replace

 

select replace(name,’vip’,’abc’) from emp11 where id=1

 

  •  Translate

 

select translate(name,’vpl’,’kop’)from emp11 where id=1

 

  •  Next_day

 

select next_day(jdate,’sun’) from emp11

Lotus it Hub

  • Last_day

 

select last_day(rdate) from emp11

Lotus it Hub

  •  Months_between

 

select round(months_between(rdate,jdate)) from emp11

 

  • Add_months

 

select add_months(rdate,10) from emp11

Lotus it hub

  • Nvl

 

select nvl(salary,0) from emp11

Lotus it hub

select nvl2(12,45,68) from dual

Aggrigate Functions

Lotus it hub

  • Max

 

select max(salary) from emp11

Louts it hub

  • Min

 

select min(salary) from emp11

Louts it hub

  • Avg

 

select avg(salary) from emp11

Louts it hub

  • Sum

 

select sum(salary) from emp11

 

  • Count

 

select count(salary) from emp11

alter table emp11 rename column salary to total

alter table emp11 add(salary int,hra int,acc int,tra int,pf int)

update emp11 set hra=total*8/100,acc=total*7/100,tra=total*6/100,pf=total*5/100,salary=total-(hra+acc+tra+pf)

select * from emp11

 

Difference Between SQL and PLSQL:

The following are the important differences between SQL and PL/SQL.

Difference Between SQL and PLSQL

 

Sr. No. Key SQL PL/SQL
1 Definition SQL, is Structural Query Language for database. PL/SQL is a programming language using SQL for a database.
2 Variables SQL has no variables. PL/SQL has variables, data types etc.
3 Variable declaration Column declaration A int:=100;

B varcharchar2(20):=’Lotus it hub’;

4 Control Structures SQL has no FOR loop, if control and similar structures. PL/SQL has FOR loop, while loop, if controls and other similar structures.
5 Basic c Sql doesn’t require to know c language. Plsql require to know c language.
6 Operations SQL can execute a single operation at a time. PL/SQL can perform multiple operations at a time.
7 Language Type SQL is a declarative language. PL/SQL is a procedural language.
8 Embedded SQL can be embedded in a PL/SQL block. PL/SQL can also be embedded in SQL code.
9 Interaction SQL directly interacts with database server. PL/SQL does not directly interact with database server.
10 Fetch  Select * from table_name Declare

Begin

Dbms_output.put_line(‘Lotus IT Hub!!’);

End;

/

8 Objective SQL is used to write queries, create and execute DDL and DML statements. PL/SQL is used to write program blocks, functions, procedures, triggers and packages.

 

PL/SQL Data Types

Data Type Description Size
BFILE Used to store large binary objects in operating system files outside the database. System-dependent. Cannot exceed 4 gigabytes (GB).
BLOB Used to store large binary objects in the database. 8 to 128 terabytes (TB)
CLOB Used to store large blocks of character data in the database. 8 to 128 TB
NCLOB Used to store large blocks of NCHAR data in the database. 8 to 128 TB
int -2,147,483,648 2,147,483,647
datetime Jan 1, 1753 Dec 31, 9999
float -1.79E + 308 1.79E + 308
real -3.40E + 38 3.40E + 38

 

The Best SQL Classes For Freshers & Professionals!

Structure Query Language (SQL), is the well-known domain-specific language used for managing the bulk data in a relational database management system. While being in the college or being a student, you must have heard and read the definition of the SQL as above, but besides that do you really know what is SQL? At LotusITHub you can learn and understand the practical implementation of the SQL in real-world. SQL classes in Pune are going to be your true mentor to understand the processing of relational database management system (RDBMS) and relation data stream management system (RDSMS).

SQL coaching classes in Pune

SQL training institute in Pune will introduce you with some new tricks and techniques of SQL to access millions of databases with a single query command. SQL consists of many types of statements, whereas the scope of SQL includes data query, data manipulation, data definition and data access control. Nowadays learning SQL is essential and important for beginners to make a successful career in the IT industry. Not only in the software domain but also in the non-technical field it is helpful for any functional, support, and technical consultant. After joining SQL coaching classes in Pune you will get the knowledge about the following area:

  • Data Mining: Various data mining tools and techniques you learn during SQL training can be easily utilized for the prediction of data.
  • Data Manipulation: SQL commands deal with the data manipulation which includes most of the SQL statements For Example INSERT (Used to insert data into the table)
  • Data Collection: The data collector is a component of SQL, that collects different sets of data and stores its relational database.
  • Big Data Tools: Hadoop, Apache Spark, Cassandra, RapidMiner, MongoDB, etc. are known and widely used Big Data tools.

 

LotusITHub SQL training institute in Pune provides unique programming course by considering all the range of aspirants, starting from newbies to experience. We start from the basic level of DBMS (Database Management System) and then move to SQL with lots of practice to solve queries on various databases. After completion of SQL classes in Pune, the candidate can explore the career opportunities like Database Administrator, Business Intelligence Administrator, Database Developer, Big Data Expert, Database Tester, Data Analyst, Data Application Interface Developer, and more across the global industrial sector.

Our SQL coaching classes in Pune are recognized for the experienced trainer and provides hands-on practice. We pleasurably invite you to attend our demo session which we lead absolutely for free. Our branches are available across the verticals of Pune starting from Karve Nagar, Dhayari, Warje, Narhe, Kothrud, etc. For detail information or to attained demo session you can visit any of our branches.

Life of SQL from the eyes of the aspirants!

SQL is a domain specific language that enables its users to access the database, manage data and represent it in a particular manner. SQL classes will make you understand how to organize the gathered information with SQL platform that associates with front-end and bake-end database. To manage the large quantity of data, relational database management systems are used which are MySQL, Oracle, and Microsoft SQL server database.

best sql training in Pune

SQL was developed in IBM at the early 1970s for accessing and modifying data held in the database. Initially, SQL is called as SEQUEL (Structured English Query Language) which later termed as SQL into an official standard for the ANSI (American National Standard Institute) and ISO (International Organization for Standardization). SQL (Structured Query Language) is a standardized programming language that manages the rational database by performing various operation on the data. What are those operations? And how to perform them? Are the important things which aspirant can learn at SQL classes in Pune.

What is the difference between SQL and MySQL?

SQL and MySQL both are the most popular choices for the data management but there is a fine line between them that you can learn at SQL coaching to separate them from one another.

  •    MySQL is open source that offers only updateable views on the other hand SQL is not and it offers index view which is powerful performance wise.
  •    MySQL doesn’t support auto turning and XML whereas SQL supports both the features.
  •    User-defined functions, extensive transactions, stored procedures are supported and functioned by SQL but not by the MySQL.
  •    Job scheduling and profiling, cursor feature, etc. are available in SQL but not in MySQL.

These are the basic feature that experts from our SQL coaching classes will explain you in an elaborate manner with the practical examples.

SQL statements are superficially differentiated in four different categories:

  •    DML (Data Manipulation Language)
  •    DDL (Data Definition Language)
  •    DCL (Data Control Language)
  •    TCL (Transaction Control Language)

SQL utilizes numerous commands for the query execution that allows you to interact with data. All these commands manage and modify the vast volume of data effortlessly, and you will learn to execute these commands seamlessly at SQL training institute in Pune; here are some of the commands: SELECT, DELETE, CREATE DATABASE, INSERT INTO, ALERT DATABASE, CREATE TABLE, CREATE INDEX and more.

SQL is a universal open-source language that manages a million rows of data with technology evolution in a simplified manner. In this digitalized world every business is demanding a digital means of data for which SQL is the only ultimate solution. SQL is the magnificent and valuable language that every business desire to implement and for that query application as per the data storage demand is taught only at the LotusITHub situated at Karve Nagar, Narhe, Warje, Dhayari, Sinhgad road, and Kothrud.

LotusITHub is the renowned SQL coaching institute with the branches available at Narhe, Karve Nagar and all the above-mentioned places with the expert’s guidance. Aspirant can visit our SQL training institute for the detailed inquiry and can be a part powerful technology that can empower you to manage and store the huge quantity of data.

© 2020 Lotus It hub

Theme by Anders NorénUp ↑