Introduction to database

MAR

1

Database concepts

Introduction to database

Databases 

1.System defined 

2.User Defined

System defined 

• Master database
• Model database-template to create 
• Temp database
• MS DB

User defined 

• User can create 'n' number of databases

Types of authentication 

• Windows Authentication
• SQL Server Authentication

Commanding Languages 

1. Data Definition Language (DDL)

• create 
• alter 
• drop 
• Truncate  

     2.Data Manipulation Language (DML)

• Insert
• Update
• Delete

   3.Data Query Language (DQL)

• Select
• Print

  4.Data Control Language (DCL)

• Grant 
• Deny
• Invoke

  5. Transactional Control Language 

• Commit
• Rollback

Simple Queries 

create table employee

(empid int primary key, empname varchar(100),empaddrs varchar(100))

Insert into employee(empid,empname,empaddrs)values 

(101,'ram','bangalore'),(102,'siv','hyderbad'),(103,'avi','bangalore'),(104,'sam','chennai')

select * from employee

Result:

empid empname empaddrs

101 ram bangalore

102 siv hyderbad

103 avi bangalore

104 sam chennai

alter table employee add salary money

update employee set salary= 10000 where empid=101

update employee set salary= 20000 where empid=102

update employee set salary= 30000 where empid=103

update employee set salary= 20000 where empid=104

select * from employee

Result:

empid empname empaddrs salary

101 ram bangalore 10000.00

102 siv hyderbad 20000.00

103 avi bangalore 30000.00

104 sam chennai 20000.00

Data Warehouse concepts

Defining Data Warehouse Concepts and Terminology

Definition of a Data Warehouse: “An enterprise structured repository of subject-oriented, time-variant, historical data used for information retrieval and decision support. The data warehouse stores atomic and summary data.”

Data Warehouse Properties

1. Subject-Oriented:

Data is categorized and stored by business subject rather than by application.

2. Integrated:

Data on a given subject is defined and stored once.

3. Time variant:

Data is stored as a series of snapshots, each representing a period of time

4. Non-volatile:

Typically data in the data warehouse is not updated or deleted.

Difference between Data warehouse and Data Mart

Methodology

1. Ensures a successful data warehouse
2. Encourages incremental development
3. Provides a staged approach to an enterprise wide warehouse   

     - Safe

     -  Manageable

     - Proven

     - Recommended

Modeling:

 1. Warehouses differ from operational structures:

      - Analytical requirements

      - Subject orientation

2. Data must map to subject oriented information:

      - Identify business subjects

      - Define relationships between subjects

      - Name the attributes of each subject

3. Modeling is iterative

 4. Modeling tools are available

Data Management:

1.Efficient  database server and management  tools for all aspects of data  management

2.Imperatives

     - Productive

     - Flexible

     - Robust

     - Efficient

3.Hardware, operating system and network management

Best SQL server question and answers on differences

Few question and answers on differences in SQL SERVER

Here I have posted some concepts in SQL server which I got from another source 

1.    

                           Differences between Functions and Procedures?

             

Procedures	Functions
Procedures can be used for performing business logic.	Function can be used for computations.
A procedure need not to return a value.	A function must return a value.
It can return more than one value.	Function can return only one value.
Return values using out parameters.	Returns value using Return expression.
Procedure can’t be called in SELECT statement.	Function can be called in SELECT statement.
They can use Temporary tables.	They cannot be used Temporary tables.
These can support Error Handling.	Raise error,@@ERROR are not allowed.
PRINT and EXEC statements can be written in Procedures.	PRINT and EXEC statements cannot be written in Functions.

     Differences between Temporary Table and Table Variable?

Temporary Table	Table Variable
It is created in TempDB.	It is created in the memory.
You cannot pass TT as parameter to Functions and Stored Procedures.	You can pass TV as parameter to Functions and Stored Procedures.
A TT can have indexes.	A TV can have only a primary index.
A TT can involve in Transactions, Logging (or) Locking.	A TV cannot involve in these. This makes TV faster than a TT.

     Differences between Triggers and Stored Procedures?

Procedures	Triggers
Procedures called explicitly.	Triggers called implicitly.
Procedures can be created without a table.	Triggers can’t be created without a table.
Procedures accept parameters.	Triggers won’t accept parameters.
Procedures return a value.	Triggers won’t return a value.

       Differences between Clustered index and Non clustered index?

Clustered Index	Non clustered Index
Clustered index will alter the physical representation of rows in a table.	This index will not alter the physical representation of rows in a table.
A table can have only One clustered index.	A table can have more non clustered indexes. [In 2005-249, In 2008-999]
It always arranges the data of a table in sorted order.	Data of a table is not arranged in order.
Data pages and Index pages are stored at same level.	Data pages and Index pages are stored at different levels.

  Differences between Star Schema and Snow Flake Schema?  

Star Schema	Snow Flake Schema
Here Dimensions connect to Fact table and resembles like a star.	Just like a star schema but having sub dimensions.
De normalized Data.	Normalized Data.
Less Joins.	More Joins.
More Memory.	Less Memory.
It is Easy to understand.	Complex to understand.
Indexes are more.	Indexes are less.
Performance high.	Performance low.

         Differences between Table and View?

Table	View
Table contains data.	View contains no data only stored queries.
Tables are limited (2 Billion).	No limit for views.
DML operations on any data.	DML operations on related data.
Tables can be associated to any trigger.	Views can be associated with only Instead of Trigger.

     Differences between Simple View and Complex View?

Simple View	Complex View
Simple view is based on one table.	Complex view is based on one (or) more tables.
Simple views can update. [update means allows DML operations]	Complex views  cannot update

         Differences between View and Synonym?

View	Synonym
View is a subset of table.	Synonym is a mirror of table.
View can be based on one (or) more tables.	Synonym is always based on one table.

         Differences between View and Index View?

View	Index View
When view is created the query is stored in DB.	When Index View is created the query result stored in DB.
A View doesn’t contain any data.	Index view contains data.

      Differences between UNION and JOIN?

UNION	JOIN
Accumulates the Data	Relates the Data
Two Dissimilar structures can’t be combined with UNION operator.	Two Dissimilar structures can be Joined.
It combines the result of two Select statements.	It combines the columns from 2 or more tables with joining condition.

     Differences between WHERE and HAVING clauses?

WHERE	HAVING
It is used to filter the data before Grouped.	It is used to filter the data after Grouped.
It can be used in SELECT, INSERT, UPDATE, DELETE statements.	It can be used in only SELECT statement.
Aggregation functions are not used in Where clause.	Aggregation functions are used in Having clause.

   Differences between DELETE and TRUNCATE?

DELETE	TRUNCATE
It is a DML command.	It is a DDL command.
It supports to delete the rows on conditional basis (where clause).	No conditional deletion.
It will not reset the Identity Column.	It will reset the Identity Column.
It is Logged Action.	It is not Logged Action.
It does not de allocate Data pages (memory).	It will de allocate Data pages.
In this Restoring is possible.	In this Restoring is not possible.
Slow in Execution, since it consult log file to store each deleted row.	Fast in Execution, since it does not consult log file.