SQL Server

                                    SQL SERVER

-----------------------------------------------------------------------------------------------------------------------------

Basic Relational Database Design

-        A relational database uses a structure that allows us to identify and access data in the database. Often, data in a relational database is organized into tables.

 

-        Benefit of relational database

 

A)  Simplicity

B)   Flexibility

C)   Ease of data retrieval

D)  Normalization

E)   Data integrity

DBMS

  *  A database management system is software used to create and manage databases.

* It gives the user and programmers a way to create ,  retrieve, update, and manage data.

Ex- SQL Server, My SQL

Database Design…..

The process of producing a Database Model that contains a detailed description of the logical and physical choice that can used to create a database.

Tables-

Key – Uniquely identify records in a table.

-                             Identify relationships between tables.

Fields-

Character string -    char, vchar, text, nchar, nvarchar, ntext

Numbers- int, bigint, smallint, bit, tinyint, float,

 dateTime, date , time , smalldatetime,

Binary – binary, varbinary,

Img -img

Relationships in database-

-        One-to-one

-        One-to-many

-        Many-to-many

 

Normal Form

-        Process or approach used to organize data efficiently in database.

·      Avoid data redundancy

·      Ensure that data is logically stored

-        Type of normal forms-

·      First normal form

·      Second normal form

·      Third normal form

- first normal form

*  Only atomic values

*  No repeating groups

* Primary key for each table

- Second normal form

* The table should be in 1NF (normal form).

* No partial dependency

 

 

- Third normal form

* The table should be in 2NF.

* All non – PK fields are dependent on the PK (primary key).

 

SQL SERVER

     What is data?

-                       * Fact related to an object.

·      What is database?

-                      *     Database is systematic collection of data.

-                     *           Database support storage and manipulation of data.

-             *    Database makes data management easy

      What is SQL SERVER?

-                * SQL SERVER is a relational database management system, or      RDBMS.

-                *    A database management system is a program which enables its user to access database, manipulate data , reporting / representation of data.

-        Editions includes-

·      Express (Free)

·      Developer (free – for testing and development)

·      Standard (License – Production)

·      Enterprise (License – Production)

-        What is  SQL?

·      SQL is standard language for storing, manipulating and retrieving data in databases.

·      Short for Structured Query Language

Create a database in SQL

-                                    CREATE DATABASE SampleDB

-- remove database

           drob database SampleDB

Table: -

Why do we use SQL constraints? Which constraints we can use while creating database in SQL? Constraints are used to set the rules for all records in the table. If any constraints get violated then it can abort the action that caused it. 

Constraints are defined while creating the database itself with CREATE TABLE statement or even after the table is created once with ALTER 

TABLE statement.

There are 5 major constraints are used in SQL, such as

 NOT NULL: That indicates that the column must have some value and cannot be left null 

UNIQUE: This constraint is used to ensure that each row and column has unique value and no value is being repeated in any other row or column 

PRIMARY KEY: This constraint is used in association with NOT NULL and UNIQUE constraints such as on one or the combination of more than one column to identify the particular record with a unique identity. 

FOREIGN KEY: It is used to ensure the referential integrity of data in the table and also matches the value in one table with another using Primary Key 

        alter table ForeignKeyTable  add constraint ForeignKeyTable_ForeignKeyColumn_FK

        foreign key (ForeignKeyColumn) references PrimaryKeyTable(PrimaryKeyColumn)

        

CHECK: It is used to ensure whether the value in columns fulfills the specified condition

Creating and working with tables

create database simpleDB

use simpleDB

-- create a table

create table Person(

Id int primary key not null,

Name nvarchar(50) not null,

Email nvarchar(50) not null,

GenderId int 

)

-- insert some values

insert into Person (Id, Name, Email, GenderId) values

(1,'Ram','ram@gmail.com',1),

(2,'Radha','radha@gmail.com',2);

insert into Person (Id, Name, Email, GenderId) values(3,'Mohan','mohan@gmail.com',1);

insert into Person (Id, Name, Email ) values(4,'Kon','kon@gmail.com');

-- fetch some values

select * from Person 

create table Gender(

Id int not null primary key,

Gender nvarchar(50) not null

)

Insert into Gender values (1,'Male');

Insert into Gender values (2,'Female');

Insert into Gender values (3,'Null');

-- Forigen Key for connect the table

alter table Person add constraint Person_GenderId_FK

foreign key (GenderId) references Gender(Id)

--Group

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country".

The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

ORDER BY clause in SQL will help us to sort the records based on the specific column of a table.

 

--Join

Different Types of SQL JOINs

Here are the different types of the JOINs in SQL:

• (INNER) JOIN: Returns records that have matching values in both tables
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table

Join to database-

 inner join

select * from car_db.cars

inner join car_db.category on car_db.category.CategoryId = car_db.cars.S_no                                                                                          

 

or we can write as

select * from car_db.cars c

inner join car_db.category cat  on cat.CategoryId = c.S_no

Left join

Right join

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------------------------------

Key 

Candidate Key -

A Candidate key is an attribute or set of attributes that uniquely identifies a record.

• Table can have multiple candidate keys.
• Among the set of candidate, one candidate key is chosen as Primary Key.

Primary Key -

Primary key is a set of one or more fields (columns) of a table that uniquely identify a record                             in database table.

• A table can have only one primary key and one candidate key can select as a primary key.
• The primary key should be chosen such that its attributes are never or rarely changed.
•  Cannot contain “NULL”.
• Primary key field contain a clustered index.

Unique Key - 

A unique key is a set of one or more attribute that can be used to uniquely identify the 

records in table. 

• Unique key is similar to primary key but unique key field can contain a “Null” value but primary key doesn’t allow “Null” value. 
• Unique field contain a non-clustered index.

Foregin Key -

Foreign Key is a field in database table that is Primary key in another table.

• Foreign key is used to generate the relationship between the tables. 
• A foreign key can accept null and duplicate value.

-----------------------------------------------------------------------------------------------------------------------------

            A transaction is a single logical unit of work which accesses and possibly modifies the contents of a database.

               ACID is a concept (and an acronym) that refers to the four properties of a transaction in a database system, which are: Atomicity, Consistency, Isolation and Durability.

type of SQL Statment

1) DML  ( DATA MANIPULATION LANGUAGE)

2) DDL  ( DATA DEFINATION LANGUAGE)

3) DCL  (DATA CONTROL LANGUAGE)

4) TCL  (TRANSACTION CONTROL LANGUAGE) 

DML  ( DATA MANIPULATION LANGUAGE) - USED TO STORE , MODIFY, RETRIEVE, DELETE UPDATE DATA IN DATABASE.

EG: -  SELECT, INSERT , UPDATE, DELETE

DDL  ( DATA DEFINATION LANGUAGE) - DEALS WITH DATABASE SCHEMAS AND DESCRIPTIONS. HOW THE DATA SHOULD RESIDE IN THE DATABASE

EG: -  CREATE, ALTER, DROP, TRUNCATE

DCL  (DATA CONTROL LANGUAGE) - CONTROL OVER THE DATA IN THE DATABASE.

EG: - GRANT, REVOKE

TCL  (TRANSACTION CONTROL LANGUAGE) - USE TO MANAGE TRANSACTION IN THE DATABASE. USE TO MANAGE THE CHANGES MADE BY DML STATMENTS.

COMMIT STATMENTS... USED TO PERMENENTLYSAVE ANY TRANSACTION

INTO A DATABASE. 

EG:  begin tran d

update emp set empName='D' where empId = 11

commit tran d

 

ROLLBACK STATMENTS... USEED TO UNDO THE CHANGES MADE BY ANY COMMAND BUT ONLY BEFORE A COMMIT IS DONE.

SAVEPOINT STATMENTS... USED TO TEMPORARILY SAVE A TRANSATION 

SO THAT YOU CAN ROLLBACK TO THE POINT WHENEVER NECESSARY.

----------------------------------------------------------

      TEMPORARY TABLE  

   - A DATABASE TABLE THAT EXIST TEMPORARILY ON THE DATABASE SERVER

   - IT IS A SUBSET OF DATA FORM A NORMAL TABLE FOR CERTAIN PERIOD OF TIME.

   - IT STORE INSIDE 'TEMPDB' WHISH IS A SYSTEM DATABASE.

   

   

----------------------------------------------------------------

     VIEWS

  A VIEW in SQL Server is like a virtual table that contains data from one or multiple tables.

• It does not hold any data and does not exist physically in the database.

• Similar to a SQL table, the view name should be unique in a database.

• It contains a set of predefined SQL queries to fetch data from the database..

• It can contain database tables from single or multiple databases as well.

• A VIEW does not require any storage in a database because it does not exist physically.

----------------------------------------------------------------------

      SELECT column_name(s)

FROM table_name

WHERE condition

GROUP BY column_name(s)

HAVING condition

ORDER BY column_name(s);

WHERE CLASS - IS USED TO SPECIFY A CONDITION WHILE FETCHING 

THE DATA FROM A SINGLE TABLE OR BY JOININIG MULTIPLE TABLE.

ORDER BY -   USED TO SORT THE DATA IN ASCENDING OR DESCENDING 

ORDER, BASED ON ONE OR MORE COLUMNS.

GROUP BY - USED IN COLLABORATION WITH THE SELECT STATMENT TO

ARRANGE IDENTICAL DATA INTO GROUPS.

HAVING - SPECIFY CONDITION THAT WHICH GROUP RESULTS APPEAR 

IN THE RESULTS.

--------------------------------------------------------------------------------

       JOINS ( INNER JOIN, OUTER JOIN (LEFT JOIN, RIGHT JOIN, FULL JOIN), CROSS JOIN)

   

   INNER JOIN - RETURN ONLY THE MATCHING ROWS B/W BOTH TTABLE

NON MATCHING ROWS ARE ELIMINATED.

    LEFT JOIN - RETURN ALL MATCHING ROWS + NON MATCHING ROWS

FROM THE LEFT TABLE.

   

        RIGHT JOIN -  RETURN ALL MATCHING ROWS + NON MATICHING ROWS

FROM THE RIGHT TABLE

FULL JOIN - RETURN ALL ROWS FROM BOTH THE LEFT AND RIGHT

TABLES, INCLUDING NON MATCHING ROWS.

-----------------------------------------------------------------------

The SQL DROP command is a DDL ( Data Definition Language ) command that 

deletes the defined table with all its table data, associated indexes,

constraints, triggers, and permission specifications.

The SQL DELETE command is a DML ( Data Manipulation Language ) command that

deletes existing records from the table in the database.

The SQL TRUNCATE command is a DDL ( Data Definition Language ) command that

modifies the data in the database. The TRUNCATE command helps us delete 

the complete records from an existing table in the database.

--------------------------------------------------------------------------

              STORE Procedure

A STORED Procedure IS A GROUP OF T-SQL (TRANSACT SQL) STATMENTS.

IF YOU HAVE A SIUTITION, WHERE YOU WRITE THE SAME QUERY OVER AND OVER 

AGAIN. YOU CAN SAVE THAT SPECIFIC QUERY AS A STORED Procedure AND CALL

IT JUST BY IT'S NAME.

--------------------------------------------------------------------------

FUNCTION

IT IS SET OF SQL STETMENT THAT PERFORME A SPECIFIC TASK.

DIFFERENCE B/W STORE PROCEDURE

A STORE PROCEDURE TO GROUP A SET OF SQL STATMENT AND EXECUTE THEM

HOWEVER , STORE PROCEDURE CANNOT BE CALLED WITHIN SQL STATMENT.

FUNCTION  CAN BE.

- SCALAR FUNCTION

ACCEPT ZERO OR MORE PARMETER AND RETURN SINGLE VALUE.

- TABLE FUNCTION

ACCEPT ZERO OR MORE PARMETER AND RETURN TABLE VARIABLE.

* INLINE TABLE VALUED FUNCTION

Inline Table Valued Function contains a single statement that 

must be a SELECT statement. 

The result of the query becomes the return value of the function. 

There is no need for a BEGIN-END block in an Inline function.

 

 

--------------------------------------------------------------------------

        Trigger

 triggers are special stored procedures that are executed automatically in response to the database 

object, database, and server events.

* Data manipulation language (DML)

triggers which are invoked automatically in response to INSERT, UPDATE, 

and DELETE events against tables.

* Data definition language (DDL) 

triggers which fire in response to CREATE, ALTER, and DROP statements.

DDL triggers also fire in response to some system stored procedures that 

perform DDL-like operations.

* Logon triggers

which fire in response to LOGON events

--------------------------------------------------------------------------

*What does SOLID mean?

- Five Object Oriented principles

S – Single Responsibility

O – Open/ Closed

L- Liskov Substitution.

I -Interface Segregation

D- Dependency Inversion

Single Responsibility-

A class should be responsible for a single part of the functionality.

Open/Closed - 

Open to extension  Closes to modification.

Liskov Substitution – 

Also known as “Substitutability”. You should be able to use a subclass in

place of its parent class.

Interface Segregation- 

A class should not depend on methods that it does not need to implement.

Dependency Inversion – 

your classes and modules should depend on abstractions

instead of concrete implementations(Directly).

Single Responsibility – 

Q. When is the principle violated?

Ans - you have error handling code in your class.

- You have Presentation logic

- File/Database Read/Write

BankAccount class comprises the properties of the account and computes the interest of the account. 

Now, look at the change Request we received from the business:

Please implement a new Property AccountHolderName.

   Some new rule has been incorporated to calculate interest.

   

   

These are very different kinds of change requests. One is changing

features while the other one is affecting the functionality. We have 2 

separate kinds of reasons to change one class, which violates SRP.

Learning Link:- 

https://www.interviewbit.com/blog/solid-principles-in-c-sharp/

https://www.c-sharpcorner.com/UploadFile/damubetha/solid-principles-in-C-Sharp/

https://www.c-sharpcorner.com/article/solid-principles-in-c-sharp-dependency-inversion-principle/