Question

1. write SQL statements to create the following two tables:

a) Department table that contains the following columns(dept_no,deptname,location) set the deptno to be the primary key.
b) Employee table contains the following columns(emp_no,empname,deptno,salary)set the emp_no to be the primary key and dept_no to be the foreign key.

2. Write SQL statements to Insert the following 3 rows in Employee table:

       (101,’Sami’,’D-101’,5000)
       (102,’Yousef’,’D-101’,4000)
       (103,’Sami’,’D-102’,7000)

3. Write SQL statements to Insert the following 3 rows in Department table:
       (‘D-101’,’Marketing’,’loc1’)
       (‘D-102’,’Sales’,’loc2’)
       (‘D-103’,’Finance’,’loc3’)

4. Create a view for employee at marketing department.

Answer 1

1. a.)

CREATE TABLE Department(
    dept_no varchar(255) NOT NULL,
    deptname varchar(255),
    location varchar(255),
    PRIMARY KEY (dept_no ),
);

1. b.)

CREATE TABLE Employee(
    emp_no int NOT NULL,
    empname VARCHAR(255) NOT NULL,
    salary int,
    dept_no varchar(255) NOT NULL,
    PRIMARY KEY (emp_no),
    FOREIGN KEY (dept_no) REFERENCES Department(dept_no)
);

2.

INSERT INTO Employee (emp_no, empname, dept_no, salary)
VALUES (101,'Sami','D-101',5000),
       (102,'Yousef','D-101',4000),
       (103,'Sami','D-102',7000);

3.

INSERT INTO Department (dept_no, deptname, location)
VALUES ('D-101','Marketing','loc1'),
       ('D-102','Sales','loc2'),
       ('D-103','Finance','loc3');

4.

CREATE VIEW [Marketing employee] AS
SELECT Employee.empname , Employee.salary
FROM Employee inner join Department
ON Employee.dept_no = Department.dept_no
WHERE deptname = "Marketing";

To retreive/read the rows store in the above view, use the following SQL query :

SELECT * FROM [Marketing employee];