Database Creation & Table Manipulations
/*creating a database*/
create database school;
/*show list of databases*/
show databases;
/*to delete a database*/
drop database school;
/*getting into a database school*/
use school;
/*creating a table info
Name Roll Section Subject
*/
create table info(
name varchar(255),
roll int,
section varchar(1),
subject varchar(25)
);
/*to show list of tables present inside school database*/
show tables;
/*to show the structure of table*/
desc info;
/*to add a new column*/
alter table info add column house varchar(255);
/*to add a class column after roll number*/
alter table info add column class int after roll;
/*to add checking to class column*/
alter table info change column class class int check(class>0 and class<13) not null;
/*to add primary key to class roll*/
alter table info change column roll roll int primary key;
/*adding a default value to house set house to white house*/
alter table info change column house house varchar(10) default "White";
/*inserting data into table info*/
insert into info(name,roll,class,section,subject,house) values("Ram",1,2,'A','English','Green');
/*to show the data present in the info table*/
select * from info; /* * means all */
/*for multiple entries*/
insert into info(name,roll,class,section,subject,house)
values("Adam",2,2,'A','English','Green'),("Sam",3,2,'D','History','Green'),("Ajay",4,2,'C','English','Green'),
("Srishti",5,2,'C','Science','Green'),("Souvik",6,2,'B','GK','Green');
/*to delete a entry*/
delete from info where roll=3;
/*to update section of Adam to Section D*/
update info set section='D' where roll=2;
/*to display the names of the students who name begins with S*/
select * from info where name like 'S%';
/*to display the name of the students who has letter a in between the word*/
select * from info where name like 'S%';