Question

The Language Is SQL

13- List full details of agents with first names having exactly three or four letters sorted by the state attribute. 14- List the first name, the last name, the address, the city, the state, the branch number, and the email of agents working in the branch 8005 and having email addresses from yahoo.com. 15-List the first name, the last name, the address, the city, the state, the branch number, and the email of agents working in the branch BOOS and having email addresses ending with extensions different from.com 16- List full details of agents with empty (NULL) email addresses. 17-Display the total number of agents. 18-Display the minimum, the average, and the maximum salaries of all agents. 19. Display the number of agents living in New York state and the sum of their salaries. 20-Display the number of agents with NULL email addresses. 21-Display the number of agents who work in the branch B002 and have salaries greater than $40000 22-Display the number of branches. Note: each different branch needs to be counted once. 23-Display, in each state, the number of agents sorted in the descending order. 24-Display the number of agents in each branch and the sum of their salaries. 25-In each branch, display the number of agents living in New Jersey or New York states and the average of their salaries. 26-Exclude, from the list you displayed in the previous task, the branches that have salary averages less than $34000.

The language is SQL

Answer 1

13.

SELECT *fROM AGENTSTABLE WHERE LENGTH(FNAME)=3 OR LENGTH(FNAME)=4 ORDER BY STATE ;

//USE LENGTH() FUNCTION TO COUNT LENGTH OF FNAME

SQL> SELECT * FROM AGENTSTABLE WHERE LENGTH(FNAME) =3 OR LENGTH(FNAME=4 ORDER BY STATE ; AGENT FNAME LNAME ADDRESS CITY STATE BRANCHNUMB EMAIL SALARY A417 EDEN A888 RAJ A777 RAM A293 RAUL JAYSON 4 IWAENA NAND JKTOWN JATAN 4 RKROAD UP THEGROVE 4 E COLONIAL BALTIMORE CITY BALTIMORE JKCITY JK RKCITY NEW JERSEY LA MESA SAN DIEGO B005 B005 B005 B002 NULL [email protected] [email protected] NULL 12000 900 99000 78900

14.

select fname,lname,address,city,state,branchNo,email from agentstable where branchNo='B005' and
substr(email,instr(email,'@',1,1)+1)='YAHOO.COM';

HERE INSTR RETURNS THE NEXT POSITION OF @ WHICH IS PASSED IN SUBSTR TO SET THE STARTING CHARACTER OF SUBSTR WHICH MUST BE EQUAL TO 'YAHOO.COM'

SOL> select fname, 1name, address, city, state,branchNo, email from agentstable where branchNo='Bee5" and 2 substr(email,instr(email, '@',1,1)+1)= 'YAHOO.COM'; FNAME LNAME ADDRESS CITY STATE BRANCHNO EMAIL BASANT DWIVEDI BNAGAR JMT JHARKHAND B005 [email protected]

15.

select fname,lname,address,city,state,branchNo,email from agentstable where
substr(email,instr(email,'.',1,1)+1) not in('COM');

//NOT IN USED TO EXCLUDE COM EXTENSION

SQL> select fname, lname, address, city, state, branchNo, email from agentstable where 2 substr(email, instr(email,'.',1,1)+1) not in ('COM'); FNAME LNAME ADDRESS CITY STATE BRANCHNO EMAIL MUNNA RAJ TIWAR NAND PKD JKTOWN PKCITY JKCITY NEW YORK JK B005 B005 [email protected] [email protected]

16.

select *From agentstable where email is null;

//IS NULL IS USED TO FIND NULL CONTAINIG ROWS

SQL> select *From agentstable where email is null; AGENT FNAME LNAME ADDRESS CITY STATE SALARY BRANCHNUMB EMAIL - -------- BOO2 B005 A293 RAUL A417 EDEN UPTHEGROVE 4 E COLONIAL JAYSON 4 IWAENA LA MESA SAN DIEGO BALTIMORE CITY BALTIMORE 78900 12000

17.

select count(agentNo) from agentstable;

//COUNT FUNCTION IS USED TO KEEP THE COUNT OF AGENTNO

SQL> select count(agentNo) from agentstable; COUNT (AGENTNO)

18.

select min(salary),avg(salary),max(salary) from agentstable;

//MIN(), MAX() AND AVG() RETURNS THE MINIMUM , MAXIMUM AND AVERAGE

SQL> select min(salary), avg(salary), max(salary) from agentstable; MIN( SALARY) AVG(SALARY) MAX( SALARY) 900 52971.4286 99000

19.

select count(agentNo),sum(salary) from agentstable where state='NEW YORK';

NOTE PLEASE USE 'NY' IN PLACE OF 'NEW YORK'

SQL> select count(agentid), sum( salary) from agentstable where state="NEW YORK'; COUNT (AGENTID) SUM( SALARY) 1 40000

20.

select count(agentNo) from agentstable where email is null;

SQL> select count(agentNo) from agentstable where email is null; COUNT (AGENTNO)

21.

select count(agentNo) from agentstable where branchNo='B002' and salary>40000;

SQL> select count(agentNo) from agentstable where branchNo-'B002' and salary>40000; COUNT (AGENTNO) 2

22.

select count(distinct(branchNo)) from agentstable;

//DISTINCT RETURNS THE UNIQUE VALUE

SQL> select count(distinct(branchNo)) from agentstable; COUNT(DISTINCT (BRANCHNO))

23.

select state,count(agentNo) from agentstable group by state order by state;

SQL> select state, count(agentNo) from agentstable group by state order by state; STATE COUNT (AGENTNO) BALTIMORE JHARKHAND JK MONTGOMERY NEW JERSEY NEW YORK SAN DIEGO 7 rows selected.

24.

select branchNo,count(agentNo) from agentstable group by branchNo;

SQL> select branchNo, count(agentNo) from agentstable group by branchNo; BRANCHNO COUNT (AGENTNO) -------- B005 B002

25.

select branchNo,count(agentNo),avg(salary) from agentstable where state='NEW JERSEY' or state='NEW YORK' group by branchNo;

NOTE PLEASE USE 'NJ' IN PLACE OF 'NEW JERSEY' AND 'NY' IN PLACE OF 'NEW YORK'

SOL> select branchNo,count (agentNo), avg(salary) from agentstable where state='NEW JERSEY' or state-'NEW YORK' group by branchNo; BRANCHNO COUNT (AGENTNO) AVG(SALARY), B005 69500

26.

select branchNo,count(agentNo),avg(salary) from agentstable where state='NEW JERSEY' or state='NEW YORK' group by branchNo
having avg(salary)<34000;

select branchNo,count (agentNo), avg(salary) from agentstable where state-'NEW JERSEY' or state='NEW YORK' group by branchNo having avg(salary)<34000;

DON'T FORGET TO LIKE.

THANKS BY HEART.