Homework Answers
Here I am providing the answers for the above questions:
1.
SELECT Fname,Lname FROM EMPLOYEE , PROJECT ,WORKS_ON
WHERE Ssn=Essn AND Pnumber=Pno AND Plocation='Houstion';
2.
SELECT Fname,Lname FROM EMPLOYEE WHERE
NOT EXISTS
( SELECT * FROM DEPENDENT WHERE Ssn=Essn);
3.
SELECT Fname,Lname FROM EMPLOYEE,DEPARTMENT
WHERE Ssn=Mgr_ssn;
4.
SELECT Fname,Lname FROM EMPLOYEE
WHERE (SELECT COUNT(*) FROM DEPENDENT WHERE Ssn=Essn AND Relationship="Spouse")>1;
5.
SELECT Fname,Lname FROM EMPLOYEE
WHERE( ( SELECT Pno FROM WORKS_ON WHERE Ssn=Essn)
CONTAINS
(SELECT Pnumber FROM PROJECT));
Hoping that the above answer will help you...Thank you...
Add Answer to:
Use the tables in the next page to answer the following questions in SQL. 1. Print...
Search all employee who work total on all project less than 40 hrs from works_on table....
Search all employee who work total on all project less than 40
hrs from works_on table.
Please give me a query for this.
Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 JZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 9876543214 JenniferSWallace 9876543211941-06-20...
(SELECT pnumber FROM project, department, employee WHERE dnum=dnumber AND mgr_ssn=ssn AND lname='Smith') UNION (SELECT pnumber FROM...
(SELECT pnumber
FROM project, department, employee
WHERE dnum=dnumber AND mgr_ssn=ssn AND lname='Smith')
UNION
(SELECT pnumber
FROM project, works_on, employee
WHERE pnumber=pno AND essn=ssnAND lname='Smith')
Can you draw the relation step by step , how to solve it
?!
Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 3334455555 Ssn Fname Minit Lname John Franklin TWong 333445555 1955-12-08 638 Voss, Houston, TXM40000...
write the following queris in sql : a) Using nested query , retreive the sependent names...
write the following queris in sql :
a) Using nested query , retreive the sependent names of male
employees whose salary > 30000
b ) list the female employee, who are not managers
EMPLOYEE Fname MinitLname John Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M 4 Ssn Sex Salary Super sen Dno B Smith 123456789 1965 01-09 731 Fondren, Houston, TX M 30000 13334455 AliciaZelaya 999987777 1968-01-19 3321 Castle, Spring, TXF Jennifer S Wallace 987654321 1941-06-20 291 Berry,...
Please provide the relation algebra, the oracle SQL code and the output tuples for the following (Answer #6 only the BONUS question). I have the others actually I have the BONUS, but I want to compar...
Please provide the relation algebra, the oracle SQL code and the
output tuples for the following (Answer #6 only the BONUS
question). I have the others actually I have the BONUS, but I want
to compare with your solution to make sure I did it correctly.
Thank you very much!
LAB exercises 2 Write the Oracle DML query codes for the following questions and take the screen shot of the output. 1. Retrieve the name and address of all employees...
Book: Fundamental of database systems Chapter 5 exercise 5.9 Figure 3-6 here provides examples on how...
Book: Fundamental of database systems
Chapter 5 exercise 5.9
Figure 3-6 here provides examples on how to answer this
question:
Photos - 20190906_111020[3691].jpg -ox See all photos + Add to Edit & Create Share : Q u ♡ 2 # d. A view that has the project name, controm of employees, and total hours worked per week on the project for each project with more than one employee working on it. 5.9. Consider the following view, DEPT SUMMARY, defined on...
Query 1: Retrieve names of all the projects as well as First and Last name of...
Query 1: Retrieve names of all the projects as well as First and Last name of managers if they are working on any of these projects. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null, Mgr_start_date date, primary key (dnumber)); CREATE...
Query #2: List the name of the project and total number of hours worked on by...
Query #2: List the name of the project and total number of hours worked on by all the employees on this project, also report the number of employees working on each project. Database: //STEP #1: CREATE TABLE employee ( fname varchar(15) not null, minit varchar(1), lname varchar(15) not null, ssn char(9), bdate date, address varchar(50), sex char, salary decimal(10,2), Super_ssn char(9), dno char(4), primary key (ssn)); CREATE TABLE department ( dname varchar(25) not null, dnumber char(4), Mgr_ssn char(9) not null,...
I need help with the following SQL query for a company database (script given below). The...
I need help with the following SQL query for a company database (script given below). The name of the Department. The number of employees working in that department. The number of different projects controlled by this department. The name of the project controlled by this department that has the maximum number of employees of the company working on it. The number of the above project. The cumulative sum of the number of employees of the company working on the projects...
drop table department cascade constraints; create table department ( Dname varchar2(15) not null, Dnumber int not...
drop table department cascade constraints;
create table department (
Dname varchar2(15) not null,
Dnumber int not null,
Mgr_ssn char(9) not null,
mgr_start_date Date,
primary key (Dnumber),
Unique (Dname));
insert into DEPARTMENT values ('Research', '5', '333445555',
'22-May-1988');
insert into DEPARTMENT values ('Administration', '4', '987654321',
'01-Jan-1995');
insert into DEPARTMENT values ('Headquarters', '1', '888665555',
'19-Jun-1981');
drop table employee cascade constraints;
create table employee (
Fname varchar2(15) not null,
Minit char(1),
Lname varchar2(15) not null,
Ssn char(9),
Bdate date,
Address varchar2(30),
Sex char(1),...
ules for submission: 1. All problems must be solved using SQL code. The SQL code and...
ules for submission:
1. All problems must be solved using SQL code. The SQL code
and the output of your query should be cut and pasted into your MS
Word file.
a. Output Tables must be screenshot from DBMS
b. Do not create a MS Word (or other application) table and
paste the results into it.
c. With the exception of Problems 4, 5 and 6, all output
should be the result of a single step. Do not paste outputs...
Search all employee who work total on all project less than 40
hrs from works_on table.
Please give me a query for this.
Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5 FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 JZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 9876543214 JenniferSWallace 9876543211941-06-20...
(SELECT pnumber
FROM project, department, employee
WHERE dnum=dnumber AND mgr_ssn=ssn AND lname='Smith')
UNION
(SELECT pnumber
FROM project, works_on, employee
WHERE pnumber=pno AND essn=ssnAND lname='Smith')
Can you draw the relation step by step , how to solve it
?!
Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Sex Salary Super ssn Dno B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 3334455555 Ssn Fname Minit Lname John Franklin TWong 333445555 1955-12-08 638 Voss, Houston, TXM40000...
write the following queris in sql :
a) Using nested query , retreive the sependent names of male
employees whose salary > 30000
b ) list the female employee, who are not managers
EMPLOYEE Fname MinitLname John Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M 4 Ssn Sex Salary Super sen Dno B Smith 123456789 1965 01-09 731 Fondren, Houston, TX M 30000 13334455 AliciaZelaya 999987777 1968-01-19 3321 Castle, Spring, TXF Jennifer S Wallace 987654321 1941-06-20 291 Berry,...
Please provide the relation algebra, the oracle SQL code and the
output tuples for the following (Answer #6 only the BONUS
question). I have the others actually I have the BONUS, but I want
to compare with your solution to make sure I did it correctly.
Thank you very much!
LAB exercises 2 Write the Oracle DML query codes for the following questions and take the screen shot of the output. 1. Retrieve the name and address of all employees...
Book: Fundamental of database systems
Chapter 5 exercise 5.9
Figure 3-6 here provides examples on how to answer this
question:
Photos - 20190906_111020[3691].jpg -ox See all photos + Add to Edit & Create Share : Q u ♡ 2 # d. A view that has the project name, controm of employees, and total hours worked per week on the project for each project with more than one employee working on it. 5.9. Consider the following view, DEPT SUMMARY, defined on...
drop table department cascade constraints;
create table department (
Dname varchar2(15) not null,
Dnumber int not null,
Mgr_ssn char(9) not null,
mgr_start_date Date,
primary key (Dnumber),
Unique (Dname));
insert into DEPARTMENT values ('Research', '5', '333445555',
'22-May-1988');
insert into DEPARTMENT values ('Administration', '4', '987654321',
'01-Jan-1995');
insert into DEPARTMENT values ('Headquarters', '1', '888665555',
'19-Jun-1981');
drop table employee cascade constraints;
create table employee (
Fname varchar2(15) not null,
Minit char(1),
Lname varchar2(15) not null,
Ssn char(9),
Bdate date,
Address varchar2(30),
Sex char(1),...
ules for submission:
1. All problems must be solved using SQL code. The SQL code
and the output of your query should be cut and pasted into your MS
Word file.
a. Output Tables must be screenshot from DBMS
b. Do not create a MS Word (or other application) table and
paste the results into it.
c. With the exception of Problems 4, 5 and 6, all output
should be the result of a single step. Do not paste outputs...
Most questions answered within 3 hours.
-
The extent to which assets are financed by borrowed funds and
other liabilities is indicated by:...
asked 15 minutes ago -
Explain in detail
Germany is the fifth largest economy
explain what goods and services Germany specializes...
asked 30 minutes ago -
The density of platinum is 21.45 g/mL. If a cube of platinum
with a mass of...
asked 35 minutes ago -
Accounts Receivable
Sales
A/R Posting
Extended Sales Invoice
Packing Slip
Compare invoice to packing slip 2...
asked 38 minutes ago -
Michaella, age 23, is a full-time law student and is claimed by
her parents as a...
asked 38 minutes ago -
Why are polymers not typically casted into products?
asked 55 minutes ago -
When rolling a die 129 times, what is the probability of rolling
a 6 no more...
asked 1 hour ago -
4. A call option currently sells for $7.75. It has a strike
price of $85 and...
asked 1 hour ago -
1.
You need to prepare 10.0 liters of an acid aqueous solution with a
pH of...
asked 1 hour ago -
Along an aggregate supply curve, if the level of output is less
than the natural level...
asked 1 hour ago -
By 2025, annual consumption in emerging markets will total $30
trillion and contribute more than ________...
asked 1 hour ago -
At what point does reformation cease to be a viable option for
those who are oppressed...
asked 1 hour ago