14 4 points Show how to use upper rtrim and ltrim functions

14. (4 points) Show how to use upper, rtrim and ltrim functions in a select statement to select title and ssn information of all instructors with last name to be George\" to retrieve row 1 and row 3. Notice that although has different upper case and lower case combinations, we deem different spellings of \"George\" the same at conceptual level. (n this problem, to test out upper, and ltrim, we have use char for both last and Pay to the upper case and lower case, and spaces in the table. Last Name First name GEORge Chiang Associate gEorgE berhent professor 567 141 567 34 333 65 333 45 141 to retum how many transacations the table has. Hint sama ple scripts 17. (4 points) write a SQL statement to retum how many ungiue SSN\'s the table has 18 (4 points) write a SQL statement to list unique SSNs and their corresponding total items. Also list the result 19. (4 points) write a SQL statement to list those SSNs and their total items such that the aggregated total items belonging to each unique ssN is larger than 40. Also list the result. (Hints, use both group by and having sub-clauses for this question and next question, read slides 8-4 and 8-5, you can download them from the course website). 20 (4 points) write a SQL statement to list those SSNs and their total items such that only those items of being larger than 10 will be involved in aggregation to reach the total for each individual SSN and the reached total items belonging to each unique SSN has to be larger than 30. Also list the result.

Solution

14. select ssn, title from Instructor where upper(ltrim(last_name)) = \'GEORGE\' ;

Above query selects all the ssn, title of instructors whose last_name is George irrespective of case.

15. create table statement and insert statements

create table Transaction (id int, ssn int, items int);

insert into Transaction values(1, 141, 20);

insert into Transaction values(2, 567, 4);

insert into Transaction values(3, 141, 5);

insert into Transaction values(4, 567, 34);

insert into Transaction values(5, 333, 65);

insert into Transaction values(6, 333, 45);

insert into Transaction values(7, 141, 34);

Group by having example: Find number of items for ssn 141

select ssn, SUM(items) from Transaction group by ssn having ssn=141

16. select count(id) from Transcation

17. select count( distint ssn) from Transaction.

18. select ssn, SUM(items) from Transaction group by ssn

Result:

ssn items

141 59

567 38

333 110

19. select ssn, SUM(items) from Transaction group by ssn having sum(items)>40

Result:

ssn items

141 59

333 110

20. select ssn, SUM(items) from Transaction where items>10 group by ssn having sum(items)>30

ssn items

141 54

567 34

333 110