Welcome to Testing Mentor

Thursday, January 28, 2010

SQL Queries

SQL Queries

Create the following Tables:

LOCATION
Location_ID	Regional_Group
122	NEW YORK
123	DALLAS
124	CHICAGO
167	BOSTON

DEPARTMENT
Department_ID	Name	Location_ID
10	ACCOUNTING	122
20	RESEARCH	124
30	SALES	123
40	OPERATIONS	167

JOB
Job_ID	Function
667	CLERK
668	STAFF
669	ANALYST
670	SALESPERSON
671	MANAGER
672	PRESIDENT

EMPLOYEE
EMPLOYEE_ID	LAST_NAME	FIRST_NAME	MIDDLE_NAME	JOB_ID	MANAGER_ID	HIREDATE	SALARY	COMM	DEPARTMENT_ID
7369	SMITH	JOHN	Q	667	7902	17-DEC-84	800	NULL	20
7499	ALLEN	KEVIN	J	670	7698	20-FEB-85	1600	300	30
7505	DOYLE	JEAN	K	671	7839	04-APR-85	2850	NULL	30
7506	DENNIS	LYNN	S	671	7839	15-MAY-85	2750	NULL	30
7507	BAKER	LESLIE	D	671	7839	10-JUN-85	2200	NULL	40
7521	WARK	CYNTHIA	D	670	7698	22-FEB-85	1250	500	30

Queries based on the above tables:

Simple Queries:

List all the employee details
List all the department details
List all job details
List all the locations
List out first name,last name,salary, commission for all employees
List out employee_id,last name,department id for all employees and rename employee id as "ID of the employee", last name as "Name of the employee", department id as "department ID"
List out the employees anuual salary with their names only.

Where Conditions:

List the details about "SMITH"
List out the employees who are working in department 20
List out the employees who are earning salary between 3000 and 4500
List out the employees who are working in department 10 or 20
Find out the employees who are not working in department 10 or 30
List out the employees whose name starts with "S"
List out the employees whose name start with "S" and end with "H"
List out the employees whose name length is 4 and start with "S"
List out the employees who are working in department 10 and draw the salaries more than 3500
list out the employees who are not receiving commission.

Order By Clause:

List out the employee id, last name in ascending order based on the employee id.
List out the employee id, name in descending order based on salary column
list out the employee details according to their last_name in ascending order and salaries in descending order
list out the employee details according to their last_name in ascending order and then on department_id in descending order.

Group By & Having Clause:

How many employees who are working in different departments wise in the organization
List out the department wise maximum salary, minimum salary, average salary of the employees
List out the job wise maximum salary, minimum salary, average salaries of the employees.
List out the no.of employees joined in every month in ascending order.
List out the no.of employees for each month and year, in the ascending order based on the year, month.
List out the department id having atleast four employees.
How many employees in January month.
How many employees who are joined in January or September month.
How many employees who are joined in 1985.
How many employees joined each month in 1985.
How many employees who are joined in March 1985.
Which is the department id, having greater than or equal to 3 employees joined in April 1985.

Sub-Queries

Display the employee who got the maximum salary.
Display the employees who are working in Sales department
Display the employees who are working as "Clerk".
Display the employees who are working in "New York"
Find out no.of employees working in "Sales" department.
Update the employees salaries, who are working as Clerk on the basis of 10%.
Delete the employees who are working in accounting department.
Display the second highest salary drawing employee details.
Display the Nth highest salary drawing employee details

Sub-Query operators: (ALL,ANY,SOME,EXISTS)

List out the employees who earn more than every employee in department 30.
List out the employees who earn more than the lowest salary in department 30.
Find out whose department has not employees.
Find out which department does not have any employees.

Co-Related Sub Queries:

47.Find out the employees who earn greater than the average salary for their department.

Joins

Simple join

48.List our employees with their department names

49.Display employees with their designations (jobs)

50.Display the employees with their department name and regional groups.

51.How many employees who are working in different departments and display with department name.

52.How many employees who are working in sales department.

53.Which is the department having greater than or equal to 5 employees and display the department names in ascending order.

54.How many jobs in the organization with designations.

55.How many employees working in "New York".

Non – Equi Join:

56.Display employee details with salary grades.

57.List out the no. of employees on grade wise.

58.Display the employ salary grades and no. of employees between 2000 to 5000 range of salary.

Self Join:

59.Display the employee details with their manager names.

60.Display the employee details who earn more than their managers salaries.

61.Show the no. of employees working under every manager.

Outer Join:

61.Display employee details with all departments.

62.Display all employees in sales or operation departments.

Set Operators:

63.List out the distinct jobs in Sales and Accounting Departments.

64.List out the ALL jobs in Sales and Accounting Departments.

65.List out the common jobs in Research and Accounting Departments in ascending order.

Answers

SQL > Select * from employee;
SQL > Select * from department;
SQL > Select * from job;
SQL > Select * from loc;
SQL > Select first_name, last_name, salary, commission from employee;
SQL > Select employee_id "id of the employee", last_name "name", department id as "department id" from employee;
SQL > Select last_name, salary*12 "annual salary" from employee
SQL > Select * from employee where last_name='SMITH';
SQL > Select * from employee where department_id=20
SQL > Select * from employee where salary between 3000 and 4500
SQL > Select * from employee where department_id in (20,30)
SQL > Select last_name, salary, commission, department_id from employee where department_id not in (10,30)
SQL > Select * from employee where last_name like 'S%'
SQL > Select * from employee where last_name like 'S%H'
SQL > Select * from employee where last_name like 'S___'
SQL > Select * from employee where department_id=10 and salary>3500
SQL > Select * from employee where commission is Null
SQL > Select employee_id, last_name from employee order by employee_id
SQL > Select employee_id, last_name, salary from employee order by salary desc
SQL > Select employee_id, last_name, salary from employee order by last_name, salary desc
SQL > Select employee_id, last_name, salary from employee order by last_name, department_id desc
SQL > Select department_id, count(*), from employee group by department_id
SQL > Select department_id, count(*), max(salary), min(salary), avg(salary) from employee group by department_id
SQL > Select job_id, count(*), max(salary), min(salary), avg(salary) from employee group by job_id
SQL > Select to_char(hire_date,'month')month, count(*) from employee group by to_char(hire_date,'month') order by month
SQL > Select to_char(hire_date,'yyyy') Year, to_char(hire_date,'mon') Month, count(*) "No. of employees" from employee group by to_char(hire_date,'yyyy'), to_char(hire_date,'mon')
SQL > Select department_id, count(*) from employee group by department_id having count(*)>=4
SQL > Select to_char(hire_date,'mon') month, count(*) from employee group by to_char(hire_date,'mon') having to_char(hire_date,'mon')='jan'
SQL > Select to_char(hire_date,'mon') month, count(*) from employee group by to_char(hire_date,'mon') having to_char(hire_date,'mon') in ('jan','sep')
SQL > Select to_char(hire_date,'yyyy') Year, count(*) from employee group by to_char(hire_date,'yyyy') having to_char(hire_date,'yyyy')=1985
SQL > Select to_char(hire_date,'yyyy')Year, to_char(hire_date,'mon') Month, count(*) "No. of employees" from employee where to_char(hire_date,'yyyy')=1985 group by to_char(hire_date,'yyyy'),to_char(hire_date,'mon')
SQL > Select to_char(hire_date,'yyyy')Year, to_char(hire_date,'mon') Month, count(*) "No. of employees" from employee where to_char(hire_date,'yyyy')=1985 and to_char(hire_date,'mon')='mar' group by to_char(hire_date,'yyyy'),to_char(hire_date,'mon')
SQL > Select department_id, count(*) "No. of employees" from employee where to_char(hire_date,'yyyy')=1985 and to_char(hire_date,'mon')='apr' group by to_char(hire_date,'yyyy'), to_char(hire_date,'mon'), department_id having count(*)>=3
SQL > Select * from employee where salary=(select max(salary) from employee)
SQL > Select * from employee where department_id IN (select department_id from department where name='SALES')
SQL > Select * from employee where job_id in (select job_id from job where function='CLERK'
SQL > Select * from employee where department_id=(select department_id from department where location_id=(select location_id from location where regional_group='New York'))
SQL > Select * from employee where department_id=(select department_id from department where name='SALES' group by department_id)
SQL > Update employee set salary=salary*10/100 wehre job_id=(select job_id from job where function='CLERK')
SQL > delete from employee where department_id=(select department_id from department where name='ACCOUNTING')
SQL > Select * from employee where salary=(select max(salary) from employee where salary <(select max(salary) from employee))
SQL > Select distinct e.salary from employee where & no-1=(select count(distinct salary) from employee where sal>e.salary)
SQL > Select * from employee where salary > all (Select salary from employee where department_id=30)
SQL > Select * from employee where salary > any (Select salary from employee where department_id=30)
SQL > Select employee_id, last_name, department_id from employee e where not exists (select department_id from department d where d.department_id=e.department_id)
SQL > Select name from department d where not exists (select last_name from employee e where d.department_id=e.department_id)
SQL > Select employee_id, last_name, salary, department_id from employee e where salary > (select avg(salary) from employee where department_id=e.department_id)
SQL > Select employee_id, last_name, name from employee e, department d where e.department_id=d.department_id
SQL > Select employee_id, last_name, function from employee e, job j where e.job_id=j.job_id
SQL > Select employee_id, last_name, name, regional_group from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id
SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name
SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having name='SALES'
SQL > Select name, count(*) from employee e, department d where d.department_id=e.department_id group by name having count (*)>=5 order by name
SQL > Select function, count(*) from employee e, job j where j.job_id=e.job_id group by function
SQL > Select regional_group, count(*) from employee e, department d, location l where e.department_id=d.department_id and d.location_id=l.location_id and regional_group='NEW YORK' group by regional_group
SQL > Select employee_id, last_name, grade_id from employee e, salary_grade s where salary between lower_bound and upper_bound order by last_name
SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound group by grade_id order by grade_id desc
SQL > Select grade_id, count(*) from employee e, salary_grade s where salary between lower_bound and upper_bound and lower_bound>=2000 and lower_bound<=5000 group by grade_id order by grade_id desc
SQL > Select e.last_name emp_name, m.last_name, mgr_name from employee e, employee m where e.manager_id=m.employee_id
SQL > Select e.last_name emp_name, e.salary emp_salary, m.last_name, mgr_name, m.salary mgr_salary from employee e, employee m where e.manager_id=m.employee_id and m.salary<e.salary
SQL > Select m.manager_id, count(*) from employee e, employee m where e.employee_id=m.manager_id group by m.manager_id
SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id
SQL > Select last_name, d.department_id, d.name from employee e, department d where e.department_id(+)=d.department_id and d.department_idin (select department_id from department where name IN ('SALES','OPERATIONS'))
SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='SALES')) union Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='ACCOUNTING'))
SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='SALES')) union all Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='ACCOUNTING'))
SQL > Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='RESEARCH')) intersect Select function from job where job_id in (Select job_id from employee where department_id=(select department_id from department where name='ACCOUNTING')) order by function

Tuesday, April 28, 2009

Why do we test?

Altimeter and autopilot possible cause of plane crash near Schiphol

I read an interesting article from the BBC, headlined "Altimeter 'had role' in air crash". In reporting a news conference conducted by Dutch Safety Board chairman Pieter van Vollenhoven, the article reads in part:

...the plane had been at an altitude of 595m (1950ft) when making its landing approach to Schiphol airport. But the altimeter recorded an altitude of around ground level. The plane was on autopilot and its systems believed the plane was already touching down, he said.

The automatic throttle controlling the two engines was closed and they powered down. This led to the plane losing speed, and stalling.

I am surprised that an autopilot would throttle back engines based on only one instrument, the altimeter. I would have assumed additional criteria would need to be met - perhaps having weight on the landing gear.

The article raises other interested points, and can be found here:
http://news.bbc.co.uk/2/hi/europe/7923782.stm

Turkish Airline disaster and the Altimeter

As you probably know, a Boeing 737-800 with 127 passengers and seven crew crashed near Schiphol airport in the Netherlands, killing nine and injuring many others. The details are starting to emerge that the left altimeter was faulty, and that from 2000 feet, it notified the autopilot that they were suddenly at -8 feet. Autopilot immediately cut the power to the engines, stalling it in mid air. Due to the weather, the pilots had to rely on their instruments and could not see what was wrong until the stall indicators came on.

What I would like to know is that how software testing is done at Boeing. I fail to see how the software would not spot a problem and carry out the landing:

1) If the two altimeters are reading very different readings,

2) If one of the altimeters switches from reading 2000 feet to -8 feet instantly,
3) If one of the altimeters reads a negative number?

If the software had warned them, I'm sure these pilots would not have died, along with several passengers.

[Somewhat similar comment from Ben Blout. Also, there has been extensive discussion on this topic around the Net. Having two of anything always suggests the problem of what to do what they disagree. (Les Lamport's paper on Buridan's Ass comes to mind). That problem suggests that having THREE might be a better strategy, and seeking consensus. But sanity checking is also a good idea, and trusting absurd readings is not wise. Perhaps the biggest problem is again that autopilots and people are not infallible, but the lack of synergy between the two can be even more debilitating. ]

Google Calendar as a single point of failure?

I keep most of my scheduled appointments on Google Calendar (although it is a pain since I have to be careful not to put proprietary information in my appointment descriptions). I missed a teleconference today, because Google Calendar said it was at 4pm, and when I showed up no one was there. Going back through my notes I discovered the meeting was supposed to be at 3pm, and I had set it up as a recurring event. I then discovered from reading Google Help forums that there are known problems - when Daylight Savings Time started, recurring events got moved either an hour earlier or an hour later, depending on whether you were the originator of the meeting or an invitee.

There was another symptom I noticed - my meeting was at 3pm (Standard time) which got shifted to 4pm (Savings) time. When I tried to change the time back to 3pm, it had no effect - presumably because it thought the meeting was already scheduled for 3pm (Standard). So to make it show up on my calendar at 3pm (Savings), I had to change the schedule to 2pm (Standard).

Once Google fixes the problem, I have to remember to move the scheduled time back again, so I show up at the right time!

RISK? When there's a shared calendar infrastructure and it's buggy, everyone ends up at the wrong time. Something similar happened last year when the US switched to Savings time earlier than in previous years - Microsoft and other vendors rushed out patches to handle the time change.

[Along many other problems discussed here, this one seems to recur.]

A firmware glitch of router software: 32-bit integer handling

I am not sure if this is computer risk in the general sense, but I feel we will see more of this type of problems (32-bit signed integer vs unsigned integer) in embedded devices for consumer electronics and elsewhere for some time to come, and so I am reporting it here.

First the public fact.

NEC, a large electronics company, and its subsidiary NEC Access Technica have announced that their line of DSL routers with IP-phone feature which are used by many Japanese ISPs including the giants NTT East and NTT West has a software bug that after a continuous use of 2485 days, the router no longer allows telephone functions. (Internet access is still usable, though.)

The problem can be fixed by firmware upgrade, or for that matter, if power recycling is done, the problem is shifted for another 2485 days into the future.

(Actually, I saw somewhere that NEC and NEC Technica was looking into the problems reported on different line of routers when they learned of the potential of similar problems. And when they checked the firmware of other products, they found the newly reported problems. I am not sure where I read it. I can't locate it any more. Maybe I read it in the letter which was sent by an ISP to notify the problem urging me to update my firmware of the said affected router.)

The cause of the bug?

The various reports I read didn't mention what are the real cause of the "software" problem, but I guessed that it must be related to the use of 32 bit integer for counting inside the firmware.

To wit, the problem interval in seconds is 214704000 [seconds] (= 2485 [days] * 24 [hours/day] * 3600 [s/h]).

One day shorter T' is 214617600 [seconds] = 2484 [days].

Also, 2 ** 32 = 4294967296
      2 ** 31 = 2147483648

We can see the following holds:

     (T' * 10) < 2**31 < (T * 10) < 2 ** 32

My conclusion:

A certain integer counter is incremented at 1/10 sec interval within the software using 32 bits data starting from 0 after power-up.

Internally, the firmware code regards this data as "signed" and suddenly somewhere between 2484th and 2485th day, this counter becomes "NEGATIVE" and wreaking havoc within the code, and rendering phone function useless.

Observation:

When I was checking web pages to write this submission, I noticed a bug report that different routers used for IP phones using optical fiber had a similar problem after 249 days. This was found last summer. Obviously 294 days is much shorter than 2485 days and some people suffered from the bug last year. In this case, I think the counter is incremented every 1/100 second. Maybe this discovery led to the massive review of the router firmware.

I noticed that similar problems concerning integer size and its signedness have occurred when * file size has begun exceeding 2GB limit (again 31/32 bit boundary),  * address space has been extended to 64 bits from 32 bits.

I noticed the first file size problems starting around the time Solaris and other Posix-based systems began offering large file size systems. Also, to this day, unmaintained shareware on windows often have problems when we try to handle a large file (2GB) like ISO image on windows. The symptoms are many. But one symptom that suggests the use of signed integer to check for the remaining file space is messages that say I don't have enough space although I have more than enough (actually larger than 2GB of free space.) I have checked that in many cases, if I create a very large dummy file to shrink the remaining free file space to less than 2GB, then these unmaintained programs proceeded without a hitch or ran into other size-related problems later.

I noticed the second address space problems when linux was ported to x86 architecture with 64bit address space: many device drivers as well as applications began failing. Solaris for x86 also saw many third party drivers facing similar problems when 64 bit address space was supported on x86 architecture. (Solaris for Sparc supported 64 bits address space for many years and I don't remember particular problems.)

We can now add the use of timers/counters to the causes that may trigger careless errors in applications. We have already seen there have been cases where a counter goes over the allocated bits and repeats again from 0, thus causing some software problems in the past: I think the early version of Windows NT had a problem of requiring reboot every 49 days or so for certain applications. (Counters incremented every 1/50 sec?)

As more consumer devices (as well as industry machines) are equipped with 32 bit CPUs, and more programmers who are accustomed to the luxury of 32 bit CPU programming under non-embedded OS have begun to develop software for embedded devices, we may see similar problems in the embedded system space more often. We have seen many already, but I am afraid that this trend will continue.

BTW, routers are complex device and some have linux inside literally. I am surprised somewhat recently to see GNU General Public License repeated verbatim in print inside the manual of my Toshiba Hard Disk recorder. Obviously, certain code used inside is based on GPL'ed code. When I think about the growing pains that drivers and OS itself had to go through when larger file sizes and address space extension were introduced, I have an uncomfortable feeling to trust the complex operations on such products unless software patches are readily available. But how are we supposed to "patch" hard disk recorder software? If power cord is accidentally removed during patching, what happens?! Does hard disk recorder store the "patch" program in a separate place, preferably a ROM or something, so that glitches during patching can not corrupt such software?

Embedded system programming requires certain different mindset, but I am afraid that not many programmers are trained to develop such mindset in the educational system and even in the industry in general. I feel this way because the problems with NT don't seem to have been learned by the would-be developers today.

I am reporting the problem here today so that at least someone can point out that such a problem is a public knowledge for a long time when a similar problem happens again in the future.

Thursday, August 28, 2008

TESTERS PASSION

CLICK HERE

Sunday, August 10, 2008