SIMPLE QUERIES IN SQL || Computer Science || SQL

 

SIMPLE QUERIES IN SQL

Very Short Answer Type Questions  Q.1     Name some basic MySQL SQL elements.

Ans: Some basic MySQL SQL elements are : (i) Literals (ii) Datatypes (iii) Nulls (iv) Comments. Q.2        Differentiate between CHAR and VARCHAR Datatypes.

Ans: The difference between CHAR and VACHAR is that of fixed length and variable length

respectively.

Q.3     Write two usage of DESC in SQL.

Ans: DESC is used with Order by Clause in select statement which displays the data in descending

order. And another user of DESC is that it is used to display the structure of table. 

Q.4       Consider the following Table named „empl‟ and Write SQL commands from (a) to (n)

   

(a) Display all the records from table empl.

Ans: select * from empl;

(b) Display EmpNo and EName of all employees from the table empl.

Ans: select EmpNo, EName from empl;

(c) Display employee name, salary, and department number who are not getting commission from table empl.

Ans: select EName, sal, deptno from empl where comm  = NULL;

(d) Display employee number, name, sal*12 as Annual salary whose commission is not NULL from table empl.

Ans: select EmpNo, EName, sal*12 ‗Annual Salary‘ from empl where sal<>NULL; (e) List all department numbers from table empl.

Ans: select deptno from empl;

(f)  List all unique department numbers from table empl.

Ans: select distinct(deptno) from empl;

(g) List the details of all clerks who have not been assigned department as yet.

Ans: select * from empl where job=‘CLERK‘ and deptno=NULL;

(h) Display the details of employees whose name have only four letters.

Ans: select * from empl where EName like ‗_ _ _ _‘;

(i)  Display the details of all employee whose annual salary is between 25000 to 40000.

Ans: select * from empl where sal between 25000 and 40000; (j) How many job types are offered to employees?

Ans: select count(distinct(job)) from empl;

(k)       List the employees who earn more commission than their salaries.

Ans: select * from emple where sal<comm;

(l)         Display name, job title and salary of employee who do not have manager. Ans: select EName, job, sal from empl where mgr = NULL;

(m)      Display the name of employee whose name contains „A‟ as third letter.

Ans: select EName from empl where EName like ‗_ _ A%‘

(n)       Display the name of employee whose name contains „L‟ as any letter. Ans: select EName from empl where EName like ‗%L%‘

 

Consider the above table „empl‟ and give answer-

Q.5     Write a query to display EName and Sal of employee whose salary is greater than or equal to 2200 from table empl.

Ans: select EName, sal from empl where sal>=2200; 

 

Ans:  (a) Select * from CLUB;

(b)  Select COACHNAME, DATEOFAPP from CLUB order by DATEOFAPP desc; (c)  Select COACHNAME, PAY, AGE, PAY*15/100 ‗BONUS‘ from CLUB;

 

 

 

 

 

 

 

 

 

 

 

 

Ans:  (a) select * from STUDENT1 where Stream = ‗NONMEDICAL‘;

(b)  select Name from STUDENT1 where CLASS like ‗12_‘ order by Stipend;

(c)  select * from STUDENT1 order by AvgMark desc;

(d)  select Name, Stipend, Stream, Stipend*12 ‗Stipend in a Year‘ from STUDENT1;