Database

 Database

Topics :

1. What is Database

2. Types of Database

3. Database Representation

4. DotNet Support Database

5. Database Terminology

6. Types of Field

7. Database tuple

8. Property of Database

9. DataBase Query

10. Types of Query – most imp

11. How To Create Database

12. Database datatype
13. Create table Query
14. Insert Query
15. Database Clause – most imp
16. Select Query – (display database records)
17. Update Query
18. Delete Query
19. SQL Function – imp

20. Database Alas

21. How to display Table structure

22. Alter Command

    22.1 Add new column

    22.2 remove database column

    22.3 rename database column – imp

    22.4 Change Datatype of Existing column

23. How to change rename tablename

24. How to Create Auto generate column

25. How to reset IDENTITY column – imp

26. Create table from a table(create duplicate table)

27. Database Constraints

1. What is Database
  - database is a collection of entity (table)

Ex
    College – database
    Student (roll,name,city)
    Faculty(name,designation,subject)

  

2. Types of Database

    1. Local Database
    2. Global Database (web database)


3. Database Representation

1. Algebric Representation(not related to practical concept)
    - Picture formate of database
    - only theoretical representation of database

2. Query Representation(practical oriented)



4. DotNet Support Database

1. SQL Server
2. XML
3. Excel
4. MySql
    Sqlserver,xml,excel covered here


5. Database Terminology
1. Field
    - Field is representing related data(Records)

        Name             city

        Raj             nagpur

6. Types of Field

1. Multivalue Field (ex. Name : firstname, lastname, middlename)
2. Single Value Field (ex. Rollno.)
3. Null Field (Not Cumpulsory)
4. Referential Field

Ex.
    Student (Roll,name)
    Addmission (roll, subject,branch)

7. Database tuple

Ex.
    Roll     Name      City

    01         Raj         Nagpur
    02       shiva

- complete information in a database called tuples

8. Property of Database

There are following properties
1. IP Address / Servername
2. UserName
3. Password
4. Catelog name (database name)
5. Max Pool (its value always greater than 5000)
6. Integrated Seccurity

- implementation of all property inside the code is called connection string

9. DataBase Query

- query is set of command perform in a database entity

10. Types of Query – most imp

1. Non Query
2. Execute Query

1.Non Query
    - non query is use to reflecting database record(insert,update,delete)

2.Execute Query
    - only display database record (select query)


11. How To Create Database

    - extension ; .mdf



12. Database datatype

1. Int - most used
2. Varchar – most used - store
3. Nvarchar – number and character only
4. Date
5. image


13. Create table Query

Syntax :
Create Table tablename(columname datatype);

Ex.
Create table Student(Roll int, Name varchar(50))

- all types of query in dot net (queryEditor)
serverexplorer > database >rightclick on table > addnew query





14. Insert Query

Methodology of Insert Query

1. Insert All Column
2. Insert Particular Column
3. Inline insert

Insert All Column

Syntax :
    Insert into tablename values(data);

Ex
    Insert into Student values(101,’Ramesh’,’Nagpur’);



Errors
    Input types mismatch error

Insert into Student values(101,’Ramesh’,’Nagpur’,’pune’); - no. of column and supplye value does not match



create table Student(Roll int, Name varchar(50), City varchar(50));
insert into Student values(101,'Rajkmal','Nagpur');
insert into Student values(102,'Ramesh','Nagpur');
insert into Student values(103,'raj','Pune');
insert into Student values(104,'Roshan','Pune');



Insert using particular column

Syntax
    Insert into tablename(columnname) values(data);

Ex
    Insert into Student(Roll,Name) values(108,’Suraj’);
    Insert into Student(Roll,Name) values(108,'Suraj');



Inline Insert

Insert into Student values(201,’Surya’,’pune’),(203,’ravi’,’nagpur’);
Insert into Student values(201,'Surya','pune'),(203,'ravi','nagpur');



15. Database Clause – most imp

Clause is provide the facility for apply the condition/ logic in a sql query

1. Where
2. Between
3. Groupby
4. Orderby
5. In
6. Exist



16. Select Query – (display database records)

1. Select all records

Syntax : 

    Select * from tablename;

Ex :

    Select * from Student; 

(*)is wild card symbol

Select particular column

Select * from Student;


Select using where clause
Display all record from student where roll no. is 101

Select * from student where Roll = 101;


Select using multiple conditions
Display all record from Student whrere roll no is 101 and city is Nagpur

Select * from Student where roll = 101 and City = ‘Nagpur’;


Select using between clause
Display all record from student where roll between 101 to 105

Select * from student whrere Roll between 102 and 105;



Select using group by clause
    - Repeated data value

Select City from Student group by City;



Select using order by

1. Accending
    Keyword - ASC
2. Descending
    Keyword - DESC

Select * from Student order by City ASC;

Select * from Student order by City DESC;


Display all record forom student whrere city is Nagpur pune and Mumbai

Select * from Student where City = ‘Nagpur’ or City =’pune’ or City = ‘Mumbai’;



In clause*
 - Match multiple string in database

Select * from student where City IN (‘Nagpur’,’Mumbail’);
Select * from student where City IN ('Nagpur','Mumbail');


17. Update Query
update name of student where roll no is 101

Syntax: 
    update tablename set columnname = value where condition;

Ex
    Update Student SET Name = ‘Ganesh’ where Roll = 101;


Que
Update name and city from Student where roll no is 102

Update Student set Name = ‘raj’, City = ‘Mumbai’ where Roll = 101;



18. Delete Query

Delete from Student where Roll = 101;

19. SQL Function – imp

Sql function is also called scaler function / aggregate function
All scaler is use to select query
All scaler is use to database column

Syntax
    Scaler (column);

1. sum();
2. avg();
3. count();
4. min()
5. max()
6. top()
7. cast()

Select sum(Roll) from Student;

Function result is called expression



Assignment 1
Perform all scalers



20. Database Alas

Provide temporary of the column

Select Name as “first Name” from Student;
Select Name as "First Name", City as "City name" from Student;



Assignment 2
Create a table Admission
Columns (Roll, Name, City, Email, Mobile, College, Branch, Semester, Address, Total, Percentage, Grade)
Logic
1Generate 20 query using Admission table
2.Insert any 10 Record in Admission table
3.Display Addmission Table with specific Alas name
4.Perform all scaler in admission table
5. Implementation of constraints(percentage colum greater than 60)
6.Implementation of constraints(Total colum greater than 250 and less than 350)
7.Generate alter statement (All)



21. How to display Table structure

Using sp_help Command
sp_help Student;



update modify record

22. alter Command
    - update command is basically use to modify table record
    - alter command is use to modify table structure

There are some types of alter process used in database

1. Add new column
2. Remove database column
3. Rename column
4. Change datatype of existing column

22.1 Add new column

Syntax
    Alter table tablename ADD columnname datatype;

Ex.
    Alter table Student ADD Email varchar(50);



22.2 remove database column

Syntax
    Alter table tablename DROP COLUMN columnname

Ex.
    Alter table Student DROP COLUMN Email;
    Alter table Student DROP COLUMN Email;

22.3 rename database column – imp
    - in this concept using table object method

Student.Roll
Student.City // this concept is called table object
Table ka name with column

Syntax
    Exec SP_RENAME ‘tableobject.columnname’, ‘newcolumnname’, ‘column’;

Ex.
    EXEC SP_RENAME ‘Student.City’, ‘Scity’, ‘COLUMN’;
    EXEC SP_RENAME 'Student.City', 'Scity', 'COLUMN';



22.4 Change Datatype of Existing column

Create table Demo (id int, Name varchar(50));

Syntax
    Alter table tablename alter column columnname datatype
Ex
    ALTER TABLE Demo ALTER COLUMN name nvarchar(50);



23. How to change rename tablename

EXEC SP_RENAME ‘oldtablename’,’newtablename’;
EXEC SP_RENAME ‘Demo’,’Demonew’;



24. How to Create Auto generate column

- Autogenerate column is automatically increase (sr no, id, etc)
- Autogenerate column is to be create using IDENTITY keyword
- Identity cannot us insert query

Syntax
    IDENTITY (starting,1)

Create table Demo2 (id int IDENTITY(1,1), Name varchar(50), City varchar(50));
  
INSERT into Demo2 (Name,City) values (‘Rajkamal’,’Nagpur’),(‘Raj’,’Pune’),(‘Suraj’,’Mumbai’);

Create table Demo3 (id int IDENTITY(101,1), Name varchar(50), City varchar(50));


25. How to reset IDENTITY column – imp
    - reset identity column using truncate command

 
Syntax :
Truncate table tablename;
Ex :
Truncate table Demo2;


26. Create table from a table(create duplicate table)
    - in this concept we have to use select into command



Syntax

Select * into newtablename from oldtaablename
SELECT * INTO newtable from Demo3;
SELECT * INTO newDemo from Demo3;



27. Database Constraints
    - all types of condition in a record using conditional operator/clause
    - all types of condition in a database column using constant;

Types of constraints

1. Table level contant(multiple column)
2. Column level contant(only one column at atime)

Contraints Rule

1. Each constraintst provide specific name in a database concept

Ex.

CONSTANT Name;
CONSTANT Checkshell;


1. Check CONSTANT

Create table MyDemo (id int , Name varchar(50), sal int,CONSTRAINT );
Create table MyDemo (id int , Name varchar(50), sal int, CONSTRAINT checksel CHECK(sal>2000));
INSERT into MyDemo (id,Name,sal) values (1, 'Rajkamal', 300);



Create table Demo3 (id int IDENTITY(101,1), Name varchar(50), City varchar(50));
INSERT into Demo3 (Name,City) values ('Rajkamal','Nagpur'),('Raj','Pune'),('Suraj','Mumbai');
select * from Demo2;
delete from Demo2;
INSERT into Demo2 (Name,City) values ('Rajkamal','Nagpur'),('Raj','Pune'),('Suraj','Mumbai');
Truncate table Demo2;
SELECT * INTO newDemo from Demo3;
select * from newDemo;





Create table MyDemo (id int , Name varchar(50), sal int, CONSTRAINT checksel CHECK(sal>2000));

INSERT into MyDemo (id,Name,sal) values (1, 'Rajkamal', 300);