A. Database practical session-1
(a) Create a database named ‘student’
Ans: CREATE DATABASE student;
(b) Get inside the database.
Ans: USE student;
(c) Create a table named 'employee' with following fields.
Employee_id—int primary key not null auto increment
Employee_name--varchar(100)
Ans: CREATE TABLE employee (
employee_id int primary key not null auto_increment,
employee_name varchar(100)
);
(d) Delete the table.
Ans: DROP TABLE employee;
(e) Know whether that deleted table exists or not.
Ans: SHOW TABLES;
(f) Delete the database which you have created(student).
Ans: DROP DATABASE student;
(g) Know whether that deleted database exists or not.
Ans: SHOW DATABASES;
B. Database practical session-2
a. Create a database named ‘student’.
Ans: CREATE DATABASE student;
b. Create a table ‘student’ with following fields.
student_id--------------integer not null primary key auto increment
student_name---------varchar(100)
student_address-------varchar(100)
student_grade----------integer(int)
ANS;
create table student (
student_id int not null primary key auto_increment,
student_name varchar(100),
student_address varchar(100),
student_grade int );
c. Insert any 6 records using ‘insert’ and ‘values’ command.
ANS:
INSERT INTO STUDENT (student_name,student_address,student_grade) VALUES
('RAHUL GUPTA','KATHMANDU',12),
('RAJNIS BHANDARI','BANEPA',12),
('SAMRAT ADHIKARI','KTM',12),
('ANJAN PUDASAINI','BOUDHA',12),
('SUBAM KARKI','DURBAR MARGA',12),
('ALEX LAL KARN','DHARMASTHALI',1);
d. Display all the records of all fields.(Use select *)
ANS: SELECT * FROM STUDENT;
e. Display all the records of fields student_id and student_name.
Ans: select student_id,student_name from student;
f. Display records of students whose name starts with letter ‘a’.
Ans: select * from student where student_name like 'a%';
g. Display records of students whose name ends at letter ‘y’.
Ans: select * from student where student_name like '%y';
h. Display all the records of students whose grade is 12. Use ‘where’ command.
Ans: select * from student where student_grade=12;
i. Display all the records of students whose grade is 12 and who are from address “Kathmandu”. Use ‘and’ operator.
Ans: select * from student where student_grade=12 and student_address='Kathmandu';
j. Update the student name with any other name who has id 1.
Ans: update student set student_name='Unnat Sapkota' where student_id=1;
k. Delete the record of student whose id is 3.
Ans: delete from student where student_id=3;
l. Display the records of students in sorted order using field ‘student_name’. Use ‘order by field name asc/desc’.
Ans: for ascending :
select * from student order by student_name asc;
for descending :
select * from student order by student_name desc;
m. Alter the table with following fields.
Add one more field student_section---varchar(100). [Use alter and add command]
Ans: alter table student
add student_section varchar(100);
Change the size of field student_address--varchar(200) [Use alter and modify command]
Ans: alter table student
modify student_address varchar(200);
C. Database practical session-3
a. Create a database named ‘employees’.
Ans: create database employees;
b. Create a table ‘employee’ with following fields.
emp_id--------------integer not null primary key auto increment
emp_name---------varchar(100)
emp_position-------varchar(100)
emp_salary----------float
Ans: create table employee (
emp_id int not null primary key auto_increment,
emp_name varchar(100),
emp_position varchar(100),
emp_salary float );
c. Insert any 6 records using ‘insert’ and ‘values’ command.
Ans: insert into employee
(emp_name,emp_position,emp_salary) values
('amrit khadka','officer',43689),
('anjan pudasainee','office assistant',30000),
('rajnis bhandari','security guard',25000),
('shubham karki','teacher',32000),
('kushal sharma','cleaner',45000),
('sonam sherpa','plumber',35000);
d. Display all the records of all fields.(Use select *)
Ans: select * from employee;
e. Display all the records of fields’ emp_id and emp_name.
Ans: select emp_id,emp_name from employee;
f. Display records of employees whose name starts with letter ‘ab’.
Ans: select * from employee where emp_name like 'ab%';
g. Display records of employees whose name ends at letter ‘y’ and starts from ‘b’.
Ans: select * from employee where emp_name like 'b%y';
h. Display all the records of employees whose position is ‘engineer’.
Ans: select * from employee where emp_position='engineer';
i. Display all the records of employees whose salary is in range 30000-45000.
Ans: select * from employee where emp_salary>=30000 and emp_salary<=45000;
j. Update the employees name with any other name who has id 1.
Ans: update employee set emp_name='asia' where emp_id=1 ;
k. Delete the record of employees whose id is 3.
Ans: delete from employee where emp_id=3;
l. Display the records of employees in sorted order. Use ‘order by field name asc/desc’.
Ans: for ascending:
select * from employee order by emp_name asc;
for descending:
select * from employee order by emp_name desc;
m. Alter the table with following fields.
Add one more field employees_address ---varchar(100). [insert after name field]
Ans: alter table employee
add employees_address varchar(100);
Change the size of field emp _position--varchar(200) [Use alter and modify command]
Ans: alter table employee
-> modify emp_position varchar(200);
n. Find the maximum and minimum salary in database.
Ans: select max(emp_salary) from employee;
for minimum salary:
select min(emp_salary) from employee;
o. Find the average salary distribution in database.
Ans: select avg(emp_salary) from employee;
[Note: Use both shell interface and graphical interface for these operations]