EXTRAS

Website Source Code

 

To get the source code of this website contact me. This website was created with Adobe Muse 2017.

 

MORE RESOURCES COMING SOON

MySQL Database Commands <Querry run from right to left>

 

Creating a database:

 

>create database student;

 

Using a database:

 

>use student;

 

Creating a table:

 

>create table student_details(slno int, name varchar(50), city varchar(50), phone varchar(15));

 

Inserting values in a table:

 

>insert into student_details(slno, name, city, phone) values(1, 'Abhijit', 'Kharagpur', '7431985236');

 

Display all values in a table:

 

>select * from student_details;

 

Let we create a new table named student_school which contain 5 columns and 5 rows. The columns are roll, name, sub1, sub2, total. The first 4 columns are completely filled up with data, but we have to fill the total column. Therefore, we need to update data.

 

Case 1: All rows

>update student set total=sub1+sub2;

 

Case 2: Single row

>update student set total=sub1+sub2 where roll=1;

 

Case 3: Two or more rows

>update student set total=sub1+sub2 where roll=1 or roll=2 or roll=4;

>update student set total=sub1+sub2 where roll in(1,2,4);

 

Deleting a table completeley;

 

>drop table student_school;

 

Delete all rows of a table:

 

>delete from student_school;

>truncate student_school;

 

Only one or two or more rows:

 

>delete from student_school where roll in (2.4);

 

View table data:

 

(all column) >select * from student_school where roll in (2,3,4);

(specific column) >select roll, name, total from student_school where roll in (2,3,4);

(condition from roll 2 to 4) >select roll, name, total from student_schools where roll>=2 and roll<=4;

                                              >select roll, name, total from student_school where roll between 2 and 4;

 

Get the highest total no -> max():

 

>select max (total) from student;

 

Get the details of the highest student who got the highest marks(total):

 

>select * from student where total = (select max(total) from student);

 

Get the sum of all subjects -sum():

 

(based on single roll no) >select sum(marks) from student_university where roll=1;

 

Display the sum in table format:

 

>select roll, sum(marks) "total", from student+university group by roll;

 

Display count of subjects, sum and average and display it in table format:

 

>select roll, count(sub) "subject-count", sum(marks) "total", avg(marks) "average" from student_university group by roll;

 

Primary Key and Foreign Key:

 

Let's create two tables emp and salary, Table 1: empno, ename, addr, ph. Table 2: empno, basic, medical, TA, DA, total. The table is full.

 

Creating table emp:

 

>create table emp(emono varchar(10), ename varchar(40), addr varchar(50), ph varchar(15), primary key(empno));

 

Creating table salary:

 

>create table salary(empno varchar(10), references emp(empno), basic float, medical float, TA float, DA float, total float, primary key(empno));

 

Joining Table:

 

>select * from emp e, salary s where e.empno=s.empno;

>select * from emp e join salary s on e.empno=s.empno;

 

Like operator:

 

>select * from emp where ename like 'Abhi%';

 

Primary key enable/disable:

 

>alter table emp enable primary key;

>alter table emp disabale primary key;

 

Column deletion:

 

>alter table emp drop column ph;

 

Rename the column:

 

>alter table emp rename ph to phone;

 

Having Clause:

 

>select roll, avg(marks) "average", from student_university group by roll having avg(marks)>=60;

 

Left outer join:

 

>select * from student s  left outer join marks m on s.roll=m.roll;

 

Right outer join:

 

>select s,roll, s.name, s.addr, s.ph, m.marks from student s right outer join marks m on s.roll=m.roll;

 

Table backup:

 

>create table student_backup as select * from student;

 

Table student_backup is already present so we need to copy only the values:

 

>insert into student_backup select * from student;

 

Union of two tables:

 

>select * from student union emp;

 

Intersection of two tables:

 

>select * from student intersect emp;

 

TOP

CONTACT ME

Email: [email protected]

Mobile: +91-7431922555

OR

License & Terms

© 2018 Debjyoti Gorai. All rights reserved.

CONTACT ME

Email: [email protected]

Mobile: +91-7431922555

© 2018 Debjyoti Gorai. All rights reserved.

CONTACT ME

Email: [email protected]

Mobile: +91-7431922555

© 2018 Debjyoti Gorai. All rights reserved.

CONTACT ME

Email: [email protected]

Mobile: +91-7431922555

OR

© 2018 Debjyoti Gorai. All rights reserved.

CONTACT ME

Email: [email protected]

Mobile: +91-7431922555

OR

© 2018 Debjyoti Gorai. All rights reserved.

CONTACT ME

Email: [email protected]

Mobile: +91-7431922555

OR

License & Terms

© 2018 Debjyoti Gorai. All rights reserved.

CONTACT ME

Email: [email protected]

Mobile: +91-7431922555

OR

License & Terms

© 2018 Debjyoti Gorai. All rights reserved.

CONTACT ME

Email: [email protected]

Mobile: +91-7431922555

OR

License & Terms

© 2018 Debjyoti Gorai. All rights reserved.

CONTACT ME

Email: [email protected]

Mobile: +91-7431922555

OR

License & Terms

© 2018 Debjyoti Gorai. All rights reserved.

000webhost logo