Home › Quizzes › SQL Programming Language

SQL Programming Language

This test consists of questions related to SQL queries for database.

15 Questions Published
Play Quiz Practise this topic out loud

Questions

Question 1 Multiple Choice (Single Answer)

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.

  1. Select customer_id, sum(price) from items_ordered group by customer_id
  2. Select customer_id, count(customer_id), price from items_ordered group by customer_id
  3. Select customer_id, count(customer_id), count(price) from items_ordered group by customer_id
  4. Select customer_id, count(customer_id), count(price) from items_ordered group by item
  5. None of the above
Question 2 Multiple Choice (Single Answer)

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.

  1. select First_name, Last_name from employee1 where employee1.Job_Id=job.Job_ID;
  2. select First_name, Last_name from employee1 e, job j where e.employee_Id=j.Job_ID;
  3. select First_name, Last_name from employee1 e, job j where e.Job_Id=j.Job_ID;
  4. select First_name, Last_name from employee1 e, job j where e.Manager_id=j.Job_ID;
  5. none of the above
Question 3 Multiple Choice (Single Answer)

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.

  1. Select item, max(price), min(price) from items_ordered group by item where max(price)>1500
  2. Select max(price), min(price) from items_ordered group by item having max(price)>1500
  3. Select item, max(price), min(price) from items_ordered group by item having max(price)>1500
  4. Select item, max(price), min(price) from items_ordered group by item having count(price)>1500
  5. None of the above
Question 4 Multiple Choice (Single Answer)

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.

  1. SELECT avg(price) FROM items_ordered WHERE order_date '%Jun%';
  2. SELECT avg(price) FROM items_ordered WHERE order_date LIKE '%Jun%';
  3. SELECT avg(price) FROM items_ordered WHERE order_date LIKE '%Jun';
  4. SELECT avg(price) FROM items_ordered WHERE order_date LIKE 'Jun';
  5. None of the above
Question 5 Multiple Choice (Single Answer)

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?

  1. SELECT MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB
  2. SELECT DEPTNO, MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB
  3. SELECT DEPTNO, JOB, MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB
  4. SELECT JOB, MAX(SAL) FROM Employee GROUP BY DEPTNO, JOB
  5. None of the above
Question 6 Multiple Choice (Single Answer)

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.

  1. SELECT firstname, city, state FROM customers WHERE state LIKE ('Arizona', 'Washington', 'Oklahoma', 'Colorado');
  2. SELECT firstname, city, state FROM customers WHERE state ('Arizona', 'Washington', 'Oklahoma', 'Colorado');
  3. SELECT firstname, city, state FROM customersAND state LIKE('Arizona', 'Washington', 'Oklahoma', 'Colorado');
  4. SELECT firstname, city, state FROM customers WHERE state IN ('Arizona', 'Washington', 'Oklahoma', 'Colorado');
  5. None of the above
Question 7 Multiple Choice (Single Answer)

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.

  1. SELECT DEPTNO, JOB FROM EMP GROUP BY DEPTNO
  2. SELECT DEPTNO FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO
  3. SELECT DEPTNO, COUNT(*) FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO
  4. SELECT DEPTNO, COUNT() FROM EMP WHERE JOB=’CLERK’ GROUP BY DEPTNO
  5. None of the above
Question 8 Multiple Choice (Single Answer)

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.

  1. SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO
  2. SELECT * FROM EMP WHERE SAL>1 GROUP BY DEPTNO
  3. SELECT * FROM EMP WHERE SAL IN(SELECT SAL FROM EMP GROUP BY DEPTNO)
  4. SELECT * FROM EMP WHERE SAL IN(SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO)
  5. None of the above
Question 9 Multiple Choice (Single Answer)

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

  1. SELECT ISNUMERIC(100); OUTPUT: 3
  2. SELECT ISNUMERIC(‘100A’) ; OUTPUT: 0
  3. SELECT ISNUMERIC(‘1000’); OUTPUT: 4
  4. Both (2) and (3)
  5. None of the above
Question 10 Multiple Choice (Single Answer)

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.

  1. SELECT item FROM items_ordered WHERE price > 1500 ORDER BY price ASC;
  2. SELECT price FROM items_ordered WHERE price > 1500 ORDER BY price ASC;
  3. SELECT item, price FROM items_ordered WHERE price > 1500 ORDER BY item ASC;
  4. SELECT item, price FROM items_ordered WHERE price > 1500 ORDER BY price ASC;
  5. None of the above
Question 11 Multiple Choice (Single Answer)

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.

  1. SELECT * FROM EMP WHERE SAL>1
  2. SELECT * FROM EMP WHERE SAL>0
  3. SELECT * FROM EMP WHERE SAL IN(SELECT COUNT(DISTINCT SAL) FROM EMP )
  4. SELECT * FROM EMP E WHERE 0=(SELECT COUNT( SAL) FROM EMP WHERE SAL>E.SAL)
  5. SELECT * FROM EMP E WHERE 0=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)
Question 12 Multiple Choice (Single Answer)

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.

  1. SELECT COUNT(DISTINCT SAL) FROM EMP E WHERE SAL>E.SAL
  2. SELECT MAX(DISTINCT SAL) FROM EMP E WHERE SAL>E.SAL
  3. SELECT * FROM EMP E WHERE 1=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)
  4. SELECT * FROM EMP E WHERE 0=(SELECT COUNT(DISTINCT SAL) FROM EMP WHERE SAL>E.SAL)
  5. None of the above
Question 13 Multiple Choice (Single Answer)

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.

  1. SELECT * FROM DEPT WHERE DEPTNO !=(SELECT DISTINCT DEPTNO FROM EMP)
  2. SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP)
  3. SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DEPTNO FROM EMP)
  4. SELECT * FROM DEPT WHERE DEPTNO NOT IN(SELECT DISTINCT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO )
  5. None of the above
Question 14 Multiple Choice (Single Answer)

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?

  1. SELECT customerid FROM customers GROUP BY state;
  2. SELECT customerid, count(state) FROM customers GROUP BY state;
  3. SELECT state, count(state) FROM customers GROUP BY state;
  4. SELECT * FROM customers GROUP BY state;
  5. SELECT count(state) FROM customers GROUP BY state;
Question 15 Multiple Choice (Single Answer)

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.

  1. SELECT * FROM EMP WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP)
  2. SELECT * FROM DEPT WHERE DEPTNO IN(SELECT DISTINCT DEPTNO FROM EMP)
  3. SELECT * FROM DEPT WHERE EXISTS(SELECT DEPTNO FROM EMP WHERE EMP.DEPTNO=DEPT.DEPTNO)
  4. Both (2) and (3)
  5. None of the above
← Back to Quizzes