Answered: Please write a view to list the project…

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Concept explainers

Question

Please write a view to list the project name, staff name, and the staff’s
branch city for the staff who are working on the same project more than one time. You should use
Project, Staff2, Branch, and Working tables in the dreamhome database. The output column header
should be Project_name, Staff_name, Branch_city. The staff_name should combine fname and lname
with a space in between.

[MariaDB [dreamhome]> Select * from Project;
+‒‒‒‒‒‒
-+
| pcode | name
| city
|
+———-
| Union
| NYC
P01
P02
P03
| P04
P05
| Kean University
| Citi Bank
| British Museum
London
| Buckingham Palace | London
| Granite
+‒‒‒‒‒‒
5 rows in set (0.001 sec)
+‒‒‒‒‒‒
| P01
P01
| P01
P02
| P02
P02
| P02
| P03
| P03
| P03
| P03
P03
+▬▬▬▬▬
[MariaDB [dreamhome]> Select * from Working;
+▬▬▬▬▬▬
-+▬▬▬▬▬▬+
| pcode | staffNo | year |
·+▬▬▬▬▬▬+
| ABC1
ABC1
SG5
XY1
| SG5
SG5
| SL21
ABC1
| SG37
| SG14
| CC1
| CC1
1980 |
| 2010
2000
| 2014 |
2002
| 2018 |
2006
| 1995 |
1995 |
| 2003 |
| 1990 |
| 2019 |
-+▬▬▬▬▬▬+
| Aberdeen |
+
12 rows in set (0.001 sec)

Transcribed Image Text:[MariaDB [dreamhome]> select * from Staff2; | staffNo | fName | 1Name | position | New | Intern DBA ABC1 CC1 SA9 SG14 SG37 SG5 SL21 SL41 Super Mary David Ann Susan John Julie New Engineer User Howe Ford Beech Brand White Lee Tester street | XY1 9 rows in set (0.001 sec) [MariaDB [dreamhome]> select * from Branch; | branch No | city +- | B002 | London B003 | Glasgow B004 | Bristol | B005 | London B007 | Aberdeen 56 Cover Drive 163 Main Street 32 Manse Road 22 Deer Road 16 Argyll Street 5 rows in set (0.001 sec) sex | M | M F Assistant | Supervisor | M | Assistant | F Manager | F IM | Assistant | F | Manager | Assistant | M | DOB | 2000-01-01 | 9000.00 | B002 1995-01-01 90000.00 B005 9000.00 | B007 | 1970-02-19 | 1958-03-24 | 18000.00 | B003 1960-11-10 12000.00 B003 1940-06-03 24000.00 B003 30000.00 | B005 9000.00 | B005 8000.00 | B002 | 1945-10-01 | 1965-06-13 | 1990-01-01 | postcode | NW10 6EU G11 9QX | BS99 1NZ SW1 4EH AB2 3SU salary | branchNo | superno | | SL21 | SG14 SG5 SG5 | SG5 SL21 NULL SL21 | SL21

Expert Solution

Step 1: Define the Task

The scope of the question is defined as follows:

The question involves creating a view in the "dreamhome" database using SQL. The view's purpose is to list specific information, including the project name, staff name, and the staff's branch city, for a particular subset of staff members. The specific criteria for selecting these staff members are that they must be working on the same project more than once. The question also specifies that certain tables in the "dreamhome" database, including "Project," "Working," "Staff2," and "Branch," should be used to retrieve the required data.

The scope includes the creation of the view, which involves writing the SQL code for the view definition. Additionally, the view should be able to be queried to retrieve the desired information. The question does not cover the setup of the database itself or the insertion of data, as it assumes that the database already exists with relevant data.

Step by step

Solved in 4 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.

Similar questions