IP PRACTICAL FILE
1. Heading: Creating a table.
Aim:
To create a table ‘student’ with roll_no of integer data type, name of string data type,
Specialization of string data type, Marks of integer data type.
SQL Query:
create table student (Roll_no int,Name varchar(15),Specialization varchar(15),Marks
int);
Output:
2. Heading: Inserting values in a table.
Aim:
To insert the following details in the student table.
Roll_no Name Specialization Marks
Subhransu 90
11 Computers
Patra
Sudhansu 69
12 English
Patra
13 Jonny English 99
14 Missy Computers 78
15 Jenny Mathematics 96
16 Kyle Computers 97
17 Nathan English 76
18 Abby Computers 85
1
19 John Mathematics 67
20 Steve Sociology 83
SQL Query:
insert into student(Roll_no, Name, Specialization, Marks)
values(11,’Subhransu Patra’,’Computers’,90), (12,’Sushansu Patra’,’English’,69),
(13,’Jonny’,’English’,99), (14,’Missy’,’Computers’,78),
(15,’Jenny’,’Mathematics’,96), (16, ‘Kyle’,’Computers’,97),
(17,’Nathan’,’English’,76), (18,’Abby’,’Computers’,85), (19,’John’,’Mathematics’,67),
(20,’Steve’,’Sociology’,83);
Output:
3. Heading: Query using Round function.
Aim:
Display PT as 50% of Marks and display the result after rounding it off to one decimal
place along with student details.
SQL Query:
Select *,round(Marks*50/100,1) as PT from student;
Output:
2
4. Heading: Query using Power function.
Aim: Display student names, Marks raise 2.
SQL Query:
Select name,Power(Marks,2) as Marks_raise_2 from student;
Output:
5. Heading: Query using Mod function.
Aim:
Display the student details whose marks are divisible by 3.
SQL Query:
Select * from student where mod(marks,3)=0;
Output:
6. Heading: Query using RIGHT function.
Aim:
To display last 4 characters from student names.
SQL Query:
Select name, right(name,4) as Last_4 from student;
Output:
3
7. Heading: Query using LEFT function.
Aim:
To display first 5 characters from student names.
SQL Query:
Select name, left(name,5) as First_5 from student;
Output:
8. Heading: Query using INSTR function.
Aim:
To display the position of ‘n’ in student names.
SQL Query:
Select name, instr(name,’n’) as Position_n from student;
Output:
4
9. Heading: Query using MIN function.
Aim:
To display minimum marks from student table.
SQL Query:
Select min(marks) from student;
Output:
10. Heading: Query using MAX function.
Aim:
To display maximum marks from student table.
SQL Query:
Select max(marks) from student;
Output:
11. Heading: Query using SUM function.
Aim:
To display sum of all students marks from student table.
SQL Query:
Select sum(marks) from student;
Output:
5
12. Heading: Query using AVG function.
Aim:
To display average of all students marks from student table.
SQL Query:
Select avg(marks) from student;
Output:
13. Heading: Query using MONTHNAME function.
Aim:
To display name of the month from your date of birth.
SQL Query:
Select monthname(’1998-07-26’);
Output:
14. Heading: Query using DAYNAME function.
Aim:
To display name of the day from your date of birth.
SQL Query:
Select dayname(’1998-07-26’);
Output:
15. Heading: Query using NOW function.
Aim:
To display present date and time.
6
SQL Query:
Select now();
Output:
16. Heading: Query using GROUP BY clause.
Aim:
To count the number of students based on specialization.
SQL Query:
Select count(*) from student group by Specialization;
Output:
17. Heading: Query using ORDER BY clause.
Aim:
To display the details of students in ascending order based on Marks.
SQL Query:
select *from student order by Marks;
Output:
