- write a SQL statement to find the maximum commission percent from the SalesPerson table
SELECT MAX(CommissionPct) AS ‘Maximum Commission Percent’ FROM Sales.SalesPerson ;
Result: 0.02
- write a SQL statement to retrieve all records from the production.product table and display names in a descending order.
SELECT * FROM Production.Product ORDER BY Name DESC;
Result: 504 rows, each with 25 columns.
- Write a SQL query to display name, product number, a list price for all products where product lines contain letter “R” and days to manufacture is less than 4. Order the results by product name in ascending order. Use the production.product table for your query.
SELECT Name, ProductNumber, ListPrice FROM Production.Product
WHERE (ProductLine LIKE ‘%R%’ AND DaysToManufacture < 4)
ORDER BY Name ASC;
Result: 57 rows, each with 3 columns.
- Write a query that retrieves the columns ProductID, Name, Color and ListPrice from the Production.Product table.
SELECT ProductID, Name, Color, ListPrice FROM Production.Product;
Result: 504 rows, each with 4 columns.
- Write a SQL statement to count the number of female employees for the humanresources. employee table
SELECT COUNT(*) AS ‘Number of Female Employees’ FROM HumanResources.Employee WHERE Gender = ‘F’;
Result: 84
- Write a SQL statement to find the highest List Price of any Product. Use production.product table .
SELECT MAX(ListPrice) FROM Production.Product;
Result: 3578.27
- Write a query that retrieves the columns ProductID, Name, Color and ListPrice from the Production.Product table, exclude the listprice 0 from the results
SELECT ProductID, Name, Color, ListPrice FROM Production.Product WHERE ListPrice <> 0 ORDER BY ListPrice ASC;
Result: 304 rows with 4 columns each.
- Write a query that retrieves the columns ProductID, Name, Color and ListPrice from the Production.Product table where the column color is NULL.
SELECT ProductID, Name, Color, ListPrice FROM Production.Product WHERE Color IS NULL;
Result: 248 rows with 4 columns each.
- Write a query that retrieves the columns ProductID, Name, Color and ListPrice from the Production.Product table where the column color is not NULL.
SELECT ProductID, Name, Color, ListPrice FROM Production.Product WHERE Color IS NOT NULL;
Result: 256 rows with 4 columns each.
- Write a query that retrieves the columns ProductID, Name, Color and ListPrice from the Production.Product table where the column color is NULL and the column listprice has a value greater than 0.
SELECT ProductID, Name, Color, ListPrice FROM Production.Product WHERE Color IS NULL AND ListPrice > 0;
Result: 59 rows with 4 columns each.
- Write a query that retrieves unique colors from the table
Production.Product. We do not want to see all the rows, just what colors that
exist in the column Color.
SELECT DISTINCT Color FROM Production.Product;
Result: 10 rows – values: NULL, Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow