Share Your Query here.. SQL Exercises

[xXghostXx]

Please everyone share a query for all as SQL Exercises

1. nested SQL statements

[xXghostXx]

List each country name where the population is larger than 'Russia'.

SELECT name FROM bbc
  WHERE population >
     (SELECT population FROM bbc
      WHERE name='Russia')

Source http://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

[rahman86]

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.

[xXghostXx]

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');

[adam_k]

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.