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.
SQL
SQL stands for Structured Query Language, is a form of communication that uses queries structured in a specific format to store, manage & retrieve data from a relational database.
Queries
A query is a type of computer programming language that is used to retrieve data from a database. Databases are useful in a variety of ways. They enable the retrieval of records or parts of records, as well as the performance of various calculations prior to displaying the results. A search query is one type of query that many people perform several times per day. A search query is executed every time you use a search engine to find something. When you press the Enter key, the keywords are sent to the search engine, where they are processed by an algorithm that retrieves related results from the search index. Your query's results are displayed on a search engine results page, or SER.
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
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)](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F2ec814c7-90f3-4435-9bb8-fcbab3f733ac%2F27f70a30-f9a8-4fea-90df-155dc9959396%2Furewa1q_processed.png&w=3840&q=75)
![[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](/v2/_next/image?url=https%3A%2F%2Fcontent.bartleby.com%2Fqna-images%2Fquestion%2F2ec814c7-90f3-4435-9bb8-fcbab3f733ac%2F27f70a30-f9a8-4fea-90df-155dc9959396%2Ffzeh1e_processed.png&w=3840&q=75)
Step by step
Solved in 4 steps
