SQL
Write SQL commands for the following:
1. To create a database with your name and class in MySQL.(eg: annXIIN)
create database annXIIN;
2. To use this database in order to create tables in it.
Use annXIIN;
3. To create a table “emp ” having the following specification:
Column Name DataType Size Constraint
empno int Primary Key
ename varchar 10 NOT NULL
gender char Default M
Job Varchar 10
Sal Int
create table empl(empno int, ename varchar(10), gender char, job
varchar(10), sal int);
4. To add a new column ‘comm’ to the table “emp” whose data type is ‘int’.
alter table empl add comm int;
5. To add a new column ‘dob’ after the column ‘gender’ having data type date’.
alter table empl add dob date after gender;
6. To increase the size of column ‘ename’ to 15.
alter table empl modify ename varchar(15);
7. To rename the column ‘job’ to ‘desig’.
alter table empl change column job desig varchar(10);
8. To view the table structure.
describe empl;
9. To insert a row into the “emp” table so that each column has a value.
insert into empl values(1, ‘Aby John David’, ‘M’, ‘1990-09-12’,
‘Salesman’, 45000, 4500);
[Link] insert a rows into the “empl” table where ‘comm’ is NULL.
insert into empl(empno, ename, sex, dob, desig, sal) values(13,
‘Zarina Khan’, ‘F’, ‘1989-11-11’, ‘Analyst’, 42500);
[Link] increase the salary of all employees by 10 percent.
update emp set sal=sal+sal/10;
[Link] reduce the salary of those employees who receive some commission by
500.
update emp set sal=sal-500 where comm is not null;
[Link] display all the rows and columns of the “emp” table.
select * from empl;
[Link] delete details of those employees who do not receive any commission.
delete from empl where comm is null;
[Link] remove the “emp” table from the database.
drop table empl;
Consider the following table:
[Link] the command to display the names of all Managers.
[Link] the command to display the no. and name of those employees who
were hired in the year 1981.
[Link] the command to display the name and annual salary of all
employees.
[Link] the command to display the name and job of employees working in
department 20 and earning more than 1000.
[Link] the command to display the no., name and salary of employees
whose salary is not between 1000 and 2000.
[Link] the command to display the names of employees working in
departments 10 and 20.
[Link] the command to display the names of employees whose name has
the second alphabet ‘A’.
[Link] the command to display the name and job of those employees whose
job does not contain the alphabet ‘A’.
[Link] the command to display the job which has some commission.
[Link] the command to display the names of all salesmen in alphabetical
order.
[Link] the command to display the name, job and salary of employees
working in department 10, in descending order of salary.
[Link] the command to display the latest and oldest date of hiring among
all the employees.
[Link] the command to display the number of employees who have a
manager.
[Link] the command to display the total salary of the employees working in
department 20.
[Link] the command to display the average salary for each department.
[Link] the command to display the department-wise count of employees.
[Link] the command to display the maximum and minimum salary for each
job.
[Link] the command to display the average salary of those departments
whose average salary is more than 500.
[Link] the command to display the department no. and no. of employees in
those departments where the number of employees is less than 5.
[Link] the output of the following SQL commands:
(i) select ename, job from empl where deptno <> 20 and deptno != 30;
(ii) select ename, sal from empl where sal between 800 and 1300;
(iii) select ename, job from empl where ename like ‘%E_’ and job like ‘%E_’;
(iv) select distinct job from empl where comm is null;
(v) select emplno, ename, job from empl where deptno=10 order by hiredate;
(vi) select min(ename), max(ename) from empl;
(vii) select count(*) as “No. of Emp not receiving Comm”
from empl where comm is null;
(viii) select deptno, max(sal), min(sal) from empl where deptno=20 or
deptno=30 group by deptno;
(ix) select deptno, max(sal), min(sal) from empl
group by deptno having max(sal) < =5000 and min(sal) > 1000;
Consider the following tables:
[Link] the command to display the name and title of each worker.
[Link] the command to display the worker id, name, job id and salary of all
workers.
[Link] the command to display the job id and title of those workers whose
sales is more than 1250000.
[Link] the command to display the worker id and sales of those workers
who are vice president.
[Link] the command to display all the columns from both the tables
without any column getting repeated.
(OR)
select * from worker join job using(jobid);
[Link] the command to display all the possible combinations of all the rows
from both the tables.
select * from worker, job;
(OR)
select * from worker join job;
(OR)
select * from worker cross join job;
[Link] the output of the following SQL commands:
(i) select title, count(*) from worker w, job j where [Link]=[Link]
group by title;
(ii) select [Link], title from worker w, job j where [Link]=[Link]
group by [Link] having avg(sales) > 1250000;