0

Please everyone share a query for all as SQL Exercises

  1. nested SQL statements

Edited by xXghostXx

3
Contributors
4
Replies
8
Views
4 Years
Discussion Span
Last Post by adam_k
0

Hi,

Here is some example. A bit explanation. It select all student from student table which the studentID is matching to the courseID in which the student take the subjectID = PHY

select *
from student
where studentID in
    (select studentID
    from course
    where courseID in
        (select courseID
        from [course Details]   
        where subjectID = PHY
        )
    )

Please correct me if I am wrong. I also new to this.

0

How to select name of person, persons_id(p_id), name of orders and order_id(o_id) for those persons who order an order ?

I am sure that select is incorrect

select p_Id,FirstName,LastName,OrderName,O_Id from persons,orders where p_id=o_id

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
FirstName varchar(255),
LastName varchar(255) NOT NULL,
Address varchar(255),
City varchar(255)
);

CREATE TABLE Orders
(
O_Id int NOT NULL,
P_Id int,
OrderName varchar(255),
OrderDate varchar (255),
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

insert into persons values ('1','sara','lala','street10','de');
insert into persons values ('2','toto','no','street10','nl');
insert into persons values ('3','momo','yes','street10','ch');
insert into persons values ('4','bobo','yea','street10','sy');
insert into persons values ('5','malk','tata','street10','sy');
insert into persons values ('6','yuo','zoo','street10','UK');


insert into Orders values ('1','2','Order1','29/5/200');
insert into Orders values ('2','1','Order2','7/6/2000');
insert into Orders values ('3','2','Order3','9/9/2001');
insert into Orders values ('4','1','Order4','22/3/2002');
0

It is indeed incorrect:

Select P_Id, FirstName, LastName, O)Id, OrderName from Persons 
inner join Orders 
on Persons.P_Id = Orders.P_Id

You don't need a where clause, as the join will eliminate all persons without records in Orders.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.