Question

From the l_employees table, list all the different combinations of dept_code and credit_limit. Count the number of employees who are in each of these categories. Sort the rows by dept_code and then by credit_limit. Exclude the ‘SHP’ department in your results. Show only the dept_code, with a credit limit of over 12.50. Show your sql below.

Show me code step by step and or take screenshots of code and run test.

Answer 1

Hi,
Your are not sharing the structure of table l_employees which your using. But based on your question I'm creating one table which can reproducible all your question,
Take loop at table which I created:

create table if not exists l_employees(
   employ_name varchar(30) not null,
   dept_code varchar(10),
   credit_limit decimal(8,2),
   department varchar(10)
);

here i'm assuming dept_code is varchar of length 10, credit_limit of 8 length and 2 decimal points max(999999.99) and etc.

Insert some data into table using insert query:

insert into l_employees values("Shiva","ADMIN", 500000.00, "ADM");
insert into l_employees values("Venkat","HR", 600000.00, "ADM");
insert into l_employees values("Venky","DEV", 700000.00, "SHP");
insert into l_employees values("Swathi","QA", 800000.00, "ADM");
insert into l_employees values("Pooji",null, 400000.00, "ADM");
insert into l_employees values("Adi","BUSS", 300000.00, "SHP");
insert into l_employees values("Amara","ADMIN", null, "ADM");
insert into l_employees values("Ammu","DEV", 550000.00, "SHP");
insert into l_employees values("Kiran","DEV", 880000.00, "ADM");

After inserting take a loop at what table contains: (you can use select * from l_employees;)

Now let's start your questions:

1. List all the different combinations of dept_code and credit_limit :
SQL command you required for this is:

select d.dept_code,c.credit_limit
from (select distinct credit_limit from l_employees) c cross join
(select distinct dept_code from l_employees) d;

here d and c are variable which are pointing to dept_code and credit_limit respectively.
distinct credit_limit from l_employees return all unique values of credit_limit column values similarly select distinct dept_code from l_employees.

cross join : is SQL gives the all possible combination of two sets.

Output after executing above commad is :

2. Count the number of employees who are in each of these categories:

Here I took two categories are  dept_code and credit_limit, employees belongs each of these categories means all employees which are having both these categories as not null.

To achieve that required command id :

SELECT COUNT(*) FROM l_employees where dept_code is not null and credit_limit is not null;

Here count(*) returns one column as count, and values from l_employees table where dept_code is not null and credit_limit is also not null.

Output after this command:

because table have total 9 rows and 2 rows having null values. So answer is 7

Note: You can count any combination using below syntax
select count(*) from table_name where condition1,condition2.etc; example select count(*) where credit_limit > 500000.00 and department = "ADM"

3.Sort the rows by dept_code and then by credit_limit:
Command for this is:

select * from l_employees order by dept_code,credit_limit;

here order by returns sorted list with respective to column dept_code and column credit_limit.

Syntax is : select * from table_name order by column1,column2..etc;

Output:

4.Exclude the ‘SHP’ department in your results:

For this you only require where clause to avoid SHP department;

Examples :
1. avoid SHP department in problem1:

select d.dept_code,c.credit_limit
from (select distinct credit_limit from l_employees where department != "SHP") c cross join
(select distinct dept_code from l_employees where department != "SHP") d;

result is:

2. avoid SHP department in problem2:

SELECT COUNT(*) FROM l_employees where dept_code is not null and credit_limit is not null and department != "SHP";
Output:

3.  avoid SHP department in problem3:

select * from l_employees where department != "SHP" order by dept_code,credit_limit;
Output:

5.   Show only the dept_code, with a credit limit of over 12.50 :
For the best suit with current table I'm making credit_card limit to 500000.00

command is:

select distinct dept_code from l_employees where credit_limit > 500000.00;
Output:

All commands at once:

Hope you got what you want . Cheers..?????!