GRADE:12
SUB: COMPUTER SCIENCE TOPIC: SQL
Consider a relation/table EMP and give the correct answer of following queries.
Relation EMP
Empno Ename Job department Hiredate Sal Comm Sex Deptno
1 Pankaj Clerk Sales 10/01/97 15000 20000 M 20
2 Shalini Salesman Sales 24/03/98 25000 200 F 20
3 Sanjay Salesman Personal 12/12/96 23000 300 M 30
4 Sudha Manager Sales 01/07/99 45000 400 F 20
5 Rakesh Analyst Operational 05/09/97 55000 NULL M 10
6 Shakeel Clerk Operational 27/06/98 15000 300 M 10
7 Surya Manager Operational 25/02/97 45000 210 M 10
8 Shikha Analyst Personal 31/07/97 55000 Null F 30
9 Ramesh President Personal 12/03/97 70000 230 M 30
Q1. Which is true in respect of Select Statement?
(a) By Select we can retrieve all the rows from table.
(b) By Where clause with select we can retrieved selected rows from table.
(c) We can retrieve unique rows from table with the use of Distinct key word.
(d) All of the above.
Q2. The clause which is used to group rows based on distinct values that exist for
specified column.
(a) Group by clause
(b) Having clause
(c) Order by Clause
(d) Where Clause
Q3. For conditionally retrieval of row from groups which clause is used?
(a) Where clause
(b) Having Clause
(c) Order By Clause
(d) (a) & (b) both
Q4. Group functions are also known as.
(a) Aggregate functions
(b) Multiple row functions
(c) Single row functions
(d) (a) & (b) both
Q5. Which option cause a group function to consider only distinct values.
(a) All
(b) Distinct
(c) Unique
(d) Diverse
Q6. Which option cause a group function to consider all values including all
duplicated.
(a) All
(b) Distinct
(c) Unique
(d) Diverse
Q7. Which is not a group function?
(a) AVG
(b) COUNT
(c) MAX
(d) MOD
Consider a relation/table EMP and DEPT and give the correct answer of following queries.
Relation EMP
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 1980-12-17 800.00 NULL 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 NULL 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 NULL 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 NULL 10
7788 SCOTT ANALYST 7566 1982-12-09 3000.00 NULL 20
7839 KING PRESIDENT NULL 1981-11-17 5000.00 NULL 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1983-01-12 1100.00 NULL 20
7900 JAMES CLERK 7698 1981-12-03 950.00 NULL 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 NULL 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 NULL 10
Relation DEPT
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
Q8. SELECT AVG(SAL) FROM EMP WHERE JOB = ‘CLERK’;
(a) 1037.5
(b) 2073.21
(c) 1040
(d) 2074
Q9. SELECT COUNT(*) FROM EMP WHERE DEPTNO = 10;
(a) 14
(b) 3
(c) 4
(d) 5
Q10. SELECT COUNT(DISTINCT JOB ) FROM EMP;
(a) 14
(b) 5
(c) 4
(d) 6
Q11. SELECT COUNT(ALL JOB ) FROM EMP;
(a) 14
(b) 5
(c) 4
(d) 6
Q12. SELECT MAX(SAL) FROM EMP WHERE JOB = ‘MANAGER’;
(a) 2975
(b) 5000
(c) 3000
(d) 2850
Q13. SELECT MIN(HIREDATE) FROM EMP;
(a) 1980-12-17
(b) 1983-01-12
(c) 1982-12-09
(d) None
Q14. SELECT MAX(HIREDATE) FROM EMP;
(a) 1980-12-17
(b) 1983-01-12
(c) 1982-12-09
(d) None
Q15. SELECT SUM(COMM) FROM EMP;
(a) Null
(b) 0
(c) 2200
(d) 1400
Q16. Which statement is used to display the total no. of employees in each
department?
(a) SELECT COUNT(*) FROM EMP WHERE DEPTNO;
(b) SELECT COUNT(*) FROM EMP GROUP BY DEPTNO;
(c) SELECT COUNT(DEPTNO) FROM EMP GROUP BY DEPTNO;
(d) (b) & (c) both
Q17. To display the jobs where the number of employees is less than 3.
(a) SELECT JOB, COUNT(*) FROM EMP WHERE COUNT(*) < 3;
(b) SELECT JOB, COUNT(*) FROM EMP WHERE COUNT(*) < 3 GROUP BY
JOB;
(c) SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB WHERE COUNT(*)
< 3;
(d) SELECT JOB, COUNT(*) FROM EMP GROUP BY JOB HAVING COUNT (*)
< 3;
Q18. Which join is used for display all possible concatenations are formed of all rows of
two or more tables.
(a) Unrestricted join
(b) Cartesian Join
(c) Equi Join
(d) (a) & (b) both
Q19. How many row are returned when we execute ‘SELECT * FROM EMP, DEPT’;
(a) 14 (b)
4
(c) 18
(d) 56
Q20. To display the name of employee & department name the MySQL statement used:
(a) SELECT ENAME, DNAME FROM EMP, DEPT;
(b) SELECT ENAME, DNAME FROM EMP, DEPT WHERE DEPTNO=DEPTNO;
(c) SELECT ENAME, DNAME FROM EMP, DEPT WHERE
[Link]=[Link];
(d) None of the above
Q21. The join where columns are compared for equality is called ………………
(a) Equi Join
(b) Natural Join
(c) Cross Join
(d) Right Join
Q22. The join in which only one identical column exists is called ………
(a) Equi Join
(b) Natural Join
(c) Cross Join
(d) Right Join
Q23. Which statement represent Cartesian join?
(a) SELECT * FROM EMP, DEPT;
(b) SELECT * FROM EMP JOIN DEPT;
(c) SELECT * FROM EMP CROSS JOIN DEPT;
(d) All of the above
Q24. Using sub-clause with JOIN causes a ……………… join whereas on sub-clause
with JOIN produces ………… join.
(a) Natural &Equi
(b) Equi& Natural
(c) Cross &Equi
(d) Natural & Cross.
Q25. Using …………. all rows from the first table will be returned whether there are
matches in the second table or not.
(a) LEFT JOIN
(b) RIGHT JOIN
(c) EQUI JOIN
(d) CROSS JOIN
Q26. Using …………. all rows from the second table will be returned whether there are
matches in the first table or not.
(a) LEFT JOIN
(b) RIGHT JOIN
(c) EQUI JOIN
(d) CROSS JOIN
Q27. To display the name of employees who are getting more salary than the maximum
salary of clerks.
(a) SELECT ENAME FROM EMP WHERE SAL >(SELECT MAX(SAL) FROM
EMP WHERE JOB=’CLERK’);
(b) SELECT ENAME , MAX(SAL) FROM EMP WHERE JOB=’CLERK’;
(c) SELECT ENAME FROM EMP WHERE SAL >MAX(SAL); (d) NONE
Q28. Scalar functions are also known as :
(a) Single row function
(b) Multiple row functions
(c) Group functions
(d) None
Answers
[Link]. Answers
1 a
2 c
3 c
4 a
5 c
6 c
7 d
8 d
9 b
10 c
11 b
12 b
13 d
14 a
15 b
16 d
17 b
18 a
19 d
20 a
21 b
22 b
23 a
24 a
25 b
26 a
27 b
28 c
29 d
30 d
31 d
32 c
33 a
34 b
35 d
36 a
37 a
38 b
39 a
40 a