Employee (EmpCode, EmpTitle, Fname, Lname, Salary, MgrCode) Which of the following queries display the number of employees managed by each manager?

Select one:

a.SELECT count(*), m.Lname FROM employee e, employee m WHERE e.MgrCode=m.EmpCode GROUP BY m.Lname;

b.SELECT count(*), m.Lname FROM employee e, employee m WHERE e.EmpCode=m.EmpCode GROUP BY m.Lname;

c.SELECT count(*), m.Lname FROM employee e, employee m WHERE e.MgrCode=m.MgrCode GROUP BY m.Lname;

d.SELECT count(*) m.Lname FROM employee e, employee m GROUP BY m.Lname;

Whats the name of the join performed without using the equality between the primary key and the foreign key?

Select one:

a.Equi-join

b.Outer join

c.Non equi-join

d.Self join

Employees(employee_id, first_name, last_name, salary, hire_date, job_title, manager_id, department_id) Departments(department_id, department_name, location_id) Which of the following queries will display the name of all employees who does not work for EXECUTIVE department

Select one:

a.SELECT first_name, last_name FROM employees WHERE department_id In (SELECT department_id FROM departments WHERE upper(department_name) = 'EXECUTIVE');

b.SELECT first_name, last_name FROM employees WHERE department_id != (SELECT department_id FROM departments WHERE upper(department_name) = 'EXECUTIVE');

c.SELECT first_name, last_name FROM employees WHERE department_id not in (SELECT department_id FROM departments WHERE upper(department_name) = 'EXECUTIVE');

d.SELECT first_name, last_name FROM employees WHERE department_id = (SELECT department_id FROM departments WHERE upper(department_name) = 'EXECUTIVE');

Which of the following operators to compare a value will all the values returned by the subquery?

Select one:

a.Exists

b.All

c.IN

d.Any

Shop (ShopCode, ShopName, ShopSales) Which of the following queries displays name of the shop that has the highest average shop sales.

Select one:

a.SELECT shopName FROM shop GROUP BY shopName HAVING avg(shopSales) = (SELECT max(avg(shopSales)) FROM shop GROUP BY shopName);

b.SELECT shopName FROM shop WHERE avg(shopSales) = (SELECT max(avg(shopSales)) FROM shop GROUP BY shopName);

c.SELECT shopName FROM shop WHERE avg(shopSales) GROUP BY shopName;

d.SELECT shopName FROM shop GROUP BY shopName HAVING max(avg(shopSales))

Which of the following operators to compare a value will any of the values returned by the subquery?

Select one:

a.In

b.Any

c.Exists

d.All

Which line in the following SQL statement contains an error?

1 SELECT name, title 2 FROM books natural join publisher 3 WHERE category = FITNESS 4 or 5 books.pubid = 4

Select one:

a.Line 5

b.Line 3

c.Line 2

d.Line 4

If you wish to create an inner join, but the two tables do not have a commonly named attribute, you can use a(n) __________ clause.

Select one:

a.OF

b.JOIN ON

c.USING

d.HAS

In single row subquery, which of the following you shouldnt use?

Select one:

a.Relational operators

b.Pair of parentheses

c.Order by clause

d.All of the above

Which line in the SQL statement contains an error?

1 SELECT last_name 2 FROM employees 3 WHERE employee_id in (SELECT department_id FROM departments);

Select one:

a.Line 3

b.Line 2

c.None of the above

d.Line 1

The following SQL statement represents which type of join? SELECT title, orderno, quantity FROM books join orderitiems using (isbn);

Select one:

a.Non-equality

b.Equality

c.Outer join

d.Self join

In which clause the subquery can be used?

Select one:

a.WHERE

b.All of the above

c.FROM

d.HAVING

Which of the following operators is used when a subquery returns multiple rows?

Select one:

a.IN

b.= (Equal sign)

c.LIKE

d.>

What will be the output when your join query doesnt have any join condition when you use the ANSI syntax JOIN...ON?

Select one:

a.Non equi join

b.Cartesian Product

c.Outer join

d.Error

Employee (EmpCode, EmpTitle, Fname, Lname, Salary, DeptName) Which of the following queries displays first name and last name of employees, who earn more than all employee(s) that work for SALES department?

Select one:

a.SELECT fname, lname FROM employee WHERE salary > ANY (SELECT salary FROM employee WHERE upper(deptName)='SALES');

b.SELECT fname, lname FROM employee WHERE salary > ALL (SELECT salary FROM employee WHERE upper(deptName)='SALES');

c.SELECT fname, lname FROM employee WHERE salary < ALL (SELECT salary FROM employee WHERE upper(deptName)='SALES');

d.SELECT fname, lname FROM employee WHERE salary >(SELECT salary FROM employee WHERE upper(deptName)='SALES');