Consider the following database schema and specify the following queriesor views in SQL.EMPLOYEE(SSN, name, salary, DNo, SupervisorSSN)DEPARTMENT (DNo, DeptName, MgrSSN)PROJECT(PNo, location, ProjName)WORKON (SSN, PNo, hours)Find the SSN and name of the employee who works on more than twoprojects.For each project, retrieve the PNo, ProjName, and the number ofemployees from 'Research' department who work on the project.Create a view called 'DEPT_INFO' that has the department name, thenumber of employees, and the total salary of the employees of the department.

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

Answered: Consider the following database schema…

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Similar questions

Write SQL update statements to do the following on the database schema shown in Figure 1.i. Insert a new course, <‘Knowledge Engineering’, ‘CS4390’, 3, ‘CS’>.ii. Delete the record for the student whose name is ‘Smith’ and whose student number is 17.
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…
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)
Consider the following schema of hotel reservations database: Hotel (Hotel No, Name, City) Room (Room No, Hotel No, Type, Price) Booking (Hotel No, Guest No, Date From, Date_To, Room No) Guest (Guest_No, Name, City) Write RA queries for the following: a) Get the details (names and price) of all hotels in California. b) Get the average price of a room grouped by city. c) Find the hotel name and city of the hotel with the highest priced room. d) Get the hotel names and room numbers of any hotel rooms that have not been booked.
Computer Science provide SQL code (Use the EBOOKS database) When a course is offered in several sections taught by more than one instructor, students have the ability to choose an instructor, otherwise they have no choice. The deans of each college would like to know how many courses have only one section in the schedule, how many courses have more than 1 section, but taught by the same instructor, how many courses have sections taught by more than one instructor. Sort the results alphabetically by College ID as shown below: COLLEGE SINGLE_SECTION MULTISECTION_ONE_INSTRUCTOR MULTIPLE_INSTRUCTORS CAS 83 21 22 CED 76 8 39 COB 72 16 13
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 taken courses in both the Computer Science and Mathematics departments. APPENDIX - You may detach this page from the exam: 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)
Based on the table provided, answer the following questions in relational algebra.
Create the following tables in your database with the following schema: Table: Employee | Column Name | Type | empId I name | supervisor | salary | int | varchar | | int | int empId is the primary key column for this table. Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager. Table: Bonus | Column Name | Type | | empId | bonus | int | int empId is the primary key column for this table. empId is a foreign key to empId from the Employee table. Each row of this table contains the id of an employee and their respective bonus. Add the following data to your tables: Input: Employee table: | empId | name | supervisor | salary | | 3 | Brad | null | 4000 | 1000 | 2000 | 4000 | John | 3 | 2 | 4 | Dan | 3 | Thomas | 3

Recommended textbooks for you

Computer Networking: A Top-Down Approach (7th Edi…

Computer Engineering

ISBN:

9780133594140

Author:

James Kurose, Keith Ross

Publisher:

PEARSON

Computer Organization and Design MIPS Edition, Fi…

Computer Engineering

ISBN:

9780124077263

Author:

David A. Patterson, John L. Hennessy

Publisher:

Elsevier Science

Network+ Guide to Networks (MindTap Course List)

Computer Engineering

ISBN:

9781337569330

Author:

Jill West, Tamara Dean, Jean Andrews

Publisher:

Cengage Learning

Computer Networking: A Top-Down Approach (7th Edi…

Computer Engineering

ISBN:

9780133594140

Author:

James Kurose, Keith Ross

Publisher:

PEARSON

Computer Organization and Design MIPS Edition, Fi…

Computer Engineering

ISBN:

9780124077263

Author:

David A. Patterson, John L. Hennessy

Publisher:

Elsevier Science

Network+ Guide to Networks (MindTap Course List)

Computer Engineering

ISBN:

9781337569330

Author:

Jill West, Tamara Dean, Jean Andrews

Publisher:

Cengage Learning

Concepts of Database Management

Computer Engineering

ISBN:

9781337093422

Author:

Joy L. Starks, Philip J. Pratt, Mary Z. Last

Publisher:

Cengage Learning

Prelude to Programming

Computer Engineering

ISBN:

9780133750423

Author:

VENIT, Stewart

Publisher:

Pearson Education

Sc Business Data Communications and Networking, T…

Computer Engineering

ISBN:

9781119368830

Author:

FITZGERALD

Publisher:

WILEY

SEE MORE TEXTBOOKS