SQL Practice
In this Section you see my SQL practice work
This all practice work completed by using MYSQL workbench.
DATABASE
First, create a database as "practice".
Using "CREATE DATABASE practice" command.
TABLES
1 . Salesman table
Input
Create a table as a salesman Using "CREATE TABLE salesman" command.
Another way, Right click on the practice database and then click Create table option and add the column's name with their datatype.
Then add values
Output
Table Salesman has 6 records and 4 columns.
2 . Customers table
Input
Create a table as a salesman Using "CREATE TABLE customer" command.
Another way, Right click on the practice database and then click Create table option and add the column's name with their datatype.
Then add values
Output
Table customer has 8 records and 5 columns.
3 . Order table
Input
Create a table as a salesman Using "CREATE TABLE orders" command.
Another way, Right click on the practice database and then click Create table option and add the column's name with their datatype.
Then add values
Output
Table customer has 12 records and 5 columns.
4 . Nobel Winner table
Input
Create a table as a salesman Using "CREATE TABLE nobel_winner" command.
Another way, Right click on the practice database and then click Create table option and add the column's name with their datatype.
Then add values
Output
Table customer has 12 records and 5 columns.
Table creation SQL query material Click Here
QUESTIONS
Q.1 Display the Name And Commission of all the salesman ?
SELECT name, commission
FROM salesman;
Q.2 Retrieve the salesman id of all salesmen from the orders table without any repeats
SELECT DISTINCT salesman_id
FROM orders
Where salesman_id
IS NOT NULL;
Q.3 Display the Names and City of a salesman, who belongs to the city of Paris.
SELECT name, city
FROM salesman
WHERE city = "Paris";
Q.4 Display all the information for those customers with a grade of 200.
SELECT *
FROM customer
WHERE grade = 200;
Q.5 Display the order number, order date and the purchase amount for orders which will be deliverd by the salesman with id 5001.
SELECT order_no, order_date, purch_amt
FROM orders
WHERE salesman_id = 5001;
Q.6 Show the winner of the 1971 prize for Literature
SELECT *
FROM NOBEL_WINNER
WHERE YEAR = 1971;
Q.7 Show all the details of the winners with first name Luis.
SELECT *
FROM nobel_winner
WHERE winner LIKE "Luis%";
Q.8 Show all the winners in Chemistry for 1970 together with the winner of Economics for 1971.
SELECT *
FROM nobel_winner
WHERE (subject = "Chemistry" and year = 1970)
UNION
SELECT *
FROM nobel_winner
WHERE (subject = "Economics" and year = 1971 );
Q.9 Show all the winners of Nobel prize in the year 1970 except the subject Physiology and Economics.
SELECT *
FROM nobel_winner
WHERE year = 1970
AND Subject
NOT IN ("Physiology","Economics");
Q.10 Find all the details of the Nobel winners for the subject not started with the letter 'P' and arranged the list as the most recent comes first, then by name in order.
SELECT *
FROM nobel_winner
WHERE winner NOT LIKE "p%"
ORDER BY year DESC, winner;
Q.11 Display all the customers, who are either belongs to the city New York or not had a grade above 100.
SELECT *
FROM customer
WHERE city = "New York"
OR NOT grade > 100;
Q.12 Find those salesmen with all information who gets the commission within a range of 0.10 and 0.14
SELECT *
FROM salesman
WHERE commission
BETWEEN 0.10 AND 0.14;
Q.13 Find all those customers with all information whose names are ending with the letter 'n'.
SELECT *
FROM customer
WHERE customer_name LIKE "%n";
Q.15 Find that customer with all information who does not get any grade except NULL
SELECT *
FROM customer
WHERE grade IS NULL;
Q.14 Find those salesmen with all information whose name containing the 1st character is 'N' and the 4th character is 'l' and the rests may be any character
SELECT *
FROM salesman
WHERE name LIKE "N__l%";
