Untitled

mail@pastecode.io avatar
unknown
plain_text
2 months ago
7.7 kB
25
Indexable
Never
SQL Test 1
Q1. From the following table, write a SQL query to identify the unique salespeople ID. Return salesman_id. 
Sample table: orders
ord_no      purch_amt   ord_date    customer_id   salesman_id
----------  ----------  ----------  -----------  -----------
70001       150.5       2012-10-05   3005         5002
70009       270.65      2012-09-10   3001         5005
70002       65.26       2012-10-05   3002         5001
70004       110.5       2012-08-17   3009         5003
70007       948.5       2012-09-10   3005         5002
70005       2400.6      2012-07-27   3007         5001
70008       5760        2012-09-10   3002         5001
70010       1983.43     2012-10-10   3004         5006
70003       2480.4      2012-10-10   3009         5003
70012       250.45      2012-06-27   3008         5002
70011       75.29       2012-08-17   3003         5007
70013       3045.6      2012-04-25   3002         5001




Q2. From the following table, write a SQL query to find customers whose grade is 200. Return customer_id, cust_name, city, grade, salesman_id.  
Sample table: customer
 customer_id |   cust_name    |             city    |                    grade |              salesman_id 
-------------+----------------+------------+-------+----------
3002 | Nick Rimando   | New York   |   100 |        5001
3007 | Brad Davis     | New York   |   200 |        5001
3005 | Graham Zusi    | California |   200 |        5002
3008 | Julian Green   | London     |   300 |        5002
3004 | Fabian Johnson | Paris      |   300 |        5006
3009 | Geoff Cameron  | Berlin     |   100 |        5003
3003 | Jozy Altidor   | Moscow     |   200 |        5007
3001 | Brad Guzan     | London     |       |        500





Q3. From the following table, write a SQL query to find customers who are either from the city 'New York' or who do not have a grade greater than 100. Return customer_id, cust_name, city, grade, and salesman_id.  
Sample table: customer (refer above customer table)

Q4. From the following table, write a SQL query to find details of all orders with a purchase amount less than 200 or exclude orders with an order date greater than or equal to '2012-02-10' and a customer ID less than 3009. Return ord_no, purch_amt, ord_date, customer_id and salesman_id.
Sample table : orders
ord_no      purch_amt   ord_date    customer_id  salesman_id
----------  ----------  ----------  -----------  -----------
70001       150.5       2012-10-05   3005         5002
70009       270.65      2012-09-10   3001         5005
70002       65.26       2012-10-05   3002         5001
70004       110.5       2012-08-17   3009         5003
70007       948.5       2012-09-10   3005         5002
70005       2400.6      2012-07-27   3007         5001
70008       5760        2012-09-10   3002         5001
70010       1983.43     2012-10-10   3004         5006
70003       2480.4      2012-10-10   3009         5003
70012       250.45      2012-06-27   3008         5002
70011       75.29       2012-08-17   3003         5007
70013       3045.6      2012-04-25   3002         5001


 

Q5. From the following table, write a SQL query to find the employees who work at depart 47 or 63. Return emp_idno, emp_fname, emp_lname, and emp_dept.  
Sample table : emp_details
 EMP_IDNO   EMP_FNAME       EMP_LNAME         EMP_DEPT
--------- --------------- --------------- ----------
   127323   Michale         Robbin                  57
   526689   Carlos          Snares                  63
   843795   Enric           Dosio                   57
   328717   Jhon            Snares                  63
   444527   Joseph          Dosni                   47
   659831   Zanifer         Emily                   47
   847674   Kuleswar        Sitaraman               57
   748681   Henrey          Gabriel                 47
   555935   Alex            Manuel                  57
   539569   George          Mardy                   27
   733843   Mario           Saule                   63
   631548   Alan            Snappy                  27
   839139   Maria           Foster                  57


Q6. From the following table, write a SQL query to retrieve the details of all customers whose ID belongs to any of the values 3007, 3008 or 3009. Return customer_id, cust_name, city, grade, and salesman_id.  
Sample table: customer (refer Q2 customer table)
 

Q7. From the following table, write a SQL query to find the details of the customers whose names end with the letter 'n'. Return customer_id, cust_name, city, grade, salesman_id.
Sample table: customer (refer Q2 customer table)

Q8. From the following table, write a SQL query to calculate total purchase amount of all orders. Return total purchase amount.
Sample table: orders
ord_no      purch_amt   ord_date    customer_id  salesman_id
----------  ----------  ----------  -----------  -----------
70001       150.5       2012-10-05   3005         5002
70009       270.65      2012-09-10   3001         5005
70002       65.26       2012-10-05   3002         5001
70004       110.5       2012-08-17   3009         5003
70007       948.5       2012-09-10   3005         5002
70005       2400.6      2012-07-27   3007         5001
70008       5760        2012-09-10   3002         5001
70010       1983.43     2012-10-10   3004         5006
70003       2480.4      2012-10-10   3009         5003
70012       250.45      2012-06-27   3008         5002
70011       75.29       2012-08-17   3003         5007
70013       3045.6      2012-04-25   3002         5001


Q9. From the following table, write a SQL query to find the highest grade of the customers in each city. Return city, maximum grade.  
Sample table: customer (refer Q2 customer table)

Q10. From the following table, write a SQL query to find the highest purchase amount ordered by each customer on a particular date. Return, order date and highest purchase amount.
Sample table: orders (refere Q8 table )

Q11. From the following table, write a SQL query to find the maximum order (purchase) amount in the range 2000 - 6000 (Begin and end values are included.) by combination of each customer and order date. Return customer id, order date and maximum purchase amount.
Sample table: orders (refere Q8 table )


Q12. From the following table, write a SQL query to determine the maximum order amount for each customer. The customer ID should be in the range 3002 and 3007(Begin and end values are included.). Return customer id and maximum purchase amount.
Sample table: orders (refere Q8 table )

Q13. From the following table, write a SQL query to determine the maximum order (purchase) amount generated by each salesperson. Filter the rows for the salesperson ID is in the range 5003 and 5008 (Begin and end values are included.). Return salesperson id and maximum purchase amount.
Sample table: orders (refere Q8 table )
Q14. From the following table, write a SQL query to count all the orders generated on '2012-08-17'. Return number of orders.
Sample table: orders (refere Q8 table )

Q15. From the following table, write a SQL query to count the number of salespeople in a city. Return number of salespeople.
Sample table: salesman
 salesman_id |    name    |   city   | commission 
-------------+------------+----------+------------
        5001 | James Hoog | New York |       0.15
        5002 | Nail Knite | Paris    |       0.13
        5005 | Pit Alex   | London   |       0.11
        5006 | Mc Lyon    | Paris    |       0.14
        5007 | Paul Adam  | Rome     |       0.13
        5003 | Lauson Hen | San Jose |       0.12




Leave a Comment