-WAQ to find the details of
employees whose jobs are CLERK, MANAGER AND SALESMAN
Sol: SELECT * FROM EMP WHERE
JOB=’CLERK’ OR JOB=’MANAGER’ OR JOB=’SALESMAN’
Sol: SELECT * FROM EMP WHERE JOB
IN (’CLERK’, ’MANAGER’, ’SALESMAN’)
-In Operator Determines whether a
specified value matches any value in the list.
-WAQ to find the details of all
employees except PRESIDENT AND MANAGER.
Sol: SELECT * FROM EMP WHERE JOB
!= ’MANAGER’ AND JOB != ’PRESIDENT’
Sol: SELECT * FROM EMP WHERE JOB
NOT IN (’MANAGER’, ’PRESIDENT’)
-WAQ to find the details of
employees who name starts with character S.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘S%’
-Like Operator determines whether
a specific character string matches a specified pattern. A pattern can include
regular characters and wildcard characters. During pattern matching, regular
characters must exactly match the characters specified in the character string.
However, wildcard characters can be matched with arbitrary fragments of the
character string. Using wildcard characters makes the LIKE operator more
flexible than using the = and != string comparison operators.
%
- it represents any string of zero or more characters.
-WAQ to find the details of
employees whose name contains M in it.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘%M%’
-WAQ to find the details of
employees whose name is SMITH, when the spelling of the name is not known
exactly as SMITH OR SMYTH.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘SM_TH’
OR
Sol: SELECT * FROM EMP WHERE
SOUNDEX(ENAME)=SOUNDEX(‘SMYTH’)
_(underscore)
- it represents any single character.
WAQ to find the details of
employees whose name starts with a characters between A to S.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘[A-S]%’
[ ] – it
represents any single character within the specified range ([a-f]) or set
([abcdef]).
WAQ to find the details of
employees whose name starts with any of the character “ABCDE”.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘[ABCDE]%’
WAQ to find the details of
employees whose name starts with a characters not between A to S.
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘[^A-S]%’
WAQ to find the details of
employees whose name starts with characters apart from “ABCDE”
Sol: SELECT * FROM EMP WHERE
ENAME LIKE ‘[^ABCDE]%’
OR
Sol: SELECT * FROM EMP WHERE
ENAME NOT LIKE ‘[ABCDE]%’
WAQ to find the details of
employees whose job is CLERK and earning 3000.
Sol: SELECT * FROM EMP WHERE
JOB=’CLERK’ AND SAL=3000
WAQ to find the details of
employees whose job is MANAGER as well as earning more than 3000.
Sol: SELECT * FROM EMP WHERE
JOB=’MANAGER’ OR SAL>3000
WAQ to find the details of
employees whose salary is not equal to 3000.
No comments:
Post a Comment