salesman_id name city commission ----------- ---------- ---------- ---------- 50
ID: 3606303 • Letter: S
Question
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
5003 Lauson Hen 0.12
5007 Paul Adam Rome 0.13
With reference to the above table with 4 columns:
Write a SQL query to retrieve only salesman_id and City. The output needs to be sorted by ascending order of city.
Write a SQL to select the maximum value of commission and display the salesman_id, name and city.
Write a SQL to display only those rows where name has a value ‘J’ in it.
Write a SQL to display only those rows where city is not null or empty.
Write a SQL to display only ‘name’ where commission = 0.13
Write a SQL to display all rows and have commission values multiplied by 100.
Write a SQL to display only the ‘total’(sum) of all commission in the table.
Write a SQL to display only the ‘average’ of all commission in the table.
Write SQL to display only top 2 (based on commission values) salesman_id and name .
Write a SQL to display all rows and columns and include a new column that will contains today’s date and time. (Use getdate() function).
Explanation / Answer
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
5003 Lauson Hen 0.12
5007 Paul Adam Rome 0.13
1)Write a SQL query to retrieve only salesman_id and City. The output needs to be sorted by ascending order of city.
Ans: SELECT salesman_id,City from salesTable order by City ASC.
2)Write a SQL to select the maximum value of commission and display the salesman_id, name and city.
Ans: Select salesman_id,name,city,MAX Commission from salesTable where Group_By salesman_id.
3)Write a SQL to display only those rows where name has a value ‘J’ in it.
Ans: Select salesman_id,name,city,Commission from SalesTable where name LIKE %J.
4)Write a SQL to display only those rows where city is not null or empty.
Ans: SELECT * from salesTable where city IS NULL.
5)Write a SQL to display only ‘name’ where commission = 0.13.
Ans: SELECT name from salesTable where commission=0.13.
6)Write a SQL to display all rows and have commission values multiplied by 100.
Ans: Update SalesTable SET commission=commission * 100.
7)Write a SQL to display only the ‘total’(sum) of all commission in the table.
Ans: Select SUM(commission) from salesTable.
8)Write a SQL to display only the ‘average’ of all commission in the table.
Ans: Select AVG(commission) from salesTable.
9)Write SQL to display only top 2 (based on commission values) salesman_id and name .
Ans: Select salesman_id,name from salesTable ORDER BY commission DESC LIMIT 2.
10)Write a SQL to display all rows and columns and include a new column that will contains today’s date and time. (Use getdate() function).
Ans: Alter table salesTable ADD today's DATETIME Not NULL DEFAULT (GETDATE());
SELECT * FROM salesTable.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.