- Create dept_manager table with emp_no as primary key and auto increment and not null for other attributes. Ensure the DBMS deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key.- Show the employee last name, title and order by title in descending order Consider the following ERD diagram and write the appropriate SQL statements.Figure 3: ERD diagramemployees titlese emp_no : int(11)e title : varchar(50)e from_date : dateo to_date : dateemployees dept_managere emp_no : int(11)e dept_no : char(4)o from_date : dateo to_date : dateemployees employees& emp_no : int(11)o birth_date : datea first_name : varchar(14)o last_name : varchar(16)• gender : enum('M','F')o hire_date : dateemployees dept_empe emp_no : int(11)a dept_no : char(4)o from_date : dateo to_date : dateemployees salariese emp_no : int(11)# salary : int(11)e from_date : dateo to_date : dateemployees departmentse dept_no : char(4)e dept_name : varchar(40)

SUMMARY: -Hence, we discussed all the points.

Create dept_manager table with emp_no as primary key and auto increment and not null for other attributes. Ensure the DBMS deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key. - Show the employee last name, title and order by title in descending order

Create dept_manager table with emp_no as primary key and auto increment and not null for other attributes. Ensure the DBMS deletes the referencing rows in the child table when the referenced row is deleted in the parent table which has a primary key. - Show the employee last name, title and order by title in descending order

Database Systems: Design, Implementation, & Management

12th Edition

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Carlos Coronel, Steven Morris

Chapter8: Advanced Sql

Section: Chapter Questions

Problem 18RQ

See similar textbooks

Similar questions

The following ERD refers to an borrow system of a library. Member can borrow different books, which were written by different authors and published by different publishers. A writer can be contracted to one or more publishers. Each member can have different borrows with different books to different expiring dates.a)Give two Attributes for each entity. Draw them directly into the ER-diagram. b)Give a primary key for each entity. Draw it directly into the ER-diagram. c)Create the SQL statement for implementing the table for one of the entities of the up standing ERD
Write the SQL statements to create all of the necessary tables to represent the above diagram, representing as many constraints as possible. Note: you do not need to worry about specifying types for the fields, but you do need to provide suitable foreign key option(s). When playing a game, the home team name should be stored in attribute htname and the away team name should be stored in atname.
Consider the E-R diagram in Figure 8.7 showing entity set instructor. Give an SQL schema definition corresponding to the E-R diagram, treating phone number as an array of 10 elements, using Oracle or PostgreSQL syntax.
Consider the following schema of employees working at Carlson Fabrics Ltd (CFL) and write SQL statements for the queries.Employee(emp_id, emp_name, address, age, gender, designation, department, shift, salary, transport) i. Give the name of the employees whose mode of transport to CFL is van. ii. List the details of female employees who work during day shift. iii. Display details of those employees coming from Vacoas or Mahebourg. iv. List all employees, whose name start with letter "B" and ends with "e". v. Set salary of all supervisors to Rs 35 000. vi. Display the designation of the employees, whose salary is betweeen 30000 and 40000. vii. Display details of employees, where salary is 35000 or age is greater than 30. viii. List name and department of employees, where age is greater than 45 order by salary.
Write SQL query for following consider tableEMP(empno , deptno, ename ,salary, Designation, joiningdate, DOB,city) i)Display employees name and number in an increasing order of salary. ii)Display employee name andemployee number dept wise. iii)Display total salary of all employee. iv)Display number of employees dept wise. v)Display employee name having experience more than 3 years. vi)Display employee name staring with “S” and working in deptno 1002 DATABASE SYSTEM
Using SQL data definition language, write the SQL statements to create each of the following relations. i. student(student id, name, dept id, total credits)ii. department(dept id, building, budget)iii. lecture_hall(hall id, campus, capacity)iv. course(course id, title, dept id, credits) v. takes(student id, course id, semester, year, grade)
Write SQL statements to convert the ER diagram (see picture) to table definitions. Capture as many constraints as possible. State the constraints that your translation cannot capture, if any.
Make a relational diagram with the following data:1. Patient Entity:RUT (Primary Key): IntegerName: StringLast name: StringDate of Birth: DateEmail: StringAddress: StringTelephone numbers: StringPension System: String 2. Health Insurance Entity:Code (primary key): IntegerType: String 3. Medical entity:RUT (Primary Key): IntegerName: StringLast name: StringDate of Birth: DateSpecialties: StringYears of practice: Integer 4. Medical Specialty Entity:Code (primary key): IntegerSpecialty Name: String 5. Pharmaceutical company entity:Code (primary key): IntegerName: StringPhone number: String 6. Medication Entity:Business name (primary key): StringFormula: StringPharmaceutical company code (foreign key): Integer 7. Pharmacy Entity:Code (primary key): IntegerName: StringAddress: StringPhone number: String 8. Patient-Doctor Assignment Entity:Assignment ID (primary key): IntegerPatient RUT (foreign key): IntegerDoctor RUT (Foreign Key): IntegerStart date: DateEnd…
a: Draw relational model b: Normalize upto 3NF
Consider the following ER diagram for a social network application.Write the SQL-DDL statements, to create tables for the following ER diagram andcapture as many of the constraints as possible. Specify your decisions with the foreignkey constraints.
A valid SQL statement should be accepted by a standard SQL interpreter, whereas an invalid SQL statement should result in an error message. Indicate for each of the following SQL statement, whether it is a valid SQL statement or NOT a valid SQL statement. 1. SELECT * FROM ARTICLES WHERE ENDPAGE-STARTPAGE>8;2. SELECT * FROM ARTICLES WHERE ENDPAGE-STARTPAGE<0;3. SELECT SUM (TITLE) FROM ARTICLES;4. SELECT AVG(YEAR) FROM ARTICLES WHERE TITLE LIKE ‘M%’;5. SELECT COUNT(*) FROM ARTICLES GROUP BY YEAR;6. SELECT YEAR, COUNT(*) FROM ARTICLES WHERE COUNT(*)>10 GROUP BY YEAR;
The following question is based upon the Patient-Treatment relation which records the details of transactions occurring in a medical surgery. You may assume the data are representative. PATIENT ID PATIENT NAME PATIENT DOB CONSULT DATE ITEM NUMBER ITEM DESCRIPTION FEE PROVIDER NUMBER DOCTOR NAME 437 REARDON 4/08/1989 19/7/2020 A012 Standard 56 S55768 Leahy 437 REARDON 4/08/1989 20/7/2020 A012 Standard 56 D42433 Meuller 562 BILSTEIN 12/05/2001 20/7/2020 A013 Short 35 S55768 Leahy 361 WILSON 13/08/1997 20/7/2020 A014 Long 75 S55768 Leahy 895 LEMOS 13/10/1952 20/7/2020 A013 Short 35 D42433 Meuller 895 LEMOS 13/10/1952 20/7/2020 A014 Long 75 D42433 Meuller 678 MORTAL 27/02/1969 20/7/2020 A014 Long 75 S55768 Leahy 432 SOAPIER 1/08/1976 21/7/2020 A012 Standard 56 S55768 Leahy You have been asked to design a…

SEE MORE QUESTIONS

Recommended textbooks for you

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

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

SEE MORE TEXTBOOKS