Skip to main content

Engineering Computer Science EBK DATABASE SYSTEM CONCEPTS

a. Program Plan To write an SQL query for a given scenario. To select the employee ID, their name, and city from table employee given the condition that the employee works for the company First Bank Corporation. So clauses such as SELECT, WHERE, and JOIN are used in the query. The SQL query for the given scenario is: SELECT e.ID, e.person_name, e.city FROM employeee WHERE e.ID=(SELECT ID FROM works w WHERE company_name = “First Bank Corporation”) JOIN company c ON c.company_name=w.company_name; b. Program Plan To write an SQL query for a given scenario. To select the employee ID, their name, and city where the employee resides from table employee given the condition that the employee works for the company First Bank Corporation and getting salary greater than 10000. So clauses such as SELECT, WHERE, AND, and JOIN are used in the query. The SQL query for the given scenario is: SELECT e.ID, e.person_name, e.city FROM employeee WHEREe.ID=(SELECT ID FROM works w WHERE company_name = “First Bank Corporation” AND salary > 10000) JOIN company c ON c.company_name=w.company_name; c. Program Plan To write an SQL query for a given scenario. To select the employee ID from table employee given the condition that the employee works for other than the company First Bank Corporation. So clauses such as SELECT, WHERE, EXCEPT, and JOIN are used in the query. The SQL query for the given scenario is: SELECT e.ID FROM employee EXCEPT SELECT e.ID FROM employeee WHEREe.ID=(SELECT ID FROM works w WHERE company_name = “First Bank Corporation”) JOIN company c ON c.company_name=w.company_name; d. Program Plan To write an SQL query for a given scenario. To select the employee ID from table employee given the condition that the employee is getting salary greater than the maximum salary of an employee in company Small Bank Corporation. So clauses such as SELECT, WHERE, MAX, and JOIN are used in the query. The SQL query for the given scenario is: SELECT e.ID FROM employeee WHEREe.ID=(SELECT ID FROM works w WHERE salary > MAX( SELECT salary FROM works WHERE company_name = “Small Bank Corporation”)) JOIN company c ON c.company_name=w.company_name; e. Program Plan To write an SQL query for a given scenario. To select the name of the company from table company given the condition that the company is located in the city where Small Bank Corporation is located. So clauses such as SELECT, WHERE, and NOT are used in the query. The SQL query for the given scenario is: SELECT c.company_name FROM company c WHERE c.city=(SELECT city FROM company c WHERE company_name= “Small Bank Corporation”)AND NOT c.company_name = “Small Bank Corporation”) f. Program Plan To write an SQL query for a given scenario. To select the name of company and number of employees from table works given the condition that company has more number of employees. So, clauses such as SELECT, WHERE, COUNT, and MAX are used in a query. The SQL query for the given scenario is: SELECT company_name, COUNT( DISTINCT company_name ) AS "Number of employees" FROM works WHERE Number of employees = MAX (Number of employees) g. Program Plan To write an SQL query for a given scenario. To select the name of the company from Table Company given the condition that employees working getting salaries in average greater than average salaries of employees in company First Bank Corporation. So clauses such as SELECT, WHERE and AVERAGE are used in the query. The SQL query for the given scenario is: SELECT DISTINCT company_name, AVERAGE (salary) AS "Average of Salary" FROM works WHERE Average of Salary > (SELECT AVERAGE (salary)FROM works WHERE company_name = “First Bank Corporation”)

a. Program Plan To write an SQL query for a given scenario. To select the employee ID, their name, and city from table employee given the condition that the employee works for the company First Bank Corporation. So clauses such as SELECT, WHERE, and JOIN are used in the query. The SQL query for the given scenario is: SELECT e.ID, e.person_name, e.city FROM employeee WHERE e.ID=(SELECT ID FROM works w WHERE company_name = “First Bank Corporation”) JOIN company c ON c.company_name=w.company_name; b. Program Plan To write an SQL query for a given scenario. To select the employee ID, their name, and city where the employee resides from table employee given the condition that the employee works for the company First Bank Corporation and getting salary greater than 10000. So clauses such as SELECT, WHERE, AND, and JOIN are used in the query. The SQL query for the given scenario is: SELECT e.ID, e.person_name, e.city FROM employeee WHEREe.ID=(SELECT ID FROM works w WHERE company_name = “First Bank Corporation” AND salary > 10000) JOIN company c ON c.company_name=w.company_name; c. Program Plan To write an SQL query for a given scenario. To select the employee ID from table employee given the condition that the employee works for other than the company First Bank Corporation. So clauses such as SELECT, WHERE, EXCEPT, and JOIN are used in the query. The SQL query for the given scenario is: SELECT e.ID FROM employee EXCEPT SELECT e.ID FROM employeee WHEREe.ID=(SELECT ID FROM works w WHERE company_name = “First Bank Corporation”) JOIN company c ON c.company_name=w.company_name; d. Program Plan To write an SQL query for a given scenario. To select the employee ID from table employee given the condition that the employee is getting salary greater than the maximum salary of an employee in company Small Bank Corporation. So clauses such as SELECT, WHERE, MAX, and JOIN are used in the query. The SQL query for the given scenario is: SELECT e.ID FROM employeee WHEREe.ID=(SELECT ID FROM works w WHERE salary > MAX( SELECT salary FROM works WHERE company_name = “Small Bank Corporation”)) JOIN company c ON c.company_name=w.company_name; e. Program Plan To write an SQL query for a given scenario. To select the name of the company from table company given the condition that the company is located in the city where Small Bank Corporation is located. So clauses such as SELECT, WHERE, and NOT are used in the query. The SQL query for the given scenario is: SELECT c.company_name FROM company c WHERE c.city=(SELECT city FROM company c WHERE company_name= “Small Bank Corporation”)AND NOT c.company_name = “Small Bank Corporation”) f. Program Plan To write an SQL query for a given scenario. To select the name of company and number of employees from table works given the condition that company has more number of employees. So, clauses such as SELECT, WHERE, COUNT, and MAX are used in a query. The SQL query for the given scenario is: SELECT company_name, COUNT( DISTINCT company_name ) AS "Number of employees" FROM works WHERE Number of employees = MAX (Number of employees) g. Program Plan To write an SQL query for a given scenario. To select the name of the company from Table Company given the condition that employees working getting salaries in average greater than average salaries of employees in company First Bank Corporation. So clauses such as SELECT, WHERE and AVERAGE are used in the query. The SQL query for the given scenario is: SELECT DISTINCT company_name, AVERAGE (salary) AS "Average of Salary" FROM works WHERE Average of Salary > (SELECT AVERAGE (salary)FROM works WHERE company_name = “First Bank Corporation”)

EBK DATABASE SYSTEM CONCEPTS

EBK DATABASE SYSTEM CONCEPTS

7th Edition

ISBN: 9781260049268

Author: SILBERSCHATZ

Publisher: MCG COURSE

See similar textbooks

Expert Solution & Answer

Want to see the full answer?

Check out a sample textbook solution

Blurred answer

Chapter 3, Problem 8PE

Chapter 3, Problem 10PE

Students have asked these similar questions

Using R language

using r language

using r language

Chapter 3 Solutions

EBK DATABASE SYSTEM CONCEPTS

Ch. 3 - Prob. 1PE Ch. 3 - Prob. 2PE Ch. 3 - Prob. 3PE Ch. 3 - Suppose that we have a relation marks(ID, score)...Ch. 3 - Suppose that we have a relation marks(ID, score)...Ch. 3 - The SQL like operator is case sensitive (in most...Ch. 3 - Prob. 7PE Ch. 3 - Prob. 8PE Ch. 3 - Prob. 9PE Ch. 3 - Prob. 10PE

Knowledge Booster

Background pattern image

Similar questions

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning

Text book image

Database Systems: Design, Implementation, & Manag...

Computer Science

ISBN:9781285196145

Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:Cengage Learning

Text book image

Database Systems: Design, Implementation, & Manag...

Computer Science

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Cengage Learning

Text book image

Computer Science

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Course Technology Ptr

Text book image

Oracle 12c: SQL

Computer Science

ISBN:9781305251038

Author:Joan Casteel

Publisher:Cengage Learning

SEE MORE TEXTBOOKS