theachoem commented 3 years ago

I. Question?

1. Should we always have a foreign key in the table? Why and Why not?

Foreign Keys make life so much easier when using report builders and data analysis tools. Just select one table, check the include related tables box and BAM! you've got you're report built. Ok Ok, it's not that easy, but they certianly save time in that respect.

Yes, you should. Foreign keys are just constrains which helps you to make relationships and be sure that you have correct information in your database. You should use them to prevent incorrect data entry from whatsoever

2. What is Views?

Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain condition.
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;
3. Explain by your understanding about the different between Views and Tables

Table: Table is a preliminary storage for storing data and information in RDBMS. A table is a collection of related data entries and it consists of columns and rows.

View: A view is a virtual table whose contents are defined by a query. Unless indexed, a view does not exist as a stored set of data values in a database. Advantages over table are

We can combine columns/rows from multiple table or another view and have a consolidated view. Views can be used as security mechanisms by letting users access data through the view, without granting the users permissions to directly access the underlying base tables of the view It acts as abstract layer to downstream systems, so any change in schema is not exposed and hence the downstream systems doesn't get affected.

5. What is the different between implicit Cursors and Explicit Cursors?

Reference: geeksforgeeks

II. SQL

1. Write a SQL query to find the total employees in department 30.

2. Write a SQL query to find the total salary that we need to pay for all employees.

3. Display the last name and department number of all employees in departments 20 or 50 in ascending alphabetical order by name

4. Write a query to display the first name and salary of any employee in the same department as Zlotkey

5. Write a query to find the hire_date of the first employees;

III. PL/SQL

1. Write a PL/SQL query to find the department name that cost the least salary

2. Write a trigger to display the old salary and the new salary whenever there is a change on salary of employees

3. Write a PL/SQL to increase the salary of all employees who work more than 10 years by 20%

theachoem commented 3 years ago

Assignment 3

Reference: https://drive.google.com/file/d/1VXJ5yFC6qnD7fGNvW83qaCGwFiKWvXgV/view background1

1. List all departments

SELECT dept_name FROM departments;

2. List all employees in department Finance

SELECT first_name, last_name
FROM dept_emp
    JOIN departments ON dept_emp.dept_no = departments.dept_no
    JOIN employees ON dept_emp.emp_no = employees.emp_no
WHERE dept_name = 'Finance';

SELECT
    emp.emp_no,
    emp.first_name,
    emp.last_name,
    emp.gender
FROM
    employees emp,
    departments dept,
    dept_emp
WHERE
    emp.emp_no = dept_emp.emp_no
    AND dept_emp.dept_no = dept.dept_no
    AND dept.dept_name = "Finance";

My answer:

SELECT e.first_name, d.dept_name 
FROM employees e 
    INNER JOIN dept_emp dep 
    USING(emp_no) 
    INNER JOIN departments d 
    USING(dept_no)
WHERE d.dept_name = "Finance";

3. List 10 employees who are currently work in department Finance

SELECT first_name, last_name
FROM dept_emp
    JOIN departments ON dept_emp.dept_no = departments.dept_no
    JOIN employees ON dept_emp.emp_no = employees.emp_no
WHERE dept_name = 'Finance'
LIMIT 10;

SELECT
    emp.emp_no,
    emp.first_name,
    emp.last_name,
    emp.gender
FROM
    employees emp,
    departments dept,
    dept_emp
WHERE
    emp.emp_no = dept_emp.emp_no
    AND dept_emp.dept_no = dept.dept_no
    AND dept.dept_name = "Finance"
    AND CURRENT_DATE() > dept_emp.from_date
    AND CURRENT_DATE() < dept_emp.to_date
LIMIT 10;

My answer:

SELECT e.first_name, d.dept_name, dep.to_date 
FROM employees e 
    INNER JOIN dept_emp dep 
    USING(emp_no) 
    INNER JOIN departments d 
    USING(dept_no) 
WHERE d.dept_name = "Finance" 
AND dep.to_date > cast(now() as date) 
LIMIT 10;

4. List all Female employees

SELECT * FROM employees
WHERE gender = 'F';

5. Count the number of female employees in department Finance

SELECT COUNT(*)
FROM
    employees emp,
    departments dept,
    dept_emp
WHERE
    emp.emp_no = dept_emp.emp_no
    AND dept_emp.dept_no = dept.dept_no
    AND dept.dept_name = "Finance"
    AND gender = 'F';

SELECT COUNT(*)
FROM
    dept_emp
    JOIN employees ON dept_emp.emp_no = employees.emp_no
    JOIN departments ON dept_emp.dept_no = departments.dept_no
WHERE 
    gender = 'F' AND 
    dept_name = 'Finance';

My answer:

SELECT count(e.first_name) as ‘Total (F)’ 
FROM employees e 
    INNER JOIN dept_emp dep 
    USING(emp_no) 
    INNER JOIN departments d 
    USING(dept_no) 
WHERE d.dept_name = "Finance" 
AND e.gender = 'F';

6. List the employee who has the highest salary in department Finance

SELECT
    employees.emp_no,
    first_name,
    last_name,
    salary
FROM
    employees
    JOIN salaries ON employees.emp_no = salaries.emp_no
WHERE
    salary = (
        SELECT MAX(salary)
        FROM dept_emp
            JOIN salaries ON 
            dept_emp.emp_no = salaries.emp_no
            JOIN departments ON 
            dept_emp.dept_no = departments.dept_no
        WHERE
            salaries.to_date >= CURRENT_DATE()
            AND dept_name = 'Finance'
    );

My answer:

SELECT count(e.first_name) as 'Total Female in dept Finance' 
FROM employees e 
    INNER JOIN dept_emp dep 
    USING(emp_no) 
    INNER JOIN departments d 
    USING(dept_no) 
WHERE d.dept_name = "Finance" 
AND e.gender = 'F';

7. Count the number of employee in each department

SELECT dept_name, COUNT(*)
FROM
    dept_emp
    JOIN departments ON 
    dept_emp.dept_no = departments.dept_no
GROUP BY dept_name;

SELECT
    dept.dept_name,
    COUNT(emp.emp_no)
FROM
    employees emp,
    departments dept,
    dept_emp
WHERE
    emp.emp_no = dept_emp.emp_no
    AND dept_emp.dept_no = dept.dept_no
GROUP BY
    dept_name;

My answer:

SELECT 
    COUNT(e.emp_no) as 'Total employees', 
    d.dept_name as "Department name" 
FROM employees e 
    INNER JOIN dept_emp dep 
    USING(emp_no) 
    INNER JOIN departments d 
    USING(dept_no) 
GROUP BY d.dept_no;

8. List the employee who has the highest salary

SELECT
    emp.emp_no,
    emp.first_name,
    emp.last_name,
    emp.gender,
    salaries.salary
FROM
    employees emp,
    salaries
WHERE
    emp.emp_no = salaries.emp_no
    AND salaries.salary = (
        SELECT MAX(salary)
        FROM salaries
    );

SELECT
    first_name,
    last_name,
    salary
FROM
    employees
    JOIN salaries ON employees.emp_no = salaries.emp_no
WHERE
    salary = (
        SELECT MAX(salary)
        FROM salaries
    );

My answer:

SELECT e.first_name, s.salary 
FROM employees e 
    INNER JOIN salaries s 
    USING(emp_no) 
ORDER BY s.salary 
DESC LIMIT 10;

9. List the female employee who get the highest salary

SELECT
    first_name,
    last_name,
    gender,
    salary
FROM
    employees
    JOIN salaries ON employees.emp_no = salaries.emp_no
WHERE
    salary = (
        SELECT MAX(salary)
        FROM
            salaries
            JOIN employees ON salaries.emp_no = employees.emp_no
        WHERE gender = 'F'
    );

SELECT
    emp.emp_no,
    emp.first_name,
    emp.last_name,
    emp.gender,
    salaries.salary
FROM
    employees emp,
    salaries
WHERE
    emp.emp_no = salaries.emp_no
    AND emp.gender = 'F'
    and salaries.salary = (
        SELECT
            MAX(salary)
        FROM
            employees emp,
            salaries
        WHERE
            emp.emp_no = salaries.emp_no
            AND emp.gender = 'F'
    );

My answer:

SELECT e.first_name, s.salary 
FROM employees e 
    INNER JOIN salaries s 
    USING(emp_no) 
WHERE e.gender = "F" 
ORDER BY s.salary 
DESC LIMIT 10;

10. List the department that pay the highest salary

SELECT
    dept_name,
    salary
FROM
    departments dept,
    dept_emp,
    employees emp,
    salaries
WHERE
    emp.emp_no = dept_emp.emp_no
    AND dept_emp.dept_no = dept.dept_no
    AND salaries.emp_no = emp.emp_no
    AND salary = (
        SELECT MAX(salary)
        FROM salaries
    );

SELECT
    dept_name,
    salary
FROM
    dept_emp
    JOIN employees ON dept_emp.emp_no = employees.emp_no
    JOIN departments ON dept_emp.dept_no = departments.dept_no
    JOIN salaries ON dept_emp.emp_no = salaries.emp_no
WHERE
    salary = (
        SELECT MAX(salary)
        FROM salaries
    );

11. List the manager of each department

SELECT
    d.dept_name,
    e.first_name,
    e.last_name,
    dm.to_date
FROM
    employees e,
    departments d,
    dept_manager dm
WHERE
    e.emp_no = dm.emp_no
    AND d.dept_no = dm.dept_no
    AND dm.to_date >= curdate()
GROUP BY d.dept_no

12. List all previous managers of each department

SELECT
    d.dept_name,
    e.first_name,
    e.last_name,
    dm.to_date
FROM
    employees e,
    departments d,
    dept_manager dm
WHERE
    e.emp_no = dm.emp_no
    AND d.dept_no = dm.dept_no
    AND dm.to_date < curdate()
GROUP BY d.dept_no

13. Get the salary of each department manager

SELECT
    d.dept_name,
    e.first_name,
    e.last_name,
    dm.to_date,
    s.salary
FROM
    employees e,
    departments d,
    dept_manager dm,
    salaries s
WHERE
    e.emp_no = dm.emp_no
    AND d.dept_no = dm.dept_no
    AND dm.to_date >= curdate()
    AND e.emp_no = s.emp_no
GROUP BY d.dept_no

14. List all title in department Finance

SELECT
    distinct t.title
FROM
    employees e,
    departments d,
    titles t,
    dept_emp de
WHERE
    d.dept_no = de.dept_no
    AND e.emp_no = t.emp_no
    AND de.emp_no = e.emp_no
    AND d.dept_name = "Finance";

SELECT
    DISTINCT title,
    dept_name
FROM
    titles
    JOIN (
        SELECT
            employees.emp_no,
            dept_name
        FROM
            employees
            JOIN dept_emp ON dept_emp.emp_no = employees.emp_no
            JOIN departments ON dept_emp.dept_no = departments.dept_no
        WHERE
            dept_name = 'Finance'
    ) finance_emp ON titles.emp_no = finance_emp.emp_no;

15. List the employees who work in more than one department

SELECT
    e.first_name,
    e.last_name,
    COUNT(d.dept_no) as number_dep
FROM
    employees e,
    departments d,
    dept_emp dp
WHERE
    e.emp_no = dp.emp_no
    AND d.dept_no = dp.dept_no
GROUP BY e.emp_no
HAVING COUNT(d.dept_no) > 1;

16. List the employees who has more than one title

SELECT
    e.first_name,
    e.last_name,
    COUNT(t.title) as num_title
FROM
    employees e,
    titles t
WHERE e.emp_no = t.emp_no
GROUP BY e.emp_no
HAVING COUNT(t.title) > 1;

theachoem commented 3 years ago

Today Exam Preparation:

1. Data Manipulation

The Data Manipulation Language (DML) includes the major commands, INSERT, UPDATE, DELETE, and SELECT…INTO. Today's article focuses specifically on the uber-useful INSERT, UPDATE, DELETE, and SELECT statements.

Reference: Read more

2. View

Views syntax

CREATE VIEW `view_name` AS SELECT statement;

CREATE VIEW `accounts_v_members` AS SELECT `membership_number`,`full_names`,`gender` FROM `members`;

SHOW CREATE VIEW `accounts_v_members`;

DROP VIEW ` general_v_movie_rentals `;

Reference: Read more

3. PL/SQL CONDITIONAL CONTROL, LOOPS

Loop

Basic Loop Statement

DECLARE 
a NUMBER:=1; 
BEGIN
dbms_output.put_line('Program started.');
LOOP
dbms_output.put_line(a);
a:=a+1;
EXIT WHEN a>5;
END LOOP;
dbms_output.put_line('Program completed');
END:
/

Labeling of Loops

DECLARE
a NUMBER:=0;
b NUMBER;
upper-limit NUMBER :=4;
BEGIN
dbms_output.put_line(‘Program started.' ); 
«outerloop»‭    ‬
LOOP 
a:=a+1;
b:=l;
«inner loop»
LOOP
EXIT outer_loop WHEN a > upper_limit;
dbms_output.put_line(a);
b:=b+l;
EXIT inner_loop WHEN b>a;
END LOOP;
END LOOP;
dbms_output.put_line('Program completed.');
END;
/

Reference: Read more

- Condition Control

Oracle provides the following types of decision making statements.

IF-THEN
IF-THEN-ELSE
IF-THEN-ELSIF
NESTED-IF
CASE
SEARCHED CASE

IF-THEN

DECLARE 
a NUMBER :=10;
BEGIN
dbms_output.put_line(‘Program started.' );
IF( a > 100 ) THEN
dbms_output.put_line('a is greater than 100'); 
END IF;
dbms_output.put_line(‘Program completed.');
END;
/

IF-THEN-ELSE

DECLARE 
a NUMBER:=11;
BEGIN
dbms_output.put_line (‘Program started');
IF( mod(a,2)=0) THEN 
dbms_output.put_line('a is even number' ); 
ELSE
dbms_output.put_line('a is odd number1); 
END IF;
dbms_output.put_line (‘Program completed.’);
END;
/

IF-THEN-ELSIF

DECLARE
mark NUMBER :=55;
BEGIN
dbms_output.put_line(‘Program started.’ );
IF( mark >= 70) THEN 
dbms_output.put_line(‘Grade A’);
ELSIF(mark >= 40 AND mark < 70) THEN
dbms_output.put_line(‘Grade B'); 
ELSIF(mark >=35 AND mark < 40) THEN
dbms_output.put_line(‘Grade C’);
END IF;
dbms_output.put_line(‘Program completed.’); 
END;
/

NESTED-IF

DECLARE 
a NUMBER :=10; 
b NUMBER :=15; 
c NUMBER :=20;
BEGIN
dbms_output.put_line(‘Program started.' );
IF( a > b)THEN
/*Nested-if l */
dbms_output.put_line(’Checking Nested-IF 1'); 
IF( a > c ) THEN
dbms_output.put_line(‘A is greatest’); 
ELSE
dbms_output.put_line(‘C is greatest’); 
END IF;
ELSE
/*Nested-if2 */
dbms_output.put_line('Checking Nested-IF 2' ); 
IF( b > c ) THEN
dbms_output.put_line(’B is greatest' ); 
ELSE
dbms_output.put_line(’C is greatest' ); 
END IF;
END IF;
dbms_output.put_line(‘Program completed.’ );
END;
/

CASE

DECLARE
a NUMBER :=55;
b NUMBER :=5;
arth_operation VARCHAR2(20) :='MULTIPLY’;
BEGIN
dbms_output.put_line(‘Program started.' );
CASE (arth_operation)
WHEN ‘ADD’ THEN dbms_output.put_line(‘Addition of the numbers are: '|| a+b );
WHEN ‘SUBTRACT' THEN dbms_output.put_line(‘Subtraction of the numbers are: '||a-b ); 
WHEN ‘MULTIPLY' THEN dbms_output.put_line(‘Multiplication of the numbers are: '|| a*b
);
WHEN ‘DIVIDE' THEN dbms_output.put_line(‘Division of the numbers are:'|| a/b);
ELSE dbms_output.put_line(‘No operation action defined. Invalid operation');
END CASE;
dbms_output.put_line(‘Program completed.' );
END;
/

Reference: Read more

4. PL/SQL Precedure, Functions, Cursor, Triggers

- Precedure

CREATE OR REPLACE PROCEDURE greetings 
AS 
BEGIN 
dbms_output.put_line('Hello World!'); 
END; 
/

EXECUTE greetings;

The procedure can also be called from another PL/SQL block −

BEGIN 
greetings; 
END; 
/

DROP PROCEDURE greetings;

Parameter

DECLARE 
a number; 
b number; 
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS 
BEGIN 
IF x < y THEN 
  z:= x; 
ELSE 
  z:= y; 
END IF; 
END;   
BEGIN 
a:= 23; 
b:= 45; 
findMin(a, b, c); 
dbms_output.put_line(' Minimum of (23, 45) : ' || c); 
END; 
/

Reference: Read more + IN OUT

- Functions

CREATE OR REPLACE FUNCTION totalCustomers 
RETURN number IS 
total number(2) := 0; 
BEGIN 
SELECT count(*) into total 
FROM customers; 

RETURN total; 
END; 
/

Calling a Function

DECLARE 
c number(2); 
BEGIN 
c := totalCustomers(); 
dbms_output.put_line('Total no. of Customers: ' || c); 
END; 
/

Recursive Function


DECLARE 
num number; 
factorial number;

FUNCTION fact(x number) RETURN number
IS f number; BEGIN IF x=0 THEN f := 1; ELSE f := x * fact(x-1); END IF; RETURN f; END;

BEGIN num:= 6; factorial := fact(num); dbms_output.put_line(' Factorial '|| num || ' is ' || factorial); END; /

Reference: [Read more](https://www.tutorialspoint.com/plsql/plsql_functions.htm)
### - Cursor
**Implicit Cursors**
```sql
DECLARE  
   total_rows number(2); 
BEGIN 
   UPDATE customers 
   SET salary = salary + 500; 
   IF sql%notfound THEN 
      dbms_output.put_line('no customers selected'); 
   ELSIF sql%found THEN 
      total_rows := sql%rowcount;
      dbms_output.put_line( total_rows || ' customers selected '); 
   END IF;  
END; 
/

Explicit Cursors Declare:

CURSOR c_customers IS 
   SELECT id, name, address FROM customers;

Open a cursor:

OPEN c_customers;

Fetch a cursor:

FETCH c_customers INTO c_id, c_name, c_addr;

Close a cursor:

CLOSE c_customers;

Example:

DECLARE 
   c_id customers.id%type; 
   c_name customer.name%type; 
   c_addr customers.address%type; 
   CURSOR c_customers is 
      SELECT id, name, address FROM customers; 
BEGIN 
   OPEN c_customers; 
   LOOP 
   FETCH c_customers into c_id, c_name, c_addr; 
      EXIT WHEN c_customers%notfound; 
      dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr); 
   END LOOP; 
   CLOSE c_customers; 
END; 
/

Reference: Read more

- Triggers

CREATE OR REPLACE TRIGGER display_salary_changes 
BEFORE DELETE OR INSERT OR UPDATE ON customers 
FOR EACH ROW 
WHEN (NEW.ID > 0) 
DECLARE 
   sal_diff number; 
BEGIN 
   sal_diff := :NEW.salary  - :OLD.salary; 
   dbms_output.put_line('Old salary: ' || :OLD.salary); 
   dbms_output.put_line('New salary: ' || :NEW.salary); 
   dbms_output.put_line('Salary difference: ' || sal_diff); 
END; 
/

Triggering a Trigger:

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) 
VALUES (7, 'Kriti', 22, 'HP', 7500.00 );

Reference: Read more

cadt-g6 / prepare-for-exam

Final Database Administration Exam (Term 4 ) #4

I. Question?

1. Should we always have a foreign key in the table? Why and Why not?

2. What is Views?

3. Explain by your understanding about the different between Views and Tables

4. When do we need to create User-defined Exceptions?

5. What is the different between implicit Cursors and Explicit Cursors?

II. SQL

1. Write a SQL query to find the total employees in department 30.

2. Write a SQL query to find the total salary that we need to pay for all employees.

3. Display the last name and department number of all employees in departments 20 or 50 in ascending alphabetical order by name

4. Write a query to display the first name and salary of any employee in the same department as Zlotkey

5. Write a query to find the hire_date of the first employees;

III. PL/SQL

1. Write a PL/SQL query to find the department name that cost the least salary

2. Write a trigger to display the old salary and the new salary whenever there is a change on salary of employees

3. Write a PL/SQL to increase the salary of all employees who work more than 10 years by 20%

Assignment 3

1. List all departments

2. List all employees in department ​ Finance

My answer:

3. List 10 employees who are currently work in department ​ Finance

My answer:

4. List all Female employees

5. Count the number of female employees in department ​ Finance

My answer:

6. List the employee who has the highest salary in department ​ Finance

My answer:

7. Count the number of employee in each department

My answer:

8. List the employee who has the highest salary

My answer:

9. List the female employee who get the highest salary

My answer:

10. List the department that pay the highest salary

11. List the manager of each department

12. List all previous managers of each department

13. Get the salary of each department manager

14. List all title in department ​ Finance

15. List the employees who work in more than one department

16. List the employees who has more than one title

Today Exam Preparation:

1. Data Manipulation

2. View

3. PL/SQL CONDITIONAL CONTROL, LOOPS

Loop

- Condition Control

4. PL/SQL Precedure, Functions, Cursor, Triggers

- Precedure

- Functions

- Triggers

5. More...

2. List all employees in department Finance

3. List 10 employees who are currently work in department Finance

5. Count the number of female employees in department Finance

6. List the employee who has the highest salary in department Finance

14. List all title in department Finance