Question

Consider the table project given below. Write command in SQL for 1 to 4 and output for 5 to 8​

Answer 1

i. To display all information about projects of 'Medium' ProjSize.

• Select * from Projects where ProjSize = 'Medium';

ii. To list the ProjSize of projects whose ProjName ends with 'LITL'.

• Select ProjSize from Projects where ProjName like '%LITL';

iii. To list ID, name, size and cost of all the projects in descending order of StartDate.

• Select ID, ProjName, ProjSize, Cost from Projects order by StartDate desc;

iv. To count the number of projects whose Cost is less than 100000.

• Select count(*) from Projects where Cost < 100000;

v. $\tt Select\ sum(Cost)\ from\ Projects;$

$\begin{array}{|c|}\cline{1-1} \bf sum(Cost)\\\cline{1-1}\sf 980000\\\cline{1-1}\end{array}$

vi. $\tt Select\ distinct(ProjSize)\ from\ Projects;$

$\begin{array}{|c|}\cline{1-1} \bf distinct(ProjSize)\\\cline{1-1}\sf Medium\\\cline{1-1}\sf Large\\\cline{1-1}\sf Small\\\cline{1-1}\end{array}$

vii. $\tt Select\ max(Cost)\ from\ Projects;$

$\begin{array}{|c|}\cline{1-1} \bf max(Cost)\\\cline{1-1}\sf 500000\\\cline{1-1}\end{array}$

viii. $\tt Select\ avg(Salary)\ from\ Projects\ group\ by\ ProjSize;$

$\begin{array}{|c|c|}\cline{1-2}\bf avg(Salary) & \bf ProjSize\\\cline{1-2}\sf 53333.3333333 & \sf Medium\\\cline{1-2}\sf 400000 & \sf Large\\\cline{1-2}\sf 20000 & \sf Small\\\cline{1-2}\end{array}$

• Select is used to display/retrieve information from the table.
• Where is a clause used to specify conditions.
• Update is used to made changes to the data present in the table.
• Delete is used to delete records from the table.
• The asterisk (*) represents all the data from the table. If you want specific columns from the table to be displayed, you need to type the column name(s) instead.
• Like is a clause used to perform pattern-matching.
• A percentage/modulus (%) is a wildcard character that represents 'n' number of characters. An underscore (_) represents a single character.
• Count() is a function used to count the number of records.
• Order by is a clause used to arrange the data in a specific order.
• Sum() is a function used to calculate the total of the specified column.
• Distinct is a clause used to eliminate redundancy.
• Max() is a function used to retrieve the highest value from the column.
• Avg() is a function used to calculate the average of the function.
• Group by is a clause used to group data according to a specific category.