Quick Learn | FAQ's | Technical Solutions

SQL Queries for learning: This application is used to keep track of information about employees of a company. It also stores the information about departments and leaves taken by employees. You are required to create tables (as shown below) and insert data into each of the table. Apart from giving you an idea about how to create tables with constraints, it also enables you to understand how to create queries, pl/sql programs, stored procedures and functions and database triggers. However, note, this sample collection of tables is only for learning purpose. Required Tables The following are the set of tables to be created to store the required information. Table Name Meaning DEPT Stores the details of departments of the company. EMPLOYEE Stores information about all the employees of the company. LEAVES Stores information about types of leaves available EMP_LEAVES Stores information about leaves taken by the employees. Structure of Tables The following is the structure of each of the req...

Normalization: Its a process of organizing data in database. This includes creating tables and establishing relationship between these tables. The result in database consistency, flexible and reduce redundancy. First Normal Form : Eliminate repeating group Second Normal Form : Eliminate redundant data, related table with foreign key Third Normal Form : Eliminate fields that do not depend on the key Employee & Department table scenario: DEPT (DEPTNO, DNAME, LOC) EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) DDL Create Scripts: create table dept (     deptno     number(2,0),     dname      varchar2(14),     loc        varchar2(13),     constraint pk_dept primary key (deptno)   ); create table emp (     empno    number(4,0),     ename    varchar2(10),     job      varchar2(9), ...

--**************************************************** --RETRIEVING DATA USING THE SQL SELECT STATEMENT --**************************************************** SELECT * FROM EMP; SELECT * FROM DEPT; --**************************************************** --RESTRICTING AND SORTING DATA --**************************************************** SELECT * FROM EMP WHERE DEPTNO = 10; SELECT * FROM EMP WHERE SAL > 800; SELECT * FROM EMP WHERE SAL >= 800; SELECT * FROM EMP WHERE DEPTNO IN (10,20); SELECT * FROM EMP WHERE MGR IS NULL; SELECT * FROM EMP WHERE HIREDATE BETWEEN '17-DEC-1980' AND '01-MAY-1981'; SELECT * FROM EMP WHERE SAL BETWEEN 1000 AND 2000; SELECT * FROM EMP WHERE ENAME LIKE '%MART%'; SELECT * FROM EMP ORDER BY ENAME DESC; SELECT * FROM EMP ORDER BY ENAME ASC; --**************************************************** --USING SINGLE – ROW FUNCTIONS TO CUSTOMIZE OUTPUT ---DUAL IS A DUMMY TABLE THAT YOU CAN USE TO VIEW RESULTS FROM FUNCTIONS AND CALCULATIONS....