SQL Practice with 24 Queries, Slides of Database Management Systems (DBMS)

Download SQL Practice with 24 Queries and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

SQL Practice 1

One table, Aggregation, Group By

24 Queries

Query 1

• Display name and commission for all the

salesmen.

SELECT name, commission FROM salesman;

name commission

James Hoog 0. Nail Knite 0. Pit Alex 0. Mc Lyon 0. Paul Adam 0. Lauson Hen 0.

Query 2

• Retrieve salesman id of all salesmen from orders table without any

repeats.

Query 3

• Display names and city of salesman, who belongs to the city of Paris.

Query 3

• Display names and city of salesman, who belongs to the city of Paris. name city Nail Knite Paris Mc Lyon Paris SELECT name,city FROM salesman WHERE city='Paris';

Query 5

• Display the order number, order

date and the purchase amount for

order(s) which will be delivered by

the salesman with ID 5001.

ord_no ord_date purch_amt 70002 2012-10-05 65. 70005 2012-07-27 2400. 70008 2012-09-10 5760. 70013 2012-04-25 3045. SELECT ord_no, ord_date, purch_amt FROM orders WHERE salesman_id = 5001;

Query 6 (table: nobel_win)

• Show the winner of the 1971 prize for Literature.

SELECT winner

FROM nobel_win

WHERE year = 1971

AND subject = 'Literature';

winner

Pablo Neruda

Query 8

• Show all the winners in Physics for 1970 together with the winner of Economics for 1971.

year subject winner country category

1970 Physics Hannes Alfven Sweden Scientist 1970 Physics Louis Neel France Scientist 1971 Economics Simon Kuznets Russia Economist SELECT * FROM nobel_win WHERE (subject = 'Physics' AND year = 1970) UNION (SELECT * FROM nobel_win WHERE (subject = 'Economics' AND year = 1971) );

Query 9

• Show all the winners of Nobel prize in the year 1970 except the subject Physiology and Economics.

year subject winner country category

1970 Physics Hannes Alfven Sweden Scientist 1970 Physics Louis Neel France Scientist 1970 Chemistry Luis Federico Leloir France Scientist 1970 Literature Aleksandr Solzhenitsyn Russia Linguist SELECT * FROM nobel_win WHERE year = 1970 AND subject NOT IN ('Physiology','Economics');

Query 11 (table: item_mast)

• Find the name and price of the cheapest item(s). SELECT pro_name, pro_price FROM item_mast WHERE pro_price = (SELECT MIN(pro_price) FROM item_mast); pro_name pro_price ZIP drive 250. Mouse 250.

Query 12 (table: customer)

• Display all the customers, who are either belongs to the city New York or not had a grade above 100. customer_id cust_name city grade salesman_id 3002 Nick Rimando New York 100 5001 3007 Brad Davis New York 200 5001 3009 Geoff Cameron Berlin 100 5003 SELECT * FROM customer WHERE city = 'New York' OR NOT grade > 100;

Query 14 (table: customer)

• Find all those customers with all information whose names are ending with the letter 'n'. SELECT * FROM customer WHERE cust_name LIKE '%n';

Query 15 (table: salesmen)

• Find those salesmen with all information whose name containing the 1st character is 'N' and the 4th

character is 'l' and rests may be any character.

SELECT * FROM salesman WHERE name LIKE ‘N__l%';