6.1 Retrive all columns all rows 6.2 Produce a list of salaries for all staff, s
ID: 3590467 • Letter: 6
Question
6.1 Retrive all columns all rows
6.2 Produce a list of salaries for all staff, showing only the staff number, first and last names and salary (from staff)
6.3 List property numbers of all properties that have been viewed (from viewing)
6.4 produce a list of monthly salaries for all staff, showing the staff number, first and last names, and the salary details
6.5 List all staff greater than 10,000
6.6 list the addresses of all branch offices in London or Glasgow
6.7 List all staff with a salary between 20000 and 30000
6.8 List all managers and supervisors from staff and include staffno, first and last name, and position
6.9 Find all owners with the string 'Glasgow" in their address
6.10 List the details of all viewings on property PG4 where a comment has not been supplied.
***BEFORE RUNNING THESE, THERE ARE SIX VALUES LISTED 'gail' you must add an 'm' after the g for results to be valid***
mysql
oracle
Sql Server
Download and install Oracle 11g express edition, Oracle SQL Developer and MySQL software. Create the DreamHome database in Oracle, SQL Server, and MySQL using the SQL statements available on D2L under Contents/Lecture05. Create the ERD of DreamHome in Oracle and the ERD of DreamHome in MySQL and copy both diagrams in your homework Microsoft Word document. Run queries 6.1 through 6.10 in Oracle, SQL Server, and MySQL. Copy the results/output of the queries from those softwares in your homework Microsoft Word document. 1. 2. 3. 4.Explanation / Answer
MySql:
6.1
select * from branch
branchno
street
city
postcode
B002
56 Clover Dr
London
NW10 6EU
B003
163 Main St
Glasgow
G11 9QX
B004
32 Manse Rd
Bristol
BS99 1NZ
B005
22 Deer Rd
London
SW1 4EH
B007
16 Argyll St
Aberdeen
AB2 3SU
select * from staff
staffno
fname
lname
position
sex
dob
salary
branchno
SA9
Mary
Howe
Assistant
F
1970-02-19
9000
B007
SG14
David
Ford
Supervisor
M
1958-03-24
18000
B003
SG37
Ann
Beech
Assistant
F
1970-11-10
12000
B003
SG5
Susan
Brand
Manager
F
1940-06-03
24000
B003
SL21
John
White
Manager
M
1945-10-01
30000
B005
SL41
Julie
Lee
Assistant
F
1965-06-13
9000
B005
select * from privateowner
ownerno
fname
lname
address
telno
password
CO40
Tina
Murphy
63 Well St, Glasgow G42
0141-943-1728
tinam@hotmail.com
********
CO46
Joe
Keogh
2 Fergus Dr, Aberdeen AB2 7SX
01224-861212
jkeogh@lhh.com
********
CO87
Carol
Ferrel
6 Achray St, Glasgow G32 9DX
0141-357-7419
cferrel@gail.com
********
CO93
Tony
Shaw
12 Park Pl, Glasgow G4 0Q4
0141-225-7025
tony.shaw@ark.com
********
select * from propertyforrent
propertyno
street
city
postcode
type
rooms
rent
ownerno
staffno
branchno
PA14
16 Holhead
Aberdeen
AB7 5SU
House
6
650
CO46
SA9
B007
PG16
5 Novar Drive
Glasgow
G12 9AX
Flat
4
450
CO93
SG14
B003
PG21
18 Dale Road
Glasgow
G12
House
5
600
CO87
SG37
B003
PG36
2 Manor Road
Glasgow
G32 4QX
Flat
3
375
CO93
SG37
B003
PG4
6 Lawrence Street
Glasgow
G11 9QX
Flat
3
350
CO40
(null)
B003
PL94
6 Argyll Street
London
NW2
Flat
4
400
CO87
SL41
B005
select * from client
clientno
fname
lname
telno
preftype
maxrent
CR56
Aline
Stewart
0141-848-1825
Flat
350
astewart@hotmail.com
CR62
mary
tregear
01224-196720
Flat
600
maryt@hotmail.co.uk
CR74
Mike
Ritchie
01475-392178
House
750
mritchie01@yahoo.co.uk
CR76
John
Kay
0207-774-5632
Flat
425
john.kay@gail.com
select * from registration
clientNo
branchNo
staffNo
dateJoined
CR56
B003
SG37
2012-04-11
CR62
B007
SA9
2012-03-07
CR74
B003
SG37
2011-11-16
CR76
B005
SL41
2013-01-02
select * from viewing
clientno
propertyno
viewdate
comment1
CR56
PA14
2013-05-24
too small
CR56
PG36
2013-04-28
(null)
CR56
PG4
2013-05-26
(null)
CR62
PA14
2013-05-14
no dining room
CR76
PG4
2013-04-20
too remote
6.2
select staffno,fname,lname,salary from staff
OUTPUT
staffno
fname
lname
salary
SA9
Mary
Howe
9000
SG14
David
Ford
18000
SG37
Ann
Beech
12000
SG5
Susan
Brand
24000
SL21
John
White
30000
SL41
Julie
Lee
9000
6.3
select * from viewing where viewdate < curdate()
OUTPUT
clientno
propertyno
viewdate
comment1
CR56
PA14
2013-05-24
too small
CR56
PG36
2013-04-28
(null)
CR56
PG4
2013-05-26
(null)
CR62
PA14
2013-05-14
no dining room
CR76
PG4
2013-04-20
too remote
6.4
select staffno,fname,lname,salary/12 as monthly_salary from staff
OUTPUT
staffno
fname
lname
monthly_salary
SA9
Mary
Howe
750
SG14
David
Ford
1500
SG37
Ann
Beech
1000
SG5
Susan
Brand
2000
SL21
John
White
2500
SL41
Julie
Lee
750
6.5
select * from staff where salary > 10000
OUTPUT
staffno
fname
lname
position
sex
dob
salary
branchno
SG14
David
Ford
Supervisor
M
1958-03-24
18000
B003
SG37
Ann
Beech
Assistant
F
1970-11-10
12000
B003
SG5
Susan
Brand
Manager
F
1940-06-03
24000
B003
SL21
John
White
Manager
M
1945-10-01
30000
B005
6.6
select street,city, postcode from branch where city in ('London','Glasgow')
OUTPUT
street
city
postcode
56 Clover Dr
London
NW10 6EU
163 Main St
Glasgow
G11 9QX
22 Deer Rd
London
SW1 4EH
6.7
select * from staff where salary between 20000 and 30000
OUTPUT
staffno
fname
lname
position
sex
dob
salary
branchno
SG5
Susan
Brand
Manager
F
1940-06-03
24000
B003
SL21
John
White
Manager
M
1945-10-01
30000
B005
6.8
select staffno,fname,lname,position from staff where position in ('Manager','Supervisor')
OUTPUT
staffno
fname
lname
position
SG14
David
Ford
Supervisor
SG5
Susan
Brand
Manager
SL21
John
White
Manager
6.9
select * from privateowner where address like '%Glasgow%'
OUTPUT
ownerno
fname
lname
address
telno
password
CO40
Tina
Murphy
63 Well St, Glasgow G42
0141-943-1728
tinam@hotmail.com
********
CO87
Carol
Ferrel
6 Achray St, Glasgow G32 9DX
0141-357-7419
cferrel@gail.com
********
CO93
Tony
Shaw
12 Park Pl, Glasgow G4 0Q4
0141-225-7025
tony.shaw@ark.com
********
6.10
select * from viewing where propertyno = 'PG4' and comment1 is null
OUTPUT
clientno
propertyno
viewdate
comment1
CR56
PG4
2013-05-26
(null)
Oracle:
6.1
select * from branch
branchno
street
city
postcode
B002
56 Clover Dr
London
NW10 6EU
B003
163 Main St
Glasgow
G11 9QX
B004
32 Manse Rd
Bristol
BS99 1NZ
B005
22 Deer Rd
London
SW1 4EH
B007
16 Argyll St
Aberdeen
AB2 3SU
select * from staff
staffno
fname
lname
position
sex
dob
salary
branchno
SA9
Mary
Howe
Assistant
F
1970-02-19
9000
B007
SG14
David
Ford
Supervisor
M
1958-03-24
18000
B003
SG37
Ann
Beech
Assistant
F
1970-11-10
12000
B003
SG5
Susan
Brand
Manager
F
1940-06-03
24000
B003
SL21
John
White
Manager
M
1945-10-01
30000
B005
SL41
Julie
Lee
Assistant
F
1965-06-13
9000
B005
select * from privateowner
ownerno
fname
lname
address
telno
password
CO40
Tina
Murphy
63 Well St, Glasgow G42
0141-943-1728
tinam@hotmail.com
********
CO46
Joe
Keogh
2 Fergus Dr, Aberdeen AB2 7SX
01224-861212
jkeogh@lhh.com
********
CO87
Carol
Ferrel
6 Achray St, Glasgow G32 9DX
0141-357-7419
cferrel@gail.com
********
CO93
Tony
Shaw
12 Park Pl, Glasgow G4 0Q4
0141-225-7025
tony.shaw@ark.com
********
select * from propertyforrent
propertyno
street
city
postcode
type
rooms
rent
ownerno
staffno
branchno
PA14
16 Holhead
Aberdeen
AB7 5SU
House
6
650
CO46
SA9
B007
PG16
5 Novar Drive
Glasgow
G12 9AX
Flat
4
450
CO93
SG14
B003
PG21
18 Dale Road
Glasgow
G12
House
5
600
CO87
SG37
B003
PG36
2 Manor Road
Glasgow
G32 4QX
Flat
3
375
CO93
SG37
B003
PG4
6 Lawrence Street
Glasgow
G11 9QX
Flat
3
350
CO40
(null)
B003
PL94
6 Argyll Street
London
NW2
Flat
4
400
CO87
SL41
B005
select * from client
clientno
fname
lname
telno
preftype
maxrent
CR56
Aline
Stewart
0141-848-1825
Flat
350
astewart@hotmail.com
CR62
mary
tregear
01224-196720
Flat
600
maryt@hotmail.co.uk
CR74
Mike
Ritchie
01475-392178
House
750
mritchie01@yahoo.co.uk
CR76
John
Kay
0207-774-5632
Flat
425
john.kay@gail.com
select * from registration
clientNo
branchNo
staffNo
dateJoined
CR56
B003
SG37
2012-04-11
CR62
B007
SA9
2012-03-07
CR74
B003
SG37
2011-11-16
CR76
B005
SL41
2013-01-02
select * from viewing
clientno
propertyno
viewdate
comment1
CR56
PA14
2013-05-24
too small
CR56
PG36
2013-04-28
(null)
CR56
PG4
2013-05-26
(null)
CR62
PA14
2013-05-14
no dining room
CR76
PG4
2013-04-20
too remote
6.2
select staffno,fname,lname,salary from staff
OUTPUT
STAFFNO
FNAME
LNAME
SALARY
SL21
John
White
30000
SG37
Ann
Beech
12000
SG14
David
Ford
18000
SA9
Mary
Howe
9000
SG5
Susan
Brand
24000
SL41
Julie
Lee
9000
6.3
select * from viewing where viewdate < sysdate
OUTPUT
CLIENTNO
PROPERTYNO
VIEWDATE
COMMENT1
CR56
PA14
2013-05-24
too small
CR76
PG4
2013-04-20
too remote
CR56
PG4
2013-05-26
(null)
CR62
PA14
2013-05-14
no dining room
CR56
PG36
2013-04-28
(null)
6.4
select staffno,fname,lname,salary/12 as monthly_salary from staff
OUTPUT
STAFFNO
FNAME
LNAME
MONTHLY_SALARY
SL21
John
White
2500
SG37
Ann
Beech
1000
SG14
David
Ford
1500
SA9
Mary
Howe
750
SG5
Susan
Brand
2000
SL41
Julie
Lee
750
6.5
select * from staff where salary > 10000
OUTPUT
staffno
fname
lname
position
sex
dob
salary
branchno
SG14
David
Ford
Supervisor
M
1958-03-24
18000
B003
SG37
Ann
Beech
Assistant
F
1970-11-10
12000
B003
SG5
Susan
Brand
Manager
F
1940-06-03
24000
B003
SL21
John
White
Manager
M
1945-10-01
30000
B005
6.6
select street,city, postcode from branch where city in ('London','Glasgow')
OUTPUT
street
city
postcode
56 Clover Dr
London
NW10 6EU
163 Main St
Glasgow
G11 9QX
22 Deer Rd
London
SW1 4EH
6.7
select * from staff where salary between 20000 and 30000
OUTPUT
staffno
fname
lname
position
sex
dob
salary
branchno
SG5
Susan
Brand
Manager
F
1940-06-03
24000
B003
SL21
John
White
Manager
M
1945-10-01
30000
B005
6.8
select staffno,fname,lname,position from staff where position in ('Manager','Supervisor')
OUTPUT
staffno
fname
lname
position
SG14
David
Ford
Supervisor
SG5
Susan
Brand
Manager
SL21
John
White
Manager
6.9
select * from privateowner where address like '%Glasgow%'
OUTPUT
ownerno
fname
lname
address
telno
password
CO40
Tina
Murphy
63 Well St, Glasgow G42
0141-943-1728
tinam@hotmail.com
********
CO87
Carol
Ferrel
6 Achray St, Glasgow G32 9DX
0141-357-7419
cferrel@gail.com
********
CO93
Tony
Shaw
12 Park Pl, Glasgow G4 0Q4
0141-225-7025
tony.shaw@ark.com
********
6.10
select * from viewing where propertyno = 'PG4' and comment1 is null
OUTPUT
clientno
propertyno
viewdate
comment1
CR56
PG4
2013-05-26
(null)
Sql server:
6.1
select * from branch
branchno
street
city
postcode
B002
56 Clover Dr
London
NW10 6EU
B003
163 Main St
Glasgow
G11 9QX
B004
32 Manse Rd
Bristol
BS99 1NZ
B005
22 Deer Rd
London
SW1 4EH
B007
16 Argyll St
Aberdeen
AB2 3SU
select * from staff
staffno
fname
lname
position
sex
dob
salary
branchno
SA9
Mary
Howe
Assistant
F
1970-02-19
9000
B007
SG14
David
Ford
Supervisor
M
1958-03-24
18000
B003
SG37
Ann
Beech
Assistant
F
1970-11-10
12000
B003
SG5
Susan
Brand
Manager
F
1940-06-03
24000
B003
SL21
John
White
Manager
M
1945-10-01
30000
B005
SL41
Julie
Lee
Assistant
F
1965-06-13
9000
B005
select * from privateowner
ownerno
fname
lname
address
telno
password
CO40
Tina
Murphy
63 Well St, Glasgow G42
0141-943-1728
tinam@hotmail.com
********
CO46
Joe
Keogh
2 Fergus Dr, Aberdeen AB2 7SX
01224-861212
jkeogh@lhh.com
********
CO87
Carol
Ferrel
6 Achray St, Glasgow G32 9DX
0141-357-7419
cferrel@gail.com
********
CO93
Tony
Shaw
12 Park Pl, Glasgow G4 0Q4
0141-225-7025
tony.shaw@ark.com
********
select * from propertyforrent
propertyno
street
city
postcode
type
rooms
rent
ownerno
staffno
branchno
PA14
16 Holhead
Aberdeen
AB7 5SU
House
6
650
CO46
SA9
B007
PG16
5 Novar Drive
Glasgow
G12 9AX
Flat
4
450
CO93
SG14
B003
PG21
18 Dale Road
Glasgow
G12
House
5
600
CO87
SG37
B003
PG36
2 Manor Road
Glasgow
G32 4QX
Flat
3
375
CO93
SG37
B003
PG4
6 Lawrence Street
Glasgow
G11 9QX
Flat
3
350
CO40
(null)
B003
PL94
6 Argyll Street
London
NW2
Flat
4
400
CO87
SL41
B005
select * from client
clientno
fname
lname
telno
preftype
maxrent
CR56
Aline
Stewart
0141-848-1825
Flat
350
astewart@hotmail.com
CR62
mary
tregear
01224-196720
Flat
600
maryt@hotmail.co.uk
CR74
Mike
Ritchie
01475-392178
House
750
mritchie01@yahoo.co.uk
CR76
John
Kay
0207-774-5632
Flat
425
john.kay@gail.com
select * from registration
clientNo
branchNo
staffNo
dateJoined
CR56
B003
SG37
2012-04-11
CR62
B007
SA9
2012-03-07
CR74
B003
SG37
2011-11-16
CR76
B005
SL41
2013-01-02
select * from viewing
clientno
propertyno
viewdate
comment1
CR56
PA14
2013-05-24
too small
CR56
PG36
2013-04-28
(null)
CR56
PG4
2013-05-26
(null)
CR62
PA14
2013-05-14
no dining room
CR76
PG4
2013-04-20
too remote
6.2
select staffno,fname,lname,salary from staff
OUTPUT
staffno
fname
lname
salary
SA9
Mary
Howe
9000
SG14
David
Ford
18000
SG37
Ann
Beech
12000
SG5
Susan
Brand
24000
SL21
John
White
30000
SL41
Julie
Lee
9000
6.3
select * from viewing where viewdate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
OUTPUT
clientno
propertyno
viewdate
comment1
CR56
PA14
2013-05-24
too small
CR56
PG36
2013-04-28
(null)
CR56
PG4
2013-05-26
(null)
CR62
PA14
2013-05-14
no dining room
CR76
PG4
2013-04-20
too remote
6.4
select staffno,fname,lname,salary/12 as monthly_salary from staff
OUTPUT
staffno
fname
lname
monthly_salary
SA9
Mary
Howe
750
SG14
David
Ford
1500
SG37
Ann
Beech
1000
SG5
Susan
Brand
2000
SL21
John
White
2500
SL41
Julie
Lee
750
6.5
select * from staff where salary > 10000
OUTPUT
staffno
fname
lname
position
sex
dob
salary
branchno
SG14
David
Ford
Supervisor
M
1958-03-24
18000
B003
SG37
Ann
Beech
Assistant
F
1970-11-10
12000
B003
SG5
Susan
Brand
Manager
F
1940-06-03
24000
B003
SL21
John
White
Manager
M
1945-10-01
30000
B005
6.6
select street,city, postcode from branch where city in ('London','Glasgow')
OUTPUT
street
city
postcode
56 Clover Dr
London
NW10 6EU
163 Main St
Glasgow
G11 9QX
22 Deer Rd
London
SW1 4EH
6.7
select * from staff where salary between 20000 and 30000
OUTPUT
staffno
fname
lname
position
sex
dob
salary
branchno
SG5
Susan
Brand
Manager
F
1940-06-03
24000
B003
SL21
John
White
Manager
M
1945-10-01
30000
B005
6.8
select staffno,fname,lname,position from staff where position in ('Manager','Supervisor')
OUTPUT
staffno
fname
lname
position
SG14
David
Ford
Supervisor
SG5
Susan
Brand
Manager
SL21
John
White
Manager
6.9
select * from privateowner where address like '%Glasgow%'
OUTPUT
ownerno
fname
lname
address
telno
password
CO40
Tina
Murphy
63 Well St, Glasgow G42
0141-943-1728
tinam@hotmail.com
********
CO87
Carol
Ferrel
6 Achray St, Glasgow G32 9DX
0141-357-7419
cferrel@gail.com
********
CO93
Tony
Shaw
12 Park Pl, Glasgow G4 0Q4
0141-225-7025
tony.shaw@ark.com
********
6.10
select * from viewing where propertyno = 'PG4' and comment1 is null
OUTPUT
clientno
propertyno
viewdate
comment1
CR56
PG4
2013-05-26
(null)
branchno
street
city
postcode
B002
56 Clover Dr
London
NW10 6EU
B003
163 Main St
Glasgow
G11 9QX
B004
32 Manse Rd
Bristol
BS99 1NZ
B005
22 Deer Rd
London
SW1 4EH
B007
16 Argyll St
Aberdeen
AB2 3SU
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.