-- Sql statement to display the information of all salesman
SELECT * FROM salesman;

-- Write a query to display a string "This is SQL Exercise, Practice and Solution".
SELECT "This is SQL Exercise, Practice and Solution";

-- Write a query to display three numbers in three columns.
SELECT 5,10,15;
-- Write a query to display the sum of two numbers 10 and 15 from RDMS sever
SELECT 10+15;
-- Write a query to display the result of an arithmetic expression
SELECT 10+15-5*2
-- Write a SQL statement to display specific columns like name and commission for all the salesmen
SELECT Name,comisssion From Salesman;
--  Write a query to display the columns in a specific order like order date, salesman id, order number and purchase amount from for all the orders.
SELECT ord_date, salesman_id, ord_no, purch_amt
FROM orders;
--From the following table, write a SQL query to find the unique salespeople ID. Return salesman_id.
SELECT DISTINCT salesman_id FROM orders;
-- From the following table, write a SQL query to find the salespeople who lives in the City of 'Paris'
SELECT name,city
FROM salesman
WHERE city='Paris';

-- write a SQL query to find those customers whose grade is 200
SELECT * FROM customer
WHERE grade=200;

-- write a SQL query to find the orders, which are delivered by a salesperson of ID. 5001
SELECT ord_no, ord_date, purch_amt FROM orders WHERE salesman_id=5001;

-- write a SQL query to find the Nobel Prize winner(s) in the year 1970. Return year
SELECT year,subject,winner
FROM Nobel_win
WHERE year=1970;

-- write a SQL query to find the Nobel Prize winner in 'Literature' in the year 1971
SELECT year,subject,winner FROM Nobel_win
WHERE subject='Literature';

-- write a SQL query to find the Nobel Prize winner 'Dennis Gabor'
SELECT year,subject,winner FROM Nobel_win
WHERE winner= 'Dennis Gabor';

-- write a SQL query to find the Nobel Prize winner in 'Physics'.
SELECT Winner FROM nobel_win WHERE subject='Physics';

-- write a SQL query to find the Nobel Prize winners in ‘Physics’ since the year 1950
SELECT Winner FROM nobel_win WHERE year>=1950 AND subject='Physics';-- write a SQL query to find the Nobel Prize winner 'Dennis Gabor'

-- write a SQL query to find those customers whose grade is 200.
SELECT *FROM customer
WHERE grade=200;

-- write a SQL query to find the Nobel Prize winners in 'Chemistry' between the years 1965 to 1975.

SELECT  year,subject,winner,country
FROM nobel_win
WHERE subject='Chemistry'AND
year>=1965 AND year<=1975;

-- Write a SQL query to show all details of the Prime Ministerial winners after 1972 of Menachem Begin and Yitzhak Rabin.
SELECT *FROM nobel_win
WHERE year>1972
AND winner IN('Menachem Begin','Yitzhak Rabin');

-- write a SQL query to find the details of the winners whose first name matches with the string 'Louis'. Return year, subject, winner, country, and category.
SELECT *FROM nobel_win
WHERE Winner LIKE 'Louis%';

-- write a SQL query to combine the winners in Physics, 1970 and in Economics, 1971
SELECT *FROM nobel_win
WHERE (Subject='Physics' AND year=1970) Union (Select *From nobel_win Where (subject='Economics' AND year = 1971));

-- write a SQL query to find the Nobel Prize winners in 1970 excluding the subjects Physiology and Economics. 
SELECT * FROM nobel_win
WHERE year=1970 AND subject NOT IN ('Physiology','Economics');

-- write a SQL query to combine the winners in 'Physiology' before 1971 and winners in 'Peace' on or after 1974
SELECT *FROM nobel_win
WHERE (year<1971 AND subject='Physiology') Union (SELECT *FROM nobel_win
WHERE (year>=1974 AND subject='Peace'));

-- write a SQL query to find the details of the Nobel Prize winner 'Johannes Georg Bednorz'.
SELECT *FROM nobel_win
WHERE winner LIKE('Johannes Georg Bednorz %');

-- write a SQL query to find the Nobel Prize winners for the subject not started with the letter 'P'. Return year, subject, winner, country, and category. Order the result by year, descending.
SELECT * FROM nobel_win
WHERE subject NOT like 'P%'
Order by year DESC,winners;

-- write a SQL query to find the details of 1970 Nobel Prize winners. Order the result by subject, ascending except 'Chemistry' and 'Economics' which will come at the end of result set.
SELECT *FROM nobel_win
WHERE year=1970
Order by CASE WHEN subject IN ('Chemistry','Economics')THEN 1 Else 0
END ASC,subject,winner;

--  write a SQL query to select a range of products whose price is in the range Rs.200 to Rs.600. Begin and end values are included.
SELECT * FROM item_mast
WHERE pro_price between 200 AND 600;

-- write a SQL query to calculate the average price for manufacturer code equal to 16
SELECT AVG (pro_price) FROM item_mast
WHERE pro_com=16;

-- write a SQL query to display the pro_name as 'Item Name' and pro_priceas 'Price in Rs.'
SELECT pro_name as "Item Name", pro_price AS "Price in Rs."
    FROM item_mast;
    
-- write a SQL query to find the items whose prices are higher than or equal to $250. Order the result by product price in descending, then product name in ascending
SELECT * FROM item_mast
pro_price,pro_name
where pro_price>=250
order by pro_price DESC,pro_name;

-- write a SQL query to calculate average price of the items of each company.
SELECT AVG(pro_price),pro_com
FROM item_mast
group by pro_com;

--  write a SQL query to find the cheapest item(s).
SELECT pro_name,pro_price 
FROM item_mast
Where pro_price=(slect min (pro_price)FROM item_list);

-- Find the last name of all employees, without duplicates
SELECT DISTICT emp_lname
FROM emp_details;

-- write a SQL query to find the details of employees whose last name is 'Snares'. Return emp_idno, emp_fname, emp_lname, and emp_dept.
SELECT * FROM emp_details
where emp_lname='Snares';

-- write a SQL query to find the details of the employees who work in the department 57
SELECT * FROM emp_details WHERE emp_dept= 57;