Lab 02: Data Manipulation – Part 1 1- Copy and paste the content of the text fil

Question

Lab 02: Data Manipulation – Part 1 1- Copy and paste the content of the text file “Lab02_Data.txt” into any SQL Editor, then execute it. This code will create the table Agents and insert sample data into it. Provide the SQL statements that perform the following tasks: 2- List full details of all agents. 3- List full details of all agents sorted in the descending order of the date of birth. 4- List the agent number, the first and last names, the state, and the salary of all agents. 5- List the agent number, the first and last names, the state, and the salary of all agents sorted in the ascending order of the state and the descending order of the salary. 6- Produce a list for all agents, showing the agent number, the first and last names, and the monthly salaries (salary/12). Use the AS clause to name the last column monthlySalary. 7- List the state attribute of all agents. 8- Use the DISTINCT keyword to list the state values without duplications. 9- List the agent number, the first and last names, the address, the city, the state, and the salary of all agents with a salary greater than $50000. 10- List the agent number, the first and last names, the address, the city, the state, and the salary of all agents with a salary greater than $50000 and live in NY state. 11- List the agent number, the first and last names, the address, the city, the state, and the salary of all agents with a salary greater than $50000 and live in either NY state or MI state. Note: In this task, parenthesis should be used appropriately. 12- Use the BETWEEN keyword to list the agent number, the first and last names, the address, the city, the state, and the salary of all agents with a salary greater than $47000 and les than $50000. 13- Use the IN keyword to list full details of all agents living in any of the following states: CT, OH, or TX. 14- Use the NOT IN keyword to list full details of all agents who do not work in the branches B002 and B005. 15- Use the LIKE keyword to list full details of all agents with phone1 starting with '212-'. 16- List full details of all agents with the string 'Highway' in their address. 17- Use the LIKE keyword to list full details of all agents with first name having exactly 4 letters. 18- Use the LIKE keyword to list full details of all agents with email addresses not from yahoo.com. 19- List details of agents with empty phone1 numbers. 20- List details of agents with valid phone2 numbers (values are not empty)

lab 2.txt

Explanation / Answer

2.

select * from agents;

3.

select * from agents order by dob desc;

4.

select agentNo, fname,lname, state,salary from agents;

5.

select agentNo, fname,lname, state,salary from agents order by state asc, salary desc;

6.

select agentNo, fname,lname, (salary/12) as monthly_salaries from agents;

7.

select state from agents;

8.

select distinct(state) from agents;

9.

select agentNo, fname,lname,address,city, state,salary from agents where salary>50000;

10.

select agentNo, fname,lname,address,city, state,salary from agents where salary>50000 and state='NY';

11.

select agentNo, fname,lname,address,city, state,salary from agents where salary>50000 and (state='NY' or state='MI';

12.

select agentNo, fname,lname,address,city, state,salary from agents where salary between 47000 and 50000;

13.

select * from agents where state in ('CT'.'OH','TX');

14.

select * from agents where branchNo not in('B002','B005');

15.

select * from agents where phone1 like '212-%';

16.

select * from agents where address like'%Highway%';

17.

select * from agents where fName like '____';

18.

select * from agents where email not like '%yahoo.com';

19.

select * from agents where phone1 is null;

20.

select * from agents where phone2 is not null;

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.