Question

HELP DATABASE QU

Question 1: Think About the relational database table data as given below, write the following queries in Oracle SQL. Company (Cid, Cname, City, Budget, Branch) Department (Deptno, Dname, Building, Cname) Employee (Ename, Deptno, Street, City, Phone, Salary) Works (Ename, Deptno, Hire_date) Location (Cname, Location ) 1. Write Query to Create the Company table, suggest appropriate data type of each attribute, consider that there should be a name for each company. 2. Find employee name/names who live in Seattle city whose salary is/are greater than 20,000 USD. 1.3. Create the view with name Emp_info to know all employee's data, but not their salary. How can we do that by a creating a view?

Answer 1

1. Below is the SQL query where we define table Company with CID of type INT and set as primary key, Cname as string with 50 characters and it cannot be NULL, then we define City of type string with 50 characters, then Budget is of type INT and finally Branch is of type string with 50 characters. CREATE TABLE Company ( CID INT PRIMARY KEY, Cname VARCHAR(50) NOT NULL, City VARCHAR(50) ,   Budget INT, Branch VARCHAR(50) );

2.  

Select Ename from Employee

Where City = "Seattle" and Salary > 20000 ;

3.  

CREATE VIEW Emp_info  AS
SELECT Ename, Deptno , Street, City, Phone
FROM Employee;

Here we created view name Emp_info where we highlighted all details of Employee except Salary by importing all the attributes of table Employee except Salary.

Please comment for any clarification.