Showing posts with label SQL Queries. Show all posts
Showing posts with label SQL Queries. Show all posts

Friday, 30 August 2013

STRUCTURED QUERY LANGUAGE | DBMS | DQL,DDL,DML,DCL

SQL, which is an abbreviation for Structured Query Language, is a language used to request data from a database, to add, update, or remove data within a database, or to manipulate the metadata of the database.
SQL is generally pronounced as the three letters in the name, e.g. ess-cue-ell, or in some people's usage, as the word sequel.
SQL is a declarative language in which the expected result or operation is given without the specific details about how to accomplish the task. The steps required to execute SQL statements are handled transparently by the SQL database. Sometimes SQL is characterized as non-procedural because procedural languages generally require the details of the operations to be specified, such as opening and closing tables, loading and searching indexes, or flushing buffers and writing data to file systems. Therefore, SQL is considered to be designed at a higher conceptual level of operation than procedural languages because the lower level logical and physical operations aren't specified and are determined by the SQL engine or server process that executes it.
Instructions are given in the form of statements, consisting of a specific SQL statement and additional parameters and operands that apply to that statement. SQL statements and their modifiers are based upon official SQL standards and certain extensions to that each database provider implements. Commonly used statements are grouped into the following categories:

Data Definition Language (DDL)
  • CREATE
  • ALTER
  • DROP
  • TRUNCATE
CREATE :
Used to create a new table, a view of a table, or other object in database.
Example : create table test (id int null, name varchar(20));

Constraints:
Constraints are the rules enforced on data columns on table. These are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the database.
Constraints could be column level or table level. Column level constraints are applied only to one column where as table level constraints are applied to the whole table.
Domain integrity constraint :Domain integrity Constraints are the rules enforced on a particular row of a table.
Not Null Constraint : Ensures that a column cannot have NULL value.
Default Constraint : Provides a default value for a column when none is specified.
Check Constraint : The CHECK constraint ensures that all values in a column satisfy certain conditions.
Entity integrity constraint :Constraints are the rules enforced on data columns on table

  • Unique Constraint : Ensures that all values in a column are different. 
  • Primary Key : Uniquely identified each rows/records in a database table 
Referential integrity constraint : It relates two or more tables
Foreign Key: Uniquely identified a rows/records in any another database table.

ALTER :

ALTER - Used to modify an existing database object, such as a table.
Example : Alter table test add d_id int;

DROP :

DROP - Used to delete an entire table, a view of a table or other object in the database.the structure remains 
Example : Drop table test;

TRUNCATE :

TRUNCATE - Used to delete an entire table, a view of a table or other object in the database with structure 
Example : truncate table test;

Data Manipulation Language (DML)

  • INSERT
  • UPDATE
  • DELETE
INSERT :
INSERT - Used to create a record. 
Example :
  • To insert values for every columns 
Insert into test values(1,’cse’,1)
  • To insert values for selected columns 
Insert into test(id,name) values(2,’it’); 

UPDATE :
UPDATE - Used to change certain records.
Example :
  • To Update with particular condition 
update test set id = 3 where id =2;
  • To update every rows of the table without condition 
update test set d_id = d_id * 1 

DELETE :
DELETE - Used to delete certain records
Example :
  • To delete a particular row 
Delete from test where d_id =1;
  • To delete every data of a table 
Delete from test

Data Control Language (DCL)


GRANT - Used to give a privilege to someone. 

REVOKE - Used to take back privileges granted to someone.


Data Query Language (DQL)

SELECT - Used to retrieve certain records from one or more tables.

Thursday, 27 June 2013

SQL CREATE TABLE AND INSERT INTO TABLE STATEMENTS | DBMS

How to create a table in Data Base using sql queries or statements

The create Table statements are used to create tables in the database

Syntax for creating table in database

SQL > CREATE TABLE SYNTAX


create table table-name(column_name 1 data_type,column_name 2 data_type,column_name 3 data_type);


Example for Table Creation in Database using sql statements


SQL > CREATE TABLE EXAMPLE


create table person(name varchar(10), address varchar(10),age int);


Thus the table named as person is created.In the below section we are going to see "How to insert data's into the table "using sql statements.




How to Insert data's into the table in Data Base using sql statements or comments


The Insert into Table sql statements are used to insert data's in the tables which is in the database.


Syntax for inserting data's in the created tables.


SQL > INSERT INTO STATEMENT SYNTAX


insert into table-name values(value int,value int,'value char');


Example for inserting data's in the created tables.


SQL > INSERT INTO STATEMENT EXAMPLE


insert into person values('Eshin','marthandam',20);
insert into person values('Jozer','nagercoil',20);

Thus the data's are inserted in the table named person.




Note:

char data-types must be enclosed by single quotes.
int data-types must be inserted without any quotes.