PRACTICAL LIST – TERM - II
INFORMATICS PRACTICES (065)
Term-II
MYSQL Commands
Lab Record 1
Table: student
Write SQL Commands for the following based on given table.
1. Create a student table with the student id, name, and marks as attributes where the
student id is the primary key.
2. Insert the details of a new student in the above table.
3. Delete the details of a student in the above table.
4. Use the select command to get the details of the students with marks more than 75.
5. Find the min, max, sum, and average of the marks in a student marks table.
6. Find the total number of customers from each country in the table (customer ID,
customer
Name, country) using group by.
7. Write a SQL query to order the (student ID, marks) table in descending order of the
marks
8. Write a SQL query to display the marks without decimal places, display the reminder
after diving marks by 3 and display the square of marks
9. Write a SQL query to display names into capital letters, small letters, display first 3
letters of name, display last 3 letters of name, display the position the letter A in
name
10.Remove extra spaces from left, right and both sides from the text – ” Informatics
Practices Class XII “.
11.Display today’s date in “Date/Month/Year” format.
12.Display dayname, month name, day, dayname, day of month, day of year for today’s
date.
Answers
Q1) Create table student
create table student
(studentid int(3) primary key,
name varchar(20) not null,
marks decimal(5,2));
Q2) Inserting records
Table data:
Q3) Deleting records
delete from student where studentid=5;
Q4) Fetching record with criteria
select *from student where marks>70;
Q5) Maximum, Minimum, Sum and Average of marks
select max(marks), min(marks), sum(marks), avg(marks) from student;
Q6) Group by
select country, count(customer_id) from customer group by country;
Q7) Sorting in descending order
select studentid, marks from student order by marks desc;
Q8) Math functions
select round(marks,0),mod(marks,3),pow(marks,2) from student;
Q9) Text functions
select ucase(name),lcase(name),left(name,3),right(name,3),instr(name,'a') from
student;
Q10) Text Functions
select ltrim(" Informatics Practices Class XII ") "Left Trim", rtrim("
Informatics Practices Class XII ") "Right Trim", trim(" Informatics
Practices Class XII ") “Both”;
Q11) Date function in MySQL
select
concat(date(now()),concat("/",concat(month(now()),concat("/",year(now())))));
Q12) Date function in MySQL
select
dayname(now()),monthname(now()),day(now()),dayname(now()),dayofmonth(now
()),dayofyear(now());
Lab Record 2
Table: Teacher
Write SQL Commands for the following based on given table.
1) Create a teacher table with the T_ID, Name, Age, Gender, Department,
Date_of_join, Salary and Post as attributes where the T_ID is the primary key.
2) Insert the details of a new teacher in the above table.
3) Delete the details of a teacher in the above table.
4) To display all information of TGT teachers.
5) To display the name, post and salary of all teachers in ascending order of their
salaries.
6) To display the total salary of teachers who are in the same post.
7) To display teacher’s name in which ‘ee’ occurs anywhere in the name.
8) To display contents of “Department” field in small letters.
9) To display first 2 characters of the “Department” field.
10) To display 3 characters of teacher’s name starting from 2nd Character.
11) To display the name, age and joining date of teachers who joined in May Month.
12) To display the name and salary of teacher’s after rounding off to the nearest
integer.
13) To display the department name and no of teachers in each department.
14) To display the maximum and minimum date of join of teachers.
15) To display the average salary of male teachers from the table.
Answers:
Q1) Create table
create table Teacher
(T_ID int(3) primary key,Name varchar(20) not null,
Age int(2), Gender char(1),Department varchar(25),
Date_of_join date, Salary decimal(7,2),
Post char(3));
Q2) Inserting records
Table data:
Q3) Deleting Records
delete from Teacher where T_ID=105;
Q4) Fetching Data
select * from Teacher where Post='TGT';
Q5) Using Order by
select Name,Post,Salary from Teacher order by Salary asc;
Q6) Aggregate function
select Post,SUM(Salary) from Teacher group by Post;
Q7) Pattern match
select Name from Teacher where Name like '%ee%';
Q8) String function
select LOWER(Department) from Teacher;
Q9) String Function
select LEFT(Department,2) from Teacher;
Q10) String function
select MID(Name,2,3) from Teacher;
Q11) Date Function
select Name,Age,Date_of_join from Teacher where MONTH(Date_of_join)=5;
Q12) Numeric Function
select Name,ROUND(Salary,0) from Teacher;
Q13) Aggregate Function & Group by
select Department,COUNT(*) from Teacher group by Department;
Q14) Date Function
select MAX(Date_of_join),MIN(Date_of_join) from Teacher;
Q15) Aggregate function
select AVG(Salary) from Teacher where Gender='M';
Lab Record 3
Table: Employee
Write SQL Commands for the following based on given table.
1) Create an Employee table with the EMPNO, ENAME, JOB, MGR, HIREDATE,
Sal, comm and DEPTNO as attributes where the EMPNO is the primary key.
2) Insert the details of a new employee in the above table.
3) Delete the details of an employee in the above table.
4) Write a query to get the details of all employees according to name in descending
order.
5) Write a query to get unique department number from employee table.
6) Write a query to get the total salaries payable to employees who joined in
February month.
7) Write a query to subtract the maximum and minimum salary from employee
table.
8) To display first three characters extracted from jobs of employees 8499 and 8654.
9) To display the position string ‘le’ in the field Job of employee table.
10) To display employee names and the name-lengths for employees 8566 and 8369.
11) To display the maximum, minimum and average salary of employees grouped by
department number.
12) To display the joining date of senior most employee.
13) To display average salary of employees with DEPTNO 20 or 30.
14) To display the number of employees with the same job
15) To display the difference of highest and lowest salary of each department having
maximum salary>1500.
Q1) Create table
Create table Employee
(EMPNO int primary key, ENAME varchar(15) not null,
JOB varchar(20),MGR int, HIREDATE date,
Sal decimal(6,2),comm int, DEPTNO int);
Q2) Inserting a new employee
Table data
Q3) Deleting records
delete from Employee where EMPNO=8521;
Q4) Order by Clause
select *from Employee order by ENAME desc;
Q5) fetching unique data from a field.
select distinct DEPTNO from Employee;
Q6) Single row function and Aggregate function
select SUM(sal) from Employee where MONTH(HIREDATE)=2;
Q7) Aggregate Functions
select MAX(sal)-MIN(sal) from Employee;
Q8) Using relational operator
select LEFT(JOB,3) from Employee where EMPNO =8499 or EMPNO=8654;
Q9) String Function
select instr(JOB,'le') from Employee;
Q10) Numeric Function
select ENAME,length(ENAME) from Employee where EMPNO=8566 or
EMPNO=8369;
Q11) Aggregate Function and group by clause
select DEPTNO,MAX(sal),MIN(sal),AVG(sal) from Employee group by
DEPTNO;
Q12) Aggregate function
select MIN(HIREDATE) from Employee;
Q13) Aggregate function
select AVG(sal) from Employee where DEPTNO=20 or DEPTNO=30;
Q14) Aggregate function
select JOB,count(*) from Employee group by JOB;
Q15) Group by and Having Clause
select Max(sal)-MIN(sal) from Employee group by DEPTNO
having Max(sal)>1500;
**********************************************************************
