Question 1 Consider the following relations with key underlined: Lives (person name, street, city) Works (person name, company_name, salary) Located (company name, city) Manages (person name, manager_name) Answer the following using SQL: a) Define the schema for relation Works in SQL, note that company name is foreign key. Specify constraint that salary should not be less than 10,000cedis. Find the names and city of persons who work for manager John. Find the names of persons who earn more than the average salary. John's manager has changed. The new manager is Anna. Remove all people whose name start with the letter 'S' from the database. List the number of companies in each city, in descending order by number. Who are the managers and for which company do they work? Add a new text column called Country to the Located table. b) c) d) f) g) h) i) j) Find how many people in each company earn more than GHC20,000 Create a view CapeWork (person_name, company_name) of all people who work in Cape Coast in ascending order of person name.

Question 1 Consider the following relations with key underlined: Lives (person name, street, city) Works (person name, company_name, salary) Located (company name, city) Manages (person name, manager_name) Answer the following using SQL: a) Define the schema for relation Works in SQL, note that company name is foreign key. Specify constraint that salary should not be less than 10,000cedis. Find the names and city of persons who work for manager John. Find the names of persons who earn more than the average salary. John's manager has changed. The new manager is Anna. Remove all people whose name start with the letter 'S' from the database. List the number of companies in each city, in descending order by number. Who are the managers and for which company do they work? Add a new text column called Country to the Located table. b) c) d) f) g) h) i) j) Find how many people in each company earn more than GHC20,000 Create a view CapeWork (person_name, company_name) of all people who work in Cape Coast in ascending order of person name.

Database Systems: Design, Implementation, & Management

11th Edition

ISBN:9781285196145

Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Chapter7: Introduction To Structured Query Language (sql)

Section: Chapter Questions

Problem 65C: Write the SQL code to create the table structures for the entities shown in Figure P7.65. The...

See similar textbooks

Similar questions

Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table in the Ch07_ConstructCo database. (See Figure P7.1.) Note that ASSIGN_CHARGE is a derived attribute that is calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS.
https://sqliteonline.com/ : Program link The first requirement: Using the SQL language, transform the following entity model into tables in the database. Entity Manger contains: M-name The name of the manager. M-ID and manager number.The department entity contains: D-name The name of the department. D-ID and department number.Create constraint master key name of the manager when creating the Manger table.Creating the foreign key constraint The name of the manager when creating the Department table. The second requirement: After creating the tables and specifying the primary and foreign key, do the following: * Add a column D_loc to the Department table, specifying the type of column.* Remove the column named M_name from the Manger table* Change the name of the department table to Dep Please:: ******* ATTACH A PICTURE OF THE CODES USED AND PICTURE OF THE PROGRAM SCREEN *******
For the following relation schema : employee(employee-name, street, city) works(employee-name, company-name, salary) company(company-name, city) manages(employee-name, manager-name) Give an expression in SQL for each of the following queries: a) Find the names, street address, and cities of residence for all employees who work for 'First Bank Corporation' and earn more than $10,000. b) Find the names of all employees in the database who live in the same cities as the companies for which they work. c) Find the names of all employees in the database who live in the same cities and on the same streets as do their managers. d) Find the names of all employees in the database who do not work for 'First Bank Corporation'. Assume that all people work for exactly one company Please ans nicely request and ans all because this one one question.
Write appropriate SQL DDL statements for declaring the database "Employee", assign admin privilege to user "AMAL" Write appropriate SQL DDL statements for declaring the table PERSONS. Specify all constraints: Field Null key PersonID No PRIMARY No LastName varchar(30) FirstName varchar(30) No Address varchar(50) YES City varchar(30) YES Type int
onsider the following schema: Product(pid, name, type, manufacturer, price), Buys(cid, pid), Customer(cid, cname, age, gender) Write the following query in relational algebra and SQL a) Find the names of all customers who have purchased all products manufactured by Sears. b) Find the names of all customers who have not purchased the most expensive product c) Find the names and cids of all customers who have purchased the second most expensive product. You can assume that no two products have the same prices.
Assignment III (5%): Design of the conceptual model (ERD) from the given relational database. (Note: students has to give appropriate labels on relationships and type of relationships between the entities) Use the same Assignment- I schema to perform the PL/SQL programs Q1. The management of the organization has decided to increase the salaries of all employees to 15% for this, write a PL/SQL block to accept the employee number and to update the salary of that employee. Display the output based on the existing records of the employees. (2 marks) Q2. having the employee age above 40 years. (2 marks) Create a PL/SQL a cursor program that will increase the employee salary to 25% who are Q3. where place belongs to Ibri, dunk or Yanul (hint: Project (projectID. projectName, place, start Year, end Year, #DepartmentID) (2 marks) Write a PL/SQL procedure to change the name of the project to "AL Dahariyah Projects"
Write a Structured Query Language (SQL) statement for each of the following questions:a) Create the AppointmentDetails table with the following requirements:  Appropriate data types. All keys can store between 6 to 10 characters. All the columns must have values (cannot be empty). Add on charges is set to 0.00 by default. Enforce entity and referential integrities. b) Diplay the therapists’ particulars (include TheraName, TheraContact) as first two columns, then calculate the Total_Count_Of_Appointment for each therapist within the year 2020 as third columns. Lastly, arrange the outcome to show the list by the higher popularity of therapists first.
All parts of this question use the following database schema: Flights(flno, from, to, distance) Aircraft(aid, aname, cruisingrange) Certified(eid, aid) Employees(eid, ename, salary) c) For each cruisingrange held by at least 3 aircraft, give the lowest salary of employees certified to fly one of those aircraft. Express in SQL only:
Relational algebra The Sailors-Boats-Reserves database schema consists of three relations, whose schemas are: Sailors(sid, sname, rating, age) Boats(bid, bname, color) Reserves(sid, bid, day) Give a relational algebra expression for each of the following queries: Find all information of sailors who have reserved boat number 102. Find the names and ages of sailors who have reserved a red boat. Find the names of sailors who have reserved a red and a green boat.
Consider the following schema (keys are in Bold): Product(pid, name, type, manufacturer, price), Buys(cid, pid), Customer(cid, cname, age, gender) a) Write the following query in relational algebra. Find the names of all customers who have purchased all products manufactured by Sears. b) Write the following query in SQL. Find the names of all customers who have not purchased the most expensive product c) Write the following query in SQL. Find the names and cids of all customers who have purchased the second most expensive product. You can assume that no two products have the same prices.
Consider the following relational schema:Course (cid, cname, dept, credit)Student (sid, sname, dept, gpa)Enroll (cid, sid, grade, semester) The meaning of these relations is straightforward and the keys are underlined. Write thefollowing queries in SQL. a) List all the students who never got a grade above ‘C’.b) Find the number of students not enrolled in any class.c) Find name of students in EE department who took all the courses offered by CSdepartment.
a) from university database , Write the following queries in SQL Find the titles of courses in the Comp. Sci. department that have 3 credits Find all instructors earning the highest salary (there may be more than one with the same salary) Find all courses that have never been offered (i.e., do not occur in the section relation). b) given the following tables( instructor, teaches) what is the output of the following query select i.name from instructor as i, teaches as t where i.ID=t.ID and semester=’ Fall’ and year=2017;

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781305627482

Author:

Carlos Coronel, Steven Morris

Publisher:

Cengage Learning

SEE MORE TEXTBOOKS