What is RDBMS |Subset of SQL

                                                   1.What is RDBMS and Subset of SQL

RDBMS

• RDBMS stands for Relational Database Management System.
• RDBMS is the basis fro SQL, and for all modern databse system such as MS SQL Server,IBM DB2,Oracle,MySQL,and Microsoft Access.
• The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.

Subset of SQL
SQL command can be classified into group on their nature.they are

1. Data Definition Language.
2. Data Manipulation Language.
3. Data Control Language.
4. Transaction Control Language.

1.Data Definition Language:-

                                                In SQL,DDL means Data Definition Language. The SQL Server DDL Commends are used to create and modify the structure of a database and database objects.Important commands and operation in data definition language.

1. Create commands
2. Alter commands 
3. Drop commands
4. Truncate commands
5. Rename commands

1.Create commands:-

                                   To create database and database object.

Syntax:- Create table Table_Name

                (

                  column_Name1     Datatype (Size),

                  column_Name2     Datatype(Size),

                  column_Name3     Datatype(Size)

                 );

Example:- Create table xyz1

                   (

                      student ID              int,

                      student_name        varchar(20),

                      student_address    varchar(10),

                      student_age            int

                   );

              

2.Alter commands:-

                                   To modify existing database objects.

Syntax:- Alter table Table_Name add 

                (

                column_Name4   Datatype(Size),

                column_Name5   Datatype(Size),

                 column_Name6   Datatype(Size)

               );

Example:- Alter table xyz1 add

                   (

                    student_gender   char(5),

                    student_weight    int,

                    student_ph.no      double(20)

                  );

                

3.Drop commands:-

                                  To drop database and database objects.

syntax:- Drop table Table_Name

Example:- Drop table xyz1;

4.Truncate commands:-

                                   To remove all records from a table.

Syntax:-Truncate table Table_Name

Example:- Truncate table  xyz2;

5.Rename commands:-

                                   To rename the database objects.

Syntax:- Exec sp_rename 'table_name.old_column_name','new_column_name';

                here Exec is a execute.

Example:-Exec sp_rename 'xyz1.student_Name', 'student_fullname';

2.Data Manipulation Language:-

                                                      The Data Manipulation Language is work on the data or record (row). it is a subset of the SQL.

1. Select
2. Insert
3. Update
4. Delete

1.Select:-

              Retrives data from a tables.

Syntax:- Select  column1,column2....

                from Table_Name;

Example:- Select student ID, student_fullname

                   from xyz1;

Example:- Select * 

                   from xyz1;

NOTE:- Asterisk (*) symbol is used to represent the all columns in the table.so here we are fetching all the columns from the table.

2.Insert:- 

               Insert data into a table.

Syntax:- Insert into Table_Name ( column1_name,column2_name,column3_name........)

                values ( col1_values,col2_values,col3_values);

Example:- Insert into xyz2 (id,name,city)

                   values (1,'abd','delhi');

NOTE:- here Table_name is not mandatory (compulsory). directly put the values and gives                     the values. like this

Syntax:- Insert into Table_Name values ( col1_values,col2_values,col3_values);

Example:- Insert into xyz1 values ( 2,'name','mumbai');

                       

3.Update:- 

                 Update a existing record into a table.

Syntax:-  Update table Table_name

                 set column_Name = value1,column_Name=value2...
                 where column_Name= value;
Example:- Update table xyz1
                   set student_age = 20,student_Name ='suman'
                   where student ID =2;

4.Delete:- 

               SQL delete command is the delete the existing record into a table.
Syntax:- Delete from Table_Name [where condition]

Example:- Delete from xyz2
                   where student ID =5; -------> particular record will be deleted.

Example:- Delete from xyz2;------> all recorded will be deleted.

3.Data Control Language:-

                                            Important command and operation in data control language.

1. Grant
2. Revoke

1.Grant:-

               To provide access on the database object to the users.
                                                   OR
                Used to provide any user access privileges or other privileges for the database.
Syntax:-
              Grant create session to User_Name;
Example:-
                 GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;

2.Revoke:-

                 To remove user access right to the database objects.
                                                     OR
                  Used to take back permission from the User.
Syntax:- Revoke create table to User_Name;

Example:-
                REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;

4.Transaction Control Language:-

                                                        TCL  command can only use with DML command like Insert,Update,Delete Only.These operations are automatically committed in the database that is why they can not be used while creating tables or dropping them. here are some TCL commands command.

1. Commit
2. Rollback
3. Save point

1.Commit:-

                Commit commands is used to save all the transaction to the database.
Syntax:-  Commit;

Example:- Delete from xyz1
                   where student ID =2
                   commit; 

2.Rollback:-

                    Rollback command is used to undo transaction that have not only been saved to the  database.
Syntax:- Rollback;

Example:- Delete from xyz2
                  where student ID =3
                  Rollback;

3. Save Point:-

                        It is used to roll the transaction back to a certain point without rolling back the entire transaction.
Syntax:- SAVE POINT savepoint_name;

Example:- save point xyz3;