• A database management system is a software designed to assist in maintaining and utilizing large collection of data.

            Example:- Oracle,MySql,SQL.

•  The alternative to use a DBMS is to store the data in files and write application specific to manage it.
• DBMS allows users to create their own databases as per requirement.
•  It provides an interface between the data and the software application.

Advantage of DBMS

• Data independence. 
• Efficient data access
• Data integrity and security
• Data Administration 
• Concurrent access and data recovery
• Reduce application development time

Types of DBMS 

1. Hierarchical DBMS
2. Network DBMS
3. Relational DBMS
4. Object Oriented

1.Hierarchical DBMS

• In Hierarchical database, model data is organized in a tree like structure.
• Data is stored hierarchically (top down or bottom down ) format .
• Data is represented using a parent child relationship.
• In hierarchical DBMS parent may have many children,but children have only one parent.

2.Network DBMS

• The network database model allow each child to have multiple parents.
• It helps you to address the need to model more complex relationship like many to many relationship.
• In this model,entities are organized in a graph which can accessed through several paths.

3.Relational DBMS 

• Relational DBMS is the widely used DBMS model in the industries because it is one of the easiest.
• Relational DBMS based on the normalization data in the row and column of the tables.
• It is stored in a fixed structures and manipulated using SQL.

4.Object Oriented 

• In object oriented model data data stored in a form of objects. 
• The structure we called is class we stored data within the class.
• It define a database as a collection of objects which store both data members values and operations.

                  2. Table form 

1. File Form      

• SQL server database have three types of file:

                        a.Primary File

                        b.Secondary File

                        c. Transaction Log  

a.Primary File:-       

• The primary data file contains the startup information for the database and point to the others file in database.
• User data and object can be stored in this file or secondary data file.
• Every database has one primary data file.
• The recommended file name extension for primary data file is ".mdf ".         

b.Secondary File:-      

• Secondary data files are optional,are user defined and store user data.
• Secondary files can be  used  to spread data across multiple disk by putting each file on a different disk drive.
• If a database exceeds the maximum size for a single windows file you can use secondary data files so the database can continue grow.
• The recommended file name extension for secondary data files is ".ndf".

c.Transaction Log:-

• The  transaction log files hold the log information that is used to recover the database.
• There must be at least one log file for each database.
• The recommended file name extension for transaction log is ".ldf".

2.Table Form

• SQL table is a collection of data which is organized in terms of rows and columns.
• In DBMS,the table as known as relation and row as a tuples.
• Table is a simple form of  data storage.A table  is also convenient representation of relation.
• Tables contains multiple records (Rows and Columns ).

What is Database

• A database is a systematic collection of data .
• Database support storage and manipulation of data.
• Database make data management easy.

Table And Record

• A table in a relational database is a predefined format of rows and column that define an entity.
• Each column contains a different type of attribute and each row corresponds to a single record.
• Each table is provided with a name.

Example:-

                    SID              Name          Address             Contact

                   1001              Shiva            Delhi                  123456

                   1002              Ram            Mumbai               987658

                   1003               Sri               Pune                   435675  --------> Record(Row)

                                                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;


                    
         

• SQL is used to create new database.
• SQL is used to create new tables in a database.
• SQL is used to insert records, update records,delete record in a database.
• SQL is used to retrieve data from database.
• SQL can set permission on tables procedures and views.
• SQL is used to create stored procedures in a database.
• SQL is used to create view in a database.

Who should learn SQL

• Database Developers.
• Database Admistrators.(DBA)
• Database Testers.

Database Developers:

                                      Design and deploy database table structures,forms,reports,and queries.

Database Admistrators:

                                      Keeping database upto data and  managing database access.writing reports documentation and operating manulas.

Database Testers:

1. Verify data Integrity ( completeness and correctness of data).
2. Verify data Manipulation (Add,Update,Delete).
3. Verify data Comparison (two database front end data with back end data).