Write the following queries in SQL on the relational database schema for COMPANYdatabase given in CompanySchema.pdf.1. Find the last name of employees who have salary greater than 30 000.2. Find the average salary of employees who works on the projects which are located in"Stafford'.3. Find the total working hours of each manager.4. Find the name of the projects on which no employee works. EMPLOYEEFrame Minit Lname Sen Bdate Address Sex Salary Super sen Dno+444DEPARTMENTDname DnumberMor an Mgr start dateDEPT LOCATIONSDnumber DlocationPROJECTPrame Prumber Plocation DnumWORKS ONEnnn Pno HoirHoursDEPENDENTEsen Dependent name Se Bdate RelationshipReforential integrity constraints displayedon the COMFWNY relational databaseschema.One possible database state for the COMPANY relational database schema.EMPLOYEEFname Minit LnameJohnFrankin TSex Salary30000 333445555 5M 40000 Jo00665555F 25000 Joe7e5432143000 J88Ge5556SanBdateAddressSuper en Dno123456789 1965-01-09 731 Fondren, Hounton, TX M333445555 1955-12-08 638 Voas, Houston, TXGo0087777 1960-01-19 3321 Castle, Spring. TX987654321 1941-06-20 291 Berry, Bellaire, TXNarayan 666884444 1962-09-15 975 Fire Oak, Humibile, TX MBSmithWongZelayaWallace5Alicia4JenniferF38000 33344565525000 333445555M 25000 007654321M 55000 NULLRameshEnglish453453453 1972-07-31 5631 Rice, Houston, TXFJoyceAhmadA9879879071969-03-29 980 Dallan, Houston, TXB80G05555 1937-11-10 450 Stone, Houston, TXJabbar4JamesEBorgDEPARTMENTDEPT LOCATIONSDnumberMar atart dateDnumberDlocationDnameMar enResearch3334455551988-06-22HoustonAdministration9876543211995-01-01StaffordHeadquarters1981-06-19BelaireSugarlandHoustonWORKS ONPROJECTEssnPnoHoursPramePnumberPlocationDnum123456709123456700325ProductXBellaireProductYProductz75Sugartand666884444340.03Houston545345345320.0Computeriation10Stafford445345345020.0Reorganization20Houston33344556510.04Newbenefite30Stafford333445566310.03334455653334455551010.0DEPENDENT20100EsanDependent naSexBdateRelationshin3030.0333445555Alice1986-04-05Daughter9098877771010.0333445556TheodoreM1983-10-25Son9879870871035.03334455551958-05-03SpouseSpouseJoy007007007987654321305.0987654321Abner1942-02-283020.0123456700Michael1988-01-04Son9876543212015.0123450700Alice1988-12-30DaughterB8866555520NULL123456789Ekrabeth1967-05-05Spoune

Note: As per our guidelines we are allowed answer only first 3 subparts. Kindly repost the remaining…

Write the following queries in SQL on the relational database schema for COMPANY latabase given in CompanySchema.pdf. 1. Find the last name of employees who have salary greater than 30 000. 2. Find the average salary of employees who works on the projects which are located in 'Stafford'. 3. Find the total working hours of each manager. 4. Find the name of the projects on which no employee works.

Write the following queries in SQL on the relational database schema for COMPANY latabase given in CompanySchema.pdf. 1. Find the last name of employees who have salary greater than 30 000. 2. Find the average salary of employees who works on the projects which are located in 'Stafford'. 3. Find the total working hours of each manager. 4. Find the name of the projects on which no employee works.

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

Q: Consider the following schema and answer the following SQL queries. Employee ( person_name , street,…

A: Hey, since multiple sub part questions posted, we will answer first three sub part questions…

Q: You are interested in compiling a small SQL database of the contents of this course. You decide you…

A: In this question, we have to create a spreadsheet on Excel which has the description of both the…

Q: Use Microsoft SQL Server Management Studio for this practical i. Create a database called ‘ORG’…

A: Structured Query Language is abbreviated as SQL or also pronounced as See-Qwell. It is developed for…

Q: Consider the following schema and write the following queries in relational algebra. Product(pid,…

A: We have to retrieve entire record and the only condition here is product name is 'shoes'. SELECT *…

Q: E. The AMD venue is where the music festival showcases up-and-coming bands. We want to identify…

A: This query can be defined in such a way that it will first be part of the ticket_sales_fact table…

Q: Write the SQL code to create the relational schemas from this data base system : Relationships :…

A: SQL code explanation: IntroductionThe provided SQL code creates a relational database schema based…

Q: Consider the database for a company named WPC that contains data about company employees,…

A: According to the Question below the Solution:

Q: Using the instance of the Airport database, formulate the following queries in the relational…

A: Relational algebra is a procedural query language.

Q: Indexing Using the relational table LINEITEM of the sample database TPCHR, for each one of the…

Q: Write the SQL code to create the relational schemas from this data base system : Entities and…

A: The following SQL code builds relational schemas for the specified database system using the…

Q: PLZ help with the following: Translate your ER diagram into a relational schema in the form of SQL…

A: E-R diagram is given with different attributes. From this we will create tables

Q: of a relational data

A: Ans 1 Event with date - Select * from customer where event_date between 'yyyy--mm--dd' and…

Q: Using the instance of the Airport database, formulate the following queries in the relational…

A: Relational algebra which refers to the a procedural query language. It also which gives a step by…

Q: Consider the following database schema and specify the following queries or views in SQL.…

A: a)select SSN,name from EMPLOYEE where SSN in(select SSN from WORKON group by SSN having…

Q: Using MySQL Workbench, create a new schema for this assignment and import assignment2.sql.…

A: ANSWER: Join the customers and shipping_details tables together based on PK/FK. The SQL should…

Q: Suppose full name of the instructors are stored as atomic value where parts of names are separated…

A: The Answer is in Below Steps

Q: SQL Help Write a SQL statement that add the following entry in the College table - CollegeID: 1234…

A: Answer: INSERT INTO College VALUES ('1234','Example College','San Francisco','CA');

Q: Single level ordered indexes are used in every DBMS, sometimes deployed automatically, sometimes…

A: Hey there, I am writing the required solution based on the above given question. Please do find the…

Q: Using the instance of the Airport database, formulate the following queries in the relational…

A: Your query is given below as you required.

Concept explainers

SQL Query

Structured Query Language is the full form of SQL. It is one of the standard languages to deal with the relational database. It is used to insert, update, delete, and search the records stored in a RDBMS. The relational databases are created and managed by using SQL.

Question

Write the following queries in SQL on the relational database schema for COMPANY
database given in CompanySchema.pdf.
1. Find the last name of employees who have salary greater than 30 000.
2. Find the average salary of employees who works on the projects which are located in
"Stafford'.
3. Find the total working hours of each manager.
4. Find the name of the projects on which no employee works.

EMPLOYEE
Frame Minit Lname Sen Bdate Address Sex Salary Super sen Dno
+444
DEPARTMENT
Dname Dnumber
Mor an Mgr start date
DEPT LOCATIONS
Dnumber Dlocation
PROJECT
Prame Prumber Plocation Dnum
WORKS ON
Ennn Pno Hoir
Hours
DEPENDENT
Esen Dependent name Se Bdate Relationship
Reforential integrity constraints displayed
on the COMFWNY relational database
schema.
One possible database state for the COMPANY relational database schema.
EMPLOYEE
Fname Minit Lname
John
Frankin T
Sex Salary
30000 333445555 5
M 40000 Jo00665555
F 25000 Joe7e54321
43000 J88Ge5556
San
Bdate
Address
Super en Dno
123456789 1965-01-09 731 Fondren, Hounton, TX M
333445555 1955-12-08 638 Voas, Houston, TX
Go0087777 1960-01-19 3321 Castle, Spring. TX
987654321 1941-06-20 291 Berry, Bellaire, TX
Narayan 666884444 1962-09-15 975 Fire Oak, Humibile, TX M
B
Smith
Wong
Zelaya
Wallace
5
Alicia
4
Jennifer
F
38000 333445655
25000 333445555
M 25000 007654321
M 55000 NULL
Ramesh
English
453453453 1972-07-31 5631 Rice, Houston, TX
F
Joyce
Ahmad
A
987987907
1969-03-29 980 Dallan, Houston, TX
B80G05555 1937-11-10 450 Stone, Houston, TX
Jabbar
4
James
E
Borg
DEPARTMENT
DEPT LOCATIONS
Dnumber
Mar atart date
Dnumber
Dlocation
Dname
Mar en
Research
333445555
1988-06-22
Houston
Administration
987654321
1995-01-01
Stafford
Headquarters
1981-06-19
Belaire
Sugarland
Houston
WORKS ON
PROJECT
Essn
Pno
Hours
Prame
Pnumber
Plocation
Dnum
123456709
123456700
325
ProductX
Bellaire
ProductY
Productz
75
Sugartand
666884444
3
40.0
3
Houston
5
453453453
20.0
Computeriation
10
Stafford
4
453453450
20.0
Reorganization
20
Houston
333445565
10.0
4
Newbenefite
30
Stafford
333445566
3
10.0
333445565
333445555
10
10.0
DEPENDENT
20
100
Esan
Dependent na
Sex
Bdate
Relationshin
30
30.0
333445555
Alice
1986-04-05
Daughter
909887777
10
10.0
333445556
Theodore
M
1983-10-25
Son
987987087
10
35.0
333445555
1958-05-03
Spouse
Spouse
Joy
007007007
987654321
30
5.0
987654321
Abner
1942-02-28
30
20.0
123456700
Michael
1988-01-04
Son
987654321
20
15.0
123450700
Alice
1988-12-30
Daughter
B88665555
20
NULL
123456789
Ekrabeth
1967-05-05
Spoune

Expert Solution

This question has been solved!

Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.

SEE SOLUTION Check out a sample Q&A here

Step 1

VIEW

Step 2

VIEW

Step by step

Solved in 2 steps with 2 images

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

Can you answer this two question as MYSQL statement using database that I uploaded Thank you Answer the following questions based on the above database: Create a view named “Atlanta_Charter_Crew_V” that includes the following “For each charter of aircraft to Atlanta (ATL), print the charter date, charter distance, and the corresponding crew (employee) number, last name, first name, and crew job”. Your answer should include both the SQL statement for view creation along with the contents of the view (you get the contents of the view by running the command select * from Atlanta_Charter_Crew_V;). Modify the MODEL table to add the following attribute and insert the values shown in the following table. (Note: use ALTER TABLE and UPDATE commands.) Attribute name Attribute Description Attribute type Attribute Values MOD_TOTAL_ WEIGHT Weight of each model Numeric 12,500 for C-90A 7,300 for PA23-250 19,770 for PA31-350
Using the Henry Books database, decrease the inventory of book code 6128 at branch 2 by one.
Consider the following schema:instructor(ID, name, dept_name, salary)What is the correct SQL statement to perform a query to find the names of all instructors excluding duplicates?(a) select name from instructor;(b) select unique name from instructor;(c) select name from instructor no duplicates;(d) select distinct name from instructor;
Assume the e-commercial database shown in the Appendix. Write the following queries in (a) relational algebra and (b) SQL. State your assumptions if the semantics is not clear. 1) Find the names of all students who have not taken any courses. APPENDIX STUDENTS(student id, first_name, last _name, email, major_code, class_year) COURSES(course id, course_name, course_description, credits, department_code) ENROLLMENTS(student id, course id, semester, grade) MAJORS(major code, major_name, department_code) DEPARTMENTS(department code, department_name)
Prepare a MySQL script that creates your database, tables, and inserts a small sampling of madeupdata. Test it out in MySQL before submission For the below diagram write the SQL queries
Using the instance of the Airport database, formulate the following queries in the relationalalgebra: 1)List the name and the state of all United States airports. 2)List full details of all airports located outside the United States.Note: You should use the not equal sign  in this query. 3)Display the number of all carriers and the average of their revenues. 4)Display the minimum, the average, and the maximum distances of the flights that arescheduled on October 17th, 2017? Example: List all airport names. π name Airport More example provided it attached file.
Relational Algebra Questions:Using the instance of the Airport database, formulate the following queries in the relationalalgebra: 1. List full details of all airports located outside the United States.Note: You should use the not equal sign  in this query. 2. List the name and the state of all United States airports. 3. List the name and the fleet size of all carriers employing more than 50000 workers. 4. List the carrier name, the carrierID, the flightNo and the date of all flights. 5. Display the number of airports. 6. Display the number of flights with distances longer than 3000 miles. 7. Display the number of all carriers and the average of their revenues. 8. Display the number of flights leaving from JFK and the total of their distances. 9. Display the minimum, the average, and the maximum distances of the flights that arescheduled on October 17th, 2017?