DATABASE
****DATA BASE*****
Definition:- Data base is a collection of entity(table).
example:-students , faculty is called entity.
Entity :- It is a collection of record.
****TYPES OF DATA BASE****
1.local data base
2.global data base(web data base)
***DATA BASE TERMINOLOGY****
1.field
roll name age
1 Ramesh 18
2 Rahul 11
3 Rakesh
field is representing a record (roll,name,age is called field).
2.Types of field
a.single value field(roll,enrollement).
b.multivalue(name,first name,last name,middle name).
c.null field.It is called blank field.
3.Data base tuple.(most imp)
Complete record in a entity is called tuple.
****HOW TO MANAGE DATA BASE RECORD****
manage all data base record using key.
What is key???
key is a special type of colunm (field) for managing complete information in a table.
****TYPES OF KEY****
1.primary key
2.unique key
3.foreign key.
1.primary key
a.no repeat
b.no blank(eg.roll no)
2.unique key
a.no repeat
b.blank(eg.mobile no)
3.foreign key(mmimp-repeat ask que)
roll(pk) name city country state subject branch semester admission fees deposite balance
*pk-primary key
1.table is complex if the number of column are increase.
2.reduce the complexity with the help of creating sub table.
Basicdetail(table)
roll(reference key) name city country state
Subject (table)
roll(reference key) branch semester subject
FEES(table)
roll(reference key) admission fees deposite balance
***IMPORTANT POINTS*****
1.Foreign key is called refrential key.
2.In foreign key concept we have to use one column common to all for eg.roll no.
3.only primary key is reffered as foreign key.
4.normalization is a technique to reduce complexity of data base(entity).
****DATA BASE QUERY****
What is query?????
Query is a set of command perform in data base.
Types of query:
1.non query
non query is reflecting the table(insert,update,delete)
2.execute query
only display the table no any change(select query).
****HOW TO CREATE DATABASE******
step 1.open sql server studio.
step 2. right click on data base folder in object explorer window
step 3. right click on data base->new database->name of database->ok
****HOW TO CREATE DATABASE IN DOT NET ENVIRONMENT*****
if all library in dot net installed in a system then data base create successfully in dot net otherwise it returning the error(instant database).
step.1 open dot net project.
step 2. select root in solution explorer -> website menu->add new item-> c#
step 3.select sql server database visual c#
step 4.name of the data base->add->select yes
****HOW TO PERFORM QUERY****
a.in sql server studio
step 1. open sql server studio.
step 2. select your data base.(eg college)
step 3.click on new query option.(down the tool menu)
b.how to perform query in visual studio dot net.
step 1.open project.(file-open-website)
step 2.go to the server explorer.
step 3.select your database(eg college.mdf)->right click->new query window.
***HOW TO CREATE TABLE(ENTITY)****
1.create table query.
data type in data base:
1.int
2.varchar(it accepts no,char,symbol,etc.)
3.nvarchar(only accepts no and char)
syntax:create table tablename(column type);
eg.create table student(roll int,name varchar(50));
output:
roll name
2.create table query in dot net
How to view your table
in studio only perform table hirarchy.
database->table->table name->column
how to view your table in visual studio.
database->table->column
create table employe(id int,name varchar(50),age int,city varchar(50),mobile varchar(20));
*****Insert query****
1.insert all record
2.insert particular column record
note:string data value used inside single quotes(' ').
1.insert all record
insert into employee values(1,'ramesh', 18,'nagpur','9988776655');
insert in particular column
Note:if there is no record in a table then default value is null.
*****HOW TO DISPLAY RECORD*****
display all record using select query.
1.display all record
select * from tablename;
2.display particular column
select name,age from tablename;
DATABASE CLAUSES:
1.where clause(apply the condition)
2.between clause(apply range)
3.order by clause(to arrange record)
4.group by clause(to combined the record)
1.where clause
select * from tablename where city='nagpur';
SELECT id, name, age, city, mobile
FROM employe
WHERE (city = 'nagpur')
***Delete query****
IMP POINT: delete and update does not execute without where condition.
delete from employe where age=22;
***UPDATE QUERY****
update employe set name='sumit' where age=18;
HOW TO REMOVE TABLE
drop table product;
*****HOW TO SET HEADING OF THE COLUMN*****
1.roll,pname
using alias concept we have to change heading of the particular column.
select name as 'employe name' from employe;
**HOW TO DISPLAY STRUCTURE OF THE TABLE
sp_help employe; it is used to display the table information.
table name
owner detail-bydefault dbo(data base object).
**HOW TO SEARCH MULTIPLE STRING IN A TABLE
multiple string search with the help of IN(used for multiple search) operator.
write a query to find multiple city in a table(nagpur,delhi).
select * from employe where city in('nagpur','delhi');
without using IN operator(using OR operator)
select * from employe where city='nagpur' or city='delhi';
*****MMIMP:alter query******
alter query just provide following functionality
1.add a new column in existing table.
alter table employe ADD state varchar(50);
2.remove column.
alter table employe drop column state;
****group by clause***
it is used to display repeated entry one time.
select city from employe group by city;
HOW TO CHECK NULL RECORD
select * from employe where city is NULL;
*************SQL FUNCTION**************
this is also called agreegate function.
there are some type of agreegate function
1.sum()
2.avg()
3.min()
4.max()
5.count()
all function apply only for column
select sum(age) from employe;
select min(age) from employe;
select max(age) from employe;
DATA BASE VIEW
It is called virtual table.
It is not physically present in data base.
There are following operation performed in a view
1.create view
2.update view
3.delete view
example:-
table name student
column roll name city
1 Rucha Nagpur
2 Riya chandrapur
500 record in a above table
store all record in nagpur city
select*from student where city='nagpur';
view is store result of query
1.How to create view.
create view myview as select id,name,age from employe;
2.how to display view record
select * from myview ;
3.how to modify view
to modify the view using alter command
alter view myview as select id,name from employe;
select * from myview;
HOW TO CREATE KEY IN A DATABSE
*****How to create primary key********
create table demodb(id int PRIMARY KEY,name varchar(50));
insert into demodb values(1,'demo1');
insert into demodb (name) values ('mohan');//primary key property.
*****How to set not null in a specific column******
create table mydemo(id int,mobile varchar(20) NOT NULL);
insert into mydemo values(1,'9421725666');
insert into mydemo values(2);
NOTE:-CREATE A TABLE INCLUDIG ALL PROPERTY(PRIMARY KEY NOT NULL)
NOTE:-CONVERT ALL PREVIOUS USED QUERY INTO VIEW.
select * from table where age>18;
create view myview as select * from table where age>18 ;
*****HOW TO CREATE FORIGHN(reference)KEY******
product table:-
productid,name,category
1 A A1
2 B B1
3 C C1
inventry table:-
invid,productid,quantity
01 1 10
02 2 20
03 3 30
04 4 50
create productid in foreign key
create table product1(pid int PRIMARY KEY,pname varchar(50),category varchar(50));
insert into product1 values(1,'shirt','cloth');
insert into product1 values(2,'sari','silk');
insert into product1 values(3,'pant','cloth1');
insert into product1 values(4,'top','cloth2');
insert into product1 values(5,'jeans','cloth3');
******create forign key with the help of constraint******
create table inventry(invid int PRIMARY KEY,pid int NOT NULL,qty int, CONSTRAINT fkey FOREIGN KEY(pid) REFERENCES product1(pid));
insert into inventry values(01,1,100);
insert into inventry values(02,2,200);
insert into inventry values(03,3,300);
insert into inventry values(04,4,500);
insert into inventry values(06,6,600);//error
******HOW TO CREATE PRIMARY KEY USING CONSTRAINT******
create table school(id int,name varchar(50), CONSTRAINT pk PRIMARY KEY(id));
when we create primary key using constraint is called table level primary key.
when we create primary key with column is called column level primary key.
********HOW TO CREATE CHECK CONSTRAINT*********
check constraint is used to apply condition in a column
create table staff(sid int,name varchar(50),age int,CONSTRAINT chkage CHECK (age>18));
insert into staff values(1,'ABC',18);
insert into staff values(2,'XYZ',20);
insert into staff values(3,'PQR',28);
insert into staff values(4,'EFG',10);//Error
MMMIMP
********JOINING**********
Joining is used to generate the result with the help of multiple table.
Basic Joinings Operation:-
1.Inner join(simple join)
2.Left outer join(Left join)
3.Right outer join(Right join)
4.Full outer join(full join)
NOTE:-All join operation is used to table object.
example:-
table name-student(roll,name,branch)
table name-admission(roll,semester,year)
syntax for table object
tablename.columnname;
select student.roll,student.name,student.branch,admission.semester,admission.year from student,admission;
Note:-In case of joining, use common field/column in all table.
1.Inner joining(simple join)
Inner join is used to equals condition.
create table inventry1(pid int,pname varchar(50));
insert into inventry1 values(1,'laptop');
insert into inventry1 values(2,'desktop');
insert into inventry1 values(3,'harddisk');
insert into inventry1 values(4,'memory');
create table product2(pid int,brand varchar(50));
insert into product2 values(1,'dell');
insert into product2 values(2,'hp');
insert into product2 values(3,'seagate');
****HOW TO PERFORM INNER JOIN********
note:-all join is to be activated using ON keyword.
select inventry1.pid,inventry1.pname,product2.brand from inventry1 INNER JOIN product2 ON inventry1.pid=product2.pid;
2.Left outer join
1.match left side table to right side table
2.it returning the null value if condition not match
example:
inventry1 product2
pid pname pid brand
1 a1 1 b1
2 a2 2 b2
3 a3
4 a4
Result:
1 a1 b1
2 a2 b2
3 a3 null
4 a4 null
select inventry1.pid,inventry1.pname,product2.brand from inventry1 LEFT OUTER JOIN product2 ON inventry1.pid=product2.pid;
FULL OUTER JOINING
match both outer
select inventry1.pid,inventry1.pname,product2.brand from inventry1 FULL OUTER JOIN product2 ON inventry1.pid=product2.pid;
******Between clause*******
select * from employe where age between 21 and 28;
order by clause(to arrange record)
two types of arrangements
1.ascending (ASC)
2.decending(DESC)
select * from employe order by name;
select * from employe order by age DESC;
select * from employe order by age ASC;
****create ,update and delete view.
create view mynewview as select name,age from employe;
select * from mynewview;
update view
alter query
alter view mynewview as select name,age,city from employe;
select * from mynewview;
remove(delete)view
drop view command
drop view mynewview;
select * from employe where age > 18 and city='nagpur';
****how to create unique key******
create table uniqdb(id int,empno int NOT NULL, CONSTRAINT ucheck UNIQUE(empno));
******store procedure******
1.store procedure is basically used to reduce the complexity of query.
2.store procedure is used to specific command:-1.create 2.alter
*****HOW TO CREATE PROCEDURE COMMAND******
SYNTAX:-
create procedure name
as
BEGIN
query logic
END;
example:-
create procedure pro1
as
BEGIN
select * from employe;
END;
*****HOW TO UPDATE PROCEDURE*****
alter procedure command
syntax:-
alter procedure procedurename(already exists)
as
BEGIN
logic
END;
example:-
alter procedure pro1
as
BEGIN
select * from student1
END;
****HOW TO EXECUTE PROCEDURE******
to execute the procedure we have to use EXEC or EXECUTE command.
EXEC pro1;
EXECUTE pro1;
*******PROCEDURE PARAMETER******
create table t1(name varchar(50),city varchar(50));
1.procedure parameter is used to communicate query to procedure.
2.all parameter is representing @ symbol
syntax:-
create procedure procedurename(@variable as datatype)
as
BEGIN
logic
END;
Example:-
create procedure pro2(@name1 as varchar(50),@city1 as varchar(50))
as
BEGIN
insert into t1(name,city)values(@name1,@city1);
END;
EXEC pro2 'mohan','nagpur';
Task for practice: Apply queries according to given table.
table 1:-actor
create table actor(act_id int PRIMARY KEY,act_fname char(20),act_iname char(20),act_gender char(1));
table 2:-directorcreate table director( dir_id int PRIMARY KEY,dir_fname char(20),dir_iname char(20));
table 3:-moviecreate table movie(mov_id int PRIMARY KEY,mov_title char(50),mov_year int,mov_time int,mov_lang char(50),mov_dt_rel int,mov_rel_country char(5));
table 4:-reviewercreate table reviewer(rev_id int PRIMARY KEY,rev_name char(30));
table 5:-genrescreate table genres(gen_id int PRIMARY KEY,gen_title char(20));
query 1:
create table movie_cast(act_id int NOT NULL,mov_id int NOT NULL, CONSTRAINT f3key FOREIGN KEY(act_id) REFERENCES actor(act_id),CONSTRAINT f4key FOREIGN KEY(mov_id) REFERENCES movie(mov_id));
query 2:create table movie_direction(dir_id int NOT NULL,mov_id int NOT NULL, CONSTRAINT f5key FOREIGN KEY(dir_id) REFERENCES director(dir_id),CONSTRAINT f6key FOREIGN KEY(mov_id) REFERENCES movie(mov_id));
query 3:create table movie_genres(mov_id int NOT NULL,gen_id int NOT NULL, CONSTRAINT f7key FOREIGN KEY(mov_id) REFERENCES movie(mov_id),CONSTRAINT f8key FOREIGN KEY(gen_id) REFERENCES genres(gen_id));
query 4:
create table rating(mov_id int NOT NULL,rev_id int NOT NULL, CONSTRAINT f9key FOREIGN KEY(mov_id) REFERENCES movie(mov_id),CONSTRAINT f10key FOREIGN KEY(rev_id) REFERENCES reviewer(rev_id));
Comments
Post a Comment