sql problem I cannot solve ORACLE RELATED?

Question

hi I had sql exam on oracle just there and I was wondering why did the following code not work ? I checked the names so the table names etc are correct ...

DESC ADUNPHY.SITE;
DESC ADUNPHY.EMPLOYEE;
DESC ADUNPHY.EQUIPMENT;
SELECT * FROM ADUNPHY.SITE;
SELECT * FROM ADUNPHY.EMPLOYEE;
SELECT * FROM ADUNPHY.EQUIPMENT;
SELECT ENAME FROM ADUNPHY.EMPLOYEE WHERE SIT='S01'order by ENAME;;
SELECT ENAME FROM ADUNPHY.EMPLOYEE WHERE SIT='S02'order by ENAME;;
SELECT EQ_ID AND EQ_DESCRIPTION FROM ADUNPHY.EQUIPMENT WHERE SER='E10' ORDER BY EQ_ID;
SELECT COUNT(SIT) FROM ADUNPHY.SITE WHERE LABEL='NUMBER_OF_SITES';
SELECT COUNT=(SIT_S) FROM ADUNPHY.SITE WHERE SITE_S='large' ;
SELECT COUNT=(SIT_S) FROM ADUNPHY.SITE WHERE SITE_S='MEDIUM' ;
SELECT COUNT=(SIT_S) FROM ADUNPHY.SITE WHERE SITE_S='SMALL' ;
SELECT COUNT(EQ_DESCRIPTION) FROM ADUNPHY.EQUIPMENT WHERE EQ_DESCRIPTION='%drill%';
SELECT EQ_ID AND EQ_DESCRIPTION FROM ADUNPHY.EQUIPMENT WHERE EQ_LAST_S IS NULL ;
SELECT EQ_ID AND EQ_DESCRIPTION FROM ADUNPHY.EQUIPMENT WHERE SER='EO9'AND RES='SO2' SORT BY EQ_ID ;

Answer 1

Hi Friend,



Kindly correct you code as below.



Remove one ; from following code

SELECT ENAME FROM ADUNPHY.EMPLOYEE WHERE SIT='S01'order by ENAME;;

SELECT ENAME FROM ADUNPHY.EMPLOYEE WHERE SIT='S02'order by ENAME;;



Remove = in following code. Put Count(SIT_S) instead of COUNT=(SIT_S)

SELECT COUNT=(SIT_S) FROM ADUNPHY.SITE WHERE SITE_S='large' ;

SELECT COUNT=(SIT_S) FROM ADUNPHY.SITE WHERE SITE_S='MEDIUM' ;

SELECT COUNT=(SIT_S) FROM ADUNPHY.SITE WHERE SITE_S='SMALL' ;



Use like operator in guessing column value

SELECT COUNT(EQ_DESCRIPTION) FROM ADUNPHY.EQUIPMENT WHERE EQ_DESCRIPTION='%drill%';

Rewrite as follows

SELECT COUNT(EQ_DESCRIPTION) FROM ADUNPHY.EQUIPMENT WHERE EQ_DESCRIPTION like '%drill%';



Put orderby clause instead of sort by in following code.

SELECT EQ_ID AND EQ_DESCRIPTION FROM ADUNPHY.EQUIPMENT WHERE SER='EO9'AND RES='SO2' SORT BY EQ_ID ;

Rewrite as follows

SELECT EQ_ID AND EQ_DESCRIPTION FROM ADUNPHY.EQUIPMENT WHERE SER='EO9'AND RES='SO2' ORDER BY EQ_ID ;



And execute again code it will be working.



All the best.



Thanks and regards,

Gitesh Trivedi

http://www.dbametrix.com/oracle-dba-tips.html

http://www.kendba.com

Answer 2

No wonder. I can spot plenty of errors / doubtful code:



1) SELECT ENAME FROM ADUNPHY.EMPLOYEE WHERE SIT='S01'order by ENAME;;

Why the two ";" ?



2) SELECT EQ_ID AND EQ_DESCRIPTION FROM ADUNPHY.EQUIPMENT WHERE SER='E10' ORDER BY EQ_ID; (multiple times)

If you want to select multiple columns, you do not use 'AND' but a comma separator.



3) SELECT COUNT(SIT) FROM ADUNPHY.SITE WHERE LABEL='NUMBER_OF_SITES';

If you want to count how many different SIT values exist, then you need to use COUNT (DISTINCT SIT).



4) SELECT COUNT=(SIT_S) FROM ADUNPHY.SITE WHERE SITE_S='large' ;

The COUNT=(SIT_S) syntax is not valid. If you want to find out the number of LARGE sites, then just say COUNT(*).



5) SELECT COUNT=(SIT_S) FROM ADUNPHY.SITE WHERE SITE_S='large' ;

You compare SITE_S with 'large' in lowercase, but in the two queries that follow you use capitals. Is this intended ? String comparisons are case sensitive.



6) SELECT COUNT(EQ_DESCRIPTION) FROM ADUNPHY.EQUIPMENT WHERE EQ_DESCRIPTION='%drill%';

You compare EQ_DESCRIPTION with '%drill%' which looks suspiciously like a LIKE pattern. Is this what you intended, i.e. find how many equipments contain the string 'drill' in their description ? And if so are you sure about the case ? String matches are case-sensitive for LIKE too. So EQ_DESCRIPTION like '%drill%' or EQ_DESCRIPTION like '%DRILL%' or maybe EQ_DESCRIPTION like '%Drill%' is what you want. This will return the rows that contain the string "drill". If you want to return the ones that contain the *word* drill (irrespective of case) then you need to use a text index and the CONTAINS operator.



Pretty much the only lines that contain no error are the DESCRIBEs at the beginning.

Answer 3

That confuses me.

DESC (short for DESCRIBE) will give you all the fields in the tables. Why do you need it?



I don't know your field names, so I don't know if SIT or SIT_S or SITE_S or EQ_LAST_S are actually field names or not.



One MAJOR flaw I see is the WHERE EQ_DESCRIPTION='%drill%'. Do NOT use the % wildcard on an equal statement. Use that only on a LIKE statement. And if you want to make it case-insensitive, then put the lower-function on it

WHERE LOWER(EQ_DESCRIPTION) LIKE '%drill%' or WHERE UPPER(EQ_DESCRIPTION) LIKE = '%DRILL%'



I don't know why you are selecting the count of a column. That is the same as selecting the COUNT(*) of everything. Are you trying to select the count of distinct values? If so, then you need to SELECT COUNT(DISTINCT EQ_DESCRIPTION) to get the count of the distinct values



SELECT COUNT=(SIT_S) is totally wrong. See the previous paragraph.



Your SITE_S='large' ; and SITE_S='MEDIUM' ; will only get the rows where the site_s is all in lowercase and the site_s is uppercase. So use the LOWER or UPPER functions I stated above.



I don't know what your instructor is teaching you, but you don't necessarily need the schema name of ADUNPHY in front of your table names if you are logged in as ADUNPHY. That is totally fine if you leave it there, but it is just FYI

Answer 4

Without knowing your schema or data, the only questionable things I saw were:



SIT='S01'order by ENAME;; - the double semicolon might not cause an error, but certainly isn't needed, however need a space before 'order'; same on following query

WHERE SITE_S='large' - syntactically OK, but maybe should be 'LARGE', based on following 2 queries

WHERE EQ_DESCRIPTION='%drill%'; - again, case might be an issue

EQ_ID AND EQ_DESCRIPTION - 'AND' incorrect here, should just be EQ_ID, EQ_DESCRIPTION