SQL classroom exercise

Here is one table and its related q&a. will be kept for future reference, or will be useful to beginners!

create table ex1
( id int(6),
name varchar(32),
surname varchar(32),
flathave varchar(32),
flatwant varchar(32));

insert into ex1 values(1, ‘pandian’, ‘muruga’, ‘pudukkottai’, ”);
insert into ex1 values(2, ‘pandian’, ‘muruga’, ‘chennai’, ”);
insert into ex1 values(3, ‘pandian’, ‘muruga’, ”, ‘coimbatore’);
insert into ex1 values(4, ‘amutha’, ‘radhakrishnan’, ”, ‘pudukkottai’);
insert into ex1 values(5, ‘Ram’, ‘narayan’, ”, ‘tirunelveli’);
insert into ex1 values(6, ‘kiran’, ‘vasudeva’, ”, ‘Palakkad’);
insert into ex1 values(7, ‘nanda’, ‘kumar’, ‘Mettur’, ”);
insert into ex1 values(8, ‘nirmal’, ‘raj’, ‘chidambaram’, ”);
insert into ex1 values(9, ‘nanda’, ‘kumar’, ‘coimbatore’, ”);

—————————————————————————————-

/*data*/
SELECT * FROM ex1 e;

/*who has a flat in pudukkottai and who wants to buy one?*/
select * from ex1 where flathave=’pudukkottai’ or flatwant=’pudukkottai’;

/*where does ‘pandian muruga’ own flats and where does he want to buy one?*/
select * from ex1 where name=’pandian’ and surname=’muruga’;

/*howmany entries are there and show splitups*/
select count(*) as total from ex1 where (flathave” and flathave is not null) or (flatwant” and flatwant is not null);
select count(*) as own from ex1 where flathave” and flathave is not null;
select count(*) as want from ex1 where flatwant” and flatwant is not null;

/*how many cliets do we have*/
select count(distinct name, surname) distinct_clients from ex1;

/*list the customers whose name starts with A*/
select name from ex1 where name like ‘A%’;

/*sort the table by name*/
select * from ex1 order by name;
select * from ex1 order by name desc;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s