Update Query in SQL server 2005

UPDATE CDR (Table name)
SET column1=value, column2=value2,...
WHERE some_column=some_value

Then press F5 to run query

Select query in SQL server 2005

Check data of the content table..

Select * from (table name)


Select query with WHERE clause....

select * from CDR (table name)  where Dt>='9/7/2009  12:00:00 AM' and Dt<='9/7/2009  11:59:00 PM'  and q931 (Table column name) = 'VERIZON USA' (file column name) and timeSlot (Table column name)= 'Success' (file column name)


Sum of select query with where clause....


USE CDR_DATA;
GO
SELECT  SUM(connectionDuration)/60
FROM dbo.CDR
WHERE connectionDuration IS NOT NULL
and q931 = 'WITRIBE' and timeSlot = 'Success'
and Dt >= '8/14/2009 12:00:00 PM' and Dt <= '8/14/2009 11:59:00 AM'
    AND connectionDuration != 0.00 
GO 
Press F5 to run query.

Create table in database......

Q: How to create table in database?

A: Let i tell u in simple query..

USE TestData (Table Name)
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Then press f5 to run query....

Import CSV file to SQL server 2005

Q: How to import csv file in SQL server 2005?

A: It's simple. let i tell u...

BULK
INSERT  CDR (Table Name)
FROM 'E:\cdr\CDR\20091006.txt'  (path location on file)
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

Then press enter F5 to run query

Database connectivity:

Database connectivity:

BindingSource component:
- Name
- DataSource

Binding data to Controls:
TextBox:
- DataBindings
    - Text

ComboBox:
- DataSource
- DisplayMember
- ValueMember


DataGridView control:
-name
- DataSource

Dataset_name.Table_name.Rows(row_index).Item(Column_name/ Column_index)
Dataset_name.Tables(table_name/table_index).Rows(row_index).Item(Column_name/ Column_index)
Dataset_name.Table_name.Rows.count()

Database Programming:

Front-end designing (user interface):
Back-end (database mamnagement)

Database Programming:

Wizard base connectivity:
Connecting to Access database:
- Data + Add new data source
- Follow wizard steps:
- Data + Show data Sources
- Drag table from datasource window to the interface

SQL statement

Select statement:

select   columns   from  table_name
select * from products
select prodid  from products
select   columns   from  table_name  order by column_name   asc/ desc
select * from products  order by prodprice
select   columns   from  table_name  where criteria
select * from products where prodprice>1000
select * from products where prodid between 1 and 10
select * from products where prodname  like 'm%'

SQL FUNCTIONS:
1- Aggregate Functions
2- Scalar Functions

select  count(*)  from products
select sum(quantity) from products
select sum(prodprice) from products
select sum(quantity) from products where prodid<=50
select invoiceno, sum(quantity) from purchase   group by  invoiceno
select invoiceno, sum(quantity) from purchase   group by  invoiceno having invoiceno=1

Selecting data from multiple tables:
Using table joins:

select  table1.columns, table2.columns from table1, table2   where   table1.column=table2.columns
select  personal.name, personal.address, fee.course, fee.advance  from  personal, fee where   personal.rollno=fee.rollno


Types of Joins:
1-Inner joins/ Join/ Equi-Join
select  table1.columns, table2.columns from table1   Join table2   on table1.column=table2.columns
select  personal.name, personal.address, fee.course, fee.advance  from  personal join  fee on  personal.rollno=fee.rollno

- Outer Joins:
     - Right outer join/right join
     - Left outer join/left join
     - Full outer join/Full join
RDBMS  -> relational database management system
- updating main table affects child table too
- deleting record from  main table affects child table too
Table Relationing:
- Primary Key Field: Field in main table/ parent table to create relationship with other tables
- Foreign Key Field:Field in sub table/ child table to create relationship with parent tables

Types of Relationships:
1- One-to-One (common field in both tables must beset as primary key field)      
2- One-to-many (common field in parent tables is set as primary key field)
3- many-to-many (use junction table  to develop this typeof relation ship)

Creating Relationship Diagram:
- right click on diagram in database objects list, select new database diagram, follow wizard steps

DBMS ( Database Management Systems)

Database Management Systems:
- MIcrosoft Access
- Microsoft SQL server

TSQL -> TRANSACT STRUCTURED QUERY LANGUAGE
ENTERPRISE MANAGER: VISUAL/ GRAPHICAL  INTERFACE TO CREATE AND MANAGE DATABASES

QUERY ANALYZER: window to work with databases using SQL statements

Creating new database using ENTERPRISE MANAGER:
- Expand registered SQL server group
- Right click on  databases and select New database
- specify new database name

inventory -> database name

inventory_Data ->inventory_Data.MDF -> data file

inventory_Log ->inventory_Log.LDF  -> transaction log file


Creating new TABLES using ENTERPRISE MANAGER:
- Select and expand database
- select tables from objects list
- right click and select New Table
- Design table structure


Importing/ exporting Data from Other Databases:
- right click on database, select All tasks, select import/export data
- Follow wizard steps

Front end & Back end

Front End (design user interface):
Back End  (binary files, Database):

SQL  -> structured query language

Desktop applications:
   Front end and back end are on the same system

Distributed Applications (Client-server application):
   Front end and back end are on the different system

software developer
database administrator
network administrator
hardware engineers
.....
....

Microsoft Access:
            complete RDMBS

Northwind sample database project:

Main Components of Microsoft Access Database:
- Tables
- Queries
- Forms
- Reports
- Macros
- Modules  
        (VBA  -> visual basic application)
- Pages (data access pages)

Tables: provide basic Structure to store data, all data is stored in rows and columns, each column is a Field, each row is a record

Creating tables in Microsoft Access:
- Select Tables from database object list
      - Create table by using wizard
      - Create table by Entering Data
      - Create table in Design View
          - table designer window

Table designer window: to create/modify table structure

Table structure: style of storage of data
- Field name (column name)
- Data Type
- Field Properties
- Field Description

Files Stream

Files:
Text Files:
Binary Files:

Imports system.io

FileStream:
Dim   File_stream_object as   FileStream
File_stream_object= new FileStream(File_name, File_Open_mode)
Dim stream_writer_object As StreamWriter

stream_writer_object= New StreamWriter(File_stream_object)
stream_writer_object.Writeline(information)
stream_writer_object.close()

File_stream_object.close()

Dim stream_reader_object As StreamReader
stream_reader_object.ReadToEnd()

Binary Files:
Dim stream_writer_object As BinaryWriter

Dim stream_Reader_object As BinaryReader