Lesson 06 (Managing Database)

 

Managing Database

  1. Create database - To create a database 

create database database_name

create database EEC


  1. Display database - To display database 

show databases  


  1. Select database - To select a database  

select database database_name

select database EEC


  1. Removing database - To delete a database 

drop database database_name

drop database EEC


Creating Table in Sql with Constraints (बिशेष शर्त के अनुसार )


Create table emp(empid int, ename varchar(40), salary int);

insert into emp values(1, ‘a’, 2000);

select * from emp;


Various Constraints


Not null or null

This constraints forces the values of a column to non- null values only (column में value भरना अनिवार्य है )


Syntax-

Col_name data_type not null;


create table emp1(empid int, ename varchar(40), salary int not null);

insert into (empid, ename) values(1, ‘a’);

क्योंकि salary के कॉलम को खाली नहीं छोड़ सकते इसलिए error  प्रदर्शित होगी |


describe emp;


table का structure प्रदर्शित होगा | इसमें आप देखंगे की salary को not null प्रदर्शित कर रहा है |


How to change any column to not null


  • Check data

  • Replace Null value to not null values

  • Alter table column.


We want to change salary column to not null column.

  1. Check data

  2. if there is any row who has null values than replace with 0


update emp;

→ set salary=0

→ where salary is null;


alter table emp;

→ change salary salary(int) not null;


insert into emp(empid) values(4);

it will show error, you can not leave salary field blank/not null


Unique constraints

This constraints forces the column to have unique values only, means no duplicat3e data in same column.


Col_name data_type unique.


Create table emp2(empid in unique, ename varchar(40), salary int not null);

now you can’t keep same values in two or more rows.


create table emp3(empid int, ename varchar(40), salary int, constraints u1 UNIQUE(empid, ename));

now empid & ename is unique columns.


Default constraints-

This constraints us used to insert a default values in a column.

col_name data_type defaultvalue;


create table emp4(empid int, ename varchar(40), salary int DEFAULT 10000);

insert into emp4(empid) values (3);

select * from emp4;


But you can insert any value in salary column also.


Auto increment Constraints

It will increase automatic values and by default it is 1 and auto increment by 1


create table emp5(empid int auto increment, ename varchar(40) salary int);

(empid is primary key)

Comments

Post a Comment

Popular posts from this blog

Lesson 07 (Primary Key & Foreign Key)

  Primary Key/Foreign Key Primary Key Combinagtion of column that uniquely identifies a row in table. Rules for Primary Key Primary key must contain unique value. Primary key can’t be null. There is only one primary key in table. Syntax- Col_name data_type PRIMARY KEY. like roll no in every class. We can define more than one column for primary key. PRIMARY KEY(COL1, COL2,......) PRIMARY KEY in existing table ALTER table(table_name) add PRIMARY KEY(primary key column) use college; create table emp11(empid int PRIMARY KEY, ename varchar(10)); describe emp11; create table emp12 (empid int, ename varchar(10),  → PRIMARY KEY(empid, ename); now you can see by describe command create table emp13 (empid int, ename varchar(10)); alter table emp13 → add PRIMARY KEY(empid); FOREIGN KEY It is a field in a table that matches another field of another table. It is used to maintain referential integrity. A table may have more than one foreign key and each fireign key in the child table may re...
Read more

Lesson 03 (Filter Data)

Filter Data with Where clause- Syntax- select              column1, column2…………… from              table1, table2…………………… where condition Data Filter करने के लिए हमें कुछ Conditional & Logical Operators का प्रयोग करना होता है - Conditional Operator =         Equal             बराबर  != or <>     Not Equal         बराबर नहीं  <         Less Than         बड़ा  >         More Than         छोटा  >=         More than Equal     बड़ा या बराबर  <=         Less than Equal     छोटा या बराबर  Logial Operator  An...
Read more

Lesson 05(Modifying Data)

  Modifying Data  (Insert, Update, Delete) Insert command का प्रयोग table में डाटा भरने के लिए किया जाता है | Syntax- insert into table(column1, column2,........) value(value1, value2….) select * from department; insert into department values (8, ‘ICE’); insert into department values (‘temp’, 6); ----error आ जाएगी | insert into department (dname, deptid) values (‘Temp’, 6) हो जायेगा | you can insert multiple rows insert into department values<enter> → (7, ‘A’), → (8, ‘B’);<enter> Insert with SELECT statement  It is used to insert data into table from another table. insert into table_name select col1, col2, …. from table_name; Update Statement Update statement is used to update existing data in a table . Syntax- update table_name set col1=exp1 col2=exp2 where condition We want to change deptname where deptid=7 in dept_temp. update department_temp → set deptname=’Applide Sc.’, → where deptid=7; select * from dept_temp; update dept_temp → set deptname=’test’; sel...
Read more