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 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’)
Create table<table name>
(cols name datatype(size) constraints
cols2 name datatype (size)constraints
cols3 name datatype(size constraints));
create table anna(Roll number(5),
name char(20), marks number(3));
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.
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.
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.
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.
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;
SQL provides an aggregate function to manage the record.
- 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
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.
Select min(fieldname) from <table name>
select sum(salary) from anna group by design sum(salary)>5000;
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
SQL allow us to remove the records the datatable. In this we also use condition form removing data.
delete from<table name>[condition] delete from anna
delete from anna where marks<20
SQL provides built-in functions to control the data records and returns particular record.
It is a function that returns absolute value.
select abs(10) from dual
dual-> Here define dual as the table name. It is a built-in table that allows executing SQL function.
It is a function that returns nearest highest value of given function data.
select ceil(10.5) from dual;
select ceil(10.2) from dual;
It is used to get nearest lowest value.
select floor(10.5) from dual;
select floor(10.2) from dual;
It is a function that returns remainder value of given that in this we pass two numeric arguments.
select mod (10.2) from dual
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
- lower->This function allows to convert existing character into lower case.
select lower(‘bhavna’) from dual
- Upper->It is useful to convert existing character into upper case.
select upper(‘bhavna’) from dual
- 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
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.
(‘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.