Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    6

    Post Unanswered: need help in some queries-urgent

    Hi there,
    i've been working on writing some querries for a table that i've created and some data i inserted to achieve the queeries i need however i seem to have some prablems for an un known reason.
    i will show u the tables and the insert and the Queerys and what do i need to acheave by them, i would be really greatfull if you can sort me out on this

    the table
    Code:
    DROP TABLE Student CASCADE CONSTRAINTS;
    DROP TABLE Class CASCADE CONSTRAINTS;
    DROP TABLE Student_Class CASCADE CONSTRAINTS;
    						--  Tables droped
    
    CREATE TABLE 
    Student(
    student_ID    				NUMBER(10) PRIMARY KEY,
    student_Name				VARCHAR2(17) NOT NULL,
    nationality				VARCHAR2(10) NOT NULL,
    student_age				NUMBER(2) NOT NULL,
    );
    						-- Student table created
    
    CREATE TABLE 
    Class(
    class_ID    				NUMBER(10) PRIMARY KEY,
    class_Name				VARCHAR2(17) NOT NULL,
    );
    						-- Class table created
    
    CREATE TABLE 
    Student_Class(
    student_ID    				NUMBER(10) REFERENCES Student(student_ID),
    class_ID				NUMBER(10) REFERENCES Class(class_ID),
    student_grade				VARCHAR2(2)  NOT NULL,
    PRIMARY KEY(student_ID, class_ID));
    						-- Student_Class table created

    The Insert's
    Code:
    INSERT INTO Student VALUES (1, 'Fahad','Saudi', 23);
    INSERT INTO Student VALUES (22, 'Mansour','Saudi', 23);
    INSERT INTO Student VALUES (331,'Aqeel','Kashmery', 22);
    INSERT INTO Student VALUES (444,'Saad','Bahrainy', 24);
    INSERT INTO Student VALUES (5555,'Abdul','Egyption', 24);
    INSERT INTO Student VALUES (660, 'Nasser','Qatari', 22);
    INSERT INTO Student VALUES (707, 'Mohammed','Pakistani', 19);
    INSERT INTO Student VALUES (840, 'Mark','British', 19);
    INSERT INTO Student VALUES (930,'Naif','Brazilean', 22);
    INSERT INTO Student VALUES (66, 'Khalid','Qatari', 24);
    INSERT INTO Student VALUES (1001, 'Yasser','Pakistani', 22);
    INSERT INTO Student VALUES (84, 'Smith','British', 23);
    INSERT INTO Student VALUES (94,'John','Brazilean', 19);
    INSERT INTO Student VALUES (90,'james','Brazilean', 25);
    INSERT INTO Student VALUES (66, 'Khalid','Saudi', 20);
    INSERT INTO Student VALUES (70707, 'Simon','British', 20);
    INSERT INTO Student VALUES (84, 'Khoan','Spain', 21);
    INSERT INTO Student VALUES (93,'willeam','Amirecan', 23);
    						
    						--Data Inserted in the Student Table
    
    INSERT INTO Class VALUES (11, 'Business System and The Envirment');
    INSERT INTO Class VALUES (80, 'Database Deisign and Implemntation');
    INSERT INTO Class VALUES (90, 'System moduling');
    INSERT INTO Class VALUES (66, 'accounting');
    INSERT INTO Class VALUES (70, 'Business');
    INSERT INTO Class VALUES (34, 'Culture');
    INSERT INTO Class VALUES (45, 'Math');
    INSERT INTO Class VALUES (95, 'Computing and the Internet');
    INSERT INTO Class VALUES (38, 'System Methods');
    INSERT INTO Class VALUES (49, 'E-BAD');
    INSERT INTO Class VALUES (95, 'Finance');
    
    						--Data Inserted in the Class Table
    
    INSERT INTO Student_Class VALUES (840,11,A);
    INSERT INTO Student_Class VALUES (930,80,C);
    INSERT INTO Student_Class VALUES (66,90,B);
    INSERT INTO Student_Class VALUES (1001,66,A);
    INSERT INTO Student_Class VALUES (84,70,F);
    INSERT INTO Student_Class VALUES (94,34,C);
    INSERT INTO Student_Class VALUES (90,45,B);
    INSERT INTO Student_Class VALUES (66,95,B);
    INSERT INTO Student_Class VALUES (70707,38,C);
    INSERT INTO Student_Class VALUES (84,49,B);
    INSERT INTO Student_Class VALUES (93,95,A);
    
    						--Data Inserted in the Student_Class Table

    The querries i've done

    Code:
    Select std_id, std_name, std_nationlity from student;
    
    
    Select * from student s inner join class_st cs s.std_id = cs.std_id;
    
    Select distinct  std_nationlity from student order by std_nationality;
    
    Select std_id, std_name, std_nationality from student group by std_nationality having std_name = ‘mansour’;
    
    Select class_id, class_name from class where class_name = ‘database’ or class_id = 1;
    
    Select max(grad) from class_st where std_id > 5;  
    Select count(nationality) from student 
    
    Select std_name from student s where exists (select * from class_st cs where cs.std_id = s.std_id);
    
    Select class_name from class c, class_st cs where cs.class_id = c.class_id;
    
    Create view manour as select std_name from student where std_name = ‘mansour’;
    
    Select &std_id from &student;
    The querries i need to be acheaving in the order i have writen the querry in

    Query 1 - a query that will select 3 columns from a table.
    Query 2 - use of an appropriate join between 3 or more tables.
    Query 3 - a query using ORDER BY and the DISTINCT clauses.
    Query 4 - the necessary use of GROUP BY with HAVING.
    Query 5 - a query that will demonstrate use of Logical Operators (AND,OR,NOT)
    Query 6 - a query to demonstrate use of Mathematical (i.e MAX,SUM, etc…) and Aggregate Functions (i.e. Count etc)

    Query 7 - the necessary use of a correlated subquery. Note that a correlated subquery can be used instead of a join - this does not count as ‘necessary’.
    Query 8 - a query that uses a self join.
    Query 9 - create a view (this should be of a complexity that would justify using a view) and a query based on view created.

    • PLUS one query, of your choice from the full set listed above, that has been adapted to allow for user input (&), to make it more flexible.

    Thanks in advance

  2. #2
    Join Date
    Jun 2003
    Location
    Ottawa
    Posts
    105
    hmmm....looks suspiciously like homework.
    You're obsessed and distressed 'cuz you can't make any sense
    Of the ludicrous nonsense and incipient senescence
    That will deem your common sense useless

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    The queries are fairly simple, however this forum is not normally in the business of doing peoples homework. If you really get stuck on a particular query, write back and we might help. But don't expect us to do your entire assignment.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Sep 2003
    Posts
    6

    Thumbs up

    dstachon
    it is not exactly a homework but u can say it is a bit

    beilstwh
    am not expecting to get my entire homework to be done otherwise i would of asked u to create the tables and the insert , i've written them and i've written the queries though when i try to test it i get some error message that i don't know how to resolve it and that what i was expecting from u to help me out in and am still hoping that u will do

    many thanx for both of u for replaying and am looking forward for your support in helping me to sort that issue out

  5. #5
    Join Date
    Jun 2004
    Posts
    46
    i normally just dont reply much because sometimes i clutter up the other person with my suggestions, but this time i must agree with me fellow colleges it seems like homework.... the queries are very simple, perhaps you should look up the oracle press complete documentation it comes packed with similar problems that you are facing.... email me if you need the file...

  6. #6
    Join Date
    Sep 2003
    Posts
    6
    Aleman,
    i would be greatful if u can give me a link to download it.
    though i have written the queries and i would be really thankful if u can point which one of those queries are wrong

    Code:
    Select std_id, std_name, std_nationlity from student;
    
    
    Select * from student s inner join class_st cs s.std_id = cs.std_id;
    
    Select distinct  std_nationlity from student order by std_nationality;
    
    Select std_id, std_name, std_nationality from student group by std_nationality having std_name = ‘mansour’;
    
    Select class_id, class_name from class where class_name = ‘database’ or class_id = 1;
    
    Select max(grad) from class_st where std_id > 5;  
    Select count(nationality) from student 
    
    Select std_name from student s where exists (select * from class_st cs where cs.std_id = s.std_id);
    
    Select class_name from class c, class_st cs where cs.class_id = c.class_id;
    
    Create view manour as select std_name from student where std_name = ‘mansour’;
    
    Select &std_id from &student;

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    See here is why you are not going to get any responses on this site. Its not even a question of being homework. But everyone here volunteers their free time (or employer's paid time) to answer questions and help.

    But you need to be VERY specific on which queries are giving you an error, and the EXACT error code (ex: Ora-00942 - Table or View does not exist). You cannot expect people to run every query, and report back to you on the problem.

    YOU need to tell US what errors you are getting. It was definitely considerate of you to post the table definitions and data. But as the others pointed out, no one has that much free time to do this for you.

    I can tell you if you are interested, that you keep referencing a CLASS_ST table, but do not have the table definition or data for it. In the view query, you are selecting "where std_name = " but there is no column named std_name. You also do not have a class_name called 'database', so why are you trying to select one? Make sure you look at the case of the statements, as well as what values are contained in the table.

    For what its worth as well, ALWAYS enter VARCHAR2 values in uppercase. I know in class they tell you its ok to do whatever. But in the real world, you always insert values in uppercase.

    And use the format:
    Code:
    insert into table (col1, col2, col3) 
    values (value1, value2, value3)
    Then you can tell exactly which column you are inserting into and make sure you are inserting into the correct value.
    Last edited by ss659; 01-27-05 at 10:12.
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •