Scribd
Upload
47 views10 pages

MySQL Database Operations Guide

The document outlines SQL commands for managing a database named 'ryan' with a 'student' table, including operations like creating tables, updating records, and querying data using various conditions. It also covers SQL joins, demonstrating natural joins, equi joins, and cross products with a second 'activity' table. Additionally, it includes aggregate functions for calculating maximum, minimum, sum, average, and count of student marks.

Uploaded by

souldream625
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
47 views10 pages

MySQL Database Operations Guide

The document outlines SQL commands for managing a database named 'ryan' with a 'student' table, including operations like creating tables, updating records, and querying data using various conditions. It also covers SQL joins, demonstrating natural joins, equi joins, and cross products with a second 'activity' table. Additionally, it includes aggregate functions for calculating maximum, minimum, sum, average, and count of student marks.

Uploaded by

souldream625
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Program File: DBMS, MySQL

create database ryan;


use ryan;
create table student (RollNo int Primary Key, Name varchar(20)
not null, Std char(3), Sec char(1), DoB date, Agg_Marks
decimal(6,2));
select * from student;
1) Distinct: select distinct Agg_Marks from student;

2) Update: update student set Agg_Marks=490 where RollNo=8;


3) Alter Table Add: Alter table student add Percentage decimal(3,2);

4) Alter Table Drop: Alter table student drop Percentage;


5) Alter Table Modify: Alter table student modify Agg_Marks decimal
(5,2);

6) Like: select * from student where DoB like '2008-02%';

7) Not Like: select * from student where DoB not like '2008-11%';
8) Null: select * from student where Agg_marks is null;

9) Not Null: select * from student where Agg_Marks is not null;

10) Between: select Agg_Marks between 450 and 500 from student;
11) Not Between: select Agg_Marks not between 450 and 500 from
student;

12) In: select * from student where Sec in ('A');

13) Not In: select * from student where Sec not in (‘A’);
14) Order by: select * from student order by Agg_Marks;

17) Aggravate Functions:


a) Max: select max(Agg_Marks) from student;

b) Min: select min(Agg_Marks) from student;

c) Sum: select sum(Agg_Marks) from student;


d) Avg: select avg(Agg_Marks) from student;

e) Count: select count(*) from student;

18) Delete: delete from student where rollno=10;


SQL Joins
create table activity (ActID int Primary Key, RollNo int, ActName
varchar(25), Fees int);
Tables Used:

1) Natural Join: select * from student natural join activity;


2) Equi Join: select [Link], Name, ActName, Fees from student,
activity where [Link]=[Link];

3) Cross Product: select Name, ActName from student,activity;

You might also like