Data Requirements The database for the system should include information of comp
ID: 3801732 • Letter: D
Question
Data Requirements
The database for the system should include information of company’s staff, respectively of its employees. The data is subdivided into the following groups:
Employee Details
Employee Salary
Employee Dept.
Title
Qualifications
Employee ID
Employee ID
DeptID
TitleID
QualID
Personal ID
SalaryID
DeptDate
EmployeeID
EmployeeID
FirstName
Currency
From Date
Experience
Middle Name
Monthly Salary
ToDate
LastName
From date
To Date
To Date
Gender
Date ofBirth
Hire Date
Using the data tale above;
Refer to your project and explain different types of SQL (DDL, DML & DCL) with suitable examples. Post your ERD for ease of understanding by fellow class-members.
2. What is a view? What is it used for? Give examples when a view can be used for your project.
Employee Details
Employee Salary
Employee Dept.
Title
Qualifications
Employee ID
Employee ID
DeptID
TitleID
QualID
Personal ID
SalaryID
DeptDate
EmployeeID
EmployeeID
FirstName
Currency
From Date
Experience
Middle Name
Monthly Salary
ToDate
LastName
From date
To Date
To Date
Gender
Date ofBirth
Hire Date
Explanation / Answer
DDL Commnds:Create ,Alter ,Drop
DML:Insert, Update, Delete,Select
DCL:Commit,Rollback,Savepoint.
Syntax :SELECT CLAUSE
SELECT <column_list>
FROM < table name >
WHERE <search_condition>
GROUP BY <columns>
[HAVING] <search_condition>
[ORDER BY {order_expression [ASC | DESC]}[, ...]];
CREATE TABLES:
// CREATE TABLE EMPLOYEE DETAILS
SQL> create table Emp_Details(Emp_ID int ,Personal_ID int ,FName varchar2(15),MN
ame varchar2(15),LName varchar2(10),To_Date date,Gender varchar2(3),Date_of_Birt
h date,Hire_Date date, primary key(emp_id,personal_id));
Table created.
// CREATE TABLE EMPLOYEE SALARY
SQL> create table EmpSalary(Emp_ID int,pid int,SalaryID int primary key,Currency varchar2(15),Monthly_Salary int,From_date date,to_date date, foreign key(emp_id
,pid) references emp_Details);
Table created.
// CREATE TABLE EMPLOYEE DEPT
SQL> create table Empl_Dept(Title varchar2(10),TitleID int ,EmpID int,pid int,From_Date date,To_Date date,primary key(title,titleid),foreign key(empid,pid) refe
rences emp_details);
Table created.
// CREATE TABLE QUALIFICATION
SQL> create table Qualification(QualID int primary key,EmpID int,pid int,Experience int, foreign key(empid,pid) references emp_details);
Table created.
Insert the values into tables
// INSERT THE VALUES IN EMP DATAILS TABLE
SQL> insert into emp_details values(101,222,'john','smith','oreilly','20-oct-2000','M','18-may-1998','10-mar-2003');
1 row created.
SQL>
insert into emp_details values(102,333,'madhu','rao','beily','20-nov-2001','F','12-dec-1998','10-feb-2003');
1 row created.
SQL> insert into emp_details values(103,444,'mohit','babu','deepati','10-apr-2010','M','08-jan-1999','10-jun-2004');
1 row created.
// INSERT VALUES IN EMP SALARY TABLE
SQL> insert into empsalary values (101,222,100,'$200',20000,'22-feb-2017','22-mar-2017');
1 row created.
SQL> insert into empsalary values (102,333,200,'$400',30000,'15-feb-2015','20-jun-2016');
1 row created.
SQL> insert into empsalary values (103,444,300,'$700',60000,'01-aug-2015','24-sep-2014');
1 row created.
// insert the values into employee department table
SQL> insert into empl_dept values ('cs',501,101,222,'22-feb-2017','21-mar-2016');
1 row created.
SQL> insert into empl_dept values ('eee',201,102,333,'14-jan-2015','21-mar-2016');
1 row created.
SQL> insert into empl_dept values ('business',801,103,444,'16-jan-2013','12-dec-2014');
1 row created.
SQL> commit;
Commit complete.
// INSERT THE VALUES IN QUALIFICATION TABLE
SQL> insert into qualification values(1,102,333,5);
1 row created.
SQL> insert into qualification values(2,101,222,10);
1 row created.
SQL> insert into qualification values(5,103,444,15);
1 row created.
SQL> insert into qualification values(4,101,222,15);
1 row created.
SQL> insert into qualification values(1,102,333,5);
1 row created.
SQL> insert into qualification values(2,101,222,10);
1 row created.
SQL> insert into qualification values(5,103,444,15);
1 row created.
SQL> insert into qualification values(4,101,222,15);
1 row created.
// DCL COMMANDS
SQL> commit;
Commit complete.
RETRIVE THE DATA FROM TABLES: SELECT / FROM /TABLE
SQL> select * from emp_details;
EMP_ID PERSONAL_ID FNAME MNAME LNAME TO_DATE GEN
---------- ----------- --------------- --------------- ---------- --------- ---
DATE_OF_B HIRE_DATE
--------- ---------
101 222 john smith oreilly 20-OCT-00 M 18-MAY-98 10-MAR-03
102 333 madhu rao beily 20-NOV-01 F 12-DEC-98 10-FEB-03
103 444 mohit babu deepati 10-APR-10 M 08-JAN-99 10-JUN-04
SQL> select * from empsalary;
EMP_ID PID SALARYID CURRENCY MONTHLY_SALARY FROM_DATE TO_DATE
---------
101 222 100 $200 20000 22-FEB-17 22-MAR-17
102 333 200 $400 30000 15-FEB-15 20-JUN-16
103 444 300 $700 60000 01-AUG-15 24-SEP-14
SQL> select * from empl_dept;
TITLE TITLEID EMPID PID FROM_DATE TO_DATE
---------- ---------- ---------- ---------- --------- ---------
cs 501 101 222 22-FEB-17 21-MAR-16
eee 201 102 333 14-JAN-15 21-MAR-16
business 801 103 444 16-JAN-13 12-DEC-14
SQL> select * from qualification;
QUALID EMPID PID EXPERIENCE
---------- ---------- ---------- ----------
1 102 333 5
2 101 222 10
5 103 444 15
4 101 222 15
SQL> update empsalary set monthly_salary=monthly_salary+200;
2 rows updated.
SQL> select * from empsalary;
EMP_ID PID SALARYID CURRENCY MONTHLY_SALARY FROM_DATE TO_DATE
102 333 200 $400 30200 15-FEB-15 20-JUN-16
103 444 300 $700 60200 01-AUG-15 24-SEP-14
SQL> update empsalary set monthly_salary=monthly_salary+500 where monthly_salary>50000;
1 row updated.
SQL> select * from empsalary;
EMP_ID PID SALARYID CURRENCY MONTHLY_SALARY FROM_DATE TO_DATE
102 333 200 $400 30200 15-FEB-15 20-JUN-16
103 444 300 $700 60700 01-AUG-15 24-SEP-14
// DELETE
SQL> delete from qualification where qualid=4;
1 row deleted.
2. VIEWS
To create, update, and drop VIEWS.
A view is virtual table or temporary table .does not exist physically. Views joins one or more table data.
A view has rows and columns,it is a logical structre.
There are two types of views they are : Simple View and Complex View
Simple View: a simple view created from only one base table, in simple view not allow functions and groups of data.
Complex View
A view can create from more than one base table .it having functions and group of data.
Create View Syntax :
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW <view name> AS <subquery> [WITH READ ONLY];
// create read only view or simple view. a view with read-only option to restrict access to the view
SQL> Create or replace view emp_view as select * from emp_details with read only;
View created.
SQL> Create or replace view emp_qualifi as select * from qualification with read only;
View created.
SQL> insert into emp_qualifi values(4,101,222,15);
insert into emp_qualifi values(4,101,222,15)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
// UPDATE VIEW: To modify the data virtual table allow is called updatable view.
In update view allow insert or update or delete the values in virtual table that effect original table.
SQL> Create or replace view emp_qualifi as select * from qualification ;
View created.
SQL> select * from emp_qualifi;
QUALID EMPID PID EXPERIENCE
---------- ---------- ---------- ----------
1 102 333 5
2 101 222 10
5 103 444 15
SQL> Update emp_qualifi set experience=20 where qualid=1;
1 row updated.
SQL> select * from emp_qualifi;
QUALID EMPID PID EXPERIENCE
---------- ---------- ---------- ----------
1 102 333 20
2 101 222 10
5 103 444 15
SQL> insert into emp_qualifi values(4,101,222,15);
1 row created.
SQL> select * from emp_qualifi;
QUALID EMPID PID EXPERIENCE
---------- ---------- ---------- ----------
1 102 333 20
2 101 222 10
5 103 444 15
4 101 222 15
// DROP VIEW: To drop view use drop command
Syntax: DROP VIEW < view_name>;
SQL> Drop view emp_qualifi;
View dropped.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.