The database(or the tables) are follow: CREATE TABLE employees ( id SERIAL NOT NULL PRIMARY KEY, name TEXT NOT NULL, salary REAL NOT NULL DEFAULT 25000.0 ); CREATE TABLE employee_audit_log ( employee_id INTEGER NOT NULL, occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ); INSERT INTO employees(name, salary) VALUES ('Arnold Schwarznegger', 35000), ('Yuri Gargarin', 27000), ('Anakin Skywalker', 450000), ('Said Faroghi', 15000), ('Zino Holwerda', 8500);
1. Create a trigger for the employees table so that a new row is
inserted in employee audit_log
to log an action (insert or update) performed on an employee.
2. Perform an insert statement which adds a new employee named,
"Poppy Harlow"
with a salary of 75,000. Also update any existing employees whose
salary
is below the default of 25,000 to be set to that default.
3. Get a list of the name and timestamp of any employees whose
information has been
changed. This should return the name of the new employee you've
just inserted,
and the others whose salary has changed.
For Creating Triggers:
Trigger for Update:
DELIMITER $$
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit_log(employee_id)
Values (New.id);
END$$
DELIMITER ;
Trigger for Insert:
DELIMITER $$
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit_log(employee_id)
Values (New.id);
END$$
DELIMITER ;
Insert Statement :
INSERT INTO employees(name, salary)
VALUES
('Poppy Harlow', 75000);
Update Statement:
UPDATE employees
SET salary = 25000
WHERE salary < 25000;
Query for displaying Names:
SELECT name,occurred_at
FROM employees
INNER JOIN employee_audit_log
ON employees.id = employee_audit_log.employee_id;
The database(or the tables) are follow: CREATE TABLE employees ( id SERIAL NOT NULL PRIMARY KEY,...
Database Management 6. [5] Create the following table: CREATE TABLE customer ( cust_name VARCHAR(30) NOT NULL, address VARCHAR(60), UNIQUE (cust name, address)); A. Run the following inserts and explain why both work and how will you prevent it INSERT INTO customer VALUES ('Alex Doe', NULL); INSERT INTO customer VALUES ('Alex Doe', NULL); 7. [5] Modify the following table definition to ensure that all employees have a minimum wage of $10 CREATE TABLE employee ( empId INT PRIMARY KEY, empName VARCHAR(40)...
a database of employees that corresponds to the employee-payroll hierarchy is provided (see employees.sql to create the employees for a MySQL database). Write an application that allows the user to: Add employees to the employee table. Add payroll information to the appropriate table for each new employee. For example, for a salaried employee add the payroll information to the salariedEmployees table 1 is the entity-relationship diagram for the employees database Figure 1: Table relationships in the employees database [1]. Add...
Use the Northwind database. Choose the best answer from those given below. Sometimes a database may have a separate table to log certain activities taking place in the database. Create a new table named ‘ChangeLog’ in the Northwind database as follows: ChangeID int identity(1,1) primary key EmpID int (will contain the ID of the employee being changed) User nvarchar(30) (will contain the login of the user making the change) Date smalldatetime (will contain the date of the change) OldRate money (will contain the old payrate of the employee) NewRate money (will contain...
Create the database and tables for the database. Show all SQL statements. Include primary and foreign keys. Insert data into each table. Show select statements and display the output of each table. Note:Student’s name must be inserted into table as part of the data! Perform the SQL below: Query one table and use WHERE to filter the results. The SELECT clause should have a column list, not an asterisk (*). State the purpose of the query; show the query and...
DROP TABLE EMPLOYEE; DROP TABLE JOB; DROP TABLE EMP; DROP TABLE EMP_1; DROP TABLE EMP_2; CREATE TABLE JOB(JOB_CODE CHAR (3) PRIMARY KEY, JOB_DESCRIPTION VARCHAR (20) NOT NULL,JOB_CHG_HOUR NUMBER (5,2) NOT NULL,JOB_LAST_UPDATE DATE NOT NULL); INSERT INTO JOB VALUES('500','Programmer','35.75','20-Nov-2017'); INSERT INTO JOB VALUES('501','System Analyst','96.75','20-Nov-2017'); INSERT INTO JOB VALUES('502','Database Designer','125.00','24-Mar-2018'); CREATE TABLE EMPLOYEE(EMP_NUM CHAR (3) PRIMARY KEY,EMP_LNAME VARCHAR (15) NOT NULL,EMP_FNAME VARCHAR (15) NOT NULL, EMP_INITIAL CHAR (1),EMP_HIREDATE DATE NOT NULL,JOB_CODE CHAR (3), EMP_YEARS NUMBER (2),FOREIGN KEY (JOB_CODE) REFERENCES JOB (JOB_CODE)); INSERT...
drop table department cascade constraints; create table department ( Dname varchar2(15) not null, Dnumber int not null, Mgr_ssn char(9) not null, mgr_start_date Date, primary key (Dnumber), Unique (Dname)); insert into DEPARTMENT values ('Research', '5', '333445555', '22-May-1988'); insert into DEPARTMENT values ('Administration', '4', '987654321', '01-Jan-1995'); insert into DEPARTMENT values ('Headquarters', '1', '888665555', '19-Jun-1981'); drop table employee cascade constraints; create table employee ( Fname varchar2(15) not null, Minit char(1), Lname varchar2(15) not null, Ssn char(9), Bdate date, Address varchar2(30), Sex char(1),...
4 Consider the following relational schema, DDL statements and tables: EMPLOYEE( EmployeeID, EmployeeName, SupervisorID, DepartmentID) PROJECT (ProjectID, EmployeeID) DEPARTMENT( Department ID, DepartmentName) CREATE TABLE EMPLOYEE ( EmployeeID INT PRIMARY KEY, EmployeeName VARCHAR(50) NOT NULL, SupervisorID INT DEFAULT 9, DepartmentID INT, FOREIGN KEY (SupervisorID) REFERENCES EMPLOYEE (EmployeeID) ON DELETE SET DEFAULT ), FOREIGN KEY (DepartmentID) REFERENCES DEPARTMENT(DepartmentID) ON UPDATE SET NULL); CREATE TABLE PROJECT ( ProjectID INT PRIMARY KEY, EmployeeID INT DEFAULT 9, FOREIGN...
Q2. Retrieve the names of all employees from the employee table to produce output on CSV format or delimited format with a common delimeter, rather than separete columns. Hint:Put the whole row into a string with a semicolon as the seperator(delimeter) between thecolumns: FORMAT:(fname;minit;lname) Example: EMPLOYEES -------------- James;E;Borg Frank;T;Wong Q3. Write a query to show the employees name from the employee table in this format: first letter of the first name, followed by a dot, a blank, and the full...
database and sql problme THE QUESTIONS 3, 4,5 and 6 REFER TO THE RELATIONAL TABLES LISTED BELOW CREATE TABLE Department ( DECIMAL(5) VARCHAR(30) CHAR(5) DATE NOT NULL NOT NULL NOT NULL * Department number /*Department name * Department manager number */ /Manager start date DNumber DName Manager MSDate CONSTRAINT Department_PK PRIMARY KEY(DNumber) CONSTRAINT Department_CK UNIQUE(DName) CREATE TABLE DeptLocation DECIMAL(5) VARCHAR(50) NOT NULL NOT NULL DNumber * Department number */ * Department location */ Address CONSTRAINT DeptLocation_PK PRIMARY KEY(DNumber, Address) CONSTRAINT...
Learning Objectives: Learn to define constraints on tables Lean to define primary keys and foreign keys Exercise In Lab 09, we will continue the practice of creating tables. You will need to write SQL statements for the tasks specified below. After you complete them, submit your SQL script (in one *.sql file) to Blackboard by 11:59pm Nov. 16 (Friday). Include your name as a comment in the SQL script submitted. The creation statement for each table is worth one point,...