Question

Given the following two related tables, write the SQL SELECT statement to solve the following queries about developers (Dev) and programs (Prog). The id column in Dev and Prog is the relation between the two tables.

Dev(id, name, dept, salary)

Prog(id, title, language, lines, size);

1. List name of Devs who have written more than 1000 line programs

2. List title of all programs written in C++ language

3. List name of all developers who have written C++ programs

Answer 1

Hi,

Please find below queries for your questions.

I have created table with dummy data to show output and working of queries, attaching script for that also after answers.

Let me know if you have any doubt/concerns on anything via Comments.

Hope this answer will helps you.

1 )

Select d.name from DEV d
inner join PROG p on p.id = d.id
where p.lines > 1000;

here we are using inner join to retrive matching records only by id from dev to prog table and adding condition that only lines greater than 1000 so other data will not come.

2)

select title from PROG where language = 'C++';

here we are only fetching title of prog who have the language C++ only.

3)

Select d.name from DEV d
inner join PROG p on p.id = d.id
where p.language = 'C++';

here we are using inner join to retrive matching records only by id from dev to prog table and adding condition that only who written code in language 'C++' only.

/***************Query tested on below schema and data********************************/

CREATE TABLE DEV(id int,name varchar(50),dept varchar(50),salary decimal);
CREATE TABLE PROG(id int,title varchar(50),language varchar(50),lines int,size int);

insert into DEV values(1,'ABC','PROG',10000);
insert into DEV values(2,'XYZ','IT',15000);
insert into DEV values(3,'BBC','PROG',8000);
insert into DEV values(4,'PQR','IT',12000);
insert into DEV values(5,'LMN','PROG',7000);

insert into PROG values(1,'TITLE1','C++',2150,5);
insert into PROG values(2,'TITLE2','JAVA',2800,8);
insert into PROG values(3,'TITLE3','C++',1850,3);
insert into PROG values(4,'TITLE4','JAVA',4500,11);
insert into PROG values(5,'TITLE5','C',5820,12);

/***********************************************/