Write a query for the following.employee(eid,name, salary, address) department(d_id, d_name, eid)Give Primary Key, foreign key constraints after creating table with constraint names in given schemas.(1) Create a view department_details of department table.(2) Join two existing tables using inner join.(3) To drop a view
Question
Write a query for the following.employee(eid,name, salary, address) department(d_id, d_name, eid)Give Primary Key, foreign key constraints after creating table with constraint names in given schemas.(1) Create a view department_details of department table.(2) Join two existing tables using inner join.(3) To drop a view
Solution
Sure, here are the SQL queries for your requirements:
(1) Creating tables with primary key and foreign key constraints:
CREATE TABLE employee (
eid INT PRIMARY KEY,
name VARCHAR(100),
salary DECIMAL(10, 2),
address VARCHAR(255)
);
CREATE TABLE department (
d_id INT PRIMARY KEY,
d_name VARCHAR(100),
eid INT,
FOREIGN KEY (eid) REFERENCES employee(eid)
);
(2) Creating a view department_details of department table:
CREATE VIEW department_details AS SELECT * FROM department;
(3) Joining employee and department tables using inner join:
SELECT * FROM employee
INNER JOIN department
ON employee.eid = department.eid;
(4) Dropping a view:
DROP VIEW department_details;
Please replace the data types and sizes according to your requirements.
Similar Questions
Write query for the following.employee(id,name,salary,address)department(d_id,d_name,id)(1) Create a view department_details of department table.(2) To join two existing tables using inner join.(3) To drop a view
Write queries for the following.Employee ( EID, Name, Age, Salary)Department ( DID, D_Name, EID,Country)(1) Create a view Emp_India which contains the name, age and salary of Indianemployees.(2) Display the name of employee in descending order whose Country starts with ‘I’
The following view statements contain the name, job title and the annual salary of employees working in the department 20: (Choose the correct)Select one:a.create view DEPT20 isselect ENAME, JOB, SAL*12 ANNUAL SALARY from EMPwhere DEPTNO = 20;b.create view DEPT20 asselect ENAME, JOB, SAL*12 ANNUAL SALARY from EMPwhere DEPTNO <> 20;c.create view DEPT20 asselect ENAME, JOB, SAL*12 ANNUAL SALARY from EMPwhere DEPTNO = 20;
Which of the following SQL statements will create a view that lists only the employees in department number 7?1 pointCREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)AS SELECT EMP_ID, F_NAME, L_NAMEFROM EMPLOYEES WHERE DEP_ID = 7;CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)AS SELECT EMP_ID, F_NAME, L_NAMEWHERE DEP_ID = 7;CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)AS SELECT EMP_ID, F_NAME, L_NAMEFROM EMPLOYEES IF DEP_ID = 7;CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)WHERE DEP_ID = 7AS SELECT EMP_ID, F_NAME, L_NAMEFROM EMPLOYEES;
Which of the following SQL statements will create a view named EMP_VIEW with an employee’s First name, last name, and ID, based on the EMPLOYEES tables?1 pointCREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)AS SELECT EMP_ID, F_NAME, L_NAMEFROM EMPLOYEES; CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)FROM EMPLOYEES; CREATE VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)AS SELECT EMP_ID, F_NAME, L_NAME;NEW VIEW EMP_VIEW (EMP_ID, FIRSTNAME, LASTNAME)AS SELECT EMP_ID, F_NAME, L_NAMEFROM EMPLOYEES;
Upgrade your grade with Knowee
Get personalized homework help. Review tough concepts in more detail, or go deeper into your topic by exploring other relevant questions.