21.
SQL COMMANDS
AIM- To write a SQL Queries to implement the following commands on
the student table:
● Create a student table
● Insert data.
● Alter table to add new attributes / drop attribute
● Update table to modify data
● Order By to display data in ascending / descending order
● Delete to remove tuple(s)
● Group BY and find the min, max, sum, count and average
SOFTWARE USED- MySQL Workbench 8.0 CE
INPUT:
● To Create Database Studentdb
CREATE DATABASE Studentdb;
USE Studentdb;
● To Create Table Student
CREATE TABLE Student (Rollno int Primary Key, stuname varchar(30) not
null, class varchar(5) not null, section char(1) not null, classstream
char(20) not null);
DESC Student;
OUTPUT:
● To Insert values to the table Student
Insert into student values(1, "Akhil", "XII", "A", "Science"), (2, "Satya", "XII", "C",
"Science"), (3, "Antony", "XII", "D", "Commerce"), (4, "Vishal", "XII", "E",
"Humanities"), (5, "Deepak", "XII", "A", "Science"), (6, "Brij", "XII", "B", "Science");
● To display the student table
SELECT * FROM STUDENT;
OUTPUT
● To display student table using where clause
select * from student where stuname like '%i%';
OUTPUT
● To Add Column In Table Using Alter Command
Alter table student add(Substream char(20) not null);
OUTPUT:
● To Update the values.
update student set Substream = "Computer Science" where rollno = 1;
update student set Substream = "Biology" where rollno = 2;
update student set Substream = "Maths" where rollno = 3;
update student set Substream = "NA" where rollno = 4;
update student set Substream = "Computer Science" where rollno = 5;
update student set Substream = "Computer Science" where rollno = 6;
OUTPUT:
● To arrange the rows in ascending / descending order using Order By
Command
Select stuname, section,Substream from student order by Substream;
OUTPUT:
● To use Aggregate Function with Group By Command
Select Count(*), section from student Group By section Having Count(*)>=1;
OUTPUT:
● To join to table using Equi join
select stuname, stud.Rollno, Total from student, stud where student.Rollno=stud.Rollno;
OUTPUT:
● To find the Cartesian Product
select * from student, stud;
OUTPUT:
