SELECT * FROM item_mast
WHERE pro_price BETWEEN 200 AND 600; [include 200 and 600]
select *
from item_mast
where pro_price >= 200 and pro_price <= 600;
SELECT * FROM nobel_win
WHERE year >1972
AND winner IN (‘Menachem Begin’,
‘Yitzhak Rabin’);
SELECT pro_name, pro_price
FROM item_mast
WHERE pro_price >= 250
ORDER BY pro_price DESC, pro_name;
SELECT pro_name, pro_price
FROM item_mast
WHERE pro_price =
(SELECT MIN(pro_price) FROM item_mast);
SELECT *
FROM nobel_win
WHERE winner LIKE ‘Louis%’;
SELECT FROM nobel_win WHERE (subject =’Physics’ AND year=1970) UNION (SELECT FROM nobel_win WHERE (subject =’Economics’ AND year=1971));
SELECT *
FROM nobel_win
WHERE year=1970
AND subject NOT IN(‘Physiology’,’Economics’);
SELECT *
FROM nobel_win
WHERE subject NOT LIKE ‘P%’
ORDER BY year DESC, winner;
SELECT *
FROM nobel_win
WHERE year=1970
ORDER BY
CASE
WHEN subject IN (‘Economics’,’Chemistry’) THEN 1
ELSE 0
END ASC,
subject,
winner;
SELECT *
FROM customer
WHERE city = ‘New York’ OR grade>100;
SELECT *
FROM customer
WHERE NOT (city = ‘New York’ OR grade>100);
SELECT *
FROM orders
WHERE NOT ((ord_date =’2012-09-10’
AND salesman_id>5005)
OR purch_amt>1000.00);
SELECT ord_no,purch_amt,
(100purch_amt)/6000 AS “Achieved %”,
(100(6000-purch_amt)/6000) AS “Unachieved %”
FROM orders
WHERE (100*purch_amt)/6000>50;
SELECT *
FROM orders
WHERE (purch_amt BETWEEN 500 AND 4000)
AND NOT purch_amt IN(948.50,1983.43);
Write a SQL statement to find those salesmen with all other information and name started with any letter within ‘A’ and ‘K’.
SELECT *
FROM salesman
WHERE name BETWEEN ‘A’ and ‘L’;
SELECT *
FROM salesman
WHERE name NOT BETWEEN ‘A’ and ‘L’;
Write a SQL statement to find those rows from the table testtable which contain the escape character underscore ( _ ) in its column ‘col1’.
SELECT *
FROM testtable
WHERE col1 LIKE ‘%/_%’ ESCAPE ‘/‘;
SELECT *
FROM testtable
WHERE col1 LIKE ‘%//%’ ESCAPE ‘/‘;
- Write a SQL statement to find those rows from the table testtable which contain the string ( / ) in its column ‘col1’. Go to the editor
SELECT *
FROM testtable
WHERE col1 LIKE ‘%///%’ ESCAPE ‘/‘;
Write a SQL statement to find those rows from the table testtable which contain the character ( % ) in its column ‘col1’.
SELECT *
FROM testtable
WHERE col1 LIKE ‘%/%%’ ESCAPE’/‘;
- Write a SQL statement to find that customer with all information who does not get any grade except NULL.
SELECT *
FROM customer
WHERE grade IS NULL;
SELECT COUNT (DISTINCT salesman_id)
FROM orders;
SELECT COUNT (ALL grade)
FROM customer;
SELECT city,MAX(grade)
FROM customer
GROUP BY city;
SELECT customer_id,MAX(purch_amt)
FROM orders
GROUP BY customer_id;
SELECT customer_id,ord_date,MAX(purch_amt)
FROM orders
GROUP BY customer_id,ord_date;
SELECT salesman_id,MAX(purch_amt)
FROM orders
WHERE ord_date = ‘2012-08-17’
GROUP BY salesman_id;
SELECT customer_id,ord_date,MAX(purch_amt)
FROM orders
GROUP BY customer_id,ord_date
HAVING MAX(purch_amt)>2000.00;
SELECT customer_id,ord_date,MAX(purch_amt)
FROM orders
GROUP BY customer_id,ord_date
HAVING MAX(purch_amt) BETWEEN 2000 AND 6000;
SELECT customer_id,ord_date,MAX(purch_amt)
FROM orders
GROUP BY customer_id,ord_date
HAVING MAX(purch_amt) IN(2000 ,3000,5760, 6000);
SELECT customer_id,MAX(purch_amt)
FROM orders
WHERE customer_id BETWEEN 3002 and 3007
GROUP BY customer_id;
SELECT customer_id,MAX(purch_amt)
FROM orders
WHERE customer_id BETWEEN 3002 and 3007
GROUP BY customer_id
HAVING MAX(purch_amt)>1000;
SELECT salesman_id,MAX(purch_amt)
FROM orders
GROUP BY salesman_id
HAVING salesman_id BETWEEN 5003 AND 5008;
SELECT ord_date,salesman_id,COUNT(*)
FROM orders
GROUP BY ord_date,salesman_id;
SELECT COUNT(*) AS “Number of Products”
FROM item_mast
WHERE pro_price >= 350;
select salesman_id, name, city, concat(commission*100, ‘%’) as “commission “
from salesman;
SELECT concat(‘For ‘,ord_date,’ ,there are ‘,
COUNT (DISTINCT ord_no),’ orders.’) as “each day summary”
FROM orders
GROUP BY ord_date;
SELECT salesman_id,ord_date,MAX(purch_amt)
FROM orders
GROUP BY salesman_id,ord_date
ORDER BY salesman_id,ord_date;
SELECT cust_name,city,grade
FROM customer
ORDER BY 3 DESC;
SELECT customer_id, COUNT(DISTINCT ord_no),
MAX(purch_amt)
FROM orders
GROUP BY customer_id
ORDER BY 2 DESC;
SELECT ord_date, SUM(purch_amt),
SUM(purch_amt)*.15
FROM orders
GROUP BY ord_date
ORDER BY ord_date;
SELECT ord_date, SUM(purch_amt),
SUM(purch_amt)*.15
FROM orders
GROUP BY ord_date
ORDER BY ord_date;
SELECT ord_no, cust_name, orders.customer_id, orders.salesman_id
FROM salesman, customer, orders
WHERE customer.city <> salesman.city
AND orders.customer_id = customer.customer_id
AND orders.salesman_id = salesman.salesman_id;
SELECT customer.cust_name AS “Customer”,
customer.city AS “City”,
salesman.name AS “Salesman”,
salesman.commission
FROM customer,salesman
WHERE customer.salesman_id = salesman.salesman_id
AND salesman.commission
BETWEEN .12 AND .14;
SELECT ord_no, cust_name, commission AS “Commission%”,
purch_amt*commission AS “Commission”
FROM salesman,orders,customer
WHERE orders.customer_id = customer.customer_id
AND orders.salesman_id = salesman.salesman_id
AND customer.grade>=200;
SELECT a.cust_name AS “Customer Name”,
a.city, b.name AS “Salesman”, b.commission
FROM customer a
INNER JOIN salesman b
ON a.salesman_id=b.salesman_id;
SELECT a.cust_name AS “Customer Name”,
a.city, b.name AS “Salesman”, b.commission
FROM customer a
INNER JOIN salesman b
ON a.salesman_id=b.salesman_id
WHERE b.commission>.12;
SELECT a.ord_no,a.ord_date,a.purch_amt,
b.cust_name AS “Customer Name”, b.grade,
c.name AS “Salesman”, c.commission
FROM orders a
INNER JOIN customer b
ON a.customer_id=b.customer_id
INNER JOIN salesman c
ON a.salesman_id=c.salesman_id;
Write a SQL statement to make a join on the tables salesman, customer and orders in such a form that the same column of each table will appear once and only the relational rows will come.
SELECT *
FROM orders
NATURAL JOIN customer
NATURAL JOIN salesman;
Write a SQL statement to make a list in ascending order for the customer who works either through a salesman or by own.
SELECT a.cust_name,a.city,a.grade,
b.name AS “Salesman”,b.city
FROM customer a
LEFT JOIN salesman b
ON a.salesman_id=b.salesman_id
order by a.customer_id;
Write a SQL statement to make a list in ascending order for the customer who holds a grade less than 300 and works either through a salesman or by own.
SELECT a.cust_name,a.city,a.grade,
b.name AS “Salesman”, b.city
FROM customer a
LEFT OUTER JOIN salesman b
ON a.salesman_id=b.salesman_id
WHERE a.grade<300
ORDER BY a.customer_id;
SELECT a.cust_name,a.city, b.ord_no,
b.ord_date,b.purch_amt AS “Order Amount”,
c.name,c.commission
FROM customer a
LEFT OUTER JOIN orders b
ON a.customer_id=b.customer_id
LEFT OUTER JOIN salesman c
ON c.salesman_id=b.salesman_id;
SELECT a.cust_name,a.city,a.grade,
b.name AS “Salesman”, b.city
FROM customer a
RIGHT OUTER JOIN salesman b
ON b.salesman_id=a.salesman_id
ORDER BY b.salesman_id;
Write a SQL statement to make a list for the salesmen who works either for one or more customer or not yet join under any of the customers who placed either one or more orders or no order to their supplier.
SELECT a.cust_name,a.city,a.grade,
b.name AS “Salesman”,
c.ord_no, c.ord_date, c.purch_amt
FROM customer a
RIGHT OUTER JOIN salesman b
ON b.salesman_id=a.salesman_id
RIGHT OUTER JOIN orders c
ON c.customer_id=a.customer_id;
Write a query in SQL to find the names of departments where more than two employees are working.
SELECT emp_department.dpt_name
FROM emp_details
INNER JOIN emp_department
ON emp_dept =dpt_code
GROUP BY emp_department.dpt_name
HAVING COUNT(*) > 2;
Write a query in SQL to find the first name and last name of employees working for departments with a budget more than Rs. 50000.
SELECT emp_details.emp_fname AS “First Name”, emp_lname AS “Last Name”
FROM emp_details
INNER JOIN emp_department
ON emp_details.emp_dept = emp_department.dpt_code
AND emp_department.dpt_allotment > 50000;
- Write a SQL query to display the name of each company along with the ID and price for their most expensive product.
SELECT A.pro_name, A.pro_price, F.com_name
FROM item_mast A INNER JOIN company_mast F
ON A.pro_com = F.com_id
AND A.pro_price =
(
SELECT MAX(A.pro_price)
);FROM item_mast A WHERE A.pro_com = F.com_id - Write a SQL query to display the names of the company whose products have an average price larger than or equal to Rs. 350.
SELECT AVG(pro_price), company_mast.com_name
FROM item_mast INNER JOIN company_mast
ON item_mast.pro_com= company_mast.com_id
GROUP BY company_mast.com_name
HAVING AVG(pro_price) >= 350;
Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for those salesmen who belongs to a city and the customers who must have a grade.
SELECT *
FROM salesman a
CROSS JOIN customer b
WHERE a.city IS NOT NULL
AND b.grade IS NOT NULL
AND a.city<>b.city;
Write a SQL statement to make a report with customer name, city, order no. order date, purchase amount for only those customers on the list who must have a grade and placed one or more orders or which order(s) have been placed by the customer who is neither in the list not have a grade.
SELECT a.cust_name,a.city, b.ord_no,
b.ord_date,b.purch_amt AS “Order Amount”
FROM customer a
FULL OUTER JOIN orders b
ON a.customer_id=b.customer_id
WHERE a.grade IS NOT NULL;Write a SQL statement to make a list for the salesmen who either work for one or more customers or yet to join any of the customer. The customer may have placed, either one or more orders on or above order amount 2000 and must have a grade, or he may not have placed any order to the associated supplier.
SELECT a.cust_name,a.city,a.grade,
b.name AS “Salesman”,
c.ord_no, c.ord_date, c.purch_amt
FROM customer a
RIGHT OUTER JOIN salesman b
ON b.salesman_id=a.salesman_id
LEFT OUTER JOIN orders c
ON c.customer_id=a.customer_id
WHERE c.purch_amt>=2000
AND a.grade IS NOT NULL;Write a query to display all the orders for the salesman who belongs to the city London.
SELECT *
FROM orders
WHERE salesman_id =
(SELECT salesman_id
FROM salesman
WHERE city=’London’);Write a query to find all the orders issued against the salesman who may works for customer whose id is 3007.
SELECT *
FROM orders
WHERE salesman_id =
(SELECT DISTINCT salesman_id
FROM orders
WHERE customer_id =3007);Write a query to display all the orders which values are greater than the average order value for 10th October 2012.
SELECT *
FROM orders
WHERE purch_amt >
(SELECT AVG(purch_amt)
FROM orders
WHERE ord_date =’10/10/2012’);Write a query to count the customers with grades above New York’s average.
SELECT grade, COUNT (*)
FROM customer
GROUP BY grade
HAVING grade >
(SELECT AVG(grade)
FROM customer
WHERE city = ‘New York’);Write a query to extract the data from the orders table for those salesman who earned the maximum commission
SELECT ord_no, purch_amt, ord_date, salesman_id
FROM orders
WHERE salesman_id IN(
SELECT salesman_id
FROM salesman
WHERE commission = (
SELECT MAX(commission)
FROM salesman));Write a query to display all the customers with orders issued on date 17th August, 2012.
SELECT b.*, a.cust_name
FROM orders b, customer a
WHERE a.customer_id=b.customer_id
AND b.ord_date=’2012-08-17’;Write a query to find the name and numbers of all salesmen who had more than one customer.
SELECT salesman_id,name
FROM salesman a
WHERE 1 <
(SELECT COUNT(*)
FROM customer
WHERE salesman_id=a.salesman_id);Write a query to find all orders with order amounts which are above-average amounts for their customers.
SELECT *
FROM orders a
WHERE purch_amt >
(SELECT AVG(purch_amt) FROM orders b
WHERE b.customer_id = a.customer_id);Write a query to find the sums of the amounts from the orders table, grouped by date, eliminating all those dates where the sum was not at least 1000.00 above the maximum order amount for that date
SELECT ord_date, SUM (purch_amt)
FROM orders a
GROUP BY ord_date
HAVING SUM (purch_amt) >
(SELECT 1000.00 + MAX(purch_amt)
FROM orders b
WHERE a.ord_date = b.ord_date);Write a query to extract the data from the customer table if and only if one or more of the customers in the customer table are located in London.
SELECT customer_id,cust_name, city
FROM customer
WHERE EXISTS
(SELECT *
FROM customer
WHERE city=’London’);- Write a query to find the salesmen who have multiple customers.
SELECT
FROM salesman
WHERE salesman_id IN (
SELECT DISTINCT salesman_id
FROM customer a
WHERE EXISTS (
SELECT
FROM customer b
WHERE b.salesman_id=a.salesman_id
AND b.cust_name<>a.cust_name));
SELECT
FROM salesman
WHERE salesman_id IN (
SELECT DISTINCT salesman_id
FROM customer a
WHERE NOT EXISTS (
SELECT FROM customer b
WHERE a.salesman_id=b.salesman_id
AND a.cust_name<>b.cust_name));
SELECT
FROM salesman a
WHERE EXISTS
(SELECT FROM customer b
WHERE a.salesman_id=b.salesman_id
AND 1<
(SELECT COUNT (*)
FROM orders
WHERE orders.customer_id =
b.customer_id));
Write a query to display all salesmen and customer located in London.
SELECT salesman_id “ID”, name, ‘Salesman’
FROM salesman
WHERE city=’London’
UNION
(SELECT customer_id “ID”, cust_name, ‘Customer’
FROM customer
WHERE city=’London’)Write a query to display all the salesmen and customer involved in this inventory management system.
SELECT salesman_id, customer_id
FROM customer
UNION
(SELECT salesman_id, customer_id
FROM orders)Write a query to make a report of which salesman produce the largest and smallest orders on each date.
SELECT a.salesman_id, name, ord_no, ‘highest on’, ord_date
FROM salesman a, orders b
WHERE a.salesman_id =b.salesman_id
AND b.purch_amt=
(SELECT MAX (purch_amt)
FROM orders c
WHERE c.ord_date = b.ord_date)
UNION
(SELECT a.salesman_id, name, ord_no, ‘lowest on’, ord_date
FROM salesman a, orders b
WHERE a.salesman_id =b.salesman_id
AND b.purch_amt=
(SELECT MIN (purch_amt)
FROM orders c
WHERE c.ord_date = b.ord_date))Write a query to list all the salesmen, and indicate those who do not have customers in their cities, as well as whose who do.
SELECT salesman.salesman_id, name, cust_name, commission
FROM salesman, customer
WHERE salesman.city = customer.city
UNION
(SELECT salesman_id, name, ‘NO MATCH’, commission
FROM salesman
WHERE NOT city = ANY
(SELECT cityFROM customer))ORDER BY 2 DESC
Write a query to that appends strings to the selected fields, indicating whether or not a specified salesman was matched to a customer in his city.
SELECT a.salesman_id, name, a.city, ‘MATCHED’
FROM salesman a, customer b
WHERE a.city = b.city
UNION
(SELECT salesman_id, name, city, ‘NO MATCH’
FROM salesman
WHERE NOT city = ANY
(SELECT cityFROM customer))ORDER BY 2 DESC
SELECT customer_id, city, grade, ‘High Rating’
FROM customer
WHERE grade >= 300
UNION
(SELECT customer_id, city, grade, ‘Low Rating’
FROM customer
WHERE grade < 300)
Write a command that produces the name and number of each salesman and each customer with more than one current order. Put the results in alphabetical order.
SELECT customer_id, cust_name
FROM customer a
WHERE 1<(SELECT COUNT (*) FROM orders b WHERE a.customer_id = b.customer_id)UNION
(SELECT salesman_id, name
FROM salesman a
WHERE 1 <(SELECT COUNT (*) FROM orders b WHERE a.salesman_id = b.salesman_id))ORDER BY 2
Write a query to create a view for those salesmen belongs to the city New York.
CREATE VIEW newyorkstaff
AS SELECT *
FROM salesman
WHERE city = ‘New York’;Write a query to create a view for all salesmen with columns salesman_id, name and city.
CREATE VIEW salesown
AS SELECT salesman_id, name, city
FROM salesman;
…