Home / DBMS / Queries And SQL Functions List
sql queries

Queries And SQL Functions List

Queries

SQL provides a different type of command operator for manipulating the record and process the data queries.

Select-> It is a command that allows getting value from the existing table.

select*from anna

select name, roll from anna

select name”std_name”, roll “std roll” from anna

Note:- display all record of all field.

Where command-> It is used to check the condition for display the record select command.

select*from anna where marks>50

select*fromm anna where marks>50 and marks<80

select*from anna where name like %a(end with ‘a’)

Syntax:

Create table<table name>

(cols name datatype(size) constraints

cols2 name datatype (size)constraints

cols3 name datatype(size constraints));

EXAMPLE

create table anna(Roll number(5),

name char(20), marks number(3));

Oracle->

It is a data application that provides a platform to execute a command to execute command related to the database using SQL. It provides interactive command prompt and supports RDBMS feature to maintain database records.

Constraints->

We define the integrity constraints into the table for controlling the data and it’s valued. SQL provides built-in constraints to manage the data fields.

  • Not null->

It is the constraint that allows protecting the value of a particular field with blank space it means the users can’t blank field related to this constant.

  • Unique->

The constant applies to the field of integrity processing. This constant allows to protect duplicate record it means we can’t repeat similar record into the exiting field.

  • Check->

It is a constraint that allows checking the value before inserting into the table. We define the value with conditional operator. It is basically used to control the values of the database.

  • Primary key->

It is a constraint that allows the use of unique identification but it doesn’t support a null value. A table can contain only one primary key.

  • Default->

It is the constraints that allow storing default value into the existing field if the user misses the data.

select*from anaa where marks between 10 and 50,

select*from anna where name in (‘anna’,…),

select*from anna where roll=1 or marks=100;

Aggregate function

SQL provides an aggregate function to manage the record.

  1. Sum-> It is a function that allows summing of the numeric fields in we pass field as argument

select um (fieldname) from<table name>

select sum(marks) from Anna

select sum(marks) from Anna where marks>50

       2.AVG->It is the function that allows getting the average of a numeric field.

Example select avg(fieldname) from <tablename>top

select AVG(marks)from anna;

select AVG(marks) from anna where marks>50;

      3.Max->It is a function that return maximum form the given numeric

Example->

Select max(fieldname) from<tablename>

select max(marks from anna;

select max(marks) from anna where marks>50;

    4.min-> It is a function that returns minimum from the given numeric field.

Example->

Select min(fieldname) from <table name>

select sum(salary) from anna group by design sum(salary)>5000;

 

UPDATE OPERATION

It is a command that allows modifying the record with different information in particular table and field.

Example: Update<table name> set cols1= value,cols2=value….

update anna set marks = marks+5;

update anna set marks= marks+5 where marks>45

DELETE OPERATION

SQL allow us to remove the records the datatable. In this we also use condition form removing data.

Example:-

delete from<table name>[condition] delete from anna

delete from anna where marks<20

SQL function

SQL provides built-in functions to control the data records and returns particular record.

  1. ABS->

It is a function that returns absolute value.

select abs(10) from dual

output =10

dual-> Here define dual as the table name. It is a built-in table that allows executing SQL function.

2. CEIL->

It is a function that returns nearest highest value of given function data.

select ceil(10.5) from dual;

output=11

select ceil(10.2) from dual;

output=11

3. Floor->

It is used to get nearest lowest value.

select floor(10.5) from dual;

output=10

select floor(10.2) from dual;

output=10

4. Mod->

It is a function that returns remainder value of given that in this we pass two numeric arguments.

select mod (10.2) from dual

output=0

5. Power

This function allows getting power value of given numeric data. In this, we pass two integer argument.

6. Character function->

SQL provides built-in functions to control the character data.

  • Int cap->It is a function that allows converting the first character into a capital letter.

select int cap(bhawna) from dual

output-> bhawna

  •  lower->This function allows to convert existing character into lower case.

        select lower(‘bhavna’) from dual

output=bhavna

  • Upper->It is useful to convert existing character into upper case.

select upper(‘bhavna’) from dual

output =BHAVNA

  • Soundex->It is a function that allows comparing the string on the base of similar sound.
  • Substr->It is a function that allows filtering the character from given string. In this we pass character value, starting position and number of character.

select substr(‘anna jain’,5,3) from dual

  • Date function->SQL provide date function to control the date related function.
  • Sysdate-> This function system return system current date.

select sysdate from dual

Add_months->

It is a function that adds the value in a month. In this, we pass two arguments as date and numeric value.

ADD_month(sysdate,10) from dual

Month_between->It is function find out the difference between 2 dates and return in form of the month. In this, we pass to date.

selectmonths_between

(‘1-month-2016’, ‘1-Jan-2017’) from dual

To_char->It is a function that returns given date. In this, we pass date and ‘day’.

select to char

(‘1-Jan-2016,’Day’) from dual

output=”day” show the Friday.

 

 

 

 

 

 

 

 

 

About Pulkit Wadhwa

Check Also

Relational algebra

Relational Algebra In Dbms

Relational algebra Relational algebra is used for processer. query language to manipulate the data with …

2 comments

  1. Nice Article

Leave a Reply

Your email address will not be published. Required fields are marked *