SQL commands

Question 1

How to delete multiple rows in SQL where id = (x to y)?

Answer 1

DELETE FROM vhd_product WHERE prd_id in (ID#1, ID#2, ID#3);

Question 2

How to join tables?

Answer 2

SELECT t.rtf_field_id, d.fdf_name, d.fdf_type
FROM vhd_report template_field t
JOIN vhd_field_definition d
ON t.rtf_field_id = d.fdf_id
WHERE t.rtf_field_id = 1102

Question 3

Applying comments to code

Answer 3

–
/* comment just as in
JavaScript */
Ctrl + / (zamienia zaznaczony obszar) na komentarz

Question 4

What is the difference between HAVING and WHERE

Answer 4

The main difference between WHERE and HAVING clause comes when used together with GROUP BY clause, In that case WHERE is used to filter rows before grouping and HAVING is used to exclude records after grouping.

One applies before and one after operations on an aggregated data set

Question 5

What are the options to concatenate

Answer 5

You can always join strings in one column using the || character.

SELECT CONCAT(“SQL “, “Tutorial “, “is “, “fun!”) AS ConcatenatedString;

Question 6

WHERE operators

Answer 6

=
<> lub !

>=, <=
BETWEEN
LIKE
IN
AND
OR

Question 7

Show employees that have:
3 letter last name starting with S
OR first_name starting with R

Answer 7

SELECT * FROM employees

WHERE last_name LIKE ’S__’ OR first_name LIKE ’R%’;

Question 8

Show records without duplicates

Answer 8

SELECT DISTINCT column_name,column_name FROM table_name;

Question 9

Substitute a value when a null value is encountered

Answer 9

NVL( string1, replace_with )

Question 10

Date functions to_date, to_char

Answer 10

The Oracle/PLSQL TO_DATE function converts a string to a date.

The Oracle/PLSQL TO_CHAR function converts a number or date to a string.

Question 11

Basic grouping functions in PLSQL

Answer 11

AVG(), COUNT(), MAX(), MIN(), SUM(), GROUP BY

Question 12

INTERSECT

Answer 12

The SQL INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries or data sets. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Question 13

MINUS (PLSQL) / EXCEPT

Answer 13

Two queries connected by the minus operator will return all lines of the first query that do not appear in the second query.

Question 14

UNION (ALL)

Answer 14

Two queries connected by the union operator will return all elements of both queries.
Duplicated records will be removed (unless we add ALL)

Question 15

Structure of nested queries

Answer 15

SELECT e.first_name, e.last_name, e.salary, d.department_name
FROM employees e
JOIN departments d
ON e.employee_id = d.manager_id
WHERE e.salary < (SELECT MEDIAN(salary)
FROM employees e
JOIN departments d
ON e.employee_id = d.manager_id)
AND e.last_name like ‘H%’;

Question 16

RPAD function

Answer 16

The Oracle/PLSQL RPAD function pads the right-side of a string with a specific set of characters (when string1 is not null).

RPAD(‘tech’, 8, ‘0’)
Result: ‘tech0000’

LPAD works similarly

Question 17

SUBSTR / SUBSTRING function

Answer 17

Extract a substring from a string (start at position 1, extract 2 characters):

SELECT SUBSTR(`nazwisko`, 1,2)
FROM `osoby`

Question 18

Creating new tables

Answer 18

CREATE TABLE KOPIA_EMPLOYEES AS

select * FROM employees;

Question 19

Deleting tables

Answer 19

DROP TABLE KOPIA_EMPLOYEES

Question 20

What is a soft delete?

Answer 20

“Soft delete” in database lingo means that you set a flag on an existing table which indicates that a record has been deleted, instead of actually deleting the record.

Question 21

Updating cells in SQL

Answer 21

UPDATE vhd_document SET doc_product_ref_id = null WHERE doc_product_ref_id = 43287;