DBMS

SQL Operators and Commands

operators and commands

SQL Operators

The operator is used to control the processing of queries of database SQL support different operator to relate the expression and control the processing. The operator like arithmetic, logical, relational etc is used in the expression for handling the operations.

There are following operator and its precedence

()

X,÷

+,-

<,<=,>,>=

=like, in, between

And, or

Table, View, and index

The table is used to store the data in rows and columns format. It represents the data file of database it controls the entities of entity sets. A subset of a table that contains the information similar to rows and columns format known as a view. It helps to filter the records from the data table and also manipulate and update together.

Index are pointers to specific roe in a table. It helps us to logically base of key fields. The indexes cannot be referenced in SQL  statement.

Table commands

SQL provides commands to control the DML, DCL, DDL processing with a particular object.

Create table->

It is a command that allows creating the table on the base of a particular field and defines a schema.

Select min(marks) from Anna

select min(mark) from Anna where mark

Count->

It is a function that returns numeric value after counting the records. In this, we pass * for all record otherwise field name. We district key unique data.

Example->Select count(field name) from <table name>

Select count(marks) from Anna

Select count(mark) from Anna where marks > 50

Order by->

It is a way to arrange the data in ascending and descending order. We use ASC for ascending and Dsc for descending.

Example:-

Select*from<table name> order by <field name>[ASC/DSC]

Select*from anna order by name ASC

Select*from anna order by name DSC

Group by->

It is a command that allows to create the group of similar data and process it. We define the particular field for creating the group.

Example:-

Select sum(<filedname>)from<table name> Group by <field name>

Select sum (salary from Anna Group by design)

Having command->

It is a command that allows checking the condition from the group by similar as for where having condition input defines the particular with the constraints that stored in the field if the user misses the data.

Example:-

Select sum(<fieldname>)from<tablename>groupby <fieldname>

References->

It is useful for defining the reference of a table with another table with defining table name with this constraints

Example

Create table anna (Roll number(3) not primary key, name char(20), marks number(3) check marks>0 and marks<101));

Table alteration commands

The table create with different field or schema so we need to alter the structure of database table SQL provide a command to alter the table structure like field data type of the field size etc

  • ADD option->It is an option that at the new field or constraints into the existing table.

example:-  after table<tablename> add <field details> or constraints;

after table anna add fee number ;

after table anna add constraints pk primary key(name);

  • Modify option->It is an option that allows modifying the structure of note data table.

after table<table name> modify<field details>;

after table anna modify fee number(10);

  • Drop option->This option allow to remove the field constraint of the data table.

alter table<table name> drop<field details> or constraints

after table anna drop column fee;

after table anna drop pk;

Insert operation

A table is used to store the data in a particular format. The information is presented in the form of its datatype. SQL allows us a command to insert the information into the existing table using insert command.

insert into<table name>value(‘data’, ‘data’,…..)

insert into anna value(‘kanna’,1,100)

NOTE :- Single quote is used character or date. The values are stored in particular sequence on the base of the table structure.

 

 

About the author

Pulkit Wadhwa

Add Comment

Click here to post a comment