please use sql to answer the following question as the question shown below 3. Create a procedure, ADD_EMPLOYEE, to insert a new employee into the EMPLOYEEStable. The procedure should call a VALID_DEPTID function to check whether thedepartment ID specified for the new employee exists in the DEPARTMENTS table.a. Create a function called VALID_DEEPTID to validate a specified department ID anda BOOLEAN value of TRUE if the department exists.Dractb. Create the ADD_EMPLOYEE procedure to add an employee to the EMPLOYEES table.The row should be added to the EMPLOYEES table if the VALID_DEPTID functionreturms TRUE; otherwise, alert the user with an appropriate message. Provide thefollowing parameters:- first_name- last_nameemailjob: Use 'SA_REP' as the defaultmgr: Use 145 as the defaultsal: Use 1000 as the defaultcomm: Use o as the defaultdeptid: Use 30 as the defaultUse the EMPLOYEES_SEQ sequence to set the employee_id column- Set the hire_date column to TRUNC (SYSDATE)c. Call ADD_EMPLOYEE for the name Jane Harris in department 15, leaving otherparameters with their default values. What is the result?d. Add another employee named Joe Harris in department 80, leaving remaininparameters with their default values. What is the result?edem

Solution 3(a): SQL code : CREATE OR REPLACE FUNCTION valid_deptid(p_deptid IN…

3. Create a procedure, ADD_EMPLOYEE, to insert a new employee into the EMPLOYEES table. The procedure should call a VALID_DEPTID function to check whether the department ID specified for the new employee exists in the DEPARTMENTS table. a. Create a function called VALID_DEPTID to validate a specified department ID and a BOOLEAN value of TRUE if the department exists. Dract b. Create the ADD_EMPLOYEE procedure to add an employee to the EMPLOYEES table. The row should be added to the EMPLOYEES table if the VALID_DEPTID function returns TRUE; otherwise, alert the user with an appropriate message. Provide the following parameters: - first_name - last_name - email job: Use 'SA_REP' as the default mgr: Use 145 as the default sal: Use 1000 as the default comm: Use o as the default - deptid: Use 30 as the default - Use the EMPLOYEES_SEQ sequence to set the employee_id column - Set the hire_date column to TRUNC (SYSDATE) c. Call ADD_EMPLOYEE for the name Jane Harris in department 15, leaving other parameters with their default values. What is the result? d. Add another employee named Joe Harris in department 80, leaving remaining parameters with their default values. What is the result? my

3. Create a procedure, ADD_EMPLOYEE, to insert a new employee into the EMPLOYEES table. The procedure should call a VALID_DEPTID function to check whether the department ID specified for the new employee exists in the DEPARTMENTS table. a. Create a function called VALID_DEPTID to validate a specified department ID and a BOOLEAN value of TRUE if the department exists. Dract b. Create the ADD_EMPLOYEE procedure to add an employee to the EMPLOYEES table. The row should be added to the EMPLOYEES table if the VALID_DEPTID function returns TRUE; otherwise, alert the user with an appropriate message. Provide the following parameters: - first_name - last_name - email job: Use 'SA_REP' as the default mgr: Use 145 as the default sal: Use 1000 as the default comm: Use o as the default - deptid: Use 30 as the default - Use the EMPLOYEES_SEQ sequence to set the employee_id column - Set the hire_date column to TRUNC (SYSDATE) c. Call ADD_EMPLOYEE for the name Jane Harris in department 15, leaving other parameters with their default values. What is the result? d. Add another employee named Joe Harris in department 80, leaving remaining parameters with their default values. What is the result? my

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: Write insert statements to enter first two records into Table 1 as shown in above figure. b.…

A: To create a table: CREATE TABLE tablename ( ID int primary key, column2 datatype, column3…

Q: https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all DATA BASE Question 1: Write…

A: The SELECT command is utilized for finding and printing the tuples from a database table. It has…

Q: Write the SQL code that will compute the total number of hours worked and total amount billed by all…

A: To compute the total number of hours worked and total amount billed by all workers, we need to have…

Q: nt database. The primary keys are highlighted in bold. Student (studNo, studName, address,…

A: Below is part of a Student database. The primary keys are highlighted in bold. Student (studNo,…

Q: In each question below, you will define a new table based on the following tables.

A: The SQL question involves creating tables and sentences based on parent-child relationships, dog…

Q: Find the service ID and property ID for each service request whose estimated hours is greater than…

A: Hello student Greetings Hope you are doing great. Thank You!!!

Q: You are given the following data tables: Employees(Employeel D, LastName, FirstName, BirthDate,…

A: Given that there are two tables Employee and Orders. The attributes of each table are:…

Q: help with sql class 2 questions 1. Report the names of all authors and the title_name of the…

A: Let's see the answer:

Q: Complete PL/SQL function below, which will return last name of employee whose id is equals to 2…

A: The above question is solved in step 2:-

Q: The only required sections of a PL/SQL block are DECLARE and END. Answers: Yes No

A: Given:

Q: Using DML SQL, write the appropriate SQL query for each of the following: Insert A new customer…

A: In SQL, DML stands for Data manipulation language which contains Select, Insert, Delete and Update…

Q: List doctor’s details where speciality is either Dermatology or Neurology. (Use INoperator). List…

A: The given requirement is to be solved in database SQL where the DOCTOR table is already created with…

Q: Based on the following SQL statement: SELECT acct_id, balance FROM Account WHERE balance < 0 AND…

A: Number list = [12,9,2,5,-7,3]. Bubble sort: In bubble sort works by repeatedly swapping the adjacent…

Q: Database Q1: Select the correct SQL statement to provide the user Samer with read only permission…

A: Database: A database is an organized collection of structured data, generally stored and…

Q: IN SQL If you omitted the WHERE clause of SELECT statement, the query returns an error because the…

A: Answer True explanation because if the condition is given with the query then where clause is…

Q: what sql statements could be use to find the following: A simple database composed of the following…

A: 1. SELECT * FROM DOCTORS WHERE DOC_SPECIALTY IN ('Dermatology', 'Neurology') ORDER BY DOC_FNAME…

Q: What are some common uses for the STRING_AGG function in SQL?

A: The STRING _ AGG function in SQL is a function that allows you to combine rows of strings into a…

Q: In what situations might the SOUNDEX function be useful in SQL when querying text data?

A: The SOUNDEX function in SQL is useful in situations where you need to perform approximate matching…

Concept explainers

Multiple Table

In a database, a table is used to store data in the form of rows and columns. Rows are aligned horizontally and identified by one or more values in a specific column. Columns are vertically aligned and identified by a unique column name.

Question

100%

please use sql to answer the following question as the question shown below

3. Create a procedure, ADD_EMPLOYEE, to insert a new employee into the EMPLOYEES
table. The procedure should call a VALID_DEPTID function to check whether the
department ID specified for the new employee exists in the DEPARTMENTS table.
a. Create a function called VALID_DEEPTID to validate a specified department ID and
a BOOLEAN value of TRUE if the department exists.
Dract
b. Create the ADD_EMPLOYEE procedure to add an employee to the EMPLOYEES table.
The row should be added to the EMPLOYEES table if the VALID_DEPTID function
returms TRUE; otherwise, alert the user with an appropriate message. Provide the
following parameters:
- first_name
- last_name
email
job: Use 'SA_REP' as the default
mgr: Use 145 as the default
sal: Use 1000 as the default
comm: Use o as the default
deptid: Use 30 as the default
Use the EMPLOYEES_SEQ sequence to set the employee_id column
- Set the hire_date column to TRUNC (SYSDATE)
c. Call ADD_EMPLOYEE for the name Jane Harris in department 15, leaving other
parameters with their default values. What is the result?
d. Add another employee named Joe Harris in department 80, leaving remainin
parameters with their default values. What is the result?
edem

Expert Solution

This question has been solved!

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

This is a popular solution!

SEE SOLUTION Check out a sample Q&A here

Step 1

Step 2

Step 3

Step 4

Step 5

Step 6

Step 7

Step 8

Trending now

This is a popular solution!

Step by step

Solved in 8 steps with 4 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

Using DML SQL, write the appropriate SQL query for each of the following:a) Insert A new customer with the following information:(Name: Ali, SSN: 1111, Address: Riyadh, Phone: 05555)b) Delete all the loans for customer with SSN: 2222c) Select all customer who got loans more than 250000 SAR, print the customers SSN, named) Print all the Accounts in Bank: ALBILAD and in branch no: 5
I use the My SQL Workbench ,this question is for My SQL class ,can you provide me the solution using the My SQL workbench ?
PLZ hlp with the following: In SQL If you add the PRIMARY KEY constraint to a column, you must also add a UNIQUE constraint to it. Select one: True False
SQL homework Please help me with this homework
Write two SQL Queries that will show the ORDER# from both the Orderand OrderItems table using the UNION set operator
Describe the use of the CAST and CONVERT functions in SQL.
how do you use PL/SQL or T-SQL procedures in SQL?
In SQL Server, you may choose between three distinct character data types: NVARCHAR, NVARCHAR, and NVARCHAR.