This discussion is archived
1 Reply Latest reply: May 16, 2013 10:49 AM by Frank Kulash RSS

No Rows Selected ! [although they are selected!]

1009300 Newbie
Currently Being Moderated
Hi,

I want to know how to select rows whilst putting in an execution command at the end where all my tables are created and data are inserted following the relations with tables, except ... when I try to enter a certain command it gives me "no rows selected" option does anyone know the solution for that cause I basically am selecting the rows but it for some reason doesn't actually SELECT !

this is my entire sql code:

ALTER TABLE PLAYERS DROP FOREIGN KEY FK_Team_ID;
ALTER TABLE WINNERSTEAM DROP FOREIGN KEY FK_TeamNo1_ID;
ALTER TABLE MATCHES DROP CONSTRAINT FK_WinnersTeam_ID;
ALTER TABLE PERFORMANCES DROP CONSTRAINT FK_Match_ID;
ALTER TABLE PERFORMANCES DROP CONSTRAINT FK_Player_ID;
ALTER TABLE PLAYERS DROP CONSTRAINT FK_PlayerPosition_ID;
DROP TABLE PLAYERS CASCADE CONSTRAINT;
DROP TABLE WINNERSTEAM CASCADE CONSTRAINT;
DROP TABLE PERFORMANCES CASCADE CONSTRAINT;
DROP TABLE MATCHES CASCADE CONSTRAINT;
DROP TABLE TEAMS CASCADE CONSTRAINT;
DROP TABLE PLAYERPOSITION CASCADE CONSTRAINT;
DROP TABLE CLUBS CASCADE CONSTRAINT;
DROP TABLE POTM CASCADE CONSTRAINT;
DROP TABLE SEASONSTATS CASCADE CONSTRAINT;
DROP TABLE MANAGERS CASCADE CONSTRAINT;
DROP TABLE COACHES CASCADE CONSTRAINT;
DROP TABLE CLUBOWNERS CASCADE CONSTRAINT;

CREATE TABLE TEAMS(
TeamNo1_ID NUMBER(3) NOT NULL,
TName VARCHAR(50),
Address VARCHAR(50),
NoOfWins NUMBER(3),
CONSTRAINT PK_Team_ID PRIMARY KEY (TeamNo1_ID));
DESC TEAMS;

CREATE TABLE PLAYERPOSITION(
PlayerPosition_ID NUMBER(3),
PositionName CHAR(20),
CONSTRAINT PK_PPosition_ID PRIMARY KEY (PlayerPosition_ID));
DESC POSITION;

CREATE TABLE PLAYERS(
PlayerNo1_ID NUMBER(3) NOT NULL,
Team_ID NUMBER(3) NOT NULL,
PPosition_ID NUMBER(3) NOT NULL,
FName CHAR(50),
LName CHAR(50),
Address VARCHAR (50),
Postcode VARCHAR (50),
City VARCHAR(50),
Country VARCHAR(50),
Height VARCHAR(20),
Weight VARCHAR(20),
DOB DATE,
Education VARCHAR(50),
Debut VARCHAR(50),
Experience VARCHAR(50),
InterM VARCHAR(20),
NatM VARCHAR(20),
CONSTRAINT PK_PPlayer_ID PRIMARY KEY (PlayerNo1_ID));
DESC PLAYERS;

CREATE TABLE WINNERSTEAM(
WinnersTeam_ID NUMBER(3),
TeamNo2_ID NUMBER(3),
CONSTRAINT PK_WinnersTeam_ID PRIMARY KEY (WinnersTeam_ID));
DESC WINNERSTEAM;

CREATE TABLE MATCHES(
Match_ID NUMBER(3) NOT NULL,
WinnersTeam_ID NUMBER(3),
Season VARCHAR(20),
HomeTeamScore NUMBER(3),
AwayTeamScore NUMBER(3),
NoOfFouls NUMBER(3),
MDate VARCHAR(20),
MType VARCHAR(100),
MLocation VARCHAR(100),
CONSTRAINT PK_Match_ID PRIMARY KEY (Match_ID));
DESC MATCHES;

CREATE TABLE PERFORMANCES(
PPlayer_ID NUMBER(3) NOT NULL,
Match_ID NUMBER(3) NOT NULL,
TwoPointAttempts VARCHAR(50),
TwoPointFieldGoalsMade VARCHAR(50),
ThreePointAttempts VARCHAR(50),
ThreePointFieldGoalsMade VARCHAR(50),
FreeThrowAttempts VARCHAR(50),
FreeThrowsMade VARCHAR(50),
Rebounds VARCHAR(50));
DESC PERFORMANCES;


CREATE TABLE POTM(
Potm_ID NUMBER(3) NOT NULL,
Player_ID NUMBER(3) NOT NULL,
FirstName VARCHAR(20),
LastName VARCHAR(20),
CONSTRAINT PK_Potm_ID PRIMARY KEY (Potm_ID));
DESC POTM;

CREATE TABLE CLUBS(
Club_ID NUMBER(3) NOT NULL,
ClubAddr_ID NUMBER(3) NOT NULL,
CName VARCHAR(50),
Address VARCHAR(50),
Postcode VARCHAR(50),
City VARCHAR(50),
Country VARCHAR(50),
CONSTRAINT PK_Club_ID PRIMARY KEY (Club_ID));
DESC CLUPS;

CREATE TABLE MANAGERS(
Man_ID NUMBER(3) NOT NULL,
Coa_ID NUMBER(3) NOT NULL,
CO_ID NUMBER(3) NOT NULL,
FirstName VARCHAR(50),
LastName VARCHAR(50),
TName VARCHAR(50),
CONSTRAINT PK_Man_ID PRIMARY KEY (Man_ID));
DESC MANAGERS;

CREATE TABLE COACHES(
Coa_ID NUMBER(3) NOT NULL,
Man_ID NUMBER(3) NOT NULL,
CO_ID NUMBER(3) NOT NULL,
FirstName VARCHAR(50),
LastName VARCHAR(50),
TName VARCHAR(50),
CONSTRAINT PK_Coa_ID PRIMARY KEY (Coa_ID));
DESC COACHES;

CREATE TABLE CLUBOWNERS(
CO_ID NUMBER(3) NOT NULL,
Man_ID NUMBER(3) NOT NULL,
Coa_ID NUMBER(3) NOT NULL,
Club_ID NUMBER(3) NOT NULL,
FirstName VARCHAR(50),
LastName VARCHAR(50),
CName VARCHAR(50),
CONSTRAINT PK_CO_ID PRIMARY KEY (CO_ID));
DESC CLUPOWNERS;


ALTER TABLE PLAYERS ADD CONSTRAINT FK_PPosition_ID FOREIGN KEY (PPosition_ID) REFERENCES PLAYERPOSITION(PlayerPosition_ID);
ALTER TABLE PLAYERS ADD CONSTRAINT FK_Team_ID FOREIGN KEY (Team_ID) REFERENCES TEAMS(TeamNo1_ID);
ALTER TABLE WINNERSTEAM ADD CONSTRAINT FK_TeamNo2_ID FOREIGN KEY (TeamNo2_ID) REFERENCES TEAMS(TeamNo1_ID);
ALTER TABLE MATCHES ADD CONSTRAINT FK_WinnersTeam_ID FOREIGN KEY (WinnersTeam_ID) REFERENCES WINNERSTEAM(WinnersTeam_ID);
ALTER TABLE PERFORMANCES ADD CONSTRAINT FK_Match_ID FOREIGN KEY (Match_ID) REFERENCES MATCHES(Match_ID);
ALTER TABLE PERFORMANCES ADD CONSTRAINT FK_PPlayer_ID FOREIGN KEY (PPlayer_ID) REFERENCES PLAYERS(PlayerNo1_ID);


INSERT INTO PLAYERS VALUES (001, 400, 150, 'Chris','Bosh','1146 Buck Drive','UT 84118','Kearns','USA','211cm','107kg', TO_DATE('1984-03-24','yyyy-mm-dd'),'Georgia Institute of Technology','2003','9','5','10');
INSERT INTO PLAYERS VALUES (002, 400, 151, 'Dwayne','Wade','3498 Mandan Road','MO 65201','Columbia','USA','193cm','100kg', TO_DATE('1982-01-17','yyyy-mm-dd'),'Marquette University','2003','9','13','15');
INSERT INTO PLAYERS VALUES (003, 400, 155, 'Juwan','Howard','444 Wayside Lane','CA 94547','Hercules','USA','206cm','118kg', TO_DATE('1973-02-07','yyyy-mm-dd'),'University of Michigan','1994','18','16','4');
INSERT INTO PLAYERS VALUES (004, 400, 152, 'Lebron','James','1101 Elkview Drive','FL 33131','Miami','USA','203cm','113kg', TO_DATE('1984-12-30','yyyy-mm-dd'),'St. Vincent-St. Mary HS (OH)','2003','9','18','8');
INSERT INTO PLAYERS VALUES (005, 400, 153, 'Rashard','Lewis','4317 Chandler Hollow Road','PA 15222','Pittsburgh','USA','208cm','104kg', TO_DATE('1979-08-08','yyyy-mm-dd'),'Alief Elsik HS (TX)','1998','14','20','17');
INSERT INTO PLAYERS VALUES (006, 400, 154, 'Chris','Andersen','4555 Prospect Valley Road','CA 90210','Beverly Hills','USA','208cm','104kg', TO_DATE('1978-07-07','yyyy-mm-dd'),'Blinn Junior College','2001','10','19','11');
INSERT INTO PLAYERS VALUES (007, 400, 150, 'Mike','Miller','2846 Capitol Avenue','IN 46204','Indianapolis','USA','203cm','99kg', TO_DATE('1980-02-19','yyyy-mm-dd'),'University of Florida','2000','12','13','22');
INSERT INTO PLAYERS VALUES (008, 400, 153, 'Mario','Chalmers','778 Poplar Street','IL 60090','Wheeling','USA','188cm','86kg', TO_DATE('1986-05-19','yyyy-mm-dd'),'University of Kansas','2008','4','16','24');
INSERT INTO PLAYERS VALUES (009, 400, 154, 'James','Jones','1844 Young Road','ID 83651','Nampa','USA','203cm','100kg', TO_DATE('1980-10-04','yyyy-mm-dd'),'University of Miami','2003','9','23','27');
INSERT INTO PLAYERS VALUES (010, 400, 151, 'Jarvis','Varnado','1502 Bolman Court','IL 62401','Effingham','USA','206cm','104kg', TO_DATE('1988/03/01','yyyy-mm-dd'),'University of Michigan','2010','1','26','28');
INSERT INTO PLAYERS VALUES (011, 400, 150, 'Norris','Cole','3793 Buckhannan Avenue','NY 13206','Syracuse','USA','188cm','79kg', TO_DATE('1988/10/30','yyyy-mm-dd'),'Cleveland State University','2001','11','17','19');
INSERT INTO PLAYERS VALUES (012, 400, 154, 'Shane','Battier','1819 Catherine Drive','ND 58601','Dickinson','USA','203cm','102kg', TO_DATE('1978/09/09','yyyy-mm-dd'),'Duke University','2001','11','26','30');
INSERT INTO PLAYERS VALUES (013, 400, 155, 'Ray','Allen','2895 Saint Marys Avenue','NY 13166','Weedsport','USA','196cm','93kg', TO_DATE('1925/07/20','yyyy-mm-dd'),'University of Connecticut','1996','16','29','34');
INSERT INTO PLAYERS VALUES (014, 400, 150, 'Udonis','Haslem','9, rue du Président Roosevelt','77176','SAVIGNY-LE-TEMPLE','FRANCE','203cm','107kg', TO_DATE('1980/06/09','yyyy-mm-dd'),'Florida','2003','9','28','35');
INSERT INTO PLAYERS VALUES (015, 400, 151, 'Joel','Anthony','Carretera del Muelle, 95','12232','Torrechiva','SPAIN','206cm','111kg', TO_DATE('1982/08/09','yyyy-mm-dd'),'UNLV','2007','5','39','45');

INSERT INTO PLAYERS VALUES (016, 500, 152, 'Andrew','Goudelock','Piazza San Carlo, 69','13030','Collobiano VC','ITALY','191cm','91kg', TO_DATE('1988/12/07','yyyy-mm-dd'),'College of Charleston','2011','1','16','48');
INSERT INTO PLAYERS VALUES (017, 500, 153, 'Darius','Morris','Piazza Trieste e Trento, 134','12030','Kearns','USA','193cm','86kg', TO_DATE('1991/01/03','yyyy-mm-dd'),'University of Michigan','2011','1','27','35');
INSERT INTO PLAYERS VALUES (018, 500, 154, 'Devin','Ebanks','48, rue Nationale','75007','Paris','FRANCE','206cm','98kg', TO_DATE('1989/10/28','yyyy-mm-dd'),'West Virginia University','2010','2','32','35');
INSERT INTO PLAYERS VALUES (019, 500, 155, 'Antawn','Jamison','97, rue des Lacs','14200','HÉROUVILLE-SAINT-CLAIR','FRANCE','206cm','107kg', TO_DATE('1976/06/12','yyyy-mm-dd'),'University of North Carolina','1998','14','29','50');
INSERT INTO PLAYERS VALUES (020, 500, 153, 'Steve','Blake','C/ Eras, 41','28723','Pedrezuela','SPAIN','191cm','78kg', TO_DATE('1980/02/26','yyyy-mm-dd'),'University of Maryland','1996','9','51','42');
INSERT INTO PLAYERS VALUES (021, 500, 151, 'Earl','Clark','Fuente del Gallo, 64','14860','Doña Mencía','SPAIN','208cm','102kg', TO_DATE('1988/01/17','yyyy-mm-dd'),'University of Louisville','2009','3','21','13');
INSERT INTO PLAYERS VALUES (022, 500, 150, 'Steve','Nash','37 Green Avenue','CA 94107','San Francisco','USA','191cm','86kg', TO_DATE('1974/02/07','yyyy-mm-dd'),'Santa Clara University','1996','16','33','44');
INSERT INTO PLAYERS VALUES (023, 500, 153, 'Dwight','Howard','2296 Heavner Avenue','GA 30245','Lawrenceville','USA','211cm','120kg', TO_DATE('1985/12/08','yyyy-mm-dd'),'SW Atlanta Christian Academy (GA)','2004','8','52','9');
INSERT INTO PLAYERS VALUES (024, 500, 153, 'Pau','Gasol','3648 Pine Street',' PA 15017','Bridgeville','USA','213cm','118kg', TO_DATE('1980/07/06','yyyy-mm-dd'),'Barcelona, Spain','2001','11','24','29');
INSERT INTO PLAYERS VALUES (025, 500, 155, 'Jodie','Meeks','4606 Doctors Drive','CA 90245','El Segundo','USA','193cm','94kg', TO_DATE('1987/08/21','yyyy-mm-dd'),'University of Kentucky','2009','3','48','59');
INSERT INTO PLAYERS VALUES (026, 500, 154, 'Chris','Duhon','558 Hedge Street','NJ 08876','Somerville','USA','185cm','86kg', TO_DATE('1982/08/31','yyyy-mm-dd'),'Duke University','2004','8','55','14');
INSERT INTO PLAYERS VALUES (027, 500, 152, 'Kobe','Bryant','4016 Pointe Lane,','FL 33478','Florida','USA','201cm','100kg', TO_DATE('1978/08/23','yyyy-mm-dd'),'Lower Merion HS (PA)','1996','16','13','28');
INSERT INTO PLAYERS VALUES (028, 500, 150, 'Jordan','Hill','355 North Street','VA 22903','California','USA','208cm','107kg', TO_DATE('1987/07/27','yyyy-mm-dd'),'University of Arizona','2009','3','36','44');
INSERT INTO PLAYERS VALUES (029, 500, 151, 'Robert','Sacre','2390 Peaceful Lane','OH 44109','Cleveland','USA','213cm','118kg', TO_DATE('1989/06/06','yyyy-mm-dd'),'Gonzaga University','2012','0','39','49');
INSERT INTO PLAYERS VALUES (030, 500, 155, 'Jackson','Sare','713 Lake Floyd Circle','MD 21740','Ohio','USA','210cm','114kg', TO_DATE('1980/05/04','yyyy-mm-dd'),'Bordeaux, France','2012','2','58','64');

INSERT INTO WINNERSTEAM VALUES (010, 400);
INSERT INTO WINNERSTEAM VALUES (020, 500);


INSERT INTO PERFORMANCES VALUES (001, 200, '1','3','3','1','3','1','2');
INSERT INTO PERFORMANCES VALUES (002, 200, '2','6','3','2','3','3','3');
INSERT INTO PERFORMANCES VALUES (003, 200, '4','8','3','1','4','2','4');
INSERT INTO PERFORMANCES VALUES (004, 200, '3','9','4','1','4','4','1');
INSERT INTO PERFORMANCES VALUES (005, 200, '4','4','4','2','5','2','5');
INSERT INTO PERFORMANCES VALUES (006, 200, '3','5','2','2','6','3','6');
INSERT INTO PERFORMANCES VALUES (007, 200, '2','3','5','1','3','1','3');
INSERT INTO PERFORMANCES VALUES (008, 200, '1','2','5','1','2','4','2');
INSERT INTO PERFORMANCES VALUES (009, 200, '3','5','5','1','2','2','4');
INSERT INTO PERFORMANCES VALUES (010, 200, '1','7','6','2','1','3','1');
INSERT INTO PERFORMANCES VALUES (011, 200, '1','9','6','1','1','4','6');
INSERT INTO PERFORMANCES VALUES (012, 200, '1','8','3','2','3','1','4');
INSERT INTO PERFORMANCES VALUES (013, 200, '1','1','4','1','4','1','2');
INSERT INTO PERFORMANCES VALUES (014, 200, '1','2','2','1','5','2','3');
INSERT INTO PERFORMANCES VALUES (015, 200, '1','4','6','1','6','3','6');
INSERT INTO PERFORMANCES VALUES (016, 300, '1','6','5','2','5','4','2');
INSERT INTO PERFORMANCES VALUES (017, 300, '2','7','7','2','4','5','3');
INSERT INTO PERFORMANCES VALUES (018, 300, '2','9','3','1','3','2','4');
INSERT INTO PERFORMANCES VALUES (019, 300, '1','4','2','1','2','4','5');
INSERT INTO PERFORMANCES VALUES (020, 300, '1','3','5','1','4','5','6');
INSERT INTO PERFORMANCES VALUES (021, 300, '1','1','6','2','5','3','1');
INSERT INTO PERFORMANCES VALUES (022, 300, '1','2','7','2','6','1','2');
INSERT INTO PERFORMANCES VALUES (023, 300, '1','4','3','1','3','5','6');
INSERT INTO PERFORMANCES VALUES (024, 300, '2','6','4','1','2','2','4');
INSERT INTO PERFORMANCES VALUES (025, 300, '4','7','6','1','5','3','4');
INSERT INTO PERFORMANCES VALUES (026, 300, '1','8','3','2','4','4','2');
INSERT INTO PERFORMANCES VALUES (027, 300, '6','4','2','1','1','5','5');
INSERT INTO PERFORMANCES VALUES (028, 300, '7','3','3','2','2','3','5');
INSERT INTO PERFORMANCES VALUES (029, 300, '3','1','4','1','3','2','1');
INSERT INTO PERFORMANCES VALUES (030, 300, '4','9','5','1','6','1','6');

INSERT INTO MATCHES VALUES (200, 010, 'SUMMER','125','160','5','05/12/12','NATIONAL','Madison Square Garden');
INSERT INTO MATCHES VALUES (300, 020, 'SPRING','155','120','10','20/03/11','INTERNATIONAL','Palacio de los Deportes de la Comunidad');

INSERT INTO TEAMS VALUES (400, 'Miami Heat','463 Neville Street','60');
INSERT INTO TEAMS VALUES (500, 'L.A. Lakers','1544 Adams Avenue','25');

INSERT INTO PLAYERPOSITION VALUES (150, 'Forward');
INSERT INTO PLAYERPOSITION VALUES (151, 'Center');
INSERT INTO PLAYERPOSITION VALUES (152, 'Guard');
INSERT INTO PLAYERPOSITION VALUES (153, 'Forward-Center');
INSERT INTO PLAYERPOSITION VALUES (154, 'Center-Forward');
INSERT INTO PLAYERPOSITION VALUES (155, 'Guard-Forward');

INSERT INTO CLUBS VALUES (550, 200, 'Rade','2982 Chipmunk Lane','ME 04101','New York','USA');
INSERT INTO CLUBS VALUES (650, 300, 'Waril','1 Ronda de Azkue','BO 48902','Alava','SPAIN');

INSERT INTO POTM VALUES (160, 027, 'Kobe','Bryant');
INSERT INTO POTM VALUES (161, 004, 'Lebron','James');
INSERT INTO POTM VALUES (162, 016, 'Andrew','Goudelock');
INSERT INTO POTM VALUES (163, 005, 'Rashard','Lewis');
INSERT INTO POTM VALUES (164, 026, 'Chris','Duhon');

INSERT INTO MANAGERS VALUES (800, 810, 820, 'Pat','Riley','Miami Heat');
INSERT INTO MANAGERS VALUES (801, 811, 821, 'Mitch','Kupchak','L.A. Lakers');

INSERT INTO COACHES VALUES (810, 800, 820, 'Erik','Spoelstra','Miami Heat');
INSERT INTO COACHES VALUES (811, 801, 821, 'Mike','Antoni','L.A. Lakers');

INSERT INTO CLUBOWNERS VALUES (820, 800, 810, 550, 'Mike','Woodson','Rade');
INSERT INTO CLUBOWNERS VALUES (821, 801, 811, 650, 'Jacque','Vaughn','Waril');


(from this part on I have errors basically I want to run these queries but for some reason I don't know for some what to input take the first query as an e.g. and also the rest tell me that "no rows are selected" !) Can someone please tell me a possible solution for this !


SELECT * FROM PLAYERS WHERE PlayerPosition_ID = &PlayerPosition;
SELECT MAX (NoOfWins) FROM TEAMS;
SELECT Match_ID,MDate,MLocation FROM MATCHES WHERE Match_ID IN (SELECT Match_ID FROM MATCHES HAVING SUM(NoOfFouls) >15 GROUP BY Match_ID);
SELECT MAX (Experience) FROM PLAYERS;
SELECT * FROM (SELECT FName, COUNT(InterM, NatM) FROM PLAYERS GROUP BY PlayerNo1_ID ORDER BY COUNT(InterM, NatM) DESC) WHERE rownum = 1



PS: I also want to know if anybody knows how I can do these points in general:



A1.     For any given a player name, list the positions that can be played. (input parameter i.e. payer name, should be inputted to run time from the SQL prompt).

A2.     Team with highest number of winning games.

A3.     List match details (ID, date, location) of all matches with more than 15 fouls.

A4.     Who is most experienced player ?

A5.     List the names of all players for any specific match (i.e. from both teams).




Thanks,

Kind Regards,

Doga,
  • 1. Re: No Rows Selected ! [although they are selected!]
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Doga,

    Welcome to the forum!
    1006297 wrote:
    Hi,

    I want to know how to select rows whilst putting in an execution command at the end where all my tables are created and data are inserted following the relations with tables, except ... when I try to enter a certain command it gives me "no rows selected" option does anyone know the solution for that cause I basically am selecting the rows but it for some reason doesn't actually SELECT !
    Sometimes "no rows selected" is the correct result. For example, if you suspect that somebody accidentally entered a negative number in the teams.TeamNo1_ID column, you might say
    SELECT  TeamNo1_ID
    FROM    teams
    WHERE   TeamNo1_ID  < 0;
    and you'd be happy to see "no rows selected".
    this is my entire sql code:
    ...
    CREATE TABLE CLUBS(
    ...
    DESC CLUPS;
    ...
    CREATE TABLE CLUBOWNERS(
    ...
    DESC CLUPOWNERS;
    Be careful! You mis-spelled the table names. (This has nothing to do with "no rows selected", of course.)

    Thanks for including the CREATE TABLE and INSERT statements; that's very helpful!
    You should always post the results you want to get from the given sample data, too.
    (from this part on I have errors basically I want to run these queries but for some reason I don't know for some what to input take the first query as an e.g. and also the rest tell me that "no rows are selected" !) Can someone please tell me a possible solution for this !


    SELECT * FROM PLAYERS WHERE PlayerPosition_ID = &PlayerPosition;
    In the players table, there's no column called PlayerPosition_ID. Did you mean PPosition_ID?
    &PlayerPosition is a SQL*Plus Substitution Variable . When you use a substitution variable in your code, and you don't assign a value to it first, then SQL*Plus will say "Enter value for x:" (where x is the name of the variable), and wait for you to type in a vlue that it will use in place of &x. If you enter a number that exists in the table, such as 150, you'll get the results from:
    SELECT * FROM PLAYERS WHERE PPosition_ID = 150;
    SQL*Plus substitutes the value you type (such as 150) for the variable (&PlayerPosition) before sending the code to the database to be run.
    If you enter a value that does not exist in that column, such as 9876, then you'll get "no rows selected", which is the correct answer to that query.
    SELECT MAX (NoOfWins) FROM TEAMS;
    I get 60 when I run the query above. Even if there were no rows in the teams table, this query would never produce "no rows selected". Are you sure that's the result you're getting from this query?
    SELECT Match_ID,MDate,MLocation FROM MATCHES WHERE Match_ID IN (SELECT Match_ID FROM MATCHES HAVING SUM(NoOfFouls) >15 GROUP BY Match_ID);
    "No rows selected" is the correct answer for the query above. That doesn't mean that there is anything wrong wioth the tables; you simply asked to see rows that met a certain condition, and no rows happened to meet it. You asked to see some information about matches where more than 15 fouls occurred, but in the sample data, the highest number of fouls in any match happens to be 10, so there really are no rows with more than 15.
    SELECT MAX (Experience) FROM PLAYERS;
    I get 9 when I run the query above. Again, whenever you use an aggregate function (such as MAX, above) and no GROUP BY clause, the query will always produce 1 row of output, regardless of what's in the table. If you're getting "no rows selected", then I don't think you're running the query above.
    SELECT * FROM (SELECT FName, COUNT(InterM, NatM) FROM PLAYERS GROUP BY PlayerNo1_ID ORDER BY COUNT(InterM, NatM) DESC) WHERE rownum = 1
    I get "ORA-00909: invalid number of arguments" when I run the query above. The COUNT function takes exactly 1 argument, but you're calling it with 2 arguments (InterM and NatM). Also, you can include FName in the SELECT clause unless it's in the GROUP BY clause, too. The following query runs:
    SELECT  * 
    FROM      (
             SELECT    PlayerNo1_ID
             ,           FName
             ,            COUNT (InterM)     AS InterM_cnt
             ,           COUNT (NatM)     AS NatM_cnt
             FROM      PLAYERS 
             GROUP BY  PlayerNo1_ID 
             ,           FName
             ORDER BY  COUNT (InterM)     DESC
             ,            COUNT (NatM)      DESC
         ) 
    WHERE      rownum = 1
    ;
    But it might not do what you want. (I don't know what you want, so I can't tell.)

    PS: I also want to know if anybody knows how I can do these points in general:
    It looks like these 5 separate questions, which have nothing in common except that they use some of the same tables (and perhaps were all on the same homework assignment). You should open 5 separate threads, one for each separate question. Post the relevant CREATE TABLE and INSERT statements, along with the desired results, in each thread.
    All of these are SQL questions, not SQL*Plus, so you should post them in the <a href="http://forums.oracle.com/forums/forum.jspa?forumID=75>SQL and PL/SQL Forum</a>. 
    If you're not sure if a question belongs in this forum or in the SQL and PL/SQL Forum, post it in the SQL and PL/SQL Forum.  (Question A1 actually does have something to do with PL/SQL, but you probably want help with the SQL aspects of that question, too.)

    In each case, show what you've already tried, and explain why you tried that query.  It will help people post better replies, and you'll learn much more.
    Always say which version of Oracle you're using, e.g. 11.2.0.2.0.

    See the FAQ from the SQL and PL/SQL Forum: {message:id=9360002                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points