SQL Training

Data:
Data is collection of information.
It is any sort of information which is stored in computer memory. It may be personal information, banking information, address information.

Database:
database is organised and it has a specific structure.
Eg: phone book which is sorted in the order of their last names.
       Online television streaming service which stores a list of movies and TV shows.
      Healthcare stores patients data in their database.
      All weather data is stored in a database and retrieved, analysed from that database.

DBMS
Data Base Management System.
It is a software which is used to perform different operations like addition,deleting and updation of data in the database.


DBMS VS RDBMS

DBMS	RDBMS
Data is stored in the form of files.	Data is stored in the form of tables.
The data may consists tables.	The data only consists tables.
Relation is not defined in DBMS.	It consists relation between the tables.
Examples for DBMS systems: dBase, MicrosoftAcces, LibreOffice Base, FoxPro.	Examples for RDBMS systems: SQL Server, Oracle , MySQL, MariaDB, SQLite.

HOW SQL Engine works
Database engine consists of two parts.
 1.storage engine:write data to disk and retrieve data from disk
 2.Query processor: accepts, parse and executes SQL commands.

When we give a query it goes to query processor the query asks the query processor to retrieve the data from the disk.
The storage engine and the query processor both working together to get the information from the give query.

There are two types of authentications in SQL
  1.Windows authentication
        User simply login their machine and Account Directory(AD) authenticate them to access the server. User need not to provide any credentials because by he is authenticating it with his windows identity.

2.SQL server authentication
      In this the user need to provide the credentials. That is you need to give IP address or server name to connect in SQL server authentication. You can access the database wherever you want using the IP or server name.


Creation of database using SSMS studio:
step 1:

step 2:

using query:

MDF and LDF files
MDF(Master Data File):

       MDF's are where all the database information is stored. It contains the rows, columns, fields and data created by an application or user. Database column creation, modifications and information record creation, modification are all stored within this file.

LDF(Log Data File):

       It contains all log details in it.

Create database using query:

create database using MS

The path where the the database files are stored

drop a database using query

drop database using MS

creation of table 

inserting records into table

Extracting the records

         output:
                


select the data into xml type

    output:
            

truncate table:
 it deletes  all the table records but the table structure will not be deleted.

     output:
          

drop table:
drop  removes the data along with the table structure         

   output:
          


Add column
table created with the following fields
           
adding the column phn_no to this table

         output:
             


change column name

The table column phn_no before and after renaming
        output:
           

drop column:

the table before and after dropping the column 'branch'.
         output:
             


Oct 24 2018

Constraints:

I have created table with following fields with constraints.

not null constraint:

If i want to insert a null value to the 'parent_phno' column it gives an error.

      output:

                 

               

Inserted some values into the students table

unique key constraint:

If we insert the 'stu_name' as vishnu it gives an error. because the name is already existed.

Check constraint:

If we give the age column value more than 16 it gives an error. Because it is violation of check constraint.

Primary key constraint:

If we give the stu_id value which is already existed

Foreign key constraint

In this we only insert the stu_id values which are there in the students table. Because we are creating foreign key relation for that field.

    sports  table                                                                           

 students table    

                             

            

created foreign key for student_id which is referenced to the stu_id of students table.

If we insert a student_id which is not there in students table it gives foreign key violation error.

Default constraint:

It inserts a default value into the column if the column value is not provided.

now I am not providing inserting value into the column "experience". So it takes default value i.e. 0

Auto increment

identity(1001,1) means it takes the student_id from 1001 and auto increment it by 1 when new record inserted.

Here I have not inserted stu_id although the following output comes.

Inserting records into the table

Inserting null values in it

update table

delete records from table:

to delete the whole table

to delete specific row from table.

here I am deleting the records which contains the st_dob as null.

before deleting the records

after deleting the records

select 

selecting records

selecting with where condition

select using in 

select using like condition

It displays the student details whose name ending with 'i'

select using between condition

select using top

select using order

descending order by st_rollno

ascending order by st_name

alias names

Arithmetic operators

I have created table called student_marks

calculating total marks of each student.

calculating average marks of each student

comparison

logical

logical or

 logical and

isnull() function:

replace  null column with the given value.

here email_id is replaced with the 'sreenigha.edu.in' if the email_id field is null.

coalesce() function:

it displays first non null value for the fields given in the function.

functions:

student_marks table

count(),min(),max(),avg(),sum(),Sqrt() functions