Question

Given the following two related tables, write the SQL SELECT statement to solve the following queries...

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

Homework Answers

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

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

Know the answer?
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for?
Ask your own homework help question
Similar Questions
Write the following questions as queries in SQL. Use only the operators discussed in class (in...
Write the following questions as queries in SQL. Use only the operators discussed in class (in particular, no outer joins or windows). Type your answers. Before starting, make sure you understand the schema of the database. If you are in doubt about it, please ask the instructor. Assume a database with schema ACTOR(name,age,address,nationality) MOVIE(title,year,genre,budget,director-name,studio) APPEARS(name,title,salary) 1. Find the title, director and studio of the most expensive movie of 2010 (note: there can be ties!). 2. Find the title, director and...
Using the Company database in Oracle, construct SQL queries for the following (note: I will make...
Using the Company database in Oracle, construct SQL queries for the following (note: I will make the files to create and populate the Company database available on Isadore shortly): List the last name and address of managers who have a dependent with the same first name as themselves. Retrieve the names of all employees who are directly supervised by ‘Franklin Wong’. Retrieve the names of employees in the Research department who work more than 20 hours per week on the...
Specify the following queries in SQL on the COMPANY database. Show the result of each query....
Specify the following queries in SQL on the COMPANY database. Show the result of each query. Retrieve the names of all employees in department 5 who work more than 10 hours per week on the ‘ProductX’ project. List the names of all employees who have a dependent with the same first name as themselves. Find the names of all employees who are directly supervised by ‘Franklin Wong’. Specify the following updates using the SQL update commands. Show the state of...
1. Consider the following tables in a relational database. Provide the appropriate "SELECT" SQL statement necessary...
1. Consider the following tables in a relational database. Provide the appropriate "SELECT" SQL statement necessary to answer the queries that follow. Primary keys are underlined and foreign key fields have an asterisk at the end of the field. CUSTOMERS (CUST-NO, C-NAME, C-ADDRESS, BALANCE) SALESPERSONS (SP-NO, S-NAME, DATE-EMPLOYED, SALARY) SALES (INVOICE-NO, DATE, CUST-NO*, SP-NO*) a) List the salesperson name and salary for all sales to customers whose balance outstanding is greater than 20000. b) List the names and addresses of...
Perform SQL queries in the given data structure. write an SQL query that gives the number...
Perform SQL queries in the given data structure. write an SQL query that gives the number of courses taken for every student in the student table. For each instructor, show the instructor name and the number of sections that have been taught by that instructor. You do not need to include instructors who have never taught a section. List in order of decreasing the number of sections taught. Give the number of semester/year combinations in which sections have been offered....
Write a script to create the following tables with attributes as specified(SQL) Customer table with Customer’s...
Write a script to create the following tables with attributes as specified(SQL) Customer table with Customer’s id, name, address, city as varchar, customer’s date of birth as date type and zip code number where the customers id is the primary key in the table, name and date of birth are mandatory. Id has a 10-character limit, name, address and city have a 50-character limit, zip has a 5-character limit Product Table with Product id, description and finish as varchar, price...
***This is a complete question. Do not tag this as incomplete. Write SQL queries to the...
***This is a complete question. Do not tag this as incomplete. Write SQL queries to the below Relational Database. • Regarding the SQL queries: • Do not use SELECT * in any query. • Do not use any SQL features (including Nullif) that were not covered in this course. • Do not use subqueries IF joins can be used to answer a question. However, some questions may require the use of subqueries. The Movie Database Notes: TheaterNum, MovieNum, and ActorNum...
Lab 5 Queries with Multiple Tables In this lab, we do queries more than one table....
Lab 5 Queries with Multiple Tables In this lab, we do queries more than one table. SQL provides two different techniques for querying data from multiple tables: • The SQL subquery • The SQL join As you will learn, although both work with multiple tables, they are used for slightly different purposes. We used WMCRM database which is what we created in Lab 4. Here is the summary of the database schema (where schema is used in its meaning of...
Given the following relations about students borrow books from a university library. Student (StudentID: integer, Name:...
Given the following relations about students borrow books from a university library. Student (StudentID: integer, Name: string, EnrollDate: date) Book(Title: string, Author: string, Quantity: integer) BorrowRecord(StudentID: integer, BookTitle: string, BookAuthor: string, BorrowDate: date, DueDate: date, Returned: integer) Note that there are no duplicate records in the three relations, and there can be duplicates of book titles. For Returned, 0 means not returned and 1 indicates the books have been returned. Write the following queries in SQL. (a) Find the StudentIDs...
Microsoft SQL Server Management Studio 1. Write SQL statements to insert, update, and delete a record...
Microsoft SQL Server Management Studio 1. Write SQL statements to insert, update, and delete a record in each of the Clients, Assist, Task, Outreach, and Specialist tables. Finish by inserting records into the tables (sample data follows). 5 points 2. Write Select statements to organize data from different tables. 4 points each Select all clients who have had an assist; include client name, assist date and type. List all assists including the name of the Specialist. List client names having...
ADVERTISEMENT
Need Online Homework Help?

Get Answers For Free
Most questions answered within 1 hours.

Ask a Question
ADVERTISEMENT