SQL Statements Explained

• DELETE FROM customer WHERE id = 1; ROLLBACK; performs a delete operation and then attempts to undo it.

• The code snippet deletes a row from the customer table where the id is 1, then attempts to rollback the operation.

• UNION ALL is faster than UNION in terms of performance.

• SELECT * FROM employees WHERE rownum = 5; will yield no rows selected.

• The command to search for NULL values is IS NULL.

• When multiple columns collectively act as the Primary Key, it is referred to as a Composite Key.

SQL Command Output

• The command below will result in an error because a group function is not allowed in the WHERE clause.

  SELECT department_id, AVG(salary)
  FROM employees
  WHERE AVG(salary) > 8000
  GROUP BY department_id;
  

• If SELECT count(product_id) FROM order; gives 15 outputs, then SELECT count(DISTINCT product_id) FROM order; will give <= 15 outputs.

• The SQL keyword used to combine the results of two or more SELECT statements is UNION.

• LEFT JOIN returns all records from the left table and matching records from the right table.

• The SQL keyword used to retrieve data from a database table is SELECT.

• The SQL statement used to make permanent changes to the database is COMMIT.

• In SQL, the operator used to check for matching values in a specified list is IN.

Select e.name, d.name 
FROM employees e 
JOIN departments d 
ON e.department_id = d.department_id;

• The command above is equivalent to the command below

Select e.name, d.name 
FROM employees e 
NATURAL JOIN departments d;

• Concat is a single-row function.

select substr('helloworld', -1, instr('helloworld', 'I', 3, 2)) from dual;

• The output of the SQL function above is d.

• SQL Aggregate functions always return single values.

• In the CONCAT function, a literal can refer to a number, character, or date.

• || is the symbol for the Concatenation Operator.

• The query below finds the total number of employees working in 'HR' departments.

select count(*) 
from employees 
where department_id = (
  Select id 
  from departments
  where name = 'HR'
);

• The query below finds the total rating of the sailors who have reserved boat 103

SELECT SUM(s.rating) 
FROM sailors s, reserves r 
WHERE s.sid = r.sid AND r.bid = 103;