SQL Test
Tests knowledge of SQL database queries, statements, operators, and functions including DCL statements, aggregate functions, subqueries, joins, and SELECT statement evaluation order.
Questions
Which of the following is not a Data Control Language (DCL) statement?
- COMMIT
- ROLLBACK
- LOCK TABLE
- SAVEPOINT
- SET TRANSACTION
What does the following query do?
SELECT * from Employee WHERE SALARY LIKE '_2%3'
- Finds any values that end with 2
- Finds any values that start with 2 and are at least 3 characters in length
- Finds any values in a five-digit number that start with 2 and end with 3
- Finds any values that have 2 in the second position and end with 3
- None of these
Which of the following is not true about aggregate functions?
- MAX and MIN functions work on numeric and non-numeric data.
- SUM and AVERAGE work on numeric data.
- Aggregate functions do not work on complex mathematical expressions like log, square root, etc.
- SUM and AVERAGE work on non-numeric data.
- There is no influence of distinct keywords in aggregate function.
Which of the following complex expressions is not allowed in SQL?
- LOG(marks)*√total
- MIN(Col1+col2)
- SUM(marks)/COUNT(marks)
- AVG(marks)
- COUNT(*)
What will be the conceptual order of evaluation of the following select statements?
(a) First, the Cartesian product of all tables in the 'form' clause is formed.
(b) From this, rows not satisfying the 'where' condition are eliminated.
(c) Groups not satisfying the having clause are then eliminated.
(d) The remaining rows are grouped in accordance with 'group by' clause.
(e) The UNION, INTERSECT, EXCEPT is taken after each subquery is evaluated.
(f) If the keyword DISTINCT is present, duplicate rows are eliminated.
(g) The expressions of the select clause target list are evaluated.
(h) The set of all selected rows is sorted if an ORDER BY is present.
- a - b - c - d - e - f - g - h
- a - b - d - c - g - f - e - h
- a - b - d - h - c - g - f - e
- a - b - e - g - f - c - d - h
- none of these
Alter command is used:
(a) to delete a column in a table
(b) to change the data type of a column in a table
(c) to add a column in a table
(d) to rename a table
- only a
- a and b
- a, b and c
- all of these
- none of these
Which of the following is not a difference between EXISTS and ANY/ALL?
- EXISTS is more powerful than ANY/ALL.
- All queries represented by ANY/ALL can be obtained by EXISTS operator.
- All queries represented by EXISTS may not be possible with ANY/ALL operator.
- ALL returns TRUE only if all the results of a subquery meet the condition, while EXISTS takes a subquery as an argument and returns TRUE if the subquery returns anything.
- Replacing EXISTS with ANY produces an identical result.
Which of the following is/are correct syntax(es) for insert statement?
(a) INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)] VALUES (value1, value2, value3,...valueN);
(b) INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);
(c) INSERT INTO CUSTOMER (column1,column5,columnN-1)VALUES (value1,value5,valueN-1);
- a and b
- a, b and c
- only a
- only b
- none of these
What will be the output of the following query?
Select sum(price) from room r where roomno not in(select roomno from booking b, hotel h where(datefrom<=current_date and dateto>=current_date)and b. hotelno=h.hotelno and hotelname='XYZ')
Hotel(hotelno, hotelname, city) Room(roomno, hotelno, type, price) Booking(hotelno, guestno, datefrom, dateto, roomno) Guest(gustno, guestname, guestadd)
- Total revenue/expenditure lost from unoccupied room of XYZ hotel in a specific period
- Total revenue/expenditure lost from unoccupied room of XYZ hotel as on date
- Total revenue/expenditure lost from occupied room of XYZ hotel as on date
- Total revenue/expenditure gained from occupied room of XYZ hotel as on date
- None of these
Which of the following is/are the difference(s) between having and where clause?
(a) Where is used to filter rows while having is used to filter groups.
(b) There is no alternative for 'where'. To filter groups, we have operators other than 'having'.
(c) Aggregate cannot be used in where clause. Aggregate can be used in having clause.
- Only a
- Both a and b
- All a, b and c
- Both b and c
- None of these
Which of the following explains 'correlated subquery'?
- Query evaluated once for every row processed by the parent statement
- Query evaluated once for the entire parent statement
- Query that retrieves multiple columns but single row
- Query displaying one column and one row
- Query which retrieves multiple columns and multiple rows
What will be the output of the following query?
select count(*) from employees e1, e2, department d where e1.lname='abc , e1.fname='xyz', e1.empid=d.mgrempid and d.dptno=e2.dptno.
Tables:
Employee(empid,fname,lname,add,dob,position,deptno)
Dpartment(deptno,deptname,mgrempid)
Project(projno,projname,deptno)
Workson(empid,projno,hoursworked)
- It finds out how many employees are managed by 'xyz abc'.
- It finds the list of employees that are managed by 'xyz abc'.
- It counts the number of managers bearing the name 'xyz abc'
- It finds out the managers bearing the name 'xyz abc'
- None of these
What will be the result of the following query?
Select title_id,copies_sold=sum(qty) from sales where ord_date between '1/1/2010 and '12/31/2010' group by all title_id
Sales(title_id,qty,ord_date)
- All the title id's and quantity of books sold
- All the title id's and total quantity sold
- Total quantity of books that were sold during the year 2010
- All the title id's and quantity of books sold during the year 2010
- None of these
What does the following select statement do?
Select fname,lname from employees where exists (select * from dependent where ssn=essn) and exists (select * from department where ssn=mgrssn);
Consider Table: Employee(fname,minit,lname,ssn,bdate,add,salary,dno)
Department(dmname,dno,mgrssn,mgrstartdate)
Work_on(essn,pno,hours)
Project(pname,pno,plocation,dno)
Dept_locations(dno,dlocation)
- Lists the names of all the employees who have managers
- Lists the names of all the managers who have dependents
- Lists the names of managers who have at least one dependent
- Lists the names of employees with at least one dependent
- None of these
What would be the query for enlisting the faculty member who does not teach any class using outer join?
Tables: Faculty(facid,facname,......) Student(stuid,stuname,.......) Class(facid,course,.......)
- select f.facid, f.facname from class c,faculty f where c.facid(+)=f.facid and c.course is null order by f.facname;
- select f.facid, f.facname from class c right join faculty f on c.facid(+)=f.facid where c.course is null order by f.facname;
- select f.facid, f.facname from class c,faculty f where c.facid(+)=f.facid(+) and c.course is null order by f.facname;
- select f.facid, f.facname from class c full join faculty f on c.facid=f.facid where c.course is null order by f.facname;
- all of the above