WorksheetsSQL Query Quiz-2
Total questions: 19
Worksheet time: 10mins
To find the names of clients who placed at least one order, the correct query is:
SELECT client_name FROM Clients WHERE client_no IN (SELECT client_no FROM Sales_order);
SELECT client_name FROM Sales_order WHERE client_no IN Clients;
SELECT * FROM Sales_order WHERE client_no = client_name;
SELECT client_name FROM Clients JOIN Sales_order;
Which query will return product_no and total quantity ordered for each product?
SELECT product_no, COUNT(qty_ordered) FROM Sales_order_details GROUP BY product_no;
SELECT product_no, SUM(qty_ordered) FROM Sales_order_details GROUP BY product_no;
SELECT DISTINCT product_no, qty_ordered FROM Sales_order_details;
SELECT product_no, SUM(qty_ordered) FROM Sales_order_details;
To display clients who have not placed any orders, choose the correct query:
SELECT * FROM Clients WHERE client_no NOT IN (SELECT client_no FROM Sales_order);
SELECT * FROM Clients WHERE NOT client_no IN Sales_order;
SELECT client_name FROM Clients MINUS Sales_order;
SELECT client_name FROM Clients WHERE order_no=NULL;
Which query gives total billed amount for orders in June, considering billed orders only?
SELECT SUM(qty_orderedproduct_rate) FROM Sales_order_details WHERE billed='Y' AND TO_CHAR(order_date,'Mon')='JUN';
SELECT SUM(qty_orderedproduct_rate) FROM Sales_order so, Sales_order_details sod WHERE so.order_no=sod.s_order_no AND billed='Y' AND UPPER(TO_CHAR(so.order_date,'Mon'))='JUN';
SELECT SUM(product_rate) FROM Sales_order WHERE billed='Y' AND order_date='June';
SELECT SUM(order_date) FROM Sales_order WHERE billed='Y' AND order_status='Fulfilled';
To list product_no and description for products not sold, the correct subquery is:
SELECT product_no, description FROM Products WHERE product_no NOT IN (SELECT product_no FROM Sales_order_details);
SELECT product_no, description FROM Products WHERE NOT EXISTS Sales_order_details;
SELECT product_no FROM Products WHERE product_no != ALL (SELECT product_no FROM Sales_order_details);
SELECT product_no FROM Products MINUS Sales_order_details;
You want to show client_no, order_no, and order_date, including clients who didn’t order anything. Which join should be used?
INNER JOIN
LEFT JOIN
RIGHT JOIN
CROSS JOIN
Which query finds the highest-priced product sold?
SELECT MAX(sell_price) FROM Products;
SELECT product_name, sell_price FROM Products WHERE sell_price = (SELECT MAX(sell_price) FROM Products);
SELECT TOP 1 product_name, sell_price FROM Products ORDER BY sell_price DESC;
SELECT product_name FROM Products WHERE sell_price > ALL(SELECT sell_price FROM Products);
To find customers who stay in either Baroda or Delhi but not in Indore, use:
SELECT * FROM Customers WHERE cust_city IN ('Baroda','Delhi') AND cust_city<>'Indore';
SELECT * FROM Customers WHERE cust_city IN ('Baroda','Delhi','Indore');
SELECT * FROM Customers WHERE cust_city BETWEEN 'Baroda' AND 'Delhi';
SELECT * FROM Customers WHERE cust_city NOT IN ('Baroda','Delhi');
Which query finds salesman with salary greater than all salesman in Surat?
SELECT salesman_name FROM Salesman WHERE sal_amt > ALL (SELECT sal_amt FROM Salesman WHERE city='Surat');
SELECT salesman_name FROM Salesman WHERE sal_amt > ANY (SELECT sal_amt FROM Salesman WHERE city='Surat');
SELECT salesman_name FROM Salesman WHERE city!='Surat';
SELECT salesman_name FROM Salesman WHERE sal_amt > (SELECT MAX(sal_amt) FROM Salesman);
To retrieve client_no and average quantity sold per client, only for clients whose total order value exceeds 15000, use:
SELECT c.client_no, AVG(qty_ordered) FROM Clients c, Sales_order so, Sales_order_details sod WHERE c.client_no=so.client_no AND so.order_no=sod.s_order_no GROUP BY c.client_no HAVING SUM(qty_ordered*product_rate)>15000;
SELECT c.client_no, AVG(qty_ordered) FROM Sales_order_details GROUP BY c.client_no;
SELECT client_no, AVG(product_rate) FROM Sales_order HAVING product_rate>15000;
SELECT client_no, AVG(qty_ordered) FROM Sales_order WHERE total>15000;
To find clients who have ordered “jeans”, which query is correct?
SELECT client_name FROM Clients WHERE product_name='jeans';
SELECT client_name FROM Clients WHERE client_no IN (SELECT client_no FROM Sales_order WHERE order_no IN (SELECT s_order_no FROM Sales_order_details WHERE product_no IN (SELECT product_no FROM Products WHERE product_name='jeans')));
SELECT client_name FROM Clients WHERE client_no IN (SELECT product_no FROM Products WHERE product_name='jeans');
SELECT client_name FROM Products JOIN Clients WHERE product_name='jeans';
Which query lists each salesman’s name and the number of clients handled?
SELECT salesman_name, COUNT(client_no) FROM Clients GROUP BY salesman_name;
SELECT s.salesman_name, COUNT(c.client_no) FROM Salesman s JOIN Sales_order o ON s.salesman_no=o.salesman_no JOIN Clients c ON o.client_no=c.client_no GROUP BY s.salesman_name;
SELECT salesman_name, client_no FROM Salesman, Clients WHERE salesman_no=client_no;
SELECT salesman_name, COUNT(*) FROM Salesman;
Which query returns orders that are “In Progress” and billed (Y)?
SELECT * FROM Sales_order WHERE order_status='In Progress' AND billed='Y';
SELECT * FROM Sales_order WHERE order_status IN ('In Progress','Billed');
SELECT * FROM Sales_order WHERE billed LIKE 'Y%';
SELECT * FROM Sales_order HAVING order_status='In Progress';
To find the second-highest selling price among products:
SELECT MAX(sell_price) FROM Products WHERE sell_price < (SELECT MAX(sell_price) FROM Products);
SELECT MAX(sell_price) FROM Products LIMIT 2;
SELECT TOP 2 sell_price FROM Products;
SELECT SECOND(sell_price) FROM Products;
Which SQL command creates a view combining order and product details?
CREATE VIEW OrderView AS SELECT order_no, product_no, qty_ordered FROM Sales_order_details;
MAKE VIEW OrderView ON Sales_order_details;
SELECT order_no, product_no INTO VIEW OrderView;
DEFINE VIEW OrderView USING Sales_order_details;
In PL/SQL, what will display the message “Hello DBMS”?
dbms_output('Hello DBMS');
dbms_output.display('Hello DBMS');
dbms_output.put_line('Hello DBMS');
show_output('Hello DBMS');
To create a trigger preventing insertion of records on Sundays, use:
CREATE TRIGGER trg_no_sunday BEFORE INSERT ON route_detail WHEN (TO_CHAR(SYSDATE,'DY')='SUN') RAISE_APPLICATION_ERROR(-20001,'No inserts on Sunday');
CREATE TRIGGER route_check AFTER INSERT ON route_detail IF DAY='SUN';
CREATE TRIGGER prevent_sunday INSERT ON route_detail WHERE TO_DATE='SUN';
TRIGGER route_block BEFORE INSERT SUNDAY ON route_detail;
To grant SELECT privilege on table Sales_order from user1 to user2:
GRANT SELECT ON user1.Sales_order TO user2;
ALLOW SELECT ON Sales_order TO user2;
PERMIT user2 TO SELECT FROM Sales_order;
GIVE SELECT PRIVILEGE Sales_order TO user2;
After granting privileges, how do you revoke user2’s access to Sales_order_details?
REVOKE SELECT ON user1.Sales_order_details FROM user2;
REMOVE SELECT FROM user2 ON Sales_order_details;
REVOKE ACCESS Sales_order_details FROM user2;
DENY user2 Sales_order_details;
