Question 1

Consider the following database table- Table Name- Items_Ordere |||||| |---|---|---|---|---| |Customer_ID|Order_date|Item|Quantity|Price| |77120|6-Jul-1999|Belt|1|140| |77121|1-Jul-1999|Watch|4|1230| |77122|30-Jun-1999|T-shirt|6|1020| |77123|12-June-1999|Fan|1|950.50| |77124|30-Aug-1999|Cooler|1|4010.20| |77125|30-May-1999|Bag|7|2950| |77126|1-Jun-1999|Shoes|2|1280| |77122|12-Feb-1999|Jeans|2|2000| |77124|1-Aug-2000|Jacket|1|1260|

How will you find the number of orders made by each customer? Select the correct option.

Select customer_id, sum(price) from items_ordered group by customer_id
Select customer_id, count(customer_id), price from items_ordered group by customer_id
Select customer_id, count(customer_id), count(price) from items_ordered group by customer_id
Select customer_id, count(customer_id), count(price) from items_ordered group by item
None of the above

Answer

Correct Option: C

Question 2

Consider the following database tables

Table Name- Jo ||| |---|---| |job_ID|Designation| |101|Clerk| |102|Analyst| |103|Manager|

Table Name- Employe |||||| |---|---|---|---|---| |EMPLOYEE_ID|FIRST_NAME|LAST_NAME|job_ID|MANAGER_ID| |7271|Amit|Saxena|102|4101| |7272|Anil|Sharma|103|4102| |7273|Mohit|Rajput|101|4103|

Find the Employee's first name and last name according to their job id. Select the correct option.

select First_name, Last_name from employee1 where employee1.Job_Id=job.Job_ID;
select First_name, Last_name from employee1 e, job j where e.employee_Id=j.Job_ID;
select First_name, Last_name from employee1 e, job j where e.Job_Id=j.Job_ID;
select First_name, Last_name from employee1 e, job j where e.Manager_id=j.Job_ID;
none of the above

Answer

Correct Option: C

Question 3

Consider the following database table- Table Name- Items_Ordere |||||| |---|---|---|---|---| |Customer_ID|Order_date|Item|Quantity|Price| |77120|6-Jul-1999|Belt|1|140| |77121|1-Jul-1999|Watch|4|1230| |77122|30-Jun-1999|T-shirt|6|1020| |77123|12-June-1999|Fan|1|950.50| |77124|30-Aug-1999|Cooler|1|4010.20| |77125|30-May-1999|Bag|7|2950| |77126|1-Jun-1999|Shoes|2|1280| |77121|3-Sep-2000|Watch|5|1800| |77125|12-Feb-2001|Jeans|6|4050| |77122|12-Feb-1999|Jeans|2|2000| |77124|1-Aug-2000|Jacket|1|1260|

Display the maximum and mimimum price of each specific item, if the price that item is more than 1500. Select the correct option.

Select item, max(price), min(price) from items_ordered group by item where max(price)>1500
Select max(price), min(price) from items_ordered group by item having max(price)>1500
Select item, max(price), min(price) from items_ordered group by item having max(price)>1500
Select item, max(price), min(price) from items_ordered group by item having count(price)>1500
None of the above

Answer

Correct Option: C

Question 4

Consider the following database table

Table Name- Items_Ordere |||||| |---|---|---|---|---| |Customer_ID|Order_date|Item|Quantity|Price| |77120|6-Jul-1999|Belt|1|140| |77121|1-Jul-1999|Watch|4|1230| |77122|30-Jun-1999|T-shirt|6|1020| |77123|12-Jun-1999|Fan|1|950.50| |77124|30-Aug-1999|Cooler|1|4010.20| |77125|30-May-1999|Bag|7|2950| |77126|1-Jun-1999|Shoes|2|1280| |77121|3-Sep-2000|Watch|5|1800| |77125|12-Feb-2001|Jeans|6|4050| |77122|12-Feb-1999|Jeans|2|2000| |77124|1-Aug-2000|Jacket|1|1260|

Calculate the average price of all of the items ordered that were purchased in the month of June. Select the correct option.

SELECT avg(price) FROM items_ordered WHERE order_date '%Jun%';
SELECT avg(price) FROM items_ordered WHERE order_date LIKE '%Jun%';
SELECT avg(price) FROM items_ordered WHERE order_date LIKE '%Jun';
SELECT avg(price) FROM items_ordered WHERE order_date LIKE 'Jun';
None of the above

Answer

Correct Option: B

Question 5

Consider the following database table

Table Name- Employe ||||| |---|---|---|---| |Employee_ID|Dept no|Job|Salary| |120|201|Clerk|20,000| |121|202|Manager|22,000| |122|203|Head|32,000| |123|204|Sr. Manager|46,000| |124|205|Boss|40,000| |125|206|HR|23,000| |126|207|Shoes|26,000| ||

Which of the following is the correct query to find the highest salaries for each department in it for each job from the employee table?

SELECT MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB
SELECT DEPTNO, MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB
SELECT DEPTNO, JOB, MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB
SELECT JOB, MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB
None of the above

Answer

Correct Option: C

Question 6

Consider the following database table

Table Name- Customer |||||| |---|---|---|---|---| |customerid|firstname|lastname|city|state| |001|Adam|Smith|Pinetop|Arizona| |002|Sarah|Graham|Nogales|Arizona| |003|Linda|Giles|Durango|Colorado| |004|Lisa|Howell|Kailua|Hawaii| |005|Anthony|Davids|Greensboro|North Carolina| |006|Kevin|Smith|Yuma|Arizona| |007|Dalton|Howell|Tillamook|Oregon| |008|Donald|Steyn|Lynden|Washington| |009|Angelo|Mathhews|Pocatello|Idaho| |010|Albie|Morne|Snoqualmie|Washington|

Select the firstname, city, and state from the customers table, where the state is either Arizona, Washington, Oklahoma, Colorado.

SELECT firstname, city, state FROM customers WHERE state LIKE ('Arizona', 'Washington', 'Oklahoma', 'Colorado');
SELECT firstname, city, state FROM customers WHERE state ('Arizona', 'Washington', 'Oklahoma', 'Colorado');
SELECT firstname, city, state FROM customersAND state LIKE('Arizona', 'Washington', 'Oklahoma', 'Colorado');
SELECT firstname, city, state FROM customers WHERE state IN ('Arizona', 'Washington', 'Oklahoma', 'Colorado');
None of the above

Answer

Correct Option: D

Question 7

Consider the following database table

Table Name- Em ||||| |---|---|---|---| |Deptno|job|department|city| |001|Manager|Sales|Kolkata| |002|Coordinator|Ministry|Delhi| |003|Clerk|HR|Delhi| |004|Clerk|Industry|Mumbai| |005|Developer|Technical dept|Banglore|

Find the number of Clerk’s working for each department.

SELECT DEPTNO, JOB FROM EMP GROUP BY DEPTNO
SELECT DEPTNO FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO
SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO
SELECT DEPTNO, COUNT() FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO
None of the above

Answer

Correct Option: C

Question 8

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow|

Find the details of employees who are earning the highest salary in each department.

SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO
SELECT * FROM EMP WHERE SAL>1 GROUP BY DEPTNO
SELECT * FROM EMP WHERE SAL IN(SELECT SAL FROM EMP GROUP BY DEPTNO)
SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)
None of the above

Answer

Correct Option: D

Question 9

Which of the following is/are the correct alternative to determine the nature of expression in Select clause?

SELECT ISNUMERIC(100); OUTPUT: 3
SELECT ISNUMERIC(‘100A’) ; OUTPUT: 0
SELECT ISNUMERIC(‘1000’); OUTPUT: 4
Both (2) and (3)
None of the above

Answer

Correct Option: B

Question 10

Consider the following database table

Table Name- Items_Ordere |||||| |---|---|---|---|---| |Customer_ID|Order_date|Item|Quantity|Price| |77120|6-Jul-1999|Belt|1|140| |77121|1-Jul-1999|Watch|4|1230| |77122|30-Jun-1999|T-shirt|6|1020| |77123|12-Jun-1999|Fan|1|950.50| |77124|30-Aug-1999|Cooler|1|4010.20| |77125|30-May-1999|Bag|7|2950| |77126|1-Jun-1999|Shoes|2|1280| |77121|3-Sep-2000|Watch|5|1800| |77125|12-Feb-2001|Jeans|6|4050| |77122|12-Feb-1999|Jeans|2|2000| |77124|1-Aug-2000|Jacket|1|1260|

Select the item and price for all of the items in the items_ordered table that the price is greater than 1500. Display the results in ascending order based on the price. Select the correct option.

SELECT item FROM items_ordered WHERE price > 1500 ORDER BY price ASC;
SELECT price FROM items_ordered WHERE price > 1500 ORDER BY price ASC;
SELECT item, price FROM items_ordered WHERE price > 1500 ORDER BY item ASC;
SELECT item, price FROM items_ordered WHERE price > 1500 ORDER BY price ASC;
None of the above

Answer

Correct Option: D

Question 11

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow|

Find the details of employees earning the highest salary.

SELECT * FROM EMP WHERE SAL>1
SELECT * FROM EMP WHERE SAL>0
SELECT * FROM EMP WHERE SAL IN(SELECT COUNT(DISTINCT SAL) FROM EMP )
SELECT * FROM EMP E WHERE 0=(SELECT COUNT( SAL) FROM EMP WHERE SAL>E.SAL)
SELECT * FROM EMP E WHERE 0=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)

Answer

Correct Option: E

Question 12

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow|

Find the details of employees earning the second highest salary.

SELECT COUNT(DISTINCT SAL) FROM EMP E WHERE SAL>E.SAL
SELECT MAX(DISTINCT SAL) FROM EMP E WHERE SAL>E.SAL
SELECT * FROM EMP E WHERE 1=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)
SELECT * FROM EMP E WHERE 0=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)
None of the above

Answer

Correct Option: C

Question 13

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow| and Table name-Dep ||| |---|---| |Deptno|deptname| |501|sales| |502|Hr| |503|CS| |504|EC|

Find the details of departments in which employees are not working.

SELECT * FROM DEPT WHERE DEPTNO !=(SELECT DISTINCT DEPTNO FROM EMP)
SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP)
SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DEPTNO FROM EMP)
SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO )
None of the above

Answer

Correct Option: B

Question 14

Consider the following database table

Table Name- Customer |||||| |---|---|---|---|---| |customerid|firstname|lastname|city|state| |001|Adam|Smith|Pinetop|Arizona| |002|Sarah|Graham|Nogales|Arizona| |003|Linda|Giles|Durango|Colorado| |004|Lisa|Howell|Kailua|Hawaii| |005|Anthony|Davids|Greensboro|North Carolina| |006|Kevin|Smith|Yuma|Arizona| |007|Dalton|Howell|Tillamook|Oregon| |008|Donald|Steyn|Lynden|Washington| |009|Angelo|Mathhews|Pocatello|Idaho| |010|Albie|Morne|Snoqualmie|Washington|

How many people are in each unique state in the customers table?

SELECT customerid FROM customers GROUP BY state;
SELECT customerid, count(state) FROM customers GROUP BY state;
SELECT state, count(state) FROM customers GROUP BY state;
SELECT * FROM customers GROUP BY state;
SELECT count(state) FROM customers GROUP BY state;

Answer

Correct Option: C

Question 15

Consider the following database table

Table Name- EM ||||| |---|---|---|---| |empid|deptno|Salary|city| |011|501|20000|Mumbai| |012|502|15000|Gurgaun| |013|503|24000|Delhi| |014|504|30000|Lucknow| and Table name-Dep ||| |---|---| |Deptno|deptname| |501|sales| |502|Hr| |503|CS| |504|EC|

Find the details of departments in which employees are working.

SELECT * FROM EMP WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP)
SELECT * FROM DEPT WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP)
SELECT * FROM DEPT WHERE EXISTS(SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO)
Both (2) and (3)
None of the above

Answer

Correct Option: D

Description: This test consists of questions related to SQL queries for database.
Number of Questions: 15
Created by: Karuna Seth
Tags: SQL database practice test programming language questions SQL test SQL questions Database

SQL Programming Language

Find more quizzes from top tags