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’
Question
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’
Solution
Sure, here are the SQL queries for your requirements:
(1) To create a view 'Emp_India' which contains the name, age and salary of Indian employees, you can use the following SQL query:
CREATE VIEW Emp_India AS
SELECT E.Name, E.Age, E.Salary
FROM Employee E
JOIN Department D ON E.EID = D.EID
WHERE D.Country = 'India';
(2) To display the name of employees in descending order whose Country starts with ‘I’, you can use the following SQL query:
SELECT E.Name
FROM Employee E
JOIN Department D ON E.EID = D.EID
WHERE D.Country LIKE 'I%'
ORDER BY E.Name DESC;
In the first query, we are creating a view named 'Emp_India' which will contain the name, age, and salary of all employees who are from India. We achieve this by joining the 'Employee' and 'Department' tables on the 'EID' column and filtering out the employees whose country is 'India'.
In the second query, we are selecting the names of all employees whose country starts with 'I'. We achieve this by joining the 'Employee' and 'Department' tables on the 'EID' column, filtering out the employees whose country starts with 'I' and then ordering the result in descending order by the employee's name.
Similar Questions
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;
Write queries for the following tables:T1 ( Empno, Ename , Salary, Designation)T2 (Empno, Deptno.)(1) Display the Deptno in which Employee Seeta is working.(2) Display Empno, Ename, Deptno.
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
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
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;
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.